20道mysql

表名和字段

–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);

SELECT * from course; -- 2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

SELECT * from score; -- 4.成绩表Score(s_id,c_id,s_score) –学生编号,课程编号,分数

SELECT * from student;-- –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

SELECT * from teacher; -- 教师表  Teacher(t_id,t_name) –教师编号,教师姓名

select 查询条件 from 表名 where 表1，2之间相同的联系 group by(分组)...having (对没有的加一起的列名提出的条件)order by （排序）

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select  e.score,s.*  from score e ,student s

where 

select s.*,e.s_score,a.s_score from student s ,score e,score a

where e.s_id = s.s_id and a.s_id=s.s_id and e.c_id='01' and a.c_id='02' and a.s_score<e.s_score

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select s.*,e.s_score,a.s_score from student s ,score e,score a

where e.s_id = s.s_id and a.s_id=s.s_id and e.c_id='01' and a.c_id='02' and a.s_score>e.s_score

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.s_id,s.s_name,avg(e.s_score)  FROM student s,score e

WHERE e.s_id=s.s_id HAVING AVG(e. s_score)>=60

-- 4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT  s.s_id,s.s_name,COUNT(c_id),SUM(e.s_score)  FROM  student s ,score e

where s.s_id=e.s_id group by s.s_id,s.s_name

-- 5、查询"李"姓老师的数量

SELECT * from course; -- 2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

SELECT * from score; -- 4.成绩表Score(s_id,c_id,s_score) –学生编号,课程编号,分数

SELECT * from student;-- –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

SELECT * from teacher; -- 教师表  Teacher(t_id,t_name) –教师编号,教师姓名

select count(t_name) from teacher where t_name like '李%'

-- 6、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select s.* from student s, score e, score a

WHERE s.s_id=e.s_id and s.s_id=a.s_id

and  e.s_id='01' and a.s_id= '02'

-- 7、检索"01"课程分数小于60，按分数降序排列的学生信息

select s.*,e.s_score from student s,score e where s.s_id=e.s_id and e.c_id='01' and e.s_score<60 ORDER BY e.s_score DESC

-- 8、查询不同老师所教不同课程平均分从高到低显示

select t.t_name,AVG( e.s_score),c.c_name from teacher t,score e ,course c

where c.c_id=e.c_id and c.t_id=t.t_id  GROUP BY t.t_name

ORDER BY avg(e.s_score) DESC

-- 9、查询每门课程被选修的学生数

SELECT e.c_id,COUNT( *) from score e group by e.c_id

-- 10、查询男生、女生人数

select s_sex,COUNT(*) from student group by s_sex

-- 11、查询名字中含有"风"字的学生信息

SELECT * from course; -- 2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

SELECT * from score; -- 4.成绩表Score(s_id,c_id,s_score) –学生编号,课程编号,分数

SELECT * from student;-- –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

SELECT * from teacher; -- 教师表  Teacher(t_id,t_name) –教师编号,教师姓名

SELECT * from student  where s_name like '风'

-- 12、查询同名同性学生名单，并统计同名人数

SELECT s_name,COUNT( *) from student GROUP by s_name HAVING COUNT(*)>1

-- 13、查询1990年出生的学生名单

SELECT * from student where s_birth=1990

-- 14、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select AVG(e.s_score),c.c_name from score e, course c WHERE e.c_id=c.c_id  GROUP BY c.c_name ORDER BY AVG( e.s_score) DESC , e.c_ID ASC

-- 15、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select s.s_id,s.s_name,AVG(e.s_score) FROM student s,score e where s.s_id=e.s_id GROUP BY e.s_id HAVING AVG( e.s_score)>85

-- 16、查询不及格的课程

select c.c_name,s.s_name,e.s_score  from student s,course c, score e where c.c_id=e.c_id and s.s_id=e.s_id  and e.s_score<60

-- 17、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select s.s_id,s.s_name from student s, score e where e.s_score>80 and e.s_id='01' GROUP BY s.s_id

-- 18、求每门课程的学生人数

SELECT * from course; -- 2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

SELECT * from score; -- 4.成绩表Score(s_id,c_id,s_score) –学生编号,课程编号,分数

SELECT * from student;-- –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

SELECT * from teacher; -- 教师表  Teacher(t_id,t_name) –教师编号,教师姓名

select c_id,count(*) as "学生人数"

from score

GROUP BY C_Id

-- 19、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select C_Id,count(*) as "学生人数"

from score

GROUP BY "学生人数",c_id ORDER BY "学生人数" DESC ,s_score ASC

-- 20、检索至少选修两门课程的学生学号

select s_id from score GROUP BY s_score HAVING COUNT( *)>1

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,088评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,715评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,361评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,099评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 60,987评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,063评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,486评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,175评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,440评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,518评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,305评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,190评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,550评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,880评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,152评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,451评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,637评论 2赞 335

推荐阅读更多精彩内容