Structured Query Language

Июль 22, 2022

Главная
Информатика
Structured Query Language

Содержание

3. Типы данных в разных СУБД
4. операторы манипуляции данными (Data Manipulation Language, DML): SELECT считывает данные, удовлетворяющие заданным условиям; INSERT добавляет новые
5. БД для примеров
6. SELECT
8. SELECT SELECT * FROM artists; SELECT albums.name, albums.id, albums.date FROM albums; SELECT name, length FROM records
9. Условия в SQL AND, OR, NOT >, =, =, !=, IS NULL, IS NOT NULL
10. Сложные условия в SQL LIKE BETWEEN IN, NOT IN ANY, ALL EXISTS CASE … WHEN …
11. Экзотические условия в SQL COALESCE NULLIF …
12. Примеры с несколькими условиями SELECT albums.id FROM albums WHERE albums.id = 1 OR (albums.name NOT LIKE
13. Фильтрация результатов и псевдонимы SELECT DISTINCT length FROM records; SELECT DISTINCT a.id_studio FROM albums a; SELECT
14. Троичная логика
16. Схемы сложных условий
18. Сортировка выборки SELECT * FROM records r WHERE r.id_album = 1 ORDER BY r.length; SELECT DISTINCT
19. Агрегатные функции
20. Агрегатные функции SELECT count(*) FROM records r WHERE r.length SELECT count(DISTINCT r.length) FROM records r ;
21. Группировка SELECT a.id_artist "artist id", AVG(age(a.date)) AS "average album age" FROM albums a WHERE a.id_file_format =
22. Горизонтальное соединение результатов запроса
23. Объединение результатов запроса
24. Примеры объединения таблиц SELECT * FROM albums a INNER JOIN records r ON a.id = r.id_album;
25. Вложенные запросы
26. Примеры подзапросов SELECT r.*, a.name FROM records r INNER JOIN albums a ON a.id = r.id_album
27. Примеры подзапросов SELECT * FROM albums a INNER JOIN (SELECT AVG(length(r.name)) "average track name length", MAX(r.length)
28. Предикаты ANY, ALL, EXISTS SELECT * FROM albums a INNER JOIN artists ar ON ar.id =
29. Примеры HAVING SELECT a.name FROM (SELECT r.id_album FROM records r GROUP BY r.id_album HAVING SUM(r.length) >
30. Операторы UNION, INTERSECT, EXCEPT SELECT a.name, a.date FROM albums a WHERE a.name NOT LIKE '%The%' UNION
31. INSERT
32. Примеры INSERT INSERT INTO artists (id, name, birthday) VALUES (42, 'Nick Cage', '15-03-1900'); INSERT INTO studio
33. UPDATE UPDATE artists SET id = 43 WHERE id = 2; UPDATE records SET length =
34. DELETE DELETE FROM records WHERE records.id DELETE FROM albums WHERE id_studio IN (SELECT s.id FROM studio
35. Транзакции BEGIN TRANSACTION; / BEGIN; SAVE TRANSACTION; COMMIT TRANSACTION; / COMMIT; ROLLBACK TO; ROLLBACK;
36. Объединение запросов с помощью транзакций BEGIN; CREATE TABLE studio ( id serial NOT NULL, name character
37. Объединение запросов с помощью транзакций BEGIN; CREATE TABLE file_format ( id serial NOT NULL, name character
38. SQL-инъекции
39. NOSQL
41. Скачать презентацию

Слайд 2

Слайд 3

Типы данных в разных СУБД

Слайд 4

операторы манипуляции данными (Data Manipulation Language, DML):
SELECT считывает данные, удовлетворяющие заданным

условиям;
INSERT добавляет новые данные;
UPDATE изменяет существующие данные;
DELETE удаляет данные;
операторы определения данных (Data Definition Language, DDL):
CREATE создает объект БД (саму базу, таблицу, представление, пользователя и т. д.);
ALTER изменяет объект;
DROP удаляет объект;
операторы управления транзакциями (Transaction Control Language, TCL):
COMMIT применяет транзакцию,
ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции,
SAVEPOINT делит транзакцию на более мелкие участки.
операторы определения доступа к данным (Data Control Language, DCL):
GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом;
REVOKE отзывает ранее выданные разрешения;
DENY задает запрет, имеющий приоритет над разрешением;

Подмножества команд SQL

Слайд 5

БД для примеров

Слайд 6

SELECT

Слайд 7

Слайд 8

SELECT
SELECT *
FROM artists;
SELECT albums.name, albums.id, albums.date
FROM albums;
SELECT name, length

FROM records
WHERE records.id = 2;

Слайд 9

Условия в SQL
AND, OR, NOT
>, <, <=, >=, =, !=, <>
IS

NULL, IS NOT NULL

Слайд 10

Сложные условия в SQL
LIKE
BETWEEN
IN, NOT IN
ANY, ALL
EXISTS
CASE … WHEN … THEN

… ELSE … END

Слайд 11

Экзотические условия в SQL
COALESCE
NULLIF
…

Слайд 12

Примеры с несколькими условиями
SELECT albums.id < 8 , albums.date
FROM albums

WHERE albums.id = 1 OR
(albums.name NOT LIKE '%th%' AND
albums.date BETWEEN '1990-01-01' AND '2000-12-31' AND
albums.date NOT BETWEEN '1997-01-01' AND '1998-12-31');
SELECT artists.birthday, artists.name
FROM artists
WHERE artists.name != 'ABBA' AND
(artists.birthday IS NOT NULL OR id IN (4, 8, 15, 16, 413));

Слайд 13

Фильтрация результатов и псевдонимы
SELECT DISTINCT length FROM records;
SELECT DISTINCT a.id_studio FROM

albums a;
SELECT a.id_studio,
CASE
WHEN a.date < '1950-01-01' THEN 'ancient'
WHEN a.date <= '1990-01-01' THEN 'nope'
WHEN a.date > '1990-01-01' THEN 'yep'
ELSE 'WAT' END AS result,
a.name
FROM albums a
WHERE a.id < 100 AND a.id_file_format = 1;

Слайд 14

Троичная логика

Слайд 15

Слайд 16

Схемы сложных условий

Слайд 17

Слайд 18

Сортировка выборки
SELECT *
FROM records r
WHERE r.id_album = 1
ORDER

BY r.length;
SELECT DISTINCT r.number
FROM records r
ORDER BY r.number DESC;

Слайд 19

Агрегатные функции

Слайд 20

Агрегатные функции
SELECT count(*)
FROM records r
WHERE r.length < 5;
SELECT count(DISTINCT

r.length)
FROM records r ;
SELECT MAX(a.birthday) max_date, MIN(a.birthday) min
FROM artists a;
SELECT AVG(age(a.date))
FROM albums a
WHERE a.name LIKE '%The%';

Слайд 21

Группировка
SELECT a.id_artist "artist id", AVG(age(a.date)) AS "average album age"
FROM albums

a
WHERE a.id_file_format = 1
GROUP BY a.id_artist;
SELECT AVG(length(r.name)) "average name length", MAX(r.length) "max track length"
FROM records r
GROUP BY r.id_album;

Слайд 22

Горизонтальное соединение результатов запроса

Слайд 23

Объединение результатов запроса

Слайд 24

Примеры объединения таблиц
SELECT *
FROM albums a
INNER JOIN records r

ON a.id = r.id_album;
SELECT r.name, a.date, ar.birthday, f.*
FROM albums a
LEFT OUTER JOIN artists ar
ON ar.id = a.id_artist
LEFT OUTER JOIN studio s
ON s.id = a.id_studio
LEFT OUTER JOIN file_format f
ON f.id = a.id_file_format
LEFT OUTER JOIN styles st
ON st.id = a.id_style
LEFT OUTER JOIN records r
ON a.id = r.id_album
WHERE ar.birthday BETWEEN '01-10-1970' AND '10-12-1990'
AND r.length > 4;

Слайд 25

Вложенные запросы

Слайд 26

Примеры подзапросов
SELECT r.*, a.name
FROM records r
INNER JOIN albums a
ON a.id

= r.id_album
WHERE a.id_studio IN
(SELECT s.id
FROM studio s
WHERE s.name LIKE '%USA%');
SELECT *
FROM artists ar
WHERE ar.birthday >
(SELECT MIN(a.date)
FROM albums a);

Слайд 27

Примеры подзапросов
SELECT *
FROM albums a
INNER JOIN
(SELECT
AVG(length(r.name)) "average track name

length", MAX(r.length) "max track length",
r.id_album
FROM records r
GROUP BY r.id_album) rc
ON rc.id_album = a.id
WHERE rc."average track name length" > 12
OR rc."max track length" = 6;

Слайд 28

Предикаты ANY, ALL, EXISTS
SELECT *
FROM albums a
INNER JOIN artists ar
ON

ar.id = a.id_artist
WHERE a.id_studio = ANY
(SELECT s.id
FROM studio s
WHERE s.name LIKE '%USA%');
SELECT *
FROM records rc
WHERE rc.length < ALL
(SELECT AVG(r.length)
FROM records r
WHERE r.number < 10
GROUP BY r.id_album);

Слайд 29

Примеры HAVING
SELECT a.name
FROM
(SELECT r.id_album
FROM records r
GROUP BY r.id_album
HAVING

SUM(r.length) > 20) rc
INNER JOIN albums a
ON a.id = rc.id_album;
SELECT s.name
FROM studio s
INNER JOIN
(SELECT a.id_studio
FROM albums a
GROUP BY a.id_studio
HAVING MAX(age(a.date)) >
(SELECT AVG(age(al.date))
FROM albums al
WHERE name NOT LIKE '%The%')) alb
ON alb.id_studio = s.id;

Слайд 30

Операторы UNION, INTERSECT, EXCEPT
SELECT a.name, a.date
FROM albums a
WHERE a.name NOT

LIKE '%The%'
UNION
SELECT a.name, a.date
FROM albums a
WHERE a.id_style = 3;
SELECT r.name
FROM records r
UNION ALL
SELECT a.name
FROM artists a;

Слайд 31

INSERT

Слайд 32

Примеры INSERT
INSERT INTO artists (id, name, birthday) VALUES (42, 'Nick Cage',

'15-03-1900');
INSERT INTO studio (name) VALUES('New studio');
INSERT INTO file_format (id, name) VALUES (DEFAULT, 'wma');
INSERT INTO styles VALUES (13, '8-bit');

Слайд 33

UPDATE
UPDATE artists SET id = 43 WHERE id = 2;
UPDATE records

SET length = length * 2
WHERE id_album IN
(SELECT a.id
FROM albums a
WHERE a.id_style=2);

Слайд 34

DELETE
DELETE FROM records WHERE records.id < 10;
DELETE FROM albums
WHERE id_studio IN

(SELECT s.id
FROM studio s
WHERE s.id > 5);
DELETE FROM records;

Слайд 35

Транзакции
BEGIN TRANSACTION; / BEGIN;
SAVE TRANSACTION;
COMMIT TRANSACTION; / COMMIT;
ROLLBACK TO;
ROLLBACK;

Слайд 36

Объединение запросов с помощью транзакций
BEGIN;
CREATE TABLE studio
(
id serial NOT NULL,

name character varying(255) NOT NULL,
CONSTRAINT pk_studio PRIMARY KEY (id),
CONSTRAINT uk_studio_name UNIQUE (name)
);
INSERT INTO studio (name) SELECT DISTINCT a.studio FROM albums a;
ALTER TABLE albums ADD COLUMN id_studio integer;
UPDATE albums SET id_studio =
(SELECT s.id FROM studio s
WHERE s.name = albums.studio);
ALTER TABLE albums ADD CONSTRAINT fk_album_studio FOREIGN KEY (id_studio) REFERENCES studio (id) ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE albums ALTER COLUMN id_studio SET NOT NULL;
ALTER TABLE albums DROP COLUMN studio;
COMMIT;

Слайд 37

Объединение запросов с помощью транзакций
BEGIN;
CREATE TABLE file_format
(
id serial NOT NULL,

name character varying(255) NOT NULL,
CONSTRAINT pk_file_format PRIMARY KEY (id),
CONSTRAINT uk_file_format_name UNIQUE (name)
);
INSERT INTO file_format (name) SELECT DISTINCT a.format FROM albums a;
ALTER TABLE albums ADD COLUMN id_file_format integer;
UPDATE albums SET id_file_format =
(SELECT f.id FROM file_format f
WHERE f.name = albums.format);
ALTER TABLE albums ADD CONSTRAINT fk_album_file_format FOREIGN KEY (id_file_format) REFERENCES file_format (id) ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE albums ALTER COLUMN id_file_format SET NOT NULL;
ALTER TABLE albums DROP COLUMN format;
COMMIT;

Слайд 38

SQL-инъекции

Слайд 39

Structured Query Language

Содержание

Типы данных в разных СУБД

операторы манипуляции данными (Data Manipulation Language, DML):SELECT считывает данные, удовлетворяющие заданным

БД для примеров

SELECT

SELECTSELECT * FROM artists;SELECT albums.name, albums.id, albums.date FROM albums;SELECT name, length

Условия в SQLAND, OR, NOT>, <, <=, >=, =, !=, <>IS

Сложные условия в SQLLIKEBETWEENIN, NOT INANY, ALLEXISTSCASE … WHEN … THEN

Экзотические условия в SQLCOALESCENULLIF…

Примеры с несколькими условиямиSELECT albums.id < 8 , albums.date FROM albums

Фильтрация результатов и псевдонимыSELECT DISTINCT length FROM records;SELECT DISTINCT a.id_studio FROM

Троичная логика

Схемы сложных условий

Сортировка выборкиSELECT * FROM records r WHERE r.id_album = 1 ORDER

Агрегатные функции

Агрегатные функцииSELECT count(*) FROM records r WHERE r.length < 5;SELECT count(DISTINCT

ГруппировкаSELECT a.id_artist "artist id", AVG(age(a.date)) AS "average album age" FROM albums

Горизонтальное соединение результатов запроса

Объединение результатов запроса

Примеры объединения таблицSELECT * FROM albums a INNER JOIN records r

Вложенные запросы

Примеры подзапросовSELECT r.*, a.name FROM records r INNER JOIN albums a ON a.id

Примеры подзапросовSELECT *FROM albums a INNER JOIN (SELECT AVG(length(r.name)) "average track name

Предикаты ANY, ALL, EXISTSSELECT * FROM albums a INNER JOIN artists ar ON

Примеры HAVINGSELECT a.name FROM (SELECT r.id_album FROM records r GROUP BY r.id_album HAVING

Операторы UNION, INTERSECT, EXCEPTSELECT a.name, a.date FROM albums aWHERE a.name NOT