6、mysql高级语句

mysql高级语句（一）

一、MySQL高级进阶SQL 语句

mysql -uroot -p
show databases;

create database train_ticket;
use train_ticket;
create table REGION(region varchar(10),site varchar(20));
create table FARE(site varchar(20),money int(10),date varchar(15));

desc REGION;
desc FARE;

insert into REGION values (‘south’,‘changsha’);
insert into REGION values (‘south’,‘nanchang’);
insert into REGION values (‘north’,‘beijing’);
insert into REGION values (‘north’,‘tianjin’);

insert into FARE values (‘changsha’,1000,‘2021-01-30’);
insert into FARE values (‘nanchang’,700,‘2021-01-30’);
insert into FARE values (‘beijing’,1500,‘2021-01-30’);
insert into FARE values (‘tianjin’,1200,‘2021-01-30’);
insert into FARE values (‘beijing’,2200,‘2021-02-05’);

select * from REGION;
select * from FARE;

1、选择select

显示表格中一个或数个字段的所有资料
语法：SELECT 字段 FROM 表名

#查看REGION表中的region字段
select region from REGION;
1
2

2、去重distinct

不显示重复的资料（去重）
语法：SELECT DISTINCT 字段 FROM 表名

#查看REGION表中的region字段并去重
select distinct region from REGION;
1
2

3、条件语句where

有条件查询
语法：SELECT 字段 FROM 表名 WHERE 条件

#查看FARE表中的site字段，且条件满足金额大于1000
select site from FARE where money > 1000;
#查看FARE表中的site字段，且条件满足金额小于1000
select site from FARE where money < 1000;
#查看FARE表中的site字段，且条件满足金额等于1000
select site from FARE where money = 1000;
1
2
3
4
5
6

4、and/or

安、岛*、or（或者）
语法：SELECT 字段 FROM 表名 WHERE 条件1 ([AND|OR] 条件2)+；

#查看FARE表中的site和money字段，且条件满足金额大于1000并小于1500
select site,money from FARE where money > 1000 and (money < 1500);
#查看FARE表中的site和money字段，且条件满足金额小于500或者大于700并小于1500
select site,money from FARE where money < 500 or (money < 1500 and money >= 700);
#查看FARE表中的site和money字段，且条件满足金额大于500小于1000，且时间在2021年2月5日之前
select site,money,date from FARE where money >= 500 and (date < '2021-02-05' and money < 1000);
1
2
3
4
5
6

5、in

显示已知的值的资料
语法：SELECT 字段 FROM 表名 WHERE 字段 IN (‘值1’,‘值2’,……);

##查看FARE表中的site和money字段，且条件满足金额等于700或1000
select site,money from FARE where money in (700,1000);
1
2

6、区间between

显示两个值范围内的资料
语法：SELECT 字段 FROM 表名 WHERE 字段 BETWEEN ‘值一’ and ‘值二’;

#查看FARE表中的site和money字段，且条件满足金额大于1000并小于1500
select * from FARE where money between 500 and 1000;
1
2

7、通配符like

通常通配符都是跟LIKE一起使用

LIKE：用于匹配模式来查找资料
语法：SELECT 字段 FROM 表名 WHERE 字段 LIKE ‘模式’;

#查看FARE表中的所有字段，且条件满足site字段以be开头或等于be
select * from FARE where site like 'be%';
#查看FARE表中的所有字段，且条件满足site字段以jin开头且后面跟一个字符
select site,money from FARE where site like '%jin_';
1
2
3
4

8、排序order by

按关键字排序
语法：SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 字段 [ASC,DESC];

#查看FARE表中的所有字段，以金额进行降序排序
select * from FARE order by money desc;
#查看FARE表中的日期和金额字段，以金额进行降序排序
select date,money from FARE order by money desc;
1
2
3
4

9、连接符| |

• 如果sql_mode开启开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的

mysql -uroot -puse train_ticket; 
select region || ' ' || site from REGION where region = 'north';
select site || ' ' || money || ' ' || date from FARE;
1
2
3

10、分组group by

• BY后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的
• GROUP BY 有一个原则，就是 SELECT 后面的所有列中，没有使用聚合函数的列，必须出现在GROUP BY后面。

语法：SELECT 字段1,SUM(字段2) FROM 表名 GROUP BY 字段1;

#查看FARE 表中的site和money字段，并且对site字段分类汇总求和金额
select site,sum(money) from FARE group by site;
#查看FARE 表中的site和money和date字段，并且对site字段分类汇总求和金额且以金额降序排序
select site,sum(money),date from FARE group by site order by money desc;
#查看FARE 表中的site和money和date字段和money统计次数，并且对site字段分类汇总求和金额且以金额降序排序
select site,count(money),sum(money),date from FARE group by site order by money desc;
1
2
3
4
5
6

11、函数having

• 用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用。
• HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。

语法：SELECT 字段1,SUM(字段2) FROM 表名 GROUP BY 字段1 HAVING(函数条件);

#查看FARE 表中的site和money和date字段和money统计次数，并且对site字段分类汇总,且求和金额大于700的记录
select site,count(money),sum(money),date from FARE group by site having sum(money) >=700;
1
2

12、统计次数count

#统计所有的数据条数
select count(*) from FARE;  
#统计相同date的记录条数
select count(site),date from FARE group by date;

1
2
3
4
5

13、限制limit

limit限制输出的结果记录
在使用MySQL select 语句进行查询时，结果集返回的是所有匹配的记录（行），有时候仅需要返回第一行或者前几行，这时候就需要用到limit

select from FARE limit 3,3;
select id,name from info order by id limit 3;
1
2

二、函数

1、数学函数

select abs(-1),rand(),mod(5,3),power(2,3),round (1.579),round(1.734,2); select sqrt(9),truncate(1.234,2),ceil(1.2),floor(1.9),greatest(1,2,3,4),least(1,2,3,4);
1

2、聚合函数

select avg(money) from FARE;
select count(money) from FARE;
select min(money) from FARE;select max(money) from FARE;
select sum(money) from FARE;
1
2
3
4

• #count(*)包括所有列的行数，在统计结果时，不好忽略值为null
• #count(字段)只包括那一行的列数，在统计结果的时候，会忽略列值为null的值

3、字符串函数

SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
1

• #[位置]：的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。
• #[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。

 1 select trim(leading 'na' from 'nanchang');
 2 select trim(trailing '--' from 'nanchang--');
 3 select trim(both '--' from '--nanchang--');
 4 
 5 select concat(region,site) from REGION where region = 'south';
 6 select concat(region,' ',site) from REGION where region = 'south';
 7 
 8 select substr(money,1,2) from FARE;
 9 
10 select length(site) from FARE;
11 
12 select replace(site,'ji','--') from FARE;
13 
14 select upper(site) from FARE;
15 
16 select lower('HAHAHA');
17 
18 select left(site,2) from FARE;
19 
20 select right(site,3) from FARE;
21 
22 select repeat(site,2) from FARE;
23 
24 select space(2); 
25 
26 select strcmp(100,200);
27 
28 select reverse(site) from FARE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Mysql高阶语句(二)

1、别名as

在MySQL查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名，使用的时候直接使用别名，简化命令，增强可读性

#列的别名
select 字段 as 字段别名 from 表名；   #as可以省略直接设置
select 字段 字段别名 from 表名；
#表的别名
select “表格別名”.“字段1” [AS] “字段1的別名” FROM “表格名” [AS] “表格別名”;
1
2
3
4
5

#查询REGION表，给region字段一个reg别名，并分类汇总,查看reg和统计site次数，给表一个RE别名
select RE.region AS reg, count(site) from REGION AS RE group by reg; 
#查询FARE表，给site字段一个si别名，分类汇总si,查看si,date,money求和，money统计次数，并给FARE表一个FA的别名
select FA.site AS si,sum(money),count(money),date AS da from FARE AS FA group by si;
1
2
3
4

2、子查询

select

连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL 语句
语法：

select 字段1，字段2 from 表名1 where 字段 in (select 字段 from 表名 where 条件)；
1

• 可以是符号的运算符
  例:=、>、<、>=、<=
• 也可以是文字的运算符
  例:LIKE、IN、BETWEEN

1.相同表查询
#查询FARE表的site和money字段，且site需要满足在FARE表里面金额小于2000的
select site,money from `FARE` where site in(select site from `FARE` where money<2000); 
1
2
3

2.多表联查
#多表联查，在FARE里金额小于2000的site和REGION表里的region查询显示出来
select A.site,region from REGION AS A where A.site in(select B.site from FARE AS B where money<2000); 
#多表联查，查询A表site、money、date字段，site的条件是满足B表region=north的site。
select B.site,money,date from FARE AS B where site in(select A.site from REGION AS A where region = 'north');
1
2
3
4
5

3.取反not
将子查询的结果，进行取反操作
##多表联查，查询A表site、money、date字段，site的条件是不满足B表region=north的site。
select B.site,money,date from FARE AS B where site not in(select A.site from REGION AS A where region = 'north');
1
2
3
4

insert

子查询还可以用在insert语句中，子查询的结果集可以通过insert语句插入到其它表中

#重新举个案例
insert into testl select from test where id in (select id from test);
1
2

update

update语句也可以使用子查询，update内的子查询，在set更新内容时，可以是单独的一列，也可以是多列。

#更改金额等于100，条件是site在REGION表中site为长沙
update FARE set money=400 where site in (select site from REGION where site='changsha');


1
2
3
4

delete

#子查询site=changsha ,主查询，将满足的site删除
delete from FARE where site in (select region from REGION where region='south');
1
2

exists

exists这个关键字在子查询时，主要用于判断子查询的结果集是否为空，如果不为空，则返回true，反之则返回false
ps：在使用exists时，当子查询有结果时，不关心子查询的内容，执行主查询操作；当子查询没有结果时，则不执行主查询操作。

1.语法
SELECT 字段1 FROM 表1 WHERE EXISTS (SELECT * FROM 表2 WHERE 条件);

#子查询为真判断
select count(*) from FARE where exists (select site from FARE where site='beijing');
#子查询为假判断
select count(*) from FARE where exists (select site from FARE where site='nanjing');
1
2
3
4
5
6
7

as别名

将结果集作为一张表进行查询的时候，需要用到别名

select count(*) from (select site from FARE where site='beijing') a;
1

4、连接查询

MysQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。
首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括：内连接、左连接和右连接（inner join、left join、right join）

1.inner join（等值相连）

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用 关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表

**内连查询：**通过inner join的方式将两张表指定的相同字段的记录行输出出来
**内连查询：**面试，直接了当的说用inner join就可以

只返回两个表中联接字段相等的行

语法：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;
1

2.left join（左联接）

左连接也可以被称为左外连接，在FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行

左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方为NULL

返回包括左表中所有记录和右表中联接字段相等的记录

右表中没用的用NULL填充

语法：SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;
1

3.right join（右联接）

右连接也被称为右外连接，在FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

返回包括右表中的所有记录和左表中联接字段相等的记录

左表中没有的用null填充

语法：SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
1

例:

update FARE set site='xingjiang' where money='2200'; 
select * from REGION AS A inner join FARE AS B on A.site = B.site;
select * from REGION AS A left join FARE AS B on A.site = B.site;
select * from REGION AS A right join FARE AS B on A.site = B.site;
1
2
3
4

**5、视图create view **

视图时一张虚拟的表，这张虚拟表中不包含真实数据，只是做了真实数据的映

功能：
简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
本质而言，视图是一种select（结果集的呈现）
注：视图适合于多表浏览时使用，不适合增、删、改

视图与表的区别和联系：

区别：

• 视图是已经编译好的sql语句。而表不是

• 视图没有实际的物理记录。而表有

• 表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改

• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构

• 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表

• 视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

联系
视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系

语法：CREATE VIEW 视图表名 AS SELECT语句;


#创建视图
create view 视图表名 as select * from 表名 where 条件;
#查看视图
select * from 视图表名
#查看表状态
show table status\G
#查看视图结构
desc 视图表名
#删除视图
drop view 视图名
1
2
3
4
5
6
7
8
9
10
11
12
13

例:

1.
select A.region,SUM(B.money) from REGION AS A inner join FARE AS B on A.site = B.site GROUP BY region;

create view C AS select A.region,SUM(B.money) from REGION AS A inner join FARE AS B on A.site = B.site GROUP BY region;

show tables;

select * from C;

1
2
3
4
5
6
7
8
9

例：

select * from FARE;
create view fare as select * from FARE where site='beijing';
show tables;
select * from fare;
1
2
3
4

查看视图与原表结构；

• 修改视图表数据
  当数据发生变化时，若数据与之前创建视图表时的关联条件不一致时，视图表的数据将会发生改变

• 更改源表数据

数据再次满足视图表的关联条件，查看视图表，数据又会出现

6、UNION 联集

将两个SQL语句的结果合并起来，两个SQL语句产生的字段需要是同样的资料种类

• UNION:生成结果的资料值将没有重复，且按照字段的顺序进行排序。

语法：SELECT语句1 UNION SELECT语句2;
1

• UNION ALL：将生成结果的资料值都列出来，无论有无重复

语法：SELECT语句1 UNION ALL SELECT语句2;
1

例：

select region from REGION union select money from FARE;select site from REGION union select site from FARE;select site from REGION union all select site from FARE;
1

7、交集值

取两个SQL语句结果的交集

例:

 1 select * from FARE;
 2 insert into FARE values ('xingjiang',2200,'2021-02-05');
 3 select * from FARE;
 4 
 5 select A.site from FARE AS A inner join REGION AS B on A.site = B.site;
 6 select A.site from FARE AS A inner join REGION AS B  using(site);
 7 
 8 select site from (select site from FARE union all select site from REGION) A group by A.site HAVING count(*) > 1;
 9 
10 select site from (select A.site from FARE A inner join REGION B on A.site = B.site) C group by C.site;
11 
12 select site from FARE where site in (select site from REGION);
1
2
3
4
5
6
7
8
9
10
11
12

8、无交集值

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

select distinct site from FARE where (site) not in (select site from REGION);
1

例：

select A.*,B.* from FARE A left join REGION B using(site);select site from FARE A left join REGION B using(site) where B.site is null;select distinct site from FARE A left join REGION B using(site) where B.site is null;
1

9、CASE

是SQL用来作为IF-THEN-ELSE之类逻辑的关键字

语法：

1 SELECT CASE （字段名）
2     WHEN 条件1 THEN  结果1
3     WHEN 条件2 THEN 结果2
4     ……
5     ELSE 结果N
6     END
7 FROM 表名
8 
9 #条件可以是一个数值或是公式。ELSE子句不是必须的。
1
2
3
4
5
6
7
8
9

例:

1 select * from FARE;
2 
3 select case site
4    when 'changsha' then money + 500
5    when 'tianjin' then money - 500
6    else money * 2
7    end
8    A,site
9 from FARE;
1
2
3
4
5
6
7
8
9

10、算排名

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

例：

1 create table SCORE (name char(10),score int(4));
2 
3 insert into SCORE values('zhangsan',95);
4 insert into SCORE values('lisi',96);
5 insert into SCORE values('wangwu',75);
6 insert into SCORE values('zhaoliu',80);
7 insert into SCORE values('qianqi',90);
8 select * from SCORE;
1
2
3
4
5
6
7
8

select A1.name,A1.score,count(A2.score) rank from  SCORE A1,SCORE A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;
1

解释：

当A1的score字段值小于A2的score字段值、或者两表score字段值相等并且name字段值相等时，从A1和A2表中 查询A1的name字段值、A1的score字段值、和A2的score字段的非空值 rank是别名 ，并为A1的name字段分组，A1的score字段降序排序

1 A1.score为75时，A2.score可为75、80、90、95、96 则 count(A2.score)为5
2 A1.score为80时，A2.score可为80、90、95、96 则 count(A2.score)为4
3 A1.score为90时，A2.score可为90、95、96 则 count(A2.score)为3
4 A1.score为95时，A2.score可为95、96 则 count(A2.score)为2
5 A1.score为96时，A2.score可为96 则 count(A2.score)为1
1
2
3
4
5

 1 这样再显示A1的name字段分组，A1的score字段降序排序 ，rank字段
 2 则为下表所示
 3 
 4 +----------+-------+------+
 5 | name     | score | rank |
 6 +----------+-------+------+
 7 | lisi     |    96 |    1 |
 8 | zhangsan |    95 |    2 |
 9 | qianqi   |    90 |    3 |
10 | zhaoliu  |    80 |    4 |
11 | wangwu   |    75 |    5 |
12 +----------+-------+------+
13 
14  统计Sales栏位的值是比自己本身的值笑得以及Sales栏位得Name栏位都相同的数量，比如zhangsan为6+1=7
1
2
3
4
5
6
7
8
9
10
11
12
13
14

11、算中位数

例：

select * from (select A1.name,A1.score,count(A2.score) rank from SCORE A1,SCORE A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc) A3 
where A3.rank = (select (count(*)+1) DIV 2 from SCORE); select score mid from (select A1.name,A1.score,count(A2.score) rank from SCORE A1,SCORE A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc) A3 
where A3.rank = (select (count(*)+1) DIV 2 from SCORE);
1

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

12、算累积总计

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

select A1.*,sum(A2.score) sum_socore from SCORE A1,SCORE A2 where A1.score < A2.score or(A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;
1

13、算总合百分比

例：

select A1.*,A1.score/(select sum(score) from SCORE) z_sum from SCORE A1,SCORE A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name;
1

select sum(socre) from SCORE 是为了算出字段值总合，然后每一行一一除以总合，算出每行的总合百分比。

14、算累计总合百分比

例:

select A1.name,A1.score,sum(A2.score),sum(A2.score)/(select sum(score) from SCORE) Z from SCORE A1,SCORE A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc; select A1.name,A1.score,sum(A2.score),TRUNCATE(sum(A2.score)/(select sum(score) from SCORE),2) ||'%' Z from SCORE A1,SCORE A2
where A1.score < A2.score or (A1.score=A2.score and A11.name=A2.name) group by A1.name order by A1.score desc;
1

15、空值（null）和无值（’’）的区别

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意 的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有 值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是NULL值

• 1、无值的长度为0，不占用空间；而空值null 的长度是null，是占用空间的
• 2、IS NULL或者IS NOT NULL，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的
• 3、无值的判断使用=’‘或者<>’'来处理。<>代表不等于
• 4、在通过count()指定字段统计又多少行数时，如果遇到NULL值会自动忽略掉，遇到空值会自动加入记录中进行计算

例：

1.环境准备
create table SITE(site varchar(20));
insert into SITE values('nanjing');
insert into SITE values('beijing');
insert into SITE values('');
insert into SITE values('taijin');
insert into SITE values();
insert into SITE values('');
select * from SITE;    

2.测试
select length(site) from SITE;
select * from SITE where site is NULL;
select * from SITE where site is not NULL;
select * from SITE where site ='';
select * from SITE where site <> '';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

16、正则表达式(与Shell部分一样)

语法：SELECT 字段 FROM 表名 WHERE 字段 REGEXP 匹配模式

例：

select * from FARE where site regexp '^[n]';select * from FARE where site regexp '[n]';select * from FARE where site regexp 'jin|jing';
1

17、存储过程（与Shell函数差不多,代码的复用）
存储过程是一组为了完成特定功能的SQL语句集合

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称来进行储存，这个过程经编译和优化后存储在数据库服务器中，当需要使用该存储过程时，只需要调用它即可，存储过程在执行上比传统SQL速度更快，执行效率更高。
存储过程的优点

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

 1 DELIMITER !!                    #将语句的结束符号从分号；临时修改，以防出问题，可以自定义
 2 CREATE PROCEDURE XXX()      #创建存储过程，过程名自定义,（）可带参数
 3      BEGIN                   #过程体以关键字BEGIN开始
 4      select * from xxx;      #过程体语句
 5      END!!                    #过程体以关键字END结尾
 6 DELIMITER ;                     #将语句的结束符号恢复为分号
 7 
 8 call XXX;                      #调用存储过程
 9 
10 ====查看存储过程====
11 show create procedure [数据库.]储存过程名;       #查看某个储存过程的具体信息
12 show create procedure XXX;
13 show procedure status [like '%XXX%'] \G
1
2
3
4
5
6
7
8
9
10
11
12
13

例：

1 DELIMITER !!
2 CREATE PROCEDURE KIND1()
3 BEGIN
4     select * from FARE;
5 END!!
6 
7 DELIMITER ;
8 CALL KIND1;
1
2
3
4
5
6
7
8

show create procedure train_ticket.KIND1\Gshow procedure status like '%KIND1%'\G
1

存储过程的参数

例：

1 DELIMITER !!
2 CREATE PROCEDURE KIND2(IN place char(20))
3 BEGIN
4     select * from FARE where site=place;                                                    
5 END!!         
6 
7 DELIMITER ;
8 CALL KIND2('beijing');
1
2
3
4
5
6
7
8

(1)存储过程的条件语句

例：

 1 DELIMITER !!
 2 CREATE PROCEDURE KIND3(IN num int(10))
 3 BEGIN
 4 declare var int;
 5 set var=num*2;
 6 if var>=10 then
 7     update FARE set money=money+1;
 8 else 
 9     update FARE set money=money-1;
10 end if;
11 END!!
12 DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12

CALL KIND3(5);CALL KIND3(4);
1

(2)循环语句while

例：

 1 create table akg(id int);
 2 
 3 DELIMITER !!
 4 CREATE PROCEDURE KIND4()
 5 BEGIN
 6 declare var int;
 7 set var=0;
 8 while var<5 do
 9     insert into akg values(var);
10     set var=var+1;
11 end while;
12 END!!
13 
14 DELIMITER ;
15 CALL KIND4;
16 select * from akg;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

[外链图片转存中…(img-kV1E4GIt-1663297820146)]

例：

1 DELIMITER !!
2 CREATE PROCEDURE KIND2(IN place char(20))
3 BEGIN
4     select * from FARE where site=place;                                                    
5 END!!         
6 
7 DELIMITER ;
8 CALL KIND2('beijing');
1
2
3
4
5
6
7
8

(1)存储过程的条件语句

例：

 1 DELIMITER !!
 2 CREATE PROCEDURE KIND3(IN num int(10))
 3 BEGIN
 4 declare var int;
 5 set var=num*2;
 6 if var>=10 then
 7     update FARE set money=money+1;
 8 else 
 9     update FARE set money=money-1;
10 end if;
11 END!!
12 DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12

CALL KIND3(5);CALL KIND3(4);
1

(2)循环语句while

例：

 1 create table akg(id int);
 2 
 3 DELIMITER !!
 4 CREATE PROCEDURE KIND4()
 5 BEGIN
 6 declare var int;
 7 set var=0;
 8 while var<5 do
 9     insert into akg values(var);
10     set var=var+1;
11 end while;
12 END!!
13 
14 DELIMITER ;
15 CALL KIND4;
16 select * from akg;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16