Конспект лекций_Администрирование БД. Теоретические основы баз данных
 Скачать 0.98 Mb.
|
Глава 2. Язык SQL.1. История развития и стандарты.SQL (Structured Query Language, Структурированный язык запросов) – стандартный язык запросов по работе с реляционными БД. Прототип языка – сначала QBE, затем SEQUEL (Structured English Query Language) – был разработан в начале 70-х годов в IBM Research и реализован в СУБД System R. В дальнейшем этот язык применялся во многих коммерческих СУБД и в силу своего широкого распространения постепенно стал стандартом «де-факто» для языков манипулирования данными в реляционных СУБД. 1989 – первый ANSI/ISO стандарт языка SQL (вторая редакция, первая была в 1987 г.). Подавляющее большинство доступных на рынке СУБД поддерживают этот стандарт полностью. Однако развитие технологий БД и необходимость создания переносимых приложений потребовали его доработки и расширения. 1992 – стандарт SQL92 или SQL2. В настоящее время все возможности стандарта ни одна СУБД не поддерживает, обычно предлагая свои собственные расширения языка. 1999 – стандарт SQL3. Если отличия между предыдущими стандартами во многом были количественными, то в SQL3 введены новые типы данных, при этом предполагается возможность задания сложных структурированных типов, которые соответствуют объектно-ориентированной идеологии программирования. Также введены стандарты на события и триггеры, которые ранее не затрагивались в стандартах. Попытка следовать стандарту SQL3 хорошо прослеживается в последней версии СУБД Oracle. Стандарт языка баз данных – наиболее эффективный способ переноса как проекта БД, так и действующей СУБД на различные платформы. Как следствие этого – возможность более успешно конкурировать со своим программным продуктом на рынке СУБД. SQL не является традиционным языком программирования: он не содержит операторы, позволяющие осуществлять действия на низком уровне, и ориентирован на работу со множествами. Обычно реализация SQL в какой-либо СУБД является подмножеством собственного языка системы, включающего в себя ещё и средства построчного доступа к таблицам, средства управления ходом выполнения программы и т.п. Например, в случае MS SQL Server языком системы является Transact-SQL, добавляющий к базовому SQL, помимо вышеперечисленного, дополнительные инструкции и ключевые слова почти для каждого оператора SQL. 2. Наборы команд SQL.Подмножества команд SQL (перечислены не все): 1) DDL – Data Definition Language – язык определения данных.
2) DMP – Data Manipulation Language – язык манипулирования данными.
3) DQL – Data Query Language – язык запросов.
4) TCS – Transactional Control Statement – cредства управления транзакциями.
5) Средства администрирования данных.
3. Оператор SELECT.Весь запрос SELECT разбивается на отдельные разделы, каждый из которых имеет своё назначение. Большая часть этих разделов может быть опущена. Упрощённый вариант синтаксиса оператора SELECT: 1 SELECT [ALL | DISTINCT] <список вывода> 2 [ INTO <имя новой таблицы> ] 3 FROM <список таблиц и условий соединения> 4 [ WHERE <условие отбора или соединения> ] 5 [ GROUP BY <список полей группировки> ] 6 [ HAVING <условия, накладываемые на группу> ] 7 [ ORDER BY <список полей для сортировки вывода> ] 8 [UNION <запрос на выборку для объединения>] 9 … <список вывода>::= { * | [<имя таблицы> | <алиас>.] {<имя столбца> | <выражение>} [AS <алиас>] | <имя столбца> = <выражение> } […n] Символ звёздочка означает, что в результирующий набор включаются все столбцы из указанных исходных таблиц: SELECT * FROM publishers Декартово произведение отношений: SELECT * FROM publishers, authors ALL – в результирующий набор включаются все строки, удовлетворяющие условиям запроса, даже если среди них будут одинаковые (?!, то есть полученное отношение не удовлетворяет требованиям реляционной алгебры). SELECT ALL p.country FROM publishers AS p DISTINCT – в результирующий набор включаются только уникальные строки. Если в результат выборки включаются несколько столбцов, то уникальность будет определяться по значениям обоих этих столбцов. SELECT DISTINCT state, contract FROM authors Простейшие вычисления в разделе SELECT: SELECT 'Название книги: ', title, yearpub-1992 FROM titles WHERE yearpub > 1992; Раздел WHERE предназначен для наложения горизонтальных фильтров на данные, обрабатываемые запросом. Для этого указывается логическое условие, от результата вычисления которого зависит, будет ли строка включена в результат выборки или нет. SELECT au_lname, au_fname, state FROM authors WHERE state<>’CA’ Предикаты, используемые в условных конструкциях SQL: 1) Предикаты сравнения: =,<>,<,>,>=,<= ; SELECT * FROM authors WHERE 1=1 2) AND – соединение нескольких логических выражений; SELECT title FROM titles WHERE yearpub>=1995 AND yearpub<=1997 3) OR – если одно из двух условий истинно, то результат True; SELECT title FROM titles WHERE yearpub<1995 OR yearpub>1997 4) NOT – отрицание, может ставиться непосредственно перед нижеследующими предикатами; 5) Предикат диапазона: Between A and B – принимает значение True, если сравниваемое значение лежит между A и В; SELECT title FROM titles WHERE yearpub NOT BETWEEN 1995 AND 1997 6) Вхождение во множество: IN (<список значений>) – принимает True, если сравниваемое значение входит во множество заданных значений; SELECT title FROM titles WHERE yearpub IN (1995, 1996, 1997) 7) Сравнение с образцом: LIKE. В шаблон могут входить специальные символы «_» – для обозначения любого одиночного символа, и «%» – для обозначения произвольной последовательности символов; SELECT publisher, url FROM publishers WHERE publisher LIKE ‘%Wiley%’ 8) Предикат сравнения с неопределённым значением: IS NULL. SELECT publisher, “url not defined !” FROM publishers WHERE url IS NULL Связь между таблицами с использованием раздела WHERE (стандарт SQL89) Представим ситуацию, когда выборку данных надо производить из отношения, которое является результатом слияния нескольких отношений. При отсутствии соединения в разделе WHERE результат будет эквивалентен расширенному декартовому произведению отношений. Обычно всегда в случае использования нескольких таблиц имени поля предшествует имя таблицы во всех разделах оператора SELECT. SELECT titles.title, titles.yearpub, publishers.publisher FROM titles, publishers WHERE titles.pub_id = publishers.pub_id AND titles.yearpub>1996 В данном запросе в разделе WHERE указаны условия связи и условия фильтрации данных. Связь между таблицами с использованием раздела FROM (стандарт SQL2, внешние объединения) С помощью раздела FROM определяются источники данных, с которыми будет работать запрос. Связи между отношениями в этом разделе реализуются как одна или несколько вложенных связей между левой и правой таблицами по одному или нескольким полям. [INNER] JOIN. Данный тип связи используется по умолчанию. Строки левой таблицы, для которых не имеется пары в правой таблице, в результат выборки не включаются. Строки правой таблицы, для которых не имеется пары в левой таблице, также в результат не включаются. LEFT [OUTER] JOIN. Все строки левой таблицы включаются в результат выборки. При этом, если отсутствуют строки в правой таблице, то в соответствующих столбцах правой таблицы, включенных в результат запроса, будет установлено значение NULL. Строки правой таблицы, для которых не имеется пары в левой таблице, в результат не включаются. RIGHT [OUTER] JOIN. Все строки правой таблицы включаются в результат выборки. Для соответствующих столбцов левой таблицы, включенных в запрос, устанавливается значение NULL. Строки левой таблицы, для которых не имеется пары в левой таблице, в результат не включаются. FULL [OUTER] JOIN. В результат будут включены все строки как левой, так и правой таблицы. CROSS JOIN – выражение эквивалентно просто запятой между таблицами. Пример связи двух таблиц: SELECT authors.au_lname, authors.au_fname, titleauthor.royalty FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id WHERE authors.state=’CA’ Если бы мы хотели узреть и тех авторов из штата Калифорния, которые не получили гонорар, то надо было бы использовать конструкцию LEFT JOIN Пример связи нескольких таблиц: SELECT countries.name_rus AS страна, subjects.name_rus AS регион, msu.name_rus AS район, data.year AS год FROM data INNER JOIN subjects ON data.subject = subjects.subject INNER JOIN msu ON data.msu = msu.id_msu INNER JOIN countries ON subjects.country = countries.country Раздел_HAVING_.'>Раздел_GROUP_BY'>Раздел GROUP BY позволяет выполнять группировку строк таблиц по определённым критериям. Типичным примером использования GROUP BY является суммирование однотипных значений. GROUP BY почти всегда используется вместе с функциями агрегирования. GROUP BY разделяет таблицу на группы, а функция агрегирования вычисляет для каждой из них итоговое значение. Основные функции агрегирования:
Следующий запрос определяет количество книг каждого издательства, зарегистрированных в базе данных: SELECT publishers.publisher, COUNT(titles.title) FROM titles, publishers WHERE titles.pub_id = publishers.pub_id GROUP BY publisher Правила использования группировок в запросах: 1) Функции агрегирования не работают со значениями NULL. 2) В разделе SELECT (т.е. для вывода) можно указывать только те поля, по которым осуществляется группировка. Чтобы вывести значения столбцов, не указанных в критериях группировки, необходимо применять к ним функции агрегирования. 3) Раздел WHERE не допускает использования функций агрегирования. Ещё один пример: подсчитать, сколько записей ввёл каждый из операторов по каждому региону за 2003 год. SELECT max(subjects.name_rus) as [субъект], COUNT(*) AS [количество записей], max(users.u_name) as [оператор] FROM subjects, data, users WHERE data.subject = subjects.subject and data.id_user = users.id_user and data.year=2003 GROUP BY data.subject, data.id_user ORDER BY [субъект] Если при выполнении группировки используется раздел WHERE, то возможно появление групп, не содержащих ни одной строки. По умолчанию эти группы не включаются в результат выборки. Однако если необходимо вывести все без исключения группы, то используется вариант GROUP BY ALL. При этом для групп, не содержащих ни одной строки, не выполняются функции агрегирования, вместо которых выводится значение NULL. Простой запрос, использующий функцию агрегирования без группировки: SELECT Count(*) FROM data Раздел HAVING. Этот раздел практически аналогичен по назначению с разделом WHERE (горизонтальная фильтрация), однако используется для задания условий групповой фильтрации. В этом разделе допускается использование функций агрегирования. Определим количество книг каждого издательства, исключая случаи единственного экземпляра. SELECT publishers.publisher, COUNT(titles.title) FROM titles, publishers WHERE titles.pub_id = publishers.pub_id GROUP BY publisher HAVING COUNT(*)>1; Другой пример: получить номера деталей, суммарное количество которых на складе превышает 400 шт. SELECT number, SUM(volume) FROM warehouse GROUP BY number HAVING SUM(volume)>400 Раздел ORDER BY предназначен для упорядочения набора данных, возвращаемых после выполнения запроса. Используются ключевые слова ASC (по возрастанию, используется по умолчанию) и DESC (по убыванию). При этом в сортировке могут участвовать столбцы, не входящие в раздел SELECT. Приоритет в сортировке по столбцам, указанным первыми. SELECT data.* FROM data ORDER BY subject, msu, year Раздел UNION служит для объединения результатов выборки, возвращаемых двумя и более запросами. Это может быть выборка из одной таблицы или слияние данных из множества таблиц. Иными словами, раздел UNION вставляется между двумя запросами, возвращающими одинаковый набор столбцов. В результат будут включены строки как первого, так и второго запроса. По умолчанию дублирующие строки в результат не включаются. SELECT publisher, url FROM publishers UNION SELECT site, url FROM wwwsites Использование вложенных запросов. Команда SELECT позволяет использовать подзапросы в предикатах главного (т.е. в разделах WHERE и HAVING). Совместно с подзапросом можно использовать предикат EXIST, который возвращает истину, если вывод подзапроса не пуст. Задача: найти названия всех изданий, выпущенные издательством «Wiley» SELECT title FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers WHERE publisher='Wiley'); Более сложные задачи: даны отношения Suppliers (id_supplier, name) – поставщики (код поставщика, ФИО поставщика) Supply (id_supplier, number) – поставки (код поставщика, номер детали) Components (number, title) – детали (номер детали, наименование детали). 1. Найти имена поставщиков, которые поставляют все детали из занесённых в базу. SELECT MAX(suppliers.name) FROM suppliers, supply WHERE suppliers.id_supplier=supply.id_supplier GROUP BY supply.id_supplier HAVING COUNT( DISTINCT supply.number) = ( SELECT COUNT(number) FROM components) 2. Получить список поставщиков, поставляющих деталь с номером 222. SELECT * FROM suppliers WHERE EXIST (SELECT * FROM supply WHERE suppliers.id_supplier = supply.id_supplier AND supply.number = 222); Такие подзапросы называются коррелируемыми (correlated). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Простой пример: контроль ссылочной целостности вручную: SELECT * FROM data WHERE item NOT IN (SELECT item FROM items) Реализация реляционной алгебры средствами оператора SELECT (Реляционная полнота SQL) Для того, чтобы показать, что язык SQL является реляционно полным, нужно показать, что любой реляционный оператор может быть выражен средствами SQL.
4. Операторы определения данных (основные сведения).Создание, удаление и модификация таблиц. Создание таблицы: Синтаксис: CREATE TABLE <имя_таблицы> (<имя_столбца> {<тип_данных> | AS <выражение>} {[DEFAULT <значение>] | [IDENTITY [(начальное значение, инкремент)]]} {[NULL | NOT NULL] | [UNIQUE | PRIMARY KEY]} [REFERENCES <имя главной таблицы> [(<имя столбца>)]] , ...) Имя таблицы должно быть уникальным в пределах базы данных, а имена столбцов – уникальными в пределах таблицы. Наиболее часто используется повторяющаяся конструкция <имя столбца> <значение>: NULL указывает на то, в этом столбце возможны неопределённые значения (естественно, такой столбец не может быть ключевым). UNIQUE (ограничение целостности) указывает, что каждое значение в столбце является уникальным в пределах этого столбца. Для такого ограничения целостности автоматически создаётся индекс. PRIMARY KEY – создаётся первичный ключ на базе соответствующего столбца. Для таблицы может быть создано только одно такое ограничение целостности. DEFAULT – указывается значение по умолчанию для данного столбца. IDENTITY – создаётся столбец-счётчик. Только один столбец может быть счётчиком. REFERENCES – определяет, что столбец будет служить внешним ключом для таблицы, указанной с помощью параметра <имя главной таблицы>. Столбцы, входящие во внешний ключ, могут ссылаться только на столбцы первичного ключевого ограничения или ограничения UNIQUE. Дополнительно ограничение может быть уточнено ключевыми словами ON DELETE {CASCADE | NO ACTION} ON UPDATE {CASCADE | NO ACTION} Примеры создания таблиц (работа с которыми была рассмотрена выше): CREATE TABLE authors (au_id INT PRIMARY KEY, author CHAR(25) NOT NULL); CREATE TABLE publishers (pub_id INT PRIMARY KEY, publisher VARCHAR(255) NOT NULL, url VARCHAR(255) DEFAULT ‘неизвестен’); CREATE TABLE titles (title_id INT NOT NULL PRIMARY KEY, title CHAR(255) NOT NULL, yearpub INT, pub_id INT REFERENCES publishers(pub_id); Создание таблицы с вычисляемыми полями: CREATE TABLE MyTable (FirstCol int, SecondCol int, ThirdCol AS (FirstCol+SecondCol)/2.0) Удаление таблицы: Синтаксис: DROP TABLE <имя_таблицы> Модификация таблицы: Команда ALTER TABLE берёт на себя все действия по копированию данных во временную таблицу, удалению старой таблицы, созданию вместо неё новой таблицы с нужной структурой и последующим переписыванием в неё данных. Все эти действия происходят без участия пользователя, при этом установленные права доступа к таблице сохраняются. Добавление столбцов (синтаксис): ALTER TABLE <имя_таблицы> ADD (< возможное содержание аналогично содержимому в скобках для команды CREATETABLE> ,...) Удаление столбцов: ALTER TABLE <имя_таблицы> DROP (<имя столбца>, …) Модификация столбцов: ALTER TABLE <имя_таблицы> MODIFY (< возможное содержание аналогично содержимому в скобках для команды CREATETABLE> ,...) Пример: ALTER TABLE MyTable ADD DateCol datetime DEFAULT GETDATE() NOT NULL UNIQUE Создание, удаление и модификация индексов. Когда в базе данных хранится большое количество информации, объём которой начинает исчисляться десятками тысяч записей, обычные подходы к поиску нужных данных, такие как полное сканирование таблиц, становятся неприемлемыми. Время, которое пришлось бы затратить на анализ всей таблицы, оказывается слишком велико. Индекс представляет собой средство, помогающее ускорить поиск необходимых данных за счет физического или логического их упорядочения. Физически индекс представляет собой специальным образом упорядоченный набор значений из индексированного столбца с указателями на места физического размещения исходных записей в структуре базы данных. К настоящему времени разработаны эффективные математические алгоритмы поиска данных в упорядоченной последовательности. Одним из таких алгоритмов является метод половинного деления. В простейшем варианте для поиска любого значения берётся начальное приближение в середине упорядоченной последовательности значений, и хранимая в ней величина сравнивается с искомым значением. Если искомая величина меньше выбранного значения, то дальнейшему делению пополам подвергается первая половина списка, иначе – вторая. Поиск заканчивается, как только достигнуто совпадение искомой величины и анализируемого значения. Основным недостатком использования индексов является замедление процесса обновления (в том числе добавления и удаления) данных, так как для обеспечения целостности данных после ввода новых записей необходимо переиндексировать таблицу. Очень часто встает вопрос, какие поля необходимо индексировать. Обязательно надо строить индексы для первичных ключей, поскольку по их значениям осуществляется доступ к данным при операциях соединения двух и более таблиц. Создание индекса (синтаксис): CREATE [UNIQUE] INDEX <имя_индекса> ON <имя_таблицы> (<имя_столбца>,...) Пример: CREATE INDEX au_index ON authors (au_id); CREATE INDEX title_index ON titles (title_id); Удаление индекса: DROP INDEX <имя_индекса> 5. Операторы манипулирования данными.К этой группе относятся операторы добавления, изменения и удаления записей. INSERT – добавление информации к таблице Синтаксис: INSERT INTO <имя_таблицы> [ (<имя_столбца>,...) ] VALUES { 1. [ (<значение>,..) ] 2. |[ 3. |[ DEFAULT VALUES ] } Синтаксис строки 1 позволяет ввести только одну строку в таблицу. Если вводятся все поля записи, то список столбцов можно не задавать. Вместо значения поля можно указывать DEFAULT. Оператор ввода данных с помощью синтаксиса строки 2 позволяет ввести сразу множество строк, если их можно выбрать из некоторой другой таблицы. При этом порядок следования столбцов и тип данных в столбцах должных совпадать. Строка 3 используется, если создаётся запись с значениями по умолчанию. Примеры: INSERT INTO publishers VALUES (16,"Microsoft Press","http://www.microsoft.com"); INSERT INTO Authors (au_id, au_lname, au_fname) VALUES (666,’Бездомный’,’Иван’) INSERT INTO Addresses (name, phone, address) VALUES ( SELECT name, phone, address FROM tmp_table) С не меньшим успехом в последнем случае можно использовать и конструкцию SELECT INTO… UPDATE – обновление информации в таблице Синтаксис: UPDATE <имя_таблицы> SET <имя_столбца> = <значение>,... [FROM {<имя таблицы> | [WHERE <условие>] За один вызов UPDATE можно изменить данные в одном или нескольких столбцах для одной или множества записей только в одной таблице. С ключевого слова SET начинается блок, в котором определяется список изменяемых столбцов. Синтаксис <значение>:=={<константа> | <переменная> | <выражение> | DEFAULT | NULL}. Изменению подвергаются все строки, удовлетворяющие критериям ограничения области действия запроса UPDATE, которые задаются с помощью раздела WHERE. Примеры: UPDATE Authors SET address = “Садовая 13А, кв. 50” WHERE au_lname = ”Берлиоз” AND au_fname = ”Михаил” UPDATE publishers SET url="сайт неизвестен" WHERE url IS NULL Если в инструкции UPDATE будет пропущен раздел WHERE, то заданные в разделе SET изменения будут сделаны в каждой строке таблицы. Имеет смысл для проверки сначала выполнить инструкцию типа SELECT Count(*) с тем же критерием WHERE, чтобы узнать, сколько строк будет изменено с помощью UPDATE. При составлении выражения можно ссылаться на любые столбцы таблицы, включая изменяемые. UPDATE Titles SET price=price+10 Если при изменении данных в таблице необходимо учесть состояние данных в других таблицах, то они указываются в разделе FROM. После того как источник данных указан в разделе FROM, в разделах WHERE и SET можно ссылаться на столбцы этого источника данных. Пример: в таблице subjects (subject, fo, name) в поле fo указан номер федерального округа, к которому принадлежит данный субъект федерации. В частично заполненной таблице peoples (xsubject, xfo, value) осталось незаполненным поле xfo с тем же смыслом. Как восстановить недостающую информацию? UPDATE Peoples SET xfo = fo FROM subjects WHERE xsubject=subject DELETE – удаление информации из таблицы Синтаксис: DELETE FROM <имя_таблицы> [ WHERE <условие> ] С помощью DELETE можно удалить как отдельные строки, так и множество строк. Без WHERE будут удалены все строки таблицы. В разделе WHERE также можно использовать вложенные подзапросы. Примеры: DELETE FROM publishers WHERE publisher = "Microsoft Press" Удалить из таблицы students студентов, имеющих две и более двойки по результатам экзаменов (если это позволяют ограничения целостности): DELETE FROM students WHERE id_student IN ( SELECT id_student FROM testing WHERE result=2 GROUP BY id_student HAVING count(*)>2 ) Дополнительные сведения о языке SQL будут изложены в следующих главах, посвящённых различным диалектам SQL в нескольких широко известных на сегодняшний день СУБД. |