《王勇_Java项目视频Oracle10G培训日志.docx》由会员分享，可在线阅读，更多相关《王勇_Java项目视频Oracle10G培训日志.docx（70页珍藏版）》请在三一办公上搜索。
1、1、纲要42、内容42.1、SQL概述42.2、什么是数据库42.3、表52.4、SQL的分类52.5、演示数据的结构52.5.1、怎么样可以取得演示数据表52.5.2、如何查看表结构62.5.3、表结构描述62.6、简单查询72.6.1、查询一个字段72.6.2、查询多个字段72.6.3、查询全部字段82.6.4、计算员工的年薪82.6.5、将查询出来的字段显示为中文92.7、SQL Plus常用命令92.7.1、set linesize 20092.7.2、l(List)102.7.3、run / r102.7.4、save112.7.5、get112.7.6、ed(edit)112.7.
2、7、如何直接执行sql脚本122.8、条件查询122.8.1、等号操作符122.8.2、操作符142.8.3、between and 操作符142.8.4、is null152.8.5、and162.8.6、or162.8.7、表达式的优先级162.8.8、in172.8.9、not182.8.10、like192.9、排序数据202.9.1、单一字段排序202.9.2、手动指定排序顺序212.9.3、多个字段排序222.9.3、使用字段的位置来排序222.10、数据处理函数232.10.1、lower232.10.2、upper242.10.3、substr242.10.4、length24
3、2.10.5、trim252.10.6、to_date252.10.7、to_char262.10.8、to_number272.10.9、nvl272.10.10、case when then end282.10.11、decode292.10.12、round292.11、聚合函数292.11.1、count302.11.2、sum302.11.3、avg312.11.4、max312.11.4、min322.11.4、组合聚合函数322.12、分组查询332.12.1、group by332.12.2、having342.12.3、select语句总结342.13、连接查询352.13.
4、1、SQL92语法352.13.2、SQL99语法382.14、子查询392.14.1、在where语句中使用子查询，也就是在where语句中加入select语句392.14.2、在from语句中使用子查询，可以将该子查询看做一张表402.14.3、在select语句中使用子查询412.15、union和minus422.15.1、union可以合并集合（相加）422.15.1、minus可以移出集合（相减）422.16、rownum的使用432.16.1、rownum隐含字段432.16.2、取得前5条数据432.16.3、取得大与第5条的所有数据442.16.4、取得薪水最高的前5名442
5、.16.5、 采用rownum进行分页452.17、添加、修改和删除462.17.1、insert462.17.2、update502.17.2、delete502.18、事务概述502.19、表512.19.1、创建表512.19.2、创建表加入约束522.19.3、t_student和t_classes完整示例582.19.4、增加/删除/修改表结构592.19.5、增加/删除/修改表约束602.19.6、删除表602.20、索引612.20.1、建立索引612.21、视图612.21.1、删除视图622.22、序列(Sequence)632.22.1、创建序列632.22.2、使用序列6
6、32.22.3、删除序列632.23、存储过程、触发器和游标632.23.1、存储过程632.23.2、触发器642.24、游标652.5、常用的DBA命令662.5.1、查看用户拥有的数据库对象662.5.2、查看约束信息662.5.3、查看用户拥有的表662.5.4、查看用户拥有的视图662.5.5、查看用户拥有的触发器662.5.6、查看用户拥有的序列662.5.7、查看用户拥有的存储过程662.5.8、查看用户拥有的索引662.5.9、显示当前用户672.5.10、切换用户672.5.11、将用户赋予某种角色登录672.5.12、查看所有的用户672.5.13、查看用户拥有的权限672
7、.5.14、给用户加锁672.5.15、给用户解锁672.5.16、修改用户密码682.5.17、新建用户682.5.18、删除用户及相关对象682.5.19、给用户授权（多个采用逗号间隔）682.5.20、分配表空间usres给用户682.5.21、授权表空间给用户682.5.21、一个完整的过程,创建用户、创建表空间、授权、建表692.25.22、导入和导出命令imp、exp692.26、数据库设计的三范式692.26.1、第一范式692.26.2、第二范式701、纲要l SQL概述l Oracle简介l SQL plus概述l 演示数据表结构l 简单查询l SQL plus常用命令 l
8、条件查询l 排序数据l 数据处理函数l 聚合函数l 分组查询l 连接查询l 子查询l union和minusl rownum的使用l 添加，修改和删除l 事务概述l 表l 索引l 视图l 序列l 存储过程、触发器和游标l 数据库设计三范式l Oracle体系结构、概念及常用DBA操作2、内容2.1、SQL概述SQL，一般发音为sequel，SQL的全称Structured Query Language)，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准。2.2、什么是数据库数据库，通常是一个或一组文件，保存了一些符合特定规格的数据数据库软件称为数据库管理系统（DBMS），全称为D
9、ataBase Managed System，如：Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql 2.3、表表是一种结构化的文件，可以用来存储特定类型的数据，如：学生信息，课程信息，都可以放到表中。另外表都有特定的名称，而且不能重复。表中具有几个概念：列、行、主键。 学生信息表学号（主键）姓名性别年龄00001张三男2000002李四女20列，通常也叫字段，表是由列构成的，列是具有类型的行，也叫记录，表中的数据是按行（记录）存储的，表里可以有0条或多条记录主键，主键是由列构成的，表中的每一行通常都有一个标识，主键
10、可以由一个字段或多个字段构成，一个字段构成的主键称为单一主键，多个字段构成的主键称为复合主键，主键通常是不能修改的2.4、SQL的分类数据查询语言（DQL），只有一个select数据操纵语言（DML），主要包括：insert/update/delete数据定义语言（DDL），主要包括：create/drop/alter事务控制语言，主要包括：commit/rollback数据控制语言，主要包括授权等等2.5、演示数据的结构2.5.1、怎么样可以取得演示数据表select * from tab;或select table_name from user_tables;2.5.2、如何查看表结构de
11、sc dept;表结构是由字段构成的，字段是有类型的2.5.3、表结构描述表名称：dept描述：部门信息表英文字段名称中文描述类型DEPTNO部门编号NUMBER(2)DNAME部门名称VARCHAR2(14)LOC位置VARCHAR2(13)表名称：emp描述：员工信息表英文字段名称中文描述类型EMPNO员工编号NUMBER(4)ENAME员工姓名VARCHAR2(10)JOB工作岗位VARCHAR2(9)MGR上级经理NUMBER(4)HIREDATE入职日期DATESAL薪水NUMBER(7,2)COMM津贴NUMBER(7,2)DEPTNO部门编号NUMBER(2)注：DEPTNO字段
12、是外键，DEPTNO的值来源于dept表的主键，起到了约束的作用表名称：salgrade描述：薪水登记信息表英文字段名称中文描述类型GRADE等级NUMBERLOSAL最低薪水NUMBERHISAL最高薪水NUMBER2.6、简单查询2.6.1、查询一个字段l 查询员工姓名select ename from emp;Select语句后面跟的是字段名称，select是关键字，select和字段名称之间采用空格隔开，from表示将要查询的表，它和字段之间采用空格隔开2.6.2、查询多个字段l 查询员工的编号和姓名select empno, ename from emp;查询多个字段，select中
13、的字段采用逗号间隔即可，最后一个字段，也就是在from前面的字段不能使用逗号了。2.6.3、查询全部字段可以将所有的字段放到select语句的后面，这种方案不方便，但是比较清楚，我们可以采用如下便捷的方式查询全部字段select * from emp;采用select * from emp，虽然简单，但是*号不是很明确，建议查询全部字段将相关字段写到select语句的后面2.6.4、计算员工的年薪l 列出员工的编号，姓名和年薪select empno, ename, sal*12 from emp;在select语句中可以使用运算符，以上存在一些问题，年薪的字段名称不太明确2.6.5、将查询出
14、来的字段显示为中文select empno as 员工编号, ename as 员工姓名, sal*12 as 年薪 from emp;可以采用as关键字重命名表字段，其实as也可以省略，如：select empno 员工编号, ename 员工姓名, sal*12 年薪 from emp;2.7、SQL Plus常用命令2.7.1、set linesize 200set linesize 可以设置一行的字符数，默认为80个字符以上set linesize 300,表示一行为300个字符2.7.2、l(List) 可以显示缓存区中的最后执行的内容2.7.3、run / r 以上三个命令功能是一
15、致的，重新运行缓存区中的语句2.7.4、save save可以将最后一次在缓存区中执行的语句保存到文件2.7.5、get get可以将文件中的sql语句放到缓存区中，采用/或r或run，可以执行2.7.6、ed(edit) ed可以采用记事本来编辑缓存区中的内容2.7.7、如何直接执行sql脚本 c:emp.sql;2.8、条件查询条件查询需要用到where语句，where必须放到from语句表的后面支持如下运算符运算法说明=等于或!=不等于小于大于=大于等于between and .两个值之间is null为nullIn包含，相当于多个orNotnot可以取非，主要用在is 或in中Like
16、like称为模糊查询，支持%或下划线匹配%匹配任意个字符下划线，一个下划线只匹配一个字符2.8.1、等号操作符l 查询薪水为5000的员工select empno, ename, sal from emp where sal=5000;l 查询job为MANAGER的员工select empno, ename from emp where job=manager;以上查询出现错误，因为job为字符串，所以出现了以上错误select empno, ename from emp where job=manager;仍然出现错误，在sql中字符串必须采用单引号，不能使用双引号select empno
17、, ename from emp where job=manager;以上查询正确，但没有找到符合条件的数据，因为sql中的值是区分大小写的，但关键字不区分大小写，如以下写法等同以上写法：SELECT empno, ename FROM emp where job=manager;select empno, ename from emp where job=MANAGER;以上输出正确，一定注意大小写问题及字符串必须采用单引号问题2.8.2、操作符l 查询薪水不等于5000的员工select empno, ename, sal from emp where sal 5000;一下写法等同于以上
18、写法，建议使用第一种写法select empno, ename, sal from emp where sal != 5000;数值也可以采用单引号引起来，如一下语句是正确的：select empno, ename, sal from emp where sal 5000;l 查询工作岗位不等于MANAGER的员工select empno, ename from emp where job MANAGER;2.8.3、between and 操作符l 查询薪水为1600到3000的员工(第一种方式，采用=和= 1600 and sal 2500;2.8.6、oror，只要满足条件即可,相当于包
19、含l 查询出job为manager或者job为salesman的员工select * from emp where job=MANAGER or job=SALESMAN;2.8.7、表达式的优先级l 查询薪水大于1800，并且部门代码为20或30的员工（错误的写法）select * from emp where sal 1800 and deptno = 20 or deptno = 30;以上输出不是预期结果，薪水小于1800的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤sal 1800 and deptno = 20，然后再将deptno = 30员工合并过来，所以是不对的l
20、 查询薪水大于1800，并且部门代码为20或30的（正确的写法）select * from emp where sal 1800 and (deptno = 20 or deptno = 30);关于运算符的问题：不用记，没有把握尽量采用括号2.8.8、inin表示包含的意思，完全可以采用or来表示，采用in会更简洁一些l 查询出job为manager或者job为salesman的员工l 查询出薪水包含1600和薪水包含3000的员工select * from emp where sal in(1600, 3000);2.8.9、notl 查询出薪水不包含1600和薪水不包含3000的员工（第
21、一种写法）select * from emp where sal 1600 and sal 3000;l 查询出薪水不包含1600和薪水不包含3000的员工（第二种写法select * from emp where not (sal = 1600 or sal = 3000);l 查询出薪水不包含1600和薪水不包含3000的员工（第三种写法）select * from emp where sal not in (1600, 3000);l 查询出津贴不为null的所有员工select * from emp where comm is not null;2.8.10、likeLike可以实现模
22、糊查询，like支持%和下划线匹配l 查询姓名以M开头所有的员工select * from emp where ename like M%;l 查询姓名以N结尾的所有的员工select * from emp where ename like %N;l 查询姓名中包含O的所有的员工select * from emp where ename like %O%;l 查询姓名中第二个字符为A的所有员工select * from emp where ename like _A%;Like中%和下划线的差别？%匹配任意字符出现的次数 下划线只匹配一个字符 Like 中的表达式必须放到单引号中，以下写法是错
23、误的：select * from emp where ename like _A%2.9、排序数据2.9.1、单一字段排序排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面l 按照薪水由小到大排序(系统默认由小到大)select * from emp order by sal;l 取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)select * from emp where job=MANAGER order by s
24、al;如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面以下写法是错误的：select * from emp order by sal where job=MANAGER;l 按照多个字段排序，如：首先按照job排序，在按照sal排序2.9.2、手动指定排序顺序l 手动指定按照薪水由小到大排序select * from emp order by sal asc;l 手动指定按照薪水由大到小排序select * from emp order by sal desc;2.9.3、多个字段排序l 按照job和薪水倒序select * fr
25、om emp order by job desc, sal desc;如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序2.9.3、使用字段的位置来排序l 按照薪水升序select * from emp order by 6;不建议使用此种方式，采用数字含义不明确，程序不健壮2.10、数据处理函数Lower转换小写upper转换大写substr取子串length取长度trim去空格to_date将字符串转换成日期to_char将日期或数字转换成字符串to_number将字符串转换成数字nvl可以将null转换成一个具体值case 分支语句decode同caseround四
26、舍五入2.10.1、lowerl 查询员工，将员工姓名全部转换成小写select lower(ename) from emp;2.10.2、upperl 查询job为manager的员工select * from emp where job=upper(manager);2.10.3、substrl 查询姓名以M开头所有的员工select * from emp where substr(ename, 1, 1)=upper(m);2.10.4、lengthl 取得员工姓名长度为5的select length(ename), ename from emp where length(ename)=
27、5;2.10.5、trimtrim会去首尾空格，不会去除中间的空格l 取得工作岗位为manager的所有员工select * from emp where job=trim(upper(manager );2.10.6、to_datel 查询1981-02-20入职的员工（第一种方法，与数据库的格式匹配上）select * from emp where HIREDATE=20-2月 -81;l 查询1981-02-20入职的员工（第二种方法，将字符串转换成date类型）select * from emp where hiredate=to_date(1981-02-20, YYYY-MM-DD
28、);等同select * from emp where hiredate=to_date(1981-02-20 00:00:00, YYYY-MM-DD );默认为0时0分0秒to_date可以将字符串转换成日期，具体格式to_date(字符串，匹配格式)日期格式的说明控制符（不区分大小写）说明YYYY表示年MM表示月DD表示日HH12,HH24表示12小时制，表示24小时制MI表示分SS表示秒2.10.7、to_charl 查询1981-02-20以后入职的员工，将入职日期格式化成yyyy-mm-dd hh:mm:ssselect empno, ename, to_char(hiredate
29、, yyyy-mm-dd hh24:mi:ss) as hiredate from emp;l 查询员工薪水加入千分位select empno, ename, to_char(sal, $999,999) from emp;l 查询员工薪水加入千分位和保留两位小数select empno, ename, to_char(sal, $999,999.99) from emp;将数字转换成字符串，格式控制符说明9表示一位数字0位数不够可以补零$美元符L本地货币符号.显示小数,显示千分位2.10.8、to_number将字符串转换成数值select * from emp where salto_nu
30、mber(1,500, 999,999);2.10.9、nvll 取得员工的全部薪水，薪水+津贴select empno, ename, sal, comm, sal+comm from emp;以上结果不正确，主要原因是津贴（comm）字段为null，所以无法计算，所以正确的做法是将津贴先转换成0，再计算。可以使用Oracle提供的nvl，该函数的语法格式为：nvl(表达式1，表达式2)，表达式1：指的是字段名称；表达式2：指的是将该字段的null转换成的值l 采用nvl函数，取得员工的全部薪水，薪水+津贴select empno, ename, sal, comm, sal+nvl(com
31、m, 0) from emp;以上结果是正确的，在做表设计的时候，关于数值字段最好不允许为null，可以设置缺省值2.10.10、case when then endl 如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%select empno, ename, job, sal, case job when MANAGER then sal*1.1 when SALESMAN then sal*1.5 end as newsal from emp;2.10.11、decode同case when then endl 如果job为MANAGERG薪水上涨10%
32、,如果job为SALESMAN工资上涨50%select empno, ename, job, sal, decode(job, MANAGER, sal*1.1, SALESMAN, sal*1.5) as newsal from emp;2.10.12、round四舍五入select round(2345343.1234, 2) from dual;Dual是oracle提供的，主要为了方便使用，因为select的时候需要用from2.11、聚合函数count取得记录数sum求和Avg取平均Max取最大的数min取最小的数2.11.1、countl 取得所有的员工数select count
33、(*) from emp;Count（*）表示取得所有记录，忽略null，为null值也会取得l 取得津贴不为null员工数select count(comm) from emp;采用count(字段名称)，不会取得为null的记录l 取得工作岗位的个数select count(distinct(job) from emp;Distinct可以去除重复的记录2.11.2、sumSum可以取得某一个列的和，如果是null会略l 取得薪水的合计select sum(sal) from emp;l 取得津贴的合计select sum(comm) from emp;如果是null会略l 取得薪水的合计
34、（sal+comm）select sum(sal+comm) from emp;从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0select sum(sal+nvl(comm, 0) from emp;2.11.3、avg取得某一列的平均值l 取得平均薪水select avg(sal) from emp;2.11.4、max取得某个一列的最大值l 取得最高薪水select max(sal) from emp;l 取得最晚入职得员工select max(to_char(hiredate, yyyy-mm-dd) from
35、emp;2.11.4、min取得某个一列的最小值l 取得最低薪水select min(sal) from emp;l 取得最早入职得员工select min(to_char(hiredate, yyyy/mm/dd) from emp;2.11.4、组合聚合函数可以将这些聚合函数都放到select中一起使用select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;2.12、分组查询分组查询主要涉及到两个子句，分别是：group by和having2.12.1、group byl 取得每个工作岗位的工资合计，要求显示岗位名称和工资合
36、计select job, sum(sal) from emp group by job;采用group by，聚合函数前面的所有字段必须参与分组如果使用了order by，order by必须放到group by后面l 按照工作岗位和部门编码分组，取得的工资合计n 原始数据SQL select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 3
37、00 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5
38、000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10已选择14行。n 分组语句select job,deptno,sum(sal) from emp group by job,deptno;2.12.2、having如果想对分
39、组数据再进行过滤需要使用having子句取得每个岗位的平均工资大于2000select job, avg(sal) from emp group by job having avg(sal) 2000;分组函数的执行顺序：1、 根据条件查询数据2、 分组3、 采用having过滤，取得正确的数据2.12.3、select语句总结一个完整的select语句格式如下select 字段from 表名where .group by .having .order by .以上语句的执行顺序1. 首先执行where语句过滤原始数据2. 执行group by进行分组3. 执行having对分组数据进行操作4. 执行select选出数据5. 执行order by排序2.13、连接查询2.13.1、SQL92语法连接查询：也可以叫跨表查询，需要关联多个表进行查询l 显示每个员工信息，并显示所属的部门名称select ename, dname from emp, dept;SQL select ename, dname from emp, dept;ENAME D
