Лекція 4. Створення таблиць та уявлень

1. Що таке таблиця?

 БД – це група об’єктів, в яку обов’язково включені об’єкти таблиць. Іншими словами, таблиця – це основний об’єкт БД. Всі дані БД зберігаються в таблицях. Таблиця складається з рядків та стовпців, які містять дані. Таблиця фізично займає місце в БД і може бути як постійною, так і тимчасовою. Визначення кожної таблиці містить визначення кожного поля або стовпця таблиці, яке, в свою чергу, являється сукупністю правил, які вказують на те, що може зберігатися в цьому стовпці. Порушення цих правил призведе до неможливості виконання дій з даними: добавлення записів, внесення змін, видалення даних.

При створенні нової БД в ній вже є певна кількість таблиць (за шаблоном БД model), які називаються системними. В цих таблицях зберігається інформація щодо таблиць цієї бази даних, індексів, ключових полів в кожній із таблиць, інформація щодо користувачів цієї бази, статистична інформація, яка характеризує продуктивність системи тощо.

При створенні таблиці необхідно визначитися щодо таких питань.

• Якого типу дані будуть уводитись у таблицю.
• Яким повинно бути ім’я таблиці.
• Яким стовпцем (або стовпцями) буде задаватися ключове поле (складений ключ).
• Які імена будуть присвоюватися стовпцям таблиці.
• Які типи даних будуть призначатися стовпцям.
• Яка буде довжина кожного стовпця.
• Які стовпці будуть вимагати обов’язкового введення даних.

2. Створення таблиць

На мові SQL таблиця створюється таким чином:

CREATE TABLE
    [ database_name.[ owner ] . | owner. ] table_name
    ( { { column_name data_type [NOT NULL]}
        | column_name AS computed_column_expression
        | < table_constraint > ::= [ CONSTRAINT constraint_name ] }

            | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
    )

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

Розглянемо деякі приклади.

1) Для створення таблиці з трьома стовпцями:
Create Table List_Main
(Name varchar (150) NOT NULL ,
ID        char (10) NOT NULL,
Phone  varchar (15) NULL);

2) Для створення таблиці із ключовим полем:
Create Table List_Main
(Name varchar (150) NOT NULL ,
ID        char (10) NOT NULL PRIMARY KEY,
Phone  varchar (15) NULL);

3) Для створення таблиці, що містить унікальне поле:
Create Table List_Main
(Name varchar (150) NOT NULL ,
ID        char (10) NOT NULL PRIMARY KEY,
Phone  varchar (15) NULL UNIQUE);

4) Для створення таблиці із зовнішнім ключем:

Create Table List_Pay

(ID_ char (10) NOT NULL,

Date_ datetime NULL,

Pay float NOT NULL,

CONSTRAINT ID_FK FOREIGN KEY (ID_) REFERENCES List_Main (ID)));

В цьому прикладі стовпець ID_ назначається зовнішнім ключем таблиці List_Pay. Цей зовнішній ключ посилається на стовпець ID в таблиці List_Main. Зв’язок, що таким чином створюється, має назву батьківське - дочірнє відношення.

5) Для створення тимчасової таблиці із полем, що обчислюється:

CREATE TABLE #mytable 

   (

    low int,

    high int,

    myavg AS (low + high)/2

   );

Створення таблиці на осонові вже існуючої

Таку операцію можна сприймати як створення копії вже існуючої таблиці. При цьому для копіювання можна вибрати як усі стовпці, так і тільки деякі. Базовий синтаксис такий:

Create Table name_new_table As

Select [* | column1, column2]

From name_table [Where]

Видалення таблиць

Drop Table name_table [ Restrict | Cascade]

3. Уявлення

Більшості користувачів необхідно мати дані, що зберігаються в різних таблицях. Уявлення – це віртуальна таблиця, що може розглядатися як засіб отримання заздалегідь запланованого відображення і обробки даних, які зберігаються в таблицях бази даних.

Властивості уявлень:

• Уявлення не містять дані.
• Відповідний план зберігається у вигляді запиту на мові SQL.
• Такий запит може містити посилання на одну чи більше таблиць або інших уявлень, посилання на один чи більше стовпців у цих таблицях, критерії відбору даних.
• Крім того, запит може містити будь-які функції, в тому числі, підсумкові функції.

4. Створення уявлень

Створення уявлення забезпечується таким оператором:

CREATE VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]
AS select_statement

На рис.1 представлена демонстраційна БД під назвою Pubs.

Приклади.

1. Створення уявлення для даних з однієї таблиці:

USE Pubs

CREATE VIEW titles_view

AS 

SELECT title, type, price, pubdate

FROM titles

В цьому прикладі створюється уявлення з назвою titles_view в базі даних Pubs із таблиці titles. Ця таблиця містить 10 стовпців, а в уявленні їх буде лише чотири.

2. Створення уявлення для даних з однієї таблиці, використовуючи критерії відбору:

USE Pubs

CREATE VIEW accounts (title, advance, amt_due)

WITH ENCRYPTION

AS 

SELECT title, advance, price * royalty * ytd_sales

FROM titles

WHERE price > $5

3. Створення уявлення для даних з двох таблиць:

USE pubs

Create VIEW titles_view

SELECT     dbo.titles.title, dbo.titleauthor.au_ord, dbo.authors.au_lname, dbo.titles.price, dbo.titles.ytd_sales, dbo.titles.pub_id

FROM         dbo.authors INNER JOIN

dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN

dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id