Oracle 9i培训胶片中文版-1-2基本的DDL和DML介绍-caith

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

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

3、st_name,salary, 12*salary+100FROMemployees; 使用圆括括号SELECTlast_name,salary, 12*(salary+100)FROMemployees; 定义空值值空值是一一种不可可用、不不被指派派、未知知、不确确定的值值空值不同同于零或或一个空空格SELECTlast_name,job_id, salary,commission_pctFROMemployees; SELECTlast_name,12*salary*commission_pctFROMemployees;在算术操操作中的的空值算术操作作中如有有空值，运算结结果返回回为空.

4、 使用列的的别名SELECTlast_name Name,salary*12 AnnualSalaryFROMemployees;SELECTlast_name AS name, commission_pctcommFROMemployees;使用连接接符SELECTlast_name|job_idASEmployeesFROMemployees;使用字符符串SELECTlast_name|isa |job_idASEmployeeDetailsFROMemployees;重复的行行查询结果果缺省显显示所有有的行，包括重重复的行行.SELECTdepartment_idFROMemployee

5、s; 排除重复复的行SELECTDISTINCTdepartment_idFROMemployees;使用SQL语句限制制显示的的行“retrieveallemployeesindepartment 90”EMPLOYEES限制挑选选出来的的行SELECT*|DISTINCTcolumn|expressionalias,.FROMtableWHEREcondition(s);使用WHERE语句限限制返回回的行WHERE语句句应该写写在FROM语语句之后后使用WHERE条件SELECTemployee_id, last_name,job_id,department_idFROMemployees

6、WHEREdepartment_id= 90 ;字符串和和日期值值都使用单单引号字符串值值区分大大小写，日期值值需注意意格式SELECTlast_name,job_id, department_idFROMemployeesWHERElast_name =Whalen;比较条件件操作符=含义等于大于 大于或等等于小于 小于或等等于不等于SELECTlast_name,salaryFROMemployeesWHEREsalary =10000ANDjob_id LIKE%MAN%;使用OR操作符SELECTemployee_id, last_name,job_id,salaryFROMemplo

7、yeesWHEREsalary = 10000ORjob_id LIKE%MAN%;满足任意意一个条条件即可可返回真真值SELECTlast_name,job_idFROMemployeesWHEREjob_idNOTIN(IT_PROG,ST_CLERK,SA_REP);使用NOT操作符优先规则则使用括号号可不遵遵照上述述优先规规则.优先级别别操操作作符1算术操作作符2连接操作作符3比较操作作符4ISNOTNULL,LIKE,NOTIN5NOTBETWEEN6NOT逻辑条件件7AND逻辑条件件8OR逻辑条件件SELECTlast_name,job_id, salaryFROMemployee

8、sWHEREjob_id =SA_REPORjob_id =AD_PRESANDsalary 15000;优先规则则SELECTlast_name,job_id, salaryFROMemployeesWHERE(job_id= SA_REPORjob_id =AD_PRES)ANDsalary 15000;优先规则则使用括号号SELECTlast_name, job_id,department_id,hire_dateFROMemployeesORDERBYhire_date;ORDERBY条件带ORDERBY条件的排排序ASC:升序，缺缺省DESC:降序ORDERBY条件在Select语句

9、最后后降序排列列SELECTlast_name, job_id,department_id,hire_dateFROMemployeesORDERBYhire_dateDESC;使用列别别名排序序SELECTemployee_id, last_name,salary*12annsalFROMemployeesORDERBYannsal;按ORDERBY列表中的的列名顺顺序依次次排序.SELECTlast_name,department_id,salaryFROMemployeesORDERBYdepartment_id,salaryDESC;多列排序序数据操作作语句一条DML语句在下下列情况况

10、下被执执行:向表中增增加新的的数据行行修改表中中存在的的数据从表中删删除数据据一个事务务由DML语句集合合形成的的逻辑工工作单元元构成.向表中增增加新的的数据行行DEPARTMENTSNewrowinsert a new row into the DEPARMENTS tableINSERT语句语法法.INSERTINTOtable(column, column.)VALUES(value, value.);向表中插插入新的的数据行行插入的新新行应包包括所有有的列值值.插入的值值应按照照表中列列缺省的的顺序排排列.如插入所所有的列列,INSERT条件中可可随意选选择是否否列出所所有列.INSE

11、RTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70, PublicRelations,100,1700);1 rowcreated.INSERTINTOdepartmentsVALUES(100, Finance,NULL,NULL);1 rowcreated.INSERTINTOdepartments(department_id,department_name)VALUES(30,Purchasing);1 rowcreated.插入带空空值的数数据行隐性的方方法:省省略为为空的列列

12、名.显性的方方法:使使用关关键字NULL来指定为为空的列列.INSERTINTOemployees(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 rowcreated.插入特殊殊的值SYSDATE函数记录录当前的的日期和和时间.INSERTIN

13、TOemployeesVALUES(114,Den,Raphealy,DRAPHEAL,515.127.4561,TO_DATE(FEB3,1999, MONDD, YYYY),AC_ACCOUNT, 11000,NULL,100, 30);1 rowcreated.插入特殊殊的日期期型值增加一行行数据：验证：带子查询询的INSERT语句字段的数数据类型型必须匹匹配INSERTINTOsales_reps(id,name,salary, commission_pct)SELECTemployee_id, last_name,salary,commission_pctFROMemployeesW

14、HEREjob_id LIKE%REP%;4 rowscreated.从另外一一个表中中复制行行修改表中中数据EMPLOYEES修改EMPLOYEES表中的某某些行.UPDATE语句语法法可一次性性修改多多行UPDATEtableSETcolumn=value,column=value,.WHEREcondition;UPDATEemployeesSETdepartment_id= 70WHEREemployee_id=113;1 rowupdated.在WHERE条件中指指定需要要修改的的行.修改表中中所有的的行.修改表中中数据UPDATEcopy_empSETdepartment_id=1

15、10;22rows updated.UPDATEemployeesSETjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id =205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id =205)WHEREemployee_id=114;1 rowupdated.使用子查查询修改改两列的的值UPDATEcopy_empSETdepartment_id=(SELECTdepartment_idFROM employeesWHEREemployee_id= 100)WHEREjob_id=(SELEC

16、Tjob_idFROM employeesWHEREemployee_id= 200);1 rowupdated.基于另外外表SELECT得到的值值修改数数据使用子查查询实现现：UPDATEemployees*ERRORatline 1:ORA-02291: integrityconstraint (HR.EMP_DEPT_FK)violated-parentkey notfoundUPDATEemployeesSETdepartment_id= 55WHEREdepartment_id= 110;修改行时时:完完整性约约束错误误Department number55does notexist

17、从DEPARTMENTS表中删除除一行.从表中删删除一行行DEPARTMENTSDELETE语句语法法DELETEFROMtableWHEREcondition;通过WHERE条件指定定删除的的行.省略WHERE条件，所所有的行行都将被被删除.从表中删删除多行行DELETEFROMdepartmentsWHEREdepartment_name =Finance;1 rowdeleted.DELETEFROMcopy_emp;22rows deleted.DELETEFROMemployeesWHEREdepartment_id=(SELECT department_idFROMdepartme

18、ntsWHEREdepartment_name LIKE%Public%);1 rowdeleted.基于另外外表SELECT得到的值值删除数数据通过子查查询语句句从另外外表中得得到值作作为删除除条件.删除行时时:完完整性约约束错误误DELETEFROMdepartmentsWHEREdepartment_id =60;DELETEFROMdepartments*ERRORatline 1:ORA-02292: integrityconstraint (HR.EMP_DEPT_FK)violated-childrecordfound不能删除除被另外外一个表表作为外外键的主主键对应应的行.在IN

19、SERT语句中使使用子查查询INSERTINTO(SELECT employee_id,last_name,email,hire_date,job_id, salary,department_idFROMemployeesWHEREdepartment_id= 50)VALUES(99999,Taylor,DTAYLOR,TO_DATE(07-JUN-99,DD-MON-RR),ST_CLERK,5000,50);1 rowcreated.在INSERT语句中使使用的子子查询验证结果果SELECTemployee_id, last_name,email, hire_date,job_id, s

20、alary,department_idFROMemployeesWHEREdepartment_id= 50;在DML语句中使使用WITH CHECK OPTION关键字WITH CHECK OPTION关键字禁禁止改变变不在子子查询结结果中的的值.INSERTINTO(SELECTemployee_id,last_name,email,hire_date, job_id,salaryFROMemployeesWHEREdepartment_id= 50WITH CHECK OPTION)VALUES(99998,Smith,JSMITH,TO_DATE(07-JUN-99,DD-MON-RR

21、),ST_CLERK,5000);INSERTINTO*ERRORatline 1:ORA-01402: viewWITHCHECKOPTIONwhere-clause violation使用显性性的缺省省值INSERTINTOdepartments(department_id,department_name,manager_id)VALUES(300,Engineering,DEFAULT);UPDATEdepartmentsSETmanager_id=DEFAULTWHEREdepartment_id =10;INSERT中使用缺缺省值:UPDATE中使用缺缺省值:数据库事事务一个数据据库

22、完整整事务包包含以下下情况:构成对数数据的一一次一致致性改变变的DML语句（一一条或多多条）一条DDL语句数据库事事务当第一条条DMLSQL语句执行行时开始始当下列任任意事件件发生时时结束:提交或回回滚语句句执行一条DDL执行(自自动提交交)系统崩溃溃COMMIT和ROLLBACK语句的优优势保证数据据一致性性在改变永永久生效效之前预预览改变变可以将一一些操作作按逻辑辑相关性性分组控制事务务SAVEPOINT BSAVEPOINT ADELETEINSERTUPDATEINSERTCOMMITTimeTransactionROLLBACKtoSAVEPOINTBROLLBACKtoSAVEPO

23、INTAROLLBACKUPDATE.SAVEPOINTupdate_done;Savepointcreated.INSERT.ROLLBACKTOupdate_done;Rollbackcomplete.回滚改变变到一个个标记在当前的的事务中中可通过过SAVEPOINT语句创建建标记.ROLLBACKTOSAVEPOINT语句可回回滚到标标记.自动的commit发生在:DDL语句执行行隐性的事事务处理理COMMIT或ROLLBACK前数据状状态数据可恢恢复到原原先状态态.当前用户户通过SELECT看到DML操作后数数据的结结果其它用户户不能SELECT看到当前前用户DML操作后数数据的结结果.受影响的的行可能能被锁，其它用用户不能能改变受受影响的的行.COMMIT后数据状状态被改变的的数据在在数据库库中永久久改变.前面的数数据状态态信息被被永久丢丢失.所有用户户将看到到同样的的数据结结果.受影响的的行锁被被释放; 这些些行能被被其它用用户使用用.COMMIT;Commitcomplete.改变数据据.Commit改变.DELETEFROMemployeesWHEREemployee_id=99999;1 rowdeleted.INSERTINTOdepartmentsVALUES(2