【数据库】详解基本SQL语句用法

一、SELECTING DATA FROM TABLES【查询数据】

SELECT命令是表上所有查询的基础，因此给出它的完整描述以显示它的功能。在描述之后提供各种格式的示例。

1.1 整体描述

SELECT column1, column2, ...
FROMtable1
[INNER | LEFT | RIGHT] JOIN table2 on conditions
WHEREconditions
GROUP BY column1
HAVING group_conditions
ORDER BY column1
LIMIT offset, length

SELECT语句由几个句子组成，如下图所示：

• SELECT 后接逗号分隔的列或者星号（*），表示要返回的列或者所有列
• FROM 指定要查询的表或视图
• JOIN 根据特定的连接条件从其他表中获取相关数据
• WHERE 根据指定条件过滤
• GROUP BY 将具有相同值的行分组为汇总行，通常与聚合函数一起使用，例如 COUNT、SUM、AVG 等
• HAVING 根据条件过滤分组。通常与 GROUP BY 一起使用
• ORDER BY 根据一个或多个列对结果集进行排序
• LIMIT 限制查询返回的行数。语法 LIMIT offset, length 允许指定起始点（offset）和要返回的行数（length）

说明:从一个或多个表中选择行和列。可以用作命令，也可以用作另一个SELECT、UPDATE或其他SQL命令中的子查询(对子句有一定的限制)。

1.2 一些参数与子句

1. ALL

• 用法：ALL 用于 SELECT 语句中，表示包括所有查询结果中的行，包括重复的行。
• 默认行为：由于 ALL 是默认行为，因此通常不需要明确指定它。SQL 查询默认包括所有行而不去除重复行。

2. DISTINCT

• 用法：DISTINCT 用于去除结果集中的重复行，确保每一行都是唯一的。
• 目的：当你只想检索唯一值时，特别是对于某列或列组合，使用 DISTINCT 可以去除重复。

3. *（星号）

• 用法：星号（*）用于选择表中的所有列。
• 目的：快速检索所有列，而不需要逐一指定每一列

4. 列名

• 用法：指定列名允许你只检索感兴趣的列。
• 目的：提供对检索数据的更多控制，使结果更易于理解。

5. 别名（Aliases）

• 用法：别名为结果集中显示的列或表提供临时名称。
• 目的：使列名更具可读性或缩短名称以方便使用。别名使用 AS 关键字定义，也可以直接跟在列名后面。

6. FROM 子句

• 用法：指定要从中检索数据的表或视图。
• 目的：定义查询的数据来源。如果指定了多个表，则表示进行连接操作。
• 别名用法：可以为表使用别名，以简化查询或提高可读性。别名在表名后立即定义。

1.3 WHERE子句

WHERE 子句用于对查询结果进行筛选，只有符合条件的行才会被检索出来。你可以使用多种比较操作符和特殊 SQL 操作符来构造 WHERE 子句的条件.

1.3.1 比较操作符

• = (等于):检查字段是否等于指定的值
• !=  和  ^=  和  <> (不等于):检查字段是否不等于指定的值
• > (大于)  和 >=(大于等于) 和 <(小于) 和 <=(小于等于)

1.3.2 特殊SQL操作符

• BETWEEN low AND high 包含范围:  检查字段是否在指定的范围内（包括边界值）
• IN (value1, value2, value3, ...): 在集合中, 检查字段是否匹配集合中的任一值。字符型值需要用单引号括起来。
• LIKE 'string pattern' 模糊匹配: 使用 % 和 _ 作为通配符来进行模糊匹配。% 代表任意数量的字符，_ 代表单个字符。
• IS NULL 检查 NULL 值:检查字段是否为 NULL。NULL 表示字段没有值。
• NOT：用来取反上述所有条件。比如：NOT IN、NOT BETWEEN等

1.3.3 多条件搜索（逻辑运算符）

AND 和 OR 逻辑运算符在 SQL 查询中非常重要，它们用于组合多个条件来筛选数据。

• AND：只有当所有条件都为真时，才会选中行。
• OR：只要其中一个条件为真，就会选中行。
• AND 的优先级高于 OR。

二、THE ORDER BY CLAUSE【排序子句】

在 Oracle 数据库中，ORDER BY 子句用于对查询结果进行排序。默认情况下，Oracle 会以无序的方式显示数据，因此需要使用 ORDER BY 子句来指定排序的规则。ORDER BY 子句应始终放在查询的最后部分。

2.1 ORDER BY子句基本用法

• 默认排序顺序：

  • 数字（NUMERICS）：按数值的升序排序（从小到大）。
  • 日期（DATES）：按时间的升序排序（从早到晚）。
  • 字符（CHAR）：按字母的升序排序（从 A 到 Z）。

• 降序排序：

  • DESC：使用 DESC 关键字可以将排序顺序反转，进行降序排序（从大到小）。

• NULL 值的排序：

  • 在排序中，NULL 值通常排在所有非 NULL 值的前面（在升序排序时）或后面（在降序排序时）。

2.2 排序顺序的注意事项

• 多列排序：可以在 ORDER BY 子句中指定多个列，每个列之间用逗号分隔。例如，ORDER BY job, sal DESC。
• 默认排序：没有指定 ASC 或 DESC 时，默认是升序（ASC）。
• 排序列的使用：可以使用列的名字或者列的索引（在 ORDER BY 中列的位置索引）。

三、JOINING TABLES 【连接表格】

3.1 连接表

在某些查询中，需要连接两个或更多的表。这通过在两个表之间建立一个关系（通常是等式关系）来完成，这个关系通常是通过外键来实现的。简单的连接通常称为等值连接。当在 FROM 子句中引用多个表时，会自动执行连接。

SELECT ename, sal, loc 
FROM emp, dept
WHERE ename = 'ALLEN'  -- 搜索条件
AND emp.deptno = dept.deptno;  -- 连接条件

此查询从 EMP 表中查找名为 'ALLEN' 的员工的姓名和薪水，并从 DEPT 表中获取 'ALLEN' 所在部门的地点。

3.2 缩写表名

表名可以缩写，以简化查询输入。在下面的示例中，E 和 D 是 emp 和 dept 的缩写名。

SELECT D.name, E.* 
FROM emp E, dept D
WHERE E.deptno = D.deptno 
AND loc = 'LUTON'
ORDER BY E.deptno;

3.3 自连接

表标签不仅可以用来缩写表名，还可以用来将一个表连接到它自身，就像将其视为两个独立的表一样。这在只需通过一次 SELECT 查询完成比较时非常有用。

SELECT WORKER.ename, WORKER.sal 
FROM emp WORKER, emp MANAGER
WHERE WORKER.deptno = MANAGER.deptno
AND WORKER.sal > MANAGER.sal;

在这个查询中，emp 表被当作两个独立的表来处理，一个是 WORKER，另一个是 MANAGER。首先通过 WORKER 的部门编号（WORKER.deptno）和 MANAGER 的部门编号（MANAGER.deptno）将所有的 WORKER 连接到同部门的 MANAGER 上。WHERE 子句找出了同部门中员工薪资高于经理的那部分员工。

3.4 选择所有可能的行组合

如果 WHERE 子句中不包含连接条件，则会显示所有可能的行组合。结果（笛卡尔积）通常是不希望的，因此通常会指定一个连接条件。这是一个常见的错误，应当避免，因为如果表 A 有 20 行，表 B 有 30 行，那么不使用连接条件将导致 600 行输出结果。

-- 将 EMP 表中的 'ALLEN' 行与 DEPT 表中的所有行连接
SELECT ename, loc 
FROM emp, dept
WHERE ename = 'ALLEN';

3.5 外连接

在处理 emp 和 dept 表之间的连接时，你会发现部门 40 的详细信息从未出现在输出中。这是因为部门 40 在 emp 表中没有对应的行，因此无法参与连接。如果需要包含表之间关系之外的记录，则必须使用外连接。

SELECT dept.deptno, dname, ename, sal 
FROM dept 
LEFT OUTER JOIN emp ON dept.deptno = emp.deptno;

左外连接实际上会为 emp 表中没有对应员工的每个部门记录添加一个虚拟行。然后，这个部门记录会与这个虚拟行连接，并在输出中出现一次，而 emp 表中的任何列都将为 NULL。

四、SQL函数 

在 SQL中，提供了广泛的函数可以应用于 Oracle 数据。这些函数分为四类：

1. 字符串函数：用于搜索和操作字符串。
2. 算术函数：用于对数值进行计算。
3. 日期函数：用于重新格式化和进行日期计算。
4. 日期函数：用于重新格式化和进行日期计算。

4.1 字符串函数

• LOWER(string)
  将大写字母转换为小写字母。其他字符不受影响。

SELECT LOWER('MR. SAMUEL HILLHOUSE') FROM DUAL;
-- 结果: mr. samuel hillhouse

• UPPER(string)
  将小写字母转换为大写字母。

SELECT UPPER('Mr. Rodgers') FROM DUAL;
-- 结果: MR. RODGERS

• SUBSTR(string, startposition, length)
  显示从指定位置开始的字符串的一部分，长度为指定值。

SELECT SUBSTR('ABCDEF', 2, 3) FROM DUAL;
-- 结果: BCD

• INSTR(string1, string2)
  查找一个字符串在另一个字符串中的起始位置。

SELECT INSTR('ABCDEF', 'DEF') FROM DUAL;
-- 结果: 4

• STR_TO_DATE(string, [format])
  将字符串转换为日期。可以选择性地指定格式。

SELECT STR_TO_DATE('12-06-1996', '%d-%m-%Y') FROM DUAL;

• LPAD(str, len, padstr)
  用指定的填充字符将字符串左侧填充到指定长度。

SELECT LPAD('hi', 4, '??') FROM DUAL;
-- 结果: ??hi

• RPAD(str, len, padstr)
  用指定的填充字符将字符串右侧填充到指定长度。

SELECT RPAD('hi', 4, '??') FROM DUAL;
-- 结果: hi??

• LTRIM(string)
  返回去除前导空格的字符串。

SELECT LTRIM('   barbar') FROM DUAL;
-- 结果: barbar

• RTRIM(string)
  返回去除尾随空格的字符串。

SELECT RTRIM('barbar   ') FROM DUAL;
-- 结果: barbar

• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str)
  返回去除给定字符串中所有前缀或后缀的字符串。

SELECT TRIM('a' FROM 'aaaabcaaa') FROM DUAL;
-- 结果: bc

• IFNULL(expression1, expression2)
  如果第一个表达式不为 NULL，则返回第一个表达式。否则，返回第二个表达式。

SELECT IFNULL(Comm, 0) FROM EMP;

• LENGTH(char)
  返回指定字符串的字符长度。

SELECT LENGTH('Anderson') FROM DUAL;
-- 结果: 8

4.2 算术函数

• ABS(numeric)
  返回数字的绝对值。

SELECT ABS(-15) AS "Absolute" FROM DUAL;
-- 结果: 15

• MOD(num1, num2)
  返回 num1 除以 num2 的余数。

SELECT MOD(7, 5) AS "Modulo" FROM DUAL;
-- 结果: 2

• ROUND(numeric[, d])
  将数字四舍五入到 d 位小数。可以选择四舍五入到小数点的两边。

SELECT ROUND(15.193, 1) AS "Round" FROM DUAL;
-- 结果: 15.2

• TRUNCATE(numeric[, d])
  截断数字到 d 位小数，不进行四舍五入。

SELECT TRUNCATE(15.79, 1) AS "Truncate" FROM DUAL;
-- 结果: 15.7

• CEIL(numeric)
  向上取整到最接近的整数。

SELECT CEIL(10.6) FROM DUAL;
-- 结果: 11

• FLOOR(numeric)
  向下取整到最接近的整数。

SELECT FLOOR(10.6) FROM DUAL;
-- 结果: 10

• SQRT(numeric)
  返回数字的平方根。如果数字为负，返回 NULL。

SELECT SQRT(25) FROM DUAL;
-- 结果: 5

• TO_CHAR(numeric[, format])
  将数字转换为字符字符串，并按照指定的格式进行格式化。

SELECT TO_CHAR(1234.5678, '9999.99') FROM DUAL;
-- 结果: 1234.57

• DATE_FORMAT(date, format)（MySQL 特有）
  按照指定格式将日期格式化为字符串。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') FROM DUAL;
-- 结果: 2024-09-11 14:30:45

4.3 聚合函数

• AVG([DISTINCT] Column)
  计算列中所有值的平均值。DISTINCT 用于去除重复值。

SELECT AVG(sal) AS "AverageSalary" FROM emp;

• COUNT(*)
  计算表中的总行数，包括 NULL 值。

SELECT COUNT(*) AS "TotalEmployees" FROM emp;

• COUNT(column-name)
  计算指定列中非 NULL 值的数量。

SELECT COUNT(sal) AS "NonNullSalaries" FROM emp;

• COUNT(DISTINCT column-name)
  计算指定列中唯一非重复值的数量。

SELECT COUNT(DISTINCT job) AS "UniqueJobs" FROM emp;

• MAX(column)
  返回指定列中的最大值。

SELECT MAX(sal) AS "HighestSalary" FROM emp;

• MIN(column)
  返回指定列中的最小值。

SELECT MIN(sal) AS "LowestSalary" FROM emp;

• SUM(expression)
  计算指定表达式的总和。

SELECT SUM(sal + COALESCE(comm, 0)) AS "TotalEarnings" FROM emp WHERE job = 'SALESMAN';

4.4 日期函数

• DATE_FORMAT (date, format)  这个函数用于将日期按照指定的格式进行显示。

SELECT DATE_FORMAT(now(), '%Y-%m-%d'); 
-- 将返回当前的日期，格式为“年-月-日”。

• DATE_ADD (start_date, INTERVAL expr unit) 这个函数用于在日期或时间戳上加上一个指定的时间间隔。你可以指定间隔的单位，如天、小时、分钟等。

SELECT DATE_ADD(now(), INTERVAL 1 DAY); 
-- 将返回当前日期加上一天的结果。

• TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) 用于计算两个日期或时间戳之间的差异，并返回以指定单位表示的结果。

SELECT TIMESTAMPDIFF(YEAR, HIREDATE, now()) FROM emp; 
-- 将返回员工入职年份到现在年份的差值。

• LAST_DAY(date)  这个函数接受一个日期或时间戳作为参数，并返回该月份的最后一天的日期。如果输入的日期无效，则返回NULL。

SELECT LAST_DAY(NOW()); 
-- 将返回当前月份最后一天的日期。

• DATE(expr)  这个函数用于从日期或时间戳表达式中提取日期部分。如果表达式包含时间信息，时间部分将被忽略。

SELECT DATE('2003-12-31 01:02:03'); 
-- 将返回“2003-12-31”。

• DATE_FORMAT(date, format)  此函数在前面的解释中已经提及，但这里再次强调其格式字符串的多样性。你可以使用不同的格式字符串来定制日期的显示方式

• %Y-%m-%d 将会显示为“年-月-日”
• %a %b %e %Y 将会显示为“缩写星期名 缩写月份名 日 年”（如 Thu Jul 4 2019）
• %H:%i:%s 将会显示为“小时:分钟:秒”

补充：各种日期格式汇总

DATE_FORMAT string	Formatted date
%Y-%m-%d	7/4/2019
%e/%c/%Y	4/7/2019
%c/%e/%Y	7/4/2019
%d/%m/%Y	4/7/2019
%m/%d/%Y	7/4/2019
%e/%c/%Y %H:%i	4/7/2019 11:20
%c/%e/%Y %H:%i	7/4/2019 11:20
%d/%m/%Y %H:%i	4/7/2019 11:20
%m/%d/%Y %H:%i	7/4/2019 11:20
%e/%c/%Y %T	4/7/2019 11:20
%c/%e/%Y %T	7/4/2019 11:20
%d/%m/%Y %T	4/7/2019 11:20
%m/%d/%Y %T	7/4/2019 11:20
%a %D %b %Y	Thu 4th Jul 2019
%a %D %b %Y %H:%i	Thu 4th Jul 2019 11:20
%a %D %b %Y %T	Thu 4th Jul 2019 11:20:05
%a %b %e %Y	Thu Jul 4 2019
%a %b %e %Y %H:%i	Thu Jul 4 2019 11:20
%a %b %e %Y %T	Thu Jul 4 2019 11:20:05
%W %D %M %Y	Thursday 4th July 2019
%W %D %M %Y %H:%i	Thursday 4th July 2019 11:20
%W %D %M %Y %T	Thursday 4th July 2019 11:20:05
%l:%i %p %b %e, %Y	7/4/2019 11:20
%M %e, %Y	4-Jul-19
%a, %d %b %Y %T	Thu, 04 Jul 2019 11:20:05

五、THE GROUP BY CLAUSE 【分组子句】

5.1 GROUP BY基本概念

GROUP BY 子句用于将表中的行分割成组或子集，然后可以对这些记录组执行汇总计算。分组是基于列（或列集）中的匹配值进行的。

• 作用：将表中的行分成多个组或子集，以便对这些组进行汇总计算。
• 分组依据：基于一列或多列中的匹配值进行分组。
• 输出：每个组只呈现一行输出。

5.2 使用示例

SELECT deptno, AVG(sal)   
FROM emp  
GROUP BY deptno;

这个查询将员工表（emp）按照部门编号（deptno）分组，并计算每个部门的平均薪水（AVG(sal)）

5.3 GROUP BY规则

1. SELECT 列表限制：SELECT 列表中只能包含聚合函数（如 MAX(sal), COUNT(empno)）和 GROUP BY 子句中指定的列。
2. 位置：GROUP BY 子句必须放在 WHERE 子句之后（如果有的话）。
3. 通常选择：通常 SELECT 列表中会包含 GROUP BY 子句中指定的列，以便为计算结果提供标签。
4. 默认分组：如果没有指定 GROUP BY 子句，则整个表被视为一个组，聚合函数将应用于整个表。

5.4 多列分组

可以基于多个列的值进行分组，例如同时按部门和职位对员工进行分组。

SELECT deptno, job, COUNT(*), AVG(sal)  
FROM emp  
GROUP BY deptno, job;

5.5 WHERE 和 GROUP BY 结合使用

WHERE 子句用于在分组前过滤行，而 GROUP BY 子句用于将过滤后的行分组。

SELECT deptno, AVG(sal)   
FROM emp  
WHERE job NOT IN ('MANAGER', 'PRESIDENT')  
GROUP BY deptno;

六、THE HAVING CLAUSE 【Having子句】

6.1 HAVING 的基本概念

• 作用：与 WHERE 子句类似，但 HAVING 子句用于过滤分组后的结果集，即基于组的聚合值进行过滤。
• 位置：HAVING 子句通常放在 GROUP BY 子句之后，ORDER BY 子句之前（如果有的话）。

6.2 使用示例

这个查询列出了员工数超过 2 人的职位，并计算了每个职位的员工数和平均薪水。

SELECT job, COUNT(*), AVG(sal)   
FROM emp  
GROUP BY job  
HAVING COUNT(*) > 2;

6.3 HAVING 与 WHERE 的区别

• WHERE：在分组前过滤行。
• HAVING：在分组后过滤组，基于聚合值。

6.4 复杂示例

SELECT deptno, job, COUNT(empno), SUM(sal)  
FROM emp  
WHERE hiredate > '01-JAN-90'  
GROUP BY deptno, job  
HAVING COUNT(empno) > 2  
ORDER BY deptno DESC, JOB;

这个查询列出了 1990 年 1 月 1 日之后入职的、每个部门中每个职位的员工数和薪水总和，但只包括员工数超过 2 人的组，并按部门编号降序和职位排序。

6.5  HAVING 子句中的子查询

HAVING 子句中可以包含子查询，用于将当前组的聚合值与另一个查询的结果进行比较。

SELECT job, AVG(sal)   
FROM emp  
GROUP BY job  
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE job = 'MANAGER');

这个查询列出了平均薪水高于所有经理平均薪水的职位。