面向面试的SQL

怎么讲，觉得SQL是一门 “一看就懂，一学就会，一做就错”的语言. 这回是第…三…次SQL（上过两次SQL的课，两个月前看过《SQL》必知必会，这回刷了leetcode和牛客网上的SQL题）。 总的来说，的确不算很难得一门语言，不过从来不用SQL自然是边学边忘，看书也是一目十行，每次学的时候也没怎么付出时间和精力，所以学的一直不好，但总归是得应付一下将要到来的面试的。

刷了差不多三四十道SQL了，还是有些题根本无从下手，通过翻书，看答案，最后总结出来为什么无从下手的原因是“课本例子太简单，但是给的题目却远远难于课本”。而且子查询真是一个套一个，这套一下那套一下，这join一下，那里笛卡尔积一下，再加上平时不写，以及平时编程的习惯，导致写了很多…….1. 无法通过编译的SQL 2. 答案错误的SQL。(每次先写Select再写FROM 子嵌套我真是伤脑筋…..)。

密集的刷了两天之后，装了个SQLiteStudio捣鼓了一通之后，坑都踩了一遍之后，简单的SQL查询已经不会有太大的问题了。

无法通过编译的SQL

  1. group by + where

  2. where + aggregation 函数

    -- 同时不等号也是错的
WHERE s.salary!=max(s2.salary))

  3. 多个join 连用（只有left join能连用）

    -- 错
SELECT DISTINCT e.last_name as last_name, e.first_name as first_name, dname.dept_name = dept_name;
FROM (departments dname inner join dept_emp d on dname.dept_no = d.dept_no)
right join employees e on e.emp_no = d.emp_no

  4. 乱用之前提到的 where+ aggregation

    -- 错
SELECT emp_no, max(salary)
FROM (SELECT e.emp_no as emp_no, s.salary as salary FROM salaries s inner join employees e
      , salaries s2
      ON e.emp_no = s.emp_no
      WHERE s.salary!=max(s2.salary))

    --- 你就说这个query你自己看不看得懂吧...不要把简单问题复杂化
SELECT emp_no, max(salary)
FROM (SELECT e.emp_no as emp_no, s.salary as salary FROM salaries s inner join employees e
      , salaries s2
      ON e.emp_no = s.emp_no)

  5. 天真的觉得下一行的select会把最大salary的员工信息返回 ，但实际情况是 max（salary）只有一行，而select e.* 返回的只是员工的第一行，哈士奇狗头….

    SELECT e.*
FROM(
 -- 天真的觉得下一行的select会把最大salary的员工信息返回 
SELECT e.*,max(s.salary)
FROM employees e INNER JOIN salaries s on e.emp_no=s.emp_no AND s.to_date='9999-01-01'
WHERE s.salary NOT IN (SELECT max(salary)
                         FROM salaries))

  6. 写法混乱,多了不需要的东西

    两个d是怎么回事? 第二行的d根本没用到啊，而且做了cartitian product后，e.emp_no多了好几行

    SELECT e.emp_no
FROM employees e, dept_manager d
WHERE e.emp_no NOT IN (SELECT e2.emp_no FROM dept_manager d inner join employees e2 on e2.emp_no=d.emp_no )

    正确写法为

    SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT e2.emp_no FROM dept_manager d inner join employees e2 on e2.emp_no=d.emp_no )

以为是错的其实是对的

  1. 可能写算法写魔怔了，总觉得SELECT max(*) 返回的是一个数组，即使只有一个数也应该是数组，不能比较大小，得和返回值的第0个比较… 果然符合大一老师说的学语言时候容易出现的问题, 张冠李戴…..

    -- 对
where s.salary < (select max(salary) from salaries)

  2. -- 下面这个写法...还真是对的，先记下，从sqllite的调试结果看应该是对整个表排了序
SELECT s.*, d.dept_no,max(salary) FROM salaries s ,  dept_manager d

  3. -- 这个语句语法上不是错的，先执行子查询，返回salary，然后选emp_no，但是...其实他是错的，因为总是返回第一个emp_no
SELECT emp_no,(SELECT salary FROM salaries)
FROM employees;

    ​

一些例题

题抄百遍，其义自见……

怎么讲，上面犯的很多错误，其实都是对DBMS的了解很浅显造成的，比如right join 和right join相互嵌套（上课其实是讲过为什么不能这么嵌套的原理的,可惜忘到一干二净）

第二也是对group by 和join的原理不清楚，导致写出臭臭长长的难以理解还错的SQL。 Inner join 是等值连接，outer join允许为空值，但其实都可以用cartitian的方式改写，然后判断条件写在where 里，inner join的方式必须强制写 on条件，这样不会忘记。

补充关于子查询的一些定义

  1. 在WHERE子句中使用子查询能够编写出功能很强并且很灵活的 SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于 性能的限制，不能嵌套太多的子查询。

  2. 在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

  3. 虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、 不等于（<>）等。

  4. -- orders 是一个计算字段，由圆括号的子查询简历的，对每个客户执行一次，执行5次，因为有五个客户。 这个子查询，涉及外部查询的子查询，叫做相关子查询。任何时候列名有多义性，就必须用这种。
SELECT cust_name, cust_state, (SELECT COUNT(*)
                              FROM orders
                              WHERE customers.cust_id = orders.cust_id) AS orders
FROM customers
Order BY cust_name;

-- 两个SQL的区别，下面这个没有限定表名，那么orders 的cust_id一直在和自身比较，所以总是返回订单总数
SELECT cust_name, cust_state, (SELECT COUNT(*)
                              FROM orders
                              WHERE cust_id = cust_id) AS orders
FROM customers
Order BY cust_name

  5. 选出部门中工资最多的

    -- 牛客网上一直过不了，不知道为啥，但我觉得牛客网答案错了
SELECT DISTINCT d1.dept_no,d1.emp_no,s1.salary
FROM dept_emp d1, salaries s1
WHERE d1.emp_no = s1.emp_no and s1.salary >= (SELECT max(s.salary)
                 FROM salaries s ,dept_emp d
                 WHERE s.emp_no =d.emp_no and d.dept_no = d1.dept_no and s.to_date = '9999-01-01') AND s1.to_date = '9999-01-01

  6. 选出工资比部门经理多的

    -- 错误解法，先将人和经理对应起来，然后再对应工资，选出最大。
-- 分析为什么为错呢？ 1. 这是一个四重的连接，包括s1和s2的自联结
-- 经过一步步调试，原来是忘了加上 s_todate='9999-01-01'的日期限定条件,所以做SQL一定要细心啊....
SELECT *
  FROM (
           SELECT DISTINCT d.emp_no AS emp_no,
                  ma.emp_no AS manager_no,
                  s.salary AS emp_salary,
                  s2.salary AS manager_salary
             FROM dept_emp d
                  INNER JOIN
                  dept_manager ma ON d.dept_no = ma.dept_no,
                  salaries s ,
                  salaries s2
            WHERE s2.emp_no = ma.emp_no AND s.emp_no = d.emp_no and d.dept_no = ma.dept_no 
       )
 WHERE emp_salary>manager_salary;

    ​

补充联结

# 自联结
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

# 和这个子查询是一样的，单选用自联结快于子查询
# question2: 里面的= 和 in 会有什么差别呢？
SELECT prod_id, prod_name
FROM products
# 注释： 一个产品只有一个vend_id，所以这里用= 比较而没有用 IN 是可以的
WHERE vend_id = (SELECT vend_id
                FROM products
                WHERE prod_id='DTNTR')

解答question 2

等号是用来查找与单个值匹配的所有数据；IN 是 用来查找与多个值匹配的所有数据；而 LIKE用来查找与一个模式匹配的所有数据。等号 确切知道所要查找的内容，且为单一值时，可以使用等号运算符来进行数据比较。等号运算符中可以使用字符串、日期或数字。IN当确切知道所要查找的内容，且为多个值时，可以使用 IN 子句来进行数据比较。IN 子句中也可以使用数字、字符串或日期。

-- 选出不是经理
SELECT DISTINCT e.emp_no
FROM employees e, dept_manager d
WHERE e.emp_no NOT IN (SELECT e2.emp_no FROM dept_manager d inner join employees e2 on e2.emp_no=d.emp_no )
ORDER BY e.emp_no ASC;
-- <>, 错误写法
SELECT DISTINCT e.emp_no
FROM employees e
WHERE e.emp_no <> (SELECT e2.emp_no FROM dept_manager d inner join employees e2 on e2.emp_no=d.emp_no )
ORDER BY e.emp_no ASC;

下面一些query的结果可以解释

SELECT emp_no FROM employees
emp_no
SELECT e2.emp_no FROM employees e2 inner join dept_manager d on e2.emp_no=d.emp_no
e2.emp_no
SELECT  emp_no, emp_no = (SELECT e2.emp_no FROM employees e2 inner join dept_manager d on e2.emp_no=d.emp_no) AS a, (SELECT e2.emp_no FROM employees e2 inner join dept_manager d on e2.emp_no=d.emp_no) as e2_emp_no
FROM employees ;
也就是说，当一个数和一个SELECT出来的子集比较时，永远只和第一个数比较，所以用IN的子集操作比较妥当，不要混用，乱用

最大第二大前K大的问题

基本思路有 排序+limit, count() +where (大于xxx的个数/小于xx的个数), max+(去掉最大的子集)

-- 寻找入职第三晚的员工, 此为不去重的写法
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;

-- 寻找最晚入职的三个员工, 此为去重的写法，应该根据题意确定是否要用distinct
SELECT * 
FROM employees
WHERE hire_date > (SELECT DISTINCT hire_date 
                   FROM employees 
                   ORDER BY hire_date ASC
                   LIMIT 2,1)

SELECT * 
FROM employees e1
WHERE 2=(SELECT COUNT(*) 
         FROM employees e2 
         WHERE e1.hire_date < e2.hire_date);

-- 寻找部门工资数目上前三高的，所以加了DISTINCT, 
-- 不能用GROUP BY + LIMIT
SELECT d.name AS department, e.name AS employee, e.salary AS salary
FROM employee e INNER JOIN Department d on d.id = e.departmentID
WHERE (SELECT COUNT(DISTINCT e2.salary) FROM employee e2 WHERE e2.salary>e.salary AND e2.departmentId = e.departmentId) <=2
ORDER BY department ASC, salary DESC

  1. 补充COUNT（）函数，使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空 值（NULL）还是非空值。使用 COUNT(column) 对特定列中具有值的行进行计数， 忽略NULL值。

  2. 补充 HAVING，HAVING和WHERE的差别这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

  3. 补充 Set Comparison >some, >all ， 并不是都支持(虽然学的课本是支持的，但最近在sqlstudio中用这个不支持)

  4. 补充if函数

     if(t.status! = "completed", t.status, NULL)

  5. 补充 if…else

    SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

    ​

总结

  1. 第三次SQL的学习时间大概为两天，整个学习效果只能说 比之前好吧，总结的经验是，一定要一层一层剥，从最小的SQL开始写，然后一步步叠加，不要乱写，也不要错写，也不要冗余，尤其注重条件是否漏了
  2. 目前遇到的比较难的题目是，求分组里面前K大的，注意重复值要不要处理。
  3. 分清楚 = 和 IN, 两个的概念是不一样的
  4. 有个很奇怪的问题还在困扰我，sql的子查询里面的别名怎么没有 编程里的作用域呢 😂，真是强行一个概念套一个概念啊，张冠李戴，有时间还是去看看吧。
  5. 如果真有人看到了这，唯一的提醒是，牛客网给了一些SQL的例题，但是那里面有些判题的OJ是错的，而且题目要求的也很模糊，LeetCode上的全部自己验证过了，都是对的。 以及自己电脑上可以装一个SQLiteStudio.

有时间还需要提升的地方

  1. DBMS是如何处理 MAX()之类的计算，是整个表排序之后选第一个么？ 还是说那种类似于编程的选择排序？
  2. DBMS怎么处理GROUP BY的，也是整个表排序么？ 还是只把group by值相同的放一起，然后内部无序？ 如果要求每组里面最大的可不可以用DESC关键字之后选第一行呢？
  3. 字符串的各种处理函数如正则还没有学
  4. 常用的函数还没有学
  5. DBMS的JDBC之类的还没有接触，以及关系型数据库的设计要点，各种范式
  6. 还需要学一下function怎么写
  7. DBMS architecture 和query optimization
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,491评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,856评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,745评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,196评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,073评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,112评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,531评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,215评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,485评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,578评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,356评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,215评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,583评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,898评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,174评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,497评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,697评论 2赞 335