MySQL（九）：MySQL语法-高级

亦凉 2024-03-17 19:04 74阅读 0赞

#### MySQL语法-高级 ####

*   *  \`LIMIT\`
     *  \`LIKE\`
     *  \`AS\`
     *  \`CREATE UNIQUE INDEX\`、\`DROP INDEX\`
     *  \`CREATE VIEW\`、\`DROP VIEW\`
     *  \`GROUP BY\`
     *  \`HAVING\`
     *  \`MYSQL\` - \`JOIN\`
     *   *  \`INNER JOIN\`、\`JOIN\`
         *  \`LEFT JOIN\`、\`LEFT OUTER JOIN\`
         *  \`RIGHT JOIN\`、\`RIGHT OUTER JOIN\`
         *  \`LEFT JOIN ... WHERE ...\`
         *  \`RIIGHT JOIN ... WHERE ...\`
     *  \`TRUNCATE TABLE\`
     *  \`INSERT INTO 表1 (列1, 列2) SELECT (列1, 列2) FROM 表2\`
     *  \`UNION\`、\`UNION ALL\`
     *  日期相关函数
     *   *  \`NOW()\`、\`CURDATE()\`、\`CURTIME()\`、\`DATE()\`、\`DATE\_FORMAT()\`、\`DATEDIFF()\`
         *  \`EXTRACT()\`、\`DATE\_ADD()\`、\`DATE\_SUB()\`

### `LIMIT` ###

> 用于指定要返回的记录数量

***示例：***

select *
    from employees limit 3;

+--------+------------+------------+-----------+--------+------------+
    | emp_no | birth_date | first_name | last_name | gender | hire_date  |
    +--------+------------+------------+-----------+--------+------------+
    |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
    |  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
    |  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
    +--------+------------+------------+-----------+--------+------------+
    3 rows in set (0.00 sec)

### `LIKE` ###

> 使用LIKE运算符来搜索列中的指定模式

*  `％`(百分号表示零个，一个或多个字符)
 *  `_`(下划线表示单个字符)

***示例：***

select *
    from employees
    where last_name like '%ce_l_'
      AND first_name like 'G_o%g_';

+--------+------------+------------+-----------+--------+------------+
    | emp_no | birth_date | first_name | last_name | gender | hire_date  |
    +--------+------------+------------+-----------+--------+------------+
    |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
    |  55649 | 1956-01-23 | Georgi     | Facello   | M      | 1988-05-04 |
    +--------+------------+------------+-----------+--------+------------+
    2 rows in set (0.10 sec)

### `AS` ###

> 别名

***示例：***

select emp.first_name as name
    from employees as emp limit 3;

### `CREATE UNIQUE INDEX`、`DROP INDEX` ###

> 创建唯一索引

***示例：***

-- 设置id为唯一索引，名称为id_name
    create unique index id_name on order_table (id);
    
    -- 删除唯一索引
    drop index id_name on order_table;

### `CREATE VIEW`、`DROP VIEW` ###

> 创建新视图
> 
> 视图（View）是一种虚拟存在的表，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变

***示例：***

-- 创建视图
    create view order_view as
    select date_01
    from order_table
    where id = 3;
    -- 删除视图
    drop view order_view;
    -- 查询视图数据
    select *
    from order_view;

+------------+
    | date_01    |
    +------------+
    | 2023-07-03 |
    +------------+
    1 row in set (0.00 sec)

### `GROUP BY` ###

> 通过…分组

***示例：***

-- 通过员工编号分组查询员工编号、薪资数、薪资和、薪资平均值、薪资最大值、薪资最小值
    select emp_no, count(salary), sum(salary), avg(salary), max(salary), min(salary)
    from salaries
    group by emp_no;

+--------+---------------+-------------+-------------+-------------+-------------+
    | emp_no | count(salary) | sum(salary) | avg(salary) | max(salary) | min(salary) |
    +--------+---------------+-------------+-------------+-------------+-------------+
    |  10001 |            17 |     1281612 |  75388.9412 |       88958 |       60117 |
    |  10002 |             6 |      413127 |  68854.5000 |       72527 |       65828 |
    |  10003 |             7 |      301212 |  43030.2857 |       43699 |       40006 |
    |  10004 |            16 |      904196 |  56512.2500 |       74057 |       40054 |
    |  10005 |            13 |     1134585 |  87275.7692 |       94692 |       78228 |
    +--------+---------------+-------------+-------------+-------------+-------------+
    5 rows in set (0.00 sec)

### `HAVING` ###

> 筛选分组后的各组数据

***示例：***

-- 按员工编号分组查询薪资总和大于2300000的数据
    select emp_no, sum(salary)
    from salaries
    group by emp_no
    having sum(salary) > 2300000;

+--------+-------------+
    | emp_no | sum(salary) |
    +--------+-------------+
    |  43624 |     2492873 |
    |  47978 |     2374024 |
    |  66793 |     2383923 |
    |  68086 |     2305351 |
    |  80823 |     2368170 |
    | 109334 |     2553036 |
    | 237542 |     2381119 |
    +--------+-------------+
    7 rows in set (0.77 sec)

### `MYSQL` \- `JOIN` ###

![在这里插入图片描述][293ea5e49e6d4108aa25f8e0f7a54cdc.png_pic_center]

***示例表（`table_01`、`table_02`）：***

select *
    from table_01;
    select *
    from table_02;

+----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    |  5 |
    |  6 |
    +----+
    6 rows in set (0.00 sec)
    
    +----+
    | id |
    +----+
    |  4 |
    |  5 |
    |  6 |
    |  7 |
    |  8 |
    |  9 |
    |  0 |
    +----+
    7 rows in set (0.00 sec)

#### `INNER JOIN`、`JOIN` ####

> 可以简写为JOIN

***示例：***

select *
    from table_01
             join table_02 on table_01.id = table_02.id;
    select *
    from table_01
             inner join table_02 on table_01.id = table_02.id;

+----+----+
    | id | id |
    +----+----+
    |  4 |  4 |
    |  5 |  5 |
    |  6 |  6 |
    +----+----+
    3 rows in set (0.00 sec)

***示例：***

-- 查询管理员所在部门和姓名等信息
    select employees.emp_no,
           employees.first_name,
           employees.last_name,
           employees.gender,
           departments.dept_no,
           departments.dept_name
    from dept_manager
             join departments on dept_manager.dept_no = departments.dept_no
             join employees on employees.emp_no = dept_manager.emp_no;

+--------+-------------+--------------+--------+---------+-----------+
    | emp_no | first_name  | last_name    | gender | dept_no | dept_name |
    +--------+-------------+--------------+--------+---------+-----------+
    | 111692 | Tonny       | Butterworth  | F      | d009    | ????      |
    | 111784 | Marjo       | Giarratana   | F      | d009    | ????      |
    | 111877 | Xiaobin     | Spinelli     | F      | d009    | ????      |
    | 111939 | Yuchang     | Weedman      | M      | d009    | ????      |
    | 110511 | DeForest    | Hagimont     | M      | d005    | ????      |
    | 110567 | Leon        | DasSarma     | F      | d005    | ????      |
    | 110183 | Shirish     | Ossenbruggen | F      | d003    | ??        |
    | 110228 | Karsten     | Sigstam      | F      | d003    | ??        |
    | 110303 | Krassimir   | Wegerle      | F      | d004    | ??        |
    | 110344 | Rosine      | Cools        | F      | d004    | ??        |
    | 110386 | Shem        | Kieras       | M      | d004    | ??        |
    | 110420 | Oscar       | Ghazalie     | M      | d004    | ??        |
    | 111035 | Przemyslawa | Kaelbling    | M      | d007    | ??        |
    | 111133 | Hauke       | Zhang        | M      | d007    | ??        |
    | 110725 | Peternela   | Onuegbe      | F      | d006    | ??        |
    | 110765 | Rutger      | Hofmeyr      | F      | d006    | ??        |
    | 110800 | Sanjoy      | Quadeer      | F      | d006    | ??        |
    | 110854 | Dung        | Pesch        | M      | d006    | ??        |
    | 110085 | Ebru        | Alpin        | M      | d002    | ??        |
    | 110114 | Isamu       | Legleitner   | F      | d002    | ??        |
    | 111400 | Arie        | Staelin      | M      | d008    | ????      |
    | 111534 | Hilary      | Kambil       | F      | d008    | ????      |
    | 110022 | Margareta   | Markovitch   | M      | d001    | ??        |
    | 110039 | Vishwani    | Minakawa     | M      | d001    | ??        |
    +--------+-------------+--------------+--------+---------+-----------+
    24 rows in set (0.00 sec)

#### `LEFT JOIN`、`LEFT OUTER JOIN` ####

***示例：***

select *
    from table_01
             left join table_02 on table_01.id = table_02.id;

+----+------+
    | id | id   |
    +----+------+
    |  1 | NULL |
    |  2 | NULL |
    |  3 | NULL |
    |  4 |    4 |
    |  5 |    5 |
    |  6 |    6 |
    +----+------+
    6 rows in set (0.00 sec)

#### `RIGHT JOIN`、`RIGHT OUTER JOIN` ####

***示例：***

select *
    from table_01
             right join table_02 on table_01.id = table_02.id;

+------+----+
    | id   | id |
    +------+----+
    |    4 |  4 |
    |    5 |  5 |
    |    6 |  6 |
    | NULL |  7 |
    | NULL |  8 |
    | NULL |  9 |
    | NULL |  0 |
    +------+----+
    7 rows in set (0.00 sec)

#### `LEFT JOIN ... WHERE ...` ####

***示例：***

select *
    from table_01
             left join table_02 on table_01.id = table_02.id
    where table_02.id is null;

+----+------+
    | id | id   |
    +----+------+
    |  1 | NULL |
    |  2 | NULL |
    |  3 | NULL |
    +----+------+
    3 rows in set (0.00 sec)

#### `RIIGHT JOIN ... WHERE ...` ####

***示例：***

select *
    from table_01
             right join table_02 on table_01.id = table_02.id
    where table_01.id is null;

+------+----+
    | id   | id |
    +------+----+
    | NULL |  7 |
    | NULL |  8 |
    | NULL |  9 |
    | NULL |  0 |
    +------+----+
    4 rows in set (0.00 sec)

### `TRUNCATE TABLE` ###

> 删除表中的所有数据

***示例：***

truncate table table_name;

### `INSERT INTO 表1 (列1, 列2) SELECT (列1, 列2) FROM 表2` ###

> 把表2数据复制到表1中

***示例：***

insert into table_02 (id) (select * from table_01);

+----+
    | id |
    +----+
    |  4 |
    |  5 |
    |  6 |
    |  7 |
    |  8 |
    |  9 |
    |  0 |
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    |  5 |
    |  6 |
    +----+
    13 rows in set (0.00 sec)

### `UNION`、`UNION ALL` ###

> 组合两个或更多SELECT语句的结果集
> 
> `UNION` 不返回任何重复的行
> 
> `UNION ALL` 返回重复的行

***要求：***

*  UNION中的每个SELECT语句必须具有相同的列数
 *  这些列也必须具有相似的数据类型
 *  每个SELECT语句中的列也必须以相同的顺序排列
 *  每个SELECT语句必须有相同数目的列表达式
 *  但是每个SELECT语句的长度不必相同

***示例(`UNION`)：***

select dept_no
    from dept_manager
    union
    select dept_no
    from departments;

+---------+
    | dept_no |
    +---------+
    | d001    |
    | d002    |
    | d003    |
    | d004    |
    | d005    |
    | d006    |
    | d007    |
    | d008    |
    | d009    |
    +---------+
    9 rows in set (0.00 sec)

***示例(`UNION ALL`)：***

select dept_no
    from dept_manager
    union all
    select dept_no
    from departments;

+---------+
    | dept_no |
    +---------+
    | d001    |
    | d001    |
    | d002    |
    | d002    |
    | d003    |
    | d003    |
    | d004    |
    | d004    |
    | d004    |
    | d004    |
    | d005    |
    | d005    |
    | d006    |
    | d006    |
    | d006    |
    | d006    |
    | d007    |
    | d007    |
    | d008    |
    | d008    |
    | d009    |
    | d009    |
    | d009    |
    | d009    |
    | d009    |
    | d005    |
    | d003    |
    | d004    |
    | d007    |
    | d006    |
    | d002    |
    | d008    |
    | d001    |
    +---------+
    33 rows in set (0.00 sec)

### 日期相关函数 ###

#### `NOW()`、`CURDATE()`、`CURTIME()`、`DATE()`、`DATE_FORMAT()`、`DATEDIFF()` ####

***示例：***

-- NOW()	                                    返回当前的日期和时间
    -- CURDATE()	                                返回当前的日期
    -- CURTIME()	                                返回当前的时间
    -- DATE('2023-07-12 08:00:09')                  提取日期或日期/时间表达式的日期部分
    -- DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')       用不同的格式显示日期/时间
    -- DATEDIFF('2023-07-12 08:00:09','2008-11-29') 返回两个日期之间的天数
    select NOW(),
           CURDATE(),
           CURTIME(), DATE ('2023-07-12 08:00:09'), DATE_FORMAT(NOW(), '%b %d %Y %h:%i %p'), DATEDIFF('2023-07-12 08:00:09', '2008-11-29');

+---------------------+------------+-----------+-----------------------------+-----------------------------------------+-----------------------------------------------+
    | NOW()               | CURDATE()  | CURTIME() | DATE('2023-07-12 08:00:09') | DATE_FORMAT(NOW(), '%b %d %Y %h:%i %p') | DATEDIFF('2023-07-12 08:00:09', '2008-11-29') |
    +---------------------+------------+-----------+-----------------------------+-----------------------------------------+-----------------------------------------------+
    | 2023-07-12 08:06:49 | 2023-07-12 | 08:06:49  | 2023-07-12                  | Jul 12 2023 08:06 AM                    |                                          5338 |
    +---------------------+------------+-----------+-----------------------------+-----------------------------------------+-----------------------------------------------+
    1 row in set (0.00 sec)

#### `EXTRACT()`、`DATE_ADD()`、`DATE_SUB()` ####

***示例：***

-- EXTRACT()    返回日期/时间的单独部分
    -- DATE_ADD()	向日期添加指定的时间间隔
    -- DATE_SUB()	从日期减去指定的时间间隔
    select EXTRACT(YEAR from NOW()), DATE_ADD(NOW(), interval 5 day), DATE_SUB(NOW(), interval 15 day);

+--------------------------+---------------------------------+----------------------------------+
    | EXTRACT(YEAR from NOW()) | DATE_ADD(NOW(), interval 5 day) | DATE_SUB(NOW(), interval 15 day) |
    +--------------------------+---------------------------------+----------------------------------+
    |                     2023 | 2023-07-17 08:11:06             | 2023-06-27 08:11:06              |
    +--------------------------+---------------------------------+----------------------------------+
    1 row in set (0.00 sec)

[293ea5e49e6d4108aa25f8e0f7a54cdc.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/9a6491d116ce456db1f8172b2b1be5a9.png