四张表:
1.学生表 Student(s_id,s_name,s_birth,s_sex)
2.课程表Course(c_id,c_name,t_id)
3.教师表Teacher(t_id,t_name)
4.成绩表Score(s_id,c_id,s_score)
建表语句:
创建学生表并且往表中插入语句
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stu_name` VARCHAR(16) NOT NULL DEFAULT '0' COMMENT '学生姓名',
`stu_age` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '学生年龄',
`stu_sex` VARCHAR(50) NULL DEFAULT NULL COMMENT '性别,1男,2女',
PRIMARY KEY (`id`)
)
COMMENT='学生表';
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (1, '赵雷', '1990-01-01', '男');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (2, '钱电', '1990-12-21', '男');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (3, '孙风', '1990-05-20', '男');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (4, '李云', '1990-08-06', '男');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (5, '周梅', '1991-12-01', '女');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (6, '吴兰', '1992-03-01', '女');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (7, '郑竹', '1989-07-01', '女');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (8, '王菊', '1990-01-20', '女');
INSERT INTO `student` (`id`, `stu_name`, `stu_age`, `stu_sex`) VALUES (9, '冯丽', '1990-01-26', '女');
创建课程表,并且插入数据:
CREATE TABLE `course` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_name` VARCHAR(50) NULL DEFAULT '0' COMMENT '课程名称',
`t_id` INT(11) NULL DEFAULT '0' COMMENT '教师id',
PRIMARY KEY (`id`)
)
COMMENT='课程表'
COLLATE='utf8_general_ci';
INSERT INTO `course` (`id`, `course_name`, `t_id`) VALUES (1, '语文', 2);
INSERT INTO `course` (`id`, `course_name`, `t_id`) VALUES (2, '数学', 1);
INSERT INTO `course` (`id`, `course_name`, `t_id`) VALUES (3, '英语', 3);
INSERT INTO `course` (`id`, `course_name`, `t_id`) VALUES (4, '物理', 4);
INSERT INTO `course` (`id`, `course_name`, `t_id`) VALUES (5, '生物', 4);
创建成绩表,并且插入数据:
CREATE TABLE `score` (
`student_id` INT(11) NULL DEFAULT NULL COMMENT '学生id',
`course_id` INT(11) NULL DEFAULT NULL COMMENT '课程id',
`score` INT(11) NULL DEFAULT NULL COMMENT '分数',
UNIQUE INDEX `course_id_student_id` (`student_id`, `course_id`)
)
COMMENT='成绩表'
COLLATE='utf8_general_ci';
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (1, 2, 90);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (8, 3, 89);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (5, 2, 87);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (3, 5, 86);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (1, 3, 86);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (3, 1, 81);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (3, 2, 80);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (3, 3, 80);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (2, 3, 80);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (5, 1, 76);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (5, 3, 69);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (2, 2, 60);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (6, 3, 59);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (6, 4, 59);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (4, 3, 59);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (4, 1, 50);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (2, 4, 50);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (6, 1, 31);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (4, 2, 30);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (9, 3, 30);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (7, 3, 30);
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES (7, 4, 25);
创建教师表,并且插入数据:
CREATE TABLE `teacher` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`t_name` VARCHAR(50) NOT NULL COMMENT '教师名称',
PRIMARY KEY (`id`)
)
COMMENT='教师表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;
INSERT INTO `teacher` (`id`, `t_name`) VALUES (1, '张三');
INSERT INTO `teacher` (`id`, `t_name`) VALUES (2, '李四');
INSERT INTO `teacher` (`id`, `t_name`) VALUES (3, '王五');
INSERT INTO `teacher` (`id`, `t_name`) VALUES (4, '叶平');
题目1:查询哪些学生的01这门课的成绩比02这门课成绩高
只用到一张表,成绩表Score,这张表里面有三个字段,s_id,c_id,s_score
考察自连接:
都是score分数表,一张给它起别名叫表a,专门用来查01这门课程的分数,另一张给它起别名叫表b,专门用来查02这门课程的分
select *
from score a
where a.c_id='01'
select *
from score b
where b.c_id='02'
现在连接两张表:
SELECT a.s_id,a.s_score course01,b.s_score course02
FROM score a,score b
WHERE a.c_id='01' AND b.c_id='02' AND a.s_id=b.s_id AND a.s_score>b.s_score
题目2:查询平均成绩大于等于60分的同学的学生编号和平均成绩
这道题显然直接告诉你最后结果需要呈现两列 学生编号,平均成绩
用到score这张表:
主要可以学到group by进行分组
select a.s_id,
avg(a.s_score) avg_s
from score a
group by a.s_id
select a.s_id,
avg(a.s_score) avg_s
from score a
group by a.s_id
having avg(a.s_score)>=60
如果还要查询出学生的姓名,则还要连接学生表student这张表
select a.s_id,
avg(a.s_score) avg_s
s.s_name
from score a,student s
where a.s_id=s.s_id
group by a.s_id
having avg(a.s_score)>=60