Oracle

Oracle学习和内容总结：INSERT：添加数据到数据库中；UPDATE：修改数据库中的数据；DELETE：删除数据库中的数据。

oracle

• SQL
  • DML
    • Data Manipulation Language 数据操纵语言
    • 查询和修改数据记录
      • INSERT
        • 使用 INSERT 语句向表中插入数据。 INSERT INTO table [(column [, column...])] VALUES (value [, value...]); •使用这种语法一次只能向表中插入一条数据。
        • 添加数据到数据库中
          • 插入数据
            • •为每一列添加一个新值。 •按列的默认顺序列出各个列的值。 •在 INSERT 子句中随意列出列名和他们的值。 •字符和日期型数据应包含在单引号中。 INSERT INTO departments(department_id, department_name,                         manager_id, location_id) VALUES      (70, 'Public Relations', 100, 1700); 1 row created. INSERT INTO employees(employee_id,last_name,email,hire_date,job_id) VALUES  (300,’Tom’,’[e-mail]’,to_date(‘2012-3-21’,’yyyy-mm-dd’),’SA_RAP’); 1 row created.
          • 插入空值
            • •隐式方式: 在列名表中省略该列的值。 INSERT INTO  departments (department_id,                           department_name    ) VALUES  (30, 'Purchasing'); 1 row created. •显示方式: 在VALUES 子句中指定空值。 INSERT INTO  departments VALUES  (100, 'Finance', NULL, NULL); 1 row created.
          • 插入指定值
            • SYSDATE 记录当前系统的日期和时间。 INSERT INTO employees (employee_id,                  first_name, last_name,                  email, phone_number,                  hire_date, job_id, salary,                  commission_pct, manager_id,                  department_id) VALUES     (113,                  'Louis', 'Popp',                  'LPOPP', '515.124.4567',                  SYSDATE, 'AC_ACCOUNT', 6900,                  NULL, 205, 100); 1 row created.
          • 插入指定值
            • •加入新员工 INSERT INTO employees VALUES      (114,              'Den', 'Raphealy',              'DRAPHEAL', '515.127.4561',              TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),              'AC_ACCOUNT', 11000, NULL, 100, 30); 1 row created.
          • 创建脚本插入
            • •在SQL 语句中使用 & 变量指定列值。 •& 变量放在VALUES子句中。 INSERT INTO departments             (department_id, department_name, location_id) VALUES   p
          • 从其他表中拷贝数据
            • •在 INSERT 语句中加入子查询。 INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90; 3 rows created. INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM   employees WHERE  job_id LIKE '%REP%'; 4 rows created. •不必书写 VALUES 子句。 •子查询中的值列表应与 INSERT 子句中的列名对应
      • UPDATE
        • •使用 UPDATE 语句更新数据。 UPDATE table SET column = value [, column = value, ...] [WHERE   condition]; •可以一次更新多条数据。
        • 修改数据库中的数据
          • 更新指定数据
            • •使用 WHERE 子句指定需要更新的数据。 UPDATE employees SET    department_id = 70 WHERE  employee_id = 113; 1 row updated. •如果省略 WHERE 子句，则表中的所有数据都将被更新。 UPDATE   copy_emp SET      department_id = 110; 22 rows updated.
          • 使用子查询更新数据
            • 题目：更新 114号员工的工作和工资使其与205号员工相同。 UPDATE   employees SET      job_id  = (SELECT  job_id                     FROM    employees                     WHERE   employee_id = 205),          salary  = (SELECT  salary                     FROM    employees                     WHERE   employee_id = 205) WHERE    employee_id    =  114; 1 row updated.
          • 使用子查询更新数据
            • 在 UPDATE 中使用子查询，使更新基于另一个表中的数据。 题目：调整与employee_id 为200的员工job_id相同的员工的department_id为employee_id为100的员工的department_id。 UPDATE  copy_emp SET     department_id  =  (SELECT department_id                            FROM employees                            WHERE employee_id = 100) WHERE   job_id         =  (SELECT job_id                            FROM employees                            WHERE employee_id = 200); 1 row updated.
          • 更新数据完整性错误
            • UPDATE employees SET    department_id = 55 WHERE  department_id = 110; UPDATE employees        * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found 不存在 55 号部门
      • DELETE
        • DELETE FROM  table [WHERE condition]; 使用 DELETE 语句从表中删除数据
        • 删除数据库中的数据
          • 删除指定数据
            • •使用 WHERE 子句删除指定的记录。  DELETE FROM departments  WHERE  department_name = 'Finance'; 1 row deleted. •如果省略 WHERE 子句，则表中的全部数据将被删除 DELETE FROM  copy_emp; 22 rows deleted.
          • 使用字查询删除数据
            • 在 DELETE 中使用子查询，使删除基于另一个表中的数据。 题目：从emp1表中删除dept1部门名称中含Public字符的部门id DELETE FROM emp1 WHERE  department_id =                        (SELECT department_id                         FROM   dept1                         WHERE  department_name LIKE '%Public%'); 1 row deleted.
          • 删除的数据完整性错误
            • DELETE FROM departments WHERE       department_id = 60; DELETE FROM departments             * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
      • 基础
        • 数据查询
          • 单表查询
            • SELECT
              • SELECT  *|{[DISTINCT] column|expression [alias],...} FROM table;
              • 选择全部列
                • SELECT * FROM   departments;
              • 选择特定的列
                • SELECT department_id, location_id FROM   departments;
              • 列
                • 列的别名
                  • 1.SELECT last_name AS name, commission_pct comm FROM   employees; 2.SELECT last_name "Name", salary*12 "Annual Salary" FROM   employees;
                • 连接符
                  • SELECT  last_name||job_id AS "Employees" FROM   employees;
                • 字符串
                  • •日期和字符只能在单引号中出现。 SELECT last_name  ||' is a '||job_id        AS "Employee Details" FROM   employees;
                • 去除重复行
                  • SELECT DISTINCT department_id FROM   employees;
              • 显示表结构
                • DESC[RIBE] tablename
          • 多表查询
            • Oracle连接
              • SELECT  table1.column, table2.column FROM  table1, table2 WHERE  table1.column1 = table2.column2; •在 WHERE 子句中写入连接条件。 •在表中有相同列时，在列名之前加上表名前缀
              • 内连接
                • 等值查询
                  • SELECT employees.employee_id, employees.last_name,        employees.department_id, departments.department_id,        departments.location_id FROM   employees, departments WHERE  employees.department_id = departments.department_id; 多个连接条件where后用AND连接 •使用表名前缀在多个表中区分相同的列。 •在不同表中具有相同列名的列可以用表的别名加以区分。
                  • 表的别名
                    • SELECT e.employee_id, e.last_name, e.department_id,        d.department_id, d.location_id FROM   employees e , departments d WHERE  e.department_id = d.department_id; •使用别名可以简化查询。 •使用表名前缀可以提高执行效率。
                  • 连接多个表
                    • •连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。
                • 非等值连接
                  • SELECT e.last_name, e.salary, j.grade_level FROM   employees e, job_grades j WHERE  e.salary        BETWEEN j.lowest_sal AND j.highest_sal; p
              • 外连接
                • p •使用外连接可以查询不满足连接条件的数据。 •外连接的符号是 (+)。
                • 右外连接
                  • SELECT  table1.column, table2.column FROM  table1, table2 WHERE  table1.column(+) = table2.column;
                  • 例题
                    • SELECT e.last_name, e.department_id, d.department_name FROM   employees e, departments d WHERE  e.department_id(+) = d.department_id ;
                • 左外连接
                  • SELECT  table1.column, table2.column FROM  table1, table2 WHERE  table1.column = table2.column(+);
              • 自连接
                • p
                • 例题
                  • 题目：查询employees表，返回“Xxx  works for Xxx” SELECT worker.last_name || ' works for '        || manager.last_name FROM   employees worker, employees manager WHERE  worker.manager_id = manager.employee_id
            • 1999语法连接
              • SELECT  table1.column, table2.column 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)];
              • 叉集（了解）
                • •使用CROSS JOIN 子句使连接的表产生叉集。 •叉集和笛卡尔集是相同的。 SELECT last_name, department_name FROM   employees CROSS JOIN departments ; p
              • 自然连接
                • •NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。 •在表中查询满足等值条件的数据。 •如果只是列名相同而数据类型不同，则会产生错误。
                • 例题
                  • SELECT department_id, department_name,        location_id, city FROM   departments NATURAL JOIN locations ; p
                • 使用USING字句创造连接
                  • •在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。 •使用 USING 可以在有多个列满足条件时进行选择。 •不要给选中的列中加上表名前缀或别名。 •JOIN 和 USING 子句经常同时使用。 p
                  • 例题
                    • SELECT e.employee_id, e.last_name, d.location_id FROM   employees e JOIN departments d USING (department_id) ; p
                • 使用ON子句创建连接（常用）
                  • •自然连接中是以具有相同名字的列为连接条件的。 •可以使用 ON 子句指定额外的连接条件。 •这个连接条件是与其它条件分开的。 •ON 子句使语句具有更高的易读性。
                  • 单表
                    • 例题
                      • SELECT e.employee_id, e.last_name, e.department_id,        d.department_id, d.location_id FROM   employees e JOIN departments d ON     (e.department_id = d.department_id); p
                  • 多表
                    • 例题
                      • SELECT employee_id, city, department_name FROM   employees e JOIN   departments d ON     d.department_id = e.department_id JOIN   locations l ON     d.location_id = l.location_id; p
              • 外连接
                • •在SQL: 1999中，内连接只返回满足连接条件的数据 •两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外连接。 •两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满 外连接。
                • 左外连接
                  • SELECT e.last_name, e.department_id, d.department_name FROM   employees e LEFT OUTER JOIN departments d ON   (e.department_id = d.department_id) ; p
                • 右外连接
                  • SELECT e.last_name, e.department_id, d.department_name FROM   employees e RIGHT OUTER JOIN departments d ON    (e.department_id = d.department_id) ; p
                • 满外连接
                  • SELECT e.last_name, e.department_id, d.department_name FROM   employees e FULL OUTER JOIN departments d ON   (e.department_id = d.department_id) ; p
          • 子查询
            • SELECT  select_list FROM  table WHERE  expr operator      (SELECT  select_list           FROM  table); •子查询 (内查询) 在主查询之前一次执行完成。 •子查询的结果被主查询(外查询)使用 。
            • 例题
              • SELECT last_name FROM   employees WHERE  salary >                (SELECT salary                 FROM   employees                 WHERE  last_name = 'Abel');
            • 注意事项
              • •子查询要包含在括号内。 •将子查询放在比较条件的右侧。 •单行操作符对应单行子查询，多行操作符对应多行子查询。
            • 单行子查询
              • •只返回一行。 •使用单行比较操作符。 pp
              • 例题
                • 题目：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资 SELECT last_name, job_id, salary FROM   employees WHERE  job_id =                  (SELECT job_id                  FROM   employees                  WHERE  employee_id = 141) AND    salary >                 (SELECT salary                  FROM   employees                  WHERE  employee_id = 143);
              • 例题
                • 题目：返回公司工资最少的员工的last_name,job_id和salary SELECT last_name, job_id, salary FROM   employees WHERE  salary =                 (SELECT MIN(salary)                  FROM   employees);
              • 例题
                • 子查询中的 HAVING 子句 •首先执行子查询。 •向主查询中的HAVING 子句返回结果。 题目：查询最低工资大于50号部门最低工资的部门id和其最低工资 SELECT   department_id, MIN(salary) FROM     employees GROUP BY department_id HAVING   MIN(salary) >                        (SELECT MIN(salary)                         FROM   employees                         WHERE  department_id = 50);
              • 非法使用子查询
                • SELECT employee_id, last_name FROM   employees WHERE  salary =                 (SELECT   MIN(salary)                  FROM     employees                  GROUP BY department_id); ERROR at line 4: ORA-01427: single-row subquery returns more than
one row 多行子查询使用单行比较符
              • 空值问题
                • SELECT last_name, job_id FROM   employees WHERE  job_id =                 (SELECT job_id                  FROM   employees                  WHERE  last_name = 'Haas'); no rows selected 子查询不返回任何行
            • 多行子查询
              • 返回多行。 使用多行比较操作符 pp
              • 使用ANY操作符
                • 题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员 工号、姓名、job_id 以及salary SELECT employee_id, last_name, job_id, salary FROM   employees WHERE  salary < ANY                     (SELECT salary                      FROM   employees                      WHERE  job_id = 'IT_PROG') AND    job_id <> 'IT_PROG';
              • 使用ALL操作符
                • 题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary SELECT employee_id, last_name, job_id, salary FROM   employees WHERE  salary < ALL                     (SELECT salary                      FROM   employees                      WHERE  job_id = 'IT_PROG') AND    job_id <> 'IT_PROG';
              • 空值问题
                • SELECT emp.last_name FROM   employees emp WHERE  emp.employee_id NOT IN                              (SELECT mgr.manager_id                               FROM   employees mgr); no rows selected
        • 过滤和排序
          • 过滤
            • SELECT employee_id, last_name, job_id, department_id FROM   employees WHERE  department_id = 90 ; WHERE 子句紧随 FROM 子句 •字符和日期要包含在单引号中。 •字符大小写敏感，日期格式敏感。 •默认的日期格式是 DD-MON月-RR。 '7-6月-1994'
            • WHERE
              • 比较运算
                • SELECT last_name, salary FROM   employees WHERE  salary <= 3000;
                • 运算符
                  • p
              • 其他比较运算
                • p
                • BETWEEN
                  • 使用 BETWEEN 运算来显示在一个区间内的值 SELECT last_name, salary FROM   employees WHERE  salary BETWEEN 2500 AND 3500;
                • IN
                  • 使用 IN运算显示在列表中的值 SELECT employee_id, last_name, salary, manager_id FROM   employees WHERE  manager_id IN (100, 101, 201);
                • LIKE
                  • •使用 LIKE 运算选择类似的值 •选择条件可以包含字符或数字: % 代表零个或多个字符(任意个字符)。 _ 代表一个字符。 •‘%’和‘-’可以同时使用。 SELECT  first_name FROM   employees WHERE  first_name LIKE 'S%';
                  • ESCAPE
                    • •回避特殊符号的：使用转义符。例如：将[%]转为[\%]、[_]转为[\_]，然后再加上[ESCAPE ‘\’] 即可。 SELECT job_id FROM   jobs WHERE  job_id LIKE ‘IT\_%‘ escape ‘\‘;
                • NULL
                  • 使用 IS (NOT) NULL 判断空值。 SELECT last_name, manager_id FROM   employees WHERE  manager_id IS NULL;
              • 逻辑运算
                • p
                • AND
                  • 俩个条件地同时都满足 SELECT employee_id, last_name, job_id, salary FROM   employees WHERE  salary >=10000 AND    job_id LIKE '%MAN%';
                • OR
                  • 只要俩个条件满足一个就输出 SELECT employee_id, last_name, job_id, salary FROM   employees WHERE  salary >= 10000 OR     job_id LIKE '%MAN%';
                • NOT
                  • 不在括号里地数据输出 SELECT last_name, job_id FROM   employees WHERE  job_id        NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
              • 优先级
                • p
          • 排序
            • •使用 ORDER BY 子句排序 –ASC（ascend）: 升序 –DESC（descend）: 降序 ORDER BY 子句在SELECT语句的结尾 SELECT   last_name, job_id, department_id, hire_date FROM     employees ORDER BY hire_date ;
            • ORDER BY
              • 降序排列
                • SELECT   last_name, job_id, department_id, hire_date FROM     employees ORDER BY hire_date DESC ;
              • 升序排列
              • 按别名排序
                • SELECT employee_id, last_name, salary*12 annsal FROM   employees ORDER BY annsal;
              • 多个列排列
                • SELECT last_name, department_id, salary FROM   employees ORDER BY department_id, salary DESC;
        • 函数
          • 单行函数
            • 只对一行进行变换 每行返回一个结果 function_name [(arg1, arg2,...)]
            • 字符函数
              • 大小写控制函数
                • p
                • 小写
                  • SELECT employee_id, last_name, department_id FROM   employees WHERE  LOWER(last_name) = 'higgins';
                • 大写
                • 首字母大写
              • 字符控制函数
                • p
                • 例题
                  • SELECT employee_id, CONCAT(first_name, last_name) NAME,        job_id, LENGTH (last_name),        INSTR(last_name, 'a') "Contains 'a'?" FROM   employees WHERE  SUBSTR(job_id, 4) = 'REP';
            • 通用函数
              • NVL
                • 将空值转换成一个已知的值： •可以使用的数据类型有日期、字符、数字。 •函数的一般形式: –NVL(commission_pct,0) –NVL(hire_date,'01-JAN-97') –NVL(job_id,'No Job Yet')
                • 例题
                  • SELECT last_name, salary, NVL(commission_pct, 0),    (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees; 0 288000 0 204000
              • NVL2
                • NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
                • 例题
                  • SELECT last_name,  salary, commission_pct,        NVL2(commission_pct,             'SAL+COMM', 'SAL') income FROM   employees WHERE department_id IN (50, 80);
              • NULLIF
                • NULLIF (expr1, expr2) :  相等返回NULL，不等返回expr1
                • 例题
                  • SELECT first_name, LENGTH(first_name) "expr1",        last_name,  LENGTH(last_name)  "expr2",        NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM   employees;
              • COALESCE
                • •COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。 •如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。 依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
                • 例题
                  • SELECT   last_name,          COALESCE(commission_pct, salary, 10) comm FROM     employees ORDER BY commission_pct;
              • CASE
                • CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
                • 例题
                  • SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary WHEN 'ST_CLERK' THEN  1.15*salary WHEN 'SA_REP'   THEN  1.20*salary ELSE      salary END     "REVISED_SALARY" FROM   employees;
              • DECODE
                • DECODE(col|expression, search1, result1 ,            [, search2, result2,...,]            [, default])
                • 例题
                  • SELECT last_name, job_id, salary,        DECODE(job_id, 'IT_PROG',  1.10*salary,                       'ST_CLERK', 1.15*salary,                       'SA_REP',   1.20*salary,                     salary)        REVISED_SALARY FROM   employees;
                • 例题
                  • SELECT last_name, salary,        DECODE (TRUNC(salary/2000, 0),                          0, 0.00,                          1, 0.09,                          2, 0.20,                          3, 0.30,                          4, 0.40,                          5, 0.42,                          6, 0.44,                             0.45) TAX_RATE FROM   employees WHERE  department_id = 80;
            • 转换函数
            • 数值函数
              • ROUND
                • 四舍五入函数 SELECT ROUND(45.923,2), ROUND(45.923,0),        ROUND(45.923,-1) FROM   DUAL; 45.92 46 50
              • TRUNC
                • 截断函数 SELECT  TRUNC(45.923,2), TRUNC(45.923),         TRUNC(45.923,-2) FROM   DUAL; 45.92 45 0
              • MOD
                • 求余函数 SELECT last_name, salary, MOD(salary, 5000) FROM   employees WHERE  job_id = 'SA_REP'; MOD(SALARY,5000) 1000 3600 2000
            • 日期函数
              • •在日期上加上或减去一个数字结果仍为日期。 •两个日期相减返回日期之间相差的天数。 –日期不允许做加法运算，无意义 •可以用数字除24来向日期中加上或减去天数。
              • 例题
                • SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM   employees WHERE  department_id = 90;
              • 例题
                • pp
              • 例题
                • p
            • 嵌套函数
              • •单行函数可以嵌套。 •嵌套函数的执行顺序是由内到外。 p
              • 例题
                • SELECT last_name,        NVL(TO_CHAR(manager_id), 'No Manager') FROM   employees WHERE  manager_id IS NULL;
          • 分组
            • SELECT  [column,] group_function(column), ... FROM  table [WHERE  condition] [GROUP BY  column] [ORDER BY  column];
            • 分组函数
              • AVG和SUM
                • SELECT AVG(salary), MAX(salary),               MIN(salary), SUM(salary) FROM   employees WHERE  job_id LIKE '%REP%';
                • 例题
                  • 组函数忽略空值。 SELECT AVG(commission_pct) FROM   employees;
                • 例题
                  • NVL函数使分组函数无法忽略空值。 SELECT AVG(NVL(commission_pct, 0)) FROM   employees;
              • MAX和MIN
                • SELECT MIN(hire_date), MAX(hire_date) FROM    employees;
              • COUNT计数
                • SELECT COUNT(*) FROM    employees WHERE  department_id = 50;
                • 例题
                  • •COUNT(expr) 返回expr不为空的记录总数。 SELECT COUNT(commission_pct) FROM   employees WHERE  department_id = 50;
                • 例题
                  • •COUNT(DISTINCT expr)返回expr非空且不重复的记录总数 SELECT COUNT(DISTINCT department_id) FROM   employees;
              • 嵌套组函数
                • SELECT   MAX(AVG(salary)) FROM     employees GROUP BY department_id; 显示各部门平均工资的最大值
            • 分组数据
              • 可以使用GROUP BY子句将表中的数据分成若干组 SELECT  column, group_function(column) FROM  table [WHERE  condition] [GROUP BY  group_by_expression] [ORDER BY  column]; WHERE一定放在FROM后面 有分组函数必须分组
              • GROUP BY
                • 单列分组
                  • 例题
                    • 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中 SELECT   AVG(salary) FROM     employees GROUP BY department_id ;
                  • 例题
                    • 在SELECT 列表中所有未包含在组函数中的列都应该包含 在 GROUP BY 子句中。 SELECT   department_id, AVG(salary) FROM     employees GROUP BY department_id ;
                • 多列分组
                  • 例题
                    • SELECT   department_id dept_id, job_id, SUM(salary) FROM     employees GROUP BY department_id, job_id ; p
            • 过滤分组
              • HAVING子句
                • 使用 HAVING 过滤分组: 1.  行已经被分组。 2.  使用了组函数。 3.  满足HAVING 子句中条件的分组将被显示。 SELECT  column, group_function FROM  table [WHERE  condition] [GROUP BY  group_by_expression] [HAVING  group_condition] [ORDER BY  column];
                • 例题
                  • SELECT   department_id, MAX(salary) FROM     employees GROUP BY department_id HAVING   MAX(salary)>10000 ;
            • 非法使用组函数
              • 例题
                • 所有包含于SELECT 列表中，而未包含于组函数中的列都 必须包含于 GROUP BY 子句中。 SELECT department_id, COUNT(last_name) FROM   employees; SELECT department_id, COUNT(last_name) ERROR at line 1: ORA-00937: not a single-group group function GROUP BY 子句中缺少列
              • 例题
                • •不能在 WHERE 子句中使用组函数。 •可以在 HAVING 子句中使用组函数。 SELECT   department_id, AVG(salary) FROM     employees WHERE    AVG(salary) > 8000 GROUP BY department_id; WHERE  AVG(salary) > 8000        * ERROR at line 3: ORA-00934: group function is not allowed here WHERE 子句中不能使用组函数
      • 子主题
  • DDL
    • Data Definition Language 数据定义语言
    • 定义数据库的结构
      • 基础查询
        • •查看用户定义的表. SELECT table_name FROM    user_tables ; •查看用户定义的各种数据库对象 SELECT DISTINCT object_type FROM     user_objects ; •查看用户定义的表, 视图, 同义词和序列 SELECT  * FROM    user_catalog ;
      • 命名规则
        • 表名和列名: •必须以字母开头 •必须在 1–30 个字符之间 •必须只能包含 A–Z, a–z, 0–9, _, $, 和 # •必须不能和用户定义的其他对象重名 •必须不能是Oracle 的保留字
      • CREATE TABLE
        • 创建数据库表
          • •必须具备: –CREATE TABLE权限 –存储空间 CREATE TABLE [schema.]table       (column datatype [DEFAULT expr][, ...]); •必须指定: –表名 –列名, 数据类型, 尺寸
          • 创建表
            • 语法 CREATE TABLE dept
  (deptno   NUMBER(2),   dname   VARCHAR2(14),   loc   VARCHAR2(13)); Table created. 确认 DESCRIBE dept p
          • 数据类型
            • p
          • 使用子查询创建表
            • •使用 AS subquery 选项，将创建表和插入数据结合起来 CREATE TABLE table       [(column, column...)] AS subquery; •指定的列和子查询中的列要一一对应 •通过列名和默认值定义列
            • 例题
              • create table emp1 as select * from employees; create table emp2 as select * from employees where 1=2; --创建的emp2是空表。
            • 例题
              • CREATE TABLE   dept80   AS 
    SELECT  employee_id, last_name,             salary*12 ANNSAL,             hire_date
    FROM    employees
    WHERE   department_id = 80; Table created. DESCRIBE dept80
      • ALTER TABLE
        • 使用 ALTER TABLE 语句可以: •追加新的列 •修改现有的列 •为新追加的列定义默认值 •删除一个列 •重命名表的一个列名
        • 更改表结构，添加，删除，修改列长度
          • 使用 ALTER TABLE 语句追加, 修改, 或删除列的语法. ALTER TABLE table ADD     (column datatype [DEFAULT expr]      [, column datatype]...); ALTER TABLE table MODIFY (column datatype [DEFAULT expr]      [, column datatype]...); ALTER TABLE table DROP COLUMN column_name; ALTER TABLE table_name RENAME COLUMM old_column_name TO new_column_name
          • 追加一个新列
            • •使用 ADD 子句追加一个新列 ALTER TABLE dept80 ADD     (job_id VARCHAR2(9)); Table altered. •新列是表中的最后一列 p
          • 修改一个列
            • •可以修改列的数据类型, 尺寸和默认值 ALTER TABLE  dept80 MODIFY  (last_name VARCHAR2(30)); Table altered. ALTER TABLE  dept80 MODIFY  (salary number(9,2) default 1000); Table altered. •对默认值的修改只影响今后对表的修改
          • 删除一个列
            • 使用 DROP COLUMN 子句删除不再需要的列. ALTER TABLE  dept80 DROP COLUMN  job_id; Table altered.
          • 重命名一个列
            • 使用 RENAME COLUMN [table_name] TO子句重命名列 ALTER TABLE  dept80 RENAME COLUMN  job_id TO id; Table altered.
      • DROP TABLE
        • •数据和结构都被删除 •所有正在运行的相关事务被提交 •所有相关索引被删除 •DROP TABLE 语句不能回滚 DROP TABLE dept80; Table dropped.
        • 删除表
      • TRUNCATE TABLE
        • •TRUNCATE TABLE 语句: –删除表中所有的数据 –释放表的存储空间 TRUNCATE TABLE detail_dept; Table truncated. •TRUNCATE语句不能回滚 •可以使用 DELETE 语句删除数据,可以回滚 •对比：   delete from emp2;   select * from emp2;   rollback;   select * from emp2;
        • 清除表
      • 改变对象名称
        • •执行RENAME语句改变表, 视图, 序列, 或同义词的名称 RENAME dept TO detail_dept; Table renamed. •必须是对象的拥有者
      • CREATE INDEX
        • 在表上建立索引
      • DROP INDEX
        • 删除索引
  • DCL
    • Data Control Language 数据控制语言
    • 控制数据库的访问
      • GRANT
        • 授予访问权限
      • REVOKE
        • 撤销访问权限
      • COMMIT
        • 提交事务处理
      • ROLLBACK
        • 事务处理回退
      • SAVEPOINT
        • 设置保存点
      • LOCK
        • 对数据库的特定部分进行锁定
  • 数据库事务
    • •事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。 •数据库事务由以下的部分组成: –一个或多个DML 语句 –一个 DDL(Data Definition Language – 数据定义语言) 语句 –一个 DCL(Data Control Language – 数据控制语言) 语句
    • 规则
      • •以第一个 DML 语句的执行作为开始 • •以下面的其中之一作为结束: –COMMIT 或 ROLLBACK 语句 –DDL 语句（自动提交） –用户会话正常结束 –系统异常终止
      • COMMIT和ROLLBACK优点
        • 使用COMMIT 和 ROLLBACK语句,我们可以: •确保数据完整性。 •数据改变被提交之前预览。 •将逻辑上相关的操作分组。
      • 控制事务
        • p
        • 回滚到保留点
          • •使用 SAVEPOINT 语句在当前事务中创建保存点。 •使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。 UPDATE... SAVEPOINT update_done; Savepoint created. INSERT... ROLLBACK TO update_done; Rollback complete.
      • 事务进程
        • •自动提交在以下情况中执行: –DDL 语句。 –DCL 语句。 –不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。 会话异常结束或系统异常会导致自动回滚
        • 提交或回滚前的数据状态
          • •改变前的数据状态是可以恢复的 •执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正 •其他用户不能看到当前用户所做的改变，直到当前用户结束事务。 •DML语句所涉及到的行被锁定， 其他用户不能操作。
        • 提交后的状态
          • •数据的改变已经被保存到数据库中。 •改变前的数据已经丢失。 •所有用户可以看到结果。 •锁被释放,其他用户可以操作涉及到的数据。 •所有保存点被释放。
          • 例子
            • •改变数据 DELETE FROM employees WHERE  employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row inserted. •提交改变 COMMIT; Commit complete.
        • 数据回滚后的状态
          • 使用 ROLLBACK 语句可使数据变化失效: •数据改变被取消。 •修改前的数据状态被恢复。 •锁被释放。 DELETE FROM copy_emp; 22 rows deleted. ROLLBACK; Rollback complete.
  • 约束
    • •作用范围：   ①列级约束只能作用在一个列上   ②表级约束可以作用在多个列上（当然表级约束也           可以作用在一个列上） •定义方式：列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。 •非空(not null) 约束只能定义在列上
    • 约束定义
      • CREATE TABLE [schema.]table       (column datatype [DEFAULT expr]   [column_constraint],   ...   [table_constraint][,...]); CREATE TABLE employees(          employee_id  NUMBER(6),            first_name   VARCHAR2(20),          ...          job_id       VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk                PRIMARY KEY (EMPLOYEE_ID));
      • 表级约束
        • column [CONSTRAINT constraint_name] constraint_type,
      • 列级约束
        • column,... [CONSTRAINT constraint_name] constraint_type   (column, ...),
      • NOT NULL约束
        • p
        • 规则
          • 只能定义在列级: CREATE TABLE employees(     employee_id    NUMBER(6),     last_name      VARCHAR2(25) NOT NULL,     salary         NUMBER(8,2),     commission_pct NUMBER(2,2),     hire_date      DATE CONSTRAINT emp_hire_date_nn                    NOT NULL, ...
      • UNIQUE约束
        • p
        • 规则
          • 可以定义在表级或列级: CREATE TABLE employees(     employee_id      NUMBER(6),     last_name        VARCHAR2(25) UNIQUE,     email            VARCHAR2(25),     salary           NUMBER(8,2),     commission_pct   NUMBER(2,2),     hire_date        DATE NOT NULL, ...      CONSTRAINT emp_email_uk UNIQUE(email)); 可以声明在email后面：CONSTRAINT emp_email_uk UNIQUE,也可以如此末处声明。
      • PRIMARY KEY 约束
        • 规则
          • 可以定义在表级或列级: CREATE TABLE   departments(     department_id        NUMBER(4),     department_name      VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL,     manager_id           NUMBER(6),     location_id          NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
      • FOREIGN KEY 约束
        • 规则
          • 可以定义在表级或列级: CREATE TABLE employees(     employee_id      NUMBER(6),     last_name        VARCHAR2(25) NOT NULL,     email            VARCHAR2(25),     salary           NUMBER(8,2),     commission_pct   NUMBER(2,2),     hire_date        DATE NOT NULL, ...     department_id    NUMBER(4),     CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),     CONSTRAINT emp_email_uk UNIQUE(email));
          • FOREIGN KEY 约束的关键字
            • –FOREIGN KEY: 在表级指定子表中的列 –REFERENCES: 标示在父表中的列 –ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除 –ON DELETE SET NULL(级联置空): 子表中相应的列置空
      • CHECK 约束
        • •定义每一行必须满足的条件 ..., salary  NUMBER(2)      CONSTRAINT emp_salary_min              CHECK (salary > 0),...
    • 添加约束
      • 使用 ALTER TABLE 语句: •添加或删除约束,但是不能修改约束 •有效化或无效化约束 •添加 NOT NULL 约束要使用 MODIFY 语句 ALTER TABLE table ADD [CONSTRAINT constraint] type (column); 以create table emp as select * from employees;为例，添加和删除约束 Alter table emp modify(empname varchar2(50) not null);
      • 例子
        • ALTER TABLE     employees ADD CONSTRAINT  emp_manager_fk   FOREIGN KEY(manager_id)   REFERENCES employees(employee_id); Table altered.
    • 删除约束
      • •从表 EMPLOYEES 中删除约束 ALTER TABLE      employees DROP CONSTRAINT  emp_manager_fk; Table altered.
    • 无效化约束
      • •在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。 ALTER TABLE  employees DISABLE CONSTRAINT  emp_emp_id_pk; Table altered.
    • 激活约束
      • ENABLE 子句可将当前无效的约束激活 ALTER TABLE  employees ENABLE CONSTRAINT  emp_emp_id_pk; Table altered. •当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引
    • 查询约束
      • 查询数据字典视USER_CONSTRAINTS SELECT  constraint_name, constraint_type,   search_condition FROM  user_constraints WHERE  table_name = 'EMPLOYEES';
    • 查询定义约束的列
      • 查询数据字典视USER_CONS_COLUMNS SELECT  constraint_name, column_name FROM  user_cons_columns WHERE  table_name = 'EMPLOYEES';
  • 数据库对象
    • 视图
      • 1.视图是一种虚表 2.视图建立在已有表的基础上，视图赖以建立的这些表称为基表 3.向视图提供数据内容的语句为select 语句，可以将视图理解为存储起来的select语句。 4.视图是向用户提供基表数据的另一种表现形式。
      • 创建视图
        • 在 CREARE VIEW 语句中嵌入子查询 CREATE [OR REPLACE] [FORACE|NOFORCE] VIEW view [ (alias[,alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; 子查询可以是复杂的select语句 create or replace view empview as select employee_id emp_id,last_name name ,department_name from employees e,departments d Where e .department_id= d.department_id
        • 简单复杂视图
        • 例子
          • 创建视图举例 CREATE VIEW empvu80 AS SELECT employee_id, last_name,salary FROM employees WHERE department_id=80; View created 描述视图结构 DESCRIBE empvu80
        • 定义列名
          • 创建视图时在子查询中给列定义别名 CREATE VIEW salvu50 AS SELECT emploee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id =50; View created 在选择视图中的列时应使用别名
        • 创建复杂视图
          • CREATE VIEW dept_sum_vu (name,minsal,maxsal,avgsal) AS SELECT d.department_name,MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employeees e, departments d WHERE e.department_id =d.department_id GROP BY d.department_name; View created.
      • 查询视图
        • SELECT * FROM salvu50；
      • 修改视图
        • 使用CREATE OR REPLACE VIEW 子句修改视图 CREATE OR REPLACE VIEW empvu80 （id_number, name，sal，department_id) AS SELECT employee_id,first_name || ' ' || last_name, salary,department_id FROM enployees WHERE department_id=80; View created CREATE VIEW 子句中各列的别名应和子查询中各列相对应
      • 删除视图
        • 删除视图只是删除视图的定义，并非删除基表的数据 DROP VIEW view； DROP VIEW empvu80； View dropped。
      • 屏蔽 DML 操作
        • 可以使用 WITH READ ONLY 选项屏蔽对视图的DML操作 任何 DML操作都会返回一个Oracle server 错误
        • 例子
          • CREATE OR REPLACE VIEW empvu10 (employee_number,employee_name, job_title) AS SELECT employee_id,last_name,job_id FROM employees WHERE department_id=10 WITH READ ONLY; View created.
      • 使用DML规则
        • 可以在简单视图中执行 DML 操作 当视图定义中包含以下元素之一时不能使用delete： 组函数 GROP BY 子句 DISTINCT 关键字 ROWNUM 伪列 create or replace view sal_view as select avg(salary) avg_sal from employees group by department_id
      • 使用DML规则
        • 当视图定义中包含以下元素之一时不能使用insert： 组函数 GROP BY 子句 DISTINCT 关键字 ROWNUM 伪列 列的定义为表达式 表中非空的列在视图定义中未包括
      • top -N 分析
        • top -N 分析查询一个列中最大或最小的n个值： 销售量最高的十种产品是什么 销售量最差的十种产品是什么 最大和最小的值的集合是 top_N 分析所关心的
        • 语法
          • 查询最大的几个值的 top_N 分析 SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top_N_column) WHERE ROWNUM <= N; 注意 对 ROWNUM 只能使用< 或 <=,而用=, > , >=都将不能返回任何数据
        • 例题
          • 查询工资最高的三名员工 SELECT ROWNUM as RANK, last_name,salary FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <=3;
    • 序列
      • 序列: 可供多个用户用来产生唯一数值的数据库对象 •自动提供唯一的数值 •共享对象 •主要用于提供主键值 •将序列值装入内存可以提高访问效率
      • 语法
        • CREATE SEQUENCE sequence        [INCREMENT BY n]  --每次增长的数值        [START WITH n]    --从哪个值开始        [{MAXVALUE n | NOMAXVALUE}]        [{MINVALUE n | NOMINVALUE}]        [{CYCLE | NOCYCLE}]     --是否需要循环        [{CACHE n | NOCACHE}];  --是否缓存登录
      • 创建序列
        • •创建序列 DEPT_DEPTID_SEQ为表 DEPARTMENTS 提供主键 •不使用 CYCLE 选项 CREATE SEQUENCE dept_deptid_seq                 INCREMENT BY 10                 START WITH 120                 MAXVALUE 9999                 NOCACHE                 NOCYCLE; Sequence created.
      • 查询序列
        • •查询数据字典视图 USER_SEQUENCES 获取序列定义信息 SELECT  sequence_name, min_value, max_value,   increment_by, last_number FROM  user_sequences; •如果指定NOCACHE 选项，则列LAST_NUMBER 显示序列中下一个有效的值
      • NEXTVAL 和 CURRVAL 伪例
        • •NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用 •CURRVAL 中存放序列的当前值 •NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。
        • 例子
          • INSERT INTO departments(department_id,             department_name, location_id) VALUES      (dept_deptid_seq.NEXTVAL,             'Support', 2500); 1 row created. •序列 DEPT_DEPTID_SEQ 的当前值 SELECT  dept_deptid_seq.CURRVAL FROM  dual;
      • 使用序列
        • •将序列值装入内存可提高访问效率 •序列在下列情况下出现裂缝: –回滚 –系统异常 –多个表同时使用同一序列 •如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值
      • 修改序列
        • 修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存 ALTER SEQUENCE dept_deptid_seq                INCREMENT BY 20                MAXVALUE 999999                NOCACHE                NOCYCLE; Sequence altered.
        • 注意事项
          • •必须是序列的拥有者或对序列有 ALTER 权限 •只有将来的序列值会被改变 •改变序列的初始值只能通过删除序列之后重建序列的方法实现
      • 删除序列
        • •使用 DROP SEQUENCE 语句删除序列 •删除之后，序列不能再次被引用 DROP SEQUENCE dept_deptid_seq; Sequence dropped.
    • 索引
      • 索引: •一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中 •索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度 •索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引 •在删除一个表时,所有基于该表的索引会自动被删除 •通过指针加速 Oracle 服务器的查询速度 •通过快速定位数据的方法，减少磁盘 I/O
      • 创建索引
        • •自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引 •手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
        • 语法
          • •在一个或多个列上创建索引 CREATE INDEX index ON table (column[, column]...); •在表 EMPLOYEES的列 LAST_NAME 上创建索引 CREATE INDEX   emp_last_name_idx ON   employees(last_name); Index created.
        • 什么时候创建
          • 以下情况可以创建索引: •列中数据值分布范围很广 •列经常在 WHERE 子句或连接条件中出现 •表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
        • 什么时候不要创建
          • 下列情况不要创建索引: •表很小 •列不经常作为连接条件或出现在WHERE子句中 •查询的数据大于2%到4% •表经常更新 1.Desc emp; 2.Create index name_index on emp(name); •索引不需要用，只是说我们在用name进行查询的时候，速度会更快。当然查的速度快了，插入的速度就会慢。因为插入数据的同时，还需要维护一个索引。
      • 查询索引
        • •可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息 SELECT  ic.index_name, ic.column_name,   ic.column_position col_pos,ix.uniqueness FROM  user_indexes ix, user_ind_columns ic WHERE  ic.index_name = ix.index_name AND  ic.table_name = 'EMPLOYEES';
      • 删除索引
        • •使用DROP INDEX 命令删除索引 DROP INDEX index; •删除索引UPPER_LAST_NAME_IDX DROP INDEX upper_last_name_idx; Index dropped. •只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引 •删除操作是不可回滚的
    • 同义词
      • synonym
        • 使用同义词访问相同的对象: •方便访问其它用户的对象 •缩短对象名字的长度 CREATE [PUBLIC] SYNONYM synonym FOR    object; 1.CREATE SYNONYM e FOR employees; 2.select * from e;
      • 创建和删除同义词
        • •为视图DEPT_SUM_VU 创建同义词 CREATE SYNONYM  d_sum FOR  dept_sum_vu; Synonym Created. •删除同义词 DROP SYNONYM d_sum; Synonym dropped.
• PL/SQL