Лекция 12. Введение в SQL
Цель занятия: усвоить знания о назначении языка SQL; об основных командах языка.
План
- Назначение языка, классификация операторов.
- Структура команды языка.
- Работа SQL со множеством пользователей.
Назначение языка, классификация операторов
Большинство современных СУБД содержат средства генерации запросов, позволяющих изменять данные (и др. операции). Один из способов манипулирования данными – QBE (queries by example), запрос по образцу. Это средство для визуального связывания таблиц и выбора полей, которые надо отобразить в результате выполнения запроса.
Другое средство – язык запросов SQL (Structured Query Language), представляет собой непроцедурный язык, используемый для управления данными и метаданными реляционных СУБД. Термин «непроцедурный» означает, что на этом языке можно сформулировать, что именно нужно сделать с данными, но нельзя проинструктировать, как это следует сделать. Иными словами, в языке SQL отсутствуют алгоритмические конструкции, такие кап метки, операторы цикла, условные переходы и т.п.
Язык SQL был создан в начале 70-х годов в результате исследовательского проекта IBM, целью которого была разработав языка манипуляции реляционными данными. Первоначально он назывался SEQUEL (Structured English Query Language), затем — просто SQL. Официальный стандарт SQL был опубликован ANSI (American National Standards Institute - Американский национальный институт стандартизации) в 1981 году, и это наиболее часто используемая ныне реализация SQL. Данный стандарт был расширен в 1989 и 1992 годах, поэтому последний стандарт SQL носит название SQL92. В настоящее время ведется работа над стандартом SQL3, содержащим некоторые объектно-ориентированные расширения.
Существует три уровня соответствия стандарту ANSI: начальный, промежуточный и полный. Многие производители серверных СУБД такие как IBM, Informix, Microsoft, Oracle и Sybase, применяют собственные реализации SQL, основанный на стандарте ANSI (отвечающие как минимум начальному уровню соответствия стандарту) и содержащие некоторые расширения, специфические для той или иной СУБД.
Классификация операторов языка
SQL позволяет не только извлекать данные, но и определять структуру данных, добавлять, удалять данные, ограничивать или предоставлять доступ к данным, поддерживать ссылочную целостность.
SQL содержит примерно 40 операторов для выполнения различных действий внутри СУБД большинство из которых представляют собой разного рода манипуляции с данными и метаданными. Эти операторы подразделяются на несколько категорий, которые кратко рассматриваются ниже.
Data Definition Language (DDL) содержит операторы, позволяющие создавать, изменять и уничтожать базы данных и объекты внутри них (таблицы, представления и др)
Create Table - Добавление новой таблицы к базе
Drop Table - Удаление таблицы из базы данных
Alter Table - Изменение структуры имеющейся таблицы
Create View - Добавление нового представления к базе данных
Drop View - Удаление представления из базы данных
Create Index - Создание индекса для данного поля
Drop Index - Удаление существующего индекса
CREATE SCHEMA - Создание новой схемы в базе данных
Data Manipulation Language (DML) содержит операторы, позволяющие выбирать, добавлять, удалять и модифицировать данные. Обратите внимание на то, что эти операторы не обязательно должны завершать транзакцию, внутри которой or. вызваны.
Insert - Добавление строк к таблице
Delete - Удаление строк из таблицы
Update - Изменение данных
Иногда оператор SELECT относят к отдельной категории, называемой Data Query Language (DQL).J
Data Control Language (DCL), иногда называемые операторами Access Control Language, применяются для осуществления административных функций, присваивающих или отменяющих право (привилегию) использовать базу данных, таблицы в базе данных, а также выполнять те или иные операторы SQL.
Grant - Присвоение привилегии.
Revoke - Отмена привилегии.
Транзакция – группа операций над данными, которые либо выполняются, либо отменяются все вместе. Завершение транзакции означает, что все ее операции завершены, и результат их работы сохранен в базе данных. Откат транзакции означает, что все уже выполненные операции, входящие в состав транзакции, отменяются, и все объекты базы данных возвращаются в исходное состояние (для этого СУБД ведет журнал транзакций). Транзакция может состоять из нескольких вложенных транзакций. Некоторые СУБД поддерживают двухфазное завершение транзакций – процесс, позволяющий осуществлять транзакции над несколькими БД, относящимися к одной и той же СУБД. Для поддержки распределенных транзакций (над базами данных, управляемых разными СУБД) используются мониторы транзакций.
Структура команды
<имя оператора><ключевое слово> <дополнит. ключевые слова, константы, выражения >
Пример:
Update Поставщик set[Адрес]=’Правды, 8а’,[Город]=’Витебск’ where [Название]=’МИТСО’
Каждый оператор SQL начинается с глагола, представляющего собой ключевое слово, которое определяет, что именно делает этот оператор (SELECT, INSERT, DELETE...). В операторе содержатся также предложения, содержащие сведения о том, над какими данными производятся операции. Каждое предложение начинается с ключевого слова, такого как FROM, WHERE к др. Структура предложения зависит от его типа: ряд предложений содержит имена таблиц или полей, некоторые могут содержать дополнительные ключевые слова, константы или выражения.
Все современные серверные СУБД (а также многие популярные настольные СУБД) содержат в своем составе утилиты, позволяющие выполнить SQL-предложение и просмотреть результат. В частности, клиентская часть СУБД Oracle содержит в своем составе утилиту SQL Plus, a Microsoft SQL Server — утилиту SQL Query Analyzer. В принципе, можно использовать иную базу данных и любую другую утилиту, способную выполнять в этой базе данных SQL-предложения и отображать результаты (или даже написать свою, используя какое-либо средство разработки — Visual Basic, Delphi, C++Builder и др.). В любом случае, перед тем как начать экспериментировать с базой данных следует, сделать ее резервную копию.
Поиск информации в базе данных - наиболее часто встречающаяся операция, выполняемая с помощью языка SQL. Оператор SELECT — один из самых важных операторов данного языка, применяемый для выбора данных. Синтаксис этого оператора имеет следующий вид:
SELECT список отбираемых полей FROM список таблиц, из которых отбираются поля [WHERE условия отбора ] [ORDER BY порядок сортировки ]
SELECT должны содержать слова SELECT и From. Другие ключевые слова, такие как WHERE или ORDER By, являются необязательными.
За ключевым словом SELECT следуют сведения о том, какие именно поля необходимо включить в результирующий набор данных. Звездочка (*) обозначает все поля таблицы, например:
SELECT*
Для выбора нескольких полей их имена разделяют знаком запятая.
SELECT CompanyName, ContactName, Contacttrtle
Если выбор данных осуществляется из нескольких таблиц, то имена полей указывают с именами таблиц, из которых они взяты. Имя поля отделяется от имени таблицы знаком точка (SELECT Customers.CompanyName, Shippers.CompanyName).
Для указания имен таблиц, из которых выбираются записи, применяется ключевое слово FROM, например:
Select * From Customers
Этот запрос возвратит все поля из таблицы Customers.
Если в запросе используются более одной таблицы, то имена таблиц разделяют знаком запятая (SELECT Customers.CompanyName, Shippers.CompanyName FROM Customers, Shippers).
Для фильтрации результатов, возвращаемых оператором SELECT, можно использовать предложение WHERE:
WHERE выражение1 [{AND OR} выражение2 [...]]
Например, вместо получения полного списка продуктов можно ограничиться только теми из них, у которых значение поля CategorylD равно 4:
SELECT * FROM Products WHERE CategorylD = 4
В предложении WHERE можно использовать различные выражения, например:
SELECT * FROM Products
WHERE CategorylD = 2 AND SupplierlD > 10
или:
SELECT ProductName, UnitPrice FROM Products WHERE CategorylD = 3 OR UnitPrice < 50
или:
SELECT ProductName, UnitPrice FROM Products WHERE Discontinued IS NOT NULL
Выражение Is NOT NULL означает, что соответствующая колонка результирующего набора данных не может содержать пустые значения.
В предложении WHERE можно использовать один из шести операторов сравнения, определенных в SQL (< <= > >= <>). Помимо перечисленных выше простых операторов сравнения можно использовать и специальные операторы cpaвнения,
Специальные операторы сравнения
ALL | Применяется совместно с операторами сравнения при сравнении со списком значений |
ANY | Применяется совместно с операторами сравнения при сравнении со списком значений |
BETWEEN | Применяется при проверке нахождения значения интервала внутри заданного (включая его границы) |
IN | Применяется для проверки наличия значения в списке |
LIKE | Применяется при заданной маске проверки соответствия значения |
Приведем несколько примеров применения этих операторов. Для сопоставления данных с маской применяется ключевое слово LIKE:
SELECT CompanyName, ContaclName FROM Customers WHERE CompanyName LIKE 'M*'
В данной маске символ * (звездочка) заменяет любую последовательность символов, а символ «?» (вопрос) может заменить один любой символ.
Предложение ORDER BY (необязательное) применяется для сортировки результирующего набора данных по одному или нескольким полям. Для определения порядка сортировки используются ключевые слова ASC (по возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по возрастанию. Синтаксис предложения ORDER BY имеет вид:
ORDER BY полеl [(ASC DESC}],поле2 [{ASC | DESC}] [,...]
Например, для сортировки списка сотрудников по фамилии и затем по имени следует использовать следующий SQL-запрос:
SELECT LastName, FirstName, Title
FROM Employees
ORDER BY LastName, FirstName
Если требуется отсортировать данные в убывающем порядке (например, необходим список продуктов в порядке убывания цен), используется ключевое слово DESC:
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
Связывание таблиц
Как мы уже убедились, с помощью языка SQL можно создавать запросы, позволяющие извлечь данные из нескольких таблиц. Одна из возможностей сделать это заключается в связывании таблиц по одному или нескольким полям. Обратите внимание на то, что без связывания таблиц в результате запроса получится набор данных, содержащий все возможные комбинации строк каждой из исходных таблиц (известные также как декартово произведение):
SELECT ProductName, CategoryName FROM Products, Categories
В то время как запрос, показанный ниже, приводит к отображению списка продуктов с указанием, к какой категории принадлежит данный продукт:
SELECT ProductName, CategoryName
FROM Products, Categories
WHERE Products.CategorylD = Categories.CategorylD
Для наглядности можно сравнить результаты этих двух запросов.
В общем случае синтаксис для связывания таблиц имеет вид:
SELECT column-list
FROMtable1,table2
WHERE table!.column 1=table2.column2
Для вычисления суммарных значений на основе данных одной или нескольких таблиц можно использовать предложение GROUP BY:
GROUP BY {поле1} [,...]
Например, следующий запрос связывает две таблицы, сортирует их по полю CustomerlD, для каждого значения CustomerlD создает одну строку в результирующем наборе данных и вычисляет количество значений поля OrderlD для каждого значения CustomerlD:
SELECT Customers.CustomerlD,
COUNT (Orders. OrderlD) AS OrdersCount
FROM Customers INNER JOIN Orders
ON Customers.CustomerlD = Orders.CustomerlD
GROUP BY Customers.CustomerlD
ORDER BY OrdersCount DESC
В приведенном выше примере запроса мы использовали в предложении SELECT агрегатную функцию COUNT, вычисляющую количество значений.
Помимо перечисленных выше агрегатных функций можно использовать также математические и строковые функции, приведенные ниже:
Avg - Вычисляет среднее
Count - Вычисляет количество непустых значений в данной колонке
Max - Вычисляет наибольшее значение в колонке
Min - Вычисляет наименьшее значение в колонке
Sum - Вычисляет сумму значений в колонке
Математические и строковые функции SQL:
ABS | Возвращает абсолютное значение числа |
CEIL | Округляет дробное число |
FLOOR | Удаляет дробную часть числа |
GREATEST | Возвращает наибольшее из двух значений. |
LEAST | Возвращает наименьшее из двух значений. |
MOD | Возвращает остаток от деления одного числа на другое |
POWER | Возвращает значение, равное одному числу в степени. |
ROUND | Округляет число с точностью до указанного десятичного знака |
SIGN | Возвращает -1, если число отрицательное, и 1, если положительное |
SQRT | Квадратный корень |
LEFT | Возвращает указанное число знаков строки, начиная слева. |
RIGHT | Возвращает указанное число знаков строки, начиная справа |
UPPER | Заменяет все буквы в строке на прописные |
LOWER | Заменяет все буквы в строке на строчные |
INITCAP | Расставляет заглавные буквы в начале слов в строке |
LENGTH | Вычисляет число символов в строке |
LPAD | Добавляет указанный символ в левую часть строки в количестве, необходимом для того, чтобы строка имела заданную длину |
RPAD | Добавляет указанный символ в правую часть строки в количестве, необходимом для того, чтобы строка имела заданную длину |
SUBSTR | Извлекает подстроку нужной длины из строки, начиная с номера указанной позиции |