SQL SERVER数据库：SQL看这一篇就不够了（附详细代码及截图）

���录

写在前面

01-SQL SERVER 数据库基础

02_01-创建数据库

02_02-创建数据表

02_03-表结构和约束的维护

03-插入数据

04-数据的修改和删除

05-基本查询

06_01-条件查询一

06_02-条件查询二

07-模糊查询

08-聚集函数

09-分组查询

10-多表查询一

11-多表查询二

---

写在前面

本篇文章是在下面这个B站课程里学的，作为我的学习记录同时也希望能帮助到大家。

【SQL SERVER数据库_D丝学编程】 https://www.bilibili.com/video/BV1XV411C7TP/?p=2&share_source=copy_web&vd_source=02052d2fc18088b96251f0ac88221db6

废话少说，现在就开始学习吧！

01-SQL SERVER 数据库基础

SQL SERVER介绍

数据库：数据库是“按照数据结构来组织、存储和管理数据的仓库”，是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

应用场景：在软件系统中无处不在，几乎所有的软件系统背后都有数据库，例如（淘宝、QQ、游戏等）。

数据表现形式：（二维表）

学号	姓名	性别	电话	邮箱
001	刘备	男	13558789854	liubei@qq.com
002	孙尚香	女	13665878523	susnhangxiang@163.com
003	关羽	男	13223569852	guanyu@163.com
004	张飞	男	1343720083	zhangfei@163.com

主流关系型数据库：SQL SERVER、MySQL、ORACLE等。

SQL SERVER官网：SQL Server 下载 | Microsoft

SQL SERVER界面

打开数据库：

（1）启动服务：

【1】命令行启动；【2】SQL SERVER配置管理器；【3】Windows服务；

（2）打开SQL SERVER Management Studio，使用工具连接到数据库。

【1】Windows身份验证；【2】SQL SERVER身份验证；

数据库基本操作：

（1）建库；（2）建表；（3）数据维护。

（1）建库：右键数据库 新建数据库

DBTEST是数据文件，DBTEST_log是日志文件。

（2）建表

 

 把StuNo（学号）设置为主键，唯一标识一个人。

 

设置好之后，刷新表就能看到刚才新建的表了。

 

选择前1000行可以查询数据，刚建的表还没有插入数据，所以现在是查询不到信息的。

 

 编辑数据：编辑前200行，可以插入、修改、删除数据。

 

 删除数据

数据库的迁移：

 （1)数据库的分离、附加；（分离和删除的区别在于硬盘上是否还留存有数据库文件）

（2）数据库的备份、还原；

（3）数据库脚本的保存。

02_01-创建数据库

--drop database DBTEST 当前数据库如果已经存在DBTEST 就先删掉再创建
--创建数据库
/*if exists (select * from sys.databases where name = 'DBTEST')
drop database DBTEST*/
--危险操作
create database DBTEST
on --数据文件
(
    name = 'DBTEST',--逻辑名称 在数据库里字符串放在单引号‘’里
	filename = 'D:\DATA\DBTEST.mdf' , --物理路径和名称
    size = 5MB,--文件的初始大小
	filerowth = 2MB--文件增长可以写大小，也可以写百分比
)
log on --日志文件
(
    name = 'DBTEST_log',--逻辑名称 在数据库里字符串放在单引号‘’里
	filename = 'D:\DATA\DBTEST_log.ldf' , --物理路径和名称
    size = 5MB,--文件的初始大小
	filerowth = 2MB--文件增长可以写大小，也可以写百分比
)

02_02-创建数据表

建表（部门表、职级表、员工表）

--默认情况下，建表会建在master库里
--切换数据库
use DBTEST
--创建表基本语法
/*
create table 表名
(
   列名1 数据类型，
   列名2 数据类型，
   列名3 数据类型
)
*/
--判断表是否存在
if exists(select * from sys.objects where name = 'Department'and type='U')
drop table Department
--建表（部门表、职级表、员工表）
create table Department
(--部门表
   --部门编号 primary:主键，唯一标识一行;identity:自动增长，初始值为1，增长步长也为1
   DepartmentId int PRIMARY KEY identity(1,1),--设置为主键之后编号不能重复。
   --部门名称
   DepartmentName nvarchar(50) not null,--nvarchar代表字符串，nvarchar(50)表示最多不能超过50个字符
   --部门描述
   DepartmentRemark text
)
/* char\varchar\text\nchar\nvarchar\ntext的区别
  ①char     定长，char(10),无论存储的数据是否真的有10字节，都要占用10字节；
             char(10)存储'ab',仍然占用10字节。
  ②varchar  变长，varchar（10），最多占10字节；
             varcahr(10)存储'ab'，占用2字节。
  ③text     长文本，可以存储几千几万个字节的数据
  ④char、varchar、text前面加n：存储Unicode字符，对中文友好。
    varchar(100):存储100个字母或者50个汉字,一个汉字占两个字节
	nvarchar(100)：存储100个字母或者100个汉字。
*/
create table [Rank]
(--职级表
   --职级编号 primary:主键，唯一标识一行;identity:自动增长，初始值为1，增长步长也为1
   RankId int PRIMARY KEY identity(1,1),--设置为主键之后编号不能重复。
   --职级名称
   RankName nvarchar(50) not null,--nvarchar代表字符串，nvarchar(50)表示最多不能超过50个字符
   --职级描述
   RankRemark text
)
create table People
(--员工表
   --员工编号 primary:主键，唯一标识一行;identity:自动增长，初始值为1，增长步长也为1
   PeopleId int PRIMARY KEY identity(1,1),--设置为主键之后编号不能重复。
   --部门 references:引用外键，将其他表的主键作为这个表的字段（一列）
   DepartmentId int references Department(DepartmentId) ,
   --职级 加了references之后，添加外键字段时会自动检测输入的字段是否正确
   RankId int references [Rank](RankId),
   --员工姓名
   PeopleName nvarchar(50) not null,--nvarchar代表字符串，nvarchar(50)表示最多不能超过50个字符
   --员工性别 用default('男')设置默认情况下员工性别为男，
   --check(PeopleSex='男' or PeopleSex='女')约束性别只能是男或者女
   PeopleSex nvarchar(1) default('男') check(PeopleSex='男' or PeopleSex='女'),
   --员工生日 datetime类型能存储年月日时分秒，date类型只能存储年月日,smalldaetime只能存储最近的一段时间
   PeopleBirth smalldatetime not null,
   --员工薪资 decimal(12,2) 12:长度最长为12，2：精确到小数点后2位
   PeopleSalary decimal(12,2) check(PeopleSalary >= 1000 and PeopleSalary200
	 alter table People alter column PeopleAddress varchar(200)

注意：在做具体设计或者项目的时候，要尽量少的使用alter语句，因为当我们修改表的结构的时候很有可能整个软件或者系统都要跟着一起修改，会造成很大的麻烦。

维护约束

约束名查找

--修改表的结构--------------------------------------------
--（1）添加列 alter table 表名 add 新列名 数据类型
	 alter table People add PeopleMail varchar(30)--添加PeopleMail列
--（2）删除列 alter table 表名 drop column 列名 
     alter table People drop column PeopleMail--删除PeopleMail列
--（3) 修改列 alter table 表名 alter column 列名 数据类型
     --修改People表中的PeopleAddress的长度300-->200
	 alter table People alter column PeopleAddress varchar(200)
--删除添加约束--------------------------------------------
--（1）删除约束 （本质上也是修改表，修改表的约束 
    --alter table 表名 drop constraint 约束名  
    --删除PeopleSalary的约束
	 alter table People drop constraint CK_PeoPleSa_34C8D9D1
--（2）添加约束 
    --alter table 表名 add constraint 约束名 check（表达式）
	--添加check约束 添加工资约束 工资范围：1000~1000000  
	alter table People add constraint CK_PeoPleSa1 check(PeopleSalary >= 1000 and PeopleSalary大于=大于等于= 10000
	--多条件|查询出性别为女且大于等于10000元的员工信息
	select * from People where PeopleSex='女' and PeopleSalary>= 10000
	--查询出月薪大于等于10000的员工或者月薪大于等于8000的女员工
	select * from People where PeopleSalary >= 10000 or (PeopleSalary>=8000 and PeopleSex='女')
    --查询出出生年月在1980-1-1之后，且月薪大于等于10000的女员工
	select * from People where PeopleBirth >='1980-1-1'and (PeopleSalary>=10000 and PeopleSex='女' )
	--查询月薪在10000-20000之间的员工信息
	select * from People where PeopleSalary >= 10000 and PeopleSalary = '1980-1-1' and PeopleBirth= 22--如果是6月要大于22
	or
	month(PeopleBirth)= 7 and day(PeopleBirth)  
	(select  PeopleSalary from People where PeopleName = '刘禅')
	--查询出和刘禅在同一个城市的员工信息
	select * from People where PeopleAddress > 
	(select  PeopleAddress from People where PeopleName = '刘禅')
   --查询出生肖是'鼠'的员工信息
   --鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
   --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=3 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 
 
 
 07-模糊查询
 
--模糊查询   like
/*
   模糊查询使用like关键字和通配符结合来实现，通配符的具体含义如下：
   %  ：代表匹0个字符，1个字符或者多个字符
   _  ：代表匹配有且只有1个字符
   [] ：代表匹配范围内
   [^]：代表匹配不在范围内
*/
--（1）查询姓刘的员工信息
select * from People where PeopleName like '刘%'
--（2）查询名字中含有'尚'的员工信息
select * from People where PeopleName like '%尚%'
--（3）显示名字中含有'尚'或者'兰'的员工信息
select * from People where PeopleName like '%尚%' or PeopleName like '%兰%'
--（4）查询姓刘的员工，名字是2个字
select * from People where PeopleName like '刘_'
select SUBSTRING('hello word',3,4)
--SUBSTRING函数取字符串里的值，第一个参数代表要操作的字符，第二个参数代表从第几个字符串开始取，第三个参数代表取几个字符
select * from People where SUBSTRING(PeopleName,1,1)= '刘' and len(PeopleName) = 2
--（5）查询出名字最后一个字是'香'，名字一共是三个字的员工信息
select * from People where PeopleName like '__香'
select * from People where SUBSTRING(PeopleName,3,1)= '香' and len(PeopleName) = 3
--（6）查询出电话号码开头为138的员工信息
select * from People where PeoplePhone like '138%'
--（7）查询出电话号码为192的，第4位是3或者8，最后一个号码是3的员工信息
select * from People where PeoplePhone like '1923%3' or PeoplePhone like '1928%3'
--（8）查询出电话号码为192的，第4位是2-5之间，最后一个号码不是5和6
select * from People where PeoplePhone like '192[2,3,4,5]%[^5,6]'
select * from People where PeoplePhone like '192[2-5]%[^5,6]'
 
 
 
08-聚集函数
 
SQL SERVER 中聚集函数主要有：
 
 
 
count：求数量
 
 
max:求最大值
 
 
min：求最小值
 
 
avg：求平均值
 
 
    --（1）求员工总人数
	select count(*)员工总人数 from People
	--（2）求最大值，求最高工资
	select max(PeopleSalary) 最高工资 from People
	--（3）求最小值，求最小工资
	select min(PeopleSalary) 最低工资 from People
	--（4）求和，求所有员工的工资总和
	select sum(PeopleSalary) 工资总和 from People
	--（5）求平均值，求所有员工的平均工资
	select avg(PeopleSalary)平均工资 from People
 
 
用AVG函数求所有员工的工资平均值
 
--（5）求平均值，求所有员工的平均工资
	select avg(PeopleSalary)平均工资 from People
	/*
	select round(a,b,c) from 表名
	round函数 a：要处理的数据 b：保留到小数点后b位 c:0就四舍五入，c是其他数字直接把后面的数值删掉
	如果要四舍五入，第三个参数可以省略，因为ground默认是0。
	*/
	select round(sum(PeopleSalary)/count(PeopleSalary),2,0)averageSalary from People
	select round(avg(PeopleSalary),2)平均工资 from People
 
 
	--（6）求数量、最大值、最小值、总和、平均值在一行显示
	select count(*) 人数,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资,
	       sum(PeopleSalary) 工资总和, avg(PeopleSalary)平均工资 from People
 
 
    --在聚集函数的基础上加上条件查询
	--（7）查询出保定地区的员工人数、总工资、最高工资、最低工资和平均工资
	select count(*)员工人数,sum(PeopleSalary)总工资,max(PeopleSalary)最高工资,
	min(PeopleSalary)最低工资,avg(PeopleSalary)平均工资
	from People 
	where PeopleAddress = '保定'
	--（8）求出工资比平均工资高的人员信息
    select * from People where PeopleSalary >
	(select round(avg(PeopleSalary),2) 平均工资
	from People)
 
 
--（9）求数量，年龄最大值，年龄最小值，年龄总和，年龄平均值，在一行显示
	select *,year(getdate())-year(PeopleBirth) 年龄 from People --①通过year()求年龄
	select datediff(year,PeopleBirth,getdate()) from People     --②通过datediff()求时间差datediff(a,b,c) a:结果的单位，有year、month、day  时间差=c-b  
	
	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 
	
 
 
	--聚集函数
	/*
	   count:求数量 
	   max：求最大值
	   min：求最小值
	   sum ：求和
	   avg：求平均值
	*/
	--（1）求员工总人数
	select count(*)员工总人数 from People
	--（2）求最大值，求最高工资
	select max(PeopleSalary) 最高工资 from People
	--（3）求最小值，求最小工资
	select min(PeopleSalary) 最低工资 from People
	--（4）求和，求所有员工的工资总和
	select sum(PeopleSalary) 工资总和 from People
	--（5）求平均值，求所有员工的平均工资
	select avg(PeopleSalary)平均工资 from People
	/*
	select round(a,b,c) from 表名
	round函数 a：要处理的数据 b：保留到小数点后b位 c:0就四舍五入，c是其他数字直接把后面的数值删掉
	如果要四舍五入，第三个参数可以省略，因为ground默认是0。
	*/
	select round(sum(PeopleSalary)/count(PeopleSalary),2,0)averageSalary from People
	select round(avg(PeopleSalary),2)平均工资 from People
	--（6）求数量、最大值、最小值、总和、平均值在一行显示
	select count(*) 人数,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资,
	       sum(PeopleSalary) 工资总和, avg(PeopleSalary)平均工资 from People
    --在聚集函数的基础上加上条件查询
	--（7）查询出保定地区的员工人数、总工资、最高工资、最低工资和平均工资
	select count(*)员工人数,sum(PeopleSalary)总工资,max(PeopleSalary)最高工资,
	min(PeopleSalary)最低工资,avg(PeopleSalary)平均工资
	from People 
	where PeopleAddress = '保定'
	--（8）求出工资比平均工资高的人员信息
    select * from People where PeopleSalary >
	(select round(avg(PeopleSalary),2)
	from People)
	--（9）求数量，年龄最大值，年龄最小值，年龄总和，年龄平均值，在一行显示
	select *,year(getdate())-year(PeopleBirth) 年龄 from People --①通过year()求年龄
	select datediff(year,PeopleBirth,getdate()) from People     --②通过datediff()求时间差datediff(a,b,c) a:结果的单位，有year、month、day  时间差=c-b  
	 --year（）
	select Count(*) 数量,
	Max(year(getdate())-year(PeopleBirth)) 年龄最大值,
	Min(year(getdate())-year(PeopleBirth)) 年龄最小值,
	sum(year(getdate())-year(PeopleBirth)) 年龄总和,
	avg(year(getdate())-year(PeopleBirth)) 年龄平均值
	from People 
	--datediff（）
    select Count(*) 数量,
	Max(datediff(year,PeopleBirth,getdate())) 年龄最大值,
	Min(datediff(year,PeopleBirth,getdate())) 年龄最小值,
	sum(datediff(year,PeopleBirth,getdate())) 年龄总和,
	avg(datediff(year,PeopleBirth,getdate())) 年龄平均值
	from People 
	--（10）计算出月薪在10000以上的男性员工的人数、最大年龄，最小年龄和平均年龄
	select COUNT(*) 数量,
	Max(datediff(year,PeopleBirth,getdate())) 年龄最大值,
	Min(datediff(year,PeopleBirth,getdate())) 年龄最小值,
	avg(datediff(year,PeopleBirth,getdate())) 年龄平均值
	from People
	where PeopleSalary >=10000 and PeopleSex = '男'
	--（11）统计出所在地在'荆州'或在'安庆'的女员工的人数、最大年龄，最小年龄和平均年龄
	select COUNT(*) 数量,
	Max(datediff(year,PeopleBirth,getdate())) 年龄最大值,
	Min(datediff(year,PeopleBirth,getdate())) 年龄最小值,
	avg(datediff(year,PeopleBirth,getdate())) 年龄平均值
	from People
	where PeopleSex = '女' and PeopleAddress in('荆州','安庆') 
	--（12）求出年龄比平均年龄高的人员信息
	select * from People where year(getdate()-year(PeopleBirth))>
	(select avg(year(getdate())-year(PeopleBirth))from People)
 
09-分组查询
 
	--分组查询
	--（1）根据员工所在地区分组统计员工人数、员工工资总和、平均成绩、最高成绩和最低工资
	--方案一：使用union，union是把多个查询结果进行合并 （不推荐：①代码多；②必须要找到有那些地区才可以查询，很繁琐）
	--（此方案需要知道所有的地区，分别查询出所有地区的数据，然后使用union拼接起来。）
	  select '保定' 地区,count(*) 数量,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary)工资总和,AVG(PeopleSalary) 平均工资
	  from People
	  where PeopleAddress = '保定' union
	  select '北京' 地区,count(*) 数量,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary)工资总和,AVG(PeopleSalary) 平均工资
	  from People
	  where PeopleAddress = '北京'
	--方案二：group by分组
	  select PeopleAddress 地区, count(*) 员工人数,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary)工资总和,AVG(PeopleSalary) 平均工资
	  from People
	  group by PeopleAddress
	--选择列表中的列只能是包含在聚集函数中的或者gruop by子句中，这里 PeopleName、PeopleId不可以加在选择列表里
	--（2）根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资，1985年及以后出生的员工不参与统计
	  select PeopleAddress 地区, count(*) 员工人数,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary)工资总和,AVG(PeopleSalary) 平均工资
	  from People
	  where PeopleBirth =2 --聚合函数条件写在group by后面，用having语句
 
 
10-多表查询一
 
    --多表查询
	select * from Department
	select * from People
	--笛卡尔乘积
	select * from People,Department
	--查询结果将Department所有记录和People表所有记录依次排列组合形成新的表
	--简单多表查询
	--查询员工信息，显示部门名称
	select * from People,Department
	where People.DepartmentId=Department.DepartmentId
	--查询员工信息，显示职级名称
	select * from People,Rank1
	where People.RankId=Rank1.RankId
	--查询员工信息，显示部门名称和显示职级名称
	select * from People,Department,Rank1
	where People.DepartmentId=Department.DepartmentId
	and People.RankId=Rank1.RankId
	--内连接查询
	--查询员工信息，显示部门名称
	select * from People 
	inner join Department on People.DepartmentId=Department.DepartmentId
	--查询员工信息，显示职级名称
	select * from People 
	inner join Rank1 on People.RankId=Rank1.RankId
	--查询员工信息，显示部门名称和显示职级名称
	select * from People 
	inner join Department on People.DepartmentId=Department.DepartmentId
	inner join Rank1 on People.RankId=Rank1.RankId
	--简单多表查询和内连接共同的特点：不符合主外键关系的数据不会被显示出来
	select * from People
	select * from Department 
	insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime )
	values (99,99,'张良','男','1975-8-9',8000,'13454432205','香港',getdate())
	--这条语句是不能插入到People表里面的，因为不符合主外键关系，DepartmentId和RankId不存在99号
    --外连接（左外连接、右外连接、全外连接）
	
	--左外连接：以左表为主表进行数据的显示，主外键关系找不到的数据用null取代
	--查询员工信息，显示部门名称
	select * from People 
	left join Department on People.DepartmentId=Department.DepartmentId
	--左连接 People在左边，显示所有的员工
	--查询员工信息，显示职级名称
	select * from People 
	left join Rank1 on People.RankId=Rank1.RankId
	--查询员工信息，显示部门名称和显示职级名称
	select * from People 
	left join Department on People.DepartmentId=Department.DepartmentId
	left join Rank1 on People.RankId=Rank1.RankId
	--右连：A left join B = B right join A
	--下面两个查询含义相同
	select * from People 
	left join Department on People.DepartmentId=Department.DepartmentId
   
	select * from Department
	right join People on People.DepartmentId=Department.DepartmentId
	--右连接 People在右边，显示所有的员工信息
	--全外连接：两张表的数据，无论是否符合关系，都要显示
	select * from People 
	full join Department on People.DepartmentId=Department.DepartmentId
	--所有的员工和所有的部门都要显示
 
11-多表查询二
 
欲知后事如何，且听下回分解。
 
持续更新......
 
2024.2.28 对不起大家，这篇我还得鸽一年。
 
大二下学期考完期末考试之后就没有再用sql server了，现在已经大三下学期了。
 
我正在准备25考研，科目408，所以目前也不会学数据库，这篇只能先搁置了。