面向面试的SQL

2019-01-11 本文已影响0人锅锅Iris

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

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

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

无法通过编译的SQL

group by + where

where + aggregation 函数

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

多个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

乱用之前提到的 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)

天真的觉得下一行的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))

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

两个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 )

以为是错的其实是对的

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

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

-- 这个语句语法上不是错的，先执行子查询，返回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条件，这样不会忘记。

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

在WHERE子句中使用子查询能够编写出功能很强并且很灵活的 SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。
在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。
虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。

-- 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

选出部门中工资最多的

-- 牛客网上一直过不了，不知道为啥，但我觉得牛客网答案错了
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

选出工资比部门经理多的

-- 错误解法，先将人和经理对应起来，然后再对应工资，选出最大。
-- 分析为什么为错呢？ 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

补充COUNT（）函数，使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。使用 COUNT(column) 对特定列中具有值的行进行计数，忽略NULL值。
补充 HAVING，HAVING和WHERE的差别这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
补充 Set Comparison >some, >all ，并不是都支持(虽然学的课本是支持的，但最近在sqlstudio中用这个不支持)

补充if函数

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

补充 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;

总结

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

有时间还需要提升的地方

DBMS是如何处理 MAX()之类的计算，是整个表排序之后选第一个么？还是说那种类似于编程的选择排序？
DBMS怎么处理GROUP BY的，也是整个表排序么？还是只把group by值相同的放一起，然后内部无序？如果要求每组里面最大的可不可以用DESC关键字之后选第一行呢？
字符串的各种处理函数如正则还没有学
常用的函数还没有学
DBMS的JDBC之类的还没有接触，以及关系型数据库的设计要点，各种范式
还需要学一下function怎么写
DBMS architecture 和query optimization