«Элементы языка SQL и запросы в форме SQL» Преподаватель: Французова Г.Н. - презентация

1 «Элементы языка SQL и запросы в форме SQL» Преподаватель: Французова Г.Н.

2 Операторы языка SQL для работы с реляционной базой данных

3 1. Создание реляционных таблиц. Создание реляционной базы данных означает спецификацию состава полей: указание имени, типа и длины каждого поля (если это необходимо). Каждая таблица имеет уникальное имя.

4 Синтаксис оператора создания новой таблицы: CREATE TABLE таблица (поле! тип [(размер)] [индекс!] [, поле 2 тип [(размер)] [индекс 2] [,...]] [, составной индекс[,...]])

5 где таблица имя создаваемой таблицы; поле!, поле 2 имена полей таблицы; тип тип поля; размер размер текстового поля; индекс 1, индекс 2 директивы создания простых индексов; составной индекс директива создания составного индекса.

6 Каждый индекс имеет уникальное в пределах данной таблицы имя. Для создания простого индекса используется фраза (помещает­ся за именем поля): CONSTRAINT имя индекса {PRIMARY KEY|UNIQUE| REFERENCES внешняя таблица [(внешнее поле)]}

7 Директива создания составного индекса (помешается в любом месте после определения его элементов): CONSTRAINT имя {PRIMARY KEY (ключевое 1[, ключевое 2 [,...]]) [UNIQUE (уникальное 1[,...]]) | FOREIGN KEY (ссылка 1[, ссылка 2[,...]]) REFERENCES внешняя таблица [(внешнее полеЦ, внешнее поле 2[,...]])]}

8 Служебные слова: UNIQUE уникальный индекс (в таблице не может быть двух записей, имеющих одно и то же значение полей, входящих в индекс); PRIMARY KEY первичный ключ таблицы (может состоять из нескольких полей; упорядочивает записи таблицы); FOREIGN KEY внешний ключ для связи с другими таблица­ми (может состоять из нескольких полей); REFERENCES ссылка на внешнюю таблицу.

9 Пример 2.1. CREATE TABLE Студент ([Имя] TEXT, [Фамилия] TEXT, [Дата рождения] DATETIME, CONSTRAINT Адр UNIQUE ([Имя]), [Фамилия], [Дата рождения]))

10 В результате выполнения запроса будет создана таблица СТУДЕНТ, в составе которой: два текстовых поля: Имя, Фамилия; одно поле типа дата/время Дата рождения.

11 Будет создан составной индекс с именем Адр по значениям ука­занных полей, индекс имеет уникальное значение, в таблице не мо­жет быть двух записей с одинаковыми значениями полей, образую­щих индекс.

12 2. Изменение структуры таблиц. При необходимости можно из­менить структуру таблицы: удалить существующие поля; добавить новые поля; создать или удалить индексы.

13 Все указанные действия затрагивают одновременно только одно поле или один индекс: ALTER TABLE таблица ADD{[COLUMNJncxne тип[(размер)][С(Ж5ТКА1МТ индекс] CONSTRAINT составной индекс}| DROP {[COLUMN] поле i CONSTRAINT имя индекса}} Опция ADD обеспечивает добавление поля, опция DROP удаление поля таблицы, добавление опции CONSTRAINT означает подобные действия для индексов таблицы

14 Пример 2.2. ALTER TABLE Студент ADD COLUMN [Группа] ТЕХТ(5) Для создания нового индекса для существующей таблицы мож­но использовать также команду: CREATE [UNIQUE] INDEX индекс ON таблица (поле[,...]) [WITH {PRIMARYJD1SALLOW NULL|IGNORE NULL}]

15 Фраза WITH обеспечивает наложение условий на значения по­лей, включенных в индекс: DISALLOW NULL запретить пустые значения в индексиро­ванных полях новых записей; IGNORE NULL включать в индекс записи, имеющие пустые значения в индексированных полях.

16 Пример 2.3. CREATE INDEX Гр ON Студент([группа[) WITH DISALLOW NULL

17 3. Удаление таблицы. Для удаления таблицы (одновременно и структуры, и данных) используется команда: DROP TABLE имя таблицы

18 Для удаления только индекса таблицы (сами данные не разру­шаются) выполняется команда: DROP INDEX имя индекса ON имя таблицы Пример 2.4. DROP INDEX Адр ON Студент удален только индекс Адр; DROP TABLE Студент удалена вся таблица.

19 4. Ввод данных в таблицу. Формирование новой записи в табли­ це выполняется командой: INSERT INTO таблица [(полеЦ, поле 2[,...]])] VALUES (значение![, значение 2[,...]); Указывается имя таблицы, в которую добавляют запись, состав полей, для которых вводятся значения.

20 Пример 2.5. INSERT INTO Студент ([Фамилия], [Имя], [Дата рождения]) VALUES ("Петров", "Иван", 23/3/80)

21 Возможен групповой ввод записей (пакетный режим), являю­щихся результатом выборки (запроса) из других таблиц:

22 INSERT INTO таблица [IN внешняя база данных] SELECT [источник.]поле![, поле 2[,...] FROM выражение WHERE условие Сначала выполняется оператор подзапроса SELECT, который и формирует выборку для добавления.

23

24 Пример 2.6. INSERT INTO Студент SELECT [Студент-заочник].* FROM [Студент- заочник] Все записи таблицы СТУДЕНТ- ЗАОЧНИК будут добавлены в таблицу СТУДЕНТ

25 Пример 2.7. INSERT INTO Студент SELECT [Студент-заочник].* FROM [Студент- заочник] WHERE [Дата рождения] > = # 01/01/80 # Записи таблицы СТУДЕНТ-ЗАОЧНИК добавляются в таблицу СТУДЕНТ, если дата рождения студента больше или равна ука­занной.

26 Пример 2.7. INSERT INTO Студент SELECT [Студент-заочник].* FROM [Студент- заочник] WHERE [Дата рождения] > = # 01/01/80 # Записи таблицы СТУДЕНТ-ЗАОЧНИК добавляются в таблицу СТУДЕНТ, если дата рождения студента больше или равна ука­занной.

27 5. Операции соединения таблиц. Операцию INNER JOIN можно использовать в любом предложении FROM. Она создает симмет­ричное объединение, наиболее частую разновидность внутреннего объединения.

28 Записи из двух таблиц объединяются, если связующие их поля содержат одинаковые значения: FROM таблица! INNER JOIN таблица 2 ON таблица 1. поле 1=таблица 2. поле 2

29 Данный оператор описывает симметричное соединение двух таблиц по ключам связи (поле!; поле 2). Новая запись формируется в том случае, если в таблицах содержатся одинаковые значения ключей связи.

30 Возможные варианты операции: LEFT JOIN (левостороннее) соединение выбираются все записи «левой» таблицы и только те записи «правой» таблицы, которые содержат соответствующие ключи связи; RIGHT JOIN (правостороннее) соединение выбираются все записи «правой» таблицы и только те записи «левой» таблицы, которые содержат соответствующие ключи связи.

31 Пример 2.8. SELECT Студент.*, Оценка.* FROM Студенты INNER JOIN Оценка ON Студент.[ зач.книжки] = Оценка.[ зач.книжки]; SELECT Студент.*, Оценка.* FROM Студенты LEFT JOIN Оценка ON Студент.[ зач. книжки] = Оценка.[ зач.книжки]; SELECT Студент.*, Оценка.* FROM Студенты RIGHT JOIN Оценка ON Студент.[ зач.книжки] = Оценка.[ зач.книжки]

32 В первом случае создается симметричное соединение двух таб­ лиц по полю [ зач. книжки]. Не выводятся записи, если значение их ключей связи (указанное поле) не представлено в двух таблицах

33 Во втором случае будут выведены все записи таблицы СТУДЕНТ и соответствующие им записи таблицы ОЦЕНКА. В третьем случае наоборот, все записи таблицы ОЦЕНКА и соответствующие им за­писи таблицы СТУДЕНТ.

34 Операции JOIN могут быть вложенными для последовательного соединения нескольких таблиц.

35 Пример 2.9. SELECT Студент.*, Оценка.*, Дисциплина.[Наименование дисциплины] FROM (Студент INNER JOIN (Оценка INNER JOIN (Дисциплина ON Оценка.[Код дисциплины] = Дисциплина.[Код дисциплины]) ON Студент.[ зач. книжки] = Оценка.[ зач. книжки])

36 Сначала происходит соединение таблиц ОЦЕНКА и ДИСЦИПЛИНА по ключу связи [Код дисциплины]. Соединение симметричное, то есть если коды дисциплины не совпадают, записи этих таблиц не соединяются. Затем происходит соединение таблиц СТУДЕНТ и ОЦЕНКА по ключу связи [ зач.книжки].

37 Таким образом, на выходе запроса получается результат соеди­нения трех таблиц, но при условии совпадения ключей связи.

38 6. Удаление записей в таблице. В исходной таблице можно уда­лять отдельные или все записи, охраняя при этом структуру и ин­дексы таблицы. При удалении записей в индексированной таблице автоматически корректируются ее индексы:

39 DELETE [таблица.*] FROM выражение WHERE условия отбора Полная чистка таблицы от записей и очистка индексов выпол­няются операцией: DELETE * FROM таблица

40 Пример DELETE * FROM Студент Все записи таблицы будут удалены. DELETE * FROM Студент WHERE [Дата рождения]> # #

41 Удаляются только те записи, в которых поле [Дата рождения] больше указанной даты. Данная операция удаляет записи в таблице, связанные с другой таблицей: условия удаления записей могут относиться к полям свя­занных таблиц:

42 DELETE таблица.* FROM таблица INNERJOIN другая таблица ON таблица.[поле N] = [другая таблица].[поле М] WHERE условие

43 Пример DELETE Студент.* FROM Студент INNER JOIN [Студент заочник] ON Студент.[Группа] = [Студент заочник].[Группа]

44 Удаляются записи в таблице СТУДЕНТ, для которых имеются связанные записи в таблице СТУДЕНТ-ЗАОЧНИК.

45 7. Обновление (замена) значений полей записи. Можно изменить значения нескольких полей одной или группы записей таблицы, удовлетворяющих условиям отбора:

46 UPDATE таблица SET новое значение WHERE условия отбора Новое значение указывается как имя поля=новое значение

47 Пример UPDATE Студент SET [Группа] = "1212" WHERE [Фамилия] LIKE 'В*' AND [Дата рождения] < = #01/01/81#

48 Студентов, чьи фамилии начинаются на букву В и дата рожде­ния не превышает указанной, перевести в группу 1212.

49 UPDATE Студент INNER JOIN [Студент заочник] ON Студент.[Еруппа] = [Студент заочник].[Еруппа] SET [Группа] = [Группа]&"а"

50 В таблице СТУДЕНТ изменяются номера групп путем добавле­ния буквы а, если они встречаются в таблице СТУДЕНТ-ЗАОЧНИК.

51 Организация запросов в форме SQL Синтаксис оператора SELECT. Выборка с помощью оператора SELECT наиболее частая ко­манда при работе с реляционной базой данных. Этот оператор обла­дает большими возможностями по заданию структуры выходной ин­формации, указанию источников входной информации, способа упорядочения выходной информации, формированию новых значе­ний и т. п. (табл. 2.5).

52 При выполнении выборки могут формироваться и новые дан­ные, так называемые вычисляемые поля, являющиеся результатом обработки исходных данных. Возможно упорядочение выводимых данных, формирование групп записей, подсчет групповых итогов, формирование подмножеств данных (записей), являющихся осно­вой для формирования условий по обработке следующего этапа вложенных запросов.

53 Аргумент НазначениеПредикат Предик аты используются для ограничения числа воз­вращаемых записей: ALL все записи; DISTINCT записи, различающиеся в указанных для вывода полях; DISTINCTROW полностью различающиеся записи по всем полям;

54 ТОР возврат заданного числа или процента записей в диапазоне, соответствующем фразе ORDER BYТаблица Имя таблицы, поля которой формируют выходные данные Поле 1, Поле 2Имена полей, используемых при отборе (порядок их следования определяет выходную структуру выборки данных)Псевдоним!, Псевдоним 2Новые заголовки столбцов результата выборки данных

55 FROMОпределяет выражение, используемое для задания ис­точника формирования выборки (обязательно присут­ствует в каждом операторе)Внешняя база данных Имя внешней базы данных источника данных для выборки[WHERE...]Определяет условия отбора записей (необязательное)[GROUP BY...]Указание полей (максимум 10) для формирования групп, по которым возможно вычисление групповых итогов; порядок их следования определяет виды итогов (старший, промежуточный и т. п.) необязательное[HAVING...]Определяет условия отбора записей для сгруппирован­ных данных (задан способ группирования GROUP BY...) необязательное[ORDER BY...]Определяет поля, по которым выполняется упорядоче­ние выходных записей; порядок их следования соот­ветствует старшинству ключей сортировки. Упорядо­чение возможно как по возрастанию (ASC), так и по убыванию (DESC) значения выбранного поля[WITH OWNERACCESS OPTION]При работе в сети в составе защищенной рабочей груп­пы для указания пользователям, не обладающим доста-тбчными правами, возможности просматривать ре­ зультат запроса или выполнять запрос

56

57

58 Универсальный оператор SELECT имеет следующую конст­ рукцию: SELECT [предикат] {"[таблица.* [таблица.]поле![,[таблица.) поле 2[,...]]} [AS псевдоним 1[, псевдоним 2[....]]] FROM выражение[, ] [IN внешняя база данных] [WHERE...] [GROUP BY...] [HAVING...] [ORDER BY...] [WITH OWNERACCESS OPTION]

59 Оператор SELECT реализует сложные алгоритмы запросов. Слово SELECT определяет структуру выводимой информации, это могут быть поля таблиц, вычисляемые выражения. Вычисляемое выражение состоит из: полей таблиц; констант; знаков операций; встроенных функций; групповых функций SQL.

60 Пример SELECT [Имя], [Фамилия] FROM Студент SELECT TOPS [Фамилия] FROM Студент SELECT TOPS [Фамилия] FROM Студент ORDER BY [Группа]

61 В первом случае выбираются все записи таблицы СТУДЕНТ в составе указанных полей. Если отбираются все поля в том же самом порядке, что и в структуре таблицы, можно указать символ точки.

62 Во втором случае отбирается пять первых фамилий студентов, в третьем случае выбирается пять первых фамилий студентов, упоря­ дочение записей осуществлено по учебным группам.

63 Если используются одноименные поля из нескольких таблиц, включенных в предложение FROM, следует указать перед именем такого поля имя таблицы через. (точку):

64 [Студент заочник].[Группа] и [Студент].[Группа] два одноименных поля из разных таблиц. Для изменения заголовка столбца с результатами выборки ис­пользуется служебное слово AS.

65 Пример SELECT DISTINCT [Дата рождения] AS Юбилей FROM Студент SELECT [Фамилия]*" "&[Имя] AS ФИО, [Дата рождения] AS Год FROM Студент

66 В первом случае будут выведены неповторяющиеся даты рожде­ния студентов, которые имеют новое наименование Юбилей. Во втором случае в результирующей таблице присутствуют все записи, но вместо [Дата рождения] указан Год и вместо Фамилия и Имя, соединенных вместе через пробел, ФИО. Наиболее часто слово AS применяется для именования вычис­ляемых полей.

67 Задание условий выборки Предложение WHERE может содержать выражения, связанные логическими операторами, с помощью которых задаются условия выборки (табл. 2.6).

68 2.6. Логические условия для построения условий выборки Опера­тор НазначениеОпера­ тор НазначениеОпера­ тор НазначениеANDЛогическое И или коньюнкция (логи­ческое умножение)Imp Логическая импликация выраженийOr Логическое ИЛИ, дизьюнкция (включающее Or)Eqv Проверка логиче­ской эквивалентно-сги выраженийNot ОтрицаниеХог Логическое ИЛИ (исключающее Or)

69 Кроме того, могут использоваться операторы для построения условий: LIKE выполняет сравнение строковых значений; BETWEEN...AND выполняет проверку на диапазон значений; IN выполняет проверку выражения на совпадение с любым из элементов списка; IS проверка значения на Null;

70 Условие обеспечивает «горизонтальную» выборку данных, т. е. результатом запроса будут только те записи, которые удовлетворяют сформулированным условиям.

71 Пример SELECT Студент.* FROM Студент WHERE [Дата рождения]>=# * SELECT Студент.* FROM Студент WHERE [Дата рождения]>=# * AND [Группа] IN ("1212", "1213") SELECT Студент.* FROM Студент WHERE [Дата рождения] BETWEEN # * AND # * AND [Группа] IN ("1212", "1213") SELECT Студент.* FROM Студент INNER JOIN [Студент заочник] On Студент.[Группа]= [Студент заочник].[Группа] WHERE Студент.[Дата рождения] >=# # =#01.01.79#">

72 В первом случае выбираются студенты, дата рождения которых позже Во втором случае будут отобраны все студенты, обу­чающиеся в группах 1212 или 1213 и дата рождения которых позже

73 В третьем случае выбираются студенты, дата рождения кото­рых находится в заданном диапазоне, и они обучаются в любой из указанных групп.

74 В четвертом случае выбираются студенты, кото­рые обучаются в тех же группах, что и студенты-заочники, дата ро­ждения которых позже

75 Групповые функции SQL Групповые функции необходимы для определения статистиче­ских данных на основе наборов числовых значений:

76 Avg вычисляет арифметическое среднее набора чисел, со­держащихся в указанном поле запроса; Count вычисляет количество выделенных записей в запросе; Min, Max возвращают минимальное и максимальное значе­ния из набора в указанном поле запроса;

77 StDev, StDevPs возвращают среднеквадратическое отклоне­ние генеральной совокупности и выборки для указанного поля в запросе; Sum возвращает сумму значений в заданном поле запроса;

78 Var, VarPs возвращает дисперсию распределения генераль­ной совокупности и выборки для указанного поля в запросе. Для определения полей группирования указывается ключевое слово HAVING для заданного условия по группе при вычислении групповых значений.

79 Пример SELECT Фамилия, Avg(Peзyльтaт) AS Средний балл FROM Результаты GROUP BY [ зач.книжки] SELECT [Код дисциплины], А\^(Результаты) AS Средний балл FROM Результаты GROUP BY [Код дисциплины]

80 В первом случае создается список фамилий студентов с указа­нием среднего балла по каждому студенту, во втором случае спи­сок кодов дисциплин и средний балл по дисциплине.

81 Пример SELECT Фамилия, Avg(Результат) AS Средний балл FROM Результаты GROUP BY [ зач.книжки] HAVING Avg (Результат) >4.5 SELECT [Код дисциплины], Ау§(Результат)А5 Средний балл FROM Результаты GROUP BY [Код дисциплины] HAVING Avg(Peзyльтaт)<4

82 В первом случае создается список фамилий студентов с указа­нием среднего балла по каждому студенту, выводятся фамилии те) студентов, которые имеют средний балл выше 4.5. Во втором случае выводится список кодов дисциплин со средним баллом при усло­вии, что он ниже 4.

83 Подчиненный запрос В инструкцию SELECT может быть вложена другая инструкция SELECT, SELECT...INTO, INSERT...INTO, DELETE или UPDATE. Различают основной и подчиненный запросы, которые являются вложенными в основной запрос.

84 Существуют три типа подчиненных запросов: сравнение (ANY|ALL|SOME) (инструкция); выражение [NOT] IN (инструкция); [NOT] EXISTS (инструкция).

85 Первый тип сравнение выражения с результатом подчинен­ного запроса. Ключевые слова: ANY каждый; ALL все; SOME некоторые.

86 Пример SELECT * FROM Оценка WHERE [Результат] > ANY (SELECT) [результат] FROM Оценка WHERE Результат.[ зач.книжки] ="123124")

87 Отбираются только те записи из таблицы ОЦЕНКА, в которых значение результата больше каждой оценки студента с зач. книж­ки

88 Второй тип выражение, которое должно быть найдено в на­боре записей, являющихся результатом выполнения подчиненного запроса.

89 Пример SELECT *FROM Студент WHERE [ зач.книжки] IN (SELECT [ зач.книжки] FROM Оценка WHERE [Результат]>=4) SELECT * FROM Lbcwbgkbyf WHERE [Rjl lbcwbgkbys] NOT IN (SELECT [Rjl lbcwbgkbys] FROM Jwtyrf)

90 В первом случае отбираются студенты, которые в таблице ОПЕНКА имеют результат 4 или выше.

91 Во втором случае отбирают­ся дисциплины, которые не встречаются в таблице ОЦЕНКА.

92 Третий тип инструкция SELECT, заключенная в круглые скоб­ки, с предикатом EXISTS в логическом выражении для определения, должен ли подчиненный запрос возвращать какие-либо записи.

93 Пример SELECT * FROM Студент WHERE EXISTS (SELECT * FROM Оценка WHERE Сотрудник.[ зач.книжки]= Оценка.[ зач.книжки])

94 Отбираются студенты, которые имеют хотя бы одну оценку

95 Литература:

96

97

98

99

100