MySQL高级SQL语句

准备表

mysql -uroot -p
show databases;
create database ky20
create table info (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5));

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);

一、常用查询

对MySQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。

使用select语句可以将需要的数据从MySQL数据库中查询出来，如果对查询的结果进行排序，可以使用order by 语句来完成排序，并最终将排序后的结果返回给用户，这个语句的排序不光可以针对某个字段，也可以针对多个字段

1.ORDER BY 语句

SELECT 字段1,字段2 FROM 表名 [WHERE 条件] ORDER BY 字段1,字段2 ASC|DESC;
ASC|DESC
ASC： 是按照升序进行排序的，默认的排序方式，即ASC可以省略。select 语句中没有指定具体的排序方式，则按照默认ASC方式进行排序
DESC： 是按照降序方式进行排列，order by 前面也可以使用 where 子句对查询结果进一步过滤。

（1）单字段排序

//按hobby字段排序默认不指定则为升序排列asc
例：select id,name,hobbid from info order by hobbid ;

//按降序排列，使用DESC
例：select id,name,hobbid from info order by hobbid desc;

（2）多字段排序

order by 之后的参数，使用“，”分隔，优先级是按照先后顺序而定，order by 之后的第一个参数只有在出现相同的数值，第二字段才有意义

select id,name,hobbid from info order by hobbid desc,id desc;

2.or/and （或/且）

select * from info where score > 70 and score <=90;

select * from info where score > 70 or score <=90;

3.嵌套/多条件

select * from info where score > 70 or (score >75 and score <90);

4.distinct查询不重复记录

select distinct 字段 from 表名;
例：
select distinct hobbid from info;

select name.hobby from info where hobby in (select distinct hobby from info);
//无法执行
1.distinct必须放在最开头
2.distinct只能使用需要去重的字段进行操作，无法传递值
3.distinct去重多个字段时，几个字段同时重复才能被过滤
4.先查询in内的子查询

5.IN显示已知的值的资料

语法：SELECT "字段" FROM "表名" WHERE 字段" IN ('值1','值2', ...);
例：
select * from info where address in ('beijing');
select * from info where address in ('beijing','shanghai');

6.GROUP BY 对GROUP BY后面的栏位的查询结果进行汇总分组

通过SQL查询出来的结果，还可以对其进行分组，使用GROUP BY 语句来实现，GROUP BY
通常都是结合聚合函数一起使用，常用的聚合函数包括：计数（count）、求和（sum）、求平均数（avg）、最大值（max）、最小值（min）、group by 分组的时候可以按一个或多个字段对结果进行分组处理
语法：

select 字段,聚合函数 from 表名(where 字段名(匹配)数值) group by 字段名;
例：
分组排序 select count(id),hobby from info group by hobbid;
//对表中hobby相同的id进行数量统计，并按照相同hobby进行分组

select count(id),hobby from info group by hobbid order by count(id) desc;
//结合order by 把统计的id数量进行按升序排列

7.函数

（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…)	返回集合中最小的值不能有字母

SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1)

（2）聚合函数

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

//count(*)包括了所有的列的行数，在统计结果的时候，不会忽略值为NULL
//count(列名)只包括列名那一列的行数，在统计结果的时候，会忽略列值为NULL的行
例:
select avg(score) from info;

select count(name) from info;
select count(address) from info;

select max(score) from info;
select min(score) from info;

select sum(score) from info;

（3）字符串函数

函数	解释
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
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 反转
length	返回字符长度length(3)规定字符长度为3个

（4）日期时间函数

日期时间函数	描述
curdate()	返回当前时间的年月日
curtime()	返回当前市价你的时分秒
now()	返回当前时间的日期和时间
month(x)	返回日期x中的月份值
week(x)	返回日期x是年度的第几个周
hour(x)	返回x中的小时值
minute(x)	返回日期x中的分钟值
second(x)	返回日期x中的秒数值
dayotweek(x)	返回x是星期几，1为星期日，2为星期一
replace(x,y,z)	将字符z替代字符串x中的字符串y
dayotmonth(x)	计算日期x是本月的第几天
dayotyear(x)	计算日期x是本年的第几天

（5）算数运算符

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余数

在除法算数和求余数运算中不能为0，若除数是0，返回的结果则为null，多个运算符按照先乘除后加减的优先级别进行运算，相同优先级的运算符没有先后顺序。

（6）比较运算

字符串的比较默认不区分大小写，可以使用binary来区分（常用的比较对象：数字，字符）

运算符	解释
=	等于
!=或<>	不等于
LIKE	通配符匹配
>	大于
>=	大于等于
<	小于
<=	小于等于
is null	判断一个值是否为null
is not null	判断一个值是否不为null
between and	两者之间
createst	两个或多个参数时返回最大值
least	两个或多个参数返回最小值
in	在合集中

• 等号(=)

  用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0。如果比较的两者有一个值是NULL，则比较的结果就是NULL。等号(=)用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0。如果比较的两者有一个值是NULL，则比较的结果就是NULL。
  PS:其中字符的比较是根据ASCLL码来判断的，如果ASCLL码相等，则表示两个字符相同;如果ASCIT码不相等，则表示两个字符不相同。例如字符串（字母）比较: (‘a’> ‘b’)其实比较的是底层ascii码
  需要关注的ascii码有: a、 A、o ascll 97、65、48、
  提问:
  如果比较的是多字符，例如: ‘abc’='acb ’ ，是如何比较的?(字符个数、字符顺序) //从左往右相同则继续比较下一位
  如果比较的是多字符，例如: ‘abc’<'baa’是如何比较的?(? )
  与linux返回值表达相反，linux中运行正常返回的是0，运行异常返回的是非0值。
  

  

• 不等于（!=或<>）

用于针对数字、字符串和表达式不相等的比较，如果不相同等则返回1（条件成立），如果相等则返回0（条件不成立），与等于（=）的返回值相反，不等于比较无法判断是否为null值

• 大于、大于等于、小于、小于等于

  成立输出1，不成立输出0
  大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1，否则返回0，同样不能用于判断NULL
  小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回1，否则返回0，同样不能用于判断NULL
  大于等于(>=)判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，不能用于判断NULL
  小于等于(<=)判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，不能用于判断NULL
  

  

• 判断一个值为/不为null（is null，is not null）

IS NULL判断一个值是否为NULL，如果为NULL返回1，否则返回0
IS NOT NULL判断一个值是否不为NULL，如果不为NULL返回1，否则返回0

• 两者之间（between … and …）

通常用于判断一个值是否在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间，条件符合返回1，否则返回0

• 当有两个或者多个参数时，返回其中最大/最小值，如果其中有一个为null，则返回null（least、greatest）

• 在/不在集合中（in/not in）

in 判断一个值是否在集合中，在则返回1，否则返回0
not in 判断一个值是否不在集合中，不在则返回1，在则返回0

8.limit 限制结果条目

在使用 MySQL select 语句进行查询时，结果集返回的是所有匹配的记录，有时候仅需要返回第一行或前几行，这时候需要用 limit 子句

select 字段,字段 from 表名 limit 数字(开始行),数字(偏移量);
例如
select * from info limit 3,2;
//显示第三行后面的2行内容

//结合order by 排序：
select * from info order by id limit 3,4;

9.设置别名

在MySQL查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性
使用场景：
1.对复杂的表进行查询的时候，别名可以缩短查询语句 2.多表相连查询的时候（通俗易懂，缩短sql语句）

AS别名
语法：

对于列的别名：select 字段 AS 字段别名 from 表名;

对于表的别名：select 字段 from 表名 AS 表别名;
//AS可以省略，使用AS之后可以用表别名代替表名，其中AS语句是可选的，AS之后的别名，主要为了表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段是不会被改变的

列别名设置：select name as 姓名,score as 成绩 from info;
表数据别名：select i.name as 姓名,i.score as 成绩 from info as i;

AS作为连接语句

复制表数据结构

create table tmp as select * from info;

解释：
1.创建一个新的tmp定义表结构，插入表数据（与info记录相同的表）
2.但是“约束“（如主键，自增长等）没有复制到新的表
与复制表数据和克隆表结构类似

加入where 判断语句

create table test1 as select * from info where score > 60;
//只复制score > 60 的数据

10.通配符

通常通配符都是跟 like（模糊查询）一起使用的，并协同 where子句共同来完成查询任务

% ：百分号表示零个、一个或多个字符
_ ：下划线表示单个字符
实例：
select * from info where name like 'l%';
//匹配以l开头的

select * from info where name like 'l_s_';
//匹配到结果是lisi

select * from info where name like 'l_%_';

• 通配符匹配

LIKE用来匹配字符串，如果匹配成功则返回1，反之返回0
LIKE支持两种通配符：‘%’用于匹配任意数目的字符,而’_'只能匹配一个字符
not like 正好跟like相反，如果没有匹配成功则返回1，反之返回0

== % ：==百分号表示零个、一个或多个字符
==_ ：==下划线表示单个字符

11.子查询

(1）概念

1.1子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。
1.2子查询是优先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤
补充子语句可以与主语句所查询的表相同，也可以是不同表

示例：
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
      in:将主表和子表关联/链接起来的语法
select name，score from info where score >80      

in 之后的子查询语句会给他提供一个范围（集合）作为‘in’之前where的判断条件



示例：
单表查询方式：
select * from info where id in (1,3,5)
子查寻方式:
create table num (id int(4));
insert into num values(1),(3),(5);
select  * from info where id in (select id from num);
1.子查询不仅可以在select 语句中使用，在 insert、update、delete 中同样使用，支持多层嵌套
2.in语句是用来判断某个值是否在给定的集合内（结果集）in往往和select 搭配使用
3.也可以使用 not in表示对结果集取反

(2) 子查询—别名

将结果集作为一张”表“进行查询的时候，我们也需要用到别名
如果 select id from (select id,name from info);会报错表名的位置是结果集，mysql并不能识别，
正确：select a.id from (select id,name from info) a;相当于 ：select 表.字段，字段 from 表;

(3)子查询—exists

select count(*) as number from info where exists (select id from info where name='zhangsan')

补充： as number 将count 统计的结果作为number（列名）返回
exists：布尔值判断，后面的内查询语句（返回的结果集）是否成立
where：之后跟条件判断
exists，只为了判断exists之后的条件是否成立，如果成立，则正常执行语句的匹配，如不成立，则不会执行主语句查询
count ：计数 子查询不成立输出0
sum：求和 ，使用sum求和结合exists，如果子查询结果集不成立的话，输出为null

12.增加内容：视图

数据库中的虚拟表，这张虚拟表中不包含真是数据，只做映射，动态保存结果集（数据）
结果根据原表数据动态显示，同时给与不同权限用户展示部分表中的内容，有一定的安全性

示例：
//创建视图 create view v_score as select * from info where score>=90;
show table status\G
//查看视图 select * from v_score;

//修改原表数据 insert into info values(7,'zhaoqi',120,'chengdu',4);

修改视图可能也会增加一条原表的数据

13.null值

（1）定义
在SQL语句使用过程中，经常会碰到null 这几个字符，通常使用null来表示缺失的值，也就是在表中该字段是没有值的。
在向表内插入记录或者更新记录时，如果该字段没有not null 并且没有值，这时候新记录的该字段将保存为null，需要注意的是，null值与数字0或者空白（spaces）的字段是不同的，值为null的字段是没有值的但是在表格中还是占用4个字节。在SQL语句中，使用IS NULL 可以判断表内的某个字段是不是null值，相反的用is not null可以判断不是null值。

查询info表结构，id和name字段不允许空值

null值和空值的区别（空气和真空）
空值长度为0，不占空间但是使用计数函数会0值会计数，null值的长度为null，占用空间
is null无法判断空值，空值使用“=”或者“<>”来处理 （！=）
count（）计算时，null会忽略，空值会加入计算

验证：
alter table info add column addr varchar(50);
//计数 select count(addr) from info;  //查看null值是否加入统计计数中

//info表中其中一条数据修改为空值，查看是否会被统计
update info set addr='' where id=7;

//对比空值与null查询时的区别
select * from info where addr='';

//差询null值是使用'is'
select * from info where addr is null;  //不能可以使用 add='null'
select * from info where addr is not null; 

14.正则表达式

MySQL正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式记录中符合要求的特殊字符串。MySQL的正则表达式使用regexp这个关键字来指定正则表达式的匹配模式，regexp 操作符所支持的匹配模式如下

匹配模式			描述									    实例
^ 				匹配文本的开始字符 						‘^bd’ 匹配以 bd 开头的字符串
$ 				匹配文本的结束字符 						‘qn$’ 匹配以 qn 结尾的字符串
. 				匹配任何单个字符							‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
* 				匹配零个或多个在它前面的字符 				‘fo*t’ 匹配 t 前面有任意个 o
+ 				匹配前面的字符 1 次或多次					‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串 			匹配包含指定的字符串 						‘clo’ 匹配含有 clo 的字符串
p1|p2 			匹配 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 info where name regexp 'g*';   //匹配所有 

15.逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0，真和假也可以用TRUE和FALSE表示。
MySQL中支持使用的逻辑运算符有四种

运算符	解释
not 或 !	逻辑非
and 或 &&	逻辑与
or	逻辑或
xor	逻辑异或

(1) 逻辑非（not 或 !）

逻辑非将跟在他后面的值取反，如果NOT后面的操作数为0时，所得值为1
如果操作数为非0时，所得值为0
如果操作数为NULL时，所得值为NULL

(2) 逻辑与（and 或 &&）

当所有操作数都为非零值并且不为NULL时，返回值为1
当一个或多个操作数为0时，返回值为0
操作数没有非0值，中有任何一个为NULL时，返回值为NULL

(3) 逻辑或（or）

两个数任意一个为非空值非null则返回1，一个为null一个为0时返回null
当两个操作数都为非NULL值时，如果有任意一个操作数为非零值，则返回值为1，否则结果为0
当有一个操作数为NULL时，如果另一个操作数为非零值，则返回值为1，否则结果为NULL
假如两个操作数均为NULL时,则返回值为NULL。

（4）逻辑异或（xor）

有NULL则为NULL
非NULL时

• 全是0或者全是非0则为0
• 不全是0则为1

16.位运算符

• 位运算是二进制数上进行计算的运输符
• 位运算会先将操作数变成二进制数，进行位运算，然后再将计算结果从二进制数变回十进制数

符号	解释
&	按位与
|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

按位与：对应的二进制位都为1，则运算结果为1，否则为0
按位或：对应的二进制位有一个为1则为1，否则为0
按位异或：对应的二进制位不同时，运算结果为1，否则为0
取反：对应的二进制数逐位反转，0取反为1,1取反为0

10(2)=1010 15(2)=1111
示例：
select 10 & 15;
select 10 | 15;
select 10 ^ 15;
select 10 & ~15;

17. 连接查询

创建模板表
CREATE TABLE test2 (
a_id int(11) DEFAULT NULL,
a_name varchar(32) DEFAULT NULL,
a_level int(11) DEFAULT NULL);
)

CREATE TABLE test3 (
b_id int(11) DEFAULT NULL,
b_name varchar(32) DEFAULT NULL,
b_level int(11) DEFAULT NULL);
）

insert into test2(a_id, a_name, a_level) values(1, 'aaaa', 10); 
insert into test2(a_id, a_name, a_level) values(2, 'bbbb', 20); 
insert into test2(a_id, a_name, a_level) values(3, 'cccc', 30); 
insert into test2(a_id, a_name, a_level) values(4, 'dddd', 40);

insert into test3(b_id, b_name, b_level) values(2, 'bbbb', 20); 
insert into test3(b_id, b_name, b_level) values(3, 'cccc', 30); 
insert into test3(b_id, b_name, b_level) values(5, 'eeee', 50); 
insert into test3(b_id, b_name, b_level) values(6, 'ffff', 60);

内连接是默认的表连接，可省略inner ，只用 jion ，查询的结果为两表相同字段。最好不要超过三张表。
左连接中主表在左边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来
右连接中主表在右边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来

（1）内连接

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

语法格式 select 字段 from 表名1 inner join 表名2 on 表名1.字段 = 表名2.字段;

select a.a_id,a.a_name,a.a_level from test2 a inner join test3 b on a.a_id = b.b_id; 

（2）左连接

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

select * from test2 a left join test3 b on a.a_name=b.b_name;
1

（3）右连接

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

select * from test2 a right join test3 b on a.a_name=b.b_name;