表操作：--判断表的存在性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_PersonnelDROP CONSTRAINT FK_t_Department_t_Personnel_psn_dept_id--添加外键约束：级联更新和级联删除ALTER TABLE t_PersonnelADD CONSTRAINT FK_t_Department_t_Personnel_psn_dept_idFOREIGN KEY(psn_dept_id) REFERENCES t_Department(dept_id)ON DELETE CASCADE ON UPDATE CASCADE --测试：级联更新或者删除：主表中的数据修改了，--从表中相关的数据也一块改,从表中数据改了，主表不受影响SELECT * FROM t_DepartmentSELECT * FROM t_PersonnelUPDATE t_Department SET dept_id=2 WHERE dept_id=1--添加外键约束：更新和删除时参照列置为NullALTER TABLE t_PersonnelADD CONSTRAINT FK_t_Department_t_Personnel_psn_dept_idFOREIGN KEY(psn_dept_id) REFERENCES t_Department(dept_id)ON DELETE SET NULL ON UPDATE SET NULL--添加外键约束：更新和删除时参照列置为默认值ALTER TABLE t_PersonnelADD CONSTRAINT FK_t_Department_t_Personnel_psn_dept_idFOREIGN KEY(psn_dept_id) REFERENCES t_Department(dept_id)ON DELETE SET DEFAULT ON UPDATE SET DEFAULT--查看表约束SP_HELPCONSTRAINT t_Personnel--添加检查约束：身份证号必须是位ALTER TABLE t_PersonnelADD CONSTRAINT CK_t_Personnel_psn_idCardCHECK (LEN(psn_idCard)=18)--添加检查约束：薪水不能低于ALTER TABLE t_PersonnelDROP CONSTRAINT CK_t_Personnel_psn_salaryALTER TABLE t_PersonnelADD CONSTRAINT CK_t_Personnel_psn_salaryCHECK(psn_salary>650)--添加列，如果不允许为空，则必须设置为默认值ALTER TABLE t_PersonnelADD psn_gender BIT NOT NULL DEFAULT 0--查看数据SELECT * FROM t_Personnel--删除约束ALTER TABLE t_PersonnelDROP CONSTRAINT DF__t_Personn__psn_g__108B795B--删除列ALTER TABLE t_PersonnelDROP COLUMN psn_gender--修改一列(必须先删除该列上的约束)ALTER TABLE t_PersonnelDROP CONSTRAINT CK_t_Personnel_psn_salaryALTER TABLE t_PersonnelALTER COLUMN psn_salary MONEY--查看数据SELECT * FROM Staff--修改列ALTER TABLE Staff ALTER COLUMN staff_id TINYINT NOT NULL--添加主键约束ALTER TABLE StaffADD CONSTRAINT PK_Staff_id PRIMARY KEY(staff_id)--添加检查约束ALTER TABLE StaffADD CONSTRAINT CK_Staff_staff_salaryCHECK (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_PersonnelSELECT * FROM t_DepartmentSELECT TOP 2 部门名称FROM ( SELECT (SELECT dept_name FROM t_Department deptWHERE dept.dept_id=psn.dept_id)部门名称,COUNT(*) 总人数FROM t_Personnel psnGROUP BY psn.dept_id) tmp ORDER BY 总人数DESC--查询除人力资源部以外的员工姓名SELECT * FROM t_PersonnelSELECT * FROM t_DepartmentSELECT psn_name FROM t_Personnel psnWHERE dept_id NOT IN(SELECT dept_id FROM t_Department deptWHERE dept_name='人力资源部')--查询除了人事以外的员工的姓名SELECT psn_name 员工姓名FROM t_Personnel psnWHERE NOT EXISTS(SELECT * FROM t_Department deptWHERE psn.dept_id=dept.dept_id AND dept_name='教学部')--随机查询三名员工信息SELECT TOP 3 * FROM t_PersonnelORDER BY newid()事务，视图，索引，游标：--SET IMPLICIT_TRANSACTIONS ON --指定事务显示提交BEGIN TRANSACTION --开始显示事务DECLARE @new_dept_id INT,@old_dept_id INT,@psn_id INTDECLARE @errorSum INTSET @errorSum=0SET @new_dept_id=11SET @psn_id=6SELECT @old_dept_id=psn_dept FROM t_PersonnelWHERE [email protected]_idSET @[email protected]+@@errorINSERT INTO t_Log(log_module) VALUES(CONVERT(VARCHAR(50),ISNULL(CONVERT(VARCHAR(20),@old_dept_id),'无部门'))+'调往'+CONVERT(VARCHAR(20),@new_dept_id))SET @[email protected]+@@errorUPDATE t_Personnel SET [email protected]_dept_idWHERE [email protected]_idSET @[email protected]+@@errorIF @old_dept_id IS NULL OR @errorSum<>0 BEGIN PRINT '日志记录不能满足条件，不能调动，应撤销操作' ROLLBACK TRANSACTION --回滚事务 ENDELSE 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_deptON t_Personnel(psn_dept)WITH FILLFACTOR=30GO--视图演示--创建部门表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_PDASSELECT psn_name,psn_gender,dept_nameFROM t_Personnel psn INNER JOIN t_Department deptON psn.psn_dept=dept.dept_id--使用视图SELECT * FROM V_PD--查看视图定义SP_HELPTEXT V_PD--修改视图ALTER VIEW V_PDWITH ENCRYPTIONASSELECT psn_name,psn_gender,dept_nameFROM t_Personnel psn INNER JOIN t_Department deptON psn.psn_dept=dept.dept_id--建立向视图中插入数据的触发器：create trigger trg_a_bon a_binstead of insertasdeclare @aid int,@aname varchar(30),@bid int ,@bname varchar(30)select @aid =aid ,@aname = aname ,@bid = bid,@bname = bname from insertedinsert into a values (@aid,@aname,@bid)insert into b values (@bid,@bname)--游标的使用--1.声明游标DECLARE csr_PensionMakeup CURSORFORSELECT pm_id,pm_rate FROM t_PensionMakeup--打开游标OPEN csr_PensionMakeup--声明变量DECLARE @s_id INT,@s_money SMALLMONEYSET @s_id=1SELECT @s_money=s_money FROM t_SalaryWHERE [email protected]_idDECLARE @pm_id INT,@pm_rate FLOAT--取一行记录FETCH NEXT FROM csr_PensionMakeupINTO @pm_id,@pm_rate--将其插入社保明细表中INSERT INTO t_PensionDetail VALUES(@s_id,@pm_id,@[email protected]_rate)--当有下一行时继续WHILE @@FETCH_STATUS=0BEGIN FETCH NEXT FROM csr_PensionMakeup INTO @pm_id,@pm_rate IF @@FETCH_STATUS=0 INSERT INTO t_PensionDetail VALUES(@s_id,@pm_id,@[email protected]_rate)END--关闭游标CLOSE csr_PensionMakeup--释放游标DEALLOCATE csr_PensionMakeup--存储过程：--创建存储过程（无参数）CREATE PROCEDURE usp_DeptInfoASSELECT ISNULL(dept_name,'无部门') 部门名称,COUNT(*) AS 部门总人数FROM t_Personnel psnLEFT JOIN t_Department deptON psn.dept_id=dept.dept_idGROUP BY dept_name--调用存储过程:EXEC 存储过程名称EXEC usp_DeptInfo--创建带参数的存储过程（输入参数）CREATE PROCEDURE usp_InputParam@psn_id INT=1,@psn_birthDate SMALLDATETIMEASSELECT * FROM t_Personnel WHERE [email protected]_id OR [email protected]_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'--ErrorEXEC usp_InputParam @psn_birthDate='1981-3-4'EXEC usp_InputParam 10,'1981-3-4'--调用存储过程EXEC usp_InputParamEXEC usp_InputParam 2--修改存储过程,带输出参数ALTER PROCEDURE usp_InputParam@psn_id INT=1,@psn_birthDate SMALLDATETIME='1900-1-1',@psn_name VARCHAR(20) OUTPUT--指明该参数为输出参数ASIF @psn_birthDate='1900-1-1'SET @psn_birthDate=GETDATE()IF EXISTS(SELECT * FROM t_Personnel WHERE [email protected]_id OR [email protected]_birthDate) SELECT @psn_name=psn_name FROM t_Personnel WHERE [email protected]_id OR [email protected]_birthDateELSE 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 OUTPUTPRINT @psn_name--修改存储过程,使用RAISERROR函数ALTER PROCEDURE usp_InputParam@psn_id INT=1,@psn_birthDate SMALLDATETIMEASIF @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_PostALTER TRIGGER trg_ForInsert_t_PostON t_PostFOR INSERTASDECLARE @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 INSERTEDSET @msg='职位：[email protected]_name+',薪水：'+CONVERT(VARCHAR(200),@pst_salary)+'插入成功！'INSERT INTO t_Log(log_module) VALUES(@msg)IF @pst_salary<0BEGIN ROLLBACK TRAN PRINT '插入的数据不合法，插入失败！'ENDELSEBEGIN RAISERROR('插入成功！',10,1)ENDGO--创建触发器CREATE TRIGGER trg_ForDelete_t_PostON t_PostFOR DELETE ASDECLARE @pst_id INTSELECT @pst_id=pst_id FROM deletedIF EXISTS(SELECT * FROM t_Personnel WHERE [email protected]_id) BEGIN PRINT '有员工任该职务，不能删除！' ROLLBACK ENDELSE RAISERROR('删除成功！',9,1)GO--列级触发器ALTER TRIGGER trg_ForUpdate_t_PersonnelON t_PersonnelFOR UPDATEASDECLARE @psn_id INT ,@pst_id INT,@pst_idA INTSELECT @psn_id=psn_id,@pst_id=pst_id FROM deletedSELECT @pst_idA=pst_id FROM INSERTEDIF UPDATE(psn_gender) BEGIN PRINT '性别不能修改！' ROLLBACK RETURN ENDELSE BEGIN UPDATE t_Personnel SET [email protected]_idA WHERE [email protected]_id RAISERROR('修改成功!',9,1) END--替代触发器ALTER TRIGGER trg_InsteadOfDelete_t_PersonnelON t_PersonnelINSTEAD OF DELETE ASDECLARE @psn_id INT,@psn_status INTSELECT @psn_id=psn_id,@psn_status=psn_status FROM DELETEDUPDATE t_Personnel SET psn_status=0WHERE [email protected]_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 DBWITH 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 intasselect *from (select row_number() over(order by did ) 伪列,* from d) tmpwhere 伪列 between ((@page-1) [email protected]+1) and (@[email protected])--调用此存储过程显示(第三页，并且设定每页四条数据)：exec pages 3,4
?