swiftR

基础

COALESCE函数用于将空值转换为 0。

1
select emp_name, salary * 12 + COALESCE(bonus,0)  from employee;

IN 用于查找列表中的任意值。

1
2
3
4

SELECT *
  FROM employee
  WHERE emp_name IN ('张三', '李四', '张飞');
1
2
3
4
SELECT emp_name,
       bonus
  FROM employee
 WHERE bonus IS NOT NULL;
1
2
3
4
SELECT emp_name, sex, hire_date
  FROM employee
 WHERE sex = '女'
   AND hire_date > DATE '2010-01-01';
1
2
3
4
5
6
7
SELECT *
  FROM employee
 WHERE 1 = 0 AND 1/0 = 1;
 
不会出错，但是查不到任何数据。

解析：因为 SQL 对于逻辑运算符 AND 和 OR 使用短路运算（short-circuit evaluation）。也就是说，只要前面的表达式能够决定最终的结果，不执行后面的计算。
1
2
3
4
SELECT DISTINCT sex
  FROM employee;
解析：DISTINCT 用于消除查询结果中的重复值，上面的查询只返回两个不同的性别记录。
distinct 不同的

ORDER BY 子句用于对查询结果进行排序；ASC 表示升序，DESC 表示降序。

按拼音排序

1
2
3
4
SELECT emp_name
  FROM employee
 WHERE emp_id <= 10
 ORDER BY CONVERT(emp_name USING GBK);

MySQL ，升序时 NULL 值排在最前面，降序时 NULL 值排在最后面。

1
2
3
SELECT *
  FROM employee
 ORDER BY bonus;

UPPER 转换大写 LOWER转换小写

获取邮箱前半部分

1
2
SELECT emp_name, SUBSTR(email, 1, INSTR(email,'@') - 1)
  FROM employee;

将.com替换为.net

1
2
SELECT emp_name, REPLACE(EMAIL, '.com','.net')
  FROM employee;

随机排序

1
2
3
4
-- MySQL 实现
SELECT emp_name, RAND()
  FROM employee
 ORDER BY RAND();

CEILING 向上取整，FLOOR 向下取整，ROUND 四舍五入。

1
2
3
SELECT CEILING(1.1), FLOOR(1.1), ROUND(1.1)
  FROM employee
 WHERE emp_id = 1;

GREATEST 函数用于返回列表中的最大值，LEAST 函数用于返回列表中的最小值。

1
2
SELECT student_id, GREATEST(chinese, math, english, history)
  FROM score;

CURRENT_DATE 函数返回当前日期，EXTRACT 函数可以提取日期数据中的各个部分，本例中使用 year 参数获取年份信息。

1
2
SELECT emp_name, EXTRACT( year FROM CURRENT_DATE) - EXTRACT( year FROM HIRE_DATE)
  FROM employee;

CASE 表达式可以类似于 IF-THEN-ELSE 的逻辑处理

1
2
3
4
5
6
SELECT emp_name,
       CASE WHEN salary < 10000 THEN '低收入'
            WHEN salary < 20000 THEN '中等收入'
            ELSE '高收入'
       END "薪水等级"
  FROM employee;

聚合函数

  • AVG - 计算一组值的平均值。
  • COUNT - 统计某个字段的行数。
  • MIN - 返回一组值中的最小值。
  • MAX - 返回一组值中的最大值。
  • SUM - 计算一组值的和值。
    1
    2
    SELECT COUNT(*), AVG(salary), MAX(salary), MIN(salary), SUM(salary)
      FROM employee;

COUNT (*) 之外，其他聚合函数都会忽略字段中的 NULL 值。

1
2
SELECT COUNT(*), COUNT(bonus)
  FROM employee;

群发邮件时，多个邮件地址使用分号进行分隔。如何获取所有员工的群发邮件地址？

1
2
SELECT GROUP_CONCAT(email SEPARATOR ';')
  FROM employee;

获取每个部门的统计信息，比如员工的数量、平均月薪

1
2
3
SELECT dept_id, COUNT(*), AVG(salary)
  FROM employee
 GROUP BY dept_id;

要知道哪些部门月薪超过 5000 的员工数量大于 5

1
2
3
4
5
SELECT dept_id, COUNT(*)
  FROM employee
 WHERE salary > 5000
 GROUP BY dept_id
HAVING COUNT(*) > 5;

SQL 高级查询

通过内连接返回员工所在的部门名称

1
2
3
4
5
6
7
8
9
10
11
SELECT d.dept_id, 
       d.dept_name,
       e.emp_name
  FROM employee e
  JOIN department d ON (e.dept_id = d.dept_id);

SELECT d.dept_id, 
       d.dept_name,
       e.emp_name
  FROM employee e, department d
 WHERE e.dept_id = d.dept_id;

SQL 高级查询

  • 内连接（INNER JOIN），用于返回两个表中满足连接条件的数据行。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT d.dept_id, 
           d.dept_name,
           e.emp_name
      FROM employee e
      JOIN department d ON (e.dept_id = d.dept_id);

    SELECT d.dept_id, 
           d.dept_name,
           e.emp_name
      FROM employee e, department d
     WHERE e.dept_id = d.dept_id;

  • 左外连接（LEFT OUTER JOIN），返回左表中所有的数据行；对于右表中的数据，如果没有匹配的值，返回空值。

    1
    2
    3
    4
    SELECT d.dept_name, COUNT(e.emp_name)
      FROM department d
      LEFT JOIN employee e ON (e.dept_id = d.dept_id)
     GROUP BY d.dept_name;
  • 右外连接（RIGHT OUTER JOIN），返回右表中所有的数据行；对于左表中的数据，如果没有匹配的值，返回空值。
集合运算
  • 并集运算（UNION、UNION ALL），将两个查询结果合并成一个结果集，包含了第一个查询结果以及第二个查询结果中的数据。
  • 交集运算（INTERSECT），返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据。MySQL 不支持 INTERSECT。
  • 差集运算（EXCEPT），返回出现在第一个查询结果中，但不在第二个查询结果中的数据。MySQL 不支持 EXCEPT，Oracle 使用 MINUS 替代 EXCEPT。
    1
    2
    3
    4
    5
    6
    7
    -- 使用连接查询实现交集运算
    SELECT t1.id FROM t1 JOIN t2 ON (t1.id = t2.id);

    -- 使用左连接查询实现差集运算
    SELECT t1.id FROM t1
      LEFT JOIN t2 ON (t1.id = t2.id)
     WHERE t2.id IS NULL;
子查询
  • 标量子查询（scalar query）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
  • 行子查询（row query）：返回包含一个或者多个值的单行结果（一行多列），标量子查询是行子查询的特例。
  • 表子查询（table query）：返回一个虚拟的表（多行多列），行子查询是表子查询的特例。
    1
    2
    3
    SELECT emp_name, salary
      FROM employee
     WHERE salary > (SELECT AVG(salary) FROM employee);

FROM 子句中不能直接使用关联子查询，因为子查询和查询处于相同的层级，不能引用前表（e）中的数据。不过，使用横向（LATERAL）子查询可以实现该功能：

1
2
3
4
5
6
7
SELECT emp_name, 
       dept_count
  FROM employee e
  JOIN LATERAL (SELECT COUNT(*) AS dept_count
           FROM employee
          WHERE dept_id = e.dept_id) d
    ON (1=1);
1
2
3
4
5
6
SELECT *
  FROM department d
 WHERE EXISTS (SELECT 1 
                 FROM employee e 
                WHERE e.sex ='女'
                  AND e.dept_id = d.dept_id);
 Previous

java 注解