文章目录
Oracle和MYSQL的差别
Oracle是多用户的, MYSQL是多数据库的
- 遵循SQL标准
- 不同厂商,不同的数据库产品,但是有自己的方言
- 使用自己的方言,也能够完成相同的功能
- Oracle安全级别要高,MYSQL开源免费
新建一个项目:
MYSQL : 创建一个数据库,创建相应的表
Oracle: 创建一个表空间,创建用户,用户去创建表
通常情况下,ORacle数据库只会有一个实例ORCL
数据库 —> 数据库实例 —> 表空间(逻辑单位)(用户) —> 数据文件(物理单位)
基本查询
SQL的分类以及每类常见的操作符
四类:
DDL : 数据定义语言 create alter drop truncate
DML : 数据操纵语言 insert update delete
DCL : 数据控制语言 安全 授权 grant revoke
DQL : 数据查询语言 select from子句 where子句
查询语句的结构：
select [列名] [*] from 表名 [where 条件] [group by 分组条件] [having 过滤] [order by 排序]
基本查询
select 1+1; --在Oracle等于报错 ,在MYSQL中输出结果是2
/*
dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构
*/
select 1+1 from dual;
--直接写一个常量比写 * 要高效
select count(1) from emp;
select count(*) from emp;
/*
别名查询: 使用as 关键字, 可以省略
别名中不能有特殊字符或者关键字, 如果有就加双引号
*/
select ename 姓名, sal 工资 from emp;
select ename "姓 名", sal 工资 from emp;
--去除重复数据 distinct
--多列去除重复: 每一列都一样才能够算作是重复
--单列去除重复
select distinct job from emp;
--多列去除重复的
select distinct job,deptno from emp;
--查询员工年薪 = 月薪* 12
select sal*12 from emp;
--查询员工年薪+奖金
select sal*12 + comm from emp;
--nvl 函数 : 如果参数1为null 就返回参数2
select sal*12 + nvl(comm,0) from emp;
--注意: null值 , 代表不确定的 不可预知的内容 , 不可以做四则运算
/*
字符串拼接:
java : + 号拼接
Oracle 特有的连接符: || 拼接
在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符
concat(str1,str2) 函数, 在mysql和Oracle中都有
*/
--使用拼接符
select '姓名:' || ename from emp;
--使用函数拼接
select concat('姓名:',ename) from emp;
/*
条件查询 : [where后面的写法]
关系运算符: > >= = < <= != <>
逻辑运算符: and or not
其它运算符:
like 模糊查询
in(set) 在某个集合内
between..and.. 在某个区间内
is null 判断为空
is not null 判断不为空
*/
--查询每月能得到奖金的员工信息
select * from emp where comm is not null;
--查询工资在1500--3000之间的员工信息
select * from emp where sal between 1500 and 3000;
select * from emp where sal >= 1500 and sal <= 3000;
--查询名字在某个范围的员工信息 ('JONES','SCOTT','FORD') in
select * from emp where ename in ('JONES','SCOTT','FORD');
/*
模糊查询: like
% 匹配多个字符
_ 匹配单个字符
如果有特殊字符, 需要使用escape转义
*/
--查询员工姓名第三个字符是O的员工信息
select * from emp where ename like '__O%';
--查询员工姓名中,包含%的员工信息
select * from emp where ename like '%\%%' escape '\';
select * from emp where ename like '%#%%' escape '#';
/*
排序 : order by
升序: asc ascend
降序: desc descend
排序注意null问题 : nulls first | last
同时排列多列, 用逗号隔开
*/
--查询员工信息,按照奖金由高到低排序
select * from emp order by comm desc nulls last;
--查询部门编号和按照工资 按照部门升序排序, 工资降序排序
select deptno, sal from emp order by deptno asc, sal desc;
函数
函数: 必须要有返回值
单行函数: 对某一行中的某个值进行处理
数值函数
字符函数
日期函数
转换函数
通用函数
多行函数: 对某一列的所有行进行处理
max() min count sum avg
1.直接忽略空值
--统计员工工资总和
select sum(sal) from emp;
--统计员工奖金总和 2200
select sum(comm) from emp;
--统计员工人数 14
select count(1) from emp;
--统计员工的平均奖金 550 错误 2200/14 =
select avg(comm) from emp;
--统计员工的平均奖金 157.
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;
数值函数
select ceil(45.926) from dual; --46
select floor(45.926) from dual; --45
--四舍五入
select round(45.926,2) from dual; --45.93
select round(45.926,1) from dual; -- 45.9
select round(45.926,0) from dual; --46
select round(45.926,-1) from dual; --50
select round(45.926,-2) from dual; --0
select round(65.926,-2) from dual; --100
--截断
select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; -- 45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-1) from dual; --40
select trunc(45.926,-2) from dual; --0
select trunc(65.926,-2) from dual; --0
--求余
select mod(9,3) from dual; --0
select mod(9,4) from dual; --1
字符函数
-- substr(str1,起始索引,长度)
--注意: 起始索引不管写 0 还是 1 都是从第一个字符开始截取
select substr('abcdefg',0,3) from dual; --abc
select substr('abcdefg',1,3) from dual; --abc
select substr('abcdefg',2,3) from dual; --bcd
--获取字符串长度 24 28
select length('abcdefg') from dual;
--去除字符左右两边的空格
select trim(' hello ') from dual;
--替换字符串
Select replace('hello','l','a') from dual;
日期函数
--查询今天的日期
select sysdate from dual;
--查询3个月后的今天的日期
select add_months(sysdate,3) from dual;
--查询3天后的日期
select sysdate + 3 from dual;
--查询员工入职的天数
select sysdate - hiredate from emp;
select ceil(sysdate - hiredate) from emp;
--查询员工入职的周数
select (sysdate - hiredate)/7 from emp;
--查询员工入职的月数
select months_between(sysdate,hiredate) from emp;
--查询员工入职的年份
select months_between(sysdate,hiredate)/12 from emp;
转换函数
--字符转数值 to_number(str) 鸡肋
select 100+'10' from dual; --110 默认已经帮我们转换
select 100 + to_number('10') from dual; --110
--数值转字符
select to_char(sal,'$9,999.99') from emp;
select to_char(sal,'L9,999.99') from emp;
/*
to_char(1210.73, '9999.9') 返回 '1210.7'
to_char(1210.73, '9,999.99') 返回 '1,210.73'
to_char(1210.73, '$9,999.00') 返回 '$1,210.73'
to_char(21, '000099') 返回 '000021'
to_char(852,'xxxx') 返回' 354'
*/
--日期转字符 to_char()
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--只想要年
select to_char(sysdate,'yyyy') from dual; --2017
--只想要日
select to_char(sysdate,'d') from dual; --2 代表一个星期中第几天
select to_char(sysdate,'dd') from dual; --10 代表一个月中的第几天
select to_char(sysdate,'ddd') from dual; --100 代表一年中的第几天
select to_char(sysdate,'day') from dual; --monday
select to_char(sysdate,'dy') from dual; --mon 星期的简写
--字符转日期
select to_date('2017-04-10','yyyy-mm-dd') from dual;
--查询1981年 -- 1985年入职的员工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');
通用函数
/*
nvl(参数1,参数2) 如果参数1 = null 就返回参数2
nvl2(参数1,参数2,参数3) 如果参数1 = null ,就返回参数3, 否则返回参数2
nullif(参数1,参数2) 如果参数1 = 参数2 那么就返回 null , 否则返回参数1
coalesce: 返回第一个不为null的值
*/
select nvl2(null,5,6) from dual; --6;
select nvl2(1,5,6) from dual; --5;
select nullif(5,6) from dual; --5
select nullif(6,6) from dual; --null
select coalesce(null,null,3,5,6) from dual; --3
select ceil(-12.5) from dual; --12
select floor(12.5) from dual; --12
多表查询
笛卡尔积: 实际上是两张表的乘积,但是在实际开发中没有太大意义，格式: select * from 表1,表2
多表查询:
内联接
等值联接
inner join
外连接
left outer join
right outer join
Oracle特有的写法(+)
内联接
内联接:
隐式内联接:
等值内联接: where e1.deptno = d1.deptno;
不等值内联接: where e1.deptno <> d1.deptno;
自联接: 自己连接自己
显示内联接:
select * from 表1 inner join 表2 on 连接条件
inner 关键字可以省略
select * from emp e1, dept d1 where e1.deptno <> d1.deptno;
--查询员工编号,员工姓名,经理的编号,经理的姓名
select e1.empno,e1.ename,e1.mgr,m1.ename
from emp e1, emp m1 where e1.mgr= m1.empno;
--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename
from emp e1, emp m1,dept d1 where e1.mgr= m1.empno and e1.deptno = d1.deptno;
--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
;
--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2,salgrade s1
where e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
;
--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname,s2.grade
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal ;
--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
--将工资等级 1,2,3,4 显示成 中文的 一级 二级 三级...
select e1.empno,
e1.ename,
d1.dname,
case s1.grade
when 1 then '一级'
when 2 then '二级'
when 3 then '三级'
when 4 then '四级'
else
'五级'
end "等级",
e1.mgr,
m1.ename,
d2.dname,
decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') "等级"
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal
;
--查询员工姓名和员工部门所处的位置
select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno = d1.deptno;
select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;
外连接
左外连接: left outer join 左表中所有的记录,如果右表没有对应记录,就显示空
右外连接: right outer join 右表中的所有记录,如果左表没有对应记录,就显示空
outer 关键字可以省略
Oracle中的外连接: (+) 实际上是如果没有对应的记录就加上空值
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;
select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;
子查询
子查询: 查询语句中嵌套查询语句; 用来解决复杂的查询语句
查询最高工资的员工信息
单行子查询: > >= = < <= <> !=
多行子查询: in not in >any >all exists not exists
查询领导信息
--查询最高工资的员工信息
--1.查询出最高工资 --5000
select max(sal) from emp;
--2. 工资等于最高工资
select * from emp where sal = (select max(sal) from emp);
--查询出比雇员7654的工资高,同时和7788从事相同工作的员工信息
--1.雇员7654的工资 1250
select sal from emp where empno = 7654;
--2.7788从事的工作 ANALYST
select job from emp where empno = 7788;
--3.两个条件合并
select * from emp where sal > 1250 and job = 'ANALYST';
select * from emp where sal > (select sal from emp where empno = 7654) and job = (select job from emp where empno = 7788);
--查询每个部门最低工资的员工信息和他所在的部门信息
--1.查询每个部门的最低工资,分组统计
select deptno,min(sal) minsal from emp group by deptno;
--2.员工工资等于他所处部门的最低工资
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal = t1.minsal;
--3.查询部门相关信息
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1,
dept d1
where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;
/*
内联接, 单行子查询, 多行子查询
in
not in
any
all
exists
通常情况下, 数据库中不要出现null 最好的做法加上Not null
null值并不代表不占空间, char(100) null 100个字符
*/
--查询领导信息
--1.查询所有经理的编号
select mgr from emp;
select distinct mgr from emp;
--2.结果
select * from emp where empno in (select mgr from emp);
--查询不是领导的信息
select * from emp where empno not in (select mgr from emp);
select * from emp where empno <>all(select mgr from emp);
--正确的写法
select * from emp where empno not in (select mgr from emp where mgr is not null);
--查询出比10号部门任意一个员工薪资高的员工信息 10 20 30
select * from emp where sal >any (select sal from emp where deptno = 10);
--查询出比20号部门所有员工薪资高的员工信息 10 20 30
--1.20号最高工资 5000
select max(sal) from emp where deptno =20;
--2.员工信息
select * from emp where sal > (select max(sal) from emp where deptno =20);
-----使用多行子查询完成上面这题
---------20号部门所有员工薪资 (800 2975 ...)
select sal from emp where deptno = 20;
---------大于集合所有的
select * from emp where sal >all(select sal from emp where deptno = 20);
/*
exists(查询语句) : 存在的意思,判断一张表里面的记录是否存在与另外一张表中
当作布尔值来处理:
当查询语句有结果的时候, 就是返回true
否则返回的是false
数据量比较大的时候是非常高效的
*/
select * from emp where exists(select * from emp where deptno = 1234567);
select * from emp where 3=4;
select * from emp where exists(select * from emp where deptno = 20);
--查询有员工的部门的信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno );
--找到员工表中工资最高的前三名(降序排序)
select * from emp order by sal desc;
/*
rownum : 伪列, 系统自动生成的一列, 用来表示行号
rownum是Oracle中特有的用来表示行号的, 默认值/起始值是 1 ,在每查询出结果之后,再添加1
rownum最好不能做大于号判断,可以做小于号判断
SQL执行顺序
from .. where ..group by..having .. select..rownum..order by
*/
Select rownum,e1.* from emp e1;
--查询rownum大于2的所有记录 ,
select rownum,e1.* from emp e1 where rownum > 2; --没有任何记录
--查询rownum大于等于1的所有记录
select rownum,e1.* from emp e1 where rownum >=1;
--查询rownum < 6 的所有记录
select rownum,e1.* from emp e1 where rownum < 6;
--rownum 排序
Select rownum,e1.* from emp e1 order by sal;
--找到员工表中工资最高的前三名
select e1.* from emp e1 order by sal desc;
--将上面的结果当作一张表处理,再查询
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1;
--只要显示前三条记录
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1 where rownum < 4;
--找到员工表中薪水大于本部门平均薪水的员工
--1.分组统计部门平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
--2.员工工资 > 本部门平均工资
select * from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal > t1.avgsal;
/*
关联子查询 , 非关联子查询
*/
select * from emp e where sal > (select avg(sal) from emp e2 group by deptno having e.deptno=e2.deptno);
/*
统计每年入职的员工个数
*/
select hiredate from emp;
--只显示年
select to_char(hiredate,'yyyy') from emp;
--分组统计
select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy');
select yy
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
select case yy when '1987' then cc end
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
select case yy when '1987' then cc end "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
--去除行记录中的空值
select sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
--统计员工的总数
select sum(cc) "TOTAL"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
--将1987 和TOTAL 合并在一起
select
sum(cc) "TOTAL",
sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
--显示所有年份的结果
select
sum(cc) "TOTAL",
sum(case yy when '1980' then cc end) "1980",
sum(case yy when '1981' then cc end) "1981",
sum(case yy when '1982' then cc end) "1982",
sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
rowid与rownum
rowid : 伪列 每行记录所存放的真实物理地址
rownum : 行号 , 每查询出记录之后,就会添加一个行号
select rowid,e.* from emp e;
--去除表中重复记录
create table p(
name varchar2(10)
);
insert into p values('黄伟福');
insert into p values('赵洪');
insert into p values('杨华');
delete from p where
select rowid,p.* from p;
select distinct * from p;
delete from p p1 where rowid > (select min(rowid) from p p2 where p1.name = p2.name);
/*
rownum : 分页查询
在oracle中只能使用子查询来做分页查询
*/
--查询第6 - 第10 记录
select rownum, emp.* from emp;
select rownum hanghao, emp.* from emp;
select * from (select rownum hanghao, emp.* from emp) tt where tt.hanghao between 6 and 10;
集合运算
集合运算:
并集: 将两个查询结果进行合并
交集
差集
--并集运算: union union all
/*
union : 去除重复的,并且排序
union all : 不会去除重复的
*/
select * from emp where sal > 1500
union
select * from emp where deptno = 20;
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;
-- 交集运算: intersect
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;
--差集运算: 两个结果相减
select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
--集合运算中的注意事项:
-- 1.列的类型要一致
-- 2.按照顺序写
-- 3.列的数量要一致,如果不足,用空值填充
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,null from emp where deptno = 20;
序列: 生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5…
auto_increment 这个是mysql
语法:
create sequence 序列的名称
start with 从几开始
increment by 每次增长多少
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle 是否循环 1,2,3,1,2,3
cache 缓存的数量3 | nocache 1,2,3,4,5,6
如何从序列获取值
currval : 当前值
nextval : 下一个值
注意: currval 需要在调用nextval之后才能使用
永不回头,往下取数据, 无论发生异常, 回滚
表空间
创建表空间: 逻辑单位, 通常我们新建一个项目,就会去新建表空间,在表空间中创建用户来创建表
语法:
create tablespace 表空间的名称
datafile '文件的路径(服务器上)'
size 大小
autoextend on 自动扩展
next 每次扩展的大小
--切换到system帐号下创建
--创建一个表空间 --- yunyu
create tablespace handong
datafile 'd:\yunyu.dbf'
size 100m
autoextend on
next 10m;
--删除表空间
drop tablespace handong;
用户和角色
创建用户
create user 用户名
identified by 密码
default tablespace 表空间的名称
create user yzh
identified by yzh
default tablespace yunyu;
--授权 grant 角色 | 权限 to 用户
grant connect to yzh;
--授予 dba的角色
grant dba to yzh;
表信息
创建表
/*
创建表:
create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);
列的类型:
varchar ,在Oracle中,目前是支持的, 但是不保证以后还支持
varchar2(长度) 可变字符长度 varchar2(10) hello 占5个字符
char(长度) 固定长度字符 char(10) hello 占10个字符,用空格填充
number(总长度,小数长度) 数字类型 --小数长度不能大于等于总长度
date 年月日时分秒 2017/4/13 9:43:49
timestamp 时间戳, 比date类型更加精确 13-APR-17 09.44.08.272000 AM +08:00
LONG/CLOB : 存放一本小说
BLOB : 存放电影 java 存进去, 再读取出来
使用子查询的方式创建表
create table 表名 as 查询语句;
注意: 只会复制表结构和表中的数据,不会复制列的约束
如果查询语句有结果, 就是复制 表结构和数据
如果查询语句没有结果, 就是复制 表结构
*/
create table test1(
name1 varchar2(10),
name2 char(10),
age number(2,3)
);
insert into test1(name1,name2) values('hello','hello');
select * from test1 where name1 like 'hello'; --可以查询数据
select * from test1 where name2 like 'hello'; --查不出数据
insert into test1(age) values(2);
select current_date from dual;
select current_timestamp from dual;
select * from test1;
-- create table 表名 as 查询语句; 复制表
select * from scott.emp;
create table emp as select * from scott.emp;
--如果查询语句是没有任何的结果的
select * from scott.emp where 1=2;
create table emp1 as select * from scott.emp where 1=2;
修改表
--添加一列
alter table stu add phone varchar2(11);
alter table stu add (
mobile varchar2(11),
sex varchar2(2)
);
--修改列的类型
alter table stu modify sex varchar2(4);
--修改列名 sex --- gender
alter table stu rename column sex to gender;
--删除列
alter table stu drop column gender;
--修改表名
rename stu to student;
删除表
drop table student;
约束
/*
表的五大约束
列的约束: 约束主要是用来约束表中数据的规则
主键约束: primary key 不能为空, 必须唯一
非空约束
唯一约束
检查约束 check(条件) 在mysql中是可以写的,但是mysql直接忽略了检查约束
外键约束:
主要是用来约束从表A中的记录,必须是存在于主表B中
*/
--男,女,人妖
create table student(
stuid number primary key,
sname varchar2(10) unique,
age varchar2(10) not null,
gender varchar2(4) check( gender in ('男','女','人妖'))
);
--主键约束违反
insert into student values(1,'张三','31','男');
insert into student values(1,'李四','31','男');
--唯一约束违反
insert into student values(1,'徐立','31','男');
insert into student values(2,'徐立','31','男');
--非空约束
insert into student values(1,'徐立',null,'男');
--检查约束
insert into student values(1,'徐立','31','男');
insert into student values(1,'徐立','31','妖');
select * from student;
/*
商品分类,商品表
*/
--商品分类表
create table category(
cid number primary key,
cname varchar2(20)
);
--创建一个商品表
create table product(
pid number primary key,
pname varchar2(20),
cno number
);
insert into category values(1,'手机数码');
insert into product values(10,'锤子',11);
--添加外键约束
alter table product add foreign key(cno) references category(cid);
insert into product values(10,'锤子',11);--插入失败
--1.首先主表中必须存在11号, 先往主表中插入数据,再往从表中插入数据
insert into category values(2,'电脑办公');
insert into product values(11,'外星人',2);
--删除Category
drop table category; --表中记录被外键关联无法删除
--强制删除表(不建议使用) : 先删除外键关联表的外键约束,然后再删除自己, 先删除product的外键约束,再删除category
drop table category cascade constraint;
--级联删除
----添加外键约束,使用级联约束 ,在删除的时候,使用级联删除
alter table product add foreign key(cno) references category(cid) on delete cascade;
insert into category values(2,'电脑办公');
insert into product values(11,'外星人',2);
--级联删除 : 首先去从表中找有没有 关联数据, 如果在从表中找到关联数据,先删除从表中关联数据,然后再删除表中的数据
delete from category where cid = 2;
数据
/*
插入数据:
insert into 表名 values(所有列的值都要对应写上)
insert into 表名(列1,列2) values(值1,值2);
使用子查询插入数据
insert into 表名 查询语句
*/
select * from emp1;
select * from emp;
--将emp中10号部门的员工信息,插入到emp1中
insert into emp1 select * from emp where deptno = 10;
/*
更新数据
update 表名 set 列名 = 列的值 [where 条件]
*/
update emp1 set ename='HUAAN' where ename = 'KING';
select * from emp1;
/*
删除数据:
delete from 表名 [where 条件]
delete和truncate 区别
delete: truncate:
DML DDL
逐条删除 先删除表再创建表
支持事务操作 不支持事务操作,
执行效率要高
*/
delete from emp1 where empno=7839;
视图
视图: 是对查询结果的一个封装
视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据
1.能够封装复杂的查询结果
2.屏蔽表中的细节
语法:
create [or replace] view 视图的名称 as 查询语句 [ with read only]
注意: 通常不要通过视图去修改,视图创建的时候,通常要加上with read only
--创建一个视图
create or replace view view_test1 as select ename,job,mgr from emp;
--通过视图修改数据
update view_test1 set ename='SMITH2' where ename = 'SMITH';
--创建一个只读视图
create or replace view view_test2 as select ename,job,mgr from emp with read only;
update view_test2 set ename='SMITH3' where ename = 'SMITH2';
--视图封装复杂的查询语句
create view view_test3 as select
sum(cc) "TOTAL",
sum(case yy when '1980' then cc end) "1980",
sum(case yy when '1981' then cc end) "1981",
sum(case yy when '1982' then cc end) "1982",
sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;
/*
事务: 就是一系列的操作,要么都成功,要么都失败
四大特性: 原子性,隔离性,持久性,一致性
如果不考虑隔离级别: 脏读,虚读,不可重复读
MYSQL隔离级别: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLE
ORACLE隔离级别: READ COMMITTED SERIALIZABLE READ ONLY
默认隔离级别: READ COMMITTED
提交 : commit
事务的保存点/回滚点: savepoint 保存点的名称
回滚: rollback
事务操作:
savepoint 保存点
rollback to 保存点
ORACLE事务隔离级别 : READ COMMITTED
*/
create table louti(
lou number primary key
);
insert into louti values(1);
insert into louti values(2);
insert into louti values(3);
insert into louti values(4);
insert into louti values(5);
savepoint dangban;
insert into louti values(5); --主键约束会发生异常
insert into louti values(6);
rollback to dangban
commit;
declare
begin
insert into louti values(1);
insert into louti values(2);
insert into louti values(3);
insert into louti values(4);
insert into louti values(5);
savepoint dangban;
insert into louti values(5); --这行代码会发生异常
insert into louti values(6);
commit;
exception --捕获异常
when others then
rollback to dangban;
commit;
end;
同义词
同义词: create synonym 名称 for 对象的名称
create synonym dept for view_test3;
select * from dept;
序列
序列: 生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5....
auto_increment 这个是mysql
语法:
create sequence 序列的名称
start with 从几开始
increment by 每次增长多少
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle 是否循环 1,2,3,1,2,3
cache 缓存的数量3 | nocache 1,2,3,4,5,6
如何从序列获取值
currval : 当前值
nextval : 下一个值
注意: currval 需要在调用nextval之后才能使用
永不回头,往下取数据, 无论发生异常, 回滚
--创建一个 1,3,5,7,9......30
create sequence seq_test1
start with 1
increment by 2
maxvalue 30
cycle
cache 10;
select seq_test1.nextval from dual;
select seq_test1.currval from dual;
--序列用的最多的一种写法
create sequence seq_test2;
select seq_test2.nextval from dual;
create sequence seq_test3
start with 1
increment by 2
maxvalue 30
minvalue 0
cycle
cache 10;
select seq_test3.nextval from dual;
索引
索引:相当于是一本书的目录,能够提高我们的查询效率
如果某一列,你经常用来作为查询条件,那么就有必要创建索引,数据量比较的情况
语法:
create index 索引的名称 on 表名(列)
注意:主键约束自带主键索引, 唯一约束自带唯一索引
索引原理: btree balance Tree 平衡二叉树
如果某列作为查询条件的时候,可以提高查询效率,但是修改的时候,会变慢
索引创建好之后,过了一段,DBA都会去做重构索引
SQL调优:
1.查看执行计划F5
2. 分析里面的cost 和 影响行数, 想办法降低
--五百万数据测试
create table wubaiwan(
name varchar2(30),
address varchar2(20)
);
insert into wubaiwan values('')
--插入500000万条数据
declare
begin
for i in 1..5000000 loop
insert into wubaiwan values('姓名'||i,'地址'||i);
end loop;
commit;
end;
--在没有添加索引的情况下,去查询 name='姓名3000000' --2.985
select * from wubaiwan where name='姓名3000000';
--创建索引 name 再去查询 name='姓名3000000'
create index ind_wubaiwan on wubaiwan(name);
select * from wubaiwan where name='姓名3000000'; --0.016
--在没有添加复合索引的情况下,再去查询 name='姓名3000000' and '地址3000000'
select * from wubaiwan where name='姓名3000000' and address='地址3000000'; --0.032
--创建复合索引的情况下, 再去查询
create index ind_wubaiwan2 on wubaiwan(name,address);
select * from wubaiwan where name='姓名3000000' and address='地址3000000'; --0.015
679
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
