Oracle入门(一)_create user xiaowang 12.23.34.45 identified by wan-CSDN博客

本文链接：https://blog.csdn.net/nanyangnongye/article/details/120253520

一、创建表空间创建用户以及用户授权

创建表空间
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 登陆用户

5.1、单行函数

Java开发增删改查一张表的增删改查多张表的增删改查一个项目的增删改查分布式项目的增删改查

增删改都没有问题难就难在查询尤其是多张表的查询

作用于一行返回一个值

5.2、字符函数

select upper(‘yes’ ) from dual; -- YES

select lower (‘YES’) from dual; --yes

首字符大写函数

字符串链接函数

字符串截取函数

字符串替换函数

获取字符串长度函数

5.3、数值函数

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; --求余数

5.4、日期函数

查询出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；

5.5、转换函数

日期转字符串这个类型是字符串类型

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;

5.6、通用函数

算出emp表中所有员工的年薪
奖金里面有null 值如果null值和任意数字做算术运算结果都为null
nva函数的意思就是如果括号内第一个参数为null 那就用第二个参数0 做运算
如果不为null 就用第一个参数内的值

select e.name ，e.sal*12+nvl（e.comm,0） from emp e;

5.8、条件表达式

给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;

5.9、多行函数

作用于多行返回一个值【聚合函数】

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;--平均值

5.10、分组查询

查询每个部门的平均工资
分组函数中，出现在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对象

10.1、视图

视图的概念：视图就是提供一个查询的窗口所有的数据来源于原表
创建视图必须有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.保证总部和分布数据及时统一

10.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’ --触发索引列