SQL基础

数据文件

1、主数据文件 *.mdf  有且只能有一个
2、次要数据文件 *.ndf  可以有0~n个

日志文件

1、*.ldf 必须有一个或多个

---

约束方法

一、主键约束

如果两列或多列合起来唯一标识表中的每一行，该主键叫“复合主键”
primary key

二、标识列

自增id
identity(x,y);x：起始值，y：每次增量

三、外键约束

foreign key references 主表名称(主键列名)

四、唯一约束

unique

五、T-SQL 创建约束

alert table 表名 add constraint 约束名称（自定义）  
	 primary key(列名)-- 主键
	 foreign key(列名) references 主表(列名)-- 外键
	 unique-- 唯一
	 check(逻辑表达式)-- 条件约束
	 default(值) for 列名--默认值

数据类型

1、varchar 最长 8000
2、nvarchar 最长 4000
3、nchar 最长 4000

分组查询

group by 字段 having 条件-- 分组后筛选条件
order by 字段 

连接查询

交叉连接：cross join

如果不带where子句时，就会返回被连接的两个表的数据行数的乘积
带了where子句，等价于inner join

类型转换

1、convert（类型（长度），值）
2、cast（值 as 类型）

字符串操作

1、字符位置查找

charindex('字符','字符串')--索引从1开始
patindex('%字符%','字符串')

2、取字符

left('字符串',X)--从左边取X个字符
right('字符串',X)--从右边取X个字符
substring('字符串',X,Y)--从字符串第X开始取Y个字符

3、大小写

upper('字符串')
lower('字符串')

4、去空格

ltrim(' 字符串 ')--去除左边的空格
rtrim(' 字符串 ')--去掉右边的空格

5、重复n次

replicate('字符串',X)--将字符串重复X次

6、翻转

reverse('字符串')--串符字

7、替换

replace('字符串','字符A','字符B')--字符串内字符A替换为字符B
stuff('字符串',x,y,'字符A')--指定字符A从字符串的第X开始替换Y长度的字符

---

索引

• 聚集索引：一张表只能有一个，如果有设置主键默认会创建主键字段的聚集索引
• 非聚集索引

-- 一般创建是非聚集索引
-- create clustered index...这种创建是聚集索引
create index 索引名称（自定义） on 表名称（表字段）
with-- with后面的可要可不要
(
drop_existing=on--表示如果这个字段已经有索引，就会删除原来的创建新的；off-不删除原有的，会提示一个错误
)

• 唯一非聚集索引

create unique nonclustered index 索引名称（自定义） on 表名称（表字段）
with
(
drop_existing=on,
pad_index=on,
fillfactor=50,--指定创建索引时，每个索引页的数据占索引页大小的百分比
ignore_dup_key=on,--单唯一索引插入重复的时候出现警告还是错误信息，off

)
-- fillfactor：填充因子
-- 读写比例：100：1   fillfactor=100
-- 读小于写：fillfactor=50-70
-- 读写各一半：fillfactor=80-90

• 复合索引
  在一个表上的多个列上建立索引

create nonclustered index 索引名称（自定义） on 表名称（表字段1,表字段2）
with
(
drop_existing=on
)

---

视图

• 标准试图：存储查询定义 没有存储数据
• 索引视图：被具体化了的，创建了索引，显著提高查询性能，聚合了很多行的查询，不太适合经常更新基本数据集；数据是实际存在的，删除视图里面的数据会同步删除掉基础表里面的数据；索引视图里面不要去删除修改数据
• 分区视图：一台或者多台服务器间连接一组成员表的分区数据，不存储数据

----- 标准视图
create view 视图名称
as
 select 字段,* from 表名称--一段查询语句
go

----- 索引视图
--select * 不能出现
-- 表名前面必须加dbo
create view 视图名称 with schemabinding
as
  select 字段 from dbo.表名称--一段查询语句
go
--- 索引视图创建唯一聚集索引
create unique clustered index 索引名称 
on 视图名称（视图字段）
go

----- 分区视图
create view 视图名称 
as
select 字段 from 表1
union all
select 字段 from 表2
go

--调用视图
select * from 视图名称

---

存储过程

• 系统存储过程：master 数据库中，其他数据库可以直接调用，调的时候不用再前面加入数据库名【因为在创建数据库的时候，这些系统存储过程会在新的数据库中自动创建】
• 自定义存储过程：可以传入参数，也可以有返回值；

执行：execute/exec 存储过程名 参数列表(多个参数，以逗号隔开)

优点:

1. 提高应用程序的通用性和可移植性。多次调用，而不必重新再去编写，维护人员可以随时修改。
2. 可以更有效的管理数据库权限。
3. 提高执行SQL的速度。
4. 减轻服务器的负担。

缺点:

1. 专门维护它，占用数据库空间。

-- create 创建
-- alter 修改
create/alter procedure/proc 存储过程名称
@参数1 参数类型
@参数2 参数类型
as
begin
--SQL语句
end
go

-- 删除存储过程
delete proc 存储过程名

---

触发器

事件触发，自动调用执行

• DDL触发器 create alter drop
• DML触发器 insert update delete
  • after触发器 insert update delete【操作之后触发】，在触发器中，标识列已经自动生成，有值
  • instead of 触发器 insert update delete【不执行定义的操作，而执行的是触发器本身】；在触发器中，标识列未生成 ，没有值

系统表

• inserted 存放插入或更新后的数据
• deleted 存放更新前或删除的数据

after触发器【对表操作之后触发】

create/alter trigger 触发器名称
on 表名
for【可以写after】 操作【insert update delete】
as
-- SQL语句
go

-- 判断表还在不在
if(OBJECT_ID('表名','U') is not null)
begin
-- 存在
end
else
begin
-- 不存在
end

instead of 触发器 【先执行的是触发器本身，再执行发生触发的内容】

create/alter trigger 触发器名称
on 表名
instead of 操作【insert update delete】
as
-- SQL语句
go

---

游标

• 对数据查询结果集的一种访问机制，用户对结果集进行逐条访问，单条数据
• 对象：结果集
• 定义在特定结果集上的指针，控制这个指针来遍历数据集或指定特定的行
• 作用：定位到结果集中的某一行，对当前位置的数据进行读写
• 缺点：数据读取出来存放到TempDB里，放到内存，内存空间有限，不可以太大，内存空间不足，适用于：数据量小的情况
• 游标分类：静态，动态，只进，键集驱动游标
  • 静态：结果集，对数据库如何操作，结果集都不会变；结果集与操作之后的数据一致—关闭游标，再重新打开
  • 动态：与静态相对，结果集获取所有的改变；提取时，行数据、顺序、成员都会发生变化，对数据库的所有操作都会通过游标可见。api函数或SQL where current of 子句通过游标进行更新。游标外部所做的更新直到提交时才会体现
  • 只进：不支持滚动，只能从头读到尾，对数据所做的更改是可见的。
  • 键集驱动游标
• 游标的生命周期：声明—打开—读取—关闭—释放

-- 创建只进游标
-- global：全局的
-- local：局部的
declare 名称 cursor global/local
for
--查询SQL

--打开
open 名称
--存放单行数据
--varUser存放单行数据UserId UserName Age
declare @varUser cursor,@uId int, @uName varchar(50) , @age int
set varUser=user sursor.
--提取数据next 向下prior向上.first第一个_ last最后一个
--absolute n 绝对位置relative n从当前位置开始第n个
fetch next from 游标变量名 into 变量列表
while @@fetch_status=0
begin
--处理SQL
end
----好多啊不想写了。。。。。

--关闭游标
close 名称
--释放游标
deallocate  名称
go

-- 创建静态、动态游标
--static ：标识是静态的游标
--dynamic：动态的
declare 名称 cursor static/dynamic global/local
for