IT评测·应用市场-qidao123.com

标题: 【数据库】详解根本SQL语句用法 [打印本页]

---

作者: 圆咕噜咕噜    时间: 2024-10-11 11:13
标题: 【数据库】详解根本SQL语句用法
一、SELECTING DATA FROM TABLES【查询数据】

SELECT下令是表上所有查询的底子，因此给出它的完备描述以显示它的功能。在描述之后提供各种格式的示例。
1.1 团体描述

1. SELECT
2.     column1, column2, ...
3. FROM
4.     table1
5. [INNER | LEFT | RIGHT] JOIN table2 on conditions
6. WHERE
7.     conditions
8. GROUP BY column1
9. HAVING group_conditions
10. ORDER BY column1
11. 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 子句中引用多个表时，会主动执行连接。

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

复制代码

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

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

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

复制代码

3.3 自连接

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

1. SELECT WORKER.ename, WORKER.sal
2. FROM emp WORKER, emp MANAGER
3. WHERE WORKER.deptno = MANAGER.deptno
4. 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 行输出结果。

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

复制代码

3.5 外连接

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

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

复制代码

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

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

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

4.1 字符串函数

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

1. SELECT IFNULL(Comm, 0) FROM EMP;

复制代码

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

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

复制代码

4.2 算术函数

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

4.3 聚合函数

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

4.4 日期函数

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

1. SELECT DATE('2003-12-31 01:02:03');
2. -- 将返回“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 使用示例

1. SELECT deptno, AVG(sal)   
2. FROM emp  
3. GROUP BY deptno;

复制代码

这个查询将员工表（emp）按照部门编号（deptno）分组，并计算每个部门的匀称薪水（AVG(sal)）
5.3 GROUP BY规则

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

5.4 多列分组

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

1. SELECT deptno, job, COUNT(*), AVG(sal)  
2. FROM emp  
3. GROUP BY deptno, job;

复制代码

5.5 WHERE 和 GROUP BY 结合使用

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

1. SELECT deptno, AVG(sal)   
2. FROM emp  
3. WHERE job NOT IN ('MANAGER', 'PRESIDENT')  
4. GROUP BY deptno;

复制代码

六、THE HAVING CLAUSE 【Having子句】

6.1 HAVING 的根本概念

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

6.2 使用示例

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

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

复制代码

6.3 HAVING 与 WHERE 的区别

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

6.4 复杂示例

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

复制代码

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

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

1. SELECT job, AVG(sal)   
2. FROM emp  
3. GROUP BY job  
4. HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE job = 'MANAGER');

复制代码

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4