前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >一个麻瓜的自我反思之SQL语句基础练习

一个麻瓜的自我反思之SQL语句基础练习

作者头像
用户2032165
发布2018-06-05 18:20:27
9720
发布2018-06-05 18:20:27
举报
文章被收录于专栏:cmazxiaoma的架构师之路

前言

最近的我,一直处于笔试面试的状态。笔试的时候,发现很多培训班的同学都在拿手机抄袭,可是我没有。卷子上面的题目比较简单,都是我平时复习的知识点,写起来也比较得心应手。

image.png

在最近的一次笔试中,我发现题目都非常简单,做起来没有什么难度。Iterator模式、HashMap原理、基本数据类型转换、throwthrows的区别、冒泡排序、SQL语句等等。可是我却没想到我竟然连笔试都没过,我很不甘心。我平时的努力复习却抵不过别人的投机取巧。不甘心的同时,也发现了自己平时学习的疏漏,如果我实力足够强也会在这群作弊的麻瓜中脱颖而出。可是我并没有脱颖而出,所以我是麻瓜。

image.png

HR告诉我,把卷子交给技术人员批改的时候还特意强调你很厉害,没想到结果却啪啪啪打脸,当时很是尴尬。那个技术人员说你最后一题sql语句写的很烂。听到这些话,我第一个反应是sql语句写的不是特别好,是我平时学习疏漏的一个点,这一次笔试有所收获。 第二个反应就是有点不甘心,为什么作弊的人中总能收获到一个不错的结果呢?

image.png

废话不多BB。总而言之,我很菜,麻瓜就要挨打。所以我准备复习一下基本的SQL语句,题目是借鉴SQL语句练习这篇博客的。

image.png


创建需要的4张表

首先创建studentcoursescoreteacher这四张表。

  • student表
    • 创建student
代码语言:javascript
复制
CREATE TABLE IF NOT EXISTS student(
sno TINYINT UNSIGNED  NOT NULL,
sname VARCHAR(20) NOT NULL,
ssex ENUM('male', 'female') DEFAULT 'male',
sbirthday DATE,
class VARCHAR(20) NOT NULL,
PRIMARY KEY(sno)
);
  • 添加数据
代码语言:javascript
复制
INSERT INTO student VALUES
(1, '阿信', DEFAULT, 19751206, 'class5'),
(2, '怪兽', DEFAULT, 19761128, 'class5'),
(3, '玛莎', DEFAULT, 19770425, 'class5'),
(4, '石头', DEFAULT, 19751211, 'class5'),
(5, '冠佑', DEFAULT, 19730728, 'class5'),
(6, '小马', DEFAULT, 19960628, 'class2'),
(7, '小兰', 'female', 19951126, 'class2'),
(8, '况儿子', DEFAULT, 19960715, 'class4'),
(9, '纯妞', 'female', 19960428, 'class4'),
(10, '豆豆', 'female', 19941211, 'class2');

image.png

  • course表
    • 创建course
代码语言:javascript
复制
CREATE TABLE IF NOT EXISTS course(
cno TINYINT UNSIGNED NOT NULL,
cname VARCHAR(20) NOT NULL,
tno TINYINT NOT NULL,
PRIMARY KEY(cno)
);
  • 添加数据
代码语言:javascript
复制
INSERT INTO course VALUES
(1, '数据结构与算法', 1),
(2, '计算机网络', 2),
(3, '计算机组成原理', 3),
(4, '操作系统', 4);

image.png

  • score表
    • 创建score
代码语言:javascript
复制
CREATE TABLE IF NOT EXISTS score(
sno TINYINT UNSIGNED NOT NULL,
cno TINYINT UNSIGNED NOT NULL,
degree DECIMAL(4, 1)
);
  • 添加数据
代码语言:javascript
复制
INSERT INTO score VALUES
(1, 1, 86),
(1, 2, 75),
(1, 3, 68),
(2, 2, 92),
(2, 3, 88),
(3, 4, 76),
(4, 1, 91),
(5, 1, 40),
(6, 3, 30),
(7, 3, 59),
(8, 4, 66),
(9, 1, 100),
(10, 1, 100),
(6, 1, 66),
(9, 2, 10),
(8, 3, 40),
(7, 1, 77),
(6, 4, 14);

image.png

  • teacher表
    • 创建teacher
代码语言:javascript
复制
CREATE TABLE IF NOT EXISTS teacher(
tno TINYINT UNSIGNED NOT NULL,
tname VARCHAR(10) NOT NULL,
tsex ENUM('male', 'female') DEFAULT 'male',
tbirthday DATE,
prof VARCHAR(26),
depart VARCHAR(10) NOT NULL,
PRIMARY KEY(tno)
);
  • 添加数据
代码语言:javascript
复制
INSERT INTO teacher VALUES
(1, '卢本伟', 'male', 19581202, '副教授', '计算机系'),
(2, '五五开', 'male', 19690312, '讲师', '电子工程系'),
(3, '德云色', 'female', 19720505, '助教', '计算机系'),
(4, '卢本皇', 'female', 19770814, '助教', '电子工程系');

image.png


开始撸题

  • 查询student表中的所有记录的sname,ssex,class列。
代码语言:javascript
复制
SELECT sname, ssex, class FROM student;
  • 查询教师所有的单位即不重复的depart列。
代码语言:javascript
复制
SELECT DISTINCT depart FROM teacher;
  • 查询student表的所有记录
代码语言:javascript
复制
SELECT * FROM student;
  • 查询score表中成绩在60-80之间的所有记录
代码语言:javascript
复制
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
  • 查询score表中成绩为30,66,10的记录
代码语言:javascript
复制
SELECT * FROM score WHERE degree IN (10 ,30, 66);
  • 查询student表中'class5'班或性别为'female'的同学记录。
代码语言:javascript
复制
SELECT * FROM student WHERE class='class5' OR ssex='female';
  • class降序查询student表的所有记录
代码语言:javascript
复制
SELECT * FROM student ORDER BY class DESC;
  • cno升序,degree降序查询score表中的记录
代码语言:javascript
复制
SELECT * FROM score ORDER BY cno ASC, degree DESC;
  • 查询“class5”班的学生人数
代码语言:javascript
复制
SELECT COUNT(*) FROM student WHERE class='class5';
  • 查询score表中的最高分的学生学号和课程号
代码语言:javascript
复制
SELECT sno, cno FROM score
WHERE degree = (SELECT MAX(degree) FROM score);
  • 查询每门课的平均成绩,要按照课程进行分组,然后求每门课程的平均成绩。
代码语言:javascript
复制
SELECT course.cno, course.cname, AVG(degree) AS degree FROM course LEFT JOIN score ON course.cno = score.cno
GROUP BY cno;
  • 查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
代码语言:javascript
复制
SELECT AVG(degree) FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(*) >= 5;
  • 查询分数大于70,小于90sno
代码语言:javascript
复制
SELECT DISTINCT(sno) FROM score
WHERE degree BETWEEN 70 AND 90;

SELECT sno FROM score
WHERE degree BETWEEN 70 AND 90;
  • 查询所有学生的snamecnodegree
代码语言:javascript
复制
SELECT student.sname, score.cno, score.degree FROM student LEFT JOIN score
ON student.sno = score.sno;
  • 查询所有学生的snocnamedegree
代码语言:javascript
复制
SELECT sno, (SELECT cname FROM course WHERE cno = score.cno) AS cname, degree FROM score ORDER BY sno ASC;
  • 查询所有学生的snamecnamedegree
代码语言:javascript
复制
SELECT student.sname, (SELECT cname FROM course WHERE cno = score.cno) AS cname, score.degree FROM student LEFT JOIN score
ON student.sno = score.sno
ORDER BY degree ASC;

SELECT a.sname, b.cname, a.degree FROM (SELECT student.sname, score.cno, score.degree FROM student LEFT JOIN score
ON student.sno = score.sno) AS a  LEFT JOIN course AS b
ON a.cno = b.cno
ORDER BY a.degree ASC;
  • 查询“class5”班学生的平均分
代码语言:javascript
复制
SELECT AVG(degree) AS degree FROM score
WHERE sno IN (SELECT sno FROM student WHERE class = 'class5');

SELECT class, AVG(degree) AS degree FROM student AS a LEFT JOIN score AS b 
ON a.sno = b.sno
WHERE a. class = 'class5';
  • 假设使用如下命令建立了一个grade表:
代码语言:javascript
复制
 create table grade(low  int(3),upp  int(3),rank  char(1))

insert into grade values(90,100,’A’)

insert into grade values(80,89,’B’)

insert into grade values(70,79,’C’)

insert into grade values(60,69,’D’)

insert into grade values(0,59,’E’)

现查询所有同学的snocnorank列。

代码语言:javascript
复制
SELECT a.sno, a.cno, b.rank FROM score AS a LEFT JOIN grade AS b
ON a.degree >= b.low AND a.degree <= b.upp
ORDER BY rank DESC;
  • 查询选修"1"号课程的成绩高于"1"号同学成绩的所有记录
代码语言:javascript
复制
SELECT a.*, b.cno FROM student AS a RIGHT JOIN (SELECT * FROM score
WHERE cno = 1 AND degree > (SELECT degree FROM score WHERE sno = 1 AND cno = 1)) AS b
ON a.sno = b.sno;
  • 查询score表中选修多门课程的同学中,分数为非最高分成绩的记录
代码语言:javascript
复制
SELECT * FROM score AS mst
WHERE sno IN (SELECT sno FROM score GROUP BY sno HAVING COUNT(*) > 1)
AND degree NOT IN (SELECT MAX(degree) FROM score GROUP BY cno);
  • 查询成绩高于学号为"5"、课程号为"1"的成绩的所有记录
代码语言:javascript
复制
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE sno = 5 AND cno = 1)
ORDER BY sno ASC;
  • 查询和学号为"1"的同学,同年出生的所有学生的snosnamesbirthday
代码语言:javascript
复制
SELECT sno, sname, sbirthday FROM student
WHERE YEAR(sbirthday) = (SELECT YEAR(sbirthday) FROM student WHERE sno = 1)

AND sno != 1;
  • 查询“卢本伟“教师的学生成绩
代码语言:javascript
复制
SELECT * FROM score
WHERE EXISTS(SELECT cno FROM course 
    WHERE tno = (SELECT tno FROM teacher WHERE tname = '卢本伟')
    AND score.cno = course.cno
);

SELECT * FROM (SELECT course.cno FROM teacher LEFT JOIN course 
ON teacher.tno = course.tno WHERE teacher.tname = '卢本伟') AS temp LEFT JOIN score
ON score.cno = temp.cno;
  • 查询选修某课程的同学人数多于5人的教师姓名
代码语言:javascript
复制
SELECT teacher.tname FROM teacher RIGHT JOIN
(   SELECT course.tno FROM course LEFT JOIN score
    ON course.cno = score.cno
    GROUP BY course.cno
    HAVING COUNT(score.sno) > 5

) AS temp
ON teacher.tno = temp.tno;
  • 查询“class5”班和"class2"班全体学生的记录
代码语言:javascript
复制
SELECT * FROM student
WHERE class = 'class2' OR  class = 'class5';
  • 查询存在有85分以上成绩的课程cno
代码语言:javascript
复制
SELECT DISTINCT (cno) FROM score
WHERE degree > 85;
  • 查询出“计算机系“教师所教课程的成绩表
代码语言:javascript
复制
SELECT score.* FROM score RIGHT JOIN (SELECT cno FROM teacher LEFT JOIN course
ON teacher.tno = course.tno
WHERE teacher.depart = '计算机系') AS temp
ON score.cno = temp.cno;
  • 查询“计算机系”与“电子工程系“不同职称的教师的tnameprof
代码语言:javascript
复制
SELECT * FROM teacher
WHERE prof NOT IN (
    SELECT prof FROM teacher AS temp0 WHERE depart = '计算机系'
    AND EXISTS (SELECT prof FROM teacher AS temp1 WHERE depart = '电子工程系'
    AND temp1.prof = temp0.prof
    )
);
  • 查询选修编号为“1“课程且成绩至少高于选修编号为“2”的同学
代码语言:javascript
复制
SELECT * FROM student RIGHT JOIN (SELECT * FROM score
WHERE cno = 1
AND degree > ANY(
    SELECT degree FROM score
    WHERE cno = 2
)
ORDER BY degree ASC) AS temp
ON student.sno = temp.sno
ORDER BY student.sno ASC;
  • 查询选修编号为“1”且成绩高于选修编号为“2”课程的同学的cnosnodegree
代码语言:javascript
复制
SELECT * FROM score
WHERE cno = 1
AND degree > ALL(
    SELECT degree FROM score
    WHERE cno = 2
)
ORDER BY degree ASC
  • 查询所有教师和同学的namesexbirthday
代码语言:javascript
复制
SELECT tname, tsex, tbirthday FROM teacher
UNION
SELECT sname, ssex, sbirthday FROM student;
  • 查询所有“女”教师和“女”同学的namesexbirthday
代码语言:javascript
复制
SELECT tname, tsex, tbirthday FROM teacher
WHERE tsex = 'female'
UNION
SELECT sname, ssex, sbirthday FROM student
WHERE ssex = 'female';
  • 查询成绩比该课程平均成绩低的同学的成绩表
代码语言:javascript
复制
SELECT * FROM score AS a
WHERE degree < (SELECT AVG(degree) FROM score AS b WHERE a.cno = b.cno);
  • 查询所有任课教师的tname和depart
代码语言:javascript
复制
SELECT tname, depart FROM teacher
WHERE EXISTS (
    SELECT tno FROM course
    WHERE teacher.tno = course.tno
);
  • 查询所有未讲课的教师的tnamedepart
代码语言:javascript
复制
SELECT tname, depart FROM teacher
WHERE tno NOT IN (
    SELECT DISTINCT (tno) FROM course RIGHT JOIN score
    ON course.cno = score.cno
);
  • 查询至少有2名男生的班号
代码语言:javascript
复制
SELECT class FROM student
WHERE ssex = 'male'
GROUP BY class
HAVING COUNT(*) > 2;
  • 查询不姓王的同学信息
代码语言:javascript
复制
SELECT a.* FROM student AS a
WHERE NOT EXISTS (SELECT b.sno FROM student AS b  WHERE b.sname LIKE '小_' AND a.sno = b.sno);
  • 查询student表中每个学生的姓名和年龄
代码语言:javascript
复制
SELECT sname, YEAR(NOW()) - YEAR(student.sbirthday) AS age FROM student
  • 查询student表中最大和最小的sbirthday
代码语言:javascript
复制
SELECT sname, sbirthday FROM student WHERE sbirthday = (SELECT MIN(sbirthday) FROM student)
UNION
SELECT sname, sbirthday FROM student WHERE sbirthday = (SELECT MAX(sbirthday) FROM student);
  • 以班号和年龄从大到小的顺序查询student表中的全部记录
代码语言:javascript
复制
SELECT * FROM student
ORDER BY class, YEAR(NOW()) - YEAR(student.sbirthday) DESC;
  • 查询“男”教师及其所上的课程
代码语言:javascript
复制
SELECT * FROM teacher LEFT JOIN course
ON teacher.tno = course.tno
WHERE teacher.tsex = 'male'; 
  • 查询最高分同学的snocnodegree
代码语言:javascript
复制
SELECT * FROM score
WHERE degree = (SELECT MAX(degree) FROM score);
  • 查询所有选修“数据结构与算法”课程的“男”同学的成绩表
代码语言:javascript
复制
SELECT temp.* FROM student RIGHT JOIN (SELECT score.* FROM course LEFT JOIN score
ON course.cno = score.cno
WHERE course.cname = '数据结构与算法') AS temp
ON student.sno = temp.sno
WHERE student.ssex = 'male';
  • 查询和“阿信”同性别的所有同学的sname
代码语言:javascript
复制
SELECT sname FROM student
WHERE student.ssex = (
    SELECT ssex FROM student
    WHERE sname = '阿信'
)
AND student.sname != '阿信';
  • 查询和“阿信”同性别并同班的同学sname
代码语言:javascript
复制
SELECT sname FROM student
WHERE student.ssex = (
    SELECT ssex FROM student
    WHERE sname = '阿信'
)

AND class = (
    SELECT class FROM student 
    WHERE sname = '阿信'
)
AND student.sname != '阿信';

笔试常考的sql语句

  • 查询每门课程都大于60分的学生信息。明显第一种写法无论从逼格,还是效率上,都要比第二种写法要好。(航天信息有限公司校招题)
代码语言:javascript
复制
SELECT * FROM student 
WHERE NOT EXISTS (SELECT DISTINCT (sno) FROM score WHERE degree < 60 AND student.sno = score.sno);

SELECT * FROM student
WHERE sno NOT IN (SELECT DISTINCT (sno) FROM score WHERE degree < 60);
  • 统计男生和女生的个数。(恒生,红星美凯龙校招题)
代码语言:javascript
复制
SELECT COUNT(*) FROM student
WHERE ssex = 'female'
UNION
SELECT COUNT(*) FROM student
WHERE ssex = 'male';
  • 找回总成绩最高的学生的基本信息(恒生,红星美凯龙校招题)
代码语言:javascript
复制
SELECT student.* FROM student RIGHT JOIN score
ON score.sno = student.sno
GROUP BY score.sno
HAVING SUM(score.degree) = (
        SELECT MAX(sum_degree) FROM(
            SELECT SUM(degree) AS sum_degree
            FROM score
            GROUP BY score.sno
        ) AS temp
);
  • 找出每门课程中成绩最高的学生的信息。
代码语言:javascript
复制
SELECT temp.cno, temp.sno, student.sname, temp.max_degree FROM student RIGHT JOIN (
    SELECT sno, cno, MAX(degree) AS max_degree FROM score
    GROUP BY score.cno
) AS temp
ON student.sno = temp.sno;

SQL语句总结

  • EXISTSNOT EXISTS很好用,性能也好。建议把NOT ININ 换成 NO EXISTSEXISTS
  • IN是把外表和内表进行hash连接,而EXISTS是对外表进行LOOP循环,每次LOOP循环再对内表进行查询。如果要查询的两个表大小相当,那么用INEXISTS差别不大,如果两个表一个较大一个较小,则子查询表大用EXISTS,子查询小的用IN
  • EXISTS只要存在就返回输出结果,这样的话很有可能不需要扫描整个表,而IN则需要扫描完整个表,并返回结果。
  • 如果子查询中返回任意一条记录含有空值,使用NOT IN将不会返回任何记录,造成错误。如果子查询字段有非空限制,这时可以使用NOT IN。如果查询语句使用了NOT IN,那么内外表都进行全盘扫描,没有用到索引,而NOT EXISTS的子查询依然可以用到表上的索引,所以无论哪个表大,用NOT EXISTS都比NOT IN性能要好
  • UNION ALL可以聚合多个结果集。
  • 多表连接时先汇总数据到统一维度(GROUP BY到相同字段,再用这些字段作为连接条件进行连接)。
  • SELECT子句中各种判断请用CASE语句。
  • 各种字符串函数,日期函数。
  • 临时表的使用。
  • 学习HAVING子句的真正价值。

参考文章

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2017.12.08 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • 创建需要的4张表
  • 开始撸题
  • 笔试常考的sql语句
  • SQL语句总结
  • 参考文章
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档