Язык запросов SQL. Введение. (Лекция 6, 7) презентация

Базы данных Язык запросов SQL. Введение

SQL – Structured Query Language SQL – это структурированный язык запросов к реляционным базам данных (БД). SQL – декларативный язык, основанный на операциях реляционной алгебры. Стандарты SQL, определённые Американским национальным институтом стандартов (ANSI): SQL-1 (SQL/89) – первый вариант стандарта. SQL-2 (SQL/92) – основной расширенный стандарт. SQL-3 (SQL/1999, SQL/2003) – относится к объектно-реляционной модели данных. Подмножества языка SQL: DDL (Data Definition Language) – команды создания/изменения/удаления объектов базы данных (create/alter/drop); DML (Data Manipulation Language) – команды добавления/модификации/удаления данных (insert/update/delete), а также команда извлечения данных select; DCL (Data Control Language) – команды управления данными (установка/снятие ограничений целостности). Входит в подмножество DDL.

Команды DDL CREATE – создание объекта. ALTER – изменения структуры объекта. DROP – удаление объекта. Общий вид синтаксиса команд DDL: create alter тип_объекта имя_объекта [параметры]; drop

Создание таблиц CREATE TABLE [имя_схемы.]имя_таблицы ( имя_поля тип_данных [(размер)] [NOT NULL] [DEFAULT выражение] [ограничения_целостности_поля…] .,.. [, ограничения_целостности_таблицы .,..] ) [ параметры ]; ограничения_целостности (ОЦ): [CONSTRAINT имя_ОЦ ] название_ОЦ [параметры]

Типы данных Символьные типы: CHAR [(длина)] – строка фиксированной длины. Длина по умолчанию – 1, максимальная длина 2000 б. Строка дописывается до указанной длины пробелами. VARCHAR2 (длина) – строка переменной длины. Максимальная длина 4000 б. Хранятся только значащие символы. Числовой тип: NUMBER [(точность[, масштаб])] – используется для представления чисел с заданной точностью. Точность по умолчанию 38, масштаб по умолчанию – 0. number(4) – числа от -999 до 9999 number(8,2) – числа от -99999.99 до 999999.99 DATE – дата и время с точностью до секунды. Занимает 7 байт. sysdate – функция получения текущих даты и времени. Тип date поддерживает арифметику дат: sysdate+1 – завтра (дата1 – дата2) – количество дней, прошедших между двумя датами (sysdate – 0.5) – 12 часов назад

Пример БД: проектная организация

Пример БД: проектная организация

Пример БД: проектная организация

Создание таблиц БД проектной организации Таблица «Отделы» (Depart): create table depart (did number(4) constraint pk_depart PRIMARY KEY, name varchar2(100) not null ); Таблица «Сотрудники» (Emp): create table emp ( tabno number(6) constraint pk_emp PRIMARY KEY, name varchar2(100) not null, born date not null, gender char not null, depno number(4) not null constraint fk_depart REFERENCES depart, post varchar(50) not null, salary number(8,2) not null constraint check_sal check (salary > 4630), passport char(10) not null constraint passp_uniq UNIQUE, pass_date date not null, pass_get varchar2(100) not null, born_seat varchar2(100), edu varchar2(30), special varchar2(100), diplom varchar2(40), phone varchar2(30), adr varchar2(80), edate date not null default trunc(sysdate), chief number(6) constraint fk_emp REFERENCES emp );

Создание таблиц БД проектной организации Таблица «Проекты» (Project): create table project (No number(5) constraint pk_project primary key, title varchar2(200) not null, pro varchar(15) not null constraint pro_uniq unique, client varchar(100) not null, dbegin date not null, dend date not null, cost number(9) ); Таблица «Участие в проектах» (Job): create table job ( pro varchar(15) not null references project (abbr), tabNo number(6) not null references emp, rel varchar(20) default 'исполнитель', primary key (tabno, pro), check ( rel IN ('исполнитель', 'руководитель', 'консультант') ) );

Подмножество команд DML INSERT – добавление строк в таблицу. Добавляет одну или несколько строк в указанную таблицу. UPDATE – изменение данных. Изменяет значения одного или нескольких полей в записях указанной таблицы. Можно указать условие, по которому выбираются обновляемые строки. Если условие не указано, обновляются все строки таблицы. Если ни одна строка не удовлетворяет условию, ни одна строка не будет обновлена. DELETE – удаление строк из таблицы. Удаляет одну или несколько строк из таблицы. Можно указать условие, по которому выбираются удаляемые строки. Если условие не указано, удаляются все строки таблицы. Если ни одна строка не удовлетворяет условию, ни одна строка не будет удалена.

Добавление данных INSERT – добавление строк в таблицу: 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'); Замечание: значение по умолчанию используется только тогда, когда значение поля не вводится в явном виде.

Изменение данных UPDATE – изменение данных: 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;

Удаление данных DELETE – удаление строк из таблицы: DELETE FROM имя_таблицы [ WHERE условие ]; Примеры. -- Удалить сведения о том, что сотрудник Афонасьев В.Н., табельный номер 147, участвует в проектах: delete from job where tabno=147; -- Удалить сведения о сотруднике Афонасьеве В.Н., табельный номер 147: delete from emp where tabno = 147; Замечание: отменить удаление данных можно командой ROLLBACK;

Язык запросов SQL. Команда SELECT

Команда SELECT – выборка данных Общий синтаксис: SELECT [{ ALL | DISTINCT }] { список_вывода | * } FROM имя_таблицы1 [ алиас1 ] [, имя_таблицы2 [ алиас2 ].,..] [ WHERE условие_отбора_записей ] [ GROUP BY { имя_поля | выражение }.,.. ] [ HAVING условие_отбора_групп ] [ UNION [ALL] SELECT …] [ ORDER BY имя_поля1 | целое [ ASC | DESC ] [, имя_поля2 | целое [ ASC | DESC ].,..]]; Примеры: select * from departs; select name, post from emp;

Формирование списка вывода (проекция)

Формирование списка вывода (проекция)

Упорядочение результата

Выбор данных из таблицы (селекция)

Логические операторы

Выбор данных из таблицы по условию

Выбор данных из таблицы по условию

Предикаты формирования условия

Предикаты формирования условия

Предикаты формирования условия

Предикаты формирования условия

Использование предикатов

Базы данных Язык запросов SQL. Команда SELECT (продолжение)

Агрегирующие функции

Примеры использования функции COUNT

Примеры использования агрегирующих функций

Группировка данных: предложение GROUP BY

Примеры использования GROUP BY

Использование GROUP BY

Группировка по нескольким полям

Использование фразы HAVING

Операции реляционной алгебры

Общий алгоритм выполнения операции SELECT Выбор записей из указанной таблицы (from). Проверка для каждой записи условия отбора (where). Группировка полученных в результате отбора записей (group by) и вычисление для этих групп значений агрегирующих функций. Выбор тех групп, которые удовлетворяют условию отбора групп (having). Сортировка полученных записей в указанном порядке (order by). Извлечение из полученных записей тех полей, которые заданы в списке вывода, и формирование результирующего отношения. Если в части FROM указывается 2 и более таблицы, то приведенный алгоритм выполняется для декартова произведения этих таблиц.

Базы данных Язык запросов SQL. Команда SELECT. Дополнительные возможности

Самосоединение

Результат самосоединения

Подзапросы

Пример БД: проектная организация

Данные таблицы Emp (сотрудники)

Расположение подзапросов в командах DML

Расположение подзапросов в команде select

Примеры использования подзапросов в части WHERE

Расположение подзапросов в команде select

Расположение подзапросов в команде select

Представления

Представления: пример

Представления: пример

Представления: примеры

Обновляемые представления

Обновляемые представления

Оператор CASE

Примеры использования оператора CASE

Примеры использования оператора CASE