7.25实习培训日志-Oracle SQL(一)

Oracle SQL(一)

重点

尽量避免select *，影响性能，不直观。

慎用Distinct，会排序，影响性能，用exists

排序尽量利用索引，索引有序

索引列不要加函数，会使索引失效

外连接：主表记录全显示，从表记录有则显示，无则留空

就用标准语法Count(*)

1. Count(*)/Count(1)全量
2. Count(字段)去Null值
3. Count(null)恒为0

Exists和In:理论上，In先执行子查询，然后执行外层主查询；而Exists属于关联子查询，因此，首先执行外层查询，然后再执行子查询，直到找到第一个符合条件的匹配项，就不会再执行了，理论上会快一些

数据字典

物化视图

分页：RowNum

索引：覆盖索引，范围索引,前缀索引,最左前缀原则

位图索引：性别只有男和女两项，用位图索引很快

函数索引：普通索引列上使用函数，会导致不走索引。除非建立函数索引；

两表关联，关联字段类型不一致，会有隐式的类型转换

select * from employees where employee_id='xxx'

UNION 去重，UNION ALL，不去重

INTERSECT 取交集， MINUS 取差集

Union去除重复行，并且含排序，因此，效率低下，需要尽量避免的

ROLLUP

闪回：删数据删表可以找回，删数据库则不行

docker oracle

拉取镜像

git pull registry.saas.hand-china.com/tools/oracle-xe-11g:16.04

运行镜像

docker run -d -p 1521:1521 name

创建用户

navicat上连接数据库(默认服务名:xe，默认用户：system,默认密码：oracle)

创建用户名

create user usename identified by password [default tablespace tablename];

创建表空间

create tablespace tablename datafile 'd:\data.dbf' size 20m;

将空间分配给用户

alter user username default tablespace tablename;

给用户授权（加引号）

grant resource,connect,dba to "username";

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

增加WITH ADMIN OPTION选项，则得到的权限可以传递。

Oracle SQL入门视频

Select

对于数值和日期型字段，可以进行 “加减乘除"

一个数值与NULL进行四则运算，其结果是 NULL

列别名时加双引号锁定大小写，不加则转换成大写

字符串连接操作符： “||”

SELECT last_name ||' is a '||job_id AS "Employee Details"

FROM employees;

条件和排序

select distinct department_id from employees;--去重

--范围查询前面的值必须不大于后面的值，否则查询不到结果。同时注意between and是包含9000和4400的(>= <=)

where e.salary between 9000 and 4400;--无结果

where e.salary between 4400  and 9000;

where e.salary between 4400  and 4400;--有结果

where e.salary >=4400 and e.salary <=9000;

--只包含90,60而不是90到60的范围

where e.department_id in (90,60);

where e.last_name like '_o%';--第二位是o，`_`是占用一位，`o`是多位

is not null;

% 代表 0 个或者多个 字符.

_ 代表一个单个字符

表中本身有通配符的怎么查询

使用ESCAPE 标识转义字符

--escape会定义转义字符

select * from t_char

where a like '%\%%' escape '\';

select * from t_char

where a like '%K%%' escape 'K';

排序，按照字段别名排序，按照多字段排序

ORDER BY department_id DESC, salary DESC;

单行函数

单行函数分为五种类型：字符函数、数值函数、日期函数、转换函数、通用函数

字符函数

大小写控制函数

oracle数据库大小写敏感

select * from dual where dummy='x' --无结果

select * from dual where lower(dummy)='x' --有结果

select lower('Hello World') 转小写, upper('Hello World') 转大写 from dual;

--initcap: 首字母大写

select initcap('hello world') 首字符大写 from dual;--别名

select initcap('hello world') as 首字符大写 from dual;--别名

字符串操作函数

--字符控制函数

-- concat: 字符连接函数, 等同于  ||

select concat('i am',' dengyouquan') from dual;

--从第三位开始截取全部

select substr('HelloWorld',3) from dual;

--从第3位开始截取4位 lloW

select substr('HelloWorld',3,4) from dual;

--length和lengthb: 字符数和字节数

select length('中国') 字符数, lengthb('中国') 字节数  from dual;

--instr:在母串中，查找子串的位置

select instr('HelloWorld','llo') from dual;

--lpad,rpad: 左右填充,将abcd用*填充到10位

select lpad('abcd',10,'*') 左填充, rpad('abcd',10,'*') 右填充 from dual;

--trim: 去掉字符串前后指定的字符 大小写敏感 HelloWorld

select trim('D' from 'DHelloWorld') from dual;

--去除前后空格

select trim(' Hello World') from dual;

--replace:字符串替换函数

select replace('HelloWorld','l','*') from dual;

数值函数

select round(1.234,2) 四舍五入, trunc(1.234,2)  截断 ,mod(1234,324) 求余 from dual;

--ROUND函数

select round(4311.234,0) 整数位, round(34211.234,-1) 十位,round(23411.234,-2) 百位 from dual;

日期函数

to_date("要转换的字符串","转换的格式") 两个参数的格式必须匹配:字符串->日期

to_char(日期,"转换格式" ) 即把给定的日期按照“转换格式”转换:日期->字符串

months_between	两个日期相差的月数
add_months	在指定日期上加上若干个月
last_day	指定日期的下一个日期
next_day	本月最后一天
round	日期四舍五入
trunc	日期截断

--显示当前日期

select sysdate from dual;

--显示昨天，今天和明天，加减数字仍未日期

select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;

--员工工龄

select sysdate-hire_date 天 from employees

--months_between:两个日期相差的月数

select (sysdate-hire_date)/30 方式一, months_between(sysdate,hire_date) 方式二 from employees;

--add_months:在指定日期上加上若干个月

select add_months(sysdate,1)  下个月 from dual

--last_day: 某个日期当月的最后一天

select last_day(sysdate) from dual;

--next_day:下周五

select next_day(sysdate,'星期五') from dual;

--对日期进行四舍五入

select round(sysdate,'MONTH')  月,round(sysdate,'YEAR') from dual;

--对日期进行截断

select trunc(sysdate,'MONTH')  月,trunc(sysdate,'YEAR') from dual;

--日期格式

select * from employees where hire_date=to_date('1982-01-23','yyyy-mm-dd');

-- 查询当前日期：显示:  2011-09-17 15:12:15今天是星期六

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

yy	two digits 两位年	显示值:07
yyy	three digits 三位年	显示值:007
yyyy	four digits 四位年	显示值:2007
mm	两位月	显示值:11
mon	abbreviated 字符集表示	显示值:11月,若是英文版,显示nov
month	spelled out 字符集表示	显示值:11月,若是英文版,显示november
dd	number 当月第几天	显示值:02
ddd	number 当年第几天	显示值:02
dy	abbreviated 当周第几天简写	显示值:星期五,若是英文版,显示fri
day	spelled out 当周第几天全写	显示值:星期五,若是英文版,显示friday
hh	two digits 12小时进制	显示值:01
hh24	two digits 24小时进制	显示值:13
mi	two digits 60进制	显示值:45
ss	two digits 60进制	显示值:25
Q	digit 季度	显示值:4
WW	digit 当年第几周	显示值:44
W	digit 当月第几周	显示值:1

通用函数

NVL (expr1, expr2)	如果expr1为空，这返回expr2
NVL2 (expr1, expr2, expr3)	如果expr1为空，这返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2)	如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, ..., exprn)	依次考察各参数表达式，遇到非 null 值即停止并返回该值。

--nvl(exp1,exp2):当exp1为空时，返回exp2

--nvl2(exp1,exp2,exp3):当exp1为空时，返回exp3；否则返回exp2

select ename,sal*12+nvl2(comm,comm,0) 年收入 from employees;

--NULLIF (expr1, expr2),如果expr1=expr2,返回null;否则,返回expr1

select nullif('abc','abc') from dual;

select nullif('abc','abcaa') from dual;

--COALESCE :找到参数列表中，第一个不为空的值

select ename,comm,sal,COALESCE(comm,sal) from employees;

转换函数

to_char(列	日期,格式)
日期 to_date(列	字符串,格式)
数字 to_number(列	字符串)

使用货币格式化显示。

--9代表占位符，如果位数少于前面的数字，都会显示#

--L代表本地货币符号

alter session set NLS_CURRENCY = ' ￥';

select to_char(4122341,'L9,999,999.00') from dual;

select to_number('$1,324','$9,999') from dual;--能转换成数字1324 后面长度要长于前面长度，否则有问题

当比较字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。

当比较字符型和日期型的数据时，oracle会把字符型隐式转换为日期型。

连接时(||)，一般是把数值型隐式转换为字符型,日期型隐式转换为字符型

数据类型优先级为：Number>字符类型>日期类型

隐式转换发生在字段列上时将使索引失效。

双引号的作用是：示意Oracle将严格区分大小写，否则Oracl都默认大写。

单引号：这个加了单引号的字段是一个字类似字符串，并不区分大小写。