Orace SQL基础-读书笔记

很久之前的笔记，今天整理一下，发出来。
1. 多列分组时：
select fk_deptid,gender,count(*) from forp_user group by rollup(fk_deptid,gender) order by fk_deptid
按照部门和性别分组后，并进行小计（横向小计）
select fk_deptid,gender,count(*) from forp_user group by cube(fk_deptid,gender) order by fk_deptid
不仅可以横向还可以纵向小计
2. 内连接：返回所有满足条件的记录数
方式：包含等值查询、natural join、using、innner join
ex1：select * from A inner join B on A.id = B.id
左外连接（left join）：
不仅返回满足连接条件的记录数，还可以返回那些不满足连接条件的操作符左边表的其他记录。
左外连接（right join）：
与左外连接相反。
全外连接（full join）：
除了返回满足条件的记录数，还会返回不满足条件的其他所有记录数。
3. 子查询
这种为主查询提供数据的而首先执行的查询语句叫做子查询。
单行子查询：
此时应该使用=，>=，<=，<，>,<>这种运算符
ex：select e.deptno,min(e.sal) min_sal from emp e group by deptno having min(e.sal) > (select min(sal) from emp where deptno = 30)
多行单列子查询：
此时应该使用in，all，any。其中all和any不能单独使用，需要配合单行的那些符号进行使用。
多行多列子查询：
如果多列子查询返回的是一行，则使用单行比较符（<.<=,>,>=,<>）
如果是多行，则使用（in,any,all）
ex：select empno,ename,job,sal,deptno from emp where (deptno,sal) in (select deptno,min(sal) from emp group by deptno)
其他子查询：
行内视图
exist
集合的操作：
union，union all 并集（不去重复|去重复）
intercept 获得两个集合的交集
minus获得两个集合的差集
4. 单行函数
字符：upper(char),lower(char),initcap(char)
字符串连接：concat(char1,char2) 其作用等同于 ||
字符串截取：substr(char,m,n)
ex:select substr(‘hello world’,1,5) from dual 注意数据库的小标从1开始，和java不一样。当然也可以使用0来代替。
字符串长度：length(char)
ex:select length(‘hello world’) from dual;
字符串位置：instr(char,str)
左右追加函数：lpad(char1,n,char2) rpad(char1,n,char2) 其中n表示补足到n位
select lpad(‘hello’,23,’world’) from dual
截取子函数串：trim(‘char1’ from ‘char2’) ltrim(‘char1’ from ‘char2’) rtrim(‘char1’ from ‘char2’) 表示从2中取出1的内容
替换函数：replace(char,seaching-str[,repl-str]) 用后面替换前面的，如果没有第三项，则默认使用空格替换
ex:select replace(‘hello world hehe huhu’,’hehe’,’jiji’) from dual
ex:select replace(‘hello world hehe huhu’,’hehe’) from dual
5. 数字函数
四舍五入：round(n[,m]) n->将被处理的数字，m->取四舍五入中小数点后几位
截取：trunc(n[,m]) n将被截取到m为，0,-1都代表不截取
ceil(n) -> 向上取整 floor(n) ->向下取整
abs(n) -> 取n的绝对值 mod(m,n) -> 返回m除以n后的余数 sign(n) ->正数返回1，负数返回-1
power(m,n) -> 返回m的n次幂 sqrt(n) -> 返回n的平方根
6. 日期函数
sysdate 显示当前日期
ex：select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual
add_months(date,i) 将当前日期加上i个月
ex：select add_months(sysdate,2) from dual
next_day(date,char) 返回下一个符合条件的日期
ex：select next_day(sysdate,4) from dual
last_day(date) -> 返回这个月的最后一天
ex：select last_day(sysdate) from dual
month_between(date1,date2) -> 计算两个日期差多少个月
ex：select months_between(to_date(‘20180702’,’yyyymmdd’),to_date(‘20180412’,’yyyymmdd’)) from dual
result：2.6…
select sessiontimezone from dual
7. 转换函数
to_char(date[,fmt[,nlsparams]]) 日期->字符
ex：select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) currenttime from dual;
ex：select to_char(sysdate,’yyyy”年”dd”月”mm”日”’) currenttime from dual;

to_date(char[,fmt[,nlsparams]]) 字符->日期
ex：select to_date(‘2019-01-01’,’yyyy-mm-dd’) from dual;

to_char(num[,fmt]) 数字->字符 显示千分位，补0，保留两位小数，显示正负号等
ex：select to_char(123456.789,’999,999.90’) from dual;

to_number(char[,fmt]) 字符->数字
ex：select to_number(‘7878.333’)from dual
8. null函数
coalesce(expr1[,expr2[,expr3…]])
nvl(expr1,expr2) -> expr1是null取expr2，否则取expr1
nvl2(expr1,expr2,expr3) -> expr1是null 返回expr3 ,不是null，返回expr2
nullif(expr1,expr2) -> 两个参数不相等，返回expr1；反之亦然
lnnvl(condition) -> 通常用在where子句中。
ex: where comm < 500 这样只能取到奖金小于500的职员；但是获取不到为null的值得员工，
where lnnvl(comm > 500) 这样就可以弥补上面的不足
select ename,comm from emp where lnnvl(comm > 500);
9. 比较函数
greatest(expr1,expr2,expr3…) -> 返回最大的值 比较值可以是：数字，日期，字符等
ex: select greatest(‘tom’,’jack’,’rose’) from dual;
least(expr1,expr2,expr3…) -> 返回最小值
ex：select least(1,22.33) from dual;

1. 环境和标识函数
   select sys_context(‘userenv’,’db_name’) from dual;
   select sys_context(‘userenv’,’language’) from dual;
   select sys_context(‘userenv’,’nls_date_format’) from dual;
   select user from user;

2. 其他函数
   decode()函数

   SELECT
   ename,
   DECODE (job,’MANAGER’,sal * 1.5,
   ‘ANALYST’,sal * 1.2,
   ‘SALESMAN’,sal * 1.1,
   sal
   ) as sal
   FROM
   emp
   等价于case语句===>
   SELECT
   ename,
   CASE job when ‘MANAGER’ THEN sal * 2
   when ‘ANALYST’ THEN sal * 1.5
   when ‘SALESMAN’ THEN sal * 1.1
   else sal end as sal
   FROM
   emp

   select vsize(‘hehe中’) from dual; ->实际占的字节数

3. 数据库DML语言
   insert,update,delete用法省略
   merge操作
   SQL->
   MERGE INTO A A USING B b ON (A . ID = b.aid)
   WHEN MATCHED THEN
   UPDATE
   SET A . YEAR = b. YEAR,
   A .city = b.city
   WHEN NOT MATCHED THEN
   INSERT (A . ID, A . YEAR, A .city)
   VALUES
   (b.aid, b. YEAR, b.city)

4. 数据库对象的创建和管理
   ①常用的数据类型
   char(n) [1,2000] 取值范围，不足补足空格
   varchar2(n) [1,4000]
   number(p,s) p是总位数（38），s是小数点后的位数
   blob 二进制数据的大对象类型，容量<=4G
   clob 字符数据的对象类型 容量<=4G
   ②数据库定义语言(DDL)
   包含create alter drop truncate grant等
   命名规则：
   对象名称长度最多为30个字符；对象名称不能有空格和*；名称必须以字母开头；不能使用服务器保留字；
   约束在的表：
   select * from user_constraints
   select * from all_constraints
   ③增加列：alter table mar_project add (city nvarchar2(200));
   修改列：alter table mar_src modify (location date);
   删除列：alter table mar_src drop(location);
   改列名：alter table mar_src rename column age to agesss;
   改表名：rename mar_src to mar_dest;
   截断表：truncate table mar_dest;(速度更快，占用更少系统资源和事务日志资源)
   删除表：drop table mar_dest;
   ④约束条件
   not null,unique,primary key,foreign key,check
   ⑤启用和禁用约束
   create table dept(
   did integer,
   name varchar2(200),
   code varchar2(200)
   )

   create table emp(
   eid integer,
   name varchar2(200),
   code varchar2(200),
   fk_did integer
   )
   alter table dept add constraint pk_dept primary key(did);
   alter table emp add constraint pk_emp primary key(eid);
   alter table emp add constraint fk_test_1 foreign key (fk_did) references dept(did);
   alter table emp disable constraint fk_test_1; 禁用约束
   alter table emp enable constraint fk_test_1; 启用约束
   alter table emp drop constraint fk_test_1; 删除外键约束
   ⑥视图(View)：虚表
   创建视图：
   CREATE OR REPLACE VIEW v_mar_project
   AS SELECT * FROM mar_project WITH READ ONLY；
   删除视图：
   DROP VIEW v_mar_project；
   在数据字典中查看视图的相关信息：
   select * from user_views
   select * from user_objects where object_type = ‘VIEW’
   ⑦索引
   有效减少IO次数，提交检索效率
   单列索引(单列)和复合索引(多列)
   唯一索引和非唯一索引
   创建索引的原则：
   1)为经常出现where子句的列创建索引
   2)为经常出现order by及distinct 后面的字段建立索引，如果是复合索引，索引的字段的顺序要和这些关键字后面的
   顺序一致，否则索引不生效
   3)将经常作为表连接条件的列上建立索引
   4)不要在经常进行DML操作的表上建立索引
   5)删除查询用不到的或很少使用的，不合理的，小表的索引
   命名规则：
   idx_tablename_columnname
   创建索引：
   CREATE INDEX idx_marProject_simplestockname ON mar_project (simplestockname); 单列索引
   CREATE INDEX idx_marProject_codeandname ON mar_project (stockcode, simplestockname);多列索引
   删除索引：
   DROP INDEX idx_marProject_simplestockname;
   重构索引：
   ALTER INDEX idx_marProject_simplestockname REBUILD;
   select * from user_ind_columns;
   select * from ind;
   ⑧同义词(synonym)

5. SQL优化
   1)避免Select *
   2)编写SQL尽量使用相同的编码风格。语句解析、语句执行、返回执行结果等几个步骤。共享的SGA区，如有省去解析sql
   3)使用truncate替换delete。delete属于DML语言。
   4)在确保业务逻辑前提下及时COMMIT事务
   5)in：子查询->主查询 exists：主查询->子查询。子查询的记录较少，主查询是记录较大且有索引使用in，反之亦然。
   not exists 的效率什么时候都高于not in
   6)使用exists替代distinct
   低效SQL:
   select distinct e.deptno,d.dname from dept d ,emp e where d.deptno = e.deptno
   高效SQL:
   select d.deptno,d.dname from dept d where exists(select * from emp e where e.deptno = d.deptno)
   7)使用union替换union all
   union->去重，排序;union all->简单的结果连接
   8)在保证功能的前提下减少对表的查询
   低效SQL:
   SELECT ename,JOB,sal,deptno FROM emp WHERE JOB = (SELECT JOB FROM emp WHERE ename = ‘SCOTT’)
   AND deptno = (SELECT deptno FROM emp WHERE ename = ‘SCOTT’);
   高效SQL:
   SELECT ename,JOB,sal,deptno FROM emp WHERE (JOB,deptno) = (SELECT JOB,deptno FROM emp WHERE ename = ‘SCOTT’);
   9)尽量使用表的别名(Alias),并在列前标注来自哪个表
   低效SQL:
   select ename,job,sal,EMP.deptno from emp,dept where EMP.deptno = dept.deptno;
   高效SQL:
   select e.ename,e.job,e.sal,e.deptno from emp e,dept d where e.deptno = d.deptno;
   10)不要使用having子句实现where子句的功能
   带分组的SQL，尽可能地把筛选条件放到where子句中进行筛选，having用来对分组过的结果进行过滤
   11)在表连接中的优化原则
   *识别驱动表，将数据量最小的表作为驱动表
   *如果是多个连接条件查询，将过滤掉最多纪录的条件放到最前面(也就是where后面有多个条件)
   12)合理使用索引
   *避免全表扫描：没有索引，没有where条件，查询条件的列没有索引，查询条件中对索引列使用了函数或算数表达式
   ex： where upper(job) = ‘SALEMAN’ ->使用函数
   where not in(‘CLARK’,’SALEMAN’) ->条件中not in
   where job is not null ->条件中有is null，is not null,<>,!=
   where job like ‘%SALEMAN%’
   *尽量避免使用like操作符
   *避免对大表查询中的列使用单行函数或算数表达式
   -> where trunc(birthday,’YEAR’) = ‘1998’
   优化为：
   where birthday >= ‘1998-01-01’
   and birthday <= ‘1998-12-31’
   -> where birthday + 30 = sysdate 避免在索引列上进行计算
   优化为：；
   where birthday = sysdate + 30;
   -> where productId = 12345678; 进行隐式数据类型转换也会使索引失效
   优化为：
   where productId = ‘12345678’
   *对于order by 语句中的列上如果没有索引会降低性能，建议在经常排序的列上添加索引，并且避免在这些列上使用函数表示式