бд
.pdf29. Состав работ, выполняемых на стадии физического проектирования БД.
Физическое проектирование — создание схемы базы данных для конкретной СУБД. Специфика конкретной СУБД может включать в себя ограничения на именование объектов базы данных, ограничения на поддерживаемые типы данных и т. п. Кроме того, специфика конкретной СУБД при физическом проектировании включает выбор решений, связанных с физической средой хранения данных (выбор методов управления дисковой памятью, разделение БД по файлам и устройствам, методов доступа к данным), создание индексов и т. д.
1.
Проектирование таблиц базы данных средствами выбранной СУБД.
2.
Реализация бизнес-правил в среде выбранной СУБД.
3.
Проектирование физической организации базы данных.
4.
Разработка стратегии защиты базы данных. 5.
Организация мониторинга функционирования базы данных и ее настройка.
30. Ограничения целостности:понятие и классификация.
Целостность данных - это механизм поддержания соответствия базы данных предметной области. В реляционной модели данных определены два базовых требования обеспечения целостности:
●целостность ссылок
●целостность сущностей.
Втеории реляционных баз данных принято выделять четыре типа ограничений целостности:
●Ограничением базы данных называется ограничение на значения, которые разрешено принимать указанной базе данных.
●Ограничением переменной отношения называется ограничение на значения, которые разрешено принимать указанной переменной отношения.
●Ограничением атрибута называется ограничение на значения, которые разрешено принимать указанному атрибуту.
●Ограничение типа представляет собой не что иное, как определение множества значений, из которых состоит данный тип.
Примером распространённого ограничения уровня переменной отношения является потенциальный ключ; примером распространённого ограничения уровня базы данных является внешний ключ.
34. Язык SQL. Общая характеристика, стандарты, подмножества языка SQL.
SQL (Structured Query Language — Структурированный язык запросов) — язык управления базами данных для реляционных баз данных. Сам по себе SQL не является Тьюринг-полным языком программирования, но его стандарт позволяет создавать для него процедурные расширения, которые расширяют его функциональность до полноценного языка программирования.
Следует отметить, что к достоинствам языка SQL относится наличие международных стандартов. Первый международный стандарт был принят в 1989 г., и соответствующая версия языка называется SQL-89. Этот стандарт полностью поддерживается практически во всех современных коммерческих реляционных СУБД (например, в Informix, Sybase, Ingres, DB2 и т.д.).
Стандарт SQL-89 во многих частях имеет чрезвычайно общий характер и допускает очень широкое толкование. В этом стандарте полностью отсутствуют такие важные разделы, как манипулирование схемой БД и динамический SQL. Многие важные аспекты языка в соответствии со стандартом определяются в реализации.
ВSQL определены два подмножества языка:
●SQL-DDL (Data Definition Language) - язык определения структур и ограничений целостности баз данных. Сюда относятся команды создания и удаления баз данных; создания, изменения и удаления таблиц; управления пользователями и т.д.
●SQL-DML (Data Manipulation Language) - язык манипулирования данными:
добавление, изменение, удаление и извлечение данных, управления транзакциями
35. Основные типы данных (на примере конкретной(ых)СУБД).
Типы данных
В MySQL, как и в других средах разработки, различают несколько типов данных. Изучая СУБД, в первую очередь необходимо изучить все типы данных. В общем, они идентичны во всех СУБД, но у каждой есть свои особенности.
1)INT - Целочисленный формат. В основном используется для идентификаторов и переключателей (0 - нет, 1 - есть).
2)CHAR – Текстовый формат. Ограничен 32 тыс. символов. Используется для хранения небольшого объема текстовой информации. Пример CHAR(128).
3)TEXT - Текстовый формат. Практически неограничен. Используется для хранения большого объема текстовой информации.
4)DATE – Дата.
5)DATETIME – Дата и время.
6)DECIMAL – Числовой формат с разделителем. Пример DECIMAL(14, 2)
позволит нам записать число, длинной 14 символов с двумя знаками после запятой.
На самом деле, все типы данных я описывать не стал, этого набора вполне достаточно для реализации практически любого приложения.
36. Какие команды относятся к категории DDL?Опишите общий вид синтаксиса команд DDL, приведите пример(ы) каждой команды.
Команды языка определения данных DDL (Data Definition Language, язык определения данных) — это подмножество SQL, используемое для определения и модификации различных структур данных.
К данной группе относятся команды предназначенные для создания, изменения и удаления различных объектов базы данных. Команды CREATE (создание), ALTER (модификация) и DROP (удаление) имеют большинство типов объектов баз данных (таблиц, представлений, процедур, триггеров, табличных областей, пользователей и др.). Т.е. существует множество команд DDL, например,
CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE USER, CREATE ROLE и т.д.
Некоторым кажется, что применение DDL является прерогативой администраторов базы данных, а операторы DML должны писать разработчики, но эти два языка не так-то просто разделить. Сложно организовать аффективный доступ к данным и их обработку, не понимая, какие структуры доступны и как они связаны. Также сложно проектировать соответствующие структуры, не зная, как они будут обрабатываться.
37. Задание ограничений целостности на языке SQL.Примеры.
Ограничения целостности
ВСУБД MySQL поддерживаются следующие ограничения целостности:
●Уникальность (значений атрибута или комбинации значений атрибутов):
UNIQUE (имя_атрибута1 [, имя_атрибута2,...])
●Обязательность / необязательность:
NOT NULL / NULL
●Первичный ключ:
PRIMARY KEY(имя_атрибута1 [, имя_атрибута2,...])
●Внешний ключ:
FOREIGN KEY(имя_атрибута1 [, имя_атрибута2,...]) REFERENCES
имя_таблицы [(имя_атрибута1 [, имя_атрибута2,...])]
●Условие на значение поля:
CHECK (условие)
Пример
CHECK (salary>=4500), check (date2 > date1)
38. Какие команды относятся к категории DML? Опишите способы добавления строк в таблицу (общий вид синтаксиса команд добавления строк в таблицу, примеры).
Подмножество команд DML
INSERT– добавление строк в таблицу.
Добавляет одну или несколько строк в указанную таблицу.
UPDATE– изменение данных.
Изменяет значения одного или нескольких полей в записях указанной таблицы. Можно указать условие, по которому выбираются обновляемые строки.
Если условие не указано, обновляются все строки таблицы.
Если ни одна строка не удовлетворяет условию, ни одна строка не будет обновлена.
DELETE– удаление строк из таблицы. Удаляет одну или несколько строк из таблицы.
Можно указать условие, по которому выбираются удаляемые строки. Если условие не указано, удаляются все строки таблицы.
Если ни одна строка не удовлетворяет условию, ни одна строка не будет удалена.
Добавление строк в таблицу
INSERT INTO имя_таблицы [(список_полей_таблицы)] { VALUES (список_выражений) | запрос };
Примеры
Добавить в таблицу "Отделы" новую запись (все поля):
INSERT INTO depart VALUES(7, 'Договорной отдел');
Добавить в таблицу "Сотрудники" новую запись (не все поля):
INSERT INTO emp (tabno, name, born, gender, depno, passport, pass_date_pass_get, post, salary, phone)
VALUES( 301, 'САВИН АНДРЕЙ ПАВЛОВИЧ', to_date('11.07.1969', 'dd.mm.yyyy'), 'М', 5, '4405092876', to_date('15.02.1999', 'dd.mm.yyyy'), 'ОВД "Митино" г.Москвы', 'программист', 38050, '121-34-11');
Замечание: значение по умолчанию используется только тогда, когда значение поля не вводится в явном виде.
39. Команда изменения данных таблицы: общий вид синтаксиса, примеры.
Изменение данных таблицы
UPDATE имя_таблицы SET имя_поля1 = выражение1 [, имя_поля2 = выражение2,…] [WHERE условие];
Примеры
Изменить статус сотрудника Бобкова Л.П., табельный номер 74, по отношению к проекту 30. "Система автоматизированного управления предприятием":
UPDATE job SET rel = 'консультант' WHERE tabno = 74 and pro = 30;
Перевести сотрудника Жаринова А.В., табельный номер 68, на должность ведущего программиста и повысить оклад на три тысячи рублей:
UPDATE emp SET post = 'ведущий программист', salary = salary+3000 WHERE tabno = 68;
40. Команда удаления строк из таблицы: общий вид синтаксиса, примеры.
Оперотор DELETE. Синтаксис:
DELETE FROM <имя таблицы > [WHERE <предикат>];
Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым). Более быстро эту операцию (удаление всех строк из таблицы) можно в Transact-SQL также выполнить с помощью команды
TRUNCATE TABLE <имя таблицы>
Однако есть ряд особенностей в реализации команды TRUNCATE TABLE, которые следует иметь в виду:
•не журнализируется удаление отдельных строк таблицы; в журнал записывается только освобождение страниц, которые были заняты данными таблицы;
•не отрабатывают триггеры, в частности, триггер на удаление;
•команда неприменима, если на данную таблицу имеется ссылка по внешнему ключу, и даже если внешний ключ имеет опцию каскадного удаления.
•значение счетчика (IDENTITY) сбрасывается в начальное значение.
Примеры:
1. Требуется удалить из таблицы Laptop все портативные компьютеры с размером экрана менее 12 дюймов.
DELETE FROM Laptop
WHERE screen < 12;
2. Пусть требуется удалить те модели ПК из таблицы Product, для которых нет соответствующих строк в таблице PC.
DELETE FROM Product WHERE type = 'pc' AND
model NOT IN (SELECT model FROM PC
);
41. Команда изменения структуры таблицы: общий вид синтаксиса, примеры.
Команда ALTER TABLE.
С ее помощью можно:
•добавить в таблицу определение нового столбца;
•удалить столбец из таблицы;
•изменить значение по умолчанию для какого-либо столбца;
•добавить или удалить первичный ключ таблицы;
•добавить или удалить внешний ключ таблицы;
•добавить или удалить условие уникальности;
•добавить или удалить условие на значение. Общий синтаксис команды ALTER TABLE: ALTER TABLE<имя_таблицы>
[ALTER COLUMN <имя_столбца> [SET DEFAULT <выражение>] | [DROP DEFAULT] ] |[ADD <определение_столбца>]
|[DROP COLUMN <имя_столбца> [CASCADE] | [RESRTICT]]
|[ADD [<определение_первичного_ключа>] | [<определение_внешнего_ключа>] | [<условие_уникальности>] | [<условие_на_значение>]]
|[DROP CONSTRAINT <имя_ограничения> [CASCATE] | [RESTRICT]]
Команда ALTER TABLE берет на себя все действия по копированию данных во временную таблицу, удалению старой таблицы и созданию вместо нее новой таблицы с нужной структурой и последующим переписыванием в нее данных.
Примеры:
1. Модификация структуры таблицы
ALTER TABLE t1 MODIFY (pole1 NOT NULL); CREATE TABLE t2
(pole1 CHAR(10) PRIMARY KEY); ALTER TABLE t1 ADD
(CONSTRAINT fk_t1 FOREIGN KEY (pole1) REFERENCES t2 (pole1));
ALTER TABLE t1 ADD (UNIQUE (p_name));
ALTER TABLE t1 ADD (p_size CHAR(4) CHECK
(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));
2.Добавление столбца в таблицу: ALTER TABLE t1(pole1 char(10));
3.Изменение размера столбца таблицы: ALTER TABLE t1 MODIFY (pole1 char(20));
4.Удаление столбца таблицы:
ALTER TABLE t1 DROP COLUMN pole1;
42. Общий синтаксис и алгоритм выполнения команды
Select языка SQL.
Синтаксис:
SELECT [ALL | DISTINCT] <список_выбора> FROM <имя_таблицы>, ...
[ WHERE <условие> ]
[GROUP BY <имя_столбца>,... ] [ HAVING <условие> ]
[ORDER BY <имя_столбца> [ASC | DESC],... ]
Основные ключевые слова и параметры команды SELECT в MySQL (по ним алгоритм работы)
•DISTINCT — возвращает только одно значение для каждого набора одинаковых выбранных значений столбца
•ALL — возвращает все выбранные строки, включая все повторяющиеся значения столбцов (принимается по умолчанию)
•* — выбирает все столбцы из всех таблиц или представлений, перечисленных после оператора FROM
•schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя
•table.* view.* — выбирает все столбцы из указанной таблицы, представления
•Expr — извлекает из таблицы (представления) некоторое определяемое выражение
•table view — имя таблицы(представления), из которой происходит выборка данных.
•subquery — подзапрос, который сервер обрабатывает тем же самым способом как представление.
•WHERE — ограничивает множество строк выборкой тех записей, для которых условие является истинным; если это предложение опускается, сервер возвращает все строки из таблиц.
•GROUP BY — группирует выбранные строки по группам строк с одинаковым значением указанных полей и возвращает одиночную строку итоговой информации для каждой группы.
•HAVING — ограничивает выбираемые группы строк такими группами, для которых определяемое условие является истинным; если это предложение опускается, сервер возвращает строки всех групп.
•UNION UNION ALL INTERSECT MINUS — объединяет строки, возвращенные двумя утверждениями SELECT с использованием операции пересечения множеств; для ссылки на столбец вводится псевдоним для его обозначения; предложение FOR UPDATE не может использоваться с этими операторами
•ORDER BY — упорядочивает строки, возвращенные запросом.
•Expr— значение выражения определяет правило упорядочивания строк.
•ASC DESC — определяет порядок вывода данных (по возрастанию или по убыванию); значением по умолчанию является ASC.
•FOR UPDATE — блокирует выбранные строки.
•OF — блокирует выбираемые строки для специфической таблицы в объединении.
•NOWAIT — возвращает управление пользователю, если команда SELECT пытается блокировать строку, которая уже блокирована другим пользователем; если это предложение опускается, сервер ждет, пока строка не станет доступной и только тогда возвращает результаты команды SELECT.
43. Формирование списка вывода в команде Select: общий синтаксис, примеры. Использование псевдонимов в SQL. Упорядочение результата в ответе. Привести примеры.
Общий синтаксис списка вывода:
[{all| distinct}] { * | выражение1[алиас1][, выражение2[алиас2] .,..]}
Список ввода находится между ключевыми словами SELECT иFROM.
1.Вывести все поля всех записей из таблицы Проекты (Project): select *from project;
2.Вывести список сотрудников с указанием их должности и No отдела: select depno, name, post from emp;
3.Вывести список сотрудников с указанием их должности и зарплаты:
select name 'ФИО', post 'Должность', salary*0.87 'Зарплата'from emp;
4.Установить другой формат вывода даты: alter sessionsetnls_date_format = 'dd/mm/yyyy';
5.Вывести должности и оклады сотрудников: select post, salary from emp;
6.Вывести должности и оклады сотрудников без повторов: select DISTINCT post, salary from emp;
7.Вывести отделы и должности сотрудников без повторов: select DISTINCT depno, postfrom emp;
8.Вывести список сотрудников с указанием ФИО, даты рождения и адреса. select name 'ФИО', born 'Дата рождения', adr 'Адрес'
from emp;
SQL Alias - псевдонимы могут быть использоваться для переименования таблиц и колонок.
Существует возможность задавать таблицам или столбцам другие имена, используя для этого псевдоним. Это может быть полезным, если у нас очень длинные или сложные имена таблиц или столбцов.
Псевдоним может быть каким угодно, но обычно это короткие имена. Синтаксис для таблиц:
SELECT column_name(s) FROM table_name
AS alias_name
Синтаксис для столбцов:
SELECT column_name AS alias_name FROM table_name
Пример:
Предположим, мы имеем одну таблицу под названием "Persons", а другую таблицу под названием "Product_Orders". Первой мы присвоим псевдоним "p" второй "po" соответственно.
Теперь мы хотим получить список всех заказов, которые имеет "Ola Hansen". Для этого используем такой запрос:
SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p,
Product_Orders AS po WHERE p.LastName='Hansen' WHERE p.FirstName='Ola'
Тот же самый запрос без использования псевдонимов.
SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons,
Product_Orders
WHERE Persons.LastName='Hansen' WHERE Persons.FirstName='Ola'
Для сортировки результата по определенному столбцу, в SQL используется оператор ORDER BY. Для каждого столбца можно задать сортировку по возрастанию – ascending (ASC) (этот параметр используется по умолчанию) или по убыванию – descending (DESC).
Пример:
Отсортируем результат по столбцу sname. После оператора ORDER BY указываем по какому столбцу сортировать, затем необходимо указать способ сортировки.
SELECT * FROM Salespeople ORDER BY sname ASC
Сортировка по убыванию:
SELECT * FROM Salespeople ORDER BY sname DESC
Сортировка по нескольким столбцам:
SELECT snum, sname, city FROM Salespeople ORDER BY sname DESC, city DESC