Создание таблиц базы данных в SQL

Учебная база данных «Чемпионат мира по футболу»

В качестве второго учебного примера будем использовать БД «Чемпионат мира по футболу». Предметная область этой базы данных достаточно понятна большинству слушателей и не требует детальных пояснений, а с другой стороны примеры запросов к этой БД очень содержательны. Объекты БД приведены в Таблицах 5.1-5.3.

Создание таблиц и индексов

Перед тем как выбирать данные из БД, их нужно в нее ввести, а для того чтобы ввести данные, необходимо создать таблицу, в которой данные будут храниться. Для определения и создания таблиц в SQL предусмотрена команда CREATE TABLE, которая определяет имя таблицы, имена и физический порядок колонок для нее, тип каждой колонки, а также некоторые указания для СУБД, такие, как определение первичного или внешнего ключа, требования на запрет неопределенных значений в колонке таблицы и т.п.

Таблица Игры (IGR) содержит информацию об играх Чемпионата мира по футболу. Для того чтобы создать таблицу нужно ввести следующую команду:

CREATE TABLE  IGR(---  имя таблицы
	GOD  		INT  		NOT NULL,  ---  имя колонки, тип, длина
	GRUP		CHAR(1)	        NOT NULL,
	IGRA 		INT 		NOT NULL,
	KOMANDAA 	CHAR(30),
	GOLA 		INT,
	KOMANDAB 	CHAR(30),
	GOLB 		INT,  
PRIMARY KEY	(GOD, GRUP,  IGRA));  --- определение первичного ключа

Хорошим тоном в приложениях реляционных БД является определение первичного ключа таблицы, это позволяет избежать сложностей при обеспечении целостности данных и конструировании соединений таблиц. Атрибуты первичного ключа должны быть определены как NOT NULL, т.е. они не должны иметь неопределенных значений. Спецификация NOT NULL является одним из примеров того, как СУБД Oracle контролирует значения данных при их вводе в БД, в частности, и чтобы поддерживать заданные вами условия ссылочной целостности. Подобный механизм контроля данных носит название автоматической проверки данных.

Если вы не указываете спецификацию NOT NULL при определении колонки, то значения колонки допускают наличие неопределенных значений, т.е. вы можете не предоставлять их при вводе данных в таблицу.

Спецификация PRIMARY KEY определяет первичный ключ таблицы. Атрибуты первичного ключа перечисляются через запятую и заключаются в круглые скобки. Если спецификация PRIMARY KEY не определяется, то считается, что таблица не имеет первичного ключа. При этом допускается дублирование строк в таблице.

Когда вы определяете PRIMARY KEY при создании таблицы, СУБД требует обязательного создания уникального индекса первичного ключа (SQL Developer создаст уникальный индекс автоматически по ограничению PRIMARY KEY). Индексы, так же как и таблицы, являются объектами реляционной БД (но не реляционной модели). Логически индексы представляют собой таблицу, в которой каждому значению индексируемой колонки ставится в соответствие некоторая информация, связанная с ее месторасположением на физическом носителе. Индексы предназначены для организации быстрого доступа к строкам отношения и обеспечения контроля целостности данных (механизм индексов будет блокировать БД от повторного ввода строк в таблицу с одинаковыми значениями индексируемых атрибутов). Индекс создания с помощью команды

CREATE UNIQUE INDEX IRG ON IGR (GOD, GRUP, MESTO);

Предложение CREATE INDEX определяет имя индекса, предложение ON определяет имя таблицы и колонок, для которой и по которым строится индекс, ключевое слово UNIQUE указывает, что индексируемые значения колонок должны быть уникальными для таблицы, т. е. исключается дублирование значений в индексируемой колонке. Таблица должна быть уже создана и содержать определения индексируемых столбцов. Спецификация UNIQUE опциональна, можно создавать и неуникальные индексы.

Определение спецификаций первичного ключа, требования ненулевых значений при вводе строк в таблицу представляют собой определение ограничений на множество допустимых значений данных. Ограничения (constraints) являются частью определения таблицы и бывают двух основных типов: ограничения на колонки (применяются только к значениям отдельных колонок) и ограничения на таблицу (применяются к группам колонок). Как можно заметить, ограничения объявляются указанием соответствующей спецификации через пробел после объявления типа колонки.

Из примеров видно, как задаются ограничения для исключения нуль-значений. Ограничение уникальности значений колонки поддерживается через определение ограничения исключения нуль-значений колонки и создания уникального индекса для этой колонки. Уникальность как ограничение таблицы реализуется через задание уникальности значений группы колонок (исключение в них нуль-значений и задание для этой группы уникального индекса). Следует иметь в виду, поддержка ограничений уникальности может отличаться в различных реализациях SQL. Так в некоторых реализациях имеется спецификация колонки UNIQUE, которая позволяет избежать использования индексов в обеспечении уникальности значений колонки.

Ограничение первичного ключа применяется к таблице в целом. По своему определению первичный ключ не может иметь нулевых значений и должен быть уникальным для каждой строки таблицы. Таблица не может иметь более одного первичного ключа.

Добавление строки в таблицу

Добавление строк в таблицу в SQL выполняется командой INSERT. Чтобы вставить конкретную строку в таблицу IGR, Вам необходимо написать команду

INSERT INTO IGR                - определение таблицы
(GOD,GRUP,IGRA,KOMANDAA,GOLA,KOMANDAB,GOLB) - список имен колонок
VALUES                                 - значения колонок
(2018,’A’,5,’Саудовская Аравия’,2,’Египет’,1);

Если вставляемая строка включает в себя значения всех колонок таблицы, то список имен колонок в круглых скобках (предложение INTO) может быть опущен. В этом случае значения вводятся в колонки в соответствии с их физическим порядком. При определении списка имен колонок значения вводятся в колонки в соответствии с указанным порядком следования колонок. Используя список имен колонок, можно вставлять данные в отдельные колонки или группы колонок. Таблица должна существовать в БД до того, как данные будут добавляться в нее.

Список значений предложения VALUES не может содержать выражений. Допускаются только константы, системные переменные NULL, USER, SYSDATE.

Добавление группы строк в таблицу

С помощью команды INSERT можно вставить целую группу строк. Для этого необходимо использовать команду INSERT ALL. Так команда

INSERT ALL 
INTO IGR (GOD,GRUP,IGRA,KOMANDAA,GOLA,KOMANDAB,GOLB)
VALUES   (2018,’A’,5,’Саудовская Аравия’,2,’Египет’,1)
INTO  IGR (GOD,GRUP,IGRA,KOMANDAA,GOLA,KOMANDAB,GOLB)
VALUES (2018,’A’,6,’Уругвай’,3,’Россия’,0)
SELECT 1 FROM DUAL;

вставляет две строки в таблицу IGR.

Изменение значений колонок

Используя команду SQL UPDATE, вы можете решить задачу индексации зарплаты у различных категорий сотрудников. Допустим, что в результате индексации зарплата ИТ-разработчиков повысилась в 1,5 раза. Тогда, чтобы отразить этот факт в БД, необходимо выполнить команду

UPDATE EMPLOYEES
SET SALARY = 1.5 *  SALARY    -  предложение SET
WHERE JOB_ID= ' IT_PROG';

Предложение UPDATE именует таблицу, предложение SET устанавливает новое значение колонки, а предложение WHERE определяет те строки, в которых должно быть сделано изменение. Если WHERE не используется, то обновляются все строки таблицы. В предложении WHERE могут быть использованы подзапросы.

В предложении SET можно задать обновление значений нескольких колонок, разделив выражения запятой. Если используется нуль-значение при обновлении, то колонка должна допускать нуль-значения в определении таблицы. Если колонка имеет уникальный индекс, то обновление не должно нарушать уникальности значений колонки, иначе будет зарегистрирована ошибка.

Удаление строк из таблицы

Для того, чтобы удалять строки из таблицы в языке SQL существует команда DELETE. При удалении конкретных строк, их следует идентифицировать условием поиска в предложении WHERE команды DELETE и указать имя таблицы в предложении FROM. Допустим, что служащий Иванов уволился из организации. Тогда можно выполнить

DELETE
FROM    DEPARTMENTS
WHERE   LAST_NAME='Tiger';

Если предложение WHERE не задано, то удаляются все строки таблицы.

Изменение схемы базы данных

SQL поддерживает команды, позволяющие изменять логическую структуру базы данных динамически. Для этого предназначена команда ALTER TABLE, которая позволяет добавлять, удалять, переопределять колонки таблицы, а также их характеристики.

Добавление новой колонки в таблицу выполняется командой

ALTER TABLE EMPLOYEES
ADD PROJ_ID  CHAR(8);

Предложение ALTER TABLE именует таблицу, а предложение ADD добавляет новую колонку с описанием типа и возможно с определением спецификаций для исключения нуль-значений. Значения в новой колонке будут пустыми, т.е. содержать нуль-значения. Чтобы заполнить ее требуемыми данными, необходимо выполнить команду UPDATE.

Команда ALTER TABLE позволяет использовать целый ряд предложений не только для вставки колонок, но и для их удаления, модификации и определения внешних ключей.

Предположим, что колонка требует больше памяти, чем определенные ранее 25 символов, на пример необходимо иметь 40 символов. Тогда вы можете выполнить команду

ALTER TABLE EMPLOYEES
MODIFY LAST_NAME char(40);

Предложение MODIFY команды ALTER TABLE позволяет вам модифицировать определение колонки PNAME. Однако, в некоторых реализациях SQL такие действия не допустимы.

Если вы хотите переименовать LAST_NAME на NAME в таблице EMPLOYEES, то вы должны использовать предложение RENAME команды ALTER TABLE.

ALTER TABLE EMPLOYEES
RENAME LAST_NAME TO NAME;

Так же можно переименовать таблицу - RENAME TABLE.

Для удаления колонки из таблицы необходимо использовать предложение DROP команды ALTER TABLE. Предположим, что вам потребовалось удалить колонку SSECNO в таблице EMPLOYEE, тогда вы можете выполнить команду

ALTER TABLE EMPLOYEES
DROP LAST_NAME;

Если в колонке были данные, то они будут потеряны. Нельзя удалить проиндексированные колонки, в том числе и колонки первичного или внешнего ключей.

В SQL существует специальная команда DROP, которая позволяет удалять из БД ненужные таблицы и индексы. Так для того, чтобы удалить ненужную копию таблицы DEPARTMENTS необходимо выполнить запрос

DROP TABLE DEPT;

При этом удаляются не только определенные на таблице индексы, но и все привилегии таблицы и ряд других объектов БД, связанных с таблицей.

Когда вы работаете с базой данных и выполняете команды манипулирования данными, происходит изменение состояния базы данных. В SQL фиксация состояния базы данных выполняется командой COMMIT. Эта команда завершает текущую транзакцию и открывает следующую. Все изменения, сделанные командами манипулирования данными, фактически заносятся в таблицы базы данных.

COMMIT;
  SQL commands
COMMIT;