SQL Server全套教程全程干货

  • 1. 数据库的基础操作
    • 1.1.0 创建数据库
    • 1.1.1 查看及修改数据库
    • 1.1.3 分离、附加和删除数据库
    • 1.1.4 数据库的备份和还原
  • 2.数据库表的相关操作
    • 2.1.0 常用数据类型
    • 2.1.1 表结构的创建
    • 2.1.2 表结构的查看及修改
    • 2.1.3 表约束的创建
    • 2.1.4 表约束的修改
    • 2.1.5 数据的添加
    • 4.1.2 数据的修改和删除
  • 3. 数据查询
    • 3.1.0 数据查询（简单查询）
    • 3.1.1数据查询（条件查询）
    • 3.1.2数据查询（模糊查询）
    • 3.1.3 数据查询（聚合函数）

1. 数据库的基础操作

1.1.0 创建数据库

数据库创建语法

-- 创建数据库1. create database 数据库名字 2. on primary( name='数据文件名', filename='完整路径\数据文件名.mdf', size=初始大小,maxsize=最大大小, filegrowth=文件增长比 )--创建日志文件3.log on (name='日志文件名',filename='完整路径\日志文件名.ldf',size=初始大小,maxsize=最大大小,filegrowth=文件增长比)

数据库创建实例示范


解说：
按上图SQL语法即可以创建名为Mi的数据库，数据主文件Mi_data.mdf,以及数据日志文件Mi_log.ldf等
拓展（三种文件类型）：.mdf主数据文件 .ndf次数据文件 .ndf日志文件

1.1.1 查看及修改数据库

数据库查看语法

 --查看数据库1. exec sp_helpdb 数据库名​


数据库修改语法

--修改数据库2. alter database 数据库名 add file/add log file /modify file （file为数据库mdf文件名）


解说：
查看数据库可以看到当前数据库的一些基本信息，数据库名、文件大小、存放位置等；修改数据库，上图操作将初始日志文件2048KB大小Mi_log.ldf文件，增加至了3072KB。

1.1.3 分离、附加和删除数据库

数据库分离语法
目的：分离数据库是为了能够手动找到数据数据文件，将其物理拷贝到其他地方，进行备份。

-- 分离数据库1.execsp_detach_db ' 数据库名'

数据库附加语法
目的：能够使用其他数据库文件，导入他人的数据库。

-- 附加数据库2.exec sp_attach_db '数据库名','完整路径\数据文件名.mdf'

数据库删除语法

-- 删除数据库（注意哦，删除不可逆哦,当前使用库删除操作不能完成）3.drop database 数据库名

数据库分离、附加及删除实例演示

1. 分离数据库 exec sp_detach_db Mi2.附加数据库 exec sp_attach_db 'Mi','E:\SqlServerDate\OthersData\Mi_data.dmf'​3.删除数据库 drop database Mi​-- 4.判断加删除数据库 if exists (select * from sys.databases where name = 'Mi')drop database Mi

1.1.4 数据库的备份和还原

数据库备份设备创建

use 数据库名goexec sp_addumpdevice '磁盘设配'，'备份名称','设备名称物理存储路径'


数据库备份

backup database 数据库名 to 备份设备名称


数据库还原（可以同时还原多个数据库，但结尾只需要一个with replace）

restore database 数据库名from 备份设备名称 with replace

2.数据库表的相关操作

2.1.0 常用数据类型

  1. 整数型 (int)
 userId int primary key identity(1.1)
  1. 定长字符（char）
 userName char(10) not null -- char(10) 即使存入'ab'两个字节，但它仍然占用10个字节
  1. 变长字符（varchar）
userName varchar(10) not mull-- varchar(10) 存入多少占用多少字节 最大为10个字节
  1. 长文本类型（text）
 address text not null -- text是长文本类型，可以无限制写入，但是执行效率比较低
  1. char、varchar、text前加n
 userName nvarchar(100) not null -- nvarchar(100) 存储unicode码  varchar(100) 存储100个字母，存储50汉字 nvarchar(100) 存储100个字母，存储100汉字
  1. 时间（date、datetime）
 -- datetime可以存储年月日时分秒，当前时间前后都可以 userBirth datetime not null, -- date存储年月日 userBirth date not null-- smalldatetime 表示在当前时间之前的时间 userBirth smalldatetime not null
  1. 小数（float，decimal,bit）
 salary decimal(12,2) check(salary >=1000 and salary <= 1000000) not null,-- 薪水 decimal(总长度，小数位数) bit类型放0和1

2.1.1 表结构的创建

数据表的创建语法

1.建表-- 切换到目标数据库use 目标数据库名create table 表名(字段1数据类型 ,字段2数据类型 )-- 创建组合主键create table user(Sno char(6) not null ,Pno char(6) not null ,Jno char(6) not null,primary key(Sno,Pno,Jno),)

数据表的创建实例

说明：
数据库表，也称二维关系表，对应具体的一个实体。针对于上文，数据库的切换，可以采用图形化界面操作，也可以使用SQL语句的方式切换，如何查看数据库是否已经切换为当期数据库。查看MSMS图形化管理工具的左上角一个下拉框。当数据表字段存在关键字时，可以采用[ ] 将字段名括起来，避免语法错误。

2.1.2 表结构的查看及修改

查看表结构：

-- 语法：exec sp_help 表名


表结构的修改：

  1. 添加列
 -- 语法：alter table 表名 add 新列名 数据类型 alter table userInfo add email nvarchar(20)
  1. 删除列
 -- 语法：alter table 表名 drop column 列名 alter table userInfo drop email
  1. 修改列
 -- 语法：alter table 表名 alter column 列名 数据类型 alter table userInfo alter coulumn phone nvarchar(13) -- 注意：如果该表数据，phone字段数据长度假设添加的是20位的，现在修改位13是会报错的

2.1.3 表约束的创建

 列名1 数据类型(长度) notnull primary key -- 说明：列名2是被定义成了外键 列名2 数据类型(长度) notnull references 主键表(主键) 列名3 数据类型(长度) notnull check(列名3='' and 列名3='') 列名4 数据类型(长度) notnull default '默认值' 列名5 数据类型(长度) notnull unique

2.1.4 表约束的修改

删除表约束：

-- 语法：alter table 表名 drop constraint 约束名alter table UserInfo drop constraint CK__UserInfo__salary__7C4F7684-- 约束名怎么找？-- 1. 当前表右键设计表 2.计入设计表后任意位置右键 找到check约束，可以选择手动删除或命令删除

说明：
约束名如果是自定义的比较好书写，但是如果是系统自动生成的约束名比较复杂。可以按照上图方式进行约束名查找。

添加表约束：

-- 添加约束-- 为salary字段添加check约束alter table UserInfo add constraintCK__UserInfo__salary66 check(salary >=1000 and salary <=200000)

常用的约束添加：

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

2.1.5 数据的添加

添加数据的第一种方式：

语法：insert into 表名(字段1,字段2) value('值','值')
--向People表输入数据ctrl+shift+rinsert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(7,1,'徐宏','男','2000-05-6',6000,'19123929393','中国南昌',getdate(),'124144512@qq.com')insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(3,2,'徐向前','男','1997-09-6',12000,'456346929393','中国广州',getdate(),'999999912@qq.com')insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(1,2,'谢颖儿','女','2000-09-9',8000,'1994329393','中国四川',getdate(),'1888144512@qq.com')insert into

添加数据的第二种方式：

-- 2.简写 前提：字段名对应位置不要打乱insert into Department valuses('值','值')

添加数据的第三种方式：

-- 3.一次性插入多条数据insert into Department(departmentName,departmentAddress,departmentLeader)select '软件部','行政大楼2层202','Mr Xie' union select '测试部','行政大楼2层206','Miss Liu'unionselect '实施部','行政大楼2层207','mr Gong'

说明：如果添加的数据个数和表字段个数一一对应(字段顺序)，则可以省略表后( )内的字段书写

4.1.2 数据的修改和删除

修改：

-- 修改格式： update 表名 set 字段1 = 值,字段2 = 值，....字段n =值 where 条件 update student set scores= scores + 5 where scores between 60 and 70-- 将选了计算机导论，Java程序设计的同学分数加一分update student set scores = scores + 1 where className in('计算机导论'，'Java程序设计')

删除

-- 删除数据格式1: delete from 表名 where 条件（也可以不带条件，不带条件默认清空表数据，请谨慎）格式2：drop table 表名 格式3：truncate from 表名

delete / drop /truncate几种删除方式的区别？

delete删除数据可以带条件，清空数据但表的结构还在；如果表中数据为自动编号，使用delete删除后序号是从下一个开始。即原表序号1,2,3,4,5,6 删除记录第6条，再次向表新增一条数据，编号从7开始；也就是说表中不会存在编号为6的记录。例如：delete from 表名 where id = 1 and name= 'xxx'

truncate删除数据不可以带条件，清空数据但表结构还在；如果表中数据为自动编号，使用truncate删除后序号是从删除的当前序号开始。即原表序号1,2,3,4,5,6 删除记录第6条，再次向表新增一条数据，编号从6开始例如：truncate table 表名

drop删除数据，直接删除表结构和数据 例如：drop table 表名

3. 数据查询

写给读者：本小节是整个数据库内容里面最为重要的内容，也是对前面所学知识的综合运用。希望每一位读者都可以自己建库建表进行实操，您可以选择使用下面的假数据，也可以使用您自己的数据。但涉及到真实的数据库的数据操作，请您慎重（因为数据删除是个危险操作哦）

伪数据

-- 创建Department部门数据表create table Department(DpartmentId int primary key identity(1,1),DepartmentName nvarchar(10) not null,DepartmentRemark text)-- 创建等级数据表create table [Rank](RankId int primary key identity(1,1),RankName nvarchar(10) not null,RankRemark text)-- 创建员工表create table People(DepartmentId int references Department(DpartmentId) not null,-- 部门 引用外键RankId int references [Rank](RankId),--职级PeopleId int primary key identity(202200,1),-- 员工编号PeopleName nvarchar(50) not null,PeopleGender nvarchar(1) default('男') check(PeopleGender='男' or PeopleGender='女') not null,PeopleBirth smalldatetime not null,PeopleSalary decimal(12,2) check(PeopleSalary >=1000 and PeopleSalary <=1000000) ,PeoplePhone nvarchar(11)unique not null,PeopleAddress nvarchar(100),peopleAddTime smalldatetime default(getdate()),)-- 数据添加-- Department表插入数据insert into Department(DepartmentName,DepartmentRemark)values('软件部','........')insert into Department(DepartmentName,DepartmentRemark)values('策划部','........')insert into Department(DepartmentName,DepartmentRemark)values('市场部','........')insert into Department(DepartmentName,DepartmentRemark)values('设计部','........')insert into Department(DepartmentName,DepartmentRemark)values('后勤部','........') -- 向Rank表insert into [Rank](RankName,RankRemark)values('初级','.....')insert into [Rank](RankName,RankRemark)values('高级','.....')insert into [Rank](RankName,RankRemark)values('中级','.....')select * from Peopleselect * from [Rank]select * from Department--向People表输入数据ctrl+shift+rinsert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(7,1,'徐宏','男','2000-05-6',6000,'19123929393','南昌',getdate(),'124144512@qq.com')insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(3,2,'徐向前','男','1997-09-6',12000,'456346929393','广州',getdate(),'999999912@qq.com')insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(1,2,'谢颖儿','女','2000-09-9',8000,'1994329393','四川',getdate(),'1888144512@qq.com')insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(4,3,'老王','男','1976-10-23',3000,'999939333','黑龙江',getdate(),'99999512@qq.com')insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,peopleAddTime,PeopleMail)values(2,1,'老六','男','1999-10-23',4500,'1291248143','山东',getdate(),'1244559512@qq.com')

3.1.0 数据查询（简单查询）

--查询指定列(姓名，性别，出生日期) 先写表名会有提示select PeopleName,PeopleGender,PeopleBirth from People​--查询指定列(姓名，性别，出生日期)并且用别名显示select PeopleName 姓名,PeopleGender 性别,PeopleBirth 出生日期 from People​-- 查询所在城市（过滤重复）selectdistinct PeopleAddressfrom People​--假设准备加工资（上调20%) 查询出加工资后的员工数据select PeopleName,PeopleGender,PeopleSalary*1.2 加薪后工资from People ​--假设准备加工资（上调20%) 查询出加工资后和加工资前的员工数据对比select PeopleName,PeopleGender,PeopleSalary, PeopleSalary*1.2 加薪后工资from People

3.1.1数据查询（条件查询）

SQL常用运算符

= :等于，比较是否相等及赋值
!=:比较不等于 （）
>:比较大于
<:比较小于
>=:比较大于等于
<=:比较小于等于
IS NUll:比较为空（null是表示此时没写该字段，而不是空值null，如果是空值”” 用=）
IS NOt NUll :比较不为空
in:比较是否再其中
like:模糊查询
BETWEEN....AND.......：比较是否在两者之间
and：逻辑与(两个条件都满足)
or:逻辑或（两个有一个条件表达式成立）
not:逻辑非（条件成立，表达式则不成立；条件不成立，表达式则成立 )

-- 查询数据为女的信息select * from People where PeopleGender = '女'​-- 查询数据为男的，工资大于8000的数据select * from People where PeopleGender ='男' and PeopleSalary >=8000​-- 查询出出生年月在1990-1-1之后，月薪大于10000的女员工select * from People where PeopleBirth >= '1990-1-1' and PeopleSalary >=10000 and PeopleGender = '女'​--查询月薪大于10000的，或者月薪大于8000的女员工select * from People where PeopleSalary >=10000 or (PeopleSalary>=8000 and PeopleGender='女')​-- 查询月薪在8000-12000之的员工姓名、住址和电话（多条件）select PeopleName,PeopleAddress,PeoplePhonefrom People where PeopleSalary >=8000 and PeopleSalary <=120000​-- 查询月薪在8000-12000之的员工姓名、住址和电话（多条件）select PeopleName,PeopleAddress,PeoplePhonefrom People where PeopleSalary between 8000 and 120000​​-- 查询出地址在南昌和贵州的员工信息select * from People where PeopleAddress ='南昌' or PeopleAddress='贵州'​-- 如果涉及条件比较多用in(或者关系)​select * from People where PeopleAddress in('南昌','贵州','黑龙江')​-- 排序​--根据工资降序排序select * from People order by PeopleSalary desc​​--根据工资升序排序（asc默认值）select * from People order by PeopleSalary asc​​-- 根据名字长度降序排序select * from People order by LEN(PeopleName) desc​-- 根据名字长度降序排序（显示前3条）select top 3 * from People order by LEN(PeopleName) desc-- 查看下数据表所有内容select * from People-- 查出工资最高的50%的员工信息select top 50 percent * from People order by PeopleSalary desc​​-- 插入一条数据insert into People(DepartmentId,[RankId],[PeopleName],[PeopleGender],[PeopleBirth],[PeopleSalary],[PeoplePhone],[peopleAddTime],[PeopleMail])values(1,1,'老李头','男','1999-12-21',23000,19293459999,GETDATE(),'888883333@qq.com')​​-- 查询地址为空值的为null 用is关键字select * from People where PeopleAddress is null​​-- 查询地址为不为空值的为null 用is not关键字select * from People where PeopleAddress is not null​​-- 查询出90后的员工信息select * from People where PeopleBirth >= '1990-1-1' and PeopleBirth <='1999-1-1'select * from People where PeopleBirth between'1990-1-1' and '1999-1-1'select * from People whereyear(PeopleBirth) between 1990 and 1999​​-- 查询年龄在20- 30之间，工资在15000-20000的员工信息-- 当前year(getdate())—year(peopelbirth)select * from People whereyear(getdate())-year(PeopleBirth) <=30 and year(getdate())-year(PeopleBirth) >=20and PeopleSalary >= 15000 and PeopleSalary <=20000​​-- 查询出星座为巨蟹座的员工信息（6.22-7.22）select * from People where month(PeopleBirth) = 6 and day(PeopleBirth) >=22 or month(PeopleBirth) =7 and day(PeopleBirth) <=22​​-- 子查询查询出工资比胡九九高的员工信息select * from People where PeopleSalary > (select PeopleSalary from People where PeopleName ='胡九九')​-- 查询出和老王同一城市的员工select * from People where PeopleAddress = (select PeopleAddress from People where PeopleName ='老王')​​-- 查询出生肖信息为老鼠的员工信息-- 鼠牛虎兔龙 蛇马 羊猴鸡狗猪-- 4 5 6 7 89 10 11 0 1 2 3select * from People where year(PeopleBirth)% 12 = 8​​-- 查询出所有员工信息的生肖信息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 =3 then '猪'else ''end '生肖'from People-- 查询出所有员工信息的生肖信息select * ,caseyear(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​

3.1.2数据查询（模糊查询）

%:代表匹配0个，1个字符或者多个字符
_:代表匹配有且只有一个字符
[]:代表匹配范围内
[^]:代表匹配不在范围内

use UserSystemselect * from Peopleselect * from [Rank]select * from Department-- 查询出姓老的员工信息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) ='胡' andlen(PeopleName) =2-- 查询名字最后一个字为九的员工信息（假设表中名字长度都为3个）select * from People where SUBSTRING(PeopleName,3,1)='九' andlen(PeopleName) =3select * from People where PeopleName like '__九'（注意这里有两个下划线占位符）-- 查询电话以191开头的员工信息 select * from People where PeoplePhone like '191%'-- 查询电话以191开头,第四位是3或6的电话，最后一位是3的select * from People where PeoplePhone like '191[3,6]%3'-- 查询电话以192开头的，中间是7-9的数字一个，结尾不是以678结尾得select * from People where PeoplePhone like '192[7,8,9]%[^6,7,8]'select * from People where PeoplePhone like '192[7-9]%[^6-8]'

3.1.3 数据查询（聚合函数）

函数名用例
count( * )查询当前记录的总数和符合条件的数目
max（）min（）avg（）最大值、最小值、平均值
sum（）求列和
round（param1,param2）保留小数位数;参数1：源数据 参数2:保留小数位数
year()返回年份
datadiff(单位差，数据2，数据1)可以返回一个以年为单位的数据
select * from People-- 求员工总人数selectcount(*) '总人数' from People​-- 求最大值 最高工资select max(PeopleSalary) '最高工资' from People​--求最小值 最低工资select min(PeopleSalary) '最低工资' from People​-- 求和 求所有员工工资的总和select SUM(PeopleSalary)'工资总和' from People​--求平均值 求所有员工的平均工资select Round(avg(PeopleSalary),2)'平均工资' from People-- 参数2表示保留几位小数select Round(999.2222,1)​​-- 求数量 最高工资 最低工资 平均工资 在一行显示selectcount(*) '总人数',max(PeopleSalary) '最高工资',SUM(PeopleSalary)'工资总和'from Peoplewhere PeopleAddress = '中国南昌'​​--求出比平均工资高的员工信息select * from People where PeopleSalary > (select ROUND(AVG(PeopleSalary),2)平均工资 from People)​-- 求出数量 最大年龄 最小年龄 年龄总和 年龄平均值select COUNT(*),MAX(year(GETDATE())-year(PeopleBirth))最高年龄,min(year(GETDATE())-year(PeopleBirth))最小年龄,sum(year(GETDATE())-year(PeopleBirth))年龄总和,avg(year(GETDATE())-year(PeopleBirth))年龄平均值from People​​-- 方案二select COUNT(*),MAX(DATEDIFF(year,PeopleBirth,getdate()))最高年龄,min(DATEDIFF(year,PeopleBirth,getdate()))最小年龄,sum(DATEDIFF(year,PeopleBirth,getdate()))年龄总和,avg(DATEDIFF(year,PeopleBirth,getdate()))年龄平均值from People​-- 求出月薪在10000以上的男员工的数量，年龄最大值 最小值select '月薪10000以上'月薪,'男'性别, count(*)数量,max(year(GETDATE())-YEAR(PeopleBirth)) 年龄最大值,min(year(GETDATE())-YEAR(PeopleBirth)) 年龄最小值,avg(year(GETDATE())-YEAR(PeopleBirth))年龄平均值 from People where PeopleSalary >10000and PeopleGender ='男'​​-- 求出年龄比平均年龄大的员工select * from People where YEAR(GETDATE())-YEAR(PeopleBirth) > (select AVG(YEAR(getdate())-year(PeopleBirth))from People)