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;
持续更新。。。
