A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

本帖最后由 小刀葛小伦 于 2018-5-3 15:18 编辑

        大家都知道,在面试中有一个重要环节就是笔试,而笔试中有个重要的环节,就是sql语句

          可见sql语句,是我们找工作当中非常重要的知识点,很多小伙伴面对众多的sql不知道如何选择!
     那么好!
     
     福利来了
,大家找工作前,把这个sql语句练好,就没问题了!
第一:创建表和表关系,并插入数据
注意:这里的数据是少量的数据,大家可以多加一些,导入你最喜欢的三国人物数据就ok
[SQL] 纯文本查看 复制代码
--班级表--
CREATE TABLE class(     
cid INT NOT NULL,    
caption VARCHAR(10), 
PRIMARY KEY(cid)
);
INSERT INTO class VALUES(1,"三年二班"),(2,"一年三班"),(3,"三年一班");
--老师表--
CREATE TABLE teacher(     
tid INT NOT NULL,    
tname VARCHAR(10),
PRIMARY KEY(tid) 
);
INSERT INTO teacher VALUES(1,"曹老师"),(2,"刘老师"),(3,"孙老师");
--学生表--
CREATE TABLE student(     
sid INT NOT NULL,  
sname VARCHAR(10),
gender VARCHAR(10),  
class_id INT,
PRIMARY KEY(sid),
FOREIGN KEY(class_id) REFERENCES class(cid)
);
INSERT INTO student VALUES(1,"大乔","女",1),(2,"小乔","女",1),(3,"关羽","男",2);
--课程--
CREATE TABLE course(     
cid INT NOT NULL,    
cname VARCHAR(10), 
teacher_id INT,
PRIMARY KEY(cid),
FOREIGN KEY(teacher_id) REFERENCES teacher(tid)
);
INSERT INTO course VALUES(1,"骑马",1),(2,"射箭",1),(3,"画画",2);
--成绩表--
CREATE TABLE score(     
sid INT NOT NULL,    
student_id INT,
corse_id INT,
number INT,
PRIMARY KEY(sid),
FOREIGN KEY(student_id) REFERENCES student(sid),
FOREIGN KEY(corse_id) REFERENCES course(cid)
);
INSERT INTO score VALUES (1,1,1,60),(2,1,2,59),(3,2,2,100);


第二:按照如下条件,进行操作

1、自行创建测试数据
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;
3、查询平均成绩大于60分的同学的学号和平均成绩;
4、查询所有同学的学号、姓名、选课数、总成绩;
5、查询姓“李”的老师的个数;
6、查询没学过“叶平”老师课的同学的学号、姓名;
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
10、查询有课程成绩小于60分的同学的学号、姓名;
11、查询没有学全所有课的同学的学号、姓名;
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名;
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;
15、删除学习“叶平”老师课的SC表记录;
16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩;
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
20、课程平均分从高到低显示(现实任课老师);
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
22、查询每门课程被选修的学生数;
23、查询出只选修了一门课程的全部学生的学号和姓名;
24、查询男生、女生的人数;
25、查询姓“张”的学生名单;
26、查询同名同姓学生名单,并统计同名人数;
27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;
29、查询课程名称为“数学”,且分数低于60的学生姓名和分数;
30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
31、求选了课程的学生人数
32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩;
33、查询各个课程及相应的选修人数;
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
35、查询每门课程成绩最好的前两名;
36、检索至少选修两门课程的学生学号;
37、查询全部学生都选修的课程的课程号和课程名;
38、查询没学过“叶平”老师讲授的任一门课程的学生姓名;
39、查询两门以上不及格课程的同学的学号及其平均成绩;
40、检索“004”课程分数小于60,按分数降序排列的同学学号;
41、删除“002”同学的“001”课程的成绩;

第三:先自行完成上面的题目,然后再对比如下的sql,进行检查!~

[SQL] 纯文本查看 复制代码
1、自行创建测试数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;
    SELECT * FROM
        (SELECT score.student_id,course.cname,score.num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="生物") AS A
     INNER JOIN 
        (SELECT score.student_id,course.cname,score.num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="物理") AS B
        on A.student_id = B.student_id WHERE A.num > B.num;
        
3、查询平均成绩大于60分的同学的学号和平均成绩;
    SELECT B.student_id,student.sname,B.s_num FROM (SELECT student_id,avg(num) AS s_num from score GROUP BY student_id HAVING s_num>60) as B
        LEFT JOIN student on B.student_id = student.sid;  取前三: %ORDER BY s_num desc LIMIT 3;

4、查询所有同学的学号、姓名、选课数、总成绩;
    SELECT score.student_id,student.sname,COUNT(score.course_id) AS courses,SUM(num) as T_Score FROM score 
        LEFT JOIN student on score.student_id=student.sid GROUP BY score.student_id;
        
5、查询姓“李”的老师的个数;
    SELECT * from teacher WHERE tname like "李%";
    
6、查询没学过“李平”老师课的同学的学号、姓名;
    SELECT student.sid,student.sname FROM student WHERE student.sid NOT IN (
        SELECT score.student_id FROM score LEFT JOIN course ON score.course_id = course.cid WHERE score.course_id IN (
            SELECT course.cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname LIKE "李平%") GROUP BY score.student_id)
    
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
    SELECT B.sid,B.sname FROM(
        SELECTstudent.sid,score.course_id,student.sname FROM score
            LEFT JOIN student ON score.student_id = student.sid WHERE score.course_id BETWEEN 1 AND 2) AS B
        GROUP BY B.sid HAVING COUNT(B.course_id)=2;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
    SELECT student.sid,student.sname FROM score LEFT JOIN student ON score.student_id=student.sid WHERE score.course_id in 
        (SELECT course.cid FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname LIKE "李平%") 
            GROUP BY student.sid HAVING COUNT(student_id)=2;

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
    SELECT A.sid,A.sname,A.num as score_2,B.num as score_1 FROM
        (SELECT student.sid,student.sname,score.num FROM score LEFT JOIN student on score.student_id=student.sid WHERE score.course_id=2) AS A
        INNER JOIN
        (SELECT student.sid,student.sname,score.num FROM score LEFT JOIN student on score.student_id=student.sid WHERE score.course_id=1) AS B
            ON A.sid = B.sid WHERE A.num<B.num;
    
10、查询有课程成绩小于60分的同学的学号、姓名;
    SELECT student.sid,student.sname FROM student WHERE sid IN
        (SELECT score.student_id FROM score WHERE score.num<60 GROUP BY student_id);

11、查询没有学全所有课的同学的学号、姓名;
    SELECT student.sid,student.sname FROM student WHERE sid in 
        (SELECT student_id FROM score GROUP BY student_id HAVING count(course_id)!=(SELECT COUNT(cid) FROM course));
    
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
    SELECT student.sid,student.sname FROM student where student.sid in 
        (SELECT score.student_id FROM score WHERE course_id in 
            (SELECT score.course_id FROM score WHERE student_id=1) and student_id !=1 GROUP BY student_id);
    
13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名;
    SELECT student.sid,student.sname FROM student where student.sid in 
        (SELECT score.student_id FROM score WHERE course_id in 
            (SELECT score.course_id FROM score WHERE student_id=1) and student_id !=1 GROUP BY student_id 
                having count(1) =(SELECT count(score.course_id) FROM score WHERE student_id=1));

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;
        
    SELECT sid AS 学号,sname AS 姓名 FROM student WHERE sid IN(
    SELECT student_id FROM score WHERE student_id IN (
        SELECT student_id FROM score WHERE student_id!=2 GROUP BY student_id 
            HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id=2))
                AND course_id in (SELECT course_id FROM score WHERE student_id=2) 
                    GROUP BY student_id HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id=2))

15、删除学习“叶平”老师课的SC表记录;
    DELETE FROM score WHERE course_id IN
        (SELECT cid FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname="李平老师")

16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩; 
    1、INSERT into score(student_id,course_id,num) 
            SELECT student_id,2,(SELECT AVG(num) FROM score WHERE course_id=2) FROM score WHERE course_id!=2 group by student_id
            
    --2、SELECT AVG(num) FROM score WHERE course_id=2 --
    
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;
    SELECT student_id AS 学生ID,
        (SELECT num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="语文" AND student_id=s1.student_id) AS 语文,
        (SELECT num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="数学" AND student_id=s1.student_id) AS 数学,
        (SELECT num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="英语" AND student_id=s1.student_id) AS 英语
            FROM score as s1 GROUP BY student_id;
    
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
    SELECT score.course_id AS 课程ID,course.cname as 课程名,MAX(num) as 最高分,MIN(num) as 最低分 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id;
        

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
    
    平均分:
    SELECT score.course_id AS 课程ID,course.cname as 课程名,AVG(num) AS 平均分 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id ORDER BY 平均分 ASC;
    及格率:
    SELECT A.cid,A.cname,ROUND(B.s/A.ss*100,1)+% AS 及格率 FROM
        (SELECT cid,cname,COUNT(course_id) AS ss FROM score LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id) as A
            INNER JOIN
        (SELECT course_id,COUNT(num) as s FROM score WHERE score.num>60 GROUP BY course_id) as B ON A.cid=B.course_id ORDER BY 及格率 DESC;
    整合:
    SELECT x.课程ID,x.课程名,x.平均分,xx.及格率 FROM
    (SELECT score.course_id AS 课程ID,course.cname as 课程名,AVG(num) AS 平均分 FROM score 
            LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id ORDER BY 平均分 ASC) AS x 
    LEFT JOIN
    (SELECT A.cid,A.cname,ROUND(B.s/A.ss*100,1) AS 及格率 FROM
        (SELECT cid,cname,COUNT(course_id) AS ss FROM score LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id) as A
            INNER JOIN
        (SELECT course_id,COUNT(num) as s FROM score WHERE score.num>60 GROUP BY course_id) as B ON A.cid=B.course_id ORDER BY 及格率 DESC) AS xx
ON x.课程ID=xx.cid 
    
    标准:
    SELECT course_id,AVG(num) AS 平均分,ROUND(sum(CASE WHEN num<60 then 0 ELSE 1 END)/sum(1)*100,1) AS 及格率 
        FROM score GROUP BY course_id ORDER BY 平均分 ASC,及格率 DESC;


20、课程平均分从高到低显示(现实任课老师);
    SELECT course.cname,teacher.tname,xxx.平均分 FROM course
        LEFT JOIN teacher ON course.teacher_id=teacher.tid 
        LEFT JOIN (SELECT course_id,AVG(num) AS 平均分 FROM score GROUP BY course_id) AS xxx ON xxx.course_id=course.cid
        ORDER BY xxx.平均分 DESC

21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 
    SELECT s1.cid AS 课程ID,s1.cname AS 课程,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 2,1) AS 第三
    FROM course AS s1    
        
22、查询每门课程被选修的学生数;
    SELECT course.cid AS 课程ID,course.cname AS 课程名,COUNT(1) AS 学生数 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id; 
23、查询出只选修了一门课程的全部学生的学号和姓名;
    SELECT student.sid AS 学号,student.sname AS 姓名 FROM student
        WHERE sid IN (SELECT student_id FROM score GROUP BY student_id HAVING count(1)=1)    
        
24、查询男生、女生的人数;
    SELECT gender AS 性别,COUNT(1) AS 人数 FROM student GROUP BY gender;

25、查询姓“张”的学生名单;
    SELECT * FROM student WHERE sname LIKE "张%"
    
26、查询同名同姓学生名单,并统计同名人数;
    SELECT sname As 姓名,COUNT(1) AS 人数 FROM student GROUP BY sname; 

27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
    SELECT
        course.cid AS 课程ID,
        course.cname AS 课程名,
        AVG(IF(ISNULL(num),0,score.num)) AS 平均分
    FROM score LEFT JOIN course ON score.course_id = course.cid 
        GROUP BY score.course_id ORDER BY 平均分 ASC,课程ID DESC;
    
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;
    SELECT student.sid AS 学号,student.sname AS 姓名,AVG(if(isnull(num),0,score.num)) AS 平均分 
    FROM score LEFT JOIN student ON score.student_id=student.sid 
        GROUP BY score.student_id HAVING 平均分>85;
    
29、查询课程名称为“数学”,且分数低于60的学生姓名和分数;
    SELECT student.sid AS 学号,student.sname AS 姓名,score.num AS 成绩 FROM score 
        LEFT JOIN course ON score.course_id=course.cid
        LEFT JOIN student ON score.student_id= student.sid
            WHERE course.cname="数学" AND score.num<60;

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
    SELECT student.sid AS 学号,student.sname AS 姓名 FROM student WHERE sid IN 
        (SELECT student_id FROM score WHERE score.course_id =3 AND num > 80) 

31、求选了课程的学生人数
    SELECT COUNT(A.student_id) AS 总人数 
        FROM (SELECT student_id FROM score GROUP BY student_id) AS A

    SELECT COUNT(DISTINCT student_id) AS 总人数 FROM score;
    
32、查询选修“杨艳”老师(这个老师没有,就以张磊老师举例)所授课程的学生中,成绩最高的学生姓名及其成绩;
    SELECT student.sid AS 学号,student.sname AS 姓名,num AS 成绩 FROM score 
        LEFT JOIN course ON score.course_id=course.cid
        LEFT JOIN student ON score.student_id=student.sid
        LEFT JOIN teacher ON course.teacher_id=teacher.tid
            WHERE teacher.tname = "张磊老师" ORDER BY num DESC LIMIT 1;
    
33、查询各个课程及相应的选修人数;
    SELECT course_id AS ID,course.cname AS 课程,count(1) AS 人数 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
    SELECT DISTINCT s1.student_id,s1.course_id,s1.num FROM score AS s1,score AS s2
        WHERE s1.student_id    != s2.student_id AND s1.course_id!=s2.course_id AND s1.num=s2.num 

35、查询每门课程成绩最好的前两名;
    SELECT cid AS 课程ID,cname AS 课程,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二
    FROM course AS s1

36、检索至少选修两门课程的学生学号;
    SELECT student_id FROM score GROUP BY student_id HAVING count(course_id)>=2

37、查询全部学生都选修的课程的课程号和课程名;
    SELECT course.cid AS 课程号,course.cname AS 课程名 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id
            HAVING COUNT(student_id)=(SELECT COUNT(sid) FROM student)

38、查询没学过“李平”老师讲授的任一门课程的学生姓名;
    SELECT student.sid,student.sname FROM student
        WHERE student.sid not IN (SELECT student_id FROM score 
            WHERE course_id IN(SELECT cid FROM course 
                LEFT JOIN teacher ON course.teacher_id=teacher.tid 
                    WHERE teacher.tname="李平老师" ) GROUP BY student_id)
        
39、查询两门以上不及格课程的同学的学号及其平均成绩;
    SELECT score.student_id AS 学号,student.sname AS 姓名,
    (SELECT AVG(if(ISNULL(A.num),0,A.num)) FROM score AS A
        WHERE A.student_id IN (SELECT student_id FROM score WHERE num<60 
            GROUP BY student_id HAVING COUNT(1)>=2)) AS 平均成绩 
    FROM score LEFT JOIN student ON score.student_id=student.sid 
        WHERE num<60 GROUP BY student_id HAVING COUNT(1)>2
    
标准:    
    SELECT student_id AS 学号,sname AS 姓名,AVG(num) AS 平均成绩 FROM score
    LEFT JOIN student ON score.student_id=student.sid
        WHERE student_id IN (SELECT student_id FROM score 
            WHERE num<60 GROUP BY student_id HAVING COUNT(1)>=2) 
                GROUP BY student_id
        
40、检索“004”课程分数小于60,按分数降序排列的同学学号;
    SELECT student_id,num FROM score 
        WHERE score.course_id=4 AND num <60 ORDER BY num ASC

41、删除“002”同学的“001”课程的成绩;
    delete from score where student_id=2 and corse_id=1;

点评

我听说好像是十五个字能获得经验  发表于 2018-5-3 16:48

评分

参与人数 1黑马币 +1 收起 理由
胡庆 + 1

查看全部评分

18 个回复

倒序浏览
我是几楼?
回复 使用道具 举报
回复 使用道具 举报
Can_Chen 来自手机 初级黑马 2018-5-3 16:44:04
板凳
盖楼盖楼
回复 使用道具 举报
Lock2333 来自手机 初级黑马 2018-5-3 16:44:33
报纸
水水水水水水水水水水水水水水水水
回复 使用道具 举报
Lock2333 来自手机 初级黑马 2018-5-3 16:45:05
地板
水水水水水水水水水水水水水水水水
回复 使用道具 举报
王小川 来自手机 初级黑马 2018-5-3 16:45:28
7#
初来乍到
回复 使用道具 举报
GhostMen 来自手机 中级黑马 2018-5-3 16:45:30
8#
666666666666666666666666666
回复 使用道具 举报
Lock2333 来自手机 初级黑马 2018-5-3 16:45:36
9#
水水水水水水水水水水水水水水水水
回复 使用道具 举报
Lock2333 来自手机 初级黑马 2018-5-3 16:46:15
10#
水水水水水水水水水水水水水水水水
回复 使用道具 举报
王小川 来自手机 初级黑马 2018-5-3 16:46:26
11#
听说有十个字能得经验
回复 使用道具 举报
王小川 来自手机 初级黑马 2018-5-3 16:47:09
12#
标准的十个字有点难啊
回复 使用道具 举报
tangyujie. 来自手机 初级黑马 2018-5-3 16:48:35
13#
看不懂是真的看不懂。
世界真奇妙。
回复 使用道具 举报
王小川 来自手机 初级黑马 2018-5-3 16:48:38
14#
再来发不怕没有十个字
回复 使用道具 举报
s1712179621 来自手机 初级黑马 2018-5-3 16:51:58
15#
哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈
回复 使用道具 举报
touchmyth 来自手机 初级黑马 2018-5-3 16:54:12
16#
这个sql可以哎
回复 使用道具 举报
Nikola 来自手机 中级黑马 2018-5-3 18:03:37
17#
数据库是懂这些就行吗,sure?

点评

这只是基础sql,数据库的知识还有很多弟!~  发表于 2018-5-24 16:56
回复 使用道具 举报
您需要登录后才可以回帖 登录 | 加入黑马