--数据库下表一般都是从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;