javascript面向对象编程指南
数据库技术之mysql50题⽬录
数据表介绍
-- 1.学⽣表
Student(SId,Sname,Sage,Ssex)
-- SId 学⽣编号,Sname 学⽣姓名,Sage 出⽣年⽉,Ssex 学⽣性别
-- 2.课程表
Course(CId,Cname,TId)
-- CId 课程编号,Cname 课程名称,TId 教师编号
-- 3.教师表
Teacher(TId,Tname)
-- TId 教师编号,Tname 教师姓名
-
- 4.成绩表
SC(SId,CId,score)
-- SId 学⽣编号,CId 课程编号,score 分数
数据SQL
-- 数据SQL
-- 学⽣表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));access数据库题
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙⻛' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');
insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥');
insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥');
insert into Student values('09' , '张三' , '2017-12-20' , '⼥');
insert into Student values('10' , '李四' , '2017-12-25' , '⼥');
insert into Student values('11' , '李四' , '2012-06-06' , '⼥');
insert into Student values('12' , '赵六' , '2013-06-13' , '⼥');
insert into Student values('13' , '孙七' , '2014-06-01' , '⼥');
-- 科⽬表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)); insert into Course values('01' , '语⽂' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)); insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
练习题⽬
1.查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数
思路：
-- 1.先分别查询01和02课程的学员id和分数
SELECT SId,score from sc WHERE CId=01;
SELECT SId,score from sc WHERE CId=02;
-- 2.对⽐两个结果，发现两个结果中有⼀些sid是不对应的
-- 因此可以对两个结果做jion联结，条件是sid要相等
-
- 并且01的成绩要⼤于02的
select s1.sid,s1.score from
(SELECT sid,score from sc WHERE cid=01) as s1
JOIN
(SELECT sid,score from sc WHERE cid=02) as s2
ON s1.sid = s2.sid
WHERE s1.score > s2.score;
-- 3.通过以上的sql，得到了符合条件的学员id和分数，再联结学⽣表，获取学员信息SELECT stu.sid,stu.sname,s.score
from student as stu
join
(
设计哪家好
select s1.sid,s1.score from
(SELECT sid,score from sc WHERE cid=01) as s1
JOIN
(SELECT sid,score from sc WHERE cid=02) as s2
ON s1.sid = s2.sid
WHERE s1.score > s2.score
) as s
on stu.sid = s.sid;
2.查询同时学习" 01 "课程和" 02 "课程的学⽣信息情况
select s1.* from
(SELECT sid,score from sc WHERE cid=01) as s1
JOIN
(SELECT sid,score from sc WHERE cid=02) as s2
ON s1.sid = s2.sid ;
3.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显⽰为 null )
select s1.*,s2.* from
(SELECT sid,score from sc WHERE cid=01) as s1
left jOIN
(SELECT sid,score from sc WHERE cid=02) as s2
ON s1.sid = s2.sid ;
4.查询不存在" 01 "课程但存在" 02 "课程的情况(存在02但不存在01的课程信息)
SELECT * FROM sc
WHERE sid not in (SELECT sid from sc WHERE cid=01)
and cid = 02;
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
select sc.sid,sname,round(avg(score),2) as avg_score
mysql面试题acidfrom sc,student
WHERE sc.SId = student.SId
GROUP BY sc.sid,sname
HAVING avg_score >=60;
#having实现的是group by分组聚合后的过滤
#在select⾥⾯有⼏列，那么在group by 后⾯就要写⼏列，否则会报1055错误
#round(avg(score),2)：把平均分精确到⼩数点后两位
6.查询在 SC 表存在成绩的学⽣信息
select DISTINCT stu.* from student as stu JOIN sc on sc.SId =stu.sid;
#DISTINCT：数据去重
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显⽰为 null )
select stu.sid,stu.sname, count(sc.cid) as num,sum(sc.score) as total_score from student as stu left join sc on stu.sid=sc.sid
GROUP BY stu.sid,stu.sname;
8.查询「李」姓⽼师的数量
SELECT ame) from (select * FROM teacher WHERE tname like '李%') as t;
9.查询学过「张三」⽼师授课的同学的信息
#sid-cid-tid-tname
#多表联结
select stu.*,sc.ame from student as stu
left join sc on stu.sid=sc.sid
left join course on sc.cid = course.cid
left join teacher as t on course.tid = t.tid
WHERE t.Tname='张三';
10.查询没有学全所有课程的同学的信息
- 1.先学过所有课程的同学
- 先统计课程表有⼏门课
- 拿成绩表按sid分组做cid的count统计
-⽤having进⾏过滤
divcss教程pdf
- 2.再⽤⼀个select语句，从student表中⽤sid不在上表的过滤条件进⾏过滤。
select DISTINCT stu.* from student as stu,sc WHERE stu.sid not in (SELECT sid from sc GROUP BY sc.SId having count(sc.cid)=3);
11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息
- 从表sc中先查01同学学的所有课程，并得到sid
- 学⽣表与成绩表sc join之后，再嵌套⼀下
SELECT DISTINCT stu.*
from student as stu
left join sc on sc.SId=stu.SId
WHERE sc.cid in(SELECT cid from sc WHERE sid = 01);
12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
#使⽤⾃联结
-
java实现接口的方法 先⽤⾃联结把⾃⼰的表与⾃⼰的表联结起来，联结条件为：左右两表的课程id相同，且左表为sid=01的信息，右表为sid!=01的信息。
- 然后⽤s2的sid和学⽣表的学⽣名字作为分组聚合的条件，
- 聚合后，⽤s2的cid的计数count，与学⽣id为01的成绩计数是否相等作为条件，对s2进⾏排序
- 加上学⽣姓名等信息，需要在group by前再把student表的sname信息join进来。
SELECT s2.sid,student.Sname
from sc as s1 join sc as s2 on s1.cid = s2.cid and s1.sid=01 and s2.sid!=01 join student on s2.sid = student.sid GROUP BY s2.sid,student.Sname HAVING count(s2.sid)=(SELECT count(*) from sc WHERE sid=01);
13.查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名
select stu.*,sc.ame from student as stu
left join sc on stu.sid=sc.sid
left join course on sc.cid = course.cid
left join teacher as t on course.tid = t.tid
WHERE t.Tname !='张三';
14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu.sid,stu.sname,avg(sc.score) as avg_score
from student as stu
join sc on stu.SId = sc.SId
WHERE sc.score<60
GROUP BY stu.sid,stu.sname HAVING count(sc.CId)>=2;
15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息
#⽤两个表join⼀下，并限制cid和score，最后再排序
select stu.*,sc.score from student as stu
join sc on stu.sid = sc.SId
WHERE cid=01 and sc.score<60
ORDER BY sc.score desc;
16.按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩
#法1：
SELECT sc.*,s2.avg_score
from sc join (SELECT sid,avg(sc.score) as avg_score from sc GROUP BY sid) as s2 on sc.sid = s2.sid
ORDER BY s2.avg_score desc ;
#法2：
select
stu.sname,
a.score as '语⽂',
b.score as '数学',
c.score as '英语',
avg(d.score) as '平均成绩'
from student as stu
left join sc as a on stu.sid = a.sid and a.cid = '01'
left join sc as b on stu.sid = b.sid and b.cid = '02'
left join sc as c on stu.sid = c.sid and c.cid = '03'
left join sc as d on stu.sid = d.sid
group by stu.sname，语⽂，数学，英语
order by 平均成绩 desc;
17.查询各科成绩最⾼分、最低分和平均分：
以如下形式显⽰：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。