数据文件
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 存储过程名 参数列表(多个参数,以逗号隔开)
优点:
- 提高应用程序的通用性和可移植性。多次调用,而不必重新再去编写,维护人员可以随时修改。
- 可以更有效的管理数据库权限。
- 提高执行SQL的速度。
- 减轻服务器的负担。
缺点:
- 专门维护它,占用数据库空间。
-- 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