Loading... ## 易错SQL ### 运算符： 如果是null，必须用is/is not，不能用=/!=null where执行顺序：右->左 `select* from emp where not (mgr = 7788 and job = 'CLERK')` ### null： 任何数字 和null结算，结果为null 需要对null进行处理comm字段：null->0 `nvl(comm,0 )` //nvl:if `nvl2(comm,comm,0)` //nvl2:if...else if(comm==null) return 0 else return comm not in不能出现null：如果出现了null，结果为null select *from emp where deptno not in(10,20,30,null) ; ### distinct: 对查询出的结果集去重 `select distinct deptno from emp` ### 连接符： java: "hello"+"world"->"helloworld" oracle: concat 或 || `select concat("hello","world") from emp;` `select "hello"||"world" from emp;` ### dual： oracle提供的 学习时使用 临时表：单行单列 `select 'hello'||'world' from dual;` ### 修改oracle默认的日期格式 默认：DD-MON-RR 修改：`alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd' ;` 还原：`alter session set NLS_DATE_FORMAT = 'DD-MON-RR' ;` ### 模糊查询： 支持字符、数字、日期 姓名长度>6的员工信息(>6 >=7):`select *from emp where ename like '_______%' ;` 姓名中包含下划线的(zhang_san):`select *from emp where ename like '%\_%' escape '\' ;` escape '\':声明\为转义字符，理论上可替换为任意字符 ### 排序： `order by asc(升序默认)/desc(降序);` null默认是最大值即 `nulls first`,可通过 `nulls last`把null放在最后 mysql实现 `nulls last`：`select * from A order by IF(ISNULL(a),1,0),a desc`//Mysql的IF和ISNULL函数。如果为空返回1，否返回0,反之则调换 `1,0` 多列排序：order by name asc,id desc(逗号隔开); --- ## 函数 单行函数：一次操作一行，有多少行返回多少行。 多行函数：一次操作多行，多少行都只返回一行。 ### 单行函数： **字符函数：** * **lower(str)**:转大写、upper(str):转小写、**initcap(str)**:首字母大写 * **dual**:单行/单列输出 * **substr(str,begin,length)**:从1开始数 * **length(str)**:字符数、**lengthb(str)**:字节数，utf8汉子3字节，gbk2字节 * **insrt(str,substr)**:在str中招substr位置，1开始 * **lpad(str,num,'\*')**:左填充、rpad(str,num,'*')右填充 * **trim(str)**:默认 去左右空格 **trim('X' from 'XXXX123XXXX')**:去X * **replace('hello','l','\*')**:把hello中的l替换成* **数值函数：** * **round(数字,n位数)**:四舍五入，保留n位小数，n可为负数 * **trunc(数字,n位数)**:舍尾，保留n位小数，n可为负数 * **mod(数字，除数)**:取余 * **sysdate**:没有()，返回当前日期：“19-2月 -20” * **to_char(日期，格式)**:转换日期格式 `select to_char(sysdate,'yyyy-mm-dd') from dual;`2020-02-19 * 日期+/-数字（默认是天） 日期减日期，加没有意义：计算员工工龄：入职日期，天，星期，月，年 `select ename ,hiredate , (sysdate - hiredate) , (sysdate - hiredate)/7 , (sysdate - hiredate)/30, (sysdate - hiredate)/365 from emp;` * **months_between(日期1,日期2)**：日期1-日期2，精确算月数 * **add_months(日期,月数)**：日期加月数 * **last_day(日期)**,当月的最后一天：“29-2月 -20” * **next_day(日期,'星期X')**:下一个星期X是哪一天：“21-2月 -20” * **round(日期,'month/year')**:对月/年四舍五入(过半) * **trunc(日期,'month/year')**:对月/年舍尾(过半) **通用函数：** * **nvl(comm,0)** //nvl:if null **nvl2(comm,comm,0)** //nvl2:if not null...else * **nullif(a,b)** //if a=b 返回null，否则返回a * **coalesce(字段1，字段2)**:从左往后 找到第一个不为null的值 * **decode(字段,条件1，返回值1，条件2，返回2，....,最后表达式)** ``` case when then when then else end ``` **转换函数：** `隐式转换：`默认转换 字符转数字、数字转字符(适用于+/-)、字符转日期、日期转字符 **nvarchar2/nchar等字符⇋number/date** `显式转换：`  占位符：   字符->字数 `select to_number('￥123,456.7','L999,999.9') from dual ;` 字数->字符 `select to_char('L999,999.9', '￥123,456.7') from dual ;` 字符-日期 `select to_date('2020-02-21','yyyy-mm-dd') from dual;` 日期-字符 `select to_char(sysdate,'yyyy') from dual;` ### 多行函数(组函数、聚合函数)： * **count(\*)**:总数，自动排空null `select count(distinct deptno) from emp;`计算部门总数 * **max/min/avg/sum()**:求最大/最小/平均/综合 **分组：Group by** ### 附：MySQL与Oracle 函数差异比较 [MySQL与Oracle 函数差异比较][1] --- ## 多表连接查询 **1. 交叉连接（笛卡尔积）:**所有情况的组合,不推荐使用 `select * from emp,dept;` **2. 内连接：**多张表通过相同字段进行匹配，只显示匹配成功的数据 a.`select * from emp e ,dept d where e.deptno = d.deptno;` b.select * from emp e inner join dept d on e.deptno = d.deptno ` **3. 外连接** **左外连接：**以左表为基准（左表数据全部显示），去匹配右表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充) a.（oracle独有）`select * from emp e ,dept d where e.deptno = d.deptno(+) ;` b.`select * from emp e left outer join dept d on e.deptno = d.deptno ;` **右外连接：**以右表为基准（右表数据全部显示），去匹配左表数据，如果匹配成功 则全部显示；匹配不成功，显示部分(无数据部分 用NULL填充) a.（oracle独有）`select * from emp e ,dept d where e.deptno(+) = d.deptno;` b.`select * from emp e right outer join dept d on e.deptno = d.deptno ;`**全外连接** = 左外 + 右外连接 - 去重 **4. 自连接：**将一张表通过别名 “视为”不同的表,费性能(n^2) 查询 员工姓名，以及该员工的领导姓名//员工表的领导编号mgr = 领导表的 员工编号号empno`select e.ename ,b.ename from emp e,emp b where e.mgr =b.empno;` 优化↓ **5.层次连接：** ```SQL select level ,empno, ename ,mgr from emp connect by prior empno=mgr start with mgr is null order by level ; ``` --- ## 子查询 1. 子查询可以出现的位置：where、select、having、from;不能写在group by后面。 select后面只跟单行列 （常量列）：<br>`select empno 第一列,ename 第二列,'HELLO' 第三列 from emp ;` 2. 主查询和子查询可以是同一张表，也可以是不同张表。 3. 子查询可以使用 单行操作符（=,<），多行操作符(in) **"只需要满足一个即可，存在一个就可以"->any**<br> `select *from emp where sal > any(select sal from emp) ;`<br>等价于 `select *from emp where sal > (select min(sal) from emp) ;`<br>**"所有、全部"->all**<br> `select *from emp where sal > all(select sal from emp) ;`<br>等价于 `select *from emp where sal > (select max(sal) from emp) ;` 4. 子查询的结果中不要有NULL！ NULL:自身特性： 如果!=/=NULL则无法查询出任何数据，要用is not/is null<br>in : = or = or<br>not in : != and != and 5. 一般不在子查询中排序，除非TOP-N问题（分页） [1]: https://www.cnblogs.com/zhanym/p/7448272.html Last modification：August 11, 2022 © Allow specification reprint Like 0 喵ฅฅ