数据库SQL综合复习.docx

1、数据库SQL综合复习SQL综合复习1、用SQL命令创建数据库 CREATE DATABASE命令用来创建一个新数据库和存储该数据库的文件。CREATE DATABASE的语法如下。 CREATE DATABASE数据库名 ON PRIMARY(NAME=数据文件的逻辑名, FILENAME=数据文件的物理名 ,SIZE=文件的初始大小 ,MAXSIZE=文件的最大容量 ,FILEGROWTH=文件空间的增长量) ，. . .n LOG ON (NANE=日志文件的逻辑名, FILENAME=逻辑文件的物理名 ,SIZE=文件的初始大小) ,MAXSIZE=文件的最大容量 ,FILEGROWTH

2、=文件空间的增长量) , . . .n2、使用CREATE TABLE语句创建表 CREATE TABLE 表名 (列名 列属性 列约束) , . 其中，列属性的格式为： 数据类型（长度） NULL | NOT NULL IDENTITY(初始值，步长) 列约束的格式为： CONSTRAINT 约束名 PRIMARY KEY (列名)： 指定主键 CONSTRAINT 约束名 UNIQUE KEY (列名)： 指定惟一键 CONSTRAINT 约束名 FOREIGEN KEY (外键列) REFERENCES 引用表名（引用列） CONSTRAINT 约束名 CHECK (检查表达式)：指定检

3、查约束 CONSTRAINT 约束名 DEFAULT 默认值： 指定默认值3、使用SQL语句修改表（1）添加新字段 通过在ALTER TABLE语句中使用ADD子句，可以在表中增加一个或多个字段 。 （2）修改字段的属性 通过在ALTER TABLE语句中使用ALTER COLUMN子句，可以修改列的数据类型、长度等属性 。 （3）删除字段 通过在ALTER TABLE语句中使用DROP COLUMN子句，可以删除表中的字段 。 注意：在删除列时，必须先删除基于该列的索引和约束后，才能删除该列。4、删除表 删除表命令基本语法如下。 DROP TABLE 表名，.n5、表数据的添加、修改和删除（

4、1）添加使用INSERT语句 INSERT INTO 表名 (字段列表) VALUES （相应的值列表） 字段的个数必须与VALUES子句中给出的值的个数相同；数据类型必须和字段的数据类型相对应。 （2）修改表中的数据 UPDATE 表名 SET 列名 = 表达式 | DEFAULT | NULL ,.n FROM 另一表名 ,.n WHERE （3）删除表中的数据 DELETE语句的简化语法格式如下。 DELETE FROM表名 WHERE TRUNCATE TABLE语句删除表中所有记录的语法格式如下。 TRUNCATE TABLE 表名 6、检查（CHECK）约束 CHECK约束通过限制

5、可输入或修改的一列或多列的值来强制实现域完整性，它作用于插入（INSERT）和修改（UPDATE）语句。 在默认情况下，检查（CHECK）约束同时作用于新数据和表中已有的老数据，可以通过关键字WITH NOCHECK禁止CHECK约束检查表中已有的数据。当然，用户对禁止检查应该确信是合理的。 与其他约束不同的是，CHECK约束可以通过NOCHECK和CHECK关键字设置为无效或重新有效，语法格式如下。ALTER TABLE 表名 NOCHECK CONSTRAINT 约束名 | CHECK CONSTRAINT 约束名7、默认值（DEFAULT） 创建默认值 创建默认值可以通过企业管理器或SQ

6、L语句来实现，使用SQL语句创建默认值对象的语法如下。 CREATE DEFAULT 默认值名称 AS 常量表达式删除默认值 可以用DROP DEFAULT语句或在企业管理器下删除默认值对象。注意：在删除一个默认值之前，应首先将它从所绑定的列或自定义数据类型上解绑，否则系统会报错。8、规 则创建规则CREATE RULE 规则名称 AS 条件表达式绑定和解绑规则 绑定和解绑操作既可以通过系统存储过程来实现，也可以使用企业管理器来完成。绑定和解绑的语法格式如下。 EXECUTE sp_bindrule 规则名称, 表名.字段名|用户自定义数据类型 EXECUTE sp_unbindrule 表名

7、.字段名|用户自定义数据类型删除规则 可以用DROP RULE语句或在企业管理器下删除默认值对象。注意：在删除一个默认值之前，应首先将它从所绑定的列或自定义数据类型上解绑，否则系统会报错。9、自定义数据类型 1在查询分析器中创建 使用系统存储过程sp_addtype创建自动数据类型的语法格式如下。 EXECUTEsp_addtype 自定义类型名称, 系统数据类型名称 , NULL|NOT NULL1、select可以给多个变量赋值。（ ）1 2、参照完整性是指从表不能引用主表中不存在的元组（ ）1 3、创建触发器的时候可以不是表的所有者或数据库的所有者。（ ）0 4、SQLServer200

8、0的实例可以分为默认实例可以为多个（ ）05、数据文件和日志文件可以同在一个文件组（ ）0 6、索引越多越好。（ ）0 7、创建触发器的时候可以不是表的所有者或数据库的所有者。（ ）0 8、unique 约束的列可以为空值（ ）1 9、Rule 不能绑定到自定义数据类型上 （ ）0 10、在主键上既不能有重复值也不能为空？（ ）111、set可以给多个变量赋值。（ ）012、从表中外键的值可以来自主表中主键的值，也可以自行输入别的值 （ ）013、存储过程是一组预先编好的Transact-SQL代码，就好象一个已经预定好的函数，用户可以像调用函数一样调用存储过程。（ ）114、SQLServe

9、r2000的实例可以分为默认实例和命名实例两种（ ）115、视图本身没有数据，因为视图是一个虚拟的表。（ ）1 16、索引就是排序（ ）0 17、存储过程是一组预先编好的Transact-SQL代码，就好象一个已经预定好的函数，用户可以像调用函数一样调用存储过程。（ ）1 18、参照完整性是指从表不能引用主表中不存在的值？（ ）1 19、 创建一个xbRule规则,将其绑定到性别字段，下列写法正确吗？ Create rule xbRule as xb in(男，女) go exec sp_bindrule xbRule,性别 （ ）0 20、一张表中可以有多个同名列 （ ）0查询xhxmjgt

10、pdtsc0701张三广东135*1986-0.8-12600702李四湖南131*1987-06-04750703王那湖北132*1987-12-06810704李华四川134*1988-06-25900705柳无天津139*1987-03-23500706陈刚重庆138*1988-04-06400707李海波上海137*1989-02-0770student在数据库mydb中建立如上所示的表student(1)查询所有学生的所有信息；SELECT * FROM student(2)查询sc（学习成绩）不及格的学生的xm(姓名)SELECT xm FROM studentWHERE sc19

11、88-00-00(5)查询所有姓李的学生的xm(姓名)和tp(电话)SELECT xm, tp FROM studentSIxhxmjgtpdtsc0701张三广东135*1986-0.8-12600702李四湖南131*1987-06-04750703王那湖北132*1987-12-06810704李华四川134*1988-06-25900705柳无天津139*1987-03-23500706陈刚重庆138*1988-04-06400707李海波上海137*1989-02-07700708张文杰云南137*801.在上表中进行以下查询操作：(1)查询除李四外的所有姓李的学生的xm和jg，并在

12、查询结果中用姓名代替xm、用籍贯代替jg；SELECT xm,jgFROM SIWHERE xm LIKE 李% AND xm NOT IN (李四)(2)查询tp(电话)为空的学生的xm(姓名)和jg(籍贯)；SELECT xm,jgFROM SIWHERE tp IS NULL(3)查询所有姓李的且sc(成绩在80分以上的学生的所有信息； SELECT *FROM SIWHERE xm LIKE 李% AND sc=80(4)列出按sc(成绩)由高到低顺序的所有学生的姓名和sc(成绩)，并显示出最高分、最低分和平均分；SELECT xm, scFROM SIORDER BY sc DESC

13、COMPUTE MAX(sc),MIN(sc),AVG(sc) (5)显示出sc(成绩)前三名的学生的所有信息；SELECT TOP 3*FROM SIORDER BY sc DESC(6)显示出sc(成绩)后三名的学生的xm和sc，并在查询结果中用姓名代替xm、用成绩代替sc。SELECT TOP 3 xm AS 姓名, sc AS scFROM SIORDER BY sc表1：基本信息学号姓名籍贯政治面貌1001张三广东团员1002李四湖南团员1003王五云南党员1004赵六广西团员1005刘八湖北党员表2：个人简历姓名性别年龄地址入学成绩张三男19广东广州400李四女19湖南长沙410王

14、五男20云南昆明450赵六男21广西贵州460江九女20河南郑州430(1)查询表1中所有学生的学号、姓名、籍贯、地址和年龄；SELECT基本信息.学号, 基本信息.姓名, 基本信息.籍贯, 个人简历.地址, 个人简历.年龄FROM基本信息 LEFT JOIN个人简历 ON 基本信息.姓名=个人简历. 姓名（1）查询表2中所有学生的学号、姓名、籍贯、地址和年龄；SELECT基本信息.学号, 个人简历.姓名, 基本信息.籍贯, 个人简历.地址, 个人简历.年龄FROM基本信息 RIGHT JOIN个人简历 ON 基本信息.姓名=个人简历. 姓名（2）查询表1和表2中所有学生的学号、姓名、籍贯、地

15、址和年龄；SELECT基本信息.学号, 基本信息.姓名, 个人简历. 姓名,基本信息.籍贯, 个人简历.地址, 个人简历.年龄FROM基本信息 FULL JOIN个人简历 ON 基本信息.姓名=个人简历. 姓名（3）合并表1中的“姓名”与“籍贯”和表2中的“姓名”与“地址”；（4）求出表2中的入学成绩的所有学生的总分数、平均成绩、最高分和最低分；（5）求出表1中的政治面貌是团员的总人数；（6）求出表2中的男生数、女生数和总人数；（7）找出入学成绩低于450的学生的学号、姓名；（8）找出入学成绩低于450的学生的学号、姓名、性别和入学成绩。视图创建视图语句：CREATE VIEW 视图名AS S

16、ELECT 表.属性1，表.属性2， FROM 表1 INNER JOIN 表2 ON 表1. 属性表2. 属性视图查询语句:SELECT 表.属性1，表.属性2， FROM 视图名 WHERE 条件语句表1 基表1：货品信息表编码名称库存量供应商编码售价成本价1电脑台1002150011002打印机70038006003移动办公软件200180006000表2 基表2：供应商信息表编码名称联系人地址电话 1腾飞信息公司章程深圳市龙岗区35672882朝阳文具实业公司郑敏哈尔滨开发区251545433导向打印机销售公司王洗上海浦东开发区85479821表3 货品信息视图1编码货品名称供应商联系

17、人1电脑台朝阳文具实业公司郑敏2打印机导向打印机销售公司王洗3移动办公软件腾飞信息公司章程表4 货品信息视图2编码货品名称供应商联系人电话1电脑台朝阳文具实业公司郑敏251545432打印机导向打印机销售公司王洗854798213移动办公软件腾飞信息公司章程3567288(1) 在企业管理器环境下，基于表1、表2，创建如表3所示的视图；(2) 在查询分析器环境下，基于表1、表2，创建如表4所示的视图；(3) 通过视图查询打印机联系人和电话。索引创建索引格式：CREATE UNIQUECLUSTEREDNONCLUSTEREDINDEX 索引名 ON 表名视图名（字段名，n）其中：UNIQUE：

18、建立惟一索引CLUSTERED：建立聚集索引NONCLUSTERED：建立非聚集索引例：使用局部变量，查找打印机的库存量DECLARE xSET x= 打印机SELECT 库存量 FROM货品信息表 WHERE库存量=x作业：（1）在企业管理器下，为表1创建名为“IX_1_name”的非聚集，该索引基于“名称”列；（2）利用CREATE INDEX语句为表2创建表2“IX_2_name”的非聚集、惟一索引，该索引基于“联系人”列；（3）为表3创建名为“IX_3_ num”的聚集、惟一索引，该索引基于“编码”列；(4) 使用局部变量，查找货品编码为2的货品的供应商.流程控制语句PRINT 字符串

19、 表示在“消息”标签窗口中显示“字符串”PRINT x 表示在“消息”标签窗口中显示x的值Student学号姓名性别政治面貌成绩1张三10852李四11543王五02754赵六00555孙七1165PRINT 字符串 表示在“消息”标签窗口中显示“字符串”PRINT x 表示在“消息”标签窗口中显示x的值例：查询表中学号、姓名和政治面貌，若政治面貌为0则显示“群众”，为1则显示“团员”，为2则显示“党员”SELECT学号, 姓名, 政治面貌=CASE政治面貌 WHEN 0 THEN 群众 WHEN 1 THEN 团员 WHEN 2 THEN 党员ENDFROM Student（1） 查询性别分

20、别为“0”和“1”的人数，如果性别为“0”的人数大于性别为“1”的人数，则在“消息”标签窗口中显示“女生多于男生”，否则显示“女生不多于男生”；（2） 查询学生的学号和性别，如果性别为“0”则在性别属性中显示“女同学”， 如果性别为“1”则在性别属性中显示“男同学”；（3） 根据成绩范围显示出相应信息：060显示“不及格”，6080显示“及格”，80100显示“优秀”；（4） 计算1+2+100.（1）、declare x int select x=count(*) from student where student.性别=0 declare a int select a=count(*)

21、from student where student.性别=1 if xa print 女生多于男生 else print 女生不多于男生（2）、SELECT 性别= case 性别 when0then女同学 when1then男同学 end from Student（3）、select 姓名,成绩= case when 成绩=60 and 成绩=80 then 及格 else 优秀 end from student（4）declare x intset x=1 declare a intset a=0while x=100 begin set a=a+x set x=x+1 end prin

22、t a游标1.定义游标 基于SQL-92标准的语法格式如下。 DECLARE 游标名称 INSENSITIVE SCROLL CURSOR FOR SELECT语句 FOR READ ONLY|UPDATE OF 列名 ,.n 其中：INSENSITIVE 定义静态游标 SCROLL 定义滚动游标 Transact-SQL 扩展的语法格式如下。 DECLARE 游标名称 CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | O

23、PTIMISTIC TYPE_WARNING FOR SELECT语句 FOR UPDATE OF 列名 ,.n 其中：FORWARD_ONLY 定义只进游标 SCROLL 定义滚动游标2. 打开游标 语法格式如下。 OPEN GLOBAL 游标名3. 判断游标是否打开成功可以通过判断全局变量ERROR是否为0来确定4.获取游标中的记录行数 全局变量CURSOR_ROWS （1）-m 表中的数据已部分填入游标，是数据子集中当前的行数 （2）-1 游标为动态 （3）0 没有被打开的游标 （4）n 表中的数据完全填入游标5. 从游标中获取数据 使用FETCH语句，从结果集中检索单独的行。语法格式如

24、下。 FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTEn|nvar | RELATIVEn|nvar FROMGLOBAL游标名称 INTO 变量名 ,.n 其中：NEXT 后一条记录 PRIOR 前一条记录 FIRST 第一条记录 LAST 最后一条记录 ABSOLUTEn|nvar 相对位置 RELATIVEn|nvar 绝对位置6. 关闭游标 语法格式为： CLOSE 游标名称7. 释放游标 语法格式为： DEALLOCATE 游标名称作业Student学号姓名性别政治面貌成绩1张三10852李四11543王五02754赵六00555孙七1165（

25、1） 定义滚动游标student_cur1，然后打开该游标，输出其行数；（2） 打开游标student_cur1，分别查看第一条记录、第二条记录、第一条记录、第四条记录、第二条记录、最后一条记录、第三条记录，并关闭和释放游标；（3） 使用CURSOR_ROWS变量，计算“Student” 表中学生的数量（定义游标student_cur2，打开游标，判断游标是否已打开，显示信息，关闭游标，释放游标）；Declare student_cur1 SCROLL CURSORFOR SELECT * from studentOPEN student_cur1print cursor_rows(2)Dec

26、lare student_cur1 SCROLL CURSORFOR SELECT * from studentOPEN student_cur1FETCH firstFROM student_cur1FETCH nextFROM student_cur1FETCH PRIORFROM student_cur1FETCH RELATIVE 4FROM student_cur1FETCH RELATIVE 2FROM student_cur1FETCH lastFROM student_cur1FETCH RELATIVE 3FROM student_cur1CLOSE student_cur1 DEALLOCATE student_cur1 (3)Declare student_cur2 SCROLL CURSORFOR SELECT * from