sql server

SQL SERVER

一、基本使用

1.1 数据库

-- 创建数据库
create database DBName

-- 使用数据库
use DBName

-- 删除
drop DBName

1.2 表

-- 创建表
create table 表名(
字段名 数据类型 约束,
    ...
);

1.2.1 列

使用alter 对表中列进行操作

-- 添加列
alter table 表名 add 新列名 数据类型

-- 删除列
alter table 表名 drop column 列名

-- 修改列
alter table 表名 alter column 列名 数据类型
-- [如果修改数据类型，可能导致已有数据损坏]

1.2.1 约束

-- 删除约束
alter table 表名 drop constraint 约束名

-- 添加约束
alter table 表名 add constraint 约束名 约束表达式;
-- [约束表达式中需指定所对应的列]

-- 添加check约束
alter table 表名 add constraint 约束名 check(表达式);
-- 添加 主键 约束
alter table 表名 add constraint 约束名 primary key(列名);
- -添加 唯一 约束
alter table 表名 add constraint 约束名 unique(列名);
-- 添加 默认值 约束
alter table 表名 add constraint 约束名 default 默认值 for 列名;
-- 添加 外键 约束
alter table 表名 add constraint 约束名 foreign key(列名) references 关联表名(主键列名)

1.3 数据

1.3.1 增删改

-- 插入
insert into [表名](字段名,...) values(数据,...);
-- [未指明的字段为默认值]

-- 修改
update [表名] set 字段1=值1, 字段2 = 值2 where 条件;

-- 删除数据
delete from [表名] where 条件;

truncate table [表名];
-- [清空数据]
-- 和 delete from [表名];的区别：
-- 如果有自增的字段，delete后插入数据，该字段将延续原有值自增（自增续写
-- truncate 将从初始值开始（自增复位

1.3.2 查询

1 基本查询

使用 select 进行查询

-- 查询所有
select * from [表名];

-- 查询字段
select 字段1,字段2 from [表名];

-- 设置别名（使用 [as＋别名] 或 [空格＋别名] 均可
select 字段1 别名1,字段2 as 别名2 from [表名];

-- 去除字段重复值查询
select distinct 字段 from [表名];
 
-- 获取字段修改前和预计修改后的值 【仅返回，不会实际修改数据】
select 字段, 字段操作 as 别名 from [表名];
select age, age + 10 as modified_age from [表名];

2 条件查询

使用 where 添加条件

-- 条件查询
select * from [表名] where 字段=值;

-- 多条件查询（多条件可使用括号包裹
select * from [表名] where 条件1 or (条件2 and 条件3);

-- 使用 in
select * from [表名] where 字段 in (值1,值2);

-- 子查询 （例1
select * from [表名] where 字段= (子查询);

-- 使用 case 判断语句
select *,
case 运算
	when 结果1 then 匹配值1
	when 结果2 then 匹配值2
	when 结果3 then 匹配值3
	else 默认匹配值
end 别名
from [表名];

3 模糊查询

使用 like 和 通配符完成模糊查询

通配符
%	表示0或多个字符
_	表示一个字符
[]	表示匹配范围
[^]	表示不匹配这个范围

-- 查询 name 中含有 '中‘的
select * from [表名] where name like '%中%';

-- 查询 name 中为 xx中 的
select * from [表名] where name like '%中';
select * from [表名] where name like '__中';

-- 查询 phone 第2位 7或8, 最后一位 不为 0和1
select * from [表名] where phone like '_[7,8]%[^0,1]';
select * from [表名] where phone like '_[7-8]%[^0-1]';

4 分组查询

使用 union 将多个查询结果拼接至一张表

select * from [表名] where name like '%中'
union
select * from [表名] where name like '__中';

使用union将多个sql查询的结果拼接到一张表（sql返回数据类型须一致

使用 group by 将查询结果按指定字段内容进行自动分组

select address, avg(age), max(age),conut(*) from table1
group by address;

5 多表查询

5.1 简单多表

select * from table1,table2; 
-- 【笛卡尔乘积】
-- 将tale1中的所有记录和table2中的所有记录依次排列组合成新的结果（数量为 table1，table2数据的乘积

-- 将两张表中的主键和外键进行对比，处理多余返回
select * from table1,table2 where table1.id = table2.id;

5.2 连接多表

-- inner join 内连
select * from table1
inner join table2 on table1.id and table2.id;
-- 效果和 select * from table1,table2 where table1.id = table2.id; 一样

两种多表查询的特点：

• 不符合主外键关系的数据不会被显示

如果使用inner join的两张表为同一张表，则为自连接

-- left join 左外连
-- 以左边表【table1】为主表进行数据全部显示，主外键找不到对应关系的数据用nul取代
select * from table1 
left join table2 on table1.id and table2.id;

-- right join 右外连
-- 以右边表【table2】为主表进行数据全部显示，主外键找不到对应关系的数据用nul取代
select * from table1 
right join table2 on table1.id and table2.id;

-- full join 全外连
-- table1，table2两张表的数据都全部显示 主外键找不到对应关系的数据用nul取代
select * from table1 
full join table2 on table1.id and table2.id;

6 分页查询

-- sql 2012 之前推荐
SELECT * FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY 排序字段) AS RowId,* FROM 表名 ) AS r 
WHERE  RowId  BETWEEN ((pageIndex-1)*pageSize + 1) AND (pageIndex * PageSize)


-- sql 2012 及之后推荐
SELECT * FROM 表名 order by 排序字段  offset (@pageIndex - 1) * @pageSize rows fetch next @pageIndex * @pageSize rows only

SELECT * FROM 表名 order by 排序字段,new_id  offset (@pageIndex - 1) * @pageSize rows fetch next @pageIndex * @pageSize rows only

order by的字段需为唯一，否则将影响分页的准确性

二、常用信息

2.1 常用数据类型

类型	描述
char(n)	字符串（定长，当存储数据小于n时，依旧占用n个字节
varchar(n)	字符串（变长，实际占用字节数由内容决定，最多占用n字节
text	长文本
int	整形
decimal(n,m)	整数有n位，小数有m位
datetime	日期（带时间
smalldatetime	日期（带时间，范围比datetime小
date	日期（无时间

char,varchar,test前面加n表示使用unicode字符存储，对中文友好

varchar(100): 存储100个字母或50个汉字

2.2 关键字

关键字
distinct	去除重复值
order by 字段 desc/asc	按某字段进行排序（desc：降序，asc：升序
top n	结果前 n 条数据
top n percent	结果数据的前 n%

2.3 运算符

• 算数运算符:
  • 加（+）、减(-)、乘（*）、除(/)、模（%)
• 逻辑运算符:
  • AND、OR、LIKE、BETWEEN、IN、EXISTS、NOT、ALL、ANY
• 赋值运算符:
  • =
• 字符串运算符:
• 比较运算符:
  • =、>、<、>=、<=、<>
• 位运算符:
  • |、&、^
• 复合运算符:
  • +=、-=、/=、%=、*=

2.4 聚合函数

函数
count(字段)	计数
max(字段)	最大值
min(字段)	最小值
sum(字段)	求和
avg(字段)	平均值

聚合函数不能在where中作为条件，需要使用having引用

select address,age from table1 where age> 18 having count(*) > 2;

2.5 常见约束

约束
primary key	设置主键
identity(n,m)	设置自增（n初始值，m增加值
default(n)	设置默认值为n
check(check code)	检查数据内容
not null	不能为null
references tableName(tablePrimaryKey)	引用其他表的主键，设置外键
unique	值不可重复

2.6 SQL server 函数

名称
getdate()	获取当前日期
len(字段)	获取字段值长度
year/month/day(时间值)	获取时间值的年份/月份/日期
substring(字段,n,m)	从字段值的第n 位开始 获取 m 个字符
round(值,n,m)	将数值保留n位小数，m为0表示四舍五入（默认为0
datediff(year/month/day,日期1,日期2)	返回两个日期相差的年月日
Couvert(转化的类型,数据) CONVERT(data_type(length),data_to_be_converted,style)	将数据转化为指定数据类型 通常用于转化日期
cast(数据,数据类型)	将数据转化为指定数据类型
all(sql语句) if 30 < all(select age from table)	将sql返回的所有数据拿去对比，需全部满足条件 对比所有age是否都小于30
any(sql语句) if 30 < any(select age from table)	将sql返回的所有数据拿去对比，只需部分满足条件 对比所有age是否有小于30的

三、进阶

3.1 变量

使用 print 和 select 均可对信息打印

-- 输出到消息中
print 'HEELO WORLD';
-- 输出到结果中
select 'hello world';

3.1.1 局部变量

@开头，先声明（declare），再赋值

declare @str1 varchar(20)

set @str1 = 'like new code'
select @str1 = 'like new code'
print @str1

-- set和select赋值的区别
-- set 赋值指定的值
-- select 赋值查询出的数据值赋值给变量，如果查询结果有多条，取最后一条赋值

3.1.2 全局变量

@@开头，由系统进行定义和维护

全局变量
@@ERROR	返回执行的上一个语句的错误号【0表示没有报错】
@@IDENTITY	返回最后插入的标识值
@@MAX_CONNECTIONS	返回运行同时进行的最大用户连接数
@@ROWCOUNT	返回受上一语句影响的行数
@@SERVERBANE	返回运行sql server的本地服务器名称
@@SERVICENAME	返回sql server其下正在运行的注册表名称
@@TRANCOUNT	返回氮气连接的活动事务数
@@LOCK_TIMEOUT	返回当前绘画的单签锁定超时设置（ms

3.2 go语句

• 等待go语句之前代码执行完成之后才能执行后面的代码

create database ttt1
go -- 等待前一句完成后再执行下一句
use ttt1

• 批处理结束的标志

declare @num int
set @num = 1
go -- @num 局部变量作用域到 go 结束
set @num = 2

3.3 流程控制

3.3.1 选择分支结构

1 if … else …

if 条件
	begin
		-- 满足条件时的sql
	end
else
	begin
		-- 不满足时的sql
	end

2 case

-- 使用 case 判断语句
-- 方式1
select *,
case 条件
	when 结果1 then 匹配值1
	when 结果2 then 匹配值2
	when 结果3 then 匹配值3
	else 默认匹配值
end 别名
from [表名];

-- 方式2
select *,
    case 
        when 条件 结果1 then 匹配值1
        when 条件 结果2 then 匹配值2
        when 条件 结果3 then 匹配值3
        else 默认匹配值
    end 别名
from [表名];

3.3.2 循环结构

-- while循环
declare @int int = 1;

while @int < 10
	begin 
		print @int 
		set @int = @int + 1;
    end

3.4 子查询

SELECT * FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY menuId) AS RowId,* FROM sys_menu ) AS r 

将子查询结果作为目标进行查询时，推荐添加别名

3.5 事务

-- 开启事务
begin transaction
declare @mySqlErr int = 0;
  -- 执行sql语句1
set @mySqlErr += @@ERROR
  -- 执行sql语句2
set @mySqlErr += @@ERROR
if @mySqlErr = 0 
	begin
		-- 提交事务
		commit transaction
	end
else
	begin
		-- 回滚事务
		rollback transaction
	end

3.6 索引

将指定列创建为索引列，通过查询该列提升查询效率（数据量大的时候推荐使用

3.6.1 sql server 索引类型

• 按存储结构区分

  • 聚集索引（聚类索引，簇集索引

    • 根据数据行的键值在表或视图中的排序存储这些数据行

    • 一张表中只能有一个聚集索引

    • 是对磁盘上实际数据按指定的一列或多列值排序（类似字典中的拼音索引

  • 非聚集索引（非聚类索引，非簇集索引

    • 具有独立于数据行的结构，包含非聚集索引键值，切每个键值项都有指向包含键值的数据行的指针

    • 可有多个

    • 类似字典中的部首索引（逻辑存储顺序

• 按唯一性区分

  • 唯一索引
  • 非唯一索引

• 按键列个数区分

  • 单列索引
  • 多列索引

3.6.2 创建索引

• 通过显示的create index命令手动创建
• 创建约束时作为隐含的对象
  • 主键约束（聚集索引
  • 唯一约束（唯一索引

-- clustered: 聚集索引  nonclustered：非聚集索引
create [unique] [clustered | nonclustered]
index 索引名 on 表名/视图名(列名 [asc|desc][...n])

3.6.3 索引查看

select * from sys.indexs where name = 索引名

3.6.4 删除索引

drop index 索引名 on 表名/视图名

3.6.5 查询

显示指定索引进行查询

select * from 表名 with(index = 索引名)
where 索引对应字段 = ‘xxx’

3.7 视图

将复杂的sql查询整合为视图，如果要进行对应查询，就不需要编写复杂的sql语句，直接查询对应视图即可

视图用于方便数据展示：可理解为虚拟表

创建视图

create view 视图名 as select ....
-- as 后跟对应的的查询sql

删除视图

drop view 视图名

3.8 游标

用于定位结果集中的某一行

3.8.1 游标分类

• 静态游标（static）
  • 操作游标时，数据发生变化，游标中的数据不变
• 动态游标（dynamic）
  • 操作游标时，数据发生变化，游标中的数据改变为对应数据类型的默认值
• 键集驱动游标（keyset）
  • 操作游标时，数据发生变化，被标识的列发生改变，改变其他列，游标中数据不变

3.8.2 创建游标

-- scroll 滚动游标，没有添加scroll，游标只能前进
declare 游标名 cursor scroll
for select 指定列名(可指定多个) from 表名

-- 打开游标
open 游标名

-- 提取数据
fetch first from 游标		-- 第一行
fetch first from 游标		-- 最后一行
fetch absolute 2 from 游标	-- 提取第二行
fetch relative from 游标		-- 当前行下移2行
fetch next from 游标		-- 下移1行
fetch prior from 游标		-- 上移一行

-- 提取游标数据存入
declare @变量 varchar(20)

fetch absolute 2 from 游标 into @变量 -- 存入变量
select * from 表名 where 字段 = @变量

-- 操作游标所在列的数据
delete from 表名 where current of 游标


-- 关闭游标（可再次打开
open 游标名

-- 删除游标
deallocate 游标名

3.8.3 遍历游标

declare @acc varchar(20)
fetch absolute 1 from 游标 into @acc
-- 全局变量 @@fetch_status: 0提取成功，-1失败，-2不存在
while @@fetch_status = 0
	begin
		print @acc
		fetch next from 游标 into @acc
	end

3.9 函数

分类

• 系统函数
• 自定义行数
  • 标量值函数（返回单个值
  • 表值函数（返回查询结果

自定义函数

-- 创建
create function 函数名(参数) returns 数据类型
as 
begin
	declare 变量
	-- 函数执行的sql
	return 变量 -- 将结果赋值给变量再返回
end

-- 执行
select 函数()

-- 删除函数
drop function 函数名


-- 如果需要返回查询结果
-- 方案一：指定返回结构
create function 函数名(参数) returns @myresult table
(
-- 返回的表结构
)
as 
begin
	insert into @myresult
	-- select 语句
	return
end

-- 方案二：返回查询结果（局限：函数体中只能有return + sql查询结果
create function 函数名(参数) returns table
as 
	return
	-- select 语句
go

3.10 触发器

触发器分类

• instead of
  • 执行操作之前触发
• after
  • 执行操作后触发

创建触发器

-- create trigger 触发器名 on 表名 触发器类型 操作 as sql语句
create trigger my_trigger on TestTable after insert
as 
if exists(select * from TestTable where id = 1)
begin
print 123
end


-- 删除触发器
drop trigger 触发器名

3.11 存储过程

存储过程是SQL语句和流程控制语句的预编译集合

sql函数只能sql中调用

存储过程可以被外部程序调用（如Java

创建存储过程

-- 没有输入参数的，没有输出参数的存储过程
create proc 存储过程名 as sql语句

-- 执行
exec 存储过程
 
-- 删除 
drop proc

-- 有输入参数的，没有输出参数的存储过程
create proc 存储过程名
@参数 数据类型
as
sql语句

-- 执行
exec 存储过程 参数

-- 有输入参数，有输出参数的存储过程
create proc 存储
@输入参数 类型,
@输出参数 类型 output
as
sql语句(输出参数使用 select @输出参数 进行设置)

-- 执行
exec 存储 输入参数, @输出参数 output