Кафедра ИС Что такое SQL Structured Query

Кафедра ИС

Что такое SQL (Structured Query Language) - язык структурированных запросов: является универсальным языком для создания, модификаций и управления данными в реляционных базах данных. Историческая справка: – предложен в 1970 И. Ф. Коддом (E. F. Codd) в исследовательской лаборатории IBM – начало 80 -х Oracle Relational Technology создаёт СУБД на основе SQL – в 1989 уже существует более 25 SQL-подобных СУБД – в 1983 выходит ANSI-стандарт (American National Standards Institute) SQL, а в 1992 расширенный стандарт (SQL 2 или SQL-92). Все промышленные версии стараются поддерживать ANSI-SQL, но не обеспечиваю его полностью, однако, каждая из них имеет свои расширения, стремясь покрыть требуемые функциональные возможности.

Общие требования к реляционной СУБД Вся информация представлена в виде таблиц. Поддерживать логическую структуру данных независимо от их физического представления. Использовать язык высокого уровня для работы со структурой данных, получения данных и их изменения. Обеспечивать теоретико-множественные операции над данными, такие как объединение, пересечение и дополнение. Поддерживать виртуальные таблицы, как альтернативный способ получения данных. Позволять различать неопределённые (пропущенные) данные. Обеспечивать механизмы для поддержки реляционной целостности, авторизованного доступа, транзакционности изменений, восстановления данных.

Данные в реляционной СУБД Вся информация представлена в виде таблиц: Отношение (relation), таблица (table), файл (file) Ячейка (cell), адрес (addres), значение атрибута (alltibute value) Кортеж (tuple), строка (row), запись (record) Атрибут (attribute), столбец (column), поле (field)

Структура СУБД В реляционных СУБД обычно существует два типа таблиц: пользовательские таблицы (user tables) системные таблицы (system tables). Системные таблицы (известные также под названием системный каталог [system catalog]) содержат описания баз(ы) данных. Доступ к этим таблицам обычно открыт и осуществляется либо через системные процедуры-функции либо непосредственно. Независимость данных в реляционных СУБД обеспечивается на двух уровнях: Физическая независимость - означает независимость от способа физического хранения данных. Логическая независимость - означает, что правильное функционирование не зависит от изменений, вносимых в структуру пользовательских данных.

Создание объектов базы данных • CREATE DATABASE [Database. Name | ? ] • CREAE TABLE | DBF Table. Name 1 [NAME Long. Table. Name] [FREE] (Field. Name 1 Field. Type [(n. Field. Width [, n. Precision])] [NULL | NOT NULL] • INDEX ON e. Expression [CHECK l. Expression 1 [ERROR c. Message. Text 1]] [DEFAULT e. Expression 1] TO IDXFile. Name | TAG Tag. Name [PRIMARY KEY | UNIQUE] [OF CDXFile. Name] [REFERENCES Table. Name 2 [TAG Tag. Name 1]] [FOR l. Expression] [NOCPTRANS] [COMPACT] [, Field. Name 2. . . ] [ASCENDING | DESCENDING] [, PRIMARY KEY e. Expression 2 TAG Tag. Name 2 [UNIQUE | CANDIDATE] |, UNIQUE e. Expression 3 TAG Tag. Name 3] [ADDITIVE] [, FOREIGN KEY e. Expression 4 TAG Tag. Name 4 [NODUP] REFERENCES Table. Name 3 [TAG Tag. Name 5]] • CREATE TRIGGER ON Table. Name [, CHECK l. Expression 2 [ERROR c. Message. Text 2]]) FOR DELETE | INSERT | UPDATE AS l. Expression | FROM ARRAY Array. Name • CREATE CONNECTION [Connection. Name | ? ] [DATASOURCE c. Data. Source. Name] [USERID c. User. ID] [PASSWORD c. Pass. Word] [DATABASE c. Database. Name] | CONNSTRING c. Connection. String] • CREATE SQL VIEW [View. Name ] [REMOTE] [CONNECTION Connection. Name [SHARE] | CONNECTION Data. Source. Name] [AS SQLSELECTStatement] • CREATE CURSOR alias_name (fname 1 type [(precision [, scale]) [NULL | NOT NULL] [CHECK l. Expression [ERROR c. Message. Text]] [DEFAULT e. Expression] [UNIQUE] [NOCPTRANS]] [, fname 2. . . ]) | FROM ARRAY Array. Name

Изменения объектов базы данных • ADD TABLE Table. Name | ? [NAME Long. Table. Name] ALTER TABLE Table. Name 1 ADD | ALTER [COLUMN] Field. Name 1 Field. Type [(n. Field. Width [, n. Precision])] [NULL | NOT NULL] [CHECK l. Expression 1 [ERROR c. Message. Text 1]] [DEFAULT e. Expression 1] [PRIMARY KEY | UNIQUE] [REFERENCES Table. Name 2 [TAG Tag. Name 1]] [NOCPTRANS] [NOVALIDATE] • MODIFY PROCEDURE • Gendbc. prg • COMPILE DATABASE Database. Name -or. ALTER TABLE Table. Name 1 ALTER [COLUMN] Field. Name 2 [NULL | NOT NULL] [SET DEFAULT e. Expression 2] [SET CHECK l. Expression 2 [ERROR c. Message. Text 2]] [DROP DEFAULT] [DROP CHECK] [NOVALIDATE] -or. ALTER TABLE Table. Name 1 [DROP [COLUMN] Field. Name 3] [SET CHECK l. Expression 3 [ERROR c. Message. Text 3]] [DROP CHECK] [ADD PRIMARY KEY e. Expression 3 TAG Tag. Name 2 [FOR l. Expression 4]] [DROP PRIMARY KEY] [ADD UNIQUE e. Expression 4 [TAG Tag. Name 3 [FOR l. Expression 5]]] [DROP UNIQUE TAG Tag. Name 4] [ADD FOREIGN KEY [e. Expression 5] TAG Tag. Name 4 [FOR l. Expression 6] REFERENCES Table. Name 2 [TAG Tag. Name 5]] • VALIDATE DATABASE [DROP FOREIGN KEY TAG Tag. Name 6 [SAVE]] [RECOVER] [RENAME COLUMN Field. Name 4 TO Field. Name 5] [NOCONSOLE] [NOVALIDATE] [TO PRINTER [PROMPT] | TO FILE File. Name]

Удаления объектов базы данных • DELETE DATABASE Database. Name | ? [DELETETABLES] [RECYCLE] • DELETE CONNECTION Connection. Name • DELETE TAG Tag. Name 1 [OF CDXFile. Name 1] [, Tag. Name 2 [OF CDXFile. Name 2]]. . . -or. DELETE TAG ALL [OF CDXFile. Name] • DELETE VIEW View. Name -or. DROP VIEW View. Name • DROP TABLE Table. Name | File. Name | ? [RECYCLE] • DELETE TRIGGER ON Table. Name FOR DELETE | INSERT | UPDATE

Добавление изменение и удаление данных из таблиц • INSERT INTO dbf_name [(fname 1 [, fname 2, . . . ])] VALUES (e. Expression 1 [, e. Expression 2, . . . ]) -or. INSERT INTO dbf_name FROM ARRAY Array. Name | FROM MEMVAR • UPDATE [Database. Name 1!]Table. Name 1 SET Column_Name 1 = e. Expression 1 [, Column_Name 2 = e. Expression 2. . . ] WHERE Filter. Condition 1 [AND | OR Filter. Condition 2. . . ]] • DELETE FROM [Database. Name!]Table. Name [WHERE Filter. Condition 1 [AND | OR Filter. Condition 2. . . ]] • DELETE [Scope] [FOR l. Expression 1] [WHILE l. Expression 2] [IN n. Work. Area | c. Table. Alias] [NOOPTIMIZE] • RECALL [Scope] [FOR l. Expression 1] [WHILE l. Expression 2] [NOOPTIMIZE] (VFP specific)

Выборка данных SELECT список_полей FROM список_таблиц WHERE условия_на_записи GROUP BY способ_группировки HAVING условия_на_группы ORDER BY способ_сортировки INTO новая_таблица

SELECT [ALL | DISTINCT] [TOP n. Expr [PERCENT]] [Alias. ] Select_Item [AS Column_Name] [, [Alias. ] Select_Item [AS Column_Name]. . . ] Команда SQL Select синтаксис FROM [FORCE] [Database. Name!]Table [[AS] Local_Alias] [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN Database. Name!]Table [[AS] Local_Alias] [ON Join. Condition …] [[INTO Destination] | [TO FILE File. Name [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]] Destination: [PREFERENCE Preference. Name] • ARRAY Array. Name • CURSOR Cursor. Name [NOFILTER] [NOCONSOLE] • DBF | TABLE Table. Name [PLAIN] [DATABASE Database. Name [NAME Long. Table. Name]] [NOWAIT] [WHERE Join. Condition [AND Join. Condition. . . ] [AND | OR Filter. Condition. . . ]]] [GROUP BY Group. Column [, Group. Column. . . ]] [HAVING Filter. Condition] [UNION [ALL] SELECTCommand] [ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC]. . . ]] Order_Item: • field • № field

Допустимые форматы Filter. Condition Команда SQL Select_Item: • поле таблицы • строкой (‘№’) • вычисляемым выражением (ydt_sales*price), которое может включать UDF

Агрегирующие функции: Команда • AVG(Select_Item) среднее значение • COUNT(Select_Item) количество выбранных записей для поля • COUNT(*) количество выбранных записей в запросе • MIN(Select_Item) наименьшее значений поля • MAX(Select_Item) наибольшее значение поля • SUM(Select_Item) сумма для числового поля SQL Select Нужно иметь ввиду: • Системная глобальная переменная _TALLY содержит количество записей выбранных в последнем запросе • Значение SET FILTER не влияет на запрос • Подзапрос не может быть вложенным, т. е. снова содержать в себе подзапрос. • Названия полей должны быть уникальны в списке полей • Использование функций: DELETED( ), EOF( ), FOUND( ), RECCOUNT( ) и RECNO( ) может привести к непредвиденным результатам по причине неопределённости рабочих областей для таблиц, участвующих в запросе • Предложение WHERE накладывает ограничения на строки в то время как HAVING на группы. WHERE не может применяться к агрегирующим функциям, в то время как HAVING может.

Команда SQL Select Коррелированные и некоррелированные подзапросы: Некоррелированный: Select pub_name from publishers where pub_id in (select pub_id from titles where type = ‘business’) Внутренний запрос «работает» независимо, передавая результаты во внешний. Коррелированный: Select pub_name from publishers p where ‘business’ in (select type from titles where pub_id = p. pub_id) Внутренний запрос «работает» каждый раз только получив результаты от внешнего.

База данных PUBS имитирует информационное хранилище издательской компании и состоит из 11 таблиц: 1. authors (авторы), 2. discounts (скидки), 3. employee (служащие), 4. jobs (должности), 5. pub_info (информация об издательствах), 6. publishers (издательства), 7. roysched (авторские гонорары), 8. sales (продажи), 9. stores (магазины), 10. titleauthor (название-автор), 11. titles (названия, информация о книге).

Учебная БД PUBS Гонорары: книга, верхний и нижний уровень, размер Должности: описание, минимальный и максимальный оклад Работник: ФИО, должность, оклад, дата приёма, издательство Продажи: склад, номер заказа, дата заказа, количество, оплата, книга Магазин (склад): название, адрес Скидки: тип, магазин, верхний и нижний уровень, размер Издательства: название, адрес Информация о книге: название, тип, цена, дата публикации, издательство Информация об издательстве: логотип, описание Взаимосоответствие автор-книга Информация об авторе: ФИО и адрес

Первичным ключом объявлен столбец au_id. Определены два ограничения целостности на столбцах au_id (au_id LIKE '[0 -9][0 -9]-[0 -9][0 -9][0 -9]') и zip (zip. LIKE '[0‑ 9][0 -9][0 -9]').

Эта таблица представляет интерес тем, что в ней отсутствует первичный ключ. Для таблицы определено ссылочное ограничение: столбец stor_id ссылается на первичный ключ stor_id таблицы stores.

Первичным ключом объявлен столбец emp_id. Имеются два ссылочных ограничения: столбец job_id ссылается на первичный ключ job_id таблицы jobs, столбец pub_id ссылается на первичный ключ pub_id таблицы publishers.

Первичный ключ - job_id. Значение по умолчанию этого столбца вырабатывается нестандартной функцией IDENTITY, генерирующей уникальные целые значения. Первичный ключ таблицы - pub_id. Этот же столбец является и внешним ключом и ссылается на первичный ключ pub_id таблицы publishers.

В этой таблице отсутствует первичный ключ. Объявлено одно ссылочное ограничение: столбец title_id ссылается на первичный ключ title_id таблицы titles.

Первичный ключ таблицы образует комбинация полей stor_id, ord_num, title_id. Определены два внешних ключа: столбец stor_id ссылается на первичный ключ stor_id таблицы stores, а столбец title_id - на первичный ключ title_id таблицы titles.

Первичный ключ - title_id. Внешний ключ pub_id ссылается на первичный ключ pub_id таблицы publishers.

Оператор SELECT возвращает информацию из базы данных в виде набора строк. Он состоит из трех основных элементов: SELECT - задает столбцы, которые должен возвратить запрос; FROM - таблицы, из которых будет проводиться выборка; WHERE - этот необязательный элемент задает фильтр, ограничивающий выборку; фильтровать разрешается по нескольким столбцам. SELECT fname, lname, emp_id FROM employee WHERE pub_id = 0877 Самая короткая форма оператора SELECT выглядит так: SELECT <столбцы> FROM <таблицы> Выполняя его, СУБД найдет указанную таблицу (или таблицы) и извлечет из нее требуемые столбцы. Допустимо выбирать все столбцы, указав знак *. Select pub_name from publishers Например, следующая инструкция метасимвол "*" возвратит все столбцы и строки таблицы авторов: SELECT * FROM authors

Но вся таблица иногда и не нужна, поэтому после ключевого слова WHERE стоит задавать условие, которому должны удовлетворять возвращаемые строки. В следующем примере из таблицы авторов выбираются только записи с фамилией Ringer: SELECT * FROM authors WHERE au_lname = 'Ringer' Обратите внимание на апострофы ('_') в фамилии. Они указывают, что выражение WHERE является текстовой строкой, так как столбец au_lname в базе данных содержит строковые значения. Если в WHERE задается числовое значение, то апострофы не нужны: SELECT * FROM titles WHERE royalty = 10

DISTINCT При использовании этого аргумента оператор SELECT возвращает только неодинаковые (уникальные) строки. Если список выборки содержит несколько колонок, то строки считаются неодинаковыми, если они различаются значениями хотя бы в одной из колонок. Строки считаются одинаковыми (дублирующимися), когда в каждой паре соответствующих колонок этих строк содержатся одинаковые значения. SELECT DISTINCT au_fname, au_lname FROM authors GO По запросу выйдет уникальной. 23 строки, каждая из которых будет

TOP n [PERCENT] При использовании этого аргумента оператор SELECT возвращает только первые n строк из набора результатов. SELECT TOP 5 au_fname, au_lname FROM authors GO Запрос вернет 5 строк. Если задано ключевое слово PERCENT, то будут возвращаться первые строки, составляющие n процентов от общего количества строк. При использовании ключевого слова PERCENT, число n должно быть в пределах от 0 до 100. SELECT TOP 50 PERCENT au_fname, au_lname FROM authors GO Запрос вернет 12 строк (приблизительно 50%, с округлением до большего числа). Если в запросе имеется предложение ORDER BY, то строки вывода сначала сортируются, а затем из отсортированного набора результатов выдаются первые n строк или n процентов от общего количества строк.

Если вы хотите, чтобы в наборе результатов вместо имеющегося заголовка "lname" выводился бы заголовок "Фамилия сотрудника", подчеркнув тем самым, что фамилии взяты из таблицы сотрудников, то надо применить ключевое слово AS, вот так: SELECT lname AS ‘Фамилия сотрудника’ FROM employee GO Эта команда выдаст такой вывод: Фамилия сотрудника ------------- Cruz Roulet Devon . . . O’Rourke Ashworth Latimer (всего 43 строки)

SELECT MAX(job_id) AS ‘Maximum Job ID’ FROM employee GO Алиас колонки заключен в кавычки, потому что он состоит из нескольких слов, разделенных пробелами. Если алиас не содержит пробелов, то его не нужно заключать в кавычки. Ниже приведен пример команды, выдающей количество проданных книг в каждом из магазинов, причем вывод отсортирован по этому количеству. SELECT SUM(qty) AS Quantity_of_Books, stor_id FROM sales GROUP BY stor_id ORDER BY Quantity_of_Books GO В этом примере алиас не заключен в кавычки, потому что он не содержит пробелов.

Пользуясь синтаксической конструкцией SELECT <список_выборки> INTO<новая таблица> вы можете извлекать данные из таблицы или из нескольких таблиц и помещать строки результатов в новые таблицы. Новые таблицы создаются автоматически при исполнении оператора SELECT . . . INTO и определены в соответствии с колонками из списка выборки.

Ниже приведен пример запроса, в котором оператор SELECT . . . INTO применяется для создания новой постоянной таблицы emp_info (информация о сотрудниках), в которой содержатся: имена, фамилии и описания должностных обязанностей сотрудников: SELECT employee. fname, employee. lname, jobs. job_desc INTO emp_info FROM employee, jobs WHERE employee. job_id = jobs. job_id GO

Таблица emp_info будет иметь три колонки: • fname, • Lname • job_desc, типы данных которых будут такими же, как и у колонок, заданных в исходных таблицах (employee и jobs). Если вы хотите, чтобы новая таблица была локальной временной таблицей, то перед ее именем надо поместить символ "#" (вот так: #emp_info), а чтобы новая таблица была глобальной временной таблицей, поместите перед ее именем "##" (вот так: ##emp_info).

Операция Проверяемое условие = <> Проверяется равенство двух выражений != Проверяется неравенство двух выражений (то же самое, что и <> ) > Проверяется, что первое выражение больше второго Проверяется неравенство двух выражений >= Проверяется, что первое выражение больше второго или равно ему !> < <= Проверяется, что первое выражение не больше второго !< Проверяется, что первое выражение не меньше второго Проверяется, что первое выражение меньше второго или равно ему

SELECT * FROM employee WHERE lname = ‘Latimer’ GO SELECT job_desc FROM jobs WHERE job_id <> 1 GO

Логические операции AND и OR проверяют два выражения и, в зависимости от их значений, возвращают булево значение TRUE, FALSE или UNKNOWN.

В следующем запросе в предложении WHERE имеются два выражения, соединенных логической операцией AND: SELECT job_desc, min_lvl, max_lvl FROM jobs WHERE min_lvl >= 100 AND max_lvl <= 225 GO Операция AND возвращает значение TRUE, когда оба условия возвращают TRUE. Этот запрос вернет четыре строки.

Ключевое слово LIKE применяется для поиска соответствию шаблону. Синтаксис: <сопоставляемое_выражение> LIKE <шаблон> Метасимволы T-SQL по Метасимвол Описание % _ [] Символ процента соответствует строке из нескольких символов (в том числе, пустой строке из одного символа) [^] Метасимвол "не в диапазоне" соответствует любому одному символу, не входящему в диапазон или набор символов. Например, [^m-p] или [^mnop] соответствуют любому из символов, кроме символов m, n, o или p Символ подчеркивания соответствует любому одному символу Метасимвол диапазона соответствует любому одному символу из заданного диапазона или набора символов. Например, [m-p] или [mnop] соответствуют любому из символов m, n, o или p

Если нужно найти в таблице authors все имена, начинающиеся с буквы S, то можно воспользоваться таким запросом с метасимволом %: SELECT au_lname FROM authors WHERE au_lname LIKE 'S%' В этом запросе "S%" означает, что нужно возвращать все строки с именами, первой буквой которых будет S, а за ней может следовать произвольное количества букв.

Чтобы извлечь информацию об авторе, идентификатор которого начинается с числа 724, и, зная, что все идентификаторы авторов имеют формат как у номеров социального страхования (три цифры, тире, затем две цифры, затем еще тире, и затем четыре цифры), вы можете воспользоваться метасимволом "_": SELECT * FROM authors WHERE au_id LIKE ‘ 724 -__-____’ GO В набор результатов попадут две строки со следующими значениями au_id: 724 -08 -9931 и 724 -80 -9391.

Применение метасимвола []. Чтобы получить имена авторов, начинающиеся на букву от A до M, можно воспользоваться метасимволом [] в сочетании с метасимволом %, вот так: SELECT au_lname FROM authors WHERE au_lname LIKE ‘[A-M]%’ GO Набор результатов будет содержать 14 строк с именами, начинающимися на букву от A до M.

Если в этом запросе поменять метасимвол [] на [^], то будут выданы строки, содержащие имена, начинающиеся на буквы вне диапазона от A до M: SELECT au_lname FROM authors WHERE au_lname LIKE ‘[^A-M]%’ GO Такой запрос вернет 9 строк.

Если вы применяете сортировку, чувствительную к регистру букв и желаете найти все имена в некотором диапазоне букв, независимо от их регистра, то можно применить запрос, который проверит и строчные, и прописные буквы, вот так: SELECT au_lname FROM authors WHERE au_lname LIKE "[A-M]%" OR au_lname LIKE "[a-m]%" GO

Перед ключевым словом LIKE можно помещать операцию NOT LIKE выдает строки, не соответствующие заданному условию. Например, чтобы найти названия книг, не начинающиеся со слова The, можно воспользоваться таким запросом: SELECT title FROM titles WHERE title NOT LIKE "The %" GO Этот запрос вернет 15 строк.

При помощи ключевого слова ESCAPE можно задать сопоставление шаблону, содержащему сами знаки, служащие для обозначения метасимволов (т. е. , сами символы ^, %, [, ] и _). Для этого вы должны задать после ключевого слова ESCAPE так называемый escape-символ, обозначающий, что символ, следующий за ним, следует понимать "как есть". Например, чтобы найти все строки из таблицы, имеющие символ подчеркивания в колонке title, можно применить такой запрос: SELECT title FROM titles WHERE title LIKE ‘%e_%’ ESCAPE ’e’ GO Этот запрос не вернет ни одной строки, потому что ни в одном названии книги нет символов подчеркивания.

<проверяемое_выражение>BETWEEN<начальное_выражение> AND <конечное_выражение> Ниже приведен пример запроса, в котором BETWEEN применяется для поиска названий книг с ценой от 5 до 25 долларов: SELECT price, title FROM titles WHERE price BETWEEN 5. 00 AND 25. 00 GO Этот запрос вернет 14 строк. Вместе с BETWEEN тоже можно применять NOT, тогда будут искаться строки, не входящие в заданный диапазон. Например, чтобы найти названия книг с ценами вне диапазона от 20 до 30 долларов (а именно, книг дешевле 20 и дороже 30 долларов), можно было бы применить такой запрос: SELECT price, title FROM titles WHERE price NOT BETWEEN 20. 00 AND 30. 00 GO

Условие поиска - текстовые строки: SELECT au_lname FROM authors WHERE au_lname BETWEEN ‘Bennet’ AND ‘Mc. Badden’ GO Этот запрос позволяет найти фамилии авторов, которые при сортировке в алфавитном порядке попали бы между фамилиями "Bennet" и "Mc. Badden". Поскольку границы диапазона BETWEEN считаются входящими в проверяемый диапазон, то фамилии "Bennet" и "Mc. Badden" тоже войдут в набор результатов запроса (эти фамилии имеются в таблице).

Пример: в примере даты заданы в строковом формате вида 'yyyymmdd' (четыре знака - для года, два - для месяца и два - для дня). SELECT title_id, title, pubdate FROM titles WHERE pubdate BETWEEN '19910601' AND '19910630'

Ключевое слово IS NULL используется в условиях поиска, ищущих строки, содержащие null-значение в заданной колонке. Например, чтобы найти в таблице titles названия книг, у которых нет данных в колонке notes (примечания), т. е. когда значением колонки notes является NULL, можно воспользоваться таким запросом: SELECT title, notes FROM titles WHERE notes IS NULL GO Этот запрос выдаст такой набор результатов: title notes ----------------------The Psychology of Computer Cooking NULL Как видите, null-значения в колонке notes в наборе результатов отображаются словом NULL.

Чтобы найти названия книг, у которых имеются данные о колонке notes (т. е. , у которых значение колонки notes не является null-значением), применяйте конструкцию IS NOT NULL, вот так: SELECT title, notes FROM titles WHERE notes IS NOT NULL GO Набор результатов будет содержать 17 строк, в каждой из которых в колонке notes будут иметься символы (один или несколько), т. е. не null-значения.

<проверяемое_выражение> IN (<подзапрос>) или <проверяемое_выражение> IN (<список_значений>) Этот оператор часто применяется в выражении WHERE для задания некоторого множества значений, элементы которого должны присутствовать в возвращаемых строках. Например, можно выбрать фамилии и индексы штата для всех авторов, которые живут в штатах Юта и Теннесси. SELECT au_lname, state FROM authors WHERE state IN ('UT', 'TN')

Благодаря ключевому слову IN ваш запроса В приведенном ниже примере запрос проще и понятней для восприятия, чем ключевое слово IN применяется в запрос, который получился, если бы вы сочетании со списком значений для поиска применили две операции OR, вот такой: трех идентификаторов должностей для описаний должностных обязанностей: SELECT job_id FROM jobs WHERE job_desc = "Operations Manager" FROM jobs OR job_desc = "Marketing Manager" WHERE job_desc IN (‘Operations Manager’, ‘Marketing "Designer" OR job_desc =Manager’, ‘Designer’) GO GO

В следующем запросе ключевое слово IN используется в одном операторе дважды – первый раз с подзапросом, а второй раз – со списком значений внутри подзапроса: Сначала будет искаться подзапрос (в нашем примере – набор значений job_id). Значения job_id получаются как результаты подзапроса и не выводятся на экран, они используются внешним запросом как выражения для его собственного условия поиска IN. Окончательный набор результатов будет содержать имена и фамилии всех сотрудников, должности которых называются Operations Manager, Marketing Manager или Designer. Набор результатов будет таким (запрос вернет 11 строк):

IN можно применять в сочетании с операцией NOT. Например, чтобы вернуть имена всех издательств, кроме находящихся в Калифорнии, Техасе и Иллинойсе, запустите такой запрос: SELECT pub_name FROM publishers WHERE state NOT IN ( "CA", "TX", "IL") GO Этот запрос вернет пять строк, у которых значение в колонке state (штат) не является обозначением ни одного из трех штатов, указанных в списке значений.

Предложение GROUP BY применяется после предложения WHERE и означает, что строки набора результатов должны быть сгруппированы в соответствии с данными в колонке группировки. Если в предложении SELECT используется агрегатная функция, то для каждой группы вычисляется и отображается в выводе итоговое агрегатное значение. Агрегатная функция выполняет вычисления и возвращает значение.

Предложение GROUP BY особенно полезно, когда в предложении SELECT имеется агрегатная функция. Рассмотрим пример оператора SELECT, применяющего предложение GROUP BY для получения сведений об общем количестве проданных книг для каждого из названий книг: SELECT title_id, SUM(qty) FROM sales GROUP BY title_id GO Будет выдан набор результатов, содержащий 16 строк:

Возможна вложенная ("гнездовая") группировка, при которой в предложении GROUP BY задается более одной колонки. При вложенной группировке набор результатов будет группироваться по каждой из колонок, участвующих в группировке, в том порядке, в котором были заданы колонки. Например, чтобы узнать средние цены книг, сгруппированных сначала по типу, а затем по издательству, можно выполнить такой запрос:

Предложение GROUP BY можно применять с необязательным ключевым словом ALL, означающим, что в набор результатов должны быть включены все группы, даже не соответствующие условию поиска. Группы, не имеющие строк, соответствующих условию поиска, будут содержать в итоговой колонке значение NULL, поэтому их будет сразу видно. Например, чтобы узнать среднюю цену для книг, имеющих авторские отчисления 12%, а также показать в наборе результатов строки для книг, имеющих авторские отчисления не 12% (у них в итоговой колонке будет значение NULL), группируя книги сначала по типам, а затем по идентификатору издательства, выполните такой запрос: Будут выведены строки для всех типов книг, но для типов книг, у которых не имеется книг с 12 -процентными авторскими отчислениями, появится NULL.

Если убрать ключевое слово ALL, то набор результатов будет содержать информацию только для тех типов книг, у которых имеются книги с 12 -процентными авторскими отчислениями. Набор результатов будет содержать 2 строки и будет таким: Предложение GROUP BY часто применяется в сочетании с предложением HAVING.

Предложение HAVING применяется, чтобы задать условия поиска для групп или для агрегатной функции. Предложение HAVING чаще всего используется после предложения GROUP BY в случаях, когда условие поиска должно проверяться уже после группировки результатов. Если условие поиска можно было бы проверить до группировки, то гораздо эффективней было бы поместить его в предложение WHERE, а не пользоваться предложением HAVING (за счет этого уменьшилось бы количество строк, участвующих в группировке). Если предложение GROUP BY отсутствует, то HAVING может применяться только в отношении агрегатной функции в списке выборки. В этом случае предложение HAVING действует точно так же, как предложение WHERE. Если попытаться использовать HAVING как-нибудь по-другому, то SQL Server выдаст сообщение об ошибке. Предложение HAVING имеет такой синтаксис: HAVING <условие_поиска> Здесь условие_поиска имеет такой же смысл, что и условие поиска. Единственным различием между предложениями HAVING и WHERE является то, что предложение HAVING может содержать агрегатную функцию в условии поиска, а предложение WHERE – нет. Примечание. Агрегатные функции можно применять в предложениях SELECT и HAVING, но не в предложении WHERE.

Ниже приведен пример запроса, использующего предложение HAVING для поиска книг, сгруппированных по типам и по издательствам, средняя цена на которые превышает 15 долларов:

В предложениях HAVING можно употреблять логические операции. Ниже показан несколько измененный последний пример, в нем теперь применяется логическая операция AND:

Чтобы применять HAVING без предложения GROUP BY, нужно иметь агрегатную функцию в списке выборки и в предложении HAVING. Например, чтобы выводить сумму цен на книги типа mod_cook (современная кулинария), только в тех случаях, когда эта сумма будет превышать 20 долларов, можно применять такой запрос: Если в этом запросе поместить выражение SUM(price) > 20 в предложение WHERE, то SQL Server выдаст сообщение об ошибке, т. к. в предложениях WHERE агрегатные функции применять нельзя.

Примечание. Помните, что предложение HAVING можно применять, только когда вы добавляете условие поиска, проверяющее результаты группировки при помощи предложения GROUP BY или проверяющее результат агрегатной функции. В остальных случаях условия поиска следует задавать в предложениях WHERE.

Необязательное выражение ORDER BY copтирует выборку по указанному столбцу (или столбцам) в порядке возрастания (ключевое слово ASC) или убывания (DESC). По умолчанию принята сортировка по возрастанию (от А до Z, от 0 до 9). В следующем примере выбираются все столбцы таблицы авторов, которые затем сортируются по фамилиям в порядке убывания: SELECT * FROM authors ORDER BY au_lname DESC

Если вы хотите отсортировать результаты для более чем одной колонки, то просто добавьте в предложение ORDER BY имена колонок, разделяя их запятыми. Ниже приведен пример запроса, выдающего идентификаторы должностей, фамилии и имена сотрудников, сортирующего вывод сначала по идентификатору должности, затем по имени, а затем по фамилии: SELECT job_id, lname, fname FROM employee ORDER BY job_id, lname, fname GO Сортировка по именам (не фамилиям) людей в этом запросе не влияет на набор результатов, потому что там нет даже двух людей с одинаковыми фамилиями и идентификаторами должности.

Рассмотрим предложение ORDER BY, работающее совместно с предложением GROUP BY и агрегатной функцией: SELECT type, pub_id, AVG(price) AS "Средняя цена" FROM titles GROUP BY type, pub_id ORDER BY type GO Набор результатов (8 строк) будет выглядеть так: Результаты отсортированы в алфавитном порядке (возрастающем) по типу книг. Также обратите внимание, что в этом запросе в предложении GROUP BY должны присутствовать и type, и pub_id, потому что они не являются частью агрегатной функции. Если вы не зададите колонку pub_id в предложении GROUP BY, то SQL Server выдаст сообщение об ошибке.

UNION считается не предложением (clause), а операцией (operator). Она используется для объединения результатов двух или нескольких запросов в один набор результатов. Применяя UNION, необходимо соблюдать два следующих правила: • все запросы должны иметь одинаковое количество колонок; • типы данных соответственных колонок из запросов должны быть совместимыми. Ниже дан пример применения операции UNION, соединяющей наборы результатов двух операторов SELECT, выдающих колонки city и state из обеих таблиц publishers и stores: SELECT city, state FROM publishers UNION SELECT city, state FROM stores GO Набор результатов (14 строк) будет таким:

Если вы хотите создать алиас для заголовка, то поместите его в первый оператор SELECT, вот так: SELECT city AS "Все города", state AS "Все штаты’ FROM publishers UNION SELECT city, state FROM stores GO

Функция AVG COUNT Описание Возвращает среднее арифметическое для значений выражения; null-значения игнорируются Возвращает количество элементов в выражении (равное количеству строк) COUNT_BIG То же самое, что и COUNT, но результат имеет тип данных bigint, а не int GROUPING Возвращает специальную дополнительную колонку; применяется, только когда предложение GROUP BY содержит операцию CUBE или ROLLUP. Для дополнительной информации откройте в SQL Server Books Online вкладку Index (Предметный указатель) и откройте тему "GROUPING keyword" MAX Возвращает максимальное значение из значений выражения MIN Возвращает минимальное значение из значений выражения STDEV Возвращает статистическое стандартное отклонение (statistical standard deviation) для всех величин выражения. Эта функция предполагает, что выражения, используемые в расчете, являются образцом всей совокупности данных STDEVP Возвращает статистическое стандартное отклонение для всех величин выражения. Эта функция предполагает, что выражения, используемые в расчете, являются всей совокупностью данных SUM Возвращает сумму всех значений выражения VAR Возвращает статистическое отклонение (statistical variance) для всех значений из выражения. Эта функция предполагает, что выражения, используемые в расчете, являются образцом всей совокупности данных VARP Возвращает статистическое отклонение для всех значений из выражения. Эта функция предполагает, что выражения, используемые в расчете, являются всей совокупностью данных

Функция COUNT применяется специальным образом: она подсчитывает все строки таблицы. Для этого нужно после COUNT поместить символ-звездочку в скобках, вот так SELECT COUNT(*) FROM publishers GO Набор результатов будет таким: -----------

Как правило, применяются понятные названия агрегатных функций. Ниже приведен пример, в котором используются сразу две агрегатные функции MAX и MIN, здесь вычисляется разница цен между самой дорогой и самой дешевой книгами: SELECT MAX(price) - MIN(price) AS "Разница цен" FROM titles GO Набор результатов будет таким: Разница цен --------------- 19. 96

В следующем примере функция SUM применяется, чтобы найти общее количество книг заказанных в каждом из магазинов: SELECT stores. stor_name, SUM(sales. qty) AS "Заказано всего" FROM sales, stores WHERE sales. stor_id = stores. stor_id GROUP BY stor_name GO Набор результатов (6 строк) будет таким:

Добавить новую запись в таблицу: INSERT INTO <имя_таблицы> [(<имя_столбца>, . . . ) ] VALUES (<значение>, . . ) Список столбцов в данной команде не является обязательным параметром. В этом случае должны быть указаны значения для всех полей таблицы в том порядке, как эти столбцы были перечислены в команде CREATE TABLE, например: INSERT INTO publishers VALUES (16, "Microsoft Press", "http: //www. microsoft. com")

SQL UPDATE оператор языка SQL позволяющий обновить значения столбцов, либо любого другого значения.

Общий вид команды UPDATE [top(x)] <объект> SET <присваивание 1 [, присваивание 2, . . . ]> [WHERE <условие>] [OPTION <хинт1 [, хинт2, . . . ]>

• top(x) — команда выполнится только х раз • <объект> — объект, над которым выполняется действие (таблица или представление (views)) • <присваивание> — присваивание, которое будет выполняться при каждом выполнении условия <условие>, или для каждой записи, если отсутствует раздел where • <условие> — условие выполнения команды • <хинт> — инструкция программе как исполнить запрос

Person UPDATE Person SET First. Name = 'Александр‘ WHERE Last. Name = 'Петров'

Person UPDATE Person SET Address = 'Ленинградская 4', City = 'Магнитогорск' WHERE Last. Name = 'Петров' Ленинградская 4 Магнитогорск

DELETE FROM <имя_таблицы> [ WHERE <условие> ] Удаляются все записи, удовлетворяющие указанному условию. Если ключевое слово WHERE и условие отсутствуют, из таблицы удаляются все записи. Пример: DELETE FROM publishers WHERE publisher = "Super Computer Publishing’ Эта команда удаляет запись об издательстве Super Computer Publishing.

Спасибо за внимание