Oracle学习

于 2021-07-18 19:58:02 发布
阅读量235 收藏 2
点赞数 2
分类专栏： oracle 文章标签： sql oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a123123sdf/article/details/118873389
版权

目录

创建数据库和表

一、使用管理员账号连接Oracle数据库

1、Oracle数据库默认提供了两个管理员数据库账号

sys 和 system

2、登录system账号
  • 使用sql命令行创建连接
SQL> conn system/123
已连接。
SQL>
  • 使用oralce的图形化界面连接数据库

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

使用管理员账号登录后，就能通过sql命令创建用户（在Oracle中一个用户等于一个独立的数据库）
在这里插入图片描述

3、创建用户（数据库）
  • 创建用户
    语法[创建用户]： create user 用户名 identified by 口令[即密码]；
 create user erhia identified by 123;
4、修改用户（数据库）连接信息
  • 修改数据库连接信息
    语法[更改用户]: alter user 用户名 identified by 口令[改变的口令];
alter user test identified by 123456;
5、最初创建的用户（数据库）是没有任何权限的——连接数据库的权限都没有
  • 授权
    我们在创建用户之后不可以直接连接，我们得给用户权限
    授权命令

语法： grant connect, resource to 用户名;

grant connect, resource to erhia;
6、切换当前的连接用户

语法：conn 用户名/密码;

conn demo/123;
7、删除用户

语法：drop user 用户名;

drop user test;

若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。

语法： drop user 用户名 cascade;

 drop user test cascade;
8、更改当前会话的类型

会话类型不对在有中文的sql文件中可能会报错

alter session set nls_language='English';
9、导入文件：

语法 ：@ sql文件所在路径

@ d:oaec.sql
10、更改显示列的长度

col 列明 for a长度;

二、创建表和初始化数据

1、创建表
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
2、初始化数据
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;

1、函数

1.1、单行函数

1.1.1、字符函数
  • LOWER（列名）：将查询到的列转换成小写
select LOWER(LAST_NAME) from S_EMP where LAST_NAME = 'Patel';

在这里插入图片描述

  • UPPER（列名）：转换成大写
select FIRST_NAME, UPPER(LAST_NAME)  from S_EMP where LAST_NAME = 'Patel';

在这里插入图片描述

  • INITCAP（列名）：首字母变大写其余变小写
select FIRST_NAME, INITCAP(LAST_NAME)  from S_EMP where LAST_NAME = 'Patel';

在这里插入图片描述

  • CONCAT（str1，str2）：字符串连接
select FIRST_NAME as first_name, CONCAT(LAST_NAME,'-名') "lsat_name"  from S_EMP where LAST_NAME = 'Patel';

在这里插入图片描述

  • SUBSTR（str，start，end）：字符串截取——start表示从哪个字符开始截取（可正可负），end表示从start指定的位置开始向后截取多少个字符

SUBSTR 函数返回一部分 char，从字符位置开始，长度为 substring_length 个字符。 SUBSTR 使用输入字符集定义的字符计算长度。 SUBSTRB 使用字节而不是字符。 SUBSTRC 使用 Unicode 完整字符。 SUBSTR2 使用 UCS2 代码点。 SUBSTR4 使用 UCS4 代码点。
如果 position 为 0，则将其视为 1。如果 position 为正数，则 Oracle 数据库从 char 的开头开始计数，以查找第一个字符。如果 position 为负数，则 Oracle 从 char 的末尾开始向后计数。如果省略 substring_length，则 Oracle 将所有字符返回到 char 的末尾。如果 substring_length 小于 1，则 Oracle 返回 null。
char 可以是任何数据类型 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB。 position 和 substring_length 都必须是 NUMBER 数据类型，或者任何可以隐式转换为 NUMBER 的数据类型，并且必须解析为整数。返回值与 char 的数据类型相同。作为参数传递给 SUBSTR 的浮点数会自动转换为整数。

1、start为正数

select FIRST_NAME , SUBSTR(LAST_NAME,1,4) "lsat_name"  from S_EMP where LAST_NAME = 'Patel';

在这里插入图片描述
2、start为负数

select FIRST_NAME , SUBSTR(LAST_NAME,-2,3) "lsat_name"  from S_EMP where LAST_NAME = 'Patel';

在这里插入图片描述

  • LENGTH（列名）：求字符串的长度
select FIRST_NAME ,LAST_NAME, LENGTH(LAST_NAME) "lsat_name_length"  from S_EMP where LAST_NAME = 'Patel';

在这里插入图片描述

  • REPLACE(str,‘被替换的字符’,‘要替换的字符’) ：将指定的字符str1 换成 指定的字符str2
select LAST_NAME , REPLACE(LAST_NAME,'N','A') from s_emp WHERE LAST_NAME like '%N%';

在这里插入图片描述

  • NVL（指定的列，指定的值）：将指定的列的NULL值转换为指定的值
select FIRST_NAME ,LAST_NAME,NVL(MANAGER_ID,88) MANAGER_ID from S_EMP

在这里插入图片描述

1.1.2、数值函数
  • ROUND（列名，保留位）：四舍五入——保留位可以是正数和负数，正数表示保留小数点后几位，负数表示小数点前几位

1、保留位位正数

注意：当小数点后面的数值全为0时，无论正数的保留位未多少都是取整数 比如ROUND(980.0000,2) 为980

select SALARY  , ROUND(SALARY,1) s  from S_EMP

在这里插入图片描述
2、保留位为负数

select SALARY  , ROUND(SALARY,-2) s  from S_EMP

在这里插入图片描述

  • TRUNC（指定列，截取位）：截取——截取后会根据指定的截取位将被截取的位数直接换成0（也就是将要截取的数直接换成0）
select SALARY  , TRUNC(SALARY,-3) s  from S_EMP

在这里插入图片描述

  • MOD（指定列，除数）：取余
select SALARY  , MOD(SALARY,300) s  from S_EMP

在这里插入图片描述

1.1.3、日期函数
  • MONTHS_BETWEEN（date1,date2）：计算两个时间相距几个月（date1为：标准日期格式1991-05-26 00:00:00）
select ID, START_DATE ,sysdate now, MONTHS_BETWEEN(sysdate,START_DATE) "from_now_on"  from S_EMP

在这里插入图片描述

  • ADD_MONTHS(date,n) ：在指定日期上增加月数
select ID, START_DATE , ADD_MONTHS(START_DATE,3)   from S_EMP

在这里插入图片描述

  • NEXT_DAY(date，‘friday’)：指定日期的下一天（如下一个星期五）是哪一天

指定日期的下个星期五是哪天

select  NEXT_DAY(sysdate,'星期五')  from DUAL

在这里插入图片描述

  • LSAT_DAY(date)：指定日期（这个月）的最后一天
select sysdate,  LAST_DAY(sysdate)  from DUAL

在这里插入图片描述

  • ROUND（date，’year‘）：指定日期进行四舍五入——data为指定日期，后面的参数为需要四舍五入的年、月、日 等
select sysdate,  ROUND(sysdate,'year')  from DUAL

在这里插入图片描述

select sysdate,  ROUND(sysdate,'month')  from DUAL

在这里插入图片描述

  • TRUNC（date，’year‘）：对指定日期进行截取
select sysdate,  TRUNC(sysdate,'month')  from DUAL

在这里插入图片描述

1.1.4、转换函数
  • to_char(date，日期格式)：将日期转换为字符串
select sysdate, to_char(sysdate,'yyyy-mm-dd hh:mi:ss AM')  from DUAL

在这里插入图片描述

  • to_date(时间字符串，这个时间字符串对应的日期格式)：将字符串转换为日期
select sysdate, to_date('2021-07-18 03:18:01','yyyy-mm-dd hh:mi:ss')  from DUAL

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

  • 将数值转换成指定格式的字符串
select to_char(98769,'$9,999,999')  from DUAL

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

  • TO_NUMBER（str）：将字符串转换成数值，str只能是数字字符，包含其他的字符会报错
select TO_NUMBER('111111')  from DUAL

在这里插入图片描述

1.2、多行函数

在这里插入图片描述

1.2.1、常用的组函数

在这里插入图片描述

where是用来筛选单条数据的，having是用来筛选组的，并且having必须与gruop by配合使用

select e.ID, d.NAME,AVG(SALARY) ,MAX(SALARY),MIN(SALARY),COUNT(*),SUM(SALARY)
from s_emp e , S_DEPT d
WHERE e.DEPT_ID = d.ID
group by e.ID, d.NAME
having AVG(SALARY) >1200
order by AVG(SALARY) desc;

在这里插入图片描述
注意：查询出来的列中，非组函数的列必须是gruop by 后面的字段。gruop by 后面的字段中没有的字段不能出现在select后面。比如：
在这里插入图片描述

2、 子查询

2.1、子查询概念

  • 子查询是一个完整的select语句，可以拥有group by、having子句、可以使用组函数，可以使用多变查询结果。
  • 子查询在外围的语句是select语句，则子查询内容必须使用小括号界定

2.2、子查询分类

按子查询出现的位置分：

  • select后面：仅仅支持标量子查询

  • from后面：支持表子查询

  • where或having后面：支持标量子查询（单行）、列子查询（多行）、行子查询

  • exists后面（也被称为’相关子查询‘）：支持表子查询

2.3、子查询使用场景

  • 使用条件：当给出的条件中，有未知的条件时，需要通过子查询查出未知条件，再得出最终结果。

2.4、子查询例题

列出薪金比“ALLEN”多的所有员工

select  LAST_NAME,SALARY 
from S_EMP 
where SALARY > (select SALARY from S_EMP where LAST_NAME = 'Ngao' )

在这里插入图片描述

select g.name,g.price,g.tid 
from good g 
join (
    select avg(price) jprice,tid 
    from good
    group by tid
) e 
on g.tid=e.tid 
where g.price>jprice;

3、左右外连查询

- Oracle左连接、右连接、全外连接以及（+）号用法

  • 左连接

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。

Select * from t_A a,t_B b where a.id=b.id(+);
  • 右连接

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在左表，右表就是全部显示，所以是右连接。

Select * from t_A a,t_B b where a.id(+)=b.id;

  • 全连接

    左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL。 全外连接不支持（+）写法。

select * from t_A a full join t_B b on a.id = b.id;
或
select * from t_A a full outer join t_B b on a.id = b.id;

4、oracle事务提交与回滚

commit 和 rollback 从最终结果上讲是相反的。
1、commit 将数据的变化永久保留
2、rollback 将变化之前的数据“还原回去”
3、一旦发出commit了 就不能rollback了。
举例讲：
update a set b=1 where b=2;
commit 的作用是将这个结果永久化。
rollback 的作用相当于 update a set b=2 where b = 1 后 再commit;

4.1、什么是事务

  • 组合在一起的一系列操作，这些操作是不可分割的原子操作，要么都成功，要么都失败。

4.2、事务的特点

  • 原子性：多个操作组合成的事务要么都成功，要都失败。
  • 一致性：事务提交前后数据保持一致，例如：一致性(Consistency):事务前后数据一致。例如，从张三转一百元至李四账户，转账前两个帐户总和为2000，转账结束后二帐户总和亦要为2000。
  • 隔离性（锁）：并发访问的事务进行过程中的状态不为其他事务所查看——注意：事务的隔离性有四各级别
  • 持久性：事务结束后需要将事务进行过程中的状态进行持久化保存入数据库，保证下次能读到修改后的数据

4.3、事务回滚

  1. 异常回滚
  • 事务进行过程中的状态丢失
  • 回复到事务开始前的状态
  • 作用于记录上的锁丢失，其他用户可对这些记录进行更新操作
  • 系统失败或非正常终止sqlpuls，将自动回滚
  1. 回滚到特定位置
  • 通过命令（savepoint 回滚点名字） 设置回滚点
  • 通过命令rollback to 回滚点名字 回退到特定的回滚点

注意：当commit后会清除所有的回滚点，commit后这个事务也就结束了。

4.4、demo

这里没有commit，因此改变的数据是当前连接的缓存数据，并没有改变数据库的数据

  • 第一次执行

  • update前

select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 执行update
update s_emp set SALARY = SALARY + 500 where DEPT_ID = 41;
  • update后
select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 切换其他的连接，进行查询

发现之前的修改只是修改了当前连接的缓存信息，并没有修改数据库的信息

conn lihua/123

select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 第二次执行
update s_emp set SALARY = SALARY + 500 where DEPT_ID = 41;
select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 回滚（rollback ）
rollback ;
  • 回到最初的数据
select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 设置回滚点1（savepoint date1;）

最初的回滚点——回滚到修改数据前

savepoint date1;
  • 第一次执行更新
update s_emp set SALARY = SALARY + 500 where DEPT_ID = 41;

select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 设置回滚点2（savepoint date2;）
savepoint date2;
  • 第二次执行更新
update s_emp set SALARY = SALARY + 500 where DEPT_ID = 41;

select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 回滚到 date1
rollback to date1;
select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

在这里插入图片描述

  • 回滚到 date2
rollback to date2;
select ID,DEPT_ID , SALARY from S_EMP where DEPT_ID = 41;

注意：这里会报错，因为只能按顺序回滚。即，先回滚date2再date1

5、隔离的级别

事务隔离级别

乌鱼鸡汤
关注
专栏目录
oracle学习
06-08
oracle学习--动态数组测试，创建一个数组类型FORALL && BULK COLLECT
Oracle数据库入门到精通实战教程
最新发布
weixin_42513870的博客
08-31 1656
本文还有配套的精品资源，点击获取 简介：Oracle数据库基础教程专为初学者设计，内容包括数据库概念、安装配置、SQL语言应用、数据库管理与维护、存储结构优化、事务处理与并发控制、安全管理等，旨在传授Oracle数据库的核心技术和全面知识，以帮助学员打下坚实的数据库管理基础，为进一步学习高级特性奠定基础。 1. Oracle数据库概述与核心概念 1.1 数据库简...
ORACLE学习
09-08
个人学习使用，诸多不足，后续补充 ，不建议新手下载使用。
Tianlesoftware Oracle 学习手册（v1.0）高清完整PDF版
03-09
### Tianlesoftware Oracle 学习手册（v1.0）中的关键知识点 #### 1. ORACLE基础知识 ##### 1.1 OLAP与OLTP介绍 **1.1.1 什么是OLTP** OLTP（Online Transaction Processing，在线事务处理）是一种主要针对企业...
Oracle学习资源与使用
04-25
Oracle学习资源与使用 Oracle是一种关系数据库管理系统（RDBMS），由Oracle Corporation开发。它是最流行的数据库管理系统之一，广泛应用于企业级应用中。下面是Oracle学习资源与使用的知识点总结： 概述 Oracle...
oracle学习视屏
10-11
"Oracle学习视屏"是一系列针对Oracle数据库的学习资源，涵盖了从基础到进阶的全面教程，适合对数据库技术感兴趣或者需要提升Oracle技能的学习者。 首先，Oracle数据库的基础部分通常包括以下内容： 1. 数据库概念...
Oracle学习笔记 PDF
11-14
### Oracle学习笔记知识点详解 #### 一、Oracle简介 Oracle是一家知名的软件公司，以其数据库管理系统闻名全球。该公司成立于1977年，总部位于美国加利福尼亚州。Oracle不仅提供数据库解决方案，还涉及中间件、...
学习Oracle
09-18
SQLPlus的基本使用，详细介绍了SQLPlus的各类常用命令
Oracle学习手册：TianleSoftware整理版
“Oracle学习详解”是一份详尽的Oracle数据库学习文档，由TianleSoftware整理，主要涵盖作者在Oracle学习过程中的实验、问题解决和经验总结。文档内容可能包括Oracle的基础知识、管理操作、性能优化、故障排查等多个...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值