# 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 - максимально возможное значение

<table border="1" id="bkmrk-%D0%A2%D0%B8%D0%BF-%D0%94%D0%BE%D0%BF.-%D0%B8%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%86%D0%B8%D1%8F-" style="border-collapse: collapse; width: 100%;"><colgroup><col style="width: 23.4859%;"></col><col style="width: 76.5121%;"></col></colgroup><thead><tr><td class="align-center">Тип</td><td class="align-center">Доп. информация</td></tr></thead><tbody><tr><td>char(255 символов)

varchar(65535)

</td><td>char(20) 20 символов

char фиксировано, при хранении добавляет справа PAD символы

</td></tr><tr><td>tinytext(255)

text(65535)

mediumtext(16 млн)

longtext(4 млр)

</td><td>если больше, то обрежутся  
конечные пробелы считаются  
при сортировке первые 1024 байта, но можно увеличить  
tinytext, text - можно не использовать</td></tr><tr><td>tinyint(1)

smallint(2)

mediumint

int

bigint(8)

</td><td>указано кол-во байтов

при создании указывается визуальное кол-во цифр+один символ на знак

```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);
```

</td></tr><tr><td>DECIMAL = NUMERIC</td><td>фиксированное кол-во цифр. Равно int с коэффициентом деления.

Первая цифра - общее кол-во цифр (M), вторая - кол-во цифр после запятой. Знак неявно добавляется. Т.е. от -9.99 до 9.99

```sql
CREATE TABLE taxes (tax_rate DECIMAL(3, 2));
```

M &lt; 65, по-умолчанию 10  
при переборе ошибка, при выходе за пределы точности - округление по математическим правилам  
способ вывода больших чисел с игнором младших цифр

```sql
select truncate(tax_rate * 1000, 0) from taxes;
```

в агрегатных функциях работает общее ограничение на 64 цифры, вычисление точное.

</td></tr><tr><td>float, double</td><td>приближенные числа  
отображаются как были внесены, но в агрегатных функциях могут появиться неточности

```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) &gt; 0.1

```sql
SELECT id, SUM(col1) as v1, SUM(col2) as v2 
FROM temp 
GROUP BY id 
HAVING v1 <> v2;
```

нельзя, внешне одинаковые числа будут разными

</td></tr><tr><td>битовый тип</td><td>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 слева

</td></tr><tr><td>date

datetime

timestamp

year

time

</td><td>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
```

</td></tr></tbody></table>

# Таблицы

**Типы таблиц:**

- Постоянные таблицы (т .е. созданные с помощью инструкции 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**

<table border="1" id="bkmrk-select-%D0%9E%D0%BF%D1%80%D0%B5%D0%B4%D0%B5%D0%BB%D1%8F%D0%B5%D1%82%2C%D0%BA%D0%B0" style="border-collapse: collapse; width: 100%;"><colgroup><col style="width: 10.8526%;"></col><col style="width: 89.1454%;"></col></colgroup><tbody><tr><td>select</td><td>Определяет,какие столбцы следует включить в результирующий набор запроса</td></tr><tr><td>from</td><td>Определяет таблицы, из которых следует выбирать данные, а также таблицы, которые должны быть соединены</td></tr><tr><td>where</td><td>Отсеивает ненужные данные</td></tr><tr><td>group by</td><td>Используется для группировки строк по общим значениям столбцов</td></tr><tr><td>having</td><td>Отсеивает ненужные данные</td></tr><tr><td>order by</td><td>Сортирует строки окончательного результирующего набора по одному или нескольким столбцам</td></tr></tbody></table>

**Фильтрация**

Логические операции в where: and, or, not, для группировки круглые скобки, операторы сравнения, такие как =,!=, &lt;, &gt;,о,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(\*) &gt; 40 нельзя. Можно having SELECT customer\_id, count(\*) FROM rental GROUP BY customer\_id HAVING count(\*) &gt;= 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;  
можно использовать номер столбца в перечислении

# Подзапросы

подзапрос выполняется до основного запроса, это временная таблица с областью видимости запроса, после выполнения запроса данные удаляются. Если возврат одну строку и один столбец, то в основном запросе используется равенство (&lt;&gt;, &gt;=,...)

```sql
SELECT customer_id, first_name, last_name FROM customer WHERE customer_id = (SELECT MAX(customer_id) FROM customer);
```

если множество строк:   
in, not in в результирующем множестве   
&lt;&gt;=all All сравнивает значение с каждым значением в подзапросе  
&lt;&gt;=any - если хотя-бы одно выполняется, то true HAVING sum(amount) &gt; 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() десятичная часть &gt;=0,5 округлено в большую сторону и наоборот ROUND(72.0909,3) - 3 знака оставить  
TRUNCATE(72.0909, 1) - простое усечение, оставит 72.0  
аргумент может быть &lt;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') Формат:

<table border="1" id="bkmrk-%25m-%D0%9F%D0%BE%D0%BB%D0%BD%D0%BE%D0%B5-%D0%B8%D0%BC%D1%8F-%D0%BC%D0%B5%D1%81%D1%8F%D1%86%D0%B0" style="border-collapse: collapse; width: 100%;"><colgroup><col style="width: 16.943%;"></col><col style="width: 83.055%;"></col></colgroup><tbody><tr><td>%M</td><td>Полное имя месяца (January..December)</td></tr><tr><td>%m</td><td>Числовое значение месяца</td></tr><tr><td>%d</td><td>Числовое значение дня месяца (00..31)</td></tr><tr><td>%j</td><td>День года (001..366)</td></tr><tr><td>%W</td><td>Полное имя дня недели (Sunday.Saturday)</td></tr><tr><td>%Y</td><td>Значение года (четыре цифры)</td></tr><tr><td>%y</td><td>Значение года (две цифры)</td></tr><tr><td>%H</td><td>Час дня в 24-часовом формате (00..23)</td></tr><tr><td>%h</td><td>Час дня в 12-часовом формате (01..12)</td></tr><tr><td>%i</td><td>Минуты в часе (00..59)</td></tr><tr><td>%s</td><td>Число секунд (00..59)</td></tr><tr><td>%f </td><td>Число микросекунд (000000..999999)</td></tr><tr><td>%p</td><td>AM или РМ</td></tr><tr><td>%a</td><td>Краткое имя дня недели — Sun, Mon,...</td></tr><tr><td>%b</td><td>Краткое имя месяца — Jan,Feb,...</td></tr></tbody></table>

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 (дополнительное условие группировки)

<table border="1" id="bkmrk-%D0%A4%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D0%9E%D0%BF%D0%B8%D1%81%D0%B0%D0%BD%D0%B8%D0%B5-ran" style="border-collapse: collapse; width: 100%; height: 318.563px;"><colgroup><col style="width: 16.3691%;"></col><col style="width: 83.7501%;"></col></colgroup><thead><tr style="height: 29.7969px;"><td class="align-center" style="height: 29.7969px;">Функция</td><td class="align-center" style="height: 29.7969px;">Описание</td></tr></thead><tbody><tr style="height: 46.5938px;"><td style="height: 46.5938px;">RANK</td><td style="height: 46.5938px;">Аналогична функции DENSE\_RANK() за исключением того, что в последовательности ранжированных значений есть пробелы, когда две или более строки имеют одинаковый ранг.</td></tr><tr style="height: 63.3906px;"><td style="height: 63.3906px;">DENSE\_RANK</td><td style="height: 63.3906px;">Присваивает ранг каждой строке в своем разделе на основе предложения ORDER BY. Он присваивает одинаковый ранг строкам с одинаковыми значениями. Если две или более строки имеют одинаковый ранг, то в последовательности ранжированных значений не будет пробелов.</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">ROW\_NUMBER</td><td style="height: 29.7969px;">Назначает последовательное целое число каждой строке в своем разделе</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">CUME\_DIST</td><td style="height: 29.7969px;">Вычисляет совокупное распределение значения в наборе значений.</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">FIRST\_VALUE</td><td style="height: 29.7969px;">Возвращает значение указанного выражения относительно первой строки в рамке окна.</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">LAG</td><td style="height: 29.7969px;">Возвращает значение N-й строки перед текущей строкой в разделе. Возвращает NULL, если предшествующей строки не существует.</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">LAST\_VALUE</td><td style="height: 29.7969px;">Возвращает значение указанного выражения относительно последней строки в рамке окна.</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">LEAD</td><td style="height: 29.7969px;">Возвращает значение N-й строки после текущей строки в разделе. Возвращает NULL, если никакой последующей строки не существует.</td></tr><tr><td>NTH\_VALUE</td><td>Возвращает значение аргумента из N-й строки рамки окна</td></tr><tr><td>NTILE</td><td>Распределяет строки для каждого раздела окна в указанное количество ранжированных групп.</td></tr><tr><td>PERCENT\_RANK</td><td>Вычисляет процентильный ранг строки в разделе или наборе результатов</td></tr></tbody></table>

# Транзакции, индексы и ограничения

**Транзакции**

**Блокировки.** Записывающие должны запрашивать и получать блокировку записи для изменения данных, а извлекающие должны запрашивать и получать блокировку чтения.  
Варианты блокировки:

- запросы на чтение блокируются пока блокировка записи не снята, за один раз для каждой таблицы (или ее части) только одна блокировка записи
- блокировка записи, не нужны блокировки чтения. Но сервер гарантирует, что каждый читатель видит согласованное представление данных до завершения запроса (versioning).

**Уровни блокировок**

- Блокировка таблиц Не позволяет нескольким пользователям одновременно изменять данные в одной таблице.
- Блокировка страниц Не позволяет нескольким пользователям изменять данные в одной и той же странице (страница — это сегмент памяти, обычно в диапазоне от 2 до 16 Кбайт ) таблицы одновременно.
- Блокировка строк Не позволяет нескольким пользователям одновременно изменять одну и ту же строку в таблице.

Есть режим автофиксации и транзакции.

- По умолчанию одиночная инструкция INSERT, UPDATE или DELETE неявно включается в транзакцию и немедленно подтверждается.
- SET AUTOCOMMIT=0 отключает для сессии
- только для транзакционных таблиц
- Некоторые команды заставляют подтвердить транзакцию до их выполнения. Команды (Data Definition Language, DDL) типа ALTER TABLE, LOCK TABLES, ...

start transaction начало транзакации, commit - завершение, rollback - возврат.

**Механизмы хранения mysql:**

<table border="1" id="bkmrk-mylsam-%D0%9D%D0%B5%D1%82%D1%80%D0%B0%D0%BD%D0%B7%D0%B0%D0%BA%D1%86%D0%B8%D0%BE%D0%BD" style="border-collapse: collapse; width: 100%; height: 301.8px;"><colgroup><col style="width: 21.6072%;"></col><col style="width: 78.512%;"></col></colgroup><tbody><tr style="height: 29.8px;"><td style="height: 29.8px;">MylSAM</td><td style="height: 29.8px;">Нетранзакционный, табличная блокировка, скоростной доступ на чтение с малым кол-вом записи</td></tr><tr style="height: 46.6px;"><td style="height: 46.6px;">MEMORY</td><td style="height: 46.6px;">Нетранзакционный, табличная блокировка, для скоростного кэша, таблицы в памяти, при нехватке своп на диск, не поддерживает TEXT BLOB</td></tr><tr style="height: 46.6px;"><td style="height: 46.6px;">CSV</td><td style="height: 46.6px;">Транзакционный, данные в файлах с данными с разделением запятыми, обмен данными. Нет индексов.</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">InnoDB</td><td style="height: 29.8px;">Транзакционный, блокировка на уровне строки</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">Merge</td><td style="height: 29.8px;">Специальный механизм, используемый для создания нескольких идентичных таблиц MylSAM в виде единой таблицы (так называемое разбиение таблицы)</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">Archive</td><td style="height: 29.8px;">хранение больших количеств неиндексированных данных, в основном для архивных целей. Не поддерживает DELETE, UPDATE.</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">Blackhole</td><td style="height: 29.8px;">для репликации</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">federated</td><td style="height: 29.8px;">Одна база на нескольких серверах. Создает клиентское соединение и выполняет еще раз запрос, получает данные. По-умолчанию выключен.</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">NDB cluster</td><td style="height: 29.8px;">  
</td></tr></tbody></table>

статус таблицы

```
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;
```

**Индексы**

Индексы независимые объекты, хотя и относятся к базе.

<table border="1" id="bkmrk-%D0%9D%D0%B0%D0%B7%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D0%9D%D0%B0%D0%B7%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D0%B5-" style="border-collapse: collapse; width: 100%;"><colgroup><col style="width: 38.8558%;"></col><col style="width: 61.1442%;"></col></colgroup><thead><tr><td class="align-center">Название</td><td class="align-center">Назначение и создание</td></tr></thead><tbody><tr><td>Простой индекс</td><td>ускоряет поиск данных ```
ALTER TABLE customer ADD INDEX idx email (email);
```

  
</td></tr><tr><td>Уникальный индекс</td><td>запрещает два элемента с одинаковым значением ```
ALTER TABLE customer ADD UNIQUE idx_email (email);
```

  
</td></tr><tr><td>Многостолбцовый индекс</td><td>важна последовательность - эффекта не будет при поиске только по последнему столбцу ```
ALTER TABLE customer ADD INDEX idx_full_name (last_name, first_name);
```

  
</td></tr></tbody></table>

Менее 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;
```

**Используются для:**

- Безопасность данных
- Агрегация данных
- Сокрытие сложности
- Соединение разделенных данных (одна таблица -&gt; две текущая и архив, но редко нужно как одна) объединяются через 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 - данные о данных

<table border="1" id="bkmrk-.schemata-%D0%B1%D0%B0%D0%B7%D1%8B-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B" style="border-collapse: collapse; width: 100%; height: 596px;"><colgroup><col style="width: 50%;"></col><col style="width: 50%;"></col></colgroup><tbody><tr style="height: 29.8px;"><td style="height: 29.8px;">.schemata</td><td style="height: 29.8px;">базы данных</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.tables</td><td style="height: 29.8px;">информация обо всех таблицах</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.views</td><td style="height: 29.8px;">о представлениях</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.columns</td><td style="height: 29.8px;">о столбцах</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.statistics</td><td style="height: 29.8px;">об индексах</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.table\_constraints</td><td style="height: 29.8px;">об ограничениях</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.user\_privilege</td><td style="height: 29.8px;">Кто имеет привилегии для различных объектов схемы</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.schema\_privileges</td><td style="height: 29.8px;">Кто имеет привилегии для различных баз данных</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.table\_privileges</td><td style="height: 29.8px;">Кто имеет привилегии для различных таблиц</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.column\_privileges</td><td style="height: 29.8px;">Кто имеет привилегии для различных столбцов таблиц</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.routines</td><td style="height: 29.8px;">Сохраненные подпрограммы (процедуры и функции)</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.triggers</td><td style="height: 29.8px;">Триггеры таблиц</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.plugins</td><td style="height: 29.8px;">Подключаемые модули сервера</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.engines</td><td style="height: 29.8px;">Доступные механизмы хранения</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.partitions</td><td style="height: 29.8px;">Разбиения таблиц</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.events</td><td style="height: 29.8px;">Запланированные события</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.processlist</td><td style="height: 29.8px;">Выполняющиеся процессы</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.referential\_constraints</td><td style="height: 29.8px;">Внешние ключи</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.parameters</td><td style="height: 29.8px;">Параметры хранимых процедур и функций</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">.profiling</td><td style="height: 29.8px;">Информация о профилях пользователей</td></tr></tbody></table>

```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/
```

**Настройка сессии**

<table border="1" id="bkmrk-%D0%A3%D1%80%D0%BE%D0%B2%D0%B5%D0%BD%D1%8C-%D0%B8%D0%B7%D0%BE%D0%BB%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%BD%D0%BE" style="border-collapse: collapse; width: 100%; height: 165.781px;"><colgroup><col style="width: 20%;"></col><col style="width: 20%;"></col><col style="width: 20%;"></col><col style="width: 20%;"></col><col style="width: 20%;"></col></colgroup><thead><tr style="height: 46.5938px;"><td style="height: 46.5938px;">Уровень изолированности</td><td style="height: 46.5938px;">Черновое чтение</td><td style="height: 46.5938px;">Неповторяющееся чтение</td><td style="height: 46.5938px;">Фантомное чтение</td><td style="height: 46.5938px;">Блокировка чтения</td></tr></thead><tbody><tr style="height: 29.7969px;"><td style="height: 29.7969px;">READ UNCOMMITTED</td><td style="height: 29.7969px;">Да</td><td style="height: 29.7969px;">Да</td><td style="height: 29.7969px;">Да</td><td style="height: 29.7969px;">Нет</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">READ COMMITTED</td><td style="height: 29.7969px;">Нет</td><td style="height: 29.7969px;">Да</td><td style="height: 29.7969px;">Да</td><td style="height: 29.7969px;">Нет</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">REPEATABLE READ</td><td style="height: 29.7969px;">Нет</td><td style="height: 29.7969px;">Нет</td><td style="height: 29.7969px;">Да</td><td style="height: 29.7969px;">Нет</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">SERIALIZABLE</td><td style="height: 29.7969px;">Нет</td><td style="height: 29.7969px;">Нет</td><td style="height: 29.7969px;">Нет</td><td style="height: 29.7969px;">Да</td></tr></tbody></table>

Можно установить на уровне сессии

```
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 - включенные инструменты.

Структура:

<table border="1" id="bkmrk-name-%D0%9D%D0%B0%D0%B7%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D0%B8%D0%BD%D1%81%D1%82%D1%80%D1%83" style="border-collapse: collapse; width: 100%;"><colgroup><col style="width: 18.1548%;"></col><col style="width: 81.9644%;"></col></colgroup><tbody><tr><td>NAME</td><td>Название инструмента</td></tr><tr><td>ENABLED</td><td>Включен или нет</td></tr><tr><td>TIMED</td><td>Планировщик. Если disabled, работать не будет</td></tr></tbody></table>

**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';
```

Привилегии:

<table border="1" id="bkmrk-alter-%D0%9C%D0%B5%D0%BD%D1%8F%D1%82%D1%8C-%D1%81%D1%82%D1%80%D1%83%D0%BA%D1%82%D1%83" style="border-collapse: collapse; width: 100%; height: 417.157px;"><colgroup><col style="width: 24.1072%;"></col><col style="width: 76.012%;"></col></colgroup><tbody><tr style="height: 29.7969px;"><td style="height: 29.7969px;">ALTER</td><td style="height: 29.7969px;">Менять структуру таблицы или БД</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">CREATE</td><td style="height: 29.7969px;">Создавать новые БД и таблицы</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">DELETE</td><td style="height: 29.7969px;">Удалять строки в таблице</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">INSERT</td><td style="height: 29.7969px;">Добавлять строки в таблицу</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">SELECT</td><td style="height: 29.7969px;">Читать данные из таблицы</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">UPDATE</td><td style="height: 29.7969px;">Обновлять данные в таблице</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">DROP</td><td style="height: 29.7969px;">Удалять БД</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">ALL PRIVILEGES</td><td style="height: 29.7969px;">все, кроме GRANT;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">USAGE PRIVILEGES</td><td style="height: 29.7969px;">никаких привилегий;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">FILE</td><td style="height: 29.7969px;">разрешает читать файлы на сервере;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">INDEX</td><td style="height: 29.7969px;">создавать индексы для таблиц;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">DROP</td><td style="height: 29.7969px;">удалять таблицы;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">EVENT</td><td style="height: 29.7969px;">обработка событий;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">TRIGGER</td><td style="height: 29.7969px;">создание триггеров.</td></tr></tbody></table>

Привилегия на действия

<table border="1" id="bkmrk-grant-%D0%B8%D0%B7%D0%BC%D0%B5%D0%BD%D1%8F%D1%82%D1%8C-%D0%BF%D1%80%D0%B0%D0%B2%D0%B0" style="border-collapse: collapse; width: 100%; height: 268.172px;"><colgroup><col style="width: 23.8691%;"></col><col style="width: 76.2501%;"></col></colgroup><tbody><tr style="height: 29.7969px;"><td style="height: 29.7969px;">GRANT</td><td style="height: 29.7969px;">изменять права пользователей;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">SUPER</td><td style="height: 29.7969px;">суперпользователь;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">PROCESS</td><td style="height: 29.7969px;">получение информации о состоянии MySQL;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">RELOAD</td><td style="height: 29.7969px;">позволяет перезагружать таблицы привилегий;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">SHUTDOWN</td><td style="height: 29.7969px;">позволяет отключать или перезапускать базу данных;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">SHOW DATABASES</td><td style="height: 29.7969px;">просмотр списка баз данных;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">LOCK TABLES</td><td style="height: 29.7969px;">блокирование таблиц при использовании SELECT;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">REFERENCES</td><td style="height: 29.7969px;">создание внешних ключей для связывания таблиц;</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">CREATE USER</td><td style="height: 29.7969px;">создание пользователей;</td></tr></tbody></table>

```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
```

# Для теста

Пар

<iframe frameborder="0" height="504" src="https://yandex.ru/map-widget/v1/?um=constructor%3A2b2129bb27ee13d2fe8e0c5c86e90c691fac9ef73de8e1be402a3d1e9fec108d&source=constructor" width="668"></iframe>

# PostgreSQL



# Общая информация

Источник: Mastering PostgreSQL 15