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
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1