数据库原理及应用教程(第4版)源代码

第1章 数据库系统概述无源代码第2章 关系数据库【例2-5】查询计算机系的全体学生。sDept=计算机 (S)或s5=计算机 (S)（其中5为属性Dept的序号）【例2-6】查询工资高于1000元（不包括1000元）的男教师。s (Sal1000) （Sex= 男）(T)【例2-7】查询教师的姓名、教师号及其职称。TN,TNo,Prof(T)或2,1,5(T)（其中2，1，5分别为属性TN，TNo和Prof的序号）【例2-8】查询教师关系中有哪些系。Dept(T)【例2-9】查询讲授C5课程的教师号。TNo(CNo=C5(TC)【例2-11】查询讲授“数据库”课程的教师姓名。TN(sCN=数据库 (C)TCTNo,TN(T)或TN(TNo(sCN=数据库(C)TC)TNo,TN(T)【例2-13】查询选修了全部课程的学生学号和姓名。SNo,CNo(SC)CNo(C) SNo,SN(S)【例2-14】查询至少选修了C1课程和C3课程的学生学号。Sno,Cno(SC)CNo(sCNo=C1Cno=C3(C)【例2-15】查询所有学生的数据。GET W (S)【例2-16】查询所有被选修的课程号码。GET W (SC.CNo)【例2-17】查询计算机系工资高于1000元（不包括1000元）的教师的姓名和工资。GET W (T.TN,T.Sal):T.Dept= 计算机T.Sal1000【例2-18】查询S3同学所选课程号及成绩，并按成绩降序排列。GET W (SC.CNo,SC.Score):SC.SNo= S3 DOWN SC.Score【例2-19】查询一名男教师的教师号和姓名。GET W (1) (T.TNo,T.TN):T.Sex= 男【例2-20】查询一名男教师的教师号和姓名，并使他的年龄最小。GET W (1) (T.TNo,T.TN):T.Sex= 男 UP T.Age【例2-21】查询S3同学所选课程号。RANGE SC XGET W (X.CNo):X.SNo= S3【例2-22】查询S3同学所选课程名。RANGE SC XGET W (C.CN):$X(C.CNo=X.CNoX.SNo= S3)【例2-23】查询至少选修一门其课时数为80的课程的学生的姓名。RANGE C CX SC SCXGET W (S.SN):$SCX(SCX.SNo=S.SNo$CX(CX.CNo=SCX.CNoCX.CT=80)【例2-24】查询选修全部课程的学生姓名。RANGE C CX SC SCXGET W (S.SN):CX$SCX(SCX.SNo=S.SNoCX.CNo=SCX.CNo)【例2-25】求学号为S1学生的平均分。GET W (AVG(SC.Score):S.SNo= S1【例2-26】求学校共有多少个系。GET W (COUNT(S.Dept)【例2-27】把刘伟教师转到信息系。HOLD W(T.Dept):T.TN= 刘伟MOVE 信息 TO W.DeptUPDATE W【例2-28】在SC表中插入一条选课记录（S6，C1，85）。MOVE S6 TO W.SNoMOVE C1 TO W.CNoMOVE 85 TO W.ScorePUT W(SC)【例2-29】删除学号为S6的学生的信息。HOLD W(S):S.SNo= S6DELETE W【例2-30】删除全部学生的信息。HOLD W(S)DELETE W【例2-31】定义学生关系S。CREATE S (SNo=C8，SN=C20，Age=I3，Sex=C2，Dept=C20)【例2-32】查询计算机系工资高于1000元的教师的姓名和工资。RANGE OF TX IS TRETRIEVE (TX.TN,TX.Sal)WHERE TX.Dept= 计算机TX.Sal1000【例2-33】查询讲授C5课程的教师的姓名。RANGE OF TX IS TRANGE OF TCX IS TCRETRIEVE (TX.TN)WHERE TX.TNo=TCX.TNo AND TCX.CNo= C5【例2-34】把刘伟教师转到信息系。RANGE OF TX IS TREPLACE（TX.Dept= 信息）WHERE TX.TN= 刘伟【例2-35】在SC表中插入一条选课记录（S6，C2，80）。APPEND TO SC(SNo= S6，CNo= C2，Score=80) 【例2-36】删除学号为S6的学生的信息。RANGE OF SX IS SDELETE SXWHERE SX.SNo=S6第3章 关系数据库标准语言SQL【例3-1】SQL命令创建数据库CREATE DATABASE TeachON( NAME=Teach_Data, FILENAME=D:TeachData.mdf, SIZE=10, MAXSIZE=500, FILEGROWTH=10)LOG ON( NAME=Teach_Log, FILENAME=D:TeachData.ldf, SIZE=5, MAXSIZE=500, FILEGROWTH=5)【例3-2】ALTER DATABASE TeachMODIFY FILE( NAME = Teach_Data, FILEGROWTH = 20)【例3-3】ALTER DATABASE TeachADD FILE( NAME=Teach_Datanew, FILENAME=E:Teach_Datanew.ndf, SIZE=100, MAXSIZE=200, FILEGROWTH=10)【例3-4】ALTER DATABASE TeachREMOVE FILE Teach_Datanew【例3-5】DROP DATABASE Teach【例3-6】CREATE TABLE S( SNo VARCHAR(6), SN NVARCHAR(10), Sex NCHAR(1) DEFAULT 男, Age INT, Dept NVARCHAR(20)【例3-7】CREATE TABLE S( SNo VARCHAR(6) CONSTRAINT S_CONS NOT NULL, SN NVARCHAR(10), Sex NCHAR(1), Age INT, Dept NVARCHAR(20)【例3-8】CREATE TABLE S( SNo VARCHAR(6), SN NVARCHAR(10) CONSTRAINT SN_UNIQ UNIQUE, Sex NCHAR(1), Age INT, Dept NVARCHAR(20)【例3-9】CREATE TABLE S ( SNo VARCHAR(6), SN NVARCHAR(10) UNIQUE, Sex NCHAR(1), Age INT, Dept NVARCHAR(20) CONSTRAINT S_UNIQ UNIQUE(SN, Sex)【例3-10】CREATE TABLE S ( SNo VARCHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN NVARCHAR(10) UNIQUE, Sex NCHAR(1), Age INT, Dept NVARCHAR(20)CREATE TABLE C( CNo VARCHAR(6) CONSTRAINT C_Prim PRIMARY KEY, CN NVARCHAR(20), CT INT)【例3-11】CREATE TABLE SC( SNo VARCHAR(6) NOT NULL, CNo VARCHAR(6) NOT NULL, Score NUMERIC(4,1), CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo)【例3-12】CREATE TABLE SC( SNo VARCHAR(6) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES S(SNo), CNo VARCHAR(6) NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo), Score NUMERIC(4,1), CONSTRAINT S_C_Prim PRIMARY KEY (SNo,CNo)【例3-13】CREATE TABLE SC( SNo VARCHAR(6), CNo VARCHAR(6), Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score=0 AND Score 85【例3-26】SELECT SNo, CNo, ScoreFROM SCWHERE (CNo = C1 OR CNo = C2) AND (Score = 85)【例3-27】SELECT TNo,TN,ProfFROM TWHERE Sal BETWEEN 1000 AND 1500【例3-28】SELECT TNo,TN,ProfFROM TWHERE Sal NOT BETWEEN 1000 AND 1500【例3-29】SELECT SNo, CNo, Score FROM SC WHERE CNo IN(C1，C2)【例3-30】SELECT SNo, CNo, Score FROM SC WHERE CNo NOT IN(C1，C2)【例3-31】SELECT TNo, TN FROM TWHERE TN LIKE 张%【例3-32】SELECT TNo, TN FROM TWHERE TN LIKE_力%【例3-33】SELECT SNo, CNoFROM SCWHERE Score IS NULL【例3-34】SELECT SUM(Score) AS TotalScore, AVG(Score) AS AvgScoreFROM SCWHERE (SNo = S1)【例3-35】SELECT MAX(Score) AS MaxScore, MIN(Score) AS MinScore, MAX(Score)MIN(Score) AS DiffFROM SCWHERE (CNo = C1)【例3-36】SELECT COUNT（SNo）FROM SWHERE Dept= 计算机【例3-37】SELECT COUNT(DISTINCT Dept) AS DeptNumFROM S【例3-38】SELECT COUNT (Score) FROM SC【例3-39】SELECT COUNT(*) FROM SWHERE Dept=计算机【例3-40】SELECT TNo,COUNT(*) AS C_NumFROM TCGROUP BY TNo【例3-41】SELECT SNo, COUNT(*) AS SC_NumFROM SCGROUP BY SNoHAVING (COUNT(*) = 2)【例3-42】SELECT SNo, ScoreFROM SCWHERE (CNo = C1)ORDER BY Score DESC【例3-43】SELECT SNo, CNo, ScoreFROM SCWHERE CNo IN (C2, C3, C4, C5)ORDER BY SNo, Score DESC【例3-44】（1）方法1：SELECT T.TNo,TN,CNoFROM T,TCWHERE (T.TNo = TC.TNo) AND (TN=刘伟)（2）方法2：SELECT T.TNo, TN, CNoFROM T INNER JOIN TCON T.TNo = TC.TNoWHERE (TN = 刘伟)（3）方法3：SELECT R1.TNo R2.TN, R1.CNo FROM(SELECT TNo,CNo FROM TC ) AS R1INNER JOIN (SELECT TNo ,TN FROM TWHERE TN=刘伟) AS R2ON R1.TNo=R2.TNo多字段分组查询【例3-45】SELECT S.SNo,SN,CN,ScoreFROM S,C,SCWHERE S.SNo=SC.SNo AND SC.CNo=C.CNo【例3-46】SELECT C.CNO,CN,COUNT(SC.SNo) as 选课人数FROM C,SCWHERE SC.CNo=C.CNoGROUP BY C.CNo,CN【例3-47】SELECT S.SNo,SN,CN,ScoreFROM SLEFT OUTER JOIN SCON S.SNo=SC.SNoLEFT OUTER JOIN CON C.CNo=SC.CNo【例3-48】SELECT *FROM S CROSS JOIN C【例3-49】方法1：SELECT X.TN,X.Sal AS Sal_a,Y.Sal AS Sal_bFROM T AS X ,T AS Y WHERE X.SalY.Sal AND Y.TN=刘伟方法2：SELECT X.TN, X.Sal,Y.Sal FROM T AS X INNER JOIN T AS YON X.SalY.Sal AND Y.TN=刘伟方法3：SELECT R1.TN,R1.Sal, R2.SalFROM (SELECT TN,Sal FROM T ) AS R1INNER JOIN (SELECT Sal FROM TWHERE TN=刘伟) AS R2ON R1.SalR2.Sal【例3-50】方法1：SELECT SN,Age,CNFROM S,C,SCWHERE S.SNo=SC.SNo AND SC.CNo=C.CNo方法2：SELECT R3.SNo,R3.Age,R4.CNFROM(SELECT SNo,SN,Age FROM S) AS R3INNER JOIN(SELECT R2.SNo,R1.CNFROM(SELECT CNo,CN FROM C) AS R1INNER JOIN (SELECT SNo,CNo FROM SC) AS R2ON R1.CNo=R2.CNo) AS R4ON R3.SNo=R4.SNo【例3-51】SELECT TNo,TNFROM TWHERE Prof= (SELECT Prof FROM T WHERE TN= 刘伟)【例3-52】SELECT TN FROM TWHERE (TNo = ANY ( SELECT TNo FROM TC WHERE CNo = C5)【例3-53】SELECT TN, SalFROM TWHERE (Sal ANY ( SELECT Sal FROM T WHERE Dept = 计算机) AND (Dept 计算机)【例3-54】SELECT TNFROM TWHERE (TNo IN ( SELECT TNo FROM TC WHERE CNo = C5)【例3-55】SELECT TN, SalFROM TWHERE (Sal ALL ( SELECT Sal FROM T WHERE Dept = 计算机) AND (Dept 计算机)【例3-56】SELECT DISTINCT TNFROM TWHERE (C5 ALL ( SELECT CNo FROM TC WHERE TNo = T.TNo)【例3-57】SELECT TNFROM TWHERE EXISTS ( SELECT * FROM TC WHERE TNo = T.TNo AND CNo = C5)【例3-58】SELECT TNFROM TWHERE (NOT EXISTS ( SELECT * FROM TC WHERE TNo = T.TNo AND CNo = C5)【例3-59】SELECT SNFROM SWHERE (NOT EXISTS ( SELECT * FROM C WHERE NOT EXISTS ( SELECT * FROM SC WHERE SNo = S.SNo AND CNo = C.CNo)【例3-60】SELECT SNo AS 学号, SUM(Score) AS 总分FROM SCWHERE (SNo = S1)GROUP BY SNoUNIONSELECT SNo AS 学号, SUM(Score) AS 总分FROM SCWHERE (SNo = S5)GROUP BY SNo【例3-61】SELECT SNo AS 学号, SUM(Score) AS 总分INTO Cal_TableFROM SCGROUP BY SNo【例3-62】INSERT INTO S (SNo, SN, Age, Sex, Dept)VALUES (S7, 郑冬, 21, 女, 计算机)【例3-63】INSERT INTO SC (SNo, CNo)VALUES (S7, C1)【例3-64】CREATE TABLE AvgSal( Department VARCHAR(20), Average SMALLINT)INSERT INTO AvgSalSELECT Dept,AVG(Sal) FROM TGROUP BY Dept【例3-65】UPDATE T SET Dept= 信息WHERE TN= 刘伟【例3-66】UPDATE S SET Age=Age+1【例3-67】UPDATE TSET Sal = 1.2 * SalWHERE (Prof = 讲师 ) AND (Sal = 18 and age =60 PRINT Pass!ELSE PRINT Fail!GO【例7-5】USE Teach GODECLARE message VARCHAR(255) /*定义变量message */IF EXISTS (SELECT * FROM S WHERE SNo=S1) SET message=存在学号为S1的学生ELSE SET message=不存在学号为S1的学生PRINT messageGO【例7-6】USE TeachGOSELECT SNo, Sex= CASE Sex WHEN 男 THEN M WHEN 女 THEN F ENDFROM SGO【例7-7】USE TeachGOSELECT SNo,CNo, Score= CASE WHEN Score IS NULL THEN 未考 WHEN Score=60 AND Score=70 AND Score=90 THEN 优秀 ENDFROM SCGO【例7-8】DECLARE s SMALLINT,i SMALLINT,nums SMALLINT SET s=0SET i=1SET nums=0WHILE (i=100) BEGIN IF (i%3=0) BEGIN SET s=s+i SET nums=nums+1 END SET i=i+1 ENDPRINT sPRINT nums【例7-9】WAITFOR DELAY 01:02:03SELECT * FROM S【例7-10】WAITFOR TIME 11:24:00SELECT * FROM S【例7-11】DECLARE s SMALLINT,i SMALLINTSET i=1SET s=0BEG:IF (i=10) BEGIN SET s=s+i SET i=i+1 GOTO BEG /*使程序跳转到标号为BEG的地方执行*/ ENDPRINT s【例7-12】CREATE FUNCTION dbo.Fun1(n AS INT)RETURNS INTAS BEGIN DECLARE i INT DECLARE sign INT SET sign=1 SET i=2 WHILE i=SQRT(n) BEGIN IF n % i=0 BEGIN SET sign=0 BREAK END SET i=i+1 END RETURN signEND【例7-13】CREATE FUNCTION dbo.Fun2()RETURNS TABLEAS return select SNo,SN from S【例7-14】CREATE FUNCTION Score_Table(student_id CHAR(6)RETURNS T_score TABLE(Cname VARCHAR(20),Grade INT)ASBEGIN INSERT INTO T_score SELECT CN,Score FROM SC,C WHERE SC.CNo=C.CNo and SC.SNo=student_id and Score60 RETURNEND【例7-15】USE TeachGOCREATE PROCEDURE MyProc ASSELECT * FROM S WHERE Sex=男【例7-16】USE TeachGOCREATE PROCEDURE InsertRecord ( sno VARCHAR(6), sn NVARCHAR(10), sex NCHAR(1), age INT, dept NVARCHAR(20)ASINSERT INTO S VALUES(sno,sn,sex,age, dept)【例7-17】USE TeachGOCREATE PROCEDURE InsertRecordDefa ( sno VARCHAR(6), sn NVARCHAR(10), sex NCHAR(1), age INT, dept NVARCHAR(20)= 无)ASINSERT INTO S VALUES(sno, sn, sex, age, dept)【例7-18】USE TeachGOCREATE PROCEDURE QueryTeach ( sno VARCHAR(6), sn NVARCHAR(10) OUTPUT, dept NVARCHAR(20) OUTPUT)ASSELECT sn=SN,dept=DeptFROM SWHERE SNo=sno【例7-19】USE TeachGOEXEC sp_helptext MyProc【例7-20】USE TeachGODROP PROCEDURE MyNewProc【例7-21】USE TeachGOEXEC MyProc【例7-22】USE TeachGOEXEC InsertRecord sno=S7, sn=王大利, sex=男, age=18, dept=计算机系【例7-23】USE TeachGOEXEC InsertRecordDefa sno=S10, sn=高平, age=18, sex=女【例7-24】USE TeachGODECLARE sn NVARCHAR