数据库原理及应用教程(第4版)源代码(共16页).docx

精选优质文档-倾情为你奉上第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='C1'Cno=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= 'S6'DELETE 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 <=100)【例3-14】CREATE TABLE S( SNo VARCHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN NVARCHAR(10) CONSTRAINT SN_Cons NOT NULL, Sex NCHAR(1) CONSTRAINT Sex_Cons NOT NULL DEFAULT '男', Age INT CONSTRAINT Age_Cons NOT NULL CONSTRAINT Age_Chk CHECK (Age BETWEEN 15 AND 50), Dept NVARCHAR(20) CONSTRAINT Dept_Cons NOT NULL)【例3-15】ALTER TABLE S ADDClass_No VARCHAR(6),Address NVARCHAR(20)【例3-16】ALTER TABLE SC ADD CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)【例3-17】ALTER TABLE SALTER COLUMNSN NVARCHAR(12)【例3-18】ALTER TABLE SDROP CONSTRAINT S_Prim【例3-19】DROP TABLE S【例3-20】使用Management Studio进行查询SELECT SNo, SN, AgeFROM S【例3-21】SELECT * FROM S【例3-22】SELECT DISTINCT SNo FROM SC【例3-23】SELECT SN Name, SNo, Age FROM S或SELECT SN AS Name, SNo, AgeFROM S【例3-24】SELECT SNo,Score FROM SC WHERE CNo= 'C1'【例3-25】SELECT SNo,CNo,Score FROM SC WHERE 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.Sal>Y.Sal AND Y.TN='刘伟'方法2：SELECT X.TN, X.Sal,Y.Sal FROM T AS X INNER JOIN T AS YON X.Sal>Y.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.Sal>R2.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 <= 1000)【例3-68】UPDATE TSET Comm = Comm + 100WHERE (TNo IN ( SELECT TNo FROM T, TC WHERE T.TNo = TC.TNo AND TC.CNo = 'C5')【例3-69】UPDATE TSET Sal = ( SELECT 1.2 * AVG(Sal) FROM T)【例3-70】DELETE FROM T WHERE TN= '刘伟'【例3-71】DELETE FROM TC【例3-72】DELETE FROM TCWHERE (TNo = ( SELECT TNo FROM T WHERE TN = '刘伟')【例3-73】CREATE VIEW Sub_T AS SELECT TNo, TN, Prof FROM T WHERE Dept = '计算机'【例3-74】CREATE VIEW S_SC_C(SNo, SN, CN, Score) AS SELECT S.SNo, SN, CN, Score FROM S, C, SC WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo【例3-75】CREATE VIEW S_Avg(SNo, Avg)AS SELECT SNo, Avg(Score) FROM SC GROUP BY SNo【例3-76】ALTER VIEW S_SC_C(SN, CN, Score) AS SELECT SN, CN, Score FROM S, C, SC WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo【例3-77】DROP VIEW Sub_T【例3-78】SELECT TNo, TNFROM Sub_TWHERE Prof = '教授'【例3-79】INSERT INTO Sub_T (TNo, TN, Prof)VALUES ('T6', '李丹', '副教授')【例3-80】UPDATE Sub_TSET Prof = '副教授'WHERE (TN = '刘伟')【例3-81】DELETE FROM Sub_TWHERE TN = '刘伟'【例3-82】CREATE UNIQUE INDEX SCI ON SC(SNo,CNo)【例3-83】CREATE CLUSTERED INDEX TI ON T(TN)【例3-84】EXEC Sp_helpindex SC【例3-85】EXEC Sp_rename 'T.TI', 'T_Index'第4章 关系数据库理论无源代码第5章 数据库安全保护【例5-1】CREATE RULE age_ruleAS age >= 18 and age <= 50【例5-2】EXEC sp_bindrule 'age_rule', 'S.Age'【例5-3】EXEC sp_unbindrule 'S.Age'【例5-4】DROP RULE age_rule【例5-5】CREATE DEFAULT birthday_defaAS '1990-1-1'【例5-6】EXEC sp_bindefault，birthday_defa，'S.Birthday'【例5-7】EXEC sp_unbindefault 'S.Birthday'【例5-8】DROP DEFAULT birthday_defa第6章 数据库设计无源代码第7章 SQL Server 2012高级应用【例7-1】DECLARE id char(8)SELECT id=''【例7-2】DECLARE sno varchar(10), sn varchar(10) SELECT sno = SNo, sn = SN FROM S WHERE SNo = 'S7'【例7-3】USE Teach /*将教学管理数据库Teach置为当前数据库*/GOSELECT * FROM SCSELECT COUNT(*) FROM SGO【例7-4】USE TeachGOIF (SELECT AVG(Score) FROM SC WHERE SNo='S1')>=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 THEN '不及格' WHEN Score>=60 AND Score<70 THEN '及格' WHEN Score>=70 AND Score<90 THEN '良好' WHEN 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:03'SELECT * FROM S【例7-10】WAITFOR TIME '11:24:00'SELECT * 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 Score<60 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 Te