Oracle9i培训胶片中文版-12.基本的DDL和DML介绍-caith.ppt

基本的DML和DDL介绍,1.0,目标,通过这部分课程的学习,您将了解以下内容 : Select 语句的功能 执行基本的Select语句 描述 DML 语句 在表中插入数据行 在表中修改数据行 从表中删除数据行 合并表中的数据行 控制事务处理,SELECT语句的功能,Selection,Projection,Table 1,Table 2,Table 1,Table 1,Join,基础SELECT 语句语法,SELECT *|DISTINCT column|expression alias,. FROM table;,SELECT * FROM departments;,选择所有的列,选择指定的列,SELECT department_id, location_id FROM departments;,如何写SQL语句,SQL 语句不区分大小写 关键字不能分行隔开 条件通常被分行放置 缩进被用来提高语句的可读性,算术表达式,通过使用算术操作符来表达数值型和日期型数据.,Operator + - * /,描述 加 Add 减 Subtract 乘 Multiply 除 Divide,使用算术操作符,SELECT last_name, salary, salary + 300 FROM employees;,算术操作符优先级,乘除的优先级高于加减. 相同优先级从左到右依次处理. 括号被要求优先执行.,操作优先级,SELECT last_name, salary, 12*salary+100 FROM employees;,使用圆括号,SELECT last_name, salary, 12*(salary+100) FROM employees;,定义空值,空值是一种不可用、不被指派、未知、不确定的值 空值不同于零或一个空格,SELECT last_name, job_id, salary, commission_pct FROM employees;,SELECT last_name, 12*salary*commission_pct FROM employees;,在算术操作中的空值,算术操作中如有空值，运算结果返回为空.,使用列的别名,SELECT last_name “Name“, salary*12 “Annual Salary“ FROM employees;,SELECT last_name AS name, commission_pct comm FROM employees;,使用连接符,SELECT last_name|job_id AS “Employees“ FROM employees;,使用字符串,SELECT last_name | is a |job_id AS “Employee Details“ FROM employees;,重复的行,查询结果缺省显示所有的行，包括重复的行.,SELECT department_id FROM employees;,排除重复的行,SELECT DISTINCT department_id FROM employees;,使用SQL语句限制显示的行,“retrieve all employees in department 90”,EMPLOYEES,限制挑选出来的行,SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s);,使用WHERE语句限制返回的行 WHERE语句应该写在FROM语句之后,使用 WHERE 条件,SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;,字符串和日期值,都使用单引号 字符串值区分大小写，日期值需注意格式,SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen;,比较条件,操作符 = = ,含义 等于 大于 大于或等于 小于 小于或等于 不等于,SELECT last_name, salary FROM employees WHERE salary = 3000;,使用比较条件,其它的比较条件,操作符 BETWEEN .AND. IN(set) LIKE IS NULL,含义 两个值之间 匹配值列表中值 匹配一个字符样式 空值,使用 BETWEEN 条件,可以使用该条件显示某范围内的值.,SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;,Lower limit,Upper limit,SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);,使用 IN 条件,满足成员列表中的条件.,使用 LIKE 条件,搜索条件能包括两种模式匹配类型: % 表示许多字符 _ 表示一个字符,SELECT first_name FROM employees WHERE first_name LIKE S%;,可混合两种模式匹配类型,使用 LIKE 条件,SELECT last_name FROM employees WHERE last_name LIKE _o%;,使用 NULL 条件,测验空值使用 IS NULL 条件.,SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;,逻辑条件,Operator AND OR NOT,含义 两部分条件都返回真值则结果返回真值 两部分条件中有任意一部分返回真值则结果返回真值 如果条件返回假则结果返回真值,使用 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%;,满足任意一个条件即可返回真值,SELECT last_name, job_id FROM employees WHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP);,使用 NOT 操作符,优先规则,使用括号可不遵照上述优先规则.,优先级别 操作符 1 算术操作符 2 连接操作符 3 比较操作符 4 IS NOT NULL, LIKE, NOT IN 5 NOT BETWEEN 6 NOT 逻辑条件 7 AND逻辑条件 8 OR 逻辑条件,SELECT last_name, job_id, salary FROM employees WHERE job_id = SA_REP OR job_id = AD_PRES AND salary 15000;,优先规则,SELECT last_name, job_id, salary FROM employees WHERE (job_id = SA_REP OR job_id = AD_PRES) AND salary 15000;,优先规则,使用括号,SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;,ORDER BY 条件,带 ORDER BY 条件的排序 ASC: 升序，缺省 DESC: 降序 ORDER BY 条件在Select语句最后,降序排列,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;,按ORDER BY 列表中的列名顺序依次排序.,SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;,多列排序,数据操作语句,一条 DML 语句在下列情况下被执行: 向表中增加新的数据行 修改表中存在的数据 从表中删除数据 一个事务由DML语句集合形成的逻辑工作单元构成.,向表中增加新的数据行,DEPARTMENTS,New row,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 departments VALUES (100, Finance, NULL, NULL); 1 row created.,INSERT INTO departments (department_id, department_name ) VALUES (30, Purchasing); 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, 100); 1 row created.,插入特殊的值,SYSDATE 函数记录当前的日期和时间.,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.,插入特殊的日期型值,增加一行数据： 验证：,带子查询的INSERT 语句 字段的数据类型必须匹配,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.,从另外一个表中复制行,修改表中数据,EMPLOYEES,修改 EMPLOYEES 表中的某些行.,UPDATE 语句语法,可一次性修改多行,UPDATE table SET column = value , column = value, . WHERE condition;,UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated.,在WHERE条件中指定需要修改的行. 修改表中所有的行.,修改表中数据,UPDATE copy_emp SET department_id = 110; 22 rows updated.,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 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.,基于另外表SELECT得到的值修改数据,使用子查询实现：,UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found,UPDATE employees SET department_id = 55 WHERE department_id = 110;,修改行时: 完整性约束错误,Department number 55 does not exist,从 DEPARTMENTS 表中删除一行.,从表中删除一行,DEPARTMENTS,DELETE 语句语法,DELETE FROM table WHERE condition;,通过WHERE条件指定删除的行. 省略 WHERE 条件，所有的行都将被删除.,从表中删除多行,DELETE FROM departments WHERE department_name = Finance; 1 row deleted.,DELETE FROM copy_emp; 22 rows deleted.,DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE %Public%); 1 row deleted.,基于另外表SELECT得到的值删除数据,通过子查询语句从另外表中得到值作为删除条件.,删除行时: 完整性约束错误,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,不能删除被另外一个表作为外键的主键对应的行.,在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.,在INSERT 语句中使用的子查询,验证结果,SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50;,在DML语句中使用 WITH CHECK OPTION 关键字,WITH 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 violation,使用显性的缺省值,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 语句执行时开始 当下列任意事件发生时结束: 提交或回滚语句执行 一条DDL 执行(自动提交) 系统崩溃,COMMIT 和 ROLLBACK 语句的优势,保证数据一致性 在改变永久生效之前预览改变 可以将一些操作按逻辑相关性分组,控制事务,ROLLBACK to SAVEPOINT B,ROLLBACK to SAVEPOINT A,ROLLBACK,UPDATE. SAVEPOINT update_done; Savepoint created. INSERT. ROLLBACK TO update_done; Rollback complete.,回滚改变到一个标记,在当前的事务中可通过 SAVEPOINT 语句创建标记. ROLLBACK TO SAVEPOINT 语句可回滚到标记.,自动的 commit 发生在 : DDL 语句执行,隐性的事务处理,COMMIT 或 ROLLBACK前数据状态,数据可恢复到原先状态 当前用户通过SELECT 看到DML操作后数据的结果 其它用户不能SELECT 看到当前用户DML操作后数据的结果. 受影响的行可能被锁，其它用户不能改变受影响的行.,COMMIT后数据状态,被改变的数据在数据库中永久改变. 前面的数据状态信息被永久丢失. 所有用户将看到同样的数据结果. 受影响的行锁被释放; 这些行能被其它用户使用.,COMMIT; Commit compl