СУБД. Лекция 4

Задача 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

Схема:

Highschooler ( ID, name, grade )

Студент с уникальным ID. Имя и Класс

Friend ( ID1, ID2 )

Студент с ID1 друг студента с ID2. Дружба взаимная, если есть запись (123, 456), то есть и (456, 123)

Задача 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

Схема:

Highschooler ( ID, name, grade )

Студент с уникальным ID. Имя и Класс

Friend ( ID1, ID2 )

Студент с ID1 друг студента с ID2. Дружба взаимная, если есть запись (123, 456), то есть и (456, 123)

Задача 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

Задача 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

Есть в том же классе и нет в других классах
==
Есть друзья и нет в друих классах

Задача 1

Найти имена и классы, которые имеют друзей только в том же классе. Вернуть результат, отсортированный по классу, затем имени в классе.

SELECT H.name, H.grade
FROM Highschooler AS H
JOIN Friend AS F ON (H.Id = F.Id1)
LEFT JOIN Highschooler AS O ON (F.Id2 = O.Id AND H.Grade <> O.Grade)
WHERE O.Id IS NULL

SELECT H.name, H.grade
FROM Highschooler AS H
JOIN Friend AS F ON (H.Id = F.Id1)
LEFT JOIN Highschooler AS O ON (F.Id2 = O.Id AND H.Grade <> O.Grade)
WHERE O.Id IS NULL
GROUP BY H.grade, H.name, H.id
ORDER BY H.grade, H.name

Задача 2

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, а средней оценкой фильмов выпущенных после 1980 года (фильмы выпущенные в 1980 году не учитываются).

(Убедитесь, что для расчета используете среднюю оценку для каждого фильма. Не просто среднюю оценку фильмов до и после 1980 года.)

Movie (mID, title, year, director)
Rating (rID, mID, stars, ratingDate)

Задача 2

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, а средней оценкой фильмов выпущенных после 1980 года (фильмы выпущенные в 1980 году не учитываются).

(Убедитесь, что для расчета используете среднюю оценку для каждого фильма. Не просто среднюю оценку фильмов до и после 1980 года.)

SELECT
	AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) AS after,
	AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS before
FROM (
	SELECT M.mID, M.year, AVG(R.stars) as stars
	FROM Movie M
	JOIN Rating R USING (mID)
	GROUP BY M.mID, M.year
) AS R

SELECT
	AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) AS after,
	AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS before
FROM (
	SELECT M.mID, M.year, AVG(R.stars) as stars
	FROM Movie M
	JOIN Rating R USING (mID)
	GROUP BY M.mID, M.year
) AS R

Задача 2

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, а средней оценкой фильмов выпущенных после 1980 года (фильмы выпущенные в 1980 году не учитываются).

(Убедитесь, что для расчета используете среднюю оценку для каждого фильма. Не просто среднюю оценку фильмов до и после 1980 года.)

SELECT
	AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) -
	AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS result
FROM (
	SELECT M.mID, M.year, AVG(R.stars) as stars
	FROM Movie M
	JOIN Rating R USING (mID)
	GROUP BY M.mID, M.year
) AS R

Задача 2 (вариант 2)

Найти разницу между средней оценкой фильмов выпущенных до 1980 года, а средней оценкой фильмов выпущенных после 1980 года (фильмы выпущенные в 1980 году не учитываются).

(Убедитесь, что для расчета используете среднюю оценку для каждого фильма. Не просто среднюю оценку фильмов до и после 1980 года.)

SELECT R.starts_after - R.starts_before AS result
FROM (
  SELECT
    AVG(CASE WHEN year > 1980 THEN stars ELSE NULL END) AS stars_after,
    AVG(CASE WHEN year < 1980 THEN stars ELSE NULL END) AS stars_before
  FROM Movie M 
  JOIN Rating R USING (mID)
  GROUP BY M.mID
) AS R

Хранимые процедуры

CREATE
	[DEFINER = { user | CURRENT_USER }]
	PROCEDURE sp_name ([proc_parameter[,...]])
	[characteristic ...] routine_body
 
CREATE
	[DEFINER = { user | CURRENT_USER }]
	FUNCTION sp_name ([func_parameter[,...]])
	RETURNS type
	[characteristic ...] routine_body
 
proc_parameter:
	[ IN | OUT | INOUT ] param_name type
 
func_parameter:
	param_name type

Хранимые процедуры

characteristic:
	COMMENT 'string'
	| LANGUAGE SQL
	| [NOT] DETERMINISTIC
	| { 
		CONTAINS SQL 
		| NO SQL 
		| READS SQL DATA 
		| MODIFIES SQL DATA 
	}
	| SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
	Valid SQL routine statement

Хранимые процедуры (+)

• Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
• Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
• Обеспечивает механизм защиты.
• Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Хранимые процедуры (-)

• Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
• Дублирование логики своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
• Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Хранимые процедуры: пример

DELIMITER //
DROP PROCEDURE IF EXISTS p1//
CREATE PROCEDURE `p1` ()
	LANGUAGE SQL
	DETERMINISTIC
	SQL SECURITY DEFINER
	COMMENT 'A procedure'
BEGIN
	SELECT 'Hello World !';
END//
 
CALL p1();

Хранимые процедуры: пример

DELIMITER //
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))  
BEGIN  
	DECLARE a, b INT DEFAULT 5;  
	DECLARE str VARCHAR(50);  
	DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;  
	DECLARE v1, v2, v3 TINYINT;      
 
	INSERT INTO table1 VALUES (a);  
	SET str = 'I am a string';  
	SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;  
END //

Хранимые процедуры: условия

IF search_condition THEN statement_list
	[ELSEIF search_condition THEN statement_list] ...
	[ELSE statement_list]
END IF

CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list] ...
	[ELSE statement_list]
END CASE

CASE
	WHEN search_condition THEN statement_list
	[WHEN search_condition THEN statement_list] ...
	[ELSE statement_list]
END CASE

Хранимые процедуры: LOOP

[begin_label:] LOOP
	statement_list
END LOOP [end_label]
 
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
	label1: LOOP
		SET p1 = p1 + 1;
		IF p1 < 10 THEN
			ITERATE label1;
		END IF;
		LEAVE label1;
	END LOOP label1;
	SET @x = p1;
END;

Хранимые процедуры: REPEAT, WHILE

[begin_label:] REPEAT
	statement_list
UNTIL search_condition
END REPEAT [end_label]

[begin_label:] WHILE search_condition DO
	statement_list
END WHILE [end_label]

CREATE PROCEDURE dowhile()
BEGIN
	DECLARE v1 INT DEFAULT 5;
		WHILE v1 > 0 DO
		...
		SET v1 = v1 - 1;
	END WHILE;
END;

Хранимые процедуры: HANDLER

DECLARE handler_action HANDLER
	FOR condition_value [, condition_value] ...
	statement

handler_action:
	  CONTINUE
	| EXIT
	| UNDO

condition_value:
	  mysql_error_code
	| SQLSTATE [VALUE] sqlstate_value
	| condition_name
	| SQLWARNING
	| NOT FOUND
	| SQLEXCEPTION

Хранимые процедуры: курсоры

DECLARE cursor-name CURSOR FOR SELECT ...;       
OPEN cursor-name;                                
FETCH cursor-name INTO variable [, variable];   
CLOSE cursor-name; 

DECLARE vBankId INTEGER;
DECLARE vBankName VARCHAR(50);
DECLARE vAddress VARCHAR(50);
DECLARE done INTEGER DEFAULT 0;
DECLARE BankCursor CURSOR FOR
	SELECT `bank`.`BankId`, `bank`.`BankName`, `bank`.`Address`
	FROM `bank`; 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN BankCursor;
WHILE done = 0 DO 
	FETCH BankCursor INTO vBankId,vBankName,vAddress;
END WHILE;
CLOSE BankCursor; 

Хранимые процедуры: EXECUTE

EXECUTE stmt_name   [USING @var_name [, @var_name] ...]

DELIMITER $$
DROP PROCEDURE IF EXISTS `create_archive`$$
CREATE PROCEDURE `create_archive`(IN current_table VARCHAR(50))
BEGIN
DECLARE template,archive_template VARCHAR(50);
SET archive_template=REPLACE(CURDATE(),"-","");
SET template=CONCAT(current_table,"_",archive_template);

SET @archive_query:=CONCAT("CREATE TABLE ",template,
	" ENGINE=ARCHIVE AS (SELECT * FROM ",current_table," )");

PREPARE archive_query FROM @archive_query;
EXECUTE archive_query;
DEALLOCATE PREPARE archive_query;

END$$

Триггеры

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body
 
trigger_time = BEFORE | AFTER
 
trigger_event = INSERT | UPDATE | DELETE

Триггеры: пример

CREATE TRIGGER 
	add_count_comment 
AFTER INSERT ON comments 
FOR EACH ROW BEGIN

UPDATE user
SET    user.countcomment = user.countcomment + 1 
WHERE  user.id = NEW.user_id; 

Триггеры: пример

CASE NEW.owner_name
	WHEN 'Blog' THEN
		UPDATE blog 
		SET comment = comment + 1 
		WHERE id = NEW.owner_id;
	WHEN 'Article' THEN 
		UPDATE article 
		SET comment = comment + 1
		WHERE id = NEW.owner_id;
	WHEN 'PopulatePlace' THEN 
		UPDATE populate_place 
		SET comment = comment + 1 
		WHERE id = NEW.owner_id;
END CASE;

Триггеры: пример

CASE NEW.owner_name
	WHEN 'Blog' THEN
		SET usertitle = (
			SELECT title FROM blog WHERE id=NEW.owner_id
		);
	WHEN 'Article' THEN 
		SET usertitle = (
			SELECT title FROM article WHERE id=NEW.owner_id
		);
	WHEN 'PopulatePlace' THEN
		SET usertitle = '';
END CASE;

INSERT INTO user_has_events
VALUES (NEW.user_id, NEW.id, "Comments", NOW() , usertitle);

END;

Подсистемы хранения: MyISAM

• Блокировка и конкуренция
• Автоматическое исправление
• Ручное исправление
  CHECK TABLE mytable и REPAIR TABLE mytable
• Особенности индексирования
• Полнотекстовый индекс
• Отложенная запись ключей
  DELAY_KEY_WRITE

Подсистемы хранения: InnoDB

• Транзакции
• Блокировки на уровне строк
• Кластерные индексы
• Внешние ключи
• Возможны Deadlocks

Подсистемы хранения: Memory

• Данные хранятся в памяти и теряются при перезапуске
• Поддерживают индексы типа HASH
• Не поддерживают TEXT, BLOB, VARCHAR => CHAR
• Используется для
  промежуточных таблиц

Подсистемы хранения: критерии выбора

• Транзакции
• Конкурентный доступ
• Резервное копирование
• Восстановление после сбоя
• Специальные возможности

Подсистемы хранения: надежность

Подсистемы хранения: практические примеры

• Протоколирование
  • MyISAM, Archive
  • Отчеты?: репликация, Merge
• Чтение, или в основном чтение
  • MyISAM
• Обработка заказов
  • InnoDB
• Распространение на дисках
  • MyISAM, Compressed MyISAM

Подсистемы хранения: сводка (1/2)

Подсистемы хранения: сводка (2/2)