Что такое target в html. Гиперссылка — что это такое, как сделать ссылку и вставить в HTML код (href, target blank и другие атрибуты тега a)

Для этого понадобится установленная система управления базами данных (СУБД) 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 существуют особые средства, называемые триггерами. Но триггеры - вещь капризная: на практике они могут и не сработать или сработать не так, как предусмотрено. Поэтому разработчики по возможности ищут программные средства решения таких задач, пример которых упомянут в этом абзаце.

Итак, мы познакомились с типами данных, теперь будем усовершенствовать таблицы для нашего форума. Сначала разберем их. И начнем с таблицы users (пользователи). В ней у нас 4 столбца:

Id_user - целочисленные значения, значит будет тип int, ограничим его 10 символами - int (10).
name - строковое значение varchar, ограничим его 20 символами - varchar(20).
email - строковое значение varchar, ограничим его 50 символами - varchar(50).
password - строковое значение varchar, ограничим его 15 символами - varchar(15).

Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.

Id_user int (10) NOT NULL
name varchar(20) NOT NULL
email varchar(50) NOT NULL

Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т.е. его значения уникальны, и они однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть специальный атрибут - AUTO_INCREMENT , который при обращении к таблице на добавление данных высчитывает максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:

name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL

Теперь надо указать, что поле id_user является первичным ключом. Для этого в SQL используется ключевое слово PRIMARY KEY () , в скобочках указывается имя ключевого поля. Внесем изменения:

Id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
PRIMARY KEY (id_user)

Итак, таблица готова, и ее окончательный вариант выглядит так:

Create table users (
id_user int (10) AUTO_INCREMENT,
name varchar(20) NOT NULL,
email varchar(50) NOT NULL,
password varchar(15) NOT NULL,
PRIMARY KEY (id_user)
);

Теперь разберемся со второй таблицей - topics (темы). Рассуждая аналогично, имеем следующие поля:

id_author int (10) NOT NULL
PRIMARY KEY (id_topic)

Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY () , которое имеет следующий синтаксис:

FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);

Укажем, что id_author - внешний ключ:

Id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
FOREIGN KEY (id_author) REFERENCES users (id_user)

Таблица готова, и ее окончательный вариант выглядит так:

Create table topics (
id_topic int (10) AUTO_INCREMENT,
topic_name varchar(100) NOT NULL,

PRIMARY KEY (id_topic),
FOREIGN KEY (id_author) REFERENCES users (id_user)
);

Осталась последняя таблица - posts (сообщения). Здесь все аналогично, только два внешних ключа:

Create table posts (
id_post int (10) AUTO_INCREMENT,
message text NOT NULL,
id_author int (10) NOT NULL,
id_topic int (10) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_author) REFERENCES users (id_user),
FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
);

Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.

Запускаем сервер MySQL (Пуск - Программы - MySQL - MySQL Server 5.1 - MySQL Command Line Client), вводим пароль, создаем БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:

Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.

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

Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables :

И, наконец, посмотрим структуру нашей последней таблицы posts:

Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием "Женаты\Замужем" и типом ENUM ("да", "нет"), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:

Married enum ("да", "нет") NOT NULL default("да")

Т.е. это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.

Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java...) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы на внесение данных сейчас научимся.

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

INSERT INTO имя_таблицы VALUES ("значение_первого_столбца","значение_второго_столбца", ..., "значение_последнего_столбца");

Давайте попробуем внести в нашу таблицу users следующие значения:

INSERT INTO users VALUES ("1","sergey", "[email protected]", "1111");

Второй вариант используется для внесения данных в некоторые поля таблицы:

INSERT INTO имя_таблицы ("имя_столбца", "имя_столбца") VALUES ("значение_первого_столбца","значение_второго_столбца");

В нашей таблице users все поля обязательны для заполнения, но наше первое поле имеет ключевое слово - AUTO_INCREMENT (т.е. оно заполняется автоматически), поэтому мы можем пропустить этот столбец:

INSERT INTO users (name, email, password) VALUES ("valera", "[email protected]", "2222");

Если бы у нас были поля с типом NULL, т.е. необязательные для заполнения, мы бы тоже могли их проигнорировать. А вот если попытаться оставить пустым поле со значением NOT NULL, то сервер выдаст сообщение об ошибке и не выполнит запрос. Кроме того, при внесении данных сервер проверяет связи между таблицами. Поэтому вам не удастся внести в поле, являющееся внешним ключом, значение, отсутствующее в связанной таблице. В этом вы убедитесь, внося данные в оставшиеся две таблицы.

Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять скобки со значениями через запятую:

Теперь внесем данные во вторую таблицу - topics (темы). Все тоже самое, но надо помнить, что значения в поле id_author должны присутствовать в таблице users (пользователи):

Теперь давайте попробуем внести еще одну тему, но с id_author, которого в таблице users нет (т.к. мы внесли в таблицу users только 5 пользователей, то id=6 не существует):

Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.

Теперь внесем несколько строк в таблицу posts (сообщения), помня, что в ней у нас 2 внешних ключа, т.е. id_author и id_topic, которые мы будем вносить должны присутствовать в связанных с ними таблицах:

Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос - как посмотреть, какие данные хранятся в таблицах. Этим мы и займемся на следующем уроке.

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

В этой главе, мы будем обсуждать создание, изменение, и удаление таблиц. Все это относится к самим таблицам, а не к данным которые в них содержатся. Будете или не будете Вы выполнять эти операции самостоятельно, но их концептуальное понимание увеличит ваше понимание языка SQL и природу таблиц которые вы используете. Эта глава вводит нас в область SQL называемую - DDL(Язык Определения Данных), где создаются объекты данных SQL.

Эта глава также покажет другой вид объекта данных SQL - Индекс. Индексы используются чтобы делать поиск более эффективным и, иногда, заставлять значения отличаться друга от друга. Они обычно работают незаметно для Вас, но если вы попробуете поместить значения в таблицу и они будут отклонены, из-за их неуникальности, это будет означать что друга строка имеет то же самое значение для этого по- л, и что это поле имеет уникальный индекс или ограничение которое предписывает ему уникальность. Обсуждение вышеупомянутого, продолжится в Главе 18 .

КОМАНДА СОЗДАНИЯ ТАБЛИЦЫ

Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу - таблицу без строк. Значения вводятся с помощью DML команды INSERT (См. Главу 15). Команда CREATE TABLE в основном определяет им таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.

Синтаксис команды CREATE TABLE: CREATE TABLE ( [()], [()] ...); Как сказано в Главе 2 , типы данных значительно меняются от программы к программе. Для совместимости со стандартом, они должны все, по край- ней мере, поддерживать стандарт типа ANSI. Он описан в Приложении B .

Так как пробелы используются для разделения частей команды SQL, они не могут быть частью имени таблицы (или любого другого объекта, такого как индекс). Подчеркивание (_) - обычно используется для разделения слов в именах таблиц.

Значение аргумента размера зависит от типа данных. Если вы его не указываете, ваша система сама будет назначать значение автоматически. Для числовых значений, это - лучший выход, потому что в этом случае, все ваши пол такого типа получат один и тот же размер что освобождает вас от проблем их общей совместимости(см. Главу 14). Кроме того, использование аргумента размера с некоторыми числовым на- борами, не совсем простой вопрос. Если вам нужно хранить большие числа, вам несомненно понадобятся гарантии, что пол достаточно велики чтобы вместить их.

Один тип данных для которого вы, в основном, должны назначать размер - CHAR. Аргумент размера - это целое число которое определяет максимальное число символов которое может вместить поле. Фактически, число символов по- л может быть от нуля (если поле - NULL) до этого числа. По умолчанию, аргумент размера = 1, что означает что поле может содержать только одну букву. Это конечно не совсем то что вы хотите.

Таблицы принадлежат пользователю который их создал, и имена всех таблиц принадлежащих данному пользователю должны отличаться друга от друга, как и имена всех столбцов внутри данной таблицы. Отдельные таблицы могут использовать одинаковые имена столбцов, даже если они принадлежат одному и тому же пользователю. Примером этому - столбец city в таблице Заказчиков и в таблице Продавцов. Пользователи не являющиеся владельцами таблиц могут ссылаться к этим таблицам с помощью имени владельца этих таблиц сопровождаемого точкой; например, таблица Employees создана Smith будет называться Smith.Employees когда она упоминается каким-то другим пользователем (мы понимаем что Smith - это Идентификатор Разрешения (ID). (ID) сообщаемый пользователем (ваш разрешенный ID - это ваше им в SQL. Этот вывод обсуждался в Главе 2 , и будет продолжен в Главе 22).

Эта команда будет создавать таблицу Продавцов: CREATE TABLE Saleepeople (snum integer, sname char (10), city char (10), comm declmal); Порядок столбцов в таблице определяется порядком в котором они указаны. Им столбца не должно разделяться при переносе строки (что сделано для удобочитаемости), но отделяется запятыми.

ИНДЕКСЫ

Индекс - это упорядоченный (буквенный или числовой) список столбцов или групп столбцов в таблице. Таблицы могут иметь большое количество строк, а, так как строки не находятся в каком-нибудь определенном порядке, их поиск по указанному значению может потребовать времени. Индексный адрес - это и забота, и в то же врем обеспечение способа объединения всех значений в группы из одной или больше строк, которые отличаются одна от другой. В Главе 18 , мы будем описывать более непосредственный способ который заставит ваши значения быть уникальными. Но этот метод не существует в ранних версиях SQL. Так как уникальность часто необходима, индексы и использовались с этой целью.

Индексы - это средство SQL, которое родил сам рынок, а не ANSI. Поэтому, сам по себе стандарт ANSI в настоящее время не поддержи- вает индексы, хотя они очень полезны и широко применяемы.

Когда вы создаете индекс в поле, ваша база данных запоминает соответствующий порядок всех значений этого пол в области памяти. Предположим что наша таблица Заказчиков имеет тысячи входов, а вы хотите найти заказчика с номером=2999. Так как строки не упорядочены, ваша программа будет просматривать всю таблицу, строку за строкой, проверяя каждый раз значение пол cnum на равенство значению 2999. Однако, если бы имелся индекс в поле cnum , то программа могла бы выйти на номер 2999 прямо по индексу и дать информацию о том как найти правильную строку таблицы.

В то врем как индекс значительно улучшает эффективность запросов, использование индекса несколько замедляет операции модификации DML(такие как INSERT и DELETE), а сам индекс занимает объем памяти. Следовательно, каждый раз когда вы создаете таблицу Вы должны принять решение, индексировать ее или нет. Индексы могут состоять из многочисленных полей. Если больше чем одно поле указывается для одного индекса, второе упорядочивается внутри первого, третье внутри второго, и так далее. Если вы имели первое и послед- нее им в двух различных полях таблицы, вы могли бы создать индекс который бы упорядочил предыдущее поле внутри последующего. Это может быть выполнено независимо от способа упорядочивали столбцов в таблице.

Синтаксис для создания индекс - обычно следующий (помните, что это не ANSI стандарт): CREATE INDEX ON

( [,]...); Таблица, конечно, должна уже быть создана и должна содержать им столбца. Им индекса не может быть использовано для чего-то другого в базе данных (любым пользователем). Однажды созданный, индекс будет невидим пользователю. SQL сам решает когда он необходим чтобы ссылаться на него и делает это автоматически. Если, например, таблица Заказчиков будет наиболее часто упоминаемой в запросах продавцов к их собственной клиентуре, было бы правильно создать такой индекс в поле snum таблицы Заказчиков. CREATE INDEX Clientgroup ON Customers (snum); Теперь, тот продавец который имеет отношение к этой таблицы сможет найти собственную клиентуру очень быстро.

УНИКАЛЬНОСТЬ ИНДЕКСА

Индексу в предыдущем примере, к счастью, не предписывается уникальность, несмотря на наше замечание, что это является одним из назначений индекса. Данный продавец может иметь любое число заказчиков. Однако, этого не случится если мы используем ключевое слово UNIQUE перед ключевым словом INDEX. Поле сnum, в качестве первичного ключа, станет первым кандидатом для уникального индекса: CREATE UNIQUE INDEX Custid ON Customers (cnum); ПРИМЕЧАНИЕ: эта команда будет отклонена если уже имеются идентичные значения в поле cnum. Лучший способ иметь дело с индексами состоит в том, чтобы создавать их сразу после того, как таблица создана и прежде, чем введены любые значения. Так же обратите внимание что, для уникального индекса более чем одного пол, это - комбинация значений, каждое из которых, может и не быть уникальным.

Предыдущий пример - косвенный способ заставить поле cnum работать как первичный ключ таблицы Заказчиков. Базы данных воздействуют на первичные и другие ключи более непосредственно. Мы будем обсуждать этот вывод далее в Главах 18 и .

УДАЛЕНИЕ ИНДЕКСОВ

Главным признаком индекса является его им - поэтому он может быть удален. Обычно пользователи не знают о существовании индекса. SQL автоматически определяет позволено ли пользователю использовать индекс, и если да, то разрешает использовать его. Однако, если вы хотите удалить индекс, вы должны знать его им. Этот синтаксис используется для удаления индекса: DROP INDEX ; Удаление индекса не воздействует на содержание полей.

ИЗМЕНЕНИЕ ТАБЛИЦЫ ПОСЛЕ ТОГО КАК ОНА БЫЛА СОЗДАНА

Команда ALTER TABLE не часть стандарта ANSI; но это - широко доступна, и довольно содержательна форма, хотя ее возможности несколько ограничены. Она используется чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения (обсужденные в Главе 18). Типичный синтаксис чтобы добавить столбец к таблице: ALTER TABLE

ADD ; Столбец будет добавлен со значением NULL для всех строк таблицы. Новый столбец станет последним по порядку столбцом таблицы. Вообще то, можно добавить сразу несколько новых столбцов, отделив их запятыми, в одной команде. Имеется возможность удалять или изменять столбцы. Наиболее часто, изменением столбца может быть просто увеличение его размера, или добавление(удаление) ограничения. Ваша система должна убедиться, что любые изменения не противоречат существующим данным - например при попытке добавить ограничение к столбцу который уже имел значение при нарушении которого ограничение будет отклонено. Лучше всего дважды проверить это. По крайней мере, посмотрите документацию вашей системы чтобы убедить- с, гарантирует ли она что именно это было причиной. Из-за нестандартного характера команды ALTER TABLE, вам все равно необходимо посмотреть тот раздел вашей системной документации где говорится об особых случаях.

ALTER TABLE - не действует, когда таблица должна быть переопределена, но вы должны разрабатывать вашу базу данных по возможности так чтобы не слишком ей в этом доверяться. Изменение структуры таблицы когда она уже в использовании - опасно! Просмотрите внимательно таблицы, которые являясь вторичными таблицами с извлеченными данными из другой таблицы (смотри Главу 20), не долго правильно работают, а программы использующие вложенный SQL(Глава 25) выполняются неправильно или не всегда правильно. Кроме того, изменение может стереть всех пользователей имеющих разрешение обращаться к таблице. По этим причинам, вы должны разрабатывать ваши таблицы так, чтобы использовать ALTER TABLE только в крайнем случае.

Если ваша система не поддерживает ALTER TABLE , или если вы хотите избежать ее использования, вы можете просто создать новую таблицу, с необходимыми изменениями при создании, и использовать команду INSERT с SELECT * запросом чтобы переписать в нее данные из старой таблицы. Пользователям которым был предоставлен доступ к старой таблице (см. Главу 22) должен быть предоставлен доступ к новой таблице.

УДАЛЕНИЕ ТАБЛИЦЫ

Вы должны быть собственником(т.е. быть создателем) таблицы чтобы иметь возможность удалить ее. Поэтому не волнуйтесь о случайном разрушении ваших данных, SQL сначала потребует чтобы вы очистили таблицу прежде, чем удалит ее из базы данных. Таблица с находящимися в ней строками, не может быть удалена. Обратитесь к Главе 15 за подробностями относительно того как удалять строки из таблицы. Синтаксис для удаления вашей таблицы, если конечно она является пустой, следующая: DROP TABLE < table name >; При подаче этой команды, им таблицы больше не распознается и нет такой команды которая могла быть дана этому объекту. Вы должны убедиться, что эта таблица не ссылается внешним ключом к другой таблице(Внешние ключи обсуждаются в Главе 19), и что она не используется в определении Представления(Глава 20).

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

РЕЗЮМЕ

Теперь Вы уже бегло ориентируетесь в основах определений данных. Вы можете создавать, изменять, и удалять таблицы. В то врем как только первая из этих функций - часть официального стандарта SQL, другие будут врем от времени меняться, особенно - ALTER TABLE. DROP TABLE позволяет вам избавиться от таблиц которые бесполезны. Она уничтожает только пустые таблицы, и следовательно не разрушает данные.

Вы теперь знаете об индексах а также, как их создавать и удалять. SQL не дает вам большого управления над ими, так как реализация которую вы используете довольно удачно определяет, как быстро выполняются различные команды. Индексы - это один из инструментов дающий Вам возможность воздействовать непосредственно на эффективность ваших команд в SQL. Мы рассмотрели индексы здесь чтобы отличать их от ограничений, с которыми их нельзя путать. Ограничения - это тема Главы 18 и Главы 19 .

РАБОТА С SQL

1. Напишите предложение CREATE TABLE которое бы вывело нашу таблицу Заказчиков.

2. Напишите команду которая бы давала возможность пользователю быстро извлекать порядки сгруппированные по датам из таблицы Порядков.

3. Если таблица Порядков уже создана, как Вы можете заставить поле onum быть уникальным (если допустить что все текущие значения уникальны) ?

4. Создайте индекс который бы разрешал каждому продавцу быстро отыскивать его порядки сгруппированные по датам.

5. Предположим, что каждый продавец имеет только одного заказчика с данной оценкой, введите команду которая его извлечет.

Разновидность языка, применяемая в конкретной СУБД, называется диалектом SQL . Например, диалект СУБДOracleназываетсяPL / SQL ; вMSSQLServerиDB2 применяется диалектTransact - SQL ; вInterbaseиFirebird–isql . Каждый диалектSQLсовместим до определенной степени со стандартомSQL, но может иметь отличия и специфические расширения языка, поэтому для выяснения синтаксиса того или иногоSQL-оператора следует в первую очередь смотретьHelp конкретной СУБД.

Для операций над базами данных и таблицами в стандарте sql предусмотрены операторы:

Ниже приводится синтаксис этих операторов по стандарту SQL92. Поскольку их синтаксис в СУБД может отличаться от стандарта, при выполнении лабораторной работы рекомендуется обращаться к справочной системе СУБД.

Имена объектов базы данных (таблиц, столбцов и др.) могут состоять из буквенно-цифровых символов и символа подчеркивания. Специальные символы (@$# и т.п.) обычно указывают на особый тип таблицы (системная, временная и др.). Не рекомендуется использовать в именах национальные (русские) символы, пробелы и зарезервированные слова, но если они всё же используются, то такие имена следует писать в кавычках ".." или в квадратных скобках [..].

Далее при описании конструкций операторов SQLбудут использоваться следующие обозначения: в квадратных скобках записываются необязательные части конструкции; альтернативные конструкции разделяются вертикальной чертой | ; фигурные скобки {} выделяют логические блоки конструкции; многоточие… указывает на то, что предшествующая часть конструкции может многократно повторяться. «Раскрываемые» конструкции записываются в угловых скобках < >.

Создание базы данных

CREATE DATABASE Имя_базы_данных

Удаление одной и более баз данных

DROP DATABASE Имя_базы_данных [,Имя_базы_данных …]

Объявление текущей базы данных

USE Имя_базы_данных –- в SQL Server и MySQL

SET DATABASE Имя _ базы _ данных – в Firebird

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

CREATE TABLE Имя_таблицы (

<описание_столбца> [, <описание_столбца> |

<ограничение_целостности_таблицы> …]

< описание_столбца > 

Имя_столбца ТИП

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

ТИП столбца может быть либо стандартным типом данных (см. таблицу 1), либо именем домена (см. п.6.2).

Некоторые СУБД позволяют создавать вычислимые столбцы (computed columns ). Это виртуальные столбцы, значение которых не хранится в физической памяти, а вычисляется сервером СУБД при всяком обращении к этому столбцу по формуле, заданной при объявлении этого столбца. В формулу могут входить значения других столбцов этой строки, константы, встроенные функции и глобальные переменные.

Описание вычислимого столбца в SQL Server имеет вид:

<описание_столбца>  Имя_столбца AS выражение

Описание вычислимого столбца в Firebird имеет вид:

<описание_столбца>  Имя_столбца COMPUTED BY <выражение>

СУБД MySQL 3.23 вычислимые столбцы не поддерживает.

< > 

CONSTRAINT Имя_ограничения_целостности

{UNIQUE|PRIMARY KEY}(список_столбцов_образующих_ключ )

|FOREIGN KEY (список _ столбцов _FK )

REFERENCES Имя_таблицы (список_столбцов_ PK )

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

|CHECK (условие_проверки )

Некоторые СУБД допускают объявление врéменных таблиц (существующих только во время сеанса). В SQL Server имена временных таблиц должны начинаться с символа # (локальные временные таблицы, видимые только создавшему их пользователю) или ## (глобальные таблицы, видимые всем пользователям); в MySQL для создания временных таблиц используется ключевое слово TEMPORARY, например:

CREATE TEMPORARY TABLE … (далее синтаксис см. CREATE TABLE).

Изменение структуры таблицы

Используется для изменения типа столбцов существующих таблиц, добавления и удаления столбцов и ограничений целостности.

ALTER TABLE Имя_таблицы

Изменение типа столбца (в SQLServerиFirebird)

ALTER COLUMN Имя_столбца новый_ТИП

Изменение типа, имени и ограничений столбца (в MySQL)

CHANGE COLUMN Имя_столбца <описание_столбца>

Добавление обычного или вычислимого столбца

|ADD <описание_столбца >

Добавление ограничения целостности

| ADD

<ограничение_целостности_таблицы >

Удаление столбца

|DROP COLUMN Имя_столбца

Удаление ограничения целостности

|DROP CONSTRAINT Имя_ограничения_целостности

Включение или отключение проверки ограничений целостности

ВMSSQLServer

|{CHECK|NO CHECK} CONSTRAINT

{Список_имен_ограничений_целостности |ALL}

Удаление таблицы

DROP TABLE Имя_таблицы



Далее рассмотрим, как при создании новых таблиц командой CREATETABLEили изменении структуры существующих таблиц командойALTERTABLEобъявить декларативные ограничения целостности (подробнее они описаны в п.4.2) .

1. Обязательное наличие данных (NULL–значения)

Объявляется словом NULL(столбец может иметь пустые ячейки) илиNOT NULL(столбец обязательный). По умолчанию принимаетсяNULL.

Пример создания таблицы 7:

CREATE TABLE Clients(

ClientName NVARCHAR (60) NOT NULL ,

DateOfBirth DATE NULL ,

Phone CHAR (12)); -- по умолчанию тоже NULL

2. Значение по умолчанию (DEFAULT)

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

Рассмотрим пример создания таблицы Orders (Заказы). Столбец OrderDate принимает по умолчанию значение текущей даты, а столбец Quantity (количество) по умолчанию равен 0.

CREATE TABLE Orders(

OrderNum INT NOT NULL , -- номер заказа

OrderDate DATETIME NOT NULL -- дата заказа

DEFAULT GetDate(),

Функция GetDate() возвращает текущую дату 8

Quantity SMALLINT NOT NULL -- кол-во товара, DEFAULT 0);

3. Объявление первичных ключей (PRIMARYKEY)

Простой первичный ключ объявляется словами PRIMARYKEYпри создании таблицы. Например,

CREATE TABLE Staff(-- таблица "Работники"

TabNum INT PRIMARY KEY , -- первичный ключ

WName NVARCHAR (40) NOT NULL , -- ФИО

... -- описание прочих столбцов );

Составной первичный ключ объявляется иначе:

-- способ 1 (объявление PK при создании таблицы)

CREATE TABLE Clients(

PasSeria NUMERIC (4,0)NOT NULL ,-- серия паспорта

PasNumber NUMERIC (6,0)NOT NULL ,-- номер паспорта

Name NVARCHAR (40)NOT NULL ,

Phone CHAR (12),

-- объявление составного первичного ключа

CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber));

-- способ 2(PK объявляется после создания таблицы)

-- сначала создаем таблицу без PK

CREATE TABLE Clients(

PasSeria NUMERIC (4,0)NOT NULL ,--серия паспорта

PasNumber NUMERIC (6,0)NOT NULL ,--номер паспорта

ClientName NVARCHAR (40)NOT NULL ,

Phone CHAR (12));

-- модификация таблицы – добавляем РК

ALTER TABLE Clients

ADD CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber);

4. Уникальность столбцов (UNIQUE)

Подобно Primary Key указывает, что столбец или группа столбцов не могут содержать повторяющихся значений, но не являютсяPK . Все столбцы, объявленныеUNIQUE, должны бытьNOTNULL. Пример объявления простого уникального столбца:

CREATE TABLE Students(

SCode INT PRIMARY KEY , -- суррогатный РК

FIO NVARCHAR (40) NOT NULL , -- ФИО

RecordBook CHAR (6) NOT NULL UNIQUE ); -- № зачетки

Пример объявления составного уникального поля:

CREATE TABLE Staff(-- таблица " Работники "

TabNum INT PRIMARY KEY , -- табельный номер

WName NVARCHAR (40) NOT NULL , -- ФИО

PasSeria NUMERIC (4,0) NOT NULL , -- серия паспорта

PasNumber NUMERIC (6,0) NOT NULL , -- номер паспорта

-- объявление составного уникального поля

CONSTRAINT Staff_UNQ UNIQUE (PasSeria,PasNumber));

5. Ограничения на значения столбца (CHECK)

Это ограничение позволяет указать диапазон, список или «маску» логически допустимых значений столбца.

Пример создания таблицы Workers (Работники) :

CREATE TABLE Workers(

-- табельные номера 4-значные

TabNum INT PRIMARY KEY

CHECK (TabNum BETWEEN 1000 AND 9999),

Name VARCHAR (60) NOT NULL , -- ФИО сотрудника

-- пол – буква " м " или " ж "

Gentry CHAR (1) NOT NULL

CHECK (Gentry IN ("м","ж")),

Возраст не менее 14 лет

Age SMALLINT NOT NULL CHECK (Age>=14),

--№ свидет-ва пенсионного страхования (по маске)

PensionCert CHAR (14)

CHECK (PensionSert LIKE ""));

В этом примере показаны разные типы проверок. Диапазон допустимых значений указывается конструкцией BETWEEN…AND; обычные условия (как для столбцаAge ) используют знаки сравнений =, <>, >, >=, <, <=, связанные при необходимости логическими операциямиAND,OR,NOT(например,Age >=14ANDAge <=70); для указания списка допустимых значений используется предикатINи его отрицаниеNOTIN; конструкция

LIKEмаска_допустимых_значений EXCEPTсписок_исключений

используется для задания маски допустимых значений строковых столбцов. В маске применяются два спецсимвола: «%» – произвольная подстрока, и «_» – любой единичный символ. Конструкция EXCEPTявляется необязательной.

В условии отбора CHECKмогут сравниваться значения двух столбцов одной таблицы и столбцы разных таблиц.

Лекция

Создание БД с помощью SQL .

Манипулирование данными в SQL

В состав языка SQL входят язык описания данных, позволяющий управлять таблицами, и язык манипулирования данными, служащий для управления данными (слайд 2 ).

17.1. Построение баз данных с помощью SQL

17.1.1. Команда создания таблицы – CREATE TABLE

Создание таблицы выполняется при помощи команды CREATE TABLE. Обобщенный синтаксис команды следующий (слайд 3 ).

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

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

определение_столбца – задание имени, типа данных и параметров отдельного столбца таблицы. Названия столбцов должны соответствовать правилам для идентификаторов и быть уникальными в пределах таблицы.

определение_ограничения_таблицы – задание некоторого ограничения целостности на уровне таблицы.

Описание столбцов

Как видно из синтаксиса команды CREATE TABLE, для каждого столбца указывается предложение <определение_столбца>, с помощью которого и задаются свойства столбца. Предложение имеет следующий синтаксис ( слайд 3 ) :

Рассмотрим назначение и использование параметров.

Имя_столбца – идентификатор, задающий имя столбца таблицы.

тип_данных – задает тип данных столбца. Если при определении столбца явно не указано ограничение на хранения значений NULL, то будут использованы свойства типа данных, т.е. если выбранный тип данных позволяет хранить значения NULL, то и в столбце можно будет хранить значения NULL. Если же при определении столбца в команде CREATE ТАBLE явно будет разрешено или запрещено хранение значений NULL, то свойства типа данных будут перекрыты установленным на уровне столбца ограничением. Например, если тип данных позволяет хранить значения NULL, а на уровне столбца будет установлен запрет, то попытка вставки значения NULL в столбец закончится ошибкой.

ограничение_столбца – с помощью этого предложения указываются ограничения, которые будут определены для столбца. Синтаксис предложения следующий (слайд 4 ):

Рассмотрим назначение параметров.

CONSTRAINT – необязательное ключевое слово, после которого указывается название ограничения на значения столбца (имя_ограничения). Имена ограничений должны быть уникальны в пределах базы данных.

DEFAULT – задает значение по умолчанию для столбца. Это значение будет использовано при вставке строки, если для столбца явно не указано никакое значение.

NULL|NOT NULL – ключевые слова, разрешающие (NULL) или запрещающие (NOT NULL) хранение в столбце значений NULL. Если для столбца не задано значение по умолчанию, то при вставке строки с неизвестным значением для столбца будет предприниматься попытка вставки в столбец значения NULL. Если при этом для столбца указано ограничение NOT NULL, то попытка вставки строки будет отклонена, и пользователь получит соответствующее сообщение об ошибке.

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

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

FOREIGN KEY ... REFERENCES – указание на то, что столбец будет служить внешним ключом для таблицы, имя которой задается с помощью параметра <имя_главной_таблицы>.

(имя_столбца [,...,n]) – столбец или список перечисленных через запятую столбцов главной таблицы, входящих в ограничение FOREIGN KEY. При этом столбцы, входящие во внешний ключ, могут ссылаться только на столбцы первичного ключа или столбцы с ограничением UNIQUE таблицы.

ON DELETE {CASCADE | NO ACTION} – эти ключевые слова определяют действия, предпринимаемые при удалении строки из главной таблицы. Если указано ключевое слово CASCADE, то при удалении строки из главной (родительской) таблицы строка в зависимой таблице также будет удалена. При указании ключевого слова NO ACTION в подобном случае будет выдана ошибка. Значением по умолчанию является вариант NO ACTION.

ON UPDATE {CASCADE | NO ACTION} – эти ключевые слова определяют действия, предпринимаемые при модификации строки главной таблицы. Если указано ключевое слово CASCADE, то при модификации строки из главной (родительской) таблицы строка в зависимой таблице также будет модифицирована. При использовании ключевого слова NO ACTION в подобном случае будет выдана ошибка. Значением по умолчанию является вариант NO ACTION.

CHECK – ограничение целостности, инициирующее контроль вводимых в столбец (или столбцы) значений.

логическое_выражение – логическое выражение, используемое для ограничения CHECK.

Ограничения на уровне таблицы

Синтаксис команды CREATE TABLE предусматривает использование предложения <ограничение_таблицы>, с помощью которого определяются ограничения целостности на уровне таблицы. Синтаксис предложения следующий (слайд 5 ) .

Назначение параметров совпадает с назначением аналогичных параметров предложения <ограничение_столбца > . Тем не менее, в предложении <ограничение_таблицы> имеются некоторые новые параметры:

имя_колонки – столбец (или список столбцов), на которые необходимо наложить какие-либо ограничения целостности.

– метод упорядочивания данных в индексе. Индекс создается при указании ключевых слов PRIMARY KEY, UNIQUE. При указании значения ASC данные в индексе будут упорядочены по возрастанию, при указании значения DESC – по убыванию. По умолчанию используется значение ASC.

Примеры создания таблиц

В качестве примера рассмотрим инструкции создания таблиц базы данных «Сессия»:

Таблица «Студенты» состоит из следующих столбцов:

ID_Студент – тип данных INTEGER, уникальный ключ;

Номер_группы - тип данных CHAR, длина 6;

слайд 6 ).

Адрес и Телефон, наложены ограничения NOT NULL

Для создания таблицы «Дисциплины» была использована команда (слайд 7 ).

Таблица содержит 2 столбца (ID _Дисциплина , Наименование ).

На столбцы ID _Дисциплина , Наименование наложены ограничения NOT NULL , запрещающие ввод строки при неопределенном значении столбца.

Столбец ID _Дисциплина объявлен первичным ключом, а на значения, вводимые в столбец Наименование, наложено условие уникальности.

Таблица «Учебный_план» включает в себя следующие столбцы:

ID_Дисциплина – тип данных INTEGER;

Семестр - тип данных INTEGER;

Количество_часов - тип данных INTEGER;

Создание таблицы выполнялось с помощью следующей команды (слайд 8 ).

Для значений столбца Семестр сформулировано логическое выражение, разрешающее вводить только значения от 1 до 10.

Таблица «Сводная_ведомость» состоит из следующих столбцов:

ID_Студент – тип данных INTEGER, столбец уникального ключа;

ID_План – тип данных INTEGER, столбец уникального ключа;

Оценка - тип данных INTEGER;

Дата_сдачи - тип данных DATETIME;

ID_Преподаватель - тип данных INTEGER.

Создание таблицы выполнялось с помощью следующей команды (слайд 9 ).

На все столбцы таблицы наложены ограничения NOT NULL , запрещающие ввод строки при неопределенном значении столбца.

Для значений столбца Оценка сформулировано логическое выражение, разрешающее вводить только значения от 0 до 5: 0 – незачет, 1 – зачет, 2 – неудовлетворительно, 3 – удовлетворительно, 4 – хорошо, 5 – отлично.

И, наконец, перечислим столбцы «Кадровый_состав»:

ID_Преподаватель – тип данных INTEGER, уникальный ключ;

Фамилия – тип данных CHAR, длина 30;

Имя - тип данных CHAR, длина 15;

Отчество - тип данных CHAR, длина 20;

Должность - тип данных CHAR, длина 20;

Кафедра - тип данных CHAR, длина 3;

Адрес - тип данных CHAR, длина 30;

Телефон - тип данных CHAR, длина 8.

Создание таблицы выполнялось с помощью следующей команды (слайд 10 ).

На все столбцы таблицы, кроме столбцов Адрес и Телефон, наложены ограничения NOT NULL , запрещающие ввод строки при неопределенном значении столбца.

Для таблиц «Учебный_план» и «Сводная_ведомость» должны быть построены внешние ключи, связывающие таблицы базы данных «Сессия»:

FK_Дисциплина – внешний ключ, связывающий таблицы «Учебный_план» и «Дисциплины» по столбцу ID_Дисциплина;

FK_Кадровый_состав – внешний ключ, связывающий таблицы «Учебный_план» и «Кадровый_состав» по столбцу ID_Преподаватель;

FK_Студент – внешний ключ, связывающий таблицы «Сводная_ведомость» и «Студенты» по столбцу ID_Студент;

FK_План – внешний ключ, связывающий таблицы «Сводная_ведомость» и «Учебный_план» по столбцу ID _План.

Добавление внешних ключей в таблицы рассмотрим далее при обсуждении возможностей команды ALTER TABLE .

17.1.2. Изменение структуры таблицы – команда ALTER TABLE

Как бы тщательно ни планировалась структура таблицы, иногда возникает необходимость внести в нее некоторые изменения. Предположим, что в уже сформированную таблицу «Преподаватели» необходимо добавить номер домашнего телефона и домашний адрес. Эту операцию можно выполнять различными путями. Например, можно удалить таблицу со старой структурой и создать вместо нее новую таблицу с нужной структурой. Недостатком этого метода является то, что необходимо будет куда-то скопировать имеющиеся в таблице данные и переписать их в новую таблицу после ее создания.

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

Добавить в таблицу определение нового столбца;

Удалить столбец из таблицы;

Изменить значение по умолчанию для какого-либо столбца;

Добавить или удалить первичный ключ таблицы;

Добавить или удалить внешний ключ таблицы;

Добавить или удалить условие уникальности;

Добавить или удалить условие на значение.

Обобщенный синтаксис команды ALTER TABLE представлен на слайде (слайд 11 ).

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

Назначение многих параметров и ключевых слов команды ALTER TABLE аналогично назначению соответствующих параметров и ключевых слов команды CREATE TABLE (например, синтаксис конструкции <определение_столбца> совпадает с синтаксисом аналогичной конструкции команды CREATE TABLE ).

Основные режимы использования команды ALTER TABLE следующие:

Добавление столбца;

Удаление столбца;

Модификация столбца;

Изменение, добавление и удаление ограничений (первичных и внешних ключей, значений по умолчанию).

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

Для добавления нового столбца следует использовать ключевое слово ADD , после которого должно стоять определение столбца.

Добавим, например, в таблицу «Студенты» столбец «Год_поступления» (слайд 12 ). После выполнения этой команды в структуру таблицы «Студент» будет добавлен еще один столбец со значением по умолчанию, равным текущему году (значение по умолчанию вычисляется с помощью двух встроенных функций - YEAR () и GETDATE ()).

Модификация столбца

Для модификации существующего столбца таблицы служит ключевое слово ALTER COLUMN . Изменение свойств столбца невозможно, если:

столбец участвует в ограничениях PRIMARY KEY или FOREIGN KEY;

на столбец наложены ограничения целостности CHECK или UNIQUE (исключение составляют столбцы, имеющие тип данных переменной длины, т.е. типы данных, начинающиеся на var);

если со столбцом связано значение по умолчанию (в этом случае допускается изменение длины, общего количества цифр или количества цифр после десятичной точки при неизменном типе данных).

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

Пример модификации столбца «Номер_группы» таблицы «Студенты» (тип данных INTEGER заменяется на CHAR ) (слайд 12 ).

Удаление столбца

Для удаления столбца из таблицы используется предложение DROP COLUMN <имя_столбца>. При удалении столбцов следует учитывать, что нельзя удалять столбцы сограничениями целостности CHECK , FOREIGN KEY , UNIQUE или PRIMARY KEY , а также столбцы, для которых определены значения по умолчанию (в виде ограничения целостности на уровне столбца или на уровне таблицы).

Рассмотрим, например, команду удаления из таблицы «Студент» столбца «Год_поступления» (слайд 12 ).

Эта команда выполнена не будет, т.т. при добавлении столбца было определено значение по умолчанию.

Добавление ограничений на уровне таблицы

Для добавления ограничений на уровне таблицы используется предложение ADD CONSTRAINT <имя_ограничения>.

В качестве примера рассмотрим команды добавления внешних ключей в таблицы базы данных «Сессия» (слайд 13 ):

· добавление внешних ключей в таблицу «Учебный_план» (создание связи с именем FK _Дисциплина и связи с именем FK _ Кадровый_состав);

· добавление внешних ключей в таблицу «Сводная_ведомость» (создание связи с именем FK _Студент и связи с именем FK _План).

С помощью конструкции ADD CONSTRAINT создается поименованное ограничение. Необходимо отметить, что удаление любого ограничения на уровне таблицы происходит только по его имени, поэтому ограничение должно быть поименовано (чтобы его можно было удалить).

Удаление ограничений

Для удаления из таблицы ограничения целостности используется предложение DROP CONSTRAINT <имя_ограничения>.

Удаление ограничения целостности возможно только в том случае, когда оно поименовано (т.е. предложение <определение_ограничения> содержит именование ограничения CONSTRAINT ).

Команда удаления построенного внешнего ключа FK _Дисциплина из таблицы «Учебный_план» выглядит следующим образом (слайд 14 ).

На слайде (слайд 14 ) показано удаление построенного ранее ограничения на значение по умолчанию DEF _Номер_группы.

17.1.3. Удаление таблиц – команда DROP TABLE

Удаление таблицы выполняется при помощи команды DROP TABLE (слайд 14 ).

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

Операция удаления таблицы в некоторых случаях требует определенного внимания. Невозможно удалить таблицу, если на нее с помощью ограничения целостности FOREIGN KEY ссылается другая таблица: попытка удаления таблицы «Дисциплины» вызовет сообщение об ошибке, т.к. на таблицу дисциплины ссылается таблица «Учебный_план».

17.2. Управление данными

Целью любой системы управления базами данных в конечном счете является ввод, изменение, удаление и выборка данных. Рассмотрим методы управления данными с помощью языка SQL.

17.2.1. Извлечение данных – команда SELECT

Основным инструментом выборки данных в языке SQLявляется команда SELECT . С помощью этой команды можно получить доступ к данным, представленным как совокупность таблиц практически любой сложности.

Чаще всего используется упрощенный вариант команды SELECT , имеющий следующий синтаксис (слайд 15 ).

Инструкция SELECT разбивается на отдельные разделы, каждый из которых имеет свое назначение. Из приведенного синтаксического описания видно, что обязательными являются только разделы SELECT и FROM , а остальные разделы могут быть опущены. Полный список разделов приведен на слайде (слайд 15 ).

Раздел SELECT

Основное назначение раздела SELECT (одного из двух обязательных разделов, которые должны указываться в любом запросе) - задание набора столбцов, возвращаемых послевыполнения запроса, т.е. внешнего вида результата. В простейшем случае возвращается столбец одной из таблиц, участвующих в запросе. В более сложных ситуациях набор значений в столбце формируется как результат вычисления выражения. Такие столбцы называются вычисляемыми и по умолчанию им не присваивается никакого имени.

При необходимости пользователь может указать для столбца, возвращаемого после выполнения запроса, произвольное имя. Такое имя называется псевдонимом (alias ). В обычной ситуации назначение псевдонима не обязательно, но в некоторых случаях требуется явное его указание. Наиболее часто это требуется при работе с разделом INTO , в котором каждый из возвращаемых столбцов должен иметь имя, и это имя должно быть уникально.

Помимо сказанного, с помощью раздела SELECT можно ограничить количество строк, которое будет включено в результат выборки. Синтаксис раздела SELECT следующий (слайд 16 ).

Рассмотрим назначение параметров.

Ключевые слова ALL | DISTINCT . При указании ключевого слова ALL в результат запроса выводятся все строки, удовлетворяющие сформулированным условиям, тем самым разрешается включение в результатодинаковых строк (одинаковость строк определяется на уровне результата отбора, а не на уровне исходных данных). Параметр ALL используется по умолчанию.

Если в запросе SELECT указывается ключевое слово DISTINCT , то в результат выборки не будет включаться более одной повторяющейся строки. Таким образом, каждая возвращенная строка будет уникальной. Уникальность строки при этом определяется на уровне строк результата выборки, а не на уровне исходных данных. Если в результат выборки включаются два столбца, уникальность будет определяться по значениям обоих этих столбцов. В отдельности значения в первом и втором столбцах могут повторяться, но комбинация значений в обоих столбцах должна быть уникальна. Аналогичные правила действуют и в отношении большего количества столбцов.

Рассмотрим результат использования ключевых слов ALL и DISTINCT на примере выборки столбцов Семестр и Отчетность из таблицы «Учебный_план» базы данных «Сессия» (слайд 17 ). Сначала выполним запрос с указанием ключевого слова ALL . Фрагмент результата представлен на слайде. Теперь заменим ключевое слово ALL на DISTINCT . В этом случае результат запроса, представленный на слайде - это строки, содержащие одинаковые значения в столбцах, включенные только один раз. Этот результат должен свидетельствовать только о наличии различных форм отчетности в семестрах.

Ключевое слово TOP n . Использование ключевого слова ТОР n, где n – числовое значение, позволяет отобрать в результат не все строки, а только n первых. При этом выбираются первые строки результата выборки, а не исходных данных. Поэтому набор строк в результате выборки при указании ключевого слова ТОР может меняться в зависимости от порядка сортировки. Если в запросе используется раздел WHERE , то ключевое слово ТОР работает с набором строк, возвращенных после применения логического условия, определенного в разделе WHERE .

Продемонстрируем использование ключевого слова ТОР (слайд 18 )

В этом примере из таблицы Студенты базы данных «Сессия» было выбрано 5 первых строк.

Можно также выбирать не фиксированное количество строк, а определенный процент от всех строк, удовлетворяющих условию. Для этого необходимо добавить ключевое слово PERCENT .

Всего в таблице было 115 строк, следовательно, 10% будет составлять 11,5 строк. В результате будут выданы 12 строк.

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

Приведем также пример, демонстрирующий влияние порядка сортировки на возвращаемый набор строк (слайд 19 ).

При указании вместе с предложением ORDER BY ключевого слова WITH TIES в результат будут включены еще и строки, совпадающие по значению колонки сортировки с последними выведенными строками запроса SELECT TOP n [ PERCENT ].

Использование ключевого слова WITH TIES в предыдущем примере позволит обеспечить выдачу в ответ на запрос информации обо всех студентах первой по порядку группы (слайд 20 ).

Предложение <Список_выбора>. Синтаксис предложения <Список_выбора>следующий (слайд 21 ).

Символ «*» означает включение в результат всех столбцов, имеющихся в списке таблиц раздела FROM .

Если в результат не нужно включать все столбцы всех таблиц, то можно явно указать имя объекта, из которого необходимо выбрать все столбцы (<Имя_таблицы>.* или <Псевдоним_таблицы>.*).

Отдельный столбец таблицы в результат выборки включается явным указанием имени столбца (параметр <Имя_столбца>). Столбец должен принадлежать одной из таблиц, указанных в разделе FROM. Если столбец с указанным именем имеется более чем в одном источнике данных, перечисленных в разделе FROM, то необходимо явно указать имя источника данных, к которому принадлежит столбец в формате <Имя_таблицы>.<Имя_столбца>.В противном случае будет выдано сообщение об ошибке.

Например, попробуем выбрать данные из столбца ID_Дисциплина, который имеется в таблицах «Дисциплина» и «Учебный_план»:

В ответ будет выдано сообщение об ошибке, указывающее на некорректное использование имени‘ID_Дисциплина".

Т. е., в этом случае необходимо явно указать имя источника данных, которому принадлежит столбец, например:

Столбцам, возвращаемым как результат выполнения запроса, могут быть присвоены псевдонимы. Псевдонимы позволяют изменить имя исходного столбца или поименовать столбец, содержимое которого получено как результат вычисления выражения. Имя псевдонима указывается с помощью параметра <Псевдоним_столбца>. Ключевое слова AS необязательно при задании псевдонима. В общем случае сервер не требует уникальности имен столбцов результата выборки, поэтому разные столбцы могут иметь одинаковые имена или псевдонимы.

Столбцы в результате выборки могут быть не только копией столбца одной из исходных таблиц, но и формироваться на основе вычисления выражения. Такой столбец в списке выбора задается с помощью конструкции <Выражение> [ <Псевдоним_столбца>]. Выражение при этом может содержать константы, имена столбцов, функции, а также их комбинации. Дополнительно столбцу, формируемому на основе вычисления выражения, можно присвоить псевдоним, указав его с помощью параметра <Псевдоним_столбца>. По умолчанию вычисляемый столбец не имеет имени.

Другой способ формирования вычисляемого столбца состоит в использовании конструкции со знаком равенства: <Псевдоним_столбца> = <Выражение>. Единственным отличием этого способа от предыдущего является необходимость обязательного задания псевдонима. В простейшем случае выражение является именем столбца, константой, переменной или функцией. Если в качестве выражения выступает имя столбца, то получаем еще один способ задания псевдонима для столбца.

Рассмотрим следующий пример. Пусть для таблицы «Студенты» необходимо построить запрос, представляющий фамилию, имя и отчество в одной колонке. Используя операцию конкатенации (сложения) символьных строк и значение ФИО в качестве псевдонима столбца, построим запрос (слайд 22 ).

Раздел FROM

С помощью раздела FROM определяются источники данных, с которыми будет работать запрос.

Синтаксис раздела FROM следующий (слайд 23 )

На первый взгляд конструкция раздела выглядит простой. Однако при ближайшем рассмотрении он оказывается довольно сложным. В основном работа с разделом FROM это перечисление через запятую источников данных, с которыми должен работать запрос. Собственно источник данных указывается с помощью предложения <Источник_данных>, синтаксис которого представлен на слайде.

С помощью параметра <имя_таблицы> указывается имя обычной таблицы. Параметр <псевдоним_таблицы> используется для присвоения таблице псевдонима, под которым на нее нужно будет ссылаться в запросе. Часто псевдонимы таблиц применяют, чтобы ссылку на нужную таблицу сделать более удобной и короткой. Например, если в запросе часто упоминается имя таблицы «Учебный_план», то можно воспользоваться псевдонимом, например, tpl . Указание ключевого слова AS не является при этом обязательным.

Раздел WHERE

Раздел WHERE предназначен для наложения вертикальных фильтров на данные, обрабатываемые запросом. Другими словами, с помощью раздела WHERE можно сузить набор строк, включаемых в результат выборки. Для этого указывается логическое условие, от которого зависит, будет ли строка включена в выборку по запросу или нет. Строка включается в результат выборки, только если логическое выражение возвращает значение TRUE .

В общем случае логическое выражение содержит имена столбцов таблиц, с которыми работает запрос. Для каждой строки, возвращенной запросом, вычисляется логическое выражение путем подстановки вместо имен столбцов конкретных значений из соответствующей строки. Если при вычислении выражения возвращается значение TRUE , то есть выражение истинно, то строка будет включена в конечный результат. В противном случае строка в результат не включается. При необходимости можно указать более одного логического выражения, объединив их с помощью логических операторов OR и AND .

Рассмотрим синтаксис раздела WHERE (слайд 24 ).

В конструкции <условие_отбора> можно определить любое логическое условие, при выполнении которого строка будет включена в результат.

Приведенный на слайде пример демонстрирует логику работы раздела WHERE . В результате будет возвращен список всех студентов, поступивших на факультет ранее 2000 года.

Помимо операций сравнения (=, >, <, >=, <=) и логических операторов OR , AND , NOT при формировании условия отбора могут быть использованы дополнительные логические операторы, расширяющие возможности по управлению данными. Рассмотрим некоторые из этих операторов.

Оператор BETWEEN . С помощью этого оператора можно определить, лежит ли значение указанной величины в заданном диапазоне. Синтаксис использования оператора следующий (слайд 25 ).

<Выражение> задает проверяемую величину, а аргументы <начало_диапазона> и <конец_диапазона> определяют возможные границы ее изменения. Использование оператора NOT совместно с оператором BETWEEN позволяет задать диапазон, вне которого может изменяться проверяемая величина.

При выполнении оператор BETWEEN преобразуется в конструкцию из двух операций сравнения.

Рассмотрим пример использования оператора BETWEEN (слайд 25 ). В результате выполнения инструкции получим список дисциплин учебного плана с количеством часов от 50 до 100.

Оператор IN . Оператор позволяет задать в условии отбора множество возможных значений для проверяемой величины. Синтаксис использования оператора следующий (слайд 26 ).

<Выражение> указывает проверяемую величину, а аргументы <выражение1>,…, <выражение N > задают перечислением через запятую набор значений, которые может принимать проверяемая величина. Ключевое слово NOT выполняет логическое отрицание.

Рассмотрим пример применения оператора IN (слайд 26 ). В результате выполнения инструкции получим строки учебного плана для дисциплин «Английский язык» и «Физическая культура».

Оператор LIKE . С помощью оператора LIKE можно выполнять сравнение выражения символьного типа с заданным шаблоном. Синтаксис оператора следующий (слайд 27 ).

<Образец> задает символьный шаблон для сравнения и заключается в кавычки. Шаблон может содержать символы-разделители. Допускается использование следующих символов-разделителей (слайд 27 ):

% - может быть заменен в символьном выражении любым количеством произвольных символов;

_ - может быть заменен в символьном выражении любым, но только одним символом;

[ ABC 0-9] - может быть заменен в символьном выражении только одним символом из указанного в квадратных скобках набора (дефис используется для указания диапазона);

[^ ABC 0-9] - может быть заменен в символьном выражении только одним символом, кроме тех, что указаны в квадратных скобках (дефис используется для указания диапазона).

Рассмотрим пример использования оператора (слайд 27 ). Применение образца для значения столбца Должность в данном случае позволило отобрать строки со значениями «Ст.преп.» и «Проф»

Раздел ORDER BY

Раздел ORDER BY предназначен для упорядочения набора данных, возвращаемого после выполнения запроса. Полный синтаксис раздела ORDER BY следующий (слайд 28 ).

Параметр <условие_сортировки> требует задания выражения, в соответствии с которым будет осуществляться сортировка строк. В простейшем случае это выражение представляет собой имя столбца одного из источников данных запроса.

Следует отметить, что в выражении, в соответствии с которым осуществляется сортировка строк, могут использоваться и столбцы, не указанные в разделе SELECT, то есть не входящие в результат выборки.

Раздел ORDER BY разрешает использование ключевых слов ASC и DESC, с помощью которых можно явно указать, каким образом следует упорядочить строки. При указании ключевого слова ASC данные будут отсортированы по возрастанию. Если необходимо отсортировать данные по убыванию, указывается ключевое слово DESC. По умолчанию используется сортировка по возрастанию.

Данные можно отсортировать по нескольким столбцам. Для этого необходимо ввести имена столбцов через запятую по порядку сортировки. Сначала данные сортируются по столбцу, имя которого было указано первым в разделе ORDER BY . Затем, если имеется множество строк с одинаковыми значениями в первом столбце, выполняется дополнительная сортировка этих строк по второму столбцу (внутри группы с одинаковым значением в первом столбце) и т.д.

Приведем пример сортировки по двум столбцам (слайд 28 ).

Раздел UNION

Раздел UNION служит для объединения результатов выборки, возвращаемых двумя и более запросами.

Рассмотрим синтаксис раздела UNION (слайд 29 ).

Чтобы к результатам запросов можно было применить операцию объединения, они должна соответствовать следующим требованиям:

запросы должны возвращать одинаковый набор столбцов (причем необходимо гарантировать одинаковый порядок следования столбцов в каждом из запросов);

типы данных соответствующих столбцов второго и последующих запросов должны поддерживать неявное преобразование или совпадать с типом данных столбцов первого запроса;

ни один из результатов не может быть отсортирован с помощью раздела ORDER BY (однако общий результат может быть отсортирован, как будет показано ниже)

Указание ключевого слова ALL предписывает включать в результат повторяющиеся строки. По умолчанию повторяющиеся строки в результат не включаются.

Продемонстрируем применение раздела UNION . Рассмотрим таблицы «Кадровый_Состав» и «Студенты» и попробуем построить, например, общий список и учащихся, и преподавателей, номер телефона которых начинается на 120. Упорядочим полученный список по алфавиту, добавив предложение ORDER BY (слайд 29 ).

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