Oracle课件资料

甲骨文（兴学）培训中心Oracle数据库入门基本概念学习目标会基本的数据库操作掌握简单查询语句掌握条件查询掌握查询结果排序基本概念什么是关系型数据库◦关系型数据库管理系统（RelationshipDataBaseManagementSystem）用于对数据进行存储、操作等管理，简称为数据库（DB）。是一个由一批数据构成的有序集合，这个集合通常被保存为一个或多个彼此相关的文件。这些数据被分门别类地存放在一些结构化的数据表（table）里，而数据表之间又往往会形成种种内在的交叉引用关系。常见概念数据库几个相关概念◦表：逻辑上用于存储数据的单元。Table（也称实体集EntrySet）◦列：column，代表存储数据的属性。(也称字段field)◦行：row,表中的一行代表一条数据。（也称实体Entry）◦主键：PrimayKey,唯一标识表中的一行数据的特殊字段。例如上面例子中的USERNAME列和ORDERID列。◦外键：ForeignKey,用于定义表和表之间联系的特殊字段。例如上例中订单表的USERNAME。在用户表里是主键，在订单表中就是外键，用来体现两个表之间的联系。基本概念连接数据库服务器端：OracleServiceXE(核心)、OracleXETNSListener(对外提供连接支持)客户端：sqlplus:oracle提供的基于dos界面的client工具。登录：sqlplushr/hr;退出：exitiSqlPlus:oracle提供的基于浏览器界面的client工具。访问：基本查询语句语法结构：select字段名1,字段名2,...from表名1.查询部分列2.查询全部列3.对查询出来的列，进行数学运算4.查询列起别名5.字符串连接||6.去除查询结果中的重复数据ORDERBY语句语法结构：select列名1，列2from表名orderby列asc|desc功能：对查询结果进行排序1.单列排序2.多列排序条件查询（WHERE）语句语法结构：select列名1，列2from表名where过滤条件orderby列asc|desc功能：对查询结果继续过滤1.等值=2.==!=andor3.null值得处理IS[NOT]null【特别注意】4.范围查询：列名[not]between小值and大值5.枚举查询：列名[not]in(值1，值2，…，值N)条件查询（WHERE）语句语法结构：casewhenexp1then结果1whenexp2then结果2else其他结果end[别名]功能：对查询数据根据不同条件返回不同结果（switch-case）说明：语法中的exp1表达式是布尔表达式selectfirst_name,salary,casewhensalary6000then'low'whensalarybetween6000and10000then'middle'else'high'endassal_levelfromployees甲骨文（兴学）培训中心oracle函数基本概念学习目标掌握单行函数掌握组函数【重点】掌握查询结果分组Where和having的区别单行函数基本概念：所谓的单行函数就是作用于表里的每一行数据，执行一次函数。单行函数数据1结果1一条数据一条数据结果2结果3数据2数据3单行函数常见单行函数分类：字符串相关，数字相关，日期相关，类型转换相关，NVL函数字符串相关函数：length(str)—获得字符串str的字符长度concat(str1,str2)—相当于||的功能substr(str,begin,length)--对str做截取，从begin(下标从1开始)开始截length位instr(str1,str2,begin)--在str1里查找str2，从begin（下标从1开始）开始查找。找到返回str2首字母所在的下标，否则返回-1lower(str),upper(str)--将str转换成小写/大写数字函数数字相关函数：mod(num1,num2)--取模，num1%num2trunc(n,length)--对数字n进行截断，精度到小数点后length位round(n1,length)--对数字n四舍五入，精度到小数点后length位abs(num)--对num取绝对值获取随机数：dbms_random.random(),产生一个很大的随机数（可正，可负）日期函数日期相关函数：SYSDATE表示当前系统时间add_months(myDate,num)--对myDate做月份的算术运算last_day(myDate)--获取myDate所在月份的最后1天months_between(myDate1,myDate2)--计算两个日期之间相隔几个月，返回值为小数trunc(myDate,日期格式字符串)--对myDate日期，根据指定日期格式做截断可选日期格式字符串:year--按年截断month-按月截断day-星期省略格式字符串表示截断时分秒类型转换函数类型转换相关函数：SYSDATE表示当前系统时间add_months(myDate,num)--对myDate做月份的算术运算last_day(myDate)--获取myDate所在月份的最后1天months_between(myDate1,myDate2)--计算两个日期之间相隔几个月，返回值为小数trunc(myDate,日期格式字符串)--对myDate日期，根据指定日期格式做截断可选日期格式字符串:year--按年截断month-按月截断day-星期省略格式字符串表示截断时分秒NVL函数NVL函数问题描述：查询员工的工号，名字，月收入:selectemployee_id,first_name,salary+comission_pct*salary月收入fromemployees;NVL函数运行结果：问题：我们发现很多员工的实发工资变成了NULL，这很显然是不对的。造成这个错误的原因在于有的员工的commission_pct为NULL,在计算总工资时，由于有空值参与，所以导致整个运算结果为空NVL函数问题解决：NVL函数nvl(n1,n2)--判断n1是否是null，是null执行n2，否则执行n1。类似(n1==null?n2:n1)改写成selectemployee_id,first_name,salary,commission_pct,(salary+salary*nvl(commission_pct,0))assumfromemployees;组函数基本概念：针对一组数据进行统计，这类函数统称为组函数。组函数的是一组数据产生一条结果组函数输出结果数据1数据2数据3组函数常用单行函数：sum(列名)--求一组数据的总和avg(列名)--求一组数据的平均值max(列名)--在一组数据中找出最大值min(列名)--在一组数据中找出最小值count(列名)--对查询结果中该列的非空记录(统计结果中该列不为null的行数)eg:查询公司中职位的个数selectcount(distinctjob)fromemployees;这些函数在运算时都会忽略null值数据分组常用单行函数：概念：组函数是把所有查询结果按照特定的条件进行分组。语法结构：Select…from…where…groupby…分组条件eg：查询各个部门的最高工资和总工资selectdepartment_id,max(salary),min(salary)fromemployees;数据分组执行过程示意图数据分组分组特别注意【重点】需要强调，由于SQL语句中使用类groupby子句之后，同一小组内的多条数据最终要被合并为一条数据，那么对select后面的列名字是有要求的。我们先来看一个错误的例子：Selectdepartment_id,first_name,max(salary),sum(salary)fromemployeesgroupbydepartment_id这条sql产生错误的原因就在于在使用groupby根据department_id进行分组之后，又要查询first_name列，我们知道一个小组最后会被合并成一条数据，那么小组内每个人的first_name由不相同，就会造成无法合并的问题。那么究竟哪些列可以出现分组SQL中呢，我们给出如下规则，请大家牢记：数据分组分组规则：规则1：只有出现在groupby中的列才能出现在select子句中或orderby子句中规则2：如果在groupby子句中的列上应用了某些函数，select子句中或orderby子句的相应列也必须应用同样的函数规则3：那些没有出现在groupby中的列，只有配合分组函数可以出现在select子句中或orderby子句中分组过滤Having子句，对分组后的数据进行过滤概念：对分组后的数据进行条件过滤语法结构：select...from...where...groupby...having过滤条件orderby...eg：查询平均工资大于3000的部门的部门id，最高工资和总工资selectdepartment_id,max(salary),sum(salary),fromemployeesgroupbydepartment_idhavingavg(salary)3000;分组过滤Where和having的区别Where：对分组之前的数据进行过滤Having：对分组后的数据进行过滤优化：如果where和having都可以实现过滤解决问题，此时应优先选择where，效率高甲骨文（兴学）培训中心Oracle复杂查询学习目标学习目标掌握伪劣的概念掌握子查询掌握分页查询【难点】掌握表连接查询伪劣伪劣概念伪列就是假的，不存在的列。这些列在表中并不存在，但是可以通过查询语句查询出来。Oracle常用的列有两种：rowID和rowNumROWID：Rowid伪劣能在整个数据库中唯一标识一条记录ROWNUM：Rownum是给查询结果的编号，在查询结果中，第一条满足查询条件的数据rowid的值为1，依次递增。注：rownum做条件，只能用,=,=1,=1条件子查询子查询概念1.查询的结果是一行一列可以将结果看做是一个值，参与条件比较，或者显示在select子句2.查询结果是多行一列可以将结果看做多个值，参与条件比较或者显示在select子句3.查询结果是多列可以将结果看做是一个临时表，针对临时表进行再次查询，分组，排序，表连接等各种操作。基本概念例子：1.查询工资低于公司平均工资的员工信息selectfirst_name,phone_number,salaryfromemployeeswheresalary(selectavg(salary)fromemployees)2.查询和king在同一部门的员工selectemployee_id,last_name,salary,department_idfromemployeeswheredepartment_idin(selectdepartment_idfromemployeeswherelast_name='King');3.查询工资最高的前5名员工select*from(select*fromemployeesorderbysalarydesc)whererownum=5;分页查询分页查询【难点】查询工资排在6到10之间的员工信息selecttab2.*from(selecttab1.*,rownumronfrom(select*fromemployeesorderbysalarydesc)tab1whererownum=10