6. 4. Визначення даних засобами sql
Визначення даних – це маніпулювання цілими таблицями. Сюди можна віднести:
створення нової таблиці;
видалення таблиці;
зміна складу полів таблиці;
створення і видалення індекса.
Ці дії виконуються з допомогою підмножини операторів визначення даних мови SQL.
До цього часу поняття "таблиця", як правило, зв'язувалося з реальною або базовою таблицею, тобто c таблицею, для кожного рядка якої в дійсності існує деякий двійник, що зберігається у фізичній пам'яті машини (рис. 11). Однак SQL використовує і створює ряд віртуальних (начебто існуючих) таблиць: виглядів, курсорів і неіменованих робочих таблиць, у яких формуються результати запитів на одержання даних з базових таблиць і, можливо, виглядів. Це таблиці, які не існують у базі даних, але як би існують з погляду користувача.
Рис. 11. База даних у сприйнятті користувача
Створення і знищення таблиці
Для створення таблиці служить оператор, який має наступний формат:
CREATE TABLE <Ім´я таблиці>
(<Ім´я поля> <Тип даних> [<атрибут>],
...
< Ім´я поля > <Тип даних> [<атрибут>])
Обов´язковими операндами є ім´я таблиці та ім´я як мінімум одного поля з відповідним типом даних. Зауважимо, що замість імені таблиці вказується ім’я головного файлу таблиці.
Наприклад, небхідно створити базу даних працівників рекламного агентства. Ця база повинна містити дві таблиці. Таблиця „Штат рекламних агентів” містить наступну інформацію про рекламних агентів, а саме:
код працівника;
прізвище, ім’я, по батькові;
домашня адреса;
телефон;
дата народження;
освіта;
стаж роботи.
У другій таблиці „Обсяг операцій” має міститися інформація про операції, які здійснюють рекламні агенти:
номер операції;
код працівника;
дата операції;
сума операції;
назва рекламодавця.
Команди створення цих таблиць приведено нижче.
create table stat
(cod smallint not null,
prizv char(20) not null,
adressa char(20) null,
tel char(10) null,
data_nar smalldatetime not null,
osvita char(20) null,
stag int not null)
create table obsag
(nom smallint not null,
cod smallint not null,
data s malldatetime not null,
suma int not null,
nazva char(20) null)
Якщо в таблиці „Обсяг операцій” необхідно вказати поля „Кількість товару”, „Ціна товару” і „Сума операції”, то необхідно використати розрахункове поле, яке в команді створення таблиці має назву „suma” і розраховується як добуток поля „Кількість товару” і поля „Ціна товару” :
номер операції;
код працівника;
дата операції;
кількість товару;
ціна товару;
сума операції;
назва рекламодавця,
то необхідно використати розрахункове поле, яке в команді створення таблиці має назву „сума операції” і розраховується як добуток кількості на ціну:
create table obsag
(nom smallint not null,
cod smallint not null,
data s malldatetime not null,
kilkist smallint not null,
cina money not null,
suma as kilkist * cina,
nazva char(20) null)
Щоб переглянути структуру створеної таблиці використовують процедуру sp_help.
Наприклад, sp_help stat.
Результат виконання команди має такий вигляд:
Column_name Type Computed Lengtp Prec Scale Nullable
cod smallint no 2 5 0 no
prizv char no 20 no
adressa char no 20 no
tel char no 10 yes
data_nar smalldatetime no 4 no
osvita char no 10 yes
stag int no 4 10 0 no
Для знищення таблиці призначений оператор:
DROP TABLE <ім´я таблиці>
Наприклад: drop table stat
Зміна складу полів таблиці
Зміна складу полів таблиці полягає у додаванні або знищенні полів і призводить до зміни структури таблиці. Зміна складу полів таблиці виконується оператором:
ALTER TABLE <ім´я таблиці>
ADD <ім´я поля> <тип даних>
ALTER TABLE <ім´я таблиці>
DROP COLUMN <ім´я поля>
Оператор ADD добавляє до таблиці нове поле, ім’я і тип якого задаються як і в операторі CREATE, а операнд DROP знищує в таблиці поле з заданим іменем. Наприклад, додавання до таблиці stat поля number виконується наступним оператором:
alter table stat
add number int
Коли з допомогою оператора ALTER TABLE до таблиці додається новий стовпець, в нього автоматично поміщаються NULL-значення.
Знищення поля number з таблиці stat виконується з допомогою оператора:
alter table stat
drop column number
Оператор
alter table stat
add number int identity (1,1)
добавляє до таблиці поле number, якому призначається атрибут identity (лічильник).
З допомогою оператора ALTER TABLE можна змінити тип даних полів таблиці, а також їх ширину. Наприклад, оператор
alter table stat
alter column telef int
змінює тип поля telef на цілочисловий (int).
Створення та знищення індексів
Індекс забезпечує швидкий доступ до даних, які зберігаються в полі, для якого він створений. Для прискорення операцій з таблицею індексними роблять поля, за якими часто проводиться пошук і відбір записів. Індекс створюється оператором наступного формату:
CREATE INDEX
< ім’я індекса > ON < ім’я таблиці > (< ім’я поля >, ..., [< ім’я поля >]);
Одним оператором можна створити один індекс, при цьому одне поле може входити в склад декількох індексів. Наприклад:
create index
ind Name ON stat (cod)
Для знищення індекса використовується оператор:
DROP INDEX
< ім’я таблиці > . < ім’я індекса >
або
DROP INDEX
< ім’я таблиці > .PRIMARY
Під час знищення індекса таблиця не повинна використовуватися іншим додатком.
Додавання первинних і вторинних ключів. Зв’язування таблиць.
Первинний ключ – це унікальне поле (або набір полів), який визначає рядки таблиці бази даних. Первинні ключі забезпечують унікальність даних шляхом створення унікального індексу в таблиці, в якій вони розміщені. Вторинні ключі – це поля таблиці, які відповідають первинним ключам інших таблиць. Співвідношення між первинними і вторинними ключами визначають область допустимих значень для вторинного ключа. Цілісність області значень зовнішнього ключа є одним із способів забезпечення цілісності зв’язків між відповідними наборами полів. При визначенні вторинних ключів індекси в таблиці не створюються.
В SQL Server первинні і вторинні ключі можна додати трьома способами: використовуючи SQL Server Enterprise Manager, оператор ALTER TABLE ... ADD CONSTRAINT або визначивши опцію PRIMARY/FOREIGN KEY в операторі CREATE TABLE.
У синтаксисі команди CREATE TABLE передбачена можливість додавання в розділі CONSTRAINT елементів PRIMARY KEY або FOREIGN KEY. Спрощений формат команди:
CREATE TABLE <Ім´я таблиці>
(<Ім´я поля> <Тип даних> CONSTRAINT . . ., . . . )
Наприклад, оператор для створення таблиці, коли не вказується ім’я первинного ключа (воно буде призначено сервером) виглядає так:
CREATE TABLE <Ім´я таблиці>
(<Ім´я поля> smallint PRIMARY KEY)
Якщо потрібно явно вказати ім’я первинного ключа при створенні таблиці, то команда має наступний вигляд:
CREATE TABLE <Ім´я таблиці>
(<Ім´я поля> smallint CONSTRAINT pk_<Ім´я поля> PRIMARY KEY,)...)
Вторинний ключ таблиці_2, який посилається на таблицю_1, можна створити наступною командою:
CREATE TABLE <Ім´я таблиці_2>
(<Ім´я поля_2> smallint FOREIGN KEY <Ім´я поля_2> REFERENCES <Ім´я таблиці_1>(<Ім´я поля_1>),...)
Цією командою встановлюється зв’язок між таблицею_1 і таблицею_2, причому ключові поля у двох таблицях повинні мати абсолютно однакові типи даних.
Тепер розглянемо оператори, які використовуються для зміни таблиць з метою додавання первинних і вторинних ключів.
Для додавання первинного ключа без визначення його імені використовують оператор наступного формату:
ALTER TABLE <Ім´я таблиці_1>
ADD PRIMARY KEY ( <Ім´я поля_1>)
Наприклад, таблиці „Штат рекламних агентів” і „Обсяг операцій” необхідно зв’язати за полем Код працівника. При цьому поле Код працівника першої таблиці є первинним ключем, а поле Код працівника другої таблиці – вторинним ключем. Команда додавання до таблиці „Штат рекламних агентів” первинного ключа має наступний вигляд:
alter table stat
add primary key (cod)
Додавання вторинного ключа до таблиці_2, який посилається на таблицю_1, виконується командою:
ALTER TABLE <Ім´я таблиці_2>
ADD FOREIGN KEY (<Ім´я поля_2>) REFERENCES <Ім´я таблиці_1> (<Ім´я поля_1>)
Аналогічно до команди CREATE TABLE, цією командою встановлюється зв’язок між таблицею_1 і таблицею_2, причому ключові поля у двох таблицях повинні мати абсолютно однакові типи.
Наприклад, додавання вторинного ключа до таблиці „Обсяг операцій” і встановлення зв’язку з таблицею „Штат рекламних агентів” за полем Код працівника виконується командою:
alter table obsag
add foreign key (cod) references stat (cod)
Для відображення інформації про ключі в SQL Server використовуються дві системні процедури. Основний спосіб відображення інформації про ключі полягає у використанні процедури sp_helpconstraint, яка має наступний формат:
SP_HELPCONSTRAINT <Ім´я таблиці>
Наприклад,
sp_helpconstraint stat
Результат виконання команди зображено на рис. 12.
Рис. 12. Інформація про ключі таблиці stat.
Системна процедура sp_help виводить загальну інформацію про таблиці баз даних. Частина цієї інформації стосується ключів таблиці. Формат процедури:
SP_HELР <Ім´я таблиці>
Видалення первинних і вторинних ключів виконується командою
ALTER TABLE <Ім´я таблиці>
DROP CONSTRAINT <Ім´я ключа>
Зауважимо, що не можна видалити первинний ключ, якщо він є вторинним ключем для інших таблиць. Спочатку потрібно видалити вторинні ключі.
Наприклад, команди для видалення зовнішнього і внутрішнього ключів:
alter table obsag
drop constraint FK__obsag__cod__239E4DCF
alter table stat
drop constraint PK__stat__22AA2996
Модифікація записів
Модифікація записів полягає в редагуванні записів, в додаванні до набору даних нових записів або видаленні існуючих записів.
Додавання записів здійснюється з допомогою оператора INSERТ, який дозволяє добавити до таблиці один або декілька записів. При додаванні одного запису оператор INSERТ має формат:
INSERТ INTO <Ім’я таблиці>
[(<Список полів>)]
VALUES (<Список значень>)
В результаті виконання цього оператора до таблиці, ім’я якої вказано після слова INTO, додається один запис. Для додавання запису заповнюються поля, перелічені в списку полів. Значення полів беруться із списку, розміщеного після слова VALUES. Списки полів і списки значень повинні відповідати один одному за кількістю елементів і за типом елементів. При цьому порядок полів і значень може відрізнятися від порядку полів в таблиці.
Наприклад, таблицю „Штат рекламних агентів” необхідно наповнити
наступними даними:
Таблиця 6.2. Штат рекламних агентів
Код працівника |
Прізвище, ім’я, по батькові |
Домашня адреса |
Телефон |
Дата народження |
Освіта |
Стаж роботи |
101 |
Шостак Т.Х. |
Львів |
772345 |
05/05/1968 |
вища |
10 |
102 |
Драч О.О. |
Львів |
456789 |
12/31/1970 |
середня |
7 |
103 |
Пилипів І.В. |
Стрий |
568905 |
08/25/1967 |
вища |
5 |
104 |
Леськів А.В. |
Київ |
2290706 |
03/13/1976 |
середня |
2 |
105 |
Дорош К.Ф. |
Київ |
2908745 |
07/23/1952 |
вища |
16 |
Таблиця 6.3. „Обсяг операцій” має містити такі дані:
Таблиця 6.2. Обсяг операцій
Номер операції |
Код працівника |
Дата операції |
Сума операції |
Назва рекламодавця |
1 |
101 |
05/05/05 |
1200 |
Ельдорадо |
2 |
102 |
05/07/05 |
2300 |
Фокстрот |
3 |
102 |
05/11/05 |
3400 |
Маестро |
4 |
103 |
05/12/05 |
800 |
Корона |
5 |
101 |
05/12/05 |
1200 |
Фокстрот |
6 |
101 |
06/01/05 |
567 |
Корона |
7 |
104 |
06/11/05 |
789 |
Ельдорадо |
8 |
104 |
06/11/05 |
1567 |
Фокстрот |
9 |
105 |
06/25/05 |
2340 |
Ельдорадо |
Додавання першого запису до таблиці „Штат рекламних агентів” виконується командою:
insert into stat
(cod, prizv, adressa, tel, data_nar, osvita, stag)
values (101, 'Шестак', 'Львів', 772345, '05/05/1968', 'вища',10)
Аналогічно додаються решта записів полів таблиці.
Список полів в операторі INSERТ може бути відсутнім, тоді необхідно вказувати значення для всіх полів таблиці. Порядок і тип значень повинен відповідати порядку і типу полів таблиці.
При додаванні відразу декількох записів оператор INSERТ має формат:
INSERТ INTO <Ім’я таблиці>
(<Список полів>)
Оператор SELECT
Тут значення полів нових записів визначаються через значення полів записів, відібраних з допомогою оператора SELECT. Кількість доданих записів дорівнює кількості відібраних записів. Список значень полів в операторі SELECT повинен відповідати кількості і типу полів в списку оператора INSERТ.
Редагування записів – це зміна значень полів в групі записів. Редагування записів виконується оператором UPDATE наступного формату:
UPDATE <Ім’я таблиці>
SET <Ім’я поля> = <Вираз>
[WHERE <Критерії відбору>]
Після виконання оператора UPDATE для всіх записів, що задовольняють критерії відбору, змінюються значення полів. <Ім’я поля> вказує поле, що редагується, всієї сукупності даних, а <Вираз> визначає значення, які будуть присвоєні цьому полю.
Наприклад,
update stat
set adressa= 'Київ', stag=10
З допомогою оператора UPDATE можна змінити декілька рядків, які задовольняють критерію, визначеному в директиві WHERE.
Наприклад,
update obsag
set suma = suma -20
where nazva =’Маестро’
Для рекламодавця ’Маестро’ сума операції зменшується на 20 одиниць. Якщо забрати операнд WHERE, то зміняться значення поля suma у всіх записах таблиці. Наприклад,
update obsag
set suma = suma -20
Видалення записів виконується оператором DELETE наступного формату:
DELETE FROM <Ім’я таблиці>
[WHERE <Критерії відбору>]
В результаті виконання цього оператора з таблиці, ім’я якої вказано після слова FROM, видаляються всі записи, які задовільняють критерій відбору. Якщо критерій відбору не задано, то з таблиці буде видалено всі записи.
Наприклад,
delete from stat
where stag <10
З таблиці stat видаляються всі записи про працівників, стаж роботи яких менший 10.