MySQL-day03

学生信息 stuinfo
sno      sname   ssex sage sdept

create table stuinfo(
	sid int unique auto_increment not null, -- 唯一
	sno varchar(50) primary key, -- 主键
	sname varchar(50) unique not null, -- 唯一
	ssex varchar(20) default '男', -- 默认
	sage int null,
	sdept varchar(50)
);
-- 第一列标识列:添加的三种写法
insert into stuinfo (sid,sno,sname,ssex,sage,sdept) 
  values(1,'95001','陈家洛','男',20,'信息系');
	
insert into stuinfo (sid,sno,sname,ssex,sage,sdept) 
  values(null,'95002','霍青桐','男',19,'计算机系');

insert into stuinfo (sno,sname,ssex,sage,sdept) 
  values('95003','喀丝丽','女',21,'中文系');

-- 
insert into stuinfo (sno,sname,ssex,sage,sdept) 
  values('95004','张召重','男',18,'计算机系');
insert into stuinfo (sno,sname,ssex,sage,sdept) 
  values('95005','于万亭','男',21,'体育系' );
insert into stuinfo (sno,sname,ssex,sage,sdept) 
  values('95006','周仲英','男',19,'计算机系');
insert into stuinfo (sno,sname,ssex,sage,sdept) 
  values('95007','福长安','男',19,'计算机系');
insert into stuinfo (sno,sname,ssex,sage,sdept) 
  values('95008','赵半山','男',14,'计算机系');


select * from stuinfo
 
 
 





课程信息   course
cid cname
1,'JAVA'
2,'C'
3,'.NET'

create table course(
   cid int primary key auto_increment not null,
	 cname varchar(20) unique
);

insert into course(cid,cname) values(1,'JAVA');
insert  course(cid,cname) values(2,'C');
insert into course(cid,cname) values(3,'.NET');

select * from course order by cid asc;
成绩信息  sc

sid sno  cid grade

create table sc(
   sid int primary key auto_increment not null,
	 sno varchar(50) not null, -- 外键
	 cid int not null, -- 外键
	 grade decimal(5,2)
)

-- 添加外键约束
-- 找到对应的主键
-- sc(sno)  主键在哪  stuinfo(sno)
-- sc(cid)  主键在哪  course(cid)
ALTER TABLE 员工表 ADD CONSTRAINT FOREIGN KEY 员工表(部门编号) REFERENCES 部门表 (部门编号);

alter table sc                  -- 修改表sc
   add constraint FK_sno foreign key   -- 添加外键约束
	  sc(sno) references stuinfo(sno);

alter table sc                  -- 修改表sc
   add constraint FK_cid foreign key   -- 添加外键约束
	  sc(cid) references course(cid);
		
-- 添加数据：如果是外键，那么这个值必须在主键中有

insert sc (sid,sno,cid,grade) values (1,'95001',1,98);

delete from sc where sid = 3;

insert sc (sid,sno,cid,grade) values
(2,'95002',1,90),
(3,'95004',1,70),  
(4,'95003',2,95), 
(5,'95005',3,80), 
(6,'95006',1,77), 
(7,'95001',2,80), 
(8,'95002',3,58), 
(9,'95006',2,70), 
(10,'95007',2,68);

-- 
select * from stuinfo;
select * from course;
select * from sc;
-- 1.删除年龄小于18的学员
delete from stuinfo where sage < 18;
-- 2.删除学号为95005学员信息及成绩信息。
-- 当我们删除主表时，如果从表有数据，是不能删除的。
-- 第一种解决方案：先删从表，再删除主表
delete from sc where sno = '95005';
delete from stuinfo where sno = '95005';
-- 第二种解决方案：在配置外键时可配置级联删除(不要这么做）
-- 3.查全体学生的姓名及其出生年份。
select sname, year(now())-sage 出生年份 from stuinfo;
select year(now());
-- 4. 转小写，转大写
select lower('ABC'),upper('abc');
-- 5.查询选修了课程的学生学号(去掉重复的记录)
-- 5.1 哪张表  成绩表 sc  考了试就代表选修了
-- 5.2 查的是学号,学号不能重复
select distinct sno from sc

-- 6.查询全体学生的学号与姓名，用中文显示列名。
select sno 学号,sname  '姓-名',sname as  姓名 from stuinfo

-- 7.给表设置别名。一般用于多表联查 
select sno,sname,sage from stuinfo as stu;
select sno,sname,sage from stuinfo  stu;

-- 8。查询年龄在20以下的学生的姓名。
select sname,sage from stuinfo where sage <20;

-- 9.查询全体学生的姓名、年龄，要求按照年龄降序排序。
select sname,sage from stuinfo order by sage desc;

-- 10.查询年龄最大的前3个学生的姓名和年龄，或第4、5个学生

select * from stuinfo order by sage desc limit 3;
select * from stuinfo order by sage desc limit 3,2
-- 11.查询学生总数
select count(*) from stuinfo;

-- 12.查询选修了课程的学生人数。
select count(distinct sno) from sc;

-- 13.查询1号课程的学生平均成绩
select avg(grade) from sc where cid = 1;

-- 14.查询1号课程的学生最高分和最低分。
select max(grade) 最高分,min(grade) 最低分 from sc where cid = 1;

-- 15.查询每个学生的平均成绩。
-- a.按学号分组
select sno,grade from sc group by sno,grade;
-- 如果有group by 那么select后面的列要么跟在group by后，
-- 要么是 max min count avg sum
-- b.求平均
select sno,avg(grade) from sc group by sno;

-- 16.查询学生的平均成绩在70分以上的。
select sno,avg(grade) from sc where avg(grade)>70 group by sno;

-- where后面不能跟 max min count avg sum聚合函数
-- having可以跟
select sno,avg(grade) from sc  
   group by sno  
      having avg(grade)>70;
			
-- 16.2查询学生的平均成绩在70分以上的。并降序排列
select sno,avg(grade) from sc  
   group by sno  
      having avg(grade)>70
			   order by avg(grade) desc;
-- 16.查询学生 1 号课程 的平均成绩在70分以上的。并降序排列
-- 外挂好 wgho
select sno,avg(grade) from sc  
  where cid = 1
   group by sno  
      having avg(grade)>70
			   order by avg(grade) desc;
				 
-- 17.查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 
select sname,sdept,sage from stuinfo where sage between 20 and 23; 
 
-- 18.查询年龄不在20~23岁之间的学生姓名、系别和年龄。
select sname,sdept,sage from stuinfo 
   where sage not between 20 and 23; 
-- 19.查询'信息系'、'美术系'和'计算机系'学生的姓名和性别。

select sname,ssex from stuinfo 
   where sdept in ('信息系','美术系','计算机系');
	 
-- 20.查询学号为95001的学生的详细情况。--精确查询
select * from stuinfo where sno = '95001';

-- 21.查询所有姓刘学生的姓名、学号和性别。
select sname,sno,ssex from stuinfo where sname like '张%';
-- 22.查询未考试的学员

-- 查不到成绩
-- 拿学员信息表的学号去学员成绩表中找
-- 两表联查

-- 两表相乘：笛卡尔集。
-- 做条件：挑出满足条件的数据。
-- 左联接：以左表为参照，left join 左边的是左表
--         去右表找对应的信息，找不到以null显示
select * from stuinfo s
   right join sc 
	   on s.sno = sc.sno
		   where grade is null;
-- 23 查所有有成绩的学生学号和课程号。
select sno,cid from sc  where grade is not null;
-- 24.	查询计算机系年龄在20岁以下的学生姓名。
select sname from stuinfo where sdept='计算机系' and sage<20;
-- 25.	查询信息系、美术系和计算机系学生的姓名和性别
select sname,ssex from stuinfo 
   where sdept in ('信息系','美术','计算机系');
可改写为：
select sname,ssex from stuinfo where sdept='信息系' or sdept='美术' or sdept='计算机系';

-- 26.查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
select sno,grade from sc where cid=1 order by grade desc;

-- 27.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
select * from stuinfo order by sdept asc,sage desc; 

-- 28.查询  学号  姓名  课程名和
-- 1.表stuinfo
-- 2.表course
-- 3.发现问题 这两表没有任何关系
-- 4.表sc成绩表，它是中间表（写中间）

select s.sno,s.sname,c.cname from 
   stuinfo s
	   inner join sc
		    on s.sno = sc.sno
				  inner join course c
					  on sc.cid = c.cid

-- 29.查询  学号  姓名 系别  课程  分数

	select s.sno,s.sname,c.cname,sc.grade from 
   stuinfo s
	   inner join sc
		    on s.sno = sc.sno
				  inner join course c
					  on sc.cid = c.cid