MySQL基础(分组查询,排序查询,多表查询,连接查询,子查询,分页查询,联合查询)_mysql多表查询和分组排序

数据库的基本概念

​ 数据库的英文单词: DataBase 简称:DB

什么是数据库?

​ 用于存储和管理数据的仓库

数据库的特点

​ 1.持久化存储数据,其实数据库就是一个文件系统

​ 2.方便存储管理数据

​ 3.使用了统一的方式操作数据 ---- sql

常见数据库

​ MySQL,Oracle,DB2,SQLServer(教学),MongoDB…

MySQL数据库

数据库分为数据库和数据库管理系统两部分,直接安装的MySQL中,被执行的部分是库,所有的sql执行都是属于数据库操作系统的操作

​ MySQL数据是一种什么类型的数据库

​ MySQL数据库是一种关系型数据库

SQL运用?

​ 1.什么是SQL?

​ structured Query Language 结构化查询语言

​ 其实就是关系型数据库的规则,每一种数据库的SQL操作方式都会稍有区别,称为"方言"

​ 2.SQL通用语法

​ sql语句可以单行或多行书写,以分号结束

​ 可以使用空格和缩进来增强语言的可读性

​ mysql数据库中,SQL语句不区分大小写,但是内容是区分大小写

3. SQL的注释
   1. 单行注释 – 单行注释
   2. 井号 注释单行
   3. /* 多行注释 */

SQL分类:

​ DDL(Data Definition Language) 数据定义语言,用于数据库中结构的操作 如: 建库,建表,修改删除结构等

​ DML(Data Manipulation Language) 数据的操作语言 针对表数据的操作

​ DQL (Data Query Language) 数据查询语言，用来查询数据库中表的记录

​ DCL(Data Control Language) 数据控制语句 对用户权限的修改

数据库的操作

-- 创建数据库 create创建
-- 格式: create database 数据库名;
create database hello;
-- 判断数据库是否存在,如果不存在就创建数据库
-- 格式: create database if not EXISTS 数据库名;
create database if not  exists hello1;
-- 删除数据库
-- drop表示删除
-- 格式:  drop database 数据库名;
drop database hello;
-- 修改数据库 alter修改
-- 修改数据库的字符集  格式: alter database 数据库名 DEFAULT CHARACTER set 编码格式
alter database hello1 DEFAULT CHARACTER set utf8;
-- 查询数据库
show databases;
-- 查询数据库的定义信息
show create database hello1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DDL结构定义语句

命名规则

​ 可以使用字母 _ $和#开头

​ 可以包含数字

​ 不可以使用数据库关键字

数据类型

表的操作

​ 1.create 创建表

格式:  create table 表名(列名1 数据类型1,列名2 数据类型2......)
1

-- 创建表
create table teacher(
	id int(8),
	name VARCHAR(20),
	sex char(2),
	age int(4),
	phone char(11),
	address VARCHAR(50)
)
1
2
3
4
5
6
7
8
9

注意: 最后一行.不需要使用逗号

​ 2.创建新的表复制另一个表

​ 复制表的结构

​ 格式: create table 表名 like 被复制的表名;

-- 创建一个表复制于另一个表
create table student like teacher;
-- 创建一个表,复制另一个表的结构不需要数据
create table teacher2 select * from teacher where 1=2;
-- 创建一个新的表,复制另一个表的结构及数据
create table teacher1 select * from teacher;
1
2
3
4
5
6

​ 3.查询表,修改表,删除表

-- 查询表
show tables;
-- 查询表结构
desc stu;
-- 修改表  关键字alter
	-- 修改表名  alter table 源表名 rename to 新表名
	alter table student rename to stu;
	-- 修改表的字符集
	-- alter table 表名 character set 字符集
	alter table stu character set gbk;
	-- 修改表中的列
	-- 添加列
	-- alter table 表名 add 列名 数据类型
	alter table stu add birth date;
	-- 修改列
	-- alter table 表名 modify 列名 新的数据类型
	alter table stu MODIFY birth varchar(15);
	-- 删除列  drop
	-- alter table 表名 drop 列名
	alter table stu drop birth;
-- 删除表 drop
-- drop table 表名
-- drop table if exists 表名
drop table if exists stu;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

约束

概念

​ 对表中的数据进行限定,保证表中数据的正确性,有效性和完整性

约束分类

​ 1.主键约束 primary key 保证数据的非空且唯一

​ 2.非空约束 not null 保证数据不能为null

​ 3.唯一约束 unique 保证数据的唯一性

​ 4.外键约束 foreign key 用于建立主外键关系

​ 5.检查约束 check 约束条件 Oracle数据库

创建一个表,设定主键和非空约束,唯一约束

-- 列后面添加约束
create table student(
	id int(8) PRIMARY key,
	name VARCHAR(20) UNIQUE,
	sex VARCHAR(2) not null,
	age int(3)
);
-- 创建列之后添加约束
-- 列后添加约束,使用关键字 constraint
-- constraint 约束名 约束
drop table if exists student;
create table student(
	id int(8),
	name VARCHAR(20),
	sex VARCHAR(2) not null,  -- 非空约束只能写在列后
	age int(3),
	CONSTRAINT stu_id_pk PRIMARY key(id),
	constraint stu_name_uk UNIQUE(name)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

主外键关联关系

外键约束的设置

create table grade(
	id int(8) PRIMARY KEY,
	name VARCHAR(10) not null,
	remark VARCHAR(50)
)
-- 创建学生表,需要建立主外键关联关系
-- constraint 约束名 FOREIGN key(外键列) REFERENCES 主键表(主键列)
drop table if exists student;
create table student(
	id int(8),
	name VARCHAR(20),
	sex VARCHAR(2) not null,  -- 非空约束只能写在列后
	age int(3),
	gid int(8),  -- 外键对应主键表的主键id
	CONSTRAINT stu_id_pk PRIMARY key(id),
	constraint stu_name_uk UNIQUE(name),
	-- 添加主外键约束
	CONSTRAINT stu_gid_fk FOREIGN key(gid) REFERENCES grade(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

注意: 主外键关联关系设置时,必须先有主键表,然后再设置外键表

​ 表中的数据,也是先存在主键数据,然后外键才能引用数据

​ 删除表的时候,必须不存在外键关联

DML语句: 主要执行数据的增删改

1.添加数据

​ 语法: insert into 表名 values( 对应表中所有列插入数据 )

​ insert into 表名(列名1,列名2,列名3…) values( 对应指定列插入数据 )

注意: 1. 列名和值要一 一对应

2.如果表名后有列名,未写出的列名必须可以为null

3.如果表名后没有列,表示为所有的列插入值,就算自增长的列也必须设置一个null

​ insert into 表名[(列名1,列名2…)] select 对应列数据 from 另一个表名;

-- 添加年级
insert into grade values(3,"大三","大三是一个花儿开放的阶段,学习升华");
insert into grade(id,name) values(4,"大四"); 
insert into grade(id,remark) values(5,"研究生的存在"); 
-- 将grade表中的数据复制插入到grade1这个表
insert into grade1 select * from grade;
1
2
3
4
5
6

2.修改,删除数据

​ 修改语法: update 表名 set 字段1=值1,字段2=值2…[where 字段=值]

​ 删除语法: delete from 表名 [where 字段=值]

-- 修改年级
update grade set name="高级" where id=3;
-- 修改如果不给where,会修改所有行的数据
update grade set name="高级",remark="开始人生的初始阶段";
-- 通过条件,删除年级
delete from grade where id=0;
-- 删除表中所有数据
delete from student;
delete from grade;
1
2
3
4
5
6
7
8
9

delete truncate drop

delete 删除表的数据,结构还在

truncate 删除表结构,但是表空间还在

drop 删除表结构及表空间

3.添加自增

​ auto_increment 表示自增,默认从1开始,如果你给第一行数据设置了id值,会从设置的值开始自增

create table teacher(
	id int(8) PRIMARY key auto_increment,		-- auto_increment 设置自增长
	name VARCHAR(20),
	sex char(2) not null DEFAULT '男',  -- 设置默认值
	age int(4),
	phone char(11),
	address VARCHAR(50)
)
1
2
3
4
5
6
7
8

DQL查询

​ 查询: 在表中通过sql的不同,在表中筛选出不同的内容

​ * 表示查询所有内容及结构, 所以在工作中不建议使用

语法: select * from 表名;
整体语法: select 字段名列表
		from 表名列表
		where 条件列表
		group by  分组字段
		having  分组后条件查询字段
		order by  排序字段
		limit   起始行下标,查询条数
1
2
3
4
5
6
7
8

1.基本查询

select 字段名列表 from 表名列表
1

-- 查询所有学生
select * from student
-- 查询学生姓名,性别,地址
select name,sex,address from student;
-- 如果列名看不懂? 可以为列名设置别名 关键字 as
select name as 姓名,sex as 性别,address as 地址 from student;
select name 姓名,sex 性别,address 地址 from student;
-- 在SQL中字符串使用单引号,结构上使用字符串需要双引号
select name "姓 名",sex "性 别",address "地 址" from student;
1
2
3
4
5
6
7
8
9

2.去重查询

-- 查询学生信息,去除重复地址  distinct表示去重
-- 去重查询,主要用于获取到表中非重复数据的个数
select DISTINCT sex from student;
1
2
3

3.计算列

​ 一般可以使用运算符计算一些列的值,(一般只会执行数字的计算)

​ null无法进行计算

​ ifnull(表达式1,表达式2): null不参与运算,表达式1 判断哪个字段为null

​ 如果为null,替换为第二个字段

-- 查询出用户资金,每个资金加500
select name,money+500 from user
-- ifnull的形式去处理null值
select name,ifnull(money,0)+500 from user;
1
2
3
4

4.带条件查询

条件查询:
	1.关键字 where
	2.运算符:  得到的结果为true查询出来,false则不需要
		> < = >= <= !=与<>相同
		between ... and...  表示为两者之间包含边界
		in   匹配任意一个内容
		like   像.....一样(模糊查询)
				占位符  _ 单个任意一个字符   %:0到多个字符
		is null 表示为null   is not null表示不为null
	逻辑运算法  and并且   or或者  !或not 非
1
2
3
4
5
6
7
8
9
10

-- 查询学生年龄大于20岁的学生
select * from student where age>20;
-- 查询性别为男的学生
select * from student where sex='男';
-- 查询性别不为男和女的学生
select * from student where sex!='男' and sex!='女';
-- 查询 年龄在19--21之间的学生
select * from student where age BETWEEN 19 and 21;
-- 查询性别不为null的学生
select * from student where sex is not null
-- 查询姓名中带第二个字符为'松'学生
select * from student where name like '_松%'
-- 查询姓名以'马'开头的学生
select * from student where name like '马%'
-- 查询出地址在广东,年龄大于20的学生
select * from student where age>20 and address like '广东%'
-- 年级为2的学生
select * from student where gid = 2;
-- 查询姓名是3个字的学生
select * from student where name like '___';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

函数查询

​ 函数查询: 使用SQL中设定好的一些功能函数进行数据的查询及转换

​ 函数查询分为: 单行函数查询和组函数查询

单行函数

​ 单行函数: 操作数据行,接收一个函数操作结果,只对一行数据进行操作,并返回一个结果

​ 单行函数: ifnull 字符函数 数字函数 转换函数

字符函数:

​ length() 获取字符串长度

​ concat(str…) 字符串之间的连接

​ upper(str) 将字符转换大写

​ lower(str) 将字符转换为小写

​ substr(str,index[,len]) 截取字符串 1.字符串 2.开始下标 3.截取长度

​ reverse(str) 逆序字符串

-- 单行函数: 字符函数
-- 获取到ename的名称长度 length
select LENGTH(ename) 长度,ename 名称 from emp;
-- 拼接内容  concat
select concat(ename,'的工资为:',sal,', 奖金为:',ifnull(comm,0)) 描述 from emp;
-- 转换大写
select upper(ename) 名称 from emp;
-- 转换为小写
select LOWER(ename) 小写 from emp;
-- 截取字符串
select substr(ename,4,3) from emp;
-- 反转字符串
select REVERSE(ename) from emp;

-- 查询出用户名中带s的员工信息,忽略大小写
select * from emp where lower(ename) like '%s%'
-- 查询出名称长度大于4的员工信息
select * from emp where length(ename) > 4
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

数学函数:

​ round() 四舍五入 ceil() 向上取整 floor()向下取整 mod()取余 truncate()截断

-- 数学函数
-- form dual  dual是默认表,空表,用于补全结构
-- 四舍五入 第二个参数为小数点位数,如果是负数向前移动
select row'w'wund(1.354) from dual;
select ROUND(3.14159,3) from dual;
select round(256.1854,-2) from dual;
-- 向上取整
select C EIL(1.195) from dual;
-- 向下取整
select floor(9.999) from dual;
-- 截断
select TRUNCATE(1.59999,2) from dual;
select TRUNCATE(9999,-2) from dual;
-- 随机数
select TRUNCATE(RAND()*10+1,0) from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

日期转换函数

​ %Y 年 %m月 %d日 %H24小时 %h12小时 %i分 %s秒

​ date_format(日期,格式) 将日期转换为指定格式字符串

​ str_to_date(str,格式) 将指定格式的字符串转换为日期

-- 日期转换为指定格式字符串
-- date_format
select * from emp;
select e.empno,e.ename,e.job,e.mgr,DATE_FORMAT(e.hiredate,'%Y年%m月%d日') 入职日期,e.sal,e.comm,e.deptno
from emp e
select e.empno,e.ename,e.job,e.mgr,DATE_FORMAT(e.hiredate,'%Y/%m/%d') 入职日期,e.sal,e.comm,e.deptno
from emp e
-- 字符串转换为日期
select STR_TO_DATE('2022年10月12日','%Y年%m月%d日') from dual;
select STR_TO_DATE('2022/10/12','%Y/%m/%d') from dual;
select STR_TO_DATE('12/08/2022','%d/%m/%Y') from dual;
1
2
3
4
5
6
7
8
9
10
11

组函数

​ 组函数: 对一组(所有行)数据进行操作,并返回一个结果

​ 组函数分为: max() min() sum() avg() count()

-- 组函数在自动运算的过程中会主动略过null
-- 聚合函数时,一般采用主键
select max(comm) 最高奖金,min(comm) 最低奖金,sum(comm) 奖金总和,avg(ifnull(comm,0)) 平均奖金,count(empno) 总人数
from emp;
1
2
3
4

分组查询

​ group by 分组查询,将需要与组函数一起查询的内容进行分组

​ 如果希望得到每个部门的平均工资,而不是所有员工的平均工资,需要把这个数据表按照部门分组,得到每一个部门的平均工资,返回给每一个部门

格式:  select 组函数,列名
		from 表名 [where 条件]
		group by 列名
		having 分组之后的条件
		order by 排序列(asc升序 desc逆序)
1
2
3
4
5

-- 查询出每个部门的平均工资
select avg(sal) avg,deptno
from emp
group by deptno

-- 查询出每个职位的人数
select job,count(empno) 人数
from emp
group by job
1
2
3
4
5
6
7
8
9

​ 注意: 在查询列中出现的单个列,在group by中必须出现,但是group by中出现的列,在查询列中可以没有.

分组后再次进行过滤

having 分组后再次过滤

​ having 再次过滤条件中可以使用组函数,where条件中不可以使用组函数

-- having分组后再次过滤
-- 查询出部门平均工资,且求出大于2000的部门
select deptno,avg(sal)
from  emp
group by deptno
having avg(sal)>2000
1
2
3
4
5
6

查询排序

​ 将查询后的属性,进行指定顺序的排列

​ 关键字: order by

​ 排序分为 自然排序和逆序 单列排序,多列排序

​ 自然排序使用 asc默认 逆序为desc

-- 查询所有员工信息,使用部门编号逆序
-- 逆序  orderby 列名 desc
select * from emp order by deptno desc
-- -- 查询所有员工信息,使用部门编号逆序,工资升序
select * from emp order by deptno desc,sal;

-- 查询出各个管理手下员工的最低工资,其中最低工资不能低于1200,没有管理者的员工不计算在内
select min(sal) 最低工资,mgr
from emp
where mgr is not null
group by mgr
having min(sal)>1200
order by 最低工资
1
2
3
4
5
6
7
8
9
10
11
12
13

多表查询

​ 为什么使用连表查询

​ 在实际工作应用中,一个系统的表存在多个,且相关联的表也会存在多个,当需要一次获取多个表信息时,需要使用多表联查

​ 什么是连表查询

​ 两个表以上的连接查询,叫做连表查询,但连表查询通常建立在相互关系的父子表中

​ 笛卡尔积

​ 指在关联查询过程中,两个表的每一行数据之间进行互相交叉关联,查询的数量为:表1数量*表2数量,这种错误叫做笛卡尔积

​ 笛卡尔积产生的条件:

​ 1.省略连接条件 连接条件无效

​ 2.所有表中的所有数据互相连接

等值查询

​ 等值查询连接查询中最常见的查询,通常关联关系中的量表之间等值连接

​ 使用外键列匹配主键表中的主键列查询,条件的个数为 表的个数-1

-- 等值查询,将主外键进行关联查询,两者互相匹配上了才查询出来
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
-- 等值条件
where e.deptno = d.deptno
1
2
3
4
5

连接查询

​ 连接查询时,使用关键字join连接的一种查询

​ 连接查询分为: 内连接查询 外连接查询: 左外连接和右外连接查询

内连接查询

​ 这种查询通过连接的方式实现一 一匹配查询

格式: select 列 from 表1 [inner] join 表2 on 表1.外键 = 表2.主键

-- 内连接查询
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e inner join dept d
on e.deptno = d.deptno;
-- 可以省略inner
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e join dept d
on e.deptno = d.deptno;
1
2
3
4
5
6
7
8
9
10

外连接查询

​ 外连接查询表示,除连接匹配查询的内容外,还会查询出对应表中无连接数据

左外连接查询

​ 在左外连接查询过程中,除等值条件查询内容外,以关键字左边这种表为主,显示所有内容

​ 关键字 left [outer] join

格式:   select 列 from 表1 left [outer] join 表2 on 表1.外键 = 表2.主键

-- 左外连接查询
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e left join dept d
on e.deptno = d.deptno;
1
2
3
4
5
6

右外连接查询

​ 在左外连接查询过程中,除等值条件查询内容外,以关键字右边这种表为主,显示所有内容

关键字 right [outer] join

格式:   select 列 from 表1 right [outer] join 表2 on 表1.外键 = 表2.主键

-- 右外连接查询
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e right join dept d
on e.deptno = d.deptno;
1
2
3
4
5
6

子查询

​ 子查询: 在查询中嵌套其他的查询语句,这种查询叫做子查询

​ 嵌套的查询使用( )括起来,优先执行,并将查询的结果给主查询使用

语法: select 列,[子查询] from 表名[,子查询] where 条件=(子查询) group by... having 子查询条件
1

​ 子查询根据返回的结果,可以分为单行子查询,多行子查询,但是查询的结果只能有1列

单行子查询

​ where中使用子查询

	-- 单行子查询
-- where中使用子查询
-- 查询出最高工资的员工信息
select max(sal) from  emp;
select * from emp where sal= 5000;
-- 子查询
select * from emp where sal= (
								select max(sal) 
								from  emp);
								
-- 查询RESEARCH部门所在员工
select * from emp
where deptno = (select deptno from dept
								where dname='RESEARCH')
-- 查询职位与JONES,工资大于WARD
select * from emp
where job=(select job from emp1
					where ename='JONES')
				and 
					sal>()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

from与select中使用子查询

-- from中使用子查询结果作为一个表
-- 查询出员工信息,部门以名称展示
select e.empno,e.ename,e.job,e.hiredate,e.sal,e.comm,d.dname
from emp e,(select deptno,dname from dept) d  -- 本身没做意义查询,只是体现结构
where  e.deptno = d.deptno
-- select使用子查询
-- 查询出每个部门员工的个数
-- 查询部门信息,但是会对应emp表查询出的人数
select d.*,(select count(empno) from emp e where e.deptno = d.deptno)
from dept d
1
2
3
4
5
6
7
8
9
10

having中使用子查询

-- 查询出每个部门的平均工资,且求出大于员工平均工资的部门
select deptno,round(avg(sal),2) avg
from emp
group by deptno
having avg>(select avg(sal) from emp)
-- 查询出最低工资大于30号部门最低工资的部门编号,和最低工资
select deptno,min(sal)
from emp
group by deptno
having min(sal)>(select min(sal) from emp where deptno=30)
1
2
3
4
5
6
7
8
9
10

多行子查询

​ 子查询查询出来的结果不止一行,会出现多个数据,那么这种子查询叫做多行子查询

​ 多行子查询关键字: in all any

​ in 匹配集合,只要匹配到数据都能查询出来

-- in
-- 查询出RESEARCH和ACCOUNTING部门的员工
select * from emp where deptno in(select deptno from dept where dname='RESEARCH' or dname='ACCOUNTING')
-- 查询出不是RESEARCH部门的所有员工
select * from emp where deptno in(select deptno from dept where dname!='RESEARCH')

1
2
3
4
5
6

​ all 全部 大于:比全部都大 小于:比全部都小

-- all
-- 查询出比30号部门所有工资高的员工
select * from emp where sal>all(select sal from emp where deptno=30)
-- 查询出比30号部门所有工资低的员工
select * from emp where sal<all(select sal from emp where deptno=30)
1
2
3
4
5

​ any 任意 大于:比任意一个数大 小于:比任意一个数小

-- any
-- 查询出比30号部门任意员工工资高的员工信息
select * from emp where sal>any(select sal from emp where deptno=30)
-- 查询出比30号部门任意员工工资低的员工信息
select * from emp where sal<any(select sal from emp where deptno=30)
-- 查询出其他职位比MANAGER任意员工工资高的员工信息
select * from emp where sal>any(select sal from emp where job='MANAGER') and job!='MANAGER'
1
2
3
4
5
6
7

分页查询

​ 分页查询: 当数据过多,一次展示不完时,使用分页查询,进行快速查询

​ 关键字: limit

语法:  select * from 表[,表2]
	  where 条件
	  ........
	  limit 起始下标,页面大小
1
2
3
4

-- 分页查询limit
-- 查询所有员工信息,每页3条,查询第2页
-- 下标0开始,行数1开始: 上一页的最大行就是当前页的起始下标
-- 算法:当前页下标 = (当前页数-1)*页面大小
select * from emp limit 6,3;
1
2
3
4
5

联合查询

​ 为了合并多个select语句的结果,可以使用集合操作符,集合操作符包括 union ,union all

​ 当列的个数,列的顺序,列的数据类型一致时,我们称这两个结果集结构相同,只有相同结构的结果集才能做集合

语法: select 查询内容
	union [all]
	select 查询内容
1
2
3

​ union 会自动去掉合并后的重复记录

-- union联合查询 去掉重复数据
-- 查询出入职日期在1981年, 工资大于1500的员工
select * from emp where DATE_FORMAT(hiredate,'%Y')='1981'
union
select * from emp where sal>1500

-- union all 联合  显示联合查询的所有数据
select * from emp where DATE_FORMAT(hiredate,'%Y')='1981'
union all
select * from emp where sal>1500
union all
select * from emp where deptno=10

1
2
3
4
5
6
7
8
9
10
11
12
13