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

前言

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

image.png

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

image.png

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

image.png

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

image.png

创建需要的4张表

首先创建student、course、score、teacher这四张表。

  • student表
    • 创建student表
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)
);
  • 添加数据
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表
CREATE TABLE IF NOT EXISTS course(
cno TINYINT UNSIGNED NOT NULL,
cname VARCHAR(20) NOT NULL,
tno TINYINT NOT NULL,
PRIMARY KEY(cno)
);
  • 添加数据
INSERT INTO course VALUES
(1, '数据结构与算法', 1),
(2, '计算机网络', 2),
(3, '计算机组成原理', 3),
(4, '操作系统', 4);
image.png
  • score表
    • 创建score表
CREATE TABLE IF NOT EXISTS score(
sno TINYINT UNSIGNED NOT NULL,
cno TINYINT UNSIGNED NOT NULL,
degree DECIMAL(4, 1)
);
  • 添加数据
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表
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)
);
  • 添加数据
INSERT INTO teacher VALUES
(1, '卢本伟', 'male', 19581202, '副教授', '计算机系'),
(2, '五五开', 'male', 19690312, '讲师', '电子工程系'),
(3, '德云色', 'female', 19720505, '助教', '计算机系'),
(4, '卢本皇', 'female', 19770814, '助教', '电子工程系');
image.png

开始撸题

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

SELECT sno FROM score
WHERE degree BETWEEN 70 AND 90;
  • 查询所有学生的sname、cno和degree列
SELECT student.sname, score.cno, score.degree FROM student LEFT JOIN score
ON student.sno = score.sno;
  • 查询所有学生的sno、cname和degree列
SELECT sno, (SELECT cname FROM course WHERE cno = score.cno) AS cname, degree FROM score ORDER BY sno ASC;
  • 查询所有学生的sname、cname和degree列
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”班学生的平均分
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表：
 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’)

现查询所有同学的sno、cno和rank列。

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"号同学成绩的所有记录
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表中选修多门课程的同学中，分数为非最高分成绩的记录
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"的成绩的所有记录
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE sno = 5 AND cno = 1)
ORDER BY sno ASC;
  • 查询和学号为"1"的同学，同年出生的所有学生的sno、sname和sbirthday列
SELECT sno, sname, sbirthday FROM student
WHERE YEAR(sbirthday) = (SELECT YEAR(sbirthday) FROM student WHERE sno = 1)

AND sno != 1;
  • 查询“卢本伟“教师的学生成绩
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人的教师姓名
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"班全体学生的记录
SELECT * FROM student
WHERE class = 'class2' OR  class = 'class5';
  • 查询存在有85分以上成绩的课程cno列
SELECT DISTINCT (cno) FROM score
WHERE degree > 85;
  • 查询出“计算机系“教师所教课程的成绩表
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;
  • 查询“计算机系”与“电子工程系“不同职称的教师的tname和prof 
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”的同学
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”课程的同学的cno、sno和degree 
SELECT * FROM score
WHERE cno = 1
AND degree > ALL(
    SELECT degree FROM score
    WHERE cno = 2
)
ORDER BY degree ASC
  • 查询所有教师和同学的name、sex和birthday 
SELECT tname, tsex, tbirthday FROM teacher
UNION
SELECT sname, ssex, sbirthday FROM student;
  • 查询所有“女”教师和“女”同学的name、sex和birthday 
SELECT tname, tsex, tbirthday FROM teacher
WHERE tsex = 'female'
UNION
SELECT sname, ssex, sbirthday FROM student
WHERE ssex = 'female';
  • 查询成绩比该课程平均成绩低的同学的成绩表
SELECT * FROM score AS a
WHERE degree < (SELECT AVG(degree) FROM score AS b WHERE a.cno = b.cno);
  • 查询所有任课教师的tname和depart
SELECT tname, depart FROM teacher
WHERE EXISTS (
    SELECT tno FROM course
    WHERE teacher.tno = course.tno
);
  • 查询所有未讲课的教师的tname和depart 
SELECT tname, depart FROM teacher
WHERE tno NOT IN (
    SELECT DISTINCT (tno) FROM course RIGHT JOIN score
    ON course.cno = score.cno
);
  • 查询至少有2名男生的班号
SELECT class FROM student
WHERE ssex = 'male'
GROUP BY class
HAVING COUNT(*) > 2;
  • 查询不姓王的同学信息
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表中每个学生的姓名和年龄
SELECT sname, YEAR(NOW()) - YEAR(student.sbirthday) AS age FROM student
  • 查询student表中最大和最小的sbirthday 
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表中的全部记录
SELECT * FROM student
ORDER BY class, YEAR(NOW()) - YEAR(student.sbirthday) DESC;
  • 查询“男”教师及其所上的课程
SELECT * FROM teacher LEFT JOIN course
ON teacher.tno = course.tno
WHERE teacher.tsex = 'male';
  • 查询最高分同学的sno、cno和degree列
SELECT * FROM score
WHERE degree = (SELECT MAX(degree) FROM score);
  • 查询所有选修“数据结构与算法”课程的“男”同学的成绩表
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 
SELECT sname FROM student
WHERE student.ssex = (
    SELECT ssex FROM student
    WHERE sname = '阿信'
)
AND student.sname != '阿信';
  • 查询和“阿信”同性别并同班的同学sname 
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分的学生信息。明显第一种写法无论从逼格，还是效率上，都要比第二种写法要好。(航天信息有限公司校招题)
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);
  • 统计男生和女生的个数。(恒生，红星美凯龙校招题)
SELECT COUNT(*) FROM student
WHERE ssex = 'female'
UNION
SELECT COUNT(*) FROM student
WHERE ssex = 'male';
  • 找回总成绩最高的学生的基本信息(恒生，红星美凯龙校招题)
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
);
  • 找出每门课程中成绩最高的学生的信息。
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语句总结

  • EXISTS和NOT EXISTS很好用，性能也好。建议把NOT IN 和 IN 换成 NO EXISTS 和 EXISTS。

  • IN是把外表和内表进行hash连接，而EXISTS是对外表进行LOOP循环，每次LOOP循环再对内表进行查询。如果要查询的两个表大小相当，那么用IN和EXISTS差别不大，如果两个表一个较大一个较小，则子查询表大用EXISTS，子查询小的用IN。

  • EXISTS只要存在就返回输出结果，这样的话很有可能不需要扫描整个表，而IN则需要扫描完整个表，并返回结果。

  • 如果子查询中返回任意一条记录含有空值，使用NOT IN将不会返回任何记录，造成错误。如果子查询字段有非空限制，这时可以使用NOT IN。如果查询语句使用了NOT IN，那么内外表都进行全盘扫描，没有用到索引，而NOT EXISTS的子查询依然可以用到表上的索引，所以无论哪个表大，用NOT EXISTS都比NOT IN性能要好

  • UNION ALL可以聚合多个结果集。

  • 多表连接时先汇总数据到统一维度(GROUP BY到相同字段，再用这些字段作为连接条件进行连接)。

  • SELECT子句中各种判断请用CASE语句。

  • 各种字符串函数，日期函数。

  • 临时表的使用。

  • 学习HAVING子句的真正价值。

参考文章

尾言

再过几天，就要去广州了。期待一场旅程，精彩万分。想要征服的世界，始终都没有改变。

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

推荐阅读更多精彩内容

  • SQL语句大全
    50个常用的sql语句 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cna...
    最美的太阳WW阅读 3,155评论 0赞 23
  • SQL SERVER
    说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。 问题及描述： --1.学生表 Stud...
    lijun_m阅读 1,286评论 0赞 1
  • sql
    50个常用的sql语句Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname...
    哈哈海阅读 1,224评论 0赞 7
  • 一家人的一顿饭
    是一个很平常的周末，本来暑假回家要我姐姐请我吃饭，反正两个人也是吃。索性叫上爸妈一起。 他们两是很节俭的人，一听到...
    樱桃城城阅读 343评论 0赞 0
  • 阅读也好，看电视也好，投入其中，就成了生活
    今天没怎么严肃地阅读，却开始看天蚕土豆的《元尊》。说实话，写得真不咋地，但就是有吸引力。因为我偏爱看这种小人物逆袭...
    燕麦文话阅读 396评论 1赞 4