数据库学习笔记

数据库（基于SQL Server）

T-SQL语句的代码

所有的基础部分我直接用单独一个sql文件讲完，追求最高效率
包括数据库的创建、文件更改、文件增加、表的增删改查、数据的增删改查、连接、子查询、视图、存储过程

	/*验证数据库是否存在，存在的话直接删除*/
if exists(select * from sys.databases where name = 'DBTEST')
drop database DBTEST
--创建数据库
create database DBTEST
--创建数据库 数据库的名称为DBTest
on(/*on 的意思就是创建数据文件，然后后面跟着的是括号
这个括号就是用来描述这个数据文件的，也就是写一些数据文件
基本的内容*/
name = 'DBTEST',
/*name就是逻辑名称，就是在数据库里面的代号
习惯上主数据文件的文件逻辑名称和数据库的名称一样
然后日志文件一般就是加上log
然后每两个描述句式之间要用逗号隔开*/
filename = 'D:\SqlS\SelfStudy\DBTest\DBTEST.mdf',
/*物理路径，就是实际上在计算机内的文件的文件路径和文件名
设置物理路径的时候不要忘记加上.mdf或者.ldf的后缀*/
size = 5mb,--设置初始大小
filegrowth = 2mb,
--filegrowth = 10%
/*文件的增长方式，文件的增长方式可以写大小
也可以写百分比。*/
maxsize = 10mb /*最大大小，可以不设置*/
)
log on(
/*同理 log on 用于编写日志文件
log内的描述方式和数据文件相同*/
name = 'DBTEST_log',
filename =  'D:\SqlS\SelfStudy\DBTest\DBTEST_log.ldf',
size = 5mb,
filegrowth = 2mb
)

--创建数据库简写
create database DBTEST1
--如果按照上述见到那的方法创建数据库，所有设置都采用默认值

/*==========以上为创建数据库相关语句============*/
 
/*==========创建数据表============*/

use DBTEST
/*正常的新建咨询会默认使用master的系统表
如果这个时候直接建表会建到master里面去
所以这里可以在左上角店家更改使用的数据库
也可以直接使用useDBTEST语句*/


/*删除表*/
drop table Department
drop table People
drop table [Rank]
/*删除表中的数据（表中的列）*/
DELETE FROM Department;
DELETE FROM People;
DELETE FROM [Rank];


/*创建表的基本语法
create table 表名
（字段名1 数据类型，
字段名2，数据类型）
*/
if exists(select * from sys.objects where name = 'Department'and type = 'U')
	drop table Department
/*建表（部门，职级，员工）*/
create table Department
( 
	DepartmentId int primary key identity(1,1),
	/*部门编号
	一般的表格都会加一个单独的编号
	一方面防止名称有重复的
	第二方面可以作为主键标识
	primary key 是设置主键
	作为唯一标识
	identity(1,1)自动增长，初始值1，增长步长为1*/
	DepartmentName nVarchar(50) not null, 
	/*部门的名称 字符串类型
	 其中有varchar和nvarchar
	 其中varchar（50）代表50个字符
	 前面加上n变成nvarchar代表50个中文字符或者50个英文字符
	 其中varchar代表可变长度，如果你输入的是一个字符
	 那么这个varchar（50）就是一个字符的长度和大小
	 但是如果是char类型的话就是定长，不管你输入了多少字符 
	 他都是50个字符的大小，所以用varchar大部分情况下更加的 灵活
	 而且可以节省资源*/
	 /*部门描述*/
	 DepartmentRemark text
	 /*text长文本,*/
)
--建立职级表
create table [Rank] 
/*这里 Rank是sql中的关键字  所以需要用[]*/
(
	RankId int primary key identity(1,1),
	RankName nvarchar(50) not null,
	RankRemark text
	/*分别床架按了职级的编号
	职级的名称、职级的描述*/
)

--建立员工表
create table People
(
	PeopleId int primary key identity(1,1),
	PeopleName  nvarchar(20) not null,
	PeopleGender nvarchar(1) default('男')check(PeoPleGender = '男'or PeoPleGender = '女')not null,
	/*这里通过check加了一个约束，设置性别的值只能为男或者女
	同时还添加了一个默认值，让这个性别默认为男*/
	PeopleBirth datetime not null,
	PeopleSalary  decimal(12,2) check(Peoplesalary >= 1000 and Peoplesalary <= 1000000)not null,
	PeoplePhone varchar(20) unique not null, 
	/*unique关键字用于创建唯一约束，首先他的本质也是一个约束
	然后唯一约束就是爆炸该列中所以的值都是唯一的，不能有重复的值*/
	PeopleAddress varchar(100),
	PeopleAddTime smalldatetime default(getdate()),  --添加时间
	/*这里用了一个getdate，是系统默认的函数，这个函数可以获取当前的时间
	*/
	/*添加外键*/
	DepartmentId int references Department(DepartmentId) not null,
	RankId int references [Rank](RankId) not null
/*添加这个reference字段就是添加了外键
然后添加了reference字段之后他就会自动去
外键对应的表中去寻找，如果找不到对应的值
他就不会添加
*/

)

/*关于getdate（）
在sqls中 getdate()是一个内置函数，用于获取对当前的系统日期和时间。它返回一个datetime类型的值，
表示当前的日期和时间
基本用法
select getdate() as currentdatetime
用于插入数据
在插入数据时，你可以使用getdate（）来记录当前的时间。例如
INSERT INTO Orders (OrderDate) VALUES (GETDATE());
***与其他函数结合使用
很多时候需要我们得到当前的年份月份，所以我们可以用getdate（）提取年月日
语法
select year(getdate()) as currentyear
select month(getdate()) as currentmonth
select day(getdate()) as currentday
*/
/*关于日期
datetime:
范围：1753-9999 年。
精度：可以存储的时间精度为 3.33 毫秒。
存储大小：8 字节。
适用场景：适合需要高精度和大范围的日期时间。
smalldatetime:
范围：1900-2079 年。
精度：可以存储的时间精度为 1 分钟。
存储大小：4 字节。
适用场景：适合对时间精度要求不高且范围较小的应用。
date:
范围：0001-9999 年（不包括时间部分）。
精度：只存储日期，不存储时间。
存储大小：3 字节。
适用场景：适合仅需要存储日期信息的场合，不需要时间。*/

/* 关于refence

在sqls中，reference关键字用于定义外键约束，它用于在一个表中建立对另一个表的引用。
外键约束的作用是确保了数字的一致性和完整性，它运行的逻辑就是你通过这个关键字添加的值
本质上是一种引用，你去引用别的表里面的数值，所以如果你通过这个关键字去添加列，再你输入值的时候
他就回去查找这个值在主表中是否存在，如果不存在就不会添加，也就是说没办法引用主表中不存在的数据*/

use DBTEST
/*===表结构和约束的维护=====*/
/*基础知识 sqls中的几种约束

主键约束（PRIMARY KEY）：
确保表中每一行的唯一性。
不允许为空（NULL），每个主键值必须唯一。

外键约束（FOREIGN KEY）：
用于在两个表之间建立关系。
确保外键列的值必须在参考表的主键或唯一键中存在，以保持数据的完整性。

唯一约束（UNIQUE）：
确保列中的所有值都是唯一的，但允许空值（NULL）。
可以应用于一个或多个列的组合。

非空约束（NOT NULL）：
确保列不能包含空值（NULL），强制要求输入数据。

检查约束（CHECK）：
限制列中允许的值范围，例如，可以要求某个年龄列的值必须大于 0。
可以使用表达式来定义条件。
默认约束（DEFAULT）：
为列设置一个默认值，当插入新行时如果未提供该列的值，将使用默认值。
*/


/*修改表结构*/

/*下面三个操作本质上都是在修改原有的表格的结构
所以都要用alter table起手
比如添加就是alter table 表名  add 新列名 数据类型
删除列的语法就是
alter table 表名 drop column 列名
drop 就不需要类型了 但是要加一个关键字
column 就是列的意思

修改列的语法
alter table 表名 alter column  列名 数据类型 


*/

/* 添加列*/
 /*假设现在给员工表添加一列邮箱*/
 alter table People add PeopleMail varchar(50)

/*删除列*/
alter table People drop column PeopleMail

/*3、修改列*/
alter table People alter column PeopleAddress varchar(40)
/*这种情况下 这种语句非常有可能报错
因为现在表中是空的没有数据，但是实际上如果有人输入了一个
超过40个字符的地址，那么这里执行语句的时候就会报错。*/

/*维护约束*/
--删除约束
/*删除约束他的本质也是要去修改表
所以这里也是要用alter table起手
语法 alter table 表名 drop constraint 约束名*/
--删除月薪的约束
alter  table People drop constraint CK__People__PeopleSa__5165187F

/*添加约束
添加check约束的语法
check约束是用来判断输入的值是不是我们想要的值
添加工资字段约束，工资必须在1000-一百万之间*/
alter table PeoPle add constraint Ck_PeoPle_Salary 
check(Peoplesalary >= 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 fey (列名)
reference 关联表名（列名（主键））
*/

/*===插入数据=====*/
--向部门表插入数据
/*insert into 表名 (列1,列2)
values('值1','值2')
*/

insert into Department(DepartmentName,DepartmentRemark)
values('市场部','...')
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','...')
insert into Department(DepartmentName,DepartmentRemark)
values('企划部','...') 
/*关于数据的插入还有一种简写*/
insert into Department
values ('硬件部','...')
/*但是这种简写一般不会用，因为这种简写有非常大的缺点
就是简写是根据默认的字段顺序写的，所以可能会遇到插入的值
插入错了列
比如上面的部门名和部门描述的值插入反了的情况
所以一般情况下不会用这种简写*/

/*一次性插入多行数据*/
insert into Department(DepartmentName,DepartmentRemark)
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,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,3,'刘备','男','1984-7-9',20000,'13554785452','成都',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'孙尚香','女','1987-7-9',15000,'13256854578','荆州',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,1,'关羽','男','1988-8-8',12000,'13985745871','荆州',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,1,'张飞','男','1990-8-8',8000,'13535987412','宜昌',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,3,'赵云','男','1989-8-8',9000,'13845789568','宜昌',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,3,'马超','男','1995-4-8',9500,'13878562568','香港',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'黄盖','男','1989-4-20',8500,'13335457412','武汉',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,1,'貂蝉','女','1989-4-20',6500,'13437100050','武汉',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,2,'曹操','男','1987-12-20',25000,'13889562354','北京',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,3,'许褚','男','1981-11-11',9000,'13385299632','北京',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,1,'典韦','男','1978-1-13',8000,'1347854263','上海',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,1,'曹仁','男','1998-12-12',7500,'13878523695','深圳',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,3,'孙坚','男','1968-11-22',11000,'13558745874','深圳',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,1,'孙策','男','1988-1-22',11000,'1355874874','深圳',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'孙权','男','1990-2-21',12000,'13698745214','深圳',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'大乔','女','1995-2-21',13000,'13985478512','上海',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,1,'小乔','女','1996-2-21',13500,'13778787874','北京',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'周瑜','男','1992-10-11',8000,'13987455214','武汉',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,3,'鲁肃','男','1984-9-10',5500,'13254785965','成都',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,3,'吕蒙','男','1987-5-19',8500,'13352197364','成都 ',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,1,'陆逊','男','1996-5-19',7500,'13025457392','南京',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'太史慈','男','1983-6-1',7500,'13077778888','上海',GETDATE())

insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'aaaa','男','1983-6-1',7500,'13177778888','上海',GETDATE())

use DBTEST
/*修改表格中的数据
语法
update 表名 set 字段1 = 值1, 字段2 = 值2 where  条件
*/ 
/*工资调整，每个人加薪一千块*/
update People set PeopleSalary = PeopleSalary+1000
/*这里一般要用where 来确定自己要更改的到底是哪一行数据
但是因为这里是要给所有人都加薪，所以无所谓*/

select * from People
/*上面的语句是用来直接查询People表中的所有 数据的*/
/*给黄盖一个人的工资加500*/
update People set PeopleSalary = PeopleSalary + 500 where PeopleName = '黄盖'
/*给员工编号为7的人减薪500*/
update People set PeopleSalary = PeopleSalary - 500 where PeopleId = 7
/*将软件部(部门编号1)人员工资低于15000的调整成15000*/
update PeoPle set PeoPleSalary = 15000 
where DepartmentId = 1 and PeopleSalary <15000

/*多字段修改
修改刘备的工资为以前的两倍，再把地址改为北京*/
update People set PeopleSalary = PeopleSalary*2,
PeopleAddress = '北京'
where PeopleName = '刘备'


/*删除数据的语法*/
/*
delete  from 表名 where 条件 

删除员工表所有记录
delete from People
*/

/*删除市场部 中 工资大于1w的人*/
delete from People
where PeopleSalary > 10000 and DepartmentId = 3

/*关于删除
drop,truncate,delete
drop table People 删除表对象
这个操作实际上是把整个表删除了
不单单是 删除了表中的数据，是连表格本身都删除掉了 
truncate table People 清空数据 表依然存在
delete from People 删除所有数据 表依然存在
truncate 和delete的区别
truncate直接删除所有数据，不可以带附加条件
不可以删除部分数据，直接清空表内容
delete可以带条件可以只删除部分数据
自动编号：
假设表中自动编号为12345
如果使用truncate之后添加数据 编号仍然是12345
使用delete删除数据，删除的自动编号永远不会在存在
如果用delete删除 之后添加数据就会变成67891

*/

/*======查询语句=====*/
/*查询基本语法
select * from 表名
查询所有列所有行
select 列名 from 表名 where 条件（用于 筛选行）
 
*/

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

/*查询指定列（姓名性别生日月薪电话）*/
select PeopleName as 姓名,
PeopleGender as 性别,
PeopleBirth as 生日,
PeopleSalary as 月薪,
PeoplePhone as 电话
from People

/*查询出员工都来自于那些城市城市*/
select distinct (PeopleAddress) 
 from People
 /*distinct 就是不重复显示相同的数据
 distinct的用法 就是distinct（列名）
 */

 /*假设准备加工资，现在还没有加
 然后现在想要咨询加工资后的员工的数据
 假设工资上调20%*/
select PeopleName as 姓名,
PeopleSalary*1.2 as 调薪后工资,
PeopleSalary as 原本工资,
PeopleSalary * 0.2 as 工资涨幅
from People

/*条件查询*/
/*
sql中常用的运算符
= 等于 比较是否相等及赋值
!= 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
is null 为空
is not null 不为空
in 比较是否在其中
like 模糊查询
between...and,,, 比较是否在两者之间
and 逻辑与
or 逻辑或
not 逻辑非
*/
use DBTEST

select * from People

/*查询性别为女的员工信息*/
select *
from People
where PeopleGender  = '女'

/*查询工资>=1w的员工信息*/
select * 
from People
where PeopleSalary >= 10000
/*查询出性别为女 工资大于等于1w的员工信息*/
select * 
from People
where PeopleGender = '女' 
and PeopleSalary >= 10000

select * 
from People
where PeopleGender = '女' 
and PeopleSalary >= 10000
and DepartmentId = 1

/*查询月薪大于等于1w或者月薪大于等于8k的女员工*/
select *
from People
where PeopleSalary >= 15000
or (PeopleSalary >= 8000
and PeopleGender = '女')

/*查询出出生年月在1980-1-1之后，而且月薪大于等于1w的女员工 
语法 select * from People where 条件1 and 条件2
*/
select * from People
where PeopleBirth >= '1980-1-1' 
and PeopleSalary >= 10000
and PeopleGender = '女'

/*查询月薪在1w-2w之间的员工信息*/
select * from People
where PeopleSalary >= 10000 and PeopleSalary <= 20000

select * from People
where PeopleSalary between 10000 and 20000

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


/*排序
查询所偶有的员工信息 工具工资排序 降序 
在默认的情况下 查询出来的虚表是工具主键来进行排序
在默认情况下  展现出来的结果是工具表格的主键来排序
比如在上面查询的语句中
People表格的主键是PeopleId 所以结果以此排序
（执行查询语句后 在下面的结果 部分
展现出来的表格就是一张虚表 
他不是真正存储在数据库中的表格 只是作为结果展示）

排序的关键字 order by
order by 就是根据 根据什么信息进行排序
默认情况下虚表根据主键排序
order by 列名 asc(升序） desc （降序）
其中 asc是默认值
只要用了order by 语法之后
就会按照你的列名 升序查询
*/

/*查询所有员工的信息 根据工资排序 降序 */
select * from People 
order by PeopleSalary  desc

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

/*在上方的代码中 len是sql提供给我们取长度的一个函数
len（） */

/*查询出工资最高的五个人的信息*/

select top 5 * from People
order by PeopleSalary desc

/*查询出工资最高的10%的员工信息*/
select top 10percent 
* from People
order by PeopleSalary desc
/*注意上面的查询百分之多少
不能直接用百分号% 而是要用percent
不然会报错
然后这个查询的结果会自动的四舍五入
比如一共有22条数据 就是四舍五入2条*/

/*null 空值*/
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddTime)
values(1,1,'马云','男','1977-7-7',500000,'138585858',GETDATE())

/*查询出地址没有填写的员工信息*/
select * from People
where PeopleAddress is null

select * from People
where PeopleAddress is not null

/*查询出80 后的员工信息*/
select * 
from People 
where PeopleBirth >= '1980-1-1'
and PeopleBirth <='1989-12-31'


select * 
from People 
where PeopleBirth between '1980-1-1'
and '1989-12-31'

select * 
from People
where year(PeopleBirth) between 1980 and 1989

/*查询30-40岁之间 并且 工资在1.5w - 3w 之间的员工信息*/
/* 年龄 =   当前年份 - 出生年份
age =  year(getdate) -  year (PeopleBirth)*/

select * 
from People
where (year(getdate()) - year(PeopleBirth) between 30 and 40)
and PeopleSalary between 15000 and 30000

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

/*查询出工资比赵云高的人的信息*/
/*首先要查询出赵云的工资*/
select PeopleSalary
from People
where PeopleName = '赵云'

select * 
from People
where PeopleSalary > 
(select PeopleSalary
from People
where PeopleName = '赵云')

/*查询出和赵云在一个城市的人的信息*/
select * 
from People 
where PeopleAddress 
= (select PeopleAddress
from People
where PeopleName = '赵云')


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 as 生肖
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 as 生肖
from People 


/*模糊查询*/
/*模糊查询使用like瓜庵案件之和通配符结合来实现
通配符如下
% 表示普配0个字符 1个字符或多个字符
_ 表示匹配有且仅有一个字符
[] 表示匹配范围内
[^] 表示不在匹配范围内
*/

/*%（百分号）：

％ 通配符代表零个或多个字符，意思是可以匹配任意数量（包括零个）字符。

例如：
SELECT * FROM students WHERE name LIKE 'J%';
这个查询将返回所有名字以 "J" 开头的学生，后面可以跟任意数量的字符（例如：James、John、Jack 等）。

另外，如果你写成 LIKE '%J%'，则会匹配所有包含字母 "J" 的名字。

_（下划线）：

_ 通配符代表 单个字符。它只能匹配一个字符，不管这个字符是什么。

例如：

SELECT * FROM students WHERE name LIKE '_o';
这个查询将返回所有名字的第二个字符是 "o" 的学生，例如 "Tom" 或 "Bob"。

如果使用 LIKE '_o_'，那么查询会返回所有名字中第二个字符是 "o"，且总长度为 3 的名字。

总结：
% 用来匹配 任意数量的字符，包括零个字符。
_ 用来匹配 单个字符。*/


/*查询出姓刘的员工信息 */
select * from People
where PeopleName like '刘%'

/*查询出名字中含有尚的员工信息*/
select * 
from People
where PeopleName like '%尚%' or PeopleName like '史'
/*用这种两个百分号的方式查询出来的结果
只是名字中有尚的 可以在前面也可以在后面*/

/*查询出姓刘的员工信息，名字是两个字*/
select * 
from People
where PeopleName like '刘_'
/*用这个下划线就能查出来 刘后面只有一个字的了*/
/*另外一种写法 */

select SUBSTRING('helko,world',3,1)

select * 
from People
where SUBSTRING(PeopleName,1,1) = '刘'
/*上面 的条件仅仅表达了第一个字是刘，没有表达名字有两个字组成
所以要加上一个条件*/
and LEN(PeopleName) = 2

/*查询名字最后一个字为香，名字一共是三个字的员工信息*/
select * 
from People
where SUBSTRING(PeopleName,3,1) = '香'
and len(PeopleName) = 3


select * 
from People
where PeopleName like '__香'

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

/*查询出电话开头 为138 第四位是 7或者8的 
最后一个号码是5*/
select *
from People
where  PeoplePhone like '138[7,8]%5'
/*这里就是用[]通配符选择*/


 
 /*查询出电话开头 为138 第四位是2-5之间的数字的 
最后一个号码是不是2和3*/

select *
from People
where  PeoplePhone like '138[2-5]%[^2,3]'

/*聚合函数*/
/*
sqls中的聚合函数主要有
count 求数量
max 求最大值
min 求最小值
sum 求和
avg 求平均值
*/

use DBTEST

/*1. COUNT() — 求数量
作用：COUNT() 用于计算表中某列或记录的数量。如果指定了列名，它会计算该列中非 NULL 值的数量；如果不指定列名，它会计算记录的总数。

用法：
计算某列非 NULL 值的数量：
SELECT COUNT(column_name) FROM table_name;
计算所有记录的数量（即使某列有 NULL 值）：

SELECT COUNT(*) FROM table_name;
示例：

SELECT COUNT(*) FROM employees;
-- 计算 employees 表中的记录总数
2. MAX() — 求最大值
作用：MAX() 用于返回指定列中的最大值。该列的数据类型必须支持比较（如数字、日期等）。

用法：
SELECT MAX(column_name) FROM table_name;
示例：
SELECT MAX(salary) FROM employees;
-- 获取 employees 表中工资的最大值
3. MIN() — 求最小值
作用：MIN() 用于返回指定列中的最小值。与 MAX() 类似，该列的数据类型必须支持比较。
用法：
SELECT MIN(column_name) FROM table_name;
示例：
SELECT MIN(salary) FROM employees;
-- 获取 employees 表中工资的最小值
4. SUM() — 求和
作用：SUM() 用于返回指定列的所有数值之和。它只能用于数值类型的列（如整数或浮动类型的列）。
用法：
SELECT SUM(column_name) FROM table_name;
示例：
SELECT SUM(salary) FROM employees;
-- 计算 employees 表中所有员工的工资总和
5. AVG() — 求平均值
作用：AVG() 用于返回指定列的平均值。它只适用于数值类型的列，返回一个浮动类型的结果。
用法：
SELECT AVG(column_name) FROM table_name;
示例：
SELECT AVG(salary) FROM employees;
-- 获取 employees 表中所有员工的平均工资
聚合函数的使用注意事项：
聚合函数通常与 GROUP BY 子句一起使用，来对数据进行分组。例如，计算每个部门的员工人数、最大工资等：
SELECT department, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM employees
GROUP BY department;
如果不使用 GROUP BY，这些函数会对整张表进行计算，返回单个结果。
结论
COUNT()：计算记录数量。
MAX()：返回最大值。
MIN()：返回最小值。
SUM()：返回总和。
AVG()：返回平均值。
这些聚合函数广泛应用于数据分析、报告生成和业务统计等场景中。*/




--求员工总人数
select count(*) as 员工人数
from People
/*count 的意思 就是求列数
有几列 结果就是多少*/
--求最大值，求最高工资
select max(PeopleSalary) as 最高工资
from People
--求最小时，求最小工资
select min(PeopleSalary) as 最低工资
from People


--求和，求所有员工的工资总和
select sum(PeopleSalary) as 工资总和
from People

--求平均值，求所有员工的平均工资
select AVG(PeopleSalary) as 平均工资
from People

--平均工资如果要只保留两位小数
/*通过round 关键字*/
SELECT ROUND(AVG(PeopleSalary), 2) AS 平均工资
FROM People;



--求数量，最大值，最小值，总和，平均值，在一行显示
Select count(*) as 员工人数,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资,
sum(PeopleSalary) as 工资总和,
avg(PeopleSalary) as 平均工资
from People

/*查询出武汉地区的员工人数，总工资，最高工资，最低工资和平均工资*/
Select count(*) as 员工人数,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资,
sum(PeopleSalary) as 工资总和,
avg(PeopleSalary) as 平均工资
from People
where PeopleAddress = '武汉' 

--求出工资比平均工资高的人员信息
--思路 先求出平均工资
select AVG(PeopleSalary) from People
--再通过where判断
select *
from People
where PeopleSalary > 
(select AVG(PeopleSalary) from People)

/*求数量，年龄最大值，最小值，总和，平均值
在一行内显示*/

--求年龄
select (year(getdate()) - year(PeopleBirth))
from People 
/*求上题*/

/*方案一*/
select count(*) as 员工总数,
max(year(getdate()) - year(PeopleBirth)) as 最高年龄,
min(year(getdate()) - year(PeopleBirth)) as 最低年龄,
sum(year(getdate()) - year(PeopleBirth)) as 年龄总和,
avg(year(getdate()) - year(PeopleBirth)) as 平均年龄
from People

/*方案2*/
/*使用datediff算日期*/
select datediff(YEAR,'1991-1-1','1993-3-3')

/*用这个datediff算出年龄*/
select PeopleName as name,
datediff(YEAR,PeopleBirth,GETDATE()) as age
from People

select count(*) as 员工总数,
max(datediff(YEAR,PeopleBirth,GETDATE())) as 最高年龄,
min(datediff(YEAR,PeopleBirth,GETDATE())) as 最低年龄,
sum(datediff(YEAR,PeopleBirth,GETDATE())) as 年龄总和,
avg(datediff(YEAR,PeopleBirth,GETDATE())) as 平均年龄
from People
/*datediff是一个日期函数，用于计算两个日期之间的差异。
它返回两个日期之间的天数差。
DATEDIFF 会返回一个整数值，表示 date1 和 date2 之间的天数差。
如果 date1 比 date2 晚，则返回正数；
如果 date1 比 date2 早，则返回负数；
如果两个日期相同，则返回 0*/

/*计算出月薪在10000 以上的男性员工的最大年龄 最小年龄和平均年龄*/
select count(*) as 员工总数,
max(datediff(YEAR,YEAR(getdate()),PeopleBirth) ) as 最高年龄,
min(datediff(YEAR,YEAR(getdate()),PeopleBirth) ) as 最低年龄,
sum(datediff(YEAR,YEAR(getdate()),PeopleBirth) ) as 年龄总和,
avg(datediff(YEAR,YEAR(getdate()),PeopleBirth) ) as 平均年龄
from People
where PeopleSalary > 10000
and PeopleGender = '男'

/*统计出所在地在 武汉或上海的 所有女员工数量以及
最大年龄 最小年龄和平均年龄*/
/*正常做法*/
select count(*) as 女员工数量,
max(year(GETDATE()) - year(PeopleBirth)) as 最大年龄,
min(year(GETDATE()) - year(PeopleBirth)) as 最小年龄,
avg(year(GETDATE())- year(PeopleBirth)) as 平均年龄
from People
where PeopleGender = '女'
and PeopleAddress in ('上海','武汉')


--年龄
select DATEDIFF(year,year(GETDATE()),PeopleBirth)
from People

select count(*) as 员工总数,
max(datediff(YEAR,PeopleBirth,GETDATE())) as 最高年龄,
min(datediff(YEAR,PeopleBirth,GETDATE())) as 最低年龄,
sum(datediff(YEAR,PeopleBirth,GETDATE())) as 年龄总和,
avg(datediff(YEAR,PeopleBirth,GETDATE())) as 平均年龄
from People
where PeopleGender = '女'
and PeopleAddress in ('武汉','上海')

select count(*) as 女员工总数,
max(year(getdate()) - year(PeopleBirth)) as 最高年龄,
min(year(getdate()) - year(PeopleBirth)) as 最低年龄,
sum(year(getdate()) - year(PeopleBirth)) as 年龄总和,
avg(year(getdate()) - year(PeopleBirth)) as 平均年龄
from People
where PeopleGender = '女'
and PeopleAddress in ('武汉','上海')

--求出年龄比平均年龄的人员信息
/*求年龄*/
select (year(getdate()) - year(PeopleBirth))
from People 
/*求平均年龄*/
select avg(year(getdate()) - year(PeopleBirth)) as 平均年龄
from People 

/* */
select *,
(year(getdate()) - year(PeopleBirth)) as 年龄
from People
where
(year(getdate()) - year(PeopleBirth))
> (select avg(year(getdate()) - year(PeopleBirth)) as 平均年龄
from People) 




/*======分组查询=====*/
/*什么是分组查询？
为什么要用分组查询？
什么时候要用分组查询？*/



--根据员工所在地区分组统计员工人数,员工工资总和,平均工资，最高工资和最低工资
select count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) 平均工资,
max(PeopleSalary)  最高工资,
min(PeopleSalary) 最低工资
from People 

/*如上面这道题 按这样的办法我们只能查出来一条信息
然后题目现在让我们根据地区进行分组
在引入group by之前 能用的方法就是union*/
/*使用union做*/
select '北京' 地区,
count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) 平均工资,
max(PeopleSalary)  最高工资,
min(PeopleSalary) 最低工资
from People 
where PeopleAddress = '北京'
union
select '武汉' 地区,
count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) 平均工资,
max(PeopleSalary)  最高工资,
min(PeopleSalary) 最低工资
from People 
where PeopleAddress = '武汉'
/*如果不使用group by 分组查询
只能不断 通过这个union 
把每一个地区的信息都咨询出来
=========注意==========
在通过union合并的时候
并不是所有的查询结果都可以合并
使用union要求两个查询结果的列的数量必须一致
同时数据类型也要求相同*/

/*方案二 使用group by*/
select PeopleAddress 地区,
count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) 平均工资,
max(PeopleSalary)  最高工资,
min(PeopleSalary) 最低工资
from People 
Group by PeopleAddress
/*如果在上面加上一行PeopleName
就会报错
因为group by 
只能显示
聚合函数的结果
或者 group by 本身的字段
其他的字段是无法显示的
从逻辑上面也说不过去
*/



--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--1985 年及以后出身的员工不参与统计。
select count(*) as 员工人数,
sum(PeopleSalary) as 工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资
from People
where year(PeopleBirth) < 1985
group by PeopleAddress



--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--要求筛选出员工人数至少在2人及以上的记录，并且1985年及以后出身的员工不参与统计。

/*
select count(*) as 员工人数,
sum(PeopleSalary) as 工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资
from People
where year(PeopleBirth) < 1985
and 员工人数 >= 2
group by PeopleAddress
一开始想到的是这种写法
然后这里报错 
显示 聚合不应该出现在where字句中
所以 where 子句中不能出现聚合函数
只能出现原始数据
所以这个时候我们就需要使用having 关键字
*/

/*用下面的做法就正常了*/
select count(*) as 员工人数,
sum(PeopleSalary) as 工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资
from People
where year(PeopleBirth) < 1985
group by PeopleAddress
having count(*)  >=2

/*然后联想到 如果不是聚合函数可不可以在having里面
比如说我把那个年龄的判断放在having里面*/

select count(*) as 员工人数,
sum(PeopleSalary) as 工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资
from People
group by PeopleAddress
having count(*)  >=2
and year(PeopleBirth) < 1985
/*上面的代码还是报错
HAVING 子句中的列 'People.PeopleBirth' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
说明没有包含聚合函数或者group by 的一般数据
having同样不接收*/

/*多表查询*/

select * from
Department
select * from
People
/*笛卡尔乘机*/
select * from 
People,Department
/*咨询结果将Department 所有记录
和People表所有记录依次排列组合

这个笛卡尔积 他不考虑这么多
直接将所有的数据相乘
之前的外键他也不考虑
所以这里出现了不应该出现的冗余数据
比如说每个人都有属于每个部门的数据
比如说刘备真实情况下属于软件部的
但是在这个笛卡尔积中既有属于软件部的刘备 
也有属于硬件部乃至其他所有部门的刘备
这样明显是不合理不科学的
所以这里要做的事情 就是
让员工表中的department id
和部门表中的department id 相同
关联 之后
再去显示他部门的id

之前有说到在建表的时候 通过创建外键的方式
关联了这个department id
所以部门表中没有的department id
无法在员工表中被创建 
这就使得在插入数据的时候就保证了数据是一一对应的

tips 外键可以为空 就是说这里 原则上可以存在部门为null的情况
不过我们在上面建表的时候加了not null的非空约束 */


/*简单多表查询*/
/*查询员工信息 显示部门名称*/
select * 
from People,
Department
where People.DepartmentId = Department.DepartmentId
/*通过DepartmentId 连接两张表
使得两张表中的数据对应*/

/*查询出员工信息 显示职级名称*/
select * ,
RankName as 职级名称
from People,
[Rank]
where People.RankId = [Rank].RankId


/*查询出员工信息 显示部门名称，职级名称*/
select *,
DepartmentName,
RankName
from
People,
Department,
[Rank]
where People.DepartmentId = Department.DepartmentId
and People.RankId = [Rank].RankId


--猜想 如果把where中的语句等号两边换位
select *,
DepartmentName,
RankName
from
People,
Department,
[Rank]
where Department.DepartmentId = People.DepartmentId
and [Rank].RankId = People.RankId
/*得出结论 等号两边换位置不影响结果*/

/*直接把所有数据显示出来*/
select * from
People,
Department,
[Rank]
where People.DepartmentId = Department.DepartmentId
and People.RankId = [Rank].RankId

/*内连接查询*/

/*内连接（INNER JOIN）是一种常用的连接方式，
指的是只返回在两个表中都有匹配的记录。
在下面的咨询中，INNER JOIN会返回People表与
Department、Rank表中符合条件的所有行。
只要存在匹配行，结果才会返回。
同时如果遇到People中的DepartmentId为空值的情况下
就不会返回记录何结果 
但是因为我在前面添加了非空约束和外键约束
所以一不会出现不对应的情况 二不会出现空值的情况	*/

/*咨询员工信息  显示部门名称 */
select * 
from People
inner join Department on People.DepartmentId = Department.DepartmentId
/**/


/*咨询员工信息  显示职级名称 */
select * 
from People
inner join [Rank] on People.RankId = [Rank].RankId


/*咨询员工信息 显示部门名称 显示职级名称*/
select * 
from People
inner join Department on People.DepartmentId = Department.DepartmentId
inner join [Rank] on People.RankId = [Rank].RankId

/*简单多表咨询和内连接共同的特点：
不符合主外键关系的数据不会被显示出来
也就是我上面说的  不满足上面约束的数据 咨询不到
*/
/*为了测试下面的外连接 和这里的内连接  现在我要手动删除外键约束
选择people表 然后选择关系 然后删除FK 接着插入下面的数据
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values（99,99,'abc','男','1975-8-9','8000,'13556857548','香港',getdate()）
*/
insert into People(DepartmentId,RankId,PeopleName,PeopleGender,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(99,99,'abc','男','1975-8-9',8000,'1351123465','香港',getdate())

/*插入成功后 首先我们先来进行查询 会发现我们查询不到abc这条数据
说明不满足主外键关系的数据不会被显示在内连接和简单多表咨询里面
这两种连接 的本质就是根据主外键关系连接*/
select * 
from People
inner join Department on People.DepartmentId = Department.DepartmentId
inner join [Rank] on People.RankId = [Rank].RankId
/**/


/*外连接
外连接分成三类
左外连接
右外连接 
全外连接*/


/*咨询员工信息  显示部门名称 */
/*左外连接*/
select * from People
left join Department on People.DepartmentId = Department.DepartmentId
/*首先左外连接的语法就是把内连接的inner改成left
然后咨询之后发现这个 数据的变化就是
左边出现了上面添加的abc

然后我们进行以下分析

什么是左表？怎么看？
上面的左外连接 可以写成
People left join Department
这里 People表在left join 语句的左边 所以是左表
通过left join 语句分成左右两张表

什么是左外连接？
左外连接就是把左表的数据当主要参考点
也就是说左表中的每一列数据
都要在右表找到对应的值
在这个例子中
People是主表
abc这列数据在people中有 
所以生成的虚表中一定会有这一列
然后在department表中又找不到这一列对应的数据
所以就用null来表示

所以这类左外连接适用的范围就是对于左表中的每一行数据
都必须要找到他的对应关系的情况
*/

select * from Department
left join People on People.DepartmentId = Department.DepartmentId
/*以department 作为主表
那么所有的部门都会显示出来
同时 没有人的部门会用null的样子来表示*/

/*咨询员工信息  显示职级名称 */
select * 
from People
left join [Rank] on People.RankId = [Rank].RankId


/*咨询员工信息 显示部门名称 显示职级名称*/
select * 
from People
left join Department on People.DepartmentId = Department.DepartmentId
left join [Rank] on People.RankId = [Rank].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

/*全外连
两张表的数据 无论是否符合关系 都要显示
在这个表的关系中
如果说left join 可以显示说有的人（无论这个人是否有部门
right join 可以显示所有的部门（无论部门是否有人
那么 full join 既可以显示所有的人 又可以显示 所有的部门
*/
select * from 
People full join Department on People.DepartmentId = Department.DepartmentId

/*多表查询综合实例*/
 /*查询出武汉地区所有员工信息，要求显示部门名称以及员工的详细资料*/
 select * 
 from People
 inner join Department on People.DepartmentId=Department.DepartmentId
 where PeopleAddress = '武汉'

  select * 
 from People
 left join Department on People.DepartmentId=Department.DepartmentId
 where PeopleAddress = '武汉'
 /*这里应该用哪一种连接方式 
 如果用inner join 则如果有人位于武汉但是没有部门名称不会被显示
 如果用left join 则没有部门名称会显示null*/


 /*咨询处武汉地区所有员工信息，要求显示部门名称
 职级名称 以及员工的详细 资料*/
 /*这种情况就不需要考虑 肯定是用left join
 因为可能出现有人没有部门编号 但是有职业级编号 的情况 */
 select PeopleName as 姓名,
 DepartmentName as 部门名,
 RankName as 职级
 from People left join Department on People.DepartmentId = Department.DepartmentId
 left join [Rank] on People.RankId = [Rank].RankId
 where PeopleAddress = '武汉'

  select PeopleName as 姓名,
 DepartmentName as 部门名,
 People.DepartmentId as 部门编号,/*
 如果要写下面连接的子句的话 
 一定要说明这个是左表还是右表里面的数据
 虽然左表和右表里面到店数据理论上是相同的*/
 RankName as 职级
 from People left join Department on People.DepartmentId = Department.DepartmentId
 left join [Rank] on People.RankId = [Rank].RankId
 where PeopleAddress = '武汉'
 
 /*根据部门分组同居员工人数
 员工工资总和 平均工资 最高最低工资*/
select 
count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资,
DepartmentName as 部门名称
from People
left join Department on People.DepartmentId = Department.DepartmentId
group by Department.DepartmentId,Department.DepartmentName
/*这样会发现 有一条错误的数据 没有部门名称 不希望显示 所以改成内连接*/

select 
count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资,
DepartmentName as 部门名称
from People
inner join Department on People.DepartmentId = Department.DepartmentId
group by Department.DepartmentId,DepartmentName

/*根据部门分组统计员工人数
员工工资总和 平均工资 最高和最低工资
平均工资10000以下的不参与统计 并且根据平均工资降序排列*/
select count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资,
DepartmentName as 部门名称
from People
inner join Department on People.DepartmentId = Department.Departmentid
group by People.DepartmentId,DepartmentName
having avg(PeopleSalary) >= 10000
order by 平均工资 desc


/*根据部门名称 然后根据职位名称
分组统计员工人数 员工工资总和 平均工资 最高最低工资*/
select count(*) as 员工人数,
sum(PeopleSalary) as 员工工资总和,
avg(PeopleSalary) as 平均工资,
max(PeopleSalary) as 最高工资,
min(PeopleSalary) as 最低工资,
DepartmentName as 部门名称,
Rankname as 职级
from People
inner join Department on People.DepartmentId = Department.Departmentid
inner join [Rank] on People.RankId = [Rank].RankId
group by People.DepartmentId,People.RankId,DepartmentName,RankName
having avg(PeopleSalary) >= 10000
order by 平均工资 desc

/*自连接
自己连自己*/
create table Dept
(
DeptId int Primary key , --部门编号
DeptName varchar(50),--部门名称
ParentId int,--上级部门编号

)

/*
DeptId DeptName ParentId
1       软件部    0
2		硬件部	  0
3		软件研发部 1
4		硬件测试部 2
*/
/*一级部门*/
insert into Dept(DeptId,DeptName,ParentId)
values (1,'软件部',0)
insert into Dept(DeptId,DeptName,ParentId)
values (2,'硬件部',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
/*要查询每个部门的上级部门*/
select A.DeptId as 部门编号,
A.DeptName as 部门名称,
B.DeptName as 上级部门
from Dept A
left join Dept B on A.ParentId = B.DeptId



/*视图*/
/*视图  可以理解陈 虚拟表
*/



/*存储过程*/

其他课堂的代码

建库表

-- 创建数据库 DB1
CREATE DATABASE DB1
ON
(
    NAME = DB1_data,
    FILENAME = 'D:\SQL2019\数据库代码\Y24M9D29\DB1_data.mdf',
    SIZE = 30MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = DB1_log,
    FILENAME = 'D:\SQL2019\数据库代码\Y24M9D29\DB1_log.ldf',
    SIZE = 10MB,
    FILEGROWTH = 20MB
);
GO

-- 检查数据库
EXEC sp_helpdb DB1;
GO

-- 添加一个数据文件 DB1_data2，位置自定义，其他设置采用默认
ALTER DATABASE DB1
ADD FILE
(
    NAME = DB1_data2,
    FILENAME = 'D:\SQL2019\数据库代码\Y24M9D29\DB1_data2.ndf'
);
GO

-- 将数据文件 DB1_data2 扩大到 20MB
ALTER DATABASE DB1
MODIFY FILE
(
    NAME = DB1_data2,
    SIZE = 20MB
);
GO

-- 使用数据库 DB1
USE DB1;
GO

-- 创建表格 StudentT
CREATE TABLE StudentT (
    sno INT NOT NULL,
    sname VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL DEFAULT '男',
    birthday DATE NOT NULL,
    IDcard CHAR(18) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(20) NULL,
    CONSTRAINT pk_Student PRIMARY KEY (sno),
    CONSTRAINT uq_IDcard UNIQUE (IDcard),
    CONSTRAINT chk_sex CHECK (sex IN ('男', '女')),
    CONSTRAINT chk_birthday CHECK (birthday < GETDATE()),
    CONSTRAINT chk_phone_email CHECK (phone IS NOT NULL OR email IS NOT NULL)
);
GO

-- 删除主键约束
ALTER TABLE StudentT
DROP CONSTRAINT pk_Student;
GO

-- 将学号字段的数据类型修改为定长8位字符型
ALTER TABLE StudentT
ALTER COLUMN sno CHAR(8) NOT NULL;
GO

-- 将学号字段的数据类型修改为 int 型
ALTER TABLE StudentT
ALTER COLUMN sno INT NOT NULL;
GO

-- 重新创建主键约束
ALTER TABLE StudentT
ADD CONSTRAINT pk_Student PRIMARY KEY (sno);
GO

-- 创建选课表 SC
CREATE TABLE SC (
    sno INT NOT NULL,
    cno INT NOT NULL,
    score DECIMAL(5, 2) NULL,
    CONSTRAINT pk_SC PRIMARY KEY (sno, cno),
    CONSTRAINT chk_score CHECK (score BETWEEN 0 AND 100),
    CONSTRAINT fk_sno FOREIGN KEY (sno) REFERENCES StudentT(sno)
);
GO




--创建表格StudentT
--学号：sno	int型		不允许空
--姓名：sname	变长20位字符型	不允许空 
--性别：sex	定长2位字符型	不允许空 
--生日：birthday	日期型		不允许空


-- 检查表是否存在，若存在则删除
IF OBJECT_ID('dbo.StudentT', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.StudentT;
END
GO

-- 创建表格 StudentT
CREATE TABLE StudentT (
    sno INT NOT NULL,                 -- 学号：int 型，不允许空
    sname VARCHAR(20) NOT NULL,      -- 姓名：变长 20 位字符型，不允许空
    sex CHAR(2) NOT NULL,            -- 性别：定长 2 位字符型，不允许空
    birthday DATE NOT NULL,           -- 生日：日期型，不允许空
    CONSTRAINT pk_Student PRIMARY KEY (sno)  -- 设置学号为主键
);





--增加一个字段：身份证号码IDcard	定长18位字符型	不允许空A
ALTER TABLE StudentT
ADD IDcard CHAR(18) NOT NULL;



--增加2个字段：电话phone	变长20位字符型	允许空
--	        邮箱email	变长20位字符型	允许空
ALTER TABLE StudentT
ADD phone VARCHAR(20) NULL,  -- 增加电话字段，变长20位字符型，允许空
    email VARCHAR(20) NULL;  -- 增加邮箱字段，变长20位字符型，允许空

--删除一下主键约束
ALTER TABLE StudentT DROP CONSTRAINT pk_Student;


--将学号字段的数据类型修改为定长8位字符型
ALTER TABLE StudentT
ALTER COLUMN sno CHAR(8) NOT NULL;



--将学号设置为主键
ALTER TABLE StudentT
ADD PRIMARY KEY(sno);


--删除与 sno 列相关的主键约束
ALTER TABLE StudentT DROP CONSTRAINT PK_sno;

--将学号字段的数据类型修改为int型
ALTER TABLE StudentT
ALTER COLUMN sno INT NOT NULL;

 ALTER TABLE StudentT
 ADD PRIMARY KEY(sno)

--将身份证号设为惟一
ALTER TABLE StudentT
ADD IDcard VARCHAR(18);
 

--将性别默认设为“男”
ALTER TABLE StudentT
ADD CONSTRAINT DF_sex DEFAULT ''



--将性别字段规定为只能是“男”和“女”
ALTER TABLE StudentT
ADD CONSTRAINT CK_sex CHECK (sex IN('男', '女'));


--将生日字段规定为必须小于今天
ALTER TABLE StudentT
ADD CONSTRAINT CK_birthday CHECK (birthday < GETDATE());

--要求电话号码和邮箱不能同时为空
ALTER TABLE StudentT
--加上 with nocheck 达到的效果就是前面的情况不管看（非唯一约束）
ADD CONSTRAINT CK_phone_email CHECK (phone is not null or email is not null);


--创建一张选课表SC，包含字段：
--学号：sno	int型		不可为空
--课程号：cno	int型		不可为空
--成绩：score	可保留2位小数	可为空
create table sc(
sno int not null,
cno int not null,
score decimal(5,2)null,);


--将学号和课程号设为主键
alter table sc
add constraint pk_SC primary key (sno,cno);

--将成绩规定为0~100之间
alter table sc
add constraint ck_score check(score between 0 and 100);

--设置学号为外键
alter table sc
add constraint fk_sno foreign key (sno) references StudentT(sno);

--创建S表格
/*表名 S

列名 数据类型 长度 是否允许为空 默认值 说明
sid  char  8  不允许为空 主键
sname varchar 20 不允许 无
sex char 2 不允许 男 男，女
birthday date 3 不允许 无 必须小于今天
IDCard char 18 不允许  无 必须唯一
phone varchar 20 允许 无
email varchar 20 允许 无 Phone与email不能同时为空*/

CREATE TABLE S (
    sid CHAR(8) NOT NULL PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL CHECK (sex IN ('男', '女')),
    birthday DATE NOT NULL CHECK (birthday < GETDATE()),
    IDCard CHAR(18) NOT NULL UNIQUE,
    phone VARCHAR(20) NULL,
    email VARCHAR(20) NULL,
    CONSTRAINT CK_PhoneEmail CHECK (phone IS NOT NULL OR email IS NOT NULL)
);
--查看建表情况
EXEC sp_help 'S';

数据操作

--向学生表StudentT(在DB1数据库中）中添加字段“联系地址address	变长50位字符型	可为空	默认为‘杭州市临安区青山湖街道杭电路1号’”
USE DB1;  -- 切换到 DB1 数据库

ALTER TABLE StudentT
ADD address VARCHAR(50) NULL DEFAULT '杭州市临安区青山湖街道杭电路1号';


--向学生表中添加字段编号：id		自动增长列	不可为空

ALTER TABLE StudentT
DROP CONSTRAINT [PK__StudentT__DDDF644604DB6235];


USE DB1;  -- 切换到 DB1 数据库

ALTER TABLE StudentT
ADD id INT IDENTITY(1,1) NOT NULL PRIMARY KEY;
--出了点问题 清除一下
DELETE FROM StudentT;



--向学生表StudentT中插入以下数据
--学号	姓名	性别	生日		身份证号		电话		邮箱		地址
--1	s1	男	1999-1-2		11111111	                88166401	                s1@163.com	杭州	
--2	s2	女	1999-12-31	22222222		88166402		不知		不知	
--3	s3	默认	1998-3-4		33333333		不知		s3@163.com	不知
INSERT INTO StudentT (sno, sname, sex, birthday, IDcard, phone, email, address)
VALUES (1, 's1', '男', '1999-01-02', '11111111', '88166401', 's1@163.com', '杭州');

INSERT INTO StudentT (sno, sname, sex, birthday, IDcard, phone, email, address)
VALUES (2, 's2', '女', '1999-12-31', '22222222', '88166402', '不知', '不知');

INSERT INTO StudentT (sno, sname, sex, birthday, IDcard, phone, email, address)
VALUES (3, 's3', '默认', '1998-03-04', '33333333', '不知', 's3@163.com', '不知');


ALTER TABLE StudentT ALTER COLUMN sex VARCHAR(4);  

EXEC sp_help 'StudentT';  



--向表中继续插入数据：
--学号	姓名	性别	生日		身份证号		电话		邮箱		地址
--4	s4	中	2999-1-2		11111111	                不知		不知		不知
--请观察成功插入后的编号值
INSERT INTO StudentT (sno, sname, sex, birthday, IDcard, phone, email, address)
VALUES (4, 's4', '中', '2999-01-02', '11111111', null, null, null);

SELECT * FROM StudentT;


--删除4号学生
DELETE FROM StudentT WHERE sno = 4;
--DELETE FROM StudentT WHERE sno = 3;老师上课的代码 


--将2号同学的邮箱改为“s2@163.com”
UPDATE StudentT 
SET email = 's2@163.com' 
WHERE sno = 2;

--将3号同学的电话改为“88166403”
UPDATE StudentT 
SET phone = '88166403' 
WHERE sno = 3;

简单查询

--使用pubs数据库完成下面操作
--检索title表中的所有列
select * from titles
--检索所有书的书的编号、书名、类型、价格
select title_id,title,type,price
from  titles
--在titles表中检索所有书的编号、单价、打7折后的价格
SELECT title_id as '标号', price as '原价', price * 0.7 AS '7折后的价格' FROM titles;
--在authors表中检索所有作者的编号、作者的姓名（firstname与lastname的组合）
SELECT au_id, CONCAT(au_fname, ' ', au_lname) AS full_name FROM authors;
--查询作者来自哪些州
SELECT DISTINCT state FROM authors;
--查询作者来自哪些州、哪些城市
SELECT DISTINCT state, city FROM authors;
--查询前面6本书的所有信息
SELECT TOP 6 * FROM titles;
--查询前10%的书的所有信息
SELECT TOP 10 PERCENT * FROM titles;
--查询前面10本书的编号和单价
SELECT TOP 10 title_id, price FROM titles;
--查询书的全部信息，按书的类型和价格升序排列
SELECT * FROM titles ORDER BY type ASC, price ASC;
--查询书的全部信息，按书的类型和价格降序排列
SELECT * FROM titles ORDER BY type DESC, price DESC;
--查询单价最高的10本书的信息
SELECT TOP 10* FROM titles ORDER BY price DESC;
--查询单价最高的5本书的信息（显示并列项）
SELECT TOP 5 WITH TIES *  --这个就是显示并列项
FROM TITLES
ORDER BY price DESC

--检索编号为’F-C16315M’的员工的编号、姓、名、雇佣日期
SELECT emp_id, lname, fname, hire_date FROM employee WHERE emp_id = 'F-C16315M';
--查询authors表中所有居住在UT州的作者姓名和电话。（作者的姓名由au_fname和au_lname组合成，中间以空格分隔）
SELECT au_fname+ ' '+ au_lname AS '姓名',phone  as '电话'
FROM authors 
WHERE state = 'UT';
--查询单价小于10的书的所有信息
SELECT * 
FROM titles 
WHERE price < 10;
--查询类型为’business’的书的书名和单价
SELECT title, price 
FROM titles 
WHERE type = 'business';
--从表 titles 中，查找书名中包含字符串“computer”的书名。
SELECT title 
FROM titles 
WHERE title LIKE '%computer%';
--从表 authors 中，查找au_fname以字符“D”打头的作者的姓、名。
SELECT au_lname, au_fname 
FROM authors 
WHERE au_fname LIKE 'D%';
--从表 authors 中，查找au_id类似于”72?-?”格式的作者的编号、姓、名、电话。
SELECT au_id, au_lname, au_fname, phone 
FROM authors 
WHERE au_id LIKE '72_-%';
--从表 authors 中，查找au_id的第2个数字是7的作者的编号、姓、名、电话。
SELECT au_id, au_lname, au_fname, phone 
FROM authors 
WHERE au_id LIKE '_7%';
--从表 authors 中，查找au_id只能是”723-?”、 ”724-?”、 ”725-?”格式 的作者的编号、姓、名、电话。
SELECT au_id, au_lname, au_fname, phone 
FROM authors 
WHERE au_id LIKE '723-%' OR au_id LIKE '724-%' OR au_id LIKE '725-%';

SELECT au_id, au_lname, au_fname, phone 
FROM authors 
WHERE au_id LIKE '72[3-5]-%'
--从表 authors 中，查找au_id的第1个数字是5-9中的一个，第2个数字是1-4中的一个的作者的编号、姓、名、电话。
SELECT au_id, au_lname, au_fname, phone 
FROM authors 
WHERE au_id LIKE '[5-9][1-4]%';
--从表 authors 中，查找au_id的第1个数字不是5-9中的一个，第2个数字不是1-4中的一个的作者的编号、姓、名、电话。
SELECT au_id, au_lname, au_fname, phone 
FROM authors 
WHERE au_id NOT LIKE '[5-9][1-4]%';
SELECT au_id, au_lname, au_fname, phone 
FROM authors 
WHERE au_id LIKE '[^5-9][^1-4]%';
--查找书名中含有“computer”，或书的编号以“B”打头的书，且价格低于15的书的信息。
SELECT * 
FROM titles 
WHERE (title LIKE '%computer%' OR title_id LIKE 'B%') 
  AND price < 15;
--从titles表中查询单价在15美元到20美元之间的书名和单价
SELECT title, price 
FROM titles 
WHERE price BETWEEN 15 AND 20;

SELECT title, price 
FROM titles 
WHERE price >=15 and price <=20;

SELECT title, price 
FROM titles 
WHERE price not BETWEEN 15 AND 20;

SELECT title, price 
FROM titles 
WHERE price < 15 or price >20;

--从表 authors 中搜索state位于‘CA’,‘KS’,‘MI’,‘IN’的作者姓名和所在州。
SELECT CONCAT(au_fname, ' ', au_lname) AS full_name, state 
FROM authors 
WHERE state IN ('CA', 'KS', 'MI', 'IN');

SELECT CONCAT(au_fname, ' ', au_lname) AS full_name, state 
FROM authors 
WHERE state ='CA' or state =  'KS'or state =  'MI'or state =  'IN';

--查询表 titles 中还没有定价格的书名和价格。
SELECT title, price 
FROM titles 
WHERE price IS NULL;

use pubs

use gradeManager
--自我练习部分（使用GradeManager数据库完成下面操作）
--（1）查询班级表（Class）的全部内容；
SELECT * 
FROM Class;
--（2）在学生表Student中查询学生姓名、所在班级。
SELECT sname, Clno 
FROM Student;
--（3）找出01311班女学生的个人信息；
SELECT * 
FROM Student 
WHERE Clno = '01311' AND Ssex = '女';
--（4）找出所有被学生选修了的课程号；
SELECT distinct cno
from Grade


--（5）找出01311班和01312班的学生姓名、性别、出生年份；
SELECT sname, Ssex,year(GETDATE())-sage as'出生年份'  
FROM Student 
WHERE Clno IN ('01311', '01312');
--（6）找出所有姓李的学生的个人信息。
SELECT *
FROM Student
WHERE sname LIKE '李%';  -- 使用LIKE语句来查找姓李的学生

聚合与分组查询

--使用pubs数据库完成下面操作
--查询titles表中类型是’popular_comp’的书的平均价格
select avg(price) as avgprice
from titles
where type = 'popular_comp'
--查询publishers表中供应商的数量
select count(*) '供应商的数量'
from publishers
--查询publishers表中供应商所在州的数量
select count(distinct state) as 供应商所在州的数量
from publishers

--查询titles表中书的类型有几种
select count(distinct type) as 书的类型有几种
from titles

--按书的种类分类，求出各类书籍的数量
select type,count(*) as book_count
from titles
group by type

--按书的种类分类，求出3种类型书籍‘business’,‘mod_cook’,‘trad_cook’的价格总和、平均价格以及该类书籍的数量
select type,
sum(price) as total_price,
avg(price) as avg_price,
count(*) as book_count
from titles
where type in ('business','mod_cook','trad_cook')
group by type
order by book_count desc;


--按书的类型分类，查询每个出版商销售的书的
--平均价和总销售额 
SELECT type,pub_id as pubid,AVG(price) avgprice,SUM(ytd_sales) total
from titles
group by type,pub_id

SELECT type,count(distinct pub_id),AVG(price) avgprice,SUM(ytd_sales) total
from titles
group by type
--按书的类型分类，查询平均价大于15的书的类型、平均价
select type,avg(price)'平均价'
from titles
group by type
having avg(price) >15;
--从titles表中，查询得到总销售额（ytd_sales）超过  $40,000 的出版商。
select pub_id
from titles
group by pub_id
having sum(ytd_sales)>40000



--从titles表中，查询得到至少出版六本书的出版商及其总销售额。
select  pub_id,
COUNT(*) as 书数,
SUM(ytd_sales) as 总额
from titles
group by pub_id
having count(*) >=6


--从titles表中，查询标识号大于 0800、支付的总预付款已超过 $15,000 且销售书籍的平均价格小于 $20 的出版商。 
select  pub_id
from titles
where title_id > '0800'
group by pub_id
having sum(advance) >15000 and AVG(price) <20


use GradeManager
--自我练习1（使用GradeManager数据库完成下面操作）
--（1）计算选2号课程的学生平均成绩，保留2位小数
select convert(decimal(5,2),AVG(Gmark)) as avggrade
from Grade
where Cno = '2'
--（2）求各课程相应的选课人数，显示课程号和选课人数
select Cno as 课程号 ,count(*) as 选课人数
from Grade
group by Cno

--（3）计算有人选修的每门课程的平均成绩，显示课程号和平均成绩
select Cno as 课程号,AVG(Gmark) as avg_grades
from Grade
group by Cno
--（4）计算参加过考试的每个学生的平均成绩
select sno as 学号,AVG(gmark) as 平均成绩
from Grade
where gmark is not null
group by sno
--（5）求选课人数最多的课程号及选课人数
select  top 1 
cno as 课程号,
COUNT(*) as 选课人数
from Grade
group by cno
order by 选课人数 desc

--（6）求平均成绩大于86分的学生学号及平均成绩
select sno as 学号,
AVG(gmark) as 平均成绩
from Grade
group by sno
having avg(gmark) > 86
--统计过后的判断用having语句而不是where

--自我练习2
--（1）统计每年每个专业的班级数
select inyear as 年份 ,
speciality as 专业,
count(distinct Clno) as 班级数
from Class
group by inyear,speciality

select inyear as 年份 ,
speciality as 专业,
count(*) as 班级数
from Class
group by inyear,speciality

--（2）统计每年每个专业的班级数及学生人数
select inyear as 年份 ,
speciality as 专业,
count(distinct Clno) as 班级数,
sum(number) as 学生人数
from Class
group by inyear,speciality

--（3）统计每个班级19岁以上的男生人数，显示班级号和人数
select clno as 班级号 ,
count(*) as 男生人数
from Student
where Ssex = '男' and sage >19
group by Clno

--（4）统计每门课程，显示平均分高于85分的课程号及平均分
select cno as 课程号,
avg(gmark) as 平均分
from Grade
group by cno
having avg (gmark)> 85

--（5）显示没有一门功课不及格的学生的学号
select sno as 学号
from Grade
group by sno
having min(gmark) >=60
--（6）统计选课人数低于3人且平均分低于80分的课程号
select cno as 课程号
from Grade
group by cno
having count(*) < 3 and AVG(Gmark) < 80

--（7）统计80分（含）以上成绩最多的学生学号
select top 1 sno as  学号,
count(*) as 成绩数量
from Grade
where gmark >= 80
group by sno
order by 成绩数量 desc



--（8）显示”李勇”同学所选课程的课程号和成绩
select cno as 课程号,
gmark as 成绩
from grade
where sno = (select sno from Student where Sname = '李勇')


select cno as 课程号,
gmark as 成绩
from grade join student on grade.Sno=student.Sno
where sname = '李勇'

连接查询

--使用GradeManager数据库完成下面操作
--（1）查询学生“李勇”选修的课程号
select cno
from grade g join student s on g.Sno = s.sno
where sname = '李勇'
--（2）查询学生“李勇”选修课程的平均分
select avg(gmark)'平均分'
from grade g join Student s on s.Sno = g.Sno
where sname = '李勇'


--（3）查询成绩大于80分的学生的姓名
select distinct sname
from student s join grade g on s.sno = g.sno
where Gmark>80


--（4）查询没有一门考试科目成绩低于70分的学生的姓名
select sname 
from student s join grade g on s.sno=g.Sno
group by s.sno,sname
having min(gmark)>=70
--（5）查询选了课的每位同学的总学分，显示学号和总学分
select sno,sum(credit)'总学分'
from Course c join Grade g on c.cno=g.cno
group by Sno
--（6）查询选课人数最多的课程号、课程名
select top 1  c.cno ,c.cname
from grade g
join Course c on g.Cno = c.Cno
group by c.cno,c.Cname
order by count(g.cno) desc


--（7）查询选课人数超过2人且平均分高于75分的课程号、课程名
select c.cno,cname
from grade g join course c on c.Cno = g.cno
group by c.Cno,
Cname
having COUNT(*) > 2 and AVG(Gmark) >75



--（8）查询所有学生的选课号和成绩。
select cno,gmark
from grade g right join Student s on g.sno = s.sno

SELECT 
    g.Sno, 
    g.Cno, 
    g.Gmark
FROM 
    Grade g;

--(子)查询选修了数据库和离散数学课程的同学信息，显示学生学号、姓名。
select g1.sno
from Grade g1
join Course c1 on g1.Cno = c1.Cno
join grade g2 on g1.sno = g2.Sno
join Course c2 on g2.Cno = c2.Cno
where c1.Cname = '数据库'
and c2.Cname = '离散数学'
group by g1.Sno
having 

select * from
(select top 2 s.sno,sname,cname,gmark
from grade g join student s on s.sno = g.Sno
join course c on c.cno = g.cno
where cname = '数据库'
order by gmark desc) as t
union all
select top 2 s.sno,sname,cname,gmark
from grade g join student s on s.sno = g.Sno
join course c on c.cno = g.cno
where cname = '离散数学'
order by gmark desc



select s.sno,sname
from student s join grade g on s.sno = g.sno
join course c on c.cno = g.cno
where cname = '数据库' or cname = '离散数学'

select s.sno,sname
from student s join grade g on s.sno = g.sno
join course c on c.cno = g.cno
where cname = '数据库' or cname = '离散数学'



select s.sno,sname
from student s join grade g on s.sno = g.sno
join course c on c.cno = g.cno
where cname = '数据库'  and s.sno in 
(select sno
from  grade g join course c on c.cno = g.Cno
where  cname = '离散数学')


--自我练习
--（1）查看全部选课信息，显示学生姓名、课程名、成绩
select sname,cname,gmark
from student s join grade g on s.sno = g.Sno
join course c on c.cno=g.Cno
--（2）查看所有学生的选课情况，显示学生姓名、课程名、成绩
select s.sname,
c.cname,
g.gmark
from grade g
join Student s on g.Sno = s.Sno
join Course c on g.Cno = c.Cno

select s.sname,
c.cname,
g.gmark
from student s left join grade g on s.sno=g.sno
left join course c on c.Cno = g.Cno
--（3）查看所有课程的选课人数，显示课程名、选课人数
SELECT c.Cname AS 课程名, COUNT(g.Sno) AS 选课人数
FROM Course c
LEFT JOIN Grade g ON c.Cno = g.Cno
GROUP BY c.Cname;


select c.cname as 课程名,
count(g.sno) as 选课人数
from Course c
left join Grade g on c.cno = g.cno
group by c.Cname

--（4）查看所有学生的选课总学分，显示学生姓名、总学分
select sname as 学生姓名,
isnull(SUM(c.Credit), 0)

from Course c
join grade g on c.cno = g.Cno
right join Student s  on s.Sno = g.Sno
group by s.sno,sname

子查询和高级数据操作

--使用GradeManger数据库完成下面操作
--（1）查询李勇的成绩


select gmark
from grade
where sno =(
select sno
from student
where sname = '李勇')

--（2）查询参加了两次考试以上的学生姓名

select sname from Student where sno in (
select Sno
from grade
group by sno
having count(Gmark) >= 2
)


--（3）查询没有参加任何一门考试的学生姓名

select sname from Student where sno not in (
select Sno
from grade
group by sno
having count(Gmark) is not null
)

select sname from Student where sno not in (
select Sno
from grade
group by sno
having count(Gmark) != 0
)

--（4）查询王一鸣高于其所有课程平均分的那些课程的课程名和成绩


select cno,gmark
from grade
where sno =(
select sno from student where sname = '王一鸣')
and Gmark >
(select AVG(gmark)
from grade
where sno =(
select sno from student where sname = '王一鸣'))

select cname,gmark
from course c join
(select cno,gmark
from grade
where sno=(select sno from student where sname='王一鸣')
and gmark>
(select avg(gmark)
from grade
where sno=(select sno from student where sname='王一鸣'))) as t
on c.cno=t.cno


--（5）查询王一鸣高于其所选课程平均分的那些课程的课程名和成绩
select cname,gmark
from course c join 
(select cno,gmark
from Grade g1
where sno =  (select sno from student where sname = '王一鸣')
and gmark >
(select avg(gmark)
from grade g2
where g2.cno = g1.cno )) as t 
on c.cno = t.cno





--数据高级操作练习
--（1）将李勇的数据库课程的成绩上调5分。
update grade
set gmark =  gmark +5
where sno = 
(select sno from student where sname = '李勇')
and cno = (select cno from course where cname = '数据库')


--（2）将没有一门考试不及格的学生的各门课成绩上调5分。
update grade
set gmark =  gmark +0
where sno in
(select sno from grade  group by sno having min(gmark) >=60)


--（3）将01311班的班长更改为贾向东
update class
set monitor = (
select sno from Student where sname = '贾向东')
where clno = 01311


select avg(price) from titles 

select price,price-(select avg(price) from titles )
from titles
where title_id = 'BU1111'




select distinct title
from titles t join sales s on t.title_id = s.title_id
where year(ord_date) = 1993

select title from titles where title_id in(
select distinct title_id
from sales
where year(ord_date) = 1993)

视图和存储过程

--视图练习
--使用GradeManger数据库，完成下面操作
--（1）建立00312班选了数据库课程并且成绩不及格的学生信息视图Stu_cs(包括学生学号、姓名、性别、成绩)
create view Stu_cs as
select s.sno as 学号,
s.sname as 姓名,
s.ssex as 性别,
g.gmark as 成绩
from Student s join Grade G on S.sno = g.sno
join course c on g.cno = c.Cno
where s.clno = '00312'
and c.cname = '数据库'
and g.gmark < 60

exec sp_help 'Stu_cs'

--（2）查询00312班选了数据库课程并且成绩不及格的学生的学号、姓名
select s.sno as 学号,
s.sname as 姓名
from Student s
join grade g on s.sno = g.sno
join course c on G.cno = c.cno
where s.clno = '00312'
and c.cname = '数据库'
and g.gmark <60

select 学号,姓名 from Stu_cs


--查00312班，学生学号和选课数
create proc up_XK
@clno char(5)
as
select s.sno,count(cno)选课数
from grade g join student s on g.sno=s.sno
where clno=@clno
group by s.sno

exec up_XK '01311'

--（3）创建视图vw_XK，查看所有学生的选课数，显示学生学号、姓名和选课数
create view vw_XK as
select s.sno as 学号,
s.sname as 姓名,
count(G.cno) as 选课数
from Student S
left join Grade G on s.sno = g.sno
group by s.sno, s.sname

select * from vw_XK




--（4）创建视图vw_01311，查询01311班的学生信息


alter view vw_01311 as 
select s.sno as 学号,
s.sname as 姓名,
s.ssex as 性别,
s.sage as 年龄,
s.Clno as 班级号
from student s

select * from vw_01311 where 班级号 = '01311'


select * from Stu_cs
select * from vw_XK


--存储过程练习
--（1）根据学号，得到该学号的选课情况，包括课程名和成绩 


CREATE PROCEDURE GetCourseInfoBySno
    @student_id VARCHAR(20)
AS
BEGIN
    SELECT C.Cname, G.Gmark
    FROM Grade G
    JOIN Course C ON G.Cno = C.Cno
    WHERE G.Sno = @student_id;
END;
GO

create proc up_xkBySno
@sno char(7)
as
select cname,gmark
from course c join grade g on c.cno = g.cno
where sno=@sno

exec up_xkBySno '2000101'
--（2）根据学号、课程号，得到该学生该课程的成绩
create proc up_CJbYNo
@sno char(7),
@cno char(1)
as
select gmark from grade
where sno = @sno
and cno = @cno

exec up_CJbYNo '2000101','1'



--（3）根据班级号，查询该系每位学生总学分，显示学号、姓名、总学分
create proc up_SumXF
@clno char(5)
as
select s.sno,sname,sum(Credit) 总学分
from Student  s  
join grade g on s.sno = g.cno
join Course c  on c.cno = g.Cno
where clno = @clno
group by s.Sno,Sname

exec up_SumXF '01311'

--（4）根据学生学号、课程号，修改该生该课程成绩为新的成绩


create proc up_Updatecj
@sno char(7),@cno char(1),@newgmark decimal(4,1)
as
update grade
set gmark=@newgmark
where sno=@sno and cno=@cno

exec up_Updatecj '2000101','1',100
exec up_cjbyno'2000101','1'

知识点复习