SqlServer基础笔记

测试库：SqlServer基础笔记测试数据库-CSDN博客

SqlServer基础操作

1.创建数据库

if DB_ID('name') is null
create database class;
go

2.删除数据库

if DB_ID('name') is not null
drop database class;
go

3.创建表

create table [dbo].[student](
	[id] [int] identity(1,1),
	[name] [varchar](50) not null,
	[course] [varchar](50) not null,
	[score] [int] not null
)ON [PRIMARY]
go

4.删除表 

-- sysobjects是数据库中的表，表的内容是数据库所有表的信息
if exists (select * from sysobjects where name='student')
drop table student
go

数据基本CRUD

1.插入

INSERT INTO 表名(表列1,表列2) values(标列1数据,表列2数据)

2.修改

-- 修改值
update 表名 set 列名1 = 值1 where 列名2 = 值2

3.删除

delete from 表名 where 条件列 = 条件值
go

 4.查询

-- 查询表中的某列
select 列名1,列名2,列名3 from 表名

-- 查询表中的所有列
select * from 表名

1.别名查询，查询出的结果拼接

-- top(10) 表示查询前十个数据
select top(10) 列名 as 别名,列名 as 别名 from 表名

2.条件查询

select * from 表名 where 列名 = 列值

3.范围查询

-- 取值范围
select * from 表名 where id > 5 and id < 20
-- 从什么到什么，前小后大
select * from 表名 where id between 4 and 20

4.null判断

select * from 表名 where 列名 is null

5.查询前多少行/按比例查询结果

-- 查询前三行
select top 3 * from 表名
-- 查询前百分之20
select top(20) percent * from 表名

6.case when 判断

select *,case when 条件1 then 条件1等于的值
			  when 条件2 then 条件2等于的值
			  else 以上条件都不满足等于的值
			  end as 别名
			  from 表名 order by course asc

 7.in查询

-- in就是列值等于()里的就显现出来
select * from 表名 where 列名 in (列值1,列值2,列值3)

8.like查询（模糊查询）

-- 此方法需要与通配符一起使用
select * from 表名 where 列名 like '%嘉%'

 9.with关键字查询

此查询是把查询出来的结果整理出一个表中，然后可以通过这个新的表再次进行查询

with 新表名 as (select * from 原表名 where)
select * from 新表名

10.子查询/exists关键字查询

-- 子查询
-- 就是用一个表所查出的值，作为关键字再去查另一个表
select * from 表名1 where 列 in(select 列 from 表名 where 条件)
-- exists关键字
-- 个人理解exists()里面有值(true)则第一查询就有结果，如果没有值(false)第一个查询就不查询
select * from 表名1 where exists (select * from 表名 where 条件)
-- 如果两个表做关联，两次查询都一样的，exists就会把后一个查询的结果返回给上一个查询的结果
select * from 表名1 t1 where exists (select * from 表名2 t2 where t1.id = t2.id and 条件)

11.复制新表/表数据复制

-- 复制新表
select * into 新表 from 被复制的表
-- 表数据复制
insert 新表 select 所复制的数据列 from 被复制的表

12.distinct同一列去掉重复

select distinct 列名,列名,列名 from 表名

13.排序

升序asc，降序desc  可多列排序，优先级从左往右

select * from 表名 order by 列名,列名 desc

14.聚合查询分组

select 列名,sum(score) 别名 from 表名 group by 按照哪一列汇总(列名)

15.分页查询

SqlServer分页查询-CSDN博客

16.union/union all操作

可以把查询到的多个数据结构完全相同的表，合起来

union：自动去重 union all 不会去掉重复

select * from student union select * from student

17.行转列/列转行

行转列

原理：通过case when语句找到每个人的每个学科所对应的成绩，再用isnull函数把查询结果中空值的给替换掉，最后用gorup by语句按照每个人的名字进行合并

select name ,
	sum(isnull((case course when '数学' then score end),0)) as '数学',
	sum(isnull((case course when '语文' then score end),0)) as '语文',
	sum(isnull((case course when '英语' then score end),0)) as '英语'
from student group by name

列转行

 原理：先把每个学科的每个人的成绩查出来，再用union把表进行连接（你也可以把它创建成临时表或者新的表）

with stu as(
	select [name],course='数学',score=数学 from studentRow
	union
	select [name],course='语文',score=语文 from studentRow
	union
	select [name],course='英语',score=英语 from studentRow
)
 select * from stu

18.链接查询

左链接

select * from 表名1 t1 left join 表名2 t2 on t1.列名=t2.列名

内链接

select * from 表名1 t1 inner join 表名2 t2 on t1.列名=t2.列名

右链接

select * from 表名1 t1 right join 表名2 t2 on t1.列名=t2.列名

 全链接

select * from 表名1 t1 full join 表名2 t2 on t1.列名=t2.列名

21.递归查询

with 新表名(Id,MenuName,ParentId,le) as
(
	select *,le=1 from 表名 where id = 3
	union all
	select a.*,le=le+1 from 表名 a join Con on a.ParentId = con.Id
)
select * from Con;

视图

视图的概念

视图是一种数据库对象，是从一个或者多个数据表活视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果；

视图中只存放了视图的定义，不存放视图所对应的数据；

基本表中的数据发生变化，从视图中查询出的数据也随之改变，也可以通过视图来更改基本表中的数据。

 视图的特点

1. 视图能过简化用户的操作，从而简化查询语句；
2. 视图使用户能以多种角度看待同一数据，增加可读性；
3. 视图对重构数据库提供了一定程度的逻辑独立性；
4. 视图能够对机密数据提供安全保护；
5. 适当的利用视图可以更清晰的表达查询。

使用视图时注意事项

1. 只能在当前数据库中创建视图；
2. 视图的命名必须遵循命名规则，不可与表同名；
3. 如果视图中某一列是函数、数学表达式、常亮或者来自多个表的列名相同，则必须为列定义名称；
4. 当视图引用基表或视图被删除，该视图也不能再被使用；
5. 不能在视图上创建全文索引，不能在规则、默认的定义中引用视图。
6. 一个视图最多可以引用1024个列；
7. 视图最多可以嵌套32层。

创建视图

create view 新建视图名称 as 查询语句

 删除视图

if exists (select * from sysobjects where name = '视图名') -- 判断语句
drop view 视图名 -- 删除语句

查看视图

select * from 视图名

同义词

同义词定义

就是为一个数据库对象起一个其他名字，这样根据这个名字就可以找到这个数据库对象。

 管理工具创建同义词

1. 在“对象资源管理器”中，展开要创建新视图的数据库；
2. 右键单击“同义词”文件夹，然后选择“新建同义词”；
3. 在“添加同义词”对话框中，输入一下信息。

T-SQL编程

变量

局部变量

注意：数据类型不可以是TEXT、NTEXT、IMAGE类型，局部变量被引用时要再其名称前加上标志“@”，如果不为局部变量赋值，则默认值为null

定义变量：

declare @name varchar(20);

赋值：

-- 方法一
set @name = "哈哈"  -- （ 不推荐）
-- 方法二
select @text = 'hello'

输出内容

-- 方法一
print @name -- 打印到消息框
-- 方法二
select @name -- 映射到结果集

全局变量

在全局可用，系统预定义，用户不可以定义全局变量，用户不可以修改全局变量，全局变量以@@开头 常用全局变量：

-- 

流程控制

批量处理

一条或多条SQL语句的集合，一个次把多条语句交给服务器去执行。语句中有一条出现错误，所有语句就等于没有执行

GO
    -- SQL语句
GO

分支结构

declare @num1 int,@num2 int;
select @num1 = 3,@num2 = 6;

if @num1 > @num2
	begin
		print'@num1的值大于@num2的值'
	end
else if @num1 < @num2
	begin
		print'@num2的值大于@num1的值'
	end
else
	begin
		print'@num1的值等于@num2的值'
	end

循环

declare @num int;set @num = 1; -- 初始条件
while @num <= 10 -- 循环条件
begin -- 循环体
	--select @num as 结果;
	print '结果为：' + convert(varchar(5),@num);
	select @num+=1; -- 状态改变
end
break -- 结束循环
continue -- 跳出本次循环

自定义错误处理

begin try
	-- 可能出错的SQL语句
end try
begin catch
	-- 出错后所需要执行的SQL语句
end catch

存储过程

什么是存储过程？

• 存储过程（Stored Procedure）是在数据库中，一组完成特定功能的SQL语句，它存储在数据库中，一次编译后永久有效，用户通过调用指定存储过程的名字来执行。在数据量特别庞大的情况下可以用存储过程达到倍数的效率提升（项目中过多使用存储过程会使服务器的工作量增加）

存储过程的优点

1.  效率高：存储过程编译一次后，就会存到数据库中，每次调用时直接执行，而查询里SQL语句每执行一次就需要编译一次，所以存储过程省去了多次编译从而提高了效率。
2. 减少了传输数据：存储过程会编译好放在数据库里，我们在远程调用时，只需要调用数据库的名称和传入参数，不需要传入多条SQL语句了。
3. 复用性高
4. 可维护性高

存储过程的缺点

1. 编写复杂
2. 如果没有相应的权限，你将无法创建存储过程
3. 当服务器调用过多存储过程，用户访问量大了，那么压力就丢给数据库来解决，数据库压力会过大
4. 过多的存储过程，优化过于麻烦

常用系统存储过程

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 '旧库名','新库名' -- 更改登录名的默认数据库

自定义存储过程

创建语法

create proc | procedure 存储名(
	[
	{@参数 数据类型} [=默认值] [out|output],
	{@参数 数据类型} [=默认值] [out|output]
	]
)
as
begin
    SQL语句
end
go

存储过程修改语法

alter proc | procedure 存储过程名
as
beign
    sql语句;
end

存储过程删除语法

drop proc | procedure 存储过程名

调用语法

exec 存储过程名; ---不带参数调用
exec 存储过程名 参数1 [out|output],参数2 [out|output]; ---带参数调用

函数 

触发器

触发器（trigger）是SQL Server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

 触发器分类

1. DML触发器
2. DDL触发器
3. 登录触发器

DML触发器

DML触发器是一些附加在特定表或视图的操作代码，当数据库服务器中发生数据操作语言时间时执行这些操作。SqlServer中的DML触发器中有三种：

1.Insert触发器：向表中插入数据时被触发

        在向目标中插入数据后，会触发该表的Insert触发器，系统自动在内存中创建inserted表；如果不满足判断数据会进行回滚，插入对蛾数据操作会失败。

2.delete触发器：从表中删除数据时被触发

        在向目标中删除数据后，会触发该表的Delete触发器，系统自动在内存中国创建deleted表，deleted表存放的时删除的数据。

3.update触发器：修改表种数据时被触发

在向目标表总更新数据后，会触发该表的update触发器，系统自动在内存中创建deleted表和inserted表，deleted表存放的是更新前的数据，inserted表存放的是更新的数据。

USE 数据库
GO

IF EXISTS(
  SELECT *
    FROM sys.triggers
   WHERE name = N'<trigger_name, sysname, table_alter_drop_safety>'
     AND parent_class_desc = N'DATABASE'
)
	DROP TRIGGER 表名 ON DATABASE
GO

ALTER TRIGGER 存储过程名 ON 表名
	FOR insert/delete/update
AS 

BEGIN
   -- 语句
   declare @name varchar(50)
   select @name = name from inserted
   insert into ta_22 values(@name)  
END
GO

当遇到下列情景时，应考虑使用DML触发器：

1. 通过数据库中的相关表实现级联更改
2. 防止恶意或者错误的insert、update和delete操作，并强制执行check约束定义的限制更为复杂的其他限制。
3. 评估数据修改后表的状态，并根据该差异采取措施 

DDL触发器(数据定义语言,Data Definition Language)

DDL触发器是当服务器或者数据库中国发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更爱或记录数据中的更改或事件操作。

登录触发器

        登录触发器将为响应LOGIN事件而激发存储过程。与SQL Server实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自PRINT语句的消息）回传送到SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

触发器优缺点：

1. 强化约束：强制符合业务的规则和要求，能实现比check语句更为复杂的约束。
2. 跟踪变化：触发器可以侦测数据库内的操作，从而禁止数据库中未经许可的更新的变化。
3. 级联运行：侦测数据库内的操作时，可自动地级联影响整个数据库的各项内容。
4. 嵌套调用：触发器可以调用一个或多个存储过程。触发器最多可以嵌套32层
5. 可移植性差。
6. 占用服务器资源，给服务器造成压力。
7. 执行速度只要取决于数据库服务器的性能与触发器代码的复杂程度。
8. 嵌套调用一旦出现问题，排错困难，而且数据容易造成不一致，后期维护不方便。

注意事项

1. 尽量避免在触发器中执行耗时操作，因为触发器会与SQL语句认为在同一事务中，事务不结束，就无法释放锁。

2. 避免在触发器中做复杂操作，影响触发器性能的因素比较多（Eg：产品版本，所使用的架构等），要想编写高效的触发器考虑因素比较多，编写高性能触发器还是很难的。

3. 触发器编写时注意多行触发时的处理。（一般不建议使用游标） 

自定义类型

用户定义数据类型

通俗定义：用户自己设计并实现的数据类型就称为用户自定义数据类型，即使这些数据类型基于系统数据类型，可以理解为基础类型的一个延申。

CREATE TYPE [dbo].[Age] FROM [int] NOT NULL

用户定义表类型

定义：类型像是表的结构，使用基础类型包含字段，可以用于存储过程的参数传递（结构体）、

CREATE TYPE [dbo].[People] AS TABLE(
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL
)

序列

在SQL Server中，你可以使用序列（Sequence）来生成自增长的值。序列是一个独立的数据库对象，可以在多个表或多个列之间共享。

CREATE SEQUENCE MySequence
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 1000
    CYCLE;

在上面的示例中，我们创建了一个名为MySwquence的序列。它的起始值为 1，每次递增 1。最小值为 1，最大值为 1000。当达到最大值时，序列会循环回到最小值（由于CYCLE选项）。如果不希望序列循环，可以将CYCLE选项移除。

创建序列后，你可以使用NEXT VALUE FOR函数来获取下一个序列值。下面是一个使用序列的示例：

DECLARE @NextValue INT;
SET @NextValue = NEXT VALUE FOR MySequence;

在上面的示例中，我们声明了一个变量@NextValue,并使用NEXT VALUE FOR函数将下一个序列值赋给该变量。

你可以在插入数据时使用序列来生成自增长的值。例如：

INSERT INTO MyTable (ID, Name)
VALUES (NEXT VALUE FOR MySequence, 'John');

在上面的示例中，我们使用序列 MySwquence来生成ID列的自增长值。

这就是在 SQL Server 中使用序列的基本方法。你可以根据需要自定义序列的起始值、递增步长、最小值、最大值等属性。

约束

SqlServer数据库为了保存的数据更具备准确性，一致性，在SqlServer中支持的有约束，有规则来限定，如果符合规则就可以保存，如果不符合就不可以保存

主键约束（PRIMARY KEY）

主键在每个表中都是唯一的标识，且主键同时要设置not null（不能为空）约束，如果在指定主键约束时没有指定not null约束，SQL Server会自动添加Not Null约束。

外键约束

外键是指在表中保存其他表的主键，同样的外键一样需要设置Not Null约束

非空（Not Null）约束

在表中，被指定Not Null约束的列的值不能为null

唯一约束/唯一索引

唯一约束是保证该数据在表数据中是唯一的

检查约束

检查约束用于指定列是否满足列的要求，比如年龄的水不可以是负数，性别只能填男女

-- 创建表时指定约束
create table Test1
(
	-- 主键约束
	id int primary key,
	-- 非空约束
	Name varchar(50) not null,
	-- CHECK约束
	Sex BIT CHECK(Sex=0 or Sex=1),
	-- 默认值约束
	GreateDate DateTime DEFAULT GETDATE(),
	-- 外键约束
	ids int null foreign key references test2(id)
)

-- 以创建约束的方式指定
create table Test2
(
	Id int,
	Name varchar(50),
	Sex BIT,
	CrateDate DateTime
)
go
-- 已有的表增加非空约束
alter table test2
alter column id int not null
go
-- 已有的表增加主键约束（定义主键约束之前必须先定义该字段非空的约束）
alter table test2
add constraint test2_id primary key nonclustered(id)
go
-- 已有的表增加检查约束
alter table test2
add constraint test2_sex check(Sex in (0,1))
go
-- 已有的表增加默认值约束
alter table test2
add constraint test2_create
default getdate() for CrateDate
go
-- 已有的表增加外键
alter table test1
add constraint test1_ids foreign key(ids) references test2(id)
go

索引

在数据库中建立索引是为了加快数据的查询速度。数据库中的索引与书籍中的目录或书后的术语类似。在书中可以通过目录进行快速查找到某一页，无需翻阅整本书。在数据库中，索引使对数据的查找不需要对整个表进行扫描，就可以找到所需的数据。

索引的特点

1. 使用索引能提升数据库的性能，主要体现在一下几个方面
   1. 极大地提升数据库的查询速度，这也是其最主要的有点
   2. 通过创建唯一索引，能保证数据库中的各行数据具有唯一性
   3. 建立在外码上的索引能加速表与表之间的链接，益于实现数据的参照完整性
   4. 显著减少分组和排序查询所使用的时间
2. 在提升数据性能的同时，索引有一些负面影响
   1. 索引会占用物理存储空间，需要一定的额外物理存储来存放索引文件
   2. 索引的维护需要耗费时间，对数据表更新数据之后，相应的索引也需要动态维护

设计索引的注意事项

索引设计不合理会对数据库及应用程序造成不良影响，索引设计索引时要注意以下几点

1. 索引并非越多越好，一个数据库中若含有许多索引，不仅占用磁盘空间，也会造成大量的维护开销；
2. 对经常用于查询的列可以建立索引提高效率，但是需要避免插入不必要的数据
3. 数据量较小的数据表最好不使用索引，因为可能不会产生明显的优化效果
4. 对于不同值较少的列，不要建立索引，这样会降低更新数据的速度 

索引的类型

SQL Server数据库的索引可分为聚集索引、非聚集索引与其他索引3类

聚集索引 

聚集索引的B树是自下而上建立的，最下层的叶级节点存放的是数据，因此他既是索引页，又是数据页。多个数据页生成一个中间层节点的索引页，然后再由数个中间层节点的索引页合成更上层的索引页，如此上推，直到生成顶层的根节点的索引页。生成高一层节点的方法是：从叶级节点开始，高一层节点中每一行由索引关键字值和该值所在的数据页编号组成，其索引关键字值选取的是其下层节点中的最大活最小索引关键字的值。

索引的使用代价

当插入或删除数据时，除了会影响数据的排列顺序外，还会引起索引页中索引项的增加或减少， 系统会对索引页进行分裂或合并，以保证B树的平衡性，因此B树的中间节点数量以及B树的高度都有可能会发生变化，但这些调整都是数据库管理系统自动完成的，因此，在对有索引的表进行插入、删除和更改操作时，有可能会减低这些操作的执行性能。聚集索引对于那些经常要搜索列在连续范围内的值的查询特别有效。使用聚集索引找到包含第一个列值的行后，由于后续要查找的数据值在物理相邻而且有序，因此只要将数据值直接与查找的终止值进行比较即可。在创建聚集索引前，应先了解数据是如何被访问的，因为数据的访问方式直接影响了对索引的使用。如果索引建立不合适，则非但不能达到提高数据查询效率的目的，而且还会影响数据的插入、删除和更改操作的效率。因此，索引并不是建立的越多越好（建立索引需要占用空间，维护索引需要耗费时间），而是要有一些考虑因素。

聚集索引使用建议

下列情况可考虑创建聚集索引：

1. 包含大量非重复值的列。
2. 使用下列运算符返回一个范围值的查询：BETWEEN AND、>、>=、< 和 <=。
3. 经常被用作连接的列，一般来说，这些列是外键列
4. 对ORDER BY或GROUP BY子句中指定的列建立索引，可以使用数据库管理系统在查询时不必对数据再进行排序，从而可以提高查询性能。对于频繁进行更改操作的列则不适合建立聚集索引。 

 非聚集索引

非聚集索引与新华字典偏旁部首查字法类似。字典的内容（数据）存在一个地方，（部首）存储在另一个地方。而且字典的内容（数据）并不按部首（索引）的顺序存放，当偏旁2部首中的每个词在字典中都有确切的位置。非聚集索引就类似偏旁部首，而数据就类似于一本字典的文字。

 非聚集索引与聚集索引一样用B树结构，但有两个重要差别：

1. 数据不按非聚集索引关键字值的顺序排序和存储。
2. 非聚集索引的叶级节点不是存放数据的数据页。非聚集索引B树的叶级节点是索引行。每个索引行包含非聚集索引关键字值以及一个或多个行定位器，这些行定位器指向该关键字对应的数据行（如果索引不唯一，则可能是多行）。

非聚集索引使用建议

在创建非聚集索引之前，应先了解数据是如何被访问的，以使建立的索引科学合理。对于下述情况可考虑创建非聚集索引：

1. 包含大量非重复值的列。如果某列只有很少的非重复值，比如中有1和0，则不对这些列建立非聚集索引。
2. 经常作为查询条件使用的列。
3. 经常作为连接和分组条件的列。