2019.8.13 oracle数据库操作

oracle

在这里插入图片描述

一.前言

oracle的简介

  • oracle中字符串和日期都用单引号表示,字符串大小写敏感(mysql中大小写不敏感),日期格式敏感。
  • 列的别名用双引号表示。
  • sql语句结果返回的是单条记录(如组函数),还是多条记录,select 后面是记录中可以显示的字段、from后面是数据表集合
  • sql中null判断不能直接通过 = 或者 != 判断,而是通过is null 或者 is not null 来判断。
  • 空值null的问题,集合中如果有null,则不能用于not in(因为 not in 等效于 and,并列都满足才可以);所以可以对集合中通过where xxx is not null来将null去掉。但in后面集合中可以有null值(in类似于or,任何一个值满足就可以)。
  • 在有group by的查询语句中,select后面指定的字段要么就包含在group by语句的后面而作为分组的依据,要么就包含在聚合函数中。

1.进入Oracle数据库,下面的命令是连接远端的oracle数据库的方式*(注意scott是数据库的一个开放用户账号。密码是自定义的,我是root。好像我自己的管理员账号是id/id)
(5560端口是直接图形化显示oracle的普通用户的命令行形式,本质就是下面sqlplus的图形网页版(http: //192. 168.37.128(远端oracle的ip):5560/isqlplus/)
)/1158端口

在这里插入图片描述
如果本地接连数据库的话:
sqlplus scott/root 就连接上本地的安装的oracle数据库服务器了。
sqlplus sys/id(我自己的管理员密码奥!) 就连接上本地的安装的oracle数据库服务器管理员账号。

二. 一些概念

数据文件:是数据库的物理存储单元oracle数据是存储在表空间中的,实质是表空间中的数据文件内的。一个表空间中可以有一个或者多个数据文件,一个数据文件只能属于一个表空间,且一旦数据文件加入表空间后,就不能删除这个数据文件了,除非删除所属的表空间。
表空间:是oracle数据库对应物理数据库上的数据文件(ORA或者DBF文件)的逻辑(虚拟的)映射。一个数据库在逻辑上被划分为一到若干个表空间。每个表空间由同一个磁盘上的一个或者多个数据文件组成的。
oracle-----用户(如scott)------表空间

三.基本查询命令

1.查看当前访问数据库的用户名:show user

在这里插入图片描述

2.查看当前用户下的数据表(区别于mysql中是show tables,表示当前数据库下的所有表)

在这里插入图片描述
注意:上图中第一个是部门表,第二个是员工表,第三个是奖金,第四个是工资级别

3.查看表结构:(这边和mysql中查看表结构的命令 相同)

在这里插入图片描述
注意:一是员工号,二是员工名,三是员工职位,四是经理,五是入职时期,六是月薪,七奖金,八是部门号

4.oracle数据库中一些辅助功能:
  • 清屏window下是host cls、linux下是host clear
  • 设置命令行记录的长度显示当前行宽:show linesize;set linesize xxx; 还有设置页的记录数 set pagesize 20;
  • 设置命令行的列宽: 字符串列——col ename(列名) for(是format的前缀) a8(a表示字符串,8表示8个长度字符串)、col age for 9999(9表示数值类型,四个9表示4位长度)
  • / 表示执行上一句sql语句ed:表示编辑上一条的dml语句。
  • oracle中修改语句的方式——由于有时语句过长,重写很麻烦,所以可以通过c命令修改,就是change缩写。当然还有一种简单的方式:ed,是edit缩写,直接将上一条sql语句文件中修改。
  • sqlplus 命令行中有一个可以计算语句的执行时间的命令:( set timing on )/(set timing off)
    在这里插入图片描述
  • 范德萨
5.查询所有员工信息

select * from emp;(注意,下图查询出的结果,显示的样式上面有修改过。结果是一样的)
在这里插入图片描述

6.查询出信息中null的分析

首先明确:null是无效的,未指定的,不可预知的值,所以怎么能操作呢;null不是空格或者0奥!
在排序order by 字段名;中,如果有null值,则oracle中null最大奥!记住,但可以通过 nulls last来让null在最后,排序那边有案例演示。

  • sql中的结果的表达式中包含有null的话,就都为null,所以要有过滤null;可以通过——nvl(com,0):表示如果字段com为null,则用0替代,不是就本身com字段。
  • 有时候某列中有null的话,可以通过条件筛选where xxx is not null 来筛选出不是空的那些列内容。
  • 实际上sql中null永远!=null;怎么理解?不能直接通过=、即等号判断,要通过is判断
    在这里插入图片描述
    1.sql trim()函数去掉两头空格
    sql语法中除了直接去除两头空格的函数trim(),还有ltrim()去除左空格rtrim()去除右空格。
    select ltrim(rtrim(UsrName));
7.oracle连接符“||”,类似java中的字符串连接+


在这里插入图片描述
这个连接符可以进行组合字符串显示
在这里插入图片描述

8.模糊查询

like关键字,有两个字符注意:%(表示任意长度的任意字符串)_(表示一个字符的任意字符串)

9.比较运算符

在这里插入图片描述

10.逻辑运算

在这里插入图片描述

11.优先级

在这里插入图片描述

12.排序查询

(1)order by 字段名 默认是升序; desc是降序。
(2)SELECT ename,sal,sal*12 年薪 FROM emp ORDER BY 3; order by后面是列的顺序号。
在这里插入图片描述
在这里插入图片描述

四.基本查询中常见函数——核心就是字符、日期、数值、类型转换的操作

前面见到了nvl滤空函数、concat连接函数;

(1)函数类型:单行函数、多行函数(如max操作至少要两个数值)
(2)单行函数分类:有下面六种,最后一种是条件表达式函数; 注意字符串用单引号,数值不用

在这里插入图片描述

一.首先是字符函数操作:

1.获取字符串中指定长度字符串
在这里插入图片描述
2.字符串的大小写转换
在这里插入图片描述
3.查找字符串中字符的位置
在这里插入图片描述
4.对字符串的填充
在这里插入图片描述
在这里插入图片描述
5.字符串中字符的替换
在这里插入图片描述

二.数值函数操作:

在这里插入图片描述
1.四舍五入——round(数值,保留位数);
在这里插入图片描述
2.截断——trunc(数值,直接从第几位截断后面所有)
在这里插入图片描述

三.日期函数操作:sysdata 表示当前时间

mysql中查看当前时间:select now();
oracle中查看当前时间:select sysdata from dual
1.可以通过to_char将时间转为指定字符串格式
在这里插入图片描述
在这里插入图片描述
2.日期是可以相加减数字的,日期和日期之间不能加,只能减,因为加是无意义的。
在这里插入图片描述
2.1oracle中日期的范围比较可以通过日期转为指定个数的字符串,通过字符串的日期范围来比较日期的范围。因为oracle中数据类型会隐式自动转换的,但比较的话,必须是同样格式的比较,因为同样格式的,转换的话才能还是同样格式的,这样才好比较
在这里插入图片描述
3.计算当前月的最后一天
在这里插入图片描述
日期其他函数:(了解即可)
在这里插入图片描述
4. 日期函数next_day:表示当前时间的下一个星期几的日期
在这里插入图片描述

5.round对于日期的用法

在这里插入图片描述
在这里插入图片描述

四.数据类型的转换——显示转换和隐式转换

注意!!!字符串是用单引号奥。

在这里插入图片描述
1.数值——字符串——日期之间的 转换函数 如下图:
在这里插入图片描述

1.1首先日期向字符串的转换格式函数——to_char

在这里插入图片描述
实验:

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh(也可以加24):mi:ss"今天是"day') "日期" from dual;

在这里插入图片描述

1.2 数值向字符串的转换格式函数——to_char

在这里插入图片描述
实验:
在这里插入图片描述

五.通用函数

什么是通用函数?——这些函数可以适用于任何数据类型,同时适用于null
1.nvl(字段,0)/(字段,‘xxx’)表示该字段为null的话,则为0,不为空不变——也叫滤空函数。
2.nvl2(a,b,c)表示当a为null时,返回c,否则返回b。
3.nullif(a,b)判断两个值是否相同,相同为空,否则返回第一个a;
在这里插入图片描述
4.coalesce函数
在这里插入图片描述

六.条件表达式

1.何为条件表达式函数呢?

即在sql语句中使用if-then-else完成条件判断逻辑

2.实现的方式

(2.1)case表达式:sql99的语法,类似basic,比较繁琐
(2.2)decode函数:oracle自己的语法,类似java,比较jian简单

  1  select ename,job,sal before,
  2  case job when 'president' then sal+1000
  3           when 'manager' then sal+800
  4           else sal+400
  5  end after
  6  from emp;
  =================================
  
ENAME      JOB           BEFORE      AFTER
---------- --------- ---------- ----------
SMITH      CLERK            800       1200
ALLEN      SALESMAN        1600       2000
WARD       SALESMAN        1250       1650
JONES      MANAGER         2975       3375
MARTIN     SALESMAN        1250       1650
BLAKE      MANAGER         2850       3250
CLARK      MANAGER         2450       2850
SCOTT      ANALYST         3000       3400
KING       PRESIDENT       5000       5400
TURNER     SALESMAN        1500       1900
ADAMS      CLERK           1100       1500
JAMES      CLERK            950       1350
FORD       ANALYST         3000       3400
MILLER     CLERK           1300       1700
sun                       10000      10400

在这里插入图片描述

条件的案例

这个例子里面用到了组函数、条件语句等语句完成了sql的逻辑操作,感觉比在java中完成这样的逻辑要简单一点。
在这里插入图片描述
在这里插入图片描述
第一种 sql语句通用的条件
在这里插入图片描述
第二种——oracle自己的条件函数
在这里插入图片描述

七.多行函数 (或者叫组函数、分组函数)

什么叫多行函数?实际也叫分组函数,其作用于一组数据,并对一组数据只返回一个值奥!!!。
(1)如max、count、avg、min、sum; 注意!!!,多行函数输出结果是一个值
(2)注意!! 组函数会自动滤空,就是为null的不参与操作,但是利用nvl滤空函数,可以让组函数不会过滤掉null的部分。因为nvl已经将null转为非空了。
基于常见的上面几个函数,下面分析分组函数
在这里插入图片描述
在这里插入图片描述
排序、分组、内外连接、分页这几个最基本的也最核心。

1.分组group by特点:—— 必须满足这条件,否则oracle编译出错

所有包含在select语句中,但是未包含分组函数中的列,都必须包含于group by子句的后面奥!!!group by子句后面包含的列,前面select语句中可以没有
如:

在这里插入图片描述

2.多列的分组——先按照第一列分组,如果相同,再按照第二列分组,以此类推。和排序类似奥!

实验:表示按照部门中不同的职位统计员工工资的总和,
在这里插入图片描述

3.多条件的排序——先按照第一列排序,如果相同,再按照第二列排序,以此类推

实验:多条件排序——按照部门号升序排列,然后同部门中薪水按照升序再排列。
select * from emp order by deptno,sal;

4.分组后,对于分组的结果,可以再继续过滤——having

和where类似,就是将满足条件的记录过滤出来
在这里插入图片描述
实验: 已经分组后的记录,又按照一定条件进行过滤——下面是查询出不同部分的平均薪水,然后又过滤出大于2000的记录
在这里插入图片描述
having和where类似,都是过滤一些条件的。区别是where后面不能是组函数的表达式,而having可以是组函数的条件过滤
在这里插入图片描述

小结

条件过滤——分组——多表连接查询(左右外连接)——排序

八.多表查询

(1)概念

1.笛卡尔积:列数相加,行数相乘。
2.多表查询就是通过连接条件,从笛卡尔积全表中选出正确的记录。连接条件个数至少是N-1个,N表示表的个数。
如:select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno ;
从上面语句就发现(1)首先是从表emp 和 dept 两个表中查询;即from emp e,dept d(2)两个表,连接条件至少2-1个,即where e.deptno=d.deptno;(3)最后查询的字段有哪些,自己需要什么就什么;

(2)多表的连接类型

在这里插入图片描述
1.等值连接查询
select e.ename,e.sal,d.dname from emp e,dept d where e.deptnum=d.deptnum ;

2.不等值连接查询
查询员工表中员工薪水在哪个等级:
在这里插入图片描述
3.外连接
效果(作用):将指定表中某些不成立的字段,仍然包含在最后的结果中
这样的sql语句书写过程:首先从表开始 from emp e,dept d,然后从连接条件开始(两张表至少一个条件2-1) 即where e.deptno=d.deptno

在这里插入图片描述
外连接:左右连接也是多表连接,即也是多表的笛卡尔积后,筛选出满足一定条件后的记录。
在这里插入图片描述
4.自连接:缺点是不适合大表,因为两张相同表,笛卡尔积就是平方个记录中根据连接条件查询出满足连接条件的记录(结果)。
在这里插入图片描述

在这里插入图片描述

九.子查询

作用: 就是减少有关联的查询语句的书写,不用分开,直接组合在一条语句上。
实际上,子查询就类似于查询的嵌套,但是注意子查询在主查询之前一次执行完成,因为子查询的结果被主查询使用
子查询分类 单行子查询(子查询语句返回结果是单行的)、多行子查询(子查询语句返回结果是多行的)

1.子查询的注意事项:

在这里插入图片描述
子查询可以用于where、select、having、from关键字后面。
(1)where后面使用子查询的情况:
在这里插入图片描述
(2) select后面使用子查询的情况: 注意只能是单行子查询。 因为关系型数据库中一条记录中其他字段后对应一个唯一的内容奥,任何一条记录总都不可以一对多内容,非关系型数据库中可以
在这里插入图片描述
下面图很重要!!!
在这里插入图片描述
实际上,除了select后面显示的字段可以是表中没有的字段,但要满足一条记录中不能有重复内容原则就可以(每条记录中的任何列都只能有唯一性),from后面表名实际上表示一个集合,只要from后面满足是一个集合,就可以直接放于from后面用作于select查询的目标
在这里插入图片描述

(3)子查询语句在having后面: 首先要知道having和where区别:能否使用组函数,组函数是作用于一组数据,最终并只返回一个值。
分析下面语句的特点:
1)首先条件筛选为啥用having,而不用where?因为条件中内容是组函数(多行函数:就是作用于一组数据,并对一组数据只返回一个值奥)
2)select 后面的显示字段中有组函数的时候,其他的显示字段必须要在分组group by中,否则出错
3)

在这里插入图片描述
(4)子查询可以放在from关键在后面,由于from后面通常是查询的表,实际上就是一个集合,所以子查询如果结果是一个集合的话,就可以放在from关键字的后面,表示select查询的目标。
在这里插入图片描述
(5)子查询和主查询可以不是同一张表,只要子查询的结果主查询中能用就可以。
在这里插入图片描述
(6)不同类型子查询,使用不同的操作符:

  • 单行子查询只能用于单行操作符(如子查询结果用于>后面,大于号后面应该是一个明确的单个值,所以子查询只能是单行子查询结果。如下是错误的,等于号后面返回的子查询是一个集合结果。肯定不正确)。
  • 同理多行子查询(返回多条记录结果)用于多行操作符(in、any、)。
  • 注意一下,这边的单行或者多行是对于子查询结果而言的,就是说这些操作符是直接用于子查询上的。如in、any多行操作符是用于多行子查询上的。
  • 在这里插入图片描述
    就是说:这些单行操作符后面的子查询结果必须只能是单条记录的子查询,因为这些单行操作符后面必须是明确的单个值,不能存在模糊不定的多个可能值奥!
    在这里插入图片描述
    多行操作符in的使用: in表示后面集合中的内容都包含在内的
    在这里插入图片描述

多行操作符any的使用: any是后面集合中的内容任意一个比较。重在比较
在这里插入图片描述
多行操作符all的使用: all是后面集合中的内容所有的比较。重在比较
在这里插入图片描述

十.集合运算(查询的最后一个了)

就类似数学上两个集合的操作:交集、并集、差集操作 (集合运算越多,效率越低,不是非常建议使用)

集合运算注意原则:多个要集合运算的语句,必须字段个数、数据类型相对应奥!这是一个前提。所以如果不满足的话,可以凑满足这个条件。

在这里插入图片描述
在这里插入图片描述

并集就类似于两个集合的相加操作

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

十一.SQL中DDL(数据定义语言:创建表,删除表等)、DML(数据操作语言:增删改查操作)

插入操作:
(1) 和mysql中插入语句相同:insert into 表名(字段,字段,字段) values(字段对应的内容,内容,内容);
(2)可以将字段内容部分用地址符来表示,这样可以灵活取值。
在这里插入图片描述
(3)插入数据的第三种方式:可以批量插入数据,不一定要values关键字,直接替换为select语句,但是子查询语句中字段要和插入语句中字段一样。
在这里插入图片描述
更新操作
在这里插入图片描述
更新操作和mysql中差不多,很相似。同时注意更新的条件,否则就更新表中所有数据奥!

在这里插入图片描述
删除操作
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

事务

oracle中事务是默认自动开启的,那么是从什么时候开始的呢?
起始标志:第一条DML语句(数据操作语言的语句:增删改查)。
结束标志:可以显示commit 提交 或者 rollback 回滚。
中间可以试着保存点:savepoint a ;
回滚到保存点:rollback to savepoint a;
在这里插入图片描述

下面开始DDL语言

上面都是一些基本的dml操作语言,下面开始数据操作语言的了解。
(1)表空间是oracle数据库的逻辑单元。一个数据库下可以建立多个表空间,一个表空间可以建立多个用户、一个用户下可以建立多张表
(2)oracle数据库和其他数据库的区别在于:表和其他的数据库对象都是存储在用户下的。

创建表方式一

在这里插入图片描述

创建表方式二

在这里插入图片描述

向表中添加数据 insert into

在这里插入图片描述
在这里插入图片描述

删除表中数据

在这里插入图片描述

修改表中数据

在这里插入图片描述

练习:
在这里插入图片描述
1,查询出员工中薪水前三名
oracle中伪列rownum,显示每一列的行号。行号一旦设置就行号就不变了,即使排序记录位置变了,对应记录的行号值没变,也会随排序位置移动的。
在这里插入图片描述
2.查找出员工薪水大于本部门平均薪水的员工
在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值