SQL 2005 总结

cyrus

表操作：
--判断表的存在性
IF EXISTS(SELECT NAME FROM sys.objects WHERE NAME='t_Department')
DROP TABLE t_Department
--创建部门表
CREATE TABLE t_Department
(
dept_id TINYINT ,--部门ID
dept_name VARCHAR(30),--部门名称
--约束语法：CONSTRAINT 约束名称约束类型
--表级约束
CONSTRAINT PK_dept_id PRIMARY KEY(dept_id)
)
--建立表级约束
CONSTRAINT PK_psn_id PRIMARY KEY(psn_id),
CONSTRAINT UK_idCard UNIQUE(psn_idCard),
--外键约束名称命名规则：FK_主表_从表_字段名
CONSTRAINT FK_t_Department_t_Personnel_psn_dept_id
FOREIGN KEY(psn_dept_id)
REFERENCES t_Department(dept_id)
)
--删除表级约束
ALTER TABLE t_Personnel
DROP CONSTRAINT FK_t_Department_t_Personnel_psn_dept_id
--添加外键约束：级联更新和级联删除
ALTER TABLE t_Personnel
ADD CONSTRAINT FK_t_Department_t_Personnel_psn_dept_id
FOREIGN KEY(psn_dept_id) REFERENCES t_Department(dept_id)
ON DELETE CASCADE ON UPDATE CASCADE
--测试：级联更新或者删除：主表中的数据修改了，
--从表中相关的数据也一块改,从表中数据改了，主表不受影响
SELECT * FROM t_Department
SELECT * FROM t_Personnel
UPDATE t_Department SET dept_id=2 WHERE
dept_id=1
--添加外键约束：更新和删除时参照列置为Null
ALTER TABLE t_Personnel
ADD CONSTRAINT FK_t_Department_t_Personnel_psn_dept_id
FOREIGN KEY(psn_dept_id) REFERENCES t_Department(dept_id)
ON DELETE SET NULL ON UPDATE SET NULL
--添加外键约束：更新和删除时参照列置为默认值
ALTER TABLE t_Personnel
ADD CONSTRAINT FK_t_Department_t_Personnel_psn_dept_id
FOREIGN KEY(psn_dept_id) REFERENCES t_Department(dept_id)
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT
--查看表约束
SP_HELPCONSTRAINT t_Personnel
--添加检查约束：身份证号必须是位
ALTER TABLE t_Personnel
ADD CONSTRAINT CK_t_Personnel_psn_idCard
CHECK (LEN(psn_idCard)=18)
--添加检查约束：薪水不能低于
ALTER TABLE t_Personnel
DROP CONSTRAINT CK_t_Personnel_psn_salary
ALTER TABLE t_Personnel
ADD CONSTRAINT CK_t_Personnel_psn_salary
CHECK(psn_salary>650)
--添加列，如果不允许为空，则必须设置为默认值
ALTER TABLE t_Personnel
ADD psn_gender BIT NOT NULL DEFAULT 0
--查看数据
SELECT * FROM t_Personnel
--删除约束
ALTER TABLE t_Personnel
DROP CONSTRAINT DF__t_Personn__psn_g__108B795B
--删除列
ALTER TABLE t_Personnel
DROP COLUMN psn_gender
--修改一列(必须先删除该列上的约束)
ALTER TABLE t_Personnel
DROP CONSTRAINT CK_t_Personnel_psn_salary
ALTER TABLE t_Personnel
ALTER COLUMN psn_salary MONEY
--查看数据
SELECT * FROM Staff
--修改列
ALTER TABLE Staff
ALTER COLUMN staff_id TINYINT NOT NULL
--添加主键约束
ALTER TABLE Staff
ADD CONSTRAINT PK_Staff_id PRIMARY KEY(staff_id)
--添加检查约束
ALTER TABLE Staff
ADD CONSTRAINT CK_Staff_staff_salary
CHECK (staff_salary>700)
/*varchar和nvarchar的区别
总结：
在非中文数据库中如果想存储中文，需要满足以下两个条件：
1.文本类型使用nchar/nvarchar/ntext
2.插入数据时需要在前面加上N:N'中文'
如果不满足以上两点会出现乱码
将来以中文做为查询条件时也需要在中文前加上N
,如：SELECT * FROM t_Test2 WHERE tmp_name=N'张三'
中文数据库不出现此类问题
*/
子查询：
--使用子查询查询数据：子查询返回供外部使用的值
--IN:选择学生成绩大于的学生信息
SELECT * FROM StuInfo WHERE stuNo IN
(SELECT stuNo FROM StuScore WHERE score>70)
--Exists:根据内查询中的条件返回行的存在性，并传递查询状态
--给外部子查询，子查询包含行时返回True
SELECT * FROM StuInfo A WHERE
Exists(SELECT * FROM StuScore B
WHERE A.stuNo=B.stuNo AND Score>70)
--使用修改的比较操作符>All、>Any、=Any、<>ALL、<>Any
SELECT * FROM StuScore WHERE Score<>All(SELECT TOP 2 Score FROM StuScore)--All：表示所有
SELECT * FROM Stuscore WHERE Score<>Any(SELECT TOP 2 Score FROM StuScore)--Any:表示任意一个
SELECT * FROM StuScore WHERE Score>All --大于大的
(SELECT TOP 2 Score FROM StuScore WHERE Score<80)
SELECT * FROM StuScore WHERE Score<All --小于小的
(SELECT TOP 2 Score FROM StuScore WHERE Score>70)
SELECT * FROM StuScore WHERE Score>Any --大于小的
(SELECT TOP 2 Score FROM StuScore WHERE Score<80)
SELECT * FROM StuScore WHERE Score<Any --小于大的
(SELECT TOP 2 Score FROM StuScore WHERE Score<80)
--使用聚合函数
SELECT * FROM StuScore
WHERE Score>(SELECT AVG(Score) FROM StuScore)
--使用嵌入子查询
--选择学习语文的学生的信息
SELECT * FROM StuInfo
WHERE stuNo IN (SELECT stuNo FROM StuScore
WHERE courseNo IN (
SELECT CourseNo FROM Course WHERE courseName='语文'))
--使用相关子查询：内部查询为外部查询指定表的每行进行求值
--选择学生成绩大于自己平均成绩的学生信息
SELECT * FROM StuScore A
WHERE score>(SELECT AVG(Score) FROM StuScore B
WHERE A.stuNo=B.stuNo)
SELECT * FROM StuInfo WHERE stuNo IN(
SELECT stuNo FROM StuScore A
WHERE score>(SELECT AVG(Score) FROM StuScore B
WHERE A.stuNo=B.stuNo))
--部门名称和部门总人数
SELECT dept_name 部门名称,(
SELECT COUNT(*) FROM t_Personnel psn
WHERE psn.dept_id=dept.dept_id
)部门总人数FROM t_Department dept
--查询员工人数最多的前两个部门名称
SELECT * FROM t_Personnel
SELECT * FROM t_Department
SELECT TOP 2 部门名称FROM
(
SELECT (SELECT dept_name FROM t_Department dept
WHERE dept.dept_id=psn.dept_id
)部门名称,COUNT(*) 总人数FROM
t_Personnel psn
GROUP BY psn.dept_id
) tmp
ORDER BY 总人数DESC
--查询除人力资源部以外的员工姓名
SELECT * FROM t_Personnel
SELECT * FROM t_Department
SELECT psn_name FROM t_Personnel psn
WHERE dept_id NOT IN(
SELECT dept_id FROM t_Department dept
WHERE dept_name='人力资源部'
)
--查询除了人事以外的员工的姓名
SELECT psn_name 员工姓名FROM t_Personnel psn
WHERE NOT EXISTS(SELECT * FROM t_Department dept
WHERE psn.dept_id=dept.dept_id AND dept_name='教学部')
--随机查询三名员工信息
SELECT TOP 3 * FROM t_Personnel
ORDER BY newid()
事务，视图，索引，游标：
--SET IMPLICIT_TRANSACTIONS ON --指定事务显示提交
BEGIN TRANSACTION --开始显示事务
DECLARE @new_dept_id INT,@old_dept_id INT,@psn_id INT
DECLARE @errorSum INT
SET @errorSum=0
SET @new_dept_id=11
SET @psn_id=6
SELECT @old_dept_id=psn_dept FROM t_Personnel
WHERE psn_id=@psn_id
SET @errorSum=@errorSum+@@error
INSERT INTO t_Log(log_module) VALUES
(CONVERT(VARCHAR(50),
ISNULL(CONVERT(VARCHAR(20),@old_dept_id),'无部门'))+
'调往'+CONVERT(VARCHAR(20),@new_dept_id))
SET @errorSum=@errorSum+@@error
UPDATE t_Personnel SET psn_dept=@new_dept_id
WHERE psn_id=@psn_id
SET @errorSum=@errorSum+@@error
IF @old_dept_id IS NULL OR @errorSum<>0
BEGIN
PRINT '日志记录不能满足条件，不能调动，应撤销操作'
ROLLBACK TRANSACTION --回滚事务
END
ELSE
BEGIN
PRINT '操作成功'
COMMIT TRANSACTION--提交事务
END
--索引演示
--判断索引的存在性
IF EXISTS(SELECT 1 FROM sys.indexes WHERE name='IDX_t_Personnel_psn_dept')
--删除索引：表名.索引名
DROP INDEX t_Personnel.IDX_t_Personnel_psn_dept
--创建索引
CREATE INDEX IDX_t_Personnel_psn_dept
ON t_Personnel(psn_dept)
WITH FILLFACTOR=30
GO
--视图演示
--创建部门表
CREATE TABLE t_Department
(
dept_id INT IDENTITY PRIMARY KEY,
dept_name VARCHAR(20)
)
--判断视图的存在性
IF EXISTS(SELECT 1 FROM sys.views WHERE name='V_PD')
DROP VIEW V_PD
--创建视图
CREATE VIEW V_PD
AS
SELECT psn_name,psn_gender,dept_name
FROM t_Personnel psn INNER JOIN t_Department dept
ON psn.psn_dept=dept.dept_id
--使用视图
SELECT * FROM V_PD
--查看视图定义
SP_HELPTEXT V_PD
--修改视图
ALTER VIEW V_PD
WITH ENCRYPTION
AS
SELECT psn_name,psn_gender,dept_name
FROM t_Personnel psn INNER JOIN t_Department dept
ON psn.psn_dept=dept.dept_id
--建立向视图中插入数据的触发器：
create trigger trg_a_b
on a_b
instead of insert
as
declare @aid int,@aname varchar(30),@bid int ,@bname varchar(30)
select @aid =aid ,@aname = aname ,@bid = bid,@bname = bname from inserted
insert into a values (@aid,@aname,@bid)
insert into b values (@bid,@bname)
--游标的使用
--1.声明游标
DECLARE csr_PensionMakeup CURSOR
FOR
SELECT pm_id,pm_rate FROM t_PensionMakeup
--打开游标
OPEN csr_PensionMakeup
--声明变量
DECLARE @s_id INT,@s_money SMALLMONEY
SET @s_id=1
SELECT @s_money=s_money FROM t_Salary
WHERE s_id=@s_id
DECLARE @pm_id INT,@pm_rate FLOAT
--取一行记录
FETCH NEXT FROM csr_PensionMakeup
INTO @pm_id,@pm_rate
--将其插入社保明细表中
INSERT INTO t_PensionDetail VALUES
(@s_id,@pm_id,@s_money*@pm_rate)
--当有下一行时继续
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM csr_PensionMakeup
INTO @pm_id,@pm_rate
IF @@FETCH_STATUS=0
INSERT INTO t_PensionDetail
VALUES(@s_id,@pm_id,@s_money*@pm_rate)
END
--关闭游标
CLOSE csr_PensionMakeup
--释放游标
DEALLOCATE csr_PensionMakeup

--存储过程：
--创建存储过程（无参数）
CREATE PROCEDURE usp_DeptInfo
AS
SELECT ISNULL(dept_name,'无部门') 部门名称,
COUNT(*) AS 部门总人数FROM t_Personnel psn
LEFT JOIN t_Department dept
ON psn.dept_id=dept.dept_id
GROUP BY dept_name
--调用存储过程:EXEC 存储过程名称
EXEC usp_DeptInfo
--创建带参数的存储过程（输入参数）
CREATE PROCEDURE usp_InputParam
@psn_id INT=1,
@psn_birthDate SMALLDATETIME
AS
SELECT * FROM t_Personnel
WHERE psn_id=@psn_id OR psn_birthDate=@psn_birthDate
--调用存储过程
EXEC usp_InputParam 2,'1981-3-4'
EXEC usp_InputParam @psn_id=2,@psn_birthDate='1981-3-4'
EXEC usp_InputParam ,@psn_birthDate='1981-3-4'--Error
EXEC usp_InputParam @psn_birthDate='1981-3-4'
EXEC usp_InputParam 10,'1981-3-4'
--调用存储过程
EXEC usp_InputParam
EXEC usp_InputParam 2
--修改存储过程,带输出参数
ALTER PROCEDURE usp_InputParam
@psn_id INT=1,
@psn_birthDate SMALLDATETIME='1900-1-1',
@psn_name VARCHAR(20) OUTPUT--指明该参数为输出参数
AS
IF @psn_birthDate='1900-1-1'
SET @psn_birthDate=GETDATE()
IF EXISTS(
SELECT * FROM t_Personnel
WHERE psn_id=@psn_id OR psn_birthDate=@psn_birthDate)
SELECT @psn_name=psn_name FROM t_Personnel
WHERE psn_id=@psn_id OR psn_birthDate=@psn_birthDate
ELSE
PRINT '编号为：'+CONVERT(VARCHAR(20),@psn_id)+
'或者出生日期为：'+CONVERT(VARCHAR(20),@psn_birthDate)+'的信息不存在！'
--调用存储过程
DECLARE @psn_name VARCHAR(20)
EXEC usp_InputParam 2,'1981-3-4',@psn_name OUTPUT
PRINT @psn_name
--修改存储过程,使用RAISERROR函数
ALTER PROCEDURE usp_InputParam
@psn_id INT=1,
@psn_birthDate SMALLDATETIME
AS
IF @psn_birthDate>CONVERT(SMALLDATETIME,
DATENAME(yyyy,getdate())+'-'+DATENAME(mm,getdate()+1)+'-1')
RAISERROR('输入日期的月份不能大于当前月份',16,1)
ELSE
RAISERROR('成功执行',9,1)
--调用存储过程
EXEC usp_InputParam 2,'1981-3-4'

--触发器：
--创建触发器
--CREATE TRIGGER trg_ForInsert_t_Post
ALTER TRIGGER trg_ForInsert_t_Post
ON t_Post
FOR INSERT
AS
DECLARE @pst_id INT,@pst_name VARCHAR(20),
@pst_salary SMALLMONEY,@msg VARCHAR(200)
SELECT @pst_id=pst_id,
@pst_name=pst_name,
@pst_salary=pst_salary
FROM INSERTED
SET @msg='职位：'+@pst_name+
',薪水：'+CONVERT(VARCHAR(200),@pst_salary)+'插入成功！'
INSERT INTO t_Log(log_module) VALUES
(@msg)
IF @pst_salary<0
BEGIN
ROLLBACK TRAN
PRINT '插入的数据不合法，插入失败！'
END
ELSE
BEGIN
RAISERROR('插入成功！',10,1)
END
GO
--创建触发器
CREATE TRIGGER trg_ForDelete_t_Post
ON t_Post
FOR DELETE
AS
DECLARE @pst_id INT
SELECT @pst_id=pst_id FROM deleted
IF EXISTS(SELECT * FROM t_Personnel WHERE pst_id=@pst_id)
BEGIN
PRINT '有员工任该职务，不能删除！'
ROLLBACK
END
ELSE
RAISERROR('删除成功！',9,1)
GO
--列级触发器
ALTER TRIGGER trg_ForUpdate_t_Personnel
ON t_Personnel
FOR UPDATE
AS
DECLARE @psn_id INT ,@pst_id INT,@pst_idA INT
SELECT @psn_id=psn_id,@pst_id=pst_id FROM deleted
SELECT @pst_idA=pst_id FROM INSERTED
IF UPDATE(psn_gender)
BEGIN
PRINT '性别不能修改！'
ROLLBACK
RETURN
END
ELSE
BEGIN
UPDATE t_Personnel SET pst_id=@pst_idA
WHERE psn_id=@psn_id
RAISERROR('修改成功!',9,1)
END
--替代触发器
ALTER TRIGGER trg_InsteadOfDelete_t_Personnel
ON t_Personnel
INSTEAD OF DELETE
AS
DECLARE @psn_id INT,@psn_status INT
SELECT @psn_id=psn_id,@psn_status=psn_status FROM DELETED
UPDATE t_Personnel SET psn_status=0
WHERE psn_id=@psn_id
--数据安全：备份，导入导出……：
--新建备份设备
EXEC SP_addumpdevice 'DISK','DB','d:\1\QQ.bak'
--查看备份设备
EXEC SP_HELPDEVICE 'DB'
--删除备份设备
EXEC SP_DROPDEVICE 'DB'
--只备份数据库
BACKUP DATABASE db TO DB
--备份日志文件
BACKUP LOG db TO DB
--差异备份
BACKUP DATABASE db TO DB
WITH DIFFERENTIAL
--恢复整个数据库
RESTORE DATABASE db FROM DB WITH REPLACE
--恢复事务日志
RESTORE LOG HRMS FROM DB
--数据库的分离
SP_Detach_db db
--附加数据库（引号中代码不能换行输入，否则系统不识）
SP_Attach_db db,
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db_log.ldf'
--数据导出到指定的Excel文件中（引号中代码不能换行输入，否则系统不识）
EXEC master..xp_cmdshell 'bcp db.dbo.Student OUT d:\1.xls -c -q -S "HSJ\SQL2005" -U "sa" -P "123456"'
--数据导出到指定的文本文件中（引号中代码不能换行输入，否则系统不识）
EXEC master..xp_cmdshell 'bcp db.dbo.Student OUT d:\1.txt -c -S "HSJ\SQL2005" -U "sa" -P "123456"'


数据分页：
--使用row_number()进行数据分页:显示第二页，没有显示条
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY psn_id) 伪列,*
FROM t_Personnel
) tmp WHERE 伪列BETWEEN 4 AND 6
/*
数据分页公式：
SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY 记录ID) 伪列,*
FROM 表名
) tmp WHERE 伪列BETWEEN (当前页-1)*每页记录数+1
AND 当前页*每页记录数
*/
--数据分页操作:显示第二页记录，每页显示条记录
SELECT TOP 3 * FROM t_Personnel
WHERE psn_id NOT IN(
SELECT TOP ((2-1)*3) psn_id FROM t_Personnel
) ORDER BY psn_id
/*
数据分页公式：
SELECT TOP 每页显示记录数* FROM 表名
WHERE 记录ID NOT IN(
SELECT TOP (当前页数-1)*每页记录数记录ID
FROM 表名
)
ORDER BY 记录ID
*/
--万能分页存储过程：
create proc pages
@page int,
@pagesize int
as
select *from (select row_number() over(order by did ) 伪列,* from d) tmp
where 伪列 between ((@page-1) *@pagesize+1) and (@page*@pagesize)
--调用此存储过程显示(第三页，并且设定每页四条数据)：
exec pages 3,4