MySQL(四)DQL查询(select, Distinct去重, 多表查询,连接查询 join, 模糊查询 like, 自连接, 分页,分组,排序,having,子查询)

4-DQL查询数据(重点)

DQL (Data Query Language: 数据查询语言)

• 所有的查询都用select

select语法

SELECT [ALL | DISTINCT]
{* | table.* | [ table.field1 [as alias1] [,table.field2 [as alias2] ][,.....] ]}
FROM table_name [AS table_alias]
	[left | right | inner join table_name2] --联合查询
	[WHERE ...] -- 指定结果需满足条件
	[GROUP BY ...] -- 指定结果按照哪几个字段来分组
	[HAVING]    -- 过滤分组的记录需满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT 开始位置, 显示条数] -- 指定查询记录范围

4.1, 查询字段

-- 查询所有的学生  select 字段,... from 表名
SELECT * FROM `student`

-- 查询指定的字段
select studentno , studentname from student;

-- 起别名 AS , 可以给字段起 ,给表起
select studentno AS 学号 , studentname AS 姓名 from student AS s;

-- 函数 Concat(a,b)
select Concat('姓名:' , studentname) AS 新名字 from student

1.查询去重

distinct 去重

-- 查询哪些人参加了考试
select * from result; 
-- 去重
select DISTINCT `studentno` FROM result;

2,数据库列(表达式)

select VERSION(); -- 查询mysql版本
select 100*3-1 AS 结果; -- 计算
select @@auto_increment_increment -- 查询自增的步长
-- 学员成绩全部+1
select studentno,studentresult,studentresult+1 AS '加分后' from result

4.2,where 条件字句

作用: 检索数据中符合条件的值

1.逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	与
or ||	a or b a||b	或
not !	not a !a	非

• 尽量使用字母

-- 查询90-100区间的人
select * from result where studentresult >= 90 and studentresult <= 100;
select * from result where studentresult >= 90 && studentresult <= 100;
-- 使用between  一定要左小右大
select * from result where studentresult BETWEEN 90 and 100;

-- 查询除了1000学号以外的学生成绩
select * from result where studentno != 1000;
select * from result where not studentno  =  1000;

4.3,模糊查询

比较运算符

运算符	语法	描述
is null	a is null	如果操作数为null, 真
is not null	a is not null	如果操作数不为null, 真
between	a between b and c	a 在 b 和 c 直接, 真
like	a like b	a包含b , 真
in	a in (a1,a2,a3…)	a 在 (a1,a2…)中, 真

-- 查询张开头的
select studentno,studentname from student where studentname like '张%'
-- 查询张后面只有一个字的
select studentno,studentname from student where studentname like '张_'
-- 查询张后面有两个字的
select studentno,studentname from student where studentname like '张__'

-- 名字有刘的
select studentno,studentname from student where studentname like '%刘%'

-- ************IN**************
-- 查询学号包含1001,1002,1004的学生
select studentno,studentname from student where studentno in (1001,1002,1004)

-- 查询地址包含广东, 北京 的学生
select studentno,studentname from student where address in ('广东深圳','北京')

-- **********null not NULL***************

-- 查询地址为空的学生
select studentno,studentname from student where address = '' or address is null

4.4, 联表查询

4.4.1 left\ inner\ right join

学生表

成绩表

inner join

– 查交集
select s.studentno,s.studentname,r.studentresult from student as s inner join result as r where s.studentno = r.studentno

– 左连接 , 以左表为主 ,左表全部列出, 匹配右表信息
select s.studentno,s.studentname,r.studentresult from student s left join result r on s.studentno = r.studentno

​

– 右连接 , 以右表为主, 右表全部列出, 匹配左表信息
select s.studentno,s.studentname,r.studentresult from student s right join result r on s.studentno = r.studentno

– 查询出student表(学号,姓名),result表(成绩),subject(学科)
select s.studentno,s.studentname,r.studentresult,sub.subjectname from student s inner join result r on s.studentno = r.studentno inner join subject sub on r.subjectno = sub.subjectno

• 先关联两张表, 查询出来再关联下一张表

4.4.2 自连接

自己的表和自己的表连接, 核心: 一张表拆分成两张一样的表即可

拆分:

• 父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

• 子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps设计

• 操作: 查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
美术设计	ps技术

– 当成两张表来查

select a.categoryName ‘父’,b.categoryName ‘子’ from category a, category b where a.categoryid = b.pid

4.5,分页和排序

排序

-- 排序: 升序ASC, 降序 DESC
-- order by 排序
-- 查询结果成绩排序
select s.studentno,s.studentname,r.studentresult,sub.subjectname 
from student s 
inner join result r on s.studentno = r.studentno 
inner join `subject` sub on r.subjectno = sub.subjectno 
order by studentresult desc

分页

-- 分页, 每页显示五条数据
-- 语法: limit 起始值, 每页条数
-- limit 0,5  1-5
-- limit 1,5  2-6

-- 查询出考高等数学前三的学生
select s.studentno,s.studentname,r.studentresult,sub.subjectname 
from student s 
inner join result r on s.studentno = r.studentno 
inner join `subject` sub on r.subjectno = sub.subjectno 
where studentresult > 60 and subjectname like '高等数学%'
order by studentresult desc limit 3

4.6,子查询

where条件嵌套一个查询语句

-- 查询分数不小于80分的学号和姓名,科目,分数, 科目为高等数学
-- 连表查询
select s.studentno , s.studentname ,sub.subjectname,r.studentresult from student s
inner join result r on s.studentno=r.studentno
inner join `subject` sub on r.subjectno = sub.subjectno
where r.studentresult >= 80 and sub.subjectname like '%高等%' 
order by r.studentresult DESC
-- 子查询
select studentno , studentname  from student 
where studentno in (
	select studentno from result where studentresult >= 80 and subjectno in (select subjectno from `subject` where subjectname like '%高等%')
)

4.7,分组和过滤(group, having)

-- 查询不同课程的平均分, 最高分,最低分, 平均分大于80
SELECT sub.subjectname,AVG(studentresult) 平均分, MAX(studentresult) 最高分,MIN(studentresult) 最低分
FROM result r
INNER JOIN `subject` sub
on r.subjectno = sub.subjectno
GROUP BY r.subjectno
HAVING 平均分>50