Обратите внимание, что в некоторых системах баз данных требуется указывать точку с запятой в конце каждого оператора. Точка с запятой является стандартным указателем на конец каждого оператора в SQL. В примерах используется MySQL, поэтому точка с запятой требуется.
Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды:
mysql -u root -p
Затем введите пароль.
Выполните следующую команду. Назовём базу данных «university»:
CREATE DATABASE university;
USE university;
SOURCE
SOURCE
Команды для работы с базами данных
1. Просмотр доступных баз данных
SHOW DATABASES;
2. Создание новой базы данных
CREATE DATABASE;
3. Выбор базы данных для использования
USE
4. Импорт SQL-команд из файла .sql
SOURCE
5. Удаление базы данных
DROP DATABASE
6. Просмотр таблиц, доступных в базе данных
SHOW TABLES;
7. Создание новой таблицы
CREATE TABLE
PRIMARY KEY (
FOREIGN KEY (
);
Ограничения целостности при использовании CREATE TABLE
Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:
ячейка таблицы не может иметь значение NULL;
первичный ключ — PRIMARY KEY (col_name1, col_name2, …);
внешний ключ — FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).
Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.
Создайте таблицу «instructor»:
CREATE TABLE instructor (
ID CHAR(5),
name VARCHAR(20) NOT NULL,
dept_name VARCHAR(20),
salary NUMERIC(8,2),
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
8. Сведения о таблице
Можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы следующей командой:
DESCRIBE
9. Добавление данных в таблицу
INSERT INTO
VALUES (
При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.
INSERT INTO
VALUES (
10. Обновление данных таблицы
UPDATE
SET
WHERE
11. Удаление всех данных из таблицы
DELETE FROM
12. Удаление таблицы
DROP TABLE
13. SELECT
SELECT используется для получения данных из определённой таблицы:
SELECT
FROM
Следующей командой можно вывести все данные из таблицы:
SELECT * FROM
14. SELECT DISTINCT
В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.
SELECT DISTINCT
FROM
15. WHERE
Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:
SELECT
FROM
WHERE
В запросе можно задавать следующие условия:
сравнение текста;
сравнение численных значений;
логические операции AND (и), OR (или) и NOT (отрицание).
Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE:
SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3;
16. GROUP BY
Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT, MAX, MIN, SUM и AVG, для группировки выходных значений.
SELECT
FROM
GROUP BY
Пример
Выведем количество курсов для каждого факультета:
SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name;
17. HAVING
Ключевое слово HAVING было добавлено в SQL потому, что WHERE не может быть использовано для работы с агрегатными функциями.
SELECT
FROM
GROUP BY
HAVING
Выведем список факультетов, у которых более одного курса:
SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name
HAVING COUNT(course_id)>1;
18. ORDER BY
ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.
SELECT
FROM
ORDER BY
Выведем список курсов по возрастанию и убыванию количества кредитов:
SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;
19. BETWEEN
BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.
SELECT
FROM
WHERE
Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:
SELECT * FROM instructor
WHERE salary BETWEEN 50000 AND 100000;
20. LIKE
Оператор LIKE используется в WHERE, чтобы задать шаблон поиска похожего значения.
Есть два свободных оператора, которые используются в LIKE:
% (ни одного, один или несколько символов);
_ (один символ).
SELECT
FROM
WHERE
Выведем список курсов, в имени которых содержится «to», и список курсов, название которых начинается с «CS-»:
SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE 'CS-___';
21. IN
С помощью IN можно указать несколько значений для оператора WHERE:
SELECT
FROM
WHERE
Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:
SELECT * FROM student
WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);
22. JOIN
JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:
SELECT
FROM
JOIN
ON
Выведем список всех курсов и соответствующую информацию о факультетах:
SELECT * FROM course
JOIN department
ON course.dept_name=department.dept_name;
Выведем список всех обязательных курсов и детали о них:
SELECT prereq.course_id, title, dept_name, credits, prereq_id
FROM prereq
LEFT OUTER JOIN course
ON prereq.course_id=course.course_id;
Выведем список всех курсов вне зависимости от того, обязательны они или нет:
SELECT course.course_id, title, dept_name, credits, prereq_id
FROM prereq
RIGHT OUTER JOIN course
ON prereq.course_id=course.course_id;
23. View
View — это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. View всегда показывает самую свежую информацию из базы данных.
Создание
CREATE VIEW
SELECT
FROM
WHERE
Удаление
DROP VIEW
24. Агрегатные функции
Эти функции используются для получения совокупного результата, относящегося к рассматриваемым данным. Ниже приведены общеупотребительные агрегированные функции:
COUNT (col_name) — возвращает количество строк;
SUM (col_name) — возвращает сумму значений в данном столбце;
AVG (col_name) — возвращает среднее значение данного столбца;
MIN (col_name) — возвращает наименьшее значение данного столбца;
MAX (col_name) — возвращает наибольшее значение данного столбца.
25. Вложенные подзапросы
Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT, FROM и WHERE, вложенные в другой запрос.
Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:
SELECT DISTINCT course_id
FROM section
WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (
SELECT course_id
FROM section
WHERE semester = ‘Spring’ AND year= 2010
);
