专栏首页cmazxiaoma的架构师之路一个麻瓜的自我反思之SQL语句基础练习

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

前言

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

image.png

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

image.png

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

image.png

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

image.png


创建需要的4张表

首先创建studentcoursescoreteacher这四张表。

  • 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,小于90sno
SELECT DISTINCT(sno) FROM score
WHERE degree BETWEEN 70 AND 90;

SELECT sno FROM score
WHERE degree BETWEEN 70 AND 90;
  • 查询所有学生的snamecnodegree
SELECT student.sname, score.cno, score.degree FROM student LEFT JOIN score
ON student.sno = score.sno;
  • 查询所有学生的snocnamedegree
SELECT sno, (SELECT cname FROM course WHERE cno = score.cno) AS cname, degree FROM score ORDER BY sno ASC;
  • 查询所有学生的snamecnamedegree
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’)

现查询所有同学的snocnorank列。

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"的同学,同年出生的所有学生的snosnamesbirthday
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;
  • 查询“计算机系”与“电子工程系“不同职称的教师的tnameprof
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”课程的同学的cnosnodegree
SELECT * FROM score
WHERE cno = 1
AND degree > ALL(
    SELECT degree FROM score
    WHERE cno = 2
)
ORDER BY degree ASC
  • 查询所有教师和同学的namesexbirthday
SELECT tname, tsex, tbirthday FROM teacher
UNION
SELECT sname, ssex, sbirthday FROM student;
  • 查询所有“女”教师和“女”同学的namesexbirthday
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
);
  • 查询所有未讲课的教师的tnamedepart
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'; 
  • 查询最高分同学的snocnodegree
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语句总结

  • 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子句的真正价值。

参考文章

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 蛋疼的ElasticSearch(一)之安装ElasticSearch

    用户2032165
  • 【分布式架构之旅-理论篇】数据库分库分表

    用户2032165
  • Servlet的那些事

    用户2032165
  • 简说Java线程的那几个启动方式

    并发是一件很美妙的事情,线程的调度与使用会让你除了业务代码外,有新的世界观,无论你是否参与但是这对于你未来的成长帮助很大。

    Java猫说
  • 韩国凭什么获得DARPA机器人挑战赛冠军?

    6月6日,DARPA机器人挑战赛在美国加利福尼亚州落下帷幕,韩国KAIST团队的机器人DRC-Hubo击败其余24个机器人获得冠军,拿走了冠军奖金200万美元。...

    机器人网
  • 精典算法之详解 河内之塔

    河内之塔(Towers of Hanoi)是法国人M.Claus(Lucas)于1883年从泰国带至法国的,河内为越战时北越的首都,即现在的胡志明市;1883年...

    lpxxn
  • Python-汉诺塔原理分析

    我把3个盘子的汉诺塔全部通过代码演示,按缩进原则,每一个缩进即进一个递归函数,每打印一次即中止当前递归,也就是每个print

    py3study
  • 数据驱动的社交媒体营销怎么玩?

    互联网时代,社交媒体扮演了直接触达到用户的重要角色,数据也赋予了社交媒体营销新动能。准确的用户画像从何而来?对标竞品的特点有哪些?真正打动消费者的KOL是谁?这...

    DT数据侠
  • 记一次kafka客户端NOT_COORDINATOR_FOR_GROUP处理过程

    转发请注明原创地址:https://www.cnblogs.com/dongxiao-yang/p/10602799.html

    sanmutongzi
  • 广告营销深水区:十万冒牌KOL,百亿灰色名利场

    楔子 晚上8点05分,罗拉的社交帐号定时推送了一篇母婴用品“种草文”。 10分钟,阅读57,在看4; 20分钟,阅读82,在看7; 30分钟,阅读119,在看...

    腾讯防水墙

扫码关注云+社区

领取腾讯云代金券