SQL SERVER复习提纲

SQL SERVER复习提纲第一部分SQL 常用命令一、数据库操作1．创建数据库简单创建CREATE DATABASE 数据库名例：create database test完整定义：例：CREATE DATABASE sales_1ON PRIMARY(NAME= sales_1_data,FILENAME='d:\ sales_1.mdf',SIZE=10,MAXSIZE=unlimited,FILEGROWTH=20%)LOG ON(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．修改数据库大小例：DBCC SHRINKDATABASE（test,40,NOTRUNCATE）DBCC SHRINKFILE（test_1,4）4．删除数据库DROP DATABASE 数据库名5．查看数据库信息sp_helpdb 查看当前服务器上的所有数据库信息，可以指定数据库名称 sp_databases查看当前服务器上的所有可以使用的数据库sp_helpfile 查看当前数据库中的所有文件信息，可以指定文件名sp_helpfilegroup 查看当前数据库中的所有文件组信息，可以指定文件组二、表操作1．创建表CREATE TABLE 数据表名(字段1名字段1的数据类型,字段2名字段2的数据类型,. . . ,字段n名字段n的数据类型)例：create table card(card_id char(6),card_name char(10),card_type char(10),card_credit decimal(5,2), card_date datetime)Create table usecard (card_id char(6), exp_type char(20) exp char(100),score decimal(10,2),scoredate datetime)注意：常用数据类型整型：int 不能设置长度浮点型：Decimal(长度，小数) numeric(长度，小数)字符型：char(10) varchar(10)日期型：datetime 不能设置长度当要创建表同时创建主键约束：create table card(card_id char(6) constraint pk_name(约束名) PRIMARYKEY CLUSTERED,card_name char(10),card_credit decimal(5,2),card_date datetime)2．修改表结构在表中增加一个的字段ALTER TABLE 数据表名ADD 字段名字段数据类型例：alter table card add card_grade int在表中删除一个字段ALTER TABLE 数据表名DROP COLUMN 字段名例：alter table card drop column card_grade修改表中某字段数据类型ALTER TABLE 数据表名ALTER COLUMN 字段名字段数据类型例：alter table card alter column card_grade decimal(5,2)为已存在的表设置主键（先将要设置成主键的字段修改为NOT NULL）ALTER TABLE 数据表名ADD CONSTRAINT card_pk PRIMARY KEY CLUSTERED(card_id)例：alter table card ADD constraint pk_name(约束名) PRIMARY KEY CLUSTERED(card_id)3．查看表sp_help 数据表名sp_depends 查看表的相关性sp_helpconstraint 查看表的约束4．删除表DROP TABLE 数据表名例：drop table card5．重命名表sp_rename oldname,newname 例：sp_rename card1,card6．插入记录INSERT INTO 数据表名（字段1，字段2，. . .，字段n）V ALUES（字段1值，字段2值，. . .，字段n值）例：insert into card(card_id,card_name,card_credit,card_date) values('1001','王晓明',15,'20070101')7．更新记录UPDATE 数据表名SET 字段1名=字段1值，字段2名=字段2值，. . .，字段n名=字段n值例：update card set card_name='wanghong',card_credit=20 where card_id='1001' 8．删除记录DELETE FROM 数据表名WHERE 条件例：delete from card where card_id='1001'9．查询命令SELECT select_ist FROM table_source [WHERE search_condition] [GROUP BY groupby_expression] [HA VING serch_conditing] [ORDER BY order_expression[ASC|DESC]]例：1．查询card表中的所有记录select * from card2．查询card表中card_credit字段，并去掉重复值select distinct card_credit from card3．查询card表中card_id,card_name,card_credit字段select card_id,card_name,card_credit from card4．查询card表中的前10条记录select top 10 * from card5．查询card表中前面10%的记录select top 10 percent * from card6．用INTO子句创建一个新表card1,表中包含card表的前10%的记录select top 10 percent * into card1 from card7．查询card表中card_credit大于15的记录select * from card where card_credit>158．查询card表中card_credit大于15,并且日期在20070101之前的记录select * from card where card_credit>15 and card_date<’20070101’9．查询card表中姓“张”的记录select * from card where card_name like '张%'10．查询card表中card_credit在10和20之间的记录select * from card where card_credit between 10 and 2011．查询card表中card_credit为12,15的记录select * from card where card_credit in (12,15)12．查询card表中每种card_type 积分的记录select card_type,sum(card_credit) from card group by (card_type)13．使用CUBE分组查询card表中每种card_type 积分的记录select card_type,sum(card_credit) from card group by (card_type) with cube 14．使用ROLLUP分组查询card表中每种card_type 积分的记录select card_type,sum(card_credit) from card group by (card_type) withrollup15．对card 表按card_credit 进行降序排序select * from card order by card_credit desc16．统计card 表中所有记录的平均积分select * from card compute avg(card_credit)17．对card 表中card_id 和card_use表中的card_id 进行联合查询select card_id from card union select card_id from usecard18．联合查询card表和usecard 表，显示用户名（card_name）、类型（exp_type）、情况描述（exp）、检查分数（score）检查时间（scoredate）select * from card compute avg(card_credit)19．查看持卡人姓名（card_name）、检查类型（exp_type）、检查情况（exp）、检查分数（score）不使用别名：select card.card_name,usecard.exp_type,usecard.exp,usecard.scoredate from usecard ,card where card.card_id=usecard.card_id用别名：select c.card_name,u.exp_type,u.exp,u.scoredate from usecard as u,card asc where c.card_id=u.card_idselect s.stu_name,c.course_name,e.t_grade from stu as s,course as c,examas e where s,stu_id=e.stu_id and c.course_id=e.course_id 20．嵌套查询（书上例子P127 实例4.19）select detail_id,goods_id,name,price from receive_detail where piciin(select pici from output_detail where (sale_price-price)*quantity>2)三、视图操作1．创建视图CREATE VIEW 视图名AS 查询命令例：create view card_view as select * from card2．查看视图信息sp_help 视图名例：sp_help card_viewsp_helptext 显示视图的定义例：sp_helptext card_viewsp_depends 显示视图所依赖的对象例：sp_depends card_view3．修改视图ALTER VIEW 视图名AS 查询语句WITH CHECK OPTION例：alter view card_view as select * from card where card_credit>20 with check option注意WITH CHECK OPTION 是对条件进行检查，如果没有with check option 不符合条件的记录也会被插入表中例：insert into card_view values('1001','王晓明',15,'20070101') 插入的card_credit 是15 比20小，但因为没做检查还是插入到表里了4．通过视图插入数据记录INSERT INTO 视图名V ALUES（字段1值，字段2值，. . .，字段n值）例：insert into card_view values('1001','王晓明',21,'20070101')5．通过视图修改数据记录UPDATE 视图名SET 字段1名=字段1值，字段2名=字段2值，. . .，字段n名=字段n值例：update card_view set card_name='wanghong',card_credit=20 where card_id='1001'6．通过视图删除数据记录DELETE FROM 视图名WHERE 条件例：delete from card_view where card_id='1001'7．重命名视图sp_rename oldname,newname 例：sp_rename card_view,card_view1 8．删除视图DROP VIEW 视图名例：drop view card_view四、索引的使用1．创建索引CREATE INDEX 索引名ON 表或视图(字段名)创建聚集唯一索引例：create unique clustered index in_card on card(card_id)创建唯一索引例：create unique index card_index on card(card_id)创建复合索引例：create index card_comindex on card(card_name,card_date)2．查看索引sp_helpindex 索引名所属的表名3．重命名索引sp_rename [@objname]‘oldname’,’newname’,’index’例：sp_rename 'card.card_index','card_index1','index'4．删除索引DROP INDEX 表名．索引名例：drop index card.card_index五、存储过程1．创建存储过程创建存储过程之前要先判断这个存储过程名是否已经存在（用IF EXISTS判断）例：IF EXISTS(SELECT name FROM sysobjects WHERE name=’card_pro’ AND type=’P’) DROP PROCEDURE card_pro 创建不带参数的存储过程CREATE PROCEDURE 存储过程名AS 查询语句例：CREATE PROCEDURE stu_pro AS select * from exam执行该存储过程有2种方式：✧直接打存储过程名：例：stu_pro✧用EXECUTE存储过程名：例：EXECUTE stu_pro创建带参数的存储过程CREATE PROCEDURE 存储过程名@变量名变量数据类型AS 查询语句例：CREATE PROCEDURE stu_pro2 @tnum char(8) AS select * from exam where t_number=@tnum执行该存储过程有3种方式：✧直接打存储过程名参数的值：例：stu_pro2 '20040301'✧用EXECUTE存储过程名：例：execute stu_pro2 '20040301'✧用EXECUTE存储过程名：例：execute stu_pro2 @tnum='20040301'创建带参数且有返回值的存储过程例：CREATE PROCEDURE stu_pro3 @tnum char(8),@cnumchar(6),@grade char(10) outputAS declare @errorvalue intset @errorvalue=0select @grade=t_grade from exam where t_number=@tnum andc_number=@cnumif (@@ERROR<>0)set @errorvalue=@@ERRORreturn @errorvalue执行：declare @returnvalue int,@tnum char(8),@cnum char(6),@grade char(10)select @tnum=20040301,@cnum=100101execute @returnvalue=stu_pro3 @tnum,@cnum,@grade outputselect @returnvalue as aselect @grade as b2．查看存储过程sp_help 存储过程名例：sp_help card_prosp_helptext 显示存储过程的定义例：sp_helptext card_prosp_depends 显示存储过程所依赖的对象例：sp_depends card_prosp_stored_procedures 当前存储过程列表3．修改存储过程ALTER PROCEDURE 存储过程名AS 查询语句例：alter procdure card_pro as select * from card where t_grade<3 and card_credit>1005．重命名存储过程sp_rename oldname,newname 例：sp_rename card_pro,card_pro1 6．删除存储过程：DROP PROCEDURE 存储过程名例：drop procedure card_pro1六、触发器1．创建触发器创建触发器之前要先判断这个触发器名是否已经存在（用IF EXISTS判断）例：IF EXISTS(SELECT name FROM sysobjects WHERE name=’ insert_data’ AND type=’Tr’) DROP TRIGGER card_pro创建INSERT触发器CREATE TRIGGER 触发器名ON 表名/视图名AFTER INSERT AS 触发内容例：创建在exam表中当输入分数大于100就拒绝这条记录插入if exists(select name from sysobjectsWhere name='insert_data' and type='tr')Drop trigger insert_datacreate trigger insert_data on exam after insert asif(select t_grade from inserted)>100beginprint 'not over 100'rollbackend创建UPDATE触发器CREATE TRIGGER 触发器名ON 表名/视图名AFTER UPDATE AS 触发内容例：创建在exam表中当输入分数大于100就拒绝这条记录修改if exists(select name from sysobjectsWhere name='update_data' and type='tr')Drop trigger update_datacreate trigger update_data on exam after update asif update(t_grade)beginif(select t_grade from inserted)>100beginprint '分数不能大于100'rollback transactionendend创建INSERT、UPDATE触发器CREATE TRIGGER 触发器名ON 表名/视图名AFTER INSERT，UPDATE AS 触发内容例：if exists(select name from sysobjectsWhere name='update_data' and type='tr')Drop trigger update_datacreate trigger update_data on exam for insert,update asif update(t_grade)beginif(select t_grade from inserted)>100beginprint '分数不能大于100'rollback transactionendend创建INSTEAD OF 触发器CREATE TRIGGER 触发器名ON 表名/视图名INSTEAD OFDELETE AS 触发内容例：create trigger exam_tri on exam instead of delete as ‘不允许删除记录！’2．查看触发器sp_help 触发器名例：sp_help exam_trisp_helptext 显示触发器的定义例：sp_helptext exam_trisp_depends 显示触发器所依赖的对象例：sp_depends exam_tri3．修改触发器修改INSERT触发器ALTER TRIGGER 触发器名ON 表名/视图名AFTER INSERT AS 触发内容修改UPDATE触发器ALTER TRIGGER 触发器名ON 表名/视图名AFTER UPDATE AS 触发内容修改INSERT、UPDATE触发器ALTER TRIGGER 触发器名ON 表名/视图名AFTER INSERT，UPDATE AS 触发内容修改INSTEAD OF 触发器ALTER TRIGGER 触发器名ON 表名/视图名INSTEAD OF DELETEAS 触发内容4．重命名触发器sp_rename oldname,newname 例：sp_rename card_tri,card_tri15．删除触发器DROP TRIGGER触发器名例：drop trigger card_tri1第二部分知识点一、数据库的有关概念：数据：文字、图表、数字、图片等等数据库对象：表、视图、存储过程、触发器、规则等等数据库：是数据和数据库对象的集合数据库管理系统：管理数据的计算机软件，提供了用户和数据库之间的软件界面，使用户更方便的操作数据库。