Oracle数据库学习整理day4

• 表与常用数据类型
• 创建表基本语法
  
  • CREATE TABLE 表名(
    列名 数据类型[(长度)] [约束]
    列名 数据类型[(长度)] [约束]
    ……
    );
• Oracle中常用的数据类型
  
  • 数字类型 ：NUMBER
    
    • 整数：NUMBER(6)，长度可以省略，如果省略则默认为38位的数字
    • 浮点数：NUMBER(8,2)，长度不能省略，第一个参数表示整个数字的长度，第二个参数表示小数点后的长度
  • 字符类型：长度不能省略，默认单位为字节
    
    • CHAR：保存固定长度的字符串，如果长度不足，使用空格不齐长度
    • VARCHAR2：保存可变长度的字符串，如果内容长度不足，不使用空格补齐
    • VARCHAR：目前和VARCHAR2的功能相同，以后版本可能会改为其他类型
  • 日期类型：不能设置长度
• 数据表操作
  
  • 添加数据：INSERT INTO语句
    
    • 向表中添加一行新数据，并向新行中所有列赋值
    • INSERT INTO 表名 VALUES(值，值，……);
    • 向表中添加一行新数据，并向新行中指定列赋值。
    • INSERT INTO 表名(列名,列名,……) VALUES(值,值,……);
    • 将其它表中的数据导入到指定的表中。
    • INSERT INTO 表名[(列名,列名,……)] SELECT语句;
  • 修改数据：UPDATE语句
    
    • UPDATE 表名 SET 列名=值[,列名=值,……] [WHERE 条件];
  • 删除数据:DELETE语句
    
    • DELETE [FROM] 表名 [WHERE 条件];

• 约束

  • 主键约束：PRIMARY KEY，通过主键可以在表中找到唯一的一行数据
  • 非空约束：NOT NULL，不能那个为空
  • 检查约束：CHECK，当向列中添加数据或者修改列中的数据时，检查数据是否合法
  • 默认值：DEFAULT，当没有向列中添加数据时，默认向列中添加的数据
  • 唯一约束：UNIQUE，可以为NULL(Oracle认为NULL可以重复)

  • 外键约束

    • CREATE TABLE S(
      S_ID NUMBER(6) PRIMARY KEY,
      S_NAME VARCHAR2(30 CHAR) NOT NULL
      );

    • CREATE TABLE C(
      C_ID NUMBER(3) PRIMARY KEY,
      C_NAME VARCHAR2(200 CHAR) NOT NULL
      );

    • CREATE TABLE SC(
      SC_ID NUMBER(8) PRIMARY KEY,
      SC_S_ID NUMBER(6) NOT NULL,
      SC_C_ID NUMBER(3) NOT NULL,
      CONSTRAINT SC_SID_FK FOREIGN KEY(SC_S_ID) REFERENCES S(S_ID),
      CONSTRAINT SC_CID_FK FOREIGN KEY(SC_C_ID) REFERENCES C(C_ID)
      );

    • 主外键添加数据：先添加主表，再添加字表
    • 主外键删除数据：先删除字表，再删除主表
    • 联合主键：一个表只能有一个主键，但一个主键可以由多个列组成
      
      • CREATE TABLE TEST3(
        T_NAME VARCHAR2(20),
        T_SEX CHAR(1 CHAR),
        CONSTRAINT TEST_ID_PK PRIMARY KEY(T_NAME,T_SEX)
        );
• SQL语句的分类
  
  • DML(数据操作语言):SELECT,INSERT,UPDATE,DELETE
  • DDL(数据定义语言):CREATE,DROP,ALTER,TRUNCATE等
  • DCL(数据控制语言):COMMIT,ROLLBACK,SAVEPOINT等
• 事务：单位时间内一系列的操作，这些要么全部成功，要么全部失败
  
  • COMMIT：提交事务，将对数据的操作保存到数据库中
  • ROLLBACK：回退事务，将数据还原到最初或最后一次提交的状态
  • 事务的组成
    
    • 可以有多个DML语句
    • 可以只有一个DDL或DCL语句
  • 当异常结束Oracle会话时，Oracle会自动回退事务
  • 当正常结束Oracle会话时，Oracle会自动提交事务
  • 当执行DDL语句时，会自动提交事务
• 截断表：TRUNCATE TABLE 表名。删除表中所有的数据，但不删除表结构，删除的数据不能回退
• 序列：产生一个数字，用于自动生成主键
  
  • 创建序列基本格式：CREATE SEQUENCE 序列名；
  • 序列的值不能回退
  • 多个表可以共用一个序列
  • 序列属性
    
    • NEXTVAL：获得序列的下一个值，每次调用此属性，序列的值都会改变
    • CURRVAL：获得序列的当前值，每次调用序列的值不改变
• 视图
  
  • CREATE [OR REPLACE] VIEW 视图名 AS SELECT语句；
  • 视图的用法与表
  • 视图的作用
    
    • 简化查询（视图不能提高查询效率）
    • 提高数据库安全性
  • 视图中没有数据，视图的数据来自于表
• 索引：提高查询的效率，但会降低增删改的效率
  
  • 当表有主键约束或者唯一约束时，Oracle会为这些列添加唯一性索引
• 多标连接
  
  • 等值连接(内连接)：只能查询出满足连接条件的数据
    
    • 查询员工的last_name,department_name
      
      • SELECT LAST_NAME,DEPARTMENT_NAME
        FROM EMPLOYEES E,DEPARTMENTS D
        WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;
    • 当在FROM子句中为表起了别名后，其它子句不能再使用表的原名。
    • 多表连接时，建议在每个列的前面都加上表前缀，可以提高查询的效率
      
      • SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
        FROM EMPLOYEES E,DEPARTMENTS D
        WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;
    • 通常情况下，连接条件的个数为表的个数减1
    • 查询员工的last_name,department_name,city
      
      • SELECT E.LAST_NAME,D.DEPARTMENT_NAME,L.CITY
        FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
        WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
        AND D.LOCATION_ID=L.LOCATION_ID;
  • 外连接：可以查询出满足连接条件与不满足连接条件的数据。
    
    • 左外连接：查询所有员工的last_name,department_name
      
      • SELECT E.LAST_NAME,D.DEPARTMENT_NAME
        FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
        ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;
    • 右外连接
      
      • SELECT E.LAST_NAME,D.DEPARTMENT_NAME
        FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
        ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;
    • 全外连接
      
      • SELECT E.LAST_NAME,D.DEPARTMENT_NAME
        FROM EMPLOYEES E FULL JOIN DEPARTMENTS D
        ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;
• 组函数（聚合函数）
  
  • SUM():求总和
    
    • SELECT SUM(SALARY)
      FROM EMPLOYEES;
  • AVG():求平均值
    
    • SELECT AVG(SALARY)
      FROM EMPLOYEES;
  • MAX()/MIN():最大/最小值
    
    • SELECT MAX(SALARY),MIN(SALARY)
      FROM EMPLOYEES;
  • COUNT()：获得查询结果的行数
    
    • SELECT COUNT(*)–获得查询结果的行数
      FROM EMPLOYEES;
    • SELECT COUNT(COMMISSION_PCT)–获得指定列中不为NULL的值的个数
      FROM EMPLOYEES;
  • 组函数与NULL值:关系数据库中所有的组函数在计算时会自动忽略NULL值。
    
    • SELECT AVG(NVL(COMMISSION_PCT,0))
      FROM EMPLOYEES;
• END