数据库SQL综合复习.docx
《数据库SQL综合复习.docx》由会员分享，可在线阅读，更多相关《数据库SQL综合复习.docx（19页珍藏版）》请在冰豆网上搜索。
数据库SQL综合复习
SQL综合复习
1、用SQL命令创建数据库
●CREATEDATABASE命令用来创建一个新数据库和存储该数据库的文件。
CREATEDATABASE的语法如下。
●CREATEDATABASE数据库名
●[ON
●{[PRIMARY]（[NAME=数据文件的逻辑名,]
●FILENAME='数据文件的物理名'
●[,SIZE=文件的初始大小]
●[,MAXSIZE=文件的最大容量]
●[,FILEGROWTH=文件空间的增长量]）
●}[，...n]]
●[LOGON
●{（[NANE=日志文件的逻辑名,]
●FILENAME='逻辑文件的物理名'
●[,SIZE=文件的初始大小]）
●[,MAXSIZE=文件的最大容量]
●[,FILEGROWTH=文件空间的增长量]）
●}[,...n]]
2、使用CREATETABLE语句创建表
•CREATETABLE表名
•{（列名列属性列约束）}[,...]
•其中，列属性的格式为：
•数据类型[（长度）][NULL|NOTNULL][IDENTITY（初始值，步长）]
•列约束的格式为：
•[CONSTRAINT约束名]PRIMARYKEY[（列名）]：
•指定主键
•[CONSTRAINT约束名]UNIQUEKEY[（列名）]：
•指定惟一键
•[CONSTRAINT约束名]FOREIGENKEY[（外键列）]REFERENCES引用表名（引用列）
•[CONSTRAINT约束名]CHECK（检查表达式）：
指定检查约束
•[CONSTRAINT约束名]DEFAULT默认值：
•指定默认值
3、使用SQL语句修改表
（1）添加新字段
•通过在ALTERTABLE语句中使用ADD子句，可以在表中增加一个或多个字段。
•
（2）修改字段的属性
•通过在ALTERTABLE语句中使用ALTERCOLUMN子句，可以修改列的数据类型、长度等属性。
•（3）删除字段
•通过在ALTERTABLE语句中使用DROPCOLUMN子句，可以删除表中的字段。
•注意：
在删除列时，必须先删除基于该列的索引和约束后，才能删除该列。
4、删除表
•删除表命令基本语法如下。
•DROPTABLE表名[，...n]
5、表数据的添加、修改和删除
（1）添加
使用INSERT语句
•INSERT[INTO]表名
•{[（字段列表）]
•{VALUES（相应的值列表）
•字段的个数必须与VALUES子句中给出的值的个数相同；数据类型必须和字段的数据类型相对应。
（2）修改表中的数据
•UPDATE
•表名SET
•{
•列名={表达式|DEFAULT|NULL}[,...n]}
•[FROM另一表名[,...n]]
•[WHERE<检索条件表达式>]
（3）删除表中的数据
•DELETE语句的简化语法格式如下。
DELETE[FROM] 表名[WHERE{<检索条件表达式>}]
•TRUNCATETABLE语句删除表中所有记录的语法格式如下。
TRUNCATETABLE表名
6、检查（CHECK）约束
•CHECK约束通过限制可输入或修改的一列或多列的值来强制实现域完整性，它作用于插入（INSERT）和修改（UPDATE）语句。
•在默认情况下，检查（CHECK）约束同时作用于新数据和表中已有的老数据，可以通过关键字WITHNOCHECK禁止CHECK约束检查表中已有的数据。
当然，用户对禁止检查应该确信是合理的。
•与其他约束不同的是，CHECK约束可以通过NOCHECK和CHECK关键字设置为无效或重新有效，语法格式如下。
ALTERTABLE表名
NOCHECKCONSTRAINT约束名|CHECKCONSTRAINT约束名
7、默认值（DEFAULT）
•创建默认值
•创建默认值可以通过企业管理器或SQL语句来实现，使用SQL语句创建默认值对象的语法如下。
CREATEDEFAULT默认值名称
AS常量表达式
删除默认值
•可以用DROPDEFAULT语句或在企业管理器下删除默认值对象。
注意：
在删除一个默认值之前，应首先将它从所绑定的列或自定义数据类型上解绑，否则系统会报错。
8、规则
创建规则
CREATERULE规则名称
AS条件表达式
绑定和解绑规则
•绑定和解绑操作既可以通过系统存储过程来实现，也可以使用企业管理器来完成。
绑定和解绑的语法格式如下。
[EXECUTE]sp_bindrule'规则名称','表名.字段名'|'用户自定义数据类型'
[EXECUTE]sp_unbindrule'表名.字段名'|'用户自定义数据类型'
删除规则
•可以用DROPRULE语句或在企业管理器下删除默认值对象。
注意：
在删除一个默认值之前，应首先将它从所绑定的列或自定义数据类型上解绑，否则系统会报错。
9、自定义数据类型
•1．在查询分析器中创建
•使用系统存储过程sp_addtype创建自动数据类型的语法格式如下。
•[EXECUTE]sp_addtype自定义类型名称,系统数据类型名称
•[,'NULL'|'NOTNULL']
1、select可以给多个变量赋值。
（）1
2、参照完整性是指从表不能引用主表中不存在的元组（）1
3、创建触发器的时候可以不是表的所有者或数据库的所有者。
（）0
4、SQLServer2000的实例可以分为默认实例可以为多个（）0
5、数据文件和日志文件可以同在一个文件组（）0
6、索引越多越好。
（）0
7、创建触发器的时候可以不是表的所有者或数据库的所有者。
（）0
8、unique约束的列可以为空值（）1
9、Rule不能绑定到自定义数据类型上（）0
10、在主键上既不能有重复值也不能为空？
（）1
11、set可以给多个变量赋值。
（）0
12、从表中外键的值可以来自主表中主键的值，也可以自行输入别的值（）0
13、存储过程是一组预先编好的Transact-SQL代码，就好象一个已经预定好的函数，用户可以像调用函数一样调用存储过程。
（）1
14、SQLServer2000的实例可以分为默认实例和命名实例两种（）1
15、视图本身没有数据，因为视图是一个虚拟的表。
（）1
16、索引就是排序（）0
17、存储过程是一组预先编好的Transact-SQL代码，就好象一个已经预定好的函数，用户可以像调用函数一样调用存储过程。
（）1
18、参照完整性是指从表不能引用主表中不存在的值？
（）1
19、创建一个xbRule规则,将其绑定到性别字段，下列写法正确吗？
CreaterulexbRuleas@xbin（＇男＇，＇女＇）goexecsp_bindrule'xbRule','性别'（）0
20、一张表中可以有多个同名列（）0
查询
xh
xm
jg
tp
dt
sc
0701
张三
广东
135********
1986-0.8-12
60
0702
李四
湖南
131********
1987-06-04
75
0703
王那
湖北
132********
1987-12-06
81
0704
李华
四川
134********
1988-06-25
90
0705
柳无
天津
139********
1987-03-23
50
0706
陈刚
重庆
138********
1988-04-06
40
0707
李海波
上海
137********
1989-02-07
70
student
在数据库mydb中建立如上所示的表student
（1）查询所有学生的所有信息；
SELECT*FROMstudent
（2）查询sc（学习成绩）不及格的学生的xm（姓名）
SELECTxmFROMstudent
WHEREsc<60
（3）查询张三的sc（成绩}和tp（电话）
SELECTsc,tpFROMstudent
WHERExm=’张三’
（4）查询1988-00-00以后出生的学生的xm（姓名）和jg（地址）
SELECTxm,jgFROMstudent
WHEREdt>1988-00-00
（5）查询所有姓李的学生的xm（姓名）和tp（电话）
SELECTxm,tpFROMstudent
SI
xh
xm
jg
tp
dt
sc
0701
张三
广东
135********
1986-0.8-12
60
0702
李四
湖南
131********
1987-06-04
75
0703
王那
湖北
132********
1987-12-06
81
0704
李华
四川
134********
1988-06-25
90
0705
柳无
天津
139********
1987-03-23
50
0706
陈刚
重庆
138********
1988-04-06
40
0707
李海波
上海
137********
1989-02-07
70
0708
张文杰
云南
137********
80
1.在上表中进行以下查询操作：
（1）查询除李四外的所有姓李的学生的xm和jg，并在查询结果中用姓名代替xm、用籍贯代替jg；
SELECTxm,jg
FROMSI
WHERExmLIKE‘李%’ANDxmNOTIN（‘李四’）
（2）查询tp（电话）为空的学生的xm（姓名）和jg（籍贯）；
SELECTxm,jg
FROMSI
WHEREtpISNULL
（3）查询所有姓李的且sc（成绩}在80分以上的学生的所有信息；
SELECT*
FROMSI
WHERExmLIKE‘李%’ANDsc>=80
（4）列出按sc（成绩）由高到低顺序的所有学生的姓名和sc（成绩），并显示出最高分、最低分和平均分；
SELECTxm,sc
FROMSI
ORDERBYscDESC
COMPUTEMAX（sc）,MIN（sc）,AVG（sc）
（5）显示出sc（成绩）前三名的学生的所有信息；
SELECTTOP3*
FROMSI
ORDERBYscDESC
（6）显示出sc（成绩）后三名的学生的xm和sc，并在查询结果中用姓名代替xm、用成绩代替sc。
SELECTTOP3xmAS姓名,scASsc
FROMSI
ORDERBYsc
表1：
基本信息
学号
姓名
籍贯
政治面貌
1001
张三
广东
团员
1002
李四
湖南
团员
1003
王五
云南
党员
1004
赵六
广西
团员
1005
刘八
湖北
党员
表2：
个人简历
姓名
性别
年龄
地址
入学成绩
张三
男
19
广东广州
400
李四
女
19
湖南长沙
410
王五
男
20
云南昆明
450
赵六
男
21
广西贵州
460
江九
女
20
河南郑州
430
（1）查询表1中所有学生的学号、姓名、籍贯、地址和年龄；
SELECT基本信息.学号,基本信息.姓名,基本信息.籍贯,个人简历.地址,个人简历.年龄
FROM基本信息LEFTJOIN个人简历ON基本信息.姓名=个人简历.姓名
（1）查询表2中所有学生的学号、姓名、籍贯、地址和年龄；
SELECT基本信息.学号,个人简历.姓名,基本信息.籍贯,个人简历.地址,个人简历.年龄
FROM基本信息RIGHTJOIN个人简历ON基本信息.姓名=个人简历.姓名
（2）查询表1和表2中所有学生的学号、姓名、籍贯、地址和年龄；
SELECT基本信息.学号,基本信息.姓名,个人简历.姓名,基本信息.籍贯,个人简历.地址,个人简历.年龄
FROM基本信息FULLJOIN个人简历ON基本信息.姓名=个人简历.姓名
（3）合并表1中的“姓名”与“籍贯”和表2中的“姓名”与“地址”；
（4）求出表2中的入学成绩的所有学生的总分数、平均成绩、最高分和最低分；
（5）求出表1中的政治面貌是团员的总人数；
（6）求出表2中的男生数、女生数和总人数；
（7）找出入学成绩低于450的学生的学号、姓名；
（8）找出入学成绩低于450的学生的学号、姓名、性别和入学成绩。
视图
创建视图语句：
CREATEVIEW视图名
AS
SELECT表.属性1，表.属性2，……
FROM表1INNERJOIN表2ON表1.属性＝表2.属性
视图查询语句:
SELECT表.属性1，表.属性2，……
FROM视图名
WHERE条件语句
表1基表1：
货品信息表
编码
名称
库存量
供应商编码
售价
成本价
1
电脑台
100
2
1500
1100
2
打印机
700
3
800
600
3
移动办公软件
200
1
8000
6000
表2基表2：
供应商信息表
编码
名称
联系人
地址
电话
1
腾飞信息公司
章程
深圳市龙岗区
3567288
2
朝阳文具实业公司
郑敏
哈尔滨开发区
25154543
3
导向打印机销售公司
王洗
上海浦东开发区
85479821
表3货品信息视图1
编码
货品名称
供应商
联系人
1
电脑台
朝阳文具实业公司
郑敏
2
打印机
导向打印机销售公司
王洗
3
移动办公软件
腾飞信息公司
章程
表4货品信息视图2
编码
货品名称
供应商
联系人
电话
1
电脑台
朝阳文具实业公司
郑敏
25154543
2
打印机
导向打印机销售公司
王洗
85479821
3
移动办公软件
腾飞信息公司
章程
3567288
（1）在企业管理器环境下，基于表1、表2，创建如表3所示的视图；
（2）在查询分析器环境下，基于表1、表2，创建如表4所示的视图；
（3）通过视图查询打印机联系人和电话。
索引
创建索引格式：
CREATE［UNIQUE］［CLUSTERED］［NONCLUSTERED］
INDEX索引名ON｛表名｜视图名｝（字段名［，……n］）
其中：
UNIQUE：
建立惟一索引
CLUSTERED：
建立聚集索引
NONCLUSTERED：
建立非聚集索引
例：
使用局部变量，查找打印机的库存量
DECLARE@x
SET@x=’打印机’
SELECT库存量FROM货品信息表WHERE库存量=@x
作业：
（1）在企业管理器下，为表1创建名为“IX_1_name”的非聚集，该索引基于“名称”列；
（2）利用CREATEINDEX语句为表2创建表2“IX_2_name”的非聚集、惟一索引，该索引基于“联系人”列；
（3）为表3创建名为“IX_3_num”的聚集、惟一索引，该索引基于“编码”列；
（4）使用局部变量，查找货品编码为2的货品的供应商.
流程控制语句
PRINT’字符串’表示在“消息”标签窗口中显示“字符串”
PRINT@x表示在“消息”标签窗口中显示@x的值
Student
学号
姓名
性别
政治面貌
成绩
1
张三
1
0
85
2
李四
1
1
54
3
王五
0
2
75
4
赵六
0
0
55
5
孙七
1
1
65
PRINT’字符串’表示在“消息”标签窗口中显示“字符串”
PRINT@x表示在“消息”标签窗口中显示@x的值
例：
查询表中学号、姓名和政治面貌，若政治面貌为’0’则显示“群众”，为’1’则显示“团员”，为’2’则显示“党员”
SELECT学号,姓名,政治面貌=
CASE政治面貌
WHEN’0’THEN‘群众’
WHEN’1’THEN‘团员’
WHEN’2’THEN‘党员’
END
FROMStudent
（1）查询性别分别为“0”和“1”的人数，如果性别为“0”的人数大于性别为“1”的人数，则在“消息”标签窗口中显示“女生多于男生”，否则显示“女生不多于男生”；
（2）查询学生的学号和性别，如果性别为“0”则在性别属性中显示“女同学”，如果性别为“1”则在性别属性中显示“男同学”；
（3）根据成绩范围显示出相应信息：
0～60显示“不及格”，60～80显示“及格”，80～100显示“优秀”；
（4）计算1+2+……+100.
（1）、declare@xint
select@x=count（*）fromstudent
wherestudent.性别='0'
declare@aint
select@a=count（*）fromstudent
wherestudent.性别='1'
if@x>@aprint'女生多于男生'
else
print'女生不多于男生'
（2）、SELECT性别=
case性别
when'0'then'女同学'
when'1'then'男同学'
end
fromStudent
（3）、select姓名,成绩=
case
when成绩<=60then'不及格'
when成绩>=60and成绩<=80then'及格'
else
'优秀'
end
fromstudent
（4）declare@xint
set@x=1
declare@aint
set@a=0
while@x<=100
begin
set@a=@a+@x
set@x=@x+1
end
print@a
游标
1.定义游标
⏹基于SQL-92标准的语法格式如下。
⏹DECLARE游标名称[INSENSITIVE][SCROLL]CURSOR
⏹FORSELECT语句
⏹[FOR{READONLY|UPDATE[OF列名[,...n]]}]
其中：
INSENSITIVE定义静态游标
SCROLL定义滚动游标
⏹Transact-SQL扩展的语法格式如下。
⏹DECLARE游标名称CURSOR
⏹[LOCAL|GLOBAL]
⏹[FORWARD_ONLY|SCROLL]
⏹[STATIC|KEYSET|DYNAMIC|FAST_FORWARD]
⏹[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
⏹[TYPE_WARNING]
⏹FORSELECT语句
⏹[FORUPDATE[OF列名[,...n]]]
其中：
FORWARD_ONLY定义只进游标
SCROLL定义滚动游标
2.打开游标
⏹语法格式如下。
⏹OPEN[GLOBAL]游标名
3.判断游标是否打开成功
可以通过判断全局变量@@ERROR是否为0来确定
4.获取游标中的记录行数
⏹全局变量@@CURSOR_ROWS
⏹
（1）-m表中的数据已部分填入游标，是数据子集中当前的行数
⏹
（2）-1游标为动态
⏹（3）0没有被打开的游标
⏹（4）n表中的数据完全填入游标
5.从游标中获取数据
⏹使用FETCH语句，从结果集中检索单独的行。
语法格式如下。
⏹FETCH[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]
⏹FROM [GLOBAL]游标名称
⏹[INTO@变量名[,...n]]
其中：
NEXT后一条记录
PRIOR前一条记录
FIRST第一条记录
LAST最后一条记录
ABSOLUTE{n|@nvar}相对位置
RELATIVE{n|@nvar}绝对位置
6.关闭游标
⏹语法格式为：
⏹CLOSE游标名称
7.释放游标
⏹语法格式为：
⏹DEALLOCATE游标名称
作业
Student
学号
姓名
性别
政治面貌
成绩
1
张三
1
0
85
2
李四
1
1
54
3
王五
0
2
75
4
赵六
0
0
55
5
孙七
1
1
65
（1）定义滚动游标student_cur1，然后打开该游标，输出其行数；
（2）打开游标student_cur1，分别查看第一条记录、第二条记录、第一条记录、第四条记录、第二条记录、最后一条记录、第三条记录，并关闭和释放游标；
（3）使用@@CURSOR_ROWS变量，计算“Student”表中学生的数量（定义游标student_cur2，打开游标，判断游标是否已打开，显示信息，关闭游标，释放游标）；
Declarestudent_cur1SCROLLCURSOR
FORSELECT*
fromstudent
OPENstudent_cur1
print@@cursor_rows
（2）
Declarestudent_cur1SCROLLCURSOR
FORSELECT*
fromstudent
OPENstudent_cur1
FETCHfirst
FROMstudent_cur1
FETCHnext
FROMstudent_cur1
FETCHPRIOR
FROMstudent_cur1
FETCHRELATIVE4
FROMstudent_cur1
FETCHRELATIVE2
FROMstudent_cur1
FETCHlast
FROMstudent_cur1
FETCHRELATIVE3
FROMstudent_cur1
CLOSEstudent_cur1
DEALLOCATEstudent_cur1
（3）
Declarestudent_cur2SCROLLCURSOR
FORSELECT*
from
升级会员 