4-4子查询视频2在线视频

子查询不仅能在查询语句中使用

也可以在insert

update

delete语句中使用

下面通过案例学习它的用法

【案例9】

新建信息工程表

包含两个字段

sid sname

将student表中信息工程系

学生的学号和姓名插入到该表中

信息工程表已经创建

查看表中数据

select * from xxgc

表中没有数据

下面通过子查询来添加记录

语句可以这样写

insert into xxgc

select sid,sname from student

where department='信息工程系'

执行成功查询xxgc表中的数据

可以看到表中已经插入的数据

【案例10】

新建user表

包含两个字段

uid psw

请将student表中

所有学号导入到user表中

每行记录的psw设置为6个8

user表已创建成功

查看表中数据

select * from user

表中没有数据

下面通过子查询来添加记录

语句可以这样写

insert into user

select sid逗号

单引号888888单引号

from student

执行成功

查询xxgc表中数据

可以看到表中已经成功插入的数据

这两个案例都是往已经存在的表中

插入查询的结果

请同学们在使用时注意

插入表的字段类型和宽度与查询结果保持一致

来看【案例11】

将‘数据库’成绩低于70分的

每人增加1分

可以先查询数据库在70分以下的记录情况

SELECT * FROM score

WHERE cno IN

(SELECT cno

FROM course

WHERE cname='数据库'

ND result<=70

可以看到表中只有一条满足条件的记录

修改表中的成绩需要使用update命令

被修改的表是score

执行的两个限制条件是

课程名cname为‘数据库’和成绩低于70

但是score表中并没有cname字段

cname字段在course表中

因此需要查询出cname=数据库的cno

因此查询语句可以这样写

UPDATE score

SET result = result +1

WHERE cno IN

(SELECT cno

FROM course

WHERE cname='数据库'

括号 and result<=70

再执行上面的语句

再查看数据库中在70分以下的记录情况

可以看到成绩增加了1分

来看【案例12】

将成绩为空的设置为平均分

Select * from score

where result is null

命令如果这样写

UPDATE score

SET result=

AVG(result)WHERE result IS NULL

错误代码

1111

Invalid use of group function

聚合函数不能这样使用

改写成子查询

Update score

Set result=

(select avg(result) from score)

Where result is null

仍然出错

score表被循环引用

MySQL不支持

请同学们思考用什么办法能实现呢？

CREATE TABLE score1 LIKE score

SELECT * FROM score1

INSERT INTO score1

SELECT * FROM score

Where result is not null

SELECT * FROM score1

UPDATE score

SET result=

(SELECT AVG(result) FROM score1

WHERE result IS NULL

Select * from score

where result is null

解决这个问题我们采用了复制数据的办法

对于数据量大的表来说是不合适的

这里我们只是为了说明

update语句的set子句也可以使用子查询

通过上面的两个案例可以看出

update语句中set子句

和where子句都可以使用子查询

再来看delete语句中使用子查询的情况

【案例13】

删除‘刘明’学生的全部成绩

要删除score表中刘明的成绩记录

score表中却没有刘明所属的sname字段

sname字段在student表中

因此需要先查出刘明的学号sid

首先来查看刘明成绩信息

Select * from score WHERE sid IN

(SELECT sid FROM student

WHERE sname='刘明'）

可以看到刘明有4条成绩记录

删除成绩，语句可以这样写

DELETE FROM score

WHERE sid IN

(SELECT sid FROM student

WHERE sname='刘明')

再查看刘明成绩信息

可以看到已经被删除

接下来对本次课做个小结

本次课我们学习了子查询的概念

子查询的关键字

以及子查询在增删改查中的应用

熟练掌握子查询

能解决复杂的记录操作问题

请同学们下载素材完成操作练习

本次课就讲到这里

谢谢，再见