oracle
数据库种类
-
oracle 性能最强
-
mysql 轻量级数据库
-
SqlServer 了解即可
为什么学数据库?
-
后端使用编程语言操作数据库 数据持久化
数据库优势
-
数据独立
-
数据库 和 后台应用独立
-
-
减少数据冗余
-
范式(设计)
-
约束
-
-
安全
-
并发
-
锁
-
-
性能
-
数据结构化
-
读写数据方便
-
-
...
用户对象
创建/删除
-- 先以管理员登录 system/system
-- 创建用户
create user test1 identified by test1
-- 删除用户
drop user test1 cascade;
查看用户/切换用户
-- 当前用户
show user;
-- 所有用户
select * from all_users;
-- 切换用户
conn test1/teset;
授权/查看权限/撤回权限
-- 授权
grant connect,resource to test1;
-- 查看权限
select * from session_privs;
-- 撤回权限
revoke connect,resource from test;1
表对象
dql
单表
基本查询
-- 设置列宽
col 列名 for a10
-- 基本查询
select last_name from s_emp;
-- 运算查询
select last_name,salary*14 from s_emp;
-- 别名
select last_name,salary*14 as yearSalary from s_emp;
select last_name,salary*14 yearSalary from s_emp;
-- 拼接 注意,数据库中的字符串,需要使用单引号括起来
select first_name || ' ' || last_name as name from s_emp;
-- 重复数据 此处对name去重
select distinct name from s_dept;
-- 此处代表name和region_id完全相同才去重
-- 但是本表有 约束 name和region_id不能完全相同
select distinct name,region_id from s_dept;
排序
-- 默认按工资升序
select * from s_emp
order by salary [asc]
-- 按工资降序
select * from s_emp
order by salary desc
-- 按入职日期 (日期可以直接比较大小)
select * from s_emp
order by start_date
-- 注意: 原表的1号数据不对
-- 删除1号数据
delete from s_emp where id = 1;
-- 手动插入数据
-- 在终端插入 设置会话
alter session set nls_language = "english";
INSERT INTO s_emp VALUES (1, 'Velasquez', 'Carmen', 'cvelasqu', '03-MAR-90' , NULL, NULL, 'President',50, 2500, NULL);
-- 提交数据
commit;
-- 多列排序 此时先按照薪水降序,如果薪水相同,再按名字顺序
select * from s_emp
order by salary desc,last_name asc
条件查询
-- 精确查询 =
-- 条件关键字where
select * from s_emp
where id = 20
-- 拼接条件 or 或者 有一个满足
select * from s_emp
where id = 20 or id = 1 or id = 2;
select * from s_emp
where id = 20 or MANAGER_ID = 1
-- 拼接条件 and 同时满足 薪水时1450并且名字还得时Ngao
select * from s_emp
where salary = 1450 and last_name = 'Ngao'
-- 范围条件 > <
-- between and 优化写法 在范围中间
select * from s_emp
where salary > 1450
select * from s_emp
where id <= 10
select * from s_emp
where id <= 10 and id >= 5
select * from s_emp
where id between 5 and 10
select * from s_emp
where id in(1,5,7,9,11)
-- 判空 is null 注意不是 = null
-- 查找没有部门的员工
select * from s_emp
where MANAGER_ID is null;
select * from s_emp
where MANAGER_ID is not null;
-- 模糊查询 注意大小写
-- %代表0-多个字符 N% N开头的任意名字
-- %n 以n结尾的任意名字
select * from s_emp
where last_name like 'N%'
select * from s_emp
where last_name like '%n'
select * from s_emp
where last_name like '%n%'
-- _ 代表单个字符 此处占4个字符
select * from s_emp
where last_name like '____'
-- 倒数第二位是m的名字
select * from s_emp
where last_name like '%m_'
-- 转义字符
-- escape '\' 代表 \ 是转义字符
-- \_ 代表此_ 没有占位符含义,此处只是单纯字符
select * from s_emp
where last_name like '%\_%' escape '\'
-- 插入一条带_d
insert into s_emp(id,last_name) values (26,'bri_up');
commit;
函数
哑表
select lower(last_name)
from S_EMP where id = 1;
-- 哑表(oracle -- sql的注释
select * from dual;
select 1 from dual;
select 1+1 from dual;
select lower('BRIUP') from dual;
单行函数
字符函数
-- ascii 97 查单个字符的ascii
select ascii('A') from dual;
-- concat(a,b) 连接字符串a和b 参数只能两位
-- 如果需要多位 可以函数嵌套
select concat(concat('%','a'),'%')
from dual;
-- instr(string,substring) 找substring在string的位置
-- 注意: oracle的下标从1开始
-- 6代表从第六个位置开始找substring 此处索引为8
select instr('hello world','o',6)
from dual;
-- length(string) 长度
select length('hello world')
from dual;
-- upper转大写
select upper('briup') as value
from dual;
-- initCap(string)
select concat('set',initcap('name'))
from dual;
-- trim 去除字符串左右空格(默认)
select length(trim(' hello '))
from dual;
-- 也可以去除两边指定的字符
select trim('=' FROM '=hello=')
from dual;
-- replace(string,oldchar,newchar) 替换
select replace('briup','i','o')
from dual;
-- substr(string,start,length) 切割字符串
select substr('1990-1-1',1,4)
from dual;
数字函数
-- round(string,number) 四舍五入 number需要保留的小数位数
-- 3.14
select round(3.1415926,2)
from dual;
-- 3
select round(3.1415926,0)
from dual;
-- 30
select round(31.1415926,-1)
from dual;
-- trunc去掉小数 不进位
select trunc(3.9)
from dual;
日期函数
-- sysdate oracle的当前日期
select sysdate
from dual;
-- 明天的此时 运算单位是天
select sysdate + 1
from dual;
-- 未来一个小时
select sysdate + 1/24
from dual;
-- 改变会话语言
alter session set nls_language = 'simplified chinese';
-- 中文时间
select sysdate
from dual;
-- 根据当前月份计算 相隔月份
select months_between(sysdate + 31, sysdate)
from dual;
-- 当前月的最后一天
select last_day(sysdate)
from dual;
-- 下个星期五 是哪天
select next_day(sysdate,'星期五')
from dual;
转换函数
-- to_char
-- 数字 -> 字符 to_char()
select to_char(10100.45,'L999,999.00')
from dual;
-- 日期 -> 字符 (默认格式 日-月-缩写年份)
select to_char(sysdate+4/24,'yyyy-mm-dd-DAY hh24:mi:ss') value
from dual;
select to_char(sysdate+4/24,'DAY-yyyy-mm-dd hh24:mi:ss') value
from dual;
-- 字符串 -> 数字 Integer.parseInt("99")
select to_number('99')
from dual;
-- 字符串 -> 日期
-- to_char(日期,'格式')
-- to_date('日期字符串','格式')
select to_date('1999-1-2 11:25:01','yyyy-dd-mm hh24:mi:ss')
from dual;
select to_date('1999-5月-2','yyyy-month-dd')
from dual;
其他函数
-- nvl(空置列,替换的值) 替换空值
select LAST_NAME,nvl(COMMISSION_PCT,0)
from S_EMP;
-- 条件分支函数
-- 750 最低工资
-- 2500 最高工资
-- 其他 中等工资
select LAST_NAME,
decode(SALARY,750,'最低工资',2500,'最高工资','中等工资')
from S_EMP;
-- 通用语法 case when
SELECT
id,
( CASE salary WHEN 2500 THEN '高产' WHEN 750 THEN '低产' ELSE '中产' END ) result
FROM s_emp
分组函数
-- 使用聚合函数 25个人一组
select avg(SALARY)
from S_EMP;
-- 部门相同的是一组
select dept_id,avg(SALARY),max(SALARY),min(SALARY),sum(SALARY),count(SALARY)
from S_EMP
group by DEPT_ID
order by DEPT_ID;
-- 该方式是否可行
select dept_id,avg(SALARY),max(SALARY),min(SALARY),sum(SALARY),count(SALARY)
from S_EMP
-- 所有员工平均工资 默认一个组
select avg(SALARY)
from S_EMP;
-- 员工数量 默认一个组
select count(id)
from s_emp
-- 思考,如何统计41号部门的平均工资?
-- DEPT_ID 不是分组
select DEPT_ID,avg(salary)
from S_EMP
where DEPT_ID = 41
group by DEPT_ID
-- 统计每个部门中,每个职位的人数有多少(相同部门、相同职位的人数统计)
select DEPT_ID,TITLE,count(ID)
from S_EMP
group by DEPT_ID,TITLE
order by DEPT_ID;
group by
-- 统计每门课程的选课人数
select SUBJECT,count(ID)
from T_TEST
group by SUBJECT;
-- 统计每个班级中的选课人数 (以及课程)
select CLASS,SUBJECT,count(ID)
from T_TEST
group by CLASS,SUBJECT
order by CLASS;
having 替代where的用法
select DEPT_ID,avg(SALARY)
from S_EMP
where DEPT_ID = 41
group by DEPT_ID;
select DEPT_ID,avg(SALARY)
from S_EMP
group by DEPT_ID
-- having 和group by一起使用 做条件筛选
having DEPT_ID = 41;
-- 平均工资 > 1000的部门 where后面不能出现组函数 此时使用having
select DEPT_ID,avg(SALARY)
from S_EMP
where avg(SALARY) > 1000
group by DEPT_ID
having
-- 平均工资 > 1000的部门 where后面不能出现组函数 此时使用having
select DEPT_ID,avg(SALARY)
from S_EMP
-- where avg(SALARY) > 1000 where紧跟在表后面
group by DEPT_ID
-- having 紧跟在group by后面 不能单独使用
having avg(SALARY) > 1500;
-- where只是不能使用组函数 可以和having同时出现
select DEPT_ID,avg(SALARY)
from S_EMP
where DEPT_ID = 50
group by DEPT_ID
-- having 紧跟在group by后面 不能单独使用
having avg(SALARY) > 1500;
-- 查询s_emp表中部门的总工资大于等于4000的部门
select DEPT_ID,sum(SALARY)
from S_EMP
group by DEPT_ID
having sum(SALARY) > 4000;
-- 查询s_emp表中部门的总工资大于等于4000的部门
-- 此处报错 last_name不是分组函数
select DEPT_ID,LAST_NAME,sum(SALARY)
from S_EMP
group by DEPT_ID,LAST_NAME
注意
-
组函数出现的位置
-
不能出现在where后面 和 group by后面
-
可以出现在select having order by后面
-
-
如果select,having语句中出现了组函数,那么没有被组函数修饰的列,那么该列必须放在group by后面, 否则报错(不是分组函数)
例如,查询s_emp表中,部门的平均工资大于等于1400的部门,并且显示出这些部门的名字,同时按照部门编号进行排序
-- 套路: 拆分需求
-- 第一步 查询每个部门的平均工资
select avg(SALARY)
from S_EMP
group by DEPT_ID;
-- 第二步 查询 平均工资 >= 1400 的部门
select DEPT_ID, avg(SALARY)
from S_EMP
group by DEPT_ID
having avg(SALARY) >= 1400;
-- 第三步 显示出这些部门的名字
select e.DEPT_ID, avg(e.SALARY), d.NAME
from S_EMP e,
S_DEPT d
group by e.DEPT_ID
-- 此处报错
having avg(e.SALARY) >= 1400
and e.DEPT_ID = d.ID;
-- 第四步 注意 select having后面没有被组函数修饰的列 放group by后面
select e.DEPT_ID, avg(e.SALARY), d.NAME
from S_EMP e,
S_DEPT d
where e.DEPT_ID = d.ID
group by e.DEPT_ID, d.NAME
having avg(e.SALARY) >= 1400;
-- 第五步 按照部门编号排序
select e.DEPT_ID, avg(e.SALARY), d.NAME
from S_EMP e,
S_DEPT d
where e.DEPT_ID = d.ID
group by e.DEPT_ID, d.NAME
having avg(e.SALARY) >= 1400
order by DEPT_ID;
注意: 完整sql的执行顺序
-
from [s_emp e, s_dept d]
-
where [e.dept_id = d.id]
-
group by [e.dept_id,d.name]
-
[having中的组函数结果][avg(e.salary)]
-
having[avg(e.salary) >= 1400]
-
[select中的 函数/表达式结果] [avg(e.salary)]
-
select ...
-
order by [dept_id]
order by的优先级最低
查询s_emp表中最大的工资数,并且显示出这个最大工资的员工的名字
-- 该写法无法查出正确结果
select last_name,max(SALARY)
from S_EMP
group by last_name;
-- 第一步: 先查出整张表的最大工资数 2500
select max(SALARY)
from s_emp;
-- 第二步: 查工资数为2500的员工
select * from S_EMP
where SALARY = 2500;
-- 第三步: 结果第一条和第二条
select e1.LAST_NAME,e1.SALARY
from S_EMP e1, S_EMP e2
group by e1.LAST_NAME,e1.SALARY
-- 去除笛卡尔积 并且将以上分组出现的25条结果 变成一条结果
having e1.SALARY = max(e2.SALARY);
例如,查询s_emp表每个部门的最大工资数,
-- 并且显示出这个最大工资的员工的名字,
-- 以及该部门的名字、该部门所属区域,并且使用部门编号进行排序
-- 第一步 s_emp表每个部门的最大工资数
select DEPT_ID, avg(SALARY)
from S_EMP
group by DEPT_ID;
-- 第二步 显示出这个最大工资的员工的名字 (结果不对)
select e1.DEPT_ID, e1.LAST_NAME, e1.SALARY
from S_EMP e1,
s_emp e2
group by e1.DEPT_ID, e1.LAST_NAME, e1.SALARY
having e1.SALARY = max(e2.SALARY);
-- 第三步: 显示出每个部门最大工资的员工的名字 (员工在所处部门做比较)
select e1.DEPT_ID, e1.LAST_NAME, e1.SALARY
from S_EMP e1,
s_emp e2
-- 两张表的数据 去重 同一个部门的值
where e1.DEPT_ID = e2.DEPT_ID
group by e1.DEPT_ID, e1.LAST_NAME, e1.SALARY
-- 此时的max(e2.SALARY) 基于e1.DEPT_ID = e2.DEPT_ID
-- 表1和表2相同部门的最大值
having e1.SALARY = max(e2.SALARY)
order by e1.DEPT_ID;
-- 第四步: 以及该部门的名字、该部门所属区域,并且使用部门编号进行排序
select e1.DEPT_ID, e1.LAST_NAME, e1.SALARY,d.NAME,r.NAME
from S_EMP e1,
s_emp e2,
s_dept d,
S_REGION r
where e1.DEPT_ID = e2.DEPT_ID
and e1.DEPT_ID = d.ID
and d.REGION_ID = r.ID
group by e1.DEPT_ID, e1.LAST_NAME, e1.SALARY,d.NAME,r.NAME
having e1.SALARY = max(e2.SALARY)
order by e1.DEPT_ID;
多表
等值连接
-- 等值连接 员工表和部门表
-- 通过部门id进行表的联系
select e.LAST_NAME,e.DEPT_ID,d.NAME
from S_EMP e,S_DEPT d
where e.DEPT_ID = d.ID
非等值连接 (此处通过范围去笛卡尔积)
-- 非等值连接
-- 范围去笛卡尔积
select e.LAST_NAME,e.SALARY,grade.GRADENAME
from S_EMP e,salgrade grade
where e.SALARY
between grade.LOSAL and grade.HISAL
外连接
-- 等值连接 多余的数据无法查询
select e.LAST_NAME, e.DEPT_ID, d.NAME
from S_EMP e,
S_DEPT d
where e.DEPT_ID = d.id;
-- 左外连接 查所有员工 即使没有部门
select e.LAST_NAME, e.DEPT_ID, d.NAME
from S_EMP e
left outer join S_DEPT d
on e.DEPT_ID = d.id;
-- 简写方式
select e.LAST_NAME, e.DEPT_ID, d.NAME
from S_EMP e,S_DEPT d
where e.DEPT_ID = d.ID(+);
--右外连接 查所有部门 即使没有员工
select e.LAST_NAME, e.DEPT_ID, d.NAME
from S_EMP e
right outer join S_DEPT d
on e.DEPT_ID = d.id;
-- 简写方式
select e.LAST_NAME, e.DEPT_ID, d.NAME
from S_EMP e,S_DEPT d
where e.DEPT_ID(+) = d.ID;
--全外连接 查所有部门和所有员工
select e.LAST_NAME, e.DEPT_ID, d.NAME
from S_EMP e
full outer join S_DEPT d
on e.DEPT_ID = d.id;
自关联 也属于多表查询 表公用了同一张
-- 查询每个员工的名字以及员工对应的管理者的名字
-- e1是员工表
-- e2是经理表
select e1.LAST_NAME,e1.MANAGER_ID,e2.LAST_NAME
from S_EMP e1,S_EMP e2
where e1.MANAGER_ID = e2.ID;
-- 参考多表查询
select e.LAST_NAME,d.NAME
from S_EMP e,S_DEPT d
where e.DEPT_ID = d.ID
子查询
定义: 一条sql结果充当另一条sql的条件 或者虚拟表
-- 子查询 一条sql的结果 是另一条sql的条件
select LAST_NAME, SALARY
from S_EMP
where SALARY = (select max(salary) from S_EMP);
-- 子查询 一条sql的结果 充当了虚拟表
select e1.LAST_NAME, e1.SALARY
from S_EMP e1,
(
select max(salary) avgSalary
from S_EMP
) temp -- temp是临时表
where e1.SALARY = temp.avgSalary
查询工资比Smith工资高的员工信息
-
先用结果值计算 最终用sql替换结果
-- 第一步 smith工资 940
select SALARY
from S_EMP
where LAST_NAME = 'Smith';
-- 第二步 工资比Smith工资
select *
from S_EMP
where SALARY > 940;
-- 第三步 sql替换940
select LAST_NAME,SALARY
from S_EMP
where SALARY > (
select SALARY
from S_EMP
where LAST_NAME = 'Smith'
)
查询平均工资比 41号部门的平均工资 高 的部门中员工的信息
-- 1.每个部门的平均工资
select DEPT_ID, avg(SALARY)
from S_EMP
group by DEPT_ID;
-- 2.41号部门的平均工资 1247.5
select DEPT_ID, avg(SALARY)
from S_EMP
group by DEPT_ID
having DEPT_ID = 41;
-- 3.所有平均工资 > 1247.5 31 32 35 50 33 10
select DEPT_ID, avg(SALARY)
from S_EMP
group by DEPT_ID
having avg(SALARY) > 1247.5;
-- 4.位于以上部门的员工信息
select LAST_NAME, SALARY
from S_EMP
where DEPT_ID in (31, 32, 35, 50, 33, 10);
-- 5.用sql替换值
select LAST_NAME, SALARY
from S_EMP
where DEPT_ID in (
select DEPT_ID
from S_EMP
group by DEPT_ID
having avg(SALARY) > (
select avg(SALARY)
from S_EMP
group by DEPT_ID
having DEPT_ID = 41
)
);
练习1:查询员工信息,这些员工的工资要比自己所在部门的平均工资高, 同时显示部门的名称以及所在地区
-- 1.所有部门的平均工资
select DEPT_ID, avg(SALARY)
from S_EMP
group by DEPT_ID;
-- 2.工资要比自己所在部门的平均工资高
select e.SALARY, temp.avgSalary
from S_EMP e,
(
select DEPT_ID, avg(SALARY) avgSalary
from S_EMP
group by DEPT_ID
) temp
where e.DEPT_ID in (temp.DEPT_ID)
and e.salary > temp.avgSalary;
-- 3.同时显示部门的名称以及所在地区
select e.SALARY, temp.avgSalary, d.NAME, r.NAME
from S_EMP e,
(
select DEPT_ID, avg(SALARY) avgSalary
from S_EMP
group by DEPT_ID
) temp,
S_DEPT d,
S_REGION r
where e.DEPT_ID in (temp.DEPT_ID)
and e.salary > temp.avgSalary
and e.DEPT_ID = d.ID
and d.REGION_ID = r.ID
练习2:查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显示当前部门的平均工资以及部门的名字和所在地区
-- 1.Ngao所在部门 41
select DEPT_ID
from S_EMP
where LAST_NAME = 'Ngao';
-- 2.Ngao所在部门平均工资 1247.5
select avg(SALARY)
from S_EMP
group by DEPT_ID
having dept_id = 41;
-- 3.工资比 Ngao所在部门平均工资 要高员工信息 (条件之一)
select *
from S_EMP
where SALARY > 1247.5;
-- 4.所在部门平均工资高于Ngao所在部门平均工资 要高员工信息
-- 平均工资 > 1247.5的部门id
select DEPT_ID, avg(SALARY)
from S_EMP
group by DEPT_ID
having avg(SALARY) > 1247.5;
-- 5.连接条件3和4
select DEPT_ID, LAST_NAME, SALARY
from S_EMP
where SALARY > 1247.5
and dept_id in (31, 32, 35, 50, 33, 10);
-- 6.替换sql
select DEPT_ID, LAST_NAME, SALARY
from S_EMP
where SALARY > (
select avg(SALARY)
from S_EMP
group by DEPT_ID
having dept_id = (
select DEPT_ID
from S_EMP
where LAST_NAME = 'Ngao'
)
)
and dept_id in (
select DEPT_ID
from S_EMP
group by DEPT_ID
having avg(SALARY) > (
select avg(SALARY)
from S_EMP
group by DEPT_ID
having dept_id = (
select DEPT_ID
from S_EMP
where LAST_NAME = 'Ngao'
)
)
);
-- 7.显示当前部门的平均工资
select e.DEPT_ID, e.LAST_NAME, e.SALARY, temp.avgSalary
from S_EMP e,
(
select DEPT_ID, avg(SALARY) avgSalary
from S_EMP
group by DEPT_ID
having avg(SALARY) > (
select avg(SALARY)
from S_EMP
group by DEPT_ID
having dept_id = (
select DEPT_ID
from S_EMP
where LAST_NAME = 'Ngao'
)
)
) temp
where e.SALARY > (
select avg(SALARY)
from S_EMP
group by DEPT_ID
having dept_id = (
select DEPT_ID
from S_EMP
where LAST_NAME = 'Ngao'
)
)
and e.dept_id in (temp.DEPT_ID);
-- 8.部门的名字和所在地区
select e.DEPT_ID, e.LAST_NAME, e.SALARY, temp.avgSalary, d.NAME, r.NAME
from S_EMP e,
(
select DEPT_ID, avg(SALARY) avgSalary
from S_EMP
group by DEPT_ID
having avg(SALARY) > (
select avg(SALARY)
from S_EMP
group by DEPT_ID
having dept_id = (
select DEPT_ID
from S_EMP
where LAST_NAME = 'Ngao'
)
)
) temp,
S_DEPT d,
S_REGION r
where e.SALARY > (
select avg(SALARY)
from S_EMP
group by DEPT_ID
having dept_id = (
select DEPT_ID
from S_EMP
where LAST_NAME = 'Ngao'
)
)
and e.dept_id in (temp.DEPT_ID)
and e.DEPT_ID = d.ID
and d.REGION_ID = r.ID;
结果运算
-- 并集 union
-- 左连接 联合 右连接 27
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID = d.ID(+)
union -- 并集
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID(+) = d.ID;
-- 连接显示所有结果 52
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID = d.ID(+)
union all -- 并集
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID(+) = d.ID;
-- minus 减去相同的结果 1
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID = d.ID(+)
minus
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID(+) = d.ID;
-- intersect 交集 25
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID = d.ID(+)
intersect
select e.*,d.*
from s_emp e,S_DEPT d
where e.DEPT_ID(+) = d.ID;
伪列
-
=1
-
>0
-
< 任何值
-- 前10条
select ROWNUM,ID,LAST_NAME
from S_EMP
where ROWNUM <= 10;
-- 只能 > 0
select ROWNUM,ID,LAST_NAME
from S_EMP
where ROWNUM > 0;
-- = 1
select ROWNUM,ID,LAST_NAME
from S_EMP
where ROWNUM = 1;
ddl
ddl是不需要提交事务 commit (自动提交)
表组成
-
主键 唯一标识某行
-
唯一 非空
-
-
外键 标识表和表的关系
-
逻辑外键 只有名字,没有真实的物理关系
-
随意删除
-
-
物理外键 真实的外键关系
-
如果表被引用 不可以删除
-
-
规则
-
一对一 外键可以放任一一方
-
一对多
-
外键放在多的一方
-
一: 主表
-
多: 从表
-
-
多对多
-
外键中间表(桥表)
-
id student_id(外键) teacher_id(外键) 1 1 1 2 2 1 3 1 2
-
-
-
范式
-
第一范式
-
列不能再分割
-
-
第二范式
-
第一范式的基础,非主键列依赖主键列(包括间接依赖)
-
-
第三范式
-
第二范式的基础,非主键列依赖主键列(直接依赖 不能传递依赖)
-
反三范式 违反了范式 在数据库阶段是常用的一种手段 (虽然数据冗余,但是对比大量多表查询 性能得到优化)
表类型
-- 建表
create table t_test(
-- () 中写列
-- 列名 类型
-- 数字类型 整数 小数
-- 字符类型 字符串
-- 日期类型 日期
id number -- 整数
);
-- 删除表
drop table t_test;
-
数字 number(位数,小数位数)
-
字符
-
char(长度) 固定长度
-
varchar(长度) 通用(mysql使用) 可变长度 2000
-
varchar2(长度) 推荐使用 可变长度 4000
-
long 大文本类型
-
-
日期
-
date 日期
-
timestamp 日期 更精确
-
-
大字节 blob
-
存任意字节 图片 视频等 4G
-
-
大文本 clob
-
大文本 4G
-
-- 建表
create table t_test(
-- () 中写列
-- 列名 类型
-- 数字类型 整数 小数
-- 字符类型 字符串
-- 日期类型 日期
id number, -- 整数
name varchar2(30), -- 字符串
startTime date, -- 日期
birthday timestamp -- 更精确日期
);
-- 删除表
drop table t_test;
表的约束
-
种类
-
primary key 主键
-
自带非空和唯一特征
-
-
not null 非空约束
-
unique 唯一约束
-
check 检查约束 范围取值
-
foreign key 外键约束
-
出现在多表
-
-
-
位置
-
列级约束 约束放在列的后面
-
行级约束 约束放在列的不同行
-
行级约束不能写not null
-
联合约束
-
-
列级约束和行级约束可以同时使用
创建表
-- 建表
create table t_test(
-- () 中写列
-- 列名 类型
-- 数字类型 整数 小数
-- 字符类型 字符串
-- 日期类型 日期
id number, -- 整数
name varchar2(30), -- 字符串
startTime date, -- 日期
-- birthday timestamp -- 更精确日期
);
-- 删除表
drop table t_test;
-- 查询数据
select * from t_test;
-- 建表的过程需要给列定义约束
-- 约束: 限制列
create table t_test(
id number primary key, -- id非空唯一
name varchar2(20) not null unique, -- 非空 唯一
age number check ( age >= 0 and age <= 200 ),
gender char(1) check ( gender in('m','f') ),
birthday date
);
-- 列级外键约束 在多表环境
-- 1.外键放在多的一方
-- 2.外键参照主表的主键
create table t_customer(
id number primary key ,
customer_name varchar2(20) not null unique
);
create table t_order(
id number primary key ,
name varchar2(20) not null unique ,
customer_id number references t_customer(id)
);
-- 行级约束
create table t_customer
(
id number,
customer_name varchar2(20),
primary key (id),
unique (customer_name) -- 非空约束不能放行级
);
create table t_order
(
id number,
name varchar2(20),
customer_id number,
primary key (id),
unique (name), -- 非空约束不能放行级
foreign key(customer_id) references t_customer(id)
);
-- 行级约束 可以声明联合约束
create table t_student(
id number primary key , --列级约束
class varchar2(20) not null ,
name varchar2(20) not null,
unique (class,name) -- 行级的联合约束
);
-- 默认的约束名字
drop table t_customer cascade constraints;
select table_name from user_tables;
select constraint_name from user_constraints;
create table t_customer
(
id number constraint t_customer_id_pk primary key,
customer_name varchar2(20) constraint t_customer_name_nn_un not null unique
);
create table t_order
(
id number constraint t_order_id_pk primary key,
name varchar2(20) constraint t_order_name_nn_un not null unique,
customer_id number constraint t_customer_id_fk references t_customer (id)
);
删除表
-
如果表没有被引用 可以直接删除
-
如果表被引用(主表)
-
先删从表 再删主表
-
先删除主表和约束,再删从表
-
-- 是否有外键 删表 如果表被引用 无法删除
-- 此时t_customer被t_order引用 t_customer不能直接直接删除
drop table t_customer;
-- 先删从表 再删主表
-- 该表没有被引用 可以删除
drop table t_order;
-- 此时t_customer可以删除 因为引用的表删除了
drop table t_customer;
-- 可以级联删除主表(删表和约束)
drop table t_customer cascade constraints;
-- 再删从表
drop table t_order;
修改表
-- 改表名字
-- rename 原名字 to 新名字
-- rename t_user to s_user;
-- rename s_user to t_user;
-- 新增列
-- alter table 表名 add (列名 列类型,列名 列类型)
alter table T_USER
add (birthday date, -- ()可写一列或者多列
age number);
-- 改列的名字
-- alter table T_USER rename column(表的结构) 原名字 to 新名字
alter table T_USER rename column
birthday to birth;
-- 改列的类型 modify 修饰
-- alter table T_USER modify (列名 类型,列名 类型)
alter table T_USER
modify (birth timestamp, -- 换类型
age number(8));
-- 改长度
-- 删除列
-- alter table T_USER drop (列名1,列名2)
alter table T_USER
drop (birthday, age);
-- 添加约束 此处相当于表级约束
-- alter table T_USER add constraint 约束名字 check (age >0 and age < 10)
alter table T_USER
add constraint user_age_check
check ( age > 0 and age < 150 );
-- 禁用约束 disable
alter table T_USER
disable constraint user_age_check;
-- 开启约束 enable 注意不能有违反约束数据,否则无法开启
alter table T_USER
enable constraint user_age_check;
-- 删除约束
alter table T_USER
drop constraint user_age_check;
截断表
-
删除数据 不需要提交事务
-
如果表支持自增 自增id会重新开始 (序列对象不是)
-
truncate属于ddl delete属于dml
delete from t_user;
commit;
truncate table t_user;
dml
单表
插入 insert
记得提交事务
-- 学习指令建表 插入数据 dml(掌握)
-- ddl
create table t_user
(
id number primary key,
name varchar2(20) not null,
gender char(1) check ( gender in ('f', 'm') ),
email varchar2(30)
);
select *
from t_user;
drop table t_user;
-- 插入数据 insert into 表名(列) values (列值)
-- 插入数据需要手动提交事务 commit;
-- 没有提交事务,该数`据只在当前会话有效
insert into t_user(id, name, gender, email)
values (1,'tom','m','vanse@gmail.com');
-- 可以指定列
insert into t_user(id, name, gender)
values (2,'lucy','m');
commit;
-- 可以省略列 默认按照顺序插入全列值
insert into t_user values (3,'briup','f','vanse@gmail.com');
commit;
select * from t_user;
-- 批量插入 oracle
insert all
into t_user values (4,'briup1','f','vanse@gmail.com')
into t_user values (5,'briup2','f','vanse@gmail.com')
select 1 from dual;
commit;
修改数据 update
-- update 表 set 列 = 列值 [条件]
select *
from t_user;
-- 修改所有名称为briup 默认修改所有name
update t_user
set name = 'briup';
commit;
-- 修改某值
update t_user
set name = 'vanse'
where id = 1;
commit;
-- 修改某id 的多列数据
update T_USER
set NAME = 'tom',
GENDER = 'f'
where id = 2;
commit;
删除数据 delete
select * from t_user;
-- delete from 表名 [条件] 不加条件,全部删除
-- 删除单条
delete from T_USER where id = 1;
commit;
-- 删除多条
delete from T_USER where id in (2,3);
commit;
-- 删除全部
delete from T_USER;
commit;
多表
-
添加数据
-
主表随意添加
-
从表的外键必须是主表存在的值
-
-
修改数据
-
主表 除了id可以随意改
-
从表 外键必须是主表存在的值
-
-
删除数据
-
主表 被引用的数据不能删除
-
从表 可以随意删除
-
create table t_customer
(
id number,
name varchar2(20)
constraint customer_name_nn not null,
constraint customer_id_pk primary key (id)
);
create table t_order
(
id number,
price number,
customer_id number,
constraint order_id_pk primary key (id),
constraint order_cid_fk foreign key (customer_id) references t_customer (id)
);
select table_name from user_tables;
-- 插入数据
-- 先插入主表数据
insert into t_customer values (1,'tom');
-- 再插入从表数据 (外键必须是主表的id已有的值)
insert into t_order
values (1,200,1);
commit;
-- 修改数据
-- 修改主表 只能修改未被引用的数据 此处id不能改
update t_customer set id = 'lucy'
where id = 1;
commit;
-- 修改从表 外键必须是主表存在
-- 主表插入一条数据 让id存在
insert into t_customer values (2,'tom');
update t_order set customer_id = 2
where id = 1;
commit;
-- 删除数据
-- 删除主表数据 如果表中的数据被引用 不可删
delete from t_customer
where id = 2;
commit;
delete from t_customer
where id = 1;
commit;
-- 删除从表数据 直接删除
delete from t_order
where id = 1;
commit;
重点: 建表时设置级联关系 主表数据删除的策略
-
主表数据删除 默认(被引用 不能删)
-
主表数据删除 引用了主表的从表数据自动置为空
-
外键设置 on delete set null
-
-
主表数据删除 引用了主表的从表数据级联删除
-
外键设置 on delete cascade
-
tcl
-
commit
-
rollback(savepoint)
-- 产生事务 A
insert into t_user values(6,'tom','f',null);
insert into t_user values(7,'lucy','f',null);
update t_user set name = 'lucy2' where id = 7;
commit; -- 提交事务A的所有操作 并结束事务A
-- 产生事务B
delete from t_user where id = 7;
rollback; -- 事务未提交之前都可以回滚
delete from t_user where id = 7;
commit;
回滚点
-- 产生事务 A
insert into t_user values(6,'tom','f',null);
savepoint A;
insert into t_user values(7,'lucy','f',null);
savepoint B;
update t_user set name = 'lucy2' where id = 7;
savepoint C;
-- rollback to A; -- insert into t_user values(6,'tom','f',null);
rollback to B;
-- insert into t_user values(6,'tom','f',null);
-- insert into t_user values(7,'lucy','f',null);
rollback; -- 未提交之前可以一直回滚
事务出现的问题
-
脏读
-
A事务读到了B未提交的事务 有可能B回滚了改数据
-
-
不可重复读
-
A事务第一次读数据,B事务修改了该数据,此时A再读数据,和第一次读的不一样
-
-
幻读
-
B事务添加了一个数据,A事务更新了name(原本更新一个值,事实上更新了两条 出现幻读)
-
隔离级别 oracle只支持读已提交和序列化读
-
读未提交 脏读
-
读已提交(oracle默认的级别)
-
解决脏读
-
出现不可重复读的问题
-
-
可重复读
-
解决不可重复读的问题
-
还是会出现幻读
-
-
序列化
-
解决以上所有问题
-
由上往下解决的问题由低到多,效率由低到高
dcl
-
grant
-
revoke
序列对象
掌握
-- 序列
-- create sequence 序列名称
create sequence user_seq;
-- 使用序列的特殊字段
-- 自增字段 nextval
-- 每次调用该字段 该值就会自增1
-- 当前字段 currval 默认没有值
-- 序列当前的值
select user_seq.nextval from dual;
select user_seq.currval from dual;
-- 查询序列名称
select sequence_name
from USER_SEQUENCES;
-- 使用序列
select * from t_user;
insert into t_user(id,name,sex,email)
values(user_seq.nextval,'tom','f',null);
commit;
-- 删除序列
drop sequence user_seq;
视图对象
视图对表的映射(虚拟表) 如果原表删除 视图无法成立
相对应的 物化视图 真实存在 根据原表创建后 脱离关系
简单视图
-
单表 不分组 没有聚合函数
-
和表一样增删改查 改视图 间接改了原表数据
-
with read only 不能修改视图
-
with check option 只能修改视图有的数据
select * from session_privs;
-- create view 视图名称
-- 视图本质上对表的映射
-- 创建简单视图
create or replace view view_emp
as
select ID,LAST_NAME,SALARY from s_emp;
-- 查询
select *
from view_emp;
-- 修改简单视图 同时会修改映射的表数据
update view_emp
set SALARY = 10000
where id = 2;
commit;
select * from S_EMP where id = 2;
-- 创建只读视图 (不能修改)
create or replace view view_emp
as
select ID,LAST_NAME,SALARY from S_EMP
with read only;
-- 创建检查选项视图
create or replace view view_emp
as
select ID,LAST_NAME,SALARY from S_EMP
where id = 1
with check option;
select * from view_emp;
insert into view_emp
values (2,'tom',200);
update view_emp set SALARY = 25000
where id = 1;
commit;
复杂视图
-
分组 聚合函数 多表
-
(多表除外)不可以修改视图
-- 多表查询 此时的视图是可以修改
create or replace view emp_dept
as
select e.id,e.last_name,d.name dept_name
from s_emp e,s_dept d
where e.DEPT_ID = d.ID;
select * from emp_dept;
update emp_dept set last_name = 'briup'
where id = 2;
commit;
索引对象
-- 创建表测试索引
create table T_INDEXTEST
(
ID NUMBER, -- 非主键 无索引
NAME VARCHAR2(30)
);
create index index_name on T_INDEXTEST(NAME);
select * from T_INDEXTEST;
-- 创建大量数据
-- oracle pl/sql语法 循环 ...
BEGIN
FOR i in 1..1000000 loop
INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
end loop;
commit;
END;
/
SELECT * from T_INDEXTEST where ID=765432;
SELECT * from T_INDEXTEST where NAME='AA765432';
存储过程
将一段pl/sql片段 放在存储过程中
将来可以调用存储过程 执行存储过程中的片段
create producure 名字(列名 参数类型)
is | as
begin
end;
/
-- 调用存储函数
call 名字('tom')