mySQL教程 第4章 数据查询

第4章 数据查询

mySQL运算符

这些运算符在SQL查询中用得到。

算数运算符

+ 加

- 减

* 乘

/ DIV 除

% MOD 取余数

比较运算符

= 等于

<> != 不等于

<

<=

>

>=

Between 指定范围

In 存在于集合

Is null 为NULL

Is not null 不为空

Like 通配符

Regexp rlike 正则表达式

比如

比较运算符，返回的结果 真为1，假为0

运算符优先级

最高------------------------------------------à最低

！ () * / div % mod - + = <>= like between case when then else not and or

单表查询

查询所有列所有行

select * from TStudent

查询指定列

select studentID,Sname,Sex,cardID from TStudent

为列指定别名

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号 from TStudent

指定查询条件

查询条件中可以使用< > <= >= != 比较运算符

1. 查询网络班学生

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级

from TStudent where class='网络与网站开发'

2. 查询网络班性别是女的学生

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级

from TStudent where class='网络与网站开发' and sex='女'

3. 查找软件测试班或性别是女的学生

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级 from TStudent where class='软件测试' or Sex='女'

4. 使用like模糊查询

使用字符比较符 like

% 0个或多个字符串

_ 任何单个的字符

[]在指定区域或集合内的任何单个字符

[^]不在指定区域或集合内的任何单个字符

查找姓名中含有“立”字的学生

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级 from TStudent where Sname like '%茂%'

思考：查找姓名最后一个字是“茂”的同学，写出SQL语句。

5. 查找姓名是“韩立刚”，“韩旭”的学生

记下上面查询结果的两个名字。查找这两个名字的记录。

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级 from TStudent where Sname in ('赵俊茂','魏清茂')

6. 查找出1975年到1980年出生的学生

使用比较操作符 = > < >= <= <>

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级,Birthday 生日

from TStudent where Birthday>'1980' and Birthday<'1985'

7. 使用关系运算符

关系运算符优先级 not and or 如下面的例子 去掉括号结果一样

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级,Birthday 生日

from TStudent where Birthday>'19820101' and Birthday<'19841230' or sex='女'

通过使用括号更运算顺序

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级,Birthday 生日

from TStudent where Birthday>'19820101' and (Birthday<'19841230' or sex='女')

8. 查询在一定范围内的值

查找生日在1985年到1990年之间的学生

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级,Birthday 生日

from TStudent where Birthday BETWEEN '19850000' and '19860000'

查找不在1985年到1986年出生的学生

select studentID 学号,Sname 姓名,Sex 性别,cardID ***号,class 班级,Birthday 生日

from TStudent where Birthday not BETWEEN '19850000' and '19860000'

尽量使用between而不使用and和比较操作符表示的表达式

如果想返回不在指定区域的行时，使用not between。这样会降低数据查询的速度。

9. 查询空值

insert into TStudent (studentid,sname) values ('19999','张永超')

select * from TStudent where studentid='19999'

可以看到没有插入的列值为Null

select * from TStudent where cardID is NULL

你绝不要这样写

select * from TStudent where cardID=''

使用is not null来查询指定列中非空的行

select * from TStudent where cardID is not NULL

格式化结果集

1. 排序

默认是降序 desc升序 asc 降序

select * from TStudent order by cardID

select * from TStudent order by cardID asc

按两列排序 先按班级排序 再按学号排序

select * from TStudent order by Class,studentid desc

以下命令就是按第7列和第1列排序，输出结果和以上的一样

select * from TStudent order by 7,1 desc

2. 消除重复的行

以下命令查看有几个班

select DISTINCT class 班级 from TStudent

多表查询

在TStudent表插入两个学生

insert into TStudent values ('90006','张勇','男','132302198903044565','19880203','[e-mail]','JAVA','20120803');

insert into TStudent values ('90007','赵洁','女','132302198905044565','19880503','[e-mail]','JAVA','20120803')

这两个学生没有成绩

3. 使用Join内连接多张表

内连接使用inner关键字，可以省去。

没有成绩的学生没有列出来。

使用外键匹配可以讲多个表组成一张大表，可以看到组成的大表记录条数4个学生*2科

select a.*,b.*,c.* 分数 FROM `TStudent` a inner join `TScore` b on a.`StudentID`=b.`StudentID` inner join `TSubject` c on b.`subJectID`=c.`subJectID`

从以上三个表组成的大表找到计算机网络课程分数大于80分的记录

select sname 姓名,sex 性别,c.`subJectName` 学科,b.`mark` 分数 FROM `TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject` c on b.`subJectID`=c.`subJectID` where c.`subJectName`='计算机网络' and b.`mark`>80

可以看到输出结果来自三张表

4. 左连接

做连接使用left有连接使用right

select a.*,b.* FROM `TStudent` a left join `TScore` b on a.`StudentID`=b.`StudentID`使用左连接可以看到没有成绩学生也出现在表中

思考：左连接

5. 自连接

自己连接自己，比如查找重名的学生

select a.studentid,a.sname,b.studentid,b.sname from TStudent a join TStudent b on a.sname=b.sname where a.studentid<>b.studentid

6. 使用子查询

使用子查询，输出结果只能来自一张表，其他表作为查询的条件。

为什么使用子查询---子查询可以把一个复杂的查询分解成一系列逻辑步骤，这样就可以用一个单个的语句解决复杂的查询问题。

为什么使用连接而不使用子查询---执行效力差不多，子查询可能要求查询优化器执行额外的操作，比如排序，而这些操作将会影响查询的处理策略。

select sname 姓名 from `TStudent` where Studentid in (select StudentID from `TScore` where mark>90)

以下SQL语句子查询中又嵌套了子查询，查询计算机网络，输出结果只能来自一个表。

select sname 姓名 from `TStudent` where Studentid in (select StudentID from `TScore` where mark>90 and subjectID in (select subjectID from `TSubject` where subJectName='计算机网络'))

7. 使用any关键字

创建两个表

create table tb1 (num int not null);

create table tb2 (num int not null);

插入数据

insert into tb1 VALUES (2),(5),(13),(25),(32);

insert into tb2 VALUES (6),(8),(20),(43),(70),(4);

查找tb1，条件是只要值大于tb2的任何一个值就可以

select num from tb1 where num>any(select num from tb2)

8. 使用关键字all

返回tb2所有值都小的值

select num from tb1 where num<all(select num from tb2)

9. 使用exist关键字

如果成绩表中的分数有大于80分的记录，就返回TStudent表中的第一条记录

当然也可以使用not exist

select * from `TStudent` where EXISTS (select * from `TScore` where mark>80) limit 1

有大于100分的学生，就查出学生记录。

select * from `TStudent` where EXISTS (select * from `TScore` where mark>100) limit 1

10. 使用union合并查询结果

查找tb1和tb2大于40的数据

Select num from tb1 where num>40 union select num from tb2 where num>40

数据分组和汇总

11. Group by进行数据汇总

如果使用聚集函数，则将对表中的所有记录的某个字段进行汇总，然后生成单个的值。如果想生成多个汇总值，同时使用聚集函数和group by 语句，联合使用having和group by子句能够使结果集只包含满足条件的记录。

计算各个班“计算机网络”平均分

select a.Class 班级,AVG(b.`mark`) 计算机网络平均分 from `TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject` c on b.`subJectID`=c.`subJectID` where c.`subJectName`='计算机网络' group by a.`Class`

Group by 必须和集合函数结合使用

12. 联合使用group by子句与having子句

Having 相当于条件

查找计算机网络课程平均分大于80分的班级

select a.Class 班级,AVG(b.`mark`) 计算机网络平均分 from `TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject` c on b.`subJectID`=c.`subJectID` where c.`subJectName`='计算机网络' group by a.`Class` having AVG(b.`mark`)>75

13. 思考题：统计各个班级所有科目的总分。

14. 查询指定的行数

使用limit显示前2行数据

select StudentID,Sname,sex,cardID from `TStudent` limit 2

显示从第2条记录后面的3条记录

select StudentID,Sname,sex,cardID from `TStudent` limit 2,3

15. 使用聚集函数统计行数

Count函数不统计Null记录

select count(*) from `TStudent`;

select count(cardID) 登记了***的学生数量 from `TStudent`，没有登记***不统计。

16. 思考:统计男生数量

使用正则表达式

一个正则表达式中的可以使用以下保留字

符号

说明

^

所匹配的字符串以后面的字符串开头

$

所匹配的字符串以前面的字符串结尾

.

匹配任何字符（包括新行

a*

匹配任意多个a（包括空串)

a+

匹配任意多个a（不包括空串)

a?

匹配一个或零个a

de|abc

匹配de或abc

(abc)*

匹配任意多个abc（包括空串)

[a-dx]

匹配“a”、“b”、“c”、“d”或“x”

[^a-dx]

匹配除“a”、“b”、“c”、“d”、“x”以外的任何字符。“[”、“]”必须成对使用

 

17. 查询特定字符或字符串开头的记录

select sname,email from `TStudent` where email REGEXP '^KY'

18. 查询以特定字符或字符串结尾的记录

select sname,email from `TStudent` where sname REGEXP '富$'

19. 使用*和+来匹配字符串中的多个字符

*匹配前面字符任意多次，包括0次，+匹配前面字符至少一次。

select sname,email from `TStudent` where email REGEXP '^TB*'

select sname,email from `TStudent` where email REGEXP '^TB+'

20. 匹配指定字符串

select sname,email from `TStudent` where email REGEXP 'BZ'

21. 匹配字符串的任意一个

select sname,email from `TStudent` where emai

l REGEXP '[BZ]'