SQL Server之菜鸟入坑学习&&练习01

use Student;
--建立学生表
create table stu_(
stu_num int primary key,
stu_name varchar(20) not null,
sty_sex varchar(20) default '男' check(sty_sex='男' or sty_sex='女' or sty_sex='未知') not null,
stu_class varchar(20)
);
--
drop table stu_;
--往学生表插入数据
insert into stu_ values('2014122103','大哥','男','18机械2');
insert into stu_ values('2014122101','天使','女','16天界1');
insert into stu_ values('2014122102','魔鬼','未知','15魔界3');
insert into stu_ values('2015122103','叶凡','男','16遮天1');
insert into stu_ values('2016122103','辰东','男','19断更1');
insert into stu_ values('2017122103','萧炎','男','15斗破1');
insert into stu_ values('2015122107','林动','男','16武祖1');
insert into stu_ values('2015122108','牧尘','男','17大主宰1');
insert into stu_ values('2014122101','周元','男','19元尊2');
insert into stu_ values('2017182103','泰日天','未知','18狗科1');
insert into stu_ values('2019182103','林诺依','女','19轮回1');
insert into stu_ values('2011182103','程序猿','男','15软件1');
select * from stu_;
--建立学习成绩表
create table stu_grade(
stu_num int references stu_(stu_num),
stu_major varchar(20) default '未知' ,
primary key(stu_num,stu_major),
stu_point int not null check(stu_point>=0 and stu_point<=100)
);
--
drop table stu_grade;
--往学习成绩表里面插入数据
insert into stu_grade values('2014122103','机械原理','88');
insert into stu_grade values('2014122103','数据库组成原理','60');
insert into stu_grade values('2014122103','机械工程','100');
insert into stu_grade values('2015122107','祖符的入门到入土','70');
insert into stu_grade values('2015122107','青天化龙诀','88');
insert into stu_grade values('2015122107','大荒囚天指','90');
insert into stu_grade values('2011182103','Java基础','99');
insert into stu_grade values('2017182103','万物皆可日','100');
insert into stu_grade values('2017182103','心如止水','100');
--尝试
insert into stu_grade values('100','Java基础','99');--发生错误
/*  INSERT 语句与 FOREIGN KEY 约束"FK__stu_grade__stu_n__22AA2996"冲突。该冲突发生于数据库"Student"，
表"dbo.stu_", column 'stu_num'。语句已终止*/
--
select *from stu_grade;
--删除学号是2014122103学生的数据库组成原理成绩
delete from stu_grade where stu_num='2014122103' and stu_major='数据库组成原理';
--更新林动的青天化龙诀成绩为100
update stu_grade
set stu_point='100'
where stu_major='青天化龙诀' and stu_num=(select stu_num from stu_ where stu_name='林动');
--学生表stu_新增数据
insert into stu_ values('2019182101','楚风','男','19轮回1');
insert into stu_ values('2019182102','段德','男','19轮回1');
insert into stu_ values('2019182104','黎龘','男','19轮回1');
insert into stu_ values('2015122101','无始','男','16遮天1');
insert into stu_ values('2015122102','狠人','女','16遮天1');
insert into stu_ values('2015122104','黑皇','未知','16遮天1');
--学生成绩表stu_grade新增数据
insert into stu_grade values('2019182101','轮回诀','100');
insert into stu_grade values('2019182102','轮回诀','88');
insert into stu_grade values('2019182103','轮回诀','95');
insert into stu_grade values('2019182104','轮回诀','80');
insert into stu_grade values('2015122108','青天化龙诀','90');
insert into stu_grade values('2015122101','无德','0');
insert into stu_grade values('2015122102','无德','0');
insert into stu_grade values('2015122104','无德','88');
insert into stu_grade values('2015122103','无德','100');
insert into stu_grade values('2011182103','数据库组成原理','99');
insert into stu_grade values('2011182103','软件工程','99');
insert into stu_grade values('2011182103','设计模式','99');
insert into stu_grade values('2011182103','C语言基础','99');
insert into stu_grade values('2011182103','操作系统','99');

--凡是在group by后面出现的字段，必须同时在select后面出现；
--凡是在select后面出现的、同时未在聚合函数中出现的字段，必须同时出现在group by后面”，检查sql是否符合上述法则。
--聚合函数MAX,MIN,SUM,AVG,SUM,CHECKSUM_EGG,COUNT,STDEV,STDEVP,VAR,VARP。--

--查询学生表里面的各性别人数
select sty_sex as 性别,count(sty_sex) as 数量
from stu_
group by sty_sex
--查询各个班级各性别的人数,按男女生人数升序,其他降序
select stu_class as 班级,sum(case when sty_sex='男' then 1 else 0 end) as 男生,
sum(case when sty_sex='女' then 1 else 0 end) as 女生,
sum(case when sty_sex='未知' then 1 else 0 end) as 未知
from stu_
group by stu_class
order by sum(case when sty_sex='男' then 1 else 0 end),
sum(case when sty_sex='女' then 1 else 0 end),
sum(case when sty_sex='未知' then 1 else 0 end) desc
--把学习成绩表的'轮回诀'学科改成'六道轮回诀'
update stu_grade 
set stu_major='心如止水,只想日天'
where stu_major='心如止水'

--having子句是用来对分组之后的信息进行过滤，因此使用having时通常都会先使用group by
--查询有女生的班级
select stu_class as 班级,sum(case when sty_sex='女' then 1 else 0 end) as 女生数量
from stu_
group by stu_class
having sum(case when sty_sex='女' then 1 else 0 end)>0;
--学生成绩表插入几条数据
insert into stu_grade values('2015122101','无始无终','80');
insert into stu_grade values('2015122101','无始经','100');
--查询各班级成绩平均分在60-80（包含60，80）的学生信息
select a.stu_name as 姓名,a.stu_class as 班级,avg(b.stu_point) as 平均分数
from stu_ a,stu_grade b
where a.stu_num=b.stu_num
group by a.stu_name,a.stu_class
having avg(b.stu_point)>=60 and avg(b.stu_point)<=80
--where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，
--即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
--having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，
--使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

--JOIN 分为：内连接（INNER JOIN）、外连接（OUTER JOIN）。
--其中，外连接分为：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN），
--其中外连接的“OUTER”关键字可以省略不写。

--内连接(等值连接)
select *
from stu_ a
inner join stu_grade b
on a.stu_num=b.stu_num;
--左外连接
select *
from stu_ a
left join stu_grade b
on a.stu_num=b.stu_num;
--右外连接
select *
from stu_ a
right join stu_grade b
on a.stu_num=b.stu_num;

--全外连接

select *
from stu_ a
full join stu_grade b
on a.stu_num=b.stu_num;
--如果两个表是按照主键进行连接的话，左连接的话返回的记录集肯定是等于左表返回的记录数；
--右连接的话记录集肯定是等于右表返回的记录数；
--内连接就返回两个表都存在的记录。
/*
select *
from stu_ a,stu_grade b
where a.stu_num=b.stu_num(+)*/

--查询名字带'天'的学生信息
select *
from stu_
where stu_.stu_name like '%天%'
--查询名字第一个字是'天'的学生信息
select *
from stu_
where stu_.stu_name like '天%'
----查询出各班级各学生的成绩（学号、学生姓名、性别、班级、学科、成绩），并按班级汇总统计
--初版
select a.stu_class as 班级,a.stu_num as 学号,a.stu_name as 姓名,a.sty_sex as 性别,
b.stu_major as 学科,b.stu_point as 分数
from stu_ a
left join stu_grade b
on a.stu_num=b.stu_num
order by a.stu_class desc
--修缮后
select a.stu_class as 班级,a.stu_num as 学号,a.stu_name as 姓名,a.sty_sex as 性别,
isnull(b.stu_major,'尚未修炼') as 学科,isnull(b.stu_point,0)as 分数
from stu_ a
left join stu_grade b
on a.stu_num=b.stu_num
order by a.stu_class desc

--各班级各学生的总成绩
select a.stu_class as 班级,a.stu_num as 学号,sum(isnull(b.stu_point,0)) as 总成绩
from stu_ a
left join stu_grade b
on a.stu_num=b.stu_num
group by a.stu_class,a.stu_num