MySQL高级（进阶）SQL语句

MySQL高级（进阶）SQL语句

一、实例准备--制表

1. 表1（商店区域表）

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table location (region char(20),store_name char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into location values('North','Beijing');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('Eaet','shanghai');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('South','Guangzhou');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('South','Shenzhen');
Query OK, 1 row affected (0.01 sec)

mysql> select * from location;
+--------+------------+
| region | store_name |
+--------+------------+
| North  | Beijing    |
| East   | Shanghai   |
| South  | Guangzhou  |
| South  | Shenzhen   |
+--------+------------+
4 rows in set (0.00 sec)

2. 表2（商店销售表）

mysql> create table store_info (store_name char(20),sales int(10),date char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into store_info values('Guangzhou',1500,'2020-12-05');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Shenzhen',250,'2020-12-07');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Guangzhou',300,'2020-12-08');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Beijing',700,'2020-12-08');
Query OK, 1 row affected (0.00 sec)

mysql> select * from store_info;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Shenzhen   |   250 | 2020-12-07 |
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
4 rows in set (0.00 sec)

3. 表3（城市表）

mysql> create table city(city_name char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into city values('beijing'),('nanjing'),('shanghai');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into city values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into city values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into city values('');
Query OK, 1 row affected (0.00 sec)

mysql> select * from city;
+-----------+
| city_name |
+-----------+
| beijing   |
| nanjing   |
| shanghai  |
| NULL      |
| NULL      |
|           |
+-----------+
6 rows in set (0.00 sec)

4. 表4（total_sales）

mysql> create table total_sales (name char(20),sales int(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into total_sales values('zhangsan',10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into total_sales values('lisi',15);
Query OK, 1 row affected (0.01 sec)

mysql> insert into total_sales values('wangwu',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into total_sales values('zhaoliu',40);
Query OK, 1 row affected (0.00 sec)

mysql> insert into total_sales values('sunqi',50);
Query OK, 1 row affected (0.00 sec)

mysql> insert into total_sales values('zhouba',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into total_sales values('wujiu',30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from total_sales;
+----------+-------+
| name     | sales |
+----------+-------+
| zhangsan |    10 |
| lisi     |    15 |
| wangwu   |    20 |
| zhaoliu  |    40 |
| sunqi    |    50 |
| zhouba   |    20 |
| wujiu    |    30 |
+----------+-------+
7 rows in set (0.00 sec)

二、SQL语句

1. select

显示表格中一个或数个栏位的所有资料
语法：select "栏位" from "表名";

mysql> select store_name from store_info;
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Guangzhou  |
| Beijing    |
+------------+
4 rows in set (0.00 sec)

2. distinct

不显示重复的资料
语法：select dstinct "栏位" from "表名";

mysql> select distinct store_name from store_info;
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Beijing    |
+------------+
3 rows in set (0.00 sec)

3. where

有条件查询
语法：select "栏位" from "表名" where "条件";

mysql> select store_name from store_info where sales>1000;
+------------+
| store_name |
+------------+
| Guangzhou  |
+------------+
1 row in set (0.00 sec)

4. and|or

且、或
语法：select "栏位" from "表名" where "条件1" {[and|or] “条件2”}...;

mysql> select store_name from store_info where sales>1000 or (sales<500 and sales>200);
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Guangzhou  |
+------------+
3 rows in set (0.00 sec)

5. in

显示已知的值的资料
语法：select "栏位" from "表名" where "栏位" in ("值1","值2",...);

mysql> select * from store_info where store_name in ('Beijing','Shenzhen');
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)

6. between

显示两个值范围内的资料
语法：select "栏位" from "表名" where "栏位" between '值1' and '值2';

mysql> select * from store_info where date between '2020-12-06' and '2020-12-10';
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
3 rows in set (0.00 sec)

7. limit

（1）显示前几行内容
语法：select "栏位" from "表名" limit '行数';

mysql> select * from store_info limit 2;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Shenzhen   |   250 | 2020-12-07 |
+------------+-------+------------+
2 rows in set (0.00 sec)

（2）显示第几行后的前几行
语法：select "栏位" from "表名" limit "第几行后,显示行数";

mysql> select * from store_info limit 2,2;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)

8. 通配符

通常通配符都是跟like一起使用的

常用通配符	说明
%	百分号表示零个、一个或多个字符
_	下划线表示单个字符

例如：
'A_Z'
表示所有以'A'起头，另一个任何值的字符，且以'Z'为结尾的字符串。例如，'ABZ'和'A2Z'都符合这一个模式，而'ABCZ'并不符合（因为A和Z之间有两个字符，而不是一个字符）
'ABC%'
表示所有以'ABC'起头的字符串。例如，'ABCD'和'ABCABC'都符合这个模式。
'%XYZ'
表示所有以'XYZ'结尾的字符串。例如，'WXYZ'和'ZZXYZ'都符合这个模式。
'%AN%'
表示所有含有'AN'这个模式的字符串。例如，'SHANGHAI'和'XIAN'都符合这个模式。
'_AN%'
表示第二个字母为'A'且第三个字母为'N'的字符串。例如，'HANGZHOU'和'LANZHOU'都符合这个模式，而'SHANGHAI'和'XIAN'则不符合这个模式。

9. like

匹配一个模式来搜查资料
语法：select "栏位" from “表名” where "栏位" like {模式};

mysql> select * from store_info where store_name like '%e%';
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)

10. order by

按关键字排序
语法：select "栏位" from “表名” [where "条件"] order by "栏位" [ASC,DESC];
ASC是按照升序进行排序的，是默认的排序方式。
DESC是按降序方式进行排序。

mysql> select store_name,sales,date from store_info order by sales desc;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Beijing    |   700 | 2020-12-08 |
| Guangzhou  |   300 | 2020-12-08 |
| Shenzhen   |   250 | 2020-12-07 |
+------------+-------+------------+
4 rows in set (0.00 sec)

三、函数

1. 数学函数

常用的数学函数	说明
abs(x)	返回x的绝对值
rand()	返回0-1的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回x的y次方
round(x)	返回离x最近的整数，即四舍五入到个位
round(x,y)	返回x的y位小数且四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x,y)	返回数字X截断为y位小数的值
ceil(x)	返回大于或等于x的最小整数
floor(x)	返回小于或等于x的最大整数
greatest(x1,x2...)	返回集合中最大的值
least(x1,x2...)	返回集合中最小的值

（1）abs(x)

返回x的绝对值

mysql> select abs(-2.3);
+-----------+
| abs(-2.3) |
+-----------+
|       2.3 |
+-----------+
1 row in set (0.00 sec)

（2）rand()

返回0-1的随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.1262695352791525 |
+--------------------+
1 row in set (0.00 sec)

（3）mod（x,y）

返回x除以y以后的余数

mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

（4）power(x,y)

返回x的y次方

mysql> select power(2,8);
+------------+
| power(2,8) |
+------------+
|        256 |
+------------+
1 row in set (0.00 sec)

（5）round(x)

返回离x最近的整数,即四舍五入到个位

mysql> select round(3.1415);
+---------------+
| round(3.1415) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(3.5415);
+---------------+
| round(3.5415) |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)

（6）round(x,y)

返回x的y位小数且四舍五入后的值

mysql> select round(3.1415,2);
+-----------------+
| round(3.1415,2) |
+-----------------+
|            3.14 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(3.1415,3);
+-----------------+
| round(3.1415,3) |
+-----------------+
|           3.142 |
+-----------------+
1 row in set (0.00 sec)

（7）sqrt(x)

返回x的平方根

mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> select sqrt(8);
+--------------------+
| sqrt(8)            |
+--------------------+
| 2.8284271247461903 |
+--------------------+
1 row in set (0.00 sec)

（8）truncate(x,y)

返回数字X截断为y位小数的值

mysql> select truncate(3.1415,2);
+--------------------+
| truncate(3.1415,2) |
+--------------------+
|               3.14 |
+--------------------+
1 row in set (0.00 sec)

mysql> select truncate(3.1415,3);
+--------------------+
| truncate(3.1415,3) |
+--------------------+
|              3.141 |
+--------------------+
1 row in set (0.00 sec)

（9）ceil(x)

返回大于或等于x的最小整数

mysql> select ceil(3.1415);
+--------------+
| ceil(3.1415) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

（10）floor(x)

返回小于或等于x的最大整数

mysql> select floor(3.1415);
+---------------+
| floor(3.1415) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

（11）greatest(x1,x2...)

返回集合中最大的值

mysql> select greatest(3.1415,3,4,5.2);
+--------------------------+
| greatest(3.1415,3,4,5.2) |
+--------------------------+
|                   5.2000 |
+--------------------------+
1 row in set (0.00 sec)

（12）least(x1,x2...)

返回集合中最小的值

mysql> select least(3.1415,3,4,5.2);
+-----------------------+
| least(3.1415,3,4,5.2) |
+-----------------------+
|                3.0000 |
+-----------------------+
1 row in set (0.00 sec)

2. 聚合函数

常用的聚合函数	说明
avg()	返回指定列的平均值
count()	返回指定列中非NULL值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum()	返回指定列的所有值之和

（1）avg()

返回指定列的平均值

mysql> select avg(sales) from store_info;
+------------+
| avg(sales) |
+------------+
|   687.5000 |
+------------+
1 row in set (0.00 sec)

（2）count()

返回指定列中非NULL值的个数

mysql> select count(store_name) from store_info;
+-------------------+
| count(store_name) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

返回指定列中非NULL值且去重的个数

mysql> select count(distinct store_name) from store_info;
+----------------------------+
| count(distinct store_name) |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)

count()
count()包括了所有的列的行数，在统计结果的时候，不会忽略值为NULL的行

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

count("列名")
count("列名")只包括列名那一列的行数，在统计结果的时候，会忽略值为NULL的行

mysql> select count(city_name) from city;
+------------------+
| count(city_name) |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

（3）min()

返回指定列的最小值

mysql> select min(sales) from store_info;
+------------+
| min(sales) |
+------------+
|        250 |
+------------+
1 row in set (0.01 sec)

（4）max()

返回指定列的最大值

mysql> select max(sales) from store_info;
+------------+
| max(sales) |
+------------+
|       1500 |
+------------+
1 row in set (0.00 sec)

（5）sum()

返回指定列的所有值之和

mysql> select sum(sales) from store_info;
+------------+
| sum(sales) |
+------------+
|       2750 |
+------------+
1 row in set (0.00 sec)

3. 字符串函数

常用的字符串函数	说明
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数x和y拼接成一个字符串
substr(x,y)	获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z)	获取从字符串x中第y个位置开始长度为z的字符串
length(x)	返回字符串x的长度
replace(x,y,z)	将字符串z替代字符串x中的字符串y
upper(x)	将字符串x的所有字母变成大写字符
lower(x)	将字符串x的所有字母变成小写字符
left(x,y)	返回字符串x的前y个字符
right(x,y)	返回字符串x的后y个字符
reprat(x,y)	将字符串x重复y次
space(x)	返回x个空格
strcmp(x,y)	比较x和y，返回的值可以为-1,0,1
reverse(x)	将字符串x反转

（1）trim()

返回去除指定格式的值
语法：select trim([[位置] [要移除的字符串] from] 字符串);
[位置]：该值可以为leading（起头），trailing（结尾），both（起头及结尾）。缺省时为both。
[要移除的字符串]：从字串的起头、结尾，或起头即结尾移除的字符串。缺省时为空格。

mysql> select trim('g' from 'guangdong');
+----------------------------+
| trim('g' from 'guangdong') |
+----------------------------+
| uangdon                    |
+----------------------------+
1 row in set (0.00 sec)

mysql> select trim(leading 'g' from 'guangdong');
+------------------------------------+
| trim(leading 'g' from 'guangdong') |
+------------------------------------+
| uangdong                           |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select trim(trailing 'g' from 'guangdong');
+-------------------------------------+
| trim(trailing 'g' from 'guangdong') |
+-------------------------------------+
| guangdon                            |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select trim(both 'g' from 'guangdong');
+---------------------------------+
| trim(both 'g' from 'guangdong') |
+---------------------------------+
| uangdon                         |
+---------------------------------+
1 row in set (0.00 sec)

（2）concat(x,y)

将提供的参数x和y拼接成一个字符串

mysql> select concat (region,' ',store_name) from location where store_name='Beeijing';
+--------------------------------+
| concat (region,' ',store_name) |
+--------------------------------+
| North Beijing                  |
+--------------------------------+
1 row in set (0.00 sec)

如sql_mode开启了PIPES_AS_CONCAT（可使用"select @@SESSION.sql_mode;"或"select @@GLOBAL.sql_mode;"进行查看），"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数相类似，这和Oracle数据库使用方法一样。

mysql> select store_name || ' ' || sales from store_info where store_name='Guanngzhou';
+----------------------------+
| store_name || ' ' || sales |
+----------------------------+
| Guangzhou 1500             |
| Guangzhou 300              |
+----------------------------+
2 rows in set (0.00 sec)

（3）substr(x,y)

获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同

mysql> select substr(store_name,3) from location where store_name='Guangzhou'; 
+----------------------+
| substr(store_name,3) |
+----------------------+
| angzhou              |
+----------------------+
1 row in set (0.00 sec)

（4）substr(x,y,z)

获取从字符串x中第y个位置开始长度为z的字符串

mysql> select substr(store_name,3,4) from location where store_name='Guangzhou'';
+------------------------+
| substr(store_name,3,4) |
+------------------------+
| angz                   |
+------------------------+
1 row in set (0.00 sec)

（5）length(x)

返回字符串x的长度

mysql> select *,length(store_name) from location;
+--------+------------+--------------------+
| region | store_name | length(store_name) |
+--------+------------+--------------------+
| North  | Beijing    |                  7 |
| East   | Shanghai   |                  8 |
| South  | Guangzhou  |                  9 |
| South  | Shenzhen   |                  8 |
+--------+------------+--------------------+
4 rows in set (0.00 sec)

（6）replace(x,y,z)

将字符串z替代字符串x中的字符串y

mysql> select replace(store_name,'ng','xx') from location;
+-------------------------------+
| replace(store_name,'ng','xx') |
+-------------------------------+
| Beijixx                       |
| Shaxxhai                      |
| Guaxxzhou                     |
| Shenzhen                      |
+-------------------------------+
4 rows in set (0.00 sec)

（7）upper(x)

将字符串x的所有字母变成大写字符

mysql> select upper(store_name) from location;
+-------------------+
| upper(store_name) |
+-------------------+
| BEIJING           |
| SHANGHAI          |
| GUANGZHOU         |
| SHENZHEN          |
+-------------------+
4 rows in set (0.00 sec)

（8）lower(x)

将字符串x的所有字母变成小写字符

mysql> select lower(store_name) from location;
+-------------------+
| lower(store_name) |
+-------------------+
| beijing           |
| shanghai          |
| guangzhou         |
| shenzhen          |
+-------------------+
4 rows in set (0.00 sec)

（9）left(x,y)

返回字符串x的前y个字符

mysql> select left('Beijing',3);
+-------------------+
| left('Beijing',3) |
+-------------------+
| Bei               |
+-------------------+
1 row in set (0.00 sec)

（10）right(x,y)

返回字符串x的后y个字符

mysql> select right('Beijing',3);
+--------------------+
| right('Beijing',3) |
+--------------------+
| ing                |
+--------------------+
1 row in set (0.00 sec)

（11）reprat(x,y)

将字符串x重复y次

mysql> select repeat('Beijing ',3);
+--------------------------+
| repeat('Beijing ',3)     |
+--------------------------+
| Beijing Beijing Beijing  |
+--------------------------+
1 row in set (0.00 sec)

（12）space(x)

返回x个空格

mysql> select space(10);
+------------+
| space(10)  |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> select space(15);
+-----------------+
| space(15)       |
+-----------------+
|                 |
+-----------------+
1 row in set (0.00 sec)

（13）strcmp(x,y)

比较x和y，返回的值可以为-1,0,1
x=y，返回0

mysql> select strcmp(1,1);
+-------------+
| strcmp(1,1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

x<y，返回-1

mysql> select strcmp(1,2);
+-------------+
| strcmp(1,2) |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

x>y，返回1

mysql> select strcmp(2,1);
+-------------+
| strcmp(2,1) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

（14）reverse(x)

将字符串x反转

mysql> select reverse(sales) from store_info;
+----------------+
| reverse(sales) |
+----------------+
| 0051           |
| 052            |
| 003            |
| 007            |
+----------------+
4 rows in set (0.00 sec)

四、SQL查询语句

1. group by

对group by后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的。
group by有一个原则，就是select后面的所有列中，没有使用聚合函数的列，必须出现在group by 后面。
语法：select "栏位1",sum("栏位2") from "表名" group by "栏位1";

mysql> select store_name,sum(sales) from store_info group by store_name order by sales desc;
+------------+------------+
| store_name | sum(sales) |
+------------+------------+
| Guangzhou  |       1800 |
| Beijing    |        700 |
| Shenzhen   |        250 |
+------------+------------+
3 rows in set (0.00 sec)

"Guangzhou"有两条属性信息，通过group by分组后，同名项将被合并，可通过以下方式确定是否有重名项以及重名次数。

mysql> select store_name,count(store_name),sum(sales) from store_info group by  store_name order by sales desc;
+------------+-------------------+------------+
| store_name | count(store_name) | sum(sales) |
+------------+-------------------+------------+
| Guangzhou  |                 2 |       1800 |
| Beijing    |                 1 |        700 |
| Shenzhen   |                 1 |        250 |
+------------+-------------------+------------+
3 rows in set (0.00 sec)

也可通过前后sales是否变化，判断是否有重名项被合并。

mysql> select store_name,sales,sum(sales) from store_info group by store_name order by sales desc;
+------------+-------+------------+
| store_name | sales | sum(sales) |
+------------+-------+------------+
| Guangzhou  |  1500 |       1800 |
| Beijing    |   700 |        700 |
| Shenzhen   |   250 |        250 |
+------------+-------+------------+
3 rows in set (0.00 sec)

2. having

用来过滤由group by语句返回的记录集，通常与group by语句联合使用。
having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被select的只有函数栏，那就不需要group by子句。
语法：select "栏位1",sum("栏位2") from "表名" group by “栏位1” having (函数条件);

mysql> select store_name,sum(sales) from store_info group by store_name having sum(sales)>1500;
+------------+------------+
| store_name | sum(sales) |
+------------+------------+
| Guangzhou  |       1800 |
+------------+------------+
1 row in set (0.00 sec)

3. [as] 别名

栏位别名/表格别名
语法：select "表格别名"."栏位1" [as] "栏位别名" from "表格名" [as] "表格别名";

mysql> select A.store_name as STORE,sum(A.sales) as 'TOTAL_SALES' from store_info as A group by STORE;
+-----------+-------------+
| STORE     | TOTAL_SALES |
+-----------+-------------+
| Beijing   |         700 |
| Guangzhou |        1800 |
| Shenzhen  |         250 |
+-----------+-------------+
3 rows in set (0.00 sec)

4. 子查询

连接表格，在where子句或having子句中插入另一个SQL语句
语法：slect "栏位1" from "表格1" where "栏位2" [比较运算符] (select "栏位1" from "表格2" where "条件");
其中：
slect "栏位1" from "表格1" where "栏位2" [比较运算符] 为外查询，
(select "栏位1" from "表格2" where "条件")为内查询。
[比较运算符]可以是符号的运算符，例如=、>、<、>=、<=；也可以是文字的匹配符，例如like、in、between等。

mysql> select sum(sales) from store_info where store_name in (select store_name from location where region='North');
+------------+
| sum(sales) |
+------------+
|        700 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(A.sales) from store_info A where A.store_name in
    -> (select store_name from location B where B.store_name=A.store_name);   
+--------------+
| sum(A.sales) |
+--------------+
|         2750 |
+--------------+
1 row in set (0.00 sec)

注：匹配两个表的列名可以不相同，但列中需有相同内容，否则将返回NULL值。

mysql> alter table location change store_name name char(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select sum(sales) from store_info where store_name in (select name from location where region='North');
+------------+
| sum(sales) |
+------------+
|        700 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(sales) from store_info where sales in (select name from locattion where region='North');
+------------+
| sum(sales) |
+------------+
|       NULL |
+------------+
1 row in set, 4 warnings (0.00 sec)

5. exists

用来测试内查询有没有产生任何结果，类似布尔值是否为真。
如果有的话，系统就会执行外查询中的SQL语句。若没有的话，那整个SQL就不会产生任何结果。
语法：select "栏位1" from "表格1" where exists (select * from "表格2" where "条件");

mysql> select sum(sales) from store_info where exists                        
    -> (select * from location where region='North');                         
+------------+
| sum(sales) |
+------------+
|       2750 |
+------------+
1 row in set (0.00 sec)

6. join--连接查询

inner join（内连接）：只返回两个表中联结字段相等的行

mysql> select * from location A inner join store_info B on A.store_name=B.store_name;
+--------+------------+------------+-------+------------+
| region | store_name | store_name | sales | date       |
+--------+------------+------------+-------+------------+
| South  | Guangzhou  | Guangzhou  |  1500 | 2020-12-05 |
| South  | Shenzhen   | Shenzhen   |   250 | 2020-12-07 |
| South  | Guangzhou  | Guangzhou  |   300 | 2020-12-08 |
| North  | Beijing    | Beijing    |   700 | 2020-12-08 |
+--------+------------+------------+-------+------------+
4 rows in set (0.00 sec)

left join（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录

mysql> select * from location A left join store_info B on A.store_name=B.store__name;
+--------+------------+------------+-------+------------+
| region | store_name | store_name | sales | date       |
+--------+------------+------------+-------+------------+
| South  | Guangzhou  | Guangzhou  |  1500 | 2020-12-05 |
| South  | Shenzhen   | Shenzhen   |   250 | 2020-12-07 |
| South  | Guangzhou  | Guangzhou  |   300 | 2020-12-08 |
| North  | Beijing    | Beijing    |   700 | 2020-12-08 |
| East   | Shanghai   | NULL       |  NULL | NULL       |
+--------+------------+------------+-------+------------+
5 rows in set (0.00 sec)

right join（右连接）：返回包括右表中所有记录和左表中联结字段相等的记录

mysql> select * from location A right join store_info B on A.store_name=B.store_name;
+--------+------------+------------+-------+------------+
| region | store_name | store_name | sales | date       |
+--------+------------+------------+-------+------------+
| North  | Beijing    | Beijing    |   700 | 2020-12-08 |
| South  | Guangzhou  | Guangzhou  |  1500 | 2020-12-05 |
| South  | Guangzhou  | Guangzhou  |   300 | 2020-12-08 |
| South  | Shenzhen   | Shenzhen   |   250 | 2020-12-07 |
+--------+------------+------------+-------+------------+
4 rows in set (0.00 sec)

也可以通过from多表后where添加同名条件达到与连接查询相同的效果

mysql> select * from location A,store_info B where A.store_name=B.store_name;
+--------+------------+------------+-------+------------+
| region | store_name | store_name | sales | date       |
+--------+------------+------------+-------+------------+
| South  | Guangzhou  | Guangzhou  |  1500 | 2020-12-05 |
| South  | Shenzhen   | Shenzhen   |   250 | 2020-12-07 |
| South  | Guangzhou  | Guangzhou  |   300 | 2020-12-08 |
| North  | Beijing    | Beijing    |   700 | 2020-12-08 |
+--------+------------+------------+-------+------------+
4 rows in set (0.00 sec)

也可对其进一步分组操作，实现去重

mysql> select A.region,sum(B.sales) from location A,store_info B              
    -> where A.store_name=B.store_name group by A.region;                     
+--------+--------------+
| region | sum(B.sales) |
+--------+--------------+
| North  |          700 |
| South  |         2050 |
+--------+--------------+
2 rows in set (0.00 sec)

mysql> select A.store_name,sum(B.sales) from location A,store_info B 
    -> where A.store_name=B.store_name group by A.store_name;                 
+------------+--------------+
| store_name | sum(B.sales) |
+------------+--------------+
| Beijing    |          700 |
| Guangzhou  |         1800 |
| Shenzhen   |          250 |
+------------+--------------+
3 rows in set (0.00 sec)

五、create view--视图

1. 视图的定义

视图，可以被当作是虚拟表或存储查询。

2. 视图和表格的区别

视图和表格的不同是，表格中有实际存储资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

3. 视图的特点

视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对净额表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

4. 视图的语法

create view "视图表名" as "select 语句";

mysql> create view V_REGION_SALES as 
    -> select A.region REGION,sum(B.sales) SALES from location A 
    -> inner join store_info B on A.store_name=B.store_name 
    -> group by REGION;
Query OK, 0 rows affected (0.01 sec)

5. 查看视图

视图保存于当前库的表列表中，查询方式同表。

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| V_REGION_SALES |
| city           |
| location       |
| rank           |
| store_info     |
| test           |
+----------------+
6 rows in set (0.00 sec)

也可使用selct语句对视图进行查看

mysql> select * from V_REGION_SALES;
+--------+-------+
| REGION | SALES |
+--------+-------+
| North  |   700 |
| South  |  2050 |
+--------+-------+
2 rows in set (0.00 sec)

6. 删除视图

可使用drop view对视图进行删除操作。

mysql> drop view V_REGION_SALES;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| city           |
| location       |
| rank           |
| store_info     |
| test           |
+----------------+
5 rows in set (0.00 sec)

六、union--联集

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类。
union：生成结果的资料值将没有重复，且按照字段的顺序进行排序。
语法：[select 语句1] union [select 语句2];

mysql> select store_name from location union select store_name from store_info;
+------------+
| store_name |
+------------+
| Beijing    |
| Shanghai   |
| Guangzhou  |
| Shenzhen   |
+------------+
4 rows in set (0.00 sec)

union all：将生成结果的资料值都列出来，无论有误重复
语法：[select 语句1] all union [select 语句2];

mysql> select store_name from location union all select store_name from store_iinfo;
+------------+
| store_name |
+------------+
| Beijing    |
| Shanghai   |
| Guangzhou  |
| Shenzhen   |
| Guangzhou  |
| Shenzhen   |
| Guangzhou  |
| Beijing    |
+------------+
8 rows in set (0.00 sec)

七、有/无交集值

取两个SQL语句结果的交集

1. 出现重复的行

可以使用on语句匹配指定列中的相同行

mysql> select A.store_name from location A inner join store_info B on A.store_name=B.store_name;
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Guangzhou  |
| Beijing    |
+------------+
4 rows in set (0.00 sec)

当两张表的列名相同时，也可适应using语句进行匹配

mysql> select A.store_name from location A inner join store_info B using(store_name);
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Guangzhou  |
| Beijing    |
+------------+
4 rows in set (0.00 sec)

也可使用union all将两张表的指定列合并显示，然后再通过group by分组去重

mysql> select A.store_name from                                               
    -> (select store_name from location union all select store_name from store_info) A
    -> group by A.store_name;                                                 
+------------+
| store_name |
+------------+
| Beijing    |
| Guangzhou  |
| Shanghai   |
| Shenzhen   |
+------------+
4 rows in set (0.00 sec)

2. 两张表都出现的重复的行

两张表其中的一个表没有指定的行，而另一个表这个行有重复时，导致以上方法仍会返回该行内容，因此我们需要使用以下几种方式将其修正为两张表都有指定行的时候才会返回该行。

也可使用union all将两张表的指定列合并显示，然后再通过group by分组去重，最后使用having语句匹配该列出现次数大于1的行

mysql> select A.store_name from                                               
    -> (select store_name from location union all select store_name from store_info) A
    -> group by A.store_name having count(*)>1;
+------------+
| store_name |
+------------+
| Beijing    |
| Guangzhou  |
| Shenzhen   |
+------------+
3 rows in set (0.00 sec)

使用内连接的方式匹配两张表中指定列都出现的行，然后通过group by分组去重

mysql> select A.store_name from 
    -> (select B.store_name from location B inner join store_info C on B.store_name=C.store_name) A
    -> group by A.store_name;
+------------+
| store_name |
+------------+
| Beijing    |
| Guangzhou  |
| Shenzhen   |
+------------+
3 rows in set (0.00 sec)

使用distinct语句进行去重后再连接两张表，并使用using语句匹配指定列（两张表指定列名需相同）

mysql> select distinct A.store_name from location A inner join store_info B using(store_name);
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Beijing    |
+------------+
3 rows in set (0.00 sec)

先用distinct将两张表都去重后再去匹配指定列的相同行

mysql> select distinct store_name from location where (store_name) in 
    -> (select distinct store_name from store_info);
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Beijing    |
+------------+
3 rows in set (0.00 sec)

使用distinct去重后左连接后using指定列去匹配

mysql> select distinct A.store_name from location A 
    -> left join store_info B using(store_name)
    -> where B.store_name is not null;
+------------+
| store_name |
+------------+
| Beijing    |
| Guangzhou  |
| Shenzhen   |
+------------+
3 rows in set (0.00 sec)

3. 无交集值

显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

mysql> select distinct store_name from location where (store_name) not in 
    -> (select store_name from store_info);
+------------+
| store_name |
+------------+
| Shanghai   |
+------------+
1 row in set (0.00 sec)

mysql> select distinct A.store_name from location A left join store_info B using(store_name) where B.store_name is null;
+------------+
| store_name |
+------------+
| Shanghai   |
+------------+
1 row in set (0.00 sec)

mysql> select A.store_name from
    -> (select distinct store_name from location union all select distinct store_name from store_info) A
    -> group by A.store_name having count(*)=1;
+------------+
| store_name |
+------------+
| Shanghai   |
+------------+
1 row in set (0.00 sec)

八、case

1. case概述

case是SQL用来做if-then-else之类逻辑的关键字

2. case语法

select case ("栏位名")
  when "条件1" then "结果1"
  when "条件2" then "结果2"
  ...
  [else "结果N"]
  end
from "表名";

条件：可以是一个数值或是一个公式，else子句并不是必须的

3. case使用实例

mysql> select store_name,case store_name
    -> when 'Beijing' then sales *2
    -> when 'Shenzhen' then 1000
    -> else sales
    -> end
    -> 'new sales',date
    -> from store_info;
+------------+-----------+------------+
| store_name | new sales | date       |
+------------+-----------+------------+
| Guangzhou  |      1500 | 2020-12-05 |
| Shenzhen   |      1000 | 2020-12-07 |
| Guangzhou  |       300 | 2020-12-08 |
| Beijing    |      1400 | 2020-12-08 |
+------------+-----------+------------+
4 rows in set (0.00 sec)

then 数字，将直接返回该数字，即重新为匹配行赋值
'new sales'是用于case字段的字段名

九、常用算法

1. 排名

表格自我连结（self join），然后将结果依序列出，算出每一行之前（包含那一行本身）有多少行数

mysql> select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2  
    -> where A1.sales < A2.sales or
    -> (A1.sales = A2.sales and A1.name = A2.name)
    -> group by A1.name,A1.sales order by A1.sales desc;                       
+----------+-------+------+
| name     | sales | rank |
+----------+-------+------+
| sunqi    |    50 |    1 |
| zhaoliu  |    40 |    2 |
| wujiu    |    30 |    3 |
| wangwu   |    20 |    4 |
| zhouba   |    20 |    4 |
| lisi     |    15 |    6 |
| zhangsan |    10 |    7 |
+----------+-------+------+
7 rows in set (0.00 sec)

统计sales字段的值是比自己本身的值小的以及sales字段和name字段都相同的数量，比如zhangsan为6+1=7。
由于wangwu和zhouba的sales列都为20，并列第四，若需将他俩分出第四和第五，可用以下方法（由于sales相同，只能根据name进行区分排序）

mysql> select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2  
    -> where A1.sales < A2.sales or
    -> (A1.sales = A2.sales and A1.name >= A2.name)
    -> group by A1.name,A1.sales order by A1.sales desc;  
+----------+-------+------+
| name     | sales | rank |
+----------+-------+------+
| sunqi    |    50 |    1 |
| zhaoliu  |    40 |    2 |
| wujiu    |    30 |    3 |
| wangwu   |    20 |    4 |
| zhouba   |    20 |    5 |
| lisi     |    15 |    6 |
| zhangsan |    10 |    7 |
+----------+-------+------+
7 rows in set (0.00 sec)

2. 中位数

中位排名取值1：通过ceil取(行数/2)后的最小整数

mysql> select name,sales middle from                                          
    -> (select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)
    -> group by A1.name,A1.sales order by A1.sales desc) A3                   
    -> where A3.rank = (select ceil(count(rank)/2) from total_sales);                                                                          
+--------+--------+
| name   | middle |
+--------+--------+
| wangwu |     20 |
+--------+--------+
1 row in set (0.00 sec)

中位排名取值2：将(行数+1)/2取商

mysql> select name,sales middle from                                          
    -> (select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)
    -> group by A1.name,A1.sales order by A1.sales desc) A3                   
    -> where A3.rank = (select (count(*)+1) div 2 from total_sales);
+--------+--------+
| name   | middle |
+--------+--------+
| wangwu |     20 |
+--------+--------+
1 row in set (0.00 sec)

div是在MySQL中算出商的方式
每个派生表必须有自己的别名，所以别名A3必须要有

也可使用view视图进行操作

mysql> create view V1 as 
    -> select A1.name,A1.sales,count(A2.sales) rank from total_sales A1,total_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)
    -> group by A1.name,A1.sales order by A1.sales desc;
Query OK, 0 rows affected (0.00 sec)

mysql> select name,sales middle from V1 
    -> where rank = (select ceil(count(rank)/2) from total_sales);
+--------+--------+
| name   | middle |
+--------+--------+
| wangwu |     20 |
+--------+--------+
1 row in set (0.00 sec)

3. 累计总计

表格自我连结（self join），然后将结果依序列出，算出每一行之前（包含哪一行本身）的总合

mysql> select A1.name,A1.sales,sum(A2.sales) sum_total,count(A2.sales) rank from total_sales A1,total_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.namee)
    -> group by A1.name,A1.sales order by A1.sales desc;
+----------+-------+-----------+------+
| name     | sales | sum_total | rank |
+----------+-------+-----------+------+
| sunqi    |    50 |        50 |    1 |
| zhaoliu  |    40 |        90 |    2 |
| wujiu    |    30 |       120 |    3 |
| wangwu   |    20 |       140 |    4 |
| zhouba   |    20 |       160 |    5 |
| lisi     |    15 |       175 |    6 |
| zhangsan |    10 |       185 |    7 |
+----------+-------+-----------+------+
7 rows in set (0.00 sec)

4. 总合百分比

mysql> select A1.name,A1.sales,A1.sales/(select sum(sales) from total_sales) peer_total,count(A2.sales) rank from total_sales A1,total_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)
    -> group by A1.name,A1.sales order by A1.sales desc;                      
+----------+-------+-----------+------+
| name     | sales | per_total | rank |
+----------+-------+-----------+------+
| sunqi    |    50 |    0.2703 |    1 |
| zhaoliu  |    40 |    0.2162 |    2 |
| wujiu    |    30 |    0.1622 |    3 |
| zhouba   |    20 |    0.1081 |    5 |
| wangwu   |    20 |    0.1081 |    4 |
| lisi     |    15 |    0.0811 |    6 |
| zhangsan |    10 |    0.0541 |    7 |
+----------+-------+-----------+------+
7 rows in set (0.00 sec)

select sum(sales) from total_sales这一段子查询用来算出总合；
总合算出来后，我们就能够将每一行除以总合来求出每一行的总和百分比。
若需要显示小数后两位的百分数，可使用"round(A1.sales/(select sum(sales) from total_sales)*100,2) || '%'"的操作。

mysql> select A1.name,A1.sales,round(A1.sales/(select sum(sales) from total_sales)*100,2) || '%' per_total,count(A2.sales) rank from total_sales A1,total_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)
    -> group by A1.name,A1.sales order by A1.sales desc;                      
+----------+-------+-----------+------+
| name     | sales | per_total | rank |
+----------+-------+-----------+------+
| sunqi    |    50 | 27.03%    |    1 |
| zhaoliu  |    40 | 21.62%    |    2 |
| wujiu    |    30 | 16.22%    |    3 |
| wangwu   |    20 | 10.81%    |    4 |
| zhouba   |    20 | 10.81%    |    5 |
| lisi     |    15 | 8.11%     |    6 |
| zhangsan |    10 | 5.41%     |    7 |
+----------+-------+-----------+------+
7 rows in set (0.00 sec)

5. 累计总合百分比

mysql> select A1.name,A1.sales,sum(A2.sales)/(select sum(sales) from total_sales) per_total,count(A2.sales) rank from total_sales A1,total_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)                                                                            
    -> group by A1.name,A1.sales order by A1.sales desc;                      
+----------+-------+-----------+------+
| name     | sales | per_total | rank |
+----------+-------+-----------+------+
| sunqi    |    50 |    0.2703 |    1 |
| zhaoliu  |    40 |    0.4865 |    2 |
| wujiu    |    30 |    0.6486 |    3 |
| wangwu   |    20 |    0.7568 |    4 |
| zhouba   |    20 |    0.8649 |    5 |
| lisi     |    15 |    0.9459 |    6 |
| zhangsan |    10 |    1.0000 |    7 |
+----------+-------+-----------+------+
7 rows in set (0.00 sec)

显示小数后两位的百分数

mysql> select A1.name,A1.sales,round(sum(A2.sales)/(select sum(sales) from total_salal_sales)*100,2) || '%' per_total,count(A2.sales) rank from total_sales A1,totaal_sales A2
    -> where A1.sales < A2.sales or (A1.sales = A2.sales and A1.name >= A2.name)                                                                            
    -> group by A1.name,A1.sales order by A1.sales desc;                      
+----------+-------+-----------+------+
| name     | sales | per_total | rank |
+----------+-------+-----------+------+
| sunqi    |    50 | 27.03%    |    1 |
| zhaoliu  |    40 | 48.65%    |    2 |
| wujiu    |    30 | 64.86%    |    3 |
| wangwu   |    20 | 75.68%    |    4 |
| zhouba   |    20 | 86.49%    |    5 |
| lisi     |    15 | 94.59%    |    6 |
| zhangsan |    10 | 100.00%   |    7 |
+----------+-------+-----------+------+
7 rows in set (0.00 sec)

十、空值(NULL)和无值('')的区别

无值的长度为0，不占用空间；而NULL值的长度是NULL，占用空间。

mysql> select length(NULL),length(''),length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
|         NULL |          0 |           1 |
+--------------+------------+-------------+
1 row in set (0.00 sec)

is null或者is not mull，是用来判断字段是不是为NULL，不能查出是不是无值。

mysql> select * from city where city_name is null;
+-----------+
| city_name |
+-----------+
| NULL      |
| NULL      |
+-----------+
2 rows in set (0.00 sec)

mysql> select * from city where city_name is not null;
+-----------+
| city_name |
+-----------+
| beijing   |
| nanjing   |
| shanghai  |
|           |
+-----------+
4 rows in set (0.00 sec)

无值的判断使用=''、<>''或!=''来处理，<>和!=代表不等于。

mysql> select * from city where city_name = '';
+-----------+
| city_name |
+-----------+
|           |
+-----------+
1 row in set (0.00 sec)

mysql> select * from city where city_name <> '';
+-----------+
| city_name |
+-----------+
| beijing   |
| nanjing   |
| shanghai  |
+-----------+
3 rows in set (0.00 sec)

mysql> select * from city where city_name != '';
+-----------+
| city_name |
+-----------+
| beijing   |
| nanjing   |
| shanghai  |
+-----------+
3 rows in set (0.00 sec)

注：<>''和!=''为不是无值的行，但NULL本质上也没有实际值，因此也会被忽略。

在通过count()指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(city_name) from city;
+------------------+
| count(city_name) |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

十一、正则表达式

匹配模式	描述	实例
^	匹配文本的开始字符	'^a'匹配以a开头的字符串
$	匹配文本的结束字符	'a$'匹配以a结尾的字符串
.	匹配任何单个字符	'a.c'匹配任何a和c之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	'ye*p'匹配p前面有任意个e
+	匹配前面的字符1次或多次	'hi+'匹配以hi开头，后面至少一个i的字符串
字符串	匹配包含指定的字符串	'test'匹配含有test的字符串
a\|b	匹配a或b	'test\|wrok'匹配test或者work
[...]	匹配字符集合中的任意一个字符	'[abc]'匹配a或者b或者c
[^...]	匹配不在括号中的字符	'[abc]'匹配不包含a或者b或者c的字符串
	匹配前面的字符串n次	'a{2}'匹配含有2个a的字符串
	匹配前面的字符串至少n次，至多m次	'a{1,2}'匹配a至少1次，至多2次

语法：select "字段" from "表名" where "字段" regexp {模式};

mysql> select * from store_info where store_name regexp 'ng';
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
3 rows in set (0.00 sec)

mysql> select * from store_info where store_name regexp '^[A-C]';
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
1 row in set (0.00 sec)

mysql> select * from store_info where store_name regexp 'ei|en';
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)

十二、存储过程

1. 存储过程的定义

存储过程是一组为了完成特定功能的SQL语句集合，类似于shell中的函数。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

2. 存储过程的优点

执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率。
SQL语句加上控制语句的集合，灵活性高。
在服务器端存储，客户端调用时，降低网络负载。
可多次重复被调用，可随时修改，不影响客户端调用。
可完成所有的数据库操作，也可控制数据库的信息访问权限。

3. 创建存储过程

mysql> delimiter $$
#将语句的结束符号从分号;临时改为$$(可以是自定义)
mysql> create procedure test()
#创建存储过程，过程名为test，不带参数
    -> begin
#过程体以关键字begin开始
    -> select * from store_info;
#过程体语句
    -> end $$
#过程体以关键字end结束
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
#将语句的结束符号恢复为分号;

4. 调用存储过程

mysql> call test;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Shenzhen   |   250 | 2020-12-07 |
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

5. 查看存储过程

show create procedure [数据库.]存储过程名
查看某个存储过程的具体信息

mysql> show create procedure test;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                                                                                 | Create Procedure                                                                                            | character_set_client | collation_connection | Database Collation |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| test      | PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER="skip-grants user"@"skip-grants host" PROCEDURE "test"()
begin
select * from store_info;
end | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

也可查看procedure状态并使用like语句进行匹配

mysql> show procedure status like '%test%'\G
*************************** 1. row ***************************
                  Db: test
                Name: test
                Type: PROCEDURE
             Definer: skip-grants user@skip-grants host
            Modified: 2021-09-03 02:40:10
             Created: 2021-09-03 02:40:10
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

6. 存储过程的参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值（传出值只能是变量）
INOUT 输入输出参数：即表示调用者向过程传入值，也表示过程向调用者传出值

mysql> delimiter $$
mysql> create procedure test1(in inname char(20))
    -> begin
    -> select * from store_info where store_name=inname;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call test1('Beijing');
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

7. 删除存储过程

存储过程内容到的修改方法使用过删除原有存储过程，之后再以相同的名称创建新的存储过程。

mysql> drop procedure if exists test;
Query OK, 0 rows affected (0.01 sec)

仅当存在时删除，不添加if exists时，如果指定过程不存在，则产生一个错误

mysql> drop procedure test;
ERROR 1305 (42000): PROCEDURE test.test does not exist
mysql> drop procedure if exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

8. 存储过程的控制语句

制表

mysql> create table t (id int(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

（1）条件语句if-then-else...end if

mysql> delimiter $$                     
mysql> create procedure test(in pro int)
    -> begin
    -> declare var int;
    -> set var=pro*2;
    -> if var>=10 then
    -> update t set id=id+1;
    -> else
    -> update t set id=id-1;
    -> end if;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call test(6);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

mysql> call test(4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

（2）循环语句while...end while

mysql> delimiter $$              
mysql> create procedure test2()
    -> begin
    -> declare var int(10);
    -> set var=0;
    -> while var<6 do
    -> insert into t values(var);
    -> set var=var+1;
    -> end while;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call test2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| id   |
+------+
|   10 |
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
7 rows in set (0.00 sec)

posted @ 2021-04-17 14:12 丨君丶陌阅读(1693) 评论(2) 编辑收藏举报

刷新页面返回顶部

丨君丶陌

君子不行陌路，管它咫尺还是天涯。

MySQL高级（进阶）SQL语句

MySQL高级（进阶）SQL语句

一、实例准备--制表

1. 表1（商店区域表）

2. 表2（商店销售表）

3. 表3（城市表）

4. 表4（total_sales）

二、SQL语句

1. select

2. distinct

3. where

4. and|or

5. in

6. between

7. limit

8. 通配符

9. like

10. order by

三、函数

1. 数学函数

（1）abs(x)

（2）rand()

（3）mod（x,y）

（4）power(x,y)

（5）round(x)

（6）round(x,y)

（7）sqrt(x)

（8）truncate(x,y)

（9）ceil(x)

（10）floor(x)

（11）greatest(x1,x2...)

（12）least(x1,x2...)

2. 聚合函数

（1）avg()

（2）count()

（3）min()

（4）max()

（5）sum()

3. 字符串函数

（1）trim()

（2）concat(x,y)

（3）substr(x,y)

（4）substr(x,y,z)

（5）length(x)

（6）replace(x,y,z)

（7）upper(x)

（8）lower(x)

（9）left(x,y)

（10）right(x,y)

（11）reprat(x,y)

（12）space(x)

（13）strcmp(x,y)

（14）reverse(x)

四、SQL查询语句

1. group by

2. having

3. [as] 别名

4. 子查询

5. exists

6. join--连接查询

五、create view--视图

1. 视图的定义

2. 视图和表格的区别

3. 视图的特点

4. 视图的语法

5. 查看视图

6. 删除视图

六、union--联集

七、有/无交集值

1. 出现重复的行

2. 两张表都出现的重复的行

3. 无交集值

八、case

1. case概述

2. case语法

3. case使用实例

九、常用算法

1. 排名