oracle第2讲笔记2.docx

1、oracle第2讲笔记2 类（对象）与表（记录）的关系图创建表基本语法create table 表名(列名 列的数据类型,.)举例说明创建一张用户表create table users(id number,name varchar2(32),password varchar2(32),birthday date); oracle的数据类型1 char(size)存放字符串，最大2000个字符，是定长。举例说明create table test1(name char(32);这样，在name这列，最多只能存放32个字符，如果超过，就报错，如果不够abc,则用空格补全。insert into te

2、st1 values(abc);create table test2(name char(4000);2 varchar2(size)变长，最大存放4000个字符举例说明特别说明：如果我们的数据的长度是固定的，比如商品编号（8位），则应当使用char来存放，因为这样存放速度快，如果存放的数据长度是变化的，则使用varchar23 nchar(size)1. 定长2. 编码方式unicode举例说明create table test4(name nchar(2);insert into test4 values(中国);/okcreate table test5 (name char(2);in

3、sert into test5 values(中国);/报错说明：一个汉字，占用nchar的一个字符空间，一个汉字，占用char的两个字符空间3. 最大字符长度20004 nvarchar2(size)1. 变长2. unicode编码3. 最大字符长度40005 clob(charater large object)字符型大数据对象1. 变长2. 8Tb6 blob(binary large object)二进制大数据对象1. 变长2. 最大8tb特别说明：我们在实际开发中很少把视频，图像文件存放数据库(效率问题)，实际上存放记录文件的一个路径(本地或url)，然后通过io/网络来操作如果我

4、们要求对文件安全性，可以考虑存放数据库。7 numberNUMBER(precision , scale) NUMBER(p,s)范围： 1 = p =38, -84 = s = 127保存数据范围：1.0e-130 = number value 1.0e+126 （正区间）科学计数法:1.0*10的-130-1.0e+126number3000;?如何查找1982.1.1后入职的员工select * from emp where hiredate1982-1-1;?如何显示工资在2000到2500的员工情况select * from emp where sal-=2000 and sal50

5、0 or job=manager) and ename like J%;使用order by子句order by 子句主要的用途是对结果进行排序显示?如何按照工资的从低到高的顺序显示雇员的信息select * from emp order by sal descend ascend;默认情况下，order by后面的字段是升序排序，如果希望降序，则desc?按照部门号升序而雇员的入职时间降序排列select ename,deptno,hiredate from emp order by deptno, hiredate desc;使用列的别名排序select sal*13+nvl(comm,0

6、)*13 年薪 from emp order by 年薪； 表的复杂查询数据分组-max,min,avg,sum,count?如何显示所有员工中最高工资和最低工资select max(sal) from emp;select min(sal) from emp;select max(sal),min(sal) from emp;?显示所有员工的平均工资和工资总和select avg(sal),sum(sal) from emp;avg(sal)会不会把sal为空统计进来？不会把sal为null统计进来，因此，如果希望为null的值也考虑，则我们可以这样做：select sum(sal)/cou

7、nt(*) from emp;?计算共有多少员工select count(*) from emp;说明:count(*)也可以针对一个字段进行统计，比如count(comm);扩展要求：?请显示工资最高的员工的名字，工作岗位思路：1. 查询出最高工资十多少？select max(sal) from emp;2. 看看谁的工资有这么多？select ename,job from emp where sal=( select max(sal) from emp);?请显示工资高于平均工资的员工信息1. 查询出平均工资是多少？select avg(sal) from emp;2.select ena

8、me,job,sal from emp where sal( select avg(sal) from emp);group by 和 having子句?如何显示每个部门的平均工资和最高工资select avg(sal),max(sal),deptno from emp group by deptno;?显示每个部门的每种岗位的平均工资和最低工资select avg(sal),min(sal),deptno,job from emp group by deptno,job?显示部门平均工资低于2000的部门号和它的平均工资思路1. 查处每个部门的平均工资select avg(sal),dept

9、no from emp group by deptno;2.select deptno,avg(sal) from emp group by deptno having avg(sal)2000;对数据分组的总结1分组函数只能出现在选择列表、having、order by子句中，where子句中不可以包含分组函数2 如果在select 语句中同时包含有group by ,having ,order by 那么他们的顺序是group by , having , order by3 在选择列表中如果有列、表达式、和分组函数,那么选择列表中任一非分组函数的所有列都应出现在group by 子句中，否

10、则就会出错 如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)all(select sal from emp where deptno=30);select ename,sal,deptno from emp where sal (select max(sal) from emp where deptno=30);4 any操作符如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号select ename,sal,deptno from emp where salany(selec

11、t sal from emp where deptno=30);select ename,sal,deptno from emp where sal (select min(sal) from emp where deptno=30);5 多列子查询如何查询与SMITH的部门和岗位完全相同的所有雇员1. 显示smith的部门和岗位是什么select deptno,job from emp where ename=SMITH;2. 显示雇员信息select * from emp where deptno=( select deptno from emp where ename=SMITH) an

12、d job=( select job from emp where ename=SMITH);select * from emp where (deptno,job)=( select deptno,job from emp where ename=SMITH);在from子句中使用子查询如何显示高于自己部门平均工资的员工的信息1. 显示部门平均工资select avg(sal),deptno from emp group by deptno;2. 把上面结果当作一个临时表来处理，显示员工信息select * from emp t2, (select avg(sal) myavg,deptno

13、 from emp group by deptno) t1 where t2.salt1.myavg and t1.deptno=t2.deptno;select e1.* from emp e1,t1.myavg where e1.sal(select avg(sal) myavg from emp where deptno= e1.deptno) t1!这个知识点一定要掌握查找每个部门工资最高的人的详细资料1. 先找出每个部门的最高工资select max(sal),deptno from emp group by deptno;2. 显示详细信息select t2.ename,t2.de

14、ptno,t2.sal,t1.mymax from emp t2,( select max(sal) mymax,deptno from emp group by deptno) t1 where t2.sal=t1.mymax and t2.deptno=t1.deptno;显示每个部门的信息（编号，名称）和人员数量显示每个部门的人员数量select deptno,count(*) from emp group by deptno;显示每个部门的信息select t2.deptno,t2.dname,t1.mynum from dept t2,( select deptno,count(*)

15、 mynum from emp group by deptno) t1 where t2.deptno=t1.deptno(+);+表示外连接这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个临时表来对待,当在from子句中使用子查询时，必须给子查询指定别名. 分页查询分页查询是我们学习数据库，必须掌握的一个要点mysql的分页查询select * from 表名 where 条件 limit 从第几条取, 取几条sql server:select top 3 * from 表名 where id not in(select top 3 id from 表名 where 条件)

16、排除前3条，再取3条，这个案例实际上取4-6oracle:select t2.* from (select t1.*,rownum rn from (select * from emp)t1 where rownum=4;三层过滤：1. 第一层过滤select * from emp where 条件 (多表查询)；2. 第二层过滤(select t1.*,rownum rn from (select * from emp)t1 where rownum=6)3. 第三层过滤select t2.* from (select t1.*,rownum rn from (select * from e

17、mp)t1 where rownum=4;实际上，我们可以把上面的sql语句当作一个模板来对待6：表示取到第几条4：表示从第几条开始取如果我们需要针对不同的情况，分页，请在最内层进行处理，包括多表请思考：请按照入职时间的先后顺序，查询从第7到第10个人都是谁？select t2.* from (select t1.*,rownum rn from (select * from emp_test)t1 where rownum=600000;看一下它的分页查询效率模拟100w的一个表create table emp_test as select * from emp;自我复制insert int

18、o emp_test select * from emp_test; 合并查询1)union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。select ename,sal,job from emp where sal2500 union select ename,sal,job from emp where job=MANAGER; 2)union all该操作赋与union相似，但是它不会取消重复行,而且不会排序。select ename,sal,job from emp where sal2500 union all select ename,sal,jo

19、b from emp where job=manager; 3)intersect使用该操作符用于取得两个结果集的交集。select ename,sal,job from emp where sal2500 intersect select ename,sal,job from emp where job=manager;4) minus使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。select ename,sal,job from emp where sal2500 minus select ename,sal,job from emp whe

20、re job=manager; oracle的内连接和外连接1 内连接内连接是我们用的做多一种连接，前面我们讲的都是内连接举例：比如我们显示员工的姓名和部门名称select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno; select emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno;2 外连接分为三种，左外连，右外连，完全外连-表stuid name 1, Jack2, Tom3, Kity4, nonocreate

21、table stu(id number,name varchar2(32);insert into stu values(1,jack);insert into stu values(2,tom);insert into stu values(1,kity);insert into stu values(1,nono);-表examid grade1, 562, 7611, 80create table exam(id number, grade number);显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空）select * from stu,exam where stu.id=exam.id;上面我们使用的内连接，它的特点是只有两张表同时匹配，才被选中使用左