【MySQL】SQL的高阶用法

文章目录

- 条件查询
- - 使用关系运算符查询
  - 使用IN关键字查询
  - 使用BETWEEN AND关键字查询
  - 使用空值查询
  - 使用AND关键字查询
  - 使用OR关键字查询
  - 使用LIKE关键字查询(模糊查询)
  - 使用LIMIT分页查询
  - 使用GROUP BY进行分组查询
  - - GROUP BY和聚合函数一起使用
    - GROUP BY和聚合函数以及HAVING一起使用
  - 使用ORDER BY对查询结果排序
- 组合查询(UNION、UNION ALL)
- 别名设置(AS)
- - 为表起别名
  - 为字段取别名
- 多表关系
- - 表的关联关系
  - 数据准备
  - 关联查询
  - 删除外键的数据
  - 交叉连接查询(CROSS JOIN)
  - 内连接查询([INNER] JOIN)
  - 外连接查询(LEFT | RIGHT [OUTER] JOIN)
- 子查询
- - 数据准备
  - 带比较运算符的子查询
  - 带EXISTS关键字的子查询
  - 带ANY关键字的子查询
  - 带ALL关键字的子查询
- 总结

条件查询

数据准备

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建student表
CREATE TABLE student (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1012', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1013', 'wang', 15, 'female');

使用关系运算符查询

关系运算符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于

示例
```
select * from student where age>=17;
```

使用IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

示例一：正向用法

select * from student where sid in ('S_1002','S_1003');

示例二：逆向用法

select * from student where sid not in ('S_1001');

使用BETWEEN AND关键字查询

用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来

示例

select * from student where age between 15 and 18;
select * from student where age not between 15 and 18;

使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串

示例

insert into student (sid,sname) values ('S_1014', 'alan');

select * from student where age is null;

使用AND关键字查询

AND左右两边条件都满足才返回数据

示例

select * from student where age>15 and gender='male';

使用OR关键字查询

在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来

示例

select * from student where age>15 or gender='male';

使用LIKE关键字查询(模糊查询)

使用LIKE关键字可以判断两个字符串是否相匹配

符号	含义	示例
%	包含零个或多个字符的任意字符串	like’Mc%’ 将搜索以字母 Mc 开头的所有字符串(如McBadden)。
_	任何单个字符	like’_heryl’ 将搜索以字母 heryl 结尾的所有六个字母的名称(如Cheryl、Sheryl)
[]	指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符	like’[M-Z]inger’将搜索以字符串inger结尾、以从M到Z的任何单个字母开头的所有名称(如 Ringer)
[^]	不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符	like’M[^C]’ 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）
*	它同于DOS命令中的通配符，代表多个字符	c*c代表cc,cBc,cbc,cabdfec等多个字符
?	同于DOS命令中的？通配符，代表单个字符	b?b代表brb,bFb等
#	只能代表单个数字	k#k代表k1k,k8k,k0k

示例

select * from student where sid LIKE '%100%';

使用LIMIT分页查询

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条

示例

select * from student order by age asc limit 3;

语法格式
格式: SELECT * FROM 表名 LIMIT start,count;
- 只有count，开始位置(start)使用默认值0
- count: 查询数量
扩展LIMIT OFFSET
LIMIT OFFSET
- M: 查询数量
- N: 开始位置
可以简写成: LIMIT 15 OFFSET 30 还可以简写成LIMIT 30, 15
分页查询

已知：每页显示m条数据，当前显示第n页，则第n页的数据的开始位置：(n-1)*m

SELECT * FROM 表名 LIMIT (n-1)*m,m;
说明
- MySQL分页中开始位置为0,(例如,检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1将检索出第二行而不是第一行)
- OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集

使用GROUP BY进行分组查询

数据准备

-- 创建数据库
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;

-- 创建员工表
CREATE TABLE employee (
    id int,
    name varchar(50),
    salary int,
    departmentnumber int
);

-- 向员工表中插入数据
INSERT INTO employee values(1,'tome',2000,1001); 
INSERT INTO employee values(2,'lucy',9000,1002); 
INSERT INTO employee values(3,'joke',5000,1003); 
INSERT INTO employee values(4,'wang',3000,1004); 
INSERT INTO employee values(5,'chen',3000,1001); 
INSERT INTO employee values(6,'yukt',7000,1002); 
INSERT INTO employee values(7,'rett',6000,1003); 
INSERT INTO employee values(8,'mujk',4000,1004); 
INSERT INTO employee values(9,'poik',3000,1001);

GROUP BY和聚合函数一起使用

示例一:统计各门的员工数

select count(*), departmentnumber from employee group by departmentnumber;

示例二:统计部门编号大于1001的各部门员工个数 MySQL命令

select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;

GROUP BY和聚合函数以及HAVING一起使用

示例:统计工资总和大于8000的部门

select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

使用ORDER BY对查询结果排序

基本语法

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

字段名1、字段名2是查询结果排序的依据；
参数 ASC表示按照升序排序，DESC表示按照降序排序；
默认情况下，按照ASC方式排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。

示例:查询所有学生并按照年纪大小升序排列
```
select * from student order by age asc;
```

组合查询(UNION、UNION ALL)

组合查询可以将多个SELECT结果拼接在一起作为最终结果输出(输出组合成单个查询结果集)

区别
- UNION会将组合后的结果进行去重处理
- UNION ALL会将组合的结果直接拼接，并不进行去重处理
注意事项
- 组合查询的两张表的内容需要对齐，包括列数需要一致，顺序需要一致；否者直接报错
- 组合查询应避免出现列的字段内容不对应的情况；虽然结果没有抛出异常，但是连接后的表数据错乱，这样的数据已经没有意义了。
- 关于UNION之后的排序，ORDER BY应该写在最后；不是对每部分结果排序，而是对最终结果排序
  
  对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句

示例

-- 导入数据
DROP TABLE IF EXISTS table_a; 
CREATE TABLE table_a( 
a_name VARCHAR(8), 
a_age INT 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 

INSERT INTO 
table_a (a_name,a_age) 
VALUE ('Alice',18) 
,('Bob',29) 
,('Allen',22) 
,('Kitty',27) 
,('Jack',23); 
 
DROP TABLE IF EXISTS table_b; 
CREATE TABLE table_b( 
b_name VARCHAR(8), 
b_age INT 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8;

INSERT INTO 
table_b (b_name,b_age) 
VALUE ('Alice',18) 
,('Bob',29) 
,('Kevin',30) 
,('Anne',24) 
,('Jean',29);


# 去重的
SELECT * 
FROM table_a 
UNION 
SELECT * 
FROM table_b;

# 不去重的
SELECT * 
FROM table_a 
UNION ALL 
SELECT * 
FROM table_b;

别名设置(AS)

在査询数据时可为表和字段取別名，该别名代替表和字段的原名参与查询操作。

为表起别名

语法格式

SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;

示例

select * from student as stu;
select * from student stu;

为字段取别名

在查询操作时，假若字段名很长使用起来就不太方便，此时可该字段取一个別名，用该别名来代替字段的名称。

语法格式

SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;

示例

select sname as '姓名', sid from student;

多表关系

表的关联关系

多对一

多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。

在多对一的表关系中，应将外键建在多的一方否则会造成数据的冗余。
多对多

多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。

通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。
一对一

在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。

数据准备

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
    cid int(4) NOT NULL PRIMARY KEY,
    cname varchar(30) 
);

-- 创建学生表
CREATE TABLE student(
    sid int(8) NOT NULL PRIMARY KEY,
    sname varchar(30),
    classid int(8) NOT NULL
);

-- 为学生表添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid);
-- 向班级表插入数据
INSERT INTO class(cid,cname)VALUES(1,'Java');
INSERT INTO class(cid,cname)VALUES(2,'Python');

-- 向学生表插入数据
INSERT INTO student(sid,sname,classid)VALUES(1,'tome',1);
INSERT INTO student(sid,sname,classid)VALUES(2,'lucy',1);
INSERT INTO student(sid,sname,classid)VALUES(3,'lili',2);
INSERT INTO student(sid,sname,classid)VALUES(4,'domi',2);

关联查询

查询Java班的所有学生

select * from student where classid=(select cid from class where cname='Java');

删除外键的数据

问题：请从班级表中删除Java班级。

在此，请注意：班级表和学生表之间存在关联关系；
要删除Java班级，应该先删除学生表中与该班相关联的学生。否则，假若先删除Java班那么学生表中的cid就失去了关联

delete from student where classid=(select cid from class where cname='Java');
delete from class where cname='Java';

直接删JAVA班级

先删了学生表中与该班相关联的学生，再删Java班级

交叉连接查询(CROSS JOIN)

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接。实际生产中用到的少

语法格式

SELECT * FROM 表1 CROSS JOIN 表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。

内连接查询([INNER] JOIN)

内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。

语法格式

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

INNER JOIN用于连接两个表
ON来指定连接条件
其中INNER可以省略

数据准备

-- 若存在数据库mydb则删除
DROP DATABASE IF EXISTS mydb;
-- 创建数据库mydb
CREATE DATABASE mydb;
-- 选择数据库mydb
USE mydb;

-- 创建部门表
CREATE TABLE department(
  did int (4) NOT NULL PRIMARY KEY, 
  dname varchar(20)
);

-- 创建员工表
CREATE TABLE employee (
  eid int (4) NOT NULL PRIMARY KEY, 
  ename varchar (20), 
  eage int (2), 
  departmentid int (4) NOT NULL
);

-- 向部门表插入数据
INSERT INTO department VALUES(1001,'财务部');
INSERT INTO department VALUES(1002,'技术部');
INSERT INTO department VALUES(1003,'行政部');
INSERT INTO department VALUES(1004,'生活部');
-- 向员工表插入数据
INSERT INTO employee VALUES(1,'张三',19,1003);
INSERT INTO employee VALUES(2,'李四',18,1002);
INSERT INTO employee VALUES(3,'王五',20,1001);
INSERT INTO employee VALUES(4,'赵六',20,1004);

示例:查询员工姓名及其所属部门名称

select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

外连接查询(LEFT | RIGHT [OUTER] JOIN)

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。

语法格式

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

LEFT | RIGHT [OUTER] JOIN 为关键字
关键字左边的表被称为左表，关键字右边的表被称为右表；OUTER可以省略。

LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

如果表1的某条记录在表2中不存在则在表2中显示为空。

数据准备

-- 若存在数据库mydb则删除
DROP DATABASE IF EXISTS mydb;
-- 创建数据库mydb
CREATE DATABASE mydb;
-- 选择数据库mydb
USE mydb;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);
-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1002);
INSERT INTO student VALUES(4,'赵六',23,1003);
INSERT INTO student VALUES(5,'Jack',22,1009);


准备这组数据有一定的特点，为的是让大家直观的看出左连接与右连接的不同之处
1、班级编号为1004的PHP班级没有学生
2、学号为5的学生Jack班级编号为1009，该班级编号并不在班级表中

示例一：查询每个班的班级ID、班级名称及该班的所有学生的名字 -> 左（外）连接查询

select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid;

结果分析

1、分别找出Java班、C++班、Python班的学生
2、右表的Jack不满足查询条件故其没有出现在查询结果中
3、虽然左表的PHP班没有学生，但是任然显示了PHP的信息；但是，它对应的学生名字为NULL

示例二：查询每个班的班级ID、班级名称及该班的所有学生的名字 -> 右（外）连接查询

select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;

结果分析

1、分别找出Java班、C++班、Python班的学生
2、左表的PHP班不满足查询条件故其没有出现在查询结果中
3、虽然右表的jack没有对应班级，但是任然显示王跃跃的信息；但是，它对应的班级以及班级编号均为NULL

子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

数据准备

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);

-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');
INSERT INTO class VALUES(1005,'Android');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1003);
INSERT INTO student VALUES(4,'赵六',23,1004);
INSERT INTO student VALUES(5,'小明',21,1001);
INSERT INTO student VALUES(6,'小红',26,1001);
INSERT INTO student VALUES(7,'小亮',27,1002);

带比较运算符的子查询

示例一：查询张三同学所在班级的信息

select * from class where cid=(select classid from student where sname='张三');

示例二：查询比张三同学所在班级编号还大的班级的信息

select * from class where cid>(select classid from student where sname='张三');

带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询，它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行

示例：假如王五同学在学生表中则从班级表查询所有班级信息
```
select * from class where exists (select * from student where sname='王五');
```

带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

示例：查询比任一学生所属班级号还大的班级编号

select * from class where cid > any (select classid from student);

带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件

示例：查询比所有学生所属班级号还大的班级编号

select * from class where cid > all (select classid from student);

总结

查询语句的书写顺序和执行顺序

select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序

from ===> where ===> group by ===> having ===> select ===> order by ===> limi