SQL 笔记

1.大小写忽略查询 UPPER/LOWER/INITCAP（首字母大写）
SQL>SELECT * FROM employees WHERE UPPER(last_name) = 'WHALEN';
SQL>SELECT * FROM employees WHERE LOWER(last_name) = 'whalen';
2.转义字符 ESCAPE
SQL> SELECT * FROM employees WHERE job_id LIKE '%SA/_%' ESCAPE '/';--把_视为普通字符，而不是like中的占位符
3.优先级排序()>AND>OR
4.ORDER BY (ASC NULL值排最后，DESC NULL值排最前) 通过NULLS LAST(FIRST) 改变NULL值排序。
SQL>SELECT * FROM employees WHERE LOWER(last_name) = 'whalen' ORDER BY department_id DESC NULLS LAST(FIRST);
4.变量查询
SQL>SELECT * FROM employees WHERE LOWER(last_name) = '&name';(如果是传入数字不加单引号);
SQL>SELECT * FROM employees WHERE LOWER(last_name) = '&&name' ORDER BY &name;(双&符号，可以替换所有相同变量。)

SQL>DEFINE name = whalen
SQL>SELECT * FROM employees WHERE LOWER(last_name) = '&name';
SQL>UNDEFINE name

SQL>SET VERIFY ON/OFF --显示/不显示输入的变量值
5.字符串函数
CONCAT('hello',' world') ---hello world
SUBSTR('helloworld',1,5) --- hello
LENGTH('hello')          ---5
INSTR('helloworld','w')  ---6
LPAD(salary,10,'*')      ---*****24000
RPAD(salary,10,'*')      ---24000*****
REPLACE('jack and jue','j','bl') ---black and blue
TRIM('h'FROM 'hello')    ---ello
6.数字函数
ROUND(45.5452,2)         ---45.55
TRUNC(45.5432)    ---45.54
MOD(1600,300)   ---100
SQL>SELECT ROUND(45.54,2),ROUND(45.54,0),ROUND(45.54,-1) FROM DUAL;
------ ------ -----
 45.54     46    50

7.数据类型转换
日期转字符
TO_CHAR(DATE,'(fm)formatModel');--加fm是去0和空格
数值转字符
TO_CHAR(number,'(fm)formatModel');
字符转数值
TO_NUMBER(char,'formatModel');--TO_NUMBER('-$12,34.5','$99,99.9') == -1234.5
字符转日期
TO_DATE(char,'(fx)formatModel');--TO_DATE('July 4,2007','Month DD,YYYY')

8.逻辑判断
NVL(number,0) 如果number是null，返回0，如果不是null 返回number本身
NVL2(number,1,0) 如果number是null，返回1，如果不是null 返回0
NULLIF(name1,name2) 如果name1=name2 返回null，如果不相等返回name1
COALESCE(name1,name2,name3) 返回第一个非null值，如果name2非null，返回name2.
SELECT CASE name1 WHEN 'name2' THEN 2*name
           WHEN 'name3' THEN 3*name
    ELSE name END AS "ABCD" FROM dual; 如果name1==name2 返回2*name...
SELECT DECODE(name1,'AB',1*name,
  'CD',2*name,
  'EF',3*name,
  name) AS ABCD FROM dual; 和CASE用法一样，用name1和AB,CD,EF比较返回对应值，如果没有匹配，返回name
9.COUNT AVG ...等组函数，在计算时，丢弃NULL行
10.基本SQL执行顺序。SELECT>FROM>WHERE>GROUP BY>GROUP_FUNCTION>HAVING>ORDER BY
11.using 字句的字段，在整个sql文中，不加别名。
12.select employee_id,city,department_name from employees e
 join department d on e.department_id = d.department_id
 join location l on d.location_id = l.location_id;
13.同表join：select worker.lastname,manager.lastname from employees worker join employees manager on
  (worker.manager_id = manager.employee_id)
14.左left、右right(内inner)外outer联:
 select employee_id,city,department_name from employees e
  left/right/full outer join department d on e.department_id = d.department_id
  join location l on d.location_id = l.location_id;

 15.笛卡尔乘积(表一条数*表二条数的乘积ss):cross join :
select employee_id,department_name from employees e cross join department
select employee_id,department_name from employees department
16.操作多行子查询结果（in,any,all）：
select employee_idfrom employees e where e.salary = ANY(select salary from employees where job_id = 'IT_PROG')
select employee_idfrom employees e where e.salary < ALL(select salary from employees where job_id = 'IT_PROG')
17.四种集合操作符union/union all/intersect/minus
select employee_id,job_id from employees union select employee_id,job_id from job_history order by employee_id;     两表合并去不同 且排序
select employee_id,job_id from employees union all select employee_id,job_id from job_history order by employee_id; 两表全合并 且不排序
select employee_id,job_id from employees intersect select employee_id,job_id from job_history;       两表取交集
select employee_id,job_id from employees minus select employee_id,job_id from job_history;       两表取 表一但是要剔除表一表二相同数据的结果集
列不够 补全法（to_char/to_number）：
SQL> select location_id,department_name "D",to_char(null) "location" from departments
  2  union
  3  select location_id,to_char(null) "D",state_province "location" from locations;
18.copy一张表带数据
SQL> create table c_department as select * from departments;
19.插入另一张表的数据到目标表
SQL>insert into c_department select * from departments;
20.delete from a;     可以rollback
   truncate table a;  不可以。
21.rollback可以在 DML执行多条过程中加入 savepoint a，插入回滚节点。。。rollback to a。回滚到a节点。