怎么讲，觉得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
SELECT e2.emp_no FROM employees e2 inner join dept_manager d on e2.emp_no=d.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 ;
最大第二大前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
扫一扫
2926
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
