Oracle DB Day02(SQL)

--数据库下表一般都是从1开始，例如字符串等

SELECT *FROM employees

--字符串连接CONCAT()
SELECT employee_id,email,salary,salary*12,CONCAT(CONCAT(EMPLOYEE_ID,','),SALARY)
FROM employees
--字符串连接 还可以使用 ||
SELECT employee_id,email,salary,salary*12,EMPLOYEE_ID||','||SALARY
FROM employees

--length只能用来求VARCHAR2，不能求CHAR没有意义
SELECT first_name,length(first_name)
FROM employees;
desc employees;

--UPPER 全变大写、LOWER全变小写、INITCAP首字母大写其余小写
SELECT first_name,UPPER(first_name)
FROM employees;
SELECT first_name,LOWER(first_name)
FROM employees;
--数据库提供了一个伪表dual，用来满足语法要求
SELECT UPPER('hello,word')
FROM dual

--TRIM、LTRIM、RTRIM截取子串
--trim从字符串前后删除某单一字符
SELECT TRIM('e' From 'eekkeeeeHello World,I am Kwineeeee!eeeeeee')
from dual;
--ltrim 删除字符串左边的某几个字符,m默认删除空格
SELECT LTRIM( 'hello','he')
from dual;
--删除字符串右面的某几个字符，默认删除口弄个
SELECT RTRIM('hello word','world')
from dual

--LPAD、RPAD
--LPAD左补位
SELECT LPAD('hello ',100,'world')
FROM DUAL ;
--RPAD右补位
SELECT RPAD('hello',100,'world')
FROM dual;

--SUBSTR、INSTR
--SUBSTR 截取从哪到哪的字符串
SELECT SUBSTR('Doctor who travels in tardis',8,16)
FROM DUAL;
--INSTR返回在字符串中的位置
SELECT INSTR('Doctor Who','Who') as "words"
FROM dual;

--数值类型
CREATE TABLE student (
id NUMBER(10),
name CHAR(20)
);
desc student

--四舍五入，第二参数为小数点精确位数
SELECT ROUND(45.678,2)
FROM  dual;
--trunc 用于截取
SELECT TRUNC(45.678,2)
from dual ;
--floor向下取整
SELECT floor(45.3)
from dual;
--CEIL向上取整
SELECT CEIL(45.3)
from dual;
--mod 求余数,若第二参数为0则返回第一参数
SELECT MOD(13.3,10)
from dual;

--日期操作
CREATE TABLE time(
c1 DATE,
c2 TIMESTAMP

)

SELECT sysdate from dual;
SELECT to_char(systimestamp,'YYYY-MM-dd HH24:mm:ss.ff DY DAY') from dual ;
--返回指日期的当月最后一天
SELECT LAST_DAY(sysDATE) FROM dual;
--指定日期加上i个月后的日期
SELECT ADD_MONTHS(sysdate,20*12) from dual;
--计算两个日期间之间有多少个月MONTHS_BETWEEN
SELECT MONTHS_BETWEEN('2019-12-25',sysdate) from dual;
--NEXT_DAY 返回日期数据的下一个周几,周日为1
SELECT NEXT_DAY(sysdate , 1) from dual;

--LEAST、GREATEST 返回最小或最大值,比较的必须是同一类型
SELECT LEAST(34,555,7999,2,3344) from dual;
SELECT GREATEST(34,555,7999,2,3344) from dual;
--EXTRACT 从参数中提取date指定的年月日等
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;

--数据库中任何数据类型都可以取null值 
--null查询用 IS NULL
--NOT NULL非空约束

desc employees
select * from employees;
--NVL() 若第一参数为null，则取值第二参数
SELECT employee_id,first_name,salary,salary + NVL(commission_pct,10) as "sal" from employees;
--NVL2() 若第一参数为null，则取值第三参数；否则第一参数非null取值第二参数
SELECT employee_id,first_name,salary,salary + NVL2(commission_pct,10,14) as "sal2" from employees;