Orace SQL基础-读书笔记

约定不等于承诺〃 2022-05-22 11:19 137阅读 0赞

很久之前的笔记，今天整理一下，发出来。  
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 语句中的列上如果没有索引会降低性能，建议在经常排序的列上添加索引，并且避免在这些列上使用函数表示式