Oracle基础命令入门

最新推荐文章于 2022-07-07 07:35:11 发布

hikktn

最新推荐文章于 2022-07-07 07:35:11 发布

阅读量735

点赞数

分类专栏： # Oracle

本文链接：https://blog.csdn.net/qq_41520636/article/details/116302583

版权

Oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.windows10安装Oracle 19

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

中间安装过程省略，可查看其他博客。

2.安装PL/SQL

https://www.allroundautomations.com/registered-plsqldev/

当Oracle是最新的，那么相应的配套软件一定要安装最新，最新的软件不用使用繁琐的配置，软件里自带配置，安装就可以使用。

3.登录Oracle

打开PL\SQL软件，密码就是你安装时设置的口令，而连接有三种方式，选择默认normal，因为开发不需要更改配置，而sysdata是用于更改配置的，所以选择normal。

新建SQL窗口

4.创建表空间

表空间，相当于一个划分给Oracle的内存，专门用于存储数据的区域。

create tablespace 表空间名

datafile 存储路径文件路径必须存在，并且后缀文件名必须是 *.dbf

size 表空间的初始大小

autoextend on 自动增长，当表空间存储都占满时，自动增长

next 指定的是一次自动增长的大小

-- 创建表空间
create tablespace hikktn
datafile 'E:\oracle_tablespaces\hikktn.dbf' size 100M
autoextend on -- 自动增长
next 10M; -- 自动增长大小

5. 删除表空间

-- 删除表空间
drop tablespace hikktn;

6.创建用户

Oracle 中已存在三个重要的角色： connect 角色， resource 角色， dba 角色。

CONNECT 角色： -- 是授予最终用户的典型权利，最基本的

ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图

RESOURCE 角色： -- 是授予开发人员的

CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型

DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统

权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除

-- 创建用户【必须给用户授权，否则无法访问表空间】
create user c##hikktn -- 用户名
identified by hikktn -- 密码
default tablespace hikktn; -- 表空间

7.授权用户

-- 给用户授权
-- oracle数据库中常用角色
connect -- 连接角色，基本角色
resource -- 开发者角色
dba -- 超级管理员角色
-- 授权
grant dba to c##hikktn;

8.创建一个person表

-- 创建一个person表
create table person(
       id number(20),
       name varchar2(10)
);

9.数据库表结构定义

-- 修改表结构
-- 添加列
alter table person add ( sex number(1), age number(5) );

-- 修改列类型
alter table person modify sex char(1);

-- 修改列名称
alter table person rename column sex to gender;

-- 删除列
alter table person drop column age;

10.CRUD

-- CRUD
-- 查询
select * from person;

-- 添加一条数据
insert into person (id,name,gender) values (1,'王三','0');
-- 提交
commit;

-- 修改
update person set name = '李四' where id =1;
commit;

-- 三种删除方式
-- 删除表中全部数据
delete from person;
-- 删除表结构
drop table person;
-- 先删除表，再创建表。效果等同于删除表中全部数据。
-- 数据量大，先删除了索引，在删除全部数据，效率高
truncate table person;

11.创建序列

-- 序列不属于任何一张表，但是可以逻辑和表做绑定
-- 序列：默认从1开始，依次递增，用于主键赋值
-- dual:虚拟表，只是用来补全语法，没有任何存在意义
create sequence s_person;
select s_person.nextval from dual;

12.准备测试sql

如果你的版本还是12以前，那么可以执行下面命令

-- 作为初学者，Oracle提供了测试案例，可完成大量学习
-- 使用Scott用户，密码tiger
-- 想要使用scott用户，需要在超级管理员解锁后才能使用
-- scott用户不存在
-- 创建scott用户
CREATE USER c##scott IDENTIFIED BY tiger;
-- 为用户授权
grant connect,resource to c##scott;
-- 设置用户使用的表空间
ALTER USER c##scott DEFAULT TABLESPACE hikktn;
ALTER USER c##scott TEMPORARY TABLESPACE TEMP;

-- 解锁scott用户命令
alter user c##scott account unlock;
-- 解锁Scott用户的密码，该命令也可以用于重置密码
alter user c##scott identified by tiger;
-- 删除用户
drop user c##scott cascade;

此SQL是从SCOTT用户里拷贝过来的，一些不明原因，无法使用scott用户，那么就直接使用自己创建的用户，运行一下下面的SQL，创建好一样的数据就行。

拷贝的路径：

E:\software\WINDOWS.X64_193000_db_home\rdbms\admin\scott.sql

DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'C##SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

13.单行函数

作用域一行，返回一个值

字符函数

转大写 upper

-- 转大写
select upper('yes') from dual;

转小写 lower

-- 转小写
select lower('YES') from dual;

首字符大写 initcap

-- 首字符大写
select initcap('hello') from dual;

左剪切 ltrim

-- 左剪切
select ltrim('xyzadmins','xyz') from dual;

右剪切 rtrim

-- 右剪切
select rtrim('xyzadmins','admins') from dual;

字符替换 translate

-- 字符替换
-- 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string
-- a 替换为1，c替换为3
select translate ('jack', 'abcd', '1234') from dual;

字符串替换 replace

-- 字符串替换
select replace ('jack and jue', 'j', 'bl') from dual;

字符串索引 instr

-- 查找子串位置，返回该字段索引位置
select instr ('worldwide', 'd') from dual;

截取字符串 substr

-- 截取字符串，从第三个字符截取两个字符
select substr ('abcdefg',3,2) from dual;

拼接字符串 concat

-- 拼接字符串
select concat ('hello','world') from dual;

数值函数

绝对值 abs

-- 绝对值
select abs(-15) from dual;

向上取整 ceil

-- 向上取整
select ceil(44.2) from dual;

正弦 sin

-- 正弦
select sin(1.34) from dual;

余弦 cos

-- 余弦
select cos(0) from dual;

向下取整 floor

-- 向下取整
select floor(100.2) from dual;

取余数 mod

-- 取余数
select mod(10,3) from dual;

四舍五入 round

-- 四舍五入
select round(1000.333,1) from dual;

数值截取 trunc

-- 数值截取，不看后面位数的数字
select trunc(100.256,2) from dual;

日期函数

当前时间 sysdate

select sysdate from dual;

返回两个日期间的月份 months_between

-- 返回两个日期间的月份
-- 查询出emp表中所有员工入职距离现在几月
select months_between (sysdate,e.hiredate) from emp e;
-- 查询出emp表中所有员工入职距离现在几年
select months_between (sysdate,e.hiredate)/12 from emp e;

转换函数

日期转字符串 to_char

-- 日期转字符串
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;

字符串转日期 to_date

-- 字符串转日期
select to_date(' 2021-4-29 1:50:57','fm yyyy-mm-dd hh24:mi:ss') from dual;

通用函数 nvl

-- 计算 null 使用nvl函数，返回0进行运算
select e.sal +nvl(e.comm,0) from emp e;

通用函数 nvl2

-- 判断nvl2函数里，如果第一参数为null，则返回第二个值，相反返回第三个值
select e.sal +nvl2(e.comm,null,1) from emp e;

条件表达式

等值判断 mysql和oracle通用

-- 等值判断
select e.ename,
       case e.ename
         when 'SMITH' then '大笨蛋'
           when 'ALLEN' then '大聪明'
             else '无名'
               end
from emp e;

范围判断 mysql和oracle通用

-- 范围判断
select e.sal,
       case 
         when e.sal > 3000 then '高收入'
           when e.sal > 1500 then '中等收入'
             else '低收入'
               end
from emp e;

oracle专用条件表达式

-- oracle专用条件表达式
select e.ename,
       decode( e.ename,
          'SMITH' , '大笨蛋',
            'ALLEN' , '大聪明',
              '无名'）"绰号"
from emp e;

14.多行函数

作用于多行，返回一个值

统计函数

总数 count

-- 总数
select count(1) from EMP e where e.empno > 7500;

最大 max

-- 最大
select max(COMM) FROM EMP;

最小 min

-- 最小
select min(COMM) FROM EMP;

平均 avg

-- 平均
select avg(COMM) FROM EMP;

总和 sum

-- 总和
select sum(COMM) FROM EMP;

15.别名

-- 别名
select e.sal as 工资 from emp e;
select e.sal 工资 from emp e;
select e.sal "工资" from emp e;

16.字符串连接 ||

-- 字符串连接 ||
select e.ename || ',' || e.empno "雇主姓名和编号" from emp e;

17.消除重复行 distinct

-- 消除重复行
select distinct e.deptno from emp e;

18.条件限定

区间查询 between ... and ...

-- 区间查询
-- 类似于查询工资1000元到2000元，没有符合条件时，默认返回1000
select e.sal 工资 from emp e where e.sal between 1000 and 2000;

多条件查询 in ()

-- 多条件查询
-- 类似于 where sal = 800 or sal = 2000 or sal = 500
select e.sal 工资 from emp e where e.sal in( 800 , 2000 , 500 );

模糊查询 like

-- 模糊查询
select * from emp e where e.ename like '%A%';

不为空查询 is not null

-- 不为空查询
select * from emp e where e.comm is null;

19.分组查询

-- 查询每个部门平均工资
select e.deptno , avg(e.sal) asal
from emp e
group by e.deptno;
-- 分组查询 过滤查询
select e.deptno , avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) < 2500;
-- 查询每个部门平均工资高于500员工
select e.deptno , avg(e.sal)
from emp e
where e.sal > 500
group by e.deptno;
-- 查询每个部门平均工资高于500员工
-- 查询平均工资低于2500的部门
select e.deptno , avg(e.sal)
from emp e
where e.sal > 500
group by e.deptno
having avg(e.sal) < 2500;
-- where 和 having 区别
-- where在group by 之前过滤， having 在 group by 之后过滤

20.笛卡尔积

-- 笛卡尔积
select * from emp e, dept d;

21.等值连接

-- 等值连接 (推荐)
select * from emp e,dept d
where e.deptno = d.deptno;

22.内连接

select * from emp e inner join dept d
on e.deptno = d.deptno;

23.简化等值连接 using

-- 简化连接，必须是等值连接，且两表的列字段名称和类型相同
select * from emp e inner join dept d
 using(deptno);

24.外连接

右连接

-- 右连接
select * from emp e right join dept d
on e.deptno = d.deptno;

左连接

-- 左连接
select * from emp e left join dept d
on e.deptno = d.deptno;

左连接和右连接区别

以那张表为主表查询并集，主表全部查询出来，次表必须等值后才能查询出

Oracle 专用外连接

右连接

-- 右连接
select * from emp e,dept d
where e.deptno = d.deptno(+);

左连接

-- 左连接
select * from emp e,dept d
where e.deptno(+) = d.deptno;

子连接

-- 子连接
-- 查询出雇员姓名，上级领导姓名
select e1.ename , e2.ename
from emp e1,emp e2
where e1.empno = e2.mgr;
-- 查询出雇员姓名，上级领导姓名，雇员的部门名称，领导的部门名称
select e1.ename , e2.ename , d1.dname ,d2.dname
from emp e1,emp e2, dept d1,dept d2
where e1.empno = e2.mgr
and d1.deptno = e1.deptno 
and d2.deptno = e2.deptno;
-- 查询出雇员编号，雇员姓名，上级领导姓名，雇员的部门名称，领导的部门名称，工资等级
select
 e1.empno, e1.ename,  d1.dname,
 decode(s1.grade,
        1,'一级',
        2,'二级',
        3,'三级',
        4,'四级',
        5,'五级') grade ,
 e2.empno,e2.ename,d2.dname,
 decode(s2.grade,
        1,'一级',
        2,'二级',
        3,'三级',
        4,'四级',
        5,'五级') grade        
from emp e1,emp e2, dept d1 , dept d2, salgrade s1 ,salgrade s2
where e1.mgr = e2.empno
and d1.deptno = e1.deptno 
and d2.deptno = e2.deptno 
and e1.sal between s1.losal and s1.hisal
and e2.sal between s2.losal and s2.hisal;

单行子查询

-- 单行子查询，返回一条记录
select * from emp where sal =
(select sal from emp where ename = 'CLARK');

多行子查询

-- 多行子查询，返回一个集合
select * from emp where sal in 
(select sal from emp where deptno = 10);

例子：

-- 查询每个部门最低工资，最低工资的员工姓名，以及该员工所在的部门名
select t.deptno , t.msal,e.ename,d.dname
from 
( select deptno , min(sal) msal from emp group by deptno ) t, emp e, dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;

select e2.ename,e2.job,
   round(
       (select avg(e.sal)
       from emp e
       where e.empno = e2.empno),
       2
   ) asal
from emp e2
order by e2.ename;

25.分页查询

-- 和mysql中的limit一样的功能，Oracle里面没有limit关键字
select * from emp e inner join dept d
 using(deptno) order by empno desc fetch next 5 rows only;
-- 返回结果集前5行
select * from emp where rownum <= 5;
-- 返回结果集第10行到100行
-- 排序操作会造成顺序乱掉
select * from (select rownum rn,t.* from emp t) where rn<=100 and rn >=10; 
-- 多层嵌套rownum
select rownum,t.* from (select rownum rn,e.* from emp e order by e.sal desc) t where rn<=100 and rn >=10; 

-- rownum 不能跳行
-- 推荐使用
select * from (
       select rownum rn, e.* from (
              select * from emp order by sal desc
       ) e where rownum < 11
) where rn > 5;

26.视图

视图的作用
1、视图屏蔽掉一些敏感字段

2、保证总部和分部数据及时统一

-- 视图
-- 视图的概念：视图就是一个提供查询的窗口，所有的数据来自于原表
-- 查询语句创建表,可以跨用户查询
create table c_emp as select * from c##hikktn.emp;
select * from c_emp;
-- 创建视图，必须有dba权限
create view v_emp as select ename, job from emp;
-- 查看视图
select * from v_emp;
-- 修改视图
update v_emp set job = 'CLERK' where ename = 'ALLEN';
commit;
-- 创建只读视图
create view v_emp1 as select ename,job from emp with read only;

27.索引

-- 索引
-- 索引的概念：索引就是在表的列上构建一个二叉树
-- 大幅度提高查询效率，但是索引会影响增删下的效率
-- 单列索引
-- 创建单列索引
create index idx_ename on emp(ename);
-- 单列索引触发规则，条件必须是索引列中的原始值
-- 单行函数，模糊查询，都会影响索引的触发
select * from emp where ename ='SCOTT' ;
-- 复合索引
-- 创建复合索引
create index idx_enameAndJob on emp(ename,job);
-- 复合索引中第一列为优先检索列
-- 如果要触发复合索引，必须含有优先检索列中的原始值
select * from emp where ename ='SCOTT' and job ='MANAGER'; -- 触发复合索引
select * from emp where ename ='SCOTT' or job ='MANAGER';-- 不触发索引
select * from emp where ename ='SCOTT'; -- 触发单列索引

hikktn

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle基础命令入门

1.windows10安装Oracle 19https://www.oracle.com/database/technologies/oracle-database-software-downloads.html中间安装过程省略，可查看其他博客。2.安装PL/SQLhttps://www.allroundautomations.com/registered-plsqldev/当Oracle是最新的，那么相应的配套软件一定要安装最新，最新的软件不用使用繁琐的配置，软件里自带配置，.
复制链接

扫一扫