SQL_SERVER复习提纲.doc

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(约束名) PRIMARY KEY 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 card 5 重命名表sp_rename oldname,newname 例：sp_rename card1,card6 插入记录INSERT INTO 数据表名（字段1，字段2，. . .，字段n） VALUES（字段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=10018 删除记录DELETE FROM 数据表名 WHERE 条件 例：delete from card where card_id=1001 9查询命令SELECT select_ist FROM table_source WHERE search_condition GROUP BY groupby_expression HAVING serch_conditing ORDER BY order_expressionASC|DESC例： 1查询card表中的所有记录 select * from card2查询card表中card_credit字段，并去掉重复值 select distinct card_credit from card 3查询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 card 6用INTO子句创建一个新表card1,表中包含card表的前10%的记录 select top 10 percent * into card1 from card7查询card表中card_credit大于15的记录 select * from card where card_credit158查询card表中card_credit大于15,并且日期在20070101之前的记录 select * from card where card_credit15 and card_date2) 三、视图操作 1创建视图 CREATE VIEW 视图名AS 查询命令 例：create view card_view as select * from card 2查看视图信息 sp_help 视图名 例：sp_help card_view sp_helptext 显示视图的定义 例：sp_helptext card_view sp_depends 显示视图所依赖的对象 例：sp_depends card_view 3修改视图ALTER VIEW 视图名AS 查询语句 WITH CHECK OPTION例：alter view card_view as select * from card where card_credit20 with check option注意 WITH CHECK OPTION 是对条件进行检查，如果没有with check option 不符合条件的记录也会被插入表中 例：insert into card_view values(1001,王晓明,15,20070101) 插入的card_credit 是15 比20小，但因为没做检查还是插入到表里了 4通过视图插入数据记录INSERT INTO 视图名VALUES（字段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 objnameoldname,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),cnum char(6),grade char(10) output AS declare errorvalue intset errorvalue=0select grade=t_grade from exam where t_number=tnum and c_number=cnumif (ERROR0) 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 b 2查看存储过程 sp_help 存储过程名 例：sp_help card_pro sp_helptext 显示存储过程的定义 例：sp_helptext card_pro sp_depends 显示存储过程所依赖的对象 例：sp_depends card_pro sp_stored_procedures 当前存储过程列表3修改存储过程ALTER PROCEDURE 存储过程名 AS 查询语句例：alter procdure card_pro as select * from card where t_grade100 5重命名存储过程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 as if(select t_grade from inserted)100beginprint not over 100rollback end 创建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) begin if(select t_grade from inserted)100 begin print 分数不能大于 100 rollback transaction endend 创建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) begin if(select t_grade from inserted)100 begin print 分数不能大于 100 rollback transaction endend 创建INSTEAD OF 触发器CREATE TRIGGER 触发器名 ON 表名/视图名 INSTEAD OF DELETE AS 触发内容例：create trigger exam_tri on exam instead of delete as 不允许删除记录！ 2查看触发器 sp_help 触发器名 例：sp_help exam_tri sp_helptext 显示触发器的定义 例：sp_helptext exam_tri sp_depends 显示触发器所依赖的对象 例：sp_depends exam_tri 3修改触发器 修改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 DELETE AS 触发内容 4重命名触发器sp_rename oldname,newname 例：sp_rename card_tri,card_tri1 5删除触发器 DROP TRIGGER触发器名 例：drop trigger card_tri1第二部分 知识点一、 数据库的有关概念： 数据：文字、图表、数字、图片等等 数据库对象：表、视图、存储过程、触发器、规则等等 数据库：是数据和数据库对象的集合 数据库管理系统：管理数据的计算机软件，提供了用户和数据库之间的软件界面，使用户更方便的操作数据库。用户能方便的定义和操纵数据，维护数据的安全性和完整性、进行多用户下的控制操作。 数据库系统：由数据库、数据库管理系统及用户等等组成的 关系模型：由实体和联系构成。关系模型以表的形式表现实体，表的一行表示实体的一个实例，一列描述实体的特征或属性；联系是实体之间的对应关系，有一对一的联系、一对多的联系、多对多的联系。 关系模型中的数据完整性：实体完整性（确保数据库中所有实体的唯一性，通过设定主键、唯一键、唯一所引等方法）、区域完整性（限定表中数据值的范围）、引用完整性（确保不同表之间关键字字段保持一致，可以通过外键、存储过程、触发器等来实现）、自定义完整性（可以通过规则、存储过程、触发器等来实现）。 约束：实现数据完整性最主要的方法，有主键约束、外键约束、唯一约束、检查约束、NOT NULL约束、默认约束 关系数据库：基于关系模型的数据库 事物：是数据库应用程序基本逻辑单元，是增强数据库一致性、完整性的重要机制。满足标准：原子性、一致性、隔离性、持久性；3种运行模式：自动提交的事务、显式事务、隐式事务二、SQL SERVER的概念 SQL SERVER 的发展历史：1998年，由Microsoft公司、Sysbase公司、Ashton-Tate公司合作开发的SQL SERVER 第一个版本OS/2版本；以后出现了SQL SERVER 6.5版本、SQL SERVER 7.0版本；2000年推出了SQL SERVER 2000版本。 常见版本：企业版（用于企业数据库）、标准版（用于小工作组）、个人版（用于移动用户）、开发版（作为开发和测试系统） SQL SERVER是客户机/服务器（C/S）体系结构的关系型数据库系统 安装SQL SERVER 2000 对软件、硬件都有要求 SQL语言： 数据定义语言 DDL（CREATE、ALTER、DROP） 数据操纵语言 DML（SELECT、INSERT、UPDATE、DELETE） 数据控制语言 DCL（GRANT、DENY、REVKOE） 其他语言元素（流程控制语言、内嵌函数、批处理等） SQL SERVER2000数据库存储结构：数据库文件：主数据库文件（扩展名.mdb，是数据库的起点，一个数据库只能有一个主数据库文件）辅助数据库文件（扩展名.ndb，一个数据库可以没有，也可以有多个辅助数据库文件）日志文件（扩展名.ldb，包含恢复数据库的日志信息，一个数据库至少有一个日志文件，可以有多个；数据修改先写入日志文件再写数据库，当日志文件空间已满，就算数据库还有空间，对数据库进行的所有更新操作也都无法进行）数据库文件组：一个文件只能存在于一个文件组；一个文件组只能被一个数据库使用；日志文件是独立的，不属于任何文件组。 SQL SERVER2000系统数据库：Master数据库：记录系统级别信息，一旦破坏，将导致系统瘫痪Model数据库：创建数据库的模板，一旦删除，系统无法正常使用Msdb数据库：为报警、任务调度、操作人员的操作提供存储空间Tempdb数据库：临时数据库Pubs和northwind：是系统提供的用户数据库 SQL SERVER2000数据库系统表：Sysdatabases表：每个数据库记录一条记录，只出现在master数据库中 sysobjects表：每个数据库对象记录一条记录，出现在所有数据库中 syscolumn表：记录表、视图每列信息，出现在所有数据库中 syscomments表：数据库对象的定义信息，出现在所有数据库中 sysindexes表：记录索引信息，出现在所有数据库中 sysusers表：记录所有服务器用户的信息，出现在所有数据库中 sysdepends表：记录数据库对象之间的关系，出现在所有数据库中 SQL SERVER2000 常用管理工具和实用工具（服务管理器、企业管理器、查询分析器、服务器网络实用工具、客户端网络实用工具、事件探测器、导入导出数据、联机丛书）三、Transact SQL 语言 Sql server 变量全局变量：以开头，用来记录服务器的活动状态，对用户而言，全局变量是只读的。P139常用全局变量局部变量：P143 只在声明局部变量的批处理、存储过程或语句块中使用；局部变量必须先用DECLARE声明后才可以使用（DECLARE 变量名 变量数据类型）； 局部变量赋值：用SET可以给一个变量赋值（SET 变量名=变量值）；用SELECT可以给多个变量赋值（SELECT 变量1名=变量1值，变量2名=变量2值） 例：DECLARE var1 char(6) SET var1=1234567 SELECT car1 as 变量值 Sql server 运算符 算术运算符：+ - * / %(取模) 例：22%6 为4（22除以6取余数） 赋值运算符：= 位运算符：& 与 | 或 按位异或 按位反 比较运算符：= 等 逻辑运算符：AND OR NOT 一元运算符 Sql server 函数P150 字符串函数：假设有一个字符串str= sql server LEFT（字符串，数字n）：从左边开始取n个字符 例：LEFT(str,6) 结果为： sqlRIGHT（字符串，数字n）：从右边开始取n个字符 例：RIGHT(str,9) 结果为：server LTRIM（字符串）：去除左边空格 例：LTRIM(str) 结果为：sql server RTRIM（字符串）：去除右边空格 例：RTRIM(str) 结果为： sql serverSUBSTRING（字符串，m，n）：从第m个字符取n个 例：SUBSTRING(str,4,10) 结果为：sql serverSTR（数字）：数字转换成字符STUFF（字符串1，m，n，字符串2）：字符串1从第m个字符去除n个字符+字符串2 例：STUFF(str,8,9,good) 结果为： sql goodUPPER（字符串）：转换成大写LOWER（字符串）：转换成大写 日期和时间函数：GETDATE()：取当前日期DATEADD（年/月/日，n，日期）：返回指定日期加n年/月/日的整数 例：DATEADD(dd,4,GETDATE()取当前日期4天后的日期DATEPART（年/月/日，日期）：返回指定日期年份/月份/天数的整数 例：DATEPART(mm,GETDATE() 取当前月份DATENAME（年/月/日，日期）：返回指定日期部分的字符串 DAY（日期）：返回日期天数的整数MONTH（日期）：返回日期月份的整数YEAR（日期）：返回日期年份的整数 数字函数：ABS（数字）：取绝对值SORT（数字）：取平方根CEILING（数字）：大于等于给定数字的最小整数 例：CEILING(15.2564) 结果为：16FLOOR（数字）：小于等于给定数字的最大整数例：FLOOR(15.2564) 结果为：15ROUND（数字，n）：四舍五入为精度为n的数例：ROUND(15.2564,2) 结果为：15.26RAND（）：返回0-1中的随机数 转换函数：CAST(表达式 AS 数据类型) 例：CAST(10.54,decimal(5,2) 结果将字符串转换成数字CONVERT(数据类型，表达式)例：CONVERT(deci