Oralce数据库的详解解析（包括操作步骤）【1】

于 2018-09-05 21:40:17 发布
阅读量1.4k 收藏 1
点赞数 1
CC 4.0 BY-SA版权
分类专栏： 数据库操作 文章标签： Oracle 数据库 基本命令 查询 sql语句
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/erpang7/article/details/82430268
本文深入介绍了Oracle数据库，包括数据库概念、DBMS、数据结构、Oracle工作原理，以及安装PL/SQL Developer的步骤。接着，文章详细阐述了如何进行数据库查询操作，如SQL基本命令、数据类型、查询、排序、条件判断和模糊查询，并举例说明了如何使用这些命令。此外，还讨论了Oracle中的单行和组函数，如TO_CHAR和TO_DATE，以及分组和HAVING子句的用法。最后，介绍了伪列ROWID和ROWNUM的概念及其在查询中的应用，并探讨了子查询的各种形式，如单值、多行单列、多行多列和分页查询。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、数据库的介绍和基本了解

1.概念 :

简而言之：是一个存储管理数据的软件( 做数据”持久化”的软件 )

什么是数据库？

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

什么是数据库管理系统？

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

数据库分类：

数据库类型英文缩写/全拼常见数据库代表
关系数据库RDBMSoracle  db2  mysql  sqlsv

                       面向对象数据库

  
NOSQL数据库HBase  Redis  MongoDB 
Java相关：MYSQL和Oracle  

 关系数据库存放数据的结构：

 

1) 二维表( table ) : 存放数据的基本单元

2) 行 (row, 记录recode) : 一组相关的业务数据

3) 列 (column, 字段 ) : 业务数据里一个具体的属性信息

   --- 主键( primary key , 简称pk) : 可以唯一标识表里的一条记录 , 非空唯一

   --- 外键( foreign key, 简称 fk ) : 体现两个表记录之间的关系 , 可以重复可以为

                                null,但是值必须来自于另外一个表的主键

  4. 安装软件-plsqldev.exe

1) 安装目录最好不要有中文

2) 输入的密码 --- 管理员密码 ( System  sys )

3) 安装成功后 , 解锁”hr用户”( 测试用户 )

  5. Oracle工作原理

   

 

   6. 常见命令解析

     1) SQL (结构化的查询语言) : 数据库领域的 “普通话”

       ① 不区分大小写  ② 必须以分号结尾

     2) PL/SQL : oracle公司的 “方言”

     3) Sqlplus命令:  sqlplus工具所支持的命令( 不能操作数据 ) , 只在sqlplus里使用, 不

                    区分大小写,通常被”缩写” , 例如: conn

7.基本数据类型

分类

类型名称

说明

整数类型

tinyInt

很小的整数

smallint

小的整数

mediumint

中等大小的整数

int(integer)

普通大小的整数

小数类型

float

单精度浮点数

double

双精度浮点数

decimal（m,d）

压缩严格的定点数

日期类型

year

YYYY  1901~2155

time

HH:MM:SS  -838:59:59~838:59:59

date

YYYY-MM-DD 1000-01-01~9999-12-3

datetime

YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59

timestamp

YYYY-MM-DD HH:MM:SS  1970~01~01 00:00:01 UTC~2038-01-19 03:14:07UTC

文本、二进制类型

CHAR(M)         

M为0~255之间的整数

VARCHAR(M)  

M为0~65535之间的整数

TINYBLOB

允许长度0~255字节

BLOB

允许长度0~65535字节

MEDIUMBLOB

允许长度0~167772150字节

LONGBLOB

允许长度0~4294967295字节

TINYTEXT

允许长度0~255字节

TEXT

允许长度0~65535字节

MEDIUMTEXT

允许长度0~167772150字节

LONGTEXT

允许长度0~4294967295字节

VARBINARY(M)

允许长度0~M个字节的变长字节字符串

BINARY(M)

允许长度0~M个字节的定长字节字符串

二、oracle数据库的查询操作

（在Oracle数据库中有默认的测试用户：username:hr     password：在你安装软件时自己设置 请记下来）

（1）基本查找

①查看employees表

-- hr用户里有一个employees表
  select employee_id,first_name,last_name,salary from employees;

②-- 查看表里所有的字段信息 

-- 查看表里所有的字段信息  *
   select * from employees;    -- 开发不建议使用,语义不明确

③-- 可以对查询结果做数学运算

--查询员工的编号，姓名，年薪，部门编号(此处salary*12便是数学运算)

注意 : 数字类型可以应用所有运算符号 ;日期类型可以( + - ) ,计算单位为天 ;字符串类型不能使用运算符号( + )

select employee_id,first_name,salary*12,department_id;

④-- 字符串连接符：||

-- 请显示公司员工的编号,姓名,完整邮箱(后缀@zparkhr.com.cn)

 -- 请显示公司员工的编号,姓名,完整邮箱(后缀@zparkhr.com.cn)
select employee_id,email||'@zparkhr.com.cn'  from employees;

-- 请打印员工的完整姓名
select last_name||' '||first_name from employees;

 注意: 在sql命令里如果使用 字符串字面值 或者 日期字面值 , 则必须使用’ ’表示

⑤-- 查询结果里的字段可以定义别名

-- select 字段名 as 别名,字段名,.... 其中as可以省略

-- 如果别名里含有特殊字符or需要区分别名的大小写,可以用"双引号"标识

select last_name||' '||first_name as name from employees;
select employee_id id,last_name,salary*12 “anual salary” from employees;

⑥ -- 去除查询结果里的重复数据 distinct(如果结果里有多个字段,多个字段联合去重)

    -- 请打印公司里所有经理的编号

  -- 请打印公司里所有经理的编号
       select distinct manager_id from employees;
       select distinct job_id,department_id from employees;

三、Oracle数据库的排序命令

①-- 请查询员工的详细信息,并对结果按工资排序

-- 请查询员工的详细信息,并对结果按工资排序
       select * from employees order by salary desc;

②-- 请查询员工编号,姓名,工资,部门编号,并按照部门编号升序显示

-- 请查询员工编号,姓名,工资,部门编号,并按照部门编号升序显示
      select employee_id id, first_name name,salary , department_id 
      from employees order by department_id;

③-- 在指定排序条件时,可以通过数字(查询结果里字段的下标)指定

-- 在指定排序条件时,可以通过数字(查询结果里字段的下标)指定   
      select employee_id id, first_name name,salary , department_id 
      from employees order by 4 ;

④-- 在指定排序条件时,可以通过列的别名排序

-- 在指定排序条件时,可以通过列的别名排序
      select employee_id id, first_name name,salary , department_id 
      from employees order by name;

⑤-- 如果条件里有多个字段,则先按第一个字段排序,然后再按第二个字段排序

   -- 如果条件里有多个字段,则先按第一个字段排序,然后再按第二个字段排序
      select * from employees order by department_id,salary ;
   -- 先按部门编号升序,再按工资降序
      select * from employees order by department_id,salary desc;
   -- 都降序
      select * from employees order by department_id desc,salary desc;

⑥-- 在oracle里,null被视作为"最大值"

select * from employees order by commission_pct;

四、Oracle数据库条件判断命令--->where

① 比较运算符   =  !=   >   >=  <  <=

-- 字符串字面值比较时区分大小写

-- 请查询工资大于10000的员工信息

   -- 请查询工资大于10000的员工信息
        select * from employees where salary>10000;

     -- 请查询last_name叫King的员工信息
        select * from employees where last_name = 'King';

②逻辑运算符  and  or

-- 请查询50部门,工资大于5000的员工信息

     -- 请查询50部门,工资大于5000的员工信息
        select * from employees where department_id=50 and salary>5000;

③特殊谓词 

1）in    not in   --- 多个值(同一字段)的或运算

-- 请打印50,70,90部门的员工信息

-- 请打印50,70,90部门的员工信息
          select * from employees
          where department_id=50 or department_id=70 or department_id=90;
          select * from employees
          where department_id in(50,70,90);

2）between...and  ,  not between...and  --- 查询区间值(包括边界值)

      -- 请查询工资介于5000到10000之间的员工

-- 请查询工资介于5000到10000之间的员工
   		select * from employees where salary<=10000 and salary>=5000;
   		select * from employees where salary between 5000 and 10000;

3） 空值处理  is null  ,  is not null

-- 查询所有提成为null的记录

select * from employees where commission_pct=null;  --error

- 一个表达式里如果有null,则结果一定为null

select * from employees where commission_pct is null;

五、Oracle数据库模糊查询命令

-- 请查询姓名(last_name)是以'S'开头的员工信息

-- 请查询姓名(last_name)是以'S'开头的员工信息
        select * from employees where last_name like 'S%';

 -- 请查询姓名(last_name)是由四个字符组成的员工信息

-- 请查询姓名(last_name)是由四个字符组成的员工信息
        select * from employees where last_name like '____';

-- 请查询姓名(last_name)是以'S_'开头的员工信息

-- 请查询姓名(last_name)是以'S_'开头的员工信息
       select * from employees where last_name like 'S\_%' escape '\';
       select * from employees where last_name like 'S(_%' escape '(';

六、Oracle数据库提供的功能函数

1.单行函数

1）作用于指定表里的每一行数据,执行一次,得到一个结果

-- abs(num) 计算绝对值

 -- abs(num) 计算绝对值
   		   select abs(salary) from employees;  --107次
   		   select abs(-2) from employees;      --107次

 -- oracle提供了一个dual表(哑表),用来维护select语句的完整性

-- dual单行单列,不允许用户操作(增删改)

-- dual单行单列,不允许用户操作(增删改)
   		   select abs(-2) from dual;   -- 1次

-- sysdate 当前数据库系统时间(日期值)

 select sysdate from dual;  --标准日期格式 dd-mon-rr

 2) to_char( 日期值 , ‘格式字符串’)

 作用: 将给定的日期值,按照格式字符串的形式变成 字符串

-- 请显示详细的当前系统时间(年月日星期小时分钟秒)

-- 请显示详细的当前系统时间(年月日星期小时分钟秒)
   		  select to_char(sysdate,'yyyy-mm-dd,day,hh24:mi:ss') from dual;

重要作用: 提取日期的各个组成部分

-- 请打印97年入职的员工信息(两种方式)

-- 请打印97年入职的员工信息(两种方式)
   			select * from employees where hire_date like '%97';--模糊查询
   			select * from employees where to_char(hire_date,'yyyy')=1997;--单行函数查询

-- 请打印50部门的员工信息,并按照入职月份升序排列

-- 请打印50部门的员工信息,并按照入职月份升序排列
   			select * from employees where department_id=50
   			order by to_char(hire_date,'mm');

3）   to_date( 字符串日期显示 , ‘格式字符串’)

         作用: 将给定的字符串时间显示,按照格式字符串的要求转换成日期值

-- 请打印你出生那天是星期几

select to_char(to_date('1996-06-14','yyyy-mm-dd'),'day') from dual;

2.组函数

1）分组语句 -- group by

-- 请打印各部门的最高工资,并升序排列

 -- 思路:  1. 数据的来源 --- from employees

 --        2. 有没有筛选条件 --- 无

 --        3. 分组条件 --- group by department_id

 --        4. 统计数据 --- select department_id,max(salary)

 --        5. 排序 --- order by max(salary) 或者 order by 2

 -- 合并:

select department_id,max(salary) from employees 
         group by department_id  order by 2;

-- 请打印97年各个月份入职的员工人数

 -- 思路: 1. from emplyoees;

 --       2. where hire_date like '%97'

 --       3. group by to_char(hire_date,'mm')

 --       4. select to_char(hire_date,'mm'),count(*)

 --       5. order by to_char(hire_date,'mm')

 -- 合并:

select to_char(hire_date,'mm'),count(*) from employees
   	    where hire_date like '%97'
        group by to_char(hire_date,'mm') order by 1;

硬性的语法规定：

2）Having子句

1. 作用: 对分组后的数据进行条件筛选( 按组筛选 )

2.语法：

3.案例：

-- 请打印各部门编号,人数( 只显示人数大于2的信息 )

-- 请打印各部门编号,人数( 只显示人数大于2的信息 )
   		select department_id,count(*) from employees
   		group by department_id   having count(*) > 2;

4. Where 和 having的区别

     1) where在分组前执行,对数据按”行”筛选,不能出现组函数

     2) having发生在分组之后,对组进行筛选 , 不能出现对行信息的筛选

     注意：如果一个功能where和having都可以完成,则优选where

-- 请打印30和50部门的编号,人数 ,最高工资

-- 请打印30和50部门的编号,人数 ,最高工资
   		select department_id , max(salary),count(*) from employees
   		where department_id=30 or department_id=50 
   		group by department_id;
   
   		select department_id , max(salary),count(*) from employees
   		group by department_id
        having department_id=30 or department_id=50;

3）Select语句的总结

   1. 语法:  select...from...where...group by...having...order by...

   2. 执行顺序

      1) from 子句  --- 数据的来源

      2) where子句  --- 对数据进行筛选(逐行筛选)

      3) group by子句 --- 对满足要求(where条件成立)的数据进行分组

      4) having子句 --- 对分组后的数据按条件筛选

      5) select子句 --- 生成结果数据

      6) order by子句 --- 对结果数据排序

4）Case...when

 1. Sql命令中的分支结构,类似于java中的switch...case

  2. 语法:

3.案例：

1.-- 请打印员工的编号,姓名,工资,工资级别

       -- 提示:工资级别( A: <6000   B: <10000  C: < 15000   D: >=15000 )

-- 请打印员工的编号,姓名,工资,工资级别
       -- 提示:工资级别( A: <6000   B: <10000  C: < 15000   D: >=15000 )
        select employee_id,last_name,salary,
                    ( case when salary<6000 then 'A' 
               		 when salary<10000 then 'B' 
               		 when salary<15000 then 'C'
                      else 'D' end ) "工资级别"
        from employees;

2.----统计50部门'ST_MAN'和'ST_CLERK'两种职位的人数

  department_id

    ST_MAN

    ST_CLERK

      50

       5

       20

select department_id,
   sum(case when job_id='ST_MAN' then 1 else 0 end ) ST_MAN,
       sum( case when job_id='ST_CLERK' then 1 else 0 end ) ST_CLERK  
from employees where department_id=50 group by department_id;

3. 有Score表如下: （列转行）

     NAME

     COURSE

      SCORE

     张三

      语文

       80

     张三

      数学

       90

     张三

      英语

       85

     李四

      语文

       70

     李四

      数学

       80

     李四

      英语

       89

将写出sql语句,将表里数据按照下面的方式显示

  NAME

    语文

     数学

     英语

  张三

     80

      90

     85

  李四

     70

      80   

     89

提示:case..when,组函数、练习表参看score.sql文件  start xxx.sql

select name , sum(case when couse='语文' then score end ) "语文"
from  student  group by name;
-- sum位置可以使用除了count之外的其他4个组函数

七、Oracle数据库中的伪列 ( rowid  rownum )

1. 概念: 数据库表里不存在的列 , 无法通过select * 查询到列

      select  *,rownum,rowid  from employees;  -- error

     改进:   

    select employees.* , rownum,rowid  from employees; 

      select e.* , rownum , rowid from employees ;

  2. Rowid

1) 当前记录在数据库里的唯一标识 , 是通过记录所在空间的物理地址计算得到

   Pk : 在表里唯一标识一条记录

  1. 通常在查询过程中, db sv通过rowid定位记录
  2. 使用场景: 记录里的业务数据完全一样,无法区分时,通过rowid区分
Delete xxxx  from employees where  rowid > ( select ....min(rowid)  );

  3. Rownum

1) db sv 会为每一条出现在结果里的记录编号, 从1开始(连续的)

2) 案例:

      -- 请查询员工表里的前5条记

select * from employees where rownum<=5 ;

      -- 请查询员工表里的第6到第10行记录

  select * from employees where rownum between 6 and 10;  --error

      -- rownum必须从1开始用,只能对rownum使用 <  <=  >=1  =1

八、Oracle数据库中的子查询

1）子查询提供一个值( 查询结果是单行单列 )

-- 请打印公司里工资高于平均工资的员工信息

-- 请打印公司里工资高于平均工资的员工信息
      -- 1. select avg(salary) from employees;  -- result
      -- 2. select * from employees where salary > result ;
         select * from employees
         where salary>( select avg(salary) from employees );

2）子查询结果是多行一列(多个值)  --- [ 了解 ]

-- 请打印与Steven(first_name)在同一部门工作的员工信息

-- 请打印与Steven(first_name)在同一部门工作的员工信息
       1. select department_id from employees where first_name='Steven';     
           (90,50)
       2. select * from employees where department_id in (result);
       select * from employees where department_id in 
         (select department_id from employees where first_name='Steven');

3）子查询结果是多行多列(虚拟表)

    -- 可以针对”虚拟表”继续定义查询命令(二次查询)

-- 请打印公司里工资最高的五位员工信息

-- 请打印公司里工资最高的五位员工信息

-- 分析:  排序 order by salary desc   筛选条件: where rownum<=5
  			 select * from employees where rownum<=5 order by salary desc;    		
-- 错误原因: 查询了表里的前五行数据,对结果按工资降序排列

-- 思路: 1.对表里所有数据按照工资降序排列

-- select * from employees order by salary desc; --- result(tab1)

--2.从tab1里提取前五行数据

--  select * from tab1 where rownum <= 5;

select * 
from (select * from employees order by salary desc)
 where rownum<=5;

4）数据分页(分段显示结果数据)【重点】

-- 请打印公司里工资最高的 第6 到 第10 员工信息

-- 打印last_name是以'D'开头的第3到第5名员工信息

 1. select * from employees where last_name like 'D%'; --- t1
 		 2. select t1.*,rownum rn from t1 where rownum<=5;    --- t2
 		 3. select * from t2 where rn between 3 and 5;

5）关联子查询

   提示: 表的别名可以用来区分查询语句

       -- 请打印各部门工资最高的员工信息

select * from employees e1 
          where salary = ( select max(salary) from employees e2
                    where e2.department_id = e1.department_id );

[未完待续，请看Oracle数据库操作（2）~]

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员巨轮

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值