4、DQL查询数据(最重点)

4.1、DQL

Data Query Language : 数据查询语句

①所有的查询操作都用它 Select
②简单的查询, 复杂的查询它都可以做
③数据库中最核心的语言, 最重要的语句
④使用频率最高的语句

⭐select完整语法: [可选]、{必选}

SELECT[ALL|DISTINCT|DISTINCTROW|TOP] -- 一些修饰词如distinct(去重)
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} -- 要查询的字段或者结果
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]	-- 指定结果需要满足的条件
[GROUP BY…]	-- 指定结果按照几个字段3来分组
[HAVING…]	--过滤分组的记录必须满足的次要条件
[ORDER BY…]	--指定查询记录按一个或者多个条件排序
[limit]	--{[offset]row_count | row_countOFFSET offset}] -- 指定查询的记录从哪条叫哪条

​

4.2、查询

4.2.1.简单查询

格式: select * from 表名(*是通配符,表示所有字段)
格式: select 字段名1 as 别名1,字段名2 as 别名2 from 表名 as 别名3

-- 查询所有学生 select 字段 from 表名
select * from student;-- *是通配符,表示所有字段

-- 查询指定字段 用,分隔字段
select `studentname`, `studentno` from student;

-- 别名, 给结果的字段和表起一个名字。
-- 格式: select `字段名1` as 别名1,`字段名2` as 别名2 from 表名 as 别名3
select `studentno` as 学号,`studentname` as 学生姓名 from student as 学生表

-- 函数 cancat(a,b) 字符串拼接
select concat('名字:	',`studentname`) as 新名字 from student;

​

4.2.2、去重 distinct

作用: 去除select查询出来的结果中的重复数据
格式: select distinct 字段名, from 表名;

-- 1.查询所有考过试的学生的学号(去重)
select distinct `studentno`,  from result;

4.2.3、select的应用

数据库中的表达式: 文本值,、列、Null、函数、计算表达式、系统变量……
用法: select 表达式 from 表名

select version();-- 查询数据库版本号(函数)
select 125-25*3 as 计算结果;-- 用来计算(表达式)
select @@auto_increment_increment -- 查询自增步长

-- 学员考试成绩+100分查结果
select `studentno`,`studentresult`+ 100 as '提分后' from result;

4.3、where 条件子句

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

4.3.1、逻辑运算符

格式: select studentno,studentresult from result where 条件语句;

运算符	语法
and &&	A and B A&&B
or
not !	not A !A

注意: 尽量用英文单词!!

-- 查询成绩60~90分的
select `studentno`,`studentresult` from result where studentresult>=60 and studentresult<=90; 
select `studentno`,`studentresult` from result where studentresult>=60 && studentresult<=90;
select `studentno`,`studentresult` from result where studentresult between 60 and 90;

-- 程序非1号学生的其他学生成绩
select `studentno`,`studentresult` from result where not studentno = 1;
select `studentno`,`studentresult` from result where studentno != 1;

4.3.2、模糊查询: 比较运算符

运算符	语法	描述
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	SQL匹配, 如果A匹配B, 则结果为真
in	A in (A1, A2, A3……)	假设A在A1, A2, A3……其中一个值中, 结果为真

4.3.2.1、like(需要匹配正则表达式使用)

"%"表示0~任意个字符 "_"表示一个字符
格式: select 字段1,字段2,字段3 ……from 查询的表 where 要匹配的字段 like ‘正则表达式’;

-- 查询姓身的同学
select `studentno`,`studentname` from student where studentname like '身%';
-- 查询有电话号码的同学
select `studentno`,`studentname`,`phone` from student where phone like '%';
-- 格式: select `字段1`,`字段2`,`字段3` from 查询的表 where 要匹配的字段 like '正则表达式';

4.3.2.2、in(匹配的条件选项)

格式: select 字段1,字段2,字段3…… from 查询的表 where 要匹配的字段 in(‘选项1’,‘选项2’,‘选项3’……);

-- 六、查询住在四川、甘肃、重庆、上海的同学
select `studentno`,`studentname`,`address` from `student`
where `address`in('四川','重庆','甘肃','上海');

注意: in()里面必须是具体的值
​

4.3.2.3、is not null 与 is null

格式: select 字段1,字段2,字段3…… from 查询的表 where 要匹配的字段 is null
select 字段1,字段2,字段3…… from 查询的表 where 要匹配的字段 is not null

-- 查询电话号为空的学生
select `studentno`,`studentname` from `student` where `phone`is null;

-- 查询有电话号码的学生
select `studentno`,`studentname` from `student` where `phone`is not null;

4.4、联表查询(重难点)

主要是3中情况
细分有7种情况

4.4.1、两张表联查

思考: 分析需求(要查什么字段)、分析交叉点(关键字段)

格式:select 字段1,字段2,字段3……
from 左表 as 别名1
inner join 右表 as 别名2 – inner视情况为right、left
on 别名1.关键字段=别名2.关键字段 – 查询范围
– ⭐遇到上图2或5的·情况需要添加 where 别名.key is null
​

⭐注意:① 要查询的字段中如果是关键字段或两个表都有的字段名需要用"别名.字段名"来表示
②遇到上图2或5的·情况需要添加where语句限制
③下面的代码默认了两张表的关键字段全部匹配！！！

-- 查询学生的学号、姓名、科目号、以及对应的成绩
-- 需要联两张表student 和 result

--inner
select s.studentno,`studentname`,`subjectno`,`studentresult` 
from `student` as s
inner join `result` as r
on s.studentno=r.studentno;

--right
select s.studentno,`studentname`,`subjectno`,`studentresult` 
from `student` as s
right join `result` as r
on s.studentno=r.studentno;

--left
select s.studentno,`studentname`,`subjectno`,`studentresult` 
from `student` as s
left join `result` as r
on s.studentno=r.studentno;

4.4.2、多张表联查

思考: 分析需求(要查什么字段)、分析交叉点(关键字段)、一张表一张表的来

格式:select 字段1,字段2,字段3……
from 表1 as 别名1
inner join 表2 as 别名2 – inner视情况为right、left
on 别名1.关键字段1=别名2.关键字段1 – 查询范围
inner join 表3 as 别名3 – inner视情况为right、left
on 别名2.关键字段2=别名3.关键字段2 – 查询范围
– ⭐遇到上图2或5的·情况需要添加 where 别名.·关键字段2· is null
……
​

注意： 表1与表3没有关系！！！意义是表一与表二输出的结果与表三联
where只能在最后一行

select s.studentno,`studentname`, sub.subjectno,subjectname,`studentresult` 
from `student` as s
inner join `result` as r
on s.studentno=r.studentno
inner join `subject` as sub
on r.subjectno=sub.subjectno

4.4.3、自连接(了解)

核心: 一张表拆成两张表即可

目的: 将表根据父子关系分开查询成两张表
注: categoryid表示自己的id、pid表示自己父类的id(如果pid为1说明没有父类,自己就是最高级的)
如果A的pid=B的categoryid, 则B是A的父类
分析:
格式: select 子表1.字段 as ‘父类别名’,子表2.字段 as ‘子类别名’
from 父表as 子表1,父表as 子表2
where 子表1.本id=子表2.父id; --父子之间的判断条件(不固定)

select a.`categoryname` as '父栏目',b.`categoryname` as '子栏目'
from `category`as a,`category`as b
where a.`categoryid`=b.`pid`;

​

结果:

4.5、分页(limit)与排序(order by)

排序 : ASC–升序 DESC–降序

格式:
select 字段名 from 表名
order by 字段 asc – 升序;

select s.`studentno`,`studentname`, sub.`subjectno`,`subjectname`,`studentresult` 
from `student` as s
inner join `result` as r
on s.studentno=r.studentno
inner join `subject` as sub
on r.subjectno=sub.subjectno
order by studentresult asc -- 升序

​

分页: limt 当前页面数*pagesize,pagesize

格式:
select 字段名 from 表名
limt 当前页面数*pagesize,pagesize

select s.`studentno`,`studentname`, sub.`subjectno`,`subjectname`,`studentresult` 
from `student` as s
inner join `result` as r
on s.studentno=r.studentno
inner join `subject` as sub
on r.subjectno=sub.subjectno
order by studentresult asc -- 升序
limit 0,5

4.6、子查询

原来的查询方法: where(固定值)
子查询: where(这个值是计算出来的)
子查询本质: 在where语句里面再嵌套一个子查询语句 where(select * from)
是
​

格式: select *
from 表
where *(select……)

-- 子查询
-- 查出考了Java的所有学生的信息(学号,课号,成绩)
select `studentno`,`subjectno`,`studentresult`
from 	`result`
where `subjectno`=(
			select `subjectno` 
			from `subject`
			where subjectname='Java')
order by studentresult desc;

-- 查询Java成绩大于20的学生学号和姓名
select `studentno`, `studentname`from `student`where `studentno` in(-- 查学生学号、名字
select `studentno` from `result` where `studentresult`>20 and `subjectno`=(-- 查20分以上的
select `subjectno`from `subject` where `subjectname`='Java') )-- 查Java这门课

注意: 子查询是先查里面的再查外面的

4.7 分组

-- 查询不同课程的平均分, 最高分, 最低分
select `subjectname`,avg(`studentresult`),max(`studentresult`),min(`studentresult`)
from `result` r 
inner join `subject` sub
on r.`subjectno`=sub.`subjectno`
group by sub.`subjectname` --按照学科姓名的不同来分组!!!

group by sub.字段 --按照当前的不同来分组记录!!!
​

4.8、分组之后再过滤(之前用where无法解决的情况)

having 平均分>60 – 分组之后再过滤

-- 查询不同课程中平均分＞80的课程平均分, 最高分, 最低分
select `subjectname`,avg(`studentresult`)as 平均分,max(`studentresult`),min(`studentresult`)
from `result` r 
inner join `subject` sub
on r.`subjectno`=sub.`subjectno`
group by sub.`subjectname` 
having 平均分>60 -- 分组之后再过滤