Как создать новую таблицу в sql. Создание БД с помощью SQL
Для этого понадобится установленная система управления базами данных (СУБД) DB2. Мы будем использовать диалект языка SQL, который используется именно в этой СУБД.
Первая команда, которую мы будем применять для создании базы данных - это команда CREATE DATABASE. Её синтаксис следующий:
CREATE TABLE ИМЯ_ТАБЛИЦЫ (имя_первого_столбца тип данных, ..., имя_последнего_столбца тип данных, первичный ключ, ограничения (не обязательно))
Так как наша база данных моделирует сеть аптек, то в ней есть такие сущности, как "Аптека" (таблица Pharmacy в нашем примере создания базы данных), "Препарат" (таблица Preparation в нашем примере создания базы данных), "Доступность (препаратов в аптеке)" (таблица Availability в нашем примере создания базы данных), "Клиент" (таблица Client в нашем примере создания базы данных) и другие, которые здесь подробно и разберём.
Разработке модели "сущность-связь" можно посвятить не одну статью, но если нас прежде всего интересуют команды языка SQL для создания базы данных и таблиц в ней, то условимся считать, что связи между сущностями уже нам понятны. На рисунке ниже приведено представление модели нашей базы данных с атрибутами сущностей (таблиц) и связями между таблицами.
Для увеличения рисунка можно нажать на него левой кнопкой мыши.
Как уже говорилось, в разбираемом здесь примере создания базы данных использовался вариант языка SQL, который используется в системе управления базами данных (СУБД) DB2. Он является регистронезависимым, то есть не имеет значение, набраны ли команды и отдельные слова в них строчными или прописными буквами. Для иллюстрации этой особенности приведены команды без особой системы набранные строчными и прописными буквами.
Теперь приступим к созданию команд. Первая наша команда SQL создаёт базу данных PHARMNETWORK:
Код SQL
CREATE DATABASE PHARMNETWORK
Описание таблицы PHARMACY (Аптека):
Пишем команду, которая создаёт таблицу PHARMACY (Аптека), значения первичного ключа PH_ID генерируются автоматически от 1 с шагом 1, вносится проверка на то, чтобы значения атрибута Address в этой таблице были уникальными:
Код SQL
CREATE TABLE PHARMACY(PH_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Address varchar(40) NOT NULL, PRIMARY KEY(PH_ID), CONSTRAINT PH_UNIQ UNIQUE(Address))
Описание таблицы GROUP (Группа препаратов):
Пишем команду, которая создаёт таблицу Group (Группа препаратов), значения первичного ключа GR_ID генерируются автоматически от 1 с шагом 1, проводится проверка уникальности наименования группы (для этого используется ключевое слово CONSTRAINT):
Код SQL
CREATE TABLE GROUP(GR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, PRIMARY KEY(GR_ID), CONSTRAINT GR_UNIQ UNIQUE(Name))
Описание таблицы PREPARATION (Препарат):
Команда, которая создаёт таблицу PREPARATION, значения первичного ключа PR_ID генерируются автоматически от 1 с шагом 1, определяется, что значения внешнего ключа GR_ID (Группа препаратов) не могут принимать значение NULL, определена проверка уникальности значений атрибута Name:
Код SQL
CREATE TABLE PREPARATION(PR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, GR_ID int NOT NULL, PRIMARY KEY(PR_ID), constraint PR_UNIQ UNIQUE(Name))
Далее нам требуется позаботиться об ограничениях целостности. Это очень удобно слелать с помощью команды alter table. Эта команда изучается на уроке SQL ALTER TABLE - изменение таблицы базы данных.
Теперь самое время создать таблицу AVAILABILITY (Доступность или Наличие препарата в аптеке). Её описание:
Пишем команду, которая создаёт таблицу AVAILABILITY. Определяются даты начала (не может быть NULL) и окончания (по умолчанию NULL).
Код SQL
CREATE TABLE AVAILABILITY(A_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL, QUANTITY INT NOT NULL, MART varchar(3) DEFAULT NULL, PRIMARY KEY(A_ID), CONSTRAINT AVA_UNIQ UNIQUE(PH_ID, PR_ID))
Создаём таблицу DEFICIT (Дефицит препарата в аптеке, то есть, неудовлетворённый запрос). Её описание:
Пишем команду, которая создаёт таблицу DEFICIT:
Код SQL
CREATE TABLE DEFICIT(D_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, Solution varchar(40) NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL)
Осталось немного. Мы уже дошли до команды, которая создаёт таблицу Employee (Сотрудник). Её описание:
Пишем команду, которая создаёт таблицу Employee (Сотрудник), с первичным ключом, генерируемым по тем же правилам, что и первичные ключи предыдущих таблиц, в которых они существуют. Внешним ключом PH_ID Сотрудник связан с PHARMACY (Аптекой).:
Код SQL
CREATE TABLE EMPLOYEE(E_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), F_Name varchar(40) NOT NULL, L_Name varchar(40) NOT NULL, POST varchar(40) NOT NULL, PH_ID INT NOT NULL, PRIMARY KEY(E_ID))
Очередь дошла до создании таблицы CLIENT (Клиент). Её описание:
Пишем команду, создающую таблицу CLIENT (Клиент), в отношении первичного ключа которого справедливо предыдущее описание. Особенность этой таблицы в том, что её атрибуты F_Name и L_Name имеют по умолчанию значение NULL. Это связано с тем, что клиенты могут быть как зарегистрированными, так и незарегистрированными. У последних значения имени и фамилии как раз и будут неопределёнными (то есть NULL):
Код SQL
CREATE TABLE CLIENT(C_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), FName varchar(40) DEFAULT NULL, LName varchar(40) DEFAULT NULL, DateReg varchar(20), PRIMARY KEY(C_ID))
Предпоследняя таблица в нашей базе данных - таблица BASKET (Корзина покупок). Её описание:
Пишем команду, создающую таблицу BASKET (Корзина покупок), так же с уникальным и инкрементируемым первичным ключом и связанную внешним ключами C_ID и E_ID с Клиентом и Сотрудником соответственно:
Код SQL
CREATE TABLE BASKET(BS_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), C_ID INT NOT NULL, E_ID INT NOT NULL, PRIMARY KEY(BS_ID))
И, наконец, последняя таблица в нашей базе данных - таблица BUYING (покупка). Её описание:
Имя поля | Тип данных | Описание |
B_ID | smallint | Идентификационный номер покупки |
PH_ID | smallint | Идентификационный номер аптеки |
PR_ID | smallint | Идентификационный номер препарата |
BS_ID | varchar(40) | Идентификационный номер корзины покупок |
Price | varchar(20) | Цена |
Date | varchar(20) | Дата |
Пишем команду, создающую таблицу BUYING (покупка), так же с уникальным и инкрементируемым первичным ключом и связанную внешними ключами BS_ID, PH_ID, PR_ID с Корзиной покупок, Аптекой и Препаратом соответственно:
Код SQL
CREATE TABLE BUYING(B_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BS_ID INT NOT NULL, PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateB varchar(20) NOT NULL, Price Double NOT NULL, PRIMARY KEY(B_ID))
И совсем уже в завершение темы создания базы данных обещанное отступление о соблюдении ограничений целостности, когда решение - более сложное, чем написание команды. В нашем примере необходимо соблюдать следующее условие: при покупке единицы препарата значение количества этого препарата в таблице AVAILABILITY должно соответственно уменьшиться. Вообще говоря, для таких операций в языке SQL существуют особые средства, называемые триггерами. Но триггеры - вещь капризная: на практике они могут и не сработать или сработать не так, как предусмотрено. Поэтому разработчики по возможности ищут программные средства решения таких задач, пример которых упомянут в этом абзаце.
Вступление
Продолжим изучение SQL запросов к базе данных. Сегодня, один из простых SQL запросов, SQL запрос для создания таблицы базы данных. Работает запрос на основе оператора CREATE TABLE.
Создание таблицы базы данных
Есть оператор sql, который используется для создания таблиц баз данных. Оператор: CREATE TABLE.
Вспоминаем, что реляционная таблица базы данных должна иметь уникальные:
- Уникальное имя;
- Столбцы (атрибуты);
- Первичный ключ.
- Строки (записи);
Для начала, мы создаем, только таблицу и не заполняем её данными.
По-хорошему, общий вид таблицы с уникальными именами строк и столбцов задается при создании . Мы для примера создадим простенькую таблицу БД и обойдемся без концептуальной модели.
В этой статье создадим таблицу БД с покупателями. Покупатели взяты для примера.
Чем идентифицируется покупатель? Решаем, что каждый покупатель идентифицируется следующими данными:
- Фамилия;
- Логин;
- Пароль;
- Email;
- телефон.
Помним, что в таблице обязательно нужно задать .
Чтобы создать таблицу базы данных, используем оператор sql CREATE TABLE.
Синтаксис оператора такой:
CREATE TABLE имя таблицы (столбец 1, тип столбца (пробел) параметр столбца (пробел), столбец 2, тип столбца (пробел) параметр столбца (пробел), столбец 3, тип столбца (пробел) параметр столбца (пробел), и т.д.)
В нашем варианте, имя таблицы: clients. Даем данным покупателей обозначения столбцов:
- Имя: пусть будет client_customer;
- Фамилия: client_subclient;
- Логин: client_login;
- Пароль: client_password;
- eMail: client_mail;
- Телефон: client_telefon.
Думаем, какой тип данных будет храниться в этих столбцах, и выбираем типы данных CHAR(длина поля постоянно) или VARCHAR(длинна поля переменное). В нашем случае подходит тип данных VARCHAR.
Особое внимание уделяем первичному ключу. Делаем первичным ключом id покупателя. В параметрах ключа задаем, что это первичный ключ, он не может быть нулём и что для следующей записи увеличивается на единицу. Последнее свойство называется автоинкремент. Смотрим подсказку в справочнике (ссылка выше) и получаем столбец с первичным ключом:
Client_id integer not null auto_increment primary key
Пишем SQL запрос для создания таблицы базы данных
Суммируем все исходные данные и получаем такой SQL запрос:
/*Таблица пользователей clients*/ create table clients (/*client_id будет первичный ключ (обязательно целое число) с автоинкрементом (+1), который никогда не будет равен нулю*/ client_id integer not null auto_increment primary key, client_customer varchar(13), /*имя */ client_surclient varchar(22), /*фамилия */ client_login varchar(21), /*логин*/ client_passwd varchar(7), /*пароль*/ client_email varchar(44) /*email*/ client_telefon varchar(26) /*телефон*/);
Примечание: SQL запрос для создания таблицы создаем в текстовом редакторе типа Notepad++. В скобках ограничиваем длину поля, может быть от 1 до 255.
Итоги статьи
- В этой статье мы познакомились с первым оператором SQL языка CREATE TABLE
- Создали SQL запрос для создания одной (первой) таблицы базы данных с первичным ключом.
Язык SQL используется не только для обработки информации, но и предназначена для выполнения всех операций с базами данных и таблицами, включая также создание таблиц и работа с ними. Существует два способа создания таблиц: 1) большинство СУБД обладают визуальным интерфейсом для интерактивного создания таблиц и управление ими; 2) таблицами можно манипулировать, используя операторы SQL. Стоит отметить, что, когда вы используете интерактивный инструментарий СУБД, на самом деле вся работа выполняется операторами SQL, т.е. интерфейс сам создает эти команды незаметно для пользователя (это подобно на запись макроса в Excel, когда макрорекодер записывает ваши действия и преобразует их в команды VBA).
1. Создание таблиц
Для создания таблиц программным способом используют оператор CREATE TABLE . Для этого нужно указать следующие данные:
- - имя таблицы, которое указывается после ключевого слова CREATE TABLE
- - имена и определения столбцов таблицы, отделены запятыми
- - в некоторых СУБД также требуется, чтобы было указано местоположение таблицы.
Давайте создадим новую таблицу и назовем ее Customers :
CREATE TABLE
Customers (
ID CHAR(10)
NOT NULL Primary key
,
Custom_name CHAR(25)
NOT NULL,
Custom_address CHAR(25)
NULL,
Custom_city CHAR(25)
NULL,
Custom_Country CHAR(25)
NULL,
ArcDate CHAR(25)
NOT NULL, DEFAULT
NOWO)
Так мы сначала указываем название новой таблицы, затем в скобках перечисляем столбцы, которие будем создавать, причем их названия не могут повторяться в пределах одной таблицы. После названий столбцов указывается тип данных для каждого поля (CHAR (10) ), затем отмечаем может ли поле содержать пустые значения (NULL или NOT NULL ), а также нужно указать поле, которое будет первичным ключом (Primary key ).
Язык SQL также позволяет определять для каждого поля значение по умолчанию, то есть, если пользователь не укажет значение определенного поля - оно будет автоматически проставлено СУБД. Значение по умолчанию определяется ключевым словом DEFAULT при определении столбцов оператором CREATE TABLE .
2. Обновление таблиц
Для того, чтобы изменить таблицу в SQL используется оператор ALTER TABLE . При использовании данного оператора необходимо ввести следующую информацию:
- - имя таблицы, которую мы хотим изменить
- - перечень изменений, которые мы хотим сделать.
Для примера давайте добавим новую колонку в таблицу Sellers , в которой будем указывать телефон реализатора:
ALTER TABLE Sellers ADD Phone CHAR (20)
Кроме добавления столбцов, мы можем их удалять. Давайте теперь удалим поле Phone . Для этого пропишем следующий запрос:
ALTER TABLE Sellers DROP COLUMN Phone
3. Удаление таблиц
Удаление таблиц осуществляется с помощью оператора DROP TABLE . Чтобы удалить таблицу Sellers_new , мы можем прописать следующий запрос:
DROP TABLE Sellers_new
Во многих СУБД применяются правила, предотвращающие удаление таблиц, которые являются уже связаны с другими таблицами. Если эти правила действуют и вы удаляете такую таблицу, то СУБД блокирует операцию удаления до тех пор, пока не будет удалена связь. Такие меры предотвращают случайное удаление нужных таблиц.
Раздел 4 Информационные системы
Введение в SQL.
Создание, изменение и удаление таблиц.
Выборка данных из таблицы.
Создание SQL-запросов.
Обработка данных в SQL.
Методика обучения данной теме в школе.
Введение в SQL. SQL - структурированный язык запросов, который дает возможность создавать и работать в реляционных базах данных, которые являются наборами связанной информации сохраняемой в таблицах. Язык ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц-отношений. Важнейшая особенность структур этого языка состоит в ориентации на конечный рез-тат обработки данных, а не на процедуру этой обработки. SQL сам определяет, где находятся данные, индексы и даже какие наиболее эффективные последовательности операций следует использовать для получения рез-та.
Изначально, SQL был основным способом работы пользователя с базой данных и позволял выполнять следующий набор операций: создание в базе данных новой таблицы; добавление в таблицу новых записей; изменение записей; удаление записей; выборка записей из одной или нескольких таблиц (в соответствии с заданным условием); изменение структур таблиц.
Со временем SQL обеспечил возможность описания и управления новыми хранимыми объектами (например, индексы, представления, триггеры и хранимые процедуры). SQL остаётся единственным механизмом связи между прикладным программным обеспечением и базой данных. В то же время, современные СУБД, а, также, информационные системы, использующие СУБД, предоставляют пользователю развитые средства визуального построения запросов. Каждое предложение SQL - это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе.
В соответствии с тем, какие изменения происходят в базе данных, различают следующие типы запросов: на создание или изменение в базе данных новых или существующих объектов; на получение данных; на добавление новых данных (записей); на удаление данных; обращения к СУБД.
Основным объектом хранения реляционной базы данных является таблица, поэтому все SQL-запросы - это операции над таблицами. В соответствии с этим, запросы делятся на:
Запросы, оперирующие самими таблицами (создание и изменение таблиц);
Запросы, оперирующие с отдельными записями (или строками таблиц) или наборами записей.
Каждая таблица описывается в виде перечисления своих полей (столбцов таблицы) с указанием: типа хранимых в каждом поле значений; связей между таблицами (задание первичных и вторичных ключей); информации, необходимой для построения индексов.
Таким образом, использование SQL сводится, по сути, к формированию всевозможных выборок строк и совершению операций над всеми записями, входящими в набор.
Команды SQL разделяются на следующие группы:
1. Команды языка определения данных - DDL (Data Definition Language). Эти SQL команды можно использовать для создания, изменения и удаления различных объектов базы данных.
2. Команды языка управления данными - DCL (Data Control Language). С помощью этих SQL команд можно управлять доступом пользователей к базе данных и использовать конкретные данные (таблицы, представления и т.д.).
3. Команды языка управления транзакциями - TCL (Тгаnsасtiоn Соntrol Language). Эти SQL команды позволяют определить исход транзакции.
4. Команды языка манипулирования данными - DML (Data Manipulation Language). Эти SQL команды позволяют пользователю перемещать данные в базу данных и из нее.
Операторы SQL делятся на:
Операторы определения данных (Data Definition Language, DDL )
CREATE создает объект БД (саму базу, таблицу, представление, пользователя и т. д.)
ALTER изменяет объект
DROP удаляет объект
Операторы манипуляции данными (Data Manipulation Language, DML )
SELECT считывает данные, удовлетворяющие заданным условиям
INSERT добавляет новые данные
UPDATE изменяет существующие данные
DELETE удаляет данные
Операторы определения доступа к данным (Data Control Language, DCL )
GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом
REVOKE отзывает ранее выданные разрешения
DENY задает запрет, имеющий приоритет над разрешением
Операторы управления транзакциями (Transaction Control Language, TCL )
COMMIT применяет транзакцию.
ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции.
SAVEPOINT делит транзакцию на более мелкие участки.
Преимущества: 1.Независимость от конкретной СУБД (тексты SQL-запросов, содержащие DDL и DML, могут быть достаточно легко перенесены из одной СУБД в другую). 2. Наличие стандартов (наличие стандартов и набора тестов для выявления совместимости и соответствия конкретной реализации SQL общепринятому стандарту только способствует «стабилизации» языка). 3. Декларативность (с помощью SQL программист описывает только то, какие данные нужно извлечь или модифицировать)
Недостатки: 1.Несоответствие реляционной модели данных 2.Повторяющиеся строки 3. Неопределённые значения (nulls) 4. Явное указание порядка колонок слева направо 5. Колонки без имени и дублирующиеся имена колонок 6. Отсутствие поддержки свойства «=» 7. Использование указателей 8. Высокая избыточность
2.2 Создание, изменение и удаление таблиц.
Создание таблицы:
Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу - таблицу без строк. Значения вводятся с помощью DML команды INSERT. Команда CREATE TABLE в основном определяет им таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.
Синтаксис команды:
CREATE TABLE
(
Изменение таблицы:
Команда ALTER TABLE – это содержательна форма, хотя ее возможности несколько ограничены. Она используется чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Типичный синтаксис чтобы добавить столбец к таблице:
ALTER TABLE