MySQL高级SQL语句
2021/10/31 19:10:15
本文主要是介绍MySQL高级SQL语句，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
目录
- 前言
- 1.按关键字排序
- 2.对结果进行分组
- 3.限制结果条目（limit）
- 4.设置别名（alias ——》as）
- 5、通配符
- 6、子查询
- 7. 视图
- 8.NULL 值
- 9.MySQL高阶语句
- 9.1正则表达式
- 9.2运算符
- 9.2.1算术运算符
- 9.2.2比较运算符
- 9.2.3逻辑运算符(布尔值)
- 9.2.4位运算符
- 10.数据库函数
- 10.1数学函数
- 10.2聚合函数
- 10.3字符串函数
- 10.4日期时间函数
- 11.存储过程
- 总结
前言
1.MySQL进阶查询？2.MySQL数据库函数？3.MySQL存储过程？
1.按关键字排序
PS:类比于windows 任务管理器
使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，可以使用 ORDER BY 语句来对语句实现排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段
（1）语法
SELECT column1, column2, … FROM table_name ORDER BY column1, column2, …
ASC|DESC;
ASC 是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC方式进行排序。
DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
模板表：
数据库有一张info表，记录了学生的id，姓名，分数，地址和爱好
create table info (id int(10) primary key not null auto_increment,name varchar(20),score decimal(5,2),address varchar(40),hobby varchar(20)); insert into info values(1,'liuyi',80,'beijing',2); insert into info values(2,'wangwu',90,'shengzheng',2); insert into info values(3,'lisi',60,'shanghai',4); insert into info values(4,'tianqi',99,'hangzhou',5); insert into info values(5,'jiaoshou',98,'laowo',3); insert into info values(6,'hanmeimei',10,'nanjing',3); insert into info values(7,'lilei',11,'nanjing',5); insert into info values(8,'caicai',16,'nanjing',5);
① 单字段排序
#按分数排序，默认不指定是升序排列 asc
select id,name,score from info order by score ;
#分数按降序排列，使用DESC
select id,name,score from info order by score desc;
② 条件查询
order by还可以结合where进行条件过滤，筛选地址是杭州的学生按分数降序排列
select name,score from info where address=‘hangzhou’ order by score desc;
③ 多字段排序
原则:
order by之后的参数，使用"，"分割，优先级是按先后顺序而定，例如:
select id,name,hobby from info order by hobby desc,id asc; +------+-----------+--------+ | id | name | hobbid | +------+-----------+--------+ | 4 | tianqi | 5 | | 5 | lilei | 5 | | 3 | lisi | 4 | | 5 | jiaoshou | 3 | | 6 | hanmeimei | 3 | | 1 | liuyi | 2 | | 2 | wangwu | 2 | +------+-----------+--------+
小结: order by 之后的第一个参数只有在出现相同的数值，第二个字段才有意义
(示例: select id, hobby from info order by id asc, hobby desc; )
④ 区间判断及查询不重复记录
AND/OR ——且/或
select * from info where score >70 and score <=90;
select * from info where score >70 or score <=90;
#嵌套/多条件
select * from info where score >70 or (score >75 and score <90);
添加：
② distinct 查询不重复记录
语法:
select distinct 字段 from 表名﹔
select distinct hobby from info;
PS:以下语句是否可以筛重
select name, hobby from info where hobby in (select distinct hobby from info);
1.distinct必须放在最开头
2.distinct只能使用需要去重的字段进行操作。
----也就是说我sidtinct了name , hobby两个字段，我后面想根据id进行排序，是不可以的，因为只能name,age两个字段进行操作．
3.distinct去重多个字段时，含义是:几个字段同时重复时才会被过滤。
2.对结果进行分组
通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现 ，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、 求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。
（1）语法
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;
selet字段，聚合函数from表名，(where字段名（匹配）数值)group by字段名;
示例:
#select count(name),score from info where score>=45 group by score;
对info进行分组，筛选范围/条件是score大于等于45的 ‘name’,score相同的会默认分在一个组
案列
#分组排序:
select count(id),hobby from info group by hobby;
对info表中兴趣(hobby)相同的id进行数量统讦，并按照相同hobby进行分组
select count(id), hobby from info group by hobby order by count(id) desc;
基于上一条操作，结合order by把统计的id数量进行按降序序排列
#分组条件
结合where语句，筛选分数大于等于80的分组，计算学生个数按降序排列
select count(name),score,hobby from info where score>=80 group by hobby order by count(name) desc;
3.限制结果条目（limit）
limit 限制输出的结果记录
在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录（行）。有时候仅 需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句
（1）语法
SELECT column1, column2, … FROM table_name LIMIT [offset,] number
LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。 如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的 位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。
#查询所有信息显示前4行记录
select * from info limit 3;
#从第4行开始，往后显示3行内容
select * from info limit 3,3;
#结合order by语句，按id的大小升序排列显示前三行
select id,name from info order by id limit 3;
#基础select 小的升阶 怎么输出最后三行
select id,name from info order by id desc limit 3;
输出前三行，怎么输出 ： limit 3
limit 2 您说的是前三行，limit 是做为位置偏移量的定义，他的起始是从0开始,而0表示的是字段
4.设置别名（alias ——》as）
在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者 多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性
（1）语法
对于列的别名：SELECT column_name AS alias_name FROM table_name;
对于表的别名：SELECT column_name(s) FROM table_name AS alias_name;
在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段名是不会被改变的
#列别名设置示例：
select name as 姓名,score as 成绩 from info;
#如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名
临时设置info的别名为i
select i.name as 姓名,i.score as 成绩 from info as i ;
#查询info表的字段数量，以number显示
select count(*) as number from info;
#不用as也可以，一样显示
select count(*) number from info;
使用场景：
1、对复杂的表进行查询的时候，别名可以缩短查询语句的长度
2、多表相连查询的时候（通俗易懂、减短sql语句）
此外，AS 还可以作为连接语句的操作符。
创建t1表，将info表的查询记录全部插入t1表
create table t1 as select * from info;
#此处AS起到的作用：
1、创建了一个新表t1 并定义表结构，插入表数据（与info表相同）
2、但是”约束“没有被完全”复制“过来 #但是如果原表设置了主键，那么附表的：default字段会默认设置一个0
相似：
#克隆、复制表结构
create table t1 (select * from info);
#也可以加入where 语句判断
create table test1 as select * from info where score >=60;
在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。
列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。
5、通配符
通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 LIKE(模糊查询) 一起使用的，并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个，分别是：
%：百分号表示零个、一个或多个字符 *
_：下划线表示单个字符 .
#查询名字是c开头的记录
select id,name from info where name like ‘c%’;
#查询名字里是c和i中间有一个字符的记录
select id,name from info where name like ‘c_ic_i’;
#查询名字中间有g的记录
select id,name from info where name like ‘%g%’;
#查询hanmei后面3个字符的名字记录
select id,name from info where name like ‘hanmei___’;
#通配符“%”和“”不仅可以单独使用，也可以组合使用
查询名字以s开头的记录
select id,name from info where name like 's%’;
6、子查询
子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。
PS: 子语句可以与主语句所查询的表相同，也可以是不同表
#相同表示例：
select name,score from info where id in (select id from info where score >80);
以上
主语句：select name,score from info where id
子语句(集合)： select id from info where score >80
PS:子语句中的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件
in: 将主表和子表关联/连接的语法
#不同表/多表示例：
create table ky13 (id int(4));
insert into ky13 values(1),(2),(3);
#多表查询
select id,name,score from info where id in (select * from ky13);
子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。
子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中
#将t1里的记录全部删除，重新插入info表的记录
insert into t1 select * from info where id in (select id from info);
select * from t1;
#UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。
注：创建ky11表的Id
将caicai的分数改为50
update info set score=50 where id in (select * from ky13 where id=2);
select * from info;
#DELETE 也适用于子查询
删除分数大于80的记录
delete from info where id in (select id where score>80);
select id,name,score from t1;
在 IN 前面还可以添加 NOT，其作用与 IN 相反，表示否定（即不在子查询的结果集里面）
删除分数不是大于等于80的记录
delete from t1 where id not in (select id where score>=80);
select id,name,score from t1;
#子查询-exists
EXISTS 这个关键字在子查询时，主要用于判断exists之后的条件是否成立，如果成立，则正常执行主语句的匹配，如不成立，则不会执行主语句查询，
如子查询结果集不成立的话，输出为null
加exists
:只是为了判断exists之后的条件是否成立，如果成立，则正常执行主语句的匹配，如不成立，则不会执行主语句查询
PS: count为计数，sum为求和，使用sum求和结合exists，如子查询结果集不成立的话，输出为null
#案列 查询如果存在分数等于80的记录则计算info的字段数
select count(*) from info where exists(select id from info where score=80);
查询如果存在分数小于50的记录则计算info的字段数，info表没有小于50的，所以返回0
select count(*) from info where exists(select id from info where score<50);
#子查询，别名as
查询info表id,name 字段
select id,name from info;
以上命令可以查看到info表的内容
#将结果集做为一张表进行查询的时候，我们也需要用到别名，示例：
需求：从info表中的id和name字段的内容做为"内容" 输出id的部分
mysql> select id from (select id,name from info);
ERROR 1248 (42000): Every derived table must have its own alias
#此时会报错，原因为：
select * from 表名 此为标准格式，而以上的查询语句，“表名"的位置其实是一个结果集，mysql并不能直接识别，而此时给与结果集设置一个别名，以”select a.id from a“的方式查询将此结果集是为一张"表”，就可以正常查询数据了，如下：
select a.id from (select id,name from info) a;
相当于
select info.id,name from info;
select 表.字段，字段 from 表；
7. 视图
什么是视图
视图是从一个或多个表中导出来的表，是一种虚拟存在的表。
视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。
这样，用户可以不用看到整个数据库中的数据，而之关心对自己有用的数据。
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。
数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了映射
镜花水月/倒影，动态保存结果集（数据）
视图的作用
1.使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件
2.增加数据的安全性，通过视图，用户只能查询和修改指定的数据。
3.提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。
总结：使用视图的大部分情况是为了保障数据安全性，提高查询效率
视图我们可以定义展示的条件
示例：
需求：满足80分的学生展示在视图中
PS：这个结果会动态变化，同时可以给不同的人群（例如权限范围）展示不同的视图
#创建视图
create view v_score as select * from info where score>=80;
show table status\G
#查看视图
select * from v_score;
#修改原表数据
update info set score=‘60’ where name=‘wangwu’;
#查看视图
select * from v_score;
8.NULL 值
在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失 的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使 用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意 的是，NULL值与数字 0 或者空白（spaces）的字段是不同的，值为NULL的字段是没有值的。在SQL语句中，使用 IS NULL 可以判断表内的某个字段是不是NULL值，相反的用IS NOT NULL可以判断不是NULL值。
查询info表结构，id和name字段是不允许空值的
null值与空值的区别(空气与真空)
空值长度为0，不占空间，NULL值的长度为null，占用空间
is null无法判断空值
空值使用"=“或者”<>"来处理（！=）
count（）计算时，NULL会忽略，空值会加入计算
mysql> desc info; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(3) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | UNI | NULL | | | score | decimal(5,2) | YES | | NULL | | | address | varchar(50) | YES | | 未知 | | | hobbid | int(3) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
插入一条记录，分数字段输入null，显示出来就是null
#验证：
alter table info add column addr varchar(50);
update info set addr=‘nj’ where score >=70;
#统计数量：检测null是否会加入统计中
select count(addr) from info;
#将info表中其中一条数据修改为空值’’
update info set addr=’’ where name=‘wangwu’;
#统计数量，检测空值是不会被添加到统计中
select count(addr) from info;
#查询null值
select * from info where addr is null;
空值数据: select count() from YourTable where Your Column Name is null
#查询不为空的值
select * from info where addr is not null;
非空值数据: select count() from YourTable where YourColumnName is not null
9.MySQL高阶语句
9.1正则表达式
MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中 符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达 式的匹配模式，REGEXP 操作符所支持的匹配模式如表所示。
regexp
匹配 描述
1、^ 匹配文本的开始字符
2、$ 匹配文本的结束字符
3、. 匹配任何单个字符
4、* 匹配零个或多个在它前面的字符
5、+ 匹配前面的字符 1 次或多次
6、字符串 匹配包含指定的字符串
7、p1|p2 匹配 p1 或 p2
8、[…] 匹配字符集合中的任意一个字符
9、[^…] 匹配不在括号中的任何字符
10、{n} 匹配前面的字符串 n 次
11、{n,m} 匹配前面的字符串至少 n 次，至多 m 次
#查询以sh开头的学生信息
mysql> select id,name from info where name regexp '^sh'; +----+-----------+ | id | name | +----+-----------+ | 2 | shangzhen | | 1 | shidapeng | +----+-----------+ 2 rows in set (0.01 sec) 查询以n结尾的学生信息 mysql> select id,name from info where name regexp 'n$'; +----+-----------+ | id | name | +----+-----------+ | 11 | nannan | | 2 | shangzhen | | 3 | tangyan | | 10 | zhaobin | | 8 | zhaokun | +----+-----------+ 5 rows in set (0.00 sec) 查询名字中包含an的学生信息 mysql> select id,name from info where name regexp 'an'; +----+-----------+ | id | name | +----+-----------+ | 11 | nannan | | 2 | shangzhen | | 3 | tangyan | +----+-----------+ 3 rows in set (0.00 sec) 查询名字是tangy开头，n结尾，中间不知道是一个什么字符的学生信息 mysql> select id,name from info where name regexp 'tangy.n'; +----+---------+ | id | name | +----+---------+ | 3 | tangyan | +----+---------+ 1 row in set (0.00 sec) 查询名字中包含an或者zh的学生信息 mysql> select id,name from info where name regexp 'an|zh'; +----+-----------+ | id | name | +----+-----------+ | 11 | nannan | | 2 | shangzhen | | 3 | tangyan | | 10 | zhaobin | | 8 | zhaokun | +----+-----------+ 5 rows in set (0.00 sec) 查询名字中有an,g可有可无的学生信息 必须要有的部分是'an' 而'g'可有可无 mysql> select id,name from info where name regexp 'ang*'; +----+-----------+ | id | name | +----+-----------+ | 11 | nannan | | 2 | shangzhen | | 3 | tangyan | +----+-----------+ 3 rows in set (0.01 sec) 查询名字中含有an，g至少出现一次的学生信息 mysql> select id,name from info where name regexp 'ang+'; +----+-----------+ | id | name | +----+-----------+ | 2 | shangzhen | | 3 | tangyan | +----+-----------+ 2 rows in set (0.00 sec) 查询名字以s-x开头的学生信息 mysql> select id,name from info where name regexp '^[s-x]'; +----+-----------+ | id | name | +----+-----------+ | 2 | shangzhen | | 1 | shidapeng | | 3 | tangyan | | 9 | xiawenjie | +----+-----------+ 4 rows in set (0.00 sec) 查询名字不是caicai的学生信息 mysql> select id,name from info where name regexp '[^caicai]'; +----+-----------+ | id | name | +----+-----------+ | 6 | chengu | | 11 | nannan | | 2 | shangzhen | | 1 | shidapeng | | 3 | tangyan | | 9 | xiawenjie | | 10 | zhaobin | | 8 | zhaokun | +----+-----------+ 8 rows in set (0.00 sec) 查询学生名字不以czx各字母开头的学生信息 mysql> select id,name from info where name regexp '^[^czx]'; +----+-----------+ | id | name | +----+-----------+ | 11 | nannan | | 2 | shangzhen | | 1 | shidapeng | | 3 | tangyan | +----+-----------+ 4 rows in set (0.00 sec)
9.2运算符
MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别 是：算术运算符、比较运算符、逻辑运算符和位运算符
9.2.1算术运算符
以 SELECT 命令来实现最基础的加减乘除运算，MySQL 支持使用的算术运算符，如表所示：
运算符 描述 + 加法 - 减法 * 乘法 / 除法 % 取余
在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需 要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序。
mysql> select 1+2,2-1,3*4,4/2,5%2; +-----+-----+-----+--------+------+ | 1+2 | 2-1 | 3*4 | 4/2 | 5%2 | +-----+-----+-----+--------+------+ | 3 | 1 | 12 | 2.0000 | 1 | +-----+-----+-----+--------+------+ 1 row in set (0.00 sec) mysql> create table mt as select 1+2,2-1,3*2,4/2,5%2; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from mt; +-----+-----+-----+--------+------+ | 1+2 | 2-1 | 3*2 | 4/2 | 5%2 | +-----+-----+-----+--------+------+ | 3 | 1 | 6 | 2.0000 | 1 | +-----+-----+-----+--------+------+ 1 row in set (0.00 sec) mysql> desc mt; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | 1+2 | int(3) | NO | | 0 | | | 2-1 | int(3) | NO | | 0 | | | 3*2 | int(3) | NO | | 0 | | | 4/2 | decimal(5,4) | YES | | NULL | | | 5%2 | int(1) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
9.2.2比较运算符
比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出 表中有哪些记录是符合条件的，如果比较的结果(以布尔值的方式进行返回判断)为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过binary关键字来实现
运算符 描述 = 等于 > 大于 < 小于 >= 大于等于 <= 小于等于 !=或<> 不等于 is null 判断一个值是否为 NULL IS NOT NULL 判断一个值是否不为 NULL BETWEEN AND 两者之间 IN 在集合中 LIKE 通配符匹配 GREATEST 两个或多个参数时返回最大值 LEAST 两个或多个参数时返回最小值 REGEXP 正则表达式
##等号（=） 是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1(true)，如果不相等则返回 0（flase)。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同
例如字符串（字母）比较：（‘a’>‘b’）其实比较的就是底层的ASCII码
需要关注的是ascii码有：a、A、0（97、65、48）
那么：
如果比较的是多字符串，如:‘abc’=‘acb’,如何比较（字符个数、字符顺序）
如果比较的是多字符，如：‘abc’ <‘baa’ 如何比较
与linux返回值表达相反，linux 中运行正常返回值是0，运行异常返回值是非0
mysql> select 2=4,2=2,2='2','e'='e','r'=null; +-----+-----+-------+---------+----------+ | 2=4 | 2=2 | 2='2' | 'e'='e' | 'r'=null | +-----+-----+-------+---------+----------+ | 0 | 1 | 1 | 1 | NULL | +-----+-----+-------+---------+----------+ 1 row in set (0.00 sec)
① 如果两者都是整数，则按照整数值进行比较。
②如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。（在程序中，一般是不会吧这两者进行相比较的）
③ 如果两者都是字符串，则按照字符串进行比较。
④ 如果两者中至少有一个值是 NULL，则比较的结果是 NULL。
##不等于（<>,!=）
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。 如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL
mysql> select 'abc'<>'cba',2<>2,3!=2,null<>null; +--------------+------+------+------------+ | 'abc'<>'cba' | 2<>2 | 3!=2 | null<>null | +--------------+------+------+------------+ | 1 | 0 | 1 | NULL | +--------------+------+------+------------+ 1 row in set (0.00 sec)
① 大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回0，同样不能用于判断NULL。
② 小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回0，同样不能用于判断NULL。
③ 大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断NULL。
④ 小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL
mysql> select 5>4,3<4,'a'<'b',4.4<5,'u'>=null; +-----+-----+---------+-------+-----------+ | 5>4 | 3<4 | 'a'<'b' | 4.4<5 | 'u'>=null | +-----+-----+---------+-------+-----------+ | 1 | 1 | 1 | 1 | NULL | +-----+-----+---------+-------+-----------+ 1 row in set (0.00 sec)
判断一个值为/不为null ( IS NULL、IS NOT NULL )
#IS NULL判断一个值是否为 NULL，如果为NULL返回1，否则返回0。
#IS NOT NOLL判断一个值是否不为 NULL，如果不为NULL返回1，否则返回 0。示例:
select 2 IS NULL,‘f’ IS NOT NULL,NULL IS NULL;
mysql> select 2 is null,'a' is not null,null is null; +-----------+-----------------+--------------+ | 2 is null | 'a' is not null | null is null | +-----------+-----------------+--------------+ | 0 | 1 | 1 | +-----------+-----------------+--------------+ 1 row in set (0.00 sec)
BETWEEN AND
比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间，具体操作，条件符合返回1，否则返回0
示例:
mysql> select 4 between 2 and 5,'c' between 'a' and 'b'; +-------------------+-------------------------+ | 4 between 2 and 5 | 'c' between 'a' and 'b' | +-------------------+-------------------------+ | 1 | 0 | +-------------------+-------------------------+ 1 row in set (0.00 sec)
Between and 覆盖的范围是>= 和 <=关系
###least 和greatest（取最小值、取最大值）
LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。
GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为NULL， 则返回结果就为 NULL。
若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和 GREATEST 来实现
mysql> select least(1,2,3),greatest(1,2,3),least('a','b','c'),greatest('a','b','c');
+--------------+-----------------+--------------------+-----------------------+
| least(1,2,3) | greatest(1,2,3) | least('a','b','c') | greatest('a','b','c') |
+--------------+-----------------+--------------------+-----------------------+
| 1 | 3 | a | c |
+--------------+-----------------+--------------------+-----------------------+
1 row in set (0.01 sec)
IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0
mysql> select 1 in (1,2,3), 2 not in ('a','b','c');
+--------------+------------------------+
| 1 in (1,2,3) | 2 not in ('a','b','c') |
+--------------+------------------------+
| 1 | 1 |
+--------------+------------------------+
1 row in set, 3 warnings (0.00 sec)
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0
LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符（*匹配的是前面一个字符），而
’_’只能匹配一个字符。
NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0。
mysql> select 'abc' like 'ab%','abc' like '_bc','abc' not like 'a%'; +------------------+------------------+---------------------+ | 'abc' like 'ab%' | 'abc' like '_bc' | 'abc' not like 'a%' | +------------------+------------------+---------------------+ | 1 | 1 | 0 | +------------------+------------------+---------------------+ 1 row in set (0.00 sec)
9.2.3逻辑运算符(布尔值)
逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则 返回 0，真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种， 具体如表所示。
运算符 描述 not 或 ! 逻辑非 and 或 && 逻辑与 or 逻辑或 xor 逻辑异或
（1）逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL
mysql> select not 2,!3,not 0, ! null; +-------+----+-------+--------+ | not 2 | !3 | not 0 | ! null | +-------+----+-------+--------+ | 0 | 0 | 1 | NULL | +-------+----+-------+--------+ 1 row in set (0.00 sec)
（2）逻辑与(and)
当所有的操作数都为非0值且不为null时，返回值为1，否则为0
（null与0比较特殊）
逻辑与使用 AND 或者&&表示
mysql> select 2 and 3,4 && 0,4 && null,0 and null,null and null; +---------+--------+-----------+------------+---------------+ | 2 and 3 | 4 && 0 | 4 && null | 0 and null | null and null | +---------+--------+-----------+------------+---------------+ | 1 | 0 | NULL | 0 | NULL | +---------+--------+-----------+------------+---------------+ 1 row in set (0.00 sec)
由结果可看出
and 和&& 的作用相同
1 and -1 没有0 或 null,所以返回值为1
1 and 0 中由有0，所以返回值为0
1 and null 有Null ，所以返回值为null
null and 0 返回值为0
（3）逻辑或（or）
逻辑或通常使用 OR
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回 0。
当有一个操作数为null时，如果另一个操作数为非0值，则返回值为1，否则为null
如两个操作数均为null，则返回值为null
使用或运算符or 进行逻辑判断，运行结果如果如下：
mysql> select 2 or 3,2 or 0, 2 or null, 0 or 0,null or null,0 or null; +--------+--------+-----------+--------+--------------+-----------+ | 2 or 3 | 2 or 0 | 2 or null | 0 or 0 | null or null | 0 or null | +--------+--------+-----------+--------+--------------+-----------+ | 1 | 1 | 1 | 0 | NULL | NULL | +--------+--------+-----------+--------+--------------+-----------+ 1 row in set (0.00 sec)
mysql> select 0 or null or 2; +----------------+ | 0 or null or 2 | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec)
小结:
1 OR -1 OR 0含有0，但同时包含有非0的值1和-1，所以返回结果为1;
1 OR 2 中没有操作数0，所以返回结果为1;
1 OR NULL虽然有 NULL，但是有操作数1，所以返回结果为1;
0 OR NULL中没有非0值，并且有NULL，所以返回值为NULL;
NULL OR NULL中只有NULL，所以返回值为NULL。
（4）逻辑异或（xor）
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；
如果一个为 0， 另一个为非 0，则返回结果为 1；
当任意一个值为 NULL 时，返回值为 NULL
示例：
mysql> select 2 xor 3,1 xor 0,0 xor null,null xor null; +---------+---------+------------+---------------+ | 2 xor 3 | 1 xor 0 | 0 xor null | null xor null | +---------+---------+------------+---------------+ | 0 | 1 | NULL | NULL | +---------+---------+------------+---------------+ 1 row in set (0.00 sec)
9.2.4位运算符
位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式（1010 1111），然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL 支持 6 种位运算符，具体如表所示。
位运算符 描述 & 按位与 | 按位或 ~ 按位取反 ^ 按位异或 << 按位左移 >> 按位右移
mysql> select 10 & 15,10 | 15,10 ^ 15,5 &~1; +---------+---------+---------+-------+ | 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 | +---------+---------+---------+-------+ | 10 | 15 | 5 | 4 | +---------+---------+---------+-------+ 1 row in set (0.00 sec)
10 转换为二进制数是 1010, 15 转换为二进制数是 1111
按位与运算（&），是对应的二进制位都是1的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10。
按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0， 所以 10 | 15 的结果为 15。
按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15 的结果为 5。
按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101
进行求与操作，其结果是二进制的 0100，转换为十进制就是 4
以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算，优先级如下表所示：
优先级 运算符 1 ！ 2 ~ 3 ^ 4 *、/、% 5 +,- 6 >>,<< 7 & 8 | 9 =,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN 10 BETWEEN,CASE,WHEN,THEN,ELSE 11 NOT 12 &&,AND 13 ||,OR,XOR 14 :=
10.数据库函数
10.1数学函数
数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。 常用的数学函数如表所示
###把mysql 中的表导出为excel格式
数学函数 描述 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…) 返回集合中最小的值
示例:
-2的绝对值 返回 x 的绝对值
mysql> select abs(-2); +---------+ | abs(-2) | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) 0-1的随机数（0<=x<1） 返回 0 到 1 的随机数 mysql> select rand(); +-------------------+ | rand() | +-------------------+ | 0.403306520919601 | +-------------------+ 1 row in set (0.00 sec) 可以搭配运算符 mysql> select rand()*100; +-------------------+ | rand()*100 | +-------------------+ | 73.72450341817411 | +-------------------+ 1 row in set (0.00 sec) 5除以2的余数 返回 x 除以 y 以后的余数 mysql> select mod(5,2); +----------+ | mod(5,2) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) 2的3次方 mysql> select power(2,3); +------------+ | power(2,3) | +------------+ | 8 | +------------+ 1 row in set (0.00 sec) 离1.89最近的整数 mysql> select round(1.49); +-------------+ | round(1.49) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select round(1.5); +------------+ | round(1.5) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec) 1.893保留小数点后2位，1.896保留小数点后2位，这里会四舍五入 mysql> select round(1.893,2); +----------------+ | round(1.893,2) | +----------------+ | 1.89 | +----------------+ 1 row in set (0.00 sec) mysql> select round(1.896,2); +----------------+ | round(1.896,2) | +----------------+ | 1.90 | +----------------+ 1 row in set (0.00 sec) 返回平方根 mysql> select sqrt(4); +---------+ | sqrt(4) | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) mysql> select sqrt(5); +------------------+ | sqrt(5) | +------------------+ | 2.23606797749979 | +------------------+ 1 row in set (0.00 sec) 保留小数点后2位，但truncate函数不会四舍五入（截断） mysql> select truncate(1.896,2); +-------------------+ | truncate(1.896,2) | +-------------------+ | 1.89 | +-------------------+ 1 row in set (0.00 sec) 返回大于或等于5.2的最小整数 mysql> select ceil(5.2); +-----------+ | ceil(5.2) | +-----------+ | 6 | +-----------+ 1 row in set (0.01 sec) 返回小于或等于5.2的最大整数 mysql> select floor(5.2); +------------+ | floor(5.2) | +------------+ | 5 | +------------+ 1 row in set (0.00 sec) 返回最大值 mysql> select greatest(1,2,3); +-----------------+ | greatest(1,2,3) | +-----------------+ | 3 | +-----------------+ 1 row in set (0.00 sec) 返回最小值 mysql> select least(1,2,3); +--------------+ | least(1,2,3) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
10.2聚合函数
MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。
聚合函数 描述
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum(x) 返回指定列的所有值之和
返回分数的总和
mysql> select sum(score) from info; +------------+ | sum(score) | +------------+ | 662.00 | +------------+ 1 row in set (0.00 sec) 返回分数字段的个数 mysql> select count(score) from info; +--------------+ | count(score) | +--------------+ | 8 | +--------------+ 1 row in set (0.00 sec) 返回分数的最小值 mysql> select min(score) from info; +------------+ | min(score) | +------------+ | 70.00 | +------------+ 1 row in set (0.00 sec) 返回分数的最大值 mysql> select max(score) from info; +------------+ | max(score) | +------------+ | 98.00 | +------------+ 1 row in set (0.00 sec) 返回分数的平均值 mysql> select avg(score) from info; +------------+ | avg(score) | +------------+ | 82.750000 | +------------+ 1 row in set (0.00 sec)
10.3字符串函数
字符串函数 描述
length(x) 返回字符串 x 的长度
trim() 返回去除指定格式的值
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
upper(x) 将字符串 x 的所有字母变成大写字母
lower(x) 将字符串 x 的所有字母变成小写字母
left(x,y) 返回字符串 x 的前 y 个字符
right(x,y) 返回字符串 x 的后 y 个字符
repeat(x,y) 将字符串 x 重复 y 次
space(x) 返回 x 个空格
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长 度为 z 的字符串
reverse(x) 将字符串 x 反转
###length(x) 返回字符串 x 的长度
返回abcd的长度，空格也算一个字符
mysql> select length('abcd');
+----------------+
| length('abcd') |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> select length('ab cd');
+-----------------+
| length('ab cd') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.01 sec)
###trim() 返回去除格式的值
mysql> select trim(' sheng');
+------------------+
| trim(' sheng') |
+------------------+
| sheng |
+------------------+
1 row in set (0.00 sec)
mysql> select ' sheng';
+----------+
| sheng |
+----------+
| sheng |
+----------+
1 row in set (0.00 sec)
###concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
mysql> select concat('abc','def');
+---------------------+
| concat('abc','def') |
+---------------------+
| abcdef |
+---------------------+
1 row in set (0.01 sec)
mysql> select concat('abc',' def');
+----------------------+
| concat('abc',' def') |
+----------------------+
| abc def |
+----------------------+
1 row in set (0.00 sec)
还可以结合其他函数，如trim（将后面的函数删除格式）
mysql> select concat('abc',trim(' def'));
+----------------------------+
| concat('abc',trim(' def')) |
+----------------------------+
| abcdef |
+----------------------------+
1 row in set (0.00 sec)
###upper(x) 将字符串 x 的所有字母变成大写字母
mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)
###lower(x) 将字符串 x 的所有字母变成小写字母
mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc |
+--------------+
1 row in set (0.01 sec)
###left(x,y) 返回字符串 x 的前 y 个字符
mysql> select left('abcdefg',3);
+-------------------+
| left('abcdefg',3) |
+-------------------+
| abc |
+-------------------+
1 row in set (0.00 sec)
##right(x,y) 返回字符串 x 的后 y 个字符
mysql> select right('abcdefg',3);
+--------------------+
| right('abcdefg',3) |
+--------------------+
| efg |
+--------------------+
1 row in set (0.00 sec)
把字符串的前3个字母和后3个字母拼接起来
mysql> select concat(left('abcdefg',3),right('abcdefg',3));
+----------------------------------------------+
| concat(left('abcdefg',3),right('abcdefg',3)) |
+----------------------------------------------+
| abcefg |
+----------------------------------------------+
1 row in set (0.00 sec)
###repeat(x,y) 将字符串 x 重复 y 次
mysql> select repeat('abc',2);
+-----------------+
| repeat('abc',2) |
+-----------------+
| abcabc |
+-----------------+
1 row in set (0.00 sec)
###space(x) 返回 x 个空格
mysql> select length(space(3)); +------------------+ | length(space(3)) | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec)
###replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
mysql> select replace('hello','ll','aa');
+----------------------------+
| replace('hello','ll','aa') |
+----------------------------+
| heaao |
+----------------------------+
1 row in set (0.00 sec)
###strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
比较17和18，小于返回-1，等于返回0，大于返回1，只会返回这3个值，它是比较第一位不同的数字
mysql> select strcmp(17,18); +---------------+ | strcmp(17,18) | +---------------+ | -1 | +---------------+ 1 row in set (0.00 sec) mysql> select strcmp(18,18); +---------------+ | strcmp(18,18) | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> select strcmp(19,18); +---------------+ | strcmp(19,18) | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) mysql> select strcmp(17,1); +--------------+ | strcmp(17,1) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
###substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
返回从字符串中第三个字符开始的4个字符
mysql> select substring('abcdefg',3,4);
+--------------------------+
| substring('abcdefg',3,4) |
+--------------------------+
| cdef |
+--------------------------+
1 row in set (0.00 sec)
##reverse(x) 将字符串 x 反转
mysql> select reverse('gfedcba');
+--------------------+
| reverse('gfedcba') |
+--------------------+
| abcdefg |
+--------------------+
1 row in set (0.00 sec)
返回字符串的前3个字符，然后反转输出
mysql> select reverse(left('gfedcba',3));
+----------------------------+
| reverse(left('gfedcba',3)) |
+----------------------------+
| efg |
+----------------------------+
1 row in set (0.00 sec)
先将字符串反转，再输出前3个字符
mysql> select left(reverse('gfedcba'),3);
+----------------------------+
| left(reverse('gfedcba'),3) |
+----------------------------+
| abc |
+----------------------------+
1 row in set (0.00 sec)
10.4日期时间函数
字符串函数 描述 curdate() 返回当前时间的年月日 curtime() 返回当前时间的时分秒 now() 返回当前时间的日期和时间 month(x) 返回日期 x 中的月份值 week(x) 返回日期 x 是年度第几个星期 hour(x) 返回 x 中的小时值 minute(x) 返回 x 中的分钟值 second(x) 返回 x 中的秒钟值 dayofweek(x) 返回 x 是星期几，1 星期日，2 星期一 dayofmonth(x) 计算日期 x 是本月的第几天 dayofyear(x) 计算日期 x 是本年的第几天 返回年月日
mysql> select curdate(); +------------+ | curdate() | +------------+ | 2020-08-25 | +------------+ 1 row in set (0.00 sec)
返回当前时间
mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 15:57:33 | +-----------+ 1 row in set (0.00 sec)
返回当前完整时间
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-08-25 15:57:45 | +---------------------+ 1 row in set (0.00 sec)
返回月份
mysql> select month('2020-08-25');
+---------------------+
| month('2020-08-25') |
+---------------------+
| 8 |
+---------------------+
1 row in set (0.00 sec)
返回当前日期是一年中的第几周
mysql> select week('2020-08-25');
+--------------------+
| week('2020-08-25') |
+--------------------+
| 34 |
+--------------------+
1 row in set (0.00 sec)
返回当前时间的小时
mysql> select hour(curtime()); +-----------------+ | hour(curtime()) | +-----------------+ | 16 | +-----------------+ 1 row in set (0.00 sec)
返回当前时间的分钟
mysql> select minute(curtime()); +-------------------+ | minute(curtime()) | +-------------------+ | 3 | +-------------------+ 1 row in set (0.00 sec)
返回当前时间的秒
mysql> select second(curtime()); +-------------------+ | second(curtime()) | +-------------------+ | 56 | +-------------------+ 1 row in set (0.00 sec)
当前是星期几
mysql> select dayofweek(curdate()); +----------------------+ | dayofweek(curdate()) | +----------------------+ | 3 | +----------------------+ 1 row in set (0.00 sec)
当前日期是本月的第几天
mysql> select dayofmonth(curdate()); +-----------------------+ | dayofmonth(curdate()) | +-----------------------+ | 25 | +-----------------------+ 1 row in set (0.00 sec)
当前日期是今年的第几天
mysql> select dayofyear(curdate()); +----------------------+ | dayofyear(curdate()) | +----------------------+ | 238 | +----------------------+ 1 row in set (0.00 sec)
11.存储过程
1、概述
前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求，有点类似shell脚本里的函数
2、简介
1、存储过程是一组为了完成特定功能的SQL语句集合。
2、存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高
开发人员
存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。
3、存储过程的优点：
（1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
（2）SQL语句加上控制语句的集合，灵活性高
（3）在服务器端存储，客户端调用时，降低网络负载
（4）可多次重复被调用，可随时修改，不影响客户端调用
（5）可完成所有的数据库操作，也可控制数据库的信息访问权限
语法：
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>
示例（不带参数的创建）
##创建存储过程##
DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 8: #̲将语句的结束符号从分号;临时改…(可以自定义)
CREATE PROCEDURE Proc() #创建存储过程，过程名为Proc，不带参数
-> BEGIN #过程体以关键字 BEGIN 开始
-> create table mk (id int (10), name char(10),score int (10));
-> insert into mk values (1, ‘wang’,13);
-> select * from mk; #过程体语句
-> END $$ #过程体以关键字 END 结束
DELIMITER ; #将语句的结束符号恢复为分号
##调用存储过程##
CALL Proc();
I 存储过程的主体都分,被称为过程体
II 以BEGIN开始，以END结束，若只有一条sQL语句，则可以省略BEGIN-END
III 以DELIMITER开始和结束
mysgl>DEL工M工TER
是用户自定义的结束符
省略存储过程其他步骤
mysql>DELIMITER ; 分号前有空格
##查看存储过程##
格式：
SHOW CREATE PROCEDURE [数据库.]存储过程名; #查看某个存储过程的具体信息
mysql> show create procedure proc\G
*************************** 1. row ***************************
Procedure: proc
sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_D_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER="root"@"localhost" PROCEDURE "proc"()
begin
select id,name from info;
update info set score='10' where name='tiqnai';
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
#查看存储过程
SHOW PROCEDURE STATUS
#查看指定存储过程信息
mysql> SHOW PROCEDURE STATUS like '%proc%'\G
*************************** 1. row ***************************
Db: info
Name: proc
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-07-15 05:45:21
Created: 2021-07-15 05:45:21
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 sec)
##存储过程的参数##
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
即表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量)
举例：
mysql> delimiter @@
mysql> create procedure proc (in inname varchar(40)) #行参
-> begin
-> select * from info where name=inname;
-> end @@
mysql> delimiter @@
mysql> call proc2('wangwu'); #实参
+--------+-------+---------+
| name | score | address |
+--------+-------+---------+
| wangwu | 80.00 | beijing |
+--------+-------+---------+
1 row in set (0.00 sec)
#修改存储过程
ALTER PROCEDURE <过程名>[<特征>… ]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
MODIFIES sQLDATA:表明子程序包含写数据的语句
SECURITY:安全等级
invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行。
##删除存储过程##
存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。
DROP PROCEDURE IF EXISTS Proc;
总结
本章介绍了如何用select语句的where子句过滤返回的数据。我们学习了如何对相等、不相等、大于、小于、值的范围以及null值等进行测试。通配符以及如何在where子句中使用SQL通配符，并且还说明了通配符应该细心使用，不要过度使用。
这篇关于MySQL高级SQL语句的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-16MySQL资料：新手入门教程
- 2024-11-16MySQL资料：新手入门教程
- 2024-11-15MySQL教程：初学者必备的MySQL数据库入门指南
- 2024-11-15MySQL教程：初学者必看的MySQL入门指南
- 2024-11-04部署MySQL集群项目实战：新手入门教程
- 2024-11-04如何部署MySQL集群资料：新手入门指南
- 2024-11-02MySQL集群项目实战：新手入门指南
- 2024-11-02初学者指南：部署MySQL集群资料
- 2024-11-01部署MySQL集群教程：新手入门指南
- 2024-11-01如何部署MySQL集群：新手入门教程
