SQL用法示例 发表于 2017-04-29 | 分类于 应用 , 数据库 | 前言一些上课学的,没有题目,只有答案. 数据库操作123456789101112131415161718192021222324252627282930313233--建立数据库 [例3-1]CREATE DATABASE TeachON( NAME=Teach_Data, FILENAME='D:\TeachData.mdf', SIZE=10, MAXSIZE=50, FILEGROWTH=5)LOG ON( NAME=Teach_Log, FILENAME='D:\TeachLog.ldf', SIZE=5, MAXSIZE=25, FILEGROWTH=5) use Teach--[例3-2] 修改Northwind数据库中的Northwind文件增容方式为一次增加2MB。 ALTER DATABASE NorthwindMODIFY FILE( NAME = Northwind, FILEGROWTH = 2mb )--系统存储过程显示数据库信息EXEC Sp_helpdb NorthwindUSE NorthwindEXEC Sp_helpfileEXEC Sp_helpfilegroup--[例3-3] 删除数据库Teach。DROP DATABASE Teach 表格操作建立学生信息表123456789101112131415161718192021222324--[例3-4] 用SQL命令建立一个学生表S--[例3-6] 定义SN为惟一键。--[例3-7] 定义SN+SEX为惟一键,此约束为表约束。SELECT * FROM SALTER TABLE S ADDClass_No CHAR(6),Address CHAR(40)CREATE TABLE S( SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN CHAR(8) CONSTRAINT SN_Uniq UNIQUE, Sex CHAR(2) DEFAULT '男', Age INT, Dept VARCHAR(20), CONSTRAINT S_UNIQ UNIQUE(SN, Sex)) --[例3-13] 在S表中增加一个班号列和住址列。--使用此方式增加的新列自动填充NULL值,所以不能为增加的新列指定NOT NULL约束。ALTER TABLE S ADDClass_No CHAR(6),Address CHAR(40) 建立学生选课表12345678--[例3-9] 定义SNo+CNo为SC的主键--[例3-10] 定义SNo,CNo为SC的外部键。--[例3-11] 定义Score的取值范围为0~100之间。CREATE TABLE SC( SNo CHAR(5) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES S(SNo), CNo CHAR(5) NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo), Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score>=0 AND Score <=100), CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo)) 修改表格12345678910111213141516171819--[例3-14] 在SC表中增加完整性约束定义,使Score在0~100之间ALTER TABLE SCoADD CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)--[例3-15] 把S表中的SN列加宽到10个字符。ALTER TABLE SALTER COLUMNSN CHAR(10)--[例3-16] 删除S表中的主键约束。 ALTER TABLE S DROP CONSTRAINT S_Prim --[例-补] 删除S表中的一列“Address”ALTER TABLE SDROP COLUMN Address 索引123456789101112131415--[例3-18] 为表SC在SNo和CNo上建立惟一索引。CREATE UNIQUE INDEX SCI ON SC(SNo,CNo)--[例3-19] 为教师表T在TN上建立聚集索引。CREATE CLUSTERED INDEX TI ON T(TN)--[例3-20] 查看表SC的索引。EXEC Sp_helpindex SC --[例3-21] 更改T表中的索引TI名称为T_Index。EXEC Sp_rename 'T.TI', 'T_Index', 'index' EXEC Sp_helpindex T--[例3-22] 删除表SC的索引SCI。DROP INDEX SC.SCI 插入数据12--向S表插入数据INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S1', '赵亦', 17, '男', '计算机') 查询操作12SELECT SNo, SN, Age FROM SSELECT DISTINCT SN Name, DISTINCT SNo, Age FROM S 1234567891011121314151617--where条件用法--[例3-28]SELECT SNo,CNo,Score FROM SC WHERE Score>85 --[例3-29]SELECT SNo, CNo, Score FROM SC WHERE (CNo = 'C1' OR CNo = 'C2') AND (Score >= 85) --[例3-30]SELECT TNo,TN,Prof,Sal FROM T WHERE Sal BETWEEN 1000 AND 1500 --例3-31]SELECT TNo,TN,Prof,Sal FROM T WHERE Sal NOT BETWEEN 1000 AND 1500 --[例3-32]SELECT SNo,CNo,Score FROM SC WHERE CNo IN('C1','C2') --[例3-33]SELECT SNo,CNo,Score FROM SC WHERE CNo NOT IN('C1','C2') --[例3-35]SELECT TNo, TN FROM T WHERE TN LIKE '__力%'--[例3-36]SELECT SNo, CNo FROM SC WHERE Score IS not NULL 12345678910--函数使用--[例3-37]SELECT SUM(Score) AS TotalScore, AVG(Score) AS AveScore FROM SC WHERE (SNo = 'S1') SELECT SUM(Score) , AVG(Score) FROM SC WHERE (SNo = 'S1') --[例3-38]SELECT MAX(Score) AS MaxScore, MIN(Score) AS MinScore, MAX(Score)-MIN(Score) AS Diff FROM SC WHERE (CNo = 'C1')--[例3-40]SELECT COUNT(DISTINCT Dept) AS DeptNum FROM S 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768--[例3-44]SELECT SNo, COUNT(*) AS SC_Num FROM SC GROUP BY SNo HAVING (COUNT(*) >= 2)--[例3-46]SELECT SNo, CNo, ScoreFROM SCWHERE (CNo IN ('C2', 'C3', 'C4', 'C5'))ORDER BY SNo, Score DESC--[例3-48]SELECT R1.TNo, R2.TN, R1.CNoFROM(SELECT TNo,CNo FROM TC) AS R1INNER JOIN(SELECT TNo,TN FROM T WHERE TN='刘伟') AS R2ON R1.TNo=R2.TNo--[例3-49]SELECT S.SNo,SN,CN,ScoreFROM S,C,SCWHERE S.SNo=SC.SNo AND SC.CNo=C.CNo --[例3-50]SELECT CN,TN,PROF,COUNT(SC.SNo) FROM C,T,TC,SC WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo GROUP BY CN,TN,PROF--[例3-50]SELECT SC.CNO,CN,TN,PROF,SC.SNoFROM C,T,TC,SC WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo ORDER BY CN,TN,SC.SNo--外连接SELECT S.SNo,SN,CN,ScoreFROM SLEFT OUTER JOIN SCON S.SNo=SC.SNoLEFT OUTER JOIN CON C.CNo=SC.CNo --内连接SELECT S.SNo,SN,CN,ScoreFROM SINNER JOIN SCON S.SNo=SC.SNoINNER JOIN CON C.CNo=SC.CNo--[例3-55] 方法1SELECT TNFROM TWHERE (TNo = ANY (SELECT TNo FROM TC WHERE CNo = 'C5')) /*[例3-60]*/SELECT TNFROM TWHERE EXISTS ( SELECT * FROM TC WHERE TNO=T.TNO AND CNO='C5')--含有IN的查询通常可用EXISTS表示SELECT TNFROM TWHERE (TNo IN (SELECT TNo FROM TC WHERE CNo = 'C5'))--[例3-62]SELECT SNFROM SWHERE (NOT EXISTS ( SELECT * FROM C WHERE NOT EXISTS ( SELECT * FROM SC WHERE SNo = S.SNo AND CNo = C.CNo))) 视图12345678910111213141516171819--[例3-77]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-79]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-80]DROP VIEW S_SC_C--[例3-82]INSERT INTO SUB_TVALUES ('T6','李丹','副教授')--[例3-84]DELETE FROM SUB_TWHERE TN='刘伟'