Для этого понадобится установленная система управления базами данных (СУБД) DB2. Мы будем использовать диалект языка SQL, который используется именно в этой СУБД.
Первая команда, которую мы будем применять для создании базы данных - это команда CREATE DATABASE. Её синтаксис следующий:
CREATE TABLE ИМЯ_ТАБЛИЦЫ (имя_первого_столбца тип данных, ..., имя_последнего_столбца тип данных, первичный ключ, ограничения (не обязательно))
Так как наша база данных моделирует сеть аптек, то в ней есть такие сущности, как "Аптека" (таблица Pharmacy в нашем примере создания базы данных), "Препарат" (таблица Preparation в нашем примере создания базы данных), "Доступность (препаратов в аптеке)" (таблица Availability в нашем примере создания базы данных), "Клиент" (таблица Client в нашем примере создания базы данных) и другие, которые здесь подробно и разберём.
Разработке модели "сущность-связь" можно посвятить не одну статью, но если нас прежде всего интересуют команды языка SQL для создания базы данных и таблиц в ней, то условимся считать, что связи между сущностями уже нам понятны. На рисунке ниже приведено представление модели нашей базы данных с атрибутами сущностей (таблиц) и связями между таблицами.
Для увеличения рисунка можно нажать на него левой кнопкой мыши.
Как уже говорилось, в разбираемом здесь примере создания базы данных использовался вариант языка SQL, который используется в системе управления базами данных (СУБД) DB2. Он является регистронезависимым, то есть не имеет значение, набраны ли команды и отдельные слова в них строчными или прописными буквами. Для иллюстрации этой особенности приведены команды без особой системы набранные строчными и прописными буквами.
Теперь приступим к созданию команд. Первая наша команда SQL создаёт базу данных PHARMNETWORK:
Код SQL
CREATE DATABASE PHARMNETWORK
Описание таблицы PHARMACY (Аптека):
Пишем команду, которая создаёт таблицу PHARMACY (Аптека), значения первичного ключа PH_ID генерируются автоматически от 1 с шагом 1, вносится проверка на то, чтобы значения атрибута Address в этой таблице были уникальными:
Код SQL
CREATE TABLE PHARMACY(PH_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Address varchar(40) NOT NULL, PRIMARY KEY(PH_ID), CONSTRAINT PH_UNIQ UNIQUE(Address))
Описание таблицы GROUP (Группа препаратов):
Пишем команду, которая создаёт таблицу Group (Группа препаратов), значения первичного ключа GR_ID генерируются автоматически от 1 с шагом 1, проводится проверка уникальности наименования группы (для этого используется ключевое слово CONSTRAINT):
Код SQL
CREATE TABLE GROUP(GR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, PRIMARY KEY(GR_ID), CONSTRAINT GR_UNIQ UNIQUE(Name))
Описание таблицы PREPARATION (Препарат):
Команда, которая создаёт таблицу PREPARATION, значения первичного ключа PR_ID генерируются автоматически от 1 с шагом 1, определяется, что значения внешнего ключа GR_ID (Группа препаратов) не могут принимать значение NULL, определена проверка уникальности значений атрибута Name:
Код SQL
CREATE TABLE PREPARATION(PR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, GR_ID int NOT NULL, PRIMARY KEY(PR_ID), constraint PR_UNIQ UNIQUE(Name))
Далее нам требуется позаботиться об ограничениях целостности. Это очень удобно слелать с помощью команды alter table. Эта команда изучается на уроке SQL ALTER TABLE - изменение таблицы базы данных.
Теперь самое время создать таблицу AVAILABILITY (Доступность или Наличие препарата в аптеке). Её описание:
Пишем команду, которая создаёт таблицу AVAILABILITY. Определяются даты начала (не может быть NULL) и окончания (по умолчанию NULL).
Код SQL
CREATE TABLE AVAILABILITY(A_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL, QUANTITY INT NOT NULL, MART varchar(3) DEFAULT NULL, PRIMARY KEY(A_ID), CONSTRAINT AVA_UNIQ UNIQUE(PH_ID, PR_ID))
Создаём таблицу DEFICIT (Дефицит препарата в аптеке, то есть, неудовлетворённый запрос). Её описание:
Пишем команду, которая создаёт таблицу DEFICIT:
Код SQL
CREATE TABLE DEFICIT(D_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, Solution varchar(40) NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL)
Осталось немного. Мы уже дошли до команды, которая создаёт таблицу Employee (Сотрудник). Её описание:
Пишем команду, которая создаёт таблицу Employee (Сотрудник), с первичным ключом, генерируемым по тем же правилам, что и первичные ключи предыдущих таблиц, в которых они существуют. Внешним ключом PH_ID Сотрудник связан с PHARMACY (Аптекой).:
Код SQL
CREATE TABLE EMPLOYEE(E_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), F_Name varchar(40) NOT NULL, L_Name varchar(40) NOT NULL, POST varchar(40) NOT NULL, PH_ID INT NOT NULL, PRIMARY KEY(E_ID))
Очередь дошла до создании таблицы CLIENT (Клиент). Её описание:
Пишем команду, создающую таблицу CLIENT (Клиент), в отношении первичного ключа которого справедливо предыдущее описание. Особенность этой таблицы в том, что её атрибуты F_Name и L_Name имеют по умолчанию значение NULL. Это связано с тем, что клиенты могут быть как зарегистрированными, так и незарегистрированными. У последних значения имени и фамилии как раз и будут неопределёнными (то есть NULL):
Код SQL
CREATE TABLE CLIENT(C_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), FName varchar(40) DEFAULT NULL, LName varchar(40) DEFAULT NULL, DateReg varchar(20), PRIMARY KEY(C_ID))
Предпоследняя таблица в нашей базе данных - таблица BASKET (Корзина покупок). Её описание:
Пишем команду, создающую таблицу BASKET (Корзина покупок), так же с уникальным и инкрементируемым первичным ключом и связанную внешним ключами C_ID и E_ID с Клиентом и Сотрудником соответственно:
Код SQL
CREATE TABLE BASKET(BS_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), C_ID INT NOT NULL, E_ID INT NOT NULL, PRIMARY KEY(BS_ID))
И, наконец, последняя таблица в нашей базе данных - таблица BUYING (покупка). Её описание:
| Имя поля | Тип данных | Описание |
| B_ID | smallint | Идентификационный номер покупки |
| PH_ID | smallint | Идентификационный номер аптеки |
| PR_ID | smallint | Идентификационный номер препарата |
| BS_ID | varchar(40) | Идентификационный номер корзины покупок |
| Price | varchar(20) | Цена |
| Date | varchar(20) | Дата |
Пишем команду, создающую таблицу BUYING (покупка), так же с уникальным и инкрементируемым первичным ключом и связанную внешними ключами BS_ID, PH_ID, PR_ID с Корзиной покупок, Аптекой и Препаратом соответственно:
Код SQL
CREATE TABLE BUYING(B_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BS_ID INT NOT NULL, PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateB varchar(20) NOT NULL, Price Double NOT NULL, PRIMARY KEY(B_ID))
И совсем уже в завершение темы создания базы данных обещанное отступление о соблюдении ограничений целостности, когда решение - более сложное, чем написание команды. В нашем примере необходимо соблюдать следующее условие: при покупке единицы препарата значение количества этого препарата в таблице AVAILABILITY должно соответственно уменьшиться. Вообще говоря, для таких операций в языке SQL существуют особые средства, называемые триггерами. Но триггеры - вещь капризная: на практике они могут и не сработать или сработать не так, как предусмотрено. Поэтому разработчики по возможности ищут программные средства решения таких задач, пример которых упомянут в этом абзаце.
Язык SQL используется не только для обработки информации, но и предназначена для выполнения всех операций с базами данных и таблицами, включая также создание таблиц и работа с ними. Существует два способа создания таблиц: 1) большинство СУБД обладают визуальным интерфейсом для интерактивного создания таблиц и управление ими; 2) таблицами можно манипулировать, используя операторы SQL. Стоит отметить, что, когда вы используете интерактивный инструментарий СУБД, на самом деле вся работа выполняется операторами SQL, т.е. интерфейс сам создает эти команды незаметно для пользователя (это подобно на запись макроса в Excel, когда макрорекодер записывает ваши действия и преобразует их в команды VBA).
1. Создание таблиц
Для создания таблиц программным способом используют оператор CREATE TABLE . Для этого нужно указать следующие данные:
- - имя таблицы, которое указывается после ключевого слова CREATE TABLE
- - имена и определения столбцов таблицы, отделены запятыми
- - в некоторых СУБД также требуется, чтобы было указано местоположение таблицы.
Давайте создадим новую таблицу и назовем ее Customers :
CREATE TABLE
Customers (
ID CHAR(10)
NOT NULL Primary key
,
Custom_name CHAR(25)
NOT NULL,
Custom_address CHAR(25)
NULL,
Custom_city CHAR(25)
NULL,
Custom_Country CHAR(25)
NULL,
ArcDate CHAR(25)
NOT NULL, DEFAULT
NOWO)
Так мы сначала указываем название новой таблицы, затем в скобках перечисляем столбцы, которие будем создавать, причем их названия не могут повторяться в пределах одной таблицы. После названий столбцов указывается тип данных для каждого поля (CHAR (10) ), затем отмечаем может ли поле содержать пустые значения (NULL или NOT NULL ), а также нужно указать поле, которое будет первичным ключом (Primary key ).
Язык SQL также позволяет определять для каждого поля значение по умолчанию, то есть, если пользователь не укажет значение определенного поля - оно будет автоматически проставлено СУБД. Значение по умолчанию определяется ключевым словом DEFAULT при определении столбцов оператором CREATE TABLE .
2. Обновление таблиц
Для того, чтобы изменить таблицу в SQL используется оператор ALTER TABLE . При использовании данного оператора необходимо ввести следующую информацию:
- - имя таблицы, которую мы хотим изменить
- - перечень изменений, которые мы хотим сделать.
Для примера давайте добавим новую колонку в таблицу Sellers , в которой будем указывать телефон реализатора:
ALTER TABLE Sellers ADD Phone CHAR (20)
Кроме добавления столбцов, мы можем их удалять. Давайте теперь удалим поле Phone . Для этого пропишем следующий запрос:
ALTER TABLE Sellers DROP COLUMN Phone
3. Удаление таблиц
Удаление таблиц осуществляется с помощью оператора DROP TABLE . Чтобы удалить таблицу Sellers_new , мы можем прописать следующий запрос:
DROP TABLE Sellers_new
Во многих СУБД применяются правила, предотвращающие удаление таблиц, которые являются уже связаны с другими таблицами. Если эти правила действуют и вы удаляете такую таблицу, то СУБД блокирует операцию удаления до тех пор, пока не будет удалена связь. Такие меры предотвращают случайное удаление нужных таблиц.
1 голосПриветствую вас на моем блоге сайт. Сегодня поговорим про sql запросы для начинающих. У некоторых вебмастеров может возникнуть вопрос. Зачем изучать sql? Разве нельзя обойтись ?
Оказывается, что для создания профессионального интернет-проекта этого будет недостаточно. Sql используется чтобы работать с БД и создания приложений для Вордпресс. Рассмотрим, как использовать запросы подробнее.
Что это такое
Sql - язык структурированных запросов. Создан для определения типа данных, предоставления доступа к ним и обработке информации за короткие промежутки времени. Он описывает компоненты или какие-то результаты, которые вы хотите видеть на интернет-проекте.
Если говорить по-простому, то этот язык программирования позволяет добавлять, изменять, искать и отображать информацию в БД. Популярность mysql связана с тем, что он используется для создания динамических интернет-проектов, основа которых составляет база данных. Поэтому для разработки функционального блога вам необходимо выучить этот язык.
Что может делать
Язык sql позволяет:
- создавать таблицы;
- изменять получать и хранить разные данные;
- объединять информацию в блоки;
- защитить данные;
- создавать запросы в access.
Важно! Разобравшись с sql вы сможете писать приложения для Вордпресс любой сложности.
Какая структура
БД состоит из таблиц, которые можно представить в виде Эксель файла.
У нее имеется имя, колонки и ряд с какой-то информацией. Создавать подобные таблицы можно при помощи sql запросов.
Что нужно знать
Основные моменты при изучении Sql
Как уже отмечалось выше, запросы применяются для обработки и ввода новой информации в БД, состоящую из таблиц. Каждая ее строка - это отдельная запись. Итак, создадим БД. Для этого напишите команду:
Create database ‘bazaname’
В кавычках пишем имя БД на латинице. Старайтесь придумать для нее понятное имя. Не создавайте базу типа «111», «www» и тому подобное.
После создания БД устанавливаем :
SET NAMES ‘utf-8’
Это нужно чтобы контент на сайте правильно отображаться.
Теперь создаем таблицу:
CREATE TABLE ‘bazaname’ . ‘table’ (
id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
log VARCHAR(10),
pass VARCHAR(10),
date DATE
Во второй строке мы прописали три атрибута. Посмотрим, что они означают:
- Атрибут NOT NULL означает, что ячейка не будет пустой (поле обязательное для заполнения);
- Значение AUTO_INCREMENT — автозаполнение;
- PRIMARY KEY — первичный ключ.
Как добавить информацию
Чтобы заполнить поля созданной таблицы значениями, используется оператор INSERT. Пишем такие строки кода:
INSERT INTO ‘table’
(login , pass , date) VALUES
(‘Vasa’, ‘87654321’, ‘2017-06-21 18:38:44’);
В скобках указываем название столбцов, а в следующей - значения.
Важно! Соблюдайте последовательность названий и значений столбцов.
Как обновить информацию
Для этого используется команда UPDATE. Посмотрим, как изменить пароль для конкретного пользователя. Пишем такие строки кода:
UPDATE ‘table’ SET pass = ‘12345678’ WHERE id = ‘1’
Теперь поменяйте пароль ‘12345678’. Изменения происходят в строке с «id»=1. Если не писать команду WHERE - поменяются все строки, а не конкретная.
Рекомендую вам приобрести книгу «SQL для чайников ». С ее помощью вы сможете шаг за шагом профессионально работать с БД. Вся информация построена по принципу от простого к сложному, и будет хорошо восприниматься.
Как удалить запись
Если вы написали что-то не так, исправьте это при помощи команды DELETE. Работает так же, как и UPDATE. Пишем такой код:
DELETE FROM ‘table’ WHERE id = ‘1’
Выборка информации
Для извлечения значений из БД используется команда SELECT. Пишем такой код:
SELECT * FROM ‘table’ WHERE id = ‘1’
В данном примере в таблице выбираем все имеющиеся поля. Это происходит если прописать в команде звездочку «*». Если нужно выбрать какое-то выборочное значение пишем так:
SELECT log , pass FROM table WHERE id = ‘1’
Необходимо отметить, что умения работать с базами данных будет недостаточно. Для создания профессионального интернет-проекта придется научиться добавлять на страницы данные из БД. Для этого ознакомьтесь с языком веб-программирования php. В этом вам поможет классный курс Михаила Русакова .
Удаление таблицы
Происходит при помощи запроса DROP. Для этого напишем такие строки:
DROP TABLE table;
Вывод записи из таблицы по определенному условию
Рассмотрим такой код:
SELECT id, countri, city FROM table WHERE people>150000000
Он отобразит записи стран где населения больше ста пятидесяти миллионов.
Объединение
Связать вместе несколько таблиц возможно используя Join. Как это работает посмотрите подробнее в этом видео:
PHP и MySQL
Еще раз хочу подчеркнуть, что запросы при создании интернет-проекта - это обычное дело. Чтобы их использовать в php-документах выполните такой алгоритм действий:
- Соединяемся с БД при помощи команды mysql_connect();
- Используя mysql_select_db() выбираем нужную БД;
- Обрабатываем запрос при помощи mysql_fetch_array();
- Закрываем соединение командой mysql_close().
Важно! Работать с БД не сложно. Главное - правильно написать запрос.
Начинающие вебмастера подумают. А что почитать по этой теме? Хотелось бы порекомендовать книгу Мартина Грабера «SQL для простых смертных ». Она написана так, что новичкам все будет понятно. Используйте ее в качестве настольной книги.
Но это теория. Как же обстоит дело на практике? В действительности интернет-проект нужно не только создать, но еще и вывести в ТОП Гугла и Яндекса. В этом вас поможет видеокурс «Создание и раскрутка сайта ».
Видео инструкция
Остались еще вопросы? Посмотрите подробнее онлайн видео.
Вывод
Итак, разобраться с написанием sql запросов не так трудно, как кажется, но сделать это нужно любому вебмастеру. В этом помогут видеокурсы, описанные выше. Подпишитесь на мою группу ВКонтакте чтобы первыми узнавать о появлении новой интересной информации.
Разновидность языка, применяемая в конкретной СУБД, называется диалектом 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}]
{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могут сравниваться значения двух столбцов одной таблицы и столбцы разных таблиц.
Итак, мы познакомились с типами данных, теперь будем усовершенствовать таблицы для нашего форума. Сначала разберем их. И начнем с таблицы 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 таблицы, в которых есть данные. Встает вопрос - как посмотреть, какие данные хранятся в таблицах. Этим мы и займемся на следующем уроке.
