SQL语言的基础操作（二）

上机实验二——完整性约束的实现

 

为Student表创建一插入和更新触发器tri_ins_upd_student：当插入新的学生或者更新学生所在班级时，检查该班级的学生人数有没有超过40人，如果没有则插入或者更新成功，如果超出40人，操作回滚。

 CREATE TRIGGERtri_ins_upd_student

 ON Student

 AFTER INSERT,UPDATE

AS

 

      IF ((

           SELECT COUNT(Clno)

            FROM Student

            WHERE Clno =(

                         SELECTClno

                          FROM inserted)) > 40)

 

 BEGIN

         PRINT '人数超出限制'

          ROLLBACK TRANSACTION

 END

 

 

为成绩管理数据库中的Student表创建一触发器：当向表中插入或删除记录时，修改Class表中相应班级的人数。

 CREATE TRIGGERtri_ins_del_student

 ON Student

 AFTER INSERT,DELETE

AS

 IF UPDATE(SNo)

      UPDATE Class

       SET Number = Number+ 1

       WHERE Clno =(

                    SELECTClno

                      FROM INSERTED)

 ELSE

        UPDATEClass

        SET Number = Number- 1

        WHERE Clno =(

                     SELECTClno

                      FROM DELETED) 

 

 

为CLass表再建一更新触发器：当更新班长学号时，检查新输入的学号是否为同一班级的学生学号，若不是，给出适当的提示信

 CREATE TRIGGERtri_upd_student

 ON Class

 AFTER UPDATE

AS

 IF UPDATE(Monitor)

   IF (

        SELECT Monitor

         FROM INSERTED) NOTIN(

                              SELECTSNo

                                FROMStudent

                                WHEREClno =(

                                             SELECTClno

                                              FROM DELETED))

    BEGIN

         PRINT '班级里没有这位学生'

          ROLLBACK TRANSACTION

    END

   

 

 

上机实验三——SELECT语句的使用(一)

 

找出所有被学生选修了的课程号；

SELECT Cno

FROM Grade

 

找出01311班女学生的个人信息； 

SELECT SNo,Sname, Ssex, Sage

FROM Student

WHERE (Clno= 01311) AND(Ssex = '女')

 

找出01311班、01312班的学生姓名、性别、出生日期；

SELECT Sname,Ssex, Sage

FROM Student

WHERE (Clno= 01311) OR(Clno = 01312)

 

找出所有姓李的学生的个人信息；

SELECT *

FROM Student

WHERE SnameLIKE '李%'

 

 

找出学生李勇所在班级的学生人数；

SELECT Number

FROM Class

WHERE Clno=(

SELECT Clno

FROM Student

WHERE Sname='李勇')

 

找出课程名为操作系统的平均成绩、最高分、最低分；

SELECT AVG(Gmark)AS AVG,MAX(Gmark)AS MAX,MIN(Gmark)AS MIN

FROM Grade

WHERE Cno=(

SELECT Cno

FROM Course

WHERE Cname='操作系统')

 

找出选修了课程的学生人数；

SELECT COUNT(DISTINCTSno)

FROM Grade

 

找出选修了课程操作系统的学生人数；

SELECT COUNT(DISTINCTSno)

FROM Grade

WHERE Cno=(

SELECT Cno

FROM Course

WHERE Cname='操作系统')

 

找出2000级计算机软件班的成绩为空的学生姓名

SELECT Sname

FROM Student

WHERE (ClnoIN(

SELECT clno

FROM Class

WHERE Inyear=2000))

AND(SnoIN(

SELSCT Sno

FROM Grade

WHERE GmarkIS NULL))

 

 

试一下以下语句是否正确：

SELECT eno，basepay，service

FROMsalary

WHEREbasepay<AVG(basepay)

答：错误，应修改为

   SELECT eno，basepay，service

FROMsalary

WHEREbasepay<(

SELECT AVG(basepay)

FROM salary)

 

思考题：

什么情况下需要使用关系的别名？别名的作用范围是什么？

 

答：当为名称较长的表分配别名，以使编码较长的查询更易进行时会使用关系别名。 

别名的作用范围就是在查询中一旦分配了别名，就一定要在该查询中一直使用该别名。查询中一定不能将表的全名和其别名混合使用。

 

 

上机实验四——SELECT语句的使用(二)

找出与李勇在同一个班级的学生信息；

SELECT *

FROM Student

WHERE Clno=(

           SELECTClno

            FROM Student

            WHERE Sname='李勇')AND Sname NOT IN('李勇')

 

找出所有与学生李勇有相同选修课程的学生信息；

SELECT *

FROM Student

WHERE SNoIN(

            SELECTSno

             FROM Grade

             WHERE Cno IN(

                         SELECTCno

                          FROM Grade

                          WHERE Sno =(

                                    SELECTSNo

                                     FROMStudent

                                     WHERESname='李勇')))AND Sname NOT IN('李勇')

 

找出年龄介于学生李勇和25岁之间的学生信息；

SELECT *

FROM Student

WHERE Sage< 25 AND Sage >(

                           SELECTSage

                             FROMStudent

                             WHERESname='李勇')

 

找出选修了课程操作系统的学生学号和姓名；

SELECT SNo,Sname

FROM Student

WHERE SNoIN(

             SELECTSno

              FROM Grade

              WHERE Cno =(

                         SELECT Cno

                          FROM Course

                          WHERE Cname='操作系统'))

 

找出所有没有选修1号课程的学生姓名；

SELECT Sname

FROM Student

WHERE SNoNOT IN(

                 SELECTSno

                  FROM Grade

                  WHERE Cno =('1'))

 

找出选修了全部课程的学生姓名。（提示：可找出这样的学生，没有一门课程是他不选修的。）

SELECT Sname

FROM Student

WHERE NOTEXISTS(

                 SELECT*

                  FROM Course

                  WHERE NOT EXISTS(

                                    SELECT*

                                     FROMGrade

                                     WHERESno= Student.SNo

                                     ANDCno= Course.Cno))

 

查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；

SELECT Sno,Gmark

FROM Grade

WHERE Cno='3'

ORDER BYGmark DESC

 

查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列；

SELECT *

FROM Student

ORDER BYClno ASC,SageDESC

 

求每个课程号及相应的选课人数；

SELECT Cno,COUNT(Cno)AS'选修人数'

FROM Grade

GROUP BYCno

 

查询选修了3门以上课程的学生学号。

SELECT Sno,COUNT(Sno)AS'选修课程数'

FROM Grade

GROUP BYSno

HAVING (COUNT(Sno)> 3)

 

使用存在量词[NOT]EXISTS的嵌套查询时，何时外层查询的WHERE条件为真，何时为假。

答：子查询并不返回数据，而是进行布尔检验，看该数据是否存在。

 

思考题：

1．  用UNION或UNION ALL将两个SELECT命令结合为一个时，结果有何不同？

答：在合并结果集时，默认将从最后的结果集中删除重复的行，除非使用ALL关键字

 

2．  当既能用连接词查询又能用嵌套查询时，应该选择哪种查询较好？为什么？

答：查询涉及多个关系时，用嵌套查询逐步求解，层次清楚，易于构造，具有结构化程序设计的优点。

 

3．  库函数能否直接使用在：SELECT选取目标、HAVING子句、WHERE子句、GROUPBY列名中？

答：SELECT选取目标可以直接使用

HAVING子句可以直接使用 

GROUP BY列名不能直接使用

WHERE子句 不能直接使用