SELECT ФИО, N_зачетки, Оценка
FROM Студенты, Ведомость
WHERE Студенты.N_зачетки = Ведомость. N_зачетки;
Если требуется объединить несколько таблиц, то это записывается в одном предложении WHERE c использованием логической операции AND.
Например,
выбрать сведения об оценках, полученных студентами на экзаменах, с указанием не только фамилий студентов, но и названий дисциплин.
SELECT ФИО, N_зачетки, Дисциплина, Оценка
FROM Студенты, Ведомость, Дисциплины
WHERE Студенты.N_зачетки= Ведомость. N_зачетки AND Ведомость.Код_дисциплины=Дисциплины.Код_дисциплины;
С той же целью можно применить внутреннее соединение с помощью конструкции INNER JOIN ON.В предложении FROM, вместо WHERE используется предложение ON
SELECT ФИО, N_зачетки, Оценка
FROM Студенты INNER JOIN Ведомость
ON Студенты.N_зачетки = Ведомость. N_зачетки;
А если таблиц больше двух?
Ничего страшного! В нашем примере будет:
SELECT ФИО, N_зачетки, Дисциплина, Оценка
FROM СтудентыINNER JOIN (ВедомостьINNER JOINДисциплины ON Ведомость.Код_дисциплины=Дисциплины.Код_дисциплины) ON
Студенты.N_зачетки= Ведомость. N_зачетки AND;
Ну, ОЧЕНЬ громоздко! Легко ошибиться в именах таблиц. Есть выход – для упрощения использовать псевдонимы таблиц: для таблицы Студенты – С, для таблицы Ведомость – В и для таблицы Дисциплины – Д.
SELECT ФИО, N_зачетки, Дисциплина, Оценка
FROM Студенты AS С INNER JOIN (Ведомость AS В INNER JOIN Дисциплины AS Д ON В.Код_дисциплины=Д.Код_дисциплины)
ON С.N_зачетки= В. N_зачетки;
Агрегатные функции в операторах SQL.
В SQL добавлены дополнительные функции, которые позволяют вычислять обобщенные групповые значения. Для применения агрегатных функций предполагается предварительная операция группировки. В чем состоит суть операции группировки? При группировке все множество кортежей отношения разбивается на группы, в которых собираются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки.
Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций:
COUNT - Количество строк или непустых значений полей, которые выбрал запрос
SUM- Сумма всех выбранных значений данного поля
AVG - Среднеарифметическое значение всех выбранных значений данного поля
MIN - Наименьшее из всех выбранных значений данного поля
MAX - Наибольшее из всех выбранных значений данного поля
Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.
Условный оператор и оператор цикла в SQL.
Условие: Условный оператор IF условие оператор или Begin опреатор1,…END else оператор или блок
Например:
Select №группы From группы Where №группы=@группа
if Exists (Select №группы From группы Where № группы =@группа) ELSE Print “Нет такой группы”
Цикл: WHILE (Select avg(цена) FROM товары)<100 Begin UPDATE товары SETцена=цена*1 .02
IF (Select max(цена) FROM товары)>1000 Break ELSE Continue
ПРАКТИЧЕСКОЕ ЗАДАНИЕ.
Создать запросы на выборку данных.
1. Создать запрос SQL, который позволит получить сведения обо всех оценках, выставленных преподавателями с указанием ФИО как преподавателя так и студента.
2. Получить данные по оценкам двух конкретных студентов по математике.
3. Получить данные, кто из студентов не сдавал экзамен по математике.
4. Вывести список студентов не сдавших экзамен по информатике в 2008 г.
5. Получить данные о студентах 1989 г.р. не имеющих телефона.
Создать запросыс групповыми операциями.
1. Подсчитать количество экзаменов, сданных каждым студентом.
2. Подсчитать количество пятерок для каждого студента.
3. Подсчитать средний балл по каждому предмету.
4. Найти дисциплину с максимальным количеством полученных двоек.
5. Проверьте полученные знания, выполнив тестовые задания.(См. стр. Тестовые задания. Тема 5).
Тема 6. Создание SQL запросов. Языки DDL и DML.
Индексирование.
Основные команды языка определения данных DDL :
· CREATE TABLE
· DROP TABLE
· ALTER TABLE
· CREATE INDEX
· DROP INDEX
· ALTER INDEX
Создание таблицы
CREATE TABLE имя таблицы [CHECK(условие)](имя_поля тип данных [NULL|NOT NULL][,…]), где [CHECK(условие)] – ограничение на значение столбца.
Например:
CREATE TABLE stud (ФИО varchar(20) NOT NULL,
Дисциплина varchar(20) NOT NULL,
Оценка smallint NOT NULL);
Кроме того должны включаться средства поддержки целостности данных
PRIMARY KEY
FOREIGN KEY ()
ON UPDATE CASCADE
ON DELETE CASCADE
Пример1:
CREATE TABLE Клиент
(КодКлиента INT IDENTITY (1,1) PRIMARY KEY,
Фирма VARCHAR(50) NOT NULL,
ФИО VARCHAR(50) NOT NULL,
Город VARCHAR(50) NOT NULL,
Телефон CHAR(10) NOT NULL CHECK(Телефон LIKE ‘[1-9][0-9][0-9]- [0-9][0-9]- [0-9][0-9]’))
Пример2:
CREATE TABLE Заказ
(КодЗаказа INT IDENTITY (1,1) PRIMARY KEY,
КодКлиента INT NOT NULL,
КодТовара INT NOT NULL,
Количество INT NOT NULL DEFAULT 0,
Дата DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT fk_Товар
FOREIGN KEY(КодТовара) REFERENCES Товар,
CONSTRAINT fk_Клиент
FOREIGN KEY(КодКлиента) REFERENCES Клиент)
Изменение структуры таблицы - ALTER TABLE
Язык DML используется для манипулирования данными внутри таблиц базы данных. Содержит всего три команды:
· INSERT
· UPDATE
· DELETE
Ввод, добавление данных в таблицу:
INSERT INTOимя_таблицы [(список полей)]
VALUES(список значений)
Например:
INSERT INTO Студенты (Номер_зачетки, ФИО, ГРУППА)
VALUES (‘ИТ34-56’ ‘ИвановИИ’, ‘425’)
Правила:
· Если задаются значения всех полей, то список полей не нужен.
· Если столбец при описании таблицы имеет признак NOT NULL, то ввод данных в это поле в каждой записи обязателен.
· Если имеется хотя бы один необязательный столбец, в который не вводится значение, задание списка имен столбцов обязательно.
Дополнительные возможности:
1. Можно по запросу извлечь значения из одной таблицы и разместить их в другой:
INSERT INTO табл.куда
FROM табл. Откуда
WHERE условие
2.Добавление выбранных данных
INSERT INTOтабл.куда (список полей)
SELECTопределяет выбираемые данные…
3.Копирование данных из одной (старой) таблицы в другую (новую) таблицу
SELECT *
INTOновая табл
FROMстарая табл.
Обновление данных:
UPDATEимя таблицы
SETимя_поля1=нов.знач.1 [,имя поля2=нов.знач.2] и т.д.
[WHEREусловие отбора]
Например:
UPDATE Ведомость
SET Ведомость.Оценка=5
WHERE Ведомость.Оценка=2 AND Ведомость.Дисциплина=’23’;
Удаление данных
DELETE
FROMимя-таблицы
[WHEREусловие отбора]
ВНИМАНИЕ! Если не указать условие, то удаляются все строки и таблица будет пуста!
Выполняя команды INSERT, UPDATE, DELETE,следите за нарушением целостности!
Индексирование.
Индексы используются для логической сортировки данных с целью повышения скорости поиска и выполнения в последующем операций сортировки.
Однако не следует увлекаться созданием множества индексов, имейте в виду следующее:
· Индексы повышают производительность операций выборки, но ухудшают производительность при выполнении операций добавления, обновления и удаления данных. Вызвано это тем, что при выполнении подобных операций СУБД должна еще и динамически обновлять индекс.
· Индексы используются для выборки и сортировки данных. Если вы часто сортируете данные определенным образом, эти данные - кандидат на индексацию.
· Для хранения индекса требуется дополнительное место на жестком диске.
· Не все данные подходят для индексации. Данные, которые являются по своей сути уникальными не дадут такого выигрыша от индексации, как данные, которые имеют больше возможных значений.
· В качестве индекса можно определить несколько столбцов, например, Город и ВУЗ. Такой индекс можно использовать, только если данные будут сортироваться в порядке «Город ВУЗ». Если нужно отсортировать данные по названию ВУЗа, такой индекс использовать нельзя.
ПРАКТИЧЕСКОЕ ЗАДАНИЕ.
Запрос на создание таблицы
В ранее созданной БД для архива с именем [номер группы_ФИО_архив], используя оператор создания таблиц CREATE TABLE, создайте таблицу «Студент» той же структуры, что и в основной базе. Таблицу данными не заполнять.
Изменение структуры таблицы
1.Изменить структуру таблицы «Преподаватели», добавить поле «Номер диплома».
3. Создать запросы-действия, реализующие следующее:
1. Добавить в таблицу «Дисциплины» две новые дисциплины '«Управление данными»-136 часов и «Проектирование систем»-205 часов.
2.Студента Иванова перевести из группы 425 в группу 427.
Проиндексировать таблицу Ведомость по полю Студент.
5. Проверьте полученные знания, выполнив тестовые задания.(См. стр. Тестовые задания. Тема 5 и 6).
Тема 7. Представления
Необходимые знания.
Представление - это именованная виртуальная таблица, содержимое которой является результатом запроса, заданного при описании представления.
К созданному представлению можно обращаться также как к обычной таблице.
Представление создается командой
CREATE VIEWимя_представления ASзапрос SQL
Представление позволяет:
· ограничивать число столбцов (с помощью предложения SELECT);
· ограничивать число строк (заданием условия в предложении WHERE);
· формировать новые столбцы из других столбцов с использованием выражений.
· выводить группы строк, если запрос с групповой операцией.
Для чего нужны представления:
· обеспечивают независимость пользовательских программ от изменений логической структуры БД.
· для каждого пользователя может быть создано «свое окно в БД».
· для обеспечения защиты данных - от определенных пользователей могут быть скрыты некоторые данные.
· для повторного использования операторов SQL.
· для упрощения выполнения сложных операций, например, объединения таблиц.
· для изменения форматирования и отображения данных.
ПРАКТИЧЕСКОЕ ЗАДАНИЕ.