基本的DML与DDL综合介绍

1、基本的DML和DDL介绍1.0目标通过这部分课程的学习,您将了解以下内容 :Select 语句的功能执行基本的Select语句描述 DML 语句在表中插入数据行在表中修改数据行从表中删除数据行合并表中的数据行控制事务处理SELECT语句的功能SelectionProjectionTable 1Table 2Table 1Table 1Join基础SELECT 语句语法SELECT*|DISTINCT column|expression alias,.FROMtable; SELECT *FROM departments;选择所有的列 选择指定的列SELECT department_id, lo

2、cation_idFROM departments;如何写SQL语句SQL 语句不区分大小写关键字不能分行隔开条件通常被分行放置缩进被用来提高语句的可读性算术表达式通过使用算术操作符来表达数值型和日期型数据.Operator+-* / 描述加 Add减 Subtract 乘 Multiply 除 Divide 使用算术操作符SELECT last_name, salary, salary + 300FROM employees;算术操作符优先级乘除的优先级高于加减.相同优先级从左到右依次处理.括号被要求优先执行.*/+_ 操作优先级SELECT last_name, salary, 12*sa

3、lary+100FROM employees; 使用圆括号SELECT last_name, salary, 12*(salary+100)FROM employees; 定义空值空值是一种不可用、不被指派、未知、不确定的值空值不同于零或一个空格SELECT last_name, job_id, salary, commission_pctFROM employees; SELECT last_name, 12*salary*commission_pctFROM employees;在算术操作中的空值算术操作中如有空值，运算结果返回为空. 使用列的别名SELECT last_name Name

4、, salary*12 Annual SalaryFROM employees;SELECT last_name AS name, commission_pct commFROM employees;使用连接符SELECTlast_name|job_id AS EmployeesFROM employees;使用字符串SELECT last_name| is a |job_id AS Employee DetailsFROM employees;重复的行查询结果缺省显示所有的行，包括重复的行.SELECT department_idFROM employees; 排除重复的行SELECT DI

5、STINCT department_idFROM employees;使用SQL语句限制显示的行“retrieve allemployeesin department 90”EMPLOYEES限制挑选出来的行SELECT*|DISTINCT column|expression alias,.FROMtableWHEREcondition(s);使用WHERE语句限制返回的行WHERE语句应该写在FROM语句之后使用 WHERE 条件SELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department

6、_id = 90 ;字符串和日期值都使用单引号字符串值区分大小写，日期值需注意格式SELECT last_name, job_id, department_idFROM employeesWHERE last_name = Whalen;比较条件操作符= = =含义等于大于 大于或等于 小于 小于或等于不等于SELECT last_name, salaryFROM employeesWHERE salary =10000AND job_id LIKE %MAN%;使用 OR 操作符SELECT employee_id, last_name, job_id, salaryFROM employe

7、esWHERE salary = 10000OR job_id LIKE %MAN%;满足任意一个条件即可返回真值SELECT last_name, job_idFROM employeesWHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP);使用 NOT 操作符优先规则使用括号可不遵照上述优先规则. 优先级别 操作符 1算术操作符 2连接操作符3比较操作符4IS NOT NULL, LIKE, NOT IN5NOT BETWEEN6NOT 逻辑条件7AND逻辑条件8OR 逻辑条件SELECT last_name, job_id, salaryFROM

8、 employeesWHERE job_id = SA_REPOR job_id = AD_PRESAND salary 15000;优先规则SELECT last_name, job_id, salaryFROM employeesWHERE (job_id = SA_REPOR job_id = AD_PRES)AND salary 15000;优先规则使用括号SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;ORDER BY 条件带 ORDER BY 条件的排序ASC:

9、升序，缺省DESC: 降序ORDER BY 条件在Select语句最后降序排列SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;使用列别名排序SELECT employee_id, last_name, salary*12 annsalFROM employeesORDER BY annsal;按ORDER BY 列表中的列名顺序依次排序.SELECT last_name, department_id, salaryFROM employeesORDER BY dep

10、artment_id, salary DESC;多列排序数据操作语句一条 DML 语句在下列情况下被执行:向表中增加新的数据行修改表中存在的数据从表中删除数据一个事务由DML语句集合形成的逻辑工作单元构成.向表中增加新的数据行DEPARTMENTS New rowinsert a new row into the DEPARMENTS tableINSERT 语句语法.INSERT INTOtable (column , column.)VALUES(value , value.);向表中插入新的数据行插入的新行应包括所有的列值.插入的值应按照表中列缺省的顺序排列. 如插入所有的列, INSE

11、RT 条件中可随意选择是否列出所有列.INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES (70, Public Relations, 100, 1700);1 row created.INSERT INTOdepartmentsVALUES(100, Finance, NULL, NULL);1 row created.INSERT INTOdepartments (department_id, department_name )VALUES(30, Purchasing

12、);1 row created.插入带空值的数据行 隐性的方法: 省略为空的列名.显性的方法: 使用关键字NULL来指定为空的列.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, 1

13、00);1 row created.插入特殊的值SYSDATE 函数记录当前的日期和时间.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.插入特殊的日期型值增加一行数据：验证：带子查询的INSERT 语句字段的数据类型必须匹配INSERT INTO sales_reps(id, name, salary, commission_pc

14、t) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE %REP%;4 rows created.从另外一个表中复制行修改表中数据EMPLOYEES修改 EMPLOYEES 表中的某些行.UPDATE 语句语法可一次性修改多行UPDATEtableSETcolumn = value , column = value, .WHERE condition;UPDATE employeesSET department_id = 70WHERE employee_id = 113

15、;1 row updated.在WHERE条件中指定需要修改的行.修改表中所有的行.修改表中数据UPDATE copy_empSET department_id = 110;22 rows updated.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.使用子查询修改两列的

16、值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.基于另外表SELECT得到的值修改数据使用子查询实现：UPDATE employees *ERROR at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated

17、- parent key not foundUPDATE employeesSET department_id = 55WHERE department_id = 110;修改行时: 完整性约束错误Department number 55 does not exist从 DEPARTMENTS 表中删除一行.从表中删除一行 DEPARTMENTS DELETE 语句语法DELETE FROM tableWHERE condition;通过WHERE条件指定删除的行.省略 WHERE 条件，所有的行都将被删除.从表中删除多行 DELETE FROM departments WHERE depar

18、tment_name = Finance;1 row deleted.DELETE FROM copy_emp;22 rows deleted.DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE %Public%);1 row deleted.基于另外表SELECT得到的值删除数据通过子查询语句从另外表中得到值作为删除条件.删除行时: 完整性约束错误DELETE FROM departmentsWHERE department_i

19、d = 60;DELETE FROM departments *ERROR at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found不能删除被另外一个表作为外键的主键对应的行.在INSERT 语句中使用子查询 INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VA

20、LUES (99999, Taylor, DTAYLOR, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK, 5000, 50);1 row created.在INSERT 语句中使用的子查询验证结果SELECT employee_id, last_name, email, hire_date, job_id, salary, department_idFROM employeesWHERE department_id = 50;在DML语句中使用 WITH CHECK OPTION 关键字WITH CHECK OPTION 关键字禁止改变不在子查询结果中的值.

21、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 violation使用显

22、性的缺省值INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, Engineering, DEFAULT);UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;INSERT中使用缺省值:UPDATE中使用缺省值:数据库事务一个数据库完整事务包含以下情况:构成对数据的一次一致性改变的DML语句（一条或多条）一条DDL 语句数据库事务当第一条 DML SQL 语句执行时开始当下列任意事件发生时结束:提交或

23、回滚语句执行一条DDL 执行(自动提交)系统崩溃COMMIT 和 ROLLBACK 语句的优势保证数据一致性在改变永久生效之前预览改变可以将一些操作按逻辑相关性分组控制事务SAVEPOINT BSAVEPOINT ADELETEINSERTUPDATEINSERTCOMMITTimeTransactionROLLBACK to SAVEPOINT BROLLBACK to SAVEPOINT AROLLBACKUPDATE.SAVEPOINT update_done;Savepoint created.INSERT.ROLLBACK TO update_done;Rollback comple

24、te.回滚改变到一个标记在当前的事务中可通过 SAVEPOINT 语句创建标记.ROLLBACK TO SAVEPOINT 语句可回滚到标记.自动的 commit 发生在 :DDL 语句执行隐性的事务处理COMMIT 或 ROLLBACK前数据状态数据可恢复到原先状态.当前用户通过SELECT 看到DML操作后数据的结果其它用户不能SELECT 看到当前用户DML操作后数据的结果.受影响的行可能被锁，其它用户不能改变受影响的行.COMMIT后数据状态被改变的数据在数据库中永久改变.前面的数据状态信息被永久丢失.所有用户将看到同样的数据结果.受影响的行锁被释放; 这些行能被其它用户使用.COMMIT;Commit complete.改变数据.Commit 改变.DELETE FROM employees