SQL Server学习笔记

数据库管理技术的基本概念

1. 数据
2. 信息
3. 数据处理

数据库管理技术的发展阶段

1. 人工管理
2. 文件系统
3. 数据库系统
4. 大数据

数据库系统的组成

1. 硬件系统
2. 操作系统
3. .数据库
4. 数据库管理系统
5. 数据库应用系统的开发工具及相关接口软件
6. 数据库应用系统
7. 用户
   1. 数据库管理员
   2. 数据库分析师
   3. 应用程序员
   4. 终端用户

数据库体系结构

1. 集中式系统
2. 分布式系统
3. 个人计算机系统
4. 客户/服务器系统
5. 浏览器/服务器系统

数据库的三级模式

1. 内模式
2. 模式
3. 外模式

数据库的二级映像

1. 外模式/模式
2. 模式/内模式

数据库管理系统的功能

1. 数据定义
2. 数据操控
3. 数据库运行控制
4. 数据字典

登录方式

1. windows登录，本地登录
2. sqlserver登录，远程登录

数据库备份

mdf数据文件，ldf日志文件

1. 分离->附加 分离后数据库会不可用
2. 备份->还原 保存为.bak文件 任务——>备份
3. 数据库脚本

注释

单行注释 -- 多行注释 /* */

数据库相关

创建数据库

完整语法

▼
sql
复制代码
create database DBTEST
on --数据文件
(
  name = 'DBTEST', --逻辑名称
  filename = '',  --物理路径和名称 以.mdf结尾
  size = 5MB,	--文件的初始大小
  maxsize = 40MB，  --文件的最大大小
  filegrowth=2MB,  --文件增长方式 可以是大小，也可以是百分比
)
log on --日志文件
(
  name = 'DBTEST_log', --逻辑名称
  filename = '',  --物理路径和名称 以.ldf结尾
  size = 5MB,	--文件的初始大小
  filegrowth=2MB,  --文件增长方式 可以是大小，也可以是百分比
)

简单语法

▼
sql
复制代码
create database DBTEST  --采用默认值创建 

修改数据库

▼
sql
复制代码
alter database DNTEST

删除数据库

▼
sql
复制代码
drop database 数据库名称

切换数据库

▼
sql
复制代码
use DBTEST --use 数据库名称

表相关

创建表基础语法

▼
sql
复制代码
create table 表名
(
 字段1 数据类型，
 字段2 数据类型，
 字段3 数据类型，
) 

约束

• primary key:主建
• references 表名(字段名)：外键
• identify(1,1):自动增长，初始值1，增长步长1
• not null：不为空
• check():检查约束
• default()：默认值
• unique：唯一约束

数据类型

• int:整型
• float:浮点型
• decimal(n,m):n表示总长度，m表示小数位数
• date:年月日 --getdate() 获取当前时间
• datetime：年月日时分秒
• smalldatetime：范围更小
• char():定长字符串
• varchar():变长字符串
• text:长文本
• nchar,nvarchar,ntext:unicode字符,对中文友好

删除表

▼
sql
复制代码
drop table 表名

修改表结构

1.添加列

▼
sql
复制代码
alter table 表名 add 新列名 数据类型

2.删除列

▼
sql
复制代码
alter table 表名 drop column 列名

3.修改列 --如果已经存在数据了则可能报错，建议建表的时候就有冗余

▼
sql
复制代码
alter table 表名 alter column 列名 数据类型

维护约束

1.删除约束

▼
sql
复制代码
alter table 表名 drop constraint 约束名

2.添加约束

check约束

▼
sql
复制代码
alter table 表名 add constraint 约束名 check(表达式)

主键约束

▼
sql
复制代码
alter table 表名 add constraint 约束名 primary key(列名)

唯一约束

▼
sql
复制代码
alter table 表名 add constraint 约束名 unique(列名)

默认值约束

▼
sql
复制代码
alter table 表名 add constraint 约束名 default 默认值 for 列名

外键约束

▼
sql
复制代码
alter table 表名 add constraint 约束名 foreign key(列名)references 关联表名(列名)

数据相关

插入数据

▼
sql
复制代码
insert into 表名(字段1,字段2,字段3)values(内容1,内容2,内容3)

简写 --不建议

▼
sql
复制代码
insert into 表名 values(内容1,内容2,内容3)

一次性插入多行数据

▼
sql
复制代码
insert into 表名(字段1,字段2,字段3)
select 内容1,内容2,内容3 union
select 内容1,内容2,内容3 union
select 内容1,内容2,内容3

修改删除数据

1.修改

▼
sql
复制代码
update 表名 set 字段1=值1,字段2=值2 where 条件

2.删除数据

▼
sql
复制代码
delete from 表名 where 条件

关于删除

• ▼
  sql
  复制代码
  drop table 表名  --删除表对象
  

• ▼
  sql
  复制代码
  truncate table 表名  --删除数据(清空数据)，表对象即表结构依然存在，不能有条件，自动编号存在
  

• ▼
  sql
  复制代码
  delete from 表名  --删除数据(清空数据)，表对象即表结构依然存在，可以有条件，自动编号清除
  

查询相关

基本查询

1.查询所有列所有行

▼
sql
复制代码
select * from 表名

2.查询指定列

▼
sql
复制代码
select 列名1,列名2,列名3 from 表名

3.查询指定列,别名显示

▼
sql
复制代码
select 列名1 别名1,列名2 别名2,列名3 别名3 from 表名

4.查询指定列,去重

▼
sql
复制代码
select distinct(列名) from 表名

条件查询

▼
sql
复制代码
select * from 表名 where 条件
select top 10 * from 表名 where 条件  --前十位
select top 10 percent * from 表名 where 条件  --前百分之十
select * from 表名 where order by 字段 desc/asc

模糊查询

• like
• %：代表匹配0个字符，一个字符或多个字符
• _:代表匹配有且只有一个字符
• []:代表匹配范围内
• ！[]:代表匹配不在范围内
• substring(n,m,t) --n为字符串，m是起始位，t是跨度

聚合函数

• count:求数量
• max:求最大值
• min:求最小值
• sum: 求和
• avg:求平均值
• round:保留小数位 round(2.556,2) ——>2.55
• datediff:求时间差 datediff(year,时间1,时间2)

分组查询

▼
sql
复制代码
select * from 表名 where 条件 group by 列名 having 条件

--普通条件在where后，聚合条件在 having后 注：分组查询只允许查询分组条件和聚合函数

多表查询

▼
sql
复制代码
select * from 表名1,表名2 where 条件

1.内连接

▼
sql
复制代码
select * from 表名1 inner join 表名2 on 条件

2.外连接

▼
sql
复制代码
select * from 表名1 left join 表名2 on 条件  --左外
select * from 表名1 right join 表名2 on 条件  --右外
select * from 表名1 full join 表名2 on 条件  --全外

3.自连接

▼
sql
复制代码
select 别名1.字段,别名2.字段 from 表名 别名1 inner join 表名 别名2 on 条件

数据库设计

1.三范式

• 第一范式：原子性 不可再分
• 第二范式：实体唯一性
• 第三范式：不能有冗余

2.表关系

• 一对一
• 一对多
• 多对多

变量

--信息打印：

▼
sql
复制代码
print'hello,sql'      --消息窗口显示

▼
sql
复制代码
select'hello,sql'     --信息窗口显示

--变量：

1. 局部变量：以@开头，先声明，在赋值

   • ▼
     sql
     复制代码
     declare @str varchar(20)
     set @str='i like sql'        --赋值变量指定的值
     select @str ='i like sql'	 --一般用于表中的查询出的数据赋值给变量，如果查询结果有多条，取最后一条赋值
     

2. 全局变量：以@@开头，由系统就行定义和维护

--go语句:

1. 等待go语句之前的代码执行完之后才能执行后面的代码
2. 批处理结束的一个标志

运算符

强制类型转化

1. Convert(数据类型，变量名) 例：Convert(varchar(10)，@zc)
2. cast(变量名 as 数据类型) 例：cast(@zc as varchar(10))

流程控制语句

(1)选择分支

case选择：

▼
sql
复制代码
	case 
		when 条件 then 执行1
		when 条件 then 执行2
		else 最终执行
	end

if判断语句：

▼
sql
复制代码
		if 条件判断
		   begin
			 语句
		   end
		else
		   begin
		     语句
		   end

(2)循环

▼
sql
复制代码
while 条件
	begin
	  语句
	end

子查询

▼
sql
复制代码
    where条件
	临时表
	exist

分页

(1)top方案

▼
sql
复制代码
	declare @PageSize int = 5
	declare @PageIndex int = 3
	select top(@PageSize)*from Student
	where StuId not in (select top(@PageSize*(PageIndex-1))StuId from Student)

(2)使用row_number分页

▼
sql
复制代码
	declare @PageSize int = 5
	declare @PageIndex int = 3
	select *from
	(select ROW_NUMBER() over(order by StuId) RowId,*from Student) Temp
	where RowId between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize

事务

▼
sql
复制代码
begin transaction   --开始语句
语句
@@ERROR             --统计错误 如果出现错误 @@ERROR的值不为零

▼
sql
复制代码
commit transaction     --提交事务
rollback transaction   --回滚事务

索引

1. 聚集索引 --类似于按照拼音查找 只能有一个
2. 非聚集索引 --类似于按照部首查询 可以有多个

添加索引

▼
sql
复制代码
create [unique][clustered|nonclustered] index <index name> on <table or view name>(<colum name>[ASC|DESC][,...n])

查看索引 （sys.indexes）

▼
sql
复制代码
select * from sys.indexes where name = '索引名'

删除索引

▼
sql
复制代码
drop index 索引名 on 表名

使用索引

▼
sql
复制代码
select * from 表名 with (index=索引名) where 条件

视图

创建视图：

▼
sql
复制代码
create view 视图名 as select语句 go

查询视图：

▼
sql
复制代码
select * from 视图名

删除视图：

▼
sql
复制代码
drop view 视图名

游标

分类：

• 静态游标
• 动态游标
• 键集驱动游标

创建游标(scroll：滚动游标，没有scroll，只进)

▼
sql
复制代码
declare 游标名 cursor scroll for select 字段名 from 表名

打开游标

▼
sql
复制代码
open 游标名

关闭游标

▼
sql
复制代码
close 游标名

删除游标

▼
sql
复制代码
deallocate 游标名

提取数据

▼
sql
复制代码
fetch first from 游标名         --提取第一行
fetch last from 游标名		   --提取最后一行
fetch absolute 2 from 游标名    --提取第二行
fetch relative 2 from 游标名    --当前行下移两行
fetch next from 游标名  		   --下移一行
fetch prior from 游标名 		   --上移一行

--提取游标数据存放入变量，进行查询所有列信息

▼
sql
复制代码
declare @acc varchar(20)
fetch absolute 2 from mycur into @acc
select * from Menber where MenberAccount=@acc

--遍历游标

▼
sql
复制代码
  @@fetch_status  ：0提取成功，-1提取失败，-2不存在
declare @acc varchar(20)
fetch absolute 2 from mycur into @acc
while @@fetch_status=0
	begin
		print'提取成功：'+@acc
		fetch next from mycur into @acc
	end

--利用游标进行数据的修改和删除

▼
sql
复制代码
fetch absolute 2 from mycur
update Member set MemberPwd = '654321' where current of mycur

fetch absolute 2 from mycur
delete from Member where current of mycur

--一行多列游标

▼
sql
复制代码
declare 游标名 cursor scroll for select 字段1，字段2，字段3 from 表名

函数

1. 系统函数
2. 自定义函数
   1. 标量值函数（返回单个值）
   2. 表值函数（返回查询结果）

创建函数

▼
sql
复制代码
create function 函数名(@传入值 传入值类型) returns 返回值类型
as 
begin
	declare @返回值 返回值类型
	select @返回值 = 
	return @返回值
end

返回值类型的情况

1. 可以有其他逻辑代码

   ▼
   sql
   复制代码
   create function 函数名(@传入值 传入值类型) returns @返回值 table
   (
   字段1 数据类型，
   字段2 数据类型，
   字段3 数据类型，
   字段4 数据类型
   )
   as
   begin
   	insert into @返回值
   	查询语句
   	return
   end
   

2. 函数体只能有return+sql查询结果

   ▼
   sql
   复制代码
   create function 函数名(@传入值 传入值类型) returns table
   as
   return
   查询语句
   go	
   

   函数调用

   ▼
   sql
   复制代码
   select dbo.函数名()        --返回值为一个值
   select * from 函数名()     --返回值为一个表
   

函数删除

▼
sql
复制代码
drop function 函数名

触发器

分类

1. instead of 事前触发器
2. after 事后触发器

创建触发器

▼
sql
复制代码
create trigger 触发器名称 on 表名 [after|instead of] [insert|delete|update]
as 
	语句
go

   inserted插入的临时表
   deleted删除的临时表

数据更新实际上是先删除再插入

存储过程

存储过程与函数的主要区别：存储过程可以被其他程序调用，函数一般用于select语句中

创建存储过程

存储过程创建

▼
sql
复制代码
create proc 存储过程名 @变量1名 变量类型，@变量2名 变量类型，@变量3名 变量类型 output，@变量4名 变量类型 output
as
语句
go

存储过程的调用

▼
sql
复制代码
exec 存储过程名 变量值1，变量值2，变量名 output，变量名 output   --前两个用于输入，后两个用于接收，如果变量名(第三四个)已经赋值了则具有输入输出功能

删除存储过程

▼
sql
复制代码
drop proc 存储过程名