CREATE TABLE student(
s_no INT(20) PRIMARY KEY COMMENT'学生学号',
s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
s_birthday DATETIME COMMENT'学生生日',
s_class VARCHAR(20) COMMENT'学生所在的班级'
);

CREATE TABLE teacher(
t_no INT(20) PRIMARY KEY COMMENT'教师编号',
t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
t_birthday DATETIME COMMENT'教师生日',
t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);

CREATE TABLE course(
c_no INT(20) PRIMARY KEY COMMENT "课程号",
c_name VARCHAR(20) NOT NULL COMMENT "课程名称",
t_no INT(20) NOT NULL COMMENT "教师编号",
FOREIGN KEY(t_no) REFERENCES teacher(t_no)
);

CREATE TABLE score (
s_no INT(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
c_no INT(20) NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
sc_degree decimal,
foreign key(s_no) references student(s_no),
foreign key(c_no) references course(c_no),
PRIMARY KEY(s_no,c_no)
);

学生表数据

INSERT INTO student VALUES(101,'曾华','男','1977-09-01','95033');
INSERT INTO student VALUES(102,'匡明','男','1975-10-02','95031');
INSERT INTO student VALUES(103,'王丽','女','1976-01-23','95033');
INSERT INTO student VALUES(104,'李军','男','1976-02-20','95033');
INSERT INTO student VALUES(105,'王芳','女','1975-02-10','95031');
INSERT INTO student VALUES(106,'陆军','男','1974-06-03','95031');
INSERT INTO student VALUES(107,'王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES(108,'张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES(109,'赵铁柱','男','1974-06-03','95031');
INSERT INTO student VALUES(110,'张飞','男','1974-06-03','95038');

教师表数据

INSERT INTO teacher VALUES(804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES(856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES(825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES(831,'刘冰','女','1977-08-14','助教','电子工程系');

添加课程表

INSERT INTO course VALUES(3105,'计算机导论',825);
INSERT INTO course VALUES(3245,'操作系统',804);
INSERT INTO course VALUES(6166,'数字电路',856);
INSERT INTO course VALUES(9888,'高等数学',831);

添加成绩表

INSERT INTO score VALUES(103,3245,'86');
INSERT INTO score VALUES(105,3245,'75');
INSERT INTO score VALUES(109,3245,'68');
INSERT INTO score VALUES(103,3105,'92');
INSERT INTO score VALUES(105,3105,'88');
INSERT INTO score VALUES(109,3105,'76');
INSERT INTO score VALUES(103,6166,'85');
INSERT INTO score VALUES(105,6166,'79');
INSERT INTO score VALUES(109,6166,'81');

INSERT INTO score VALUES(101,3105,'90');
INSERT INTO score VALUES(102,3105,'91');
INSERT INTO score VALUES(104,3105,'89');

1.查询student表中所有记录的s_name,s_sex和s_class列

SELECT s_name, s_sex, s_class FROM student;

2.查询教师所有的单位但是不重复的t_depart列

SELECT DISTINCT t_depart from teacher;

3.查询score表中成绩在60-80之间所有的记录(sc_degree),BETWEEN是包含边界的

SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;

SELECT * FROM score WHERE sc_degree > 60 AND sc_degree < 80;

4.查询score表中成绩为85, 86, 或者88的记录(sc_degree)

SELECT * FROM score WHERE sc_degree IN (85,86,88);

SELECT * FROM score WHERE sc_degree = 85 OR sc_degree = 86 OR sc_degree = 88;

5.查询student表中'95031'班或者性别为'女'的同学记录

SELECT * FROM student WHERE s_class = '95031' OR s_sex = '女';

6.以s_class降序查询student表中所有的记录

SELECT * FROM student ORDER BY s_class DESC;

7.以c_no升序.sc_degree降序插叙score表中所有的数据

SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;

8.查询'95031'班的学生人数

SELECT COUNT(*) FROM student WHERE s_class = '95031';

9.查询score表中的最高分数的学生号和课程号.

SELECT s_no, c_no, sc_degree FROM score WHERE sc_degree IN (SELECT MAX(sc_degree) FROM score);

如果只有一条最高分的数据还可以这样写

SELECT s_no, c_no, sc_degree FROM score ORDER BY sc_degree DESC LIMIT 0,1;

10.查询每门课的平均成绩

SELECT c_no, AVG(sc_degree) FROM score;

11.查询score表中至少有2名学生选修的,并且课程号以3开头的课程的平均分

SELECT c_no, AVG(sc_degree) from score WHERE c_no LIKE "3%" GROUP BY c_no HAVING COUNT(c_no) >= 2;

12.查询所有的学生 s_name , c_no, sc_degree列

SELECT s_name, c_no, sc_degree FROM student s, score sc WHERE s.s_no = sc.s_no;

13.查询所有学生的s_name, c_name, sc_degree列

SELECT student.s_name, course.c_name, score.sc_degree FROM student, score, course WHERE student.s_no = score.s_no AND course.c_no = score.c_no;

14.查询班级是'95031'班学生每门课的平均分

SELECT c_no, AVG(sc_degree) FROM score sc,student s WHERE s.s_class = '95031' AND sc.s_no = s.s_no GROUP BY c_no;

SELECT c_no, AVG(sc_degree) FROM score WHERE s_no IN (SELECT s_no FROM student WHERE s_class = '95031') GROUP BY c_no;

SELECT c_no, AVG(sc_degree) FROM score sc LEFT JOIN student s ON sc.s_no=s.s_no WHERE s.s_class = '95031' GROUP BY c_no;

15.查询选修"3105"课程的成绩高于'109'号同学'3105'成绩 的所有同学的记录

SELECT s.*, sc.c_no, sc.sc_degree FROM student s LEFT JOIN score sc ON s.s_no = sc.s_no WHERE sc.c_no = 3105
AND sc.sc_degree > (SELECT sc_degree FROM score sc2 WHERE sc2.c_no = 3105 AND sc2.s_no = 109);

16.查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

SELECT s_no, s_name, s_birthday FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no = 101 OR s_no = 108);

SELECT s_no, s_name, s_birthday FROM student WHERE EXTRACT(YEAR FROM s_birthday)IN (SELECT EXTRACT(YEAR FROM s_birthday) FROM student WHERE s_no = 101 OR s_no = 108);

函数YEAR:返回年份; 函数EXTRACT(type FROM d):从日期d中获取指定的值,type指定返回的值。type可取值为YEAR,MOUTH,DAY等等不一一列举

17.查询 张旭 教师任课的学生的成绩

SELECT * FROM score WHERE c_no IN(SELECT c_no FROM course WHERE t_no IN(SELECT t_no FROM teacher WHERE t_name = '张旭'));

18.查询选修课程的同学人数多于 5 人的教师姓名

SELECT * FROM teacher WHERE t_no IN (SELECT t_no FROM course WHERE c_no IN(SELECT c_no FROM score HAVING COUNT(s_no) > 5));

19.查询存在85分以上成绩的课程名c_name及对应老师

SELECT t.t_name, c.c_name, sc.sc_degree FROM teacher t, course c, score sc WHERE t.t_no = c.t_no AND c.c_no = sc.c_no AND
sc.sc_degree > 85;

SELECT t.t_name, c.c_name, sc.sc_degree FROM teacher t LEFT JOIN course c ON t.t_no = c.t_no LEFT JOIN score sc ON
c.c_no = sc.c_no WHERE sc.sc_degree > 85;

持续更新。。。