Oracle SQL基础培训

OracleSQL基础培训,李建国2013.03.16,目录,课程介绍,DML基础,SQL介绍,DCL基础,DDL基础,TL基础,课程介绍,教程概述本教程假设读者已了解关系型数据库基本原理，明白表、视图、主键、索引、外键、约束、关联等基本概念本教程定位是OracleSQL简明、实用教程，偏向于SQL开发，若进一步学习Oracle数据库设计，请参阅其他教程或书籍文档课程目标是完成课程后可进行项目中大部分OracleSQL开发适用对象学习过标准SQL，未使用过Oracle数据库的读者适用过SQLServer或其他数据库，未使用过Oracle数据库的读者,目录,课程介绍,DML基础,SQL介绍,DCL基础,DDL基础,TL基础,SQL介绍,概述SQL是StructuredQueryLanguage简写SQL用于组织、访问和处理数据库中的数据标准的计算机语言SQL具有第四代语言的特征，程序关注要告诉DBMS要得做什么操作，及其相应条件，至于如何得到数据，如何使用索引，如何关联多表，由DBMS完成分类DataDefinitionLanguage(DDL数据定义语言)DataManipulationLanguage(DML数据操作语言)DataControlLanguage(DCL数据控制语言)TransactionLanguage(TL数据事务语言),目录,课程介绍,DML基础,SQL介绍,DCL基础,DDL基础,TL基础,DML基础,算术运算符关系运算符逻辑运算符SELECT语句:查询数据集合运算符:复合查询SUBQUERY子查询LEVEL、PRIOR与CONNECTBYSTARTWITHROWNUM与分页查询INSERT语句:插入数据UDATE语句:更新数据DELETE语句:删除数据SELECT与INSERT、UPDATE、DELETE,DML基础,算术运算符号+加1+1-减2-1*乘2*3/除4/2|字符连接Beijing|2008,DML基础,关系运算符号=等于，1=1不等于，21大于，21=大于等于，2=1(SelectsalaryFromemployeesplWherepl.employee_number=1002);,DML基础-SUBQUERY子查询,示例查询有下采购订单的员工(不相关子查询)Select*Fromemployeesem,(SelectDistinctemployee_numberFromorders)vWhereem.employee_number=v.employee_number查询所有部门及其部门员工工资总和(相关子查询)Selectdp.department_id,dp.department_name,(SelectSum(salary)FromemployeesplWherepl.department_id=dp.department_id)total_salaryFromdepartmentsdp,DML基础-SUBQUERY子查询,示例查询有下采购订单的员工(相关子查询)Select*FromemployeesemWhereem.employee_numberIn(SelectDistinctemployee_numberFromorders)查询有下采购订单的员工(相关子查询)Select*FromemployeesemWhereExists(Select1FromordersodWhereod.employee_number=em.employee_number),DML基础-level与CONNECTBYSTARTWITH,背景应用系统长通常有类似菜单、产品类别等有上下级关系的树形结构数据通常会设计为ID、PARENT_ID结构,DML基础-level与CONNECTBYSTARTWITH,原理Oracle提供CONNECTBYSTARTWITH用于类似结构查询提供关键字level在CONNECTBYSTARTWITH查询返回数据所在树形结构层次，level值越大表示层级越深示例查询“订单管理”及其下属菜单，按树形结构展现，使用lpad函数辅助效果更明显(缩进)，lpad函数参考后续教程介绍,DML基础-ROWNUM与分页查询,背景应用系统在数据量大的时候，通常需要将数据分页显示,DML基础-ROWNUM与分页查询,原理Oracle提供关键字rownum，在SELECT语句查询返回数据中表示是第几行示例查询“订单管理”及其下属菜单，按树形结构展现，使用lpad函数辅助效果更明显，并读取第1至第7行数据，选定部分要排好序,DML基础,INSERT语句基本INSERT语句INSERT与SELECT,DML基础-INSERT-基本INSERT语句,用途将数据插入到指定表中语法INSERTINTOtable(column-1,column-2,.column-n)VALUES(value-1,value-2,.value-n);示例InsertIntosuppliers(supplier_id,supplier_name,city,type)Values(53553,TestInsertSupplier,Detroit,Cooking);,DML基础-INSERT-INSERT与SELECT,用途将数据插入到指定表中语法INSERTINTOtable(column-1,column-2,.column-n)SELECTColumn-1,column-2.column-nFROMTablesWhEREpredicates;示例将所在城市为Newark的客户生成供应商，其中供应商supplier_id对应为客户account_no，supplier_name对应为customer_nameINSERTINTOsuppliers(supplier_id,supplier_name)SELECTaccount_no,customer_nameFROMcustomersWHEREcity=Newark;,DML基础-INSERT-INSERT与SELECT,示例INSERTINTOclients(client_id,client_name,client_type)SELECTsupplier_id,supplier_name,advertisingFROMsuppliersspWHEREnotexists(select*fromclientsclwherecl.client_id=sp.supplier_id);,DML基础,UPDATE语句基本UPDATE语句,DML基础-UPDATE语句,用途UPDATE用于更新指定表符合条件的数据;语法UPDATEtableSETcolumn1=value1,column2=value2WHEREpredicates;示例将名称为Smith的供应商名称更新为HP，同时在备注记录旧名称UPDATEsuppliersSETsupplier_name=HP,commets=SmithWHEREsupplier_name=Smith;,DML基础-UPDATE语句,用途更新根据客户生产的供应商(客户account_no生成为supplier_id)的备注为相应客户的备注UpdatesuppliersspSmets=(SmetsFromcustomersccWherecc.account_no=sp.supplier_id)WhereExists(SmetsFromcustomersccWherecc.account_no=sp.supplier_id);,DML基础,DELETE语句DELETE语句,DML基础-DELETE语句,用途DELETE用于将数据从指定表删除;语法DELETEFROMtablesWHEREpredicates;示例删除供应商为DELETEFROMsuppliersWHEREsupplier_name=Smath;删除没有采购订单的供应商DeleteFromsuppliersspWhereNotExists(Selectod.order_idFromordersodWhereod.supplier_id=sp.supplier_id);,DML基础-SELECT与INSERT、UPDATE、DELETE,用途SELECT语句可以使用字符串拼凑的方法生成INSERT、UPDATE、DELETE语句，通常用于数据处理，比如清除按一定条件重复数据，修正数据，调整数据等;语法SELECTcolumn1,column2,.column_n,INSERTSTATEMENT,insert_sql,UPDATESTATEMENTupdate_sql,DELETESTATEMENTdelete_sqlFROMtablesWHEREpredicates示例找到按名称、所属城市有重复的供应商数据，删除ID最小的数据,DML基础-SELECT与INSERT、UPDATE、DELETE,示例将每个部门工资低于平均部门平均工资的员工工资，增加1000,目录,课程介绍,DML基础,SQL介绍,DCL基础,DDL基础,TL基础,DDL基础,语句CREATE创建数据库对象DROP删除数据库对象TRUNCATE删除指定表全部数据，不可回滚ALTER修改数据库对象COMMENT给表、字段添加备注RENAME重命名数据库对象分类表相关视图相关同义词相关,DDL基础,表标准创建表语句从查询创建表修改表结构删除表数据删除表结构,DDL基础-表-标准建语句,用途在数据库中建立表语法示例,DDL基础-表-标准建语句,示例创建客户表并设置CUSTOMER_ID为主键,DDL基础-表-标准建语句,示例创建部门表departments并设置DEPARTMENT_ID为主键创建员工表eployees并设置EMPLOYEE_NUMBER为主键，DEPARTMENT_ID为外键，引用部门表主键DEPARTMENT_ID,DDL基础-表-从查询创建表,用途在数据库中建立表，并将相应查询返回的数据插入至新建表中。但查询相关表的索引、主键等不会复制到新建表中语法CREATEtabletable_nameasselectquery示例备份整个suppliers表数据到新建表supplier_20100303CREATEtablesuppliers_20100303asselect*fromsuppliers,DDL基础-表-从查询创建表,示例单表查询，按指定查询列创建表suppliers，并填充数据多表关联，按指定查询列创建表suppliers，并填充数据创建表单但不填充数据，再用脚本插入数据（数据处理时常用）,DDL基础-表-修改表结构,用途在数据库中对已有表增加备注语法示例,DDL基础-表-修改表结构,用途在数据库中对已有表字段增加备注语法示例,DDL基础-表-修改表结构,用途在数据库中对已有表重命名语法示例,DDL基础-表-修改表结构,用途在数据库中对已有表添加字段语法示例,DDL基础-表-修改表结构,用途在数据库中对已有表修改字段语法示例,DDL基础-表-修改表结构,用途在数据库中对已有表删除字段语法示例,DDL基础-表-删除表数据,用途在数据库中删除指定表数据(全部数据，不可回滚)语法TRUNCATETABLEtable_name;示例TRUNCATETABLEsupplier;,DDL基础-表-删除表结构,用途在数据库中删除指定表结构语法DROPTABLEtable_name;示例DROPTABLEsupplier;,DDL基础,视图创建视图修改视图删除视图,DDL基础-视图-创建视图,用途在数据库中创建视图语法示例查询数据,DDL基础-视图-修改视图,用途在数据库中修改或者创建视图语法示例,DDL基础-视图-删除视图,用途在数据库中删除指定视图语法DROPVIEWview_name;示例DROPVIEWsup_orders_v;,DDL基础,同义词创建同义词修改同义词删除同义词同义词概念同义词本身也是数据库对象，一般用于映射别的用户的对象，使用同义词可以屏蔽具体被映射对象的所属用户和真实对象名称同义词和视图类似，是一种映射，视图只能映射查询SQL，同义词可以映射表、视图、存储过程、函数、包、序列等,DDL基础-同义词-创建同义词,用途在数据库中使用别的用户(schema)的数据库对象语法示例查询数据：,DDL基础-视图-修改同义词,用途在数据库中修改或者创建视图语法示例,DDL基础-表-删除同义词,用途在数据库中删除指定同义词语法DROPSYNONYMsynonym_name;示例DROPSYNONYMfnd_user;,目录,课程介绍,DML基础,SQL介绍,DCL基础,DDL基础,TL基础,DCL基础,权限清单授权语句GrantRevoke,DCL基础-授权语句-Grant,语法grantprivilegesonobjecttousers;示例授指定表一个权限，给指定用户grantselectonsupplierstosmithj;授指定表多个权限，给指定用户grantselect,insert,update,delteonsupplierstosmithj;授指定表全部权限，给指定用户grantallonsupplierstosmi