SQL语言之DQL

SQL语句

准备语句

说明：

在schoolDB数据库下，在3张表：学生表tstudent、分数表tscore、课程表tsubject

一、简单查询

1、查询所有字段

*可以表示所有字段

示例：

查询学生表中的所有学生信息？

select studentid,sname,sex,cardid,birthday,email,class,ent

from tstudent;

select *

from tstudent;

回忆：

投影：从源表中选择符合条件的列（字段、属性）

选择：从源表中选择符合条件的行（记录、元组）

2、查询指定手段

• 查询单个字段
• 查询多个字段

示例：

查询学生表中的姓名、性别、邮箱？

select sname,sex,email from tstudent;---投影操作

3、查询指定记录

查询满足条件的元组（记录），使用where子句

示例：

查询班级是Java开发的学生的姓名、性别、邮箱和专业班级？

select sname,sex,email,class

from tstudent

where class = 'java开发';----投影 操作

4、带in关键字的查询

示例：

查询专业班级为java开发、大数据和网络开发的学生的信息？

select *

from tstudent

where birthday >='1983-1-1' and birthday <='1987-12-31';

select *

from tstudent

where birthday between '1983-1-1' and '1987-12-31';

5、like关键字

模糊查询

通配符：

_表示任意1个字符

%表示任意多个字符

示例：

查询学生表中所以姓'王'的学生的信息？

select *

from tstudent

where sname like '雷%';

查询姓名中有'军'这个字的学生的作息？

select *

from tstudent

where sname like '%军%';

查询1985年出生的学生的信息？

select *

from tstudent

where birthday like '1985%';

6、空值查询

空值：null值 不确定的值 空值不是0，也不是空字符串

示例：查询奖金或补助为空值的雇员信息？

select *

from emp

where comm is null;

select *

from emp

where comm is not null;

7、and 并且

示例：

查询学号小于18的，且性别为男的，并且专业班级为java开发的学生的信息。

select *

from tstudent

where studentid<18 and sex='男' and class='java开发';

8、or 或者

示例：

查询姓名中含有'生'字的，或专业班级为'大数据'的学生的信息？

select *

from tstudent

where sname like '%生%' or class ='大数据';

select sname,sex,birthday,class

from tstudent

where class ='大数据' or class ='.net' or class ='java开发';

select sname,sex,birthday,class

from tstudent

where class in('大数据','.net','java开发')；

9、去掉重复值

distinct 去掉重复值

示例：查询学生表的专业班级有哪些？

select distinct class from tstudent;

10、使用limit限制查询结果的数量

limit [位置偏移量] 行数

功能：返回指定位置的制定条数条记录

示例：查询学生表中的前5名学生的信息？

select *

from tstudent limit 5;

select *

from tstudent limit 4,6; --从第411条的位置开始取,取6条记录

11、union合并查询结果

union集合的合并

要求第一个SQL语句返回的列和第二条SQL语句返回的列数相同

select studentid,sname,from tstudent where studentid='00013'

union

select sname,studentid,sex from tstudent where sname like '雷%';

12、为表和字段取别名

字段名 [as] 别名

select studentid 学号,sname as 姓名,birthday as 出生日期

from tstudent;

表名 [as] 别名

select s.studentid 学号,s.sname as 姓名,s.birthday as 出生日期

from tstudent as s;

小结：用户感兴趣的数据来自于1个表

语法：

select 字段名1 as 别名1,字段名2 别名2,......

from 表名 [as] 别名

where 条件

二、连接查询

1、内连接查询

inner join

• 父表：主关键字所在的表
• 子表：外部关键字所在的表
• 主关键字：学生表中的学号 在一个表中至少应该有1个字段，这个字段所对应的值一不能有重复、二不能是空值，它能起到唯一标识这一条记录的作用
• 外部关键字：成绩表中的学号 在某一张表中某个字段它不是当前这个表的主关键字，但是它是另外一个表的主关键字。

注意：学生表与成绩表的关联字段 学号

示例：

查询学生的学号、姓名、科目和成绩?

select s.studentid, s.sname, sub.subjectname, sc.mark

from tstudent s,tscore sc, tsubject sub

where s.studentid = sc.studentid and sc.subjectid=sub.subj

补充：

SQL99标准语法：

表1 inner join 表2 on 连接条件

示例：

查询学生的学号、姓名、科目和成绩?

select s.studentid.s.sname.sub.subiectname.sc.mark

from tstudent as s inner join tscore as sc on s.studentid =sc.studentidinner join tsubjectassub on sc.subjectid= sub.subjectid;

连接查询：去掉重复属性的等值相连

提示：“两个表都有的字段，一般要保留父表的”。

2、外连接查询

• 左连接（以左边的表为主）

• 右连接（以右边的表为主）

--左连接

select s.*,sc.*

from tstudent s left join tscore sc

on s.studentid=sc.studentid;

--右连接

select s.*,sc.*

from tstudent s right join tscore sc

on s.studentid=sc.studentid;

3、自连接查询

插入15000条学生的记录。

call addStudent(15000);

查询学生表中同名的学生的学号和姓名？-----使用自连接查询

select a.studentid,a.sname, b.studentid, b.sname

from tstudent a inner join tstudent b

on a.sname=b.sname

where a.studentid<>b.studnetid;

select a.studentid,a.sname,b.studentid,b.sname

from tstudent a,tstudent b where a.name=b.sname and a.studentid<>b.studentid;

三、分组查询

分组查询是对数据按照某个字段或多个字段进行分组计算

1、使用聚合函数的查询

• count()统计个数

示例：统计tstudent表中每个专业班级的人数？

select class 专业班级,count(studentid) as 人数 from tstudent group by class;

说明：什么情况下使用分组查询？

①查询结果中包含聚合函数计算出的结果，如计较、求和、求平均、求最小、求最大等。

②出现了每个、各个、按…，每个谁，按谁，这个“谁”就是分组字段。

• sun()求和

示例：查询每个学生的总分？

select concat(s.studentid,' ',s.sname) as gg,sum(sc.mark) as 总分

from tstudent s, tscore sc

where s.stydentid=sc.studentid

group by gg;

• avg()求平均值

示例：求每个班平均分？

select s.class 班级,avg(sc.mark) 平均分

from tstudent s inner join tscore sc on s.tstudentid=sc.studentid

group by class;

• max()求最大值
• min()求最小值

2、多个字段分组

示例：

统计每个班每科平均分，需要按2列进行分组（class和subjectname）

select s.class 班级,subsubjectname 科目,avg(sc.mark) 平均分

from tstudent s inner join tscore sc

on s.studentid=sc.studentid

inner join tsubject sub

on sc.subjectid=sub.subjectid

group by class,subjectname;

3、使用having过滤分组

使用聚合函数计算出来的结果来过滤分组，不能跟到where子句的后面。

示例：查询平均分大于80的学生信息？

select concat(s.studentid,' ',s.sname) gg,avg(sc.mark)

from tstudent s,tscore sc

where s.studentid=sc.studentid

group by gg

having avg(sc.mark)>80;

说明：问什么聚合函数计算的条件不能放到where子句后面。

原因是SQL语句中各关键字的执行顺序

①from

②join

③on

④where

⑤group by

⑥聚合函数 count() sum() avg() max() min()

⑦having

⑧select

⑨distinct

⑩order by

4、group by 与order by一起使用

select concat(s.studentid,' ',s.sname) gg,avg(sc.mark)

from tstudent s,tscore sc

where s.studentid=sc.studentid

group by gg having avg(sc.mark)>80

order by m desc；

5、在group by子句中使用with rollup

select s.class 班级,sub.subjectname 科目,avg(sc.mark) 平均分

from tstudent s,tscore sc,tsubject sub

where s.studentid=sc.studentid and sc.subjectid=sub.subjectid

group by 班级,科目 with rollup；

上述语句能够统计每个班每个科目的平均成绩，每个的平均成绩也能统计，全部班级的平均成绩也可以统计。

小结：使用with rollup时可以检索出更多的聚合信息。

四、子查询

应用场景：用户要查询的内容来自于一个表，而相关的条件要用到另一个表；也可以使用连接方式。

1、带in关键字的子查询

示例：

查询成绩大于98的学生的信息？

(select studentid from tscore where mark>98);

等价于

select * from tstudent

where studendid in

('00007','00010','00016','00017','00018'); --外层查询的条件，使用的是子查询的结果

2、带exists关键字的子查询

exists：存在

exists关键字的后面是任何一个子查询，子查询执行结束后，系统对子查询结果进行验证，如果有结果返回则exists的结果为true，否则exists结果为false。

示例：

查询学号为01001的，并且选修了课程的学生的信息？

select * from tstudent

where studentid='00001'

and exists(select * from tscore where studentid='00001')

3、带any、some关键字的子查询

• any：任意一个
• some：任意一个

示例：

查询成绩大于98的学生的信息？

select * from tstudent

where studendid=any

(select studentid from tscore where mark>98);

子查询时也可以使用>

示例：

查询学号小于任意一个成绩大于98学生的信息？

select * from tstudent

where studendid<any

(select studentid from tscore where mark>98);

--<any只要比子查询结果中的最大的值小即可

--改some也可以

查询学号大于任意一个成绩大于98学生的信息？

select * from tstudent

where studendid>any

(select studentid from tscore where mark>98);

--<any只要比子查询结果中的最大的值小即可

--改some也可以

4、带all关键字的子查询

示例：

查询学号大于任意一个成绩大于98学生的信息？

• >all比子查询结果中最大的值要大
• <all比子查询结果中最小的值要小

select * from tstudent

where studendid>all

(select studentid from tscore where mark>98);

--'00007','00010','00016','00017','00018' 比学号为00018大的学生的记录

select * from tstudent

where studendid<all

(select studentid from tscore where mark>98);

--'00007','00010','00016','00017','00018' 比学号为00007小的学生的记录

五、使用正则表达式查询

在mysql中可以使用正则表达式来实现模糊查询。

regexp 正则表达式的模式

1、查询以特定字符或字符串开头的记录

^匹配以特定字符或字符串开头的文本(英文输入法状态下shift+6)

示例：查询姓“邓”的所有学生的记录?

select * from TStudent where sname like '邓%';

-要实现更为复杂的功能可以使用正则表达式

select * from tstudent where sname regexp '^邓';

select * from tstudent where sname regexp'^邓士

2、查询以特定字符或字符串结尾的记录

$匹配以特定字符或字符串结尾的记录

示例：查询以身份证号以36结尾学生的记录?

select * from tstudent where cardid regexp '36$';

3、用.来代替字符串中的任意一个字符

.匹配任意一个字符

示例：查询姓名中含有康字的学生信息?

select *from tstudent where sname regexp '.康.';

4、使用*和+来匹配多个字符

*匹配星号前面的那个字符任意多次，包含0次

*匹配加号前面那个字符至少1次

示例1:

查询身份证号以19开始，以6结束的学生的信息?

select *from tstudent where cardid regexp '^19.*6$';

-以19开始，中问是任意数字匹配多次，以6结束

示例2：查询身份证号中有123数字的学生的信息?

select * from tstudent where cardid regexp '.*123+.*';

-- 任意数字匹配多次：123数字至匹配1次：任意数字匹配多次

5、匹配指定字符串

|含义：或者

示例：查询姓名中包含武、尹、罗三个字符的学生的信息?

select * from tstudent where sname regexp'武|尹|罗'；

select * from tstudent where sname regexp '张武|尹进|罗树'；

6、匹配指定字符串中的任意一个

[ ]方括号 在方括号中指定一个字符集合，只匹配其中任何一个字符.

示例：

查询学生表中邮箱中含有x-z的所有学生的信息?

select* from tstudent where email regexp '[x-z]';

示例：

查询身份证号中含有1-3和7的学生的信息?

select * from tstudent where cardid regexp '[1-3, 7]';

7、匹配指定字符以外的字符

[^字符集合] 匹配不在指定字符集合中的任何字符

示例：查询身份证号不是以1-7开头的学生的信息?

select * from tstudent where cardid regexp '^[^1-7]';

--方括号外的^表示以什么开头

8、使用{M}或{M，N}来指定字符串连续出现的次数

字符串{n}表示至少匹配n次前面的字符。

字符串{m，m}表示匹配前面的字符串不少于n次，不多于次

示例：

查询身份证号中出现152并且后面有8位0-9之间的8位数字的学生信息

select * from tstudent where cardid regexp '1520[9]{8}';