MySQL 高级 SQL 语句

目录

• 一、常用查询
  • 1. 按关键字进行排序
  • 2. 区间判断及查询不重复记录
  • 3. 嵌套 多条件查询
  • 4. 查询不重复记录
• 二、对结果进行分组
• 三、限制结果条目
• 四、设置别名
• 五、通配符
• 六、子查询
• 七、视图
• 八、NULL 空值
• 九、连接查询
  • 1. 内连接
  • 2. 左连接
  • 3. 右连接
• 十、存储过程
  • 1. 概念
  • 2. 简介
  • 3. 优点
  • 4. 语法

操作环境

create database sql1;
use sql1;
create table tab1 (id int,name varchar(10) primary key not null,score decimal(5,2),address varchar(20),hobbid int(5));

insert into tab1 values (1,'liuyi',80, 'beijing',2);
insert into tab1 values (2, 'wangwu' ,90, 'shengzheng',2);
insert into tab1 values (3, 'lisi', 60, 'shanghai' ,4 );
insert into tab1 values (4, 'tianqi' ,99,'hangzhou' ,5);
insert into tab1 values (5, 'jiaoshou' ,98, 'laowo' , 3) ;
insert into tab1 values (6, 'hanmeimei'  ,10, 'nanjing' ,3);
insert into tab1 values (7,'lilei',11,'nanjing' ,5);

一、常用查询

1. 按关键字进行排序

格式：

select 字段1 [字段2] from [表名] order by 排序字段1 asc|desc,[排序字段2] asc|desc;

按排序字段1的值进行排序，asc表示升序，默认排序方式（可省略），desc表示降序。当多个排序字段1的值相同时按排序字段2进行排序。

可以搭配 where 筛选条件进行使用。

格式	作用
order by A,B desc	A用升序，B用降序
order by A asc,B desc	A用升序，B用降序
order by A desc,B desc	A用降序，B用降序

实例：

筛选出表tab1中 score >= 60 的记录，并且按照 hobbid字段的值进行升序排序，若hobbid值一样，按id降序排序

select * from tab1 where score>=60 order by hobbid,id desc;

2. 区间判断及查询不重复记录

用 and 和 or 来表示区间

and：且

or：或

实例1：

筛选出 score 大于等于60 小于等于90 的记录，并且按升序排序

select * from tab1 where score>=60 and score<=90 order by score;

实例2：

筛选出 id=1 或者 score 等于60 或者 等于90 的记录，并且按降序排序

select * from tab1 where id=6 or score=60 or score=90 order by score desc;

3. 嵌套 多条件查询

用 ([查询条件]) 进行嵌套查询，先处理括号里的条件，再处理括号外的条件

实例：

先筛选出 score 大于 70 小于90 的记录，再筛选出等于90的记录）

select * from tab1 where score=90 or (score>70 and score<90);

4. 查询不重复记录

distinct 用于查询不重复记录

格式：

select distinct 字段 from 表名

实例：

筛选出 hobbid，重复记录不显示

select distinct hobbid from tab1;

二、对结果进行分组

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

格式：

select 函数(字段1),字段2 from 表名 group by 字段2;

实例1：

按hobbid相同的分组，计算相同分数的学生个数（基于name个数进行计数），并按计数升序排序

select count(name),hobbid from tab1 group by hobbid order by count(name);

实例2

结合where语句，筛选分数大于等于80的分组，计算学生个数

select count(name),hobbid from tab1 where score>=80 group by hobbid;

三、限制结果条目

limit 限制输出结果记录

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

格式	作用
limit 2	显示前两行记录
limit 2,3	显示第2行之后的前3行记录

实例1：

显示tab1表中按id升序排序的前3行记录

select * from tab1 order by id limit 3;

实例2：

显示tab1表中按id升序排序的第3行后的前3行记录

select * from tab1 order by id limit 3,3;

四、设置别名

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

在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。

列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。

格式：

对于列的别名：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 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名 或字段名是不会被改变的

AS 还可以作为连接语句的操作符。创建t1表，将tab1表的查询记录全部插入t1表

但是”约束“没有被完全”复制“过来 ，但是如果原表设置了主键，那么附表的 default字段会默认设置一个0

create table t1 as select * from tab1;

相似：

克隆、复制表结构

create table t1 (select * from tab1);

也可以加入where 语句判断

create table t2 as select * from tab1 where score >=60;

实例1：

筛选出sql1数据库中tab1学生表中的学号，姓名，分数，按分数倒序

select 学生表.id 学号,学生表.name 姓名,学生表.score 分数 from sql1.tab1 学生表 order by 学生表.score desc;

实例2：

筛选出最高分、最低分及计算总分和平均分

select max(学生表.score) 最高分,min(学生表.score) 最低分,sum(学生表.score) 总分,avg(学生表.score) 平均分 from sql1.tab1 学生表;

五、通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。

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

常用的通配符有两个，分别是：

%：百分号表示零个、一个或多个字符

_：下划线表示单个字符

实例：

① 查询 name 以 l 开头的记录

select * from tab1 where name like 'l%';

② 查询 name 以 u 结尾的记录

select * from tab1 where name like '%u';

③ 查询 name 中间有 s 的记录

select * from tab1 where name like '%s%';

④ 查询 name 第2个字母为a的记录

select * from tab1 where name like '_a%';

六、子查询

• 子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。

  PS：子语句可以与主语句所查询的表相同，也可以是不同表

• 子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

• 当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE。 若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据，如果需 求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。 多数情况下，子查询都是与 SELECT 语句一起使用的

• 将结果集做为一张表进行查询的时候，我们也需要用到别名

实例1：

创建表 t1，只有一个字段 id，有3个记录为1、2、3

create table t1 (id int);
insert into t1 values (1),(2),(3);

将表tab1中符合表t1中id字段的记录拷贝为一个新的表tab2

create table tab2 (select * from tab1 where id in (select id from t1));

实例2：

查询表tab1中符合表t1中id字段的name和score

select a.name,a.score from (select * from tab1 where id in (select id from t1)) a;

七、视图

视图是优化操作+安全方案

数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射

视图可以理解为镜花水月/倒影，动态保存结果集（数据）

功能：

简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性

本质而言视图是一种select(结果集的呈现)

PS：视图适合于多表连接浏览时使用!不适合增、删、改

而存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率!

视图和表的区别和联系

（1）区别：

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

② 视图没有实际的物理记录。而表有。
show table status\G

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

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

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

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

（2）联系：

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

实例1：

创建一个表tab1中sorce>=80的视图v_sorce

create view v_score as select * from tab1 where score>=80 order by score desc;

实列2：

对源表tab1进行更新，查看视图v_score是否更新

update tab1 set score=100 where id=1;

实列3：

对视图v_score进行更新，查看源表tab1是否更新

update v_score set score=80 where id=1;

实例4：

创建一个表tab3，记录id、name、age

create table tab3 (id int,name varchar(10),age char(10));
insert into tab3 values(1,'zhangsan',20);
insert into tab3 values(2,'lisi',30);
insert into tab3 values(3,'wangwu',29);

再创建一个v_ages多表视图，需要在tab1中与表tab3中name对应的记录，输出id、name、score、age

create view v_ages(id,name,score,age) as select a.id,a.name,a.score,b.age from tab1 a,tab3 b where a.name=b.name;

八、NULL 空值

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

null值与空值的区别（相当于空气与真空的区别）

空值长度为0，不占空间，NULL值的长度为null，占用空间

is null 无法判断空值

空值使用"=“或者”<>"来处理（！=）

count（）计算时，NULL会忽略，空值会加入计算

实例：

将表tab2中id为1的score改为null

update tab2 set score=null where id=1;

分别筛选出表tab2中score为null和不为null的记录

# score为null
select * from tab2 where score is null;

# score不为null
select * from tab2 where score is not null;

九、连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。

使用较多的连接查询包括：内连接、左连接和右连接

环境

create table test1 (
a_id int(11) default null,
a_name varchar(32) default null,
a_level int(11) default null);

create table test2 (
b_id int(11) default null,
b_name varchar(32) default null,
b_level int(11) default null);

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

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

1. 内连接

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

内连查询：通过inner join 的方式将两张表指定的相同字段的记录行输出出来

实例：

筛选出表test1和test2中name相同的记录

select a.a_id,a.a_name,a.a_level from test1 a inner join test2 b on a.a_name=b.b_name;

2. 左连接

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

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

实例：

显示表test1中的记录和筛选出表test1与test2中name相同的记录

select * from test1 left join test2 on test1.a_name=test2.b_name;

3. 右连接

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

在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹配的行，这些记录在左表中以 NULL 补足

显示表test2中的记录和筛选出表test2与test1中name相同的记录

select * from test1 right join test2 on test1.a_name=test2.b_name;

十、存储过程

1. 概念

前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的 SQL语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条SQL 语句一起去处理才能够完成，这时候就可以使用存储过程，轻松而高效的去完成这个需求，有点类似shell脚本里的函数

2. 简介

（1）存储过程是一组为了完成特定功能的SQL语句集合。两个点第一触发器（定时任务)第二个判断

（2）存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统SQL语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高

存储过程在数据库中，创建并保存，它不仅仅是SQL语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、在不同的应用程序或平台上执行相同的函数等等。

3. 优点

（1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率

（2）sQL语句加上控制语句的集合，灵活性高

（3）在服务器端存储，客户端调用时，降低网络负载

（4）可多次重复被调用，可随时修改，不影响客户端调用

（5）可完成所有的数据库操作，也可控制数据库的信息访问权限

4. 语法

不带参数存储过程的创建

# 语句开始
delimiter $$

# 创建存储过程
create procedure 过程名()
# 存储过程开始（若只有一条sql语句，可以省略 begin --- end ）
begin
# 过程体
create table 表名 (字段1,字段2,……);
insert into 表名 values (值1,值2,……);
select * from 表名;
# 存储过程结束
end $$

# 语句结束
delimiter ;

# 调用存储过程
call 过程名;

查看存储过程

# 查看存储过程
show procedure status;

# 查看指定存储过程信息
show procedure status like '%存储过程名%'\G;

带参数的存储过程

in 输入参数：表示调用者向过程传入值（传入值可以是自变量或变量）

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

inout 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

delimiter @@
create procedure 过程名 (in 形参1,形参2,……)
begin
select * from 表名 where 字段=形参;
end @@
delimiter ;

# 调用传参
call 过程名('实参1','实参2',……);

删除存储过程

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

drop procedure if exists 过程名;
# if exists 表示如果 过程名 不存在就不执行操作