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

用户2032165

发布于 2018-06-05 18:20:27

9720

发布于 2018-06-05 18:20:27

文章被收录于专栏：cmazxiaoma的架构师之路

前言

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

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