sql学习

DISTINCT --去重
GROUP BY --分组
HAVING --分组过滤（只能在GROUP BY 后面使用）
-- 基于单列排序
-- SQL查询语句不保证结果的顺序，如果想要对查询结果进行排序显示，需要使用ORDER BY子句
SELECT　column1,
        column2,...
  FROM table
 [WHERE conditions]
 ORDER BY column3 [ASC|DESC];
-- ASC按照升序排列，DESC按照降序排序，默认为升序排序

-- 多列排序
-- ORDER BY 可以指定多列，首先按照第一个指定的列进行排序，然后按照第2个指定的列进行排序，以此类推。
SELECT first_name,
       last_name,
       hire_date
  FROM employees
  ORDER BY first_name,last_name DESC;

-- NULL值排序
-- 对于NULL值，SQL允许使用NULLS FIRST 指定空值最先出现，或者使用NULLS LAST 指定空值最后出现
-- Oracle，PostgreSQL and DB2
SELECT first_name,
      last_name,
      commission_pct
   FROM employees
   ORDER BY commission_pct NULLS LAST；

--限制数量：
--Top-N查询
-- SQL标准中定义了用于对最终结果进行限定的OFFSET子句和FETCH子句，但是，许多数据库使用了另外一种实现（LIMIT子句）
-- Oracle，SQL Server
-- PostgreSQL，and DB2
SELECT　column1,
        column2,...
  FROM table
  [WHERE conditions]
  [ORDER BY column3,...]
  FETCH N;(N表示行数)
-- 分页查询
-- 结合使用OFFSET子句和FETCH子句可以实现分页的效果
SELECT column1,
       column2,...
  FROM table
 [WHERE conditions]
 [ORDER BY column3,...]
OFFSET M ROWS
 FETCH FIRST N ROWS ONLY;
-- 数据库差异


--连接语法
-- 使用FROM和WHERE关键字指定表的连接条件；使用JOIN和ON关键字指定表的连接条件。
FROM -- 第一个表
JOIN -- 第二个表
ON  -- 连接条件
WHERE　--查询条件
--内连接：内连接只返回两个表中匹配的数据行。
--INNER　JOIN　
--左外连接：返回左边中所有的数据行，对于右表中的数据，如果没有匹配的值，返回空值。
--　LEFT　（OUTER）　JOIN　
--　右外连接：右外连接返回右表中所有的数据行，对于左边中的数据，如果没有匹配的值，返回空值。
--　RIGHT　JOIN
--全外连接：全外连接等效于左外链接加上右外连接，返回左边和右表中所有的数据行。（对于重复的行，只返回一次）
--FULL　OUTER　JOIN
--交叉连接：也称为笛卡尔积，它相对一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘。
--　CROSS　JOIN
--　自连接：是指连接操作符的两边都是同一个表，即把一个表和它自己进行连接（显示出上下级的关系）

常见函数

函数针对零个或多个输入，产生一个输出结果

函数：内置函数、自定义函数

内置函数：标量函数、聚合函数

自定义函数：PL／SQL．．．

标量函数：ABS、LOWER、CAST．．．

聚合函数：AVG、COUNT、SUM．．．

数值函数：

函数	描述	函数	描述
ABS（x）	计算x的绝对值	MOD（x，y）	计算x除以y的余数，即求模
CEIL(x)/CEILING(x)	计算大于等于x的最小整数	POWER（x,y）	计算x的y次幂
EXP（x）	计算e的x次幂	ROUND（x,y）	计算x四舍五入到y位小数
FLOOR(x)	计算小于等于x的最小整数	SQRT(x)	计算x的平方根
LN(x)	计算x的自然对数	GREATEST(x,y,…)	计算列表中的最大值
LOG(x)	计算以y为底x的对数	LEAST(x,y,…)	计算列表中的最小值

字符函数:

函数	描述	函数	描述
ASCII(str)	返回第一个字符ASCII编码	OCTET_LENGTH(str)	返回字符串中字节的数量
CHR(n)/CHAR(n)	返回ASCII编码对应的字符	SUBSTRING(str,n,m)	返回字符串中从位置n开始的m个字符
CONCAT(str1,str2,…)	连接多个字符	REPLACE(str,old,new)	将字符串中从位置n开始替换为new子串
LOWER(str)	返回字符串的小写形式	TRIM(sub FROM str)	删除字符串开头或者结尾的子串
CHAR_LENGTH(str)	返回字符串中字符的数量	UPPER(str)	返回字符串的大写形式

日期函数：

函数	描述
CURRENT_DATE	返回服务器当前日期
CURRENT_TIME	返回服务器当前时间
CURRENT_TIMESTAMP	返回服务器当然日期和时间
EXTRACT(field FROM dt)	提取日期时间中的一部分
date +/- arg	日期加减一个时间间隔
date1-date2	计算两个日期之间的时间差

转换函数

用于数据类型转换的函数：CAST（expr AS type)

SELECT CAST('666' AS INTEGER),
      CAST('666' AS SIGNED INTEGER),
      CAST(hire_date AS CHAR(10))
      FROM employees
      WHERE employee_id = 100;

条件表达式

简单CASE表达式：它可以基于一个条件列表返回不同的结果值，类似于其他的编程语言中的条件语句（if-then-else或者switch）

CASE expression(表达式)
   WHERN expression_1 THEN result_1
   WHERN expression_2 THEN result_2
   ...
   [ELSE default_result]
END AS 

搜索CASE表达式：如果想要基于更加复杂的条件进行判断，例如某个值是否为空，是否大于指定的数值，甚至使用子查询的结果进行判断，都需要使用更加强大的搜索CASE表达式

CASE 
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   [ELSE default_result]
END

缩写CASE表达式：定义两个与NULL值相关的缩写CASE表达式（函数）：nullif 和coalesce

-- 如果两个表达式相等，返回NULL值，否则，返回第一个参数的值
NULLIF(expr_1,expr_2)

-- 等价的CASE表达式
CASE 
  WHEN expr_1 = expr_2 THEN NULL,
  ELSE expr_1
END

-- COALESCE 函数接受一个参数列表，并且返回第一个非空的参数值，如果所有的参数都为空，返回空值
COALESCE(expr_1,expr_2,expr_3,...)
-- 等价的CASE表达式
CASE 
  WHEN expr_1 IS NOT NULL THEN expr_1
  WHEN expr_2 IS NOT NULL THEN expr_2
  WHEN expr_3 IS NOT NULL THEN expr_3
  ...
END

专有扩展函数：六种数据库产品都遵循SQL标准中定义的CASE表达式和缩写函数，另外，不同的数据库产品还实现了一些类似的扩展函数。

子查询概述：假设我们想要知道哪些员工的薪水大于所有员工的平均薪水值。

-- 第一步查询所有员工的平均薪水值
SELECT AVG(salary)
FROM employees;

-- 第二步查询所有薪水大于平均薪水值的员工
SELECT first_name,
       last_name,
       salary
   FROM employees
   WHERE salary > -- (平均薪水值)
   ORDER BY salary;

在SQL中，子查询是指嵌套在其他语句（SELECT、INSERT、UPDATE、MERGE）中的SELECT语句；子查询中也可以嵌套另外一个子查询。

-- 外查询
SELECT first_name,
       last_name,
       salary
   FROM employees
   WHERE salary >(
                  SELECT AVG(salary)
                   FROM employees
                  )
                  -- 子查询也称为内查询或者嵌套查询
   ORDER BY salary;

标量子查询：返回单个值（一行一列）的子查询

行子查询：返回包含一个或者多个值的单行结果（一行多列），标量子查询是行子查询的特例。

表子查询：FROM中的子查询，相对于创建了一个临时表，可以作为查询的一个数据源使用

SELECT d.department_name,
       ds.sum_salary
 FROM departments d
 JOIN (SELECT department_id,
              SUM(salary) AS sum_salary
      FROM employees
      GROUP BY department_id) ds
      ON (d.department_id = ds.department_id)
      ORDER BY d.department_name;

关联子查询：关联子查询会引用外部查询中的列，因而与外部查询产生关联，非关联子查询与外部查询没有关联。

集合运算

并集运算：操作符UNION用于将两个查询结果集合并成一个结果集

SELECT column1,column2
   FROM table1
   UNION [DISTINCT|ALL]
SELECT col1,col2
  FROM table2;
  
  -- UNION DISTINCT 表示将合并后的结果集进行去重， UNION等价于UNION DISTINCT
-- UNION ALL 表示保留最终结果集中的重复记录据

交集运算：INTERSECT用于返回两个查询结果集中的共同部分(MYSQL不支持)

SELECT column1,column2
  FROM table1
 INTERSECT [DISTINT|ALL]
SELECT col1,col2
  FROM table2;
-- INTERSECT DISTINCT 表示针对最终的结果集进行去重，INTERSECT等价于INTERSECT DISTINCT
-- INTERSECT ALL 表示保留最终结果集中的重复记录据

差集运算：操作符EXCEPT用于返回出现在第一个查询结果集，但不在第二个查询结果集中的数据

SELECT column1,column2
   FROM table1
 EXCEPT [DISTINCT|ALL]
 SELECT col1,col2
 FROM table2;
 -- EXCEPT DISTINCT 表示针对最终的结果集进行去重，EXCEPT等价于EXCEPT DISTINCT
 -- EXCEPT ALL 保留最终结果集中的重复记录据

1. MySQL不支持EXCEPT操作符
2. Oracle使用关键字MINUS而不是EXCEPT
3. DB2同时支持使用MINUS和EXCEPT

集合运算优先级

集合运算：可以通过多个集合操作符对多个查询结果集进行组合操作

1. 相同的集合操作符按照从左至右的顺序执行；
2. INTERSECT 的优先级高于UNION和EXCEPT，但是Oracle和SQLite不支持
3. 使用括号可以明确指定执行的顺序

视图：一个预定义的查询，不包含数据，有时候也被称为虚拟表，在查询语句中，视图可以当做表使用。

优点：替代复杂查询，减少复杂性，提供一致性接口，实现业务规则，控制对于表的访问，提高安全性。

缺点：不当使用可能会导致查询性能问题，视图通常是只读的，更新视图数据需要满足一定的条件。

创建视图：视图使用CREATE VIEW语句进行创建

CREATE　VIEW　view_name
  AS select_statement;

修改视图：扩展的CREATE命令以及单独的ALTER命令

-- Oracle，Mysql，PostgreSQL，and Db2
CREATE OR REPLACE VIEW view_name
AS select_statement;

-- SQL Server
CREATE OR ALTER VIEW view_name AS select_statement;

合并数据MERGE：它可以同时完成INSERT和UPDATE的操作，甚至DELETE的功能

-- Oracle,SQL Server and Db2
MEGRE INTO target_table [AS t_alias]
USING source_table [AS s_alias]
    ON (condition)
 WHEN MATCHED THEN
    UPDATE SET column1 = expr_1,
               column2 = expr_2,
               ...
 WHEN NOT MATCHED THEN
    INSERT (column1,column2,...)
    VALUES (expr_1,expr_2,...)

模式匹配：SQL提高了LIKE运算符，用于判断字符串是否匹配某种模式。百分号（%）可以匹配零个或者多个字符；下划线（_）可以匹配一个字符

SELECT column1,
       column2,
       ...
   FROM table
   WHERE column LIKE pattern;

如果要匹配通配符（%或_)自身，可以在它们前面加上一个字符转义

WHERE expression LIKE pattern [ESCAPE 'escape_character'];

WHERE '25%' LIKE '25#%' ESCAPE '#';-- 匹配25%

空值判断：在SQL中，NULL是一个特殊值，表示未知数据对NULL值执行常规的比较运算，结果总是返回未知，为此需要使用特殊的运算符

SELECT column1,
       column2,
       ...
  FROM table
 WHERE column IS [NOT] NULL;

复杂条件AND：指定两个条件，只有当它们都为真时，结果才为真；否则结果为假，不返回结果

	T	F	N
T	T	F	N
F	F	F	F
N	N	F	N