oracle第2讲笔记2

■类（对象）与表（记录）的关系图

■创建表

基本语法

create table 表名(

列名列的数据类型,

...

)

举例说明

创建一张用户表

create table users(

id number,

name varchar2(32),

password varchar2(32),

birthday date);

■ oracle的数据类型

① char(size)

存放字符串，最大2000个字符，是定长。

举例说明

create table test1(name char(32));

这样，在name这列，最多只能存放32个字符，如果超过，就报错，如果不够’abc’,则用空格补全。

insert into test1 values(‘abc’);

create table test2(name char(4000));

② varchar2(size)

变长，最大存放4000个字符

举例说明

特别说明：如果我们的数据的长度是固定的，比如商品编号（8位），则应当使用char来存放，因为这样存放速度快，如果存放的数据长度是变化的，则使用varchar2

③ nchar(size)

1.定长

2.编码方式unicode

举例说明

create table test4(name nchar(2));

insert into test4 values(‘中国’);//ok

create table test5 (name char(2));

insert into test5 values(‘中国’);//报错

说明：一个汉字，占用nchar的一个字符空间，一个汉字，占用char的两个字符空间

3.最大字符长度2000

④ nvarchar2(size)

1.变长

2.unicode编码

3.最大字符长度4000

⑤ clob(charater large object)字符型大数据对象

1.变长

2.8Tb

⑥ blob(binary large object)二进制大数据对象

1.变长

2.最大8tb

特别说明：我们在实际开发中很少把视频，图像文件存放数据库(效率问题)，实际上存放记录文件的一个路径(本地或url)，然后通过io/网络来操作

如果我们要求对文件安全性，可以考虑存放数据库。

⑦ number

NUMBER[(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+126

保存在机器内部的范围： 1 ~ 22 bytes

1.number可以存放整数，也可以存放小数

2.语法number(p,s)

3.举例说明

4.是变长

特别说明：如果在实际开发中，我们明确要求保留到小数点几位，则明确指定，如果没有，可以直接使用number

⑧date日期类型

1.用于表示日期（年/月/日时/分/秒）

2.案例:

create table test _1 (birthday date);

insert into test_1 values('23-9月-2013');

添加时候，使用默认格式(dd-mm-yyyy)

oracle日期的默认格式dd-mm-yyyy,如果我们希望使用自己习惯的日期格式，也可以，需要借助oracle函数

⑧ timestamp

和date的主要区别，当更新数据时，timestamp类型自动更新为当前时间。

建表的综合案例

字段字段类型

Id整形

name字符型

sex字符型

brithday日期型

fellowship小数型

resume大文本型

create table students(

Id number,

name varchar2(64);

sex char(2),

birthday date,

fellowship number(10,2),

resume clob);

■表的管理---修改表

//添加一个字段

//修改字段类型

//删除一个字段

■SELECT查询语句

查询过程我们使用三张表

第一张表

EMPNO:雇员编号E NAME:名字 JOB:职位MGR:上级编号H IREDATE:入职日期 SAL:薪水COMM:奖金 DEPTNO:部门编号

DEPTNO:部门编号 DNAME:部门名 LOC:地点

G RADE:工资级别 LOSAL:最低工资 HISAL:最高工资

基本语法：

SELECT [DISTINCT]*|{列名1，列名2...} FROM 表名[WHERE {条件}]

●S ELECT 指定查询哪些列的数据。

●COLUMN指定列名。

●*号代表查询所有列。

●F ROM指定查询哪张表。

●WHERE是条件

●DISTINCT可选，指显示结果时，是否剔除重复数据

SELECT * FROM 表名

SELECT 列名1，列名2 FROM 表名

规定：尽可能返回较少的列，不要轻易使用*

如何取消重复行？

select distinct deptno,job from emp order by deptno;

什么是重复行？返回的数据完全一样才是重复行

?查询SMITH的薪水,工作,所在部门

SELECT SAL,JOB,DEPTNO FROM EMP WHERE ENAME=’SMITH’;

特别说明：ORACLE的SQL语句不区分大小写，但是查询的内容是区分大小写。

?显示每个雇员的年工资

select sal*13+nvl(comm,0)*13 年薪 from emp;

说明:nvl函数是oracle提供的，用于处理数据null的问题，基本用法nvl(comm,0)；如果comm为空null，则返回0,如果不为空，则返回本身的值||的使用

在查询时候，如果希望将多列拼接起来，作为一列返回，可以使用||

具体用法:

select ename||'是一个'||job from emp;

where子句

?如何显示工资高于3000的员工

SELECT * FROM EMP WHERE SAL>3000;

?如何查找1982.1.1后入职的员工

SELECT * FROM EMP WHERE HIREDATE>’1982-1-1’;

?如何显示工资在2000到2500的员工情况

SELECT * FROM EMP WHERE SAL>-=2000 AND SAL<=2500;

select * from emp where sal between 2000 and 2500;

如何使用LIKE操作符

%表示0到多个字符

_表示任意一个字符

?如何显示首字符为S的员工姓名和工资

SELECT ENAME,SAL FROM EMP WHERE ENAME LIKE ‘S%’;

?如何显示第三个字符为大写O的所有员工的姓名和工资

SELECT ENAME,SAL FROM EMP WHERE ENAME LIKE ‘__O%’;

在WHERE条件中使用IN

?如何显示EMPNO为123,345,800...的雇员情况

SELECT * FROM EMP WHERE EMPNO=123 OR EMPNO=345 OR EMPNO=800;

SELECT * FROM EMP WHERE EMPNO IN (123,345,800);

使用IS NULL的操作符

?如何显示没有上级的雇员的情况

SELECT * FROM EMP WHERE MGR IS NULL;

使用逻辑操作符号

?查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J

SELECT * FROM EMP WHERE (SAL>500 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)*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)/COUNT(*) 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 ENAME,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),DEPTNO 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 BY

3在选择列表中如果有列、表达式、和分组函数,那么选择列表中任一非分组函数的所有列都应出现在GROUP BY 子句中，否则就会出错

如SELECT DEPTNO,AVG(SAL),MAX(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)<2000; 这里DEPTNO就一定要出现在 GROUP BY 中

■自连接

比如显示’FORD’的上级.

思路：1.显示ford上级编号

select mgr from emp where ename=’FORD’;

2.显示ford上级的信息

select * from emp where empno=( select mgr from emp where ename=’FORD’); 显示员工的上级领导的姓名

思路：把emp表看作两个表，一个表是雇员表worker,一个表是boss表

select worker.ename,boss.ename from emp worker, emp boss where worker.mgr=boss.empno;

疑惑：这里我们看到KING没有显示，因为他没有上级，如果希望把没有上级的人显示出来，则需要使用外连接。

■子查询

什么是子查询？子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询①单行子查询

如何显示与SMITH同一部门的所有员工?

思路：1.SMITH在哪个部门

select deptno from emp where ename=’SMITH’;

2.显示该部门所有员工

select * from emp where deptno=( select deptno from emp where ename=’SMITH’);

②多行子查询

如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

1.先查询10号部门有哪些职位

select job from emp where deptno=10;

2.显示信息

select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);

③all操作符

如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号select ename,sal,deptno from emp where 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);

④any操作符

如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);

select ename,sal,deptno from emp where sal> (select min(sal) from emp where deptno=30);

⑤多列子查询

如何查询与SMITH的部门和岗位完全相同的所有雇员

1.显示smith的部门和岗位是什么

select deptno,job from emp where ename=’SMITH’;

2.显示雇员信息

select * from emp where deptno=( select deptno from emp where ename=’SMITH’) and 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 from emp group by deptno) t1 where t2.sal>t1.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.deptno,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(*) 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 条件)

排除前3条，再取3条，这个案例实际上取4-6

oracle:

select t2.* from (select t1.*,rownum rn from (select * from emp)t1 where rownum<=6) t2 where rn>=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 emp)t1 where rownum<=6) t2 where rn>=4;

实际上，我们可以把上面的sql语句当作一个模板来对待

6：表示取到第几条

4：表示从第几条开始取

如果我们需要针对不同的情况，分页，请在最内层进行处理，包括多表

请思考：请按照入职时间的先后顺序，查询从第7到第10个人都是谁？

select t2.* from (select t1.*,rownum rn from (select * from emp_test)t1 where rownum<=600010) t2 where rn>=600000;

看一下它的分页查询效率

模拟100w的一个表

create table emp_test as select * from emp;

自我复制

insert into emp_test select * from emp_test;

■合并查询

1)UNION

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>2500 UNION

SELECT ENAME,SAL,JOB FROM EMP WHERE JOB=‘MANAGER';

2)UNION ALL

该操作赋与UNION相似，但是它不会取消重复行,而且不会排序。

SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>2500

UNION ALL SELECT ENAME,SAL,JOB FROM EMP WHERE

JOB='MANAGER';

3)INTERSECT

使用该操作符用于取得两个结果集的交集。

SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>2500

INTERSECT SELECT ENAME,SAL,JOB FROM EMP WHERE

JOB='MANAGER';

4) MINUS

使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。

SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>2500 MINUS

SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER';

■ORACLE的内连接和外连接

①内连接

内连接是我们用的做多一种连接，前面我们讲的都是内连接

举例：比如我们显示员工的姓名和部门名称

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;

②外连接

分为三种，左外连，右外连，完全外连

--表STU

ID NAME

1,J ACK

2,T OM

3,K ITY

4, NONO

CREATE 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’);

--表EXAM

ID GRADE

1,56

2,76

11,80

CREATE TABLE EXAM(ID NUMBER, GRADE NUMBER);

显示所有人的成绩，如果没有成绩，也要显示该人的姓名和ID号,成绩显示为空）select * from stu,exam where stu.id=exam.id;

上面我们使用的内连接，它的特点是只有两张表同时匹配，才被选中

使用左外连

SELECT STU.ID,https://www.360docs.net/doc/a611704318.html,,EXAM.GRADE FROM STU LEFT JOIN EXAM ON STU.ID=EXAM.ID;

如果STU（左表）的记录没有和EXAM任何一条记录匹配，也要被选中

怎么判断一张表是左表？如果在LEFT JOIN的左边，就是左表

select * from stu,exam where stu.id=exam.id(+);

把+写在右边，表示左外连

■右外连

（显示所有成绩，如果没有名字匹配，显示空）

SELECT STU.ID,https://www.360docs.net/doc/a611704318.html,,EXAM.GRADE FROM STU RIGHT JOIN EXAM ON STU.ID=EXAM.ID; select * from stu,exam where stu.id(+)=exam.id;（+写在左边是右外连）说明：右外连，指的是右边的表如果没有和左边的表任何一条记录匹配，也要被选中

小结：实际上，左外连和右外联是可以互为转换的

（显示所有成绩，如果没有名字匹配，显示空）

（1）SELECT STU.ID,https://www.360docs.net/doc/a611704318.html,,EXAM.GRADE FROM STU RIGHT JOIN EXAM ON STU.ID=EXAM.ID; （2）select * from stu,exam where stu.id(+)=exam.id;

（3）SELECT STU.ID,https://www.360docs.net/doc/a611704318.html,,EXAM.GRADE FROM EXAM LEFT JOIN STU ON STU.ID=EXAM.ID;

(4) select * from stu,exam where exam.id=stu.id(+);

完全外连

显示所有成绩和所有人的名字，如果相应的匹配值，则显示空）

SELECT https://www.360docs.net/doc/a611704318.html,,EXAM.GRADE,STU.ID FROM STU FULL JOIN EXAM ON STU.ID=EXAM.ID;

列出部门名称和这些部门的员工姓名，同时列出那些没有员工的部门。(要求4种写法)

SELECT EMP.ENAME,DEPT.DNAME FROM DEPT LEFT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO; SELECT EMP.ENAME,DEPT.DNAME FROM DEPT , EMP WHERE DEPT.DEPTNO=EMP.DEPTNO(+); SELECT EMP.ENAME,DEPT.DNAME FROM EMP RIGHT JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO; SELECT EMP.ENAME,DEPT.DNAME FROM DEPT , EMP WHERE EMP.DEPTNO(+)=DEPT.DEPTNO;