查询数据

前一章已经讲完了数据库的：增删改表，和增删改数据。

https://blog.csdn.net/shinhwa96/article/details/124319033?spm=1001.2014.3001.5501

针对测试来说，日常用的最多其实的查询数据。故整理了下常用的查询语句
1.单表查询
2.分组查询
3.多表查询
4.子查询

---

一、单表查询（不带查询条件）

1.查询全表

SELECT * FROM wjh.teacher;
1

2.查询部分字段：

SELECT t_name FROM wjh.teacher;
SELECT t_name,t_depart FROM wjh.teacher;
1
2

3.查询带表达式：

SELECT s_no,s_no%2 FROM wjh.student;
SELECT s_no,if(s_no%2=0,'双号','单号') 学号 FROM wjh.student;
1
2

4.设置别名：

空格、或者as 别名

SELECT s_name,s_sex 性别 FROM wjh.student;
SELECT s_name,s_sex as 性别 FROM wjh.student;
1
2

5.排序：order by

升序：asc
降序：desc
查询语句不加关键字，默认升序

通过classid进行整体数据排序
SELECT * FROM wjh.student order by classid asc;
SELECT * FROM wjh.student order by classid desc;
1
2
3

注意，如果按照多个字段排序，且有相同排序，先按照第一顺序排序，再按第二排序

SELECT * FROM wjh.student order by classid desc,s_no asc;
1

6.查询部分数据（limit）

有时候查询数据不需要查看全部，只需要取特定几行的数据。
limit（M,N）:表示：从M行开始。显示N行

#显示前五行
SELECT * FROM wjh.student limit 5;
#显示从第5行开始，显示3行
SELECT * FROM wjh.student limit 5,3;
1
2
3
4

7.去重distinct

SELECT distinct classid FROM wjh.student;
1

注意事项：distinct 必须要用在SELECT之后。
如果是多个字段去重，前提是这多个字段都要相同才去重，也就是要满足并的关系

二、单表查询（含查询条件）

1.等值查询

在where之后加上过滤条件

SELECT * FROM wjh.student where classid=1101;
1

2.不等值查询

经常使用的：< 、<=、>、>=、!=、<>、

SELECT * FROM wjh.score where scores>'88';
SELECT * FROM wjh.score where scores< '60';
SELECT * FROM wjh.score where scores !='56';
SELECT * FROM wjh.score where scores <>'56';
1
2
3
4

3.模糊查询 like

通配符%代表任意内容，_代表匹配单个字符,不能多也不能少,就是一个字符。rlike代表正则

#查询王字开头的数据
SELECT * FROM wjh.student where s_name like '王%';
#查询以一字结尾的数据
SELECT * FROM wjh.student where s_name like '%一';
#查询含有王的数据
SELECT * FROM wjh.student where s_name like '%王%';
1
2
3
4
5
6

4.逻辑语句 and or not

优先级：and的优先级高于or

#and 全部满足
select * from wjh.score where scores='56' and s_no='103'；
#or 部分满足
select * from wjh.score where scores='56' or s_no='103'
#既有and，又有or
select * from wjh.score where scores>'60' and c_no ='3-1' or scores>'85'
#not 
#查询不含有王的数据
SELECT * FROM wjh.student where s_name  not like '%王%';
1
2
3
4
5
6
7
8
9

5.空值处理

SELECT * FROM wjh.student where s_name is null;
SELECT * FROM wjh.student where s_name is not null;
1
2

6.区间查询 between 小值 and 大值

#查询分数在【75，96】区间的数据
SELECT * FROM wjh.score where scores between 75 and 96;
#查询分数不在【75，96】区间的数据
SELECT * FROM wjh.score where scores not  between 75 and 96;
1
2
3
4

7.集合查询 in，not in

#查询分数为56,85,96的数据
SELECT * FROM wjh.score where scores  in('56','85','96')
#查询分数不为56,85,96的数据
SELECT * FROM wjh.score where scores  not in('56','85','96')
SELECT * FROM wjh.score where (c_no,scores) in (('3-1',56),('3-3',96))
1
2
3
4
5

三、分组查询

1.函数

这位博主整理的比较全，可以去看他的推文。

https://blog.csdn.net/daxiong0816/article/details/125263849

2.聚合函数

count（col） 统计个数（注意：不能统计空值）
max（col） 最大值
min（col） 最小值
sum（col）求和
avg（col）求平均值

注意：这些函数只能在select之后，不能在where之后出现。

#统计有多少个分数
SELECT count(scores) FROM wjh.score ;
#统计最高分
SELECT max(scores) FROM wjh.score ;
#统计最低分
SELECT min(scores) FROM wjh.score ;
#统计课程3-1的总分
select sum(scores) from wjh.score where c_no='3-1';
#统计课程3-1的平均分
select avg(scores) from wjh.score where c_no='3-1';
1
2
3
4
5
6
7
8
9
10

3.分组函数 Group by

确定按照什么分组，例如：查询每个班级多少人，查询每科分数总分多少

#查询每个班级多少人
select classid,count(s_no) from wjh.student group by classid;
#查询每科最高分
select  c_no,max(scores)   from wjh.score group by c_no;
1
2
3
4

4.having 的二次筛选

在分组函数后需要对数据进行二次过滤
例如：查询每科的最低分，但是只显示最低分数大于60的数据；

select  c_no,min(scores)   from wjh.score group by c_no having min(scores)>'60';
1

特殊事项：
针对普通字段的筛选，用where，在group by之前
针对聚合函数的筛选，用having ,在group by之后

Select语句完整的执行顺序
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、select 的字段；
8、使用order by对结果集进行排序。

四、多表查询

多张表之间的连接查询，具体可分为：
内连接：只选出两张表互相匹配的数据
外连接：既选出两张表匹配的数据，又包括不匹配的数据，可分为左连接和右连接。

内连接 inner join

例如查询student表和score表，得出学生和分数详情
三种写法：
1.只是用where

select s_name,c_no,scores from wjh.student ,wjh.score where student.s_no=score.s_no;
1

2.用join…on…

select s_name,c_no,scores from wjh.student  join wjh.score  on student.s_no=score.s_no;
1

3.用inner join on

select s_name,c_no,scores from wjh.student  inner join wjh.score  on student.s_no=score.s_no;
1

select s_name,c_no,scores from wjh.student  inner join wjh.score  on student.s_no=score.s_no where scores>80;
1

左连接 left join on

以左表为主（显示完整的左表）,根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null表示

select s_name,c_no,scores from wjh.student  left join wjh.score  on student.s_no=score.s_no;

1
2

查询出来的数据和内连接查询的数据就有明显区别，内连接只查出两表都匹配的数据，所有只有24条
但是左连接，以左边Student表为主，右表不匹配的以null补齐，故查询25条，曾一的课程和分数以null显示

右连接 right join on

根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配，如果找不到匹配的，用null表示

select s_name,c_no,scores from wjh.student  right join wjh.score  on student.s_no=score.s_no;
1

子查询

子查询：是多表连接查询的一种实现方式，在一个select语句的from子句或where子句中嵌套了另一个select语句，外层的select查询语句成为主查询，换句话将就是WHERE或FORM中的查询语句称为子查询

WHERE子句中的子查询：子查询返回的值作为主查询的查询条件
FROM子句中的子查询：子查询返回的是一张虚拟的表，主查询从该查询从临时表查询出满足的条件

select * from wjh.score where s_no in (select s_no from wjh.student where s_sex ='女')
1