数据库学习笔记【sql server】

数据库

创建数据库

if not exists(select * from sysdatabases where name='DB_库名')
begin
	create database DB_库名
	on(		--数据主文件
		name = '',	--逻辑名
		filename = '.mdf',	--物理名
		size = mb,	--大小
		filegrowth = %/mb,	--增长大小
		maxsize = mb	--最大存储
	),(		--数据附文件
		name = '',	--逻辑名
		filename = '.ndf',	--物理名
		size = mb,	--大小
		filegrowth = %/mb,	--增长大小
		maxsize = mb	--最大存储
	)
	log on(
	),(
	)
end

修改数据库

修改库名

alter database 库名
modify name = '库名'

修改库属性

alter database 库名
modify file(
)

查看数据库属性

exec sp_helpDB

删除数据库

drop database 库名

数据表

创建表

if not exists(select * from sysobjects where name='TB_表名')
begin
	create table TB_表名
	(
		字段名 数据类型(长度)
		primary ket --主键
		default ('')  --默认值
		identity(起始值,增长值)
		unique --唯一值
		check(字段 like[in] '')
		references 表明 (字段名)  --外键
	)
end

修改表

表名

exec sp_rename 'old','new'

字段

修改字段名

exec sp_rename 'old.字段名','new'

添加字段

alter table 表名
add 字段名 数据类型(长度)

修改字段属性

alter table 表名
alter colunm 字段名 数据类型(长度)
[注]：标识符不同

//设置字段初始值
dbcc checkident('表名',reseed,数值)

删除字段

alter table 表名
drop colunm 字段名

删除表

drop table TB_表名

约束

主键约束

alter table 表名
add constraint PK_字段名 primary key(字段名)

唯一约束

alter table 表名
add constraint UQ_字段名 unique(字段名)

默认值约束

alter table 表名
add constraint DF_字段名 default('') for 字段名

检查约束

alter table 表名
add constraint CK_字段名 check(字段名 like[in] ''[('','')])

外键约束

alter table 表名
add constraint FK_字段名 foreign key (字段) references 主表(字段)

删除约束

alter table 表名
drop constraint 约束名

数据处理

插入数据

//方法一
insert into TB_表名
values ('',''),('','')

//方法二
insert into TB_表名
select '','' union
select '',''

修改数据

update TB_表名 set 列名=''
where 条件	--用于定位记录

删除数据

//方法一
delete from TB_表名 
where 条件

//方法二
truncate table 数据表明		--删除所有行

数据查询

语法：
select [distinct]/[top (num or percent)] <列名> as 表现,<列名> as 表现
from TB_表名
[group by <条件>]		--聚合函数
[where <条件>]
[order by <列明> [asc/desc] ]

//字段表现方式
1、字段 as 表现
2、表现 = 字段
3、字段 表现

// 注意：
// 凡是在group by后面出现的字段，必须同时在select后面出现；
// 凡是在select后面出现的、同时未在聚合函数中出现的字段，必须同时出现在group by后面

1. 补充：row_number() over partition by 分组聚合

-- mysql根据字段分组排序生成新列记录排名
SELECT
*, Row_Number() OVER (partition by 分组字段 ORDER BY 排序字段 desc/asc) col_name 
FROM
table_name

2. 手机号脱敏操作

select name, concat(left(mobile,3),'****',right(mobile,4)) as mobile from table

3. 根据字段排序获取对应值前后

-- dt 日期
-- num 
-- lead(num,0) over(order by dt desc) as num_1 获取当天的数值
-- lead(num,1) over(order by dt desc) as num_1 获取前一天的数值
-- lead(num,7) over(order by dt desc) as num_7 获取七天前的数值
select dt,num
lead(num,1) over(order by dt desc) as num_1
from table_name

4. 字段排序

SELECT 
  Score,
  DENSE_RANK() OVER(ORDER BY Score DESC) AS `Rank`
FROM
  Scores;
-- 使用说明
DENSE_RANK	并列连续排名
RANK	并列跳跃排名
ROW_NUMBER	连续排名

5. 查询表字段名

select COLUMN_NAME as columnName from information_schema.COLUMNS where table_name = '{表名}' and table_schema = '{库名}';

连接查询

谓词连接

select *from 表1,表2
where <条件>

内连接

select *from 表1 join 表2 on <条件>

自连接

复制复制

表存在

insert into TB_new select *from TB_old

表未存

insert * into TB_new from TB_old

两表结构不同

insert into TB_new (字段1,字段2) select 字段1,字段2 from TB_old

连接查询

谓词连接

select *from 表1 表2 where 条件

内连接

select *from 表1 [inter] join 表2 on 条件

外连接

select *from 表1 (left/right/full) [outer] join 表2 on 条件

子查询

语法：
select ... from 表1,表2
on 条件
where 字段1 运算符[in/not in] (子查询)

子查询：select 字段 from 表 where 条件

MAX(字段) = >=(子查询)
=any = in
<>any != not in

exists

if exists() 是否存在,返回true/false

语法：
select ... from 表1,表2
on 条件
where [not] exists (子查询)

子查询：select *from 表 where 条件 and 表1[2].字段=子查询.字段

视图

创建视图

create view V_视图名(字段列表)
[with encryption]    --加密,看不到基表
as (查询语句)
[with check option]    --防止用户修改数据

管理视图

alter view V_视图名
as (查询语句)	--直接修改

删除视图

drop view V_视图名

索引

创建索引

create [unique][clustered|nonclustered]
index idx_索引名
on 表名 (列名[asc|desc])
[注]：
1、unique:用来指定创建的索引是唯一索引
2、clustered|nonclustered:聚集索引|非聚集索引,指定被创建索引的类型。

管理索引

alter index 索引名 on 表名 rebuild --重新生成索引
alter index 索引名 on 表名 reorganize -- 重新组织索引
alter index 索引名 on 表名 disable --禁用索引
drop index 索引名 on 表名 --删除索引

查询索引

select 字段 from 表名
with(index=索引名)

查看索引

dbcc showcontig
(表名,索引名)

函数

日期

datediff(参数[yyyy/mm/dd],开始日期，结束日期)	--返回数字
datepart(yyyy,字段)	--获取字段数值
getdate()	--获取当前时间

T-SQL数据库编程

全局变量：@@
局部变量：declare申明@，初始值为null，由set和select赋值

//例子1
declare @a varchar(20),@b decimal(15,2)
set @b = ''
select @a = 字段 from 表 where  字段=@b
print @a

//例子2
declare @a varchar(20),@b decimal(15,2)(数据类型转换-->cast(变量 as varchar(20)))
set @b = ''
if exitis(查询条件)
begin
	赋值
end
print @a

存储过程

创建和使用存储过程

//例子1
create proc 过程名
@参数名 数据类型='默认值(会被覆盖)',
@参数名 数据类型 output
as
(1)查询过程
where 字段=@参数
(2)方法
declare @参数名 数据类型,@参数名 数据类型	--申明变量
select @参数名=数值	--多参赋值
while 判断条件
begin
select @参数=数值	
end

(1)exec 过程名 ['参数']		--通过参数查看字段
(2)	declare @参数名 数据类型
	exec 过程名 数值,@参数名 output
	select @参数名		--通过数值查看数值

修改存储过程

alter

删除存储过程

drop proc 过程名

自定义函数

//创建标量函数
create function F_函数名(@参数名 数据类型,@参数名 数据类型)
returns int
as
begin
return 数值
end

调用语句：select dbo.函数名(参数)		--调用函数

//创建表值函数
create function 函数名(@参数名 数据类型)
returns table
as
return (查询语句)

查询语句：select *from 函数名('参数')

create function 函数名
(@参数)
returns @临时表名 table
(
	字段 数据类型
)
as
begin
	insert into @表名
	查询语句
return
end

//删除函数
drop funciton 函数名

游标的使用

//创建游标
declare cur_Name cursor
for
查询语句

//打开游标
open 游标名
declare @变量 数据类型
print '游标结果集中的记录总数为'+cast(@@cursor_rows as 数据类型)
//遍历游标
fetch next from cur_Name into @变量
while @@Fetch_status=0
begin
print ''+cast(@id as 数据类型)(转为字符)
--定位到下一条记录
fetch next from cur_Name into @变量
end
close cur_Name
deallocate cur_Name

//修改游标
update 表名 set 字段
where current of cur_Name

触发器

create trigger trigger_name
on table/view
for/alter/instead of
[insert/update/dalete]
as
输出

创建用户和分配权限

//创建登陆名
--创建一个”soft“，”123“登录名（服务器）
create login soft
with password='1234'

--修改密码
alter login soft
with password='123'

--删除用户
drio login soft

//创建数据库用户，访问指定数据库(如DB_HR)
use DB_HR
go

--创建数据库用户
create user soft
for login soft

--删除数据库用户
drop user soft

//分配权限
grant select,insert,update on table_name to soft

--禁止权限
deny delete on table_name to soft

--收回权限
revoke insert on table_name from soft

备份和恢复

--备份数据库
backup database 数据库名 to disk='路径/.bak'

--删除数据库
drop database 数据库名

--恢复数据库
resfore database 数据库名_NEW from disk='路径/.bak'