强生公司营销网站为什么要这样做,南京市公共建设管理中心网站,app建设网站,全网响应式网站目录
数据表介绍
--1.学生表Student(SId,Sname,Sage,Ssex)--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表Course(CId,Cname,TId)--CId 课程编号,Cname 课程名称,TId 教师编号
--3.教师表Teacher(TId,Tname)--TId 教师编号,Tname 教师姓名
--4.成绩…目录
数据表介绍
--1.学生表Student(SId,Sname,Sage,Ssex)--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表Course(CId,Cname,TId)--CId 课程编号,Cname 课程名称,TId 教师编号
--3.教师表Teacher(TId,Tname)--TId 教师编号,Tname 教师姓名
--4.成绩表SC(SId,CId,score)--SId 学生编号,CId 课程编号,score 分数
学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values(01 , 赵雷 , 1990-01-01 , 男);
insert into Student values(02 , 钱电 , 1990-12-21 , 男);
insert into Student values(03 , 孙风 , 1990-12-20 , 男);
insert into Student values(04 , 李云 , 1990-12-06 , 男);
insert into Student values(05 , 周梅 , 1991-12-01 , 女);
insert into Student values(06 , 吴兰 , 1992-01-01 , 女);
insert into Student values(07 , 郑竹 , 1989-01-01 , 女);
insert into Student values(09 , 张三 , 2017-12-20 , 女);
insert into Student values(10 , 李四 , 2017-12-25 , 女);
insert into Student values(11 , 李四 , 2012-06-06 , 女);
insert into Student values(12 , 赵六 , 2013-06-13 , 女);
insert into Student values(13 , 孙七 , 2014-06-01 , 女);
科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values(01 , 语文 , 02);
insert into Course values(02 , 数学 , 01);
insert into Course values(03 , 英语 , 03);
教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values(01 , 张三);
insert into Teacher values(02 , 李四);
insert into Teacher values(03 , 王五);
成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values(01 , 01 , 80);
insert into SC values(01 , 02 , 90);
insert into SC values(01 , 03 , 99);
insert into SC values(02 , 01 , 70);
insert into SC values(02 , 02 , 60);
insert into SC values(02 , 03 , 80);
insert into SC values(03 , 01 , 80);
insert into SC values(03 , 02 , 80);
insert into SC values(03 , 03 , 80);
insert into SC values(04 , 01 , 50);
insert into SC values(04 , 02 , 30);
insert into SC values(04 , 03 , 20);
insert into SC values(05 , 01 , 76);
insert into SC values(05 , 02 , 87);
insert into SC values(06 , 01 , 31);
insert into SC values(06 , 03 , 34);
insert into SC values(07 , 02 , 89);
insert into SC values(07 , 03 , 98); 查询 01 课程比 02 课程成绩高的学生的信息及课程分数 查询同时存在 01 课程和 02 课程的情况
mysql select * from (select * from sc where CId01) as t1 inner join ( select * from sc where CId02) as t2 on t1.SIdt2.SId;--------------------------------------| SId | CId | score | SId | CId | score |--------------------------------------| 01 | 01 | 80.0 | 01 | 02 | 90.0 || 02 | 01 | 70.0 | 02 | 02 | 60.0 || 03 | 01 | 80.0 | 03 | 02 | 80.0 || 04 | 01 | 50.0 | 04 | 02 | 30.0 || 05 | 01 | 76.0 | 05 | 02 | 87.0 |--------------------------------------5 rows in set (0.00 sec) 查询存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null )
mysql select * from (select * from sc where CId01) as t1 left outer join ( select * from sc where CId02) as t2 on t1.SIdt2.SId;--------------------------------------| SId | CId | score | SId | CId | score |--------------------------------------| 01 | 01 | 80.0 | 01 | 02 | 90.0 || 02 | 01 | 70.0 | 02 | 02 | 60.0 || 03 | 01 | 80.0 | 03 | 02 | 80.0 || 04 | 01 | 50.0 | 04 | 02 | 30.0 || 05 | 01 | 76.0 | 05 | 02 | 87.0 || 06 | 01 | 31.0 | NULL | NULL | NULL |--------------------------------------6 rows in set (0.00 sec) 查询不存在 01 课程但存在 02 课程的情况
mysql select * from (select * from sc where CId01) as t1 right outer join ( select * from sc where CId02) as t2 on t1.SIdt2.SId where t1.CId is null;--------------------------------------| SId | CId | score | SId | CId | score |--------------------------------------| NULL | NULL | NULL | 07 | 02 | 89.0 |--------------------------------------1 row in set (0.00 sec) 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
mysql select * from ( select SId,avg(score) from sc group by SId having avg(score)60) as t1 inner join (select Sname,SId from student) as t2 on t2.SIdt1.SId;-------------------------------| SId | avg(score) | Sname | SId |-------------------------------| 01 | 89.66667 | 赵雷 | 01 || 02 | 70.00000 | 钱电 | 02 || 03 | 80.00000 | 孙风 | 03 || 05 | 81.50000 | 周梅 | 05 || 07 | 93.50000 | 郑竹 | 07 |-------------------------------5 rows in set (0.00 sec)
查询在 SC 表存在成绩的学生信息
mysql select * from student right outer join sc on student.SIdsc.SId;------------------------------------------------------------| SId | Sname | Sage | Ssex | SId | CId | score |------------------------------------------------------------| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 01 | 80.0 || 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 02 | 90.0 || 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 03 | 99.0 || 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 | 01 | 70.0 || 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 | 02 | 60.0 || 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 | 03 | 80.0 || 03 | 孙风 | 1990-12-20 00:00:00 | 男 | 03 | 01 | 80.0 || 03 | 孙风 | 1990-12-20 00:00:00 | 男 | 03 | 02 | 80.0 || 03 | 孙风 | 1990-12-20 00:00:00 | 男 | 03 | 03 | 80.0 || 04 | 李云 | 1990-12-06 00:00:00 | 男 | 04 | 01 | 50.0 || 04 | 李云 | 1990-12-06 00:00:00 | 男 | 04 | 02 | 30.0 || 04 | 李云 | 1990-12-06 00:00:00 | 男 | 04 | 03 | 20.0 || 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 05 | 01 | 76.0 || 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 05 | 02 | 87.0 || 06 | 吴兰 | 1992-01-01 00:00:00 | 女 | 06 | 01 | 31.0 || 06 | 吴兰 | 1992-01-01 00:00:00 | 女 | 06 | 03 | 34.0 || 07 | 郑竹 | 1989-01-01 00:00:00 | 女 | 07 | 02 | 89.0 || 07 | 郑竹 | 1989-01-01 00:00:00 | 女 | 07 | 03 | 98.0 |------------------------------------------------------------18 rows in set (0.00 sec) 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
mysql select * from (select SId,Sname from student) as t1 left outer join ( select SId,count(CId),sum(score) from sc group by SId) as t2 on t1.SIdt2.SId;-------------------------------------------| SId | Sname | SId | count(CId) | sum(score) |-------------------------------------------| 01 | 赵雷 | 01 | 3 | 269.0 || 02 | 钱电 | 02 | 3 | 210.0 || 03 | 孙风 | 03 | 3 | 240.0 || 04 | 李云 | 04 | 3 | 100.0 || 05 | 周梅 | 05 | 2 | 163.0 || 06 | 吴兰 | 06 | 2 | 65.0 || 07 | 郑竹 | 07 | 2 | 187.0 || 09 | 张三 | NULL | NULL | NULL || 10 | 李四 | NULL | NULL | NULL || 11 | 李四 | NULL | NULL | NULL || 12 | 赵六 | NULL | NULL | NULL || 13 | 孙七 | NULL | NULL | NULL |-------------------------------------------12 rows in set (0.00 sec)
4.1 查有成绩的学生信息
mysql select * from student right outer join sc on student.SIdsc.SId;------------------------------------------------------------| SId | Sname | Sage | Ssex | SId | CId | score |------------------------------------------------------------| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 01 | 80.0 || 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 02 | 90.0 || 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 03 | 99.0 || 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 | 01 | 70.0 || 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 | 02 | 60.0 || 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 | 03 | 80.0 || 03 | 孙风 | 1990-12-20 00:00:00 | 男 | 03 | 01 | 80.0 || 03 | 孙风 | 1990-12-20 00:00:00 | 男 | 03 | 02 | 80.0 || 03 | 孙风 | 1990-12-20 00:00:00 | 男 | 03 | 03 | 80.0 || 04 | 李云 | 1990-12-06 00:00:00 | 男 | 04 | 01 | 50.0 || 04 | 李云 | 1990-12-06 00:00:00 | 男 | 04 | 02 | 30.0 || 04 | 李云 | 1990-12-06 00:00:00 | 男 | 04 | 03 | 20.0 || 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 05 | 01 | 76.0 || 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 05 | 02 | 87.0 || 06 | 吴兰 | 1992-01-01 00:00:00 | 女 | 06 | 01 | 31.0 || 06 | 吴兰 | 1992-01-01 00:00:00 | 女 | 06 | 03 | 34.0 || 07 | 郑竹 | 1989-01-01 00:00:00 | 女 | 07 | 02 | 89.0 || 07 | 郑竹 | 1989-01-01 00:00:00 | 女 | 07 | 03 | 98.0 |------------------------------------------------------------18 rows in set (0.00 sec)
查询「李」姓老师的数量
mysql select count(1) from teacher where Tname like 李%;----------| count(1) |----------| 1 |----------1 row in set (0.00 sec) 查询学过「张三」老师授课的同学的信息
mysql select * from student where SId in (select SId from sc where CId( select CId from course where TId(select TId from teacher where Tname 张三)));----------------------------------------| SId | Sname | Sage | Ssex |----------------------------------------| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 || 02 | 钱电 | 1990-12-21 00:00:00 | 男 || 03 | 孙风 | 1990-12-20 00:00:00 | 男 || 04 | 李云 | 1990-12-06 00:00:00 | 男 || 05 | 周梅 | 1991-12-01 00:00:00 | 女 || 07 | 郑竹 | 1989-01-01 00:00:00 | 女 |----------------------------------------6 rows in set (0.00 sec)
查询没有学全所有课程的同学的信息 查询至少有一门课与学号为 01 的同学所学相同的同学的信 查询和 01 号的同学学习的课程 完全相同的其他同学的信息 查询没学过张三老师讲授的任一门课程的学生姓名 查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
mysql select * from (select SId from student) as t1 inner join (select SId,avg(score) from sc where score 60 group by SId having count(CId)2) as t2 on t1.SIdt2.SId;-----------------------| SId | SId | avg(score) |-----------------------| 04 | 04 | 33.33333 || 06 | 06 | 32.50000 |-----------------------2 rows in set (0.00 sec) 检索 01 课程分数小于 60按分数降序排列的学生信息
mysql select * from student inner join (select * from sc where CId01 and score 60 order by score desc) as t2 on student.SIdt2.SId;-----------------------------------------------------------| SId | Sname | Sage | Ssex | SId | CId | score |-----------------------------------------------------------| 04 | 李云 | 1990-12-06 00:00:00 | 男 | 04 | 01 | 50.0 || 06 | 吴兰 | 1992-01-01 00:00:00 | 女 | 06 | 01 | 31.0 |-----------------------------------------------------------2 rows in set (0.00 sec)
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
mysql select * from (select SId,avg(score) from sc group by SId order by avg(score) desc) as t1 left outer join sc on sc.SIdt1.SId;-------------------------------------| SId | avg(score) | SId | CId | score |-------------------------------------| 07 | 93.50000 | 07 | 02 | 89.0 || 07 | 93.50000 | 07 | 03 | 98.0 || 01 | 89.66667 | 01 | 01 | 80.0 || 01 | 89.66667 | 01 | 02 | 90.0 || 01 | 89.66667 | 01 | 03 | 99.0 || 05 | 81.50000 | 05 | 01 | 76.0 || 05 | 81.50000 | 05 | 02 | 87.0 || 03 | 80.00000 | 03 | 01 | 80.0 || 03 | 80.00000 | 03 | 02 | 80.0 || 03 | 80.00000 | 03 | 03 | 80.0 || 02 | 70.00000 | 02 | 01 | 70.0 || 02 | 70.00000 | 02 | 02 | 60.0 || 02 | 70.00000 | 02 | 03 | 80.0 || 04 | 33.33333 | 04 | 01 | 50.0 || 04 | 33.33333 | 04 | 02 | 30.0 || 04 | 33.33333 | 04 | 03 | 20.0 || 06 | 32.50000 | 06 | 01 | 31.0 || 06 | 32.50000 | 06 | 03 | 34.0 |-------------------------------------18 rows in set (0.00 sec)
