Архив / Пособие_SQL

ПетрГУ, кафедра прикладной математики и кибернетики

Конспект лекций по дисциплине «Базы данных»

Тема: Язык SQL

составитель: Л. В. Щёголева

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

1

ПетрГУ, кафедра прикладной математики и кибернетики

§ 1 Введение

Сфера применения баз данных имеет тенденцию к постоянной интеграции. Это обстоятельство привело к необходимости создания стандартного языка, пригодного для использования на множестве вычислительных платформ. Наличие такого языка дает возможность пользователям, освоив один набор команд, успешно применять его независимо от конкретных программных систем и ЭВМ. В настоящее время таким стандартом «де-факто» стал язык SQL – наиболее мощный и популярный язык манипулирования данными, который поддерживается практически всеми существующими системами управления базами данных, в том числе такими, как Oracle, Informix, Ingress, SQL Server и др. (Стандарты: ISO/IEC 9075:1987, ISO/IEC 9075:1989, ISO/IEC 9075:1992, ISO/IEC 9075:1999, ISO/IEC 9075:2000, ISO/IEC 9075:2003, ISO/IEC 9075:2008).

SQL – Structured Query Language (структурированный язык запросов) был разработан фирмой IBM и, оказавшись удачным, впоследствии принят многими производителями программных средств, в качестве языка управления реляционными базами данных.

Помимо манипулирования данными, SQL реализует и другие возможности:

позволяет определять и модифицировать данные;

обеспечивает синхронизацию обработки БД различными приложениями;

координирует работу конкурирующих приложений, обеспечивая их одновременную работу с БД;

обеспечивает защиту данных от несанкционированного доступа;

позволяет устанавливать ограничения целостности БД

и другие.

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

§ 2	Типы данных
Char[(<длина>)]		Строка текста, длина – максимальное
		количество символов.
Bit[(<длина>)]		Двоичные данные, длина – размер в битах
Numeric[(<точность>[,<масштаб>])]		Вещественное число
Decimal[(<точность>[,<масштаб>])]		Вещественное число
Dec[(<точность>[,<масштаб>])]		Вещественное число
Integer		Целое число
Float[(<точность>)]		Вещественное	число	в	экспоненциальной
		форме, точность – минимальное количество
		знаков.
Real		Вещественное	число	в	экспоненциальной
		форме, точность зависит от реализации.
Double precision		Вещественное	число	в	экспоненциальной
		форме, точность зависит от реализации.
Date		Дата

<точность> – общее число значащих цифр <масштаб> – число значащих цифр после запятой <длина> – размер

Для преобразования типов данных предназначена команда:

CAST ( { <выражение> | NULL } AS { <тип данных> | <домен> } )

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

2

ПетрГУ, кафедра прикладной математики и кибернетики

§ 3 Команды определения данных

§ 3.1 Домены

§ 3.1.1 Создание домена

Create Domain <имя домена> [As] <тип данных> [ Default <значение по умолчанию> ]

[ <ограничение> ] Создает домен, определяющий альтернативный тип данных.

<ограничение> = CHECK(<предикат>)

Функция задает ограничение на значения домена – только те, которые удовлетворяют предикату. В предикате можно использовать ключевое Value слово для обозначения элемента множества.

Пример

Create Domain DomYear As Integer Check((Value>0)and(Value<8))

Команда создает домен DomYear для года обучения в университете (курс).

§3.1.2 Изменение домена

Вдомене можно добавить или удалить значение по умолчанию и добавить или удалить ограничение.

Alter Domain <имя домена>

{Set Default <значение по умолчанию> |

Drop default |

Add <ограничение> |

Drop constraint <имя ограничения> }

Команда изменяет домен.

Пример

Alter Domain DomYear Add Default 1

Команда устанавливает для домена DomYear значение по умолчанию равное единице (первый

курс).

§ 3.1.3 Удаление домена

Drop Domain <имя домена> [ { Cascade | Restrict } ]

Команда удаляет домен из базы данных.

Если указана опция «Cascade», то вместе с объектом будут удалены все объекты БД, с ним связанные.

Если указана опция «Restrict», то объект будет удален только в том случае, если он не связан ни с каким другим объектом.

Если опция не указана, то объект будет удален вне зависимости от других объектов, при этом могут быть нарушены некоторые связи.

В данном случае в качестве объекта выступает домен.

Пример

Drop Domain DomYear Restrict

Команда удаляет домен DomYear из базы данных. При этом если этот домен используется в какойлибо таблице, то он не будет удален.

§ 3.2 Таблицы

§ 3.2.1 Создание таблицы

Create Table <имя таблицы> (<описание столбца>, …,<описание ограничения таблицы>, … )

где

<описание столбца> = <имя столбца> {<тип данных>|<имя домена>} <ограничение столбца>

Команда создает таблицу. В круглых скобках через запятую записываются описания столбцов, после чего через запятую записываются ограничения таблицы.

Ограничение столбца

Primary Key – первичный ключ,

Not Null – обязательность заполнения значений столбца,

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

3

ПетрГУ, кафедра прикладной математики и кибернетики

Check(<предикат>) – ограничения на значения столбца,

Unique – уникальность всех значений столбца.

Ограничение таблицы

Primary Key(<имя столбца>, …) – первичный ключ, Check(<предикат>) – ограничение на значения кортежа таблицы,

Foreign Key <имя столбца> references <имя таблицы>(<имя столбца>) – внешний ключ.

Пример

Create Table tblStudent (

intStudentNumber integer not null primary key, txtStudentName char(45) not null, intStudyYear domYear)

Create Table tblCompetition ( intStudentNumber integer not null, intAttempt integer not null, fltResult decimal(5,2),

Primary Key (intStudentNumber, intAttempt),

Foreign Key (intStudentNumber) references tblStudent(intStudentNumber))

Первая таблица содержит информацию о студентах: номер студента, ФИО, год обучения. Вторая таблица содержит информацию о результатах соревнований, в которых участвуют студенты: номер студента, номер попытки, результат попытки. Во второй таблице ключ является составным, поэтому он записывается как ограничение для таблицы, атрибут intStudentNumber является внешним ключом, который также записывается как ограничение таблицы.

§ 3.2.2 Удаление таблицы

Drop Table <имя таблицы> [ { Cascade | Restrict } ]

Команда удаляет таблицу из базы данных вместе со всеми записями таблицы.

§ 3.2.3 Изменение структуры таблицы

Можно добавить новый или удалить существующий в таблице столбец, добавить новое или удалить существующее ограничение для таблицы.

Alter Table <имя таблицы>

{ Add [column] <описание столбца> | Drop [column] {Cascade | Restrict} | Add <ограничение таблицы> |

Drop Constraint <имя ограничения> {Cascade | Restrict}}

Команда изменяет структуру таблицы.

Пример

Alter Table tblStudent Add column txtDepartment Char(25)

Команда добавляет в таблицу tblStudent новый столбец txtDepartment.

§ 4 Ограничения

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

§ 4.1 Создание ограничения

Create Assertion <имя ограничения> Check(<предикат>)

Команда создает ограничение.

Пример

Create Assertion AssStudentCount Check( 30 > Select Count(*) From tblStudent)

Команда создает ограничение на количество записей в таблице tblStudent.

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

4

ПетрГУ, кафедра прикладной математики и кибернетики

§ 4.2 Удаление ограничения

Drop Assertion <имя ограничения>

Команда удаляет ограничение из базы данных.

§ 5 Команды обновления данных

Операции обновления данных предназначены для работы с кортежами таблицы и включают добавление, удаление и изменение.

§ 5.1 Операция добавление

Insert into <имя таблицы> ( <имя столбца>, … ) values ( <значение>, … ), ( <значение>, … ) Команда добавляет один или несколько кортежей в таблицу.

Пример

Insert into tblStudent ( txtStudentName, intStudentNumber) values (‘Иванов А. А.’, 123456), (‘Петров С. Е.’, 123457)

Команда добавляет два новых кортежа в таблицу tblStudent.

Можно добавить записи, полученные в результате запроса.

Insert into <имя таблицы> ( <имя столбца>, … ) <Select - запрос>

Пример

Insert into tblStudent (txtStudentName, intStudyYear) Select txtFIO, 1 From tblAbitur Where txtStatus

= ’зачислен’

Команда добавляет в таблицу tblStudent информацию из таблицы tblAbitur. Из таблицы tblAbitur выбираются только ФИО абитуриентов, которые были зачислены в университет. Для каждого такого абитуриента устанавливается первый курс. ФИО и номер курса записываются соответственно в столбцы txtStudentName, intStudyYear таблицы tblStudent.

§ 5.2 Операция удаление

Delete from <имя таблицы> [Where <условие>]

Команда удаляет из таблицы записи, удовлетворяющие условию. Если условие отсутствует, то удаляются все записи из таблицы, но сама таблица не удаляется.

Пример

Delete from tblStudent Where intStudyYear=5

Команда удаляет из таблицы информацию о пятикурсниках.

Delete from tblStudent

Команда удаляет все записи из таблицы.

Вусловии можно использовать подзапросы.

§5.3 Операция изменение

Update <имя таблицы> Set <имя столбца> = <значение>, … [Where <условие>]

Команда изменяет значения в указанных столбцах для записей, удовлетворяющих условию. Если условие отсутствует, то изменения применяются ко всем записям таблицы.

Пример

Update tblStudent Set intStudyYear = 2 Where year = 1

Команда переводит первокурсников на второй курс.

Update tblStudent Set intStudyYear = intStudyYear + 1

Команда изменяет все записи в таблице.

В условии можно использовать подзапросы.

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

5

ПетрГУ, кафедра прикладной математики и кибернетики

§6 Команда выборки данных

§6.1 Таблицы для демонстрации работы команды выборки данных

Для демонстрации выполнения					запросов					определим три таблицы: «детали», «поставщики» и
«поставки».
	d – «детали»
	id_d			dname				color				weight				city
	1			винт				красный				12				Москва
	2			болт				зеленый				17				СПб
	3			гайка				синий				17				Тула
	4			гайка				красный				14				Москва
	5			шайба				синий				12				СПб
	6			гвоздь				красный				19				Москва
	7			болт				зеленый				12				Тула
		p – «поставщики»
		id_p					pname				status			city
		1					Лосев				20			Москва
		2					Мишин				10			СПб
		3					Носов				30			СПб
		4					Титов				20			Москва
		5					Песков				30			Киев
							dp – «поставки»
							id_p			id_d		n
							1			1		100
							1			5		200
							2			3		300
							3			2		200
							3			5		100
							3			1		200
							4			4		100
							4			5		300

Ключевые атрибуты выделены жирным шрифтом. В таблице dp атрибут id_p является внешним ключом, ссылающимся на отношение p. В таблице dp атрибут id_d является внешним ключом, ссылающимся на отношение d.

§ 6.2 Команда выборки данных

Select <выражение> From <таблицы>

[ Where <условие> ]

[ Group by <выражение> [ Having <условие> ] ]

[ Order by <выражение> ]

§ 6.3 Примеры запросов

1. Получить цвет и название города для деталей не из Санкт-Петербурга весом более 10 грамм.

Select d.color, d.city From d

Where (d.city <> ’СПб’) and (d.weight > 10)

color	city
красный	Москва
синий	Тула
красный	Москва
красный	Москва
зеленый	Тула

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

6

ПетрГУ, кафедра прикладной математики и кибернетики

Результирующая таблица содержит 2 столбца с названиями «color», «city» и 5 кортежей, два из которых – дубликаты.

Результат SQL запроса может содержать повторяющиеся кортежи!

Чтобы исключить повторения нужно добавить опцию distinct перед списком атрибутов

Select distinct d.color, d.city From d

Where (d.city <> ’СПб’) and (d.weight > 10)

color	city
синий	Тула
красный	Москва
зеленый	Тула

Результирующая таблица будет содержать 3 кортежа, однако порядок кортежей неизвестен.

Порядок кортежей в результирующей таблице не определен.

Чтобы задать порядок необходимо добавить опцию Order by. Select distinct d.color, d.city

From d

Where (d.city <> ’СПб’) and (d.weight > 10) Order by d.color

Кортежи в результирующей таблице будут упорядочены по возрастанию названий цветов. Для того чтобы задать обратный порядок сортировки, необходимо добавить после названия столбца DESC для сортировки по убыванию или ASC – для сортировки по возрастанию (задается по умолчанию).

Можно упорядочить по нескольким столбцам:

Select distinct d.color, d.city From d

Where (d.city <> ’СПб’) and (d.weight > 10) Order by d.city asc, d.color desc

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

color	city
красный	Москва
синий	Тула
зеленый	Тула

Замечание если запрос выполняется для одной таблицы можно не указывать название таблицы пред названием столбца.

Select distinct color, city From d

Where (city <> ’СПб’) and (weight >10) Order by color desc, city asc

2. Получить номер детали и вес детали в килограммах.

Select id_d, weight / 1000 From d

После Select можно указать не только названия столбцов, но и арифметические выражения, а также константы.

Название второго столбца неопределенно, можно задать его явно.

Select id_d, weight / 1000 as Kg From d

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

7

ПетрГУ, кафедра прикладной математики и кибернетики

id_d	Kg
1	0,012
2	0,017
3	0,017
4	0,014
5	0,012
6	0,019
7	0,012

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

Select id_d as First, weight / 1000 as Second, ‘вес’ as Third

From d

First	Second	Third
1	0,012	вес
2	0,017	вес
3	0,017	вес
4	0,014	вес
5	0,012	вес
6	0,019	вес
7	0,012	вес

3. Получить для каждого поставщика его номер, имя, статус и город.

Select id_p, pname, status, city From p

Перечень всех столбцов таблицы можно заменить символом «*».

Select *

From p

В результате выполнения запроса мы получим целиком таблицу p.

Это самый короткий запрос на языке SQL, но более медленный по сравнению с перечислением всех столбцов.

4. Получить номер детали, название детали и объем поставки для поставок, выполняемых поставщиками с номерами, не превосходящими значение 3.

Select d.id_d, d.dname, dp.n From d, dp

Where (d.id_d = dp.id_d) and (dp.id_p <= 3)

id_d	dname	n
1	винт	100
5	шайба	200
3	гайка	300
2	болт	200
5	шайба	100
1	винт	200

Если запрос выполняется по нескольким таблицам, то в нем обязательно должно присутствовать условие на соединение таблиц (d.id_d = dp.id_d), а потом уже все остальные условия запроса.

В данном примере соединение выполняется по внешнему ключу отношения «поставки». Операцию соединения можно записать иначе.

Select d.id_d, d.dname, dp.n

From d join dp using (d.id_d = dp.id_d) Where dp.id_p <= 3

5. Получить номер, название, город для детали и номер, имя и город для поставщика для поставщиков и деталей, находящихся в одном городе.

Select d.id_d, d.city, p.id_p, p.city From d, p

Where p.city = d.city

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

8

ПетрГУ, кафедра прикладной математики и кибернетики

id_d	dname	d.city	id_p	pname	p.city
1	винт	Москва	1	Лосев	Москва
1	винт	Москва	4	Титов	Москва
2	болт	СПб	2	Мишин	СПб
2	болт	СПб	3	Носов	СПб
4	гайка	Москва	1	Лосев	Москва
4	гайка	Москва	4	Титов	Москва
5	шайба	СПб	2	Мишин	СПб
5	шайба	СПб	3	Носов	СПб
6	гвоздь	Москва	1	Лосев	Москва
6	гвоздь	Москва	4	Титов	Москва

Можно записать этот запрос иначе:

Select *

From d join p using (p.city = d.city)

Такое соединение называется внутренним, кроме него можно выполнить:

left join – левое соединение содержит все кортежи из левой таблицы, если для кого-то кортежа нет пары в правой таблице, то соответствующие столбцы заполняются значением «Null»;

right join – правое соединение содержит все кортежи из правой таблицы, если для кого-то кортежа нет пары в левой таблице, то соответствующие столбцы заполняются значением «Null»;

union join – внешнее соединение содержит кортежи из левой таблицы, для которых отсутствуют пары в правой таблице, и кортежи из правой таблицы, для которых отсутствуют пары в левой таблице, отсутствующие данные заполняются значением «Null»;

full join – полное соединение содержит все кортежи из левой таблицы и все кортежи из правой таблицы, отсутствующие данные заполняются значением «Null»;

cross join – декартово произведение содержит все возможные комбинации кортежей из двух таблиц.

Примеры

Левое соединение:

Select id_d, dname, id_p, pname

From d left join p using (p.city = d.city)

id_d	dname	id_p	pname
1	винт	1	Лосев
1	винт	4	Титов
2	болт	2	Мишин
2	болт	3	Носов
3	гайка	Null	Null
4	гайка	1	Лосев
4	гайка	4	Титов
5	шайба	2	Мишин
5	шайба	3	Носов
6	гвоздь	1	Лосев
6	гвоздь	4	Титов
7	болт	Null	Null

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

9

ПетрГУ, кафедра прикладной математики и кибернетики

Полное соединение:

Select id_d, dname, id_p, pname

From d full join p using (p.city = d.city)

id_d	dname	id_p	pname
1	винт	1	Лосев
1	винт	4	Титов
2	болт	2	Мишин
2	болт	3	Носов
3	гайка	Null	Null
4	гайка	1	Лосев
4	гайка	4	Титов
5	шайба	2	Мишин
5	шайба	3	Носов
6	гвоздь	1	Лосев
6	гвоздь	4	Титов
Null	Null	5	Песков
7	болт	Null	Null

Внешнее соединение:

Select id_d, dname, id_p, pname

From d union join p using (p.city = d.city)

id_d	dname	id_p	pname
3	гайка	Null	Null
Null	Null	5	Песков
7	болт	Null	Null

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

Select distinct p.city as first, d.city as second From p, d, dp

Where (p.id_p = dp.id_p) and (dp.id_d = d.id_d) Order by first, second desc

first	second
Москва	СПб
Москва	Москва
СПб	Тула
СПб	СПб
СПб	Москва

Соединение двух таблиц требует одного условия для соединения. Соединение трех таблиц требует двух условий для соединения. Таким образом, при выборке из n таблиц необходимо (n–1) условий для соединения.

7. Получить пары номеров поставщиков, таких, что оба поставщика в каждой паре проживают в одном и том же городе.

Select first.id_p, second.id_p From p as first, p as second

Where (first.city = second.city) and (first.id_p < second.id_p)

first.id_p	second.id_p
1	4
2	3

8. Получить общее количество поставщиков.

Select count(*) From p

Конспект лекций по дисциплине «Базы данных» (Язык SQL)

10