Oracle学习笔记

Oracle的基本概念介绍

数据： 描述信息的，如图片、音频、视频、字符串

数据的存储：

int a = 10; Student s = new Student(1,”zhang3″,18); 缺点：临时数据

采用文件的形式保存：缺点：不安全、数据类型单一、描述的数据含义模糊、对数据的增、删、改、查麻烦(I/O)、并发处理麻烦

数据库：用于管理、持久化数据

数据库的优点：

持久保存数据

保存数据安全

提供丰富的数据类型

提供多用户操作

支持错误操作的撤销

支持数据的移植和备份

关系型数据库：RDBMS RelationShip DataBase Management System 以表格的形式来保存数据(table)

table ：表，保存数据类型相同的数据 ，有行和列构成 class ：类

row ：行，代表一条具体的数据 一个具体的对象(new)

column :列 ，对表中数据的描述 类中的属性

6、常见的关系型数据库：

Oracle 数据库 ：由Oracle公司提供

DB2 数据库：由IBM提供

SqlServer ：由微软提供

Mysql：由oracle提供

非关系数据库：

MongoDB、Redis

7、Oracle数据库：由Oracle公司提供的一种关系型数据库

二、数据库的准备

1、Oracle数据库的安装：

如果需要切换安装路径：提前准备一个文件夹(不含中文和空格)

安装结束后，不要修改计算机名

双击打开安装

卸载

Oracle数据需要启动的服务

OracleServiceXE ：核心服务 ，对外提供数据支持

OracleXETNSListener ：监听服务

Oracle数据库：C/S结构的应用(Client/Server)

Oracle的客户端操作形式：

sqlplus ： Oracle自带的一种client操作方式(了解)

ii. isqlplus : Oracle数据库提供基于网页操作(11g不支持网页操作)

iii.第三方client的操作软件：plsql developer

hr测试用户的解锁：

使用管理员身份登录：输入sql命令 alter user hr account unlock;

hr用户登陆成功：

Sql语句书写–执行的过程

ii. Select table_name from user_tables; 显示当前用户下所有的表名称

iii. Hr用户下的表：

EMPLOYEES ：员工表，管理员工的信息

DEPARTMENTS：部门表 ，管理部门信息

LOCATIONS ：地址表 ，管理部门所在地的信息

SQL ：Structure Query Language 结构化查询语言，适用于RDBM，用于对数据库的增删改查操作的语法

Oracle中的单表基本查询操作【重点】

查询的语法：select… from …

select ： 查询的内容

from ：确定表，数据源

查询一张表中所有字段的信息 :select * from 表名;

查询表中的部分字段：select 字段1, 字段2，字段3… ,字段n from 表名；

列做运算：

数字类型 ：+ – * / b) 字符串拼接 c) 日期做加减实际是时间推移的天数

给字段取别名 select 字段1 [as] 别名，字段2 [as] 别名 ….from 表名;

排序【重要】 select …. from … order by 排序的依据 排序的规则;

排序的依据是表中相关的字段

排序规则 ：升序 (默认) ASC 降序 :DESC

条件查询where【重点】

等值查询 = select … from ..where 字段=值;

不等值 ： > < >= <= !=

and or

and ：并列关系，连接的两个查询条件必须同时满足，数据才可以显示

or ：或者关系，连接的两个查询条件只需要满足其中之一，数据就可以显示

in 针对于一些零散数据的处理(枚举) select … from… where 字段 in(值1,值2,…)

null值处理：null 数据类型不确定，默认最大，不能直接使用= > <做比较

语法：is [not] null [不]为空

between ..and …等价于 >= and <=

模糊查询 like

% ：通配0–n个字符

_ : 通配一个字符

举例 ：

以”A”开头： “A%”

以”A”结尾： “%A”

含A ： “%A%”

必须是四位 “_ _ _ _”

含A 并且是四位 ： “%A%” and “_ _ _ _”

概念：RDBMS

单表的基本操作 ：1) * 2) 部分字段 3)字段运算 4)别名 5) 排序

条件查询：查询条件的书写语法

比较 = > < != b) and or c) in d) null处理

e) between and f) like （% _）

复习：

Oracle数据库： 持久化数据

plsql的使用

select的语法：

单表的基本查询操作

查询所有字段 select * from 表名

查询部分字段：select 字段1,字段2,….字段n from 表名

字段运算 ：数字类型：算术运算 字符串类型 ：||

别名： [as] 提高sql的可读性

排序： order by 排序的字段 ASC|DESC

select …from….order by 字段 ASC|DESC

b)条件查询：where

语法：select …from …where 查询条件

比较 ： = > < != select ….from … where 字段 >值

and (&&) or (||) ：对多个查询条件连接

and ：并列

or ：或者

in ：处理的是零散数据

where 字段 in(值1,值2) :在值1和值2中

where 字段 not in (值1,值2) :不在1和值2中

null处理： is [not] null

between …and … :在区间中查询

salary between 100 and 200 :salary>=100 and salary<=200

salary not between 100 and 200 : salary<100 or salary>200

模糊查询 [not] like

% _

select …from … where ….order by …

一、Oracle中的函数

Oracle中的函数：完成功能，数据库提供好的，只需要调用

单行函数：针对一条数据，调用一次

sysdate ： 调用：select sysdate from 表名 作用：显示当前的系统时间

显示当前的系统时间：select sysdate from dual;

dual :哑表，没有实际的数据意义，作用是保证select语法的完整的

b) length() : 作用：返回指定字符串的长度。

调用：length(参数) ，参数通常是字段名

c)to_char() : 作用：把指定的日期转化格式的字符串日期

调用：to_char(日期参数,’给定的日期格式’):

d) to_date():作用：把给定的字符串日期转化为Date类型

数据表中的字段类型是Date，只能提供字符串类型，需要通过to_date函数转换

调用：to_date(‘字符串日期’,’日期格式’)

组函数(聚合函数) ：针对一组数据，调用一次

avg() :求平均值

sum():求和

max() :求最大值

min() :求最小值

v. count函数 ：

语法1 ：count(*) :统计表中所有的数据条数 （应用在分页，总的页码计算）

语法2：count(字段名) ：统计指定字段不为null的数据条数

分组查询

1、分组：按照指定的共性进行分类

2、分组查询的语法：select … from…group by 分组的依据

3、分组语法规范：

1)只有在group by 后出现的字段，才可以出现在select (和order by )子句之后

2)如果在group by 后没有出现的字段，可以配合组函数，出现在select (和order by )子句之后

3)如果在group by 后出现了单行函数，那么在select子句后使用相同的单行函数

复杂的分组： select …from .. group by 字段1,字段2

意义：先按照字段1分组，后在分好组的内部再根据字段2分组

对分组后的数据再次过滤 having

语法：select …from .. group by…having..

where和having的区别：

语法区别：where是在from之后，对原始的数据进行过滤；having出现在group by之后，对分组后的数据进行过滤。

查询条件的书写：where后的条件不能使用组函数，having后的条件：可以使用组函数，页可以使用单个字段的对比，这个字段出现在group by之后

如果where和having过滤出的数据结果相同，优先使用where

单表基本查询的关键字

语法顺序： select …from …where..group by..having..order by ..DESC|ASC

执行顺序：

from ：确定数据源

where ：对原始的数据进行过滤

group by :对where过滤后的数据进行分组

having：对分组后的数据再次过滤

select ： 运算查询(字段的别名、算术运算、字符串拼接)

order by :对查询的结果排序

子查询

伪列：在原始的数据表中不存在的列，在查询时被构造出来

rowid ：在数据库唯一标识一行数据

b) rownum : 对查询的数据进行编号

子查询：在原始完整的select语句之上又嵌套了select语句

应用：查询的条件是一个与原始表中数据相关的动态值

分类：子查询的结果是一个值、子查询的结果是多个值、子查询的结果是多行多列

子查询的具体实现：

子查询的结果是一个值(一行一列)

处理方式：把子查询的结果当做一个值，直接做比较运算 (= > < !=)

b) 子查询的结果是多个值(一列多行)

处理方式：把子查询的结果当做多个值，做in运算

c) 子查询的结果是多行多列(分页)

处理方式：把子查询的结果当做一张临时表，再次做select操作

总结：

应用：查询的条件是一个动态值

作为查询条件，出现在where后

作为临时表，出现在from后

具体的语法：

一行一列 ：当做一个值直接做比较运算 where

一列多行：当做多个值，做in运算 where

多行多列：当做临时表处理 from 分页

排序的分页

不排序

表连接【重点】

应用：需要查询的数据来源于多张表

表连接的语法：

select …from 表1 [连接方式] join 表2 on 连接条件

连接方式：内连接、外连接(左外连接、右外连接、全外连接)、自连接

3、内连接语法：select … from 表1 [inner] join 表2 on 连接条件

连接条件确定：能够从本表中在另外一张表定位到相关的数据，通常是外键

内连接的特点：

外连接：

语法：

左外连接：select … from 表1 left [outer] join 表2 on 连接条件【重点】

右外连接：select … from 表1 right [outer] join 表2 on 连接条件

全外连接：select … from 表1 full[outer] join 表2 on 连接条件

b)举例：

c)左外连接的特点：

语法：必须有连接条件

查询的结果：满足连接条件的数据+左表中不满足连接条件的数据

主次关系：左表为主(数据不满足连接条件，会显示)

d)右外连接的特点：

1、语法：必须有连接条件

2、查询的结果：满足连接条件的数据+右表中不满足连接条件的数据

3、主次关系：右表为主(数据不满足连接条件，会显示)

e)全外连接：特点

1、语法：必须有连接条件

2、查询的结果：满足连接条件的数据+左、右表中不满足连接条件的数据

3、主次关系：没有

5、多表连接：(3张表为例)

做法：把前两张表做表连接，查询的结果再和第三张表做表连接

select … from 表1 join 表2 on 表1和表2的连接条件

join 表3 on 表1和表2的结果与表三的连接

自连接：

特殊的连接方式：把一张表看做两张表然后进行连接

总结：

表连接：内连接 左外连接 子连接(场景)

连接的语法、连接的特点

SQL体系的分类

sql：Structure Query Language 结构化查询语言 sql命令 用于对数据库的增删改查操作，适用于大多数的关系型数据。

DQL：Data Query Language 数据查询语言 select操作【重点】

DML：Data Munipulate Language 数据操纵语言 insert/delete/update【重点】

DDL：Data Define Language 数据定义语言 create / alter

TCL : Transaction Controller Language 事务控制语言 commit /rollback【重点】

DDL 数据定义语言

Oracle中的数据类型

数字相关 number

语法1 ：number 即可以保存小数，也可以保存整数

语法2：number(n) :保存最大为n位整数

语法3：number(n,m) : 总长度为n，小数位数为m

number(5,2) : 123.45 12.345

1234.5 error 值过大

b)字符串相关

varchar2(n) : 最大可以存放n个长度的字符串 ，可变长字符串

char(n): 最大可以存放n个长度的字符串 ，不可变长度

varchar2(5): ‘abdce’ ‘ab’

char(5): ‘abdce’ ‘ab ‘

c)日期类型 Date

注意：在Oracle数据库中，没有boolean类型,描述boolean类型

varchar2(1) 要求值必须是 ‘y’ ,’n’

数据库中的约束

约束:针对于值修改时的限定

主键 ：primary key

作用：在数据表中唯一代表一条数据

约束要求：值在当前表内不重复、不能为null(unique 、not null)

iii. 非空 not null

约束要求：这个字段必须给值

iiii. 唯一约束 unique

约束要求：要求给定的值不能重复

iv. 自定义约束 ：检查约束 check

姓别必须是 ‘f’ 或 ‘m’ check(sex in(‘f’,’m’))

密码必须是六位 ：check(length(pass)=6)

手机号码必须是11：check(length(mobile)=11)

邮箱必须含’@’ :check(email like ‘%@%’)

v. 默认值 ：如果在添加数据时，有默认值得字段会默认添加上指定的值

default 值

3、建表

语法：create table 表名(

字段名1 数据类型1 [默认值] [约束1] [约束2],

字段名2 数据类型2 [默认值] [约束1] [约束2]，

……..

字段名n 数据类型n [默认值] [约束1] [约束2]

)

如：创建一张学生表学号、姓名、生日、分数、性别

create table t_student(

id number(8) primary key,

name varchar2(20) not null,

birthday Date,

score number(4,1)，

sex varchar2(1) default ‘f’ check(sex in(‘f’,’m’))

)

外键列设定

外键 ：一张表的主键充当另外一张表的外键

表之间的关系体现(RDBMS)

保证数据的完整性(添加的数据必须来源于父表)

b)语法 ：references父表(主键名)

c)创建具有外键关系的表：

5、删除表结构 ：drop table 表名 (注意：删除表时，先删除子表)

DML操作(insert/delete/update)【重点】

添加数据(insert)

语法1 ： 全表插入

insert into 表名 values(值1，值2，值3,…..值n)

如：–向t_student表中添加一条数据

insert into t_student values(1,’zhang3′,to_date(‘2001-01-01′,’yyyy-mm-dd’),100,’m’);

注意：

全表添加数据时，需要把每一个字段都给定值

全表插入数据时，注意表结构的字段顺序

b)语法2：部分字段添加

insert into 表名(字段1,字段2,…字段n)values(值1，值2，….值n)

如：–向t_student表中添加数据，id，name

insert into t_student(id,name)values(4,’yanxj’);

注意 ：部分字段添加数据时，对于非空约束的字段必须出现在字段中

在日后的开发中，建议使用部分字段的添加(可读性、脱离原始的表结构字段的顺序、效率高)

针对于有外键的表数据添加：先添加父表的数据，再添加子表的数据

序列 ：(sequence)【重点】

作用 ：生成一组不重复的数字，保证主键、唯一键不重复(数据库提供)

序列使用：

创建一个序列 :create sequence 序列名; 默认从1开始，步长为1

序列取值 ：序列名.nextval; 获得序列的下一个值

把序列使用在添加数据上：（主要应用在主键生成中）

insert into t_student1(id,name)values(seq_test.nextval,’testAA’);

iii. 序列使用的注意事项：

1)序列的值一旦使用，不会再重复，如果需要从头开始，需要删除序列重新创建 drop sequence 序列名。

2)序列一旦创建，在当前用户下，所有的表都可以使用，建议一张表对应创建一个序列来保证主键的不重复，因此序列取名： seq_stu

3)序列的创建可以指定起始位置和步长

4、添加(修改数据)数据时可能发生的错误：

针对主键、唯一键：违反唯一约束

values给定值：值过多、值不足

未找到父向关键字：具有外键 的列给定的值在父表中没有

违反检查约束：注意check约束的字段

删除数据

语法：delete from 表名 where 删除条件。

注意事项：

1)删除时切记加删除条件：否则将会删除所有的数据

2)delete 和drop ： drop连同表结构(含数据)一起删掉，delete只是删除数据。

3)在具有外键关系的表中删除数据：先删除子表的数据

6、修改数据:update

语法 ：update 表名 set 字段1=新值1,字段2=新值2,….字段n=新值n

where 修改条件

事务控制(Transaction TX)【重点概念】

事务 ：数据库中的最小操作单元。包括一条或者多条sql语句，这些sql语句要么同时成功，整个事务成功(事务提交 commit);在这些sql语句中如果有一条sql语句失败，所有的sql语句的操作需要撤销，事务失败(回滚：rollback)。

一个完整的事务和一个完整的功能等价。

事务的原理：

数据库的服务端 DBServer会给每一个数据库client分配一个空间(回滚段)，回滚段保存client执行的sql语句，如果一个client的多条sql语句都成功，遇到commit，把当前client对应的回滚段中的sql一次发送到DBServer，并清空回滚段；如果在回滚段中的sql语句有一条失败，撤销所有影响数据的DML操作，清空回滚段。

注意：影响事务控制的操作是在client，不同的client事务之间没有影响

事务的范围：

开始：同一个事务中的第一条sql执行开始

sql1 开始

sql2

…

sqln

结束 ：commit/rollback

面试题 ：事务的特点(ACID)

A ：事务的原子性 ：同一个事务中的多条sql语句不可再分

C：事务的隔离性 ：不同的事务之间互不影响，相互独立

I：事务的一致性 ：事务影响的前后状态一致

D：事务的持久性：事务对数据库的影响是永久的

注意：后续无论是在哪种client做数据库的DML操作，习惯加上commit