MySQL高级语句

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

---

一、SQL高级语句

1.导入文件至数据库

#将脚本导入  source  加文件路径
mysql> source /backup/test.hu;

2. select

显示表格中的一个或者多个字段中所有的信息

#语法：
select 字段名  from 表名;

mysql> select * from hu1;
+------+--------+-------+
| id   | name   | money |
+------+--------+-------+
|    1 | 张三   |  3000 |
|    2 | 李四   |  4000 |
+------+--------+-------+
2 rows in set (0.00 sec)

mysql> select name from hu1;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
+--------+
2 rows in set (0.00 sec)

3. distinct

查询不重复记录

#语法:
select distinct 字段 from 表名﹔

#示例1：去除年龄字段中重复的
select distinct age from students;

#示例2：查找性别
select distinct gender from students;

4.where

where 有条件的查询

#语法：
select '字段' from 表名  where 条件

5.and;or

and 且 ； or 或

#语法：
select 字段名  from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;

6.in

显示已知值的资料

#语法：
select 字段名  from 表名 where 字段 in ('值1'，'值2'....);

7.between

显示两个值范围内的资料

#语法：
select 字段名  from 表名 where 字段 between  '值1' and '值2';
包括 and两边的值

8.like 通配符

通配符通常是和 like 一起使用

#语法：
select 字段名  from 表名 where 字段 like 模式

通配符	含义
%	表示零个，一个或者多个字符
_	下划线表示单个字符
A_Z	所有以A开头 Z 结尾的字符串 ‘ABZ’ ‘ACZ’ 'ACCCCZ’不在范围内 下划线只表示一个字符 AZ 包含a空格z
ABC%	所有以ABC开头的字符串 ABCD ABCABC
%CBA	所有以CBA结尾的字符串 WCBA CBACBA
%AN%	所有包含AN的字符串 los angeles
_AN%	所有 第二个字母为 A 第三个字母 为N 的字符串

9. order by

order by 按关键字排序

#语法：
select 字段名  from 表名 where 条件 order by 字段 [asc,desc];
asc ：正向排序
desc ：反向排序
默认是正向排序

10.函数

10-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…)	返回返回集合中最小的值

10-2 聚合函数

函数	含义
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

10-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 个字符
repeat(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(起头及结尾)
 
#区分大小写
要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串，缺省时为空格。

2）length

#语法：
select length（字段） from 表名；

3）replace

#语法：
select replace(字段，'原字符''替换字符') from 表名；
select replace(name,'ua','hh') from students;

4）concat

#语法：
select concat(字段1，字段2）from 表名

4）substr

#语法：
select substr(字段，开始截取字符，截取的长度)  where 字段='截取的字符串' 

11.group by
对group by 后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的

group by 有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by 的后面。

#语法：
select 字段1，sum(字段2) from 表名 group by 字段1；

12.having

having：用来过滤由group by语句返回的记录集，通常与group by语句联合使用

having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。

要根据新表中的字段，来指定条件

#语法:
SELECT 字段1，SUM("字段")FROM 表格名 GROUP BY 字段1 having（函数条件);

13.别名

栏位別名 表格別名

#语法：
SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";

14.连接查询

1）inner join(等值相连)
只返回两个表中联结字段相等的行

SELECT * FROM students A INNER JOIN scores B on A.stuid = B.stuid;

2）left join(左联接)
返回包括左表中的所有记录和右表中联结字段相等的记录

select * from scores A left join students B on A.stuid = B.stuid;

3）right join(右联接)
select * from scores A right join students B on A.stuid = B.stuid;

15.子查询

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

语法：
SELECT "栏位1" FROM "表格1" WHERE "栏位2"	[比较运算符]			
#外查询
(SELECT "栏位1" FROM "表格1" WHERE "条件");	

16.EXISTS

用来测试内查询有没有产生任何结果类似布尔值是否为真

如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。

#语法：
SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

二、 CREATE VIEW（视图）

可以被当作是虚拟表或存储查询

（1）视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。

（2）临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

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

#语法：
CREATE VIEW "视图表名" AS "SELECT 语句";
#示例1：创建视图表，表名为new_students，数据是跟在as后面的select语句，scores表的别名为a，students表的别名为b；将a表和b表等值连接，连接字段为stuid，将a表的score和stuid列显示到新表中为score和stuid；将b表中的name列显示到新表为name
create view new_students as select a.score score,a.stuid stuid,b.name name from scores a inner join students b on a.stuid=b.stuid;
 
#删除视图
drop view new_students;

三、联集

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类

3-1UNION

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

#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];

#示例：搜索两个表中courses列的所有记录，先安装原有顺序列出第一个select语句中的数据，再将第二个表中的数据对照第一个表的数据去重后按源于的顺序列出
select courseid from coc union select courseid from courses;

3-2UNION ALL

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

#语法：
[SELECT 语句 1] UNION ALL [SELECT 语句 2];

四、交集值

取两个SQL语句结果的交集

select a.place_name from destination a inner join info b on a.place_name = b.place_name;
select a.courseid from courses a inner join coc b using(courseid);

两表没用重复的行，并且确实有交集的时候用

select a.city from( select city from food union all select city from info) a group by a.city having count(*) > 1;

取两个SQL语句结果的交集，且没有重复

 select a.city from (select b.city from food b inner join info c on b.city=c.city) a group by a.city;
select distinct a.city from food a inner join info b using(city);
select distinct city from food where (city) in (select city from info);
select distinct a.city from food a left join info b using(city) where b.city is not null;

五、无交集值

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

select distinct city from food where (city) not in (select city from info);
select distinct a.city from food a left join info b using(ccity) where b.city is null;

六、CASE

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

#语法：
SELECT CASE ("栏位名")
  WHEN "条件1" THEN "结果1"
  WHEN "条件2" THEN "结果2"
  ...
  [ELSE "结果N"]
  END
FROM "表名";
 
#"条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

七、表格内容排序

#新建一个表格

create database test1;
use  test1;
insert into class values('小明',88);
insert into class values('小红',76);
insert into class values('小斌',90);
insert into class values('小云',64);
insert into class values('小芳',73);
insert into class values('小龙',52);
insert into class values('小伟',99);
 
select * from class;

7-1 算排名

表格自我连结 (Self Join)，然后将结果依序列出，算出每一行之前 (包含那一行本身) 有多少行数

select a1.name,a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name = a2.name) group by a1.name,a1.scores order by a1.scores desc;

7-2 算中位数

select scores middle from ( select a1.name,a1.scores,count(a2.scores) rank from class a1,class a2a1.scores < a2.scores or (a1.scores = a2.scores and a1.name <= a2.name) group by a1.name ,a1.scores order by a1.scores desc) a3 where a3.rank = (select (count(*)+1) div 2 from class);

7-3 算累积总计

select a1.*,sum(a2.scores) sum_scores from class a1,class a2 where a1.scores < a2.scores or(a1.scores = a2.scores and a1.name = a2.name) group by a1.name order by a1.scores desc;

7-4 算总合百分比

#SELECT SUM(Sales) FROM Total_Sales 这一段子查询是用来算出总合
#总合算出后，我们就能够将每一行一一除以总合来求出每一行的总合百分比
select a1.*,a1.scores/(select sum(scores) from class) per_sum from class a1,class a2 where a1.sco2.scores or(a1.scores = a2.scores and a1.name = a2.name) group by a1.name order by a1.scores desc;

取小数点后几位数

select a1.name,a1.scores,sum(a2.scores),truncate(sum(a2.scores)/(select sum(scores) from class),2) ||'%' per_sum from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name=a2.name) group by a1.name order by a1.scores desc;

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

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

（2）IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。

（3）无值的判断使用=’‘或者<>’'来处理。<> 代表不等于。

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

#建立一个表

create table info(name char(100));
insert into info values('小明');
insert into info values('小红');
insert into info values(null);
insert into info values('小云');
insert into info values('');
insert into info values(null);
insert into info values('小伟');

九、正则表达式

匹配模式	描述	实例
^	匹配文本的开始字符 '^bd’匹配以bd开头的字符串
$	匹配文本的结束字符 'qn$'匹配以gn结尾的字符串
.	匹配任何单个字符 's.t’匹配任何s和t之间有—个字符的字符串
*	匹配零个或多个在它前而的字符 'fo*t’匹配t前而有任意个。
+	匹配前面的字符1次或多次 'hom+'匹配以ho开头，后而至少一个m的字符串.
字符串	匹配包含指定的字符串 'clo’匹配含有clo的字符串
p1lp2	匹配p1或p2 'bg	fg’匹配bg或者fg
[…]	匹配字符集合中的任意一个字符 '[abc]'匹配a或者b或者c
[^…]	匹配不在括号中的任何字符 "[^ab]"匹配不包含a或者b的字符串
{n}	匹配前面的字符串n次 'g{2}'匹配含有2个g的字符串
{n,m}	匹配前而的字符串至少n次，至多m次 "f{1,3}'匹配f最少1次，最多3次

#语法:
SELECT "栏位”FROM "表名" WHERE "栏位”REGEXP {模式};

十、日期时间函数

字符串函数	描述
curdate()	返回当前时间的年月旦
curtime()	返回当前时间的时分秒
now()	返回当前时间的日期和时间
month(x)	返回日期×中的月份值
week(x)	返回日期×是年度第几个星期
hour(x) 返回×中的小时值
minute(x)	返回×中的分钟值
second(x)	返回×中的秒钟值
dayofweek(x)	返回×是星期几，1星期日，2星期一
fdayofmonth(x)	计算日期×是本月的第几天
dayofyear(x)	计算日期×是本年的第几天

十一、存储过程

存储过程是组为了完成特定功能的SQL语句集合。

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

11-1 存储过程的优点

封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码

可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

提高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能

提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

使数据独立

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

10-2 创建、调用和查看存储的过程

1.创建存储过程

#语法：
CREATE PROCEDURE <存储过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

1. 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。 需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2. 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

3. 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束 在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter $ $

语法说明如下：
$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??
mysql > DELIMITER ??
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之间一定要有一个空格
示例：

delimiter $$					#将语句的结束符号从分号;临时改为两个$$ (可以是自定义)
create procedure proc5()        #创建存储过程，过程名为proc， 不带参数
-> begin                        #过程体以关键字BEGIN开始
-> select * from students;      #过程体语句（自己根据需求进行编写）
-> end $$						#过程体以关键字END结束
delimiter ;                     #将语句的结束符号恢复为分号

2.调用存储过程

#语法
call 过程名

3.查看存储过程

#查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE [数据库.] 存储过程名;

4.删除存储过程

#语法
DROP PROCEDURE IF EXISTS 过程名;

10-3 存储过程的参数

IN输入参数: 表示调用者向过程传入值(传入值可以是字面量或变量)

OUT输出参数: 表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

INOUT输入输出参数: 既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)

10-4 存储过程的控制语句

10-4-1 条件控制语句 if-then-else … end if

delimiter $$ 										#修改默认结束符为$$
create procedure proc12(in pro int)					#创建存储过程proc2，参数为pro，类型为int
-> begin											#过程体以关键字begin开始
-> declare var int;									#定义变量var为int类型
-> set var=pro*3;									#设置变量var等于传入参数的3倍
-> if var>=10 then									#如果变量var大于10，则执行下面过程体
-> update students set age=age+10;				    #设置表students中的age加10
-> else												#如果变量var不大于10，则执行下面过程体
-> update students set age=age-10;					#设置表students中的age减10
-> end if;											#结束if语句
-> end $$   										#创建存储过程结束
delimiter ; 										#重新修改默认结束符为原始的;
CALL proc12(4);										#调用proc2存储过程，并传入参数4

10-4-2 循环语句 while … end while

#创建表
create table nametest(id int auto_increment primary key,name char(40),age int default 20);
							
DELIMITER $$											#修改默认结束符为$$
-> create procedure yxp() 							#创建存储过程yxp
-> begin												#过程体以关键字begin开始
-> declare i int; 								#定义变量i为int类型（长度最大为10）
-> set i = 1; 											#设置i = 1;
->  while i <= 100									#使用while循环，i要小于100
-> do insert into nametest(name,age) values
 			#满足条件则进行添加数据，内容为变量i
-> set i=i+1;										#变量i每次循环后加1 							
-> end while; 											#结束while循环
-> end $$												#创建存储过程结束
delimiter ;												#重新修改默认结束符为原始的;
CALL proc6;												#调用yxp存储过程