oracle数据库知识点汇总(上)

Oracle 数据库

1. Oracle数据库：

Oracle公司成立于1977年，最早开发数据库的厂商，经过近40年的发展，oracle公司目前已经是世界数据库以及相关领域领导者。

Oracle数据库系统是由Oracle公司（甲骨文）提供的一款关系数据库管理系统。

目前主流的版本是Oracle9i，Oracle10g，Oracle11g，Oracle12c

表是数据库中存储数据的基本单元。

2. 表空间：

1）表空间是用来存储数据库对象（表，视图，索引，序列...）的容器，一个表空间只能属于一个数据库。所有的数据库对象都存放到指定的表空间中 。

2）表空间是由一个或者若干个数据文件组成，这些数据文件是数据库实际存放数据的位置

3）每个数据库创建的时候，系统都会默认的创建一个SYSTEM表空间，一个数据库可以包含若干个表空间，也可以 只有一个SYSTEM表空间。

3.Oracle中常用数据类型

1）字符型：char， varchar，varchar2

a）char（n）：用来保存固定长度的字符串，n代表字符串的长度，当实际数据库不足定义的长度，右边会使用空格补全

比如: char（10）'liu',实际保存时候占据10个存储空间，浪费了7个存储空间

b）varchar（n）：可变的字符类型，n代表字符串的长度。当实际数据小于n的时候，则按照实际长度进行存储数据

比如：varchar2（10）。‘liu’实际保存时候占据3个存储空间，比较节约空间

c）varchar2（n）：Oracle自行定义的类型，兼容性特别好，建议使用varchar2取代varchar类型。

2.）数值型：number

number：可以用来存储整数和小数类型。

number（n）：只能保存整数m ，整数的最大的位数是n。

比如：number（3），表示能保存最大的整数的位数是3。

number（n，m）：可以保存有效数字的位数最多是n，小数的位数最多是m，整数的最多的位数是n-m

比如：number（7,2），7表示有效数字的位数是7,2代表小数的位数，5代表整数的位数。

3）日期时间类型：date

data：包含（世纪），年，月，日，时，分，秒

DD-MON-YY（默认日期格式）

DD:几号

MON：月份

YY：年份，后两位年份

比如：2011-12-23 23-12月-11

SQL语句是学习的重点，掌握了SQL语句就可以操作任意的数据库。

SQL语句的分类：

1）数据定义语言（DDL）：用来创建，修改，删除表结构

create（创建） drop（删除） alter（修改）

2）数据操作语言（DDL）：用来操作表中的数据，完成插入，修改，删除功能

insert（插入） delete（删除） update（修改）

3）事务控制语言（TCL）：用来管理数据库中的事务

commit rollback

4）数据查询语言（SQL）:用来查询所需要的数据

select

5.创建表：

create table person1(

id number(4) primary key,

name varchar2(12),

age number(3)

);

create table person2(

id number(4) primary key,

name varchar2(12)

);

create table person3(

id number(4) primary key,

name varchar2(12),

job varchar2(12)

);

create table person4(

id number(4) primary key,

name varchar2(12),

address varchar2(12),

age number(3)

);

案例：创建一张表customer， cid number(4)

编号 主键，cname varchar2（12）姓名，

sex char（3）性别，address varchar2（12）地址，phone varchar2（11），email varchar2（50）

create table customer(

cid number(4) primary key,

cname varchar2(12),

sex char(3),

address varchar2(12),

phone varchar2(11),

email varchar2(50)

);

案例：创建一张book表，bid number（4）图书

编号 主键，bname varchar2(50)书名，pub varchar2(50) 出版社， author varchar2（50）作者，type varchar2（50）类型，numinput number（10）进货量，numstore number（10）库存量

create table book1(

bid number(4) primary key,

bname varchar2(50),

pub varchar2(50),

author varchar2(50),

type varchar2(50),

numinput number(10),

numstore number(10)

);

2) desc 表名：查询表结构

案例：desc person1；desc person2;

6插入操作（insert）：

1）向所有列都插入列值

格式：

insert into 表名

values（列值1，列值2，列值3.....）

a）每次执行insert语句，往表中插入一条数据

b）不要反复地执行同一条insert语句，避免出现重复数据。

c）insert语句中任意一个位置出现错误，则整个数据都无法插入表中。

insert into person1

values(1,'李世民',32);

d)主键所修饰的列的列值非空唯一。往person1表中任意插入3条数据

insert into person1

values(10,'程咬金',30);

insert into person1

values(11,'李靖',28);

insert into person1

values(12,'苏烈',24);

2）查询表中所有的数据：

select * from 表名；

insert into person2 values(1,'刘备');

2)向指定列插入列值

格式：

insert into 表名（列名1，列名2，列名3...）

values（列值1，列值2，列值3....）

a）表名中的列必须跟values中列值一一对应。

b）主键所在的列，必须要插入列值

insert into person4(id) values(1);

insert into person4(id,name) values(2,'曹操');

7.修改语句（update）

1)格式：

update 表名 set 列名1=该列新值，

列名2=该列新值，列名3=该列新值

where 条件：

2）根据where条件修改表中的数据，如果没有where条件修改表中所有的数据

person2;

ID NAME

----- -----------------

1 刘备

2 关羽

3 张飞

4 诸葛亮

案例：修改person2表ID是2的信息，把姓名改为马超

update person2 set name='马超'

where id=2；

where条件

根据where条件修改表中对应的数据，如果没有where条件，修改表中所有的数据

案例：练习修改

2删除语句

1）使用delete删除

delete from 表名 where 条件

根据where条件，删除表中的数据，如果没有where条件，删除表中所有的数据

案例： 删除person表中所有的数据

案例2：删除person4表中id=1的数据

案例3：删除person4表中姓名是刘备的数据

2）使用truncate删除：直接删除表中所有的数据，而且删除速度很快，但是删除的数据不能还原（不能恢复）

格式：truncate table=表名

truncate table person4;

案例： 删除person2，person3表中的数据

3）删除表

格式：drop table 表名;

drop table person1;

案例：删除person2，person3，person4

4简单的查询语句（没有where条件）

1）查询表中所有的数据

格式：

select * from 表名（emp/dept）

2）查询表中某些列的列值

格式：

select 列名1，列名2，列名3....from表名

select empno,ename,job from emp

案例：查询emp表中员工的编号，姓名，职位，工资，入职时间

select empno,ename,job,sal,hiredate from emp

案例：查询emp表中员工的编号，姓名，上级领导（经理）的编号，入职时间

select empno,ename,mgr,hiredate from emp

案例：查询emp表中员工的姓名，工资，奖金，所属部门的编号

select ename,sal,comm,deptno from emp

案例：查询dept表中部门的名称和地址

select dname,loc from dept

案例：查询salgrade表中登记最低工资和最高工资

select losal,hisal from salgrade

5.别名：给表或者列起别名

1)给列起别名

a）使用as

格式：

select 列名1 as 别名1，列名2 as 别名2...from表名

--select empno as 员工的编号， enamel as 姓名，job as 职位 from emp

案例：查询emp表中员工的姓名，职位，上级领导的编号，入职时间以及工资，对查询的列起别名

select ename as 姓名,job as 职位,mgr as 上级领导的编号,hiredateas 入职时间,sal as 工资 from emp

案例：查询dept表中所有的信息，对查询返回的每一个列都起别名

--select deptno as 部门编号,dname as 部门名称,loc as 部门地址 from dept

b）使用空格起别名

格式：

select 列名1 别名1， 列名2 别名2.... from 表名

select deptno 部门编号，dname 部门名称 from dept

案例：查询emp表中员工的姓名，职位，工资，奖金以及所在部门的编号，对查询返回的每一个列都去起别名

--select ename 姓名,job 职位,sal 工资,comm 奖金,deptno 所在部门的编号

from emp

2）给表起别名：多表连接查询中，容易出现多张表中列名一致现象，通过给表起列名，别名指向对应表中的列

格式：

select 别名.列名1，别名.列名2....

from 表名 别名

select e.aname ,e.job,e.sal,e.hiredate

from emp e

6排序：对查询返回的结果，根据某一个列或者多个列的列值大小进行升序或者降序排列

1.根据某一个列的列值大小进行升序或者降序排列

格式：

select */列名 from 表名

order by 列名 asc/desc

asc：升序，默认值

desc：降序

案例：查询emp表中员工的编号，姓名，职位，根据与昂的编号进行降序排列

--select empno,ename,job from emp

order by empno desc

案例：查询emp表中员工的姓名，职位，工资，入职时间以及奖金，最后根据员工的工资进行升序排列

--select ename,job,sal,hiredate,comm from emp

order by sal asc

案例：查询emp表中员工的编号，姓名，入职时间以及上级领导的编号，最后根据入职时间进行升序排列

-- select empno,ename,hiredate,mgr from emp

order by hiredate asc

案例：查询emp表中员工的编号，姓名，职位以及所属部门的编号，最后根据部门的编号进行降序排列

--select empno,ename,job,deptno from emp

order by deptno desc

2）根据多个列的列值大小进行升序或者降序排列

格式：

select */列名 from 表名

order by 列名1 asc/desc，列名2 asc/desc

首先根据列名1中的列值大小进行升序或者降序排列，如果列名1中的列值大小一致，根据列名2中的列值大小进行升序或者降序排列

案例：查询emp表中员工的姓名，职位，工资，奖金。入职时间，首先根据员工的工资进行升序排列，如果工资一致根据员工的入职时间进行降序排列

-- select ename,job,sal,comm,hiredate from emp

order by sal ,hiredate desc

案例：查询emp表中员工的编号，姓名，职位，工资，入职时间以及所属部门的编号，根据员工 所属部门

的编号进行升序排列，如果部门编号一致，根据原的编号进行降序排列

-- select empno,ename,job,sal,hiredate,deptno from emp order by deptno,empno desc

3)排序永远是最后被执行的。

7.distinct：去掉重复的列值

案例：查询emp表中部门的编号

--select distinct deptno from emp

案例：查询emp表中员工的职位名称（去掉重复的职位）

--select DISTINCT job from emp

8.nvl（）函数的使用：

1）数字类型的数据可以进行正常的算术运算

案例：查询emp表中员工的姓名，职位，工资以及入职的时间，年薪（工资*12）

-- select ename,job,sal,hiredate,sal*12 年薪 from emp

知识点：

1. nvl（）函数的使用

案例：查询emp表中员工的姓名，职位，工资，以及入职时间和年薪（工资*12+奖金）

1）任何空值（null）使用“+”来连接其他类型的数据，返回的结果也是空值。

2）nvl（）函数：专门用来处理空值的问题

nvl（列名，数值）：如果改列的列值不为null，返回改列的列值；如果改列的列值为null，返回是数值。

nvl(comm,o):如果改列的列值不为null，返回comm的列值；如果改列的列值为null，返回时0

--select ename,job,sal,comm,sal*12+nvl(comm,0)年薪 from emp

2带条件的查询语句：

格式：

select */列名 from 表名

where 条件

根据where条件查询对应的数据

1）关系运算符；

>，>=（大于或者等于）,<,<=（小于或者等于）,=,

!=(不等于)，<>（不等于）

案例：查询emp表中工资高于1000的所有元的编号，姓名，职位，工资

--select empno,ename,job,sal from emp

where sal > 1000

案例：查询emp表中姓名是King的 员工的编号，姓名，职位

select empno,ename,job from emp

where ename='KING'

案例：查询工资低于等于3000的所有员工的编号，姓名，职位，工资，最后根据工资进行升序排列

-- select empno,ename,job,sal from emp

where sal<=3000

order by sal

案例：查询emp表中职位不是‘manager’的所有元的编号，姓名，职位，工资，最后根据编号进行降序排列（两种方法）

-- select empno,ename,job,sal from emp

where job <> 'MANAGER'（job！='MANAGER'）

order by empno desc

2)判断改列的列值是否为空值（null）

列名 is null：改列的列值为null

案例：查询emp表中奖金为空的员工的编号，姓名，职位以及奖金。

--select empno,ename,job,comm from emp

where comm is null

案例：查询emp表中没有上级领导的员工的编号，姓名，上级领导标号以及工资

--select empno,ename,mgr,sal from emp

where mgr is null

列名 is not null：改列的列值不为null

案例：查询emp表中含有上级领导的员工 所有的信息

--select* from emp where mgr is not null

案例：查询emp表中含有奖金的员工的编号，姓名，职位，工资，奖金，最后根据编号进行降序排列

3）like：模糊查询

%：指代任意的0个或者多个字符。

_：指代任意的一个字符

案例：查询emp表中员工的姓名的第一个字符时‘s’的员工的姓名

分析：‘s’的后面可能有也有可能没有字符 s%

--select ename from emp

where ename like 'S%'

案例：查询emp表中名字中的最后一个字符是“e”的员工的姓名

--select ename from emp

where ename like '%E'

案例：查询emp表中名字中包含‘E’的所有员工的信息

分析：1）以“E”开头 E%

2）以“E”结尾 %E

3）包含“E”%E%

%E%包含所有的可能性

--select * from emp

where ename like '%E%'

案例：查询emp表中名字的倒数第2个字符是‘k’的员工的姓名

分析：首先字符 'k'DE 前面可能有字符也有可能没有字符，在'k'的后面一定只有一个字符

--select * from emp

where ename like '%K_'

案例：查询emp表中员工的倒数第三个字符是'i'的员工的姓名

--select ename from emp

where ename like '%I_ _'

not like：

案例：查询emp表中名字不是以‘S’开头的信息

--select *from emp

where ename not like 'S%'

案例：查询emp表中名字的最后一个字母不是'N'的员工的姓名

案例：查询emp表中姓名中没有字母k所有员工的编号，姓名，工资，最后根据原的编号进行升序排列

3.逻辑运算符：and，or，not

1）and：并且，连接多个条件，都必须满足

案例：查询emp表中员工的工资在1000-3000之间的每一个员工的编号，姓名，职位，工资

--select empno,ename,job,sal from emp

where sal >=1000 and sal <=3000

案例：查询emp表中员工的职位是'MANAGER'，并且在30号部门所在的所有员工的所有信息

-- select * from emp

where job='MANAGER' and deptno='30'

案例：查询emp表中含有上级领导的员工，并且名字中不包含字母‘s’的所有员工的编号，姓名，职位，工资，最后根据员工的编号进行升序排列

-- select empno,ename,job,sal from emp

where mgr is not null and ename not like '%S%'

order by empno

2）or：或者，连接多个或者关系的条件

案例：查询emp表中员工的工资高于1000，或者员工在20号部门的员工的所有信息

select * from emp

where sal > 1000 or deptno = 20

案例：查询emp表中名字的倒数第二个字母是‘L’或者没有上级的员工的编号，姓名，职位，工资，最后根据工资进行升序排列，如果工资一致，根据员工的编号进行降序排列

--select empno,ename,job,sal from emp

where ename like '%L_' or mgr is null

order by sal, empno desc

案例：查询emp表中员工不在10号部门。或者工资低于3000的所有员工的编号，姓名，职位，工资。所在部门的编号以及入职时间，最后根据入职时间进行升序排列

-- select empno,ename,job,sal,deptno,hiredate from emp

where deptno != '10' or sal < 3000

order by hiredate

3）not：否定，对整个条件的否定（取反）

案例：查询emp表中名字不是king的信息

--select * from emp

where ename <> 'KING'

select * from emp

where not ename ='KING'

案例：查询emp表中不是30号部门的所有员工的姓名，编号

--select empno,ename, deptno from emp

where not deptno=30

4 聚合函数（分组）：

count（），sun（），avg（），max（），min（）

1）count（*/列名）：“*”统计该表中数据的总条数；“列名”统计该列中列值不为null的数据的总条数。

案例：查询emp表中员工总人数

--select count(*) from emp

案例：查询emp表中查询含有上级领导的员工的数量

-- select count(mgr) from emp

案例：查询emp表中含有奖金的员工的数量

-- select count(comm) from emp

案例：查询emp表中职位种类的个数

-- select count(distinct job)from emp

2）sum（列名）：统计该列的列值总和

--select sum (sal) from emp

案例：查询emp表中奖金的累加之和

--select sum(comm) from emp

3）avg（列名）：统计该列的平均值

--select avg(comm) from emp

案例：查询emp表中工资的平均值

- -select avg(sal) from emp

4）max（列名）：统计该列的最大值

案例：查询emp表中员工的最高工资

-- select max(sal) from emp

案例：查询emp表中员工的编号最大值

-- select max(empno) from emp

5）min（列名）：统计该列的最小值

案例：查询emp表中员工的最低工资和最低奖金

--select min(sal) 最低工资, min(comm) 最低奖金 from emp

案例：查询emp表中员工的人数，工资的总和，平均工资，最高工资，最低工资，对查询返回的列都起别名

-- select count(*) 总人数,SUM(sal) 工资总和,avg(sal) 平均工资,max(sal) 最高工资,

min(sal) 最低工资 from emp

5.分组：根据某一个列把表中的数据分成几组，然后经常对每一组的数据使用聚合函数（分组函数）。

1）格式：

select 列名/聚合函数 from 表名

where 条件

group by 列名

order by 别名/列名/聚合函数 asc/desc

2）执行的顺序：首先执行where条件，对表中所有的数据进行过滤，然后执行group by根据某一个列进行分组（分组以后的每一组数据使用聚合函数），最后执行order by，对查询返回的结果进行排序。

案例：查询emp表中每一个部门的编号，人数，最低工资，最高工资，最后根据部门的编号进行升序排序

--select deptno,count(*),min(sal),max(sal)

from emp

group by deptno

order by deptno asc

案例：查询emp表中工资在1000-3000之间的员工的信息，根据部门分组，查询每个部门的编号，人数，工资总和，平均工资，最后根据平均工资，进行降序排序

--select deptno,count(*),sum(sal),avg(sal) 平均工资

from emp

where sal>=1000 and sal<=3000

group by deptno

order by avg(sal) 平均工资desc

案例：查询emp表中含有上级领导的员工，每个职业的名称，人数，平均工资，工资总和，最后根据人数进行升序排列，如果人数一直根据工资总和进行降序排列

-- select job,count(*),avg(sal),sum(sal) from emp

where mgr is not null

group by job

order by count(*) asc,avg(sal) desc

案例：查询emp表中名字不是以‘s’开头，并且不在20号部门的员工，每个职业的名字，人数，最高工资和最低工资，最后根据最高工资进行降序排列

-- select job,count(*),max(sal),min(sal) from emp

where empno not like 'S%' and not deptno=20

group by job

order by max(sal) desc

6.Oracle中常用字符处理函数：用来处理char，varchar，varchar2类型数据。

1）length（列名/字符串）：统计当前列值/字符串中字符的个数

--select ename,length(ename) from emp

--select length('hello word') from emp

2)dual:虚拟表，Oracle中专门用来测试的表

--select length('hello werld') from dual

3)upper(列名/字符串)：把当前的列值/字符串中的小写字母改为大写字母

--select upper('hello WORLD') from dual

4)lower(列名/字符串)：把当前的列值/字符串中的大写字母改为小写字母。

--select lower('hello WORLD') from dual

案例：查询emp表中员工的姓名和职位 把姓名和职位所有的字母都改为小写字母显示

-- select ename,lower(ename), job,lower(job) from emp

5）trim(列名/字符串)：删除列名/字符串两端的空格

--select trim (' hello world ') from dual

6)串联字符串

a）||

--select ename || job from emp

--select ename ||','|| job from emp

b)concat（字符串1，字符串2）

select concat('努力','学习') from dual

7）substr（参数1，参数2，参数3）：用于截取字符串

参数1：要截取列名/字符串

参数2：如果为正数表示从1开始，根据正数的位置开始往后截取，如果为负数，表示从倒数第几个开始往后截取

参数3：要截取的字符个数

--select substr('hello',3,2) from dual

--select substr('hello',-3,2) from dual

案例：查询emp表中员工的姓名和姓名中倒数后两个字符

-- select ename,substr(ename,-2,2) from emp

length(ename)-1:倒数第2个字符位置

--select ename,substr(ename,

length(ename)-1,2)

from emp

案例：查询emp表中员工姓名的倒数后3个字符

--select ename,substr(ename,-3,3) from emp

---select ename,substr(ename,

length(ename)-2,3)

from emp

------------码字不易，给个关注呗^_^