Oracle数据库学习笔记

Oracle

SQL

• SQL（Structural Query Language, 结构化查询语言）分为三种类型：
  • DML（Data Manipulation Language）数据操纵语言

    用于查询和修改数据记录，包括增删改查。

  • DDL（Data Definntion Language）数据定义语言

    用于定义数据库结构，包括
    1.CREATE TABLE：创建数据库表
    2.ALTER TABLE：更改表结构，增加、删除、修改列长度
    3.DROP TABLE：删除表
    4.CREATE INDEX：在表上建立索引
    5.DROP INDEX：删除索引

  • DCL（Data Control Language）数据控制语言

    用于控制数据库的访问，包括：
    1.GRANT：授予访问权限
    2.REVOKE：撤销访问权限
    3.COMMIT：提交事务处理
    4.ROLLBACK：事务处理回退
    5.SAVEPOINT：设置保存点
    6.LOCK：对数据库部分内容进行锁定

• SQL不区分大小写，作为习惯，关键字通常大写，其余小写。
• 出现关键字换行，增加可读性。
• SQL注释：两根 短横线 –

SELECT

1.查看表table0的列(describe)：desc table0;
2.数字和日期可以使用算术运算符，日期不能乘除。

SELECT last_name,salary,12*salary+1000
FROM employees;

3.dual是一个虚拟表，用来构成select的语法规则，执行命令，oracle保证dual里面永远只有一条记录。
4.空值：无效，未指定，不同于0，凡是null参与的运算都为null。
5.列的别名，默认大写，中间可以加 as。

SELECT employee_id id, last_name, 12salary annual_sal
FROM employees;
–加双引号，原格式输出
SELECT employee_id as id, last_name “Name”, 12salary annual_sal
FROM employees;

6.连接符：把列与列，列与字符连接在一起，用 || 表示。可以用来合成列。

SELECT last_name||’`s job_id is '||job_id as details
FROM employees;

7.日期和字符只能在单引号中出现，每返回一行，字符串被输出一次。只有起别名用双引号。
8.重复行：默认情况下，查询会反胃全部行，包括重复行，null也算一行。去重用 distinct .

SELECT DISTINCT department_id from employees;
–查询多个时注意行数匹配

9.SQL和SQLPlus
SQL是一种语言；SQLPlus是一种环境 ，Oracle的特征之一，有自己的关键字，并可以缩写（如desc, ed）。

过滤（where）

• 字符和日期要包含在单引号内，字符大小写敏感，日期格式敏感。日期的默认格式时 DD-MON月-RR

select employee_id,last_name,salary,hire_date
from employees
–where hire_date = ‘7-6月-1994’
where to_char(hire_date,‘yyyy-mm-dd’) = ‘1994-06-07’

• betwen…and…(包含边界)

select employee_id,last_name,salary
from employees
where salary between 4000 and 7000

• in(set)

select employee_id,department_id,salary
from employees
– where department_id = 90 or department_id = 80 or department_id = 70
where department_id in(70,80,90)

• like

select employee_id,last_name,salary
from employees
–含有字母a，一个‘%’表示0个以上字符
where last_name like ‘%a%’
–第二个字母为a，一个‘_’表示一个字符
where last_name like ‘_a%’
–查找含有下划线，此时斜线为转义字符
where last_name like ‘%_%’ escape ‘’
–查询94年雇佣的员工
select last_name,hire_date
from employees
–where hire_data like ‘%94’
where to_char(hire_date,‘yyyy’) = ‘1994’
–选择姓名中有a和e的员工姓名
select last_name
from employees
where last_name like ‘%a%e%’ or last_name like ‘%e%a%’
–where last_name like ‘%a%’ and last_name like ‘%e%’

• is null / is not null

select employee_id,last_name,salary,commission_pct
from employees
where commission_pct is null

排序（order by）

• order by语句在select语句的结尾，也可以按别名排。
• ASC(ascend) 升序 （默认）
• DESC(descend) 降序

select employee_id,department_id,salary
from employees
where department_id=80
order by salary desc
–多层排序
–order by salary desc,last_name asc

函数

单行函数

只对一行进行操作，且只返回一个结果。可以转换数据类型，可以嵌套，参数可以是一列或一个值。

字符函数

• 大小写控制函数
  • lower 转为小写
  • upper 转为大写
  • initcap 首字母大写

select *
from employees
where lower(last_name) = 'king'

• 字符控制函数
  • concat(‘str1’, ‘str2’) 连接字符
  • substr(‘str’, start, num) 取子字符串
  • length(‘str’) 长度
  • instr(‘str1’, str2’’) 判断str2在str1中首次出现的位置，若不存在输出0
  • lpad(var, num, ‘str’) 左补位，用str来补齐，不能为空
  • rpad(var, num, ‘str’) 右补位
  • trim(‘str1’, FROM ‘str2’) 将str1从str2中移除，仅能去首尾
  • replace(‘str’, ‘str1’, ‘str2’) 取代（所有）

 SELECTt raplace('abcab','b','m')
 FROM dual

数字函数

• ROUND(num,bit) 四舍五入，bit为小数位数，默认为保留整数部分，以小数点位为0，为负的话左取整
• TRUNC(num,bit) 截断
• MOD(num1,num2) 求余

日期函数

日期型数据实际包含两个值：日期和时间。

• 日期和数字可以做加减运算。
• 两日期相减返回相差天数，但不能做加法运算。
• 可以用数字除以24来向日期中加上或者减去天数。
• MONTHS_BETWEEN 两个日期间相差的月数
• ADD_MONTHS 向指定日期中加上若干月
• NEXT_DAY 指定日期的下个星期*对应的日期
• LAST_DAY 本月最后一天
• ROUND 日期四舍五入（月初），默认按日取整。
• TRUNC 日期截断

SELECT employee_id,last_name,(sysdate-hire_date)/30,MONTHS_BETWEEN(sysdate,hire_date)
FROM employees
--查询入职时间是月底倒数第二天的员工
SELECT last_name,hire_date
FROM employees
where hire_date = last_day(hire_date)-1

转换函数

• 隐式数据类型转换（Oracle自动完成）
  • DATE <——> VARCHAR2(CHAR) <——> NUMBER
• 显式数据类型转换
  
• to_char to_number

--不填充
SELECT to_char(1234567.89,'999,999,999.99') from dual
--填充0
SELECT to_char(1234567.89,'L000,000,999.99') from dual
--
SELECT to_number('L001,234,567.89', ,'L000,000,999.99') + 1 from dual

通用函数

适用于任何数据类型，也适用于空值。

• NVL(expr1,expr2)
  • 将空值转换成一个已知的值
  • 可以使用的数据类型有日期、字符、数字。
  • 当expr1为空，用expr2代替。

SELECT employee_id last_name,salary*12*(1+NVL(commission_pct,0)) “Annual Salary”
FROM employees
-- 注意类型转换
SELECT last_name,NVL(to_char(department_id,'9999'),'这是老板') "部门"
FROM employees

• NVL2(expr1,expr2,expr3)
  • expr1不为空，返回expr2；为空，返回expr3。
• NULLIF(expr1,expr2)
  • 相等返回NULL，不等返回expr1。
• COALESCE(expr1,expr2,…,exprn)
  • 若expr1为空，返回expr2，依次类推…

条件表达式

• 在SQL语句中使用 IF-THEN-ELSE 逻辑。
• 两种方法
  • CASE表达式

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN ... THEN ...
ELSE else_expr]
END
-- 例
select employee_id,last_name,department_id,case department_id when 10 then salary * 1.1
                                                               when 20 then salary * 1.2
                                                               else salary * 1.3
                                                               end new_salary
from employees
where department_id in (10,20,30)
order by department_id

• DECODE函数

select employee_id,last_name,department_id,decode(department_id,10,salary * 1.1,
                                                                20,salary * 1.2,
                                                                salary * 1.3) new_salary
from employees
where department_id in (10,20,30)

多表查询

• 笛卡尔集：
  • 1 省略连接条件；2 连接条件无效；3 所有表中的所有行相互连接。
  • 为了避免笛卡尔集，可以用where加入有效条件。
• 等值连接

select employees.employee_id,employees.department_id,department_name
from employees,departments
where employees.department_id = departments.department_id
--其别名
select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id
-- 三个表连接
select e.employee_id,e.department_id,d.department_name,l.city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id

• 非等值连接

select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal

内连接与外连接

• 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表和另一个表不匹配的行。
• 外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行，称为左（或右）外连接。没有匹配的行，结果表中相应的列为空（NULL）。外连接的where子句条件类似于内部链接，但连接条件中没有匹配行的表的列后面要加外连接运算符(+)。

--左外连接
select e.employee_id,e.department_id,d.department_name
from employees e,departments d
where e.department_id = d.department_id(+)
--右外连接
select e.employee_id,e.department_id,d.department_name
from employees e,departments d
where e.department_id(+) = d.department_id
-- 不能左右都连接，一个谓词只能引用一个外部联接的表

SQL99语法连接

• cross join table2 （叉集，错误）
• natural join table2 会以两个表中具有相同名字的列为条件创建等值链接，当数据类型不同时会出错。
• join table2 using col_name
• join table2 on table1.col_name = table2.col_name
• left | right | full couter join table2 on …

 select e.employee_id,e.department_id,d.department_name
from employees e join departments d
on e.department_id = d.department_id
-- 三表连接
 select e.employee_id,e.department_id,d.department_name,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
-- 左外
 select e.employee_id,e.department_id,d.department_name
from employees e left outer join departments d
on e.department_id = d.department_id
-- 满外连接
 select e.employee_id,e.department_id,d.department_name
from employees e full outer join departments d
on e.department_id = d.department_id

自连接

本表连接查询，相当与把本表复制一份。

-- 查询公司中员工‘chen’的manager的信息
select emp.last_name,manager.last_name,manager.salary,manager.email
from employees emp, employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name) = 'chen'
-- 子查询
select ‘Chen’||last_name,salary,email
from employees 
where employee_id  = (select manager_id
									 from employees 
									 where lower(last_name) = 'chen')

组函数

组函数作用于一组数据，并对一组数据返回一个值。处理多行数据返回一行
1 AVG / SUM
只能存放number类型
2 COUNT
适用于任何数据类型，仅仅计算非空的值
3 MAX / MIN
适用于varchar,date,number等任何类型
4 STDDEV (分析样本标准差)

select count(1),count(2),count(*) from employees; --返回进入记录行数（employees的行数）
-- 忽略空值
select avg(commission_pct),sum(commission_pct)/count(nvl(commission_pct,1)) from employees;
-- 去重
select count(distinct department_id) from employees;
-- Toronto这个城市中的平均工资
select ‘Toronto’,avg(salary)
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
and lower(l.city) = 'toronto'

分组数据

1 group by

• 在select查询中，所有未包含在组函数中的列，都应该出现在group by之中
• 不能做where子句中使用组函数
• 可以在having子句中使用组函数

-- 求各部门的平均工资
select department_id,avg(salary)
from employees
where department_id in (40,60,80)
group by departmen_id
-- 求不同部门不同工种的平均工资
select department_id,job_id,avg(salary)
from employees
where department_id in (40,60,80)
group by departmen_id,job_id
-- 组函数嵌套
select max(avg(salary))
from employees
group by department_id

2 having 过滤分组
不用像where一样和from挨着。

-- 求出各部门中平均工资大于6000的部门，以及其平均工资
select department_id,avg(salary)
from employees
having avg(salary) > 6000
group by departmen_id
order by department_id asc
-- 查询各管理者手下员工的最低工资
select manager_id, min(salary)
from employees
-- where manager_id is not null
group by manager_id
having min(salary) >= 6000 and manager_id is not null
-- 查询公司在1995-1998年间的雇佣人数
select count(*) "total",
		  count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
		  count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
		  count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
		  count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')

子查询

-- 单行子查询
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary) > (
								select min(salary)
								from employees
								where department_id = 50
								)
-- 查询平均工资最低的部门的信息
select *
from departments
where department_id = (
									select department_id
									from employees
									having avg(salary) = (
																	select min(avg(salary))
																	from employees
																	group by department_id   -- 嵌套分组函数应该有 GROUP BY，对分组求平均
																	)
									group by department_id   -- 修饰 avg(salary) ，先分组，再算各个组内平均工资
									)
-- 查询平均工资最低的部门的信息和平均工资
select d.*,(select avg(salary) from employees where department_id = d.department_id)
from departments d
where department_id = (
									select department_id
									from employees
									having avg(salary) = (
																	select min(avg(salary))
																	from employees
																	group by department_id   -- 嵌套分组函数应该有 GROUP BY，对分组求平均
																	)
									group by department_id   -- 修饰 avg(salary) ，先分组，再算各个组内平均工资
									)
-- 多行子查询   ANY / ALL
-- 查询各部门中平均工资比本部门平均工资高的员工的员工号，姓名和工资
select employee_id,last_name,salary
from employees e1
having salary > (
						select avg(salary)
						from employees e2
						where e1.department_id = e2.department_id		--内外表连接
						group by department_id
						)

创建和管理表（DDL）

DDL原子操作，不能回滚rollback，增删改可以回滚

• 常见的数据库对象
  
• 查看表
  select * from user_tables;
• 命名规则
  
• 创建表

-- 方法1
create table emp1(
id nuber(10),
name varchar2(20),
salary number(10,2),  -- 10位，其中后两位为小数
dire_date date
)
-- 方法2 (导入表)
create table emp2
as
select employee_id id,last_name name,hire_date,salary
from employees
where department_id = 80
-- where 1=2  --创建空表

• 更改表
  更改时只能改以后插入的数据，以前的不变。
  
• 删除/清空表
  drop table emp1;
  truncate table emp1;–表数据清空，结构不变
• 改变表名 (没有TABLE)
  rename emp2 to employees2;

数据处理（DML）

• 插入数据 insert into … value(…) / select …from …where …

-- 建立一个空表
create table emp1
as
select employee_id,last_name,hire_date,salary from employees
where 1=2 
--
insert into emp1
value(1001,aa,to_date(''1998-12-21,'yyyy-mm-dd'),10000)
-- 按value导入
insert into emp1(employee_id,last_name,hire_date)
value(1001,aa,to_date(''1998-12-21,'yyyy-mm-dd'))
-- 从已知表中导入数据
insert into emp1(employee_id,last_name,hire_date,salary)
select employee_id,hire_date,last_name,salary
from employees
where dapartment_id = 80
--手动输入(数字型不加引号，字符型加引号)
insert into emp1(employee_id,last_name,hire_date,salary)
value(&id,'&name',&salary,'&hire_date')

• 更新数据(可回滚) update … set … where …

update emp1
set salary = 12000
where employee_id=179
commit

• 删除数据(可回滚) delete from … where …

delete from employees
where department_id = (
										select department_id
										from departments
										where department_id is like '%Public%'
										)

• 更新和删除数据可能出现 数据完整性错误

数据库事务

• 一种逻辑处理单元，使数据从一种状态到另一种状态。
• 事务有以下部分组成：
  • 一个或多个DML语句
  • 一个DDL语句
  • 一个DCL语句
    
    – 未完待续