# SQL # Тестовые данные и общая информация Перейдите по [ссылке](https://dev.mysql.com/doc/index-other.html) загрузите “sakila database” в разделе Example Databases. ```bash wget https://downloads.mysql.com/docs/sakila-db.zip unzip sakila-db.zip MariaDB [sakila]> source /home/kali/sql/sakila-db/sakila-schema.sql MariaDB [sakila]> source /home/kali/sql/sakila-db/sakila-data.sql ``` - К некоторым БД нельзя отправить запрос без from. Поэтому таблица dual со столбцом dummy: SELECT now() FROM dual; - Можно подключить вывод отсета в xml: mysql -u lrngsql -p --xml bank - При соединении с БД возвращается id Welcome to the MySQL monitor. Commands end with ; or \\g. Your MYSQL connection id is 11 Server version: 8.0.15 MySQL Community Server - GPL - В операциях сравнения использовать is null. Значение может быть null, но оно не может быть равно null - Если столбец autoincrement not null, то 0 = NULL = value+1 # Типы данных и наборы символов **Наборы символов:** Список установленных наборов ``` SHOW CHARACTER SET; ``` По умолчанию utf 8mb4 varchar(20) character set latinl : при создании таблицы другой набор набор по-умолчанию для всей таблицы ``` create database european_sales character set latinl; ``` **Общая информация** - M максимальное количество. Для целых - кол-во цифр, для дробных - общее кол-во цифр, для строк макс. кол-во символов - D кол-во цифр после запятой, должно быть не больше M-2 - fsp применимо к дате/времени, определяет точность (кол-во дробной части секунды) - при strict mode перебор значения вызывает исключение, при restrictive - максимально возможное значение
ТипДоп. информация
char(255 символов) varchar(65535) char(20) 20 символов char фиксировано, при хранении добавляет справа PAD символы
tinytext(255) text(65535) mediumtext(16 млн) longtext(4 млр) если больше, то обрежутся конечные пробелы считаются при сортировке первые 1024 байта, но можно увеличить tinytext, text - можно не использовать
tinyint(1) smallint(2) mediumint int bigint(8) указано кол-во байтов при создании указывается визуальное кол-во цифр+один символ на знак ```sql CREATE TABLE employees (salary INTEGER(5) UNSIGNED); ``` int максимум 10, поэтому это преобразуется в int(11) ```sql CREATE TABLE employees (id INT(255)); ``` ZEROFILL заполняет 0 слева. ```sql CREATE TABLE documents (document_no INT(5) ZEROFILL); ```
DECIMAL = NUMERICфиксированное кол-во цифр. Равно int с коэффициентом деления. Первая цифра - общее кол-во цифр (M), вторая - кол-во цифр после запятой. Знак неявно добавляется. Т.е. от -9.99 до 9.99 ```sql CREATE TABLE taxes (tax_rate DECIMAL(3, 2)); ``` M < 65, по-умолчанию 10 при переборе ошибка, при выходе за пределы точности - округление по математическим правилам способ вывода больших чисел с игнором младших цифр ```sql select truncate(tax_rate * 1000, 0) from taxes; ``` в агрегатных функциях работает общее ограничение на 64 цифры, вычисление точное.
float, doubleприближенные числа отображаются как были внесены, но в агрегатных функциях могут появиться неточности ```sql CREATE TABLE typed_numbers( id TINYINT, float_values FLOAT, decimal_values DECIMAL(3, 2)); INSERT INTO typed_numbers VALUES (1, 1.1, 1.1), (2, 1.1, 1.1), (3, 1.1, 1.1); SELECT * FROM typed_numbers; | id | float_values | decimal_values | +------+--------------+------------------+ | 1 | 1.1 | 1.10 | | 2 | 1.1 | 1.10 | | 3 | 1.1 | 1.10 | SELECT SUM(float_values), SUM(decimal_values) FROM typed_numbers; | SUM(float_values) | SUM(decimal_values) | +--------------------+---------------------+ | 3.3000000715255737 | 3.30 | ``` Нельзя напрямую сравнивать в агрегатах, нужно сравнивать разницу ABS(v1 - v2) > 0.1 ```sql SELECT id, SUM(col1) as v1, SUM(col2) as v2 FROM temp GROUP BY id HAVING v1 <> v2; ``` нельзя, внешне одинаковые числа будут разными
битовый типcolumn\_name BIT(M) ``` CREATE TABLE bit_values (val BIT(7)); INSERT INTO bit_values VALUES(b'1011'); - равно VALUES(b'0001011'), в этой нотации b и B равны. INSERT INTO bit_values VALUES(0b1011); INSERT INTO bit_values VALUES(2); - автоматическое преобразование числа в битовую маску ``` select выводит в виде 0x0A select bin(...) - без 0 слева select lpad(bin(...), 7, '0') - привычный вид с 0 слева
date datetime timestamp year time 00-00-0000 вместо NULL, не отображается ```sql %а Краткое имя дня недели - Sun, Mon, ... %b Краткое имя месяца — Jan, Feb, ... %с Числовое значение месяца (0..11) %d Числовое значение дня месяца (00..31) %f Число микросекунд (000000..999999) %Н Час дня в 24-часовом формате (00..23) %h Час дня в 12-часовом формате (01..12) %i Минуты в часе (00..59) %j День года (001..366) %М Полное имя месяца (January..December) %m Числовое значение месяца %р AM или РМ %s Число секунд (00..59) %W Полное имя дня недели (Sunday..Saturday) %w Числовое значение дня недели (0=Sunday..6=Saturday) %Y Значение года (четыре цифры) ``` timestamp зависит от time\_zone ```sql CREATE TABLE datetime_temp(ts TIMESTAMP, dt DATETIME); INSERT INTO datetime_temp VALUES(NOW(), NOW()); SELECT ts, dt FROM datetime_temp; | 2017-10-14 18:10:25 | 2017-10-14 18:10:25 | SET time_zone = '+03:00'; SELECT ts, dt FROM datetime_temp; | 2017-10-14 21:10:25 | 2017-10-14 18:10:25 | ``` Если сервер MAXDB mode, TIMESTAMP = DATETIME column\_name TIME; ``` 838:59:59 to 838:59:59 + 6 цифр миллисекунд некорректное в 00:00:00, нельзя отличить от корректно добавленного 00:00:00 ```
# Таблицы **Типы таблиц:** - Постоянные таблицы (т .е. созданные с помощью инструкции create table) - Производные таблицы (т .е. строки, возвращаемые подзапросом и хранящиеся в памяти) - Временные таблицы (т.е. изменяемые данные, хранящиеся в памяти) - Виртуальные таблицы (т.е. созданные с помощью инструкции create view). **Создание таблицы** ```sql create table person ( person_id smallint unsigned, fname varchar(20), lname varchar(20), eye_color char(2), constraint pk_person primary key (person_id) ); ``` изменение струтуры таблицы ``` ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; ``` какие таблицы ссылаются по внешним ключам на таблицу X ``` SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'X'; ``` ``` DROP TABLE favorite_food; ``` **Ограничения (constraint)** Первичный ключ, может быть любое кол-во столбцов ``` constraint pk_person primary key (person_id) ``` Создание связи ``` CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id) ``` Ограничение набора в столбце ``` enum('ht', 'hy') create table person (... eye_color ENUM('BR','BL','GR', ....); ``` AUTO\_INCREMENT в описании ключа автоматическое увеличение номера desc person - описание таблицы not null при создании столбца запрещает быть пустым проверка ограничений ```sql SELECT * FROM information_schema.check_constraints WHERE table_schema = 'db name' and table_name = 'table name'; ``` просмотр скрипта создания таблицы со всеми внесенными изменениями ```sql SHOW CREATE TABLE mytable; ``` **Получение/изменение данных** ```sql INSERT INTO person (person_id, fname, lname, eye_color, birth_date) VALUES (null, 'William','Turner', 'BR', '1972-05-27'); ORDER BY - группировка в селекте UPDATE person SET street = '1225 Tremont St.', city = Boston', state = 'MA', country = 'USA', postal_code = '02138' WHERE person_id = 1; DELETE FROM person WHERE person_id = 2; ``` **Структура select**
selectОпределяет,какие столбцы следует включить в результирующий набор запроса
fromОпределяет таблицы, из которых следует выбирать данные, а также таблицы, которые должны быть соединены
whereОтсеивает ненужные данные
group byИспользуется для группировки строк по общим значениям столбцов
havingОтсеивает ненужные данные
order byСортирует строки окончательного результирующего набора по одному или нескольким столбцам
**Фильтрация** Логические операции в where: and, or, not, для группировки круглые скобки, операторы сравнения, такие как =,!=, <, >,о,like,in и between; арифметические операторы, такие как +, -, \* и /. Подзапросы в фильтрах: ```sql ... where film_id = (SELECT film_id FROM film WHERE title = ’RIVER OUTLAW’) ... ``` Двухсторонний диапазон. Сначала нижняя граница включительно. ```sql WHERE rental date BETWEEN '2005-06-14 AND '2005-06-16'; ``` ```sql WHERE amount BETWEEN 10.0 AND 11.99; WHERE last name BETWEEN 'FA' AND 'FR' - FRA не войдет. Можно 'FRB' ``` множество IN, NOT IN - WHERE rating IN ('G', 'PG'); первая буква Q - WHERE left(last name, 1) = 'Q'; регулярки: WHERE last\_name REGEXP '^\[QY\]' WHERE return\_date IS NULL; (is not null) Если WHERE return\_date = NULL; - ошибки не будет, но пустая выборка on результат BETWEEN 180 AND 240; exist - наличие хотя-бы одного значения в подмножестве ```sql SELECT с.first_name, c.last_name FROM customer c WHERE EXISTS (SELECT 1 FROM rental r WHERE r.customer_id = c.customer id AND date(r.rental date) < '2005-05-25'); ``` # Объединения, соединения и группировки **Логическое объединение таблиц** объединяются по толбцам последовательно. Лучше одинаковые псевдонимы. union (или) объединяет построчно +сортировка +удаление дубликатов, union all без +. Есть предопрделенный необязательный столбец typ для ссылки на источник для строки. intersect (И) except (не) все что есть в первом исключая повторяющиеся со вторым order by можно добавить после последнего запроса, относится к первому выполнение сверху вниз, но intersect высший приоритет, можно использовать скобки **Соединения таблиц** ограничение внешнего ключа не является обязательным условием, чтобы соединить две таблицы. on - условие соединения таблиц. Если во всех таблицах совпадают названия столбцов, то USING (address\_id); Using лучше не использовать. ```sql SELECT с.first_name, c.last_name, time(r.rental_date) rental_time FROM customer c INNER JOIN rental r ON c.customer_id = r.customer id WHERE date(r.rental_date) = '2015-04-03'; ``` несколько таблиц, порядок перечисления значения не имеет: ```sql select c.first_name, c.last_name, ci.city from city ci inner join address a on a.address_id = ci.address_id inner join customer c on a.address_id = c.address_id; ``` с подзапросом: ```sql SELECT с.first_name, с.last_name, addr.address, addr.city FROM customer c INNER JOIN (SELECT a.address_id, a.address, ct.city FROM address a INNER JOIN city ct ON a.city_id = ct.city_id WHERE a.district = 'California') addr ON c.address id = addr.address id; ``` если в inner join несколько раз соединение с одной таблицей, то псевдонимы обязательны. возможно ссылаться на саму себя (например если в таблице есть строка, указывающая на родителя) **Типы соединений** - inner join - на каждую строку из левой таблицы строки из правой. (оператор И) - left/right outer join - все строки левой таблицы, если в правой есть то добавляем иначе null - cross join - перекрестное (чистое декартовое) соединение. Используется при создании новых данных. - natural join - типа естественное соединение, предоставляющее выбор типа базе. Лучше не использовать. Пример cross join ```sql SELECT days.dt, COUNT(г.rental_id) num_rentals FROM rental r RIGHT OUTER JOIN (SELECT DATE_ADD('2005-01-01', INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt FROM (SELECT 0 num UNION ALL SELECT 1 num UNION ALL SELECT 2 num UNION ALL SELECT 3 num UNION ALL SELECT 4 num UNION ALL SELECT 5 num UNION ALL SELECT 6 num UNION ALL SELECT 7 num UNION ALL SELECT 8 num UNION ALL SELECT 9 num) ones CROSS JOIN (SELECT 0 num UNION ALL SELECT 10 num UNION ALL SELECT 20 num UNION ALL ' SELECT 30 num UNION ALL SELECT 40 num UNION ALL SELECT 50 num UNION ALL SELECT 60 num UNION ALL SELECT 70 num UNION ALL SELECT 80 num UNION ALL SELECT 90 num) tens CROSS JOIN (SELECT 0 num UNION ALL SELECT 100 num UNION ALL SELECT 200 num UNION ALL SELECT 300 num) hundreds WHERE DATE_ADD('2005-01-011, INTERVAL (ones.num + tens.num + hundreds.num) DAY) < '2006-01-01' days ON days.dt = date(r.rental_date) GROUP BY days.dt ORDER BY 1 ``` **Группировка** ```sql SELECT customer_id, count(*) FROM rental GROUP BY customer id; ``` сортировка по count - либо по номеру, либо определив имя столбца и по имени. Условия для агрегатных функций до создания полного набора, поэтому where count(\*) > 40 нельзя. Можно having SELECT customer\_id, count(\*) FROM rental GROUP BY customer\_id HAVING count(\*) >= 40; но where можно для условий данных WHERE f.rating IN ('G', 'PG') **Агрегатные функции:** count(\*) ```sql COUNT(DISTINCT customer_id) ``` ```sql SELECT MAX(amount) max_amt, MIN(amount) min_amt, AVG(amount) avg_amt, SUM(amount) tot_amt, COUNT(*) num_payments FROM payment; ``` агрегаты применяются либо ко всей выборке, либо к группам определенным в group by в аргументе агрегатов любые функции, возвр число строку дату null: игнорируется при рассчетах. Но count(\*) отдает кол-во строк, и null считается. Т е count(val) число значений столбца без null, count(\*) число строк в столбце val **Способы группировки** Множественная: ```sql SELECT fa.actor_id, f.rating, count(*) FROM film_actor fa INNER JOIN film f ON fa.film_id = f.film_id GROUP BY fa.actor_id, f.rating ORDER BY 1,2; ``` Выражения: ```sql SELECT extract(YEAR FROM rental_date) year, COUNT(*) how_many FROM rental GROUP BY extract(YEAR FROM rental date); ``` Добавка подсумм: ```sql GROUP BY fa.actor_id, f.rating WITH ROLLUP ``` Непересекающееся множество DISTINCT (затратная операция) ```sql SELECT DISTINCT actor_id FROM film_actor ORDER BY actor_id; ``` В столбцы можно добавлять вычисляемые поля order by несколько столбцов desc по убыванию ORDER BY time(r.rental date) desc; можно использовать номер столбца в перечислении # Подзапросы подзапрос выполняется до основного запроса, это временная таблица с областью видимости запроса, после выполнения запроса данные удаляются. Если возврат одну строку и один столбец, то в основном запросе используется равенство (<>, >=,...) ```sql SELECT customer_id, first_name, last_name FROM customer WHERE customer_id = (SELECT MAX(customer_id) FROM customer); ``` если множество строк: in, not in в результирующем множестве <>=all All сравнивает значение с каждым значением в подзапросе <>=any - если хотя-бы одно выполняется, то true HAVING sum(amount) > ANY (подзапрос) если в результате подзапроса одно из значений null, а сравнение not in, то будет пустой вывод: результат сравнения с null - unknown **Типы подзапросов** некоррелированный: может быть выполнен отдельно и не ссылается ни на что из другого кореллированный: когда в подзапросе используется таблица запроса. выполняется по одному разу для каждой строки-кандидата. Может быть проблема с производительностью. часто вместе с update, delete ```sql UPDATE customer с SET с.last_update = (SELECT max(г.rental_date) FROM rental r WHERE r.customer_id = c.customer_id); ``` проверка наличия ```sql UPDATE customer с SET с.last_update = (SELECT max(г.rental_date) FROM rental r WHERE r.customer_id = c.customer_id) WHERE EXISTS (SELECT 1 FROM rental r WHERE r.customer_id = c.customer_id); ``` ```sql select f.title from film f where exist ( select 1 from film_category fc where category_id = 10 and f.film_id = fc.film_id) ``` создание данных подзапросом. Нужно разделить на группы, когда такой группировки нет в базе ```sql SELECT pymnt_grps.name, count(*) num_customers FROM (SELECT customer_id, count(*) num rentals, sum(amount) tot_payments FROM payment GROUP BY customer_id) pymnt INNER JOIN (SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit UNION ALL SELECT 'Average Joes' name, 75 low_limit, 149.99 high_limit UNION ALL SELECT 'Heavy Hitters' name, 150 low_limit, 9999999.99 high_limit) pymnt_grps ON pymnt.tot_payments BETWEEN pymnt_grps.low_limit AND pymnt_grps.high_limit GROUP BY pymnt_grps.name; ``` Обобщенные табличные выражения: создается именованый подзапрос, который потом используется в запросе with actor\_s as (запрос) Например общий доход от проката тех фильмов с рейтингом PG, актерский состав которых включает актера, фамилия которого начинается с S ```sql WITH actors_s AS (SELECT actor_id, first_name, last_name FROM actor WHERE last name LIKE 'S%'), actors_s_pg AS (SELECT s.actor_id, s.first_name, s.last_name,f.film_id, f.title FROM actors_s s INNER JOIN film_actor fa ON s.actor_id = fa.actor_id INNER JOIN film f ON f.film_id = fa.film_id WHERE f.rating = 'PG'), actors_s_pg_revenue AS (SELECT spg.first_name, spg.last_name, p.amount FROM actors_s_pg spg INNER JOIN inventory i ON i.film_id = spg.film_id INNER JOIN rental r ON i.inventory_id = r.inventory_id INNER JOIN payment p ON r.rental_id = p.rental_id) SELECT spg_rev.first_name, spg_rev.last_name, sum(spg_rev.amount) tot_revenue FROM actors_s_pg_revenue spg_rev GROUP BY spg_rev.first_name, spg_rev.last_name ORDER BY 3 desc; ``` скалярные подзапросы можно использовать и в select ```sql SELECT (SELECT c.first_name FROM customer c WHERE c.customer_id = p.customer_id) first_name, (SELECT c.last_name FROM customer c WHERE c.customer_id = p.customer_id) last__name, (SELECT ct.city FROM customer c INNER JOIN address a ON c.address_id = a.address_id INNER JOIN city ct ON a.city_id = ct.city_id WHERE c.customer_id = p.customer_id) city, sum(p.amount) tot_payments, count(*) tot_rentals FROM payment p GROUP BY p.customer id; ``` создать новую строку в таблице film\_actor и у вас имеются следующие данные:имя и фамилия актера; название фильма ```sql INSERT INTO film_actor (actor_id, film_id, last_update) VALUES ( (SELECT actor_id FROM actor WHERE first_name = ’JENNIFER' AND last_name = ’DAVIS'), (SELECT film_id FROM film WHERE title = 'ACE GOLDFINGER'), now()); ``` # Встроенные функции и условная логика **Встроенные функции** **Строки:** экранирование: дополнителная кавычка '' или \\' quote() +кавычки при выборке текста: ```sql SELECT quote(text_fId) FROM string_tbl; -> 'This string didn\'t work, but it does now' ``` char() берет номера, и объединяет в строку ```sql char(97,98,99) -> abc ``` concat() (+ в MS SQL) объединяет строки ```sql UPDATE string_tbl SET text_fld = CONCAT(text_fld, 'but now it is longer'); select concat(first_name, ' ', last_name, ' has been customer since ', date(create_date)) narrative from customer; ``` length() длина строки position начало подстроки SELECT POSITION('characters' IN vchar\_fld) FROM string\_tbl; Первый с 1. 0 если не найдено. locate как position, но 3 аргумент - старт поиска insert вставка replace заменяет substring находит подстроку объединение данных из группировки в столбец ```sql SELECT id, GROUP_CONCAT(data) FROM yourtable GROUP BY id  ``` **Числа:** ceil(), floor()округление в большую или меньшую сторону к ближайшему целому числу round() десятичная часть >=0,5 округлено в большую сторону и наоборот ROUND(72.0909,3) - 3 знака оставить TRUNCATE(72.0909, 1) - простое усечение, оставит 72.0 аргумент может быть <0 TRUNCATE(17, -1) = 10, round(17, -1) = 20 sign() знак числа abs() абсолютное значение **Даты:** cast() строку формата YYY-MM-DD HH:MM:SS в дату ```sql SELECT HOUR(@dt), MINUTE(@dt), SECOND(@dt), DAY(@dt), WEEK(@dt), MONTH(@dt), QUARTER(@dt), YEAR(@dt); ``` str\_to\_date(str, 'format') Формат:
%MПолное имя месяца (January..December)
%mЧисловое значение месяца
%dЧисловое значение дня месяца (00..31)
%jДень года (001..366)
%WПолное имя дня недели (Sunday.Saturday)
%YЗначение года (четыре цифры)
%yЗначение года (две цифры)
%HЧас дня в 24-часовом формате (00..23)
%hЧас дня в 12-часовом формате (01..12)
%iМинуты в часе (00..59)
%sЧисло секунд (00..59)
%f Число микросекунд (000000..999999)
%pAM или РМ
%aКраткое имя дня недели — Sun, Mon,...
%bКраткое имя месяца — Jan,Feb,...
CURRENT\_DATE(), CURRENT\_TIME(), CURRENT\_TIMESTAMP() interval ```sql SELECT DATE ADD(CURRENT DATE(), INTERVAL 5 DAY) ``` последний день месяца ```sql LAST_DAY('2019-09-17') ``` имя месяца ```sql DAYNAME('2019-09-18') ``` извлекают элемент даты ```sql EXTRACT(YEAR FROM '2019-09-18 22:19:05') ``` кол-во полных дней ```sql DATEDIFF('2019-09-03', 2019-06-211) ``` ```sql SELECT CURRENT_TIME() AS 'CUR_TIME', ADDTIME(CURRENT_TIME(), 020000) AS 'ADDTIME', SUBTIME(CURRENT_TIME(), 020000) AS 'SUBTIME'; | CUR_TIME | ADDTIME | SUBTIME | | 10:12:34 | 12:12:34 | 08:12:34 | ``` **Условная логика** **case:** ```sql SELECT c.first_name, c.last_name, CASE WHEN active = 0 THEN 0 ELSE (select count(*) from rental r where r.customer_id = c.customer_id) END activity_type FROM customer c; ``` преимущество перед if-then в возможности использовать внутри select, insert, update, delete возвращаемые значения одного типа Использование case для разделения по столбцам ```sql SELECT SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1 ELSE 0 END) May_rentals, SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1 ELSE 0 END) June_rentals, SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1 ELSE 0 END) July_rentals FROM rental WHERE rental date BETWEEN 2005-05-01' AND '2005-08-01'; ``` использование case для получения факта участия актера в фильмах статуса G ```sql SELECT a.first_name, a.last_name, CASE WHEN EXISTS (SELECT 1 FROM film_actor fa INNER JOIN film f ON fa.film_id = f.film_id WHERE fa.actor_id = a.actor_id AND f.rating = 'G') THEN 'Y' ELSE 'N' END g_actor FROM actor a WHERE a.last name LIKE 'S%' OR a.first name LIKE 'S%'; ``` условные обновления ```sql UPDATE customer SET active = CASE WHEN 90 <= (SELECT datediff(now(), max(rental_date) FROM rental r WHERE r.customer id = customer.customer id)) THEN 0 ELSE 1 END WHERE active = 1; ``` **Аналитические функции** Постобработка сформированных данных. Использование: функция(столбец) over (дополнительное условие группировки)
ФункцияОписание
RANKАналогична функции DENSE\_RANK() за исключением того, что в последовательности ранжированных значений есть пробелы, когда две или более строки имеют одинаковый ранг.
DENSE\_RANKПрисваивает ранг каждой строке в своем разделе на основе предложения ORDER BY. Он присваивает одинаковый ранг строкам с одинаковыми значениями. Если две или более строки имеют одинаковый ранг, то в последовательности ранжированных значений не будет пробелов.
ROW\_NUMBERНазначает последовательное целое число каждой строке в своем разделе
CUME\_DISTВычисляет совокупное распределение значения в наборе значений.
FIRST\_VALUEВозвращает значение указанного выражения относительно первой строки в рамке окна.
LAGВозвращает значение N-й строки перед текущей строкой в разделе. Возвращает NULL, если предшествующей строки не существует.
LAST\_VALUEВозвращает значение указанного выражения относительно последней строки в рамке окна.
LEADВозвращает значение N-й строки после текущей строки в разделе. Возвращает NULL, если никакой последующей строки не существует.
NTH\_VALUEВозвращает значение аргумента из N-й строки рамки окна
NTILEРаспределяет строки для каждого раздела окна в указанное количество ранжированных групп.
PERCENT\_RANKВычисляет процентильный ранг строки в разделе или наборе результатов
# Транзакции, индексы и ограничения **Транзакции** **Блокировки.** Записывающие должны запрашивать и получать блокировку записи для изменения данных, а извлекающие должны запрашивать и получать блокировку чтения. Варианты блокировки: - запросы на чтение блокируются пока блокировка записи не снята, за один раз для каждой таблицы (или ее части) только одна блокировка записи - блокировка записи, не нужны блокировки чтения. Но сервер гарантирует, что каждый читатель видит согласованное представление данных до завершения запроса (versioning). **Уровни блокировок** - Блокировка таблиц Не позволяет нескольким пользователям одновременно изменять данные в одной таблице. - Блокировка страниц Не позволяет нескольким пользователям изменять данные в одной и той же странице (страница — это сегмент памяти, обычно в диапазоне от 2 до 16 Кбайт ) таблицы одновременно. - Блокировка строк Не позволяет нескольким пользователям одновременно изменять одну и ту же строку в таблице. Есть режим автофиксации и транзакции. - По умолчанию одиночная инструкция INSERT, UPDATE или DELETE неявно включается в транзакцию и немедленно подтверждается. - SET AUTOCOMMIT=0 отключает для сессии - только для транзакционных таблиц - Некоторые команды заставляют подтвердить транзакцию до их выполнения. Команды (Data Definition Language, DDL) типа ALTER TABLE, LOCK TABLES, ... start transaction начало транзакации, commit - завершение, rollback - возврат. **Механизмы хранения mysql:**
MylSAMНетранзакционный, табличная блокировка, скоростной доступ на чтение с малым кол-вом записи
MEMORYНетранзакционный, табличная блокировка, для скоростного кэша, таблицы в памяти, при нехватке своп на диск, не поддерживает TEXT BLOB
CSVТранзакционный, данные в файлах с данными с разделением запятыми, обмен данными. Нет индексов.
InnoDBТранзакционный, блокировка на уровне строки
MergeСпециальный механизм, используемый для создания нескольких идентичных таблиц MylSAM в виде единой таблицы (так называемое разбиение таблицы)
Archiveхранение больших количеств неиндексированных данных, в основном для архивных целей. Не поддерживает DELETE, UPDATE.
Blackholeдля репликации
federatedОдна база на нескольких серверах. Создает клиентское соединение и выполняет еще раз запрос, получает данные. По-умолчанию выключен.
NDB cluster
статус таблицы ``` show table status like ’customer' \G; ``` изменить механизм хранения определенной таблицы ``` alter table customer engine = InnoDB; ``` Нельзя сочетать разные механизмы в одной транзакции. управление конкурентным доступом с помощью многоверсионности (multiversion concurrency control, MVCC) **Точки сохранения:** Возможность частично откатывать транзакцию. ```sql SAVEPOINT my_savepoint; ROLLBACK ТО SAVEPOINT my_savepoint; START TRANSACTION; UPDATE product SET date_retired = CURRENT_TIMESTAMP() WHERE product_cd = 'XYZ'; SAVEPOINT before close accounts; UPDATE account SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(), last_activity_date = CURRENT_TIMESTAMP{) WHERE product_cd = 'XYZ'; ROLLBACK TO SAVEPOINT before_close_accounts; COMMIT; ``` **Индексы** Индексы независимые объекты, хотя и относятся к базе.
НазваниеНазначение и создание
Простой индексускоряет поиск данных ``` ALTER TABLE customer ADD INDEX idx email (email); ```
Уникальный индексзапрещает два элемента с одинаковым значением ``` ALTER TABLE customer ADD UNIQUE idx_email (email); ```
Многостолбцовый индексважна последовательность - эффекта не будет при поиске только по последнему столбцу ``` ALTER TABLE customer ADD INDEX idx_full_name (last_name, first_name); ```
Менее 64 индексов на таблицу, 16 столбцов максимум для мультииндекса Просмотр индексов: ```sql SHOW INDEX FROM customer \G; ``` Удаление индекса: ```sql ALTER TABLE customer DROP INDEX idx_email; ``` **Типы индексов:** - B-Tree Индекс на основе деления отрезка пополам. Хорош для разнородных почти сбаллансированных данных - Битовые маски - когда значений относительно мало. Генерит несколько таблиц, соотв. каждому значению ключа. ```sql CREATE BITMAP INDEX idx_active ON customer (active); ``` - Текстовые индексы **Просмотр плана выполнения запроса:** ```sql EXPLAIN SELECT customer_id, first_name, last_name FROM customer WHERE first_name LIKE 'S%' AND last name LIKE 'P%' \G; ``` **Ограничение** Ограничение — условия, накладываемые столбцы таблицы. Типы ограничений: - Ограничения первичного ключа Определяют столбец или столбцы, для которых гарантируется их уникальность в таблице - Ограничения внешнего ключа содержать только значения, найденные в столбце первичного ключа другой таблицы (могут также ограничиваться допустимые значения в других таблицах при установке правил update cascade и/или delete cascade) - Ограничения уникальности Ограничивают один или несколько столбцов таким образом, чтобы они содержали уникальные значения в таблице (ограничение первичного ключа — частный случай ограничения уникальности) - Проверочные ограничения Ограничивают допустимые значения для столбца **Создание ограничений** при создании таблицы ```sql CREATE TABLE customer customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, ... store_id TINYINT UNSIGNED NOT NULL, address_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY (customer_id), CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE ``` при изменении таблицы ```sql ALTER TABLE customer ADD CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address id) ON DELETE RESTRICT ON UPDATE CASCADE; ``` **удаление ограничений** ```sql ALTER TABLE customer drop ``` # Представления и метаданные **Представления** **Создание представления** ```sql CREATE VIEW customer_vw (customer_id, first_name, last_name, email) AS SELECT ... ``` ```sql CREATE VIEW film_stats AS SELECT f.filmed, f.title, f.description, f.rating, (SELECT c.name FROM category c INNER JOIN film_category fc ON c.category_id = fc.category_id WHERE fc.film_id = f.film_id) category_name, (SELECT count(*) FROM film_actor fa WHERE fa.film_id = f.film_id) num_actors, (SELECT count(*) FROM inventory i WHERE i.film_id = f.film_id) inventory_cnt, (SELECT count(*) FROM inventory i INNER JOIN rental r ON i.inventory_id = r.inventory_id WHERE i.film_id = f.film_id) num_rentals FROM film f; ``` **Используются для:** - Безопасность данных - Агрегация данных - Сокрытие сложности - Соединение разделенных данных (одна таблица -> две текущая и архив, но редко нужно как одна) объединяются через union all **Представление обновляемое, если:** - не используются агрегатные функции - нет group by и having. - В предложениях select и from нет подзапросов, а любой подзапрос в предложении where не ссылается на таблицы в предложении from. - нет union,union all или distinct. - Предложение from содержит как минимум одну таблицу или обновляемое представление. - Если имеется более одной таблицы или представления, предложение from использует только внутренние соединения. В представлении с одной таблицей (простое представление) не может быть использовано для вставки данных. В представлении с внутренними join: CREATE VIEW customer details AS SELECT c.customer\_id, \- можно обновлять столбцы одной из таблиц \- можно добавлять данные **Метаданные** information\_schema - данные о данных
.schemataбазы данных
.tablesинформация обо всех таблицах
.viewsо представлениях
.columnsо столбцах
.statisticsоб индексах
.table\_constraintsоб ограничениях
.user\_privilegeКто имеет привилегии для различных объектов схемы
.schema\_privilegesКто имеет привилегии для различных баз данных
.table\_privilegesКто имеет привилегии для различных таблиц
.column\_privilegesКто имеет привилегии для различных столбцов таблиц
.routinesСохраненные подпрограммы (процедуры и функции)
.triggersТриггеры таблиц
.pluginsПодключаемые модули сервера
.enginesДоступные механизмы хранения
.partitionsРазбиения таблиц
.eventsЗапланированные события
.processlistВыполняющиеся процессы
.referential\_constraintsВнешние ключи
.parametersПараметры хранимых процедур и функций
.profilingИнформация о профилях пользователей
```sql SET @qry = 'SELECT customer_id, first_name, last_name FROM customer WHERE customer_id = ?'; PREPARE dynsql2 FROM @qry; SET @custid = 9; EXECUTE dynsql2 USING @custid; DEALLOCATE PREPARE dynsql2; ``` # Администрирование # MySQL Базы данных хранятся в /var/lib/mysql ``` sudo grep -R 'datadir' /etc/mysql/ ``` **Настройка сессии**
Уровень изолированностиЧерновое чтениеНеповторяющееся чтениеФантомное чтениеБлокировка чтения
READ UNCOMMITTEDДаДаДаНет
READ COMMITTEDНетДаДаНет
REPEATABLE READНетНетДаНет
SERIALIZABLEНетНетНетДа
Можно установить на уровне сессии ``` SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` Взаимоблокировки: Innodb отказывает самой короткой транзакции цели в трех ключевых областях: задержка, доступность и ошибки **Performance Schema** Это отдельный engine Проверить факт наличия: ```sql SHOW ENGINES; +--------------------+---------+----------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | ``` включен или нет: ```sql SHOW VARIABLES LIKE 'performance_schema'; ``` **Переменные окружения** Просмотр ```sql SHOW VARIABLES LIKE 'performance_schema'; ``` Поддерживает % **Настройки** Настройка при запуске ``` shell> mysql --max_allowed_packet=16M ``` Настройка при работе ``` mysql>SET GLOBAL max_connections = 1000; - потеря при перезагрузке mysql>SET PERSIST max_connections = 1000; - сохранение после перезагрузки ``` Инструменты (setup\_instruments) пишут в потребителя (таблицы данных, ). Схемой sys представления и хранимые подпрограммы над performance\_schema. включение инструментов вызывает дополнительный код ``` statement/sql/select; wait/synch/mutex/innodb/autoinc_mutex ``` Крайний левый - тип, далее слева направо подсистемы от общей к частной. Дайджест — агрегирование запросов путем удаления из них вариаций Структура таблиц потребителей: - \*\_current — события, происходящие на сервере в данный момент; - \*\_history — последние 10 завершенных событий на поток; - \*\_history\_long — последние 10 000 завершенных событий на поток по всему миру Ограничения: - Инструментарий должен поддерживаться компонентом MySQL. - Она собирает данные только после включения конкретного инструмента и потребителя. - Трудно освободить память. Даже если позже отключите определенные инструменты или потребители, память не будет освобождена, пока вы не перезапустите сервер. **Настройка инструментов мониторинга** Список инструментов: [https://mariadb.com/kb/en/performance-schema-setup\_instruments-table/](https://mariadb.com/kb/en/performance-schema-setup_instruments-table/) Таблица setup\_instruments - включенные инструменты. Структура:
NAMEНазвание инструмента
ENABLEDВключен или нет
TIMEDПланировщик. Если disabled, работать не будет
**CRUD db, tables, users** ```sql ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPassword'; CREATE DATABASE IF NOT EXISTS my_timeweb; DROP DATABASE IF EXISTS my_timeweb; CREATE USER 'sub_user'@'10.0.%' IDENTIFIED BY 'password'; SELECT * FROM mysql.user; DROP USER 'user'@'localhost'; ``` Привилегии:
ALTERМенять структуру таблицы или БД
CREATEСоздавать новые БД и таблицы
DELETEУдалять строки в таблице
INSERTДобавлять строки в таблицу
SELECTЧитать данные из таблицы
UPDATEОбновлять данные в таблице
DROPУдалять БД
ALL PRIVILEGESвсе, кроме GRANT;
USAGE PRIVILEGESникаких привилегий;
FILEразрешает читать файлы на сервере;
INDEXсоздавать индексы для таблиц;
DROPудалять таблицы;
EVENTобработка событий;
TRIGGERсоздание триггеров.
Привилегия на действия
GRANTизменять права пользователей;
SUPERсуперпользователь;
PROCESSполучение информации о состоянии MySQL;
RELOADпозволяет перезагружать таблицы привилегий;
SHUTDOWNпозволяет отключать или перезапускать базу данных;
SHOW DATABASESпросмотр списка баз данных;
LOCK TABLESблокирование таблиц при использовании SELECT;
REFERENCESсоздание внешних ключей для связывания таблиц;
CREATE USERсоздание пользователей;
```mysql GRANT SELECT, INSERT ON my_timeweb.* TO 'user'@'localhost'; из-под root GRANT ALL PRIVILEGES ON my_timeweb.* TO 'user'@'localhost'; REVOKE SELECT, INSERT ON my_timeweb.* FROM 'user'@'localhost'; ``` применение изменений ``` flush privileges; ``` **статус, приложения** mysqladmin version mysqlshow mysql - список таблиц БД mysql mysql\_secure\_installation mysqladmin - административное mysqlcheck - проверка mysqldump - сохранение mysqlimport - импорт текстовых файлов в нужную таблицу mysqlpump - экспорт базы в sql файл mysqlslap - статус загрузки сервера \--print-defaults выводит конфиг по умолчанию # Postgresql Подключение: ``` psql -U имя пользователя -W пароль -d имя базы ``` Сброс пароля пользователя: в файле pg\_dba.conf список пользователей ```postgresql \du SELECT usename, usesuper, usecreatedb FROM pg_catalog.pg_user; ``` создать пользователя ```postgresql CREATE USER user_name WITH PASSWORD 'myPassword'; ``` сменить пароль пользователя ```postgresql ALTER USER user_name WITH PASSWORD 'new_password'; ``` список баз данных ``` \l SELECT datname FROM pg_database; ``` создать базу данных ``` CREATE DATABASE имя_БД; drop database имя_БД ``` установить права пользователя на базу ``` GRANT ALL PRIVILEGES ON DATABASE database1 to dmosk; ``` список таблиц ``` \dt ``` # Для теста Пар # PostgreSQL # Общая информация Источник: Mastering PostgreSQL 15