Oracle 数据库基础知识总结

Oracle
数据库以表的形式存储文件、数据
Oracle 一般不区分大小写，但引号中的内容区分大小写。Oracle 只支持单引号，别名需要用双引号

数据库中的数据类型：
    1.数值类型 number(number(10)/number(38, 2)）不写括号时，默认大小为38，括号第一个参数指定数据最大长度，第二个参数表示小数位数
    2.字符类型 varchar2（长度） 该类型后面必须有括号，里面写数据的最大长度
    3.时间类型 date，数据的默认格式 '26-4月-2017'
    4.char        默认长度为 1 字符
    5.blob
    6.clob
    7.long

建表：    1.create table 表名(列名 数据类型, ..., 列名 数据类型);    表名自定义
        例如：create table student(name varchar2(10), age number, sex char);
    2.create table 表名 as select ...    ---用已经存在的表的（部分）结构创建新表并添加数据

删除表：drop table 表名;

排序：order by desc/asc(默认)
拼接：||    --除了数字记得加单引号
去重：distinct 列名
运算符：+ - * /(这个除可以有小数，不是取整)； mod(10,3)取余；
    > < >= <= !=  <> 后面两个是不等于
    and,or,not ...

插入数据：
    1.insert into 表名 values（'abc', , 'm'); 数据类型对应表中的顺序，不写默认为null。
    2.insert into 表名(sex, age) values('f', 24); values括号中值的顺序对应表名括号中的列名顺序。
    3.把一个表中的（某些）数据添加/复制到另一个表中：
        insert into 表1 select * from 表2 (where ...);

删除数据：
    1.delete from 表名 where name = 'abc'; 删除名为abc的那一行数据
    2.delete from 表名; 删除该表所有数据，表还在，数据没了
    3.truncate table 表名;功能同 2,但这个是DDL语句，无法回滚，速度比 2 快；

删除列：drop column 列名
添加列：add(列名 类型 约束);
重命名表：rename 旧表名 to 新表名
重命名列：rename column 旧列名 to 新列名

修改数据：
    uptate 表名 set 列名=值，列名=值 where 子句;  不写where子句则修改整列。
    例如：update student set age = age + 2;    ---所有学生年龄都加了2岁

        update student set age = age + 2 where name = 'abc';  ---名为abc的学生的年龄加了2岁

查询数据：
    一、简单查询
    1.select * from 表名;  ---查询该表的所有数据
    2.select * from 表名 where name = 'abc';  ---查询abc的所有信息
    3.select name, sex from 表名 where age=24;  ---查询年龄为24的学生的姓名和性别

    二、子查询
        就是用一条查询语句的结果作为另一查询的条件
        all(多个值) -- >all大于最大的，<all小于最小的
        any(多个值) -- >any大于最小的，<any小于最大的

    分页查询：rownum:行号，系统自动添加的只能和 < 或 = 连用。
                如果自己显示写出来了，则也可以 >
        eg：select * from emp where rownum < 5;
        select * from (select emp.*,rownum r from emp) d where d.r >= 5 and d.r <= 10

    三、多表查询
    笛卡尔积(集)错误：一个表的一条记录连接了另一个表的多条记录
    1.内连接：只显示符合条件的记录
        等值连接：
        不等值连接：
        自连接：
    2.外连接：不仅显示符合条件的记录，也显示不符合条件的其他记录
        左外连接：(1)在右边表写（+）eg: ...where dept.deptno = emp.deptno(+)，(2) ...from 表1 left (outer) join 表2 on 条件
        右外连接
        全外连接
    3.变形：
        cross join (差集) 类似笛卡尔积错误
        自然连接：natural join  --系统自己找连接条件，同列名就连接。没有同名列则无结果，有多个同名列则多连接，产生笛卡尔积错误
        join on 条件   --可以连接 n 个表。  ...from 表1 join 表2 on 条件 join 表3 on 条件 join 表4 on 条件....

过滤关键词（可用在查找删除修改等语句中）
    1. in()/not in()    ---值为/不为...
    例如：select * from student where age in(24,25,26);   ---查找年龄为24,25,26的这些学生
    2. between...and../not between...and...        ---是在/不在..之间
    例如：select * from student where age between 24 and 26;  ---查找年龄在24-26之间的学生的信息，包含24,26；
    3. is null/is not null        ---值为/不为空
    4. like 模糊查询（% 表示0至多个字符，_ 表示 1 个字符）
    例如：select * from student where name like %b%;    ---查询姓名中含有 b 的学生的所有信息。

约束：在建表时写在数据类型后面，或者后来设置也行。当设置了约束时，在插入数据时会进行检查，不符合约束条件不让添加。
    1. not null    设置了该约束条件的列名下的数据不能为空
    2. unique    唯一
    3. primary key     主键（不为空，且唯一），一般采用和对象无关的数字作主键；
    4. foreign key    外键（与另一个表的联系）
    5. check()     括号内自定义约束条件，比如指定某个范围
    
    约束写法一：创建表时添加(外键一般写在后面)
    例如：create table student(name varchar2(20) not null,
                    age number,
                    id numbmer primary key,
                    constraint 外键名 foreign key(name) references 父表(外键关联的列名)
                    );
    约束写法二：创建表之后添加
        1.alter table student add constraint 约束名(自定义) 约束(被约束列名);    ---not null 用第 2 种方式
        例如：alter table student add constraint age_con unique(age);
        2.alter table student modify(age not null/unique/primary key)    ---外键约束用第 1 种方式

    删除约束：alter table 表名 drop constraint 约束名;    (如果设置约束时没有设置约束名，系统会默认给一个)

    一个表中只有一个主键，可以有多个外键，可以用同表的有唯一约束的列作外键
    一个表中的一个字段可以有多个外键
        有外键关系的两个表，没有使用联级删除的情况下，增加数据时，需先增加被外键关联的表(父表)的数据，再增加外键所在的表的数据(子表)；
    删除数据时，需先删除子表中对应的数据，再删除父表被关联的数据。
        联级删除：设置外键是在后面加上 on delete set null 删除父表被关联的数据时，子表相应的数据设置为空；
                        on delete cascade; 删除父表被关联的数据时，子表相应的数据被删除；

commit 提交；在一个客户端提交到数据库，另一个客户端才能访问
    有些语句自动提交：DDL语句
    有些语句手动提交：DML语句
rollback 回滚；返回上次提交的状态，类似撤销

单行函数：一行返回一个结果
    1.转换函数
    to_date(); eg:to_date('1234-5-6','yyyy/mm/dd');前面的参数是要转换成日期的字符串，后面的参数是要转换成的日期格式。
    注意：格式主要是要显示到月还是日，时分秒，/可以换成 -和英文下的其他特殊符号
    to_char();
    to_number();

    2.通用函数
    nvl();
    nvl2(); --类似 Java 的条件运算符；

    case 列名 when 值1 then 语句1
          when 值2 then 语句2
          esle 语句3
    end 名字(必须写)
    注意：后面的语句的结果的类型必须与语句1 的类型相同

    decode(
        列名，值1，语句1，
            值2，语句2，
            语句3
    )名字(必须写)

    字符：
    upper();
    lower();
    initcap('hello,world'); --将每个单词首字母大写
    cancat('a','b'); --拼接
    substr('aaaaaaaaaa',1,5); --截取，从 1 开始，截取一定长度的字串
    instr('hello', o);  -- o 出现的位置为 5；
    trim('h'from 'hhhabcdhhabch');  --去除两边的 h，得 abchhabc
    replace('abcd','b','m');      --用 m 替换 b
    lpad(salary, 10, '*'); 不足 10 位填充 * ，从左边填充
    rpad();

    数字
    trunc();截断，可以有第二位小数，表示保留的小数位数，可以是负数
        eg: select trunc(12345,-2) from dual; --输出 12300
    round();四舍五入，可以有第二位参数，表示保留的小数位数，可以是负数
        eg: select round(12345,-1) from dual; --输出12340
    mod();取余

    日期
    months_between();  --两日期相差的月份数
    add_months();
    next_day();
    last_day()

多行函数/聚合函数：多行返回一个结果
    min();max();avg();sum();count();

分组函数：group by
    having 类似 where ，但是 having 必须跟 group by 一起使用
    group by 后面可以接多个列名

视图：是映射了某个表中(数据)的一个虚表。
    优点：不占空间；可以只映射需要用的数据，不映射不想公开的数据，保证数据的安全性；
    创建视图：create view 视图名 select 子句(选择需要映射的数据)
    删除视图：drop view 视图名

索引：系统提供的一种对象，用来提高查询速度
    create index 索引名 on 表名（列名1，列名2）
    主键 或 unique 约束的列自动创建索引

    不要随意创建索引，索引是占空间的
    在数据量大的情况下，且某列的查询频繁时，为该列创建索引

    删除索引：drop index 索引名

    系统不使用索引的情况：
        A.模糊查询中在首位用了%；... like '%a';
        B.有 is null,is not null
        C.有not
        ....

序列：可以自增长的一串有序数字，通常用作主键
    创建：create sequence 序列名 （start with 值，increment by 值 （没增加几），maxvalue/minvalue, cycle/nocycle() 循环, cache 10/nocache 缓存 10个数字）
          简化版：create sequence 序列名；

    使用：序列名.currval: 序列现在的值
        序列名.nextval: 序列的下一个值
    注意：首先需要使用 nextval，才能使用 currval
    修改：alter sequence 序列名 increment by ... --不能修改起始值
    裂缝：产生原因：多个表共用一个序列；序列使用了缓存；回滚；系统异常
   
事物：并发执行的一个单位
    特点：ACID
    原子性：事务的步骤等要么全部执行完，要么一个也不执行
    一致性：数据库的状态保持一致
    持久性：数据持久保存在数据库中
    隔离性：事务之间共用一个数据库时不会相互干扰，每个事务都有自己的数据空间

设置回滚点：savepoint 回滚点名称
        rollback to 回滚点名称

并发情况下可能出现的问题：
    脏读：因为回滚
    不可重复读：因为修改
    幻读：因为增删

问题解决：锁
    隔离级别：1.read uncommitted: 问题都不解决
        2.read committed: 可防止脏读
        3.repeatable read: 可防止脏读和不可重复读
        4.serializable:都可防止

    Oracle 只支持2,4，默认2
    设置隔离级别：set transaction isolation level 隔离级别

共享锁：可以共同读取数据，无法修改数据
排它锁：一个时间段内只能一个事务访问
悲观锁：认为所有事务都会来修改数据，所以当一个事务来访问数据时，会加锁
乐观锁：认为所有事务都是来查询数据，不加锁。 --同过版本号+1判断数据有没有被修改；