Oracle笔记
Oracle数据库
1、什么是数据库?
数据的存储。
2、Oracle数据库?
关系型数据库,使用二维表完成数据的存储。
关系型:表结构 —> 行: 一个数据对象 列:数据的属性
3、数据库的分类?
关系型: Oracle DB2 MySQL SQLServer
非关系型的NoSQL: Redis key-value、MongoDB 文档型/BSON 、HBase 文件…
安装
1、不建议使用中文路径、避免特殊字符
2、登陆
使用网页 localhost:8080/apex IE + http://
CMD窗口 sqlplus 注意:环境变量是否配置成功
用户名/密码 system/口令
建议使用oracle中的子用户: 权限更小 hr用户 需要解锁 参考笔记
3、使用PLSQL工具 安装路径不准出现中文/特殊字符(X86)
验证
hr用户 查询语句 ----> 107行结果
select * from employees;
一、SQL命令
DQL 数据的查询语言
1.简单查询
select 数据(列) from 表 不区分大小写!! 建议大写。
1、查询所有数据
select * from employees;
2、具体的列名
select 列名1,列名2 from 表名;
3、支持简单的运算 + - * /
select employee_id,first_name,salary*12 from employees;
4、列别名 as 关键字 可以省略
select employee_id,first_name,salary*12 (as) yearSalary from employees;
5、列的合并 ||
select first_name||last_name as name from employees;
select salary||1*10 as name from employees;
6、去重
select distinct salary from employees;
2.条件查询 where
1、 比较查询 > < = != >= <=
select * from employees where employee_id != 100;
select * from employees where employee_id >= 100;
2、 逻辑查询 and or not
--查询工资为 24000 或者 17000的员工
select * from employees where salary = 24000 or salary = 17000
--查询工资为17000 和 编号为 102 的员工
select * from employees where salary = 17000 and employee_id = 101;
注意: 一列中的多个条件用 or 一行中的用 and
-- 不等于 100
select * from employees where not employee_id = 100;
3、谓词
枚举 in 类似 or
查询工资 等于 24000 17000 10000 9000 的员工信息
select * from employees
where salary in(24000,17000,10000,9000);
区间 between...and... 相当于>= 和 <=
select * from employees
where salary between 10000 and 24000;
模糊 like
--查询first_name 以 S 开头的数据
select * from employees
where first_name like 'S%';
--查询first_name 以 S 开头的且总长度为5的数据
select * from employees
where first_name like 'S____';
select * from employees
where first_name like '%a_e%';
% 代表了 占位符 0~N _ 标识一位
空值 null 是空 is null
select * from employees
where commission_pct is null;
非空 is not null
select * from employees
where commission_pct is not null;
3. 排序 order by
对于查询结果进行排序
-- 排序 默认升序 按照提成排序
select *
from employees
where commission_pct is not null
order by commission_pct asc;
--降序 desc
select *
from employees
where commission_pct is not null
order by commission_pct desc;
-- 多列排序 按照提成 以及工资
select *
from employees
where commission_pct is not null
order by commission_pct desc,salary desc/asc;
4.函数
1. 内置函数 日期相关
sysdate
--函数 from 只是为了满足语法正确
--dual 虚表 / 哑表 只是为了满足语法正确 一行一列的表
select * from dual;
select sysdate from dual;
-- 进行数据类型的转换 String <---> Date
to_char();
to_date();
--应用在数据的插入
-- 把 String ---> Date
-- to_date('String字符串','日期格式');
select to_date('2000-01-01','yyyy-MM-dd') from dual;
--查询的展示
--to_char Date ----> String
--to_char(日期 ,'最终转换的数据格式')
select to_char(sysdate,'yyyy-mm-dd day') from dual;
--java yyyy-MM-dd HH:mm:ss
--数据库 yyyy-mm-dd hh:mi:ss
2. 组函数 针对一组数据的操作
max() 最大值 min() 最小值
avg() 平均 sum() 求和
count() 计数
--最大工资 最小工资
--列中的所有数据 一组数据的操作
select max(salary) from employees;
select min(salary) from employees;
select avg(salary) from employees;
--所有 *
select count(*) from employees;
--commission_pct 非空数据的统计
select count(commission_pct) from employees;
5.分组 group by
--查询每个部门的员工数
--将所有的员工 按照部门编号进行分组
select department_id,count(*) from employees
group by department_id;
--查询每个部门中每个工种的最大工资
--查询最大工资 按照部门和工种进行分组操作
select department_id,job_id,max(salary) from employees
group by department_id,job_id;
注意:
1、如果列不存在 group by 表达式之后,则无法书写在 select 关键字之后进行数据的展示
为什么?
分组条件 部门ID/Job_ID 展示的结果一定是唯一值。
select first_name 有可能会有两条数据 部门ID/Job_ID/最大工资完全相同 则姓名不同,
可能会在一行一列的数据格中出现多个选项。
2、组函数为什么可以书写在 select 关键字之后?
组函数对于一组数据进行操作,结果一定是单值唯一数据。
3、内置函数如果应用在 group by的条件中 也可以书写在 select 关键字之后
-- 1997 年每个月入职的员工数
--求 1997 年 再 按照月份分组
select to_char(hire_date,'mm'),count(*) from employees
where to_char(hire_date,'yyyy') = 1997
group by to_char(hire_date,'mm');
6. having判断
分组之后的条件再判断
-- 查询平均工资大于5000的部门ID -- 查询部门的平均工资且大于5000
-- 部门的平均工资
select department_id,avg(salary) from employees
group by department_id;
--再判断 大于 5000
select department_id,avg(salary) from employees
group by department_id
having avg(salary)>5000;
----查询1997年 每个月入职的员工数大于2人的月份以及数量 按降序排序。
--1、 获取1997年的员工
--2、 按照月份分组
--3、 判断人数大于2
--4、 排序
select to_char(hire_date,'mm'),count(*) from employees
where to_char(hire_date,'yyyy') = 1997
group by to_char(hire_date,'mm')
having count(*)>2
order by count(*) desc;
关键字??? SQL命令未正确结束 SQL语句语法关键字书写顺序错误
select from (表存在表连接) where group by having order by
查询 数据来自于 条件是 进行分组 在次筛选判断 排序
Day2
一、子查询
一条SQL语句去嵌套另外一条SQL语句
1、where子查询
一条SQL语句的执行结果是另外一条SQL语句的判断条件
-- 获取110号员工的部门名称
SELECT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 110);
select department_name from departments
where department_id = (SQL语句);
--值过多: 一行中有多余的列信息
--单行子查询返回多个行:一列中存在多个行数据
--获取 first_name 为 Steven 的员工的部门名称
select department_name from departments
where department_id in (select department_id from employees
where first_name ='Steven')
2、from子查询
表 实际是一条SQL语句的执行结果
--结果表
-- 求 年薪 大于 10 万的员工信息
select * from
(select first_name,salary*12 yearSalary from employees)
where yearSalary > 100000;
存在数据查询的嵌套
内部SQL语句的执行结果列(进行了运算等操作非原始数据)一定需要别名(外部的列进行的使用)
二、伪列 — Oracle独有
1、rowid
数据库进行数据存储时,会创建一个伪列rowid用来唯一标识一行数据在数据库中的存储位置。
如果不主动发起查询则不会进行数据的展示。
--rowid 唯一
select rowid from employees;
select * from employees
where rowid='AAAC9EAAEAAAABXABD';
重点:表别名!!!
--展示表中所有的数据以及rowid
--表别名 注意:没有as关键字
select rowid,e.* from employees e;
2. rownum
对于查询结果符合条件的数据进行编号
分页
rownum在进行编号的同时 需要结果数据满足条件判断
> >=非1 =非1 数据 永远不会满足条件!!!
需要对于查询结果预先编号 后执行条件判断 使用from子查询
select * from
(select rownum,e.* from employees e)
where rownum >10
-- 依然没有结果
rownum作为伪列 每一条SQL语句都拥有属于自己的rownum
where中使用的rownum 是外层SQL的结果,明确使用内层预先编号的rownum进行条件的判断
-- 起别名
select * from
(select rownum r,e.* from employees e)
where r >10;
--MySQL
select * from 表 where 条件 limit(1,5); 1 起始数据 5 取5条数据(到6结束)
查询工资排名3~5名(降序)的员工的employee_id、 first_name 和 salary
– SQL 预期结果 和 实际执行结果
– 有结果只能说明语法没错!!!!不代表你写对了!!!
select t2.* from
(select t1.*,rownum r from
(select employee_id,first_name,salary from employees
order by salary desc)t1) t2
where r between 3 and 5;
三、表连接
员工表 employees 部门表 departments
需求:展示员工的ID、first_name以及部门名称
表和表之间的数据是否存在对应关系(关联关系)
主键 / 外键
主键:表中对于一行数据的唯一标识。 (唯一、非空)
外键:表示多张表之间数据的关联关系 (外键列的数据一定来源于另外一张表的唯一标识)
语法: 关键字
()代表省略
1、 (内)连接 (inner) join ...表 on ...(连接条件 关联关系)
select e.*,d.* from employees e
inner join departments d
on e.department_id = d.department_id
2、左(外)连接 left (outer) join ...表 on ...(连接条件 关联关系)
--左外链接 107
select e.employee_id,e.first_name,e.department_id,
d.department_id,d.department_name
from employees e left join departments d
on e.department_id = d.department_id
3、右(外)连接 right (outer) join ...表 on ...(连接条件 关联关系)
--右外链接 122
select e.employee_id,e.first_name,e.department_id,
d.department_id,d.department_name
from employees e
right join departments d
on e.department_id = d.department_id
注意: 左右只是相对关键字的位置
4、全外 了解 123 full outer join ...(表) on ...(连接条件 关联关系)
条件判断
需求:展示工资大于10000的员工的ID、first_name以及部门名称
where salary > 10000
select e.employee_id,e.first_name,e.department_id,
e.salary,d.department_id,d.department_name
from employees e
join departments d
on e.department_id = d.department_id
where e.salary>10000
select e.employee_id,e.first_name,e.department_id,
e.salary,d.department_id,d.department_name
from (select * from employees where salary>10000) e
join departments d
on e.department_id = d.department_id
三张表怎么办?
A B C 表名
select * from A
inner join B
on a = b
inner join C
on b = c
--展示员工的 employee_id first_name department_name location_id street_address 、、、
select
e.employee_id,e.first_name,d.department_id,
d.department_name,d.location_id,l.street_address
from employees e
inner join departments d
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id
where
group by
having
order by ...
自(自己)连接
关联是同一张表中的不同列,将一张表想象成两张数据完全相同的表。
-- 求 员工的 employee_id,first_name,manager_id 以及领导的first_name
-- 领导本质上也是员工 数据也保存在员工表中
-- 关联关系 on manager_id 领导的ID = employee_id 员工的ID
-- employees 员工表
select *
from employees e1
inner join employees e2
on e1.manager_id = e2.employee_id
Day3
一、DDL 数据定义语言
数据库中 表的创建,删除(修改)
1、创建表
关键字
create table 名称()
表:组成
列 : 表可以存储的数据的信息 (列 名称/数据的类型)
create table 名称(
列名 数据类型,
列名 数据类型
);
数据类型
1、数字类型
number(位数)
number(5) 五位整数 99999
number(5,2) 五位数,两位是小数 999.99
Integer 整数 相当于 number(38,0)
2、字符类型
char(位数) 定长
varchar(位数) 变长
存储空间:
定长— char(5) 无论数据的实际长度是多少,都会在存储数据的时候开辟5位的长度
去存储数据,如果数据长度不够会补充空格。 存: ‘a’ 取:'a ’
变长— 根据数据的实际长度进行存储空间的开辟
中文不一定就是两个英文长度,取决于编码方式。
varchar2()!!!
Oracle独有的数据格式,在Oracle数据库中使用建议使用varchar2()!!!
3、日期类型
Date
create table my_table(
id number(32),
name varchar2(64),
age number(3),
sex char(2),
birthday date
);
存在:约束 (可选项)
create table 名称(
列名 数据类型 【约束】,
列名 数据类型 【约束】
);
约束:
1、主键约束
primary key —> 列中的数据一定是唯一且非空
2、非空约束 not null
3、唯一约束 unique
4、检查约束(check) 了解
对于存入数据的格式进行校验 (基本使用前端或者后端程序进行了处理)
check(email like ‘%@%’) / check(sex in(‘M’,‘F’))
5、外键约束
references 表名(列名)
外键数据来源于另外一张表的唯一约束(主键比较方便)
外键需要建立在数据的多方(存在外键的表就称之为子表,被外键引用的表称之为父表)
例子:班级和学生 部门和员工 用户和地址 分类和商品(结合生活常识)
创建表:需要先创建父表再创建子表
删除表:需要先删除子表再删除父表
外键的参照完整性:外键列的数据一定要来源于另一张表的唯一标识且已经存在。
create table my_table1(
id number(32) primary key,
name varchar2(64) not null,
phone char(11) unique,
email varchar2(64) ,
age number(3),
sex char(2),
birthday date
);
--学生表 班级表 关联关系
create table clazz(
cla_id integer primary key,
cla_name varchar2(24)
);
create table student(
id integer primary key,
name varchar2(32),
sex char(3),
cla_id integer references clazz(cla_id)
);
-- 记忆语法规范和关键字!!! (后续实际工作中绝大多数都会使用专业的工具)
2、删除表
drop table 表名;
注意:删除中需要先删除子表再删除父表。
级联删除: 强制删除父表(外键的关联关系),保留子表。
drop table clazz cascade constraint; 不建议使用,使表中存在大量的无用数据
不遵循外键的参照完整性约束。
3、修改表 alter … 基本没用
二、DML 数据的修改语言
增删改!!!!
注意 : 需要执行commit提交或rollback回滚。
1、添加
insert into 表名(列名,列名...) values(数据,数据....);
简写:向表中所有的列添加数据
insert into 表名 values(数据,数据....);
注意:数据的顺序必须和列的顺序一致
2、删除
delete from 表名 删除表中的所有数据。
delete from 表名 where 条件。
truncate table 表名; 删除表中的所有数据,但是不需要commit操作。
表的删除再创建,效率高!!!
3、修改
update 表名 set 列名=数据,列名=数据... where 条件
一般不建议去修改主键!!!
三、TCL 事务控制语言
一种数据库机制,保证业务操作的完整性,要么一起彻底的成功,要么一起彻底的失败。
commit / rollback : 没有针对一条DML操作语句,范围从上一次commit/rollback到本次执行过程中所有的DML操作语句一起生效。
转账(业务操作): 对于用户数据库余额数据的修改。
----> 多条SQL语句的共同执行结果 你的账户修改update(减钱) 目标账户修改update(加钱)
多条DML操作语句共用一个commit或者rollback语句
转账(){
update A -钱 -1000
//执行一半发生了未知错误 不可抗力
update B +钱 +1000
COMMIT/ROLLBACK
}
当执行完DML操作后数据存入回滚段中,commit提交当前回滚段中所有的数据,rollback撤销回滚段中所有的数据。
事务是保证多条SQL语句(DML操作)一起进行数据的提交或者回滚。
A : 原子性 — 业务操作(多条SQL语句),不可分割的原子操作。
C : 一致性 — 业务操作前后数据保持一致。
I : 隔离性 — 并发访问中操作者的事务相互独立
D : 持久性 — 操作完成后需要将数据保存入(持久化操作)数据库
补充:Oracle中的对象
一、sequence 序列
oracle 中的数字自增长策略
--创建
--默认创建 (参数) 默认从1开始每一次增加1
create sequecne 名称;
-- create sequence my_seq start with 23; 从几开始
-- increment by 每次增加几
-- minvalue maxvalue 最小和最大值
-- cycle 循环
-- Cache size 缓存大小 序列并不是使用的时候临时创建,而是一次创建多个 默认一次创建20。
--使用 名称.nextval
insert into student values(my_seq.nextval,'刘洋','F',1);
--销毁
drop sequecne 名称;
二、view 视图
一条SQL语句的查询结果。
--创建
--create view 名称 as (SQL语句)
create view my_view as (select rownum r,e.* from employees e)
--使用和表一模一样
--销毁
drop view 名称
视图: 简化操作(数据筛选屏蔽复杂的数据查询操作)、屏蔽敏感字段、对于分组再连接的SQL语句提升可读性
注意: 不能使用视图进行DML操作!!! 没有执行效率的提升。
三、index 索引
提升查询效率 类似目录的概念
--创建
-- create index 索引名称 on 表名(列名)
create index my_index on student(name);
--使用 自动使用
当你使用含有索引的列作为条件进行查询操作,会自动使用索引提升查询效率。
--销毁
drop index 名称;
索引不要随意创建,会降低增删改效率,Oracle会自动为主键添加唯一索引。
Cache size 缓存大小 序列并不是使用的时候临时创建,而是一次创建多个 默认一次创建20。
–使用 名称.nextval
insert into student values(my_seq.nextval,‘刘洋’,‘F’,1);
–销毁
drop sequecne 名称;
##### 二、view 视图
> 一条SQL语句的查询结果。
```sql
--创建
--create view 名称 as (SQL语句)
create view my_view as (select rownum r,e.* from employees e)
--使用和表一模一样
--销毁
drop view 名称
视图: 简化操作(数据筛选屏蔽复杂的数据查询操作)、屏蔽敏感字段、对于分组再连接的SQL语句提升可读性
注意: 不能使用视图进行DML操作!!! 没有执行效率的提升。
三、index 索引
提升查询效率 类似目录的概念
--创建
-- create index 索引名称 on 表名(列名)
create index my_index on student(name);
--使用 自动使用
当你使用含有索引的列作为条件进行查询操作,会自动使用索引提升查询效率。
--销毁
drop index 名称;
索引不要随意创建,会降低增删改效率,Oracle会自动为主键添加唯一索引。