SQL基础二(Oracle)

DQL数据查询语言：查询语句基本由SELECT子句，与FROM子句构成.SELECT子句指定要查询的字段.FROM指定数据来源.SELECT子句中可以出现表中的字段,函数或表达式.

ex:

SELECT * FROM emp;
SELECT ename,job,sal,deptno FROM emp;
SELECT ename,sal*12 FROM emp;

1、WHERE子句，可以添加过滤条件，用在查询中可以将只满足WHERE条件的记录查询出来

ex:查看工资大于2000的员工?

SELECT ename,sal FROM emp WHERE sal>2000;

ex: 不等于判断用"<>"

SELECT ename,job,sal,deptno FROM emp WHERE deptno<>20;

2、字符串操作函数：

2.1 连接字符串 CONCAT(c1,c2) 、"||"可以同时连接多个字符串(oracle 独有的)

SELECT CONCAT(ename,sal) FROM emp;
SELECT CONCAT(CONCAT(ename,':'),sal) FROM emp;
SELECT ename||':'||sal FROM emp;

2.2、查看字符串长度 LENGTH(ch)

SELECT ename,LENGTH(ename) FROM emp
SELECT ename,sal FROM emp WHERE LENGTH(ename)>4

2.3、转换大写，小写，首字母大写 UPPER,LOWER,INITCAP

dual:伪表。当查询的内容不是，任何表中的内容时使用。

SELECT UPPER('helloworld')FROM dual ;
SELECT LOWER('HELLOWORLD') FROM dual;
-- <Oracle> 独有的将首字母转换为大写的字符串函数
SELECT INITCAP('HELLO WORLD') FROM dual ;
SELECT ename,sal,deptno FROM emp WHERE ename=UPPER('scott');

2.4、去除字符串两边，左边，右边的指定内容 TRIM | LTRIM | RTRIM

SELECT TRIM('e' FROM 'eeeeliteee') FROM dual;
SELECT LTRIM('esfsffefesliteeee','efs')FROM dual;
SELECT RTRIM('eeeeliteeee','e') FROM dual;

2.5 补位函数 LPAD 左补位 | RPAD 右补位

ex:名字不够10位在右边填充空格

SELECT RPAD(ename,10,' ') FROM emp;

2.6、截取字符串 SUBSTR(ch,m,n)，从第m个字符开始截取n个字符，n不指定则是截取到末尾，n超过实际可以截取的个数也是截取到末尾。m若是负数，则是从倒数位置开始截取。数据库的下标都是从1开始

SELECT SUBSTR('thinking in java',-7,2)FROM dual;

2.7、INSTR(ch1,ch2,m,n) 查看ch2在ch1中的位置，m表示从哪里开始查找，n表示第几次出现；m,n不写默认为 1。

--<oracle的用法>
SELECT INSTR('thinking in java','in',4,2) FROM dual ;
-- <mysql的用法>
SELECT INSTR('thinking in java','in') FROM dual;

2.8、数字函数: ROUND(n[, m]) 四舍五入

SELECT ROUND(45.678, 2) FROM DUAL;
SELECT ROUND(45.678, 0) FROM DUAL;
SELECT ROUND(55.678, -2) FROM DUAL;

-- TRUNC(n[, m]) 截取字符串 Oracle独有的函数
SELECT TRUNC(45.678, 2) FROM DUAL
SELECT TRUNC(45.678, 0) FROM DUAL
SELECT TRUNC(55.678, -1) FROM DUAL

3、数值操作函数

3.1、MOD(m,n) 取模

ex:

SELECT MOD(589,11) as value;
--value:6

3.2 CEIL(n)和FLOOR(n) 向上取整（大于等于n的最小整数）和向下取整（小于等于n的最大整数）

ex:

SELECT CEIL(45.678) FROM DUAL;
--46
SELECT FLOOR(45.678) FROM DUAL;
--45

4、日期类型相关的关键字

4.1、SYSDATE:对应一个内置的函数，返回一个DATE类型的当前系统时间。SYSTIMESTAMP:返回一个时间戳类型的当前系统时间。

-- Oracle
SELECT SYSDATE FROM dual;
SELECT SYSTIMESTAMP FROM dual;

--MySQL
SELECT SYSDATE() FROM dual;--2021-03-26 12:52:42
SELECT CURRENT_TIMESTAMP() FROM dual;--2021-03-26 12:52:42
SELECT now() FROM dual;--2021-03-26 12:52:42

4.2 、TO_DATE()函数：可以将一个字符串按照给定的日期格式转换为DATE

-- Oracle
SELECT TO_DATE('1992-08-03','YYYY-MM-DD') FROM dual;
select to_date(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd HH24:mi:ss') from dual;

-- MySQL
--str_to_date()
select str_to_date('2012-05-01 23:59:59','%Y-%m-%d %T')

4.3 、日期格式表达方式和Oracle也不同

日期可以进行计算：对一个日期加减一个数字，结果相当于加减了天。两个日期之间相减差为相差的天数。日期可以比大小，越晚的越大。

SELECT SYSDATE+1 FROM dual

ex:查看每个员工入职到今天共多少天?

SELECT ename,SYSDATE-hiredate FROM emp;
SELECT SYSDATE-TO_DATE('1992-08-03','YYYY-MM-DD') FROM dual;

4.4、TO_CHAR() 将日期按照指定格式转换为字符串 [ORACLE]

SELECT ename,TO_CHAR(hiredate,'YYYY-MM-DD') FROM emp  ;
SELECT ename,TO_CHAR(hiredate,'YYYY"年"MM"月"DD"日"') FROM emp;

4.5、YY与RR在TO_DATE函数中将2位数字解析为DATE时是有所区别的

YY:不关注世纪 RR:自行判定世纪

SELECT TO_CHAR( TO_DATE('49-05-01','RR-MM-DD'),'YYYY-MM-DD') FROM dual;

4.6、LAST_DAY(date) [Mysql 和 Oracle都适用]返回给定日期所在月的月底日期

--MySQL
SELECT LAST_DAY(SYSDATE()) FROM dual;
--output:2021-03-31

4.7、ADD_MONTHS(date,i) 对指定日期加指定的月,若i是负数则是减去。[Oracle独有]

ex:查看每个员工入职20周年纪念日:

SELECT ename,ADD_MONTHS(hiredate,12*20) FROM emp;

4.8、MONTHS_BETWEEN(date1,date2) 计算两个日期之间相差的月。[Oralce独有]

ex：查看每个员工入职至今共多少个月?

SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;

4.9、NEXT_DAY(date,i) 返回给定日期第二天开始一周内的指定周几的日期。 [Oralce独有]

SELECT NEXT_DAY(SYSDATE,3) FROM dual

4.10、LEAST、GREATEST 求最小值与最大值**

SELECT LEAST(SYSDATE, TO_DATE('2008-08-08','YYYY-MM-DD')) FROM dual;

4.11、EXTRACT函数获取给定日期指定时间分量对应的值. [MySQL 和 Oracle都适用]

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;--Oracle
SELECT EXTRACT(YEAR FROM SYSDATE()) FROM dual;--MySQL
--output:2021
SELECT EXTRACT(MONTH FROM SYSDATE()) FROM dual;--MySQL
--output:3

ex:查看1980年入职的员工?

SELECT ename,hiredate FROM emp WHERE EXTRACT(YEAR FROM hiredate)=1981;

5、空值操作

CREATE TABLE student(id NUMBER(4),name CHAR(20),gender CHAR(1));
INSERT INTO student VALUES(1000, '李莫愁', 'F');
INSERT INTO student VALUES(1001, '林平之', NULL);
INSERT INTO student(id, name) VALUES(1002, '张无忌');
SELECT * FROM student;

ex:更新成NULL

UPDATE student SET gender=NULL WHERE id=1000

5.1、在判断一个字段值是否为NULL要使用 IS NULL 或 IS NOT NULL

ex：

DELETE FROM student WHERE gender IS NULL;

5.2、NULL参与运算的注意事项

5.2.1、NULL与字符串连接等于什么也没做
5.2.2、NULL与数字运算结果还是NULL

SELECT ename,sal,comm,sal+comm FROM emp;
select "HelloSQL" + NULL;
--output:什么也没有

5.3、空值函数

若第一个参数为NULL，函数返回第二个参数，否则直接返回第一个参数

-- Oracle
NVL(arg1,arg2)

ex:

SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp;

--MySQL
--ifnull(arg1,arg2)
select IFNULL("HelloSQL","HelloWorld");
--output:HelloSQL
select IFNULL(null,"HelloWorld");
--output:HelloWorld

ex: 查看每个员工的绩效情况，即:有绩效的显示为"有绩效"，为NULL的则显示为"没有绩效"

-- Oracle
NVL2(arg1,arg2,arg3)
--若arg1不为NULL，函数返回arg2
--若arg1为NULL，函数返回arg3
SELECT ename,comm, NVL2(comm,'有绩效','没有绩效') FROM emp;
SELECT ename,sal,comm,NVL2(comm,sal+comm,sal) FROM emp;

--MySQL
--如果expr1=expr2成立，那么返回值为NULL，否则返回值为expr1
nullif(expr1,expr2)