SqlServer补充

一、数据库和表的操作

1、其他

1.1、备份表

select * into table2 from table1

说明：表table1已经存在，table2不存在。当需要对table1测试时，可以先备份表table1，执行完上述SQL之后，会复制table1的结构和数据到table2。

1.2、表1数据复制到表2（表2已存在）

insert into table2 select * from table1

2、sql分页

2.1、分页查询1

  declare @pageIndex int
  set @pageIndex = 1
  declare @pageSize int
  set @pageSize = 5

  select top (@pageSize) *
  from person
  where id not in
		(select top (@pageSize*(@pageIndex-1)) id from person order by id)

2.2、分页查询2

  declare @pageIndex int
  set @pageIndex = 2
  declare @pageSize int
  set @pageSize = 5

  select top (@pageSize) *
  from (select ROW_NUMBER() over(order by id) as ord,*  from person) a
  where ord >(@pageSize*(@pageIndex-1))

2.3、分页查询3

  declare @pageIndex int
  set @pageIndex = 1
  declare @pageSize int
  set @pageSize = 5

  select *
  from person
  order by id
  offset (@pageSize*(@pageIndex-1)) rows    --从多少条+1开始
  fetch next (@pageSize) rows only            --查询多少条

3、递归查询

也叫层级查询，类似于内查询，本质是查询树形结构

  --递归查询
  CREATE TABLE Employees (  
    ID INT PRIMARY KEY,  
    Name NVARCHAR(100),  
    ManagerID INT NULL FOREIGN KEY REFERENCES Employees(ID)  
); 

INSERT INTO Employees (ID, Name, ManagerID) VALUES  
(1, 'John Doe', NULL),  
(2, 'Jane Smith', 1),  
(3, 'Bob Johnson', 1),  
(4, 'Alice Brown', 2),  
(5, 'Charlie Davis', 3); 

select * from Employees
go


-- 递归查询获取每个员工的经理链  
WITH Temp as (
		select id empId, name empName, managerId managerId, cast(null as nvarchar(100)) managerName
		from employees
		where managerId is null
		union all
		select a.id empId, a.name empName, a.ManagerID managerId, b.empName managerName
		from employees a inner join Temp b on a.managerId = b.empId
)
select * from Temp order by empId

4、数据类型

4.1、uniqueidentifier类型(Guid)

通过使用newid()函数

select newid()

5、索引

5.1、索引

5.1.1、索引概述

整个表的数据被分成一个个的数据页，数据页之间相连通，类似链表，索引就是通过数据页查询的，所以快

6、数据库误操作恢复

1、

发生灾难前

第一步：数据库-属性-选项-恢复模式设置为：完整

第二步、至少做一次完整备份

第三步、做一次事务日志尾部备份

7、事务

7.1、事务分类

7.1.1、显式事务

用begin transaction开启事务，由commit transaction提交事务，由rollback transaction回滚事务

7.1.2、隐式事务

通过设置 set implicit_transactions on 语句，将隐式事务模式设置为打开。当以隐式事务模式操作时，不必使用 begin transaction 开启事务，当一个事务结束后，这个模式会自动启用下一个事务。只需使用 commit transaction 提交事务或 rollback transaction 回滚事务即可。

7.1.3、自动提交事务

SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交，否则回滚。

二、存储过程

1、概述：

1.1、定义：存储过程（procedure）是一组为了完成特定功能的SQL语句的集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行

1.2、优缺点（自己网上找）

1.3、常见存储过程

exec sp_databases;查看所有数据库

exec sp_helpdb;查询数据库信息

exec sp_helpdb 数据名;查询指定数据库信息

exec sp_renamedb‘旧库名’,‘新库名’;更改数据库名称

exec sp_tables;查询当前数据库的所有表

exec sp_columns 表名;查看列

exec sp_help 表名;返回表的所有信息

exec sp_helpIndex 表名;查看索引

exec sp_helpConstraint 表名;约束

exec sp_stored_procedures;当前环境的所有存储

exec sp_helptext‘存储过程’;查看存储过程源码

exec sp_rename‘旧名’,‘新名’;修改表、索引、列的名称

exec sp_defaultdb‘旧库名’,‘新库名’;更改登录名的默认数据库注意：

exec 用于调用存储过程

2、存储过程操作

2.1、创建存储过程

create procedure 存储过程名
		@参数1 数据类型,
		@参数2 数据类型 output    --output表示输出值（返回值）

as    
begin
	SQL语句	
end

修改则把create换成alter即可

2.2、删除

drop procedure 存储过程名

2.3、调用存储过程

exec 存储过程名;        ---不带参数调用
exec 存储过程名 参数1, 参数2 ;    ---带参数调用

2.4、查看存储过程

exec sp_helptext 存储过程名

PPT:

https://pan.baidu.com/s/163EH1ew5uA491ThoN_bkNQ?pwd=6666 
提取码：6666