1. 建立表 4个表关系 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_s_score) –学生编号,课程编号,分数
-- 学生表 CREATE TABLE Student( s_id VARCHAR(20), s_name VARCHAR(20) NOT NULL DEFAULT '', s_birth VARCHAR(20) NOT NULL DEFAULT '', s_sex VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(s_id) ); -- 课程表 CREATE TABLE Course( c_id VARCHAR(20), c_name VARCHAR(20) NOT NULL DEFAULT '', t_id VARCHAR(20) NOT NULL, PRIMARY KEY(c_id) ); -- 教师表 CREATE TABLE Teacher( t_id VARCHAR(20), t_name VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(t_id) ); -- 成绩表 CREATE TABLE `Score`( s_id VARCHAR(20), c_id VARCHAR(20), s_score INT(3), PRIMARY KEY(s_id,c_id) ); 1.1 插入对应的数据 -- 插入学生表测试数据 insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女'); insert into Student values('09' , '如花' , '1991-02-15' , '女'); -- 课程表测试数据 insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03'); insert into Course values('04' , '体育' , '01'); -- 教师表测试数据 insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五'); -- 成绩表测试数据 insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98); insert into Score values('09', '01', 50); insert into Score values('09', '02', 40); insert into Score values('09', '03', 90); insert into Score values('09', '04', 99); 2. SQL练习 查询姓"张"老师的个数 select count(t_id) from Teacher where t_name like "张%"; 查询1990年出生的学生名单 select s_id, s_name from Student where year(s_birth)=1990; 查询课程编号为"02"的总成绩 select c_id,sum(s_score) as '总成绩' from Score where c_id=02; 查询选了课程的学生人数 思路学生唯一, 然后再计算总数 select count(distinct s_id) as "选课人数" from Score; 查询各科成绩最高和最低的分: 以如下的形式显示: 课程ID, 最高分, 最低分 select c_id as 课程ID, max(s_score) as 最高分, min(s_score) as 最低分 from Score group by c_id; 查询每门课程被选修的学生数 select c_id, count(s_id) from Score group by c_id; 查询男生,女生人数 select s_sex as 性别, count(s_id) as 人数 from Student group by s_sex; 查询平均成绩大于60分的学生的学号和平均成绩 学号与平均成绩都在Score表中可以获取到
...