vs做的本地网站,触屏网页界面设计,网页设计图片大小怎么调,网站流量统计系统数据库表名和字段设计
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) 学生编号…数据库表名和字段设计
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( 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) );
插入测试数据
学生表
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 Course values(01 , 语文 , 02);
insert into Course values(02 , 数学 , 01);
insert into Course values(03 , 英语 , 03);
课程表
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);
常见题目
1、查询01课程比02课程成绩高的学生的信息及课程分数
select a.*,b.s_score as 01_score,c.s_score as 02_score from
student ajoin score b on a.s_idb.s_id and b.c_id01left join score c on a.s_idc.s_id and c.c_id02 or c.c_id null whereb.s_scorec.s_score;
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score fromstudent bjoin score a on b.s_id a.s_idGROUP BY b.s_id,b.s_name HAVING avg_score 60;
3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-- (包括有成绩的和无成绩的)
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score fromstudent bleft join score a on b.s_id a.s_idGROUP BY b.s_id,b.s_name HAVING avg_score 60union
select a.s_id,a.s_name,0 as avg_score from student awhere a.s_id not in (select distinct s_id from score);
4、查询学过张三老师授课的同学的信息
select a.* fromstudent ajoin score b on a.s_idb.s_id where b.c_id in(select c_id from course where t_id (select t_id from teacher where t_name 张三));
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from student aleft join score b on a.s_id b.s_idGROUP BY a.s_id,a.s_name;
6、查询学过编号为01并且也学过编号为02的课程的同学的信息
select a.* fromstudent a,score b,score cwhere a.s_id b.s_id and a.s_id c.s_id and b.c_id 01 and c.c_id02;
7、查询学过编号为01但是没有学过编号为02的课程的同学的信息
select a.* fromstudent awhere a.s_id in (select s_id from score where c_id01) and a.s_id not in (select s_id from score where c_id02);
