MySQL丨基础语法练习2

#创建表和插入数据
create database test;
use test;
#创建学生表
create table student(
	stu_id int primary key,
	name varchar(10),
	sex varchar(2),
	birthday datetime,
	class int
);

#创建课程表
create table course(
cno varchar(10),
cname varchar(10),
tno int
);

#创建成绩表
create table score(
stu_id int ,
cno varchar(10) ,
degree int
);

#创建老师表
CREATE TABLE teacher(
teach_id INT PRIMARY key auto_increment,
teach_name VARCHAR(10),
sex varchar(2),
birthday datetime,
prof varchar(10),
depart varchar(10)
);


#插入学生数据
INSERT into student values(5001,'李勇','男','1987-7-22 0:00:00',95001),
(5002,'刘晨','女','1987-11-15 0:00:00',95002),
(5003,'王敏','女','1987-10-5 0:00:00',95001),
(5004,'李好尚','男','1987-9-25 0:00:00',95003),
(5005,'李军','男','1987-7-17 0:00:00',95004),
(5006,'范新位','女','1987-6-18 0:00:00',95005),
(5007,'张霞东','女','1987-8-29 0:00:00',95006),
(5008,'赵薇','男','1987-6-15 0:00:00',95007),
(5009,'钱民将','女','1987-6-23 0:00:00',95008), 
(5010,'孙俪','女','1987-9-24 0:00:00',95002),
(108,'赵里','男','1987-6-15 0:00:00',95007),
(109,'丘处机','男','1987-6-23 0:00:00',95008),
(107,'杨康','男','1987-9-24 0:00:00',95001);

#插入课程数据


INSERT INTO course  VALUES ('3-101', '数据库', 1);
INSERT INTO course  VALUES ('5-102', '数学', 3);
INSERT INTO course  VALUES ('3-103', '信息系统', 4);
INSERT INTO course  VALUES ('3-104', '操作系统', 6);
INSERT INTO course  VALUES ('3-105', '数据结构', 4);
INSERT INTO course  VALUES ('3-106', '数据处理', 5);
INSERT INTO course  VALUES ('4-107', 'Pascal语言', 5);
INSERT INTO course  VALUES ('4-108', 'c++', 7);
INSERT INTO course  VALUES ('4-109', 'java', 8);
INSERT INTO course  VALUES ('3-245', '数据挖掘', 10);
INSERT INTO course  VALUES ('3-111', '软件工程', 11);

#插入成绩数据
INSERT INTO score VALUES (5001, '5-102', 55);
INSERT INTO score VALUES (5003, '3-105', 98);
INSERT INTO score VALUES (5004, '4-109', 68);
INSERT INTO score VALUES (5005, '3-245', 100);
INSERT INTO score VALUES (5006, '3-105', 53);
INSERT INTO score VALUES (5003, '3-105', 98);
INSERT INTO score VALUES (5008, '3-105', 98);
INSERT INTO score VALUES (5004, '4-109', 68);
INSERT INTO score VALUES (5010, '3-105', 88);
INSERT INTO score VALUES (5005, '4-109', 68);
INSERT INTO score VALUES (5002, '3-105', 88);
INSERT INTO score VALUES (107, '3-111', 88);
INSERT INTO score VALUES (108, '4-109', 68);
INSERT INTO score VALUES (109, '4-109', 80);
INSERT INTO score VALUES (109, '4-109', 80);
INSERT INTO score VALUES (107, '3-111', 88);
INSERT INTO score VALUES (5003, '3-111', 80);
INSERT INTO score VALUES (5001, '3-105', 69);
INSERT INTO score VALUES (5003, '4-109', 45);

#插入老师数据
INSERT INTO teacher  VALUES (1, '李卫', '男', '1957-11-5 00:00:00', '教授', '电子

工程系');
INSERT INTO teacher  VALUES (2, '刘备', '男', '1967-10-9 00:00:00', '副教授', 

'math');
INSERT INTO teacher  VALUES (3, '关羽', '男', '1977-9-20 00:00:00', '讲师', 'sc');
INSERT INTO teacher  VALUES (4, '李修', '男', '1957-6-25 00:00:00', '教授', 

'elec');
INSERT INTO teacher  VALUES (5, '诸葛亮', '男', '1977-6-15 00:00:00', '教授', '计

算机系');
INSERT INTO teacher  VALUES (6, '殷素素', '女', '1967-1-5 00:00:00', '副教授', 

'sc');
INSERT INTO teacher  VALUES (7, '周芷若', '女', '1947-2-23 00:00:00', '教授', 

'sc');
INSERT INTO teacher  VALUES (8, '赵云', '男', '1980-6-13 00:00:00', '副教授', '计

算机系');
INSERT INTO teacher  VALUES (9, '张敏', '女', '1985-5-5 00:00:00', '助教', 'sc');
INSERT INTO teacher  VALUES (10, '黄蓉', '女', '1967-3-22 00:00:00', '副教授', 

'sc');
INSERT INTO teacher  VALUES (11, '张三', '男', '1967-3-22 00:00:00', '副教授', 

'sc');

#3.以class降序输出student所有记录
desc student;
select * from student order by class desc;
#4.列出教师所在的单位depart（不重复）
desc teacher;
select distinct depart from teacher;
#5.列出student表中所有的记录的name,sex和class列
select name,sex,class from student;
#6.输出student中不姓王的同学
select student.name from student where student.name not like '王%';
#7.输出成绩为85,86,88或在60-80之间的记录的stu_id,cno,degree
select score.stu_id,score.cno,score.degree from score
where degree in (85,86) or degree between 60 and 80;
#8.输出班级为95001或性别为 女 的同学在student表中的所有属性
desc student;
select * from student where class=95001 or sex='女';
#9.以cno升序，degree降序输出score的所有记录。
select * from score order by cno,degree desc;
#10.输出男生人数，并统计出有男生的班级班级数
select count(student.stu_id) as '男生数',count(distinct student.class) as '班级数' 

from student;
#11.输出存在分数大于85分以上成绩的课程
desc score;
desc course;
select distinct cno from score where degree > 85
#12.输出95001班级的人数
select count(*) from student where class = 95001;
#13.输出 3-105 号课程的平均分
select avg(score.degree) from score where score.cno = '3-105';
#14.输出student中最大和最小birthday日期值
select max(student.birthday),min(student.birthday) from student;
#15.显示95001和95004班全体学生的全部个人信息（不包括选课）。student的全属性
select * from student where class in (95001,95004);
#16.输出至少有两名男同学的班级编号
select class from student where  sex='男' group by class having count(*) >= 2;
#17.列出于108号同学同年出生的同学信息
desc student;
select * from student where year(student.birthday) in (
	select year(student.birthday) from student where student.stu_id = '108'
)
#18.列出存在成绩高于85分的课程名
select course.cname from course where course.cno in (
	select score.cno from score where score.degree > 85
)
#19.列出‘计算机系’教师所教课程的成绩表,课程编号，课程名，学生名，成绩
desc score;
select distinct score.cno,course.cname,student.name,score.degree from 

score,student,course,teacher 
where teacher.depart = '计算机系' 
and teacher.teach_id = course.tno 
and course.cno = score.cno
and score.stu_id = student.stu_id ;
#20.列出所有可能的‘计算机系’与‘电子工程系’不同职称的教师配对信息，要求输出教师

姓名和职称
select t1.teach_name as name,t1.prof as prof,t2.teach_name as name,t2.prof as prof 
from teacher as t1 join teacher as t2
on t1.depart in ('计算机系','电子工程系') 
and t2.depart in ('计算机系','电子工程系')
and t1.prof != t2.prof;
#21列出所有处于不同班级中，但具有相同生日的学生，要求输出每个学生的学号和姓名
select distinct 

s1.stu_id,s1.name,s1.birthday,s1.class,s2.stu_id,s2.name,s2.birthday,s2.class
from student as s1 join student as s2
on s1.class != s2.class
and datediff(s1.birthday,s2.birthday)=0;
#22.显示由‘张三’教师任课的学生姓名，课程名，成绩
select distinct student.name,course.cname,score.degree from teacher  
join course on teacher.teach_name = '张三' and teacher.teach_id = course.tno
join score on course.cno = score.cno
join student on score.stu_id = student.stu_id;
#23.列出所讲课已被选修的教师姓名和系别
select teacher.teach_name,teacher.depart from score
join course on score.cno = course.cno
join teacher on course.tno = teacher.teach_id;
#24.列出所有任课老师的name和depart（从课程选修和任课两个角度考虑）
#本题同23
#25。输出男教师所上课程的名称
select course.cname from course
join teacher on teacher.sex = '男' and teacher.teach_id = course.tno;
#26.输出所有李军同性别学生的name
select * from student where student.name = '李军';
select student.name from student where student.sex = (
	select student.sex from student where student.name = '李军'
)
#27.输出选修‘数据结构’课程的男同学成绩
select score.degree from score
join course on course.cname = '数据结构' and course.cno = score.cno
join student on student.sex = '男' and student.stu_id = score.stu_id;
#28.列出选修编号 为‘3-105’课程并且课程成绩比课程‘3-111’的最高分还要高的

cno,stu_id和degree
select distinct * from score where score.cno = '3-105' and score.degree > (
	select max(score.degree) from score where score.cno = '3-111'
)