第一部分 SQL 常用命令
一、 数据库操作
1．创建数据库
nbsp; 简单创建 CREATE DATABASE 数据库名 例：create database test
nbsp; 完整定义：
例：
CREATE DATABASE sales_1
ON 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．修改数据库大小
例：DBCCSHRINKDATABASE（test,40,NOTRUNCATE）
DBCC SHRINKFILE（test_1,4）
4．删除数据库
DROPDATABASE 数据库名
5．查看数据库信息
nbsp;sp_helpdb查看当前服务器上的所有数据库信息，可以指定数据库名称
nbsp;sp_databases查看当前服务器上的所有可以使用的数据库
nbsp;sp_helpfile查看当前数据库中的所有文件信息，可以指定文件名
nbsp;sp_helpfilegroup查看当前数据库中的所有文件组信息，可以指定文件组
二、表操作
1．创建表
nbsp;CREATETABLE 数据表名(字段1名字段1的数据类型,字段2名字段2的数据类型,. . . ,字段n名 字段n的数据类型)
例：createtable card(card_id char(6),card_name char(10),card_type char(10),card_creditdecimal(5,2), card_date datetime)
Createtable usecard (card_id char(6), exp_type char(20) exp char(100),scoredecimal(10,2),scoredate datetime)
注意：常用数据类型
整型： int 不能设置长度
浮点型：Decimal(长度，小数) numeric(长度，小数)
字符型：char(10) varchar(10)
日期型：datetime不能设置长度
nbsp;当要创建表同时创建主键约束：
create table card(card_id char(6) constraint pk_name(约束名) PRIMARY KEY CLUSTERED,card_name char(10),card_creditdecimal(5,2), card_date datetime)
2．修改表结构
nbsp;在表中增加一个的字段
ALTER TABLE 数据表名 ADD 字段名 字段数据类型
例：altertable card add card_grade int
nbsp;在表中删除一个字段
ALTER TABLE 数据表名 DROPCOLUMN 字段名
例：altertable card drop column card_grade
nbsp;修改表中某字段数据类型
ALTER TABLE 数据表名 ALTERCOLUMN 字段名 字段数据类型
例：altertable card alter column card_grade decimal(5,2)
nbsp;为已存在的表设置主键（先将要设置成主键的字段修改为NOT NULL）
ALTER TABLE 数据表名 ADDCONSTRAINT card_pk PRIMARY KEY CLUSTERED(card_id)
例：altertable card ADD constraint pk_name(约束名) PRIMARYKEY CLUSTERED(card_id)
3． 查看表
nbsp;sp_help数据表名
nbsp;sp_depends查看表的相关性
nbsp;sp_helpconstraint查看表的约束
4． 删除表
DROP TABLE 数据表名 例：drop table card
5． 重命名表
sp_rename oldname,newname 例：sp_renamecard1,card
6． 插入记录
INSERT INTO 数据表名（字段1，字段2，. . .，字段n） VALUES（字段1值，字段2值，. . .，字段n值）
例：insertinto card(card_id,card_name,card_credit,card_date) values(‘1001′,’王晓明’,15,’20070101′)
7． 更新记录
UPDATE 数据表名 SET 字段1名=字段1值，字段2名=字段2值，. . .，字段n名=字段n值
例：updatecard 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 [WHEREsearch_condition] [GROUP BY groupby_expression] [HAVING serch_conditing] [ORDERBY order_expression[ASC|DESC]]
例：
1．查询card表中的所有记录
select * fromcard
2．查询card表中card_credit字段，并去掉重复值
selectdistinct card_credit from card
3．查询card表中card_id,card_name,card_credit字段
selectcard_id,card_name,card_credit from card
4．查询card表中的前10条记录
select top 10* from card
5．查询card表中前面10%的记录
select top 10percent * from card
6．用INTO子句创建一个新表card1,表中包含card表的前10%的记录
select top 10percent * into card1 from card
7．查询card表中card_credit大于15的记录
select * fromcard where card_credit>15
8．查询card表中card_credit大于15,并且日期在20070101之前的记录
select * fromcard where card_credit>15 and card_date<’20070101’
9．查询card表中姓“张”的记录
select * fromcard where card_name like ‘张%’
10．查询card表中card_credit在10和20之间的记录
select * fromcard where card_credit between 10 and 20
11．查询card表中card_credit为12,15的记录
select * fromcard where card_credit in (12,15)
12．查询card表中每种card_type积分的记录
selectcard_type,sum(card_credit) from card group by (card_type)
13．使用CUBE分组查询card表中每种card_type 积分的记录
selectcard_type,sum(card_credit) from card group by (card_type) with cube
14．使用ROLLUP分组查询card表中每种card_type 积分的记录
selectcard_type,sum(card_credit) from card group by (card_type) with rollup
15．对card 表按card_credit进行降序排序
select * fromcard order by card_credit desc
16．统计card 表中所有记录的平均积分
select * fromcard compute avg(card_credit)
17．对card 表中card_id 和card_use表中的card_id 进行联合查询
select card_idfrom card union select card_id from usecard
18．联合查询card表和usecard 表，显示用户名（card_name）、类型（exp_type）、情况描述（exp）、检查分数（score）检查时间（scoredate）
select * fromcard compute avg(card_credit)
19．查看持卡人姓名（card_name）、检查类型（exp_type）、检查情况（exp）、检查分数（score）
不使用别名：
select card.card_name,usecard.exp_type,usecard.exp,usecard.scoredatefrom usecard ,card where card.card_id=usecard.card_id
用别名：
select c.card_name,u.exp_type,u.exp,u.scoredate from usecardas u,card as c where c.card_id=u.card_id
select s.stu_name,c.course_name,e.t_grade from stu ass,course as c,exam as 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,pricefrom receive_detail where pici in(select pici from output_detail where(sale_price-price)*quantity>2)
三、视图操作
1．创建视图
CREATE VIEW 视图名AS 查询命令
例：createview card_view as select * from card
2．查看视图信息
nbsp;sp_help视图名 例：sp_help card_view
nbsp;sp_helptext显示视图的定义 例：sp_helptextcard_view
nbsp;sp_depends显示视图所依赖的对象 例：sp_depends card_view
3．修改视图
ALTER VIEW 视图名AS 查询语句 WITH CHECK OPTION
例：alter viewcard_view as select * from card where card_credit>20 with check option
注意 WITHCHECK OPTION 是对条件进行检查，如果没有with check option 不符合条件的记录也会被插入表中
例：insert into card_view values(‘1001′,’王晓明’,15,’20070101′) 插入的card_credit是15 比20小，但因为没做检查还是插入到表里了
4．通过视图插入数据记录
INSERT INTO 视图名VALUES（字段1值，字段2值，. . .，字段n值）
例：insertinto card_view values(‘1001′,’王晓明’,21,’20070101′)
5．通过视图修改数据记录
UPDATE 视图名 SET 字段1名=字段1值，字段2名=字段2值，. . .，字段n名=字段n值
例：updatecard_view set card_name=’wanghong’,card_credit=20 where card_id=’1001′
6．通过视图删除数据记录
DELETE FROM 视图名 WHERE 条件
例：deletefrom card_view where card_id=’1001′
7．重命名视图
sp_rename oldname,newname 例：sp_renamecard_view,card_view1
8．删除视图
DROP VIEW 视图名 例：drop viewcard_view
四、索引的使用
1．创建索引
CREATE INDEX 索引名 ON 表或视图(字段名)
nbsp;创建聚集唯一索引
例：create unique clustered index in_card on card(card_id)
nbsp;创建唯一索引
例：create unique index card_index on card(card_id)
nbsp;创建复合索引
例：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 indexcard.card_index
五、存储过程
1．创建存储过程
nbsp;创建存储过程之前要先判断这个存储过程名是否已经存在（用IF EXISTS判断）
例：IFEXISTS(SELECT name FROM sysobjects WHERE name=’card_pro’ AND type=’P’) DROPPROCEDURE card_pro
nbsp;创建不带参数的存储过程
CREATE PROCEDURE 存储过程名 AS 查询语句
例：CREATEPROCEDURE stu_pro AS select * from exam
执行该存储过程有2种方式：
2 直接打存储过程名： 例：stu_pro
2 用EXECUTE存储过程名： 例：EXECUTEstu_pro
nbsp;创建带参数的存储过程
CREATE PROCEDURE 存储过程名 @变量名变量数据类型 AS 查询语句
例：CREATEPROCEDURE stu_pro2 @tnum char(8) AS select * from exam where t_number=@tnum
执行该存储过程有3种方式：
2 直接打存储过程名 参数的值： 例：stu_pro2 ‘20040301’
2 用EXECUTE存储过程名：例：executestu_pro2 ‘20040301’
2 用EXECUTE存储过程名：例：executestu_pro2 @tnum=’20040301′
nbsp;创建带参数且有返回值的存储过程
例：CREATEPROCEDURE stu_pro3 @tnum char(8),@cnum char(6),@grade char(10) output
AS declare @errorvalueint
set @errorvalue=0
select @grade=t_grade from exam where t_number=@tnum and c_number=@cnum
if (@@ERROR<>0)
set@errorvalue=@@ERROR
return @errorvalue
执行：
declare @returnvalue int,@tnum char(8),@cnumchar(6),@grade char(10)
select @tnum=20040301,@cnum=100101
execute @returnvalue=stu_pro3 @tnum,@cnum,@grade output
select @returnvalue as a
select @grade as b
2．查看存储过程
nbsp;sp_help存储过程名 例：sp_help card_pro
nbsp;sp_helptext显示存储过程的定义 例：sp_helptext card_pro
nbsp;sp_depends显示存储过程所依赖的对象 例：sp_depends card_pro
nbsp;sp_stored_procedures当前存储过程列表
3．修改存储过程
ALTER PROCEDURE 存储过程名 AS 查询语句
例：alterprocdure card_pro as select * from card where t_grade<3 andcard_credit>100
5．重命名存储过程
sp_rename oldname,newname 例：sp_renamecard_pro,card_pro1
6．删除存储过程：
DROP PROCEDURE 存储过程名 例：drop procedure card_pro1
六、触发器
1．创建触发器
nbsp;创建触发器之前要先判断这个触发器名是否已经存在（用IF EXISTS判断）
例：IFEXISTS(SELECT name FROM sysobjects WHERE name=’ insert_data’ AND type=’Tr’)DROP TRIGGER card_pro
nbsp;创建INSERT触发器
CREATE TRIGGER 触发器名 ON 表名/视图名 AFTER INSERT AS 触发内容
例：创建在exam表中当输入分数大于100就拒绝这条记录插入
if exists(select name fromsysobjects
Where name=’insert_data’ and type=’tr’)
Droptrigger insert_data
create trigger insert_data on exam afterinsert as
if(select t_grade from inserted)>100
begin
print ‘not over 100’
rollback
end
nbsp;创建UPDATE触发器
CREATE TRIGGER触发器名 ON 表名/视图名AFTERUPDATE AS 触发内容
例：创建在exam表中当输入分数大于100就拒绝这条记录修改
if exists(select name from sysobjects
Where name=’update_data’ and type=’tr’)
Droptrigger update_data
create trigger update_data on exam after update as
if update(t_grade)
begin
if(select t_grade frominserted)>100
begin
print’分数不能大于 100′
rollback transaction
end
end
nbsp;创建INSERT、UPDATE触发器
CREATE TRIGGER 触发器名 ON 表名/视图名AFTERINSERT，UPDATE AS 触发内容
例：ifexists(select name from sysobjects
Where name=’update_data’ and type=’tr’)
Droptrigger update_data
create trigger update_data on exam forinsert,update as
if update(t_grade)
begin
if(select t_grade frominserted)>100
begin
print ‘分数不能大于 100’
rollback transaction
end
end
nbsp;创建INSTEAD OF 触发器
CREATE TRIGGER 触发器名 ON 表名/视图名 INSTEAD OF DELETE AS 触发内容
例：create trigger exam_tri on exam instead of delete as ‘不允许删除记录！’
2．查看触发器
nbsp;sp_help触发器名 例：sp_help exam_tri
nbsp;sp_helptext显示触发器的定义 例：sp_helptext exam_tri
nbsp;sp_depends显示触发器所依赖的对象 例：sp_depends exam_tri
3．修改触发器
nbsp;修改INSERT触发器
ALTER TRIGGER 触发器名 ON 表名/视图名 AFTER INSERT AS 触发内容
nbsp;修改UPDATE触发器
ALTER TRIGGER 触发器名 ON 表名/视图名AFTERUPDATE AS 触发内容
nbsp;修改INSERT、UPDATE触发器
ALTER TRIGGER 触发器名 ON 表名/视图名AFTER INSERT，UPDATE AS 触发内容
nbsp;修改INSTEAD OF 触发器
ALTER TRIGGER 触发器名 ON 表名/视图名 INSTEAD OF DELETE AS 触发内容
4．重命名触发器
sp_rename oldname,newname 例：sp_renamecard_tri,card_tri1
5．删除触发器
DROP TRIGGER触发器名 例：drop trigger card_tri1
第二部分 知识点
一、 数据库的有关概念：
nbsp;数据：文字、图表、数字、图片等等
nbsp;数据库对象：表、视图、存储过程、触发器、规则等等
nbsp;数据库：是数据和数据库对象的集合
nbsp;数据库管理系统：管理数据的计算机软件，提供了用户和数据库之间的软件界面，使用户更方便的操作数据库。用户能方便的定义和操纵数据，维护数据的安全性和完整性、进行多用户下的控制操作。
nbsp;数据库系统：由数据库、数据库管理系统及用户等等组成的
nbsp;关系模型：由实体和联系构成。关系模型以表的形式表现实体，表的一行表示实体的一个实例，一列描述实体的特征或属性；联系是实体之间的对应关系，有一对一的联系、一对多的联系、多对多的联系。
nbsp;关系模型中的数据完整性：实体完整性（确保数据库中所有实体的唯一性，通过设定主键、唯一键、唯一所引等方法）、区域完整性（限定表中数据值的范围）、引用完整性（确保不同表之间关键字字段保持一致，可以通过外键、存储过程、触发器等来实现）、自定义完整性（可以通过规则、存储过程、触发器等来实现）。
nbsp;约束：实现数据完整性最主要的方法，有主键约束、外键约束、唯一约束、检查约束、NOT NULL约束、默认约束
nbsp;关系数据库：基于关系模型的数据库
nbsp;事物：是数据库应用程序基本逻辑单元，是增强数据库一致性、完整性的重要机制。满足标准：原子性、一致性、隔离性、持久性；3种运行模式：自动提交的事务、显式事务、隐式事务
二、SQL SERVER的概念
nbsp;SQLSERVER 的发展历史：1998年，由Microsoft公司、Sysbase公司、Ashton-Tate公司合作开发的SQLSERVER 第一个版本OS/2版本；以后出现了SQLSERVER 6.5版本、SQL SERVER 7.0版本；2000年推出了SQL SERVER 2000版本。
nbsp;常见版本：企业版（用于企业数据库）、标准版（用于小工作组）、个人版（用于移动用户）、开发版（作为开发和测试系统）
nbsp;SQLSERVER是客户机/服务器（C/S）体系结构的关系型数据库系统
nbsp;安装SQL SERVER 2000 对软件、硬件都有要求
nbsp;SQL语言：
数据定义语言 DDL（CREATE、ALTER、DROP）
数据操纵语言 DML（SELECT、INSERT、UPDATE、DELETE）
数据控制语言 DCL（GRANT、DENY、REVKOE）
其他语言元素（流程控制语言、内嵌函数、批处理等）
nbsp;SQLSERVER2000数据库存储结构：
数据库文件：
主数据库文件（扩展名.mdb，是数据库的起点，一个数据库只能有一个主数据库文件）
辅助数据库文件（扩展名.ndb，一个数据库可以没有，也可以有多个辅助数据库文件）
日志文件（扩展名.ldb，包含恢复数据库的日志信息，一个数据库至少有一个日志文件，可以有多个；数据修改先写入日志文件再写数据库，当日志文件空间已满，就算数据库还有空间，对数据库进行的所有更新操作也都无法进行）
数据库文件组：一个文件只能存在于一个文件组；一个文件组只能被一个数据库使用；日志文件是独立的，不属于任何文件组。
nbsp;SQLSERVER2000系统数据库：
Master数据库：记录系统级别信息，一旦破坏，将导致系统瘫痪
Model数据库：创建数据库的模板，一旦删除，系统无法正常使用
Msdb数据库：为报警、任务调度、操作人员的操作提供存储空间
Tempdb数据库：临时数据库
Pubs和northwind：是系统提供的用户数据库
nbsp;SQLSERVER2000数据库系统表：
Sysdatabases表：每个数据库记录一条记录，只出现在master数据库中
sysobjects表：每个数据库对象记录一条记录，出现在所有数据库中
syscolumn表：记录表、视图每列信息，出现在所有数据库中
syscomments表：数据库对象的定义信息，出现在所有数据库中
sysindexes表：记录索引信息，出现在所有数据库中
sysusers表：记录所有服务器用户的信息，出现在所有数据库中
sysdepends表：记录数据库对象之间的关系，出现在所有数据库中
nbsp;SQLSERVER2000 常用管理工具和实用工具（服务管理器、企业管理器、查询分析器、服务器网络实用工具、客户端网络实用工具、事件探测器、导入导出数据、联机丛书）
三、Transact SQL 语言
nbsp;Sqlserver 变量
全局变量：以@@开头，用来记录服务器的活动状态，对用户而言，全局变量是只读的。P139常用全局变量
局部变量：P143
2 只在声明局部变量的批处理、存储过程或语句块中使用；局部变量必须先用DECLARE声明后才可以使用（DECLARE@变量名变量数据类型）；
2 局部变量赋值：用SET可以给一个变量赋值（SET@变量名=变量值）；用SELECT可以给多个变量赋值（SELECT @变量1名=变量1值，@变量2名=变量2值）
2 例：DECLARE @var1 char(6)
SET @var1=’1234567’
SELECT@car1 as ‘变量值’
nbsp;Sqlserver 运算符
2 算术运算符：+ – * / %(取模) 例：22%6 为4（22除以6取余数）
2 赋值运算符：=
2 位运算符：& 与 | 或 ^ 按位异或 ~ 按位反
2 比较运算符：= > < 等
2 逻辑运算符：AND OR NOT
2 一元运算符
nbsp;Sqlserver 函数P150
2 字符串函数：假设有一个字符串str=’ sql server ’
LEFT（字符串，数字n）：从左边开始取n个字符
例：LEFT(str,6) 结果为：’ sql’
RIGHT（字符串，数字n）：从右边开始取n个字符
例：RIGHT(str,9) 结果为：’server ’
LTRIM（字符串）：去除左边空格
例：LTRIM(str) 结果为：’sql server ’
RTRIM（字符串）：去除右边空格
例：RTRIM(str) 结果为：’ sql server’
SUBSTRING（字符串，m，n）：从第m个字符取n个
例：SUBSTRING(str,4,10) 结果为：’sql server’
STR（数字）：数字转换成字符
STUFF（字符串1，m，n，字符串2）：字符串1从第m个字符去除n个字符+字符串2
例：STUFF(str,8,9,’good’) 结果为：’ sql good’
UPPER（字符串）：转换成大写
LOWER（字符串）：转换成大写
2 日期和时间函数：
GETDATE()：取当前日期
DATEADD（年/月/日，n，日期）：返回指定日期加n年/月/日的整数 例：DATEADD(dd,4,GETDATE())取当前日期4天后的日期
DATEPART（年/月/日，日期）：返回指定日期年份/月份/天数的整数 例：DATEPART(mm,GETDATE()) 取当前月份
DATENAME（年/月/日，日期）：返回指定日期部分的字符串
DAY（日期）：返回日期天数的整数
MONTH（日期）：返回日期月份的整数
YEAR（日期）：返回日期年份的整数
2 数字函数：
ABS（数字）：取绝对值
SORT（数字）：取平方根
CEILING（数字）：大于等于给定数字的最小整数
例：CEILING(15.2564) 结果为：16
FLOOR（数字）：小于等于给定数字的最大整数
例：FLOOR(15.2564) 结果为：15
ROUND（数字，n）：四舍五入为精度为n的数
例：ROUND(15.2564,2) 结果为：15.26
RAND（）：返回0-1中的随机数
2 转换函数：
CAST(表达式 AS 数据类型)
例：CAST(’10.54’,decimal(5,2))结果将字符串转换成数字
CONVERT(数据类型，表达式)
例：CONVERT(decimal(5,2),age)结果将年龄转换成数字
2 其他函数：
nbsp;流程控制语句
BEGIN END语句：多条语句组成一个语句块，作为一个单元处理，BEGIN END语句允许嵌套
IF ELSE语句： 用于在执行的SQL语句强加条件判断
CASE语句：
CASE WHEN 条件 RESULT 结果 ELSE 结果表达式 END
例：selectt_number,c_number, ‘成绩等级’=
CASE
WHENt_grade<60 THEN ‘不及格’
WHEN t_grade>85 THEN ‘优良’
ELSE ‘中等’
END
from exam
WHILE BREAK CONTINUE语句：重复执行某语句P164实例5.23
GOTO语句：将执行语句变更到标签处继续处理P165 实例5.24
WAITFOR DELAY time|TIME：
time要等待的时间；TIME系统指定的时间
例：WAITFOR DELAY ‘0:0:6’ select * from exam 6秒以后执行查询语句
RETURN语句：用于查询或过程中无条件退出
四、视图：
nbsp;概念：从一个或多个数据表或视图派生出来的虚表，
2 本身不存储数据，只存储了执行检索的查询语句的定义
2 可以通过视图来查询、新增、修改、删除基表中的数据，但对数据要满足一定的条件
2 通过视图修改数据后，相应基表会发生变化；同时，基表变化时，会自动反映在视图中
2 视图最多可以定义一个或多个基表的1024个字段
nbsp;视图实现功能：
2 屏蔽数据复杂性
2 简化权限管理：定义不同视图供不同用户使用
nbsp;其他
五、索引
nbsp;概念：对数据库中一个或多个列的值进行排序的结构
nbsp;优缺点：
2 优点：加快数据检索速度；提高系统性能；加速表与表之间的连接
2 缺点：占用磁盘空间，降低添加、更新、删除行的速度，过多的索引不能提高数据库的性能
nbsp;索引分类：
2 聚集索引：一个表只能有一个聚集索引，表如果设置了主键就自动创建了一个聚集索引；聚集索引改变了行的物理顺序
2 非聚集索引：不影响数据表中记录的实际存储顺序，检索效率比聚集索引低；在创建非聚集索引之前必须先创建聚集索引；可以创建多个非聚集索引。
2 唯一索引：创建唯一索引要求字段或字段的组合必须唯一；一个表可以有多个唯一索引。
2 复合索引：对多个字段组合创建索引，一个复合索引最多有16个字段组合。
nbsp;其他
六、存储过程
nbsp;概念：是预先定义并编译的Transact SQL语句集，调用一次就保存在内存，下次调用时直接执行，速度单个SQL语句快；存储过程之间可以互相调用；可以被多个用户共享和充用
nbsp;分类：系统存储过程、用户自定义存储过程、临时存储过程
七、触发器
nbsp;概念：是一种特殊的存储过程；主要是通过事件触发而执行
nbsp;与普通存储过程区别：不能被直接调用；不传递参数或接受参数；触发器被视为单个事物在任何地方回滚。
nbsp;与check约束相比：强制实现更加复杂的数据完整性
文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树SQL高级技巧CTE和递归查询31559 人正在系统学习中
来源：icfh7501
声明：本站部分文章及图片转载于互联网，内容版权归原作者所有，如本站任何资料有侵权请您尽早请联系jinwei@zod.com.cn进行处理,非常感谢！