数据库学习

一、数据库
1. 用户
系统用户： sys system
sysman（企业管理的账号）
scott/tiger
登录： connect [username/password][@server][as sysdba][sysoper]
查看系统用户：
select * from dba_users；
select * from all_users；
查询当前链接的用户：
select * from v$session；
查询当前用户权限：
select * from session_privs；
启动scott用户： alter user username account unlock
2. 表空间
系统用户表空间是由多个数据文件构成的。

     表空间的分类：永久表空间、临时表空间、UNDO表空间（用于存储数据修改之前的值，用于回滚）
      SYSTEM：存放sys用户的表、视图、存储过程的数据库对象
      SYSAUX：做为EXAMPLE的辅助表空间
      UNDOTBS1：存储撤销信息
      TEMP：存储sql语句处理的表和索引信息，是临时表空间
      USERS：存储用户创建的数据库对象
      EXAMPLE：安装oracle示例的表空间

     查看用户的表空间：
              select tablespace_name from user_tablespaces;
              select tablespace_name from dba_tablespaces;
     设置用户的默认或临时表空间（普通用户没有修改和设置表空间的权限）
               ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name
     创建表空间：
              CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xx.dbf' SIZE xx
     查询数据库文件存放的位置：select file_name from dba_data_files；

     修改表空间：
    （1）设置联机或脱机状态
              ALTER TABLESPACE tablespace_name ONLINE|OFFLINE；
    （2）查询表空间的状态
              select status from dba_tablespaces ；
    （3）设置只读或可读写状态
               ALTER TABLESPACE tablespace_name READ ONLY |READ WRITE
    （4）删除表空间(及数据文件)
               DROP TABLESPACE tablespace_name [INCLUDING CONTENTS]

     修改数据文件：
    （1）增加数据文件
              ALTER TABLESPACE tablespace_name ADD DATAFILE 'xx.dbf' SIZE xx；
    （2）删除数据文件（不能删除创建表空间时的数据文件(第一个文件)）
              ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename.dbf'；

3. 表
数据类型：
（1）字符型：固定长度，空格补位： CHAR（n）、NCHAR（n）—用于存储Unicode格式的数据，更适合存储中文数据
可变长度：VARCHAR2（n）、NVARCHAR2（n）—用于存储Unicode格式的数据，更适合存储中文数据
（2）数值型：NUMBER（p,s） p：有效数字，s：小数点后的位数
FLOAT（n） 存储二进制数据
（3）日期型：DATE 表示范围：公元前4712年1月1日到公元9999年12月31日
TIMESTAMP：包括DATE数据类型的年月日时分秒信息，而且包括小数秒的信息
（4）其他类型：BLOB 以二进制的形式存放4GB
CLOB 以字符串的形式存放4GB

     修改表：
    （1）添加字段：ALTER TABLE table_name ADD column_name datatype；
    （2）更改字段数据类型：ALTER TABLE table_name MODIFY column_name datatype [not null | null]；
    （3）删除字段：ALTER TABLE table_name DROP COLUMN column_name；
    （4）修改字段名：ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name
    （5）修改表名：RENAME table_name TO new_table_name；

      删除表：
             TRUNCATE TABLE table_name；删除表中的全部数据，不删除表，截断表，速度快
             DROP TABLE table_name；删除表结构及数据

      操作表中的数据：
     （1）设置字段默认值：ALTER TABLE table_name MODIFY column_name default 值；
     （2）复制表数据：
                 创建表时复制数据：CREATE TABLE table_new AS SELECT * FROM table_old；
                 添加时复制数据：INSERT INFO table_new [(column1,...)] SELECT column1,...| * FROM table_old       

4. 约束 （user_constraints）
5个约束：非空约束、主键约束(primary key)、外键约束(references)、唯一约束(UNIQUE)、检查约束

       查询表的约束：select * from user_constraints; 
       更改约束名字：ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name 

       在创建表时设置主键约束：CONSTRAINT constraint_name PRIMARY KEY (column_name1,...)
       在修改表是日添加主键约束：ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name1,...)
       删除主键约束：
              ALTER TABLE table_name DISABLE |  ENABLE CONSTRAINT constraint_name；
              ALTER TABLE table_name DROP CINSTRAINT constraint_name

       在创建表是设置外键约束：CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES table_name(column_name) [ON DELETE CASCADE]--级联删除
       在修改表时添加外键约束：ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table_name(column_name) [ON DELETE CASCADE]
       删除外键约束：
              DISABLE | ENABLE CONSTRAINT constraint_name ；禁用
              DROP CONSTRAINT constraint_name

      **唯一约束和主键约束的区别**：
            主键字段值必须是非空的，唯一约束允许有一个空值
            主键在每张表中只能有一个，唯一约束在每张表中可以有多个
       在创建表时设置唯一约束：
            CREATE TABLE table_name （column_name datatype UNIQUE,...）
            或 CONSTRAINT constraint_name UNIQUE(column_name)     
       在修改表时添加唯一约束：      
            ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name)
       删除唯一约束：ALTER TABLE table_name 
            DISABLE | ENABLE CONSTRAINT constraint_name；禁用约束
            DROP CONSTRAINT constraint_name

       检查约束作用：表中的值更具有实际意义。
       在创建表时设置检查约束：
            CREATE TABLE table_name （column_name datatype CHECK(expressions),...）;
            CONSTRAINT constraint_name CHECK(expressions)
       在修改表时添加检查约束：
            ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (expressions)
       删除检查约束：ALTER TABLE table_name 
            DISABLE | ENABLE CONSTRAINT constraint_name;
            DROP CONSTRAINT constraint_name

5. 查询
case when语句：
CASE column_name WHEN value1 THEN result1，… [ELSE result] END
CASE WHEN column_name=value1 THEN result1，… [ELSE result] END
decode函数： 这个函数运行的结果是，当column_name的值等于value1时，该函数返回result1。。。否则返回defaultvalue
decode（column_name，value1，result1，…，defaultvalue）