oracle基础知识过一遍(原创)

用户、角色、权限、表空间

create tablespace test1_tablespace datafile ‘test1file.dbf’ size 10m;

create temporary tablespace temptest1_tablespace tempfile ‘temp1file.dbf’ size 10m;

创建永久表空间和临时表空间，并指定表空间名字，指定文件名称，大小（如不指定位置即放在默认位置）

select file_name from dba_data_files where tablespace_name=’test1_tablespace’;

select file_name from dba_temp_files where tablespace_name=’temptest1_tablespace’;

查看上面刚创建的永久表空间和临时表空间的文件所在位置（dba_data_files、dba_temp_files）

connect sys/oracle@orcl as sysdba  链接使用系统管理用户sys密码oracle,并以sysdba身份链接实例orcl（sysdba数据库管理员权限，sysoper数据库操作员权限，另外不写实例的话会链接默认实例）

create user yan identified by test default tablespace test1_tablespace temporary tablespace temptest1_tablespace;

创建用户yan密码test，设置默认表空间test1_tablespace、临时表空间temptest1_tablespace

select username from dba_users;  从dba_users表中查询username

grant connect to yan;   授权connect操作给用户yan

connect yan/test   链接使用用户yan密码test

alter user yan identified by test123;   修改yan用户密码为test123

alter user yan account lock;  锁定用户yan（解锁就是unlock，当然要切换到有权限的用户下去执行此类命令）

drop user yan cascade;  删除用户yan及其创建的数据（cascade参数是连同其创建的东西全部删除）

 

connect(连接角色) 只可以登录oracle，不可以创建实体，不可以创建数据库结构

resource(资源角色) 可以创建实体，不可以创建数据库结构

dba(数据库管理员角色) 拥有全部权限，最高权限，只有dba才可以创建数据库结构

grant connect to user01;

grant resource to user01;

grant dba to user01;

 

创建自定义的角色

create role 角色名；

例子：create role manager;

赋予权限、回收权限

grant 权限 to 角色或用户   （grant a to b）

例子：grant create table,create view to manager;   创建表、创建视图权限给角色manager

           grant manager to user01,user02;  角色manager赋予给用户user01、user02

revoke 权限或角色 from 角色或用户   （grant a from b）

例子：revoke manager from user01;  回收用户user01的角色manager权限

          revoke create table,create sequence from manager;  回收角色manager的创建表、创建序列权限

select * from system_privilege_map

 

create session 创建会话 create sequence 创建序列 create table 创建表 create user 创建用户 alter user 更改用户drop user 删除用户 create view 创建视图

授权对象权限、回收对象权限

对象权限有select、update、insert、delete、all......

grant 对象权限 on 对象 to 用户或角色   （如对象on user01.school）

revoke 对象权限 on 对象 from 用户或角色  （如对象on user01.work）

数据字典 dba_tablespace、user_tablespace

select tablespace_name from dba_tablespace;

select tablespace_name from user_tablespace;     

数据字典 dba_users、user_users

select default_tablespace,temporary_tablespace from dba_users where username=’system’

alter user 用户名 default tablespace或temporary tablespace 表空间名

alter user user01 default tablespace test1_tablespace temporary tablespace temptest1_tablespace

修改表空间状态

alter tablespace 表空间名 online或offline或read only或read write

修改表空间数据文件

alter tablespace 表空间名 add datafile’filename.dbf’ size 10m;  此为增加数据文件

alter tablespace 表空间名 drop datafile’filename.dbf’;  此为删除数据文件（不能删除第一个创建的数据文件，除非删除整个表空间）

删除表空间

drop tablespace 表空间名 （只删除表空间，不删除对应数据文件）

drop tablespace 表空间名 including contents （删除表空间及对应数据文件） 

 

表管理

ddl  数据定义语言

dml  数据操作语言

dcl  数据控制语言

tcl  事务控制语言

 

ddl常用语句：

create table 创建表 create index 创建索引 drop table 删除表 drop index 删除索引 truncate 删除表所有行 alter table 更改表结构，增加、修改、删除列 alter table add constraint 在已有表中增加约束

dml常用语句：

insert 添加数据 update 修改数据 delete 删除数据 select 查询数据

dcl常用语句：

grant 授予权限 revoke 撤销权限 lock 锁定

tcl常用语句：

commit 提交事务 rollback 回退事务 savepoint 设置保存点

 

创建表语法结构：

create table 表名 ( 列名 列数据类型 ,  列名 列数据类型 ... ...);

数据类型：字符类型、数值类型、日期时间类型、lob类型

字符类型有 char、varchar2

数值类型 number[(p[,s])]   p表示精度，s表示小数点位数

如 number(5,0)最多可存储5位数整数  number(5,2)最大可存储999.99的浮点数

日期类型有 date

lob类型有clob、blob

例子：create table student( sid number(8,0),name varchar2(20),sex char(2),birthday date,address varchar2(50) );

约束是oracle提供的自动保持数据完整性的一种方法，它通过限制字段中的数据、记录中数据和表之间的数据来保证数据的完整性。

约束语法结构：[constraint 约束名] <约束类型>

主键约束、唯一性约束、默认约束、非空约束、检查约束、外部键约束

主键约束 primary key 它是唯一确定表中每一条记录的标识符，其值不能为null，也不能重复。表中主键只能有一个，但可以由多个列组成。

create table student(

sid number(8,0),

name varchar2(20),

sex char(2),

birthday date,

address varchar2(50),

constraint sid_pk primary key(sid) );

修改表添加主键约束

alter table student add constraint sid_pk primary key(sid);

非空约束用于确保列不能为null，它是列级约束。

列级约束：column [constraint constraint_name] constraint_type 列级约束必须跟在列的定义后面

表级约束：column,… …

          [constraint constraint_name] constraint_type

          (column,… …),                                   表级约束不与列一起，而是单独定义的

create table student(

sid number(8,0),

name varchar2(20) not null,  列级约束

sex char(2) constraint nn_sex not null,  列级约束

birthday date,

address varchar2(50),

constraint sid_pk primary key(sid) );  表级约束

修改表添加约束格式：

alter table table_name add [constraint constraint_name] constraint_type (column);

而非空约束要使用modify语句 alter table table_name modify (column datatype not null);

删除约束方式可以禁用或者彻底删除

disable|enable constraint constraint_name;

drop constraint constraint_name;

drop primary key  为何这个可以这样写，因为表中只有一个呀，写不写约束名都知道的

alter table table_name modify column_name datatype null;

唯一约束用于指定一个或者多个列的组合值具有唯一性，以防止在列中输入重复值。

create table student(

sid number(8,0) unique, 列级约束

name varchar2(20),

sex char(2),

birthday date,

address varchar2(50),

constraint uk_sid unique(sid)); 表级约束

修改表添加唯一约束 alter table student add constraint uk_sid unique(sid);

检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据完整性。

create table student(

sid number(8,0),

name varchar2(20),

sex char(2) check(sex=’男’ or sex=’女’),  列级约束

birthday date,

address varchar2(50),

constraint sid_pk primary key(sid) );

create table student(

sid number(8,0),

name varchar2(20),

sex char(2),

birthday date,

address varchar2(50),

constraint ck_sex check(sex=’男’ or sex=’女’));  表级约束

修改表添加检查约束 alter table student add constraint ck_sex check(sex=’男’ or sex=’女’);

外键约束是用于建立和加强两个表数据之间的链接的一列或多列。它是唯一涉及两个表关系的约束

列级约束：

create table 从表

(column_name datatype references

主表(column_name) [on delete cascade],… …);

表级约束：

constraint constraint_name foreign key (column_name)

references 主表(column_name) [on delete cascade]

设置外键约束时主表的字段必须是主键列（或唯一列），主从表中相应字段必须是同一数据类型，从表中外键字段的值必须来自主表中的相应字段的值（或为null）

例子：

主表 create table department ( depid varchar2(10) primary key, depname varchar2(30) );

从表 create table student (sid number(8,0), name varchar2(20), sex char(2), birthday date, address varchar2(50),

            depid varchar2(10) references department(depid) );       列级约束，其中从表列名称可以和主表名称不一样

主表 create table department ( depid varchar2(10) primary key, depname varchar2(30) );

从表 create table student (sid number(8,0), name varchar2(20), sex char(2), birthday date, address varchar2(50),

             depid varchar2(10), constraint fk_depid foreign key(depid)  

             references department(depid) on delete cascade );       表级约束，同样从表定义的列可以和主表名称不一样

 

添加列语句结构：

alter table 表名 add 新增列名 数据类型

alter table student add tel varchar2(11)

修改列语句结构：

alter table 表名 modify 列名 新数据类型

alter table student modify tel number(11,0)

修改列名语句结构：

alter table  表名 rename column  列名 to 新列名

alter table student rename colnmn sex to gender

删除表语句

truncate table 表名 （用于删除表全部数据，但表结构还在，这种删除方式也叫截断表）

drop table 表名  （删除表结构）

 

添加信息语句结构：

insert into 表名[(列1,列2,… …)] values(值1,值2,… …)

insert into student (sid,name,sex) values(18733,’周小仙’,’男’)

查询信息语句结构：

select * column_name from 表名

select * from student      select sid,name,sex from student

修改信息语句结构：

update table set column = value [where condition]

update student set tel = ‘15899999999’ where sid = 18733

删除信息语句结构：

delete from table [where condition]

delete from student where sid = 18733

 

事务可以看作是由数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证数据满足一致性的要求。

事务组成可以是一条或者多条dml,或一条ddl,或一条dcl

dml语句需要使用commit提交事务或使用rollback回滚事务

ddl和dcl是自动提交事务的

使用事务的原因主要是为保证数据的安全有效，这里需要提到“表锁” 

 

基本查询

语法格式：

select * {[distinct] column_name | expression [alias],… …} from table_name [where condition]

其中expression指表达式，alias指列的别名，condition指查询条件

 

查询有特定列查询、全部列查询、排除重复行、日期列的查询、条件查询（过滤数据，涉及运算符使用、范围查询、模糊查询、空值判断、逻辑运算符使用等）查询结果排序

select distinct deptno,job from emp   排除重复数据

alter session set nls_language=’simplified chinese’  以简体中文显示日期

alter session set nls_date_format=’yyyy/mm/dd’  以特定格式显示日期

运算符优先级 + - * / 从左到右执行，乘除高于加减，括号内优先

null表示未知值，和其运算结果也为空（即null）

另外列可以给予默认值，使用default

列的别名用于改变列的显示标题

列别名可以跟在列名的后面，也可以在二者之间加as关键字

select  empno  “雇员编号”,  ename  “雇员名”,  sal*12  “全年工资” from emp

select  empno as “雇员编号”,  ename as “雇员名”,  sal*12 as “全年工资” from emp

连接符 ||

它把列与列，列与字符 连接在一起

select ename ||’的岗位是：’|| job as 员工的职位信息描述 from emp

where子句过滤条件

select * from emp where deptno=20

select * from emp where job=’manager’

select * from emp where hiredate=’22-3月-86’

范围查询between…and和in

模糊查询like               (通配符%表示0个或多个字符，通配符_表示单个字符)

判断空值is null

模糊查询中如果字符值本身就包含_和%字符，就需要用escape选项和转义符实现

select * from emp where ename like ‘g\_%’ escape ‘\’  其实模糊查询的是g_%

另外判断是否为空，不可以写成=null，<>null 这都是错误写法，应该是is null或is not null

not优先级最高，and其次，or最低  

单列排序、多列排序、列别名排序

select *|column_name from table_name [where condition] [order by column_name [asc|desc] ]

当select语句包含多个子句(where,group by,having,order by)时，order by必须是最后一条语句

select ename,sal from emp order by sal desc  降序排序

select empno,ename,sal*12 年收入 from emp order by 年收入 desc  使用列别名排序

select empno,ename,deptno,sal from emp order by deptno asc, sal desc  多列排序

 

单行函数

顾名思义就是输出一行的函数，当然多行函数就是输出一行或多行的函数

单行函数有字符函数、数值函数、日期函数、转换函数、通用函数

字符函数：1.大小写控制函数 有upper、lower、initcap

                2.字符控制函数 有concat、substr、length、instr、lpad、rpad、trim、replace

upper(char) 用于将字符串转换为大写格式

lower(char) 用于将字符串转换为小写格式

initcap(char) 用于将字符串中的每个单词的首字母转换为大写格式，其他字符小写

concat(str1,str2) 用于字符串的连接

例子concat(‘hello’,’oracle’)   结果：hellooracle

substr(char,m[n]) 用来截取字符串，char指定源字符串，m用于指定从哪个位置开始截取，n用于指定截取字符串的长度。如果m为0，则从首字符开始，如果m为负数，则从尾部开始。

例子 substr(‘hello’,1,3)  结果：hel

例子 substr(‘hello’,0,3)  结果：hel

例子 substr(‘hello’,-1,1)  结果：o

length(char) 用于返回字符串的长度，字符串中的后缀空格也记作字符串的长度。

例子 length(‘oracle ‘)  结果：7

instr(char1,char2[,n[m]]) 用于指定字符串在源字符串中的位置，char1源字符串，char2指定字符串，n用于指定起始搜索位置，m用于指定字符串第m次出现的次数（n,m默认都是1）

例子 instr(‘hello oracle’,’oracle’)  结果：7

例子 instr(‘hello oracle hello oracle’,’oracle’,2,2)  结果：20

lpad(char1,n,char2) 用于在字符串的左端填充字符，char1源字符串，char2被填充字符，n用于指定填充后的char1的总长度。

例子 lpad(‘hello’,10,’#’)  结果：#####hello

rpad(char1,n,char2) 用于在字符串的右端填充字符，char1源字符串，char2被填充字符，n用于指定填充后的char1的总长度。

例子 rpad(‘hello’,10,’@’)  结果：hello@@@@@

replace(char,search_string[,replacement_string]) 用于替换字符串的子串内容，char源字符串，search_string被替换子串，replacement_string替换子串

例子 replace(‘hello oracle’,’oracle’,’world’)  结果：hello world

 

数值函数有round、trunc、mod

round(n,[m]) 用于四舍五入结果，n可以是任意数字，m必须是整数

例子 round(25.328,2) 结果：25.33     round(25.328,-1) 结果：30

trunc(n,[m]) 用于截取数字，n可以是任意数字，m必须是整数

例子 trunc(25.328,2) 结果：25.32      trunc(25.328,-1) 结果：20

mod(m,n) 用于求余数，m除于n得到的余数，如果n为0,则返回结果为m

例子 mod(25,6) 结果：1     mod(25,0) 结果：25

日期函数常用函数有sysdate、months_between、add_months、next_day、last_day、round、trunc

sysdate 用于返回当前系统日期

例子 select sysdate from dual

months_between(d1,d2) 用于返回日期d1和d2之间相差的月数。d1大于d2结果为正数，否则为负数

例子 select months_between(sysdate,hiredate) from emp

add_months(d,n) 用于返回特定日期时间之后或之前的月份所对应的日期时间。（求出若干月之后的日期）d用于指定日期时间，n可以是任意整数。

例子 select ename,add_months(hiredate,30*12) from emp

next_day(d,char) 用于返回特定日期之后的第一个工作日所对应的日期。d用于指定日期时间值，char用于指定工作日。注意：当使用该函数时，工作日必须与日期语言匹配，假如日期语言为american,那么周一对应于monday;假如日期语言为简体中文，那么周一对应于”星期一”。

例子 select sysdate,next_day(sysdate,’星期一’) from dual

round(d,[fmt]) 用于返回日期时间的四舍五入的结果。d用于指定日期的时间值，fmt用于指定四舍五入的方式，如果设置fmt为year,则7月1日为分界线，如果设置fmt为month,则16日为分界线。

例子 如果系统时间是’20-7月-17’ round(sysdate,’year’) 结果：01-1月-18 round(sysdate,’month’) 结果：01-8月-17

trunc(d,[fmt]) 用于截断日期时间数据。d用于指定日期的时间值，fmt用于指定截断日期时间数据的方法，如果设置fmt为year,则结果为本年度的1月1日，如果设置fmt为month,则结果为本月1日。

例子 trunk(sysdate,’year’)    trunk(sysdate,’month’)

转换函数 to_date、to_char、to_number

to_char(d,[fmt[‘nlsparams’]]) 将日期类型转换为字符类型。d用于指定日期值，fmt用于指定日期格式模型，‘nlsparams’用于指定日期显示语言

例子 select to_char(hiredate,’dd-mon-rr’, ‘nls_date_language=american’) from emp

to_char(n,[fmt]) 将数值类型转换为字符类型。n用于指定数值，fmt用于指定数字格式模型。

格式模型，常见的元素如下：

9：显示数字，并且会忽略前导0

0：显示数字，如果位数不足，则用0补齐

. ：在指定位置显示小数点

, ：在指定位置显示逗号

$ ：在数字前加美元符号

l ：在数字前加本地货币符号

例子 select sal,to_char(sal,’l999,999.99’) from emp  select sal,to_char(sal,’$999.999.99’) from emp

to_date(char,[fmt[‘nlsparams’]]) 将字符串转换成日期类型的数据。char用于匹配日期数据的字符串，fmt用于指定日期格式模型，’nlsparams’用于指定日期语言。

例子 select ename,hiredate from emp where hiredate>to_date(‘1986-3-22’,’yyyy-mm-dd’)

to_number(n,[fmt]) 将包含数字的字符串转换成数值类型。n是包含数字的字符串，fmt用于指定数字格式模型

例子 select ename,sal from emp where sal>to_number(‘￥2000’,’l99999’)

通用函数有nvl、nvl2、nullif、coalesce

 

nvl(expr1,expr2) 该函数用于处理null，如果expr1是null,则返回expr2,如果expr1不是null，则返回expr1

例子 select ename,sal,comm,sal+nvl(comm,0) from emp

nvl2(expr1,expr2,expr3) 该函数用于处理null，如果expr1不是null,则返回expr2,如果expr1是null,则返回expr3

例子 select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp

nullif(expr1,expr2) 该函数用于比较表达式expr1和expr2,如果二者相等，则返回null，否则返回expr1

例子 select empno,ename,hiredate,nullif(hiredate,trunk(sysdate,’month’)) from emp

coalesce(expr1,[expr2]… ) 用于返回表达式列表中的第一个not null表达式的结果

例子 select ename,sal,comm,coalesce(sal+comm,sal) from emp

 

case表达式语法格式

case expr

     when comparison_expr1 then return_expr1

     [when comparison_expr2 then return_expr2

     … …

     else else_expr]

end

例子 select empno,ename,

          case job

          when ‘clerk’ then ‘办事员’

          when ‘salesman’ then ‘销售’

          else ‘总裁’

          end

     from emp

decode(column | expression,search1,result1,[ search2,result2,  …] [default])

例子 select empno,ename,job,decode(job, ‘clerk’, ‘办事员’, ‘salesman’ , ‘销售’, ‘总裁’) from emp

 

单行函数看可以嵌套。嵌套函数的执行顺序是由内到外。

例子 to_char(next_day(add_months(hiredate,3),’星期一’),’yyyy-mm-dd’)

       to_char(round(sal/30,2),’l999,999’)

 

分组函数

分组函数有avg、sum、min、max、count、wm_concat

分组函数就是多行函数，作用于一组数据，并一组数据返回一个值。

select avg(sal) from emp

select min(sal) from emp

select max(sal) from emp

select count(*) from emp

distinct关键字去除重复记录 select count(distinct deptno) from emp

wm_coucat行转列(让查询结果行转列)该函数可以把列值以 逗号 分隔起来，并显示成一行。

select wm_concat(ename) from emp

使用分组函数要注意空值的问题，分组函数会自动忽略空值，如果要空值的数据，可以用nvl函数

 

数据分组 group by

在select列表中的所有未包含在组函数中的列都应包含在group by子句中

正确写法：select deptno,avg(sal) from emp group by deptno

错误写法：select deptno,avg(sal) from emp

错误写法：select deptno,job,avg(sal) from emp group by deptno

正确写法：select deptno,job,avg(sal) from emp group by deptno,job

包含在group by子句中的列不必包含在select列表中

select avg(sal) from emp group by deptno

 

having子句 用于对分组后的结果进行过滤

select deptno,avg(sal) from emp group by deptno having avg(sal)>2500  这里的having不能换成where

不能在where子句中使用组函数，可以在having子句中使用组函数。

分组函数嵌套时候需要配合group by子句一起使用

例子 select * max(avg(sal)) from emp group by deptno  如果没有group by子句会报错的

 

多表查询和笛卡尔集

多表查询顾名思义就是从多个表中获取数据

笛卡尔集是集合的一种，它是数学上的概念。假设a和b都是集合，a和b的笛卡尔积用a*b来表示，那么a*b所形成的集合叫笛卡尔集。

oracle多表查询

等值连接，它是指使用等值比较符 = 指定连接条件的查询

例子 select empno,ename,job,dname from emp,dept where emp.deptno=dept.deptno

例子 用表别名方式 select e.empno,e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno

在连接查询中，当有多个连接条件时，使用and指定其他条件

例子 select e.empno,e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.deptno=10

不等值连接，它是指使用除等值比较符之外的其他比较操作符执行连接查询

例子 select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between losal and hisal

外连接是标准连接的拓展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的部分记录

外连接是用+操作符来完成的，分右外连接、左外连接

右外连接：select table1.column, table2.column from table1,table2 where table1.column(+)=table2.column

返回右边表table2的全部记录，包括不满足条件的记录。返回左边table1满足条件的记录。

左外连接：select table1.column, table2.column from table1,table2 where table1.column=table2.column(+)

返回左边表table1的全部记录，包括不满足条件的记录。返回右边table2满足条件的记录。

例子 select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname

自连接是同一张的互相连接，实质是将同一张表看成是多张表

sql:1999标准

select table1.column_name, table2.column_name from table1

[cross join table2]|[natural join table2]|[join table2 using (column_name)]|

[join table2 on (table1.column_name= table2.column_name)]|

[left | right | full outer join table2 on (table1.column_name= table2.column_name)]

交叉连接

例子 select e.detpno,e.ename,d.deptno,d.dname from emp e cross join dept d

自然连接是一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件

例子 select table1.column_name,table2.column_name from table1 natural join table2

内连接，返回两个表中想匹配的数据（只返回满足条件的数据）

等值连接、非等值连接、自然连接都属于内连接

用using子句建立相等连接

例子 select e.ename,e.sal,d.dname from dept d join emp e using(deptno)  两个表都有的列deptno

用on子句建立相等连接

例子 select e.ename,e.sal,d.dname from dept d join emp e on e.deptno=d.deptno

左外连接、右外连接、完全外连接

例子 select e.ename,e.sal,d.dname from dept d left join emp e on e.deptno=d.deptno

例子 select e.ename,e.sal,d.dname from dept d right join emp e on e.deptno=d.deptno

例子 select e.ename,e.sal,d.dname from dept d full join emp e on e.deptno=d.deptno

 

set运算符,并不是set这个符号，它是集合操作符，专门用于合并多条select语句的结果。

有union/union all 并集  intersect 交集  minus 差集

例子 select deptno,empno,ename from emp1

        union

        select deptno,empno,ename from emp2

union all与union不同的是它不会取消重复行，并且不会对结果集数据进行排序

例子 select deptno,empno,ename from emp1

        union all

        select deptno,empno,ename from emp2

例子 s