ЯЗЫК SQL_управление_таблицами


ЯЗЫК SQL
УПРАВЛЕНИЕ ТАБЛИЦАМИ базы данных
ПЛАН
Создание таблицы
Изменение существующей таблицы
Удаление таблицы
Ограничения на множество допустимых значений данных
Добавление данных в таблицу
Создание таблицы
Создание объектов базы данных осуществляется с помощью операторов языка определения данных (DDL).
Таблицы базы данных создаются с помощью команды CREATE TABLE. Эта команда создает пустую таблицу, то есть таблицу, не имеющую строк. Значения в эту таблицу вводятся с помощью команды INSERT. Команда CREATE TABLE определяет имя таблицы и множество поименованных столбцов в указанном порядке. Для каждого столбца должен быть определен тип и размер. Каждая создаваемая таблица должна иметь, по крайней мере, один столбец.
Синтаксис команды CREATE TABLE имеет следующий вид:
CREATE TABLE <ИМЯ ТабЛИЦЫ>
(<имя столбцаХтип данных>[(<размер>)]);
Следующий пример показывает команду, которая позволяет создать таблицу STUDENT.
CREATE TABLE STUDENT1
(STUDENT__ID INTEGER,
SURNAME VARCHAR(60;,
NAME VARCHAR (60),
STIPEND DOUBLE,
KURS INTEGER,
CITY VARCHAR(60),
BIRTHDAY DATE,
UNIV_ID INTEGER);
Изменение существующей таблицы
Для модификации структуры и параметров существующей таблицы используется команда ALTER TABLE.
В данном формате предусмотрены возможности для выполнения ряда действий:
добавить новый столбец в существующую таблицу — ADD COLUMN;
удалить столбец из существующей таблицы — DROP COLUMN;
добавить в определение таблицы новое ограничение — ADD CONSTRAINT;
удалить из определения таблицы существующее ограничение —DROP CONSTRAINT;
задать для существующего столбца значение по умолчанию —ALTER [COLUMN] SET DEFAULT;
отменить установленное для столбца значение по умолчанию ALTER [COLUMN] DROP DEFAULT.
Синтаксис команды ALTER TABLE для добавления столбцов в таблицу имеет вид
ALTER TABLE <ИМЯ ТабЛИЦЫ> ADD (<ИМЯ СТОЛбца> <ТИП ДАННЫХ><размер>);
По этой команде для существующих в таблице строк добавляется новый столбец, в который заносится NULL-значение.
Этот столбец становится последним в таблице. Можно добавлять несколько столбцов, в этом случае их определения в команде ALTER TABLE разделяются запятой.
Возможно изменение описания столбцов. Часто это связано с изменением размеров столбцов, добавлением или удалением ограничений, накладываемых на их значения. Синтаксис команды в этом случае имеет вид
ALTER TABLE <ИМЯ ТАБЛИЦЫ> MODIFY <ИМЯ СТОЛбЦА> <ТИП ДАННЫХ><размер/точность >;
Следует иметь в виду, что модификация характеристик столбца может осуществляться не в любом случае, а с учетом следующих ограничений:
• изменение типа данных возможно только в том случае, если столбец пуст;
• для незаполненного столбца можно изменять размер/точность. Для заполненного столбца размер/точность можно увеличить, но нельзя понизить;
• ограничение NOT NULL может быть установлено, если ни одно значение в столбце не содержит NULL. Опцию NOT NULL всегда можно отменить;
• разрешается изменять значения, установленные по умолчанию.
Удаление таблицы
Чтобы удалить существующую таблицу, необходимо предварительно удалить все данные из этой таблицы, то есть сделать ее пустой. Таблица, имеющая строки, не может быть удалена.
Синтаксис команды, осуществляющей удаление пустой таблицы, имеет следующий вид:
DROP TABLE <имя таблицы>;
Ограничения на множество допустимых значений данных
До сих пор рассматривалось только следующее ограничение — значения, вводимые в таблицу, должны иметь типы данных и размеры, совместимые с типами/размером данных столбцов, в которые эти значения вводятся (как определено в команде CREATE TABLE или ALTER TABLE). Описание таблицы может быть дополнено более сложными ограничениями, накладываемыми на значения, которые могут быть вставлены в столбец или группу столбцов. Ограничения (CONSTRAINTS) являются частью определения таблицы.
При создании (изменении) таблицы могут быть определены ограничения на вводимые значения. В этом случае SQL будет отвергать любое из них при несоответствии заданным критериям.
Ограничения могут быть статическими, ограничивающими значения или диапазон значений, вставляемых в столбец (CHECK,NOT NULL). Они могут иметь связь со всеми значениями столбца, ограничивая новые строки значениями, которые не содержатся в столбцах или их наборах (уникальные значения, первичные ключи). Ограничения могут также определяться связью со значениями, находящимися в другой таблице, допуская, например, вставку в столбец только тех значений, которые в данным момент содержатся также в другом столбце другой или этой ,же таблицы (внешний ключ). Эти ограничения носят динамический характер.
Ограничение NOT NULL
Чтобы запретить возможность использования в поле NULL-значений, можно при создании таблицы командой CREATE TABLE указать для соответствующего столбца ключевое слово NOT NULL.
Это ограничение применимо только к столбцам таблицы. Как уже говорилось выше, NULL — это специальный маркер, обозначающий тот факт, что поле пусто. Но он полезен не всегда. Первичные ключи, например, в принципе не должны содержать NULL-значений (быть пустыми), поскольку это нарушило бы требование уникальности первичного ключа (более строго — функциональную зависимость атрибутов таблицы от первичного ключа). Во многих других случаях также необходимо, чтобы поля обязательно содержали определенные значения. Если ключевое слово NOT NULL размещается непосредственно после типа данных (включая размер) столбца, то любые попытки оставить значение поля пустым (ввести в поле NULL-значение) будут отвергнуты системой.
Например, для того, чтобы в определении таблицы STUDENT запретить использование NULL-значений для столбцов STUDENT_ID, SURNAME и NAME, можно записать следующее:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER NOT NULL,
SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER,
KURS INTEGER,
CITY CHAR (15),
BIRTHDAY DATE,
UNIV_ID INTEGER);
Важно помнить: если для столбца указано NOT NULL, то при использовании команды INSERT обязательно должно быть указано конкретное значение, вводимое в это поле. При отсутствии ограничения NOT NULL в столбце значение может отсутствовать, если только не указано значение столбца по умолчанию (DEFAULT). Если при создании таблицы ограничение NOT NULL не было указано, то его можно указать позже, используя команду ALTER TABLE. Однако для того, чтобы для вновь вводимого с помощью команды ALTER TABLE столбца можно было задать ограничение NOT NULL, таблица, в которую добавляется столбец, должна быть пустой.
Уникальность как ограничение на столбец
Иногда требуется, чтобы все значения, введенные в столбец, отличались друг от друга. Например, этого требуют первичные ключи. Если при создании таблицы для столбца указывается ограничение UNIQUE, то база данных отвергает любую попытку ввести в это поле какой-либо строки значение, уже содержащееся в том же поле другой строки. Это ограничение применимо только к тем полям, которые были объявлены NOT NULL. Можно предложить следующее определение таблицы STUDENT, использующее ограничение UNIQUE:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER NOT NULL UNIQUE,
SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER,
KURS INTEGER,
CITY CHAR (15),
BIRTHDAY DATE,
UNIV_ID INTEGER);
Объявляя поле STUDENT_ID уникальным, можно быть уверенным, что в таблице не появится записей для двух студентов с одинаковыми идентификаторами. Столбцы, отличные от первичного ключа, для которых требуется поддержать уникальность значений, называются возможными ключами или уникальными ключами (CANDIDATE KEYS ИЛИ UNIQUE KEYS).
Уникальность как ограничение таблицы
Можно сделать уникальными группу полей, указав UNIQUE в качестве ограничений таблицы. При объединении полей в группу важен порядок, в котором они указываются. Ограничение на таблицу UNIQUE является полезным, если требуется поддерживать уникальность группы полей. Например, если в нашей базе данных не допускается, чтобы студент сдавал в один день больше одного экзамена, то можно в таблице объявить уникальной комбинацию значений полей STUDENT_ID и EXAM_DATE. Для этого следует создать таблицу EXAM_MARKS следующим способом:
CREATE TABLE EXAM_MARKS
(EXAM_ID INTEGER NOT NULL,
STUDENT_ID INTEGER NOT NULL,
SUBJ_ID INTEGER NOT NULL,
MARK CHAR (1),
EXAM_DATE DATE NOT NULL,
UNIQUE (STUDENT_ID, EXAM_DATE));
Обратите внимание, что оба поля в ограничении таблицы UNIQUE все еще используют ограничение столбца — NOT NULL. Если бы использовалось ограничение столбца UNIQUE для поля STUDENT_ID, то такое ограничение таблицы было бы необязательным.
Если значение поля STUDENT_ID должно быть различным для каждой строки в таблице EXAM_MARKS, это можно сделать,объявив UNIQUE как ограничение самого поля STUDENT_ID. В этом случае не будет и двух строк с идентичной комбинацией значений полей STUDENT_ID, EXAM_DATE. Следовательно, указание UNIQUE как ограничение таблицы наиболее полезно использовать в случаях, когда не требуется уникальность индивидуальных полей, как это имеет место на самом деле в рассматриваемом примере.
Присвоение имен ограничениям
Ограничениям таблиц можно присваивать уникальные имена. Преимущество явного задания имени ограничения состоит в том, что в этом случае при выдаче системой сообщения о нарушении установленного ограничения будет указано его имя, что упрощает обнаружение ошибок.
Для присвоения имени ограничению используется несколько измененный синтаксис команд CREATE TABLE и ALTER TABLE.
Приведенный выше пример запроса изменяется следующим образом:
CREATE TABLE EXAM_MARKS
(EXAM_ID INTEGER NOT NULL,
STUDENT_ID INTEGER NOT NULL,
SUBJ_ID INTEGER NOT NULL,
MARK CHAR (1),
EXAM_DATE DATE NOT NULL,
CONSTRAINT STUD_SUBJ_CONSTR
UNIQUE (STUDENT ID, EXAM DATE);
В этом запросе STUD__SUBJ_CONSTR — это имя, присвоенное указанному ограничению таблицы.
Ограничение первичных ключей
Первичные ключи таблицы — это специальные случаи комбинирования ограничений UNIQUE и NOT NULL. Первичные ключи имеют следующие особенности:
• таблица может содержать только один первичный ключ;
• внешние ключи по умолчанию ссылаются на первичный ключ таблицы;
• первичный ключ является идентификатором строк таблицы (строки, однако, могут идентифицироваться и другими способами).
Улучшенный вариант создания таблицы STUDENTI с объявленным первичным ключом имеет теперь следующий вид:
CREATE TABLE STUDENT
(STUDENT_ID INTKGER PRIMARY KEY,
SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER,
KURS INTEGER,
CITY CHAR (15),
BIRTHDAY DATE,
UNIV_ID INTEGER);
Составные первичные ключи
Ограничение PRIMARY KEY может также быть применено для нескольких полей, составляющих уникальную комбинацию значений — составной первичный ключ. Рассмотрим таблицу EXAM_MARKS. Очевидно, что ни к полю идентификатора студента (STUDENT_ID), ни к полю идентификатора предмета обучения (EXAM_ID) по отдельности нельзя предъявить требование уникальности. Однако для того, чтобы в таблице не могли появиться разные записи для одинаковых комбинаций значений полей STUDENT_ID и EXAM_ID (конкретный студент на конкретном экзамене не может получить более одной оценки), имеет смысл объявить уникальной комбинацию этих полей. Для этого мы можем применить ограничение таблицы PRIMARY KEY объявив пару EXAM_ID И STUDENT_ID ПерВИЧНЫМ КЛЮЧОМ Таблицы.
CREATE TABLE NEW_EXAM_MARKS
(STUDENT_ID INTEGER NOT NULL,
SUBJ_ID INTEGER NOT NULL,
MARK INTEGER,
DATA DATE,
CONSTRAINT EX_PR_KEY PRIMARY KEY (EXAM ю, STUDENT ID));
Проверка значений полей
Ограничение CHECK позволяет определять условие, которому должно удовлетворять вводимое в поле таблицы значение, прежде чем оно будет принято. Любая попытка обновить или заменить значение поля такими, для которых предикат, задаваемый ограничением CHECK, имеет значение ложь, будет отвергаться.
Рассмотрим таблицу STUDENT. Значение столбца STIPEND в этой таблице выражается десятичным числом. Наложим на значения этого столбца ограничение — величина размера стипендии должна быть меньше 200.
Соответствующий запрос имеет следующий вид:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY,
* SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER CHECK (STIPEND < 200),
KURS INTEGER,
CITY CHAR (15),
BIRTHDAY DATE,
UNIV_ID INTEGER);
8. Проверка ограничивающих условий с использованием составных полей
Ограничение CHECK можно использовать в качестве табличного ограничения, то есть при необходимости включить более одного поля в ограничивающее условие.
Предположим, что ограничение на размер стипендии (меньше 200) должно распространяться только на студентов, живущих в Воронеже. Это можно указать в запросе со следующим табличным ограничением CHECK:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY,
SURNAME CHAR(25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER,
KURS INTEGER,
CITY CHAR (15),
BIRTHDAY DATE,
UNIV_ID INTEGER UNIQUE,
CHECK (STIPEND < 200 AND CITY = 'Воронеж'));
или в несколько другой записи:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY,
SURNAME CHAR(25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER,
KURS INTEGER,
CITY CHAR (15),
BIRTHDAY DATE,
UNIV_ID INTEGER UNIQUE,
CONSTRAINT STUD_CHECK CHECK (STIPEND < 200
AND CITY = 'Воронеж'));9. Установка значений по умолчанию
В SQL имеется возможность при вставке в таблицу строки, не указывая значений некоторого поля, определять значение этого поля по умолчанию. Наиболее часто используемым значением по умолчанию является NULL. Это значение принимается по умолчанию для любого столбца, для которого не было установлено ограничение NOT NULL.
Значение поля по умолчанию указывается в команде CREATE TABLE тем же способом, что и ограничение столбца, с помощью ключевого слова
DEFAULT<значение по умолчанию>.
Строго говоря, опция DEFAULT не имеет ограничительного свойства, так как она не ограничивает значения, вводимые в поле, а просто конкретизирует значение поля в случае, если оно не было задано.
Предположим, что основная масса студентов, информация о которых находится в таблице STUDENT, проживает в Воронеже. Чтобы при задании атрибутов не вводить для большинства студентов название города 'Воронеж', можно установить его как значение поля CITY по умолчанию, определив таблицу STUDENT
следующим образом:
CREATE TABLE STUDENT
(STUDENT_ID INTEGER PRIMARY KEY,
SURNAME CHAR (25) NOT NULL,
NAME CHAR (10) NOT NULL,
STIPEND INTEGER CHECK (STIPEND < 200),
KURS INTEGER,
CITY CHAR (15) DEFAULT 'Воронеж',
BIRTHDAY DATE,
UNIV_ID INTEGER);Другая цель практического применения задания значения по умолчанию — это использование его как альтернативы для NULL.
Добавление данных в таблицу
Команда INSERT осуществляет вставку в таблицу новой •строки. В простейшем случае она имеет вид:
INSERT INTO <имя таблицы> VALUES (<значение>, <значение>,);
При такой записи указанные в скобках после ключевого слова VALUES значения вводятся в поля добавленной в таблицу новой строки в том порядке, в котором соответствующие столбцы указаны при создании таблицы, то есть в операторе CREATE TABLE.
Например, ввод новой строки в таблицу STUDENT может быть осуществлен следующим образом:
INSERT INTO STUDENT
VALUES (101,'Иванов','Александр', 200, 3,'Москва1,'6/10/1979', 15);
Чтобы такая команда могла быть выполнена, таблица с указанным в ней именем (STUDENT) должна быть предварительно определена (создана) командой CREATE TABLE. Если в какое-либо поле необходимо вставить NULL-значение, то оно вводится как обычное значение:
INSERT INTO STUDENT
VALUES (101, 'Иванов', NULL, 200, 3, 'Москва','6/10/1979', 15);
В случаях, когда необходимо ввести значения полей в порядке, отличном от порядка столбцов, заданного командой CREATE TABLE, или требуется ввести значения не во все столбцы, следует использовать следующую форму команды INSERT:
INSERT INTO STUDENT (STUDENT_ID, CITY, SURNAME, NAME)
VALUES (101, 'Москва', 'Иванов', 'Саша');
Столбцам, наименования которых не указаны в приведенном в скобках списке, автоматически присваивается значение по умолчанию, если оно назначено при описании таблицы (команда CREATE TABLE), либо значение NULL.
С помощью команды INSERT можно извлечь значение из одной таблицы и разместить его в другой, например, запросом следующего вида:
INSERT INTO STUDENT1
SELECT *
FROM STUDENT
WHERE CITY = 'Москва';
При этом таблица STUDENTI должна быть предварительно создана командой CREATE TABLE (раздел 4.1) и иметь структуру, идентичную таблице

ПРАКТИЧЕСКАЯ РАБОТА
СОЗДАНИЕ И ЗАПОЛНЕНИЕ ТАБЛИЦ С ПОМОЩЬЮ ЯЗЫКА SQL
Создайте следующие таблицы, ограничения для каждого столбца таблицы выберите самостоятельно.
Заполните таблицы
STUDENT (Студент)
STUDENT_ID — числовой код, идентифицирующий студента, SURNAME — фамилия студента, NAME — имя студента, STIPEND — стипендия, которую получает студент,
KURS — курс, на котором учится студент, CITY — город, в котором живет студент,
BIRTHDAY — дата рождения студента,
UNIV_ID— числовой код, идентифицирующий университет, в котором учится студент.
STUDENT_ ID SURNAME NAME STIPEND KURS CITY BIRTHDAY UNIV_ID
1 Алексеев Иван 150 1 Орел 3/12/1982 10
36 Вязников Петр 200 3 Курск 1/12/1980 10
10 Скоморохов Вадим 150 4 Москва 7/06/1979 22
12 Ильин Борис 0 2 Брянск 8/12/1981 10
265 ЗдроговаОльга 250 2 Липецк 1/05/1981 10
32 Артемьев Андрей 0 3 Воронеж 5/11/1979 10
654 Соколов Павел 150 5 Белгород NULL 14
276 Пивоваров Артем 200 3 Воронеж 1/12/1981 10
55 Дубинин Антон 200 4 NULL 5/08/1981 22
LECTURER (Преподаватель)
LECTURER_ID — числовой код, идентифицирующий преподавателя,
SURNAME — фамилия преподавателя,
NAME — имя преподавателя,
CITY — город, в котором живет преподаватель,
UNIV_ID — идентификатор университета, в котором работает преподаватель.
LECTURER_ID SURNAME NAME CITY UNIV_ ID
24 Колесников Борис Воронеж 10
46 Никонов Иван Воронеж 10
74 Лагутин Павел Москва 22
108 Струков Николай Москва 22
276 Николаев Виктор Воронеж 10
328 Сорокин Андрей Орел 10
UNIVERSITY (Университеты)
UNIV_ID — идентификатор университета,
UNIV_NAME — название университета,
RATING — рейтинг университета,
CITY — город, в котором расположен университет.
UNIV_ID UNIV_NAME RATING CITY
22 МГУ 606 Москва
10 ВГУ 296 Воронеж
11 НГУ 345 Новосибирск
32 РГУ 416 Ростов
14 БГУ 326 Белгород
15 ТГУ 368 Томск
18 ВГМА 327 Воронеж
SUBJECT (Предмет обучения)
Создайте таблицу предметов обучения SUBJECT так, чтобы количество отводимых на предмет часов по умолчанию было равно 36, не допускались записи с отсутствующим количеством часов, поле SUBJ_ID являлось первичным ключом таблицы и значения семестров (поле SEMESTER) лежали в диапазоне от 1 до 12.
SUBJ_ID — идентификатор предмета обучения,
SUBJ_NAME — наименование предмета обучения,
HOUR — количество часов, отводимых на изучение предмета,
SEMESTER — семестр, в котором изучается данный предмет.
SUBJ_ ID SUB_ NAME HOUR SEMESTER
10 Информатика 56 1
22 Физика 34 1
43 Математика 56 2
56 История 34 4
94 Английский 56 3
73 Физкультура 34 5

EXAM_MARKS (Экзаменационные оценки)
Создайте таблицу EXAM_MARKS так, чтобы не допускался ввод в таблицу двух записей об оценках одного студента по конкретным экзамену и предмету обучения и чтобы не допускалось проведение двух экзаменов по любым предметам в один день,
EXAM_ID — идентификатор экзамена,
STUDENT_ID — идентификатор студента,
SUBJ_ID — идентификатор предмета обучения,
MARK — экзаменационная оценка,
EXAM DATE — дата экзамена. SUB_LECT
LECTURER_ID — идентификатор преподавателя,
SUBJ_ID — идентификатор предмета обучения.
LECTURER _ID SUBJ_ID
24 24
46 46
74 74
108 108
276 276
328 328
EXAM_ID STUDENT ID SUBJ ID MARK EXAM_DATE
145 12 10 5 12/01/2000
34 32 10 4 23/01/2000
75 55 10 5 05/01/2000
238 12 22 3 17/06/1999
639 55 22 NULL 22/06/1999
43 6 22 4 18/01/2000

Приложенные файлы

  • docx 15661188
    Размер файла: 42 kB Загрузок: 2

Добавить комментарий