Oracle基础整理

向右看齐 2022-08-10 09:50 262阅读 0赞

# 一、数据库原理 #

## 1. 数据库的基本概念： ##

数据库（Database,简称DB）是按照数据结构来组织、存储和管理数据的仓库。

数据库管理系统（DatabaseManagement System,简称DBMS）管理数据库的软件。

## 2. 关系数据库 ##

关系：描述两个元素间的关联或对应关系

使用关系模型把数据组织到二维数据表中

产品化：Oracle、DB2、Sybase、SQL server、MySQL

## 3. 表的概念 ##

一个关系数据库由多个数据表组成，数据表是数据库的基本存储结构。

表是二维的，由行和列组成。

表的行（row）是横排数据，也被称作记录（Record）

表的列（Column）是纵列数据，也被称作字段（Field）

表和表之间存在关联关系。

# 二、主流关系型数据库 #

## 1. Oracle数据库 ##

Oracle（甲骨文）公司的数据库产品；

世界上第一个商品化的关系型数据库管理系统；

采用标准SQL（结构化查询语言），支持多种数据类型，提供面向对象的数据支持；

支持多种平台。

## 2. DB2数据库 ##

IBM公司的关系型数据库管理系统；

很多不同版本，可以运行的从掌上产品到大型机不同的终端机器上

DB2 Universal Database Personal Edition 和

DB2 Universal Database Workgroup Edition分别是单用户和多用户系统，

可以运行在OS/2和windows上

DB2是oracle的主要竞争对手

##  3. Sybase数据库 ##

美国Sybase公司的关系型数据库系统

较早采用C/S技术的数据库厂商

典型的UNIX或Windows NT平台上客户机/服务器环境下的大型数据库系统。

Sybase通常与Sybase SQL Anywhere用于客户机/服务器环境，前者作为服务器数据库，

后者作为服务器数据库，后者为客户机数据库，采用PowerBuilder作为开发工具，

国内大中型系统中具有广泛的应用；

2010年被SAP收购

## 4. SQL server数据库 ##

微软产品，运行在WindowNT服务器上

广泛适用于大型、跨国企业的数据库管理

## 5. MySQL数据库 ##

开源的小型关系型数据库管理系统，广泛用于中小型网站中；

成本低、规模较Oracle和DB2小；

2008年1月16日，被Sun收购MySQL，2009年4月20日，Sun被Oracle收购；

# 三、SQL结构化程序语言 #

## 1. 数据定义语言（DDL） ##

Create：创建表或其他对象的结构

Alter：修改表或其他对象的结构

Drop： 删除表或其他对象的结构

Truncate：删除表数据，保留表结构

## 2. 数据操作语言（DML） ##

Insert：将数据插入到数据表中

Update：更新数据表中已经存在的数据

Delete：删除表中的数据

## 3. 事务控制语言（TCL） ##

Commit： 提交，确认已经进行的数据改变

RollBack：回滚，取消已经进行的数据改变

Savepoint：保存点，是当前事务可以回退到指定的保存点，便于取消部分改变

## 4. 数据查询语言（DQL） ##

Select：查询所需要的数据

## 5. 数据控制语言（DCL） ##

Grant：授予，用于给用户或角色授予权限

Revoke：用于收回用户或角色已有的权限

Create user：创建用户

## 6. 相关概念（非重点） ##

SQL\*Plus是Oracle提供的数据库交互工具。基于传统C/S结构的SQL开发工具。

# 四、Oracle数据类型 #

## 1. NUMBER ##

数字类型，NUMBER（P , S）形式，P：表示数字的总位数；S：表示小数点后面的位数

例：

Sal number（6,2）  》最大取值为9999.99

## 2. CHAR ##

固定长度的字符类型，CHAR（N）形式，N：表示占用的字节数

例：

ename char（20）   》最多存储20个字符串，占用空间是固定长度20个字节

## 3. VARCHAR2 ##

变长的字符类型，VARCHAR2（N），N：表示最多可占用的字节数

例：

Job varchar2（100）  》最多存储100个字符串，占空间变化，最多100个字节空间

## 4. DATE ##

用于定义日期时间的数据，长度是7个字节，默认格式DD-MON-RR

# 五、数据定义语言（DDL）与一些SQL语句 #

## 1. create语句 ##

Create table 表名(

列名 列的数据类型\[定义默认值或约束\]，

列名 列的数据类型

)

例：

创建表employee

Create table employee(

Id number(4),

Name varchar2(20),

Gender char(1),

…..

)

## 2. DESC语句 ##

Desc 表名；查看表结构

例：

查看employee表结构

Desc employee;

## 3. DEFAULT语句 ##

用于给列指定默认值

例：

给gender指定默认值‘M‘

创建表employee

Create table employee(

Id number(4),

Name varchar2(20),

Gender char(1) default ‘M’,

…..

)

## 4. NOT NULL ##

非空约束，确保字段值（列值）不为空

例：

定义id非空约束

创建表employee

Create table employee(

Id number(4) NOT NULL,

Name varchar2(20),

Gender char(1) default ‘M’,

…..

)

## 5. 修改表 ##

Rename修改表名

Rename 旧表名 to 新表名;

Alter 表名 add  》给表增加列

例：

给表employee增加列hiredate

Alter table employee add( hiredate date default sysdate);

Alter 表名 drop(列名) 》给表增加列

例：

给表employee删除列hiredate

Alter table employee drop( hiredate);

Alter表名 modify(列名列的数据类型) 》给表增加列

例：

修改表employee列hiredate

Alter table employee modify( hiredate char(7));

# 六、DML（数据操作语言）语句 #

## 1. insert语句 ##

给数据表增加记录

Insert into 表名\[（列名，列名….）\] values(对应列的数据，对应列的数据)；

例：

插入一条记录：

Insert into employee（id,name,job,salary）values(1001,’rose’,’good’,100000);

## 2. update语句 ##

更新表中的记录

Update 表名 set  列名=新数据 where 条件

例：

更新rose薪水为250；

Update employee set salary=250 where id=1001；

## 3. delete语句 ##

删除表中的记录

Delete from 表名 where 条件；

例：

删除id=1001的记录

Delete from employee where id=1001；

# 七、Oracle字符串操作 #

## 1.字符串类型 ##

Char和VARCHAR的默认单位为字节，可指定为字符

CHAR：定长字符，存不满不空格；最大取值为2000字节，不指定长度默认人值为1,

VARCHAR2：变长字符，存多少占用多少；最大取值为4000字节，必须指定长度

每个英文字符占用一个字节，每个中文按编码不同占用2-4个字节

ZHS16GBK：2个字节，UTF-8:2-4个字节

例：

Char(10),等价于char(10 byte),指定单位为字符char（10 char）

Long和clob类型

Varchar的加长版，存储变长；

long最多存储2gb的字符数据；clob最多存储4gb（常用）

## 2.字符串函数 ##

Concat和‘||’

Concat（char1，char2）返回两个字符串连接后的结果，

例：

Select concat(concat(ename,’:’),sal) from emp;

多个字串连接，用||更直观

Select ename ||’:’|| sal from emp;

Length(char)：用于返回字符串的长度。

如果是varchar2返回实际长度，如char，返回定长（含空格）

例：

Select ename,length(ename) from emp;

Upper、lower和initcap

Upper：转换成大写形式；lower转换成小写形式；initcap用于将字符串中首位大写

Trim、Ltrim、Rtrim

用于截去子串；

Trim(c2 from c1)：从c1的前后截去c2

Ltrim（c1，c2）：从c1的左边截去c2

Rtrim（c1，c2）：从c1的右边截去c2

例：

Select trim(‘e’ from ‘elite’) as “t1”, Ltrim(‘elite’,’e’) as “t2”,Rtrim(‘elite’,’e’)as “t3” from dual;

输出结果为：

T1  T2   T3

---  ----  ----

lit  lite  elit

Lpad(char1,n,char2)、Rpad(char1,n,char2)

补位函数，用于在字符串左（Lpad）或右(Rpad)补足n位，char2可重复多次

例：

Select ename,Lpad(sal,6,’$’)as salary from emp;

结果为$$4000,$10000

Substr（char,m,n）

获取字符串的字串，从1开始，第m个字符开始，，n长度。

例：

Select substr('youou',3,2) from dual;

结果为:

Instr(char1,char2,m,n)

用于返回子串char2在原字符串char1中的位置，从1开始，第m个字符开始，n为第几次出现。

例：

Select instr(‘youou’ ,'o’,1,2) from dual;

返回结果为4

# 八、Oracle数值操作 #

## 1.数值类型 ##

Number（p）表示整数，number（p，s）表示浮点数

Round（n\[,m\]）：用于四舍五入；m为0取整，m为负数则四舍五入到小数点前m位

例：

Round（5456.45,-1）from dual;---------5460

Trunc（n\[,m\]）:非四舍五入，而是截取数值；

例：

Trunc（5456.45,-1）from dual;---------5450

Mod（m，n）：返回m除以n后的余数，n为0则直接返回m

Ceil（n）和floor（n）取n的最大或最小整数值；

例：

Select ceil（45.678） from dual;--------46

Select floor（45.678） from dual;--------45

# 九、Oracle日期操作 #

## 1. 日期类型 ##

Date类型在数据库中的存储固定为7个字节

Timestamp：Oracle常用的日期类型，最高精准度可以到达ns（纳秒），7或11个字节；

## 2. 日期关键字 ##

Sysdate本质是一个内部函数，返回系统当前时间，精准到秒；格式：DD-Mon-RR

例：

Select sysdate from dual;-------------- 23-7月 -15

Systimestamp 本质是内部函数，精准到毫秒

例：

Select systimestamp from dual ;---------- 23-7月 -1510.34.26.893000000 上午 +08:00

Select to\_char(systimestamp,’ssss.FF’) from dual；-------5050.922000

## 3. 日期转换函数 ##

To\_date：将字符串按照定制格式转换为日期类型

To\_char：将其他类型的数据转换为字符类型

## 4. 日期常用 ##

Last\_day：返回日期date所在月的最后一天；

Add\_months：返回日期date加上i个月后的日期值

例：

Select ename，add\_months(hiredate，20\*12) as “20周年” from emp；

Months\_between（date1，date2）：计算date1和date2两个日期之间隔了多少个月

例：

Select ename, months\_between(sysdate,hiredate) from emp;

Next\_day(date, char)：返回date日期数据的下一个周几；

例：

查询下个周三是几号；

Select next\_day(sysdate,4) from dual;

Least、greatest：比较函数，返回结果是参数中最大或最小的值

例：

Select least(sysdate,’10-10月-08’) from dual；

Extract：从参数datetime中提取参数date指定的数据，比如年，月，日；

例：

Select extract(year from sysdate) current\_year from dual;-----------2015

Select extract(hour from timestamp ‘2008-11-1 10:16:14’) fromdual;--------10

# 十、空值操作 #

## 1.null的操作 ##

更新成null值

例：

Update student set gender=null;

条件查询null

例：

Select \* from student where gender is null;

非空约束

例：

Create table student(

Gender char(1) not null

);

## 2.空值函数 ##

NVL（expr1，expr2）与NVL2（expr1,expr2,expr3）

NVL如果expr1为空，则返回expr2

NVL如果expr1不为空，则返回expr2；如果expr1为空，则返回expr3；

# 十一、SQL基础查询 #

## 1.基本查询语句 ##

From子句

例：

Select \* from dept；

使用别名

例：

Select empno as id, ename “name”, sal\*12 “annual salary” from emp;

Where 子句

例：

Select \* from emp where deptno=10;

Select 子句

例：

Select enamel，sal from emp；

## 2.查询条件 ##

使用>，<，>=，<=，!=，<>，=

使用and，or关键字

使用like条件（模糊查询），通配符：%:表示0 到多个字符 ；\_：表示单个字符

例：

Select ename，job from emp where ename like‘\_A%’;

使用in和not in比较操作符IN(list)用来取出符合列表范围中的数据

例：

Select ename,job from emp where job in(‘manager’,’clerk’);

使用between…and…操作符用来查询符合某个值域范围条件的数据

例：

Select ename,sal from emp where sal between 1500 and 3000;

使用is not null和is null

例：

Select ename,sal,comm from emp where comm is null;

使用any和all，不能单独使用

例：

Select empno,ename,job,sal,deptno from emp where sal>any(3500,4500);

使用distinct过滤重复

Select distinct deptno from emp；

## 3.排序 ##

使用order by子句

例：

Select ename,deptno from emp order by deptno;

ASC和DESC

默认排序是ASC升序，DESC来指定降序

例：

Select empno,ename,mgr from emp where deptno=10 order by mgr;

## 4. 聚合函数 ##

Max和min表示取列的最大、最小值

例：

Select max(sal),min(sal) from emp;

Avg和sum用来统计平均值和总值

例：

Select avg(sal),sum(sal) from emp;

Count用来统计表中的记录条数

例：

Select count(job) from emp;

## 5. 分组 ##

Group by子句：用于分组

例：

每个部门的最高薪水

Select deptno,max(sal) from emp group by deptno;

Having子句：使用分组时的过滤条件

例：

Select deptno,max(sall) from emp group by deptno havingmax(sal)>4000;

## 6.查询语句顺序 ##

1. from子句：从后往前，数据量较少的表尽量放在后面

2. where子句：自下而上，从右往左，将能过滤最大记录数量的条件放在右边

3. group by子句：从左往右分组，group前最后使用where过滤不需要的记录

4. having子句：消耗资源，尽量避免使用

5. select子句：少使用\*号，尽量取字段名称，通过数据字典转换成列名，消耗时间

6.order by子句：从左往右，消耗资源；

# 十二、SQL（关联查询） #

## 1.关联基础 ##

需要查询两个或两个以上数据表或视图的查询叫连接查询

例：

Select table1.column, table2.column fromtable1,table2

where table1.column1=table2.column2;

笛卡尔积：关联操作每个表的每一行都和其他表的每一行做组合，两表记录xy，返回x\*y

等值连接：通常实在主外键关联关系的表间建立；

例：

Select e.name ,e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;

## 2.关联查询 ##

内连接where或join….on….：内连接所有满足连接条件的记录；

例：

Select e.name ,e.job, d.dname, d.loc from emp e, dept d wheree.deptno=d.deptno;

等价于

Select e.name,e.jon, d.dname, d.loc from emp e join dept d one.deptno=d.deptno;

外连接Left|Right|Full\[outer\] join…on…

例：

Select e.name, e.job, d.dname, d.loc from emp e Left|Right|Full \[outer\]join dept d

On d.deptno=e.deptno;

自连接：略

# 十三、SQL高级查询 #

## 1.子查询 ##

子查询在where子句中

例：

查询和scott同职位的员工

Select e.ename,e.job from emp e where e.job=(Select job from emp whereename=‘scott’);

查找薪水比整个机构平均薪水高的员工

Select deptno，ename，sal from emp where sal>(select avg(sal) from emp);

查询部门中有salesman但职位不是salesman的员工信息

Select empno,ename from emp where deptno in (Select deptno from empwhere job=’salesman’)and job <> ‘salesman’;

列出那些有员工的部门信息

Select deptno, dname from dept d where exists(Select \* from emp e whered.deptno=e.deptno)

子查询在having子句中

查询最低薪水高于部门30的最低薪水的部门信息

Select deptno, min(sal) from emp group by deptno havingmin(sal)>(Select min(sal)Select min(sal) from emp where deptno=30);

子查询在From部分

查询出薪水比本部门平均薪水高的员工信息

Select e.deptno,e.ename, e.sal from emp e join (Select deptno,avg(sal)avg\_sal from emp group by deptno) x

on e.deptno=x.deptno and e.sal>avg\_sal order by e.deptno;

子查询在select部分

Select e.ename,e.sal,(select d.dname from dept d whered.deptno=e.deptno) dname

From emp e;

等价于

Select e.ename,e.sal,d.dname from emp e left join dept d one.deptno=d.deptno;

## 2. 分页查询 ##

Rownum：被称作伪列，用于返回标识行数据的数字，

Select rownum, empno,ename, sal from emp;

不能从结果集中直接截取

Select rownum,empno, ename,sal from emp where rownum >3;------查不到结果

正确实例：

Select \* from (select rownum rn,e.\* from emp e) where rn between 8 and10;---示3行结果

## 3. decode函数 ##

Decode（expr, search1, result1\[,search2,result2…\]\[,default\]）:用于比较参数expr的值，如

果匹配到search条件，返回对应的result结果；没有提供default参数值，返回null；

例：

Select ename,job,sal decode(job, ‘manager’ ,sal\*1.2,’analyst’,sal\*1.1,sal) bonus from emp;

等价于(有case语句)                                               from emp;γ

Select ename,case job when ‘manager’ then sal\*1.2,when ‘analyst’ thensal\*1.1 else sal end

分组中的应用

场景：计算职位的人数，analyst/manager属于vip，其余是普通员工operation，无法使

用group By简单实现。

Select decode(job,’analyst’,’vip’,’manager’,’vip’,’operation’)job,count(1) job\_cnt from emp

Group by decode((job,’analyst’,’vip’,’manager’,’vip’,’operation’);

场景：Dept表中按”operation”, ”accounting”, “sales”排序。

Select deptno,dname,loc from dept order by decode(dname,”operation”,1,”accounting”,2 ,“sales”,3);

## 4.排序函数 ##

Row\_number() over(partition by col1 order by col2)：表示根据col1分组，在分组内根据col2排序。

场景：按照部门编号分组显示，然后每组按照职员编号排序，并赋予组内编码。

Select deptno,ename,empno,row\_number() over(partition by deptno order byempno) as Emp\_id from emp;

Rank() over(partition by col1 order by col2)：表示根据col1分组，在分组内给予等级标识。

和row\_number的区别是：row\_number没有重复值，而rank有重复值，跳跃排序。

Dense\_rank() over(partition by col1 order by col2)：连续排序

## 5.集合操作 ##

Union、union all：用来获取两个或两个以上结果集的并集。

Union操作符会自动去掉合后的重复记录

Union返回所有记录，包括重复行

Union操作对查询结果排序，union all不排序；

Intersect获取两个结果集的交集

Minus获取两个结果集的差集，只有在第一个结果集中存在，而第二个不存在才显示

例：

Select ename, job, sal from emp

Where job=’ manager’

Minus

Select ename, job, sal from emp

Where sal>=2500;

## 6.高级分组函数 ##

Rollup、cube和groupingsets运算符是Group by子句的扩展，

（此部分重点在于：多多练习书上例题）

# 十四、视图、序列、索引 #

## 1.视图 ##

视图也称作虚表，是一组数据的逻辑表示。作用：简化复杂查询；限制数据访问。

授权创建视图：grantcreate view to tarena;

创建简单视图

例：

Create view v\_emp\_10

Select empno,ename,sal,deptno from emp where deptno=10;

查看视图：descv\_emp\_10

对视图进行DML操作，会影响基表。

创建具有withcheck option约束的视图，对可见范围内的视图修改，否则导致对视图进

行DML操作失败

Create or replace view v\_emp\_10

Select empno,ename,sal,deptno from emp where deptno=10;

With check option;

20部门不在视图内，所有相关20的DML操作都会失败

创建具有with readonly 约束的视图，所有操作都会失败

与视图相关的数据字典：user\_objects、user\_views、user\_update\_columns

场景：查询user\_objects中所有视图名称

select object\_name from user\_objects where object\_type=’view’;

场景：查询指定视图

Select text from user\_views where view\_name=’v\_emp\_10’;

场景：数据字典中user\_updatable\_columns查询视图

Select column\_name,insertable,updatable,deletable from user\_updatable\_columns

From user\_updatable\_columns

Where table\_name=’v\_emp\_10’;

删除视图：dropview view\_name;

## 2.序列 ##

序列是一种用来生成唯一数字值的数据库对象；

使用序列

场景：创建一个序列，起始数据是100，递增10；

Create sequence emp\_seq

Start with 100

Increment by 10;

序列中有两个伪列

Nextval：获取序列的下个值

Currval：获取序列的当前值

场景：插入新记录到emp

Select emp\_seq.nextval from dual;

Insert into emp(empno,ename)values(emp\_seq.nextval, ‘donna’);

删除序列

Drop dequence emp\_seq;

## 3.索引 ##

索引是一种允许直接访问数据表中某一行的树形结构。

索引记录中存有 索引关键字和指向表中数据的指针（地址）

Rowid：伪列，唯一标识一条记录，可理解为地址。

创建索引

Create \[unique\] index index\_name on table(column\[,column….\]);

Index\_name表示索引名称

Table表示表名

Column表示列名，可以建立单列索引或符合索引

Unique 表示唯一索引

场景：创建emp表的ename上建立索引

Create index idx\_emp\_ename on emp(ename);

场景：创建多列索引

Create index idx\_emp\_job\_sal on emp(job,sal);

场景：创建基于函数的索引

Creat index emp\_ename\_upper\_idx on emp(upper(ename));

当做下面的查询自动应用索引

Select \* from emp where upper(ename)=’KING’;

修改和删除索引

Alter index index\_name rebuild;

Drop index index\_name ;

# 十五、约束 #

## 1.约束 ##

非空约束：not null

唯一约束：unique

主键约束：primarykey

外键约束：Foreignkey

检查约束：check

\*\*以下实例多为省略建表时添加约束\*\*

非空约束：

修改/添加非空约束（建表后）

Alter table employees modify(列名 列类型 not null/null)

添加唯一性约束

Create tablle employees(

Eid number(6) unique,

Name varchar2(30),

Email varchar2(50),

Salary number(7,2),

Hiredate date,

Constraint employees\_email\_uk unique(email)

);

建表后增加

Alter table empleyees add constraint employees\_name\_uk unique(name);

添加主键约束（建表后添加）

Alter table employees add constraint employees\_eid\_pk primary key(eid);

添加外键约束（建表后添加）

Alter table employees add constraint employees\_deptno\_fk foreignkey(deptno) references dept(deptno);

添加检查约束（建表后添加）

Alter table employees add constraint employees\_salary\_check check(salary>2000);

Oracle基础整理

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关基础知识整理

相关 java基础整理

相关 oracle数据自动整理,oracle数据库空间自动整理

相关 Oracle基础整理

相关 oracle连接种类整理

相关基础知识整理

相关 requirejs基础整理

相关 JavaScript基础整理

相关 oracle常识整理

相关 Oracle blob 的整理