【学习笔记】SQL语句练习

1. 知识点

• ORDER BY的位置应该放在WHERE和GROUP BY之后，否则会报错。

• %通配符匹配0或多个字符，而_通配符匹配一个字符。

• GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

• GROUP BY子句中出现的字段必须在SELECT语句中出现，反之则非必然。

• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

• HAVING子句的作用是过滤分组，即规定包括哪些分组，排除哪些分组。而WHERE过滤的是行。还有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。HAVING支持所有的WHERE操作符。

• GROUP BY并不保证分组的有序性，还是应该使用ORDER BY进行排序。

• 子句顺序：
  

• MYSQL不支持FULL OUTER JOIN语法。

• 自然连接（natural-join）和内连接（inner-join）的不同之处在于，自然连接可以避免重复列（注意不是重复字段）。

• 使用UNION可以将多个SELECT语句的查询结果组合起来。

• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。

• UNION从查询结果集中自动去除了重复的行，如果想返回所有的匹配行，可使用UNION ALL而不是UNION。

• 在用UNION组合查询时，只能使用一条ORDER BY子句对合并后的数据排序，它必须位于最后一条SELECT语句之后。

• 在做连接查询时，ON后面可以跟多个条件，不必放在WHERE子句中。

2. 查询实例

# DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。如果指定的字段值的组合是一样的，就被认为是相同的
SELECT DISTINCT fieldA, fieldB FROM tableA;

# 判断是否为空
SELECT * FROM tableA WHERE fieldA IS NULL;

# 查询订单数大于2的订单
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

# 查询订单号为指定产品号的客户ID。注意使用IN条件时，子查询只能是查询单列字段
SELECT cust_id
FROM Orders
WHERE order_num IN (
    SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01');

# 从顾客表查询客户名和该客户的订单数
SELECT cust_name, 
       (SELECT COUNT(*) 
        FROM Orders 
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers;

# 三表连接查询
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
 AND OrderItems.prod_id = Products.prod_id”;
 
# 三表左连接查询
SELECT last_name, first_name, d.dept_name
FROM 
(employees e LEFT JOIN dept_emp de
ON e.emp_no = de.emp_no)
LEFT JOIN departments d
ON d.dept_no = de.dept_no;
 
# 查询与Jim Jones同一公司的所有顾客信息，以下两种写法作用相同
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

# 自连接（self-join）查询
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
 AND c2.cust_contact = 'Jim Jones';
 
# 将查询出的结果插入到表中，SELECT子句和INSERT子句的字段名可以不对应，但顺序要对应
INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

# 从一个表复制到另一个新表
CREATE TABLE CustCopy AS
SELECT * FROM Customers;

• 从薪水表查询薪水记录超过15次的员工号一起对应的记录次数

  SELECT emp_no, count(*) t from salaries
  group by emp_no
  having t>15
  

• 从员工表和薪水表查询每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列

  SELECT e1.dept_no, e1.emp_no, s1.salary from 
  
  dept_emp e1
  inner join
  salaries s1
  on e1.emp_no = s1.emp_no
  
  where s1.salary = 
  
      (select max(s2.salary) from
      dept_emp e2
      inner JOIN
      salaries s2
      on e2.emp_no = s2.emp_no
      where e2.dept_no = e1.dept_no
      group by e2.dept_no)
  
  order by e1.dept_no
  

  使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。

• 从薪水表查询第二多的薪水，不能使用order by

  SELECT s1.salary FROM
  
  salaries s1
  inner JOIN
  salaries s2
  on s1.salary <= s2.salary
  
  group by s1.salary
  having count(distinct s2.salary)=2
  

内连接查询的三种方式

SELECT
	*
FROM
	A a
JOIN B b ON a.id = b.pid

SELECT
	*
FROM
	A a
INNER JOIN B b ON a.id = b.pid

SELECT
	*
FROM
	A a,
	B b
WHERE
	a.id = b.pid

以上三者等同

窗口函数

有三种用于进行排序的专用窗口函数

• RANK()

  在计算排序时，若存在相同位次，会跳过之后的位次。

  例如，有3条排在第1位时，排序为：1，1，1，4······

• DENSE_RANK()

  这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。

  例如，有3条排在第1位时，排序为：1，1，1，2······

• ROW_NUMBER()

  这个函数赋予唯一的连续位次。

  例如，有3条排在第1位时，排序为：1，2，3，4······

• 窗口函数用法

<窗口函数> OVER ( [PARTITION BY <列清单> ]
                 ORDER BY <排序用列清单> )

其中[ ]中的内容可以忽略

# 对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列，且按照emp_no升序排列
select emp_no, salary,
    DENSE_RANK() over (order by salary desc) as rk
from salaries
order by rk, emp_no asc