为了演示，我们先新建一张user表，SQL语句如下:
当插入数据时，如果唯一性校验出现重复问题，则报错；
如果没有重复性问题，则执行插入 *** 作。
简单总结：重复则直接报错，sql 语句不执行，不重复则插入。
示例
执行结果
当插入数据时，如果唯一性校验出现重复问题，则忽略错误，只以警告形式返回，不执行此SQL语句；
如果没有重复性问题，则执行插入 *** 作。
简单总结：重复则忽略，sql 语句不执行，不重复则插入。
示例
执行结果
当插入数据时，如果唯一性校验出现重复问题，则在原有记录基础上，更新指定字段内容，其它字段内容保留；
如果没有重复性问题，则执行插入 *** 作。
简单总结：重复则更新指定字段，不重复则插入。
示例
执行结果
表记录， mobile_phone_number 从 '13800000077' 更新为 '13800000088' 了， update_time 也从 NULL 更新为有值了，但是 id 没有变:
replace into表示插入替换数据，当插入数据时，如果唯一性校验出现重复问题，删除旧记录，插入新记录；
如果没有重复性问题，则执行插入 *** 作，效果和insert into是一样的。
简单总结：重复则先删除再插入新记录，不重复则插入
。
示例
执行结果
表记录， id 和 mobile_phone_number 变了， update_time 变为了字段默认值 NULL :
replace into 执行的逻辑:
示例一
示例一 insert into ... on deplicate key update *** 作在 binlog 中记录为：
示例二
示例二 replace into *** 作在binlog中记录为：
从示例可以看出，使用 replace into 会有以下问题:
执行结果
因为全部列都是指定的值，所以，相当于所有字段全部更新了一次。
binlog 中的记录:
如果出现重复异常，希望捕获异常，则使用 insert into
如果出现重复异常，希望保存旧纪录，忽略新纪录，则使用 insert ignore into
如果出现重复异常，希望更新指定字段，则使用 insert into … on duplicate key update
如果出现重复异常，希望删除旧记录，插入新记录，则使用 replace into 。
https://blog.csdn.net/ThinkWon/article/details/106610789
https://dsb123dsb.github.io/2019/01/13/%E4%B8%80%E6%AE%B5replace-into-%E5%BC%95%E5%8F%91%E7%9A%84%E8%A1%80%E6%A1%88/
https://blog.csdn.net/quuqu/article/details/110636263
insert allinto dept(deptno,dname) values(50,'测试一部')
into dept(deptno,dname) values(51,'测试二部')
SELECT 1 FROM DUAL
insert all into dept(deptno,dname)
into dept(deptno,dname) values(60,'测试一部')
into dept(deptno,dname) values(61,'测试二部')
SELECT 62,'测试三' FROM DUAL
begin
insert into dept(deptno,dname) values(70,'测试一部')
insert into dept(deptno,dname) values(71,'测试二部')
end
oracle的序列 sequences(mysql自增长)
sequences的使用分两步
1、新建一个序列(工具建)
2、如何使用
3、函数对比
https://blog.csdn.net/qq_39137554/article/details/76034335
3.1）数学函数
oracle: ceil(-1.001) mysql: ceiling(-1.001)
3.2)字符串函数
1）ascii(str)一样：返回一个字符对应的ascii码值
2）chr||char （不同）
ORACLE:chr(97) MYSQL:char(97)返回这个整数所代表的 ASCII 码值
oracle示例：
3）INSTR||locate（mysql新增 mysql可以用instr locate）
INSTR(s1,s2,[,n1],[n2]) 返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值为1
oracle例子：
-- 查询s字符串在sdsq中第一次出现的位置，从头开始查找,索引从1开始计算 返回1
select INSTR('sdsq','s') value from dual
-- 查询s字符串在sdsq中第一次出现的位置，从索引从2开始查找 返回3
select INSTR('sdsq','s',2) value from dual
-- 查询s字符串在sdsq中第一次出现的位置，从索引从2开始查找，如果没有查找到，返回0
select INSTR('sdsq','s',2,2) value from dual
mysql:
instr||locate
4）length||char_length(不同)
SELECT length('AAAASDF') VALUE FROM DUAL
CONCAT：连接
LPAD：在左边加点内容（左追加）
RPAD：在右边加点内容（右追加）
把sal变成10位，不足10位的，右侧 补*
select RPAD(sal,10,'*') as v from emp
REPLACE 替换一样
LOWER 转小写一样
UPPER 转大写 一样
initcap首字母大写
SELECT REPLACE('18604001139', '0400', '****') value from dual
select LOWER(ename) username from emp 转小写
select UPPER(ename) username from emp
select initcap(ename) username from emp
3.2.4.1TRIM:去掉首尾的指定字符，不能去除中间的
oracle:trim(子串 from 父串)
select TRIM('S' FROM 'SSMISSTH') value from dual//MISSTH
select TRIM(' ' FROM ' ab c ') value from dual//ab c
3.2.4.2SUBSTR(oracle)||substring(mysql)
SUBSTR(原字符串，开始处位置，截取长度)
select SUBSTR('String',2,5) value from dual //tring
ORACLE:
练习1：
1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显
示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字
的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序
排序。（提示：使用initcap、length、substr）
练习2 都用函数解决
• 1.查询员工姓名中中包含大写或小写字母A的员工姓名。
select ename
from emp
where instr(ename,'A')>0 OR instr(ename,'a')>0
• 2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中
包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函
数，不能使用like进行判断)
select ename,length(ename) len
from emp
where deptno in(10,20)
and hiredate>'1-5月-1981'
and instr(ename,'A')>0
• 3.查询每个职工的编号,姓名，工资
– 要求将查询到的数据按照一定的格式合并成一个字符串.
– 前10位：编号,不足部分用 填充,左对齐(右填充)
– 中间10位：姓名，不足部分用 填充,左对齐
– 后10位：工资，不足部分用*填充,右对齐
select rpad(empno,10,' ')||rpad(ename,10,' ')||lpad(sal,10,'*') as value
from emp
select concat(concat(rpad(empno,10,' ') , rpad(ename,10,' ')),lpad(sal,10,'*')) as value
from emp
https://www.cnblogs.com/aipan/p/7941917.html
3.4.1 to_char vs date_format/ time_format
select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') VALUE FROM emp
select to_char(-125.8, '999D99S') from dual
3.4.2 to_date vs STR_TO_DATE(str,format)
select to_date('2021-04-23 15:12:20','YYYY-MM-DD HH24:MI:SS') VALUE FROM DUAL
3.4.3 to_number vs CAST("123" AS SIGNED INTEGER)
select to_number('12,454.8-', '99G999D9S') value from dual
select to_number('-12,454.8', 'S99G999D9') value from dual
1.显示服务器系统当前时间，格式为2007-10-1217:11:11(提示：使用to_char函数)
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
from dual
3.查询员工姓名，工资，格式化的工资（￥999,999.99）(提示：使用to_char函数)
select to_char(sal,'L999G999D99')
from emp
4.把字符串2015-3月-18 13:13:13 转换成日期格式
select to_date('2015-3月-18 13:13:13','YYYY-MON-DD HH24:MI:SS')
from dual
select cast(11 as unsigned int) / 整型 /
select cast(11 as decimal(10,2)) / 浮点型 /
3.5.1 nvl/COALESCE vs ifnull
**NVL函数等价于mysql的ifnull
– 语法:NVL (expr1,expr2)
– 如果expr1不是null,返回expr1,否则返回expr2
select comm,nvl(comm,0) from emp
select comm,coalesce(comm,sal,0) from emp
3.5.2 nvl2 vs if
NVL2函数
– 语法:NVL2(expr1,expr2,expr3)
– 如果expr1不是null,返回expr2,否则返回expr3
select nvl2(comm,'无奖金','有奖金') from emp
3.5.3 NULLIF vs 无(可以考虑用if 建议了解，不用掌握)
NULLIF函数
– 语法: NULLIF(expr1,expr2)
– 比较两个表达式,如果相等，返回null,否则，返回第一个表达式
3.5.4 DECODE（解密的意思）/case vs if/case 多分支情况
DECODE(字段| 表达式, 条件1,结果1[,条件2,结果2…，][,缺省值] )
SELECT ename, deptno,
decode(deptno,
10,' 销售部',
20,' 技术部',
30 , ' 管理部',
' 无') deptname
FROM emp
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
SELECT ename, deptno,
(CASE deptno
WHEN 10 THEN ' 销售部'
WHEN 20 THEN ' 技术部'
WHEN 30 THEN ' 管理部'
ELSE ' 无' END) deptname FROM emp
EXTRACT ([YEAR] [MONTH][DAY] FROM [日期表达式])
SELECT ename, sal, MONTHS_BETWEEN(SYSDATE,hiredate) months
FROM emp
ORDER BY months
SELECT ename, sal, hiredate, ADD_MONTHS(hiredate,3) new_date
FROM emp
WHERE hiredate>'01-1月-82'
SELECT NEXT_DAY(sysdate,'星期二') NEXT_DAY
FROM DUAL
SELECT Last_DAY('1-2月-2019') last_DAY
FROM DUAL
select EXTRACT (YEAR FROM HIREDATE) AS 年 from emp
select EXTRACT (YEAR FROM HIREDATE) AS 年 from emp
select EXTRACT (MONTH FROM HIREDATE) AS 年 from emp
select EXTRACT (DAY FROM HIREDATE) AS 年 from emp
4、外连接的加号定法。oralce支持，mysql不支持
查询所有雇员姓名，部门编号，部门名称， 包括没有员工
的部门也要显示出来
select e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno
4、分页的处理
比较日期相等
https://www.cnblogs.com/bailing80/p/11440927.html
本文主要介绍 SQL 标准中定义的各种连接的意义和区别，例如，交叉连接（ CROSS JOIN ）、内连接（ INNER JOIN ）、外连接（ OUTER JOIN ）、自然连接（ NATURAL JOIN ）等，并结合例子讲解这些连接在 MySQL 中的语法和表现。
从网上的资料看， JOIN 更多翻译为连接，本文中凡是出现中文“连接”的地方都是指 JOIN 。
本文中用到的所有例子涉及两张表—— customers 用户表和 orders 订单表，其中订单表中的 cust_id 字段表示用户的唯一 ID，也就是用户表的主键 cust_id 。两张表的数据如下：
注：两张表都经过了简化，实际业务中这两张表肯定还包括其他字段。
英文维基百科 JOIN 词条 对连接的定义如下：
翻译过来就是，“连接可以根据一张（自连接）或多张表中的共同值将这些表的列数据合并为一个新的结果集，标准 SQL 定义了五种连接：内连接、左外连接、右外连接、全外连接和交叉连接。”
也就是说，连接是 SQL 标准中定义的一种组合多张表的方式，当然一张表自身也可以和自身组合，称为自连接。连接后得到的结果集的每一列其实都来自用于连接的多张表，不同的连接类型只是区分了这些列具体从哪张表里来，列里填充的是什么数据。
其实英文维基百科的 JOIN 词条已经把各种连接的类型解释地非常清楚了，非常值得去看一下。
我们来看一下 SQL 标准中定义的各种连接类型，理解各种连接最好的方法就是把需要连接的表想象成集合，并画出可以反映集合的交与并的情况的图——韦恩图，例如下图就画出了 SQL 中定义的几种主要连接。
请先仔细查看一下图中的内容，你可以从中归纳出几种连接类型呢？
虽然图中画了 7 种集合的交并情况，但是总结起来，主要是两种连接类型在起作用——内连接（ INNER JOIN ）和外连接（ OUTER JOIN ），其中外连接又分为了左外连接（ LEFT OUTER JOIN ）、右外连接（ RIGHT OUTER JOIN ）和全外连接（ FULL OUTER JOIN ）。
下面先简单介绍一下 SQL 标准中各种连接的定义，然后在「MySQL 中的连接」一节再用例子来演示 MySQL 中支持的各种连接。
连接既然是用来合并多张表的，那么要定义一个连接就必须指定需要连接的表，并指定可选的连接条件。例如，一个典型的 SQL 连接语句如下：
我们用表 A 和表 B 指代需要连接的两张表，经过 内连接 后得到的结果集 仅 包含所有满足 连接条件 的数据；而经过 外连接 后得到的数据集 不仅 包含满足 连接条件 的数据，还包含其他数据，具体的差别是：
在上面「SQL 标准定义的主要连接」一图中并没有列出交叉连接，交叉连接会对连接的两张表做笛卡尔积，也就是连接后的数据集中的行是由第一张表中的每一行与第二张表中的每一行配对而成的，而不管它们 逻辑上 是否可以搭配在一起。假设交叉连接的两张表分别有 m 和 n 行数据，那么交叉连接后的数据集就包含 m 乘以 n 行数据。
连接根据连接的条件不同，又可以区分为等值连接和非等值连接，「SQL 标准定义的主要连接」图中画出的连接的连接条件都是比较两个字段是否相等，它们都是等值连接。
自然连接是等值连接的一种特殊形式，自然连接会自动选取需要连接的两张表中字段名相同的 所有 列做相等比较，而不需要再指定连接条件了。
注：以下内容全部基于 MySQL 5.7 版本，所有例子只保证在 MySQL 5.7 上是可以正确执行的。
MySQL 中支持的连接类型和关键字如下：
上面的表示方法摘自 MySQL 5.7 版本 官方文档 ，其中 | 表示两者皆可出现， [] 表示的是可选的， {} 表示的是必选的，例如 NATURAL LEFT JOIN 和 NATURAL JOIN 都是合法的。
可以看到，除了全外连接（ FULL OUTER JOIN ）以外， MySQL 基本支持了 SQL 标准中定义的各种连接。在 MySQL 中全外连接可以通过 UNION 合并的方式做到，当然前提是你知道自己为什么需要这么做，具体参见： Full Out Join in MySQL 。
MySQL 语法中还支持一个并不在 SQL 标准中的 STRAIGHT_JOIN ，它在 表现上 和内连接或者交叉连接并无区别，只是一种给 MySQL 优化器的一个提示， STRAIGHT_JOIN 提示 MySQL 按照语句中表的顺序加载表，只有在你明确清楚 MySQL 服务器对你的 JOIN 语句做了负优化的时候才可能用到它。
还有一点需要说明的是，根据 官方文档 ，在 MySQL 中， JOIN 、 CROSS JOIN 和 INNER JOIN 实现的功能是一致的，它们在语法上是等价的。从语义上来说， CROSS JOIN 特指无条件的连接（没有指定 ON 条件的 JOIN 或者没有指定 WHERE 连接条件的多表 SELECT ）， INNER JOIN 特指有条件的连接（指定了 ON 条件的 JOIN 或者指定了 WHERE 连接条件的多表 SELECT ）。当然，如果你非要写 ... CROSS JOIN ... ON ... 这样的语法，也是可以执行的，虽然写着交叉连接，实际上执行的是内连接。
下面我们就用例子来看一看 MySQL 中支持的几种连接的例子。
注：下面的例子都没有指定 ORDER BY 子句，返回结果的顺序可能会因为数据插入顺序的不同而略有不同。
MySQL 的交叉连接或内连接有两种写法，一种是使用 JOIN 并用 ON 或者 USING 子句指定连接条件的写法，一种是普通的 SELECT 多表，并且用 WHERE 子句指定连接的键的写法。
下面的例子是一个交叉连接：
上面的写法等价于：
当然，第二种写法中如果将 CROSS JOIN 替换成 JOIN 或者 INNER JOIN 也是可以正确执行的。上面两条语句的执行结果如下：
可以看到共返回了 30 行结果，是两张表的笛卡尔积。
一个内连接的例子如下：
上面的写法等价于：
在连接条件比较的字段相同的情况下，还可以改用 USING 关键字，上面的写法等价于：
上面三条语句的返回结果如下：
可以看到只返回了符合连接条件 customers.cust_id = orders.cust_id 的 6 行结果，结果的含义是所有有订单的用户和他们的订单。
左外连接和右外连接的例子如下，其中的 OUTER 关键字可以省略：
其中右外连接的返回与内连接的返回是一致的（思考一下为什么），左外连接的返回结果如下：
可以看到一共返回了 8 行数据，其中最后两行数据对应的 order_id 的值为 NULL ，结果的含义是所有用户的订单，不管这些用户是否已经有订单存在了。
根据前面介绍的自然连接的定义，自然连接会自动用参与连接的两张表中 字段名相同 的列做等值比较，由于例子中的 customers 和 orders 表只有一列名称相同，我们可以用自然连接的语法写一个与上面的内连接的例子表现行为一样的语句如下：
可以看到，使用自然连接就不能再用 ON 子句指定连接条件了，因为这完全是多余的。
当然，自然连接同样支持左外连接和右外连接。
下面用一个 customers 表自连接的例子再来说明一下自然连接，语句如下：
因为是自连接，因此必须使用 AS 指定别名，否则 MySQL 无法区分“两个” customers 表，运行的结果如下：
可以看到结果集和 customers 表完全一致，大家可以思考一下为什么结果是这样的。
文章之前也提到了，MySQL 还支持一种 SQL 标准中没有定义的“方言”， STRAIGHT_JOIN ， STRAIGHT_JOIN 支持带 ON 子句的内连接和不带 ON 子句的交叉连接，我们来看一个 STRAIGHT_JOIN 版本的内连接的例子：
返回结果与前面内连接的例子是一致的，如下：
STRAIGHT_JOIN 的表现和 JOIN 是完全一致的，它只是一种给 MySQL 优化器的提示，使得 MySQL 始终按照语句中表的顺序读取表（上面的例子中，MySQL 在执行时一定会先读取 customers 表，再读取 orders 表），而不会做改变读取表的顺序的优化。关于 MySQL 优化器的话题这里不做展开，需要说明的是除非你非常清楚你在做什么，否则不推荐直接使用 STRAIGHT_JOIN 。
你能理解上面的语句是在检索什么数据吗？
本文主要介绍了 SQL 标准里定义的各种连接的概念，以及 MySQL 中的实现，并通过各种例子来介绍了这些连接的区别。这些连接不一定都能在实际开发中用到，但是做到心中有知识也还是很有必要的。
那么，现在再回忆一下，什么是内连接、外连接、自连接、等值连接和自然连接？他们的区别是什么？
最后，给大家留一个思考题，为什么 MySQL 中没有左外连接或者右外连接版本的 STRAIGHT_JOIN ？
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）