Mysql建表操作及基本命令使用（Teacher，Student,Course,Curse）

CREATE DATABASE IF NOT EXISTS ExamDB  
CHARACTER SET utf8mb4  
COLLATE utf8mb4_unicode_ci;
 
use ExamDB;
-- 学生表
create table Student (
Sno char(3) not null comment '学号',
Sname char(8) not null comment '学生姓名',
Ssex char(2) not null comment '学生性别',
Sbirthday datetime comment '学生出生年月',
Class char(5) comment '学生所在的班级'
);
insert into Student(Sno,Sname,Ssex,Sbirthday,Class)
values('108','曾华','男','1977-09-01','95033'),
('105','匡明','男','1975-10-02','95031'),
('107','王丽','女','1976-01-23','95033'),
('101','李军','男','1976-02-20','95033'),
('109','王芳','女','1975-02-10','95031'),
('103','陆军','男','1974-06-03','95031');
-- 教师表
create table Teacher(
Tno char(3) not null comment '教工编号（主码）',
Tname char(4) not null comment '教工姓名',
Tsex char(2) not null comment '教工性别',
Tbirthday datetime comment '学生出生年月',
Prof char(6) comment '职称',
Depart Varchar(10) not null comment '教工所在部门'
);
insert into Teacher(Tno,Tname,Tsex,Tbirthday,Prof,Depart)
values('804','李诚','男','1958-12-02','副教授','计算机系'),
('856','张旭','男','1969-03-12','讲师','电子工程系'),
('825','王萍','女','1972-05-05','助教','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系');
-- 课程表
create table Course (
Cno char(5) not null comment '课程号（主码）',
Cname Varchar(10) not null comment '课程名称',
Tno char(3) not null comment '教工编号（外码）'
);
insert into Course(Cno,Cname,Tno)
values('3-105','计算机导论','825'),
('3-245','操作系统','804'),
('6-166','数字电路','856'),
('9-888','高等数学','831');


-- 创建成绩表
create table Score (
Sno char(3) not null comment '学号（外码）',
Cno char(5)  not null comment '课程号（外码）',
Degree Decimal(4,1) comment '成绩'
);
insert into Score(Sno,Cno,Degree)
values('103','3-245',86),
('105','3-245',75),
('109','3-245',68),
('103','3-105',92),
('105','3-105',88),
('109','3-105',76),
('101','3-105',64),
('107','3-105',91),
('108','3-105',78),
('101','3-166',79),
('107','6-166',79),
('108','3-166',81);
-- 查询每门课的平均成绩，要按照课程分组group by，然后求没门课平均成绩
select Cno, avg(Degree) as average_score  
from Score  
group by Cno;
-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
-- s1 原始表  s2是临时表
select s1.Cno, AVG(Degree) AS average_score  
from Score s1  
join(  
    select Cno  
    from Score  
    where Cno like '3%'  
    group by Cno 
    having count(*) >= 5  
) s2 on s1.Cno = s2.Cno  
group by s1.Cno; 

--  查询所有学生的Sname、Cno和Degree列
SELECT t1.Sname,t2.Cno,t2.Degree  
FROM Student t1
JOIN Score t2
ON t1.Sno = t2.Sno;

-- 查询所有学生的Sno、Cname和Degree列
SELECT  t1.Cname,t2.Sno,t2.Degree
FROM Course t1
CROSS JOIN Score t2;

-- 查询所有学生的Sname、Cname和Degree列
SELECT t1.Sname,t2.Cname,t3.Degree 
FROM Student t1  
CROSS JOIN Course t2  
CROSS JOIN Score t3;
-- 查询“95033”班学生的平均分。  
SELECT AVG(Score.degree) AS average_degree  
FROM Student  
INNER JOIN Score 
ON Student.Sno = score.Sno  
WHERE Student.class= '95033';


-- 建立grade表
create table grade(
low int(3),
upp int(3),
rank char(1));
insert into grade(low,upp,rank)
values(90,100,'A'),
(80,89,'B'),
(70,79,'C'),
(60,69,'D'),
(0,59,'E');
select * from     grade;
-- 1、查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname ,Ssex,Class
from Student;

-- 2、查询教师所有的单位即不重复的Depart列。
select distinct Depart from Teacher;
-- 3、查询Student表的所有记录。
select * from Student;

-- 4、查询Score表中成绩在60到80之间的所有记录。

select * from Score where Degree between 60 and 80;
-- 5、查询Score表中成绩为85，86或88的记录。

select * from Score where Degree in(85,86,88);
-- 6、查询Student表中“95031”班或性别为“女”的同学记录。
Select *from Student where Class='95031' or Ssex='女';

-- 7、以Class降序查询Student表的所有记录。
select *from Student order by Class desc; 

-- 8、以Cno升序、Degree降序查询Score表的所有记录。
select * from Score order by Cno asc,Degree desc;


-- 9、查询“95031”班的学生人数。
select count(*) as sumpople from Student where Class='95031';

-- 10、查询Score表中的最高分的学生学号和课程号。
select Cno ,Sno,max(Degree) aveg
from Score;




-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select s1.Cno,avg(Degree) as ping_jun
from Score s1
join(
Select Cno
from Score
where Cno like '3%'
group by Cno
having count(*)>=5
)s2  on s1.Cno=s2.Cno
group by s1.Cno;



-- 13、查询分数大于70，小于90的Sno列。

select Sno
from Score
where Degree>70 and Degree <90;

-- 14、查询所有学生的Sname、Cno和Degree列。
select t1.Sname,t2.Cno,t2.Degree
from Student t1
join Score t2
on t1.Sno=t2.Sno;

-- 15、查询所有学生的Sno、Cname和Degree列。
select t2.Sno,t1.Cname,t2.Degree
from Course t1
cross join Score t2
;



-- 16、查询所有学生的Sname、Cname和Degree列。
select t1.Sname,t2.Cname,t3.Degree
from Student t1
cross join Course t2
cross join Score t3;


-- 17、查询“95033”班学生的平均分。
select  avg(t2.Degree) as AverageScore  
from Student  t1
join Score  t2 ON t1.Sno = t2.Sno  
where t1.Class = '95033';

-- 现查询所有同学的Sno、Cno和rank列。
SELECT  t1.rank,t2.Sno,t2.Cno
FROM grade t1
CROSS JOIN Score t2;



-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT s1.*  
FROM Score s1  
JOIN Score s2 ON s1.Cno= s2.Cno AND s2.Sno = '109'
WHERE s1.Cno = '3-105' AND s1.Degree > s2.Degree;



-- 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
-- 1.找出课程大于1的课程号
SELECT Cno  
FROM Score  
GROUP BY Cno  
HAVING COUNT(*) > 1;

SELECT s1.*  -- 选择除最大数据的其他数据
FROM Score s1  
LEFT JOIN (  
    SELECT Cno, MAX(Degree) AS MaxDegree  
    FROM Score  
    GROUP BY Cno  
) -- 找出最高分数

s2 ON s1.Cno = s2.Cno AND s1.Degree = s2.MaxDegree  
WHERE s2.MaxDegree IS NULL;  




-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT *  
FROM Score  
WHERE Cno = '3-105'  
  AND Degree > (  
    SELECT Degree  
    FROM Score  
    WHERE Sno= '109' AND Cno = '3-105'  
  );
  
 --  22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
 -- 步骤1: 查询学号为108的学生的出生年份  
SELECT YEAR(Sbirthday) AS BirthYear  
FROM Student  
WHERE Sno = '108';  
  
-- 步骤2: 使用上一步得到的出生年份去查询所有同年出生的学生  
SELECT Sno, Sname, Sbirthday  
FROM Student  
WHERE YEAR(Sbirthday) = (  
    SELECT YEAR(Sbirthday)  
    FROM Student  
    WHERE Sno = '108'  
);


-- 23.查询“张旭“教师任的学生成绩
SELECT s.Sno, s.Degree  
FROM Score s  
JOIN Course st ON s.Cno = st.Cno  
WHERE s.Cno = '6-166';


-- 24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT  t.Tname  
FROM Course c  
JOIN Teacher t ON c.Tno = t.Tno -- 假设Course表中有TeacherId字段指向Teacher表的Tno  
JOIN (  
    SELECT Cno  
    FROM Score  
    GROUP BY Cno  
    HAVING COUNT(DISTINCT Sno) > 5 -- 选修该课程的学生数量大于5个  
) s ON c.Cno = s.Cno;



-- 25、查询95033班和95031班全体学生的记录。
SELECT *  
FROM Student  
WHERE class IN ('95033', '95031');


-- 26、查询存在有85分以上成绩的课程Cno。

SELECT Cno  
FROM Score  
GROUP BY Cno  
HAVING MAX(Degree) > 85;



-- 27、查询出“计算机系“教师所教课程的成绩表。

SELECT t.Tno, c.Cno, s.Degree  
FROM Teacher t  
JOIN Course c ON t.Tno = c.Tno  
JOIN Score s ON c.Cno = s.Cno  
WHERE t.Depart = '计算机系';
-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT Tname, Prof  
FROM Teacher  
WHERE Depart IN ('计算机系', '电子工程系')  
ORDER BY Depart, Prof;
-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno

SELECT s1.*  
FROM Score s1  
JOIN Score s2 ON s1.Sno = s2.Sno -- 假设Sno是学生的标识  
WHERE s1.Cno = '3-105'  -- 过滤出 3-105
  AND s2.Cno = '3-245'  -- 过滤出比 3-245 大的成绩
  AND s1.Degree > s2.Degree;
  
  
  
-- 30、和Degree，并按Degree从高到低次序排序。
SELECT Cno, SUM(Degree) AS TotalDegree  
FROM Score  
GROUP BY Cno  
ORDER BY TotalDegree DESC;

-- 31、查询所有教师和同学的name、sex和birthday。
SELECT DISTINCT t1.Tname,t1.Tsex,t1.Tbirthday,
t2.Sname,t2.Ssex,t2.Sbirthday
FROM Teacher t1
CROSS JOIN Student t2
WHERE t1.Tsex='女' and t2.Ssex='女';
-- 33、查询成绩比该课程平均成绩低的同学的成绩表。
-- 计算每个课程的平均分：
SELECT Cno, AVG(Degree) AS AvgDegree  
FROM Score  
GROUP BY Cno;
-- 找出比平均分少的Degree的Sno：
SELECT s.Sno, s.Cno, s.Degree  
FROM Score s  
WHERE s.Degree < (  
    SELECT AVG(Degree)  
    FROM Score s2  
    WHERE s2.Cno = s.Cno  
);
-- 34、查询所有任课教师的Tname和Depart。
select Tname,Depart
from Teacher;

-- 35、查询所有未讲课的教师的Tname和Depart。

-- 查找出在Course中和Score没有的Tno

SELECT c.Cno, t.Tname, t.Depart  
FROM Course c  
JOIN Teacher t ON c.Tno = t.Tno  
WHERE c.Cno NOT IN (  
    SELECT s.Cno  
    FROM Score s  
);


-- 36、查询至少有2名男生的班号。
SELECT Class  
FROM Student  
WHERE Ssex = '男'  
GROUP BY Class  
HAVING COUNT(*) >= 2;


-- 37、查询不姓王的同学信息。
SELECT *  
FROM Student  
WHERE Sname NOT LIKE '王%';

-- 38、查询Student表中每个学生的姓名和年龄。
-- 根据生日推算年龄
SELECT   
    Sname,   
    Sbirthday,   
    TIMESTAMPDIFF(YEAR, Sbirthday, CURDATE()) AS age   
FROM Student;


-- 39、查询Student表中最大和最小的Sbirthday日期值。
SELECT   
    MIN(Sbirthday) AS maxBirthday,   
    MAX(Sbirthday) AS minBirthday   
FROM Student;


-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT *, TIMESTAMPDIFF(YEAR, Sbirthday, CURDATE()) AS age  
FROM Student  
ORDER BY Class DESC, age DESC;
-- 41、查询“男”教师及其所上的课程。
SELECT Course.Cname  
FROM Teacher  
JOIN Course ON Teacher.Tno = Course.Tno -- 假设的关联字段  
WHERE Teacher.Tsex = '男';
-- 42、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT Student.Sno, Student.Sname, Score.Degree  
FROM Student  
JOIN Score ON Student.Sno = Score.Sno  
JOIN Course ON Score.Cno = Course.Cno  
WHERE Course.Cno = '3-105' AND Student.Ssex = '男';
-- 43、查询和“李军”同性别的所有同学的Sname。
SELECT Sname  
FROM Student  
WHERE Ssex = (  
    SELECT Ssex  
    FROM Student  
    WHERE Sname = '李军'  
);
-- 44、查询和“李军”同性别，并同班的同学Sname

SELECT Sname  
FROM Student  
WHERE Ssex = (  
    SELECT Ssex  
    FROM Student  
    WHERE Sname = '李军'  
)  
AND Class = (  
    SELECT Class  
    FROM Student  
    WHERE Sname = '李军'  
);




-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT Student.Sname, Score.Degree  
FROM Student  
JOIN Score ON Student.Sno = Score.Sno  
JOIN Course ON Score.Cno = Course.Cno  
WHERE Course.Cname = '计算机导论' AND Student.Ssex = '男';