泰安正规的网站建设,集团门户网站建设公司,行业网站 源码,seo基础培训一、SELECTING DATA FROM TABLES【查询数据】
SELECT命令是表上所有查询的基础#xff0c;因此给出它的完整描述以显示它的功能。在描述之后提供各种格式的示例。
1.1 整体描述
SELECT column1, column2, ...
FROMtable1
[INNER | LEFT | RIGHT] JOIN table2 on conditions…一、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 数据。这些函数分为四类
字符串函数用于搜索和操作字符串。算术函数用于对数值进行计算。日期函数用于重新格式化和进行日期计算。日期函数用于重新格式化和进行日期计算。
4.1 字符串函数 LOWER(string) 将大写字母转换为小写字母。其他字符不受影响。
SELECT LOWER(MR. SAMUEL HILLHOUSE) FROM DUAL;
-- 结果: mr. samuel hillhouseUPPER(string) 将小写字母转换为大写字母。
SELECT UPPER(Mr. Rodgers) FROM DUAL;
-- 结果: MR. RODGERSSUBSTR(string, startposition, length) 显示从指定位置开始的字符串的一部分长度为指定值。
SELECT SUBSTR(ABCDEF, 2, 3) FROM DUAL;
-- 结果: BCDINSTR(string1, string2) 查找一个字符串在另一个字符串中的起始位置。
SELECT INSTR(ABCDEF, DEF) FROM DUAL;
-- 结果: 4STR_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;
-- 结果: ??hiRPAD(str, len, padstr) 用指定的填充字符将字符串右侧填充到指定长度。
SELECT RPAD(hi, 4, ??) FROM DUAL;
-- 结果: hi??LTRIM(string) 返回去除前导空格的字符串。
SELECT LTRIM( barbar) FROM DUAL;
-- 结果: barbarRTRIM(string) 返回去除尾随空格的字符串。
SELECT RTRIM(barbar ) FROM DUAL;
-- 结果: barbarTRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) 返回去除给定字符串中所有前缀或后缀的字符串。
SELECT TRIM(a FROM aaaabcaaa) FROM DUAL;
-- 结果: bcIFNULL(expression1, expression2) 如果第一个表达式不为 NULL则返回第一个表达式。否则返回第二个表达式。
SELECT IFNULL(Comm, 0) FROM EMP;LENGTH(char) 返回指定字符串的字符长度。
SELECT LENGTH(Anderson) FROM DUAL;
-- 结果: 84.2 算术函数 ABS(numeric) 返回数字的绝对值。
SELECT ABS(-15) AS Absolute FROM DUAL;
-- 结果: 15MOD(num1, num2) 返回 num1 除以 num2 的余数。
SELECT MOD(7, 5) AS Modulo FROM DUAL;
-- 结果: 2ROUND(numeric[, d]) 将数字四舍五入到 d 位小数。可以选择四舍五入到小数点的两边。
SELECT ROUND(15.193, 1) AS Round FROM DUAL;
-- 结果: 15.2TRUNCATE(numeric[, d]) 截断数字到 d 位小数不进行四舍五入。
SELECT TRUNCATE(15.79, 1) AS Truncate FROM DUAL;
-- 结果: 15.7CEIL(numeric) 向上取整到最接近的整数。
SELECT CEIL(10.6) FROM DUAL;
-- 结果: 11FLOOR(numeric) 向下取整到最接近的整数。
SELECT FLOOR(10.6) FROM DUAL;
-- 结果: 10SQRT(numeric) 返回数字的平方根。如果数字为负返回 NULL。
SELECT SQRT(25) FROM DUAL;
-- 结果: 5TO_CHAR(numeric[, format]) 将数字转换为字符字符串并按照指定的格式进行格式化。
SELECT TO_CHAR(1234.5678, 9999.99) FROM DUAL;
-- 结果: 1234.57DATE_FORMAT(date, format)MySQL 特有 按照指定格式将日期格式化为字符串。
SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s) FROM DUAL;
-- 结果: 2024-09-11 14:30:454.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规则
SELECT 列表限制SELECT 列表中只能包含聚合函数如 MAX(sal), COUNT(empno)和 GROUP BY 子句中指定的列。位置GROUP BY 子句必须放在 WHERE 子句之后如果有的话。通常选择通常 SELECT 列表中会包含 GROUP BY 子句中指定的列以便为计算结果提供标签。默认分组如果没有指定 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);
这个查询列出了平均薪水高于所有经理平均薪水的职位。
