SQL Server笔记

Sql Server

约束：规定表中的数据规则。如果存在违反约束的数据行为，行为就会被阻止。
创建约束的时间：建表中或建表后

主键：Primary Key约束，唯一，非空，不可修改
外键：Foreign Key约束，加强两个表的一列或者多列数据之间的连接。建立的外键与其他表的主键相关联。

Unique约束：唯一约束，该列数据不可有相同的值

Check约束：通过逻辑表达式来判断数据的有效性，如时间格式/数据范围等

Default约束：默认值

---

数据库脚本

• 用于创建数据库对象的语句集合

T-SQL：Transact-SQL基于SQL的结构化查询语言，用于应用程序和数据库之间沟通的编程语言。Sql Server支持脚本语言。

SQL语言：一种有目的的编程语言，用于存储、查询、更新和管理关系数据库。高级的非过程化的编程语言。

作用：可完全移植。

分类：
DQL：数据查询语言 SELECT SELECT 列 FROM表名 WHERE 条件
DML：数据操纵语言

• INSERT INSERT INTO 表名(列1，列2，……) VALUES (值1，值2，……)
• UPDATE UPDATE 表名SET 列 = 值，列=值 where 条件；
• DELETE delete from 表名 where 条件；

DDL：数据定义语言 创建数据库及其对象

• CREATE/ALTER/DROP database/table/view/Proc/Index

DCL：数据控制语言 用于授予或回收访问数据库的某种特权，对数据进行监视(事务)

• Commit 提交；rollback回滚；grant授权

创建脚本数据库：

• 使用脚本语言创建数据库，为什么首先定位到master数据库？
  Master：系统数据库，记录了Sql Server系统中的所有系统级信息，还记录了其他数据库的存在，数据库文件的位置，SQL Server的初始化消息

实例：

use master --选择操作的数据库
	go  --批处理命令

	--数据库创建
create database TestNewBase -- 数据库名
on primary   --主文件组
(
	name = 'TestNewBase', --数据库主要数据文件的逻辑名
	fileName = 'E:\database\TestNewBase.mdf',--主要数据文件的路径(绝对路径)
	size=5MB,--数据库主要数据文件的初始大小
	filegrowth=1MB--文件的增量
)
log on --创建日志文件
(
	name = 'TestNewBase_log', --日志主要数据文件的逻辑名
	fileName = 'E:\database\TestNewBase_log.mdf',--主要日志文件的路径(绝对路径)
	size=5MB,--日志文件的初始大小
	filegrowth=10% --文件的增量,可以直接指定大小，也可以使用百分比
)
go

---

表创建

use TestNewBase
go
--创建一个新的表,产品信息表
create table ProductInfos
(
	Id int identity(1001,1) primary key,
	ProNo varchar(50) not null,
	ProName varchar(50) not null,
	TypeId int not null,
	Price decimal(18,2) null,
	ProCount int null
)

create table ProductType
(
	TypeId int identity(1,1) primary key,
	TypeName nvarchar(20) not null
)
go

--删除表
drop table Productinfos
go

---

表修改

1. 直接在脚本上进行修改
   在原来脚本上进行修改，删除原表，再次执行创建表的脚本(注：空表，或者数据没啥用的时候)

2. 脚本语言修改

--添加一列：alter table 表名 add 列名 参数类型 约束
alter table ProductInfos add Protectmark nvarchar(max) not null

--删除一列：alter table 表名 drop column 列名
alter table ProductInfos drop column Protectmark

--修改一列的属性：alter table 表名 alter column 列名 参数类型 约束
alter table ProductInfos alter column ProNo nvarchar(50) not null

--修改一个列名  慎用！！
exec sp_rename 'ProductInfos.ProCount','Count','column' 

---

表约束

1. 创建中建立约束

--主键约束    ... +primary key
Id int identity(1001,1) primary key,
--外键约束   ...+foreign key references 表名(列名)
TypeId int not null foreign key references ProductType(TypeId),
--唯一约束  直接加unique关键字
ProNo varchar(50) unique not null,
--默认约束 default后加默认值
ProCount int default('') null
--check约束 ：check跟表达式
Price decimal(18,2) check(Price < 10000) null,

2. 创建后建立约束

--创建约束
--主键
alter table ProductInfos add constraint PK_ProductInfos primary key(Id)

--外键
alter table ProductInfos add constraint FK_ProductInfos foreign key(TypeId) references ProductType(TypeId)

--唯一约束
alter table ProductInfos add constraint IX_ProductInfos_ProNo unique(ProNo)
--唯一约束 多个列
alter table ProductInfos add constraint IX_ProductInfos_ProNo unique(ProNo,ProName)

--check约束
alter table ProductInfos add constraint CK_ProductInfos_Price check(Price < 10000)

--default约束
alter table ProductInfos add constraint DF_ProductInfos default(0) for ProCount

---

插入数据

--插入数据
 --1、单条数据插入:
 --insert (into) 表名(列名，列名，…) values (值，值，…)
 insert into ProductType(TypeName) values ('人工智能')
--insert (into) 表名(列名，列名，…) select 值，值，…
 insert into ProductType(TypeName) select '人事'

 --2、多条数据插入
  --insert (into) 表名(列名，列名，…) values (值，值，…),(值，值，…),(值，值，…),(值，值，…),…
 insert into ProductType(TypeName) values ('人工智能1'),('人工智能2'),('人工智能3'),('人工智能4')
 --insert (into) 表名(列名，列名，…) select 值，值，… union  select 值，值，… union  select 值，值，…
 --union 插入去重，union all 插入可重复
 insert into ProductType(TypeName) select '人事1' union select '人事2' union select '人事2' union all select '人事'

 --3、克隆表:  将一张表中的数据复制到另一张表
 insert into ProductType(TypeName) --目标
 select TypeName from ProductType  --源表

 --将查询的数据新建一张表存储
 select TypeName into TName from ProductType

---

跟新与删除

 --表的更新操作 :update 表名 set 属性 = 修改值,属性 = 修改值,… where 条件1 and/or 条件2
update TName set TypeName = '长泰' where TypeName = '人事'

--数据删除
--1、只删除表的数据  delete造成的删除会造成标识列不连续
delete from TName where TypeName = '人事1'

--truncate  谨慎使用，不可恢复性
truncate table TName --将表的数据清空，标识列中的数据也恢复到初始化状态
--truncate的效率比delete要高，delete每次删除都要记录一次日志，
--truncate不会记录日志,不激活触发器,drop,truncate即时操作，不能rollback,delete,update,insert记录日志，可恢复。

---

表查询

--表查询
--1、查询全部
select * from ProductType
--2、选择列查询
select TypeName from ProductType
--3、别名查询 方式 列名 as 别名   列名 别名   别名=列名
select TypeName as 类型名 from ProductType
--4、排序
--主键,默认升序排列asc,  desc倒叙
--order by 后面可以跟多个属性，排序顺序按照属性的前后顺序排序
select * from ProductType order by TypeId desc
-- 一行语句 前后顺序  where …… group by …… order by

---

模糊查询

--模糊查询
--1、% 0或多个字符匹配
select * from TName where TypeName like '%1%'  --包含于
select * from TName where TypeName like '%2'  --结尾于
select * from TName where TypeName like '人%'  --开始于
--2、_ 匹配单个字符
select * from TName where TypeName like '人_智能'  
--3、[] 范围匹配，包含括号内的字符
select * from TName where TypeName like '人[共工六]智能'  
select * from TName where TypeName like '人工智能[0-9]'
--4、[^]不在括号内的单个字符
select * from TName where TypeName like '人工智能[^2-9]'

---

范围查询

--范围查询
select top 3 * from TName
-- 百分比取整数，若不能取整，则取整后加一
select top 26 percent * from TName

--比较运算符 > < >= <= <>
select * from TName where TypeName in ('人工智能','长泰')
select * from TName where TypeName not in ('人工智能','长泰')
--嵌套查询
select * from TName where TypeName not in (
	select TypeName from TName where TypeName like '人工智能[^2-9]'
)
--brtween…and… 等价于 >= 和 <=
--推荐brtween…and…写法，包含前后最大最小值
select * from TName where TypeName between '1' and  '99'

---

聚合函数

--聚合函数 :对一组值进行计算返回单一的值
-- count 统计数   sum 求和   avg求平均   max 求最大值   min求最小值
select count(1) Ncount from TName
select sum(Age) Asum from TName
select avg(Age) Aavg from TName
select max(Age) Amax from TName
select min(Age) Amin from TName

---

分组查询

--分组查询
select TypeId, count(1) 类型数 from ProductInfos group by TypeId

--select后的列名都要出现在group by之后或者聚合函数之中
select TypeId, count(1) 类型数 from ProductInfos
where Id != 1002  --查询条件
group by TypeId  --查询后的分组
having TypeId >1 -- 分组后的第一次筛选
order by TypeId desc   --展示顺序

---

内连接

--显示写法
select Id,ProNo,Price,typ.TypeName,ProCount from ProductInfos info inner join ProductType typ on info.TypeId = typ.TypeId
--隐式写法
select Id,ProNo,Price,typ.TypeName,ProCount from ProductInfos info ,ProductType typ where info.TypeId = typ.TypeId

---

外连接

--左连接，返回左表的所有数据，右表中没有匹配的不显示，为null    outer 可以不写
select info.Id,info.ProNo,info.Price,typ.TypeName,info.ProCount from ProductType typ left outer join ProductInfos info on info.TypeId = typ.TypeId

--右连接
select info.Id,info.ProNo,info.Price,typ.TypeName,info.ProCount from ProductType typ right outer join ProductInfos info on info.TypeId = typ.TypeId

---

全连接，交叉连接

--全连接，返回左右两表全部的数据，没有在另外一个表匹配到的属性值显示为null
select info.Id,info.ProNo,info.Price,typ.TypeName,info.ProCount 
from ProductType typ 
full outer join ProductInfos info 
on info.TypeId = typ.TypeId
--交叉连接 cross join  笛卡尔积   如果不带where，则显示两个表的乘机行数，如果进行了关联的话，返回等价于inner join返回的匹配数据
select info.Id,info.ProNo,info.Price,typ.TypeName,info.ProCount 
from ProductType typ 
cross join ProductInfos info 
--带where条件子句的
select info.Id,info.ProNo,info.Price,typ.TypeName,info.ProCount 
from ProductType typ 
cross join ProductInfos info 
where info.TypeId = typ.TypeId

---

数值转换

--数制转换
select '1'+'2'
select 1+2
select 'ab'+CONVERT(varchar,4) --转换为字符进行拼接
select CONVERT(varchar(10),GETDATE(),126) --日期格式，126为字符转换后的格式，有不同的格式，自己搜

select cast('2022-02-12 12:12:12' as datetime)
select 'ab'+cast(2 as varchar(3)) --转换为字符进行拼接

---

字符串操作

--返回字符串中出现的初始位置  sql中首位是从 1 开始计数
select CHARINDEX('bf','sadbfdsabcd')

--效果同上，不过 patindex 函数必须字串前后带有%
select patindex('%bf%','sadbfdsabcd')

--大小写转换
select UPPER ('asda')
select LOWER('SDSAs')

--取长度
select LEN('sdad')

--去空格
select LTRIM('     das       ') --'das      ' 
select rtrim('     das       ') --'     das'

--取子串
select LEFT('asgdhqjwgdyuag',4) --从左边取四个字符
select right('asgdhqjwgdyuag',4) --从右边取四个字符
select SUBSTRING('asgdhqjwgdyuag',4,3)--从第四个取三个  dhq

--重复字符串后输出
select REPLICATE('ass',4) --重复四次后输出

--字符串翻转
select REVERSE('asdfg')  --点到后输入

select REPLACE('assdfssda','ss','oo') --字符串替换，所有的ss替换成oo

select STUFF('asdfg',2,2,'bb') --指定位置与长度替换子串

---

索引

• 相当于一个目录，在查找的过程中，快速定位查找的数据，不必搜索整个表，可以提高查询速度
• 缺点，占用存储空间，并不是越多越好，索引并不能提高系统性能
• 目的，更加高效的查询数据，减少系统的响应时间
• 分类，聚簇索引(主键索引)，非聚簇索引(唯一索引)
• 聚簇索引，Clustered，逻辑顺序和物理地址是一致的，最多只有一个，可以没有
• 非聚簇索引，NonClustered，逻辑顺序与物理地址顺序并不一致，可以有多个，也可以没有
• 非聚簇索引 比 聚集索引效率 低
• 多个列上建立索引，这些列建立组合索引
• 选择建立索引的列，小数据类型的，访问速度比较快

--创建索引时，默认是非聚簇索引
create clustered index PK_ProductInfos_Id 
on ProductInfos(Id) 
with 
(
	drop_existing=on  --如果存下，删除后在创建一个空的
)


create unique nonclustered index PK_ProductInfos_ProNo  --唯一非聚簇索引
on ProductInfos(ProNo) 
with 
(
	pad_index =on,
	fillfactor=50, --指定创建索引时,每个索引的数据	占索引页大小的百分比
	--读写比 100:1    100
	--读小于写 50-70
	--读写各一半 80-90
	ignore_dup_key=on
)

create unique nonclustered index PK_ProductInfos_ProNamePrice  --复合非聚簇索引
on ProductInfos(ProName,Price) 
with 
(
	pad_index =on,  --打开填充因子，就要设置 fillfactor
	fillfactor=50, --指定创建索引时,每个索引的数据	占索引页大小的百分比
	--读写比 100:1    100
	--读小于写 50-70
	--读写各一半 80-90
	ignore_dup_key=on --插入拒绝重复索引列的值
)

---

视图

• 虚拟表，由一个或者多个表查询得到的，将查询定义保存起来，实际不包含数据
• 与表的区别，表是存储数据的地方，视图存储的是存储语句(索引视图除外，被具体化了)
• 简化查询，增加数据的保密性，安全上得到保证
• 缺点，只是简化查询，并不能提高查询效率，增加维护成本
• 分类，标准视图 索引视图 分区视图
• 标准视图，定义了查询定义，没有存储数据
• 索引视图，被具体化的，创建了索引，适合聚合了很多行的查询，不适合经常查询的表，显著提高查询效率，由于数据是实际存在的，所以删除索引中的数据，实际表中的数据也会被删除，在索引视图中不要去删除或者修改数据
• 分区试图，一台或多台服务器间水平连接一组成员表的分区数据，不允许删除和修改视图中的数据

--标准视图
create view Pro_View
as
--T-SQL语句
select Id,ProNo,ProName from ProductInfos

--对视图的使用和表的使用方式一样
select * from Pro_View

--索引视图 查询语句不能带 * ，from后的表必须由两部分构成，并且对象不能引用自身
create view Pro_View_Index with schemabinding
as
select Id,ProNo,ProName from dbo.ProductInfos
--使用与表等同
select * from Pro_View_Index

--索引视图创建唯一聚簇索引
create unique clustered index UQ_Pro_View_Index_Index 
on Pro_View_Index(Id)

---

存储过程

• 一种为了完成特定任务的一组或多组SQL语句的集合，经编译存储在服务器端的数据库中，可以利用存储过程来加速SQL语句的执行。调用名称，传入参数，执行来完成特定的功能
• 分类：
  – 系统存储过程，master数据库中，其他数据库可以直接调用，并且不必在前面加上数据库名，创建数据库时，这些系统存储过程在新的是数据库中自动创建
  –自定义存储过程，用户自己创建，特定功能而创建。可以传入参数，也可有返回值，返回值用于标志是否执行成功。里面可以只有一个操作或者多个操作。
  –执行，execute/exec 存储过程名 参数列表(多个参数使用逗号隔开)
• 优点，提高应用程序的可用性和可移植性。多次调用而不必重新去编写，维护人员可以随时修改；可以更有效的管理数据库权限；提高执行SQL的速度；减轻服务器的负担
• 缺点，专门维护，占用数据空间
• 语法：create proc 存储过程名 @userId int，@userName varchar(20) AS BEGIN T-SQL语句 END go
• 自我理解，预存sql语句，使用直接调用,运行不是基于事务的，不是一次提交。

--存储过程 
--创建存储过程
create proc Product_Proc 
AS BEGIN  
	select * from ProductInfos
END 
go

--使用存储过程
exec Product_Proc

--带参数的
create proc Add_ProductType 
@typeName varchar(20)
AS BEGIN  
	insert into ProductType (TypeName) values (@typeName);
	select * from ProductType
END 
go

exec Add_ProductType '轻工业'