ISSUEISSUE业务与软件产品课程开发室业务与软件产品课程开发室业务与软件产品课程开发室业务与软件产品课程开发室基本的基本的DML和和DDL介绍介绍1.01华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开目标目标通过这部分课程的学习,您将了解以下内容 :lSelect 语句的功能l执行基本的Select语句l描述 DML 语句l在表中插入数据行l在表中修改数据行l从表中删除数据行l合并表中的数据行l控制事务处理2华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT语句的功能语句的功能SelectionProjectionTable 1Table 2Table 1Table 1Join3华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开基础基础SELECT 语句语法语句语法SELECT*|DISTINCT column|expression alias,.FROMtable;4华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT *FROM departments;选择所有的列选择所有的列5华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开选择指定的列选择指定的列SELECT department_id, location_idFROM departments;6华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开如何写如何写SQL语句语句lSQL 语句不区分大小写l关键字不能分行隔开l条件通常被分行放置l缩进被用来提高语句的可读性7华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开算术表达式算术表达式通过使用算术操作符来表达数值型和日期型数据.Operator+-* / 描述描述加加 Add减减 Subtract 乘乘 Multiply 除除 Divide8华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用算术操作符使用算术操作符SELECT last_name, salary, salary + 300FROM employees;9华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开算术操作符优先级算术操作符优先级l乘除的优先级高于加减.l相同优先级从左到右依次处理.l括号被要求优先执行.* */ /+ +_ _10华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开操作优先级操作优先级SELECT last_name, salary, 12*salary+100FROM employees;11华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用圆括号使用圆括号SELECT last_name, salary, 12*(salary+100)FROM employees;12华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开定义空值定义空值l空值是一种不可用、不被指派、未知、不确定的值l空值不同于零或一个空格SELECT last_name, job_id, salary, commission_pctFROM employees;13华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT last_name, 12*salary*commission_pctFROM employees;在算术操作中的空值在算术操作中的空值l算术操作中如有空值，运算结果返回为空.14华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用列的别名使用列的别名SELECT last_name Name, salary*12 Annual SalaryFROM employees;SELECT last_name AS name, commission_pct commFROM employees;15华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用连接符使用连接符SELECTlast_name|job_id AS EmployeesFROM employees;16华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用字符串使用字符串SELECT last_name| is a |job_id AS Employee DetailsFROM employees;17华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开重复的行重复的行查询结果缺省显示所有的行，包括重复的行.SELECT department_idFROM employees;18华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开排除重复的行排除重复的行SELECT DISTINCT department_idFROM employees;19华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用使用SQL语句限制显示的行语句限制显示的行“retrieve allemployeesin department 90”EMPLOYEES20华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开限制挑选出来的行限制挑选出来的行SELECT*|DISTINCT column|expression alias,.FROMtableWHEREcondition(s);l使用WHERE语句限制返回的行lWHERE语句应该写在FROM语句之后21华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用使用 WHERE 条件条件SELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department_id = 90 ;22华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开字符串和日期值字符串和日期值l都使用单引号l字符串值区分大小写，日期值需注意格式SELECT last_name, job_id, department_idFROM employeesWHERE last_name = Whalen;23华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开比较条件比较条件操作符操作符= = =含义含义等于等于大于大于 大于或等于大于或等于 小于小于 小于或等于小于或等于不等于不等于24华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT last_name, salaryFROM employeesWHERE salary =10000AND job_id LIKE %MAN%;33华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用使用 OR 操作符操作符SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary = 10000OR job_id LIKE %MAN%;满足任意一个条件即可返回真值满足任意一个条件即可返回真值34华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT last_name, job_idFROM employeesWHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP);使用使用 NOT 操作符操作符35华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开优先规则优先规则使用括号可不遵照上述优先规则使用括号可不遵照上述优先规则. 优先级别优先级别 操作符操作符 1算术操作符算术操作符 2连接操作符连接操作符3比较操作符比较操作符4IS NOT NULL, LIKE, NOT IN5NOT BETWEEN6NOT 逻辑条件逻辑条件7AND逻辑条件逻辑条件8OR 逻辑条件逻辑条件36华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT last_name, job_id, salaryFROM employeesWHERE job_id = SA_REPOR job_id = AD_PRESAND salary 15000;优先规则优先规则37华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT last_name, job_id, salaryFROM employeesWHERE (job_id = SA_REPOR job_id = AD_PRES)AND salary 15000;优先规则优先规则使用括号使用括号38华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;ORDER BY 条件条件l带 ORDER BY 条件的排序ASC: 升序，缺省DESC: 降序lORDER BY 条件在Select语句最后39华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开降序排列降序排列SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;40华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用列别名排序使用列别名排序SELECT employee_id, last_name, salary*12 annsalFROM employeesORDER BY annsal;41华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开l按ORDER BY 列表中的列名顺序依次排序.SELECT last_name, department_id, salaryFROM employeesORDER BY department_id, salary DESC;多列排序多列排序42华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开数据操作语句数据操作语句l一条 DML 语句在下列情况下被执行:向表中增加新的数据行修改表中存在的数据从表中删除数据l一个事务由DML语句集合形成的逻辑工作单元构成.43华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开向表中增加新的数据行向表中增加新的数据行DEPARTMENTS New rowinsert a new row into the DEPARMENTS table44华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开INSERT 语句语法语句语法.INSERT INTOtable (column , column.)VALUES(value , value.);45华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开向表中插入新的数据行向表中插入新的数据行l插入的新行应包括所有的列值.l插入的值应按照表中列缺省的顺序排列. l如插入所有的列, INSERT 条件中可随意选择是否列出所有列.INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES (70, Public Relations, 100, 1700);1 row created.46华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开INSERT INTOdepartmentsVALUES(100, Finance, NULL, NULL);1 row created.1 row created.INSERT INTOdepartments (department_id, department_name )VALUES(30, Purchasing);1 row created.1 row created.插入带空值的数据行插入带空值的数据行l 隐性的方法: 省略为空的列名.l显性的方法: 使用关键字NULL来指定为空的列.47华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开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.1 row created.插入特殊的值插入特殊的值SYSDATE 函数记录当前的日期和时间.48华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开INSERT INTO employeesVALUES (114, Den, Raphealy, DRAPHEAL, 515.127.4561, TO_DATE(FEB 3, 1999, MON DD, YYYY), AC_ACCOUNT, 11000, NULL, 100, 30);1 row created.1 row created.插入特殊的日期型值插入特殊的日期型值l增加一行数据：l验证：49华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开l带子查询的INSERT 语句l字段的数据类型必须匹配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.4 rows created.从另外一个表中复制行从另外一个表中复制行50华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开修改表中数据修改表中数据EMPLOYEES修改修改 EMPLOYEES 表中的某些行表中的某些行.51华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开UPDATE 语句语法语句语法l可一次性修改多行UPDATEtableSETcolumn = value , column = value, .WHERE condition;52华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开UPDATE employeesSET department_id = 70WHERE employee_id = 113;1 row updated.1 row updated.l在WHERE条件中指定需要修改的行.l修改表中所有的行.修改表中数据修改表中数据UPDATE copy_empSET department_id = 110;22 rows updated.22 rows updated.53华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开UPDATE employeesSET 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.1 row updated.使用子查询修改两列的值使用子查询修改两列的值54华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开UPDATE copy_empSET 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.1 row updated.基于另外表基于另外表SELECT得到的值修改数据得到的值修改数据使用子查询实现：55华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开UPDATE employees *ERROR at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not foundUPDATE employeesSET department_id = 55WHERE department_id = 110;修改行时修改行时: 完整性约束错误完整性约束错误Department number 55 does not exist56华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开从从 DEPARTMENTS 表中删除一行表中删除一行.从表中删除一行从表中删除一行 DEPARTMENTS 57华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开DELETE 语句语法语句语法DELETE FROM tableWHERE condition;58华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开l通过WHERE条件指定删除的行.l省略 WHERE 条件，所有的行都将被删除.从表中删除多行从表中删除多行 DELETE FROM departments WHERE department_name = Finance;1 row deleted.1 row deleted.DELETE FROM copy_emp;22 rows deleted.22 rows deleted.59华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE %Public%);1 row deleted.1 row deleted.基于另外表基于另外表SELECT得到的值删除数据得到的值删除数据通过子查询语句从另外表中得到值作为删除条件.60华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开删除行时删除行时: 完整性约束错误完整性约束错误DELETE FROM departmentsWHERE department_id = 60;DELETE FROM departments *ERROR at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found不能删除被另外一个表作为外键的主键对应的行.61华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开在在INSERT 语句中使用子查询语句中使用子查询 INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, Taylor, DTAYLOR, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK, 5000, 50);1 row created.1 row created.62华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开在在INSERT 语句中使用的子查询语句中使用的子查询l验证结果SELECT employee_id, last_name, email, hire_date, job_id, salary, department_idFROM employeesWHERE department_id = 50;63华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开在在DML语句中使用语句中使用 WITH CHECK OPTION 关键字关键字lWITH CHECK OPTION 关键字禁止改变不在子查询结果中的值.INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION)VALUES (99998, Smith, JSMITH, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK, 5000);INSERT INTO *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation64华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开使用显性的缺省值使用显性的缺省值INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, Engineering, DEFAULT);UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;lINSERT中使用缺省值:lUPDATE中使用缺省值:65华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开数据库事务数据库事务一个数据库完整事务包含以下情况:l构成对数据的一次一致性改变的DML语句（一条或多条）l一条DDL 语句66华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开数据库事务数据库事务l当第一条 DML SQL 语句执行时开始l当下列任意事件发生时结束:提交或回滚语句执行一条DDL 执行(自动提交)系统崩溃67华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开COMMIT 和和 ROLLBACK 语句的优势语句的优势l保证数据一致性l在改变永久生效之前预览改变l可以将一些操作按逻辑相关性分组68华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开控制事务控制事务SAVEPOINT BSAVEPOINT ADELETEINSERTUPDATEINSERTCOMMITTimeTransactionROLLBACK to SAVEPOINT BROLLBACK to SAVEPOINT AROLLBACK69华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开UPDATE.SAVEPOINT update_done;Savepoint created.Savepoint created.INSERT.ROLLBACK TO update_done;Rollback complete.Rollback complete.回滚改变到一个标记回滚改变到一个标记l在当前的事务中可通过 SAVEPOINT 语句创建标记.lROLLBACK TO SAVEPOINT 语句可回滚到标记.70华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开l自动的 commit 发生在 :DDL 语句执行隐性的事务处理隐性的事务处理71华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开COMMIT 或或 ROLLBACK前数据状态前数据状态l数据可恢复到原先状态.l当前用户通过SELECT 看到DML操作后数据的结果l其它用户不能SELECT 看到当前用户DML操作后数据的结果.l受影响的行可能被锁，其它用户不能改变受影响的行.72华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开COMMIT后数据状态后数据状态l被改变的数据在数据库中永久改变.l前面的数据状态信息被永久丢失.l所有用户将看到同样的数据结果.l受影响的行锁被释放; 这些行能被其它用户使用.73华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开COMMIT;Commit complete.Commit complete.l改变数据.lCommit 改变.DELETE FROM employeesWHERE employee_id = 99999;WHERE employee_id = 99999;1 row deleted.1 row deleted.INSERT INTO departments VALUES (290, Corporate Tax, NULL, 1700);1 row inserted.1 row inserted.提交数据提交数据74华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开回滚后数据状态回滚后数据状态将丢弃所有未COMMIT的改变：DELETE FROM copy_emp;22 rows deleted.22 rows deleted.ROLLBACK;Rollback complete.Rollback complete.75华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开读一致性读一致性l读一致性保证数据视图一直保持一致l一个用户所作的改变不会与另外用户的改变相冲突. l读一致性保证对同一数据:读操作不等待写操作写操作不等待读操作76华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开SELECT *FROM userA.employees;执行读一致性执行读一致性UPDATE employeesSET salary = 7000WHERE last_name = Goyal;DatablocksRollbacksegmentschangedand unchanged databefore change“old” dataUser AUser BReadconsistentimage77华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开锁锁Oracle数据库中的锁:l阻止并发事务中破坏性的交互操作l要求不需要用户参与l自动使用低级别受限制的锁l在事务执行期间保留l显性和隐性两种类型78华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开隐性的锁隐性的锁l两种锁的模式:独占型: 排除其它用户共享型: 允许其它用户介入l高级别的并发数据处理:DML: 表共享, 行独占Queries: 没有锁要求DDL: 保护对象定义l锁保留到commit或rollback79华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开总结总结通过这部分课程的学习,您将了解以下内容 :l写一个SELECT语句:从一个表中返回所有的行和列从一个表中返回指定的列使用列别名描述列头l描述 DML 语句l在表中插入数据行l在表中修改数据行l从表中删除数据行l合并表中的数据行l控制事务处理80华为机密，未经许可不得扩散华为机密，未经许可不得扩散文档密级：内部公开文档密级：内部公开练习练习这部分练习包括以下主题:l从不同的表中选出所有数据l执行算术运算并指定相应的列81