软件设计师知识点汇总

grant createtab on database dbname to user3 revoke update(sno) on table tbname from user4

求选修了课程名J的姓名：select sname from s where sno IN select sno from sc where cno IN select cno from c where cname='J'

求不选C3课程的姓名：select sname from S where NOT EXISTS (select * from sc where sc.sno=s.sno and cno='C3')

求选修了全部课程的姓名:select sname from S where NOT EXISTS (select * from C where NOT EXISTS (select * from SC where sno=s.sno and cno=c.cno) )

求至少选修了学生S2所修课程的学生姓名：select DISTINCT SNO from SC x where NOT EXIST (select * from SC y where y.sno='s2' and NOT EXISTS ( select * from sc z ehere z.sno=x.sno and z.cno=y.cno))

求选修了C2课程的学生选修的其他课程号：select cno from SC x where CNO<>'c2' and SNO IN select SNO from sc y where y.cno='c2'

求定购了bid='123'图书的用户定购的其他图书(表orders orderlist)：select bid from orderlist A where NOT exists (select * from orders B where A.orderbum=B.orderbum and B.cid NOT IN (select cid from orderlist C,orders D where D.cid='123' and c.ordernum=D.ordernum))

按学号给出每个学生选修课程的门数：select sno,count(CNO) from sc group by SNO

查某工程至少用了3家供应商供应零件的平均数（表S、P、J、SPJ）：select JNO,avg(QTY) from SPJ group bu JNO having count(distinct(SNO))>=3 order by JNO DESC

规范化1NT:没有表中表，2NT消除了1NT中非主属性对码的部分函数依赖即每一个非主属性完全依赖于全部的码（X->Y即Y依赖X）、3NT消除了非主属性对码传递依赖、BCNF消除了主属性对码的部分和传递依赖、4NT表中没有多值依赖

事务的四个特征：原子性、一致性（数据不会因事务而破坏）、隔离性（事务独立运行）、持久性（事务一旦提交）。

BEGIN TRANSACTION ; COMMIT;ROLLBACK

并发控制的主要技术是封锁，三级封锁协议：1级可解决丢失更新问题；2级可解决读脏数据；3级防止丢失更新、不读脏数据、防不可重复读 建立冗余数据的方法是数据转储和登记日志文件。

13