Курс лекций "Базы данных и субд" Ульянов В. С. Лекция Язык sql. Выборка данных
| Вид материала | Курс лекций |
- Курс лекций "Базы данных и субд" Ульянов В. С. Лекция Язык sql. Создание таблиц и ограничений, 146.46kb.
- Курс лекций "Базы данных и субд" Ульянов В. С. Лекция Язык sql. Однострочные функции, 247.53kb.
- Курс лекций "Базы данных и субд" Ульянов В. С. Лекция. Манипулирование реляционными, 276.31kb.
- Лекция №1: Стандарты языка sql, 1420.56kb.
- Курс лекций "Базы данных и субд" Логинова С. А. Лекция 13. Язык sql. Команды dml. Команды, 133.93kb.
- Отчёт по курсовой работе на тему «Лабораторный практикум по изучению языка структурированных, 1960.59kb.
- Курс лекций "Базы данных и субд" Ульянов В. С. Лекция. Целостная часть реляционной, 213.79kb.
- Курс, 1 поток, 5-й семестр лекции (34 часа), экзамен, 52.85kb.
- Курс 2778. Создание запросов на языке Microsoft sql server 2005 Transact-sql. Курс, 16.57kb.
- Курсовая работа, 52.16kb.
Курс лекций “Базы данных и СУБД” Ульянов В.С.
Лекция 3. Язык SQL. Выборка данных.
Сортировка. Ограничение количества выбираемых строк.
Язык SQL
SQL (Structured Query Language – Структурированный Язык Запросов) – стандартный язык запросов по работе с реляционными БД. Изначально под “запросом” подразумевалась операция выборки данных или манипулирования данными (вставка, обновление, изменение строк). На самом деле уже при его создании SQL являлся полным языком баз данных, позволявшим выполнять весь спектр операций с базой данных: создание объектов БД (таких как таблицы, представления, последовательности и т.п.), изменение структуры объектов БД, добавление ограничений целостности, удаление объектов БД и т.д.
SQL появился после создания реляционной алгебры в середине 70-х, он был разработан фирмой IBM в рамках проекта экспериментальной реляционной СУБД System R. Но этот язык был настолько прост и удобен, что получил широкое распространение и постепенно стал стандартом “де-факто” (фактическим стандартом) для языков манипулирования данными в реляционных СУБД. Все СУБД, претендующие на название "реляционные", реализовали тот или иной диалект SQL. Изначально эти диалекты могли существенно различаться между собой, что затрудняло переносимость приложений между различными СУБД. Чтобы решить эту проблему в дальнейшем были разработаны юридические стандарты SQL. Такого рода стандарты разрабатываются специальными международными организациями по стандартизации. Каждый такой стандарт представляет собой объемный документ, тщательным образом описывающий все команды и функции языка.
Первый международный стандарт SQL был принят в 1989 г. Американским Национальным Институтом Стандартов (ANSI – American National Standards Institute), как ANSI X3.135-1989 или ANSI SQL/89. Этот стандарт, помимо ANSI был также одобрен Международной Организацией Стандартов (ISO – International Standards Organization) в документе ISO 9075-1989. Иногда этот стандарт еще называют SQL1. Дальнейшее развитие информационных технологий, связанных с базами данных, потребовало расширения и доработки первого стандарта SQL. Так в конце 1992 г. был принят новый международный стандарт языка SQL – SQL/92 или SQL2, а в 1999 году – SQL3.
Нужно заметить, что в настоящее время, ни одна СУБД не реализует стандарт SQL в полном объеме. Кроме того, во всех диалектах языка имеются возможности, не являющиеся стандартными. Таким образом, можно сказать, что каждый диалект - это надмножество некоторого подмножества стандарта SQL.
Язык SQL оперирует терминами, несколько отличающимися от терминов реляционной теории, например, вместо "отношений" используются "таблицы", вместо "кортежей" - "строки", вместо "атрибутов" - "колонки" или "столбцы". Язык SQL является реляционно полным. Это означает, что любой оператор реляционной алгебры может быть выражен подходящим оператором SQL.
Стандарт языка SQL, хотя и основан на реляционной теории, но во многих местах отходит он нее. Например, отношение в реляционной модели данных не допускает наличия одинаковых кортежей, а таблицы в терминологии SQL могут иметь одинаковые строки. Имеются и другие отличия.
Все примеры в лекциях по SQL основаны на базе данных, приведенной в Приложении А.
Команды SQL
В SQL имеется множество команд. Следующая таблица содержит команды, которые рассматриваются в данном курсе.
| Команда | Действие |
| SELECT | Используется для извлечения данных из базы данных. Команда, заменяющая все операторы реляционной алгебры и использующаяся наиболее широко. |
| INSERT UPDATE DELETE | Команды, предназначенные, соответственно, для ввода новых, изменения существующих и удаления ненужных строк из таблиц базы данных. Известны как команды языка манипулирования данными (DML – Data Manipulation Language). |
| CREATE ALTER DROP RENAME TRUNCATE | Используются для создания, изменения и удаления объектов базы данных (таблиц, представлений, последовательностей и т.п.) Известны как команды языка определения данных (DDL – Data Definition Language). |
| COMMIT ROLLBACK SAVEPOINT | Команды управления транзакциями. Управляют изменениями, которые производятся с помощью команд DML. |
Условные обозначения синтаксиса:
| Обозначение | Объект или термин | Пример |
| Прописные буквы | Команды, функции, ключевые слова | SELECT * FROM таблица |
| Строчные буквы | Синтаксические переменные, то есть обозначения, вместо которых надо подставить конкретные значения | SELECT * FROM таблица |
| Вертикальная черта | Разделяет альтернативные элементы синтаксиса | OFF|ON |
| Квадратные скобки | Список необязательных элементов синтаксиса | [OFF|ON] |
| Фигурные скобки | Список обязательных элементов синтаксиса | {OFF|ON} |
| Подчеркивание | Значение по умолчанию | {OFF|ON} |
| Многоточие | Означает, что выражение перед многоточием может быть повторено несколько раз | SELECT столбец, … FROM таблица |
Выборка данных
Для выборки строк из базы данных используется команда SELECT языка SQL. Сокращенный синтаксис команды SELECT:
SELECT [ DISTINCT ] { *| столбец [ псевдоним ], …}
FROM таблица
[WHERE условие]
[ORDER BY {столбец | выражение [ASC | DESC], … }];
где:
| предложение SELECT | указывает список выбираемых столбцов; |
| ключевое слово DISTINCT | подавляет выборку дубликатов строк; |
| столбец | выбирает заданный столбец; |
| * | выбирает все столбцы таблицы; |
| предложение FROM | указывает таблицу, из которой производится выборка; |
| предложение WHERE | ограничивает запрос строками, удовлетворяющими заданному условию; |
| условие | логическое выражение, состоящее из имен столбцов, выражений, констант и операторов сравнения; |
| предложение ORDER BY | сортирует строки по значению задаваемых столбцов или выражений; |
| ASC | задает сортировку по возрастанию (используется по умолчанию); |
| DESC | задает сортировку по убыванию. |
Команда SELECT используется именно для выборки данных. Получая эту команду от программы-клиента, сервер выбирает нужные строки и столбцы и передает их клиенту. Что произойдет с этими данными в дальнейшем – зависит от конкретной программы-клиента, она может вывести их на экран, на печать, сохранить в файл, передать другой программе на обработку и т.п. За все эти операции команда SELECT уже не отвечает.
Обратите внимание, команду SELECT можно логически разделить на 4 предложения, каждое из которых несет свою смысловую нагрузку: предложение SELECT (содержит список выбираемых столбцов), предложение FROM (содержит имя таблицы), предложение WHERE (содержит условие, ограничивающее выборку) и предложение ORDER BY (задает сортировку строк). При составлении команды принято (но не обязательно) каждое предложение писать с новой строки. Такой подход облегчает восприятие структуры запроса и поиск ошибок. Каждая команда SQL должна оканчиваться символом ‘;’, который является признаком того, что ввод команды закончен и ее можно передать серверу на выполнение.
Команда SELECT обязательно должна содержать список столбцов таблицы, которые пользователь желает выбрать в предложении SELECT, а также имя самой таблицы в предложении FROM. В случае если нужно выбрать все столбцы, можно не затрудняться их перечислением и воспользоваться символом *.
Пример: Вывести все данные из таблицы s_dept:
SELECT *
FROM s_dept;
Результат:
ID NAME REGION_ID
----- ---------- --------
10 Finance 1
31 Sales 1
32 Sales 2
33 Sales 3
34 Sales 4
35 Sales 5
41 Operations 1
…
12 rows selected.
Если требуется выбрать только некоторые столбцы, их список необходимо указать в предложении SELECT через запятую. Выборка только части столбцов таблицы соответствует оператору проекции реляционной алгебры.
Пример: Вывести номера и названия отделов из таблицы s_dept:
SELECT id, name
FROM s_dept;
Результат:
ID NAME
---- -----------
10 Finance
31 Sales
32 Sales
33 Sales
34 Sales
35 Sales
41 Operations
…
12 rows selected.
В предложении SELECT можно использовать не только имена столбцов, но и выражения, составленные с помощью арифметических операторов (‘+’ - сложение, ‘-‘ - вычитание, ‘*’ - умножение, ‘/’ - деление), имен столбцов и констант.
Пример: Для каждого служащего вывести его фамилию и удвоенную зарплату:
SELECT last_name, salary*2
FROM s_emp;
-- В этом примере в предложении SELECT присутствует арифметическое выражение -- salary*2, где salary – имя столбца, * - оператор умножения, 2 –
-- константа.
Результат:
LAST_NAME SALARY*2
--------------- ---------
Velasquez 5000
Ngao 2900
Nagayama 2800
Quick-To-See 2900
…
25 rows selected.
При использовании арифметических операторов необходимо помнить об очередности их выполнения: умножение и деление выполняются в первую очередь, затем – сложение и вычитание. Если операторы в выражении имеют один и тот же приоритет, они выполняются слева направо. В случае если Вам необходимо поменять очередность – используйте скобки.
При выводе столбцы получают те имена, которые были указаны для них в предложении SELECT. Часто эти имена бывают трудны для понимания и даже бессмысленны. Если Вы желаете, чтобы при выводе у столбцов были другие имена, можно задать для них псевдонимы. Псевдоним задается в списке команды SELECT сразу за именем столбца и отделяется от него пробелом. Если вы желаете включить в псевдоним пробелы или специальные символы (например, символы русского алфавита), его необходимо заключить в кавычки (“ “). Кавычки также необходимо использовать, если Вы хотите, чтобы в псевдониме различались символы верхнего и нижнего регистра. В противном случае все символы псевдонима выводятся в верхнем регистре.
Пример: Вывод фамилии, заработной платы и суммы компенсационных выплат за год для каждого служащего. Объем выплат за год вычисляется путем прибавления к заработной плате ежемесячной премии в размере 100 долларов и умножения суммы на 12. Назвать столбец ‘годовая зарплата’:
SELECT last_name, salary, 12 * (salary + 100) "годовая зарплата"
FROM s_emp;
Результат:
LAST_NAME SALARY годовая зарплата
-------------- --------- ----------------
Velasquez 2500 31200
Ngao 1450 18600
Nagayama 1400 18000
…
25 rows selected.
Помимо арифметических операторов в предложении SELECT может присутствовать единственный символьный оператор – оператор конкатенации – || . Он позволяет соединять столбцы с другими столбцами, арифметическими выражениями или постоянными значениями для создания символьных выражений. Столбцы, указанные по обе стороны этого оператора, объединяются в один выходной столбец.
Кроме того, в список SELECT можно включать литералы. Литерал – это любой символ, выражение или число, включенные в список SELECT и не являющиеся ни именем, ни псевдонимом столбца. Литералы печатаются для каждой возвращаемой строки. Символьные литералы и литералы-даты должны быть заключены в апострофы (‘ ‘), а числовые литералы – нет.
Пример: Вывести фамилию и заработную плату для каждого сотрудника в виде <фамилия> получает <зарплата>. Столбец назвать ‘служащие’.
SELECT last_name||' получает '||salary "служащие"
FROM s_emp;
Результат:
Служащие
--------------------------
Velasquez получает 2500
Ngao получает 1450
Nagayama получает 1400
Quick-To-See получает 1450
Ropeburn получает 1550
Urguhart получает 1200
…
25 rows selected.
Если какое-то из полей в БД содержит неопределенное значение, при выводе на экране на его месте отображается пустое поле.
Пример: Вывод фамилии, должности и комиссионного процента для каждого служащего:
SELECT last_name, title, commission_pct
FROM s_emp;
Результат:
LAST_NAME TITLE COMMISSION_PCT
--------------- -------------------- --------------
Velasquez President
Ngao VP, Operations
Nagayama VP, Sales
Quick-To-See VP, Finance
Ropeburn VP, Administration
Urguhart Warehouse Manager
Menchu Warehouse Manager
Biri Warehouse Manager
Catchpole Warehouse Manager
Havel Warehouse Manager
Magee Sales Representative 10
Giljum Sales Representative 12,5
Sedeghi Sales Representative 10
…
25 rows selected.
Если Вы хотите, чтобы вместо пустого поля выводилось какое-то конкретное значение, используйте функцию NVL. Ее синтаксис:
NVL (выражение1, выражение2)
где:
| выражение1 | исходное или вычисленное значение, которое может быть неопределенным; |
| выражение2 | значение, которое подставляется вместо неопределенного значения. |
Функцию NVL можно применять для преобразования любого типа данных, но необходимо следить, чтобы тип данных выражения2 был тем же, что и у выражения1.
Пример: Вывод фамилии, должности и комиссионного процента для каждого служащего. В случае, если служащий не получает комиссионных, вместо комиссионного процента должен выводиться 0.
SELECT last_name, title, nvl(commission_pct,0) comm
FROM s_emp;
Результат:
LAST_NAME TITLE COMM
------------ --------------------- --------------
Velasquez President 0
Ngao VP, Operations 0
Nagayama VP, Sales 0
Quick-To-See VP, Finance 0
Ropeburn VP, Administration 0
Urguhart Warehouse Manager 0
Menchu Warehouse Manager 0
Biri Warehouse Manager 0
Catchpole Warehouse Manager 0
Havel Warehouse Manager 0
Magee Sales Representative 10
Giljum Sales Representative 12,5
Sedeghi Sales Representative 10
…
25 rows selected.
При отсутствии указаний со стороны пользователя в результат запроса включаются все строки, даже если они имеют дубликаты.
Пример: Вывод названий отделов из таблицы s_dept:
SELECT name
FROM s_dept;
Результат:
NAME
----------------
Finance
Sales
Sales
Sales
Sales
Sales
Operations
Operations
Operations
Operations
Operations
Administration
12 rows selected.
Предотвратить выборку дубликатов можно, используя ключевое слово DISTINCT.
Пример: Вывод названий отделов из таблицы s_dept, исключая дубликаты:
SELECT DISTINCT name
FROM s_dept;
Результат:
NAME
----------------
Administration
Finance
Operations
Sales
Сортировка
При отсутствии дополнительных указаний результирующие строки выдаются пользователю в неотсортированном виде. С помощью предложения ORDER BY Вы можете указать столбец (или список столбцов), по значению которого Вы хотите отсортировать данные. При этом в случае сортировки в порядке возрастания числа выводятся от меньшего к большему, даты – от более ранних к более поздним, символьные значения – в алфавитном порядке. Неопределенные значения при сортировке по возрастанию выводятся последними, при сортировке по убыванию – первыми.
Сортировка по возрастанию задается с помощью ключевого слова ASC, по убыванию – с помощью ключевого слова DESC. Если ни одно из этих ключевых слов не указано, сортировка проводится по возрастанию. В предложении ORDER BY можно указывать не только имя столбца, но и его псевдоним.
Пример: Вывод из таблицы s_emp фамилии, номера и даты найма каждого служащего. Результат сортируется таким образом, чтобы служащие, нанятые последними, возглавляли список:
SELECT last_name, dept_id, start_date
FROM s_emp
ORDER BY start_date DESC;
Результат:
LAST_NAME DEPT_ID START_DATE
--------------- --------- --------
Catchpole 44 09.02.92
Maduro 41 07.02.92
Nguyen 34 22.01.92
Giljum 32 18.01.92
Dumas 35 09.10.91
Patel 42 06.08.91
Newman 43 21.07.91
Nagayama 31 17.06.91
…
25 rows selected.
Еще один способ сортировки результатов запроса – это сортировка по позиции столбца. Он особенно полезен при сортировке по длинному выражению. Вместо повторного ввода выражения можно указать его позицию в списке SELECT.
Пример: Запрос из предыдущего примера можно записать следующим образом
SELECT last_name, dept_id, start_date
FROM s_emp
ORDER BY 3 DESC;
Сортировать результат можно и по нескольким столбцам. Предельным количеством столбцов сортировки является количество столбцов таблицы. Столбцы указываются в предложении ORDER BY через запятые. Для изменения порядка сортировки по какому-либо столбцу на обратный следует задать ключевое слово DESC после его имени или позиции. Сортировать можно и по столбцам, не входящим в список SELECT.
Пример: Вывод фамилии, номера отдела и заработной платы всех служащих. Результат сортируется по номерам отделов, а внутри отделов – в порядке убывания заработной платы:
SELECT last_name, dept_id, salary
FROM s_emp
ORDER BY 2, 3 DESC;
Результат:
LAST_NAME DEPT_ID SALARY
-------------- --------- ---------
Quick-To-See 10 1450
Nagayama 31 1400
Magee 31 1400
Giljum 32 1490
Sedeghi 33 1515
Nguyen 34 1525
Patel 34 795
Dumas 35 1450
…
25 rows selected.
Ограничение количества выбираемых строк
Ограничить набор строк, возвращаемых в результате запроса, можно с помощью предложения WHERE. Ограничение количества выбираемых строк с помощью предложения WHERE соответствует операции выборки реляционной алгебры.
Предложение WHERE следует сразу за предложением FROM и задает условие, которое должно быть выполнено. Условие представляет собой некоторое логическое выражение, которое может состоять из имен столбцов, выражений, констант, операторов сравнения и логических операторов. В результирующую выборку попадут только те строки таблицы, для которых заданное логическое выражение будет иметь значение ‘истина’.
I. Простые операторы сравнения.
В предложении WHERE могут использоваться следующие операторы сравнения:
= равно
<> не равно
> больше
>= больше или равно
< меньше
<= меньше или равно
Пример: Вывод должности сотрудника по фамилии Ngao:
SELECT title
FROM s_emp
WHERE last_name=’Ngao’;
Результат:
TITLE
----------------
VP, Operations
Обратите внимание, что строки символов и даты в предложении WHERE должны быть заключены в апострофы (‘ ‘), в то время как числа – нет.
Пример: Вывод фамилий и должностей сотрудников с зарплатой от 2000:
SELECT last_name, title
FROM s_emp
WHERE salary>=2000;
Результат:
LAST_NAME TITLE
------------- -----------
Velasquez President
II. Специальные операторы сравнения SQL.
1. BETWEEN…AND… – используется для поиска значений попадающих в заданный интервал (включительно).
Пример: Вывод фамилии сотрудников, чья зарплата больше или равна 1500 и меньше или равна 2000:
SELECT last_name
FROM s_emp
WHERE salary BETWEEN 1500 AND 2000;
Результат:
LAST_NAME
---------
Ropeburn
Sedeghi
Nguyen
2. IN (список) – используется для поиска значений, совпадающих с каким-либо значением из списка.
Пример: Вывод фамилий сотрудников с номерами 1, 3 и 5:
SELECT last_name
FROM s_emp
WHERE id IN (1, 3, 5);
Результат:
LAST_NAME
-----------
Ropeburn
Nagayama
Velasquez
3. LIKE – позволяет производить поиск по некоторому символьному шаблону. Такая операция называется поиском по метасимволам. В SQL можно использовать два метасимвола:
% - заменяет любую последовательность из нуля и более символов;
_ - заменяет любой одиночный символ.
Пример: Вывод фамилий сотрудников с фамилиями, начинающимися на букву ‘C’:
SELECT last_name
FROM s_emp
WHERE last_name like ‘C%’;
Результат:
LAST_NAME
----------
Catchpole
Chang
Пример: Вывод фамилий, второй буквой в которых является “а”:
SELECT last_name
FROM s_emp
WHERE last_name like ‘_a%’;
Результат:
LAST_NAME
---------
Nagayama
Catchpole
Havel
Magee
Maduro
Patel
Markarian
Patel
Dancs
4. IS NULL – используется для поиска неизвестных значений. Как мы уже упоминали в Лекции 3, любые операции сравнения с неизвестным значением дают ‘неизвестно’. Поэтому для поиска неизвестных значений нельзя использовать простые операторы сравнения. Вместо этого используются специальные операторы SQL IS NULL – для поиска неизвестных значений, IS NOT NULL – для поиска значений не являющихся неизвестными.
Пример: Вывод фамилий и должностей для всех служащих, не получающих комиссионные:
SELECT last_name, title
FROM s_emp
WHERE commission_pct = NULL;
Результат:
no rows selected
-- Запрос не вернул ни одной строки, так как при любом значении
-- commission_pct выражение commission_pct = NULL дает значение
-- ‘неизвестно’.
Правильный запрос:
SELECT last_name, title
FROM s_emp
WHERE commission_pct IS NULL;
Результат:
LAST_NAME TITLE
-------------- ---------------------
Velasquez President
Ngao VP, Operations
Nagayama VP, Sales
Quick-To-See VP, Finance
Ropeburn VP, Administration
Urguhart Warehouse Manager
Menchu Warehouse Manager
Biri Warehouse Manager
…
20 rows selected.
Пример: Вывод фамилий и должностей для всех служащих, получающих комиссионные:
SELECT last_name, title
FROM s_emp
WHERE commission_pct <> NULL;
Результат:
no rows selected
-- Запрос не вернул ни одной строки, так как при любом значении
-- commission_pct выражение commission_pct <> NULL дает значение
-- ‘неизвестно’.
Правильный запрос:
SELECT last_name, title
FROM s_emp
WHERE commission_pct IS NOT NULL;
Результат:
LAST_NAME TITLE
---------------- ----------------------
Magee Sales Representative
Giljum Sales Representative
Sedeghi Sales Representative
Nguyen Sales Representative
Dumas Sales Representative
III. Логические операторы.
- AND – используется, когда необходимо, чтобы в выборку попали строки, для которых одновременно выполняются два условия.
Пример: Вывод номеров служащих, работающих в 31 отделе и получающих зарплату более 1300:
SELECT id
FROM s_emp
WHERE dept_id=31 AND salary>1300;
Результат:
ID
--------
3
11
- OR – используется, когда необходимо, чтобы в выборку попали строки, для которых выполняется как минимум одно из двух условий.
Пример: Вывод номеров служащих, получающих зарплату более 2000 или комиссионные более 10%:
SELECT id
FROM s_emp
WHERE salary>2000 OR commission_pct>10;
Результат:
ID
---------
1
12
14
15
- NOT – используется для того, чтобы инвертировать результат какого-либо условия.
Пример: Вывод фамилий и должностей всех служащих кроме тех, кто работает в 41, 43 или 45 отделах:
SELECT last_name, title
FROM s_emp
WHERE dept_id not in (41,43,45);
Результат:
LAST_NAME TITLE
------------- -------------------------
Velasquez President
Nagayama VP, Sales
Quick-To-See VP, Finance
Ropeburn VP, Administration
Menchu Warehouse Manager
Catchpole Warehouse Manager
Magee Sales Representative
…
15 rows selected.
Л
екция 3. Язык SQL. Выборка данных. Сортировка. Ограничение количества выбираемых строк.