Слайд 1Введение в язык SQL
5. Запросы на создание и изменение компонент базы
данных
Автор: к.т.н. Герасимов Н.А.
Слайд 2Занятие 5. Запросы на создание и изменение базы данных
Тема занятия :
Освоение запросов, с помощью которых производится изменение базы данных (создание, изменение и удаление таблиц)
Команды определения таблиц относятся к разделу языка SQL, который принято называть языком DDL (Data Definition Language). Команды этого раздела позволяют реализовать процедуры создания и изменения объектов (таблиц, индексов, представлений и т.п.) реляционной базы данных.
Слайд 3Раздел DDL
(Data Definition Language)
Слайд 4Команда создания базы данных
CREATE DATABASE [параметры]
Пример:
CREATE DATABASE my_first_db
DEFAULT CHARACTER
SET utf8
COLLATE utf8_general_ci
USE my_first_db
Слайд 5Полезные команды MySQL
SHOW DATABASES – показывает список БД
USE -
активизирует БД.
SHOW TABLES – показывает список в базе.
DESCRIBE <имя таблицы> - выводит описание таблицы.
STATUS – показывает параметры окружения
SHOW FIELDS FROM < имя таблицы > - показывает поля таблицы
HELP – выдает список команд
Слайд 65.1. Запросы на создание таблиц базы данных
В реляционных базах данных таблицы
состоят из записей и полей (столбцов). Чтобы создать таблицу, достаточно задать ее имя и описать поля.
Описание каждого поля содержит:
имя поля, тип данных в поле, (опционально можно задать синоним, размер поля, индекс и ограничения).
Слайд 7Команда CREATE TABLE
Создание таблиц происходит с помощью команды CREATE TABLE, синтаксис
которой можно представить так:
CREATE TABLE <имя таблицы> (
<имя поля 1> <тип данных> [<размер>] [ограничения],
<имя поля 2> <тип данных> [<размер>] [ограничения],
… ,
<имя поля N> <тип данных> [<размер>] [ограничения] );
Слайд 8Рекомендация (!!!):
Имя таблицы и имена столбцов обычно составляются из латинских букв
(хотя Access допускает использование любых алфавитных символов) и желательно без пробелов.
Это связано с тем, что если Вам придется использовать созданную базу данных в приложениях построенных на продуктах других фирм (т.е. не фирмы Microsoft), то могут возникнуть проблемы доступа к таблицам. Особенно чувствительны к именам таблиц и полей Интернет приложения.
Использование кириллицы предпочтительно в синонимах имен столбцов.
Слайд 9Описание типа данных
Наиболее часто используемые типы данных в описании полей следующие:
CHAR
(n) - символьные данные;
INTEGER - числовые целые;
FLOAT - числовые с плавающей запятой;
DATE - данные типа даты.
Слайд 10Справка:
Полный список типов данных можно получить в команде «Справка» справочной системы
Access (разделе: «Эквивалентные типы данных ANSI SQL»). Фрагмент справочной таблицы типов данных Access приведен ниже:
Слайд 14Ограничения на поля
При описании типа данных в полях можно, используя специальные
ключевые слова, ввести дополнительные ограничения (columns constraints) на множество допустимых значений. Ниже приведены некоторые ключевые слова, описывающие ограничения:
NOT NULL – значение поля не должно быть пустым;
UNIQUE – значение поля должно быть уникальным;
PRIMERY KEY – определяет принадлежность поля ключу;
DEFAULT – автоматическая подстановка конкретного значения;
CHECK (<условие>) – проверка условия, которому должен удовлетворять вводимое значения в поле.
Слайд 15Пример создания таблицы с помощью команды CREATE
Рассмотрим пример создания таблицы базы
данных, учитывающей сотрудников предприятия.
Положим, что таблица Employees (Сотрудники) будет содержать следующие поля:
код сотрудника (E_KOD) – не пустое ключевое поле,
фамилия (E_FAM),
имя (E_NAM),
дата рождения (E_DATE) и
стаж работы (E_STAG).
Создадим описание таблицы Employees с помощью запроса на SQL, выбрав для этого режим Запросы→Создать→Конструктор→Запрос→SQL и выйдем на окно редактора запросов на языке SQL. В редакторе запишем запрос на языке SQL на создание таблицы Employees
Слайд 16Создание таблицы Employees в редакторе SQL СУБД Access
CREATE TABLE Employees (
E_KOD
CHAR (4) NOT NULL,
E_FAM CHAR (30),
E_NAM CHAR (30),
E_DATE DATE,
E_STAG INTEGER );
Слайд 17Выполнение команды CREATE
Выполним созданный запрос (нажав !) и убедимся, что в
списке таблиц появилась новая таблица (Employees). Вызвав режим «Конструктор таблиц», проверим правильность описания полей. Отметим, что в созданной таблице не указано ключевое поле.
Замечание: При благополучном исполнении запроса на создание новой таблицы Access не выдает никаких сообщений. В случае же обнаружения ошибки синтаксиса или невозможности выполнения запроса будет дано соответствующее сообщение.
Слайд 18Описание ключевого поля
Усложним запрос на создание таблицы Employees, введя ограничение, которое
определяет поле E_KOD как ключевое («первичный ключ»):
CREATE TABLE Employees (
E_KOD CHAR (4) NOT NULL PRIMARY KEY ,
E_FAM CHAR (30),
E_NAM CHAR (30),
E_DAT DATE,
E_STAG INTEGER);
Слайд 19Пояснение:
Ограничение на поле данных можно описать иначе - отдельной строкой. Например,
если необходимо указать что поле является ключевым (поле E_KOD), то его можно как PRIMARY KEY проиндексировать. Иными словами, можно создать специальный список (индекс), который позволит значительно ускорить поиск по полю, к которому «привязан» индекс. Запрос на создание таблицы с индексированным ключевым полем запишется так:
Слайд 20Запрос с индексацией
ключевого поля
CREATE TABLE Employees (
E_KOD CHAR (4) NOT
NULL ,
E_FAM CHAR (30),
E_NAM CHAR (30),
E_DAT DATE,
E_STAG INTEGER,
CONSTRAINT [Index1] PRIMARY KEY (E_KOD) );
Слайд 21Создание связей между таблицами
Для описания связей между таблицами и обеспечения целостности
базы данных используется ключевое слово FOREIGN KEY, полный синтаксис которого следующим образом:
FOREIGN KEY<список столбцов>REFERENCES < имя таблицы > [<список столбцов >]
Слайд 22Фрагмент схемы БД Avto.mdb
PRICE
P_KOD (PK)
ORDERS
O_KOD (PK)
P_KOD (FK)
1
М
Условие выполнения связи (1:M)
тип(PK)=тип(FK)
В
описание таблицы Orders введем строку:
FOREIGN KEY (P_KOD) REFERENCES PRICE(P_KOD)
Слайд 23Пример описания связи между таблицами
Например, таблица Price («Цена») связана с таблицей
Orders («Заказы») через поле pnum (код строки в таблице Price). Поле pnum в таблице Price является ключевым (т.е. оно описано как PRIMARY KEY), а в таблице Orders поле pnum играет роль «внешнего» ключа для связи (FOREIGN KEY).Обычно такая связь отображается на схемах БД как отношение «один-ко-многим» (1:М). Поэтому в таблице Orders поле pnum надо описать как внешний ключ следующим образом:
Слайд 24Пример описания таблицы со связью через внешний ключ
CREATE TABLE Orders (
onum
CHAR (4) NOT NULL PRIMARY KEY,
odate DATE,
amont FLOAT,
cnum CHAR(4),
snum CHAR(4),
pnum CHAR(4),
FOREIGN KEY (pnum) REFERENCES Price (pnum)
);
Слайд 25Внимание: (тип PK) = (тип FK) !!!
Замечание: Типы данных и размер
поля у первичного ключа и у поля внешнего ключа должны быть одинаковые.
Если таблица Orders связана с несколькими таблицами, то каждая связь должна быть описана аналогичным способом.
Слайд 265.2. Запросы на создание индексов в таблицах
Для повышения быстродействия базы данных
в режиме поиска возможно создание специальных средств типа индексов. Синтаксис команды создания индекса можно представить так:
Слайд 27Синтаксис команды на создание индексов
CREATE INDEX ON
(<имя столбца 1> [,<имя столбца 2>] …);
Описание обязательно должно содержать: имя индекса, имя таблицы, к которой привязан индекс и хотя бы одно имя столбца.
Слайд 28Пример создания индекса
Пример использования индекса. Если в базе данных имеется таблица,
в которой хранятся данных счетов клиентов (таблица INVOICES), то очень часто требуется наитии счет по его номеру (поле NUM_INV). В этом случае целесообразно создать индекс (имя индекса INDEX1 ) для поля NUM_INV:
CREATE INDEX INDEX1 ON INVOICES (NUM_INV);
Слайд 29Работа с индексами
Удаление индекса производится следующей командой:
DROP INDEX .
Замечание: следует
заметить, что в некоторых SQL-системах создание и удаление индексов происходит автоматически в зависимости от потребностей системы. Так что пользователи могут ничего не знать о существовании индексов в системе.
Слайд 305.3. Запросы на удаление базы данных
Удаление таблиц выполняется с помощью команды
DROP TABLE <имя таблицы>
Например: Удалить таблицу Employees
DROP TABLE Employees
Слайд 315.4. Модификация таблиц
Модификацию таблиц с помощью SQL-запросов можно выполнить следующей последовательностью
процедур:
- создать новую таблицу с помощью
CREATE TABLE NewTab (<описание столбцов>);
- перенести данные из старой таблицы в новую с помощью
INSERT INTO <условия отбора данных>;
- Удалить исходную таблицу с помощью
DROP TABLE <имя таблицы>.
Слайд 32Команды изменения структуры таблицы
Изменить структуру созданной таблицы можно с помощью команд
- ALTER TABLE – добавить столбец;
- DROP COLUMN – удалить столбец;
- ALTER COLUMN – изменить параметры столбца;
- RENAME COLUMN – переименовать столбец;
Слайд 33Работа со столбцами таблицы
Пример добавления столбца NewPrice в таблицу Price:
ALTER TABLE
Price ADD COLUMN NewPrice FLOAT
Удалить столбец NewPrice из таблицы Price:
ALTER TABLE Price DROP COLUMN NewPrice
Слайд 34Пример добавления связи
ALTER TABLE Sales
ADD
CONSTRAINT K_TempSales_SalesReason
FOREIGN KEY (TempID)
REFERENCES SalesR (Sales_ID)
ON DELETE CASCADE
ON UPDATE CASCADE ;
GO
Слайд 35Вставка CHECK в столбец
ALTER TABLE
ADD
CHECK (выражение);
Пример:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person
CHECK (P_Id>0 AND City='Sandnes')
Слайд 365.5 Пример описания базы даны «Автосалон»
База данных «Автосалон» (avto.mdb) предназначена для
регистрации продаж в автосалоне. База данных avto.mdb состоит из 4-х таблиц:
«Продавцы» (salespeople),
«Покупатели» (customers),
«Цена» (price) и
«Заказы»( orders).
Описание каждой таблицы на языке SQL приведено ниже:
Слайд 37Таблица salespeople
create table salespeople (
snum char(4) not null primary key,
sname char(30)
not null,
saddress char(60),
comm float ,
stel char(20),
semail char(40)
);
Слайд 38Таблица customers
create table customers (
cnum char(4) not null primary key,
cname char(30) not
null,
caddress char(60),
rating int ,
ctel char(20),
cemail char(40)
);
Слайд 39Создание таблицы Price
create table price (
pnun char(4) not null primary key,
pname char(60)
not null,
peddim char(10),
price float ,
pdate datetime Not null
);
Слайд 40Таблица Orders
create table orders (
onum char(4) not null primary key,
odate datetime Not
null,
amount float ,
snum char(4) not null,
cnum char(4) not null,
pnun char(4) not null,
FOREIGN KEY (snum) REFERENCES Salespeople (snum),
FOREIGN KEY (cnum) REFERENCES Customers (pnum),
FOREIGN KEY (pnum) REFERENCES Price (pnum)
);
Слайд 41Пояснение
Выполнить каждый из выше приведенных запросов можно в среде СУБД
Access в режиме
Запросы→Конструктор→Режим SQL.
Слайд 42Пример подготовки запроса
Пример подготовки запроса на создание таблицы в среде
Access (редактор SQL-запросов)
Слайд 43Пояснения
Следует отметить, что при благополучном выполнении запросов СУБД Access не выдает
никаких сообщений, а в режиме Таблицы можно просмотреть результат. При обнаружении ошибки в тексте запроса на экран выдается сообщение, комментирующее тип ошибки.
В результате реализации всех SQL-запросов в среде СУБД Access получаем базу данных, схема которой приведена на рис.2. ниже.
Слайд 44Вариант схемы базы данных avto.mdb
Слайд 45Создание БД в среде MS SQL Server
create databases avtoshop;
use avtoshop;
create table
salespeople (
snum char(4) not null primary key,
sname char(30) not null,
saddress char(60),
comm float(6,2) default 0,
stel char(20),
semail char(40) );
Слайд 46Заключение
Команды этого раздела DDL (Data Definition Language) позволяют реализовать процедуры создания
и изменения объектов (таблиц, индексов, представлений и т.п.) реляционной базы данных
Основные команды DDL:
CREATE, ALERT и DROP
Слайд 47Обсуждение результата
Отлаженный набор SQL-запросов, описывающих базу данных, можно легко перенести в
другую СУБД (например, из СУБД Access в СУБД SQL Server 2000), что позволяет создать «двух-ступенчатую» технологию разработки баз данных:
- сначала отработать схему базы данных в локальной версии СУБД Access;
- затем с минимальными переделками перенести набор отлаженных SQL-запросов в серверную версию СУБД SQL Server 2000, используя для этого Query Analyzer.