一个麻瓜的自己省察之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;
  • 询问所有学生的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必赢亚洲766net简易端,特性要好

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

  • 多表连接时先汇总数据到统一维度(GROUP BY到均等字段,再用这些字段作为连接条件举办连续)。

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

  • 各个字符串函数,日期函数。

  • 临时表的应用。

  • 学习HAVING子句的实在价值。

参考作品

尾言

再过几天,就要去都德国首都了。期待一场旅程,精彩卓殊。想要制服的社会风气,始终都不曾改动。

cmazxiaoma_big_dream.jpg

发表评论

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