SQL Server复习.docx
- 文档编号:25046558
- 上传时间:2023-06-04
- 格式:DOCX
- 页数:21
- 大小:23.23KB
SQL Server复习.docx
《SQL Server复习.docx》由会员分享,可在线阅读,更多相关《SQL Server复习.docx(21页珍藏版)》请在冰豆网上搜索。
SQLServer复习
1、连接网络服务器:
server312
☞选择【网上邻居】∕【查看工作组计算机】∕【搜索】:
server312
∕【用户名】:
Administrator【密码】:
13572468
2、打开SQLServer2005(假设计算机名为:
XG43)
☞选择【开始】∕【程序】∕【MicrosoftSQLServer2005】∕【SQLServerManagementStudio】∕【服务器名称】:
XG43
3、修改身份验证模式
☞右击【XG43】∕【属性】∕【安全性】∕【服务器身份验证】∕【确定】,
右击【XG43】∕【重新启动】
4、连接数据库
☞单击【连接】∕【数据库引擎】∕【服务器名称】:
SERVER312”(若未连接上,身份验证选择SQLServer身份验证,登录名:
sa,记住密码)或“浏览更多”,
5、语句建立数据库
☞先在E盘建立文件夹,命名为“吴伟”。
☞右击【XG43】∕【新建查询】,输入代码格式如下:
createdatabase吴伟教务管理on
(name=JWGL,filename='E:
\吴伟\教务.mdf',
size=3,maxsize=10,filegrowth=1)
logon
(name=JWJLL,filename='E:
\吴伟\教务.ldf',
size=1,maxsize=5,filegrowth=10%)
6、引用数据库数据(机器号:
XG48,目标库:
吴伟教学管理,源库:
白老师教学管理)
☞右击【吴伟教学管理】∕【任务】∕【导入数据】,单击【下一步】,【数据源】选择“MicrosoftOLEDBProivderforSQLServer”,【服务器名称】选择“server312”,【身份验证】选择“使用Window身份验证”,【数据库】选择“白老师教学管理”,单击【下一步】,【目标】选择“MicrosoftOLEDBProivderforSQLServer”,【服务器名称】选择“XG48”,【身份验证】选择“使用Window身份验证”,【数据库】选择“吴伟教学管理”,单击【下一步】,。
。
。
。
。
。
7、语句建立表
--例1:
使用主键
createtable教室
(教室编号char(6)primarykey,
座位数decimal(3),
有无多媒体binary
(1))
--例2:
使用计算列
createtable班级
(班级编号char(6)primarykey,
班主任char(8),
男生人数tinyint,
女生人数tinyint,
总人数AS男生人数+女生人数)
--例3:
使用域完整性约束
createtable课程
(课程编号char(8)primarykey,
课程名称varchar(20)notnull,
学分decimal(3,1)constraintXFcheck(学分>1and学分<=8),
教材varchar(40)null,
教学大纲text,
任课教师char(8)null)
--例4:
使用记录完整性约束、全局唯一标识符字段、参照性约束
createtable学生
(学号char(6)primarykey,
姓名varchar(8),
性别char
(2)default'男'check(性别='男'or性别='女'),
出生日期datetimenotnull,
出生地varchar(10),
入党日期datetime,check(入党日期>出生日期),
班级char(6)foreignkeyreferences班级(班级编号),
密码uniqueidentifierconstraintmimadefaultnewid())
--例5:
使用参照性约束
createtable排课
(班级编号char(6)notnullforeignkey(班级编号)references班级(班级编号),
课程编号char(8)notnullconstraint课号foreignkeyreferences课程(课程编号),
教室号char(6)notnullreferences教室(教室编号),
学期char(4),
起止周char(5))
--例6:
createtable成绩
(学号char(6)notnullreferences学生(学号),
课程编号char(8)notnullreferences课程(课程编号),
分数decimal(4,1)check(分数>=0and分数<=100))
8、语句修改表
--例1:
删除列
altertable排课drop
column起止周
--例2:
添加列
altertable排课add
开始周tinyint,
星期tinyint,
节次char(4),
结束周tinyint,constraintpklcheck(结束周>=开始周)
--例3:
删除约束(课程表中的学分约束)
altertable课程dropconstraintXF
--例4:
添加列
altertable学生add
已修学分decimal(4,1)default0,
组长char(6)nullconstraintXS_ZZreferences学生(学号)
--例5:
添加约束
altertable学生add
constraintBJforeignkey(班级)references班级(班级编号)
9、语句在表中插入数据
--例1:
插入数据
insert班级values('200201','郭大壮',30,32)
--例2:
insertselect语句修改表,将表“班级0”中的数据导入表“班级”中
altertable班级add班级char(10)//插入“班级”列
insert班级(班级编号,班级,班主任,男生人数,女生人数)
select班级编号,班级,班主任,男生人数,女生人数
from班级0
--例3:
处理null和default值
①建表如下:
createtable职工
(编号smallintidentity(1,2),
姓名char(8)notnull,
性别char
(2)default'男',
年龄tinyint,
民族char(10)notnulldefault'汉族')
②插入数据:
情况一:
insert职工values('张三','女',30,'回族')
//各列显示为:
1张三女30回族
情况二:
insert职工(姓名,民族)values('张三','回族')
//各列显示为:
3张三男null回族
insert职工(姓名)values('张三')
//各列显示为:
5张三男null汉族
情况三:
insert职工(姓名,民族)values(null,'回族')
//姓名已声明为notnull,若指定为null则失败
insert职工(民族)values('回族')
//姓名已声明为notnull,若省略则失败
情况四:
insert职工values('张三',null,default,'蒙古族')
//各列显示为:
11张三nullnull蒙古族
情况五:
insert职工values(null,null,default,'蒙古族')
//姓名已声明为notnull,若指定为null则失败
insert职工values(default,null,default,'蒙古族')
//姓名已声明为notnull,若指定为default则失败
insert职工(民族)values('回族')
//姓名已声明为notnull,若省略则失败
情况六:
insert职工(姓名,年龄)values('张三',null)
//各列显示为:
21张三男null汉族
情况七:
insert职工values(default,default,null,default,'蒙古族')
//编号标识属性,若指定为default则失败
10、数据操作
☞更新数据:
update语句
--例1:
更新某列(统计男、女生数及总人数)
update班级set男生人数=(selectcount(*)
from学生
where班级.班级编号=班级and性别='男')
update班级set女生人数=(selectcount(*)//波浪线处可以用,替换
from学生
where班级.班级编号=班级and性别='女')
--例2:
更新某列(统计学分)
update学生set已修学分=(selectsum(学分)
from成绩,课程
where学生.学号=成绩.学号and分数>=60
and成绩.课程编号=课程.课程编号)
☞获取数据:
select语句
--例1:
单表查询
①查找在1983年6月01日出生的学生的姓名,性别,民族,按日期降序排列
select姓名,性别,民族
from学生
where出生日期>'1983-6-01'
orderby出生日期DESC
--例2:
多表查询:
①查找已修学分达到20分的学生的姓名,出生日期,班级名称和已修学分
select姓名,出生日期,班级.班级as班级名称,已修学分//as定义别名
from学生,班级
where学生.班级=班级.班级编号and已修学分>=20
②查找班主任'刘成河’班的全部男生的信息
select*
from学生,班级
where班主任='刘成河'and班级编号=学生.班级and性别='男'
③查找“中国历史”考试及格的全部学生的学号,姓名,班级名称和分数
select学生.学号,姓名,班级,分数
from学生,成绩,课程
where课程名称='中国历史'and课程.课程编号=成绩.课程编号
and学生.学号=成绩.学号and分数>=60
--例3:
多行子查询in语句:
①查找出“计算机应用”课程考试取得95分以上的学生的学号和姓名
select学号,姓名
from学生
where学号in
(select学号
from成绩
where分数>=95and课程编号in
(select课程编号
from课程
where课程名称='计算机应用'))
②查找所有学生考试都及格的班级名称
方法一:
方法二:
select班级select班级
from班级from班级
where班级编号notinwherenotexists
(select班级(select*
from学生from学生,成绩
where学号inwhere分数<60and班级.班级编号=学生.班级(select学号and成绩.学号=学生.学号)
from成绩
where分数<60))
--例4:
多行子查询exists语句:
①查找考试成绩全部及格的课程名称
select课程名称
from课程
wherenotexists
(select*
from成绩
where成绩.课程编号=课程.课程编号and分数<60)
②查找所有学生考试成绩都及格的班级,并排除那些没有学生的班级
select班级
from班级
where总人数>0andnotexists
(select*
from学生
whereexists
(select*
from成绩
where分数<60and班级.班级编号=学生.班级and成绩.学号=学生.学号))
--例5:
字符匹配符查询:
%,_,[],[^]
①查找姓名中第二个字是“林”的学生信息
select*
from学生
where姓名like'_林%'//也可写为:
where姓名like'_[林]%'
②查找姓名中第二个字是“林”或“爱”的学生信息
select*
from学生
where姓名like'_[林爱]%'//[]不可以省略
--例6:
聚合查询:
sum,avg,max,abs,rand,
①查找“数学专业”班“数学”课程考试最高分的学生姓名和分数
select姓名,分数
from学生,成绩
where学生.学号=成绩.学号
and班级=(select班级编号from班级where班级='数学专业')
and课程编号=(select课程编号from课程where课程名称='数学')
and分数=
(selectmax(分数)
from学生,成绩
where学生.学号=成绩.学号
and班级=(select班级编号from班级where班级='数学专业')
and课程编号=(select课程编号from课程where课程名称='数学'))
--例7:
别名及其应用:
①查找和计算各门课程名及平均分数
select课程名称,均分
from课程,(select课程编号,avg(分数)as均分
from成绩
groupby课程编号)asjf
where课程.课程编号=jf.课程编号
②查找和计算各门课程名及考试达到90分的人数
select课程名称,count(*)as人数
from课程,成绩
where课程.课程编号=成绩.课程编号and分数>=90
groupby课程名称
③查找考试成绩优秀人数多于3人的课程名称及人数(分数达到90分为优秀)
select课程名称,count(*)as优秀人数
from成绩,课程
where分数>=90and课程.课程编号=成绩.课程编号
groupby课程名称
havingcount(*)>3
--例8:
多表查询:
①复制表的信息创建新表(select…into)
select*intoxs1from学生
导入表的信息至已有表中(insert…into)
insertintocj1select*from成绩
②内连接(innerjion……on)
select学生.学号,姓名,分数
from学生innerjoin成绩
on学生.学号=成绩.学号
where分数>=96
③外连接(left/right/fullouterjoin……on)
selectxs1.学号,xs1.姓名,xs2.学号,xs2.姓名
fromxs1leftouterjoinxs2
onxs1.学号=xs2.学号
④联合查询:
union
⑤多表交集:
ineresect
select学号fromxs1
intersect
select学号fromxs2
⑥多表差集:
except
select学号fromxs1
except
select学号fromxs2
11、表的索引、视图
☞索引:
--例1:
命令创建索引
createuniqueindex学生学号on学生(学号)on'primary'//非聚集索引
createclusteredindex学生学号onXS(学号)on'primary'//聚集索引
--例2:
命令删除索引
dropindex学生.学生学号
☞索引的几点说明:
1.一个表只能包含一个聚集索引,最多允许有249个非聚集索引。
2.默认情况下,PRIMARYKEY建立的索引是聚集索引;UNIQUE建立的索引是非聚集索引。
3.索引名在表或视图中必须唯一,但在数据库中不必唯一。
4.ntext、text、image数据类型的列不能作为索引列。
5.所有索引名都被保存在本数据库的系统视图sys.indexes的name列中。
6.在CREATETABLE和ALTERTABLE命令中建立的索引不能用DROPINDEX命令删除,只能用ALTERTABLE命令修改。
☞视图:
--例1:
命令创建视图
createview考试as
SELECT姓名,课程名称,分数
FROM学生
INNERJOIN成绩ON学生.学号=成绩.学号
INNERJOIN课程ON成绩.课程编号=课程.课程编号
--例2:
命令删除索引
dropview考试
12、SQL语言
☞控制语言:
--例1:
if……else条件语句
--例2:
case循环语句
--例3:
while循环语句
declare@sumnumeric(20),@snumeric(20),@itinyint
select@sum=1,@i=1,@s=1
while@i<64
begin
set@s=2*@s
set@i=@i+1
set@sum=@sum+@s
end
print@sum
☞函数
--例1:
常用函数
declare@ismallint,@nsmallint/*输出菱形图案*/
select@n=20,@i=1-@n
while@i<@n
begin
printspace(10+abs(@i))+replicate('*',(@n-1-abs(@i))*2+1)
set@i=@i+1
end
--题2:
创建自定义函数
createfunctionSalaryP(@anvarchar(20))/*形参类型为nvarchar,变量为@a*/
returnsdecimal(3,2)/*返回类型为decimal*/
as
begin
declare@bdecimal(3,2)/*定义类型为decimal的变量@b*/
select@b=case
when@a='技术部'then1.10
when@a='服务部'then1.15
when@a='营销部'then1.20
else1.05
end
return@b/*返回变量@b的值*/
end
/*函数测试:
printdbo.SalaryP('技术部')*/
--题3:
用自定义函数完成“调薪记录”表的更新操作
insert调薪记录
select员工编号,getdate(),'普调',工资,工资*dbo.SalaryP(部门名称)
from部门信息,员工信息
where部门编号=所在部门编号
--题4:
用自定义函数完成“员工信息”表的更新操作
update员工信息
set工资=工资*dbo.SalaryP(部门名称)
from部门信息
where部门编号=所在部门编号
☞游标:
--例1:
创建游标
例1:
declare成绩单cursorfor
select姓名,课程名称,分数
from成绩,学生,课程
where成绩.学号=学生.学号and成绩.课程编号=课程.课程编号
orderby姓名
例2:
declare成绩表cursorscrollfor
select姓名,课程名称,
case(round(分数/10,0,1))
when10then'优秀'
when9then'优秀'
when8then'良好'
when7then'一般'
when6then'差'
else'不及格'
end
as成绩
from成绩,学生,课程
where成绩.学号=学生.学号and成绩.课程编号=课程.课程编号
--例2:
打开游标
open成绩单
--例3:
检索游标
declare@xmchar(8),@kcmcvarchar(20),@fsdecimal(4,1)
fetchnext
from成绩单
into@xm,@kcmc,@fs
select@xm,@kcmc,@fs
/*fetch后面可接:
next|prior|first|last|
absolute数字n(绝对位置第n行)|relative数字n(当前行前后第n行)*/
--例4:
关闭游标
close成绩单
close成绩表
--例5:
释放游标
deallocate成绩单
deallocate成绩表
13、存储过程与触发器
☞存储过程:
(普通存储过程)
--例1:
创建普通存储过程
/*在成绩表中插入一条记录,若成绩合格,需在学生表中增加相应学分*/
createprocedure增加分数@xhchar(8),@kchchar(8),@fsdecimal(4,1)
as/*procedure可简写成proc*/
ifexists(select*from学生where学号=@xh)
ifexists(select*from课程where课程编号=@kch)
begin
insert成绩表values(@xh,@kch,@fs)
if@fs>=60
update学生
set已修学分=已修学分+(select学分from课程where课程编号=@kch)
where学号=@xh
end
else
print'无此课程号'+@kch
else
print'无此学号'+@xh
/*在成绩表中删除一条记录,若成绩合格,需在学生表中减去相应学分*/
createprocedure修改学分@xhchar(8),@kchchar(8)
as
declare@fsdecimal(4,1)
set@fs=(selectmax(分数)from成绩where学号=@xhand课程编号=@kch)
deletefrom成绩where学号=@xhand课程编号=@kch
if@fs>=60
update学生
set已修学分=已修学分-(select学分from课程where课程编号=@kch)
where学号=@xh
--例2:
执行存储过程
execute增加学分'20040301','w2020610',88
--例3:
存储过程参数
createproc班级人数@Nintoutput,@IDchar(6)='200401'
as
select@N=count(*)from学生where班级编号=@ID
/*存储过程参数*/
declare@Mint
exec班级人数@Moutput,'200501'/*参数'200501'可省略*/
s
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server复习 Server 复习
![提示](https://static.bdocx.com/images/bang_tan.gif)