Oracle超详细学习笔记
-----员工培训----
--1.最简单的查询
--例
SELECT *
FROM employees；
DESC employees；
SET linesize 600;
SET PAGESIZE 50;
--例2
SELECT table_name FROM user_tables;--查询数据库中所有表名
--2.查询特定的列
--例
SELECT employee_id,fisrt_name,last_name.salary
FROM employees；
--3.在SELECT子句中使用直接量
--例
SELECT 5
FROM employees；
-----------------------
SELECT 5
FROM dual;--这是Oracle提供的伪表
--例2：
SELECT 'Hello Oracle SQL'--在Oracle中如何表示字符串常量
FROM employees；
SELECT 'Hello Oracle SQL'--在Oracle中如何表示字符串常量
FROM dual；
--例3：
SELECT 'Hello Oracle''s SQL'--用两个连续单引号进行单引号转义
FROM employees；
--4在SELECT子句中使用算术表达式
--例1：查询员工的编号、姓名和年薪
SELECT employee_id,fist_name,last_name,salry,salary*12
FROM employees；
--例2：
SELECT 5/2
FROM dual；
--5.给例定义别名
--例1:
SELECT employee_id AS id
FROM employees；
--例2：
SELECT employee_id AS "Id"--双引号中的字符原样显示
FROM employees；
--例3：
SELECT employee_id,fist_name,last_name,salry,salary*12 AS annual_salary
FROM employees；
--例4：
SELECT employee_id,fist_name,last_name,salry,salary*12 AS "annual salary"
FROM employees；
--如果别名有特殊字符必须使用双引号
--6.字符串的链接运算符
--例：
SELECT employee_id||''||fist_name,last_name,salry,salary*12 AS annual_salary
FROM employees；
--7.过滤重复记录
--例1：
SELECT DISTINCT department_id
FROM employees；
--例2：
SELECT DISTINCT department_id,job_id
FROM employees;
--第二章 过滤查询和结果集排序
--1.使用WHERE子句过滤记录
--例1：
SELECT *
FROM employees
WHERE salary > 8000;
--例2：
SELECT employee_id，first_name,last_name,salary
FROM employees
WHERE salary = 17000;
--例3：
SELECT employee_id，first_name,last_name,salary
FROM employees
WHERE salary <> 17000;-- !=
--比较运算符号：>,>=,<,<=,=,!=或<>
--例4
SELECT employee_id，first_name,last_name,salary
FROM employees
WHERE salary >=2500 AND salary <= 17000;
--逻辑运算符：AND,OR,NOT
--2.在WHERE子句中使用字符串和日期
--例1：查询职位ID为st_clerk的员工信息
SELECT employee_id,job_id，first_name,
FROM employees
WHERE job_id = 'ST_CLERK';
--表中字段的值是区分大小写的
--例2：查询1998年7月9日入职的员工信息
SELECT employee_id,job_id,first_name
FROM employees
WHERE hire_date = '09-7月-1998';
--日期直接量必须以字符串的形式来体现
--Oracle的日期是格式敏感的
--标准日期表示DD-MM-RR
--3.其他比较运算符
--1）BETWEEN AND
--例1：查询工资在【2500,7000】上的员工信息
SELECT employee_id,job_id,first_name,salary
FROM employees
WHERE salary BETWEEN 2500 AND 7000;
--包含边界值
--第一个值要小于第二个值
--练习：查询1998年入职的员工信息
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE hire_date BETWEEN '01-1月-1998' AND '31-12月-1998';
--2) IN
--例1：查询职位ID是ST_CLERK或者ST_MAN的员工
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE job_id IN ('ST_CLERK','ST_MAN');
--例2
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE NOT job_id IN ('ST_CLERK','ST_MAN');
----------------------------------------------------------------------------
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE job_id NOT IN ('ST_CLERK','ST_MAN');
--3）LIKE
--例1：模糊查询
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE job_id LIKE 'ST%';
--通配符：%任意多个字符，_一个字符。
--只能用于日期或者字符串
--练习：
--查询first_name首字母是大写S，第三个字母是e的员工
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE first_name LIKE 'S_e%';
--查询first_name含有字母a和e的员工
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE first_name LIKE '%a%e%' OR first_name LIKE '%e%a%';
--查询1988年入职的员工
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE hire_date LIKE '%98';
--例2：查询职位ID含有_P的员工信息
SELECT employee_id,job_id,first_name,salary,hire_date
FROM employees
WHERE job_id LIKE '%\_P%' ESCAPE '\';
--转义字符是开发者自己定义的
--4.SELECT,FROM和WHERE子句的执行顺序 ```
--例：
SELECT employee_id,last_name AS ename,salary
FROM employees
WHERE last_name = 'King'; --Error,在WHERE子句中不能使用列的别名
--执行顺序：FROM--------------->WHERE------------->SELECT
--5).结果集排序
--例：
SELECT employee_id,first_name,last_name ,salaty
FROM employees
ORDER BY salary DESC;
--默认为升序排列（ASC），降序需要在字段后使用DESC
--例2:
SELECT employee_id,first_name,last_name
FROM employees
ORDER BY salary
--例3：
SELECT employee_id,first_name,last_name ,salaty
FROM employees
ORDER BY 4;--是字段在SELECT子句中的索引
--例4：
SELECT employee_id,first_name,last_name ,salaty
FROM employees
WHERE salary > 6000
ORDER BY salary;
--例5：查询员工的编号、姓名和年薪、并按年薪排序
SELECT employee_id,first_name,last_name ,salaty*12
FROM employees
ORDER BY salary*12;
----------------------------------------------
SELECT employee_id,first_name,last_name ,salaty*12 AS annual
FROM employees
ORDER BY annual;
--语句的执行顺序FROM------>WHERE--------->SELECT---------->ORDER BY
--例6
SELECT employee_id,first_name,last_name ,salaty*12 AS "annual Salary"
FROM employees
ORDER BY "annual Salary";
--别名在双引号中，是大小写敏感的
--例7
SELECT employee_id,first_name,last_name ,salaty
FROM employees
ORDER BY job_id,salary;
----------------------------
SELECT employee_id,first_name,last_name ,salaty
FROM employees
ORDER BY job_id,salary DESC;--只约束salary
--第三章 单行函数
--LOWER/UPPER/INITCAP
--例1:将查到的列的字符串全部转换成小写输出
SELECT LOWER(last_name)
FROM employees;
--例2：将将查到的列的字符串全部转换成大写输出
SELECT UPPER(last_name)
FROM employees;
--例3：将每个字符串的首字母变为大写
SELECT INITCAP('last_name HELLO dog')
FROM dual;
--CONCAT/LPAD/RPAD
--例1：链接字符串
SELECT first_name || ''|| last_name AS fullname
FROM employees;
-------------------------------------------
SELECT CONCAT( first_name,CONCAT('',last_name)) AS fullname
FROM employees;
--例2：在检索到的结果前循环加上第三个参数所表示字符串
--第二个参数为输出字符串长度，若参数1得长度大于限制从右边舍去
SELECT LPAD(employee_id,8,'123')
FROM employees;
-------------------------------
--在检索到的结果后循环加上第三个参数所表示字符串
--第二个参数为输出字符串长度，若参数1得长度大于限制从右边舍去
SELECT RPAD(employee_id,2,'123')
FROM employees;
--SUBSTR
--1:(使用2个参数）从第二个参数所表示的值开始截取字符串
SELECT SUBSTR('ABC D 1234 234 NARS na',4)
FROM dual;
SELECT SUBSTR(last_name,4),last_name
FROM employees;
--INSTR
--例1：查找第二参数的首字母在第一个参数中的位置
--若不匹配，则返回0
SELECT INSTR('SQL allows for dynamic DB changes','all')
FROM dual;
--指定起始位置（只返回按要求第一次出现位置）
SELECT INSTR('SQL allows for dynamic DB changes','a',6)
FROM dual;
--指定起始位置并指明第几次出现
SELECT INSTR('SQL allows for dynamic DB changes','a',6,2)
FROM dual;
--5）TRIM
--例1：去掉字符串两端空格
SELECT TRIM(' VFBAPSTAK ')
FROM dual;
--例2：去掉字符串前端指定的字符
SELECT TRIM(LEADING'A'FROM'AAABA VFBAPSTAK ')
FROM dual;
--例3：去掉字符串末端指定的字符
SELECT TRIM(TRAILING'A'FROM'AAABA VFBAPSTAK ABAA')
FROM dual;
--例4：去掉字符串两端指定的字符
SELECT TRIM('A'FROM'AAABA VFBAPSTAK ABAA')
FROM dual;
--6）REPLACE
--例1：用第三个参数替换第二个参数
SELECT REPLACE('SQL*PLUS supports loops or if statements.',
'supports',
'does not support')
FROM dual;
--7）LENGTH
--例1：统计参数字符串长度
SELECT LENGTH('SQL lets you supports loops or if statements.'
)
FROM dual;
--2。数值函数
--1）ROUND/TRUNC
--例1：保留参数2所表示的有效位小数，TRUNC不四舍五入
SELECT ROUND(168.888,2),TRUNC(168.888,2)
FROM dual;
------------------------
SELECT ROUND(168.888,0),TRUNC(168.888,0)
FROM dual;
-----------------
--无参数取整
SELECT ROUND(168.888),TRUNC(168.888)
FROM dual;
---------
--从个位开始取0运算
SELECT ROUND(168.888,-2),TRUNC(168.888,-2)
FROM dual;
--2）MOD
--例：求余运算
SELECT MOD(9,4)
FROM dual;
--3）CEIL/FLOOR
--例：向上向下取整
SELECT CEIL(34.5),FLOOR(34.5)
FROM dual;
--3)日期函数
-- 1）SYSDATE
--例：查询当前系统时间
SELECT SYSDATE
FROM dual;
--Oracle默认日期格式：DD-MON-YY
--例2:7天后的日期
SELECT SYSDATE + 7
FROM dual;
--例3:100小时之后的时间
SELECT SYSDATE + 100/24
FROM dual;
--2）MONTHS_BETWEEN
--例1：计算两个日期之间相隔几个月
SELECT MONTHS_BETWEEN('01-12月-2010','31-1月-2010')
FROM dual;
--3）ADD_MONTHS
--例：在指定日期后加上参数二指定的月份
SELECT ADD_MONTHS(SYSDATE,3)
FROM dual;
--4）NEXT_DAY
--例：当前日期之后的星期一
SELECT NEXT_DAY(SYSDATE,'星期一')
FROM dual;
--系统时间为中文
SELECT NEXT_DAY(SYSDATE,'MONDAY')
FROM dual;
-------------------------
SELECT NEXT_DAY(SYSDATE,'MON')
FROM dual;
--系统时间为英文
SELECT NEXT_DAY(SYSDATE,'1')
FROM dual;
--使用数字，1代表周日
--5）LAST_DAY
--例:返回当前月份的最后一天
SELECT LAST_DAY(SYSDATE)
FROM dual;
--4.字符串。数字和日期之间的转换函数
--准备：
--Oracle日期类型DATE数据的内部存储格式：世纪、年。月。日。时。分。秒
--日期的缺省输入和输出的格式：DD-MON-RR
--1)TO_CHAR
--例1：以默认日期格式输出：DD-MON-RR
SELECT first_name,last_name,TO_CHAR(hire_date)
FROM employees;
--例2：
SELECT first_name ,last_name,
TO_CHAR(hire_date,'YYYY-MM-DD DY HH24:MI:SS')
FROM employees；
--第一个参数必须是日期类型，不能是以字符串形式表示的日期直接量
--格式描述：
--年：YYYY、YY、RR
--月：MM、MON、MONTH
--日：DD
--星期：DY、DAY
--小时：HH24、HH
--分：MI
--秒：SS
--例3：借助TO_CHAR函数获取日期中存储的特殊值
SELECT TO_CHAR(SYSDATE,'DY')
FROM dual;
--例4:（数值格式化）
SELECT first_name,last_name,TO_CHAR(salary*1.6,'$999,999.99')
FROM employees;
--格式描述
/*
9：一位数字
0：一位数字，但会保留前导0
$：显示美元符号
L：显示本地货币符号
.：显示小数点
,：显示千分位
*/
--2）TO_DATE
--例1：使用缺省格式化描述
SELECT TO_DATE('03-9月-1975')
FROM dual;
--尽管默认格式年时2位，但在函数可以使用4为的年
--例2：指定格式化描述
SELECT TO_DATE('07.04.75','MM.DD.YY')
FROM dual;
----------------------
SELECT TO_DATE('05-FEB-1998 19:36:36','DD.MON.YYYY HH24:MI:SS')
FROM dual;
--例3：YY与RR的区别
SELECT TO_DATE(TO_CHAR('03-09-98','DD.MM.YY'),'DD-MM-YYYY')
FROM dual;
----------------------
SELECT TO_DATE(TO_CHAR('03-09-98','DD.MM.RR'),'DD-MM-YYYY')
FROM dual;
--3）TO_NUMBER 将字符串解析成数值
--例1：
SELECT TO_MUNBER('$12,345.69','$99,999.99')
FROM dual;
--5.ROUND和TRUNC用于日期
--1）ROUND函数：
--例1：对星期进行计算
SELECT ROUND(TO_DATE('16-2月-2011','DAY')
FROM dual;
/*
第一个参数必须是DATE类型，如果是日期直接量必须使用TO_DATE函数转换
如果是周日到周三，结果为这个星期的第一天，否则为下个星期的第一天（1为周日）
*/
--例2：对月份进行计算
SELECT ROUND(TO_DATE('16-10月-2010','MONTH')
FROM dual;
/*
如果是1~15日，结果为当月的第一天
否则为下个月的第一天
*/
--例3：对年份进行计算
SELECT ROUND(TO_DATE('16-10月-2010','YEAR')
FROM dual;
/*
如果是1~6月，结果为但年的第一天，
否则为下一年的第一天
*/
--2)TRUNC函数
--例1:对象星期进行TRUNC计算，求这个星期的第一天
SELECT TRUNC(TO_DATE('3-MAR-2010'),'DAY')
FROM dual;
--例2：对象月进行TRUNC计算，求这个月第一天
SELECT TRUNC(TO_DATE('3-MAR-2010'),'MONTH')
FROM daul;
--例3：对象年进行TRUNC计算，求这一年的第一天
SELECT TRUNC(TO_DATE('3-MAR-2010'),'YEAR')
FROM daul;
--6.DECODE函数
--例：
SELECT first_name,last_name,job_id,salary,
DECODE(
job_id,
'IT_PROG',salary*2,
'ST_CLERK',salary*1.2,
salary*1.1
) AS new_salary
FROM employees
ORDER BY job_id;
--7.CASE表达式
--例1：
SELECT first_name,last_name,job_id,salary,
CASE job_id
WHEN 'IT_PROG' THEN salary*2
WHEN'ST_CLERK' THEN salary*1.2
ELSE salary*1.1
END AS new_salary
FROM employees
ORDER BY job_id;
--例2：搜索CASE表达式
SELECT first_name,last_name,job_id,salary,
CASE
WHEN job_id = 'IT_PROG' THEN salary*2
WHEN job_id = 'ST_CLERK' THEN salary*1.2
ELSE salary*1.1
END AS new_salary
FROM employees
ORDER BY job_id;
--第四章 处理NULL值
--NULL值从何而来？
--1.表中的字段没有值，则其值视为NULL
--2.表达式计算的结果
--3.子查询的结果
--1.含有NULL值的表达式
--1）NULL参与算术运算
--例:
SELECT employee_id,first_name,last_name,salary+salary*commission_pct AS
income
FROM employees;
--NULL值参与算术运算，表达式的结果一定为NULL
--2)NULL值参与比较运算
--例1:
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE commission_pct = NULL;
-----------------------------------------
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE commission_pct != NULL;
--如果NULL值参与比较运算，结果一定为NULL
--因为NULL值得存在，SQL中的逻辑成为了“3种逻辑”：TRUE/FALSE/NULL
--例2：
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE commission_pct IS NULL;
-------------------------------
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE commission_pct IS NOT NULL;
--3)NULL值参与逻辑运算
--例1：
SELECT employee_id,first_name,last_name,job_id
FROM employees
WHERE job_id = 'IT_PROG' AND job_id != NULL;
--逻辑AND取值规律：FALSE-------------NULL------------>TRUE
--例2：
SELECT employee_id,first_name,last_name,job_id
FROM employees
WHERE job_id = 'IT_PROG' OR job_id != NULL;
--逻辑OR取值规律：TRUE------------->NULL------>false
--例3：
SELECT employee_id,first_name,last_name,job_id
FROM employees
WHERE NOT job_id != NULL;
--逻辑NOT取值规律：NOT NULL ==NULL
--4)ORDER BY子句中的NULL
--例1：
SELECT employee_id,first_name,last_name,commission_pct
FROM employees
ORDER BY commission_pct;
--升序排列时，NULL值在后
SELECT employee_id,first_name,last_name,commission_pct
FROM employees
ORDER BY commission_pct NULLS FIRST;--NULLS LAST
--2.NVL和NVL2
--例1：
SELECT employee_id,first_name,last_name,salary+salary*NVL(commission_pct,0) AS
income
FROM employees;
--例2：
SELECT employee_id,first_name,last_name,salary+salary*NVL(commission_pct,commission_pct,0) AS
income
FROM employees;
--3.NULLIF 如果两个值相等就返回NULL
--例：
SELECT employee_id,first_name,last_name,NVL(NULLIF(salary,17000),7000) AS nsalary
FROM employees;
--语法NULLIF(exp1,exp2)
--如果1和2相等，则返回NUL，否则返回1
--注意：参数的类型必须一致
--4。COALESCE
--例1：
SELECT employee_id,first_name,last_name,salary+salary*COALESCE(commission_pct,0) AS
income
FROM employees;
--可以接受N个参数，返回第一个部位NULL的参数
--参数的类型必须一致
--第五章 组函数与分组查询
--1.COUNT
--例1：
SELECT COUNT(employee_id)
FROM employees;
--例2：
SELECT COUNT(*)
FROM employees;
--例3：
SELECT COUNT(1)
FROM employees;
--语法：COUNT( [DISTINCT|ALL]exp),默认为ALL
--例4：
SELECT COUNT(job_id)
FROM employees;
----------------------------------
SELECT COUNT(DISTINCT job_id)
FROM employees;
--例5：
SELECT COUNT(commission_pct)
FROM employees;
--所有的组函数，都会忽略NULL值（单个字段）
--例6：查寻80号部门的人数
SELECT COUNT(1)
FROM employees
WHERE department_id = 80;
--2AVG
--例1：
SELECT AVG(salary) AS avg_salf
FROM employees;
--例2：
SELECT AVG(salary) AS avg_salf
FROM employees
WHERE department_id = 80;
--例3：
SELECT AVG(salary) AS avg_salf
FROM employees
WHERE job_id = 'ST_CLERK';
--3.SUM
--例：
SELECT SUM(salary)
FROM employees;
--练习：统计50号部门的工资总额、平均工资和部门人数
SELECT SUM(salary),AVG(salary),COUNT(*)
FROM employees
WHERE department_id = 50;
--4.MIN和MAX
--例
SELECT MIN(salary),MAX(salary)
FROM employees;
--分组查询
--1)GROUP BY
--例1：
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
--只有作为分组依据的列，才能单独出现在SELECT子句中
--练习：统计每个部门的工资总额、平均工资和人数
SELECT SUM(salary),AVG(salary),COUNT(*)
FROM employees
GROUP BY department_id;
--例2：查询每个部门中不同岗位的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
--2)分组查询中的ORDER BY
--例1：查询部门的平均工资，并按这个平均工资排序
SELECT department_id,AVG(salary) AS avg
FROM employees
GROUP BY department_id
ORDER BY avg;
------------------------
SELECT department_id,AVG(salary) AS avg
FROM employees
GROUP BY department_id
ORDER BY AVG(salary);
------------------------
SELECT department_id,AVG(salary) AS avg
FROM employees
GROUP BY department_id
ORDER BY 2;
-------------------------
SELECT department_id,AVG(salary) AS avg
FROM employees
GROUP BY department_id
ORDER BY salary;--Error!只有作为分组依据的列，才能作为排序的条件
--3）HAVING子句
--例1：查询平均工资高于8000的职位
SELECT job_id,AVG(salary) AS avg
FROM employees
WHERE AVG(salary) >8000 --Error!
GROUP BY job_id;
--例2：HAVING只能跟GROUP BY一起使用，用于对分组后的信息进行过滤
SELECT job_id,AVG(salary) AS avg
FROM employees
GROUP BY job_id
HAVING AVG(salary) >8000;
--例3：HAVING 子句中只能使用作为分组依据的列，或者组函数
SELECT job_id,AVG(salary) AS avg
FROM employees
GROUP BY job_id
HAVING salary >8000;--Error
---------------------------
SELECT job_id,AVG(salary) AS avg
FROM employees
GROUP BY job_id
HAVING avg >8000;
--执行顺序FROM------->WHERE--------->GROUP BY--------->HAVING--->SELECT--->ORDER BY
--例4：查询60号以上部门平均工资
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 60
GROUP BY department_id;
-----------
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id > 60;
--不要使用HAVING替代WHERE
--6.组函数的嵌套调用
--例：统计岗位平均工资额最低值和最高值
SELECT MIN(AVG(salary)),MAX(AVG(salary))
FROM employees
GROUP BY job_id;--对内层使用
--组函数最多嵌套两层
--7.组函数中的NULL值处理
--例：查询员工的总佣金和平均佣金
SELECT SUM(salary*NVL(commission_pct,0)) AS sum_sal,
AVG(salary*NVL(commission_pct,0)) AS avg_sal,
COUNT(salary*NVL(commission_pct,0))
FROM employees;
--第六章 多表连接查询
--1.笛卡尔积（表的交叉连接）
--例1：
SELECT countries.*,regions.*
FROM countries,regions;
--例2：表的别名，一旦定义，不能使用原表明
SELECT cou.*,reg.*
FROM countries cou,regions reg;
--例3：
SELECT c.country_name,c.region_id,r.region_id,r.region_name
FROM countries c,regions r;
--2.内连接
--1)等值连接
--例1：查询国家ID，国家名称和国家所在地的地区的名称
SELECT c.country_id,c.country_name,r.region_name,r.region_id,c.region_id
FROM countries c,regions r
WHERE c.region_id = r.region_id;
--练习：
--1.查询员工的编号、职位ID、工资和所在的部门名称
SELECT e.employee_id,e.job_id,e.salary,e.department_id,d.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
--2.查询部门编号、名称和该部门经理的名字
SELECT
--例2：查询员工的编号、姓名和其主管的姓名
SELECT e.employee_id,e.first_name,e.last_name,
e.manager_id,em.employee_id,
em.first_name,em.last_name
FROM employees e,employees em
WHERE e.manager_id = em.employee_id;
--例3：查询部门名称，及部门所在的国家的名称
SELECT department_name,country_name
FROM departments d,locations l,countries c
WHERE d.location_id = l.location_id AND
l.country_id = c.country_id;
--2)不等连接
--例：查询员工的姓名、工资以及工资级别
SELECT e.first_name,e.last_name,e.salary,g.grade_level,
g.lowest_sal,g.highest_sal
FROM employees e,job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;
--3.外连接
--例1：
SELECT e.employee_id,e.first_name,e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);--左外连接
--例2：
SELECT e.employee_id,e.first_name,e.last_name,d.department_name
FROM employees e,departments d
WHERE d.department_id(+) = e.department_id ;--左外连接
--练习：查询没有员工的部门
SELECT d.department_id,department_name
FROM employees e,departments d
WHERE d.department_id = e.department_id(+);
--4)多表连接查询的SQL:99语法
--1）笛卡尔积
--例：
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
CROSS JOIN departments dept;
--2)等值连接
--例1：查询员工的姓名和所在的部门的名称
SELECT e.last_name,e.first_name,d.department_name
FROM employees e,departments d
WHERE d.department_id = e.department_id;
-----------------------------------------
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
INNER JOIN departments dept
ON (d.department_id = e.department_id);
--------------------------------
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
JOIN departments dept
ON (d.department_id = e.department_id);
---------------------------------
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
JOIN departments dept
ON (d.department_id = e.department_id AND e.manager_id = d.manager_id);
--例2：
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
JOIN departments dept
USING (department_id);
------------------
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
JOIN departments dept
USING (department_id,manager_id);
--练习
--查询员工的编号、职位ID、工资和所在部门的名称
SELECT e.employee_id,e.job_id,e.salary,d.department_name
FROM employees e
JOIN departments d
USING (department_id);
--查询50号以上部门的编号、名称和该部门所在城市的名称
SELECT department_id,department_name,city
FROM departments d
JOIN locations l
USING (location_id)
WHERE department_id > 50;
--ON子句中只写与连接相关的条件
--例3：查询部门，以及部门所在的国家名称
SELECT department_id,department_name,country_name
FROM departments d
JOIN locations l
USING(location_id)
JOIN countries c
USING(country_id);
--3)外连接
--例：左外连
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id =d.department_id);
---------------------------
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
RIGHT LEFT OUTER JOIN departments d
ON (d.department_id =e.department_id);
--练习：查询没有员工的部门
SELECT d.department_id,department_name
FROM departments d
LEFT OUTER JOIN employees e
ON (d.department_id =e.department_id)
WHERE e.department_id IS NULL;
---------------------------
--4）全外连接
--例
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id =d.department_id);
--练习：查询没有员工的部门和没有部门的员工
SELECT e.employee_id,e.first_name,d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id =d.department_id)
WHERE e.department_id IS NULL OR d.department_id IS NULL;
--第七章 子查询
--1.标量子查询
--例1：查询与Neena(first_name)同一职位的员工
SELECT employee_id,first_name,last_name,job_id
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE first_name = 'Neena'
);
/*
1.能使用表达式的地方，就可以使用子查询
2。先执行子查询，再执行主查询
3。如果子查询返回一行、一列，则称其为“标量子查询”
4.子查询一定要放在圆括号中，建议将子查询放在运算符的右侧
5。如果子查询没有结果就视为NULL
*/
SELECT employee_id,first_name,last_name,job_id
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE first_name = 'NeenaA'
);
--练习：--1.查询工资少于总平均工资的员工编号、名字、工资和职位ID
SELECT employee_id,first_name,last_name,salary,job_id
FROM employees
WHERE salary < (
SELECT AVG(salary)
FROM employees
);
--2.查询平均工资最低的职位
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (
SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id
);
--2.单行子查询
--例：查询与Neena(first_name)同一部门、同一岗位的员工
SELECT employee_id,first_name,last_name,department_id,job_id
FROM employees
WHERE (department_id,job_id) = (
SELECT department_id,job_id
FROM employees
WHERE first_name = 'Neena'
);
--3表子查询
--例1：查询所有管理者的信息
SELECT employee_id,first_name,last_name,
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
);
--例2：查询工资是所在职位最高的员工的信息
SELECT employee_id,first_name,last_name,salary,job_id
FROM employees
WHERE (job_id,salary)IN(
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id
);
--例3：查询所有人民的信息
SELECT employee_id,first_name,last_name
FROM employees
WHERE NOT employee_id IN(
SELECT manager_id
FROM employees
);
-----------------------------
SELECT employee_id,first_name,last_name
FROM employees
WHERE NOT employee_id IN(
SELECT NVL(manager_id,-1)
FROM employees
);
--练习：
--1.查询位于Seattle市的部门的员工的信息
SELECT employee_id,first_name,last_name,department_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN(
SELECT location_id
FROM locations
WHERE city = 'Seattle'
)
);
--2.查询与名字（first_name）中包含字母x的员工在相同部门工作的员工的信息
SELECT employee_id,first_name,last_name,department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE first_name like '%x%'
);
--例4：哪些员工的工资比任何一个职位的平均工资都要低
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary < ALL (
SELECT AVG(salary)
FROM employees
GROUP BY job_id
);
------------------------
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary < ALL (
SELECT AVG(salary)
FROM employees
WHERE department_id > 1000
GROUP BY job_id
);
--ALL要与比较运算符配合使用
--若果子查询没有结果，那么带有ALL的比较为TRUE
--例5：哪些员工的工资比某一个职位的平均工资低
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary < ANY (
SELECT AVG(salary)
FROM employees
WHERE department_id > 1000
GROUP BY job_id
);
---如果子查询没有结果，那么带有ANY的比较为FALSE
--例6：哪些员工的工资高于所属职位的平均工资
SELECT employee_id,first_name,last_name,salary
FROM employees e,(
SELECT job_id,AVG(salary) avg
FROM employees
GROUP BY job_id
) a
WHERE e.job_id = a.job_id AND e.salary > a.avg;
--写在FROM子句中的子查询叫做“内联视图”
--4.关联子查询（相关子查询）
--例1：哪些员工的工资高于所属职位的平均工资
SELECT employee_id,first_name,last_name,salary
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e1.job_id = e2.job_id
);
--练习：查询谁的工资是所在部门的最高工资
SELECT department_id,salary
FROM employees e1
WHERE salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
);
--例2：查询所有管理者的信息
SELECT employee_id,first_name,last_name,job_id
FROM employees e1
WHERE EXISTS (
SELECT employee_id
FROM employees e2
WHERE e2.manager_id = e1.employee_id
);
--例3：所有普通员工的信息
SELECT employee_id,first_name,last_name,job_id
FROM employees e1
WHERE NOT EXISTS (
SELECT employee_id
FROM employees e2
WHERE e2.manager_id = e1.employee_id
);
--练习：
--1：查询所有调换过岗位的员工
SELECT employee_id,first_name,last_name,job_id
FROM employees e1
WHERE EXISTS (
SELECT employee_id
FROM job_history jh
WHERE e1.employee_id = jh.employee_id
);
--2: 查询没有员工的部门
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
SELECT 'OK'
FROM employees e1
WHERE e1.department_id = d.department_id
);
--TOP N分析和结果集分页
--例1：伪劣ROWNUM
SELECT ROWNUM,employee_id,first_name,last_name
FROM employees;
------------------------------
SELECT ROWNUM,employee_id,first_name,last_name
FROM employees
WHERE department_id = 50;
------------------------------
SELECT ROWNUM,employee_id,first_name,last_name
FROM employees
WHERE ROWNUM <=5;
------------------------
--例3：
SELECT ROWNUM,employee_id,first_name,last_name
FROM employees
WHERE ROWNUM BETWEEN 6 AND 10;
-------------------------------
SELECT ROWNUM,employee_id,first_name,last_name
FROM employees
WHERE ROWNUM >=1;
--例4：
SELECT ROWNUM,employee_id,first_name,last_name
FROM employees
ORDER BY first_name;
--例5：物理分页
SELECT employee_id,first_name,last_name
FROM(
SELECT ROWNUM linenum,employee_id,first_name,last_name
FROM(
SELECT employee_id,first_name,last_name
FROM employees
ORDER BY first_name
)
WHERE ROWNUM <= 10
)
WHERE linenum >=6;
--ROWID;标出行在磁盘上的物理位置，行的唯一标识
--第八章 结果集的集合运算
--1.UNION
--例1：查询所有员工曾经从事和正在从事的职位
SELECT employee_id,job_id
FROM job_history
UNION
SELECT employee_id,job_id
FROM employees;
/*
1.UNION会重新排序并去掉重复记录
2.两个查询的列数，以及列的类型必须意义匹配，列名可以不同
3.最终结果显示第一个查询的列明或者列的别名
4.除UNION ALL外，结果集自动按第一个查寻的第一列的升序排列
*/
--例2：关于ORDER BY
SELECT employee_id,job_id
FROM job_history
UNION
SELECT employee_id,job_id
FROM employees
ORDER BY job_id;
----------------------
SELECT employee_id,job_id j1
FROM job_history
UNION
SELECT employee_id,job_id j2
FROM employees
ORDER BY j1;
----------------------
SELECT employee_id,job_id j1
FROM job_history
UNION
SELECT employee_id,job_id j2
FROM employees
ORDER BY 2;
--2.UNION ALL
--例1：查询员工的职位变动情况
SELECT employee_id,job_id
FROM job_history
UNION ALL
SELECT employee_id,job_id
FROM employees
ORDER BY employee_id;
--例2：
SELECT department_name AS dname_and_ename
FROM departments
WHERE department_id = 50
UNION ALL
SELECT first_name
FROM employees
WHERE department_id = 50;
--练习：查询没有员工的部门编号、名称，以及没有部门的员工的编号和姓名
SELECT employee_id,first_name
FROM employees
WHERE department_id is NULL
UNION ALL
SELECT d.department_id,department_name
FROM departments d
LEFT OUTER JOIN employees e
ON (d.department_id =e.department_id)
WHERE e.department_id IS NULL;
--3.INTERSECT
--例：哪些员工现在从事的是他曾今从事过的职位
SELECT employee_id,job_id
FROM job_history
INTERSECT
SELECT employee_id,job_id
FROM employees;
--4.MINUS(差集）
--例；查询所有离职的员工
SELECT employee_id
FROM job_history
MINUS
SELECT employee_id
FROM employees;
--第九章 表的创建
--1.常用数据类型
/*
1.CHAR[(<size>[BYTE|CHAR])]
name CHAR
name CHAR(45)
name CHAR(45 BYTE)
name CHAR(45CHAR)
以定长的方式存储字符数据，不足在又侧自动补空格字符
如果不指定长度，默认为一个字节
如果指定长度，最大为2000字节，不指定单位，默认为“字节”
2.VARCHAR2(<size>[BBYTE|CHAR])
name VARCHAR2(45)
name VARCHAR2(45 BYTE)
name VARCHAR2(45 CHAR)
以变长的方式存储字符数据
定义的时候必须指定长度，默认单位为“字节”
最长为4000字节
3NUMBER[(p,s)]
salary NUMBER
salary NUMBER(12)
salary NUMBER(8,2) 2表示小数部分位数
p：表示精度，即数字的个数，取值在1~38之间
S：小数位数
如果定义时不指定精度，默认为38，任意分布在小数点两侧
如果定义时指定了精度，可以不指定S的值，S此时为0，只能存储整数
存储数据时，如果违反精度，Oracle会报错，如果违反S，自动四舍五入
当S>0,并且s>=P的时候，只能存储纯小数，并且该数的小数点后边至少要有S减P个0
4.REAL---------->18为精度的浮点数
5.INTEGER--------->NUMBER(38)
6.DATE
包括7个属性，世纪、年、月、日、时、分、秒
*/
--2.手工建表
--例：
CREATE TABLE products(
P_code NUMBER(6)
P_name VARCHAR2(30 CHAR)
P_desc VARCHAR2(100 CHAR)
p_price NUMBER(6,2)
is_import CHAR(1) DEFAULT 'N'
);
/*
以字母开头，之后可以是字母、数字、$、#、_的任意组合，多国语言下字母不限于英文
名字最大的长度为30个字符
不建议使用SQL，或者Oracle的关键字
*/
--3.借助子查询创建表
--例1：
CREATE TABLE workers
AS (
SELECT employee_id,first_name,last_name,salary
FROM employees
)；
---------------------------------
CREATE TABLE workers
AS (
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE employee_id = 6666666
)；
--例2：
CREATE TABLE workers
AS (
SELECT employee_id AS empno,first_name||''||last_name AS fullname,salary AS sal
FROM employees
)；
-----------------------------
CREATE TABLE workers (
empno,fullname,sal
)
AS (
SELECT employee_id,first_name,last_name,salary
FROM employees
)；
--4.删除与截断表
--例1：
DROP TABLE table_name;
--例2：
TRUNCATE TABLE table_name;
--第十章 数据的维护（DML）
--准备工作：
CREATE TABLE emp (
empo,ename,hiredate,job,sal,comm,deptno
) AS (
SELECT employee_id,first_name||''||last_name,hire_date,job_id,salary,salary*commission_pct,department_id
FROM employees
);
CREATE TABLE dept(
deptno,dname,dept
) AS(
SELECT dept.department_id,dept.department_name,loc.city
FROM departments dept
JOIN locations loc
USING(location_id)
);
CREATE TABLE workers(
id NUMBER(6),
name VARCHAR2(20),
salary NUMBER(8,2),
commission NUMBER(4) DEFAULT 1000
);
--2.INSERT
--例1：
INSERT INTO workers
VALUES (166,'Peter',4500.00,2000);
------------------------
INSERT INTO workers(name,salary,commission,id)
VALUES ('Peter',4500.00,2000,166);
--例2：
INSERT INTO workers(name,salary)
VALUES ('Peter',4500.00);
--例3：
INSERT INTO workers(name,salary,commission)
VALUES ('Peter',4500.00,DEFAULT);
--例4：
INSERT INTO workers
VALUES (NULL,'Peter',4500.00,DEFAULT);
--例5：
INSERT INTO emp(empo,hiredate,job,sal)
VALUES (266,'08-3月-2011','IT_PROG',8888);
--例6：
INSERT INTO emp(empo,hiredate,job,sal)
VALUES (266,TO_DATE('08-2012-03','MM-YYYY-DD'),'IT_PROG',8888);
--例7：
INSERT INTO emp(empo,hiredate,job,sal)
VALUES (266,DATE '2010-03-08','IT_PROG',8888);
--例8：
INSERT INTO workers
SELECT empo,ename,sal,comm
FROM emp;
--------------------------------
INSERT INTO workers(id,name)
SELECT empo,ename
FROM emp;
--3.UPDATE
--例1：
UPDATE emp SET sal= 999,job = 'IT_NARS'
WHERE ename LIKE '%x%';
--例2：
UPDATE emp SET (job,sal) = (
SELSCT job, sal
FROM emp
WHERE empno = 176
)
WHERE empno = 266;
--4.DELETE
--例：
DELETE FROM emp --FROM可省
WHERE empno = 266；
--5.事务处理
/*
A：原子性
C：一致性
I：隔离性
D：持久性
*/
/*
事物的开始和结束：
开始：
连接到数据库，并执行第一个DML语句
前一个事物结束后，再一次执行了一条DML语句
结束：
执行COMMIT或者ROLLBACK
执行了一条DDL语句或者DCL语句之后，会自动执行COMMIT
使用exit命令退出sql*plus,会自动执行COMMIT
如果SQL*PLUS意外退出，会自动执行ROLLBACK
*/
--第十一章 约束
--1.Oracle中的5中约束
/*
实体完整性--------->主键约束 PRIMARY KEY
参照完整性--------->外键约束 FOREIGN KEY
用户自定义完整性--------->NOT NULL/UNIQUE/CHECK
NOT NULL:列的值不能为空
UNIQUE:列的值不能重复
CHCK:列的值满足一定的条件
PRIMARY KEY:列的值可以唯一标识一条记录
FOREIGN KEY：用来维护表与表之间的参照完整性
*/
--2.NOT NULL约束
--1）在创建表的时候指定NOT NULL
CREATE TABLE dept(
deptno NUMBER(3),
dname VARCHAR2(15) CONSTRAINT dept_adname_nn NOT NULL,
loc VARCHAR2(20)
);
--------------------------------------
CREATE TABLE dept(
deptno NUMBER(3),
dname VARCHAR2(15) NOT NULL,
loc VARCHAR2(20)
);
--2)为已有表添加NOT NULL约束
ALTER TABLE dept
MODIFY (dname CONSTRAINT dept_adname_nn NOT NULL);
--3.UNIQUE约束
--1）在创建表的时候指定UNIQUE
--例1：列级别约束
CREATE TABLE dept(
deptno NUMBER(3),
dname VARCHAR2(15)
CONSTRAINT dept_adname_nn NOT NULL
CONSTRAINT dept_adname_uq UNIQUE, --列级别约束
loc VARCHAR2(20)
);
--例2：表级别定义
CREATE TABLE dept(
deptno NUMBER(3),
dname VARCHAR2(15)
CONSTRAINT dept_adname_nn NOT NULL,
loc VARCHAR2(20),
CONSTRAINT dept_adname_uq UNIQUE(dname)
);
--NOT NULL 只能在列级别定义
------------------------------------------
CREATE TABLE dept(
deptno NUMBER(3),
dname VARCHAR2(15)
CONSTRAINT dept_adname_nn NOT NULL,
loc VARCHAR2(20),
CONSTRAINT dept_adname_uq UNIQUE(deptno,dname)
);
--2)为已有表添加
ALTER TABLE dept
ADD CONSTRAINT dept_adname_uq UNIQUE(deptno);
--4.CHECK约束
--1）在创建表时指定
CREATE TABLE emp(
deptno NUMBER(6),
dname VARCHAR2(20),
gender CHAR CONSTRAINT emp_gender_ck CHECK(gender IN('F','M')),
age NUMBER(3) CONSTRAINT emp_age_ck CHECK(age >= 18 ),
sal NUMBER(8,2),
deptno NUMBER(4)
);
--例2：
CREATE TABLE emp(
deptno NUMBER(6),
dname VARCHAR2(20),
gender CHAR ,
age NUMBER(3) ,
sal NUMBER(8,2),
deptno NUMBER(4),
CONSTRAINT emp_gender_ck CHECK(gender IN('F','M')),
CONSTRAINT emp_age_ck CHECK(age >= 18 )
);
--2)为已有表添加
ALTER TABLE emp
ADD (CONSTRAINT emp_gender_ck CHECK(gender IN('F','M')),
CONSTRAINT emp_age_ck CHECK(age >= 18 )
);
--5. PRIMARY KEY约束
--1) 在定义表的时候指定
--例1：
CREATE TABLE dept (
deptno NUMBER(3) CONSTRAINT dept_deptno_pk PRIMARY KEY,
dname VARCHAR2(15),
loc VARCHAR2(20)
);
--例2：
CREATE TABLE dept (
deptno NUMBER(3),
dname VARCHAR2(15),
loc VARCHAR2(20),
CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno)
);
----------------------------------------------------------------------------
CREATE TABLE dept (
deptno NUMBER(3),
dname VARCHAR2(15),
loc VARCHAR2(20),
CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno, dname) --联合主键
);
--2) 为已有表添加
ALTER TABLE dept
ADD CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno);
--6. FOREIGN KEY约束
--1) 在定义表时指定
--例1：
CREATE TABLE emp (
empno NUMBER(6),
ename VARCHAR2(20),
gender CHAR,
age NUMBER(3),
sal NUMBER(8,2),
deptno NUMBER(3) CONSTRAINT emp_deptno_fk REFERENCES dept(deptno)
);
--例2：
CREATE TABLE emp (
empno NUMBER(6),
ename VARCHAR2(20),
gender CHAR,
age NUMBER(3),
sal NUMBER(8,2),
deptno NUMBER(3),
CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
--2) 为已有的表添加
ALTER TABLE emp
ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno);
Oracle超详细学习笔记
