Руководство по сбою синтаксиса SQL

В этой статье рассматривается общий синтаксис реляционных баз данных. Из-за нехватки места в этой статье основное внимание уделяется использованию и не будут объясняться функции и принципы.

1. Основные понятия

терминология базы данных

数据库（database）- Контейнер (обычно файл или группа файлов), который содержит упорядоченные данные.
数据表（table）- Структурированный список определенного типа данных.
模式（schema）- Информация о структуре и свойствах баз данных и таблиц. Схема определяет, как данные хранятся в таблице, в том числе, какие данные хранятся, как данные декомпозируются и как именуется каждая часть информации. Базы данных и таблицы имеют схемы.
列（column）- Поле в таблице. Все таблицы состоят из одного или нескольких столбцов.
行（row）- Запись в таблице.
主键（primary key）- Столбец (или набор столбцов), значение которого однозначно идентифицирует каждую строку в таблице.

Синтаксис SQL

SQL (язык структурированных запросов), стандартный SQL управляется комитетом по стандартам ANSI, поэтому он называется ANSI SQL. Каждая СУБД имеет свою реализацию, например PL/SQL, Transact-SQL и т. д.

Структура синтаксиса SQL

sql-syntax

Структуры синтаксиса SQL включают:

子句- является компонентом операторов и запросов. (В некоторых случаях они необязательны.)
表达式- Может создавать любое скалярное значение или таблицу базы данных по столбцам и строкам
谓词- Укажите условия для трехзначной логики SQL (3VL) (истина/ложь/неизвестно) или логические значения истинности, которые необходимо оценить, и ограничьте влияние операторов и запросов или измените ход программы.
查询- Получить данные на основе определенных критериев. Это важная часть SQL.
语句- может постоянно влиять на схему, и данные также могут контролироваться транзакциями базы данных, программным потоком, соединениями, сеансами или диагностикой.

Основы синтаксиса SQL

Операторы SQL не чувствительны к регистру, но различаются ли имя таблицы базы данных, имя столбца и значение, зависит от конкретной СУБД и конфигурации.

Например:SELECTиselect,SelectТакие же.

Перед несколькими операторами SQL должна стоять точка с запятой (;) разделенный.
При обработке операторов SQLВсе пробелы игнорируются. Операторы SQL могут быть записаны в одну строку или разделены на несколько строк.

-- 一行 SQL 语句
UPDATE user SET username='robot', password='robot' WHERE username = 'root';

-- 多行 SQL 语句
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

SQL поддерживает три вида комментариев

## 注释1
-- 注释2
/* 注释3 */

Классификация SQL

Язык определения данных (DDL)

Язык определения данных (DDL) — это язык в наборе языков SQL, отвечающий за определение структур данных и объектов базы данных.

Основная функция DDL —Определить объекты базы данных.

Основные инструкции DDL:CREATE,ALTER,DROP.

Язык манипулирования данными (DML)

Язык манипулирования данными (DML) — это оператор программирования, используемый для работы с базой данных и выполнения операций доступа к объектам и данным в базе данных.

Основные функции DML:доступ к данным, поэтому его синтаксис начинается сЧтение и запись базы данныхглавный.

Основные инструкции DML:INSERT,UPDATE,DELETE,SELECT. Эти четыре инструкции вместе называются CRUD (Create, Read, Update, Delete), то есть добавить, удалить, изменить и проверить.

Язык управления транзакциями (TCL)

Язык управления транзакциями (TCL) используется дляУправление транзакциями в базе данных. Они используются для управления изменениями, сделанными операторами DML. Он также позволяет группировать операторы в логические транзакции.

Основные инструкции TCL:COMMIT,ROLLBACK.

Язык управления данными (DCL)

Язык управления данными (DCL) — это инструкция, которая может управлять правами доступа к данным.Он может управлять правами управления определенной учетной записи пользователя на объекты базы данных, такие как таблицы данных, таблицы представлений, хранимые процедуры и пользовательские функции.

Основные инструкции DCL:GRANT,REVOKE.

ДКЛ начинается сКонтролировать доступ пользователейГлавным образом, поэтому его командная практика не сложна, а разрешения, которыми может управлять DCL:CONNECT,SELECT,INSERT,UPDATE,DELETE,EXECUTE,USAGE,REFERENCES.

В зависимости от СУБД и различных объектов безопасности поддерживаемый ею контроль разрешений различается.

(Ниже приведено использование оператора DML)

2. Добавления, исключения и изменения

CRUD, также известный как CRUD, является базовой операцией в основных операциях с базой данных.

вставить данные

INSERT INTOОператоры используются для вставки новых записей в таблицу.

вставить полную строку

INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');

вставить часть строки

INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');

Вставьте данные запроса

INSERT INTO user(username)
SELECT name
FROM account;

обновить данные

UPDATEОператоры используются для обновления записей в таблице.

UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

удалить данные

DELETEОператоры используются для удаления записей в таблице.

TRUNCATE TABLEТаблицу можно очистить, то есть удалить все строки.

Удалить указанные данные из таблицы

DELETE FROM user
WHERE username = 'robot';

Очистить данные в таблице

TRUNCATE TABLE user;

Данные запроса

SELECTОператоры используются для запроса данных из базы данных.

DISTINCTИспользуется для возврата уникально различных значений. Он работает со всеми столбцами, что означает, что все столбцы одинаковы, если они имеют одинаковое значение.

LIMITОграничьте количество возвращаемых строк. Параметров может быть два, первый параметр — начальная строка, начиная с 0, второй параметр — общее количество возвращаемых строк.

ASC: в порядке возрастания (по умолчанию)

DESC: в порядке убывания

Запросить один столбец

SELECT prod_name
FROM products;

запросить несколько столбцов

SELECT prod_id, prod_name, prod_price
FROM products;

запросить все столбцы

ELECT *
FROM products;

запрос для разных значений

SELECT DISTINCT
vend_id FROM products;

Ограничить результаты запроса

-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;

3. Подзапросы

Подзапрос — это SQL-запрос, вложенный в более крупный запрос. Подзапросы также известны какВнутренний запросилиВнутренний отбор, а оператор, содержащий подзапрос, также называетсяВнешний запросилиВнешний выбор.

Подзапросы могут быть вложены вSELECT,INSERT,UPDATEилиDELETEвнутри инструкции или в другом подзапросе.
Подзапросы обычно находятся в другомSELECTПриговорWHEREдобавлен пункт.
Вы можете использовать операторы сравнения, например>,<,или=. Операторы сравнения также могут быть многострочными операторами, такими какIN,ANYилиALL.
Подзапросы должны быть заключены в круглые скобки()скобка.
Внутренний запрос выполняется первым перед его родительским запросом, чтобы результаты внутреннего запроса могли быть переданы внешнему запросу. Процесс выполнения может относиться к следующему рисунку:

подзапрос подзапрос

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'RGAN01'));

WHERE

WHEREПредложения используются для фильтрации записей, т. е. сужения объема данных, к которым осуществляется доступ.
WHEREс последующим возвращениемtrueилиfalseусловия о.
WHEREС участиемSELECT,UPDATEиDELETEиспользовать вместе.
допустимыйWHEREОператоры, используемые в пунктах

оператор	описывать
=	равный
<>	не равно. Примечание. В некоторых версиях SQL этот оператор может быть записан как !=
>	больше, чем
<	меньше, чем
>=	больше или равно
<=	меньше или равно
BETWEEN	в пределах определенного диапазона
LIKE	поиск шаблона
IN	Укажите несколько возможных значений для столбца

SELECTв предложенииWHEREпункт

SELECT * FROM Customers
WHERE cust_name = 'Kids Place';

UPDATEв предложенииWHEREпункт

UPDATE Customers
SET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';

DELETEв предложенииWHEREпункт

DELETE FROM Customers
WHERE cust_name = 'Kids Place';

В и МЕЖДУ

INоператор вWHEREОн используется в предложении для выбора одного из указанных значений.
BETWEENоператор вWHEREПункт, заключается в выборе значения в пределах определенного диапазона.

В примере

SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

МЕЖДУ примером

SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;

И, ИЛИ, НЕ

AND,OR,NOTЭто логическая инструкция обработки условий фильтрации.
ANDприоритет надOR, для уточнения порядка обработки можно использовать().
ANDОператор указывает, что должны быть выполнены левое и правое условия.
ORОператор указывает, что любое из левых и правых условий может быть выполнено.
NOTОператоры используются для отрицания условия.

И пример

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

ИЛИ пример

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

НЕ пример

SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;

LIKE

LIKEоператор вWHEREпредложение, чтобы определить, соответствует ли строка шаблону.
Используется, только если поле является текстовым значениемLIKE.
LIKEПоддерживаются два варианта сопоставления подстановочных знаков:%и_.
Не злоупотребляйте подстановочными знаками, подстановочные знаки в начале будут очень медленными.
%Указывает любое количество вхождений любого символа.
_Указывает, что любой символ встречается один раз.

% Пример

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';

_ пример

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

В-четвертых, соединение и сочетание

Присоединяйтесь (присоединяйтесь)

еслиJOINимеет хотя бы одно общее поле и между ними существует связь, т.JOINМожет работать на двух и более столах.

Присоединение используется для объединения нескольких таблиц, используйтеJOINключевые слова и условные операторы используютONвместоWHERE.

JOINОставьте базовую таблицу (структуру и данные) без изменений.

JOINСуществует два типа соединений: внутренние соединения и внешние соединения.

Внутреннее соединение, также известное как соединение по эквивалентности, использует INNER.JOINключевые слова. Возвращает декартово произведение без условного оператора.

Самосоединение можно рассматривать как разновидность внутреннего соединения, но связанная таблица — это она сама.

Естественные соединения соединяют столбцы с одинаковыми именами через тест =, и может быть несколько столбцов с одинаковыми именами.

Внутреннее соединение против естественного соединения

Внутренние соединения обеспечивают объединение столбцов, в то время как естественные соединения автоматически объединяют все столбцы с одинаковыми именами.

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

Левое внешнее соединение должно сохранять несвязанные строки левой таблицы.

Правое внешнее соединение должно сохранять несвязанные строки правой таблицы.

соединения против подзапросов

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

Внутреннее соединение (INNER JOIN)

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

самоподключение

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ

SELECT *
FROM Products
NATURAL JOIN Customers;

ЛЕВОЕ СОЕДИНЕНИЕ

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

Правое соединение (ПРАВОЕ СОЕДИНЕНИЕ)

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

комбинация (СОЮЗ)

UNIONоператор объединяет результаты двух или более запросов и создает набор результатов, содержащийUNIONвыбранные строки, участвующие в запросе.
UNIONосновные правила
- Количество и порядок столбцов должны быть одинаковыми для всех запросов.
- Типы данных столбцов таблиц, участвующих в каждом запросе, должны быть одинаковыми или совместимыми.
- Обычно возвращаемые имена столбцов берутся из первого запроса.
По умолчанию эта же строка будет удалена. Если вам нужно сохранить ту же строку, используйтеUNION ALL.
может содержать только одинORDER BYпункт и должен быть в конце утверждения.
Сценарии применения
- Возврат структурированных данных из разных таблиц в одном запросе.
- Выполнять несколько запросов к таблице, возвращая данные одним запросом.

Комбинированный запрос

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';

JOIN vs UNION

JOIN vs UNION
- JOINСтолбцы в таблице соединения могут быть разными, но вUNION, количество и порядок столбцов должны быть одинаковыми для всех запросов.
- UNIONпоместите строки после запроса вместе (по вертикали), ноJOINСтолбцы после запроса складываются вместе (по горизонтали), т.е. образует декартово произведение.

5. Функция

🔔 Примечание. Функции, как правило, различаются для разных баз данных и, следовательно, не переносимы. В этом разделе в качестве примера в основном используются функции Mysql.

обработка текста

функция	инструкция
`LEFT()`,`RIGHT()`	левый или правый символ
`LOWER()`,`UPPER()`	Преобразование в нижний или верхний регистр
`LTRIM()`,`RTIM()`	Удалить пробелы слева или справа
`LENGTH()`	длина
`SOUNDEX()`	Преобразовать в фонетическое значение

в,SOUNDEX()Строка может быть преобразована в буквенно-цифровой шаблон, описывающий ее фонетическое представление.

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

Обработка даты и времени

Формат даты:YYYY-MM-DD
Формат времени:HH:MM:SS

функция	инструкция
`AddDate()`	Добавьте дату (день, неделя и т. д.)
`AddTime()`	Добавьте время (часы, минуты и т. д.)
`CurDate()`	Возвращает текущую дату
`CurTime()`	вернуть текущее время
`Date()`	Возвращает часть даты datetime
`DateDiff()`	Вычислить разницу между двумя датами
`Date_Add()`	Очень гибкие арифметические функции даты
`Date_Format()`	Возвращает отформатированную строку даты или времени
`Day()`	Возвращает дневную часть даты
`DayOfWeek()`	Для даты возвращает соответствующий день недели
`Hour()`	Возвращает часовую часть времени
`Minute()`	Возвращает минутную часть времени
`Month()`	Возвращает месячную часть даты
`Now()`	Возвращает текущую дату и время
`Second()`	Возвращает секунды часть времени
`Time()`	Возвращает временную часть datetime
`Year()`	Возвращает часть года даты

mysql> SELECT NOW();

2018-4-14 20:25:11

Численная обработка

функция	инструкция
SIN()	Синус
COS()	косинус
TAN()	Тангенс
ABS()	абсолютная величина
SQRT()	квадратный корень
MOD()	остаток
EXP()	показатель
PI()	ЧИСЛО ПИ
RAND()	случайный номер

Резюме

функция	инструкция
`AVG()`	Возвращает среднее значение столбца
`COUNT()`	Возвращает количество строк в столбце
`MAX()`	Возвращает максимальное значение столбца
`MIN()`	Возвращает минимальное значение столбца
`SUM()`	Возвращает сумму значений столбца

AVG()Строки NULL игнорируются.

Используйте DISTINCT, чтобы итоговые значения функций суммировали различные значения.

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable

6. Сортировка и группировка

ORDER BY

ORDER BYИспользуется для сортировки набора результатов.
- ASC: в порядке возрастания (по умолчанию)
- DESC: в порядке убывания
Вы можете сортировать по нескольким столбцам и указывать разные сортировки для каждого столбца.

Укажите направление сортировки для нескольких столбцов

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

GROUP BY

GROUP BYпредложение группирует записи в итоговые строки.
GROUP BYВозвращает одну запись для каждой группы.
GROUP BYОбычно также задействованы агрегации: COUNT, MAX, SUM, AVG и т. д.
GROUP BYВы можете группировать по одному или нескольким столбцам.
GROUP BYПосле сортировки по полю группировкиORDER BYВы можете сортировать по полям сводки.

группировка

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;

Сортировать по группам

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

HAVING

HAVINGдля резюмеGROUP BYРезультаты фильтруются.
HAVINGтребует наличияGROUP BYпункт.
WHEREиHAVINGможет быть в том же запросе.
HAVING vs WHERE
- WHEREиHAVINGиспользуются для фильтрации.
- HAVINGРаботает с агрегированными групповыми записями, WHERE работает с отдельными записями.

Фильтровать данные с помощью WHERE и HAVING

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;

(Ниже приведено использование оператора DDL)

7. Определение данных

Основная функция DDL — определение объектов базы данных (таких как базы данных, таблицы данных, представления, индексы и т. д.).

База данных (DATABASE)

создать базу данных

CREATE DATABASE test;

удалить базу данных

DROP DATABASE test;

выберите базу данных

USE test;

Таблица данных (ТАБЛИЦА)

Создать таблицу данных

Обычное создание

CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';

Создать новую таблицу из существующей таблицы

CREATE TABLE vip_user AS
SELECT * FROM user;

удалить таблицу данных

DROP TABLE user;

Изменить таблицу

добавить столбец

ALTER TABLE user
ADD age int(3);

удалить столбец

ALTER TABLE user
DROP COLUMN age;

Изменить столбец

ALTER TABLE `user`
MODIFY COLUMN age tinyint;

добавить первичный ключ

ALTER TABLE user
ADD PRIMARY KEY (id);

удалить первичный ключ

ALTER TABLE user
DROP PRIMARY KEY;

Посмотреть (ВИД)

определение

Представление — это визуальная таблица, основанная на наборе результатов оператора SQL.

Представление — это виртуальная таблица, которая сама не содержит данных, поэтому ее нельзя индексировать. Работа с представлением такая же, как и с обычной таблицей.

эффект

Упростите сложные операции SQL, такие как сложные соединения;

Используйте только часть данных из фактической таблицы;

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

Изменить формат данных и представление.

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

CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;

удалить вид

DROP VIEW top_10_user_view;

индекс (ИНДЕКС)

эффект

Данные можно запрашивать быстрее и эффективнее с помощью индексов.

Пользователи не могут видеть индексы, их можно использовать только для ускорения запросов.

Уведомление

Обновление таблицы с индексом занимает больше времени, чем обновление таблицы без индекса, потому что сам индекс также нуждается в обновлении. Поэтому идеально создавать индексы только для тех столбцов (и таблиц), которые часто используются для поиска.

уникальный индекс

Уникальный индекс указывает, что каждое значение индекса этого индекса соответствует только уникальной записи данных.

создать индекс

CREATE INDEX user_index
ON user (id);

Создать уникальный индекс

CREATE UNIQUE INDEX user_index
ON user (id);

падение индекса

ALTER TABLE user
DROP INDEX user_index;

ограничение

Ограничения SQL используются для указания правил для данных в таблице.

Если есть поведение данных, которое нарушает ограничение, поведение завершается ограничением.
Ограничения можно указать при создании таблицы (через оператор CREATE TABLE) или после создания таблицы (через оператор ALTER TABLE).
Тип ограничения
- NOT NULL- Указывает, что столбец не может хранить значения NULL.
- UNIQUE- Гарантия того, что каждая строка столбца должна иметь уникальное значение.
- PRIMARY KEY- Комбинация NOT NULL и UNIQUE. Обеспечение уникальной идентификации столбца (или комбинации двух столбцов) упрощает и ускоряет поиск определенной записи в таблице.
- FOREIGN KEY- Гарантия ссылочной целостности, когда данные в одной таблице совпадают со значениями в другой таблице.
- CHECK- Гарантирует соответствие значений в столбце заданным критериям.
- DEFAULT- Указывает значение по умолчанию, когда столбцу не присвоено значение.

Используйте ограничения при создании таблиц:

CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用户名',
  Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '邮箱地址',
  Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',
  PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

(Ниже приведено использование оператора TCL)

8. Обработка транзакций

Оператор SELECT не может быть отменен, и нет смысла откатывать оператор SELECT, а также нельзя откатывать операторы CREATE и DROP.

MySQL по умолчанию использует неявную фиксацию, каждый раз, когда оператор выполняется, он обрабатывается как транзакция, а затем фиксируется. когда он появитсяSTART TRANSACTIONзаявление, неявная фиксация отключена; когдаCOMMITилиROLLBACKПосле выполнения инструкции транзакция автоматически закрывается, и неявная фиксация возобновляется.

пройти черезset autocommit=0Autocommit можно отменить до тех пор, покаset autocommit=1commit; флаг autocommit относится к конкретному соединению, а не к конкретному серверу.

инструкция

START TRANSACTION- Директивы используются для обозначения начальной точки транзакции.

SAVEPOINT- Директивы используются для создания точек удержания.

ROLLBACK TO- Инструкция используется для отката к указанной точке сохранения; если точка сохранения не установлена, выполняется возврат кSTART TRANSACTIONутверждение.

COMMIT- Зафиксировать транзакцию.

-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

(Ниже приведено использование оператора DCL)

Девять, контроль разрешений

GRANT и REVOKE контролируют доступ на нескольких уровнях:

Весь сервер использует Grant All и Revoke All;

Для всей базы данных используйте ON database.*;

Для конкретной таблицы используйте ON database.table;

определенный столбец;

конкретная хранимая процедура.

Вновь созданная учетная запись не имеет никаких разрешений.

Учетные записи определяются в форме имя_пользователя@хост, а имя_пользователя@% использует имя хоста по умолчанию.
Информация об учетной записи MySQL хранится в базе данных mysql.
USE mysql;
SELECT user FROM user;

зарегистрироваться

CREATE USER myuser IDENTIFIED BY 'mypassword';

Изменить имя учетной записи

UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;

удалить аккаунт

DROP USER myuser;

Просмотр разрешений

SHOW GRANTS FOR myuser;

Разрешение предоставлено

GRANT SELECT, INSERT ON *.* TO myuser;

удалить разрешение

REVOKE SELECT, INSERT ON *.* FROM myuser;

изменить пароль

SET PASSWORD FOR myuser = 'mypass';

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

Хранимая процедура может рассматриваться как пакет последовательности операций SQL;

Преимущества использования хранимых процедур

Инкапсуляция кода для обеспечения определенной степени безопасности;

повторное использование кода;

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

Создать хранимую процедуру

Для создания хранимых процедур в командной строке требуются настраиваемые разделители, поскольку командная строка начинается с;Это терминатор, а хранимая процедура также содержит точку с запятой, поэтому эта часть точки с запятой будет ошибочно принята за терминатор, что приведет к синтаксической ошибке.

Содержит три параметра in, out и inout.

Присвоение значений переменным требует использования оператора select into.

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

Создать хранимую процедуру

DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;

    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
;;
DELIMITER ;

использовать хранимую процедуру

set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

Одиннадцать, курсор

Курсор — это запрос к базе данных, хранящийся на сервере СУБД, это не оператор SELECT, а результирующий набор, извлекаемый оператором.

Курсор можно использовать в хранимой процедуре для перемещения по набору результатов.

Курсоры в основном используются в интерактивных приложениях, где пользователю необходимо просмотреть и изменить любую строку в наборе данных.

Четыре шага для использования курсоров:

Объявите курсор, этот процесс фактически не извлекает данные;

открыть курсор;

извлечь данные;

закрыть курсор;

DELIMITER $
CREATE  PROCEDURE getTotal()
BEGIN
    DECLARE total INT;
    -- 创建接收游标数据的变量
    DECLARE sid INT;
    DECLARE sname VARCHAR(10);
    -- 创建总数变量
    DECLARE sage INT;
    -- 创建结束标志变量
    DECLARE done INT DEFAULT false;
    -- 创建游标
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
    -- 指定游标循环结束时的返回值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    SET total = 0;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE(NOT done)
    DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;

    CLOSE cur;
    SELECT total;
END $
DELIMITER ;

-- 调用存储过程
call getTotal();

12. Триггер

Триггер — это объект базы данных, связанный с табличными операциями.При возникновении заданного события на таблице, где находится триггер, будет вызван объект, то есть событие операции таблицы запускает выполнение триггера на таблице.

Триггеры можно использовать для проведения аудита и регистрации изменений в другой таблице.

MySQL не позволяет использовать операторы CALL в триггерах, то есть нельзя вызывать хранимые процедуры.

BEGINиEND

Когда условие запуска триггера выполнено, он будет выполнятьсяBEGINиENDмежду триггерами для выполнения действий.

🔔 Примечание. В MySQL точка с запятой;Это идентификатор конца оператора.Когда встречается точка с запятой, это означает, что оператор закончился, и MySQL может начать выполнение. Поэтому интерпретатор начинает выполнение, когда встречает точку с запятой в действии выполнения триггера, а затем сообщает об ошибке, поскольку не найдено END, соответствующее BEGIN.

будет использоваться тогдаDELIMITERКоманда (DELIMITER — разделитель, значение разделителя). Это команда, которая не требует маркера конца оператора, и имеет следующий синтаксис:DELIMITER new_delemiter.new_delemiterМожно установить 1 или более символов длины, по умолчанию используется точка с запятой.;, мы можем изменить его на другие символы, такие как$ - DELIMITER $. На утверждения после этого, заканчивающиеся точкой с запятой, интерпретатор не ответит, только если встретит$, считается концом оператора. Обратите внимание, что после его использования мы должны не забыть изменить его обратно.

NEWиOLD

определено в MySQLNEWиOLDКлючевое слово используется для указания строки данных, вызвавшей срабатывание триггера в таблице, в которой находится триггер.
существуетINSERTтипа триггер,NEWиспользуется для обозначения того, что будет (BEFORE) или был(AFTER) добавлены новые данные;
существуетUPDATEтипа триггер,OLDИспользуется для представления исходных данных, которые будут или были изменены,NEWИспользуется для представления новых данных, подлежащих изменению;
существуетDELETEтипа триггер,OLDиспользуется для представления исходных данных, которые должны быть удалены или были удалены;
Инструкции:NEW.columnName(columnName — имя столбца соответствующей таблицы данных)

Создать триггер

Совет: Для того, чтобы понять суть триггеров, необходимо сначала разобраться с инструкцией по созданию триггеров.

CREATE TRIGGERДирективы используются для создания триггеров.

грамматика:

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

инструкция:

Trigger_name: имя триггера
trigger_time: время триггера. ценностьBEFOREилиAFTER.
trigger_event: событие прослушивания триггера. ценностьINSERT,UPDATEилиDELETE.
table_name: цель прослушивания триггера. Указывает, для какой таблицы создать триггер.
ДЛЯ КАЖДОЙ СТРОКИ: мониторинг на уровне строк, фиксированный метод записи Mysql, отличный от других СУБД.
trigger_statements: Триггер выполняет действия. представляет собой список из одного или нескольких операторов SQL, перед каждым оператором в списке должна стоять точка с запятой;в конец.

Пример:

DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user',  now());
END $
DELIMITER ;

Посмотреть триггеры

SHOW TRIGGERS;

удалить триггер

DROP TRIGGER IF EXISTS trigger_insert_user;

(Заканчивать)

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

Бенфорта, SQL должен знать и должен знать [M], People's Posts and Telecommunications Press, 2013.
«Мелкая и глубокая» реализация транзакций в MySQL
Обучение MySQL -- триггер
Запись в Википедии — SQL
www.sitesbay.com/sql/index
SQL Subqueries
Quick breakdown of the types of joins
SQL UNION
SQL database security
Хранимые процедуры в Mysql