oracle数据库笔记

墨蓝 2022-05-12 01:26 964阅读 0赞

# oracle数据库笔记 #

### 文章目录 ###

*  oracle数据库笔记
 *   *  数据库四种语言:
     *   *  1.DDL （Data Definition Language ）数据库定义语言 statements are used to define the database structure or schema.
         *   *  创建表:
             *  删除表:
             *  修改表:
             *  查看表结构 (需要在命令窗口执行)
             *  清空表 ddl: truncate
             *  利用现有表创建新表
             *  创建新表 同时 复制数据
             *  创建新表 但是不复制数据
             *  复制表数据插入 要求：表结构一致
         *  2.DML （Data Manipulation Language）数据操纵语言 statements are used for managing data within schema objects.
         *   *  插入一条记录:
             *  修改一条记录:
             *  删除一条记录:
             *  查询一条记录:
             *  查询并修改:(过后必须commit)
             *  表达式:
             *  取别名:
             *  关系运算符 > < = >= <= <> !=
             *  逻辑运算符 and or not
             *  集合操作符:
             *  交集intersect:
             *  集合差运算minus:
             *  排序查询
             *  聚合函数(分组函数)(统计函数)max(列名)最大,min(列名)最小,count(列名)数量,sum(列名)总和,avg(列名)平均数
             *  分组查询:在oracle中 分组查询中 只有在group by中列 才能出现在select的列中
             *  distinct 去除重复
             *  模糊查询 like not like
             *  区间 between..and..(between and 必须从小到大)
             *  外连接:(left join --right join --full join)
             *  内连接:(inner join)
             *  日期函数
             *  字符函数
             *  数字函数
             *  nvl 判断是否为null 如果为null 则替换
             *  如果两个表达式相同 则为空 否则为第一个值
             *  四大排名函数row\_number ,dense\_rank, rank ,ntile
         *  3.DCL（Data Control Language）数据库控制语言 授权，角色控制等
         *   *  创建工作空间
             *  创建用户, 指定表空间
             *  用户权限
             *  切换用户
             *  创建用户
             *  创建表格
             *  表格添加内容
             *  查询表格
             *  修改用户
             *  解锁用户、锁定用户
             *  删除用户
             *  查看用户和默认表空间关系
             *  查看所有的角色
             *  看当前数据库名
             *  查看当前用户权限
             *  查看所有用户
             *  创建角色
             *  授予角色权限
             *  从角色收回权限
             *  删除角色
         *  4.TCL（Transaction Control Language）事务控制语言
         *   *  事务:
             *  rollback---回滚
             *  commit---提交
             *  savepoint---设置回滚点
             *  一致性:
             *  原子性:
             *  隔离性:
             *  持久性:
     *  oracle的数据类型:
     *  oracle的两个伪列:(rowid rownum)
     *  同义词
     *   *  创建私有同义词
         *  使用同义词
         *  创建公有同义词 在任何其他模式下 都可以使用的同义词
         *  创建或替换
         *  删除同义词
         *  删除公有同义词需要授权
     *  序列
     *   *  创建序列 默认 从1开始 步长1
         *  使用序列 查看并增长（.nextval）
         *  序列其他选项
         *  修改序列
         *  删除序列
     *  锁和表分区
     *   *  加锁 : for updet
         *  创建表,并进行分区
         *  添加分区:在最后一个分区之后添加新分区
         *  删除分区:删除一个指定的分区，分区的数据也随之删除
         *  截断分区:删除指定分区中的所有记录
         *  合并分区:将范围分区或复合分区的两个相邻分区连接起来
         *  拆分分区:将一个大分区中的记录拆分到两个分区中
     *  连接数据库
     *  select from where group by having order by limit 顺序 不能颠倒
     *  索引：提高查询效率
     *  查询优化：分区，索引，分表，（视图），优化SQL
     *  锁机制：共享锁，排他锁
     *  乐观锁：
     *  防重复提交：令牌机制 token

## 数据库四种语言: ##

ddl---数据库定义语言
          dml---数据库操作语言
          dcl---数据库控制语言
          tcl---事务控制语言

### 1.DDL （Data Definition Language ）数据库定义语言 statements are used to define the database structure or schema. ###

DDL是SQL语言的四大功能之一。
    用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
    DDL不需要commit. 
    
    CREATE 创建表
    ALTER 修改表
    DROP 删除表
    TRUNCATE 清空表
    COMMENT 提交
    RENAME 重命名

#### 创建表: ####

create table 表名(
                              列名 类型 约束primary key,---唯一主键
                              列名 类型 约束 not null ,---不能为空
                              列名 类型 约束 default' ' , ---默认约束
                              类名 类型 约束 unique , ---唯一约束
                        )

#### 删除表: ####

drop table 表名;

#### 修改表: ####

*  增加列:
    
        alter table 表名 add 列名 类型 约束;
 *  删除列:
    
        alter table 表名 drop column 列名;
 *  修改列类型:
    
        alter table 表名 modify 列名 类型;
 *  重命名列名:
    
        alter table 表名 rename column 列名1 to 列名2;
 *  给列添加唯一约束:约束命名（pk\_主键,uk\_唯一,fk\_外键,ck\_检查）
    
        alter table 表名 add constraint 列名 unique(列名);
 *  为教师表、部门表创建主键约束；
    
        alter table teacher add constraint pk_teacher primary key(tno);
         	alter table dept add constraint pk_dept primary key(deptno);
 *  为教师姓名列添加唯一约束；
    
        alter table teacher add constraint uk_teacher unique(tname);
 *  为教师性别列添加检查约束，其值只能为男和女；
    
        alter table teacher add constraint ck_teacher check(gender in ('男','女'));
 *  教师表添加外键约束，要求所有有雇员的部门一定在部门表中存在
    
        alter table teacher add constraint fk_teacher foreign key (deptno) references dept(deptno);
         		     (表名)                  	(约束名)        (字表中的字段)       (父表名)(父表主键)

#### 查看表结构 (需要在命令窗口执行) ####

desc usertbl;

#### 清空表 ddl: truncate ####

truncate table usertbl;

#### 利用现有表创建新表 ####

select * from student;

#### 创建新表 同时 复制数据 ####

create table student_backup
                    as
                    select * from student;

#### 创建新表 但是不复制数据 ####

create table student_backup2
                    as
                    select * from student where 1=2;
                    select * from student_backup2;

#### 复制表数据插入 要求：表结构一致 ####

insert into student_backup2
                    select * from student;  
                    drop table student_backup2;
          select * from student_backup2 for update;

### 2.DML （Data Manipulation Language）数据操纵语言 statements are used for managing data within schema objects. ###

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。 
    DML分成交互型DML和嵌入型DML两类。
    依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
    需要commit. 
    SELECT 查询数据
    INSERT 插入
    UPDATE 修改数据
    DELETE 删除数据
    MERGE 
    CALL 
    EXPLAIN PLAN 
    LOCK TABLE

#### 插入一条记录: ####

insert into  表名 (列名1 , 列名2 , 列名3 )values(值1 , 值2 , 值3 );

#### 修改一条记录: ####

update 表名 set 列名="修改后的值" where 条件;

#### 删除一条记录: ####

delete from 表名 where 条件

#### 查询一条记录: ####

select from 表名 where 条件

#### 查询并修改:(过后必须commit) ####

select * from emp for update;

#### 表达式: ####

select 1+1 from dual
                select 1-1 from dual
                select 3*2 from dual
                select 4/2 from dual
                select mod(6,4) ss from dual

#### 取别名: ####

select 1+1 as 计算结果 from dual;
                select 1+1  计算结果 from dual;
                select uname 姓名,upass 密码 from dual;
                select '姓名为：'||uname ||'   密码为：'|| upass as 基本信息 from usertbl;
                用户信息 from usertbl; 
                select userid+upass as 结果 from usertbl;

#### 关系运算符 > < = >= <= <> != ####

select * from student where stuid>=2;
                select * from student where stuid=2;
                select * from student where stuid<>2;
                select * from student where stuid!=2;

#### 逻辑运算符 and or not ####

select * from student where stuid>1 and stuid<3;
                select * from student where stuid>1 or stuid<3;
                select * from student where not stuid>1
    
                select * from usertbl;

#### 集合操作符: ####

select * from student where stuid=1 or stuid=2
                union
                select * from student where stuid=2 or stuid=3;
    
                select * from student where stuid=1 or stuid=2
                union all
                select * from student where stuid=2 or stuid=3;

#### 交集intersect: ####

select * from student where stuid=1 or stuid=2
                intersect
                select * from student where stuid=2 or stuid=3;

#### 集合差运算minus: ####

select * from student where stuid=1 or stuid=2 
                minus
                select * from student where stuid=2 or stuid=3;

#### 排序查询 ####

select * from student order by sage asc;--升序(默认)  desc  降序
                select * from student order by sage desc;
                select * from student order by scid asc,sage desc ;--多列排序
                select * from student where stuid >1 and sage>18 order by sage desc;

#### 聚合函数(分组函数)(统计函数)max(列名)最大,min(列名)最小,count(列名)数量,sum(列名)总和,avg(列名)平均数 ####

select max(sage) from student;
                select max(sage),min(sage),avg(sage),sum(sage),count(*) from student;
                select count(*),count(sname) from student;--count(*)统计数量包含所有记录的  count(列名)统计此列不为空的记录数

#### 分组查询:在oracle中 分组查询中 只有在group by中列 才能出现在select的列中 ####

select scid,count(*),max(sage)
                from student
                group by scid;

*  分组查询 显示school中scname
    
        select sc.scid,scname,avg(sage)
                  from school sc,student st
                  where sc.scid=st.scid
                  group by sc.scid,sc.scname
 *  分组查询 条件查询
    
        select sc.scid,scname,avg(sage)
                  from school sc,student st
                  where sc.scid=st.scid
                  group by sc.scid,sc.scname
                  having avg(sage)>26
 *  按分组查询出来再再组里进行按工资排序:
    
        select e.* , rank() over (partition by e.deptno order by sal desc)from emp e;

#### distinct 去除重复 ####

select distinct scid from student;
                select distinct sage,scid from student;

#### 模糊查询 like not like ####

select * from emp where ename like '%a%';
                  select * from emp where ename like 'a%';

#### 区间 between…and…(between and 必须从小到大) ####

select * from emp where sal>=1000 and sal<=3000;
                  select * from emp where sal between 1000 and 3000;

#### 外连接:(left join --right join --full join) ####

*  左外:(左外就显示全部左边 )
    
        select d.* , e.* 
                        from dept d left join emp e 
                        on d.deptno = e.deptno
 *  右外:(右外就全部显示右边 )
    
        select d.* , e.* 
          			from dept d right join emp e 
          			on d.deptno = e.deptno
 *  全外连接
    
        select dname,ename,sal 
                    from emp e full join dept  d
                    on e.deptno=d.deptno;

#### 内连接:(inner join) ####

select dname,ename,sal
                  from emp e,dept d
                  where e.deptno=d.deptno;
    
                  select dname,ename,sal
                  from emp e inner join dept d
                  on d.deptno=e.deptno;

#### 日期函数 ####

select sysdate from dual;(查看当前时间)
    
                  select sysdate + 10 from dual;(加减天数)
                  select sysdate - 10 from dual;
                  select add_months(sysdate,10) from dual;(加月数)
                  select add_months(sysdate,-10) from dual;(减月数)
    
    
                  select sysdate,last_date(sysdate) from dual;(这个月的最后一天)
    			  select sysdate,next_day(sysdate) from dual;(下一天)
    
                  select del_date, round(del_date,'year') 
                  from order_master 
                  where vencode='v001';
    
                  select round(hiredate,'year') from emp;(round为四舍五入)(hiredate是表中字段名)
                  select round(hiredate,'month') from emp;
                  select round(to_date('2017-12-23','yyyy-mm-dd'),'month') from dual;
    
                  select round(date'2005-5-10', 'month'),
                         round(date'2005-6-16', 'month')
                  from dual;
    
                  select trunc(sysdate, 'year') from (今年的第一天)
    
                  select trunc(sysdate,'day') from dual;(这月第一天)
                  
                  select to_char(sysdate,'dd') from dual;(取出当前天)
    
                  select to_date('2005-12-06' , 'yyyy-mm-dd') from dual;
    
                  select trunc(sysdate) from dual;(今天)

*  只取出 年份—extract
    
        select extract(year from sysdate)from dual;(今年年份)
                    select extract(month from sysdate)from dual;(当前月)

#### 字符函数 ####

select initcap('hello world') from dual;(首字母大写)
    
                  select lower('FUN') from dual;(转小写)
    
                  select upper('sun') from dual;(转大写)
    
                  select ltrim( 'xyzadams','xyz') from dual;(去左空格/指定字符)
    
    
                  select rtrim('xyzadams','ams') from dual;(去右空格/指定字符)
    
    	 		  selete trim(leading 9 from 99998769789999) from dual;(去掉左边所有的9,中间的9保留)
    
                  selete trim (trailing 9 from 99998769789999) from dual;(去掉右边所有的9,中间的9保留)
    
                  selete trim (9 from 99998796789999) from dual;(去掉两边所有的9,中间的9保留)
    
    
    			  select length('ams') from dual;(求字符长度)
    
                  select translate('jack','abcd' ,'1234') from dual;(指定字符替换)
    
                  select translate('jack','jack' ,'1234') from dual;(指定字符替换)
    
                  select replace('jack and jue' ,'j','bl') from dual;(指定字符替换)
    
    			  select ascii ('w') from dual;(求ascii码)
    
                  select instr ('worldwide','w') from dual;(此字母第一次出现的位置)
    
                  select substr('abcdefg',3,2) from dual;(取出指定位置的字符)
    
                  select concat ('hello',' world') from dual;(字符串拼接)
    
                  select chr(65) from dual;(用ascii码找到字符)
    
                  select lpad('xyz',8,'=') from dual;(在xyz 的左边拼接=使之成为8位 , 当所取个数小于字符长度则截取字符)
    
                  select rpad('xyz',8,'=') from dual;(在xyz 的右边拼接=使之成为8位 , 当所取个数小于字符长度则截取字符)
    
                  select trim(leading 9 from 99998769789999) from dual;(去掉左边所有的9,中间的9保留)
    
                  select trim(trailing 9 from 99998769789999) from dual;(去掉右边所有的9,中间的9保留)
    
                  select trim(9 from 99998796789999) from dual;(去掉两边所有的9,中间的9保留)
    
                  select decode('c','p','准备处理','c','已完成') from dual;(三元运算符)
    
    			  select decode('c','p','相等','不相等') from dual;(相等则输出"相等",不相等则输出"不相等")
    
                  select orderno, decode(ostatus,'p','准备处理','c','已完成')from order_master;(结合表使用三元运算符)

#### 数字函数 ####

select abs(-15) from dual;(绝对值)      
                  select sin(1.571) from dual;(求sin值)
                  select cos(0) from dual;(求cos值)
                  select sign(-32) from dual;(求符号位, 正数为1/负数为-1/0为0)
    			  select ceil(44.078) from dual;(求整)(有小数则加一)
                  select floor(100.2) from dual;(求整)(有小数则直接舍弃)
                  select power(4,2) from dual;(求指数,4的2次方)
                  select mod(10,3) from dual;(取模)
                  select round(100.256,2) from dual;(保留两位小数,四舍五入)
                  select trunc(100.256,2) from dual;(保留两位小数,不四舍五入)
                  select sqrt(4) from dual;(开方)

*  yyyymmddhhmm —转化指定格式
    
        select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
                    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
        
                    ---******************************
                    select to_char(1234.235,'$99,999.99')  from dual;
                    select to_char(1234,'c99,999.99')  from dual;
                    select to_char(1234,'99,999.99')  from dual;
                    select to_char(1234,'99,999')  from dual;
                    select to_char(1234,'9,999.99')  from dual;
        
                    select sqrt('100') from dual;(开方)
                    select sqrt(to_number('100')) from dual;(转化为数字再开方)
        
              select  * from emp;

#### nvl 判断是否为null 如果为null 则替换 ####

select ename,nvl(comm,0) from emp;
                  select ename,nvl2(comm,0,999) from emp;

#### 如果两个表达式相同 则为空 否则为第一个值 ####

select nullif(24,24) from dual;

#### 四大排名函数row\_number ,dense\_rank, rank ,ntile ####

select ename,sal from emp order by sal desc;
                  select ename,sal,row_number () over(order by sal desc) 排名 from emp;(直接顺序排列)
                  select ename,sal,dense_rank() over(order by sal desc) 排名 from emp;(并列第五,下个第六)
                  select ename,sal,rank() over(order by sal desc) 排名 from emp;(并列第五,下个第七)
    			  select ename,sal,ntile(4) over(order by sal desc) 排名 from emp;(平均分段)

### 3.DCL（Data Control Language）数据库控制语言 授权，角色控制等 ###

GRANT 授权 
    REVOKE 取消授权

#### 创建工作空间 ####

create tablespace myoralspace datafile 'd:/myoralspace.dbf'size 100m  autoextend on;
                  （工作空间名）          （工作空间地址）   （文件大小） (是否自增长)

#### 创建用户, 指定表空间 ####

create user wqy identified by java default tablespace myoralspace;
           （用户）           （密码）                （工作空间名）

#### 用户权限 ####

grant create session to wqy;
    		             （用户名）
    grant all privileges to wqy;---给用户所有权限
    grant resource to wqy;---给用户添加等修改权限

#### 切换用户 ####

conn wqy/java@oracle;
      （用户名）（用户类型）（数据库）

#### 创建用户 ####

create user wangwu identified by java default tablespace myspeace;
            （用户名）           （密码）    (指定表空间)

#### 创建表格 ####

create table userifo(userid int,uname varchar2(20),upass varchar2(20))(tablespace myspace
     //指定创建表所处的空间);

#### 表格添加内容 ####

insert into userifo(userid,uname,upass)values(1,'wang','123');

#### 查询表格 ####

select * from userifo;

#### 修改用户 ####

alter user zhangsan identified by 123456;

#### 解锁用户、锁定用户 ####

alter user scott account unclock;解锁用户
     alter user scott account lock锁定用户

#### 删除用户 ####

drop user zhangsan;
           (用户名)

#### 查看用户和默认表空间关系 ####

select username,default_tablespace from dba_users;

#### 查看所有的角色 ####

select * from dba_roles;

#### 看当前数据库名 ####

select name from v$database;

#### 查看当前用户权限 ####

select * from session_privs;

#### 查看所有用户 ####

select * from all_users;

#### 创建角色 ####

create role myrole;

#### 授予角色权限 ####

grant create table to wang;

#### 从角色收回权限 ####

revoke create table from myrole;

#### 删除角色 ####

drop role myrole;

### 4.TCL（Transaction Control Language）事务控制语言 ###

SAVEPOINT 设置保存点 
    ROLLBACK  回滚
    SET TRANSACTION

#### 事务: ####

数据操作的集合
            特点:---原子性-一致性-隔离性-持久性

#### rollback—回滚 ####

update emp set sal=sal+800 where empno in(2222,7369);
                  rollback;

#### commit—提交 ####

update emp set sal=sal-500 where empno in(2222,7369);
                  commit;

#### savepoint—设置回滚点 ####

update emp set sal=sal+800 where empno in(2222,7369);
                  rollback to savepoint aaa;
                  commit;

#### 一致性: ####

数据保持一致。

#### 原子性: ####

一个事务包含多个操作，这些操作要么全部执行，要么全都不执行。
    	实现事务的原子性，要支持回滚操作，在某个操作失败后，回滚到事务执行之前的状态。

#### 隔离性: ####

并发事务之间互相影响的程度，比如一个事务会不会读取到另一个未提交的事务修改的数据。

#### 持久性: ####

事务提交后，对系统的影响是永久的。

## oracle的数据类型: ##

*  char—不可变长字符(无论输入多少字符都占固定长度)查询效率高
 *  varchar2—可变长字符(输入几个字符就占几个空间)省空间 , 一般电话号码省份证号都是用这个
 *  long—2gb的字符存储
 *  number\[(p,s)\]—数值型(p为精度,s为小数点位数)
 *  raw—存储二进制的数据(存储图片等)
 *  lob—4gb的大对象数据类型(用于存储大数据)

## oracle的两个伪列:(rowid rownum) ##

*  前者存储表中地址(课用于快速定位,效率最高)
 *  后者是临时编号(一般用于返回查询的行数和分页)
 *  rownum不能使用大于号和等号
 *  用rownum分页:
    
        select e.*,rowid,rownum from lukaifang.emp e where rownum<6;(前五个)
          select e.*,rownum from emp e where empno not in (select empno from emp where rownum<=5)and rownum<=10
          //取出后五个
          select ee.* ,rownum from(select e.* , rownum rn from emp e where rownum<=10) ee where rn >5;
          //另一种方法取出后五个

## 同义词 ##

select * from scott.emp;

### 创建私有同义词 ###

create synonym myemp for scott.emp

### 使用同义词 ###

select *from myemp;

### 创建公有同义词 在任何其他模式下 都可以使用的同义词 ###

create public synonym myemp for wang.emp;

### 创建或替换 ###

create or replace synonym myemp for wang.emp;

### 删除同义词 ###

drop synonym myemp;

### 删除公有同义词需要授权 ###

drop public synonym pub_emp;

## 序列 ##

select * from user_sequence;

### 创建序列 默认 从1开始 步长1 ###

create sequence myseq;

### 使用序列 查看并增长（.nextval） ###

select myseq.nextval from dual;(下一个)
    select myseq.currval from dual;(当前)

### 序列其他选项 ###

create sequence myseq
    start with 10(开始位置)
    increment by 10(每次增长的长度)
    maxvalue 100(最大增长值)
    cycle (循环)
    cache3 (缓存序列号);

### 修改序列 ###

aleate sequence myseq maxvalue 5000 cycle;

### 删除序列 ###

drop sequence myseq;

## 锁和表分区 ##

### 加锁 : for updet ###

用于保护正在被修改的数据
      一致性 和 完整性 和 并行性

*  类型:  
    行级锁 和 表级锁  
    前者由以下引起: update-delete-insert-for update  
    后者有以下几种: 共享锁-排他锁-行排他锁-共享行排他-行共享

### 创建表,并进行分区 ###

create table students(
    stuid number primary key,
    sname varchar2(30),
    birthday date,
    score float,
    province varchar2(3)
    )
    partition by range(score)(
    partition D values less than(60),
    partition C values less than(70),
    partition B values less than(80),
    partition A values less than(maxvalue)
    )

### 添加分区:在最后一个分区之后添加新分区 ###

alter table students
    add partition X values less than (4000);

### 删除分区:删除一个指定的分区，分区的数据也随之删除 ###

alter table students drop partition p4;

### 截断分区:删除指定分区中的所有记录 ###

alter table students truncate partition p3;

### 合并分区:将范围分区或复合分区的两个相邻分区连接起来 ###

alter table students 
    merge partitions s1, s2 into partition s2;

### 拆分分区:将一个大分区中的记录拆分到两个分区中 ###

alter table students split partition p2 at (1500)
    into (partition p21, partition p22);

## 连接数据库 ##

*  加载驱动
 *  获取连接
 *  操作数据库
 *  关闭