数据库第二周

数据查询（接上节课）：

带有谓词in的子查询：

SELECT sname FROM s,sc WHERE s.sno=sc.sno AND cno='c2';
SELECT sname FROM s WHERE sno in (SELECT sno FROM sc WHERE cno='c2');//嵌套查询先做里面的SELECT

带有ANY和ALL的比较子查询：

SELECT s.sno,sname FROM s WHERE sec='男' AND s.sno=sc.sno GROUP BY s.sno HAVING AVG(grade)>ALL (SELECT grade FROM s,sc WHERE s.sno=sc.sno AND sex='女')//找男同学，平均分大于所有女同学的分

带有存在量词EXISTS子查询：

SELECT sname FROM s WHERE sno NOT IN (SELECT sno FROM sc WHERE cno='c2');
SELECT sname FROM s WHERE NOT EXISTS (SELECT * FROM sc WHERE cno='c2' AND s.sno=sc.sno);//存在量词是先做外面再做里面的

SQL语言只有存在量词，不支持全称量词：

查询所有学生都选修的课程号和课程名==没有学生不选修的课程号和课程名 \[ (\forall x)P\equiv\neg(\exist x)\neg P \]

SELECT cno,cname FROM c WHERE NOT EXISTS (SELECT * FROM s WHERE NOT EXISTS (SELECT * FROM sc WHERE sno=s.sno AND cno=c.cno))//不存在一个学生不选

SQL不支持逻辑蕴含：

求选修学号为S3的学生所修全部课程的学生号码 \[ (\forall c_y)(p \rightarrow q) \\ \equiv \neg(\exist c_y)\neg(p \rightarrow q) \\ \equiv \neg(\exist c_y)\neg(\neg p \lor q) \\ \equiv \neg(\exist c_y)( p \land \neg q) \]

SELECT sno FROM sc x WHERE NOT EXiSTS (SELECT * FROM sc y WHERE sno='s3' AND NOT EXISTS (SELECT FROM sc z WHERE sno=x.sno and cno=y.cno))

SQL的数据操作：

1.数据插入

2.数据删除

DELETE FROM<表名> WHERE
DELETE FROM s WHERE sno NOT IN (SELECT sno FROM sc)
DELETE FROM sc WHERE cno IN (SELECT cno FROM c WHERE cname='数据库原理')

3.数据修改

UPDATE<表名> SET<列名>=<表达式>
UPDATE sc SET grade=grade*1.04 WHERE grade>=70
UPDATE sc SET grade=grade*1.05 WHERE grade<70