数据库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