目录
创建表空间创建用户以及用户授权
#%E5%88%9B%E5%BB%BA%E8%A1%A8%E7%A9%BA%E9%97%B4%E5%88%9B%E5%BB%BA%E7%94%A8%E6%88%B7%E4%BB%A5%E5%8F%8A%E7%94%A8%E6%88%B7%E6%8E%88%E6%9D%83
修改表结构
#%E4%BF%AE%E6%94%B9%E8%A1%A8%E7%BB%93%E6%9E%84
数据的增删改
#%E6%95%B0%E6%8D%AE%E7%9A%84%E5%A2%9E%E5%88%A0%E6%94%B9
序列的使用
#%E5%BA%8F%E5%88%97%E7%9A%84%E4%BD%BF%E7%94%A8
oracle的查询
#oracle%E7%9A%84%E6%9F%A5%E8%AF%A2
单行函数
#%E5%8D%95%E8%A1%8C%E5%87%BD%E6%95%B0
字符函数
数值函数
日期函数
转换函数
通用函数
条件表达式
多行函数
#%C2%A0%E5%A4%9A%E8%A1%8C%E5%87%BD%E6%95%B0
分组查询
多表查询中的一些概念
自连接概念和练习
子查询
分页查询
oracle对象
视图
索引
索引的概念
单列索引
复合索引
创建表空间创建用户以及用户授权
创建表空间
create tablespace xiaowang
datafile 'D:\xiaowang.dbf'
size 100m
autoexdent on
next 10m;
删除表空间
drop tablespace xiaowang;
创建用户表
create user xiaowang
identified by xiaowang
default tablespace xiasowang;
给用户授权
grant dba to xiaowang;
修改表结构
添加一列
alter table student sex number(1); --增加一列
alter table student (sex number(1),address varchar2(20));--增加多列
修改列类型
alter table student modify sex varchar(5);
修改列名称
alter table student rename column sex to xingbie;
删除一列
alter table sutdent drop column xingbie;
数据的增删改
添加一条记录
insert into student (pId ，pName) values （1，'晓航'）;
修改一条数据
update student set pname = '小航航' where pId = 1;
三个删除
delete from student -- 删除表中全部记录
drop table student --删除表结构
在表数据量大的情况下 尤其是表中带有索引的情况下 该操作效率高
索引可以提高查询效率 但是会影响增删改 效率
truncate table student --先删除表 再次创建表 等同于删除表中全部记录
序列的使用
- 默认从1开始 依次递增 主要用来给主键赋值使用
- 序列不真的属于任何一张表，但是可以逻辑和表绑定
create sequence s_person;
select s_person.nextval from dual;
insert into student (pId ，pName) values （ s_person.nextval，'晓航'）;
oracle的查询
scott用户介绍
- 用户名scott 密码tiger
- 解锁scott用户
alter user scott account unlock;
- 解锁用户密码 【词句也可以重置密码】
alter user scott identified by tiger;
- 切换到scott用户下 Log off退出当前用户 Log on 登陆用户
单行函数
- Java开发 增删改查 一张表的增删改查 多张表的增删改查 一个项目的增删改查 分布式项目的增删改查
增删改都没有问题 难就难在查询 尤其是多张表的查询
- 作用于一行 返回一个值
字符函数
select upper(‘yes’ ) from dual; -- YES
select lower (‘YES’) from dual; --yes
首字符大写函数
字符串链接函数
字符串截取函数
字符串替换函数
获取字符串长度函数
数值函数
select round(26.18) from dual; --26 四舍五入 后面的参数表示保留的位数
select round(26.18,1) from dual; --26.2
select trunc(26.18，-1) from dual; --50 直接截取 不看后面位数的数字是否大于5
select mod(10，3) from dual; --求余数
日期函数
查询出emp表中所有员工入职距离现在几天
select e.ename，sysdate-e.hiredate from emp e；
算出明天此刻
select SYSDATE+1 from dual
注意 navicat mysql server 和oracle函数语法不样 此文都为oracle数据库
查询出emp表中所有的员工入职距离现在几月
select e.ename，month_between(sysdate,e.hiredate) from emp e；
查询出emp表中所有的员工入职距离现在几年
select e.ename，(sysdate-e.hiredate)/365 from emp e；也可以
select e.ename，month_between(sysdate,e.hiredate) /12 from emp e；
查询出emp表中所有的员工入职距离现在几周
select e.ename，month_between(sysdate,e.hiredate)/4 from emp e；这样算不够精准
select e.ename，（sysdate-e.hiredate）/7 from emp e；
select e.ename，round(（sysdate-e.hiredate）/7) from emp e；
转换函数
日期转字符串 这个类型是字符串类型
select to_char(sysdate ,’fm yyyy-mm-dd hh24:mi:ss’) from dual;
字符串转日期 这个类型是date类型
select to_date(‘2021-9-12 20:12:30’ , ‘fm yyyy-mm-dd hh24:mi:ss’ ) from dual;
通用函数
- 算出emp表中所有员工的年薪
- 奖金里面有null 值 如果null值和任意数字做算术运算 结果都为null
- nva函数的意思就是如果括号内第一个参数为null 那就用第二个参数0 做运算
- 如果不为null 就用第一个参数内的值
select e.name ，e.sal*12+nvl（e.comm,0） from emp e;
条件表达式
给emp表中员工起中文名称
select e.ename ,
CASE e.ename
WHEN 'SMITH' THEN'管理员'
WHEN 'ALLEN' THEN '销售员'
WHEN 'WARD' THEN '销售经理'
WHEN 'JONES' THEN '区域经理'
-- ELSE '员工' END
from emp e
不要else时候 表内数据显示为null
- 判断emp表中员工工资，如果高于3000显示高收入 如果高于1500低于3000显示中等收入
select e.sal
case
when e.sal > 30000 then '高收入'
when e.sal > 1500 then '中等收入'
else '低等收入'
from emp e;
等值判断 case 后面跟字段 范围判断不需要跟字段
Oracle数据库专用条件表达式语句
select e.ename
decode ( e.ename
'SMITH ','曹贼 '
'ALLEN ','大耳贼 '
'WARD ','诸葛小二 ',
'无名'
)中文名 --这里的【中文名】可以不加引号 如果加引号的话直接加双引号 因为它代表的是字段名称是一个字符串
from emp e;
多行函数
- 作用于多行 返回一个值【聚合函数】
select count(1) from emp e; --查询总条数
select SUM(sal) from emp e;--和计算
select MAX(sal) from emp ;--最大值
select MIN(sal) from emp e;--最小值
select AVG(sal) from emp e;--平均值
分组查询
- 查询每个部门的平均工资
- 分组函数中，出现在group by后面的原始列，才能出现在select后面
- 没有出现在group by后面的列 想在select后面 必须加上聚合函数 如sum max min avg count
- 聚合函数有一个特性 可以把多行记录变成一个值
select e.deptno，avg（e.sal）
from emp e
group by e.deptno；
- 查询出平均工资高于2000的部门信息
- 所有条件都不能使用别名来判断
select e.deptno ,avg(e.sal) asal
from emp e
group by e.deptno having avg(e.sal) > 2000;
- 查询出每个部门工资高于800的员工的平均工资
- 然后在查询出平均工资高于2000的部门
select e.deptno avg（e.sal ）
from emp e
where e.sal > 800
group by e.deptno
having avg(e.sal)> 2000
WHERE后面跟的是条件。HAVING是跟在GROUP BY 分组后面的
多表查询中的一些概念
笛卡尔积： 一张表中所有的记录 一一 和另外一整表的所有的记录匹配
等值连接
select *
from emp e，dept d
where e.deptno = d.deptno
内连接
select *
from emp e
inner join dept d
on e.deptno = d.deptno
外连接
select *
from emp e
left jion dept d
on e.deotno = d.deptno
Oracle专用外连接
- 一般使用通用的连接查询sql 不使用专用的
select *
from emp e , dept d
where e.deptno(+) = d.deptno
自连接概念和练习
- 查询出员工姓名 员工领导姓名
- 自连接 其实就是站在不同的角度把一张表看成多张表
select e1.ename,e2.ename
from emp e1 ,emp e2
where e1.MGR = e2.empno;
在此sql中e1是员工表 e2是领导表 为什么呢
e2表中的员工字段都是e1表中的领导字段
- 查询出员工姓名 员工部门名称 员工领导姓名 员工领导部门名称
select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1 ,emp e2,dept d1,dept d2
where e1.MGR = e2.empno
and e1.deptno = d1.deptno
and d2.deptno = d2.deptno;
子查询
- 子查询返回一个值
- 查询出工资和SCOTT一样的员工
select *
from emp e
where e.sal in(select sal from emp where ename = 'SCOTT')
sal 后面不能使用 = 因为括号内where 后面的字段不是主键 不能确保唯一非空
当ename出现另一SCOTT 时 查询语句使用 = 连接就不对了 应该使用 in 连接
但是如果使用的是主键 那就可以使用 = 连接查询语句
- 子查询返回一个集合
- 查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in(
SELECT sal from emp where deptno = 10
);
- 子查询返回一张表
- 查询出每个部门最低工资 和最低工资员工姓名 和该员工所在部门名称
- 1.先查询出每个部门的最低工资
select e.deptno,min(e.sal) msal
from emp e
group by e.deptno
- 2.三表联查，得到最终结果
SELECT t.deptno,t.msal,e.ename,d.dname
from (
select e.deptno ,min(e.sal)
from emp e
group by e.deptno
) t,emp e,dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno
这个查询在工作中用的比较多 如果想掌握好这个sql语句 首先要把简单的子连接查询搞清楚 搞明白
分页查询
rownum 行号
rownum行号：当我们做select操作的时候 每查询出一行记录 就会在改行上加上一个行号
行号从1开始 依次递增，不能跳着走 排序操作会影响rownum的顺序
select *
from emp e
where rownum < 4
order by e.sal desc
注意 这个语句是错误的 能运行 但是数据不对
select rownum, e.*
from emp e
order by e.sal desc
这个sql语句可以清楚的表达rownum 和 order by排序冲突问题
如果涉及到排序 但是还要使用rownum的话 我们可以再次 嵌套查询
select rownum, t.*
from (select *
from emp e
order by e.sal desc ) t;
- emp表工资倒叙排列后 每页五条记录 查询第二页
select rownum e.*
from (
select rownum rn, e.*
FROM (select *from emp order by SAL desc) e
where rownum < 11
) where rn > 5
oracle对象
视图
- 视图的概念：视图就是提供一个查询的窗口 所有的数据来源于原表
- 创建视图必须有dba权限
- 查询语句创建表
create table emp select * from scott.emp ;
select * from emp
创建视图
create view v_emp as select ename, job from emp;
查询视图
select * from v_emp;
修改视图【不推荐】
uodate v_emp set job = 'CLERK' where ename = 'ALLEN';
commit;
修改成功 那么原表中的数据也随之改变 我们修改视图 修改的是原表中的数据 因为视图中本来就没有数据
创建只读视图
create view v_emp as select ename,job from emp with read only;
视图有什么用
1.视图可以屏蔽掉表中的敏感字段
2.保证总部和分布数据及时统一
索引
索引的概念
索引就是在表的列上构建一个二叉树 达到大幅度提高查询效率的目的 但是索引会影响增删改的效率
单列索引
创建单例索引
create index idx_ename on emp(ename);
索引触发规则 条件必须是索引列中的原始值
单行函数 列如单行函数分为五种类型：字符函数、lower upper数值函数0、round、trunc、mod日期函数、month_between转换函数、to_char、to_date通用函数nvl
模糊查询 都会影响索引的触发
复合索引
创建复合索引
create index idx_enamejob on emp(ename,job);
复合索引中 第一列为优先检索列
如果要触发复合索引，必须包含有有限检索列中的原始值
select * from emp where ename = ‘SCOTT’ and job = ‘xx’ --触发索引列
select * from emp where ename = ‘SCOTT’ or job = ‘xx’ -- 不触发索引列 or 表示一个触发 或者 一个不触发 那么结果就是不触发
select * from emp where ename = ‘SCOTT’ --触发索引列