oracle数据库培训.ppt

Oracle数据库培训（SQL基础）主讲：侯同盼,2013/01/08,課程內容,Oracle简介Oracle的安装和卸载基本的SQL语句限定查询和排序数据单行函数和组函数多表查询和子查询使用集合的运算控制用户访问和维护表约束/事务/视图的使用,Oracle简介（了解）,历史Oracle在古希腊神话中被称为“神喻”指的是上帝的宠儿,在中国的商周时期，把刻在龟壳上的文字也称为上天的指示,所以在中国Oracle又翻译为甲骨文.Oracle的发展最早起源于IBM的一个技术员的论文论关系性数据库的发展,后来这篇论文被Oracle公司的老总：LarryEllision所发现,从而创办了今天的Oracle公司.数据库的产品大型数据库：IBMDB2,Oracle.中小型数据库：SQLServer,MySQL,Access.古老级别的数据库：Informix,Sybase.,Oracle数据库的安装和卸载,Oracle数据库的安装(重点)1.安装Oracle之前最好是：关闭防火墙，断开网络.2.安装程序：F:/oracle10G/install/setup.ext.图2-1正在启动安装程序3.选择“下一步”.4.选择安装路径：D:/oracle/product/10.1.0/db_1.“下一步”.,Oracle数据库的安装和卸载,5.选择“下一步”6.选择“下一步”图5图67.输入数据库的名称，而且将“创建带样本方案的数据库”选中.如果此处没有选中的话，则以后不会有大的数据用户出现,选择“下一步”。图7,Oracle数据库的安装和卸载,8.选择“下一步”图89.选择“下一步”图910.选择“下一步”,Oracle数据库的安装和卸载,11.安装到最后将所有的密码统一设置为”oracleadmin”,字母全部小写，不要擅自更改选择“下一步”图11图1212.直接选择“安装”启动安装程序，之后让其自行安装，并不要在做任何操作.,Oracle数据库的安装和卸载,13.安装完成之后进行数据库的配置，之后进入“口令管理”的界面。oracle数据库主要有四个用户：1)超级管理员：sys/changeoninstall;2)普通管理员：system/manager;3)普通用户：scott/tiger;默认锁定的4)大数据用户：sh/sh;,Oracle数据库的安装和卸载,安装完成之后window自动会给用户注册若干个服务。但是这些服务都是默认启动的,这样启动会造成速度缓慢,所以建议大家还是采用手工启动方式.,Oracle数据库的安装和卸载,Oracle数据库的卸载(重点)卸载Oracle最好按照如下步骤完成：直接运行OracleUniversalinstaller卸载oracle;停止Oralce的所有服务;删除Oracle系统目录C:programfilesoracle;删除Oracle环境变量;删除程序菜单项中的Oracle菜单;同时删除注册表中所有与Oracle有关的配置项;(重启系统后)删除Oracle工作主目录D:oracle如果没有正常的安装成功，除了操作系统的问题之外，直接采用以上的第二和第三两个步骤就可以完成卸载.,基本的SQL语句,一、SQL简介SQL（structuredquerylanguage)语句是美国国家标准协会为了操作关系数据库提出的标准语言,它包含:DQL(数据查询):selectDML(数据操作语言）:insert/update/delete/mergeDDL(数据定义语言）：create/alter/drop/truncateTCL(事务控制语言):commit/rollback/savepointDCL(数据控制语言）：grant/revoke二、数据库的简单操作2.1创建一个用户表SQLcreatetableusers_henry(idnumber,passwordchar(4),namechar(20),phonechar(11),emailchar(30);,基本的SQL语句,2.2增加数据到users_henry表中.2.3查询刚刚增加的数据.select*fromusers_henry;2.4调整格式SQLcolumn列名format格式columnnameformata10columnidformat9999colidfor9999setlinesize300setpagesize1002.5常用的数据类型数字类型字符类型2.6删除表结构droptableusers_henry;,基本SQL语句,练习1.计算员工的年薪？2.计算员工的月收入是多少?3.员工都分布在哪些部门？4.查询数据表有多少条记录?5.员工表中有多少种职位？6.去除重复记录？7.把1003号员工的经理改成1004?8.把编号1008的员工工资改成800？,限定查询和排序数据,限定查询就是在简单查询语法的基础上，增加了一个WHERE子句，用于指定限定条件。语法结构SELECTDISTINCT*字段别名,字段别名FROM名称别名WHERE条件(s)在WHERE子句后可以增加多个条件，最长见的条件是最基本的关系运算：、=、)、BETWEENAND、LIKE、IN、NULL、ISNULL、AND、OR/NOT；Orderby子句对于排序的说明：1.排序时，可以指定多个排序的字段；2.排序的方式2种：ASC升序默认的DESC降序用户需要指定序列，从大到小排序,练习查询哪些员工的薪水大于10000?哪些员工的薪水在5000-10000之间？列出职位是Analyst的员工？列出职位不等于Progarmmer的员工？查询部门10或者部门20的员工？哪些员工的职位中包含sales?查找第三个字符是a的员工名字?查询奖金是空值的员工？查询奖金不是空值的员工？薪水由低到高排序？薪水由高到低排序？按部门排序，在按薪水由高到低排序?,限定查询和排序数据,单行函数和组函数,字符函数1)upper(字符串|列）将输入的字符串变为大写2)lower(字符串|列）将输入的字符串变为小写3)initcap将开头字母大写4)length求字符串的长度5)replace进行替换6)substr字符串截取数值函数1)round四舍五入操作2)trunc截取到指定的小数位3)mod取余数日期函数1)month_between(p1,p2)两个时间p1,p2之间间隔的月份2)next_day(sysdate,mon)下一个周几3)last_day(sysdate）当月的最后一天,单行函数和组函数,转换函数1)to_char将日期转换字符串2)to_date将字符串转换成日期3)to_number将字符串变为数字通用函数1)nvl()函数，是处理null的，将null变为02)decode多数值判断组函数常用的组函数：1)avg平均值2)count统计3)max最大值4)min最小值5)sum合计分组统计groupby子句having分组后的过滤条件,单行函数和组函数,练习1.将编号为1001的员工名字变为大写字母显示.2.将编号为1005的员工名字变为小写字母显示.3.将所有的员工名字的首字母变为大写.4.求出员工名字的所占字符的长度.5.将编号为1006的员工名字中为“a字符替换成A.6.查出所有员工名字的前三个字符.7.员工的年终奖金=年薪*0.123456,按小数点后两位四舍五入.8.员工的年终奖金=年薪*0.123456,截取两位小数.9.查出每个员工入职了多少个月.10.查出下一个星期一的日期.11.查出这个月的最后一天.12.显示职员的入职时间,用2010-04-01格式显示.13.根据职位计算加薪后的数据analyst:30%programmer:20%manager:10%14.计算职员表中薪水的最大/最小/总和/平均值/人数15.计算每个职位的平均薪水和人数16.查询薪水总和大于20000的部门编码和薪水总和.17.人数在3个人以上的部门?,多表查询和子查询,多表查询的基本概念等值连接在之前所使用的查询操作之中,都是从一张表之中查询出所需要的内容,那么如果现在一个查询语句需要显示多张表的数据,则就必须应用到多表查询的操作,多表查询的语法如：select表别名1.字段名1，表别名2.字段名2,.from表1表别名1join表2表别名2on表别名1.字段名3=表别名2.字段名4;在用查询语句之前，一定要先用count(*)来确定一下数据库的记录确定好数据后用:select*fromemp,dept;这样查出来的结果是把两张表的总记录条数相乘,多表查询的性能太低。以上的操作在数据库中被称为笛卡尔积。,多表查询和子查询,如何去消除笛卡尔积：可以采用关联字段的形式Select*fromempejoindetpdone.deptno=d.deptno例：查出每一位编号、姓名、职位、部门名称和位置.非等值连接输出员工编号、姓名、工资、工资等级selecte.empno,e.ename,e.salary,s.gradefromemp_henryejoinsalgrade_henryone.salarybetweens.lowsalands.hisal;自连接自连接比较特殊，一般反映记录的上下级关系，例如员工信息表中会有一个字段：上级领导，这个字段对应的上级其实也是一个员工，这个员工信息就是通过自连接进行查找的.自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果.查询员工名字和他的经理名字,m.enamefromemp_henrywjoinemp_henrymonw.mgr=m.empno;,外连接外连接会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。左外连接(leftouterjoin)selecte.empno,e.ename,d.dname,d.locfromemp_henryeleftouterjoindept_henrydone.deptno=d.deptno;右外连接(rightouterjoin)查出所有的部门记录，包括没有员工的部门selecte.empno,,d.dname,d.locfromemp_henryerightouterjoindept_henrydone.deptno=d.deptno;全外连接（fullouterjoin)列出全部的记录,包括没有部门的员工和没有员工的部门.selecte.empno,,d.deptno,d.dname,d.locfromemp_henryefullouterjoindept_henrydone.deptno=d.deptno;,多表查询和子查询,子查询1)在之前强调过多表查询不建议大家使用,因为性能很差,所以子查询在实际开发之中使用的相当的多;2)所谓的子查询就是在一个查询之中嵌套了其他的若干查询.3)子查询可以出现在查询语句的任意位置.4)子查询出现在where和from之后的较多.-where:子查询一般返回单行单列、单行多列、多行单列的数据.-from:子查询返回多行多列的数据，当作一张临时表出现.练习1)谁的薪水比guojing高?2)所在部门的平均薪水高于5000的员工姓名和薪水.3)查询和guojing同部门的员工姓名和入职时间.4)谁的薪水最低?,多表查询和子查询,ANY操作符：与每一个内容相匹配,有三种匹配形式1)=ANY：功能与IN操作符是完全一样的;select*fromempwheresalANY(selectsalfromempwherejob=guojing);2)ANY:比子查询中返回记录最小的还要大的数据;select*fromempwheresalANY(selectsalfromempwherejob=guojing);3)ANY(selectsalfromempwherejob=guojing);ALL操作符：与每一个内容相匹配,有三种匹配形式1)ALL:比子查询中返回的最大的记录还要大select*fromempwheresalANY(selectsalfromempwherejob=guojing);2)ANY(selectsalfromempwherejob=guojing);,多表查询和子查询,合并查询在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号：union,unionall,intersect,minus1)union用于取得两个结果集的并集，使用操作符会自动去掉结果集中重复行selectename,sal,jobfromempwheresal2500unionselectename,sal,jobfromempwherejob=manger2)unionall3)intersect4)minus,使用集合的运算,控制用户和维护表,控制用户a)系统权限为了让大家快速理解，我们来举例说明：1.创建两个用户ken，tom初始阶段他们没有任何权限如果登录就会给出错误的信息createuserkenidentifiedbyken2.给用户ken授权1)grantcreatesession,createtabletokenwithadminoption2)grantcrateviewtoken3.给用户tom授权通过ken给tom授权，因为withadminoptionjl是加上的1)grantcreatesession,createtabletotom2)grantcreateviewtotom;回收系统权限用system执行如下操作：revokecreatesessionfromken,b)对象权限1.xiaohong用户要操作system.emp表，则必须授予相应的对象权限a)希望xiaohong可以查询system.emp的表数据?grantselectonemptoxiaohongb)希望xiohong可以修改system.emp的表数据?grantupdateonemptoxiaohongc)希望xiohong可以删除system.emp的表数据?grantdeleteonemptoxiaohongd)有没有更加简单的方法，一次把所有权限赋给xiaohong?grantallonemptoxiaohong2.授予system用户创建session的权限，即登陆权限grantcreatesessiontosystem;3.这条比较重要，授予所有权限(all)给所有用户(public)grantalltopublic;4.授予system用户alert任意表的权限grantalertalltabletosystem;c)查看权限查看当前用户所有权限select*fromuser_sys_privs;查看所用用户对表的权限select*fromuser_tab_privs;,控制用户和维护表,维护表1)增加字段ALTERTABLE表ADD字段CHAR(200)2)修改字段类型ALTER