oracle学习笔记（基础版）

一、 Oracle支持表达式 包括：+ - * /   
 dual(虚表，用于简单的输出实验用)
1.SELECT 5+3,5-3,5*3,5/2  FROM dual;


2.SELECT 'hello,world',100 FROM dual; 


3.查员工编号，姓名，工资，新工资（=原始工资上浮25%)  (列值可以直接加减运算，产生的是临时结果)
    SELECT employee_id,last_name,salary,salary*1.25
    FROM    employees; 


 4.查员工编号，姓名，工资，12月总工资（=原始工资+100)*12  
    SELECT employee_id,last_name,salary,(salary+100)*12
    FROM    employees; 


Oracle数据库中，空值是无效的，未指定的，未知的或不可预知的值
空值不是空格或者0
在Oracle中，null和空字符串是等价的 
包含空值的数学表达式的值都为空值
5.SELECT 5+NULL FROM dual;查询结果为空 


6.列的别名(用于衍生列) 有两种方式 一种是 AS； 一种是加空格
    如果别名要是非法标识符，可以使用双引号


7.拼接字符串 ( || )
    SELECT 'hello' || 'world' FROM dual;                                 helloworld
    SELECT 'hello' || 123 FROM dual;                                      hello123
    SELECT 123  ||  123 FROM dual;                                        123123
    SELECT '200' + '100' FROM dual;                                       300 
    SELECT first_name || '·' ||  last_name  as ename
    FROM employees;    查询得到1列 格式为 姓·名；


8.多个重复行并成一行
    SELECT DISTINCT department_id FROM employees;


9.通过dual生成订单编号（getorderno('') 引号中为订单类型 可以为空 但必须加引号）
select getorderno('apple') from dual 
    
二、Oracle常用数据类型
 1.字符型：varchar2(n):变长字符串，n代表允许的最大字节长度，最大4000字节
                   char(n):同上，定长字符串，最大2000字节，长度不够会填充半角空格，
                                查询效率高 用于（手机号、身份证号等确定长度的）
                clob:大字符串数据，最大4G，默认数据在4000字节内，存储在表段空间中，超过4000字节会用LOB段存储，查询效率低
2.数字类型：number:存储整型或浮点型，最大38位精度
                    number(n):仅存整数，n代表最大位数 number(4) 取值范围： -9999~9999
                    number(p,s):存浮点类型，P代表最大精度（小数位精度和整数精度和的精度），s代表小数位
3.日期：date：存储年月日时分秒，精确到秒
                timestamp(n) 时间戳，精确到纳秒 很少用
4.blob :大二进制数据，最大4G 可以存图片、视频、音乐等等              
 
 
 三，Oracle过滤语句 where
1.加了where，数据查询出来就做了比较，所以比不带where的效率要低


2.查询工资超过10000的员工
SELECT * FROM  employees WHERE salary >= 90    


3.操作符 
= 
>
>=
<
<=
<> 不等于 （其实！=也可以用）如果值为空值也查不出来


 4. 如果需要查询无条件为真 可以用
SELECT * FROM employees WHERE 1==1；
   无条件为假 
SELECT * FROM employees WHERE 1==0；


5.查询员工编号，姓名，工资，新工资（只看新工资超过10000的员工）
SELECT employee_id,last_name,salary,salary*1.25 AS new_sal
WHERE salary*1.25 >=10000;
值得注意的是WHERE后面不能用别名 


6.日期类型比较,日期格式敏感，默认的日期格式是DD-MON-RR，没有常量。
   查询所有在90年以前入职的员工
SELECT * FROM employees WHERE hire_date < '1-1月-90';
有中文，在其他平台会出问题
 
7.特殊比较运算符
BETWEEN...AND... 在两个值之间 包含边界 可以是日期类型（查不出来空值）
IN(set)     等于值列表中的一个（查不出来空值）
LIKE        模糊查询
IS NULL     空值 


查询所有有部门的员工
SELECT last_name ， manager_id FROM employees WHERE dapartment_id IS NOT NULL;
 
查询工资在5000-10000之间的员工
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000 ;（有边界）


查询工资不在5000-10000之间的员工
SELECT * FROM employees WHERE salary NOT BETWEEN 5000 AND 10000 ;（没有边界）


查询部门不是10,20，50号员工
SELECT * FROM employees WHERE department_id NOT IN (10,20,50);
注意，如果IN后面的值有个为NULL，那么所有数据都查不出来。

四、在oracle中模糊查询的关键字是like

1.其中‘%’代表含有0到多个 ‘_’代表占一个位
如果查询中要查询倒数第5个是下划线的可以使用ESCAPE关键字
其中'\'是自定义的，可以是任何符号 
SELECT * FROM employee WHERE job_id LIKE '%\_____' ESCAPE '\';
其中后四个'_'占四个位置，倒数第五个'_'代表它本来的意思'_';


2.查询工资超过5000且last_name以s结尾的员工
SELECT * 
FROM employees
WHERE salary>=5000
AND last_name LIKE '%s';
 
3.查询部门是10,20，以及没有部门的员工
SELECT * 
FROM employees
WHERE department IN (10,20) 
OR department IS NULL; 


4.单引号转义 如果查询带单引号的字符串时 ， 要多加一个单引号对其单引号进行转义
例如查询 HELLO'WORLD ：
SELECT 'HELLO''WORLD' FROM dual; 
数据库中单引号要做处理 ，防止别人SQL注入


5.查询员工编号，姓名，工资，新工资，部门编号，按工资升序排列
SELECT employee_id,last_name,salary,salary*1.25 new_sal
FROM employees
ORDER BY salary;
  
6.查询员工编号，姓名，工资，新工资，部门编号，按工资降序排列
SELECT employee_id,last_name,salary,salary*1.25 new_sal
FROM employees
ORDER BY salary DESC; 


 7.排序可以是别名
SELECT employee_id,last_name,salary,salary*1.25 new_sal
FROM employees
ORDER BY new_sal DESC;  


8.排序可以是表达式
 SELECT employee_id,last_name,salary,salary*1.25 new_sal
FROM employees
ORDER BY salary*1.25 DESC;  


9.排序可以是列索引,2代表第二列 也就是说按 last_name这一列排序
SELECT employee_id,last_name,salary,salary*1.25 new_sal
FROM employees
ORDER BY 2 DESC;   


10.查询员工编号，姓名，工资，入职日期，部门编号(多排序表达式)
    按部门升序，工资降序，入职日期升序
SELECT employee_id,last_name,salary,hire_date,department_id
FROM     employees 
ORDER BY  department_id,salary DESC,hire_date;


11.排序表达式可以不是列列表中的列
SELECT employee_id,last_name,salary,hire_date,department_id
FROM     employees 
ORDER BY  job_id;
 
12.查询50号部门的员工，按工资降序
子句是有顺序的，先过滤，后排序
SELECT * 
FROM     employees
WHERE     department_id = 50
ORDER BY salary DESC; 


13.大数据排序的问题（非常耗费资源，容易造成服务器死机）
排序就是两两比较，排序默认产生的临时数据放到排序区的内存中，
如果排序区不够用，就会利用临时表空间（排序区默认128K很小）
大排序之前一定要做数据库优化 （怎么优化目前不太清楚。。继续学习中！）

 rownum和rowid是三大伪列中的其中两个，是最容易搞混的两个
伪列和虚表差不多，看不到，却可以使用


五、rownum伪列

1.SELECT rownum,employee_id,last_name,salary
FROM    employees 
WHERE department_id = 50;
查询结果是多了一列名为ROWNUM的列，这一列数据从1开始一直递增。


2.查询某表中的前5条数据(有其他字段的时候查询所有列要在前面加表名.*)
SELECT rownum,employees.* 
FROM employees
WHERE rownum<=5;


3.
rownum在以下条件一定为假，查不出来数据
rownum>n
rownum>=n
rownum=1以外的值 


4.rowid伪列
表数据行的物理地址
在插入数据时生成
在数据库汇总是唯一的 
SELECT ROWID,employees.* 
FROM employees


六、函数（和带返回值的方法一样）分为两类 
单行函数 和 多行函数
1.单行函数 upper() 将小写变成大写 传入一行
用于 字符 通用 转换 日期 数值
SELECT last_name,upper(last_name) 
FROM employees;


2.多行函数 传入多行 得到一个结果
SELECT count(last_name)
FROM enployees; 


3.单行函数-字符函数 
    lower,upper:转换大小写
SELECT lower(last_name),upper(last_name)
FROM    employees;
 
4.initcap:单词首字母大写，其余小写
SELECT initcap('This IS a book') 
FROM dual ;
查询结果 This Is A Book


5. concat(字符串1，字符串2)：拼接字符串
SELECT concat('hello','world')
FROM dual;
查询结果 helloworld 


6.substr(字符串，起始位置，截取个数) ：截取一个字符的子串，起始位置可以是负数（右数第N位）
 SELECT substr('abcdefg',3) FROM dual;   结果：cdefg
 SELECT substr('abcdefg',3,2) FROM dual;   结果：cd
 SELECT substr('abcdefg',-3) FROM dual;   结果：efg
SELECT substr('abcdefg',-3,2) FROM dual;   结果：ef 


7.length:返回字符串长度
SELECT length('abcd') FROM dual;  结果：4
SELECT length('') FROM dual;          结果：null(不返回0)


8.instr(字符串，查找子串[,起始位置[,第几次出现]]);
查找字符串中子串的起始位置，如果找不到返回0
起始位置可以为负数（从右向左反向搜索） 
SELECT inset('abcdefg','cd') FROM dual;   结果：3
SELECT inset('abcdefg','cdf') FROM dual;   结果：0
SELECT inset('abcdefgcdefg','cd') FROM dual;   结果：3  
SELECT inset('abcdefgcdefg','cd',4) FROM dual;   结果：8
SELECT inset('abcdefgcdefg','cd',-1) FROM dual;   结果：8 
SELECT inset('abcdefgcdefg','cd',1,2) FROM dual;   结果：8   


9.lpad(字符串，固定长度，填充字符)：左填充
    rpad：同上，右填充
SELECT lpad('abcd',7,'#') FROM dual; 结果：###abcd
SELECT lpad('abcd',3,'#') FROM dual;结果：abc  
SELECT rpad('abcd',7,'#') FROM dual;结果：abcd###
SELECT lpad('',7.'*') FROM dual;         结果：null 给空字符串填充结果还是null
SELECT lpad(' ',7 ,'*') FROM dual;        结果： ******空格占一个位
SELECT lpad('abcd',0,9,'*') FROM dual;结果：null 0.9算0 ，1.9算1   


10.trim(关键字 from 字符串)： 修建字符串两边的关键字
SELECT trim('a' FROM 'aabcdaaxyza') FROM dual; 结果 bcdaaxyz(两边的a没了)


SELECT 123||ltrim('   abcd   ') ||456 FROM dual; 结果：123abcd   456(修剪左边空格)
SELECT 123||rtrim('   abcd   ') ||456 FROM dual; 结果：123   abcd456(修剪右边空格)


SELECT rtrim('aabcdaaxyza','a') FROM dual; 结果：aabcdaaxyz(修剪右边a)
SELECT ltrim('aabcdaaxyza','a') FROM dual; 结果：bcdaaxyza(修剪左边a) 


11.replace(字符串，查找字符串[,替换字符串])
替换字符串中的子串，默认替换为空的字符串
SELECT replace('abcdefgabcd','cd') FROM dual; 结果：abefgab
SELECT replace('abcdefgabcd','cd','#') FROM dual; 结果：ab#efgab#


12.chr:把编码转化字符
SELECT chr(65) FROM dual; 结果 A


13.ascii:把字符转换为编码
SELECT ascii('A') FROM dual; 结果：65
SELECT ascii('国') FROM dual;结果：47610 
 
14.查询员工姓和名字数相等的员工
SELECT * 
FROM    employees
WHERE     length(first_name) = length(last_name); 


15.查询last_name以s结尾的员工（不用like）
SELECT * 
FROM  employees
WHERE substr(last_name,-1) = 's';


16.查询所偶遇员工姓和名，输出以下格式s.king
SELECT substr(first_name,1,1)||'.'||last_name
FROM employees ;


17.查询所有的电话号码，把分隔符“.”换成“-”之后再输出
SELECT replace(phone_number,'.','-')
FROM employees ;


18.用户输入一个任意编号，查询此编号的员工
(&后面的input是变量，input可以改成任意值，执行此SQL语句时Oracle会提示你输入一个值)
SELECT * 
FROM employees
WHERE employee_id = &input; 


19.用户输入一个关键字，查询last_name包含此关键字的员工（不用like）
 SELECT * 
FROM employees
WHERE instr(last_name,'&input')>0; 
用这个方法好处是用户查询带%的数据不用转义

七、常用的数学函数和日期函数

1.round:（数字【，小数位数】）:按照指定小数位数，四舍五入，默认到整数位
SELECT round(3.1415927) FROM dual;   结果--3
SELECT round(3.5415927) FROM dual;   结果--4
SELECT round(3.5415927,2) FROM dual;   结果--3.54 
 
2.trunc:(数字【，小数位数】):截断到指定位数，不四舍五入，默认保留到整数位
SELECT trunc(3.1415927) FROM dual;  结果：3
SELECT trunc(3.5415927) FROM dual;  结果：3
SELECT trunc(3.5415927,2) FROM dual; 结果3.54
SELECT trunc(3.5) FROM dual; 结果：3


3.floor:返回不大于本身的最大整数 
SELECT floor(-3.5) FROM dual; 结果-4


4.ceil（数字）:进位取整
SELECT ceil(3.000001) FROM dual; 结果：4
SELECT ceil(3.0) FROM dual;   结果：3 


5.mod:(被除数，除数)：求模
SELECT mod(5,3) FROM dual;   结果：2


6.sysdate:返回当前系统的日期时间
SELECT sysdate FROM dual; 


7.日期类型和数字类型可以做加减运算：一个日期加减一个数字返回的还是一个日期（单位是天）
SELECT sysdate+3 FROM dual;
SELECT sysdate-100 FROM dual;
SELECT sysdate+1/24/60*25 FROM dual;         +25分钟 


8.一个日期减去另外一个日期返回的是两个日期间隔的天数
日期加减会有小数，可以用数学函数进行截断
SELECT hire_date,trunc(sysdate-hire_date) AS 间隔天数 FROM    employees;


9.months_between(日期1，日期2):返回两个日期间隔多少月 hire_date为入职日期
查询每个员工的编号，姓名，入职日期，工龄
SELECT employee_id,last_name,hire_date,trunc(months_between(sysdate,hire_date)/12) 工龄
FROM employees ;


10.add_months(日期，N)：给一个日期加减若干个月，返回一个新日期
N为正数是加，为负数是减
SELECT add_months(sysdate,-15) FROM dual; 


11.查询入职日期超过20年的员工信息（months_between和add_months两种方式）
 SELECT *
FROM employees 
WHERE trunc(months_between(sysdate,hire_date)/12)>=20;


  SELECT *
FROM employees 
WHERE add_months(hire_date,20 * 12)<=sysdate;


12.next_day(日期，星期几)：返回以指定日期为准，一个最近的星期几的日期
SELECT next_day(sysdate,'星期五') FROM dual;
可以用数字1-7代表日—六  1代表星期日
SELECT next_day(sysdate,6) FROM dual; 下周五


13. last_day(日期)：返回指定日期的月最后一天的日期
SELECT last_day(sysdate) FROM dual;


14.round(日期【，日期单位】):对日期进行四舍五入 从12点开始
SELECT round(sysdate) FROM dual; 
SELECT round(sysdate,'month') FROM dual; 超过半月就是下一个月
SELECT round(sysdate,'year') FROM dual ; 超过半年就是下一年


15.trunc(日期【，日期单位】):对日期进行截断
SELECT trunc(sysdate) FROM dual;    返回当天日期 月分秒都会被舍去
SELECT trunc(sysdate,'month') FROM dual ; 返回月初日期
 SELECT trunc(sysdate,'year') FROM dual ; 返回年初日期

oracle转换函数主要转换3种类型，日期，数字，字符串
分隐式和显示转换 日期和数字都可以自由转化字符串
但是日期不能转换为数字


16.SELECT '100' + '50' FROM dual;  结果：150


17.SELECT * 
FROM employees 
WHERE hire_date<='1-1月-90'; 字符类型自动转化成日期类型 


18.SELECT 100||'hello' FROM dual; 结果100hello 转化成字符类型


19.SELECT ’现在的时间：‘||sysdate FROM dual; 查询结果为 现在时间：10-10月-16


20.显示转化 3个函数
TO_NUMBER
TO_DATE
TO_CHAR


21.to_char(日期|数字,'模式'):把一个日期或者数字按照指定模式转化为字符串
 SELECT '现在时间:'||sysdate FROM dual; 结果：现在时间:10-OCT-16
 SELECT '现在时间:'||to_char(sysdate,'yyyy-mm-dd') FROM dual; 结果：现在时间:2016-10-10
 SELECT '现在时间:'||to_char(sysdate,'dd/mm/yyyy') FROM dual; 结果：现在时间:10/10/2016
 SELECT '现在时间:'||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') FROM dual; 结果：时间:10/10/2016 21:26:22
 SELECT '现在时间:'||to_char(sysdate,'dd/mm/yyyy day') FROM dual; 结果：现在时间:10/10/2016 monday
  SELECT '现在时间:'||to_char(sysdate,'dd/mm/yyyy d') FROM dual; 结果：现在时间:10/10/2016 2 (2代表星期一)
 SELECT '现在时间:'||to_char(sysdate,'year-month-ddspth day') FROM dual; 结果：现在时间:twenty sixteen-october  -tenth monday  
（单词性质的日期）
  SELECT '现在时间:'||to_char(sysdate,'yyyy"年"mm"月"dd"日"') FROM dual; 结果：现在时间:2016年10月10日 注意：要双引号转义！
 SELECT '现在时间:'||to_char(ADD_MONTHS(sysdate,-1),'fmyyyy-mm-dd') FROM dual 
结果：现在时间:2016-9-10 fm去除月或者日前面的0.


SELECT '数字：'|| to_char(&input,'fm9990.99') FROM dual; 最大4位整数，两位小数，个数位必须有一个数字
SELECT '数字：'|| to_char(&input,'fm9990.0099') FROM dual; 最大4位整数，四位小数，个数位必须有一个数字，小数位有两位小数
SELECT '数字：'|| to_char(&input,'fmL9990.0099') FROM dual; L为本地货币符号
SELECT '数字：'|| to_char(&input,'fmL9,990.0099') FROM dual;多一个千分为 ：1.001.0052


22.查询17号入职的员工
SELECT * 
FROM employees
WHERE to_char(hire_date , 'dd') = '17';


23,查询7，8月份入职的员工
SELECT * 
FROM employees
WHERE to_char(hire_date,'mm') IN (7,8);  


24.to_date(日期字符串，’模式‘)：把日期字符串按一定模式解析为一个日期类型
查询95年以前入职员工
SELECT * 
FROM employees
WHERE hire_date<=to_date('1995-1-1','yyyy-mm-dd');


25,计算世界末日之后过了多少天
SELECT sysdate - to_date('2012-12-21','yyyy-mm-dd') FROM dual; 


26.to_number（数字字符串，’模式‘）：把一个字符串解析为一个数字类型
SELECT * 
FROM employees
WHERE salary>to_number('$5,600','$9,999');


SELECT * 
FROM employees
WHERE salary>to_number(￥5,600','￥9,999'); 

 八、通用函数，适合所有数据类型
1.nvl(参数1，参数2)：如果参数1不为空，返回参数1，如果为空，返回参数2
SELECT nvl(1,2) FROM dual;  结果1
SELECT nvl(null,2) FROM dual; 结果2


2.nvl2(参数1，参数2，参数3)：如果参数1不为空，返回参数2，如果参数1为空，返回参数3
SELECT nvl2(1,2,3) FROM dual;  结果：2
SELECT nvl2(null,2,3)    FROM dual;结果：3


3.nullif(参数1，参数2)： 参数1不等于参数2，返回参数1，如果相等，返回空
SELECT nullif(1,2) FROM dual;         结果：1
SELECT nullif(1,2) FROM dual;        结果：NULL 


4.coalesce(参数1，参数2，参数3...) ：返回第一个非空值，如果都为空，则返回空
SELECT coalesce(1,2,3,4,5) FROM dual;  结果：1
SELECT coalesce(NULL,NULL,3,4,5) FROM dual;


5.查询员工编号，姓名，工资，奖金金额，实发工资（工资+奖金）
(奖金有可能是空 直接加NULL结果是空 还有运算时不能用别名)
SELECT employee_id , last_name , salary , salary*nvl(commission_pct , 0) AS comm,
                salary+salary*nvl(commission_pct , 0)  AS money
FROM employees; 
 
6.查询10号 ， 20号 和没有部门的员工（不用is null ,is not null实现）
SELECT * 
FROM employees
WHERE nvl(department_id,-1) IN (10,20,-1);


7. 用case表达式做等值判断


case 表达式
    when 值1 then 返回值1
    when 值2 then 返回值2 
    ... ...
    CASE department_id
            WHEN 90 THEN 'NEC'
            WHEN 50 THEN 'HSW'
            WHEN 60 THEN 'USO'
            WHEN 80 THEN 'NEC'
    [else 默认返回值]
end


查询员工编号，姓名，工资，部门编号，部门名称
部门编号：
90        HEC
50        HSW
60        USO
80        NEC
其他     ICSS
SELECT employee_id,last_name,salary,
                department_id,
                CASE department_id
                    WHEN 90 THEN 'NEC'
                    WHEN 50 THEN 'HSW'
                    WHEN 60 THEN 'USO'
                    WHEN 80 THEN 'NEC'
                    ELSE 'ICSS'
                END    AS department_name 
FROM employees
 
--查询员工编号，姓名，工资，工资级别，部门编号
工资级别：
>=17000    A
>=10000    B
>=5000      C
其他           D
SELECT employee_id , last_name,salary,
        CASE
                    WHEN salary>=17000    THEN    'A'
                    WHEN salary>=10000     THEN    'B'
                    WHEN  salary>=5000        THEN  'C' 
                    ELSE    'D'
            END AS 工资级别
            department_id
FROM    employees; 


8.decode（表达式，值1，返回值1，值2，返回值2，....【，默认返回值】）做等值判断
 查询员工编号，姓名，工资，部门编号，部门名称
部门编号：
90        HEC
50        HSW
60        USO
80        NEC
其他     ICSS
SELECT employee_id,last_name,salary,department_id,
                decode(department_id,90,'NEC',50,'HSW',60,'USO',80,'GE','ICSS') AS department_name
FROM employees

RDBMS:关系型数据库管理系统
表和表之间有引用关系，可以减少数据冗余，方便后期维护


九、关系表的概念名词
主表：被从表引用的表 有主键 唯一 不重复 不为空
从表：引用其他表的表 有外键 外键允许重复 允许为空 必须是主表中存在的值


数据库中三大关系：
一对多    一条记录匹配多条记录（最常见的）
一对一    一条记录匹配一条记录
多对多    一个表的多条记录匹配另外一个表的多条记录（间接形成 用户权限会用到）


关系表三范式（可以违反提高效率，主要看需求）
表不可以分割，表要有主键，表只引用其它表的主键


1.查询员工编号，姓名，部门名称(SQL1992)(笛卡尔集)
SELECT employee_id , last_name,department_name
FROM employees,departments;
笛卡尔集产生条件：
省略连接条件
连接条件无效
所有表中的所有行互相连接


2.查询员工编号，姓名，部门名称
SELECT employee_id , last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
这样写得话有一个缺点 ， 如果部门值为空的就查不出来 
连接n个表，至少需要n-1个连接条件 起别名要用空格不能用AS
连接表查询，首先要弄清表关系！


3.查询所有部门的编号，部门名称，部门经理ID，部门经理名称,部门所在城市及地区
SELECT  d.department_id,
                d.department_name,
                e.employee_id,
                e.last_name,
                l.city,
                c.country_name,
                r.region_name
FROM     departments d,
                employees e,
                locations l,
                countyies c,
                regions r
WHERE   d.manager_id=e.employee_id 
 AND        d.location_id=l.location_id
 AND        l.country_id=c.country_id
AND         c.region_id=r.region_id;


 4.查看员工职务变更历史记录：（表多的话两个两个来）
员工编号，姓名，起始日期，终止日期，职务名称，部门名称
SELECT      e.employ_id,
                    e.last_name,
                    h.start_date,
                    h.end_date,
                    j.job_title,
                    d.department_name
FROM         employees e,
                    job_history h,
                    jobs j,
                    department d
WHERE       e.employee_id=h.employee_id 
AND             h.job_id=j.job_id
AND             h.department_id=d.department_id 


5.非等值连接(级别不存在相交，蛮少用)
查询每个员工编号，姓名，工资，工资级别
SELECT          e,employee_id,
                        e.employee_name,
                        e.salary,
                        g.grade_level
FROM             employee e,
                        job_grades g
 WHERE          e.salary BETWEEN g.lowest_sal  AND g.highest_sal
ORDER BY      e.employee_id 


 6.内连接：查询仅满足连接条件的（连接查询容易漏掉NULL值的条件）
    外连接：不仅返回满足连接条件的记录，不满足连接条件的也返回 返回空值
查询员工编号，姓名，部门名称(外连接，没有部门的员工也返回)
SELECT     e.employee_id , 
                   e.last_name,
                   d.department_name
FROM        employees e,
                  departments d
WHERE      e.department_id=d.department_id(+); 


查询员工编号，姓名，部门名称(外连接，没有员工的部门也返回) 
SELECT     e.employee_id , 
                   e.last_name,
                   d.department_name
FROM        employees e,
                  departments d
WHERE      e.department_id(+)=d.department_id;  


7.查询每个部门的编号，部门名称，部门经理ID，部门经理姓名 (没有部门经理的部门也返回)
SELECT      d.department_id,
                    d.department_name,
                    e.employee_id,
                    e.employee_name
FROM         departments d,
                    employees  e
WHERE       e.manager_id=e.employee_id(+);


8.自连接查询(把它当作两个表)
查询员工编号，姓名，员工管理者编号，员工管理者姓名
SELECT        e.employee_id,
                      e.last_name,
                      m.employee_id MGR_ID,
                      m.last_name MGR_NAME
FROM            employees e,
                       employees m
WHERE          e.manager_id=m.employee_id(+);


9.查询谁的工资比Abel高
SELECT     e1.employee_id,
                  e1.last_name,
                  e1.salary, 
                  e2.last_name,
                  e2.salary  
FROM        employees e1,
                   employees e2
WHERE       e1.salary>e2.salary
AND             e2.last_name='Abel' 

十、SQL1999语法(效率比SQL1992的效率要高)
SELECT        table1.column , table2.column
FROM           table1
[CROSS JOIN table2]    |
[NATURAL JOIN table2]    |
[JOIN table2 USING (column_name)]    |
[JOIN table2
    ON(table1.column_name = table2.column_name)]    |
[LEFT|RIGHT|FULL OUTER JOIN table2
    ON (table1.column_name = table2.column_name)];


1.SQL1999连接语法
查询员工编号，姓名，部门编号，部门名称（叉表 查询笛卡尔集 没啥用）
SELECT     e.employee_id,
                   e.last_name,
                   d.department_id,
                   d.department_name
FROM        employees e
CROSS    JOIN    departments d


2.内连接：INNER JOIN
   外连接：
   左外连接    LEFT  OUTER JOIN
   右外连接    RIGHT OUTER JOIN
    满外连接    FULL OUTER JOIN


3.查询员工编号，姓名，部门编号，部门名称,职务编号，职务名称（内连接 查不出来空值）
SELECT     e.employee_id,
                   e.last_name,
                   d.department_id,
                   d.department_name,
                   j.job_id,
                   j.job_title 
FROM        employees e
INNER    JOIN    departments d  ON  e.department_id=d.department_id
INNER    JOIN    jobs j     ON      e.job_id=j.job_id
WHERE     e,salary>=5000
ORDER BY e,salary DESC;


4. 查询员工编号，姓名，部门编号，部门名称（左外连接 LEFT OUTER左边不满足连接条件的也返回 返回employees数据）
SELECT     e.employee_id,
                   e.last_name,
                   d.department_id,
                   d.department_name, 
FROM        employees e  LEFT OUTER JOIN departments d  ON  e.department_id=d.department_id;


5. 查询员工编号，姓名，部门编号，部门名称（右外连接 RIGHT OUTER右边不满足连接条件的也返回 返回departments数据）
SELECT     e.employee_id,
                   e.last_name,
                   d.department_id,
                   d.department_name, 
FROM        employees e  RIGHT OUTER JOIN departments d  ON  e.department_id=d.department_id; 


6. 查询员工编号，姓名，部门编号，部门名称（满外连接 部门为空的员工和员工为空的部门都返回）
SELECT     e.employee_id,
                   e.last_name,
                   d.department_id,
                   d.department_name, 
FROM        employees e  FULL OUTER JOIN departments d  ON  e.department_id=d.department_id; 

十一、组函数及分析函数

1.组函数作用于一组数据，并对一组数据返回一个值。
 查询所有工资的总和
SELECT SUM(salary) FROM employees;


2.组函数(如果是0行数据，count返回0 其他返回null)
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary)
FROM employees
WHERE department_id=50; 


3.所有组函数都是忽略空值
SELECT count(commission_pct) FROM employees;
 
4.查询所有没有奖金的人数
SELECT COUNT(*)-COUNT(commission_pct) FROM employees; 


5.查询部门总数(统计不重复的计数)
SELECT COUNT(DISTINCT department_id) FROM employees;


6.查询每个部门的ID，员工工资总和,最高工资(三大子句同时使用)
SELECT department_id,
               SUM(salary) SAL_SUM,
               MAX(salary)
FROM    employees
WHERE department_id IS NOT NULL
GROUP BY department_id 
ORDER BY sal_sum DESC;
 
7. 多个分组表达式(两个字段完全相同分成一组)
SELECT department_id,
               job_id,
               SUM(salary)
FROM     employees
GROUP BY department_id,job_id;


8.查询每个部门的名称.人数
SELECT    d.department_name , count(e.employee_id) emp_count
FROM        employees e
INNER　JOIN departments d ON e.department_id=d.department_id
GROUP BY d.department_name;


9.统计每年入职的人数：年份，人数
SELECT to_char(hire_date,'yyyy') 年份,count(*) 人数
FROM    employees
GROUP BY to_char(hire_date,'yyyy') 
ORDER BY 1;


10 .统计每年入职的人数：年份，人数(仅返回不少于2人的年份的数据)
用HAVING还是用WHERE 主要看需要的过滤的结果是分组之前的，还是分组之后的
SELECT to_char(hire_date,'yyyy') 年份,count(*) 人数
FROM    employees
GROUP BY to_char(hire_date,'yyyy')
HAVING COUNT(*)>=2 
ORDER BY 1;


11.分析函数
 over函数连续求和
 over1列数据是根据员工id进行累加求和(每一行结果是上行累加工资总和加这行工资)
 over2总工资
SELECT     employee_id,
                   salary,
                   department_id,
                   SUM(salary) over(ORDER BY employee_id)  over1,
                   SUM(salary) over() over2
FROM         employees; 


12.连续求和 分组求和 将相同部门工资求一个总和 （相同组结果一样）
 SELECT     employee_id,
                   salary,
                   department_id,
                   SUM(salary) over(PARTITION BY department_id)  over1,
FROM         employees; 


13. 连续求和 over1和12一样 over2是分组之后累加（相同组结果一样）
     over3将相同部门进行累计求和 
SELECT     employee_id,
                   salary,
                   department_id,
                   SUM(salary) over(PARTITION BY department_id)  over1,
                   SUM(salary) over(ORDER BY employee_id)  over2,
                   SUM(salary) over(PARTITION BY department_id ORDER BY employee_id)  over3 
FROM         employees; 


14.按照部门编号做排名
row_number()通过部门ID排序做累加 从1开始 1,2,3,4,5,6.....(没有并列)
dense_rank()通过部门ID分组后排序做累加 相同部门公用1个值 从1开始 1,2,2,3,3,3,4,5,5,6.....
rank()通过部门ID分组后排序做累加 相同部门公用1个值 从1开始 空出被占的名次 1,2,2,4,4,4,4,4,9,9......
SELECT    row_number()    over(ORDER BY department_id) row_number,
                  dense_rank()    over(ORDER BY department_id) dense_rank,
                  rank() over(ORDER BY department_id) rank,
                  department_id,
                  employee_id,
                  last_name
FROM        employees; 


 15.按照部门编号降序做排名 降序空值排第一位
SELECT     rank() over(ORDER BY department_id DESC) rank,
                  department_id,
                  employee_id,
                  last_name
FROM        employees;  

十二、汇总（ANY、相关子查询、EXISTS、WITH）

1查询工资最高的前5名员工
SELECT     * 
FROM      (SELECT    *
                 FROM employees
                  ORDER BY salary DESC)
WHERE　ROWNUMBER <=5;


2.查询员工表中第6到第12条数据
SELECT     *
FROM      (SELECT  ROWNUM rnum     ,e.*     FROM employees    e    WHERE    ROWNUM<=12) 
WHERE     rnum    > =6 ;


3,查询工资最高的第6到12条员工
SELECT *
FROM (SELECT ROWBUM rnum, e.* 
             FROM (SELECT  * 
                          FROM employees 
                          ORDER BY salary DESC) e 
              WHERE ROWNUM<=12)
where rnum>=6;    
 
4.查询所有不是部门经理的员工
SELECT *
FROM　employees
WHERE employee_id NOT IN   (SELECT manager_id  FROM department WHERE　manager_id IS NOT NULL)


5    ANY 逻辑或比较 ALL逻辑与比较（可以用MAX MIN 取代）


6.相关子查询（内外交互式相关子查询）
    按照一行接一行的顺序执行，主查询的每执行一行都执行一次子查询
    子查询用到主查询的数据
查询员工编号，姓名，部门编号，工资，本部门的工资总和
SELECT employee_id,
               last_name,
               department_id,salary,
               (SELECT SUM(salary) 
                 FROM employees 
                 WHERE department_id = e.department_id) 
FROM employees e; 


7查询所有工资超过本部门平均工资的员工
SELECT * 
FROM employees e
WHERE salary>(SELECT AVG(salary) FROM employees WHERE　department_id = e.department_id);


8.查询是本部门入职最早的但不是部门经理的员工
SELECT * 
FROM employees e
WHERE　hire_date=(SELECT MIN(hire_date) 
                                   FROM employees 
                                   WHERE department_id=e.department_id)
AND         employee_id NOT IN (SELECT manager_id 
                                                    FROM employees 
                                                    WHERE manager_id IS NOT NULL) 
9.EXISTS查询(EXISTS后面的子查询如果查得出数据，那么主查询才查得出来数据)
SELECT    *
FROM    employees
WHERE    EXISTS(SELECT * FROM departments WHERE 1=0) 


10.查询所有是部门经理的员工(代替in语法 提高效率 X代表任意数据)
SELECT    *
FROM    employees e
WHERE EXISTS(SELECT 'X' 
                            FROM departments d
                            WHERE    e.employee_id = d,manager_id); 


11.查询所有不是部门经理的员工(代替in语法 提高效率 X代表任意数据)
SELECT    *
FROM    employees e
WHERE  NOT EXISTS(SELECT 'X' 
                            FROM departments d
                            WHERE    e.employee_id = d,manager_id);  


 12.查询工资最高的前5的员工(WITH子句)
WITH new_emp   as (SELECT * FROM employees ORDER BY salary DESC)
SELECT * FROM new_emp WHERE ROWNUM<=5;


13.WITH子句 定义子查询作为一个表起别名 然后在后面的查询中调用（可以定义多个表）
WITH dept_costs AS
(SELECT d.department_name,
                SUM(e.salary) AS dept_total)
  FROM    employees e,
                 departments d
  WHERE e,department_id = d.department_id
  GROUP BY d.department_name),
avg_cost AS
(SELECT SUM(dept_total) / COUNT(*) AS dept_avg
  FROM dept_costs)


SELECT * 
FROM dept_costs
WHERE dept_total > (SELECT dept_avg
                                    FROM   avg_cost)
ORDER BY department_name;

十三、树状结构分级查询

 1.分级查询（遍历树结构的数据）（通过子节点查询父节点 通过父节点查询子节点）
CONNECT BY PRIOR cloumn1 = column2
从顶到底 cloumn1 = Parent Key
               cloumn2 = Child Key
从底到顶 cloumn1 = Child key
               cloumn2 = Parent Key 


2.查询206号员工所有的上级管理者（包括管理者的管理者）
<level>代表层次 从1开始 可以并列
SELECT level,employee_id,last_name,manager_id
FROM employees
START WITH employee_id = 206
CONNECT BY PRIOR manager_id = employee_id; 


3.查询101员工所有的下级员工（从上向下）
SELECT level , employee_id,last_name,manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id =manager_id; 


4.统计101员工的所有的手下的人数
（WHERE子句过滤单个节点）
SELECT COUNT(*)
FROM employees
WHERE employee_id<>101
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;


5.过滤整个分支(将205整个分支过滤掉)
SELECT COUNT(*)
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id AND employee_id <>205;


6. DML（增删改）
插入数据 insert in 表名 【(列1，列2，...)】 values (值1，值2,...) ;
省略列列表，默认就是表中的所有列
列和值必须要个数，顺序，类型相同


7.增加一个新部门
INSERT INTO departments (department_id,department_name,manager_id,localtion_id)
VALUES (120,'NEC',206,1700);


8,查询结果保存为表(快速创表)
CREATE TABLE new_emp
AS
SELECT employee_id,last_name,salary
FROM  employees; 

(创建空表)
CREATE TABLE new_dept
AS
SELECT  * FROM departments WHERE 1=0;


9.插入多行数据(底层数据库维护用处比较多)
INSERT INTO new_dept SELECT * FROM departments;


10.更新数据 update  表名 set 列1=值1，列2=值，...[where 子句]（不加where更新所有数据）
UPDATE new_emp SET first_name='三',last_name='张' WHERE employee_id = 100


11.修改60号部门员工的工资上浮50元
UPDATE new_emp SET salary = salary+50 WHERE department_id = 60;


12.修改103号员工的工资和100号员工相同
UPDATE new_emp SET salary = (SELECT salary FROM new_emp = WHERE employee_id = 100) 
WHERE employee_id = 103;


13.删除数据 delete from 表名 [where 子句] 
 删除部门名称为IT的部门的员工
DELECT FROM new_emp 
WHERE     department_id = (SELECT     department_id
                                              FROM        departments
                                              WHERE      department_name = 'IT');    
 14合并语句merge 解决效率问题 做数据同步 (同步修改 新增)
按照指定的条件执行插入或跟新操作
如果满足条件的行存在，执行跟新操作，否则执行插入操作
避免多次重复执行插入和删除操作
提高效率而且使用方便
在数据仓库用的比较多
 
创建俩个表 修改部分数据
CREATE TABLE emp1
AS
SELECT employee_id,last_name,salary
FROM employees; 


CREATE TABLE emp2
AS
SELECT employee_id,last_name,salary
FROM employees;  


MERGE INTO emp2 e2
USING  emp1 e1 ON(e1.employee_id = e2.employee_id)
WHEN MATCHED THEN
            UPDATE    SET e2.last_name = e1.last_name,e2.salary=e1.salary
WHEN NOT  MATCHED  THEN
            INSERT VALUES (e1.employee_id,e1.last_name, e1.salary)

十三、oracle锁

oracle锁一般都是默认加，当一个事务开始的时候默认加锁，当一个事务结束的时候，默认取消锁。当然也可以人为加锁。 
一.oracle锁按照颗粒划分可以分为行锁和表锁。
1.行锁是锁住一行（DML语句中，增，删，改等都是加的行锁）。
2.表锁是锁住一个表，比如(DDL语句一般加的都是表锁)。


二.oracle锁按照显隐可以划分为显示锁和隐式锁
1.隐式锁是默认加的锁（DML语句中，增，删，改等都是加的都是隐式锁）；
2.显示锁是人工手动加锁。一般查询语句是不会加锁的，但是也可以手动加锁（sql语句后面加 for update 锁行）
   还有一种情况是手动锁表，手动锁表分为两种：lock table 表 in share | exclusive mode;
    share模式：禁止其他会话对表做DML操作，但是允许其他会话也对表加share锁
    exclusive模式：禁止其他会话对表进行DML操作，也禁止其他会话对表加任何锁


三.oracle锁按类型分可以分为独占锁和共享锁
    1.独占锁  
    2.共享锁 
     （DML语句对表中行加的是独占锁，对表加的是共享锁） 也就是说，事务执行增，删，改操作操作的时候，锁住了表的一行，
    其他的事物对这一行不能进行操作，可以对该表的其他行进行操作。
     （加了共享锁的表不能再加独占锁） 事务执行增，删，改操作操作的时候，给表加上了共享锁，那么该表就不能执行DDL语句，
    也就是说不能对该表进行删除表等操作 


四.oracle死锁（两个事务互相锁）
举个例子。A事务和B事务同时执行操作a，b两行，首先A事务锁住了a行，然后B事务锁住了b行，A事务还要操作b行，
但是b行被B事务锁住了，所以A事务等待B事务解锁。而B事务还要操作a行，但是现在a行被A事务锁住，所以B事务要等待A事务
对a行解锁。然后两个事务就发生了死锁（互相等待别的事务解锁）。
死锁是不可避免的，但是oracle会对死锁进行检查，如果检测到死锁会抛出一个异常，这里可以用JAVA捕获异常的机制捕获死锁异常
回滚其中一个事务，就能解决死锁问题了。


五.oracle强行解锁
1.下面的语句用来查询哪些对象被锁：
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)
【注】以上两步，可以通过Oracle的管理控制台来执行。
3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）
4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。

oracle锁一般都是默认加，当一个事务开始的时候默认加锁，当一个事务结束的时候，默认取消锁。当然也可以人为加锁。 

十四、DDL语句
表名和列名：
必须是已字母开头
必须在1-30个字符之间
必须只能包含A-Z,a-z,0-9,_,$和#
必须不能和用户定义的其他对象重名
必须不能是Oracle保留字


创建表 CREATE TABLE [schema.] table (column datatype [DEFAULT expr][,...]);
必须具备 CREAT TABLE权限
存储空间
必须指定表名，列名，数据类型，尺寸


创建表
CREATE TABLE student
(
    stu_id NUMBER(6),
    stu_name VARCHAR2(50).
    stu_sex CHAR(2),
    stu_hiredate DATE
);
删除表
DROP TABLE student;


创建表(带默认值 插入时候用户没赋值用默认值，插入赋值就用赋的值)
CREATE TABLE student
(
    stu_id NUMBER(6),
    stu_name VARCHAR2(50).
    stu_sex CHAR(2) DEFAULT '男';,
    stu_hiredate DATE DEFAULT SYSDATE
);
利用子查询创建表
CREATE TABLE new_emp2
AS 
SELECT employee_id,last_name
FROM employees;
常用的数据字典
SELECT * 
FROM user_tables;


SELECT * 
FROM user_objects;


SELECT * 
FROM user_catalog; 


修改列（使用 ALTER TABLE语句）
追加新的列
修改现有的列
为新追加的列定义默认值
删除一个列


--追加 
ALTER TABLE student
ADD (phone VARCHAR2(50),address VARCHAR2(100)); 


--修改(数据结构要兼容)
ALTER TABLE student
MODIFY (address VARCHAR2(200)); 


--删除列
ALTER TABLE student
DROP (phone);


--表注释
COMMENT ON TABLE stu IS '这是我的学生表';


--列注释
COMMENT ON COLUMN stu.stu_id IS '学生编号';
COMMENT ON COLUMN stu.stu_name IS '学生姓名';
 
--回收站的数据字典
SELECT * FROM user_recyclebin;


--还原表(闪回)
FLASHBACK TABLE stu TO BEFORE DROP;


--清空回收站
PURGE RECYCLEBIN; 


临时表
创建事务临时表 ： 数据仅在一个事务中存在
CREATE GLOBAL TEMPORARY TABLE temp1
(
id NUMBER , 
name VARCHAR2(50)
) 
ON COMMIT DELETE ROWS;


创建会话临时表：数据仅在一个会话中存在
CREATE GLOBAL TEMPORARY TABLE temp2
(
id NUMBER,
name VARCHAR2(50)
) 
ON COMMIT PRESERVE ROWS; 


约束（5种）
NOT NULL 非空约束（限制列值不能为NULL）
UNIQUE    唯一值约束
PRIMARY KEY 主键约束 （现在列值不允许重复，不能为空,一个表只能有一个）
FOREIGN KEY 外键约束
CHECK  检查约束


--增加约束
ALTER TABLE 表名
ADD CONSTRAINTS 自定义约束名称（一般设置表名_列名_pk）  约束设置(列名,...)


--删除约束
ALTER TABLE newemp
DROP CONSTRAINTS 自定义约束名称; 

oracle锁一般都是默认加，当一个事务开始的时候默认加锁，当一个事务结束的时候，默认取消锁。当然也可以人为加锁。