Достаточно практической коллекции операторов SQL

предисловие

Статья по дизайну гипотетического приложенияawesome_appДля основной линии вы можете создавать и изменять базы данных, таблицы, атрибуты полей, индексы, наборы символов, значения по умолчанию, автоинкремент, добавление, удаление, изменение, запрос, многотабличный запрос, встроенные функции и другие практические SQL заявления с нуля. Добавьте эту статью в закладки и попрощайтесь с разрозненными и неэффективными поисками часто используемых операторов SQL. Все SQL-запросы проверяются в MySQL, и их можно сохранить для будущего просмотра и справки, или вы можете сделать это вместе.Если MySQL не установлен, вы можете обратиться к«macOS установить mysql»(Установка Windows аналогична).

1. Создать

1.1 Создайте базу данных

грамматика:create database db_name

Пример: создание базы данных приложенияawesome_app

create database `awesome_app`

1.2 Создайте таблицу

грамматика:create table table_name ( ... columns )

Пример: создание пользовательской таблицыusers

create table `users`
(
    `id` int,
    `name` char(10),
    `avatar` varchar(300),
    `regtime` date
)

1.3 Создайте индекс

грамматика:create index index_name on table_name (column_name)

Пример: для пользователяidсоздать индексidx_id

create index `idx_id` on `users` (`id`)
/* 创建唯一索引 */
create unique index `idx_id` on `users` (`id`)

1.4 Создайте первичный ключ для существующего столбца

Более распространенный способ — добавить строку после всех определений столбцов в операторе CREATE TABLE.primary key (column_name).

грамматика:alter table table_name add primary key (column_name)

Пример: поставить пользователяidустановить в качестве первичного ключа

alter table users add primary key (`id`)

1.5 Создание ограничений автоинкремента для существующих столбцов

Более распространенный способ — добавить столбец с автоматическим приращением в операторе создания таблицы.id int not null auto_increment.

alter table `users` modify `id` int not null auto_increment

2. Вставьте

грамматика:

• insert into table_name values (value1, value2, ...)
• insert into table_name (column1, column2, ...) values (value1, value2, ...)

Пример: Добавление зарегистрированного пользователя

insert into `users` values (1, 'ken', 'http://cdn.awesome_app.com/path/to/xxx/avatar1.jpg', curdate())
/* 指定列插入 */
insert into `users` (`name`, `avatar`) values ('bill', 'http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg')

3. Модификация

3.1 Изменение записей данных

грамматика:

• update table_name set column=new_value where condition
• update table_name set column1=new_value1,column2=new_value2,... where condition

Пример:

update `users` set `regtime`=curdate() where `regtime` is null
/* 一次修改多列 */
update `users` set `name`='steven',`avatar`='http://cdn.awesome_app.com/path/to/xxx/steven.jpg' where `id`=1

3.2 Измените набор символов базы данных наutf8

alter database `awesome_app` default character set utf8

3.3 Изменение набора символов таблицыutf8

alter table `users` convert to character set utf8

3.4 Измените набор символов поля таблицы наutf8

alter table `users` modify `name` char(10) character set utf8

3.5 Изменить тип поля

alter table `users` modify `regtime` datetime not null

3.5 Изменить значение поля по умолчанию

alter table `users` alter `regtime` set default '2019-10-12 00:00:00'
/* 设置默认为当前时间 current_timestamp，需要重新定义整个列 */
alter table `users` modify `regtime` datetime not null default current_timestamp

3.6 Изменить комментарии к полям

alter table `users` modify `id` int not null auto_increment comment '用户ID';
alter table `users` modify `name` char(10) comment '用户名';
alter table `users` modify `avatar` varchar(300) comment '用户头像';
alter table `users` modify `regtime` datetime not null default current_timestamp comment '注册时间';

После модификации просмотрите измененные столбцы:

mysql> show full columns from users;
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
| Field   | Type         | Collation       | Null | Key | Default           | Extra          | Privileges                      | Comment      |
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
| id      | int(11)      | NULL            | NO   | PRI | NULL              | auto_increment | select,insert,update,references | 用户ID       |
| name    | char(10)     | utf8_general_ci | YES  |     | NULL              |                | select,insert,update,references | 用户名       |
| avatar  | varchar(300) | utf8_general_ci | YES  |     | NULL              |                | select,insert,update,references | 用户头像     |
| regtime | datetime     | NULL            | NO   |     | CURRENT_TIMESTAMP |                | select,insert,update,references | 注册时间     |
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+

4. Удалить

4.1 Удаление записей данных

грамматика:delete from table_name where condition

Пример: удалить пользователя, чье имя пользователя не заполнено

# 先增加一条用户名为空的用户
mysql> insert into `users` (`regtime`) values (curdate());
mysql> select * from users;
+----+--------+----------------------------------------------------+------------+
| id | name   | avatar                                             | regtime    |
+----+--------+----------------------------------------------------+------------+
|  1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg  | 2019-10-12 |
|  2 | bill   | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
|  3 | NULL   | NULL                                               | 2019-10-12 |
+----+--------+----------------------------------------------------+------------+
# 删除用户名为空的行
mysql> delete from `users` where `name` is null;
mysql> select * from users;
+----+--------+----------------------------------------------------+------------+
| id | name   | avatar                                             | regtime    |
+----+--------+----------------------------------------------------+------------+
|  1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg  | 2019-10-12 |
|  2 | bill   | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
+----+--------+----------------------------------------------------+------------+

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

drop database if exists `awesome_app`

4.3 Удалить таблицу

drop table if exists `users`

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

Эта операция эквивалентнаdrop tableСноваcreate table, поэтому необходимо иметьdropразрешения.

truncate table `users`

4.5 Удаление индексов

drop index `idx_id` on `users`

5. Запрос

5.1 Синтаксис

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

5.2 Запрос одной таблицы

5.2.1 Подготовка данных:

insert into users (`name`, `avatar`) values
('张三', 'http://cdn.awesome_app.com/path/to/xxx/3.jpg'),
('李四', 'http://cdn.awesome_app.com/path/to/xxx/4.jpg'),
('王五', 'http://cdn.awesome_app.com/path/to/xxx/5.jpg'),
('马六', 'http://cdn.awesome_app.com/path/to/xxx/6.jpg'),
('肖七', 'http://cdn.awesome_app.com/path/to/xxx/7.jpg'),
('刘八', 'http://cdn.awesome_app.com/path/to/xxx/8.jpg'),
('杨九', 'http://cdn.awesome_app.com/path/to/xxx/9.jpg'),
('郑十', 'http://cdn.awesome_app.com/path/to/xxx/10.jpg');

/* 增加重复行 */
insert into users (`name`, `avatar`) values
('张三', 'http://cdn.awesome_app.com/path/to/xxx/3.jpg'),
('李四', 'http://cdn.awesome_app.com/path/to/xxx/4.jpg'),
('王五', 'http://cdn.awesome_app.com/path/to/xxx/5.jpg');

5.2.2 Запрос всех столбцов

mysql> select * from users;
+----+--------+----------------------------------------------------+---------------------+
| id | name   | avatar                                             | regtime             |
+----+--------+----------------------------------------------------+---------------------+
|  1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg  | 2019-10-12 00:00:00 |
|  2 | bill   | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 00:00:00 |
|  3 | 张三   | http://cdn.awesome_app.com/path/to/xxx/3.jpg       | 2019-10-13 10:58:37 |
|  4 | 李四   | http://cdn.awesome_app.com/path/to/xxx/4.jpg       | 2019-10-13 10:58:37 |
|  5 | 王五   | http://cdn.awesome_app.com/path/to/xxx/5.jpg       | 2019-10-13 10:58:37 |
|  6 | 马六   | http://cdn.awesome_app.com/path/to/xxx/6.jpg       | 2019-10-13 10:58:37 |
|  7 | 肖七   | http://cdn.awesome_app.com/path/to/xxx/7.jpg       | 2019-10-13 10:58:37 |
|  8 | 刘八   | http://cdn.awesome_app.com/path/to/xxx/8.jpg       | 2019-10-13 10:58:37 |
|  9 | 杨九   | http://cdn.awesome_app.com/path/to/xxx/9.jpg       | 2019-10-13 10:58:37 |
| 10 | 郑十   | http://cdn.awesome_app.com/path/to/xxx/10.jpg      | 2019-10-13 10:58:37 |
| 11 | 张三   | http://cdn.awesome_app.com/path/to/xxx/3.jpg       | 2019-10-13 11:20:17 |
| 12 | 李四   | http://cdn.awesome_app.com/path/to/xxx/4.jpg       | 2019-10-13 11:20:17 |
| 13 | 王五   | http://cdn.awesome_app.com/path/to/xxx/5.jpg       | 2019-10-13 11:20:17 |
+----+--------+----------------------------------------------------+---------------------+

5.2.3 Запрос указанного столбца

mysql> select id,name from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
|  2 | bill   |
|  3 | 张三   |
|  4 | 李四   |
|  5 | 王五   |
|  6 | 马六   |
|  7 | 肖七   |
|  8 | 刘八   |
|  9 | 杨九   |
| 10 | 郑十   |
| 11 | 张三   |
| 12 | 李四   |
| 13 | 王五   |
+----+--------+

5.2.4 Запрос уникальных записей

mysql> select distinct name,avatar  from users;
+--------+----------------------------------------------------+
| name   | avatar                                             |
+--------+----------------------------------------------------+
| steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg  |
| bill   | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg |
| 张三   | http://cdn.awesome_app.com/path/to/xxx/3.jpg       |
| 李四   | http://cdn.awesome_app.com/path/to/xxx/4.jpg       |
| 王五   | http://cdn.awesome_app.com/path/to/xxx/5.jpg       |
| 马六   | http://cdn.awesome_app.com/path/to/xxx/6.jpg       |
| 肖七   | http://cdn.awesome_app.com/path/to/xxx/7.jpg       |
| 刘八   | http://cdn.awesome_app.com/path/to/xxx/8.jpg       |
| 杨九   | http://cdn.awesome_app.com/path/to/xxx/9.jpg       |
| 郑十   | http://cdn.awesome_app.com/path/to/xxx/10.jpg      |
+--------+----------------------------------------------------+

5.2.5 Ограничение количества строк запроса

Подсчитайте несколько строк запроса

mysql> select id,name from users limit 2;
+----+--------+
| id | name   |
+----+--------+
|  1 | steven |
|  2 | bill   |
+----+--------+

Запросить несколько строк, начиная с указанного смещения (первая строка имеет смещение 0)

mysql> select id,name from users limit 2,3;
+----+--------+
| id | name   |
+----+--------+
|  3 | 张三   |
|  4 | 李四   |
|  5 | 王五   |
+----+--------+

5.2.6 Сортировка

# 正序
mysql> select distinct name from users order by name asc limit 3;
+--------+
| name   |
+--------+
| bill   |
| steven |
| 刘八   |
+--------+
# 倒序
mysql> select id,name from users order by id desc limit 3;
+----+--------+
| id | name   |
+----+--------+
| 13 | 王五   |
| 12 | 李四   |
| 11 | 张三   |
+----+--------+

5.2.7 Группировка

добавить поле города

alter table `users` add `city` varchar(10) comment '用户所在城市' after `name`;
update `users` set `city`='旧金山' where `id`=1;
update `users` set `city`='西雅图' where `id`=2;
update `users` set `city`='北京' where `id` in (3,5,7);
update `users` set `city`='上海' where `id` in (4,6,8);
update `users` set `city`='广州' where `id` between 9 and 10;
update `users` set `city`='深圳' where `id` between 11 and 13;

Статистика пользователей по городам

mysql> select city, count(name) as num_of_user from users group by city;
+-----------+-------------+
| city      | num_of_user |
+-----------+-------------+
| 上海      |           3 |
| 北京      |           3 |
| 广州      |           2 |
| 旧金山    |           1 |
| 深圳      |           3 |
| 西雅图    |           1 |
+-----------+-------------+
mysql> select city, count(name) as num_of_user from users group by city having num_of_user=1;
+-----------+-------------+
| city      | num_of_user |
+-----------+-------------+
| 旧金山    |           1 |
| 西雅图    |           1 |
+-----------+-------------+
mysql> select city, count(name) as num_of_user from users group by city having num_of_user>2;
+--------+-------------+
| city   | num_of_user |
+--------+-------------+
| 上海   |           3 |
| 北京   |           3 |
| 深圳   |           3 |
+--------+-------------+

5.3 Запрос ассоциации с несколькими таблицами

5.3.1 Подготовка данных

create table if not exists `orders`
(
    `id` int not null primary key auto_increment comment '订单ID',
    `title` varchar(50) not null comment '订单标题',
    `user_id` int not null comment '用户ID',
    `cretime` timestamp not null default current_timestamp comment '创建时间'
);
create table if not exists `groups`
(
    `id` int not null primary key auto_increment comment '用户组ID',
    `title` varchar(50) not null comment '用户组标题',
    `cretime` timestamp not null default current_timestamp comment '创建时间'
);
alter table `users` add `group_id` int comment '用户分组' after `city`;

insert into `groups` (`title`) values ('大佬'), ('萌新'), ('菜鸡');
insert into `orders` (`title`, `user_id`) values ('《大佬是怎样炼成的？》', 3), ('《MySQL 从萌新到删库跑路》', 6), ('《菜鸡踩坑记》', 9);
update `users` set `group_id`=1 where `id` between 1 and 2;
update `users` set `group_id`=2 where `id` in (4, 6, 8, 10, 12);
update `users` set `group_id`=3 where `id` in (3, 5, 13);

5.3.2 join

join

Используется для запроса нескольких таблиц для сопоставления данных.

mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users`, `orders` where `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title                          |
+-----------+--------------------------------------+
| 张三      | 《大佬是怎样炼成的？》               |
| 马六      | 《MySQL 从萌新到删库跑路》           |
| 杨九      | 《菜鸡踩坑记》                       |
+-----------+--------------------------------------+

inner join

Внутреннее соединение. эффект иjoinто же самое, но использование отличается,joinиспользоватьwhere,inner joinиспользоватьon.

mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users` inner join `orders` on `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title                          |
+-----------+--------------------------------------+
| 张三      | 《大佬是怎样炼成的？》               |
| 马六      | 《MySQL 从萌新到删库跑路》           |
| 杨九      | 《菜鸡踩坑记》                       |
+-----------+--------------------------------------+

left join

Левое соединение. возвращениелевый столвсе строки, даже еслиправый столНет соответствующей строки вNULLзаполнение.

mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users` left join `orders` on `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title                          |
+-----------+--------------------------------------+
| 张三      | 《大佬是怎样炼成的？》               |
| 马六      | 《MySQL 从萌新到删库跑路》           |
| 杨九      | 《菜鸡踩坑记》                       |
| steven    | NULL                                 |
| bill      | NULL                                 |
| 李四      | NULL                                 |
| 王五      | NULL                                 |
| 肖七      | NULL                                 |
| 刘八      | NULL                                 |
| 郑十      | NULL                                 |
| 张三      | NULL                                 |
| 李四      | NULL                                 |
| 王五      | NULL                                 |
+-----------+--------------------------------------+

right join

право присоединиться. иleft joinВсегда напротив, вернетсяправый столвсе строки, даже еслилевый столНет соответствующей строки вNULLзаполнение.

mysql> select `groups`.`title` as `group_title`, `users`.`name` as `user_name` from `groups` right join `users` on `users`.`group_id`=`groups`.`id`;
+-------------+-----------+
| group_title | user_name |
+-------------+-----------+
| 大佬        | steven    |
| 大佬        | bill      |
| 萌新        | 李四      |
| 萌新        | 马六      |
| 萌新        | 刘八      |
| 萌新        | 郑十      |
| 萌新        | 李四      |
| 菜鸡        | 张三      |
| 菜鸡        | 王五      |
| 菜鸡        | 王五      |
| NULL        | 肖七      |
| NULL        | 杨九      |
| NULL        | 张三      |
+-------------+-----------+

5.3.3 union

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

mysql> (select `id`, `title` from `groups`) union (select `id`, `title` from `orders`);
+----+--------------------------------------+
| id | title                                |
+----+--------------------------------------+
|  1 | 大佬                                 |
|  2 | 萌新                                 |
|  3 | 菜鸡                                 |
|  1 | 《大佬是怎样炼成的？》               |
|  2 | 《MySQL 从萌新到删库跑路》           |
|  3 | 《菜鸡踩坑记》                       |
+----+--------------------------------------+

6. Функции

6.1 Синтаксис

select function(column) from table_name

6.2 Общая функция (агрегатные функции)

Агрегатные функции работают с рядом значений и возвращают одно значение. обычно сgroup byзаявление вместе.

функция	описывать
avg(column)	Возвращает среднее значение столбца
count(column)	Возвращает количество строк для столбца (исключая значения NULL)
count(*)	Вернуться к выбранной линии
first(column)	Возвращает значение первой записи в указанном поле
last(column)	Возвращает значение последней записи в указанном поле
max(column)	Возвращает наибольшее значение столбца
min(column)	Возвращает наименьшее значение столбца
sum(column)	Возвращает сумму столбца

6.3 Скалярные функции

функция	описывать
ucase(c)	преобразовать в верхний регистр
lcase(c)	Преобразовать в нижний регистр
mid(c, start[, end])	Извлечение символов из текста
len(c)	вернуть длину текста
instr(c, char)	Возвращает числовую позицию указанного символа в тексте
left(c, number_of_char)	Возвращает левую часть текста
right(c, number_of_char)	Возвращает правую часть текста
round(c, decimals)	округляет число до указанного количества знаков после запятой
mod(x, y)	Возьмите остаток (по модулю)
now()	Возвращает текущую системную дату
format(c, format)	форматированный дисплей
datediff(d, date1, date2)	расчет даты

---

Если mysql не установлен, см.«macOS установить mysql»(Установка Windows аналогична).

Первый публичный номер этой статьи