Oracle使用教程

最新推荐文章于 2024-05-11 14:27:59 发布
最新推荐文章于 2024-05-11 14:27:59 发布
阅读量2.1k 收藏 19
点赞数 2
分类专栏： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42437102/article/details/116942185
版权

Oracle基本篇

函数实操这块，没有附带结果，因为结果粘贴出来截图会导致文章篇幅很长，大家可以自己编写SQL测试，也可以粘贴我的SQL进行测试（本人亲测都是可以查询出来的），本文有不当之处欢迎指正，也就是抛砖引入供大家参考学习，谢谢大家。

一、SQL大致分类
类别语句
数据定义语言(DDL)：Data Definition Languagecreate、alter、drop、truncate
数据操作语言（DML）：Data Manipulation Languageinsert、update、delete
数据查询语言（DQL）:Data Query Languageselect
事务控制语言（TCL）:Transaction Control Languagecommit、rollback、savepoint
数据控制语言（Data Control Language）grant、revoke、create user
1. 数据定义语言（DDL）:Data Definition Language

CREATE TABLE: 用于创建表

CREATE TABLE TableNAME(
	columName1 dataType,
    columName2 dataType,
    columName3 dataType,
    ......
)

ALTER TABLE：用于对已有的表中添加、修改或者删除

ALTER TABLE TableName ADD columName dataType;
ALTER TABLE TableName DROP columName;
ALTER TABLE tableName MODIFY(columName dataType);
2. 数据操纵语言（DML）:Data Manipulation Language

INSERT: 新增数据

INSERT INTO TableName(columName1，columName2...) VALUES (value1，value2...);

UPDATE: 更新数据

UPDATE TabelName SET columName1=value1，columName2=value2... WHERE 条件;

DELETE: 删除数据

DELETE FROM TableName WHERE 条件;
3. 数据查询语言（DQL）:Data Query Language

SELECT: 查询数据

// 查询所有列
SELECT * FROM TableName;
// 条件查询
SELECT * FROM TabelName WHERE 条件；
..........
4. 事务控制语言（TCL）:Transaction Control Language

用来维护数据一致性的语句

COMMIT：提交，确认已经进行的数据改变
ROLLBACK：回滚，取消已经进行的数据改变
SAVEPOINT：保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变
5. 数据控制语言（Data Control Language）

用于执行权限的授予和收回操作

GRANT：授予，用于给用户或角色授予权限
REVOKE：用于收回用户或角色已有的权限
CREATE USER：创建用户
二、数据类型

列举实际工作中最常见的三种数据类型，还有char：固定字符串，TIMESTAMP：时间，CLOB：大文本，BLOB：二进制。

类型详情
NUMBER(p,s)数字类型，可以存储整数，也可以是浮点数，还有FlOAT/DOUBLE/INT, p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为, s是指小数位数。
VARCHAR2(length)存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。
DATE存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
三、序列的使用
// 创建序列
CREATE sequence sequenceName;
// 查询下一个值
SELECT sequenceName.nextval FROM dual;
// 查询当前值
SELECT sequenceName.currval FROM dual;
四、常用函数

演示数据来自于Oracle数据库SCOTT用户的员工表

1. 大小写函数
select upper(e.job) from emp e;
select lower(e.job) from emp e;
2. 数值函数
select round(e.sal,2) from emp e; -- 四舍五入，后面的参数表示小数点后保留的位数
select trunc(e.sal,2) from emp e; -- 直接截取，不在看后面位数的数字
select mod(10,2) from dual;       -- 求余
3. 日期函数
-- 查询emp表所有员工入职距离现在多少天
select round(sysdate-hiredate,2) from emp; 
select sysdate-hiredate from emp;
-- 算出明天此刻时间
select hiredate+1 from emp; 
-- 查询emp表中所有员工入职距离现在几个月
select months_between(sysdate,hiredate) from emp;
-- 查询emp表中所有员工入职距离现在几年
select months_between(sysdate,hiredate)/12 from emp;
-- 查询emp表中所有员工入职距离现在几周
select round((sysdate-hiredate)/7,2) from emp;
4. 转换函数
-- 日期转字符串
select to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
-- 字符串转日期
select to_date('1980-12-17 16:39:50','yyyy-mm-dd hh24:mi:ss') from dual;
5. 通用函数

Oracle中null与任意数字做算术，都为null

select ename,sal+nvl(comm,0) from emp;
6. 条件判断

Oracle中除了取别名用双引号，其他均用单引号

通用用法：

--- 等值条件判断
select e.ename,
   case e.ename
     when 'SMITH' then '史密斯'
       when 'ALLEN' then '艾伦'
         when 'JONES' then '约翰'
           else '无名'
             end "中文名"
from emp e;

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

Oracle专用用法：

select e.ename,
   decode( e.ename,
      'SMITH',  '史密斯',
        'ALLEN',  '艾伦',
          'JONES',  '约翰',
            '无名') "中文名"
from emp e;
7. 多行函数
select count(1)from emp e; 		-- 统计
select max(e.sal)from emp e;	-- 最大
select min(e.sal)from emp e;	-- 最小
select sum(e.sal)from emp e;	-- 求和	
select avg(e.sal)from emp e;	-- 平均
8. 分组查询

分组查询中，出现在group by 后面的原始列才能出现在select后面，没有出现在group by后面的原始列想要在select后面，必须使用聚合函数

-- 查询每个部门的平均工资
select e.deptno,avg(e.sal),--e.ename
from emp e
group by e.deptno;

-- 查询出平均工资高于2000的部门
select e.deptno,avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000;

-- 查询出每个部门工资高于800的员工的平均工资
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno;

-- 查询出每个部门工资高于800的员工的平均工资,然后平均工资高于2000的部门
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno
having in avg(e.sal)>2000;

where是过滤分组前的数据，having是过滤分组后的数据。

表现形式：where必须在group by之前，having是在group by之后。

9. 多表查询

内连接 inner join on：也叫等值查询,会产生笛卡尔积

select * from emp e;

-- 等值查询
select *
from emp e, dept d
where e.deptno = d.deptno;

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

外连接，分为左外连接和右外连接

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

Oracle专用: +号在哪边，就以另外一个表为主表

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

-- 查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
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 e2.deptno =d2.deptno;
10. 子查询
-- 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
select t.deptno,t.msal,e.ename,d.deptno
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;
11. 分页查询

rownum行号：当我们做select查询的时候，没查询出一条记录，就会在该条记录前加一个行号，行号从1开始，一次递增，不能跳着走

select rownum, e.* 
from emp e 
order by e.sal 
desc
五、视图

什么是视图：视图就是提供一个查询的窗口，所有的数据来源与原表。

视图的作用:

  1. 可以有效避免敏感字段，如工资。
  2. 可以保证数据及时统一，如库存。
-- 创建视图：
-- 跨用户查询
create view v_emp as select empno,ename,job,deptno from 用户.表明 with read only; 
-- 从本库创建视图
create view v_emp as select empno,ename,job,deptno from emp with read only; 

select * from v_emp; -- 查询视图
update v_emp set JOB = 'CLERK' where ename='ALLEN'; -- 修改视图 [不推荐]
commit;

创建视图要有dba权限，创建视图报警无权限窗口的解决办法：

赋予用户创建视图的权限：GRANT CREATE VIEW TO 用户;
赋予可以查询任何表的权限：GRANT SELECT ANY TABLE TO 用户;
赋予可以查询任何字典的权限：GRANT SELECT ANY DICTIONARY TO 用户;
六、索引

什么是索引？

​ 索引就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的，同时也会降低增删改的效率。

1. 单列索引
-- 创建单列索引
create index idx_ename on emp(ename);
select * from emp where ename = 'SCOTT';

单列索引触发规则：条件必须是索引列中的原始值，单行函数、模糊查询都会影响索引的出发。

2. 复合索引
-- 创建复合索引
create index idx_ename_job on emp(ename,job);

复合索引触发规则：第一列为优先检索列，如果要触发复合索引，必须包含有优先检索列中的原始值。

select * from emp where ename = 'SCOTT' and job ='xx'; -- 触发复合索引
select * from emp where ename = 'SCOTT' or job ='xx';  -- 不触发复合索引
select * from emp where ename = 'SCOTT'; -- 既有单列索引又有复合索引，触发单列索引
7、简单sql练习
1. select * from
-- 查看视图
select * from v_emp;

-- 查看scott用户下所有的对象
select * from tab;

-- 查询emp表的员工编号，姓名，工资，部门号，列名，大小写不敏感，但提倡大写
select e.empno  "员工编号",
       e.ename  "姓名",
       e.sal    "工资",
       e.deptno "部门号",
       e.comm   "奖金"
  from emp e;

-- 查询emp表的不重复的工作
select distinct job from emp;

-- 查询员工的编号，姓名，月薪，年薪（月薪*12)
select e.empno  "员工编号",
       e.ename  "姓名",
       e.sal    "月薪",
       e.sal*12 "年薪"
  from emp e;
  
-- 查询员工的编号，姓名，入职时间，月薪，年薪，年收入（年薪+奖金)  
select e.empno  "员工编号",
       e.ename  "姓名",
       e.hiredate   "入职时间",
       e.sal    "月薪",
       e.sal*12 "年薪",
       e.sal*12+nvl(e.comm,0)   "年收入" -- Oracle中null做算数运算，都为null，使用通用函数NVL(a,b) 当a不为null时使用a,为null使用b
  from emp e;
  
-- 伪表的使用
select 'hello' || ' world' "结果" from dual;
-- sysdate时间
select sysdate from dual;
2. where 条件的使用
-- 除了别名是双引号，其他都是单引号，字符串大小写敏感
select * from emp where ename = 'SMITH'; -- 写smith就查询不出来结果

-- 查询1980年12月17日入职的员工,to_date()日期转换
select *
  from emp
 where hiredate = to_date('1980-12-17', 'yyyy-mm-dd hh-mi-ss');

-- 查询工资大于1500的员工
select * from emp where sal > 1500;

-- 查询工资不等于1500的员工【!=或<>】
select * from emp where sal <> 1500;

-- 查询薪水在1300到1600之间的员工，包括1300和1600
select * from emp where sal between 1300 and 1600;

-- 查询薪水不在1300到1600之间的员工，不包括1300和1600
select * from emp where sal NOT between 1300 and 1600;

-- 查询入职时间在"1981-2月-20"到"1982-1月-23"之间的员工
select *
  from emp
 where hiredate between to_date('1981-02-20', 'yyyy-mm-dd') and
       to_date('1982-01-23', 'yyyy-mm-dd');

-- 查询20号或30号部门的员工，例如：根据ID号，选中的员工，批量删除
select * from emp where deptno in (20, 30);

-- 查询不是20号或30号部门的员工
select * from emp where deptno not in (20, 30);
3. 多行函数，group by、having
-- 统计emp表中员工总人数
select count(*) from emp;
-- *号适用于表字段较少的情况下，如果字段较多，扫描多间多，效率低，项目中提倡使用某一个非null唯一的字段，通常是主键 

-- 统计公司有多少个不重复的部门
select count(distinct deptno) from emp;

-- 统计有佣金的员工人数
select count(comm) from emp;
-- 注意：今天讲的这些多个行函数，不统计NULL值

-- 员工总工资，平均工资，四舍五入，保留小数点后0位
select sum(sal) "总工资", round(avg(sal), 0) "平均工资" from emp;

-- 查询员工表中最高工资，最低工资
select max(sal) "最高工资", min(sal) "最低工资" from emp;

-- 入职最早，入职最晚员工
select max(hiredate) "最晚入职时间", min(hiredate) "最早入职时间" from emp;

-- 按部门求出该部门平均工资，且平均工资取整数，采用截断
select deptno "部门编号", trunc(avg(sal), 0) "部门平均工资"
  from emp
 group by deptno;

-- (继续)查询部门平均工资大于2000元的部门
select deptno "部门编号", trunc(avg(sal), 0) "部门平均工资"
  from emp
 group by deptno
having trunc(avg(sal), 0) > 2000;

-- (继续)按部门平均工资降序排列
select deptno "部门编号", trunc(avg(sal), 0) "部门平均工资"
  from emp
 group by deptno
having trunc(avg(sal), 0) > 2000
 order by 2 desc;

-- 除10号部门外，查询部门平均工资大于2000元的部门，方式一【having deptno<>10】
select deptno, avg(sal) from emp group by deptno having deptno <> 10;

-- 除10号部门外，查询部门平均工资大于2000元的部门，方式二【where deptno<>10】
select deptno, avg(sal) from emp where deptno <> 10 group by deptno;
一个不懂技术的人
关注
  • 2
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
专栏目录
Toad For Oracle使用教程
11-10
Toad For Oracle使用教程详细讲解了如何使用Toad For Oracle工具操作数据库。
Oracle入门
qq_62898618的博客
02-19 1259
1.Oracle在安装时会自动创建和配置数据库（1个） 2.Oracle数据库服务器只有一个数据库以及至少一个数据库实例 3.数据库：存储数据的 4.数据库实例：当Oracle开启后，就启动一个进程，该实例用来存储Oracle的所有操作。 5.Oracle在安装完成后会自动产生多个用户 SYS----超级管理员 SYSTEM----普通管理员 SCOTT---最常见的普通用户（使用率最高） 默认密码：tiger 注意：第一次使用SCOTT用户，需要超...
Oracle数据库的安装及使用教程
热门推荐
Mr_XiMu的博客
07-09 3万+
Oracle数据库的安装及使用教程
Oracle数据库之 常用数据库对象（二）
最新发布
pursue_mony的博客
05-11 704
视图是一个虚表.视图建立在已有表的基础上，视图赖以建立的这些表称为基表.向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句.视图向用户提供基表数据的另一种表现形式.最大的优点就是简化复杂的查询。
全网Oracle基础最全教程
程序员乌拉的博客！
03-08 1万+
Oracle最全基础教程 用户与权限 创建用户 CREATE USER USER_NAME IDENTIFIED BY PASSWD; --创建用户 修改密码 ALTER USER USER_NAME IDENTIFIED BY NEW_PASSWD; --修改新密码 --修改用户密码 PASSWD USER_NAME; 删除用户 --可选参数 CASCADE DROP USER USER_NAME [CASCADE]; --注意： /* 在进行删除用户操作时，如果此用户已创建表，删除时需要加参数
快速入门到精通：一篇学会Oracle
weixin_63021300的博客
08-14 7686
Oracle全面的基础知识和知识点的举例，帮助初学者快速入门。 SQL语句，SQL查询，Oracle函数，代码块知识，条件判断/循环语句，索引、游标，锁，视图，存储过程，触发器，分区表，Oracle优化，建模
史上最全的ORACLE基础教程
目不识丁
11-30 1652
用户与权限 创建用户 create user 用户名 identified by 密码; 给用户修改密码 alter user 用户名 identified by 新密码; 修改自己的密码 password 用户名; 删除用户 drop user 用户名 [cascade]可选参数 cascade 在删除用户时，注意： 如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个...
Oracle的使用
m0_46203525的博客
09-17 1263
1、分页查询 oracle数据库中是没有limit关键字的，想在Oracle数据库中实现limit功能可以通过使用rownum来限制结果集行数。 分页 返回10~20条数据，tableName改为自己的表名 select * from (select rownum rn,t.* from tableName t) where rn<=20 and rn >10; rownum 行号 -- 使用where rownum <=2 进行分页 只有< 和<= 的判断 返回结果集
三.数据库基础-Oracle入门到精通学习这一篇就够了（详细）
ThatsThePrice的博客
03-07 4694
oracle入门到精通
oracel教程
03-26
oracel教程
TOAD FOR ORACLE 使用教程
02-05
TOAD FOR ORACLE的 教程 一个总体的描述
oracle使用教程
08-30
上面有初学的教程，很详细，有关用户管理的也有
Oracle一些常见用法
qq_19338473的博客
08-30 540
1、Decode,Case when： Decode：语法简介，只能应用a=b然后取c的情况，只能在查询中使用，不能作为判断逻辑，有一种很好的使用场景，在判断两个字段是否相等且认为同为空时也是相等(null=null不成立，null<>null也不成立，应表示为is null或is not null)，则可使用Decode(a,b,1,2)=1可放在where后简化代码(efa用到这一...
orcal入门，这一章足够
小白有个大牛梦
09-10 2889
oracle学习
Oracle基本语句和操作(超详细)
qq_61576523的博客
07-25 702
创建表空间）createtablespace表空间名datafile'磁盘位置dsize100m--表空间大小autoextendon--开启自增next10m;--超过了100m的话以每10m自增。
非常好的Oracle基础教程
w2393040183的专栏
06-16 2万+
http://www.blogjava.ne 字符串函数 LENGTH()    字符长度 LENTTHB()    字节长度；一个汉字内存中占用 2字节 LTRIM、RTRIM、TRIM 截串 SUBSTR(表达式，位置，长度) Oracle 无左右取串函数，但可以使用变通方式完成。 左取串：    SUBSTR('abcdefg', 1, 3) 右取串：    S
toad for oracle使用教程
06-28
Toad for Oracle 是一款常用的 Oracle 数据库管理工具，下面是 Toad for Oracle 的使用教程： 1. 安装 Toad for Oracle：从官网下载安装程序，安装 Toad for Oracle。 2. 连接数据库：打开 Toad for Oracle，点击...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值