4.1 Tаблицы являются основными объектами базы данных (БД), предназначенными
для хранения информации. Таблицы БД (на языке реляционных моделей - отношения)
включают набор столбцов (атрибутов) и состоят из строк (кортежей).
Значения данных в каждом столбце могут быть только одного определенного типа
(принадлежат одному домену). Таблицы и столбцы имеют имена, строки имен не
имеют (если не считать уникальных имен (oid - Object IDentifier),
автоматически присваиваемых системой каждому новому кортежу).
Модель безопасности, реализованная в СУБД Postgres, связывает с каждой
таблицей владельца-создателя таблицы (owner) и остальных пользователей
(database user). Владелец может выполнять в отношении таблицы любые действия -
создание, модификацию, пополнение данными и уничтожение, в то время как
действия остальных пользователей регламентированы набором "прав" выполнять
те или иные операции (privileges). Права-привилегии предоставляются (grant)
владельцем таблицы, о чем пойдет речь в дальнейшем.
4.2 Для создания таблицы необходимо определить ее структуру, т.е. имена и типы
данных набора столбцов. Создать таблицу может любой пользователь,
зарегистрированный в БД, однако доступ ко всем этим таблицам строго разграничен
признаками владения и привилегий. Тем не менее, при работе в одной базе
большого количества пользователей (над разными проектами) они могут испытывать
определенные неудобства, связанные хотя бы с тем, что некоторые команды
интерактивного монитора psql (например \dt ), будут давать список всех таблиц
БД, а не только таблиц, относящихся к данному проекту. Поэтому рациональный
выбор имен таблиц и столбцов представляется немаловажным условием успешной
работы. Целесообразно использовать имена, составленные из нескольких частей,
соединенных "подчерком" ( _ ), для таблиц:
Имя-или-абревиатура-имени-пользователя_имя-таблицы
для стобцов:
Абревиатура-имени-таблицы_имя-столбца
Например, пользователь Sidorov, таблица Students, столбцы Fio и Address,
выбираем имена для таблицы - SDR_STUDENTS,
для столбцов - STD_FIO, STD_ADDRESS.
Следует учесть чужой опыт, утверждающий, что "копеечная" экономия
на времени ввода"длинного" имени чревата гораздо большими потерями его
при отладке нетехнологично написанных текстов.
В простейшем случае оператор создания таблицы имеет следующий синтаксис
CREATE TABLE <имя-таблицы> ( <имя-столбца> <тип-данного>
[,<имя-столбца> <тип-данного>] ...
);
Например,
CREATE TABLE sdr_students
( std_fio INTEGER, std_address TEXT );
CУБД Postgres поддерживает большое количество типов данных, наиболее
употребительными из которых являются:
-целые различной длины - INTEGER, INT8, INT, INT4, INT2,
-числовые с плавающей точкой - REAL,
-десятичные с выбираемой точностью - DECIMAL (p,s),
-логические "истина"/"ложь" - BOOLEAN, BOOL,
-строки переменной длины - TEXT, VARCHAR (n), CHAR (n),
-даты в формате yy/mm/dd - DATE .
В определения столбцов таблицы могут быть включены различные ограничения на
допустимые значения в этих столбцах. Простейшими являются ограничения
NULL | NOT NULL - по умолчанию устанавливается "пустое" значение или
"пустое" значение недопустимо,
UNIQUE - значения в столбце не должны повторяться,
DEFAULT <выражение> - присваиваемое значение по умолчанию.
Упражнение - создать таблицу "Дни рождения" с полями - фамилия, имя и день
рождения.
Таблица может быть создана также на основании запроса к другой таблице
CREATE TABLE <имя-таблицы> [(список-столбцов)] AS <оператор-запроса>;
4.3 Простейшим способом заполнения таблицы данными является использование
оператора вставки INSERT с синтаксисом:
INSERT INTO <имя-таблицы> VALUES( <выражение1>, <выражение2> ...);
при этом количество выражений должно соответвовать числу столбцов, а значения
выражений должны соответствовать типам данных в столбцах.
Продолжение упражнения - заполнить таблицу "Дни рождения" данными на трех
своих знакомых.
Указание: Даты следует вводить в апострофах ' ', поэкспериментируйте с вводом
года в полной и сокращенной (две последние цифры) форме. Проверьте результаты
ввода оператором SELECT.
Изменение существующих значений атрибутов в строках выполняет оператор UPDATE
со следующим синтаксисом:
UPDATE <имя-таблицы> SET <имя-столбца>=<выражение>[, ...]
[ WHERE < условие> ];
при этом будут изменены значения только в перечисленных столбцах для строк,
удовлетворяющих задаваемому условию. Если фраза WHERE отсутствует, обновлены
будут значения во всех строках, например, оператор
UPDATE stock SET cost = cost + cost*15/100,
retail = retail + retail*20/100;
увеличивает цену книг на складе на 15%, а розничную цену - на 20%.
4.4 Удаление строк таблицы производится с помощью оператора DELETE, при этом
оператор
DELETE FROM <имя-таблицы>;
удаляет все строки из таблицы, сохраняя ее структуру, а оператор
DELETE FROM <имя-таблицы> WHERE <условие>;
удаляет только те кортежи, которые удовлетворяют заданному условию.
Удаление всей таблицы целиком производится опреатором
DROP TABLE <имя-таблицы>;
излишне напоминать, что операции вставки, обновления, удаления кортежей и
таблицы целиком может выполнять только владелец таблицы или пользователь,
которому владелец передал часть привилегий.
4.5 Структуру созданной таблицы можно модифицировать, добавив в нее
новые столбцы или некоторые условия относительно существующих столбцов.
Это может быть выполнено с помощью оператора ALTER TABLE, имеющего следующий
синтаксис:
ALTER TABLE <имя-таблицы> АDD [COLUMN]
<имя-столбца> <тип-значения>;
ALTER TABLE <имя-таблицы> ALTER [COLUMN]
<имя-столбца> SET DEFAULT <значение>;
ALTER TABLE <имя-таблицы> RENAME [COLUMN]
<имя-столбца> TO <новое-имя>
ALTER TABLE <имя-таблицы> RENAME TO <новое-имя-таблицы>
Дополнительную информацию по рассмотренным командам можно получить с
помощью слэш-команд монитора psql:
\h <имя-SQL-команды>
Задания
Выполняя задание, необходимо в рабочей тетради предварительно записать
соответствующие операторы SQL, а также вид создаваемых и модифицируемых
таблиц.
1. Просмотреть структуру таблиц authors, books, editions из БД booktown,
выписать названия столбцов и типы их данных.
2. Переключиться в БД с номером Вашей группы
(команда монитора: \c <новая-база>)
и создать в этой БД таблицу для хранения данных о людях, характеризуемых
фамилиями, именами, возрастом (число прожитых лет), весом (в кг)
и ростом (в см).
3. Внести в созданную таблицу данные о шести произвольных людях в возрасте от
16 до 50 лет, имеющих вес от 40,5 до 99,5 кг и рост 150 - 195 см.
4. Создать вторую таблицу, включив в нее из первой таблицы данные о
людях старше 20 лет и выше 180 см.
5. Создать третью таблицу - копию первой таблицы, но не содержащую столбца с
именами людей.
6. Преобразовать третью таблицу таким образом, чтобы она содержала данные о
росте в дюймах, а весе - в фунтах (1 фунт = 400г, 1 дюйм = 25,4 мм).
7. Из третьей таблицы удалить строки, содержащие сведения о людях моложе
20 лет.
8. Модифицировать третью таблицу таким образом, чтобы значением столбца
фамилий по умолчанию была фамилия 'Синебрюхов'.
9. Добавить в третью таблицу 2 строки, содержащие только данные о возрасте
25 и 30 лет (остальные поля должны получить значения по умолчанию).
10. Получить точную копию третьей таблицы под другим именем.
11. Продемонстрировав все созданные таблицы преподавателю, уничтожить все
таблицы, кроме первой.
| < Предыдущая | Следующая > |
|---|
