SQLSERVER复习提纲.docx
《SQLSERVER复习提纲.docx》由会员分享，可在线阅读，更多相关《SQLSERVER复习提纲.docx（17页珍藏版）》请在冰豆网上搜索。
SQLSERVER复习提纲
SQLSERVER复习提纲
第一部分SQL常用命令
一、数据库操作
1．创建数据库
Ø简单创建CREATEDATABASE数据库名例：
createdatabasetest
Ø完整定义：
例：
CREATEDATABASEsales_1
ONPRIMARY
（NAME=sales_1_data,
FILENAME='d:
\sales_1.mdf',
SIZE=10,
MAXSIZE=unlimited,
FILEGROWTH=20%）
LOGON
（NAME=sales_1_log,
FILENAME='d:
\sales_1_log.ldf',
SIZE=3,
MAXSIZE=10,
FILEGROWTH=2）
2．更改数据库名称
sp_renamedb'oldname','newname'例：
sp_renamedb'test','test1'
3．修改数据库大小
例：
DBCCSHRINKDATABASE（test,40,NOTRUNCATE）
DBCCSHRINKFILE（test_1,4）
4．删除数据库
DROPDATABASE数据库名
5．查看数据库信息
Øsp_helpdb查看当前服务器上的所有数据库信息，可以指定数据库名称
Øsp_databases查看当前服务器上的所有可以使用的数据库
Øsp_helpfile查看当前数据库中的所有文件信息，可以指定文件名
Øsp_helpfilegroup查看当前数据库中的所有文件组信息，可以指定文件组
二、表操作
1．创建表
ØCREATETABLE数据表名（字段1名字段1的数据类型,字段2名字段2的数据类型,...,字段n名字段n的数据类型）
例：
createtablecard（card_idchar（6）,card_namechar（10）,card_typechar（10）,card_creditdecimal（5,2）,card_datedatetime）
Createtableusecard（card_idchar（6）,exp_typechar（20）expchar（100）,scoredecimal（10,2）,scoredatedatetime）
注意：
常用数据类型
整型：
int不能设置长度
浮点型：
Decimal（长度，小数）numeric（长度，小数）
字符型：
char（10）varchar（10）
日期型：
datetime不能设置长度
Ø当要创建表同时创建主键约束：
createtablecard（card_idchar（6）constraintpk_name（约束名）PRIMARYKEYCLUSTERED,card_namechar（10）,card_creditdecimal（5,2）,card_datedatetime）
2．修改表结构
Ø在表中增加一个的字段
ALTERTABLE数据表名ADD字段名字段数据类型
例：
altertablecardaddcard_gradeint
Ø在表中删除一个字段
ALTERTABLE数据表名DROPCOLUMN字段名
例：
altertablecarddropcolumncard_grade
Ø修改表中某字段数据类型
ALTERTABLE数据表名ALTERCOLUMN字段名字段数据类型
例：
altertablecardaltercolumncard_gradedecimal（5,2）
Ø为已存在的表设置主键（先将要设置成主键的字段修改为NOTNULL）
ALTERTABLE数据表名ADDCONSTRAINTcard_pkPRIMARYKEYCLUSTERED（card_id）
例：
altertablecardADDconstraintpk_name（约束名）PRIMARYKEYCLUSTERED（card_id）
3．查看表
Øsp_help数据表名
Øsp_depends查看表的相关性
Øsp_helpconstraint查看表的约束
4．删除表
DROPTABLE数据表名例：
droptablecard
5．重命名表
sp_renameoldname,newname例：
sp_renamecard1,card
6．插入记录
INSERTINTO数据表名（字段1，字段2，...，字段n）VALUES（字段1值，字段2值，...，字段n值）
例：
insertintocard（card_id,card_name,card_credit,card_date）values（'1001','王晓明',15,'20070101'）
7．更新记录
UPDATE数据表名SET字段1名=字段1值，字段2名=字段2值，...，字段n名=字段n值
例：
updatecardsetcard_name='wanghong',card_credit=20wherecard_id='1001'
8．删除记录
DELETEFROM数据表名WHERE条件
例：
deletefromcardwherecard_id='1001'
9．查询命令
SELECTselect_istFROMtable_source[WHEREsearch_condition][GROUPBYgroupby_expression][HAVINGserch_conditing][ORDERBYorder_expression[ASC|DESC]]
例：
1．查询card表中的所有记录
select*fromcard
2．查询card表中card_credit字段，并去掉重复值
selectdistinctcard_creditfromcard
3．查询card表中card_id,card_name,card_credit字段
selectcard_id,card_name,card_creditfromcard
4．查询card表中的前10条记录
selecttop10*fromcard
5．查询card表中前面10%的记录
selecttop10percent*fromcard
6．用INTO子句创建一个新表card1,表中包含card表的前10%的记录
selecttop10percent*intocard1fromcard
7．查询card表中card_credit大于15的记录
select*fromcardwherecard_credit>15
8．查询card表中card_credit大于15,并且日期在20070101之前的记录
select*fromcardwherecard_credit>15andcard_date<’20070101’
9．查询card表中姓“张”的记录
select*fromcardwherecard_namelike'张%'
10．查询card表中card_credit在10和20之间的记录
select*fromcardwherecard_creditbetween10and20
11．查询card表中card_credit为12,15的记录
select*fromcardwherecard_creditin（12,15）
12．查询card表中每种card_type积分的记录
selectcard_type,sum（card_credit）fromcardgroupby（card_type）
13．使用CUBE分组查询card表中每种card_type积分的记录
selectcard_type,sum（card_credit）fromcardgroupby（card_type）withcube
14．使用ROLLUP分组查询card表中每种card_type积分的记录
selectcard_type,sum（card_credit）fromcardgroupby（card_type）withrollup
15．对card表按card_credit进行降序排序
select*fromcardorderbycard_creditdesc
16．统计card表中所有记录的平均积分
select*fromcardcomputeavg（card_credit）
17．对card表中card_id和card_use表中的card_id进行联合查询
selectcard_idfromcardunionselectcard_idfromusecard
18．联合查询card表和usecard表，显示用户名（card_name）、类型（exp_type）、情况描述（exp）、检查分数（score）检查时间（scoredate）
select*fromcardcomputeavg（card_credit）
19．查看持卡人姓名（card_name）、检查类型（exp_type）、检查情况（exp）、检查分数（score）
不使用别名：
selectcard.card_name,usecard.exp_type,usecard.exp,usecard.scoredatefromusecard,cardwherecard.card_id=usecard.card_id
用别名：
selectc.card_name,u.exp_type,u.exp,u.scoredatefromusecardasu,cardascwherec.card_id=u.card_id
selects.stu_name,c.course_name,e.t_gradefromstuass,courseasc,examasewheres,stu_id=e.stu_idandc.course_id=e.course_id
20．嵌套查询（书上例子P127实例4.19）
selectdetail_id,goods_id,name,pricefromreceive_detailwherepiciin（selectpicifromoutput_detailwhere（sale_price-price）*quantity>2）
三、视图操作
1．创建视图
CREATEVIEW视图名AS查询命令
例：
createviewcard_viewasselect*fromcard
2．查看视图信息
Øsp_help视图名例：
sp_helpcard_view
Øsp_helptext显示视图的定义例：
sp_helptextcard_view
Øsp_depends显示视图所依赖的对象例：
sp_dependscard_view
3．修改视图
ALTERVIEW视图名AS查询语句WITHCHECKOPTION
例：
alterviewcard_viewasselect*fromcardwherecard_credit>20withcheckoption
注意WITHCHECKOPTION是对条件进行检查，如果没有withcheckoption不符合条件的记录也会被插入表中
例：
insertintocard_viewvalues（'1001','王晓明',15,'20070101'）插入的card_credit是15比20小，但因为没做检查还是插入到表里了
4．通过视图插入数据记录
INSERTINTO视图名VALUES（字段1值，字段2值，...，字段n值）
例：
insertintocard_viewvalues（'1001','王晓明',21,'20070101'）
5．通过视图修改数据记录
UPDATE视图名SET字段1名=字段1值，字段2名=字段2值，...，字段n名=字段n值
例：
updatecard_viewsetcard_name='wanghong',card_credit=20wherecard_id='1001'
6．通过视图删除数据记录
DELETEFROM视图名WHERE条件
例：
deletefromcard_viewwherecard_id='1001'
7．重命名视图
sp_renameoldname,newname例：
sp_renamecard_view,card_view1
8．删除视图
DROPVIEW视图名例：
dropviewcard_view
四、索引的使用
1．创建索引
CREATEINDEX索引名ON表或视图（字段名）
Ø创建聚集唯一索引
例：
createuniqueclusteredindexin_cardoncard（card_id）
Ø创建唯一索引
例：
createuniqueindexcard_indexoncard（card_id）
Ø创建复合索引
例：
createindexcard_comindexoncard（card_name,card_date）
2．查看索引
sp_helpindex索引名所属的表名
3．重命名索引
sp_rename[@objname]‘oldname’,’newname’,’index’
例：
sp_rename'card.card_index','card_index1','index'
4．删除索引
DROPINDEX表名．索引名例：
dropindexcard.card_index
五、存储过程
1．创建存储过程
Ø创建存储过程之前要先判断这个存储过程名是否已经存在（用IFEXISTS判断）
例：
IFEXISTS（SELECTnameFROMsysobjectsWHEREname=’card_pro’ANDtype=’P’）DROPPROCEDUREcard_pro
Ø创建不带参数的存储过程
CREATEPROCEDURE存储过程名AS查询语句
例：
CREATEPROCEDUREstu_proASselect*fromexam
执行该存储过程有2种方式：
✧直接打存储过程名：
例：
stu_pro
✧用EXECUTE存储过程名：
例：
EXECUTEstu_pro
Ø创建带参数的存储过程
CREATEPROCEDURE存储过程名@变量名变量数据类型AS查询语句
例：
CREATEPROCEDUREstu_pro2@tnumchar（8）ASselect*fromexamwheret_number=@tnum
执行该存储过程有3种方式：
✧直接打存储过程名参数的值：
例：
stu_pro2'20040301'
✧用EXECUTE存储过程名：
例：
executestu_pro2'20040301'
✧用EXECUTE存储过程名：
例：
executestu_pro2@tnum='20040301'
Ø创建带参数且有返回值的存储过程
例：
CREATEPROCEDUREstu_pro3@tnumchar（8）,@cnumchar（6）,@gradechar（10）output
ASdeclare@errorvalueint
set@errorvalue=0
select@grade=t_gradefromexamwheret_number=@tnumandc_number=@cnum
if（@@ERROR<>0）
set@errorvalue=@@ERROR
return@errorvalue
执行：
declare@returnvalueint,@tnumchar（8）,@cnumchar（6）,@gradechar（10）
select@tnum=20040301,@cnum=100101
execute@returnvalue=stu_pro3@tnum,@cnum,@gradeoutput
select@returnvalueasa
select@gradeasb
2．查看存储过程
Øsp_help存储过程名例：
sp_helpcard_pro
Øsp_helptext显示存储过程的定义例：
sp_helptextcard_pro
Øsp_depends显示存储过程所依赖的对象例：
sp_dependscard_pro
Øsp_stored_procedures当前存储过程列表
3．修改存储过程
ALTERPROCEDURE存储过程名AS查询语句
例：
alterprocdurecard_proasselect*fromcardwheret_grade<3andcard_credit>100
5．重命名存储过程
sp_renameoldname,newname例：
sp_renamecard_pro,card_pro1
6．删除存储过程：
DROPPROCEDURE存储过程名例：
dropprocedurecard_pro1
六、触发器
1．创建触发器
Ø创建触发器之前要先判断这个触发器名是否已经存在（用IFEXISTS判断）
例：
IFEXISTS（SELECTnameFROMsysobjectsWHEREname=’insert_data’ANDtype=’Tr’）DROPTRIGGERcard_pro
Ø创建INSERT触发器
CREATETRIGGER触发器名ON表名/视图名AFTERINSERTAS触发内容
例：
创建在exam表中当输入分数大于100就拒绝这条记录插入
ifexists（selectnamefromsysobjects
Wherename='insert_data'andtype='tr'）
Droptriggerinsert_data
createtriggerinsert_dataonexamafterinsertas
if（selectt_gradefrominserted）>100
begin
print'notover100'
rollback
end
Ø创建UPDATE触发器
CREATETRIGGER触发器名ON表名/视图名AFTERUPDATEAS触发内容
例：
创建在exam表中当输入分数大于100就拒绝这条记录修改
ifexists（selectnamefromsysobjects
Wherename='update_data'andtype='tr'）
Droptriggerupdate_data
createtriggerupdate_dataonexamafterupdateas
ifupdate（t_grade）
begin
if（selectt_gradefrominserted）>100
begin
print'分数不能大于100'
rollbacktransaction
end
end
Ø创建INSERT、UPDATE触发器
CREATETRIGGER触发器名ON表名/视图名AFTERINSERT，UPDATEAS触发内容
例：
ifexists（selectnamefromsysobjects
Wherename='update_data'andtype='tr'）
Droptriggerupdate_data
createtriggerupdate_dataonexamforinsert,updateas
ifupdate（t_grade）
begin
if（selectt_gradefrominserted）>100
begin
print'分数不能大于100'
rollbacktransaction
end
end
Ø创建INSTEADOF触发器
CREATETRIGGER触发器名ON表名/视图名INSTEADOFDELETEAS触发内容
例：
createtriggerexam_trionexaminsteadofdeleteas‘不允许删除记录！
’
2．查看触发器
Øsp_help触发器名例：
sp_helpexam_tri
Øsp_helptext显示触发器的定义例：
sp_helptextexam_tri
Øsp_depends显示触发器所依赖的对象例：
sp_dependsexam_tri
3．修改触发器
Ø修改INSERT触发器
ALTERTRIGGER触发器名ON表名/视图名AFTERINSERTAS触发内容
Ø修改UPDATE触发器
ALTERTRIGGER触发器名ON表名/视图名AFTERUPDATEAS触发内容
Ø修改INSERT、UPDATE触发器
ALTERTRIGGER触发器名ON表名/视图名AFTERINSERT，UPDATEAS触发内容
Ø修改INSTEADOF触发器
ALTERTRIGGER触发器名ON表名/视图名INSTEADOFDELETEAS触发内容
4．重命名触发器
sp_renameoldname,newname例：
sp_renamecard_tri,card_tri1
5．删除触发器
DROPTRIGGER触发器名例：
droptriggercard_tri1
第二部分知识点
一、数据库的有关概念：
Ø数据：
文字、图表、数字、图片等等
Ø数据库对象：
表、视图、存储过程、触发器、规则等等
Ø数据库：
是数据和数据库对象的集合
Ø数据库管理系统：
管理数据的计算机软件，提供了用户和数据库之间的软件界面，使用户更方便的操作数据库。
用户能方便的定义和操纵数据，维护数据的安全性和完整性、进行多用户下的控制操作。
Ø数据库系统：
由数据库、数据库管理系统及用户等等组成的
Ø关系模型：
由实体和联系构成。
关系模型以表的形式表现实体，表的一行表示实体的一个实例，一列描述实体的特征或属性；联系是实体之间的对应关系，有一对
升级会员 