SQL面试必备小册子

以下是 SQL 语句的基础，刷 Leecode、牛客网之前可以先学习一下。

一、建库

drop database DBTEST--所建库的名称不能重复
--创建数据库
create database DBTEST
ON --数据文件
(
	name = 'DBTEST',--逻辑名称
	filename = 'D:\DATA\DBTEST.mdf',--物理路径和名称
	size = 5MB,--文件的初始大小
	filegrowth = 2MB--文件的增长方式可以写大小，也可以写百分比
)
log on ==日志文件
(	
	name = 'DBTEST_log',--逻辑名称
	filename = 'D:\DATA\DBTEST_log.ldf',--物理路径和名称
	size = 5MB,--文件的初始大小
	filegrowth = 2MB,--文件增长方式可以写大小，也可以写百分比
)

简单方式可以写成

create database DBTEST

二、建表

一般规则

--切换数据库
use DBTEST
--创建表基本语法
--create table 表名
--(
--	字段名1 数据类型，
--	字段名2 数据类型
--)

实例：建部门表、职级表、员工表

--判断表存在
if exists(select * from sys.objective where name='Department' and tyoe='U')
	drop table Department
create table Department
(
	--部门编号,primary key--主键,identity(1,1)自动增长，初始值1，增长步长1
	Departmentid int primary key identity(1,1),
	--部门名称
	DepartmentName nvarchar(50) not null,
	--部门描述
	DEpartmentRemark text
)
--char定长,char(10),无论存储的数据是否真的到了10个字节，都要占用10个字节
--char(10)存储’ab‘仍然占有10个字符，varchar(10),占用2个字节
--varchar:变长,varchar(10),最多占用10个字节
--text长文本
--char,varchar,text前面加n：存储Unicode字符，对中文友好
--varchar(100):存储100个字母或者50个汉字
--nvarchar(100):存储100个字母或者100个汉字


create table [Rank]
(
	--职级编号，primary key：主键，identity(1,1):自动增长1，增长步长1
	RankId int primary key identity(1,1),
	--职级名称
	Rankname nnvarchar(10) not null,
	--职级描述
	RankRemark text
)

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 nvarchar(50) not null,--姓名
	PeopleSex nvarchar(1) default('男') check (PeopleSex='男' or PeopleSex = '女') not null,--性别
	PeopleBirth smalldatetime not null,--生日
	--最近年smalldatetime
	PeopleSalary decimal(12,2) check (PeopleSalary >=1000 and PeopleSalary<=1000000) not null,--月薪
	--12总长度，2位小数
	PeoplePhone varchar(20) unique not null,--电话
	--unique 每个员工的电话是唯一的
	PeopleAdress varchar(300),--地址
	PeopleAddTime smalldatetime default(getdate)--添加时间
)

三、修改表结构

1. 添加列
2. 删除列
3. 修改列

--(1)添加列
--alter table 表名 add 新列名 数据类型
--给员工表添加一列邮箱
alter table People add PeopleMall varchar(200)

--删除列
--alter table 表名 deop column 列名
-- 删除邮箱这一列
alter table People drop column PeopleMall

--（3）修改列
-- alter table 表名 alter column 列名 数据类型
-- 修改地址varchar(300)位varchar（200）
alter table People aletr column PeopleAdress varchar(200)

维护约束（删除，添加）

--删除约束
-- alter table 表名 drop constraint 约束名
--删除月薪的约束
alter table People drop constraint CK_People_PeopleSa_34C8D9D1

--添加约束（check约束）
--alter table 表名 add constraint 约束名 check (表达式)
--添加工资字段约束，工资必须在1000-1000000之间ai
alter table People add constraint CK_PeopleSa1
check (PeopleSalry>=1000 and PeopleSalary<=1000000)

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

四、插入数据

--向部门表插入数据
insert into Department(DepartmentName,DepartmentReamark)
values ('市场部','......')
insert into Department(DepartmentName,DepartmentReamark)
values ('软件部','......')
insert into Department(DepartmentName,DepartmentReamark)
values ('企划部','......')
--简写
insert into Department values('市场部'，'......')-最好不要
--一次插入多行数据
insert into Department(DepartmentName,DepartmentReamark)
select '测试部','......' union
select '实施部','......' union
select '产品部','......'

--向职级表插入数据
insert into [Rank](RankName,RankRemark)
values('初级','......')
insert into [Rank](RankName,RankRemark)
values('中级','......')
insert into [Rank](RankName,RankRemark)
values('高级','......')

--向员工表插入数据
insert into People(DepartmentId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAdress,PeopleAddTime)
values(8,1,'刘备','男','1988-8-8',5000,13888888888,'中国',getdate())

五、修改和删除数据

--修改
--update 表名 set 字段1=值1, 字段2=值2 where 条件
--工资调整，每个人加薪1000元
update People set PeopleSalary = PeopleSalary + 1000
--将员工编号为7的人加薪500元
update People set PeopleSalary = PeopleSalary + 500
where PeopleId =7
--将软件部（部门编号1）人员工资低于15000的调整成15000
update People set PeopleSalary = 10000
where DepartmentId = 1 and PeopleSalary<10000

--删除
--delete from 表名 where 条件
--删除员工所有记录
delete from People 
--删除市场部（部门编号3）中工资大于10000的人
delete from People where DepartmentId =3 and PeopleSalry >1000
--关于删除（drop,truncate,delete）
drop table People--删除表对象
truncate table People --清空数据，表结构还存在
delete table People--清空数据，表结构还在
--truncate和delete的区别
--truncate清空所有记录，不能由条件
--delete 可以删除所有数据，也可以删除符合条件的数据
--自动编号
--假设表中自动编号为1，2，3，4，5
--使用truncate清空数据之后添加的数据，编号仍然是1，2，3，4，5
--使用delete删除数据，删除的自动编号将永远不存在了
--如果使用delte删除了所有数据之后再添加数据，编号变成了6，7，8，9，10

六、基础查询

--查询所有列所有行
select * from Department
select * from [Rank]
select * from People

--查询出生年月在1980-1-1之后，而且月薪大于10000的女员工
select *from People where PeopleBirth >= '1980-1-1' and 
(PeopleSalary >=10000 and PeopleSex = '女')

--查询出地址在武汉或广州或北京的员工信息
select * from People where PeopleAdress  = '武汉' or PeopleAdress = '北京'
select * from People where PeopleAdress in ('北京','武汉')

--查询所有员工信息，根据名字长度排序（降序）
select * from People order by len(PeopleName) desc

--查询出工资最高的前5个人的信息
select top 5 * from People order by PeopleSalary desc

--查询处地址没有填写好（已经填写好）的员工信息
select * from People where PeopleAdress is null
select * from People where PeopleAdress is not null

--查询30-40岁之间，并且工资在15000-30000之间的员工信息
--假设年龄 = 当前年份-生日年份
select * from People where 
(year(getdate())-year(PeopleBirth)>=30 and year(getdate())-year(PeopleBirth)<=40)
and (PeopleSalary between 15000 and 30000)

--查询出星座巨蟹座的员工信息（6.22-7.22）
select * from Peole where
(month(PeopleBirth) = 6 and day(PeopleBirth)>=22)
or
(month(PeopleBirth) = 7 and day(PeopleBirth)<=22) 

--查询出生生肖是鼠的人员信息
--鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
--4   5   6  7   8   9  10  11  0  1   2  3
select * from People where year(PeopleBirth)% 12 =4
--查询所有员工信息，添加一列，显示生肖
select * 
case 
	when year(PeopleBirth)% 12 =4 then '鼠'
	when year(PeopleBirth)% 12 =5 then '牛'
	when year(PeopleBirth)% 12 =6 then '虎'
	when year(PeopleBirth)% 12 =7 then '兔'
	when year(PeopleBirth)% 12 =8 then '龙'
	when year(PeopleBirth)% 12 =9 then '蛇'
	when year(PeopleBirth)% 12 =10 then '马'
	when year(PeopleBirth)% 12 =11 then '羊'
	when year(PeopleBirth)% 12 =0 then '猴'
	when year(PeopleBirth)% 12 =1 then '鸡'
	when year(PeopleBirth)% 12 =2 then '狗'
	when year(PeopleBirth)% 12 =2 then '猪'
	else ""
end 生肖
from People
--简化
select * 
case 
	when year(PeopleBirth)% 12 =4 then '鼠'
	when year(PeopleBirth)% 12 =5 then '牛'
	when year(PeopleBirth)% 12 =6 then '虎'
	when year(PeopleBirth)% 12 =7 then '兔'
	when year(PeopleBirth)% 12 =8 then '龙'
	when year(PeopleBirth)% 12 =9 then '蛇'
	when year(PeopleBirth)% 12 =10 then '马'
	when year(PeopleBirth)% 12 =11 then '羊'
	when year(PeopleBirth)% 12 =0 then '猴'
	when year(PeopleBirth)% 12 =1 then '鸡'
	when year(PeopleBirth)% 12 =2 then '狗'
	when year(PeopleBirth)% 12 =2 then '猪'
	else ""
end 生肖
from People

select * 
case year(PeopleBirth)% 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

七、模糊查询

--%： 代表匹配0个字符、1个字符或多个字符
--_:  代表匹配有且只有1个字符
--[]: 代表匹配范围内
--[^]: 代表匹配不在范围内的

--查询姓刘的员工信息
select * from People where PeoplName like '刘%'
--查询名字中含有“尚”的员工信息
select * from People where PeoplName like '%尚%'
--显示名字中含有“尚”或者“史”的员工信息
select * from People where PeoplName like '%尚%' or PeopleName like '%陈%'

--查询出姓刘的员工信息，名字是2个字,substring(字符,起始位置,长度)
select * from People where PeopleName like '刘_'
select * from People where substring(PeopleName,1,1)='刘'
and len(PeopleName)=2

--查询出姓名最后一个字是“香”的员工信息，名字是3个字,substring(字符,起始位置,长度)
select * from People where PeopleName like '_香'
select * from People where substring(PeopleName,3,1)='香'
and len(PeopleName)=3

--查询出电话号码开头为138的员工信息
select * from People where PeoplePhine like '138%'

--查询出电话号码开头为138的,第四位好像是7或者8，最后一个号码是5
select * from People where PeoplePhine like '138[7,8]%5'


--查询出电话号码开头为138的,第四位好像是2-5之间，最后一个号码不是2和3
select * from People where PeoplePhine like '138[2,3,4,5]%[^2,3]'

八、聚合函数

--count：求数量
--max:求最大值
--min: 求最小值
--sum: 求和
--avg: 求平均值
-datediff(单位，时间1，时间2)
select datediff(year,'1991-1-1','1993-3-3')

--统计出所在地在广州或上海的所有女员工数量以及最大年龄
select count(*) 数量,max(year(getdate())-year(PeopleBirth)) 最高年龄
from People 
where PeopleSex = '女' and PeopleAdress in ('上海','广州')

九、分组查询

--根据员工所在地区分组统计员工人数，平均工资，且1985年以后出生的员工不参与统计
select PeopleAdress 地区, count(*) 员工人数, avg(PeopleSalary)
from People
where PeopleBirth < '1985-1-1'
group by PeopleAdress

--根据员工所在地区分组统计员工人数，平均工资，且1985年以后出生的员工不参与统计,要求筛选出员工人数至少在2人以上的记录
select PeopleAdress 地区, count(*) 员工人数, avg(PeopleSalary)
from People
where PeopleBirth < '1985-1-1'
group by PeopleAdress
having count(*) >=2

十、多表查询

内连接（inner join）：不符合主外键关系的不会被显示
外连接（左连接、右连接、全外连接）
左外连接(left join)：以左表为主表进行数据显示，主外键找不到的数据NULL取代
右连(right join)： A left join B = B right joint A
全外联 (outer join)：无论是否符合关系，都要显示
自连接：自己连自己

create table Dept
(
	DeptId int Primary key,--部门编号
	DeptName varchar(50),--部门名称
	ParentId int,--上级名称	
)
--一级
insert into Dept(DeptId,DeptName,ParentId) values(1,'软件部',0)
insert into Dept(DeptId,DeptName,ParentId) values(1,'硬件部',0)
--二级
insert into Dept(DeptId,DeptName,ParentId) values(3,'软件研发部',1)
insert into Dept(DeptId,DeptName,ParentId) values(4,'软件测试部',1)
insert into Dept(DeptId,DeptName,ParentId) values(5,'软件实施部',1)
insert into Dept(DeptId,DeptName,ParentId) values(6,'硬件研发部',2)
insert into Dept(DeptId,DeptName,ParentId) values(7,'硬件测试部',2)
insert into Dept(DeptId,DeptName,ParentId) values(8,'硬件实施部',2)

select * from Dept
--部门编号 部门名称 上级部门
--3     软件研发部  软件部
--4     软件测试部  软件部

select * from Dept A
inner join Dept B
on A.DeptId = B.ParentId