1.基础操作

1.1创建数据库

create database DB --创建数据库
on --数据文件
(
    name='DB', --逻辑名称
    filename='D:\SQL\R\MSSQL16.MSSQLSERVER\MSSQL\DATA\DB.mdf', --物理路径和名称
    size=5MB, --文件的初始大小
    filegrowth=2MB --文件的增长方式(可以写大小,也可以写百分比
)
--注意 : 别忘了逗号(英文的)
log on --日志文件
(
    name='DB_log', --逻辑名称
    filename='D:\SQL\R\MSSQL16.MSSQLSERVER\MSSQL\DATA\DB_log.ldf', --物理路径和名称
    size=5MB, --文件的初始大小
    filegrowth=2MB --文件的增长方式(可以写大小,也可以写百分比)
)

drop database DB   --删除数据库DB

1.2创建数据表

use DB --切换数据库

create table DB_Student--创建数据表
(
    StudentID int primary key identity(1,1),
    --规定学号作为主键,数据类型为整型,自动增长(初始值为1,每次增长1)
    StudentName nvarchar(20)not null, --规定姓名,数据类型为字符串,长度最大20且不能为空
    StudentRemark text --规定简介，数据类型为长文本
    StudentSex nvarchar(1) default('男')check(StudentSex='男' or StudentSex='女')
    --规定性别,数据类型为字符串,长度为1且只能写男或女,不写则默认为男
)

drop table DB_Student --删除数据表DB_Student

常见字符类型
1.char : char(10) --无论是否存储,都占用固定空间10
2.varchar : varchar(10) --存储多少,占用多少,最多占用空间10
3.text : 长文本
在char,varchar,text前加n : 变成存储unicode字符,这时字母和汉字都只占一个空间

date类型 : 存储年月日
datetime类型 : 存储年月日,时分秒
smalldatetime类型 : 范围变小,但查询效率变高
decimal类型: decimal(10,2) --总长度为10且保留两位小数
unique关键字 : 数据唯一约束
getdate() : 取到当前的时间
year() : 取年份
month() : 取月份
day() : 取日期
len() : 取长度
references : 引用外键(将其他表的主键作为列)
cast('123'as int) : 把字符串'123'转换为整数型
into : 将查询结果插入到另一个表
--如StudentID int references DB_Student(StudentID)

SQL Server有float,但是没有double

1.3 修改表结构和约束的维护

alter table DB_Student add 新列名 数据类型
--给数据表DB_Student添加一列
alter table DB_Student drop column 列名 
--给数据表DB_Student删除一列
alter table DB_Student alter column 列名 数据类型
--给数据表DB_Student修改一列

alter table DB_Student drop constraint 约束名
--给数据表DB_Student删除一个约束
alter table DB_Student add constraint 约束名 cheak(表达式)
--给数据表DB_Student添加一个cheak约束
alter table DB_Student add constraint 约束名 primary(列名)
--给数据表DB_Student添加一个主键约束
alter table DB_Student add constraint 约束名 default 默认值 for 列名
--给数据表DB_Student添加一个默认值约束
alter table DB_Student add constraint 约束名 foreign key(列名) references 关联表名(主键)
--给数据表DB_Student添加一个外键

1.4 插入数据

一次插入一行数据 ： 
insert into 表名(列名1,列名2)
values('数据1','数据2')
如 : 
insert into DB_Student(StudentID,StudentName)
values('123456','张三')
一次插入多行数据 : 
insert into 表名(列名1,列名2)
select '数据1','数据2' union
select '数据1','数据2'
如 ： 
insert into DB_Student(StudentID,StudentName)
select '123456','张三' union
select '456789','李四'

1.5 修改和删除数据

修改数据 : 
update 表名 set 列1=值1,列2=值2 where 条件
如 : 将软件部(部门编号为1)人员工资低于10000的调整成10000,户口改成北京
update People set PeopleSalary=10000,Address='北京'
where DepartmentID=1 and PeopleSalary<10000
--and=&&,or=||
删除数据 : 
delete from 表名 where 条件
如 : 删除软件部(部门编号为1)人员工资小于10000的
delete from People where DepartmentID=1 and PeopleSalary<10000

常见删除函数
1.drop table DB_Student --删除表对象(连表结构都没了)
2.truncate table DB_Student --删除表数据(清空,表结构还在,数据没了)
3.delete table DB_Student --删除表数据(有选择的删除,表结构还在,数据没了)
delete和truncate区别
自动编号中 : 
假设表中自动编号为1,2,3,4,5
使用truncate清空数据后再添加数据,编号仍然是1,2,3,4,5
使用delete清空数据后再添加数据,编号就变成6,7,8,9,10
即删除的编号永远不存在了

1.6 基础查询

select*from DB_Student --查询表中所有列所有行的数据
(*表示所有列)
select StudentName,StudentSex from DB_Student --查询表中指定的两个列的数据

select StudentName学生姓名,StudentSex学生性别 from DB_Student --查询表中指定的两个列的数据,并显示中文列名
select distinct(StudentName) from DB_Sudent
--查询表中指定列的数据并去重
假设准备加20%的工资,查询前后对比,并显示中文列名 : 
select PeopleName,PeopleSalary原始工资,
PeopleSalary*1.2加薪后工资 from People

2. 各种查询

2.1 条件查询

常用运算符
1.=,!= --等于,不等于
2.>,<,>=,<= --大于,小于,大于等于,小于等于
3.BETWEEN...AND... --在两个数之间
3.and,or,not --与,或,非
4.in() --在某个范围中
5.is null,is not null --内容为空,内容不为空(空字符串也算内容)

select*from 表名 where 条件
如 : 
select*from People where PeopleSalary between 10000 and 20000
--查询月薪在10000-20000之间的员工信息
select*from People where PeopleAddress in('武汉','北京')
--查询地址在武汉或者北京的员工信息
select top 5*from People order by PeopleSalary
--查询工资最高的五个人的员工信息,升序排序
select top 10 percent*from People order by PeopleSalary
--查询工资前10%的员工信息,升序排序
select*from People where PeopleAddress is null
--查询地址没有填写的员工信息

查询所有员工信息,根据工资排序 ：
select*from People order by PeopleSalary asc(升序,为默认值,可以不写)
select*from People order by PeopleSalary desc(降序)

select*from People where PeopleSalary>
(select PeopleSalary from People where PeopleName='张三')
--查询工资比张三高的员工信息

判断语句case-end : 
case
    when...then... --当...时,输出...
    else... --否则输出...
end
--case后面可以跟一个表达式,类似switch(表达式),如case year(PeopleBirth)%12

2.2 模糊查询

模糊查询使用like关键字和通配符来实现

常见通配符 : 
1.% : 匹配n个字符(n可以为0)
2._ : 匹配1个字符
3.[] : 匹配方括号内存在的字符
4.[^] : 匹配方括号内不存在的字符

select*from People where PeopleName like '刘%'
--查询姓刘的员工信息(这里可以是刘x和刘xx)
select*from People where PeopleName like '刘__' --(这里是2个_)
select*from People where SUBSTRING(PeopleName,1,1)='刘' 
and len(PeopleName)=3
--查询姓刘的员工信息,且名字是3个字
select*from People where PeoplePhone like '138[7,8]%5'
--查询电话开头为138的,第4位是7或者8,最后1位是5的员工信息
select*from People where PeoplePhone like '138[2-5]%[^2,3]'
--查询电话开头为138的,第4位是2-5之间,最后1位不是2和3的员工信息

SUBSTRING(列名,3,2) : 从第3个字符开始取，一共取2位

2.3 聚合函数

常见聚合函数 : 
count : 求数量
max : 求最大值
min : 求最小值
sum : 求和
avg : 求平均值

select count(*) 人数 from People
--求员工人数
select max(PeopleSalary) from People
--求工资最大值
select min(PeopleSalary) from People
--求工资最小值
select sum(PeopleSalary) from People
--求工资的总和
select avg(PeopleSalary) from People
--求工资的平均值

round(x,y) --x:输入的数,y:保留的位数

2.4 分组查询

select ...from... 查询
where... --普通条件
group by... --分组
having... --聚合条件

select PeopleAddress 地区,sum(PeopleSalary) 工资总和 from People
where PeopleBrith <='1985-1-1'
group by PeopleAddress
having count(*) >= 2
--根据员工所在地区分组统计员工工资总和
--要求筛选出员工人数在2人及以上的记录,1985年及以后出生的员工不参与统计

2.5 多表查询

select*from People,Department
where People.DepartmentID = Department.DepartmentID
--查询员工信息,显示部门名称

内连接查询 : 
select*from People inner join Department 
on People.DepartmentID = Department.DepartmentID
--查询员工信息,显示部门名称

多表查询和内连接查询共同的特点 ： 
不符合主外键关系的数据不会被显示出来

外连接(左外连,右外连,全外连)
1.左外连 --以左表作为主表进行数据显示,主外键关系找不到的数据用null取代
select*from People left join Department 
on People.DepartmentID = Department.DepartmentID
--查询员工信息,显示部门名称
--这里以员工表作为主表,只要员工表内存在的员工,
不管有没有对应的部门,都会进行显示
2.右外连 --以右表作为主表进行数据显示,主外键关系找不到的数据用null取代
select*from People left join Department 
on People.DepartmentID = Department.DepartmentID
--查询员工信息,显示部门名称
--这里以部门表作为主表,只要部门表内存在的部门,
不管部门内有没有员工,都会进行显示
全连接 --两张表的数据,无论是否符合主外键关系,都显示,不符合的地方用null取代
select*from People full join Department 
on People.DepartmentID = Department.DepartmentID
--查询员工信息,显示部门名称
select DepartmentName 部门名称,count(*) 员工人数,sun(PeopleSalary)
工资总和 from People inner join Department on People.DepartmentID = 
Department.DepartmentID
group by Department.DepartmentID,DepartmentName
having avg(PeopleSalary) >= 10000
order by avg(Peoplesalary)desc
--根据部门分组统计员工人数,员工工资总和,平均工资在10000以下 的不参与统计,并且根据平均工资降序排列

自连接 : 自己连自己
select A.DepaermentID 部门编号,A.DepartmentName 部门名称,B.DepartmentName
上级部门 from Department A
inner join Department B on A.ParentID = B.DepartmentID
--把一个表分成A,B两部分,一个做主表,一个做副表

2.6 子查询

类似于C语言的递归

3. 杂七杂八的

3.1 变量

1.局部变量 : 以@开头,先声明,再赋值
declare @变量名 数据类型
declare @str varchar(20) --定义变量
set@str = 'hellow world' --赋值
print@str --输出
2.全局变量 : 以@@开头,由系统进行定义和维护
@@inentity : 返回最后插入的标识值

go语句 : 
1.等待go语句之前的代码执行完成之后才能执行后面的代码
create database DB
go --这里就能保住先创建数据库再切换数据库
use DB
2.批处理结束的一个标志
go
declare@num1 int --声明的num1作用范围在两个go之间
set@num1 = 100
go
set@num1 = 200  --这里的num1已经声明不到了

3.2 运算符

运算符 : 
1.和C语言一样的 : 省略
2.逻辑运算符 ： AND逻辑与  OR逻辑或  LIKE模糊查询  BETWEEN在...之间
IN在...内  EXISTS查询是否存在  ALL所有  ANY任一

if EXISTS(selsct*from DB_Student where StudentName = '张三')
--如果表中存在张三,则...
begin
...
end
else --不存在,则...
begin
...
end

if 85 < ALL(selsct StudentScore from DB_Student) --数字必须写在ALL前面
--如果表中所有学生的成绩都大于85,则...
begin
...
end
else --如果没有,则...
begin
...
end

if 85 < ANY(selsct StudentScore from DB_Student) --数字必须写在ALL前面
--如果表中有一个学生的成绩都大于85,则...
begin
...
end
else --如果没有,则... 
begin
...
end

3.3 流程控制

1.选择分支结构 : 省略
if-else   case-end
2.循环结构
declare @a int = 1
while @a<=10
begin 
    print @a
    set @a=@a+1
end
--循环打印1-10

4. 视图

4.1 索引

用处 : 为某一列添加索引,提高查询速度
聚集索引 : 只能有一个(通常是主键)
非聚集索引 : 可以有多个
唯一索引 : 一个列对应一个索引

创建索引 : 
create [unique] [clustered | nonclustered] --关键字选择添加  (唯一  聚集  非聚集) 
index 索引名字 on 表名 (列名[升序][降序])
查询索引 : 
select*from sys.indexes where name = '索引名'
删除索引 : 
drop index 索引名 on 表名
指定某个索引进行查询 ： 
select*from DB_Student with(index = 索引名)
where StudentID = 123

4.2 视图

视图 : 类似C语言的结构体
创建视图 : 
create view 视图名称 as select语句 go
使用视图 : 
select*from 视图名称