sql server知识备忘录

一、库、表操作

0、类型

（0）关键字

如果表名或键名为sql关键字, 比如name, rank等, 需要使用方括号[name] [rank]

（1）字符

char(n) 定长字符, 无论存储数据是否真的到了n个字节, 都要占用n个字节
varchar(n) 变长字符, 最多占用n个字节, 如果有m个字节, 且m<n, 就真实占用m个字节
text 长文本, 比如有几千几万字, 效率不高
类型前面加n 存储unicode字符, 对中文友好, 1中文2字节
- varchar(100)能存100英文50中文
- nvarchar(100)能存100英文100中文

（2）时间

date 存储年月日
datetime 存储年月日时分秒
smalldatetime 存储年月日时分秒, 但范围比datetime更小

（3）小数

float
decimal(m, n) 总长度为m, 小数位数为n
money 钱

1、创建数据库

ON 数据文件
LOG ON 日志文件

-- 创建前删库慎用！
IF EXISTS(select * from sys.databases where name = 'DBTST')
    DROP DATABASE DBTEST

CREATE DATABASE [DBTEST]
 CONTAINMENT = NONE
 ON  PRIMARY -- 数据文件
(   -- 逻辑名称
    NAME = N'DBTEST', 
    -- 物理路径和名称
    FILENAME = N'D:\SQL Server\SQL  Server19\MSSQL15.MSSQLSERVER\MSSQL\DATA\DBTEST.mdf' ,
    -- 文件初始大小, 最大大小
    SIZE = 8192KB , 
    MAXSIZE = UNLIMITED, 
    -- 增长方式可以写大小, 也可以写百分比
    FILEGROWTH = 65536KB 
)
 LOG ON -- 日志文件
(   -- 逻辑名称\物理名称:"习惯直接在数据文件名后加_log"
    NAME = N'DBTEST_log', 
    FILENAME = N'D:\SQL Server\SQL Server19\MSSQL15.MSSQLSERVER\MSSQL\DATA\DBTEST_log.ldf' , 
    SIZE = 8192KB , 
    MAXSIZE = 2048GB , 
    FILEGROWTH = 65536KB 
)
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

2、创建表

primary key主键
identity(n,m)自动增长, 初始值n, 增长值m
default(值) 默认为值
check(字段1 = 值1 or/and 字段2 = 值2) 对某个字段进行规定, 如果对数据进行添加修改的时候, 必须遵守规定
unique 唯一约束
references 关联表(外键) 引用外键, 将关联表的键作为当前键

-- 切换数据库
use DBTEST;
-- 创建表
create table 表名
(
    键 类型(长度) 
    primary key identity(初始值,增长值) 
    unique
    default(值)
    check(字段1 = 值1 or / and 字段2 = 值2) 
    not null,
)

（1）例子 -- 建表(部门, 职级, 员工)

部门
- 部门编号
  - primary key主键
  - identity(1,1)自动增长, 初始值1, 增长值1
- 部门名称
- 部门描述

-- 判断表是否存在 type = U 代表用户自定义的表, 如果有就删了
if exists(select * from from sys.objects where name = 'Department' and type = 'U')
    drop table Department
     
create table Department
(
    -- 部门编号, primary key: 主键, `identity(n,m)`自动增长, 初始值n, 增长值m
    DepartmentId int primary key identity(1,1),
    -- 部门名称, 字符串(长度50) 非空
    DepartmentName nvarchar(50) not null,
    -- 部门描述, 内容比较多的字符
    DepartmentRemark text
)

职级
- 职级编号
- 职级名称
- 职级描述

if exists(select * from from sys.objects where name = 'Rank' and type = 'U')
    drop table Rank
     
create table Rank
(
    RankId int primary key identity(1,1),
    RankName nvarchar(50) not null,
    RankRemark text
)

员工
- 员工编号
- 部门编号, 不能超过已有的编号
- 职级编号, 不能超过已有的编号
- 员工名
- 性别, 男女单选
- 出生日期
- 薪水, 1000 - 1000000
- 电话
- 地址
- 添加时间, 自动记录当前时间

if exists(select * from sys.objects where type = 'U' and name = 'People')
    drop table People
create table People
(
    PeopleId int primary key identity(1,1),
    DepartmentId int references Department(DepartmentId) not null,
    RankId int references Rank(RankId) not null,
    PeopleName varchar(50) not null,
    PeopleSex nvarchar(1) default('男') check(PeopleSex = '男' or PeopleSex = '女') not null,
    PeopleBirth smalldatetime not null,
    PeopleSalary decimal(12,2) check(PeopleSalary >= 1000 and <=1000000) not null,
    PeoplePhone varchar(20) unique not null,
    PeopleAddress varchar(300),
    -- 自动记录当前时间
    PeopleAddTime smalldatetime default(getdate())
)

3、修改表结构

（1）添加列

alter table 表名 add 新列名 数据类型

（2）修改列

如果该列已有数据, 修改长度, 修改数据类型都会报错

alter table 表名 alter column 列名 数据类型

（3）删除列

alter table 表名 drop column 列名

4、维护约束

（1）删除约束

不是专业运维, 尽量别操作, 约束名要去sys表里查询, 约束名称是系统自动生成的

alter table 表名 drop constraint 系统自动生成的约束名

（2）添加约束（check）

alter table 表名 add constraint 系统自动生成的约束名 check(约束表达式)
-- 添加工资字段约束, 工资必须在1000-100000
alter table Prople add constraint 约束名 check(PeopleSalary >= 1000 and <=1000000)

（3）添加约束（主键）

alter table 表名 add constraint 系统自动生成的约束名 primary key(列名)

（3）添加约束（唯一）

alter table 表名 add constraint 系统自动生成的约束名 unique(列名)

（4）添加约束（默认值）

alter table 表名 add constraint 系统自动生成的约束名 default 默认值 for 列名

（5）添加外键约束

alter table 表名 add constraint 系统自动生成的约束名 foreign key(列名) references 关联表(列名:通常是主键)

二、表的增删改

1、插入一条数据

-- 标准写法
insert into 表名 (键1, 键2, 键3) values (值1, '值2', 值3)
-- 缩略写法, values列出所有非自增列, 最好别简写
insert into 表名 values (值1, '值2', 值3)

（1）插入部门记录

insert into Department ('DepartmentName', 'DepartmentRemark') values ('软件部', '无备注')
-- 或者
insert into Department values ('软件部', '无备注')

2、一次性插入多条数据

insert into 表名(键1, 键2)
select '值1-1','值1-2' union
select '值2-1','值2-2' union
select '值3-1','值3-2'

（1）插入多个部门

insert into Department('DepartmentName', 'DepartmentRemark')
select '软件部','无备注' union
select '硬件部','无备注' union
select '运维部','无备注'

（2）插入多个职级

insert into Department('RankName', 'RankRemark')
select '初级','无备注' union
select '中级','无备注' union
select '高级','无备注'

（3）插入多个人

insert into People('DepartmentId', 'RankId', 'PeopleName', 'PeopleSex', 'PeopleBirth', 'PeopleSalary', 'PeoplePhone', 'PeopleAddress')
select 1, 1, '刘备', '男', '2000-01-01', '19819882000', '沛县' union
select 2, 1, '刘禅', '非', '2000-01-02', '19819882001', '沛县'

3、修改

update 表名 set 键1 = 值1, 键2 = 值2 where 条件

（1）工资调整, 每个人加薪1000

update People set PeopleSalary = PeopleSalary + 1000

（2）为某个人加薪1000, 编号为7

update People set PeopleSalary = PeopleSalary + 1000 where PeopleId = 7

（3）将部门编号为1, 工资低于15000的调整为15000

update Pepole set PeopleSalary = 15000 where DepartmentId = 1 and PeopleSalary < 15000

（4）将人员编号为1的人, 工资调整为以前两倍, 并且把地址修改为北京

update People set PeopleSalary = PeopleSalary * 2, PeopleAddress = '北京' where PeopleID = 1

4、删除记录

delete from 表名 where 条件

（1）删除部门为3的部门且工资大于10000的

delete from People where DepartMentId = 3 and PeopleSalary > 10000

5、截断表清空数据

truncate
- 清空所有数据, 不能有条件
- 自增编号从头开始计数
delete
- 可以删除所有数据, 也可以带条件删除所需数据
- 删除的自增编号将永远不存在

truncate table 表名

三、简单查询

0、最基本查询

-- 查询所有行所有列
select * from 表名

1、查询指定列

select 键1 as 别名1, 键2 as 别名2, 键3 as 别名3 from 表名

2、去重查询

select distinct(键1) from 表

四、条件查询

0、不太一样的运算符

运算符	用法
`in`	是否在其中
`not`	逻辑非(条件成立, 表达不成立, 条件不成立, 表达式成立)

1、关于时间条件的查询

select 键1, 键2 from 表 where 关于时间的键 >= '某个时间/日期'
select 键1, 键2 from 表 where 关于时间的键 <= '某个时间/日期'

2、排序

（1）简单排序

select 键1 from 表 order by 键2 asc -- 升序 默认升序  
select 键1 from 表 order by 键2 desc -- 降序

（2）带函数排序

select 键1 from 表 order by len(键2) -- 根据键2的长度排序

（3）取前N个 / 前n%个

select top N 键 from 表 order by 键
select top n percent 键 from 表 order by 键

（4）查询为某个值为空/不为空的

赋值的时候不添加就为空
赋值的时候添加空字符串不是空

select 键 from 表 where 键 is null
select 键 from 表 where 键 is not null
-- 空字符串
select 键 from 表 where 键 = ''

（5）获取时间的年份 / 月份

select 键 from 表 where year(时间键) = XXXX年
select 键 from 表 where month(时间键) = XXXX月
-- 获取今年 / 今月
select 键 from 表 where year(getdate()) = XXXX年
select 键 from 表 where month(时间键) = XXXX月

（6）case when查询每个人的生肖

-- 查询每个人的生肖
select * 
case 
	when year(People) % 12 = 4 then '鼠'
	when year(People) % 12 = 5 then '牛'
	when year(People) % 12 = 6 then '虎'
	when year(People) % 12 = 7 then '兔'
	when year(People) % 12 = 8 then '龙'
	when year(People) % 12 = 9 then '蛇'
	when year(People) % 12 = 10 then '马'
	when year(People) % 12 = 11 then '羊'
	when year(People) % 12 = 0 then '猴'
	when year(People) % 12 = 1 then '鸡'
	when year(People) % 12 = 2 then '狗'
	when year(People) % 12 = 3 then '猪'

	else ''
end
from People
-- 查询每个人的生肖
select * 
case year(People) % 12
	when 4 then '鼠'
	when 5 then '牛'
	when 6 then '虎'
	when 7 then '兔'
	when 8 then '龙'
	when 9 then '蛇'
	when 10 then '马'
	when 11 then '羊'
	when 0 then '猴'
	when 1 then '鸡'
	when 2 then '狗'
	when 3 then '猪'

	else ''
end
from People

五、模糊查询

使用like和如下通配符结合实现

通配符	用法
`_`	代表匹配有且只有1个字符
`%`	代表匹配0个、1个字符、或多个字符
`[]`	匹配在括号范围内的
`[^]`	代表匹配不在括号范围内的

有时候也等价与SUBSTRING(字符串, 第几位开始取, 取几个字), 从1开始计数
[m, n, o] 匹配这一位有可能是m、n、o的
[m-n] 匹配这一位有可能是m~n的
[^m, n, o] 匹配这一位不可能是m、n、o的
[^m-n] 匹配这一位不可能是m~n的

例子

-- 查询姓刘的
select from People where PeopleName like '刘%'
-- 查询出名字中含有尚的
select from People where PeopleName like '%刘%'
-- 查询出名字含有尚或者史的
select from People where PeopleName like '%尚%' or PeopleName like '%史%'
-- 查询出姓刘但名字两个字 如下俩相等
select from People where PeopleName like '刘_'
select from People where SUBSTRING(PeopleName, 1, 1) = '刘_' and len(PeopleName) = 2
-- 查询出名字最后一个字为香 名字有三个字的
select from People where PeopleName like '__香'
select from People where SUBSTRING(PeopleName , 3, 1) = '香_' and len(PeopleName) = 3
-- 查询出电话号码为138的第4位可能是7或者8, 最后一位为5
select from People where PeoplePhone like '138[7, 8]%5'
-- 查询出电话号码为138的第4位是 2~5 , 最后一个号码不是2或3
select from People where PeoplePhone like '138[2, 3, 4, 5]%[^2, 3]'

六、聚合函数

函数	用法
`count`	总数
`max`	最大
`min`	最小
`sum`	求和
`avg`	平均值
`round(待处理数字, 位数)`	保留位数
`datediff(标准<year/month/day>, 时间1, 时间2)`	按标准计算时间2-时间1

1、例子

（1）求员工总数

select count(PeopleId) TotalPeople from People

（2）求最高工资

select max(PeopleSalary) HighestSalary from People

（3）求最小工资

select min(PeopleSalary) LowestSalary from People

（4）求和, 求所有员工的工资总和

select sum(PeopleSalary) TotalSalary from People

（5）求平均值, 求有员工的平均工资, 保留2位小数

select round(avg(PeopleSalary, 2) AverageSalary from People

（6）查询出武汉地区的员工总数, 求总工资, 最高工资, 最低工资, 平均工资

select 
	count(PeopleId) TotalPeople, 
	sum(PeopleSalary) TotalSalary, 
	select max(PeopleSalary) HighestSalary, 
	min(PeopleSalary) LowestSalary, 
	round(avg(PeopleSalary, 2) AverageSalary
from People
where PeopleAddress = '武汉'

（7）求出工资比平均工资高的人

select *
from People
where 
	PeopleSalary > (select round(avg(PeopleSalary), 2) as AverageSalary from People)

（8）求数量, 年龄最大, 年龄最小, 年龄平均值

select count(PeopleId) TotalPeople, 
	max(DATEDIFF(year, PeopleBirth, getdate())) MaxAge, 
	min(DATEDIFF(year, PeopleBirth, getdate())) MinAge, 	
	round(avg(DATEDIFF(year, PeopleBirth, getdate())),2)
from People

（9）计算出月薪在10000以上的男性员工的最大年龄, 最小年龄和平均值

select 
	max(year(getdate()) - year(PeopleBirth)), 
	min(year(getdate()) - year(PeopleBirth)), 
	avg(year(getdate()) - year(PeopleBirth))
from People
where PeopleSalary > 10000 and PeopleSax = '男'

（10）求年龄比平均年龄高的人员信息

select *
from People
where PeopleAge > (select avg(DATEDIFF(year, PeopleBirth, getdate()))) from People)

七、分组查询

分组查询通常与聚合函数一起用
分组后每一列不是代表一个记录, 而是代表一个地区的所有记录
group by在where前边
having表示以聚合函数的结果作为条件, 在group by后边, 只有经过分组之后, 才能使用聚合函数的结果作为条件

（1）根据员工所在地区分组统计员工人数, 员工工资总和, 平均工资, 最高和最低工资

select
	PeopleAddress 所在地区,
	count(*) 人数总和,
	sum(PeopleSalary) 工资总和,
	round(avg(PeopleSalary), 2) 平均工资,
	max(PeopleSalary) 最高工资,
	min(PeopleSalary) 最低工资,
from People
group by PeopleAddress

（2）根据员工所在地区统计员工人数, 员工工资总和, 平均工资, 最高和最低工资, 1985年及之后的不计入

select
	PeopleAddress 所在地区,
	count(*) 人数总和,
	sum(PeopleSalary) 工资总和,
	round(avg(PeopleSalary), 2) 平均工资,
	max(PeopleSalary) 最高工资,
	min(PeopleSalary) 最低工资,
from People
where year(PeopleBirth) < '1985'
group by PeopleAddress

（3）根据员工所在地区统计员工人数, 员工工资总和, 平均工资, 最高和最低工资, 1985年及之后的不计入, 低于1人的区域不计入

select
	PeopleAddress 所在地区,
	count(*) 人数总和,
	sum(PeopleSalary) 工资总和,
	round(avg(PeopleSalary), 2) 平均工资,
	max(PeopleSalary) 最高工资,
	min(PeopleSalary) 最低工资,
from People
where year(PeopleBirth) < '1985'
group by PeopleAddress
having count(*) >= 2

posted on 2023-08-16 00:40 老菜农阅读(14) 评论(0) 编辑收藏举报

刷新页面返回顶部

sql server知识备忘录

一、库、表操作

0、类型

（0）关键字

（1）字符

（2）时间

（3）小数

1、创建数据库

2、创建表

（1）例子 -- 建表(部门, 职级, 员工)

3、修改表结构

（1）添加列

（2）修改列

（3）删除列

4、维护约束

（1）删除约束

（2）添加约束（check）

（3）添加约束（主键）

（3）添加约束（唯一）

（4）添加约束（默认值）

（5）添加外键约束

二、表的增删改

1、插入一条数据

（1）插入部门记录

2、一次性插入多条数据

（1）插入多个部门

（2）插入多个职级

（3）插入多个人

3、修改

（1）工资调整, 每个人加薪1000

（2）为某个人加薪1000, 编号为7

（3）将部门编号为1, 工资低于15000的调整为15000

（4）将人员编号为1的人, 工资调整为以前两倍, 并且把地址修改为北京

4、删除记录

（1）删除部门为3的部门且工资大于10000的

5、截断表 清空数据

三、简单查询

0、最基本查询

1、查询指定列

2、去重查询

四、条件查询

0、不太一样的运算符

1、关于时间条件的查询

2、排序

（1）简单排序

（2）带函数排序

（3）取前N个 / 前n%个

（4）查询为某个值为空/不为空的

（5）获取时间的年份 / 月份

（6）case when查询每个人的生肖

五、模糊查询

例子

六、聚合函数

1、例子

（1）求员工总数

（2）求最高工资

（3）求最小工资

（4）求和, 求所有员工的工资总和

（5）求平均值, 求有员工的平均工资, 保留2位小数

（6）查询出武汉地区的员工总数, 求总工资, 最高工资, 最低工资, 平均工资

（7）求出工资比平均工资高的人

（8）求数量, 年龄最大, 年龄最小, 年龄平均值

（9）计算出月薪在10000以上的男性员工的最大年龄, 最小年龄和平均值

（10）求年龄比平均年龄高的人员信息

七、分组查询

（1）根据员工所在地区分组统计员工人数, 员工工资总和, 平均工资, 最高和最低工资

（2）根据员工所在地区统计员工人数, 员工工资总和, 平均工资, 最高和最低工资, 1985年及之后的不计入

（3）根据员工所在地区统计员工人数, 员工工资总和, 平均工资, 最高和最低工资, 1985年及之后的不计入, 低于1人的区域不计入

导航

公告

5、截断表清空数据