非常经典的Oracle基础知识

非常经典的Oracle基础知识

1、介绍

1.1 特点：

• 实现数据共享
• 减少数据冗余度
• 数据的独立性
• 数据实现集中控制
• 数据一致性和可维护性，确保数据的安全性和可靠性
• 故障恢复

Oracle 自带管理系统 ：http://127.0.0.1:8080/apex/

sql语句分类

DQL (Data Query Language)，数据查询语言
用于检索数据库中的数据，主要是 SELECT 语句
DML (Data Manipulation Language)，数据操纵语言
用于改变数据库中的数据，主要是 INSERT , UPDATE , DELETE 语句
DDL（Data Define Langage)，数据定义语言
用来建立、修改、删除数据库对象，主要是 CREATE 、 ALTER 、 DROP 、 TRUNCATE 语句
TCL (Transaction Control Language)，事务控制语言
用于维护数据的一致性，主要是 COMMIT , ROLLBACK , SAVEPOINT 语句
DCL（Data Control Language），数据控制语言
用于执行权限授予和权限收回操作，主要是 GRANT , REVOKE 语句
1
2
3
4
5
6
7
8
9
10

1.2 登录

sqlplus 用户名/用户名
1

查看用户

show user
1

1.3修改会话

会话修改为英语环境
alter session set nls_language=english;

会话修改为简体中文环境
alter session set nls_language='simplified chinese';
1
2
3
4
5

1.4 创建用户

//创建用户test 设置密码为 test
create user test identified by test;
//把角色connect 和 resource 授权给test账号
grant connect, resource to test;
1
2
3
4

切换用户

conn 
conn 用户名/密码
1
2

删除用户

drop user test cascade
1

用户将表s_emp的查询权限授予给另外一个test用户，并且test用户可以继续将该权限授予给别人

切换到s_emp表所在的用户
conn spark/system
grant select on s_emp to test with grant option;
1
2
3

1.5 哑表

Oracle 中特殊的表 dual

是单行单列的虚拟表，是Oracle内部自动创建的，只有一列

2 Oracle

2.1 导入sql文件、查看表格

终端窗口导入sql文件
@table.sql

查看表格
show table_name from user_tables
1
2
3
4
5

2.2 sql语句分类

* DQL(Data Query Language) 数据查询语句
	检索数据库中的数据，SELECT语句
* DML(Data Manipulation Language) 数据操纵语言
	改变数据库中的数据，INSERT,UPDATE,DELETE语句
* DDL(Data Define Language) 数据定义语言
	建立，修改，删除数据库对象，CREATE,ALTER,DROP,TRUNCATE语句
* TCL(Transaction Control Language) 事务控制语言
	维护数据一致性，COMMIT,ROLLBACK,SAVEPOINT语句
* DCL(Data Control Language) 数据控制语言
	执行权限授予，权限收回操作  GRANT,REVOKE语句
1
2
3
4
5
6
7
8
9
10

2.3 查询

查询所有

select * 
from s_dept;
1
2

查询指定列明

select id
from s_dept;
1
2

2.4 运算

直接运算 + - * /
select 语句永远不会对原始数据进行修改
1
2

2.5 别名 as

select id as bianhao
from s_dept;
(as可以省略)
1
2
3

2.6 拼接 ||

select col_name||'spe_char'||col_name
from table;

(注意引号)
1
2
3
4

2.7 空替换 nvl

将列中为空替换成指定的值

select nvl(col_name,change_value)
from tb_name;
1
2

2.8 去重 distinct

select distinct col_name,col_name...
from tb_name;

//distinct 关键字词只能放在select关键词后面
1
2
3
4

2.9 sqlplus语句

sqlplus 相关的命令：

l 查看缓存中的sql语句

a 在[定位]的那一行后面追加新的内容

i 在[定位]的那一行下面插入新的一行

c 替换[定位]的那一行中的某些字符串 ，格式为：c/老的字符串/新的字符串

del 删除[定位]的那一行内容

n 后面加内容可以重写这一行

$ 后面跟一个终端命令，例如$cls清屏，linux中使用! / 执行缓存sql命令

3. Oracle

3.1 排序 order by

select col_name...
from tb_name
order by col_name asc/desc

//asc  升序
//desc  降序
1
2
3
4
5
6

3.2 条件查询

(隐式查询)

select col_name...
from tb_name
where col_name 比较操作表达式

//逻辑比较操作符
= > < >= <= !=

//不等于操作符
!=(常用)  <>  ^=
1
2
3
4
5
6
7
8
9

3.2.1 between and 操作符，表示在两个值之间

//查看员工工资在700 到 1500之间的员工id和名字
select id,last_name,salary
from s_emp
where salary between 700 and 1500;
1
2
3
4

3.2.2 in() 表示值在一个指定的列表中

//查看员工号1,3,5,7,9员工的工资
select id last_name,salary
from s_emp
where id in (1,3,5,7,9);
1
2
3
4

3.2.3 like 模糊查询，在值不精确的时候使用

%  通配0到多个字符
_  通配一个字符，而且是一定要有一个字符
\  转义字符，需要使用escape关键字指定，转义字符只能转义后面一个字符
1
2
3

列子

//查看员工名字中包含一个_的员工id和工资
select id,last_name,salary
from s_emp
where last_name like '%\_%' escape '\';
1
2
3
4

3.2.4 is null 判断值为空时使用，null值的判断不能使用等号

is not null 不为空

3.2.5 and or 逻辑操作符，当条件有多个的时候可以使用

and 的优先级比or高

4.函数

4.1 单行函数

4.1.1字符函数

4.1.2 数字函数

4.1.3日期函数

显示当前时间

select sysdate from dual
1

4.2 转换函数

三种转换函数：

TO_CHAR 数字或日期数据转换为字符

TO_NUMBER 把字符转为数字

TO_DATE 把字符转换为日期

4.2.1数字转字符的常用格式

使用 TO_CHAR

select to_char(salary,‘$999,999.00’) as result

from s_emp;

4.2.2 日期转字符常用格式

使用 TO_CHAR

样例

select to_char(sysdate,‘dd-mm-yy’) from dual;

4.2.3字符转数字

TO_NUMBER

select to_number(‘1000’)

from dual;

4.2.4字符转日期

TO_DATE

select to_date(‘10-12-2022’,‘dd-mm-yyyy’) as result from dual;

4.3聚合函数

4.3.1分组函数 group by

group by 按照某一条件，给数据进行分组

常用聚合函数

• avg 平均值
• count 计算有多少条数据
• max 求最大值
• min 求最小值
• sum 求和

聚合函数能够出现的位置

• select 后面
• having 后面
• order by 后面

select having 语句后面出现了组函数，那么select，having后面没有组函数修饰的列，就必须出现在group by后面

5.多表查询

• 等值连接
• 不等值连接
• 外连接

​ 左外连接

​ 右外连接

​ 全连接

• 自连接

5.1等值连接

在连接中使用等号（=）操作就是等值连接。

一张表中某列的值和另一张表中某列的值相等的关系，把两张表连接起来，满足条件的数据才会组合。

//查询员工的名字，部门编号，部门名称
select last_name,dept_id,s_dept.id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id;
1
2
3
4

5.2不等值连接

主要用除了等号之外的操作符，比如：<>、>、<、>=、<=、LIKE、IN、BETWEEN…AND

5.3 左连接

LEFT OUTER JOIN左外连接就是在结果中除了满足连接条件之外的行，还包括LEFT OUTER JOIN左侧表的所有行。

简写

select last_name,dept_id,name from s_emp,s_dept where s_emp.dept_id=s_dept.id(+);

5.4右连接

与左外连接同理，无限满足右表，即根据右表中数据去左表搜索，如果没有匹配数据，填入null

简写：

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

5.5全连接

select last_name,dept_id,name
full outer join tb_name 
on s_emp.dept_id=s._dept.id;
1
2
3

5.6自连接

自连接就是一张表，自己和自己连接后进行查询

可以给同一张表，起两个不同的别名，然后进行查询
1
2
3

5.7 操作结果集

两个结果集中查询的列要完全一致（名称和类型）

* union 取两个结果集的并集(相同的只会显示一次)
* union all 把两个结果集合在一起显示（两个表都显示出来）
* mius  第一个结果集除去第二个结果集和它相同的部分
* intersect 求两个结果集的交集 




例如：
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
intersect
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

5.8伪列

rownum 常用于分页查询

rownum 当条件查询时，只能
* 等于1
* 大于0
* 小于任何数
1
2
3
4

6.子查询

• 子查询位置： select from where having
• 不可在 group by

7.数据库

7.1 表的设计

实体：有相同特征和性质的事务，能够用一个一个来表示的
属性：实体所具有某一特性就是它的属性


	* 关系
			一对一
			一对多
			多对多
			
	范式：
			第一范式：每一列里面的值都不可以在分割
			第二范式：在满足第一范式条件下，表中的非主键列必须依赖主键列
			第三范式：在满足第二范式的条件下，表中的非主键列直接依赖主键列
			
			
			
			
命名规则
对应表和列的名字
	* 必须是字母开头
	* 必须是1-30个字符之间的长度
	* 表名中只能出现字母，数字，_、#
	* 名字不能重复
	* 不能是关键字
	
		
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

7.2 数据类型

数据类型
	存储字符串
		* char      长度固定
		* varchar	可以存空字符串
		* varchar2 	不能存空字符串 可以存null
		
	
    数字
    	* number(p,s) p表示最大位数（整数位+小数位），s表示保留小数位（四舍五入）
    	
    	
   
    日期类型
    	*  date
    	
    存二进制对象  例如视频，音频，图片
   		*  blob
    	
    存储大文本
    
    	* clob
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

7.3 约束

* 主键  primary key
* 外键  foreign key
* 唯一  unique
* 非空  not null
* check  check


1、列级约束  

        check 约束
        例如
        gender char(1) check(gender in('f','m'))


        外键约束    （自定义一个列的外键名 + 类型 + reference + 外键关联的表名（属性名）;

        create table t_customer(
            id number primary key,
            name varchar2(200) not null
        );
        create table t_order(
            id number primary key,
            content varchar2(200) not null,
            customer_id number references t_customer(id)
        );




2、表级约束：非空约束（not null）,不能声明成表级约束

        表级外键约束
            foreign key(自己定义的外键名) reference 关联外键的表名(属性)

        表级约束还可以联合约束
            约束名称(约束属性1，约束属性2.....)
            primary key (id,name)

	
	
	
3、可以给约束起名字
		* constraint关键字
		起名规律：表名_列名_约束类型

		
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

复制表格

可以用 as

例如

create table t

as

select * from s_dept;
1
2
3
4
5

7.4 DML语句

* insert 
		insert into 表名 (属性名,....)
		value(属性值，....);


* update
		update 表名
		set 属性值=新属性值，....;



* delete

		delete from 表名 
		where 条件；
		
		
		
		
		on delete xxx   是在声明外键约束的时候使用的
		on delete no action
		on delete cascade
		on delete set null
		
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

7.5 事务

* 原子性 Atomicity
	一个事务中所有的DML操作，同时成功或者同时失败
	
* 一致性 Consistency
	事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
	
* 隔离性 isolation
	事务操作间应该相互独立
	
* 持久性 Durability
	事务所做的影响，在事务结束之后应该能够持久的
1
2
3
4
5
6
7
8
9
10
11

7.6 alter

* 在表中添加数据
		alter table 表名
		add 属性名 类型;
		
* 删除列
		alter table 表名
		drop column 属性
		
* 添加约束
		alter table 表名
		add constraint 约束名称 约束(属性)；
例如：
		alter table t_user
		add constraint user_name_un
		unique(name);
		
* 删除约束
		alter table 表名
		drop constraint 约束名称;    这就是起约束名称的好处   
		
* 修改表名
		rename 表名 to 表名;

* 修改列的数据类型
		alter table 表名
		modify (列明 新的数据类型)
		
* 设置约束失效
		alter table 表名
		disable constraint 约束名称 cascade;
		
		
		让失效的约束再次生效
				alter table 表名
				enable constraint 约束名称;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35