一个麻瓜的本人省察之SQL语句基础操练

前言

近年的自个儿,平昔处于笔试面试的状态。笔试的时候,发现众多培训班的同学都在拿手机抄袭,但是我没有。卷子上面的题材比较简单,都是自家日常复习的知识点,写起来也比较一箭穿心。

image.png

在近期的四回笔试中,我发觉难点都极度不难,做起来没有何难度。Iterator模式、HashMap规律、基本数据类型转换、throw

throws的区分、冒泡排序、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;
  • 询问所有学生的snocname航天科工,和degree

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子句的确实价值。

参考小说

尾言

再过几天,就要去圣地亚哥了。期待一场旅程,漂亮相当。想要制服的社会风气,始终都不曾改动。

cmazxiaoma_big_dream.jpg

发表评论

电子邮件地址不会被公开。 必填项已用*标注