前言
上课的笔记全部读完也没意义，建议学习时或者以后用的上时，一边用一边搜，里边的概念和数据库结构，介绍的比较详细，建议搜着看(ctrl+F)
Oracle 关系型数据库(关系数据模型作为数据的组织方式.)
关系数据库 : 数据结构,关系操作集合,关系的完整性约束3部分组成.
-
二维表
- 由一组关系组成,每隔关系的数据结构是一张泛华的二维表/二维表就是用来表示实体和实体间联系的数据模型.
- 关系的逻辑是一个二维表,表中的每一列表示关系的一个属性,每一列的名称即为属性名
- 每一行表示一个记录,代表的是一个物理实体,在关系数据库中,所有的数据都是通过表来存储的.
-
关系术语
- 在关系数据模型中,列称为属性或字段,行被称为元组或记录等.
- 关系(Relation)
- 一个关系对应通常是一张表
- 元组(Tuple)
- 表中的一行数据即为一个元素.
- 属性(Attribute)
- 表中的一个列即为一个熟悉.
- 域(Domain)
- 属性的取值范围称为该属性的域
- 关键字或码(id/主键)(key)
- 表中唯一确定一个元组的列称为(码键/关键字/id/主键)
- 分量(Component)
- 元组中的某一个属性的属性值
- 关系模式(Relation Schema)
- 对关系描述方式, 关系名(属性1,属性2,属性3…,属性n)
R(U,D,dom,F)
R关系名; U是组成关系的属性名集合, D是属性的域,dom是属性向域映射的集合,F为属性之间的依赖关系
R(U),R(A1,A2…An)
E(ID,Nanme,Sex…)select * from user;
Select * from user;
SELECT * FROm USER;
select * from USER;
D:\app\linne
D:\app\linne\product\11.2.0\dbhome_1
D:\app\linne\oradata
sql*plus连接数据库
sys
system
dbsnmp
sysman
mgmt_view
默认解锁账户可以被使用.
登陆 sqlplus 用户名/密码
查询当前用户下面的所有表
select * from tabs;
1. 登陆管理员用户后查询.所有用户
SQL> select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN OPEN
SCOTT OPEN
HR OPEN
OUTLN EXPIRED & LOCKED
FLOWS_FILES EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
2. 解锁用户
SQL> alter user 用户名 account unlock;
3. 修改用户密码
SQL> alter user 用户名 identified by 新密码
4. 切换用户
SQL> conn 用户名/密码
sqlplus system/ as sysdba 以管理员身份登陆
sqlplus 用户名/密码@host
查看表结构
SQL> desc dept;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> describe dept;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
run 和 / 执行缓冲区保存的SQL语句.
save filename 将缓冲区中的命令存储到当前用户的文档目录下
ed filename 打开已有的SQL文件,如果没有创建新的SQL文件
@ filename 运行指定的SQL文件
回顾:
关系数据库
1. 关系数据模型
2. 关系数据库的泛化理论
3. 数据库设计(实体-关系模型,E-R图绘制)
4. Oracle应用体系
1. 单磁盘独立主机结构
2. 多磁盘独立知主机结构
3. 客户端/服务器端系统结构
4. 浏览器/服务器系统结构
5. 分布式数据库体系结构
Oracle数据库体系结构
1. 物理存储结构
1. 数据文件
2. 控制文件
3. 日志文件
1. 重做日志文件…
4. 其他文件
2. 逻辑结构
1. 表空间
数据库实例
1. 内存结构
1. 系统全局区
2. 程序全局区
3. 排序区
4. 大池
5. java 池...
6.
2. 后台进程
1. CKPT进程
2. DBWR
3. LGWR
4. SMON
5. PMON
6. RECO
7. LCKN
8. DNNN
9. SNPN
3. 数据字典
是Oracle存放数据库实例信息的一组表,通过数据字典可以了解数据库的详细配置信息.
数据库字典的所有者为sys用户,而数据字典表和数据字典视图都是存储在system表空间中的
1. user_* : 用户对象信息表.
user_tables;
2. all 视图
1. all_为前缀的用来记录用户对象的所有信息,和授权访问对象的信息
3. dba 视图
1. dba_user,dba_tables等都是存储数据库实例所有对象信息.\
4. V$视图
1. 活动相关的性能统计信息.
5. GV$视图
1. 分布式系统环境下使用的多文件存储信息
dba_tables
dba_views
....
sql*plus:
注册表:
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
页面设置大小
set pagesize 行数
SQL> desc dept 部门表
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
表空间
物理结构上,数据信息存储在数据文件中
逻辑结构上,数据信息存储在表空间中.
一个数据库是由多个表空间构成,表空间和数据文件存在紧密的对应关系
每个表空间是由一个或多个数据文件组成.
dba_tablespaces; 存储Oracle数据库中的所有表空间信息.
SQL> select tablespace_name,block_size,allocation_type from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE ALLOCATIO
------------------------------ ---------- ---------
SYSTEM 8192 SYSTEM
SYSAUX 8192 SYSTEM
UNDOTBS1 8192 SYSTEM
TEMP 8192 UNIFORM
USERS 8192 SYSTEM
EXAMPLE 8192 SYSTEM
已选择6行。
创建基本表空间
在创建表空间的时候 :
1. 在数据字典和控件文件中添加新建的表空间信息
2. 操作系统中,创建指定大小的操作系统文件,并作为与表空间对应的数据文件
使用脚本创建:
create [temporary|undo] tablespace tablespace_name
datafile filepath
blocaksize number k
autoallocate number [k|m]
SQL> create tablespace hnkj
2 datafile 'd:\db_file\hnkj.dbf'
3 size 10m
4 autoextend on
5 next 5m
6 maxsize unlimited;
表空间已创建。
修改表空间
1. 为表空间增加新的数据文件
SQL> alter tablespace hnkj
2 add datafile 'd:\db_file\hnkj_02.dbf'
3 size 10m;
表空间已更改。
2. 查询数据文件信息
SQL> select file_id,file_name,bytes from dba_data_files;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
4 D:\APP\LINNE\ORADATA\ORCL\USERS01.DBF 5242880
3 D:\APP\LINNE\ORADATA\ORCL\UNDOTBS01.DBF 104857600
2 D:\APP\LINNE\ORADATA\ORCL\SYSAUX01.DBF 534773760
1 D:\APP\LINNE\ORADATA\ORCL\SYSTEM01.DBF 713031680
5 D:\APP\LINNE\ORADATA\ORCL\EXAMPLE01.DBF 104857600
6 D:\DB_FILE\HNKJ.DBF 10485760
7 D:\DB_FILE\HNKJ_02.DBF 10485760
已选择7行。
3. 修改数据文件大小
SQL> alter database datafile 'd:\db_file\hnkj_02.dbf' resize 5m;
数据库已更改。
SQL> select file_id,file_name,bytes from dba_data_files;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
4 D:\APP\LINNE\ORADATA\ORCL\USERS01.DBF 5242880
3 D:\APP\LINNE\ORADATA\ORCL\UNDOTBS01.DBF 104857600
2 D:\APP\LINNE\ORADATA\ORCL\SYSAUX01.DBF 534773760
1 D:\APP\LINNE\ORADATA\ORCL\SYSTEM01.DBF 713031680
5 D:\APP\LINNE\ORADATA\ORCL\EXAMPLE01.DBF 104857600
6 D:\DB_FILE\HNKJ.DBF 10485760
7 D:\DB_FILE\HNKJ_02.DBF 5242880
已选择7行。
4. 修改数据文件是自动增长
SQL> alter database datafile 'D:\DB_FILE\HNKJ_02.DBF' autoextend on;
数据库已更改。
SQL> select file_id,file_name,autoextensible from dba_data_files;
FILE_ID FILE_NAME AUT
---------- -------------------------------------------------- ---
4 D:\APP\LINNE\ORADATA\ORCL\USERS01.DBF YES
3 D:\APP\LINNE\ORADATA\ORCL\UNDOTBS01.DBF YES
2 D:\APP\LINNE\ORADATA\ORCL\SYSAUX01.DBF YES
1 D:\APP\LINNE\ORADATA\ORCL\SYSTEM01.DBF YES
5 D:\APP\LINNE\ORADATA\ORCL\EXAMPLE01.DBF YES
6 D:\DB_FILE\HNKJ.DBF YES
7 D:\DB_FILE\HNKJ_02.DBF YES
已选择7行。
修改表空间状态
正常状态 : online(在线),read write(读写)
非正常状态 : offline(离线),read only(只读)
移动数据文件
1. 修改表空间状态为offline状态,防止其他用户操作
alter tablespace hnkj offline;
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
HNKJ OFFLINE
2. 复制数据文件到另一个磁盘
3. 使用 alter tablespace rename 语句修改数据文件名称
SQL> alter tablespace hnkj
2 rename datafile 'd:\db_file\hnkj.dbf'
3 to
4 'd:\db\hnkj.dbf';
表空间已更改。
SQL> alter tablespace hnkj
2 rename datafile 'd:\db_file\hnkj_02.dbf'
3 to
4 'd:\db\hnkj_02.dbf';
表空间已更改。
SQL> select file_id,file_name,bytes from dba_data_files;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
4 D:\APP\LINNE\ORADATA\ORCL\USERS01.DBF 5242880
3 D:\APP\LINNE\ORADATA\ORCL\UNDOTBS01.DBF 104857600
2 D:\APP\LINNE\ORADATA\ORCL\SYSAUX01.DBF 534773760
1 D:\APP\LINNE\ORADATA\ORCL\SYSTEM01.DBF 713031680
5 D:\APP\LINNE\ORADATA\ORCL\EXAMPLE01.DBF 104857600
6 D:\DB\HNKJ.DBF
7 D:\DB\HNKJ_02.DBF
已选择7行。
4. 将表空间的状态修改为onlin
SQL> alter tablespace hnkj online;
表空间已更改。
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
HNKJ ONLINE
已选择7行。
删除表空间
drop tablespace tablespace_name [including contents] | [including contents and datafiles]
including contents : 表示删除表空间,但是保留该表空间的数据文件(即使保留其数据文件,这些文件也无法使用)
including contents and datafiles : 删除表空间,将内容全部和数据文件全部删除
SQL> drop tablespace hnkj including contents and datafiles;
表空间已删除。
filename 字段最多允许占用50个字符
SQL> column file_name format a50
临时表空间
临时表空间主要用来为排序或者汇总等操作提供的临时工作空间
create temporary tablespace temp_01
tempfile 'page.temp_01.dbf'
size 10m
autoextend on
next 10m maxsize 100m
临时表空间用于存储临时数据,不能够存储永久性数据.
大文件表空间
最大可以存储4G字节的数据块大小
create bigfile tablespace bigfile_name
datafile 'page'
size 10m
撤销表空间
控制文件和日志文件
控制文件,是一个很小的二进制文件,用于描述数据块物理结构,包括数据库文件和日志文件的信息
1. 数据库名和标识符
2. 数据库创建的时间戳
3. 表空间的名称
4. 数据文件和日志文件得位置
5. 当前日志文件的序列号
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\APP\LINNE\ORADATA\ORCL\SYSTEM01.DBF
D:\APP\LINNE\ORADATA\ORCL\SYSAUX01.DBF
D:\APP\LINNE\ORADATA\ORCL\UNDOTBS01.DBF
D:\APP\LINNE\ORADATA\ORCL\USERS01.DBF
D:\APP\LINNE\ORADATA\ORCL\EXAMPLE01.DBF
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
D:\APP\LINNE\ORADATA\ORCL\REDO03.LOG
D:\APP\LINNE\ORADATA\ORCL\REDO02.LOG
D:\APP\LINNE\ORADATA\ORCL\REDO01.LOG
SQL> select name from v$controlfile
2 ;
NAME
--------------------------------------------------------------------------------
D:\APP\LINNE\ORADATA\ORCL\CONTROL01.CTL
D:\APP\LINNE\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL
SQL>
表
表是基本的数据存储结构.一个表通常由行和列(字段)组成
创建表可以指定创建到那个表空间上,如果不指定将默认创建到用户的默认表空间里.
创建表的类型:
1. 堆表 : 数据安装堆组织,以无序方式存放在单独的表字段中,也称为标准表.
2. 索引表 : 数据以"B树"解耦股,存放在主键约束所对应的索引字段中
3. 簇表 : 簇由供需数据库的组表组成,在某些情况下,使用簇表可以节省空间,提高SQL的性能
4. 分区表 : 数据被划分为很小区间范围,并且存储到响应的分区段中,每个分区段可以独立和操作
数据类型
number(p,s) : 可## 变长度数字 ##,p:数字可用的最大位数(如果有小数点,是小数点前后之和) s: 小数点的最大位数.最大精度不能超过38位
number(4,2) : 12.25
double,float : 都是number的子类型,38精度的浮点数
real : number的子类型18位精度的浮点
int,integer,smallint : 38位精度的整数
long : 可变长的字符数据类型 : 2GB
xml
varchar2(size) : 最大长度4000字节,最小位1字节
nvarchar2(size) : 可变长字符串,最大长度4000个字节
char() 定长字符串
nchar()
date : 年月日
timestamp : 年月日时分秒和毫秒
clob : 大字符串对象可以存储4GB的字符文本
NCLOB
BLOB : 二进制文件
创建表:
create table [schema.]table_name(
column_name data_type [defaulte expression] [constraint],
column_name data_type [defaulte expression] [constraint],
column_name data_type [defaulte expression] [constraint],
......
)
schema : 指定表所属的用户名
table_name : 创建表的表名
column_name : 列名,一个表每列必须具有唯一性
data_type : 列的数据类型
defaulte expression : 列的默认值
constraint : 添加约束,表示该列的值必须满足的规则
创建表:
create table book(
bookid number(10) not null,
bookname varchar2(40),
author varchar2(40) ,
press varchar2(40) ,
bookprice number(4,2) ,
presstime date,
constraint pk_book_id primary key(bookid)
);
指定表空间:
create table book(
bookid number(10) not null,
bookname varchar2(40),
author varchar2(40) ,
press varchar2(40) ,
bookprice number(4,2) ,
presstime date,
constraint pk_book_id primary key(bookid)
)tablespace 表空间名称;
修改表:
增加和删除列
SQL> alter table book2 add c1 number;
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
C1 NUMBER
SQL> alter table book2 drop column c1;
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
禁用指定的类(我们将不能使用该列,但是当前列还占用存储空间)
SQL> alter table book2 set unused(presstime);
表已更改。
删除标记为unused的列,可以将这些列从物理上删除,收回占用的磁盘空间
SQL> alter table book2 drop unused column;
表已更改。
修改列
SQL> alter table book2 rename column bookprice to price;
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
PRICE NUMBER(4,2)
SQL> alter table book2 modify price number(6,3);
表已更改。
SQL> desc book2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
PRICE NUMBER(6,3)
对表重命名
SQL> alter table book2 rename to book3;
表已更改。
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
BOOK3
BOOK
SALGRADE
BONUS
EMP
DEPT
已选择6行。
将表移动到另一个表空间
SQL> select table_name,tablespace_name from user_tables where table_name = 'BOOK4';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BOOK4 SYSTEM
SQL> alter table book4 move tablespace users;
表已更改。
SQL> select table_name,tablespace_name from user_tables where table_name = 'BOOK4';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BOOK4 USERS
删除表
drop table 表名;
删除表并立即释放该表所占用的资源空间
drop table 表名 purge;
不仅删除表,还会将引用该表的视图,约束,索引,触发器等.全部一并删除
drop table 表名 cascade constraints
完整性约束
数据库中的数据正确性和相容性,用来防止想数据库中添加不合语义的数据.
表级别约束 : 定义在一个表中,可用于表中多列
列级别约束 : 对表中的一列进行约束,只能应用一列
约束的用途分类:
1. primary key (主键约束)
主键是由一列或多列组成,唯一标识
在一个表中,只能定义一个主键约束
不能重复,并且不能包含null.
2. foreign key (外检约束)
外键引用另一个表中的一列,取值范围只能是引用中以存在的值
在使用外键约束的时候,被引用的列应该具有主键约束,或者具有唯一性约束.
3. unique (唯一约束)
只能存储唯一的值,可以存储null
oralce将会自动为unique的列创建一个唯一索引
可以在同一个列上建立not null和unique约束(主键约束)
4. not null (非空约束)
指定一列不允许存储空值,这就是一种强制的检查约束
5. check (检查约束)
指定一列的值必须满足某种条件
在check约束的表达式中,必须引用表中的一个或多个列,并且表达式的运算结构是一个布尔值.
一个列中可以定义多个检查约束
检查约束可以定义在表和列上.
SQL> alter table book4 modify bookname not null;
表已更改。
SQL> desc book4;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME NOT NULL VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
SQL> alter table book4 modify bookname null;
表已更改。
SQL> desc book4;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BOOKID NOT NULL NUMBER(10)
BOOKNAME VARCHAR2(40)
AUTHOR VARCHAR2(40)
PRESS VARCHAR2(40)
BOOKPRICE NUMBER(4,2)
PRESSTIME DATE
添加主键:
create table table_name(
id number(10) primary key,
...
)
为已经创建的表添加主键约束
alter table table_name add constraint pk_... primary key(列名)
alter table table_name add primary key(列名)
删除约束:
alter table table_name drop constraint 约束的名称;
检查约束
create table book(
age number(3) constraint ck_age check (age>0)
)
SQL> alter table book4 add constraint ck_book4_price check(bookprice>10);
表已更改。
创建外键:
-- Create the table in the specified schema
CREATE TABLE classes
(
classid number(10) NOT NULL PRIMARY KEY, -- primary key column
cname NVARCHAR2(50) NOT NULL,
stunum NVARCHAR2(50) NOT NULL
-- specify more columns here
);
CREATE TABLE student(
sid number(4),
sname varchar2(30),
classid number(10) references classes(classid)
);
为已有表添加外键:
alter table student add constraint fk_student_cid foreign key(classid) references classes(classid)
引用类型:
cascade
如果定义外键约束时使用cascade关键字,那么当父表中被引用列删除时,子表中对应的数据将同时被删除.
set null
当删除父表中引用列数据的时候,子表中的数据列将被设置成null
no action
当删除父表中数据会被提示违反外键约束.
禁用和激活约束
SQL> insert into book4(bookid,bookname,bookprice) values(2,'aa',-5);
insert into book4(bookid,bookname,bookprice) values(2,'aa',-5)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SYSTEM.CK_BOOK4_PRICE)
SQL> alter table book4 disable constraint ck_book4_price;
表已更改。
SQL> insert into book4(bookid,bookname,bookprice) values(2,'aa',-5);
已创建 1 行。
启用
SQL> alter table book4 enable constraint ck_book4_price;
表已更改。
查询当前用户下所有约束
select * from user_constraints;
hnkj
create table user()tablespace hnkj
** 创建用户,创建表空间只有管理员可以创建
用户权限
创建用户
create user user_name
identified by password
[default tablespace default_tablespace
| temporary tablespace temp_tablespace
|profile profile
| password expire
| account lock | unlock
]
user_name : 用户名
password : 密码
default tablespace : 设置默认表空间
temporary tablespace : 设置临时表空间
profile : 用户资源文件
password expire : 强制用户在第一次登录数据库后必须修改密码
account : 表示锁定或解锁某个账号
创建用户
create user user1
identified by root
default tablespace hnkj
password expire;
创建用户必须拥有 create user系统权限
修改:
alter user 用户名 identified by 密码
授予用户登录权限
SQL> grant create session to user1 ;
修改默认表空间
alter user user1 default tablespace 表空间名称;
让密码失效
alter user 用户名 password expire;
锁定用户
alter user 用户名 account lock;
解锁
alter user 用户名 account unlock;
删除用户
drop user 用户名 [cascade]
登录用户
connect 用户名/密码
权限
权限是数据库中执行某种操作的能力.创建一个用户,表示该用户具有了连接和操作
数据库的资格.但是要对数据库进行实际操作,则需要为用户赋予相应的操作权限.
系统权限
系统级控制数据库的存取和使用,是否可以连接到数据库.
创建会话,创建表,创建视图,创建用户等.
系统权限分类:
包含 any 关键字的系统权限 , 可以在任何Oracle模式(用户)下进行相应的操作
不包含 any 关键字的系统权限, 只能在自身用户模式中进行相应的操作.
select any ->可以查询任何用户下面的表
select -> 只能查询自身用户下面的表
create table
create session
manage tablespace
create/alter/drop user
create view;
对象权限
模式对象控制存取和使用的机制
对表,视图,存储过程和函数等操作的权限
insert,select,update,delete,alter,index
赋权 :
grant 权限名称 to 用户名
grant create session to user1;
grant create session to user1 with admin option;
with admin option : 该用户拥有赋予其他用户赋予此权限的能力
权限回收:
revoke create table,create view from user1;
角色 :
角色是一组权限的集合.可以向用户授予角色的所有权限,也可以收回角色权限.
可以使用预定义角色,也可以自定义角色.
SQL> select role,password_required from dba_roles;
ROLE PASSWORD
------------------------------ --------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
CONNECT : 用户典型权限
alter session,create cluster,create database,create sequence,create synonym,create view等...
RESOURCE : 开发者权限
create table,操作表空间....
DBA : 管理员权限
select any table,create type,createt table....
SELECT_CATALOG_ROLE : 具有查询数据字典的权限,并执行部分存储过程和函数的权限
创建角色:
create role role_name
not identified | identified by password;
修改默认角色
alter user 用户名 default role 角色名称,角色名.....
删除角色
drop role 角色名称;
dba_roles : 数据库中的所有角色
dba_role_privs : 授予用户和角色的角色.
查询当前会话包含的所有角色
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
创建用户,授权.
权限/角色
SQL 基础
SQL (Structured Query Language,结构化的查询语言),用来实现关系运算中的查询.
只要是关系型数据库SQL语言就是一套标准的语言系统.
SQL语言分类
1. 查询语言
用于检索数据库表中存储的行,可以使用SQL中的Select语句编写查询
2. 数据操作语言(DML)
insert 添加
update 修改
delete 删除
merge 合并
3. 数据定义语言(DDL)
create 创建数据结构的时候使用
alter 修改数据库结构
drop 删除数据库结构
reanme 修改表名
truncate 截断表(删除表中的全部数据)
4. 数据控制语言(DCL)
grant 授予其他用户/角色 数据结构的操作权限
revoke 收回.....
5. 事务控制(TC[L])
将修改的数据永久的保存到表中
commit 永久性保存
rollback 取消所做的修改(回滚)
savepoint 设置保存点,可以回滚到指定的位置
编写SQL语句的规则
1. SQL关键字不区分大小写的.
2. 对象名和列不区分大小写
3. 列中的值区分大小写
SCOTT 用户
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP 员工表
DEPT 部门表
SQL> desc dept;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2) 部门编号
DNAME VARCHAR2(14) 部门名称
LOC VARCHAR2(13) 部门地址
SQL> desc emp
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4) 员工编号
ENAME VARCHAR2(10) 员工姓名
JOB VARCHAR2(9) 工种
MGR NUMBER(4) 上司编号
HIREDATE DATE 入职时间
SAL NUMBER(7,2) 工资
COMM NUMBER(7,2) 奖金
DEPTNO NUMBER(2) 部门编号
select * from table_name;
select : 用于指定要检索的列(用于展示的列)
* : 表示所有列
from : 用于指定检索的表
table_name : 查询的表名
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
select <column1,column2,....> from table_name
where expression
where : 用于指定条件语句,如果条件语句返回为true,返回当前行数据,如果为false,这不会显示
expression : 表示条件语句
=
<>,!=
>=
<=
<
between ... and ... 在两个值直接
in 匹配列表中的值
like 模糊查询
isnull 检查null
时间类型比较
1. 以Oracle默认时间格式查询(dd-MM月-YY)
SQL> select ename,sal,hiredate from emp where hiredate >'01-1月-82';
ENAME SAL HIREDATE
---------- ---------- --------------
SCOTT 3000 19-4月 -87
ADAMS 1100 23-5月 -87
MILLER 1300 23-1月 -82
2. 时间转换将字符串转换成时间格式
to_date('1982-01-01','yyyy-MM-dd') 将字符串转换成时间类型
SQL> select ename,sal,hiredate from emp where hiredate >to_date('1982-01-01','yyyy-MM-dd');
ENAME SAL HIREDATE
---------- ---------- --------------
SCOTT 3000 19-4月 -87
ADAMS 1100 23-5月 -87
MILLER 1300 23-1月 -82
between...and...
SQL> select ename,sal,hiredate from emp where sal between 1100 and 3000;
ENAME SAL HIREDATE
---------- ---------- --------------
ALLEN 1600 20-2月 -81
WARD 1250 22-2月 -81
JONES 2975 02-4月 -81
MARTIN 1250 28-9月 -81
BLAKE 2850 01-5月 -81
CLARK 2450 09-6月 -81
SCOTT 3000 19-4月 -87
TURNER 1500 08-9月 -81
ADAMS 1100 23-5月 -87
FORD 3000 03-12月-81
MILLER 1300 23-1月 -82
已选择11行。
SQL> select ename,sal,hiredate from emp where sal >= 1100 and sal<= 3000;
ENAME SAL HIREDATE
---------- ---------- --------------
ALLEN 1600 20-2月 -81
WARD 1250 22-2月 -81
JONES 2975 02-4月 -81
MARTIN 1250 28-9月 -81
BLAKE 2850 01-5月 -81
CLARK 2450 09-6月 -81
SCOTT 3000 19-4月 -87
TURNER 1500 08-9月 -81
ADAMS 1100 23-5月 -87
FORD 3000 03-12月-81
MILLER 1300 23-1月 -82
已选择11行。
like "%": 0个到多个字符 "_"单个字符
SQL> select ename,sal,hiredate from emp where ename like 'S%';
ENAME SAL HIREDATE
---------- ---------- --------------
SMITH 800 17-12月-80
SCOTT 3000 19-4月 -87
SQL> select ename,sal,hiredate from emp where ename like '__A%';
ENAME SAL HIREDATE
---------- ---------- --------------
BLAKE 2850 01-5月 -81
CLARK 2450 09-6月 -81
ADAMS 1100 23-5月 -87
逻辑操作符
1. and : 条件都为true才为true 否则都为false
2. or : 如果任意一个条件为true都为true,否则返回false
3. not : 条件是false,返回true,如果条件是true就返回false
查询部门编号20 工种为CLERK
SQL> select ename,job,deptno from emp where deptno = 20 and job='CLERK';
ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
ADAMS CLERK 20
岗位,MANAGER 工资高于2500
SQL> select ename,job,deptno from emp where sal>2500 or job='MANAGER';
ENAME JOB DEPTNO
---------- --------- ----------
JONES MANAGER 20
BLAKE MANAGER 30
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
FORD ANALYST 20
已选择6行。
工种为CLERK,部门编号不是10,30,40
SQL> select ename,sal,hiredate from emp where deptno not in(10,30,40) and job = 'CLERK'
2 ;
ENAME SAL HIREDATE
---------- ---------- --------------
SMITH 800 17-12月-80
ADAMS 1100 23-5月 -87
Order By 子句,对行进行排序
select <cloumn1,...> from table_name
where expression
order by column1[,column2...] [asc | desc]
order by : 表示按列名进行排序
asc 升序(默认)
desc 降序
查询部门编号30,安装工资升序排序
SQL> select ename,sal,comm from emp where deptno = 30 order by sal asc;
ENAME SAL COMM
---------- ---------- ----------
JAMES 950
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
ALLEN 1600 300
BLAKE 2850
已选择6行。
多列排序
SQL> select ename,sal,comm from emp where deptno = 30 order by sal asc,comm desc;
ENAME SAL COMM
---------- ---------- ----------
JAMES 950
MARTIN 1250 1400
WARD 1250 500
TURNER 1500 0
ALLEN 1600 300
BLAKE 2850
已选择6行。
如果在select语句中同时包含group by,having以及order by ,则必须将order by 放在最后
## group by 分组
select <cloumn1,...> from table_name
group by column1[,column2...]
多列分组:
SQL> select job,deptno,count(1) from emp group by job,deptno order by deptno;
JOB DEPTNO COUNT(1)
--------- ---------- ----------
CLERK 10 1
MANAGER 10 1
PRESIDENT 10 1
ANALYST 20 2
CLERK 20 2
MANAGER 20 1
CLERK 30 1
MANAGER 30 1
SALESMAN 30 4
已选择9行。
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7369 SMITH CLERK 7902 1980-12-17 800 20
2 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
3 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30
4 7566 JONES MANAGER 7839 1981-4-2 2975 20
5 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
6 7698 BLAKE MANAGER 7839 1981-5-1 2850 30
7 7782 CLARK MANAGER 7839 1981-6-9 2450 10
8 7788 SCOTT ANALYST 7566 1987-4-19 3000 20
9 7839 KING PRESIDENT 1981-11-17 5000 10
10 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
11 7876 ADAMS CLERK 7788 1987-5-23 1100 20
12 7900 JAMES CLERK 7698 1981-12-3 950 30
13 7902 FORD ANALYST 7566 1981-12-3 3000 20
14 7934 MILLER CLERK 7782 1982-1-23 1300 10
部门编号分组
10 20 JOB 30
CLARK SMITH CLERK ALLEN
KING JONES MANAGER WARD
MILLER SCOTT ANALYST MARTIN
ADAMS CLERK BLAKE
FORD ANALYST TURNER
JAMES
根据部门编号工种分组
20 JOB
SMITH CLERK ADAMS CLERK
JONES MANAGER
SCOTT ANALYST FORD ANALYST
having 子句过滤组
显示平均工资高于2000的部门编号,平均工资,最高工资..
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) >2000;
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
只保留工资大于1000的行,根据部门分组,保留部门平均工资高于2000的行
SQL> select deptno,avg(sal) from emp where sal > 1000 group by deptno having avg(sal)>2000 order by deptno;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2518.75
使用 distinct 来检索唯一的列值
去除列中的重复值
SQL> select job,count(1) from emp group by job;
JOB COUNT(1)
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
SQL> select distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
使用运算符:
(+,-,*,/)
SQL> select ename,sal*(1.2) from emp;
ENAME SAL*(1.2)
---------- ----------
SMITH 960
ALLEN 1920
WARD 1500
JONES 3570
MARTIN 1500
BLAKE 3420
CLARK 2940
SCOTT 3600
KING 6000
TURNER 1800
ADAMS 1320
ENAME SAL*(1.2)
---------- ----------
JAMES 1140
FORD 3600
MILLER 1560
已选择14行。
DML语句
1. insert
insert into table_name [column1,column2,....] values(value1,value2,....);
insert into table_name [column1,column2,...] select (column1,column2,....) from table_name2;
insert into : 指明要插入表中的字段
values : 插入相应字段中的值
SQL> create table dept4 as select * from dept where 1!=1;
表已创建。
2. Update
update table_name set column1 = value1[,column2=value2,....] where expression;
SQL> update newemp set sal = sal*1.1 ;
已更新14行。
SQL> select ename,sal from newemp;
ENAME SAL
---------- ----------
SMITH 880
ALLEN 1760
WARD 1375
JONES 3272.5
MARTIN 1375
BLAKE 3135
CLARK 2695
SCOTT 3300
KING 5500
TURNER 1650
ADAMS 1210
ENAME SAL
---------- ----------
JAMES 1045
FORD 3300
MILLER 1430
已选择14行。
SQL> update newemp set sal = sal*1.2 where ename = 'WARD' ;
已更新 1 行。
SQL> select ename,sal from newemp;
ENAME SAL
---------- ----------
SMITH 880
ALLEN 1760
WARD 1650
JONES 3272.5
MARTIN 1375
BLAKE 3135
CLARK 2695
SCOTT 3300
KING 5500
TURNER 1650
ADAMS 1210
ENAME SAL
---------- ----------
JAMES 1045
FORD 3300
MILLER 1430
已选择14行。
-
delete
delete from table_name [where expression];使用delete语句删除表中的数据,并不能释放被善用的数据块空间,它只是把哪些被删除的数据块标记为 Unused,将来还可以回退(rollback)操作.
-
merge
把数据从一个表中复制到另一个表,插入新数据或替换掉旧数据.merge into table11 using table2 on expression
when matched then update…
when not matched then insert …;SQL> merge into newemp ne
2 using emp e
3 on (ne.empno = e.empno)
4 when matched then
5 update set ne.sal = e.sal
6 when not matched then
7 insert values(e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);14 行已合并。
表连接查询
连接查询是指将两个或两个以上的表或试图的查询.在实际应用中,查询单个表可能无法满足需要.
-
简单连接
使用逗号将两个或多个表进行连接.自然连接
自然连接是指使用相等比较(=)指定连接条件,主要用于检索主从表之间的关联数据
select table1.column,table2.column… from table1,table2
where table1.column1 = table2.column2;显示部门名称,员工名称,工资 只显示部门编号为10的
表的别名:
SQL> select dname as 部门名称,ename “员工姓名”,sal 员工工资 from dept d,emp e where e.deptno = d.deptno and d.deptno = 10;部门名称 员工姓名 员工工资
ACCOUNTING CLARK 2450
ACCOUNTING KING 5000
ACCOUNTING MILLER 1300内连接
内连接用于返回满足条件的记录select table1.column,table2.column…
from table1 [inner] join table2
on table1.column1 = table2.column2;SQL> select d.dname,e.ename from dept d inner join emp e on d.deptno = e.deptno;
DNAME ENAME
SALES ALLEN
SALES WARD
RESEARCH JONES
SALES MARTIN
SALES BLAKE
ACCOUNTING CLARK
RESEARCH SCOTT
ACCOUNTING KING
SALES TURNER
RESEARCH ADAMS
SALES JAMESDNAME ENAME
RESEARCH FORD
ACCOUNTING MILLER已选择13行。
外连接
select table1.column,table2.column…
from table1 [left | right] join table2
on table1.column1 = table2.column2;外连接是内连接的扩展,它不仅会返回满足条件的所有记录,也会返回不满足条件的记录,如果连接表中(主表)有一列空值,也会被显示.
SQL> select d.dname,e.ename from dept d left join emp e on d.deptno = e.deptno;
DNAME ENAME
SALES ALLEN
RESEARCH JONES
SALES MARTIN
SALES BLAKE
ACCOUNTING CLARK
RESEARCH SCOTT
ACCOUNTING KING
SALES TURNER
RESEARCH ADAMS
SALES JAMESDNAME ENAME
RESEARCH FORD
ACCOUNTING MILLER
吃饭部
OPERATIONS已选择15行。
SQL> select d.dname,e.ename from dept d ,emp e where d.deptno = e.deptno(+);
DNAME ENAME
SALES ALLEN
SALES WARD
RESEARCH JONES
SALES MARTIN
SALES BLAKE
ACCOUNTING CLARK
RESEARCH SCOTT
ACCOUNTING KING
SALES TURNER
RESEARCH ADAMS
SALES JAMESDNAME ENAME
RESEARCH FORD
ACCOUNTING MILLER
吃饭部
OPERATIONS已选择15行。
自连接
同一张表之间的连接查询SQL> select m.ename||’ 是 ‘||e.ename||’ 领导!’ from emp m,emp e where m.mgr = e.empno;
M.ENAME||‘是’||E.ENAME||'领导!
FORD 是 JONES 领导!
SCOTT 是 JONES 领导!
JAMES 是 BLAKE 领导!
TURNER 是 BLAKE 领导!
MARTIN 是 BLAKE 领导!
WARD 是 BLAKE 领导!
ALLEN 是 BLAKE 领导!
MILLER 是 CLARK 领导!
ADAMS 是 SCOTT 领导!
CLARK 是 KING 领导!
BLAKE 是 KING 领导!M.ENAME||‘是’||E.ENAME||'领导!
JONES 是 KING 领导!
SMITH 是 FORD 领导!已选择13行。
SQL> select m.ename,e.ename from emp m,emp e where m.mgr = e.empno;
ENAME ENAME
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KINGENAME ENAME
JONES KING
SMITH FORD已选择13行。
交叉连接(笛卡尔积)
######## 数据库事务
事务(Transaction) 是由一组(多条)SQL语句组成的最小逻辑工作单元.
事务是为了保证数据的一致性.
事务的提交和回滚
要永久性的记录事务中SQL语句的结果,需要执行commit语句,从而提交事务.
事务的开始于结束:
连接到数据库上,并执行一条DML语句(insert,update或delete)
前一个事务结束后,又输入一个DML语句.
事务结束:
执行commit或rollback语句.
执行一条DDL语句,create table,都会自动提交commit;
断开数据库的连接,退出sql*plus时,输入exit的时候 都会被commit.
如果sql*plus 被意外终止了,计算机蓝屏,会自动执行rollback语句.
执行一条DML语句,该语句失败,这种情况,会执行rollback语句;
只有DML存在事务的概念,DDL和DCL没有事务的概念,执行就被提交.
设置保存点:
保存点就是事务中标记.把一个较长的事务分为多个较短的事务.
可以回滚到制定的保存点.
savepoint savepoint_name;
事务的ACID特性:
原子性(Atomic) 事务是原子的,这就是说事务中包含的所有SQL语句都是一个不可分割的工作单元.
一致性(Consist) 事务必须确保数据库的状态保存一致,事务开始时,数据库的状态是一致的,事务结束时,数据库的状态也必须是一致的.
隔离性(Isolated) 多个事务可以独立运行,而不会彼此影响.
持久性(Durable) 一旦事务被提交后,数据库的变化就会被永久记录,即使数据库软件崩溃.
并发事务:
数据库支持多个用户同时对数据库进行交互,每个用户都可以同时运行自己的事务,这种事务就称为并发事务
用户同时运行多个事务,而这些事务对同一张表产生影响.那么这些事务是互相独立的.
直达执行一条commit才会彼此产生影响.
事务锁:
当多个事务对同一条数据进行操作的时候,就会产生事务锁.
第二个事务必须等待第一个事务释放该锁(提交/回滚);
并发事务会产生的问题:
事务隔离级别:
是一个事务对数据库的修改与冰箱的另外一个事务的隔离程度.
两个并发事务T1和T2正在访问相同的行数据.
幻象读取:
事务T1读取一条指定的where子句返回的结果集.然后事务T2新插入一行数据,这行
恰好可以满足T1查询的where条件.然后T1又使用相同的查询再次对表进行查询,但
此时看到了事务T2刚才插入的新行,这个新行就称为"幻象"
不可重复读:
事务T1读取一行数据,紧接着T2修改了T1刚才读取的哪一行数据.然后T1再次读取的时候,
发现刚才读取的结果不同的了.这种现象称为 " 不可重复读".
脏读:
事务T1更新了一行数据,但是并没有提交所修改的内容,事务T2读取更新后的行,然后T1回滚操作,
取消了刚才所做的修改,先T2所读取的行就无效了.这种现象"脏读".
因为在T2读取这行数据的时候,T1所做的修改并没有提交.
隔离级别 从低到高
read uncommitted 幻读,不可重复读和脏读都允许
read committed 允许幻读和不可重复读,但是不允许脏读
reoeatabke read 允许幻读,但是不允许不可重复读和脏读
serializable 幻象读,不可重复读和脏读都不允许
Oracle 数据库只支持read committed和serializable两种事务隔离级别.
set transaction isolation level
{
read committed|
serializable
}
简单函数
SQL 函数包含单行函数和多行函数
单行函数就是输入一行输出页数一行.
多行函数也称为分组函数,将多行数据返回一个值.
1. 字符函数
1. ascii(x) 用于返回字母的ascii码
SQL> select ascii(dname) from dept;
ASCII(DNAME)
------------
98
65
82
83
79
2. length(x) 获取字符个数
SQL> select length(dname) from dept;
LENGTH(DNAME)
-------------
1
10
8
5
10
SQL> select dname from dept where length(dname)>5;
DNAME
--------------
ACCOUNTING
RESEARCH
OPERATIONS
3. concat(x,y) 拼接字符串
SQL> select deptno,dname||loc from dept;
DEPTNO DNAME||LOC
---------- ---------------------------
50 ba
10 ACCOUNTINGNEW YORK
20 RESEARCHDALLAS
30 SALESCHICAGO
40 OPERATIONSBOSTON
SQL> select deptno,concat(dname,loc) from dept;
DEPTNO CONCAT(DNAME,LOC)
---------- ---------------------------
50 ba
10 ACCOUNTINGNEW YORK
20 RESEARCHDALLAS
30 SALESCHICAGO
40 OPERATIONSBOSTON
4. lower(x) 转换小写
SQL> select * from dept where lower(dname) = lower('SalEs');
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
5. upper(x) 转换大写
SQL> select upper(dname) from dept;
UPPER(DNAME)
--------------
B
ACCOUNTING
RESEARCH
SALES
OPERATIONS
6. substr(x,start[,length])
x : 需要截取的字符串
start : 开始位置
length : 攫取的长度
7. instr(x,find_string[,start][,occurrence]) 查询find_string,然后返回所在的位置.occurrence第几次出现的位置
SQL> select instr('welcome','e'),instr('welcome','e',3,2) from dual;
INSTR('WELCOME','E') INSTR('WELCOME','E',3,2)
-------------------- ------------------------
2 0
SQL> select instr('welcome','e'),instr('welcome','e',1,2) from dual;
INSTR('WELCOME','E') INSTR('WELCOME','E',1,2)
-------------------- ------------------------
2 7
8. replace(x,search_string,replace_string) 替换
x : 用于查找的字符串
search_string : 要查询的字符串
replace_string : 替换的字符串
SQL> select replace(dname,'S','*') from dept;
REPLACE(DNAME,
--------------
b
ACCOUNTING
RE*EARCH
*ALE*
OPERATION*
9. initcap(x) : 用于将单词首字母转换成大写.
SQL> select initcap('welcome to you!') from dual;
SQL> select initcap(dname),initcap(loc) from dept;
INITCAP(DNAME) INITCAP(LOC)
-------------- -------------
B A
Accounting New York
Research Dallas
Sales Chicago
Operations Boston
SQL> select initcap(substr(dname,2,3)) from dept;
INITCA
------
Cco
Ese
Ale
Per
10. rpad(x,widht[,pad_string]) : 在x的右边补齐多少个空格
SQL> select rpad(dname,20,'*') from dept;
RPAD(DNAME,20,'*')
----------------------------------------
b*******************
ACCOUNTING**********
RESEARCH************
SALES***************
OPERATIONS**********
11. lpad()
SQL> select lpad(dname,20,'*') from dept;
LPAD(DNAME,20,'*')
----------------------------------------
*******************b
**********ACCOUNTING
************RESEARCH
***************SALES
**********OPERATIONS
2. 数字函数
1. ABS(X) 绝对值
SQL> select abs(10),abs(-10) from dual;
ABS(10) ABS(-10)
---------- ----------
10 10
2. MOD(X) 余数
SQL> select mod(8,3),mod(8,4) from dual;
MOD(8,3) MOD(8,4)
---------- ----------
2 0
3. CEIL(X) 向上取整
SQL> select ceil(13.1),ceil(-5.2) from dual;
CEIL(13.1) CEIL(-5.2)
---------- ----------
14 -5
4. FLOOR(x) 向下取整
SQL> select floor(13.8) from dual;
FLOOR(13.8)
-----------
13
5. ROUND(X[,y]) 四舍五入
SQL> select round(123.456,2),round(5.75,-1) from dual;
ROUND(123.456,2) ROUND(5.75,-1)
---------------- --------------
123.46 10
SQL> select round(123.456,2),round(3.75,-1) from dual;
ROUND(123.456,2) ROUND(3.75,-1)
---------------- --------------
123.46 0
6. POWER(x,y) x的y次幂
SQL> select power(2,3),power(-3,2) from dual;
POWER(2,3) POWER(-3,2)
---------- -----------
8 9
7. SQRT(x) 平方根
SQL> select sqrt(4) from dual;
SQRT(4)
----------
2
8. SIGN(x) 检测正负值
SQL> select sign(10),sign(0),sign(-1) from dual;
SIGN(10) SIGN(0) SIGN(-1)
---------- ---------- ----------
1 0 -1
9. COS(x)
SQL> select cos(0.5) from dual;
COS(0.5)
----------
.877582562
10. ACOS(x) 反余弦
SQL> select acos(0.3),acos(-0.3) from dual;
ACOS(0.3) ACOS(-0.3)
---------- ----------
1.26610367 1.87548898
3. 转换函数
1. to_char(x) : 将x转换成一个字符串,格式化字符串
SQL> select to_char(123459.67,'$999,999.99') from dual;
TO_CHAR(1234
------------
$123,459.67
2. to_number(x,[,format]) : 将x转换成一个数字.
SQL> select 123+to_number('25') from dual;
123+TO_NUMBER('25')
-------------------
148
SQL> select to_number('-$12,345.67','$99,999.99')+10 from dual;
TO_NUMBER('-$12,345.67','$99,999.99')+10
----------------------------------------
-12335.67
3. cast(x as type) : 将x转换成指定type的兼容类型
SQL> select
2 cast(123 as varchar2(10)),
3 cast('456' as number(10))
4 from dual;
4. asciistr() : 将任意字符串转换成数据库字符集的ascii字符串
SQL> select asciistr('中国') from dual;
ASCIISTR('
----------
\4E2D\56FD
5. bin_to_num(x) : 将二进制数字转换成十进制
6. to_date() : 将字符串转换成时间类型
SQL> select to_date('2019-08-09','yyyy-MM-dd') from dual;
TO_DATE('2019-
--------------
09-8月 -19
4. 正则表达式(忽略)
1. regexp(x,pattern[,match_option]) 用于在x中查找pattern参数中定义的正则表达式
SQL> select dname from dept where regexp_like(dname,'^r','i');
DNAME
--------------
RESEARCH
SQL> select dname from dept where regexp_like(dname,'^\w{4,5}$','i');
DNAME
--------------
SALES
5. 聚合函数
1. avg() 平均数,非null的平均值
2. count() 获取非空值得数量
3. max()和min() 函数
4. sum() 所有值得和
SQL> select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
800
30 9400
20 10075
10 8750
5.
6. 日期函数
to_char()和to_date()转换时间
SQL> select to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2019-08-31 10:50:07
SQL> insert into mydate values(to_date('2019-09-09','yyyy-MM-dd'))
2. sysdate 获取系统当前时间
SQL> select sysdate from dual
2 ;
SYSDATE
--------------
31-8月 -19
3. months_between(x,y) 函数判断2个月份差
SQL> select months_between(sysdate,to_date('2020/01/01','yyyy/MM/dd')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('2020/01/01','YYYY/MM/DD'))
----------------------------------------------------------
-4.0175806
4. add_months(x,y) 计算x+y个月后的结果
SQL> select add_months(sysdate,4) from dual;
ADD_MONTHS(SYS
--------------
31-12月-19
SQL> select add_months(sysdate,-5) from dual;
ADD_MONTHS(SYS
--------------
31-3月 -19
5. next_day(x,day)函数 返回由第二个参数day指定的星期几,第一次出现的日期.
SQL> select next_day(to_date('2019-08-27','yyyy-MM-dd'),'星期三')from dual;
NEXT_DAY(TO_DA
--------------
28-8月 -19
SQL> select next_day(to_date('2019-08-27','yyyy-MM-dd'),'星期一')from dual;
NEXT_DAY(TO_DA
--------------
02-9月 -19
6. round(x) 四舍五入
SQL> select round(to_date('2019-06-26','yyyy-MM-dd'),'yyyy') from dual;
ROUND(TO_DATE(
--------------
01-1月 -19
SQL> select round(to_date('2019-07-16','yyyy-MM-dd'),'MM') from dual;
ROUND(TO_DATE(
--------------
01-8月 -19
SQL> select round(to_date('2019-07-19 13:25:5','yyyy-MM-dd HH24:MI:SS'),'dd') from dual;
ROUND(TO_DATE(
--------------
20-7月 -19
7. TRUNC() 截断
SQL> select trunc(to_date('2019-08-04','yyyy-MM-dd'),'YYYY') from dual;
TRUNC(TO_DATE(
--------------
01-1月 -19
SQL> select trunc(to_date('2019-08-04','yyyy-MM-dd'),'MM') from dual;
TRUNC(TO_DATE(
--------------
01-8月 -19
8. extract() 提取时间
SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
2019
7. 其他函数
【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
注意两者的类型要一致
【语法】NVL2 (expr1, expr2, expr3)
【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。
expr2和expr3类型不同的话，expr3会转换为expr2的类型
SQL> select ename,decode(deptno,10,'A',20,'B',30,'C','D') from emp;
ENAME D
---------- -
SMITH D
ALLEN C
WARD C
JONES B
MARTIN C
BLAKE C
CLARK A
SCOTT B
KING A
TURNER C
ADAMS B
ENAME D
---------- -
JAMES C
FORD B
MILLER A
已选择14行。
子查询(嵌套查询)
单行子查询 : 不向外部的SQL语句返回结构,或者只返回一行.
多行子查询 : 向外部的SQL语句返回一行或多行
多列子查询 : 向外部SQL语句返回多列
关联子查询 : 引用外部SQL语句中的一列或多列,这种子查询被称为关联子查询
嵌套子查询 : 位于子查询中的查询. 子查询最多可以嵌套 255 层
子查询可放在另一个查询的where子句中.
查询部门平均工资,低于部门平均工资的最高值
1000
1500
800
select avg(sal) from emp group by deptno
显示高于自身部门平均工资的员工信息
部门编号,员工姓名,工种,工资
获取每个部门的平均工资
当员工的部门编号等于 查询出来平均工资的部门编号,使用工资和平均工资比较
where中的子查询
SQL> select empno,ename from emp where deptno = (select deptno from dept where dname = 'SALES');
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
已选择6行。
having中的子查询
SQL> select deptno,avg(sal) from emp group by deptno
2 having avg(sal) < (select max(avg(sal)) from emp group by deptno);
DEPTNO AVG(SAL)
---------- ----------
800
30 1566.66667
20 2518.75
from中的子查询
SQL> select d.deptno,ename,job,sal from emp,(select deptno,avg(sal) avgsal from emp group by deptno) d
2 where emp.deptno=d.deptno and emp.sal<d.avgsal;
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
10 SMITH CLERK 800
30 WARD SALESMAN 1250
30 MARTIN SALESMAN 1250
30 TURNER SALESMAN 1500
20 ADAMS CLERK 1100
30 JAMES CLERK 950
10 MILLER CLERK 1300
已选择7行。
子查询常见的错误:
1. 单行子查询返回多个值
SQL> select deptno,ename from emp where deptno = (select deptno from dept);
select deptno,ename from emp where deptno = (select deptno from dept)
*
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行
2. 子查询不能包含order by语句
SQL> select * from emp where sal<(select avg(sal) from emp order by avg(sal) desc);
select * from emp where sal<(select avg(sal) from emp order by avg(sal) desc)
*
第 1 行出现错误:
ORA-00907: 缺失右括号
多行子查询:
in : 匹配子查询结构的任意一个值即可.
all : 必须符合子查询结果的所有值
any : 只要符合子查询结构的人一个值即可
CLERK
PRESIDENT
MANAGER
select ename,job deptno from emp where job in (CLERK,PRESIDENT,MANAGER);
查询部门编号30的所有员工,工资.
显示所有员工中工资大于上面工资中任意一个工资的员工信息
SQL> select ename,sal,deptno from emp where sal < any(select sal from emp where deptno = 30);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
JAMES 950 30
ADAMS 1100 20
WARD 1250 30
MARTIN 1250 30
MILLER 1300 10
TURNER 1500 30
ALLEN 1600 30
CLARK 2450 10
已选择9行。
SQL> select ename,sal,deptno from emp where sal < any(500,2000);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
ALLEN 1600 30
WARD 1250 30
MARTIN 1250 30
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
MILLER 1300 10
已选择8行。
SQL> select ename,sal,deptno from emp where sal < all(select sal from emp where deptno = 30);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
SQL> select * from dept where deptno > all(10,20,30);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 b a
多列子查询
SQL> select deptno,ename,sal from emp where (deptno,sal)in(select deptno,min(sal) from emp group by deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 JAMES 950
20 ADAMS 1100
10 SMITH 800
关联子查询:
关联子查询对外部查询中每一行都会运行一次,这与非关联子查询是不同的.
非关联子查询只在运行外部查询之前执行一次,
SQL> select empno,deptno,ename,sal from emp outer
2 where sal >
3 (select avg(sal) from emp inner where inner.deptno = outer.deptno)
4 ;
EMPNO DEPTNO ENAME SAL
---------- ---------- ---------- ----------
7499 30 ALLEN 1600
7566 20 JONES 2975
7698 30 BLAKE 2850
7782 10 CLARK 2450
7788 20 SCOTT 3000
7839 10 KING 5000
7902 20 FORD 3000
已选择7行。
select * from emp e,
(select deptno,avg(sal) a from emp group by deptno) d;
where e.deptno = d.deptno and e.sal<d.a
子查询 exists和not exists
select ename,job,sal,deptno from emp e
where exists(
select 1 from dept d
where loc = 'NEW YORK' and d.deptno = e.deptno
)
选课表 : 学号,课程号
学生表 : 学号,姓名
课程表 : 课程号,课程名
查询选修了java课程的学生姓名
select 姓名 from 学生表 where 学号 in(
select 学号 from 选课表 where 课程号 =
(select 课程号 from 课程表 where 课程名= ‘java’)
)
exists的写法
select 姓名 from 学生表
where exists
(
select * from 选课表 where 学生表.学号 = 选课表.学号 and 课程号 =(…)
)
查询选修了所有课程的学生:
select 姓名 from 学生表
where not exists
(
select * from 课程表 where not exists(
select * from 选课表 where 学生表.学号 = 选课表.学号 and 课程表.课程号 = 选课表.课程号
)
)
嵌套子查询
SQL> select deptno,avg(sal) from emp
2 group by deptno
3 having avg(sal)>
4 (select max(avg(sal)) from emp
5 where deptno in
6 (select deptno from dept
7 where deptno > 10)
8 group by deptno);
select deptno from dept where deptno > 10
查询部门编号大于10的部门编号 20,30,40,50
select max(avg(sal)) from emp where deptno in (20,30,40,50) group by deptno
计算部门平均工资的最大值 2200
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2200
显示大于20,30,40,50部门最高平局工资的部门编号和平均工资
包含子查询的修改和删除
update where
delete where
delete from emp where deptno =(
select deptno from dept where dname = 'SALES'
)
update emp set (sal,comm) = (select sal,comm from emp where ename = 'SMITH')
where job = (select job from emp where ename = 'SMITH')
高级查询
集合操作 : 将两个或多个查询返回的结构结合起来
union,union all,intersect,minus
并集(获取的结果集合并到一个结果集中)
SQL> select * from dept where deptno > 10
2 union all
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 b a
40 OPERATIONS BOSTON
50 b a
已选择6行。
SQL> select * from dept where deptno > 10
2 union
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 b a
并集:
SQL> select * from dept where deptno > 10
2 intersect
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 b a
差集:
SQL> select * from dept where deptno > 10
2 minus
3 select * from dept where deptno >30;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
case表达式
case search_expression
when expression1 then result1
when expression2 then result2
......
when expression then result
else default_result;
员工工资小于1000
1000~2000
>2000
low med high
3 8 10
SQL> select empno,ename,
2 case deptno
3 when 10 then 'A'
4 when 20 then 'B'
5 when 30 then 'C'
6 else 'not exists'
7 end
8 from emp;
EMPNO ENAME CASEDEPTNO
---------- ---------- ----------
7369 SMITH A
7499 ALLEN C
7521 WARD C
7566 JONES B
7654 MARTIN C
7698 BLAKE C
7782 CLARK A
SQL> select
2 count(case when sal<1000 then 1 else null end) low,
3 count(case when sal between 1000 and 2000 then 1 else null end) med,
4 count(case when sal>2000 then 1 else null end) high
5 from emp;
LOW MED HIGH
---------- ---------- ----------
2 6 6
一行数据展示出来所有部门的人数(列转行)
select * from
(select deptno,count(1) c from emp group by deptno) t
pivot (min(c) for deptno in(10,20,30,40,50))
可编程SQL(Oracle)
pl/sql : PL/SQL 是Oracle在标准SQL语句上的过程性扩展.pl/sql 不仅允许嵌入SQL语句,还可以定义变量和常量.允许使用条件语句和循环语句,异常处理
pl/sql 优势:
1. 支持面向对象编程,支持自定义类型,实例化,继承,程序块中的重载.
2. 更好的提升程序性能,pl/sql 把一个pl/sql 语句块统一进行编译后执行,同时还可以吧编译好的SQL快存储到Oracle的服务器上面,可以重复调用.而sql非过程语句,只能一条一条执行.pl/sql的执行快速高效
3. 良好的移植性 : 使用pl/sql编写应用程序,,可以移植到任何操作系统上面.,同时可以编写程序库,在不同的环境中重用.
4. 安全性 : 可以使用权限控制是否可以访问存储过程.
5. .......
1. 掌握pl/sql程序结构
2. 掌握流程控制语法
3. 掌握复合变量的语法
4. 掌握游标的用法
5. 会使用异常
过程/函数/无名块,它们之间可以互相嵌套
1. 当定义的变量的时候 ,建议使用v_作为前缀,v_sal,v_job....
2. 当定义常量的时候, 建议使用 c_ 前缀
3. 定义游标的时候,通常使用 _cursor作为后缀, dept_cursor
4. 定义异常的时候,e_
5. ...
## pl/sql程序块
pl/SQL 分为两种:无名块,命名块:函数,过程,包,触发器....
pl/sql 由3部分组成:定义部分,执行部分,异常处理部分
定义部分 : 定义变量,常量,游标,异常,复合数据等类型.
执行部分 : 实现用用模块的一些功能,dml,输出语句,判断....
异常部分 : 处理异常的代码,当程序出现异常的时候执行.
[DECLARE
... -- 定义部分
]
begin
.. -- 执行部分
[ EXCEPTION
... -- 异常处理部分
]
end;
DECLARE,begin,EXCEPTION 后面都没有分号,而end后则必须要带分号;
DECLARE
-- 定义
num NUMBER; -- 定义变量
BEGIN
num := 6+6; -- 为变量赋值
dbms_Output.put_line('6 + 6 = '||num);--输出变量
EXCEPTION -- 异常处理
when OTHERS THEN
Dbms_Output.put_line('出现异常了!');
END;
变量和类型
pl/sql数据类型有标量类型,复合类型,参照数据类型,lob数据类型
1. 标量只能存放单个数值,最常用的变量都是标量.
1. 变量名以字母开头,不区分大小写
2. 变量名由字母,数字,$,_组成
3. 变量长度最多30个字符
4. 变量名中不能有空格
5. 不能使用关键字
variable_name [constant] data_type not null [default | := value]
variable_name : 变量名
constant : 是否是常量
data_type : 数据类型(char,number,date,varchar2)
not null : 表示该变量非空,必须指定默认值
default 或 := value : 给变量赋值默认值
declare
c_id constant integer := 66;
v_gender char(1);
v_num number(5,2) default 66.6;
v_date date;
isfindshed boolean default true;
begin
end;
pl/sql运算符
= 等于(逻辑判断)
<>,!=,~=,^= 不等于
:= 赋值运算符
=> 关系号
.. 范围运算符
|| 字符串连接
注释
-- 单行注释
/*
多行注释
*/
declare
/*
c_id constant integer := 66;
v_gender char(1);
v_num number(5,2) default 66.6;
v_date date;
isfindshed boolean default true;
*/
v_sal number;
begin
select sal into v_sal from emp where empno = 7788;
dbms_output.put_line(v_sal);
end;
if分支语句
condition1,condition2 只能是boolean表达式
statements1,statements2,statements3 是pl/sql语句,如果condition1为true,执行statements1...
if condition1 then
statements1
elsif condition2 then
statements2
else
statements3
end if;
if语句基本选择结构,每一个if语句都有then,以if开头的语句不能跟结束符合,每一个if语句以 end if结束.每一个if只能有1个else
declare
n1 number := 100;
n2 number := 50;
begin
--查询部门20人数和30人数哪一个部门人数多,输出 : 20 部门多少人 大于 30部门 多少人.30
-- 查询 20 和 30 部门的人数,进行
select count(1) into n1 from emp where deptno = 20;
select count(1) into n2 from emp where deptno = 30;
if n1 > n2 then
dbms_output.put_line('n1 大于 n2!');
else
dbms_output.put_line('n1 小于 n2!');
end if;
end;
循环
基本循环,while循环,for循环
1. 基本循环
loop
statements
exit [when condition]
end loop;
至少会被执行一次.
2. while 循环
while condition loop
......
end loop;
declare
total number :=60;
v_count number :=1;
begin
while total < 50 loop
total := total+total;
v_count:=v_count+1;
end loop;
dbms_output.put_line('v_count = '||v_count ||'次结果:'||total);
end;
3. for循环
for loop_variable in [reverse] lower..upper loop
....
end loop;
loop_variable : 指定循环变量
reverse : 每次递减输出.
lower : 循环的起始值
upper : 循环的终止值
declare
i integer;
begin
for i in reverse 1..20 loop
dbms_output.put_line(i);
end loop;
end;
GOTO 和 NULL
goto : 跳转结构.使用goto可以跳转到指定的设定标签"<<>>"创建标签
##复合变量
-
%type : 指定变量的数据类型和数据库中的列类型一致.
- 获取一个变量的数据类型
-
%rowtype
- 获取表中一行记录.
-
PL/SQL记录(自定义类型)
一个类型中可以包含多个变量
type record_name_type is record(field1_name data_type [not null][default| :=] default_value.…
)
record_name_type : 自定义记录(类型) 的名称
field1_name : 字段名称
data_type : 字段属性标量是存储一行单列的值(标量),复合变量一行多列(pl/sql记录)
多行单列,pl/sql集合
pl/sql集合类型是类似于高级语言中的数据(集合),集合类型包括
1. 索引表(pl/sql表)
1. 处理pl/sql中的数组数据类型
1. 高级语言的数组长度是有限制的,并且下标不能为负数
2. 索引表中的元素个数没有限制,并且下标可以为负数.type type_name is table of element_type [not null] index by key_type; identifier type_name; type_name : 自定义数据类型的名称(is table..index 表示是一个索引表) element_type 索引表元素的数据类型 not null : 表示不能引入null元素 key_type : 索引表元素下标的数据类型(binary_integer,pls_integer或varchar2) 整形(只能为整数) binary_integer : 2~31次方 ,如果超出存储上限,自动转换成number类型 pls_integer : 2~31次方 如果超出存储上限,抛出异常 identifier : 自定义索引表变量名称; -- Created on 2019-09-04 by LINNE declare type dname_table_type is table of dept.dname%type -- 指定索引表的存储的数据类型 index by binary_integer; -- 指定索引表下标的数据类型 dname_table dname_table_type; i number; begin -- Test statements here select dname into dname_table(1) from dept where deptno = 10; select dname into dname_table(2) from dept where deptno = 20; select dname into dname_table(3) from dept where deptno = 30; select dname into dname_table(4) from dept where deptno = 40; -- dbms_output.put_line(dname_table(2)); for i in 1..4 loop dbms_output.put_line(dname_table(i)); end loop; end; -- Created on 2019-09-04 by LINNE declare -- Local variables here i integer; type area_table_type is table of number index by varchar2(10); area_table area_table_type; begin -- Test statements here area_table('c新乡') := 6; area_table('b郑州') := 5; area_table('a驻马店') :=30; dbms_output.put_line('第一个元素 : '||area_table.first); dbms_output.put_line('最后一个元素 : '||area_table.last); dbms_output.put_line('下一个元素 : '||area_table.next('新乡')); end; 2. 嵌套表 1. 嵌套表也是pl/sql数组的数据类型的 2. 高级语言数组元素下标从0或1开始的.并且元素个数有限制 3. 嵌套表下标必须从1开始,并且元素没有元素限制 4. 高级语言中数组是有序的,嵌套表元素的数组可以是无序的. 5. 索引表类型不能做为表中列的数据类型使用 6. 嵌套表类型可以作为表中列的数据类型使用 type type_name is table of element_type; idetifer type_name; type_name : 用于指定嵌套表的类型名 element_type : 嵌套表元素的数据类型 idetifer : 定义嵌套表类型变量 ** 在使用嵌套表元素之前,必须首先使用其构造方法初始化嵌套表. -- Created on 2019-09-04 by LINNE declare type ename_table_type is table of emp.ename%type; ename_table ename_table_type; begin -- Test statements here ename_table := ename_table_type('李四','王五','赵柳','丽丝尔');-- 使用构造方法初始化嵌套表变量 select ename into ename_table(2) from emp where empno = 7788; dbms_output.put_line(ename_table(1)); end; -- 表列中使用嵌套表 create type phone_type is table of varchar2(50); create table employee( id number(4), name varchar2(10), phone phone_type ) nested table phone store as phone_table;--phone_table 就是集合的表名 insert into employee values(1001,'ccc',phone_type('0373-1234567','13603738558')) insert into employee values(1002,'bccc',phone_type('0373-1234567','13603738558','12345678')) select * from employee; 3. 边长数组 1. 其他同上 2. 元素的最大个数是有限制的 type type_name is varray(size) of element_type [not null]; declare type dept_count_record is record( dno emp.deptno%type, v_count number); -- 自定义pl/sql记录 type dept_count_type is table of dept_count_record index by binary_integer; -- 定义索引集合表 dept_table dept_count_type; --创建索引表实例 i integer; --定义标量 begin -- 查询第一行数据赋值给索引表集合第一个元素 select deptno, n into dept_table(1) from (select rownum r, c.* from (select deptno, count(1) n from emp group by deptno) c) t where t.r = 1; select deptno, n into dept_table(2) from (select rownum r, c.* from (select deptno, count(1) n from emp group by deptno) c) t where t.r = 2; select deptno, n into dept_table(3) from (select rownum r, c.* from (select deptno, count(1) n from emp group by deptno) c) t where t.r = 3; dbms_output.put_line(dept_table.count); -- 循环集合中的所有元素 for i in 1 .. dept_table.count loop -- 分支判断 dbms_output.put_line(dept_table(i) .dno || ' 总人数 : ' || dept_table(i).v_count); if dept_table(i).v_count > 5 then dbms_output.put_line(dept_table(i).dno || '是土豪...有' || dept_table(i) .v_count || '个人!'); elsif dept_table(i).v_count > 3 then dbms_output.put_line(dept_table(i).dno || '是小土豪...有' || dept_table(i) .v_count || '个人!'); else dbms_output.put_line(dept_table(i) .dno || '你真穷' || dept_table(i).v_count || '个人!'); end if; end loop; end;
## 集合方法
exists() : 检查集合中是否存在指定的下标
count() : 获取集合中元素的个数
limit() : 获取最大元素个数
first()
last()
next()
prior()
....
delete() : 删除
## 游标
pl/sql 的游标是指把从数据库中查询出来的数据以临时表的形式存放的内存中,游标可以对存储在内存中的数据进行操作,返回一条或一组记录,或者一条也不返回.
pl/sql 中的记录和表类型虽然可以存储数据,但是对一组存在内存中的数据进行操作比较麻烦,游标就是解决内存查询到的数据进行处理改变.
游标的基本操作:
pl/sql 包含隐士游标和显示游标
1. 声明游标
cursor cursor_name is select....
2. 打开游标
open cursor_name;
3. 提取游标
fetch cursor_name into variable1,variable2.....
4. 关闭游标
close cursor_name
cursor cursor_name is select...
cursor : 游标关键字
cursor_name : 游标名称
select.. : 建立游标所使用的查询语句
### 游标属性
游标作为一个临时表,可以通过游标的属性获取游标的状态.
1. %isopen 属性主要用于判断游标是否打开,在使用游标的时候如果不能确定是否已经打开可以判断使用(未打开的游标不可以提取)
2. %found 属性主要用于判断游标是否找到记录,如果找到记录用fetch语句提取游标数据
3. %notfound 如果提取到数据返回false否则返回true和%found正好相反
4. %rowcount 该属性用于返回到当前提取的实际行数
declare
cursor c_emp is select * from emp;
v_emp emp%rowtype;
begin
open c_emp;
fetch c_emp into v_emp;
while c_emp%found loop
dbms_output.put_line(c_emp%rowcount||' '||v_emp.ename|| ' '||v_emp.sal);
fetch c_emp into v_emp;
end loop;
if c_emp%isopen then
close c_emp;
end if;
end;
参数化游标
定义游标后,使用后再传参
cursor cursor_name(paramter) is select....
-- Created on 2019-09-04 by LINNE
declare
cursor emp_cursor(dno number) is select ename from emp where deptno = dno;
v_ename emp.ename%type;
begin
-- Test statements here
open emp_cursor(20);
loop
fetch emp_cursor into v_ename;
dbms_output.put_line(v_ename);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
dbms_output.put_line('---------------------------------');
open emp_cursor(10);
loop
fetch emp_cursor into v_ename;
dbms_output.put_line(v_ename);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
游标for循环
游标for循环是在pl/sql块中使用游标最简单的方式.简化了游标的处理.Oracle会隐含的打开游标,提取游标,关闭游标.
for record in cursor_name loop
.....
end loop;
declare
cursor c_dept(dno number) is select * from dept where deptno = dno;
begin
for v_dept in c_dept(20) loop
dbms_output.put_line('第'||c_dept%rowcount||'个员工'||v_dept.dname);
end loop;
end;
## 隐式游标
在执行一个SQL语句的时候,Oracle服务器将自动创建一个隐式游标.隐式游标的固定名称 SQL,隐式游标不需要声明和打开.使用完也不需要关闭.
隐式游标只能处理一行数据,所以into只能给一组变量赋值!
操作游标(游标的删除和修改)
## 修改和删除游标
## 游标变量
## 异常
pl/sql 程序运行期间经常会发生各种异常,一旦异常,如果不进行处理程序就会被终止.Oracle系统将异常分为预定义异常和自定义异常.
EXCEPTION
when exception1 then
.....
when exception2 then
.....
when others then
.....
如果异常都不匹配,将执行others块中的代码
预定定义异常:
timeout_on_resource : 等待资源发生超时
invalid_cursor : 使用了一个无效的游标
no_logged_on : 未连接Oracle
login_denied : 无效的用户名和密码
no_data_found : 没有找到数据(into)
zero_divide : 视图被零除
value_error : 转换或截断错误
access_into_null : 给null对象赋值
too_many_rows : 返回过多的数据
.....
自定义异常:
EXCEPTION_name exception; 声明异常对象
raise EXCEPTION_name 触发异常
异常函数
sqlcode() : 返回Oracle的错误编号
sqlerrm() : 返回对应的错误信息
raise_application_error(error_code,message,[{true|false}])
error_code : 定义错误编号,-20000~20999之间的负数
message : 提示的错误信息,长度不能超过2048字节
true|false : 如果是true,则该错误被放在之前的错误栈中,如果设置false(默认值),会替换之前的所有错误.
declare
标量
%type;
%rowtype;
pl/sql记录
集合
游标
异常
begin
if
case
while
for
loop
goto
null
end;
pl/sql 高级应用
之前所有创建的pl/sql程序都是匿名的,没有为程序块提供一个名词,这就无法被存储,每次只需后不可以被重复使用.
每次允许匿名块的时候都需要先编译再执行,在很多时候为了提高系统的应用性能,需要数据库保存程序块,方便以后重复
使用,这就意味着程序块需要一个名词.
命名程序块 : 过程,函数,程序包,触发器...
存储过程
存储过程是用于执行特定操作.
1. 创建
create [or replace] procedure procedure_name
[(paramater_name [in|out|in out] datatype [,...])]
{ is | as }
begin
....
endl
or replace : 表示如果存储过程已经存在,则替换已有的存储过程,如果不存在就创建
in|out|in out : 参数的模式
is | as : declare 定义变量使用 is和as是等价的.
datatype : 参数的数据类型
.... : 过程的实际代码
2. 调用
exec
call
3. 修改
or replace
4. 删除
drop
参数:
create or replace procedure add_dept
(dno number,dname varchar2 default null,loc varchar2 default null)
is
begin
insert into dept values(dno,dname,loc);
end;
1. 位置传值
按照定义参数的顺序依次为参数指定响应的变量和值.
call add_dept(13,'吃饭','新乡市');
2. 按照名称传值 =>
call add_dept(dname => '玩耍',dno=>14);
3. 组合传值,先按照位置传值然后名称传值
call add_dept(15,loc => '郑州市');
参数模式:
1. in : 输入参数
2. out : 输出参数
3. in out : 输入输出参数
函数
函数用于返回特定的数据,如果在应用程序中进程需要通过SQL语句来返回特定数据,可以创建函数
函数和存储过程,存储过程用来完成一项工作,可能返回值,可能不返回值.但是函数必须返回通过return
create [or replace] function function_name(......)
return datatype
is | as
begin
....
end;
程序包
包通常都是由包头和包体两部分组成,
包头就是定义(接口)
包体就是实现(实现类)
在包体中包含的子程序,游标,都必须在包头中声明,实现全部包体中.
包头编译失败,包体一定失败.
创建包头
create [or replace] package package_name
{is | as}
package_specification
end;
系统预定义包:
dbms_ddl
dbms_alert
utl_tcp
utl_file
utl_smtp
utl_http
包的构造:
在包中定义了全局变量,有些时候,会话中可能还需要初始化全局变量,可以通过包构造初始化.
当在会话内第一次调用公共组件的时候,会自动执行其构造过程.
声明 : 在包体的最后 添加begin 关键字,后面编写构造内容
触发器
触发器(tigger) 是一种特殊的类型的pl/sql块,触发器类似于过程和函数,也具有声明部分,执行部分,异常部分.
触发器是在 数据库事件发生时被隐式触发的,而且触发器不能接收参数,不能像过程和函数已有显示调用.
触发器的组成:
触发事件 : 引起触发器被触发的事件.例如: DML语句(insert,update,delete)语句的时候对表或试图进行数据操作的时候触发,DDL事件(create,alter,drop),数据库事件(启动,退出,异常错误)用户事件(登录,退出)
触发的时间 : 设置触发事件发生之前(before)还是之后(after)
触发对象 : 包括 表,试图,模式,数据库. 只有在指定的对象上发生了特定的时间才会被触发
触发条件 : 是一个逻辑表达式
触发频率 : 设置语句基本的触发器还是行基本的触发器
触发器大小不能超过32kb,如果要实现触发器功能需要超过这个限制,可以考虑使用存储过程来代替触发器.在触发器中调用存储过程.
不能创建递归触发器
在触发器中不能使用事务控制 : commit,rollback,savepoint
触发器中不能声明任何 long或long raw变量
触发器的类型:
1. DML触发器
dml所包含的事件,insert,update,delete ! DML语句触发器可以为这些触发事件创建 before(发生前)和after(发生后)触发器. DML触发器可以在语句级别或行级别操作上被触发
语句级别是对于每一个SQL语句只触发一次,行级对SQL受影响的表中的每一行都触发一次
2. DDL触发器
1. create,alter,drop.....
3. 系统触发器
1. logon,logoff,serverror,startup,shutdown
4. 替代触发器
1. 在对视图进行dml操作的时候执行的触发器
create [or replace] trigger trigger_name
{before | after } trigger_event
on {table_name | view_name}
[for each row]
begin
end;
trigger : 表示触发器对象
trigger_name : 触发器名称
before | after : 表示在事件发生之前触发还是事件发生之后触发
trigger_event : 触发事件
for each row : 表示是否是行级触发器.,默认是语句触发器
修改触发器状态:
alter trigger trigger_name enabled | disabled;
删除触发器:
drop trigger trigger_name;
条件谓词 : 为了触发器代码中区分具体触发的事件.
INSERTING
UPDATING
DELETING
触发器的新值和旧值
OLD关键字获取数据库操作之前的旧值
NEW获取操作之后的新值
在使用的时候前面必须添加 :, :old,:new
OLD的值只对update和delete操作有效
NEW值只对update和insert有效
OLD和NEW关键字只能用于行级触发器,不能用在语句级别的触发器.
数据库触发器
create table event_table(
event varchar2(30),
time date
)
create or replace trigger tir_startup
after startup on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
create or replace trigger tir_shutdown
before shutdown on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
create table log_table(
username varchar2(20),
logo_time date,
logoff_time date,
address varchar2(20)
);
登录触发
create or replace trigger tir_logon
after logon on database
begin
insert into log_table(username,logo_time,address)
values(ora_login_user,sysdate,ora_client_ip_address)
end;
退出触发
create or replace trigger tir_logon
before logoff on database
begin
insert into log_table(username,logoff_time,address)
values(ora_login_user,sysdate,ora_client_ip_address)
end;
DDL 触发器
create or replace trigger tir_ddl
after ddl on scott.schema
begin
......
insert into event_ddl values(ora_sysevent,ora_login_yser,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
所有命名pl/sql块就结束了.
函数
存储过程
触发器
包
数据的其他对象
索引
临时表
外部表
分区表
簇表
视图
视图是一个虚拟表,它同真实表包含名称,列,行数据.视图不在数据中存储数据值,它的行和列中的数据来自于定义视图的查询语句所使用的表.
数据库只在数据字典存储定义视图本身的SQL语句.
用户可以在视图上进行 insert,update,delete操作.通过视图修改数据的时候,实际上修改的是表中存储的数据.
视图可以 基于 表创建视图
可以基于视图和表创建
创建视图,主要是为了将一个或多个表中经常被使用到的数据结合到一起,方便用户操作.
create [or replace] view view_name as <select 语句> [with check option]
whit check option : 主要是对视图中数据进行操作的时候验证.
实际上,系统在创建视图的时候,只是将视图中定义的SQL存入到了数据库的数据字典中,并不执行其中的select语句,只有当用户对视图进行查询的时候,系统才会按照
视图定义从基本表中获取数据.
create or replace view emp_dept_view(员工编号,员工姓名,工资,部门名称)
as
select empno,ename,sal,dname from emp e,dept d where e.deptno = d.deptno;
可更新试图
可以通过修改视图中的数据,修改基本表.
一个视图可以同时包含可更新字段和不可更新自动,字段是否可以被更新主要取决于select语句.
一个视图中,通过计算得到的列,是不可以被更新.
create or replace view emp_insert2_view
as
select empno,ename,deptno from emp where deptno = 10
with check option;
在创建有条件限制视图的时候,应该添加with check option 选项,可以有效的限制对视图的操作.
drop view 视图名称;
序列
序列是Oracle提供的用于生产一系列唯一数字的数据库对象.
使用序列可以实现主键值增长.序列也可以在多用户下并发使用.
为用户生产不重复的顺序数字.
序列不占用实际的存储空间,只是在数据字典中存储了定义.
创建序列必须要有 create sequence系统权限.
create any sequence
create sequence sequence_name
[start with start]
[increment by increment]
[maxvalue maxvalue | nomaxvalue]
[minvalue minvalue | nominvalue]
[cache | nocache]
[order | noorder]
start : 开始位置,递增序列起始值是从minvalue开始的....
increment : 增量,正数递增序列,负数就是递减序列
cache : 在内存中是否预习分配序列值
order : 是否保证是按照顺序生产的.
create sequence emp_seq
start with 1000
increment by 10
nomaxvalue
cache 10
--获取下一个序列的值
select EMP_SEQ2.nextval from dual
--获取当前序列的值
select EMP_SEQ2.currval from dual
同义词
同义词,是表,索引,视图等模式对象的一个别名,不占用实际存储空间.
公有同义词 : 在数据中所有的用户都已使用
私有同义词 : 只有自身用户可以使用,可以控制谁可以访问我的私有同义词.
create [public] synonym sysnonym_name for object(表名,视图名称)
create public synonym public_dept for dept
drop public synonym public_dept
select * from public_dept
** 当数据被打开或关闭的时候被触发**
索引
1.说明
1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
6）oracle创建主键时会自动在该列上创建索引
## B树索引
## 位图索引
## 反向建索引
## 函数索引
select * from emp where upper(ename) = upper('smith');
语法
create unique | btimap index <index_name>
on <table_name>
(column_name | <expression> asc | desc )
tablespace <tablespace_name>
如果全部省略默认B树索引.
create index ename_index on emp(ename);
create index ename_job_index on emp(ename,job);
create bitmap index job_bitmap_index on emp(job)
create index dno_reveres_index on em1(empno) reverse;
create index edate_func_index on emp(to_char(hiredate,'yyyy'))
select * from emp where to_char(hiredate,'yyyy')>'1981'
临时表:
只有向临时表中添加数据的时候才会被oracle分配存储空间.
堆表:通过create table语句执行后,oracle就会分配一个盘区
l临时表存储数据是以实物或会话为基础的.
事务级别的临时表
create global temporary table temp_stu(
id number,
name varchar2(30)
)on commit delete rows;
会话级别:
create global temporary table temp_stu(
id number,
name varchar2(30)
)on commit preserve rows;
退出数据会自动清空
外部表:
oracle系统提供的文件系统数据库.
oracle 可以将一个格式化的文本文件,转换成虚拟的数据库表
create directory wuyu_dir as 'd:\db'; 指向数据文件的目录(创建一个目录对象)
create table stu(
id number,
name varchar2(20),
gender varchar2(2),
age number
)
organization external(
type oracle_loader
default directory wuyu_dir
access parameters(
fields terminated by ',')
location('wuyu.txt')
);
oracle_loader : 本地加载进程
default directory : 用来指定所使用的目录对象,该目录对象指向外部数据文件所在目录.
location : 指定源数据文件
access parameters : 设置访问驱动程序数据格式转换的参数
fields terminated by : 用来指定字段直接的分隔符
分区表和分区索引表:
分区表是指将巨型表分割成较小的,可独立管理的部分.分区后的表和未分区的表在执行DML操作的时候没有区别.
优点:
增强可用性 : 表的某个分区出现故障,不影响其他分区的数据使用.
维护方便 : 如果表的某个分区坏了,只需要修改该分区.
均衡I/O 可以将不同的分区映射到不同磁盘中.
改善查询性能 可以仅搜索某个分区,从而提高查询性能
分类:
1. 范围分区
2. 散列分区
3. 列表分区
4. 组合分区
5. 组合范围分区
create table student(
id number primary key,
name varchar2(20),
subject varchar2(10),
score number
)
partition by range(score)
(
partition part1 values less than(60) tablespace hnkj,
partition part2 values less than(80) tablespace hnkj,
partition part3 values less than(maxvalue) tablespace hnkj
)
create table student2(
id number primary key,
name varchar2(20),
subject varchar2(10),
score number
)
partition by hash(id)
(
partition part1 tablespace hnkj,
partition part2 tablespace hnkj,
partition part3 tablespace hnkj
)
create table student3(
id number primary key,
name varchar2(20),
subject varchar2(10),
score number
)
partition by list(subject)
(
partition part1 values ('java','jsp','javascript') tablespace hnkj,
partition part2 values ('C#','ASP.NET') tablespace hnkj
)
查询指定分区中的数据
SQL> select * from student partition(part3)
备份和恢复
物理备份 : 对数据库的物理文件进行复制,分为脱机备份和联机备份
RMAN
逻辑备份 : 通过使用Oracle提供的导出工具进行备份
SQL 优化:
1. 避免使用 "*" 代替所有列
select * from test; Oracle数据库需要先分析,在编译只执行
select tid,tname from test; : 直接编译,执行
2. delete 和 truncate
SQL> truncate table student drop storage; 删除数据的时候,直接将物理空间收回
表被截断。
SQL> truncate table student reuse storage; 保留被删除的空间提供给新数据使用
表被截断。
delete 是逐行删除,性能差,可以被事务回滚
truncate 一次性删除,效率高,但是不可回退
3. 在确保完整性前提下,尽量多使用commit;
4. 减少表的查询次数
select tname from teacher where tid = (select tid from student where sname = '无语')
select tname from teacher t,student s where t.tid = s,tid;
5. 使用where代替having
select class,count(*) from student where class!='java班' group by class
select class,count(*) from student group by class having class!='java班'
6. 使用 <= 替代 <
select * from student where score < 60
select * from student where score <= 59
7.
审计/数据库的数据加密/数据加载/数据传输/闪回..../日志恢复..
扫一扫
1237
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
