oracle基础教程（第二天）

一、oracle表的管理

表名和列的命名规范

1.必须以字母开头

2.长度不能超过30字符

3.不能使用oracle的保留字（关键字）

4.只能使用如下字符 A-Z，a-z，0-9，$,#等

oracle支持的数据类型

1.字符型

     char    定长  最大2000字符         比较占用空间，但是对于定长的数据很实用，查询速度极快，例如身份证号字段设置为char(18)

    例子：char(10) '小韩'    前四个字符放'小韩'，后添6个空格不全

     varchar2  变长  最大4000字符

    例子：varchar2(10)  '小韩'  oracle分配四个字符，这样可以节省空间

      clob(character large object)  字符型大对象  最大4G    

2.数字型

    number  范围  -10的38次方-10的38次方

    可以表示整数，也可以表示小数

     number(5,2)

    表示一个小数有5位有效数，2位小数

    范围：-999.99~999.99

     number(5)

    表示一个五位整数

    范围：-99999~99999

3.日期类型

    date                    包括年月日和时分秒

    timestamp          这是oracle9i对date数据类型的拓展

4.图片类型

blob    二进制数据    可以存放图片/声音  最大4G

建表

--学生表

    SQL>create table student(  --  表名

    xh number(4),    --    学号

    xm varchar2(20),    --    姓名

    sex char(2),    --    性别

    birthday date,    --    出生日期

    sal number(7,2)    --    奖学金

    );

--班级表

    SQL>create table class(

    classId number(2),

    cname varchar2(20)

    );

修改表

添加一个字段

SQL> alter table student  add (classId number(2));

修改字段的长度

SQL> alter table student  modify (xm varchar2(30));

修改字段的类型/或是名字（不能有数据）

SQL>alter table student modify (xm char(30));

删除一个字段

SQL>alter table student drop column sal;

修改表的名字

SQL>rename student to stu;

删除表

SQL>drop table student;

添加数据

所有字段都插入

SQL>insert into student values ('A001','张三','男','01-5月-05',,10);

ORACLE中默认的日期格式'DD-MON-YY'  dd日子（天） mon 月份  yy 2位的年  '09-6月-99'  1999年6月9日

改日期的默认格式

SQL>alter session set nls_date_format = 'yyyy-mm-dd';

修改后，可以用我们熟悉的格式添加日期类型：

SQL>insert into student values ('A002','MIKE','男','1905-05-06',,10);

插入部分字段

SQL>insert into student (xh,xm,sex) values ('A003','JOHN','女');

插入空值

SQL>insert into student (xh,xm,sex,birthday) values ('A004','MARTIN','男'，null);

改一个字段

SQL>update student set sex='女' where xh='A001';

修改多个字段

SQL>update student set sex='男',birthday='1980-04-01' where xh='A001';

删除数据

SQL>delete from student;

删除所有记录，表结构还在，写日志，可以恢复，速度慢

需要设置保存点： savepoint aa;  回滚保存点： rollback to aa;

SQL> drop table student;   删除表的结构和数据
SQL> delete from student where xh='A001';  删除一条记录
SQL> truncate table student;

删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快

简单的查询语句

查看表结构

SQL>desc dept;

查询所有列

SQL>select * from dept;

查询指定列

SQL>select ename,sal,job,deptno from emp;

如何取消重复行

SQL>select distinct deptno,job from emp;

使用算术表达式显示每个雇员的年工资

SQL>select sal*12+nvl(comm,0)*12     "年工资",ename,comm from emp;

使用列的别名

SQL>select ename "姓名"，sal*12 "年收入" from emp;

如何处理null值

使用nvl函数来处理

如何连接字符串(||)

SQL>select ename || ' is a ' || job from emp;

使用where子句

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

SQL> select * from emp where sal >=3000;

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

SQL> select * from emp where hiredate > '1-1月-1982';

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

SQL> select ename,sal from emp where sal between 2000 and 2500;

如何使用like操作符

%：表示任意0到多个字符    _：表示任意单个字符

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

SQL> select ename,sal from emp where ename like 'S%';

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

SQL> select ename,sal from emp where ename like '__O%';

在where条件中使用in

如何显示empno为123,345,800...的雇员情况？

SQL>select ename,empno from emp where empno in (123,345,7844);

使用is null的操作符

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

SQL> select * from emp where mgr is null;

使用逻辑操作符号

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

SQL> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

使用order by子句

如何按照工资的从低到高的顺序显示雇员的信息？

SQL> select * from emp order by sal;

按照部门号升序而雇员的工资降序排列

SQL> select * from emp order by deptno,sal desc;

使用列的别名排序

SQL>select ename,sal*12 "年薪" from  emp order by "年薪";

oracle表复杂查询 - 单表查询

说明：在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句

数据分组函数-max,min,avg,sum,count

如何显示所有员工中最高工资和最低工资？

SQL>select max(sal),min(sal) from emp;

显示所有员工的平均工资和工资总和？

SQL> select sum(sal),avg(sal) from emp;

计算共有多少员工？

SQL> select count(*) from emp;

扩展要求：

请显示工资最高的员工的名字，工作岗位

SQL> select ename,sal from emp where sal = (select max(sal) from emp);

请显示工资高于平均工资的员工信息

SQL> select * from emp where sal > (select avg(sal) from emp);

group by和having子句

group by用于对查询的结果进行分组显示

having子句用于限制分组显示结果

如何显示每个部门的平均工资和最高工资

SQL>select avg(sal),max(sal),deptno from emp group by deptno;

显示每个部门的每种岗位的平均工资和最低工资

SQL>select avg(sal),min(sal),deptno,job from emp group by deptno,job;

显示平均工资低于2000的部门号和他的平均工资

SQL> select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;

对数据分组的总结

1.分组函数只能出现在选择列表、having、order by子句中

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中。

oracle表复杂查询 - 多表查询

说明

多表查询是指基于两个和两个以上的表或是视图的查询，在实际应用中，查询单个表可能不满足你的需求，（如显示sales部门位置和其员工的姓名），这种情况下需要使用到（dept表和emp表）

显示雇员名，雇员工资及其所在部门的名字【笛卡尔积】

规定：多表查询的条件是 至少不能少于表的个数-1

SQL> select t1.ename,t1.sal,t2.dname,t2.deptno from emp t1,dept t2 where t1.deptno=t2.deptno;

如何显示部门号为10的部门名、员工名和工资

SQL>select t1.ename,t1.sal,t2.dname,t2.deptno from emp t1,dept t2 where t1.deptno=t2.deptno and t1.deptno=10 ;

SQL> select t1.dname,t2.ename,t2.sal from dept t1,(select ename,sal,deptno from emp where deptno=10) t2 where t1.deptno=t2.deptno;

显示各个员工的姓名，工资，及其工资的级别

SQL> select t2.grade,t1.ename,t1.sal from emp t1,salgrade t2 where t1.sal between t2.losal and t2.hisal;

扩展要求：

显示雇员名，雇员工资及其所在部门的名字，并按部门排序。

SQL> select t2.ename,t2.sal,t1.dname,t1.deptno from dept t1,emp t2 where t1.deptno=t2.deptno order by t1.deptno;

自连接

自连接是指在同一张表的连接查询

显示某个员工的上级领导的姓名

SQL> select a1.ename,a1.mgr,a2.ename from emp a1,emp a2 where a1.mgr=a2.empno;

什么是子查询

子查询是指嵌入到其他sql语句中的select语句，也叫嵌套查询

单行子查询是指只返回一行数据的子查询语句

请思考，如何显示与smith同一部门的所有员工。

SQL> select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查询是指返回多行数据的子查询语句

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

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

在多行子查询中使用all操作符

请思考，如何显示    工资比部门30的所有员工的工资高的员工的姓名，工资和部门号

SQL>select * from emp where sal > all (select sal from emp where deptno=30);

扩展要求：

大家想想还有没有别的查询方法。

SQL> select * from emp where sal > (select max(sal) from emp where deptno=30);

在多行子查询中使用any操作符

请思考，如何显示工资比部门30的任意一名员工的工资高的员工的姓名，工资和部门号

SQL> select * from emp where sal > any (select sal from emp where deptno=30);

扩展要求：

大家想想还有没有别的查询方法。

SQL>select * from emp where sal > (select min(sal) from emp where deptno=30);

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

请思考如何查询与smith的部门和岗位完全相同的所有雇员

SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

在from子句中使用子查询

请思考，如何显示高于自己部门平均工资的员工的信息

SQL> select * from emp t1,(select deptno,avg(sal) avg_sal from emp group by deptno) t2 where t1.sal>t2.avg_sal and t1.deptno=t2.deptno;

这里需要说明的是当在from子句中使用子查询的时候，该子查询会被作为一个视图来对待，因此叫做内嵌视图。党在from子句中使用子查询时，必须给子查询指定别名。

分页查询

oracle分页一共有三种方式：

1、rownum分页

(select * from emp)

2、显示rownum[oracle分配的]

SQL>select a1.*,rownum rn from (select * from emp) a1;

3、按rownum分页查询

SQL>select * from (select a1.*,rownum rn from (select * from emp) a1) where rn<=10;

SQL>select * from (select * from (select a1.*,rownum rn from (select * from emp) a1) where rn<=10) where rn>=6;

4、几个查询变化

a.指定查询列，只需要修改最里层的子查询

b.如何排序，只需要修改最里层的子查询

用查询结果创建新表

这个命令是一种快捷的建表方法

SQL>create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

合并查询

有时候在实际应用中，为了合并多个select语句的查询结果，可以使用集合操作符号，union(合并去重)，union all(合并不去重)，intersect(取交集)，minus(取差集)。 比and，or的效率高很多。

在oracle中操作数据-使用特定格式插入日期值

如何插入列带有日期的表，并按照年-月-日的格式插入。

SQL> insert into emp values('9996','ROSE','CLERK','7902',to_date('1993/7/22','yyyy/mm/dd'),'1000.00','200.00','10');

SQL>select * from emp where to_char('RKSJ','yyyy-mm-dd') >='2016-05-12';

在oracle中操作数据-使用子查询插入数据

当使用values子句时，一次只能插入一行数据，当使用子查询插入数据时，一条insert语句可以插入大量的数据。当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。

SQL> insert into kkk (myid,myname,mydept) select empno,ename,deptno from emp where deptno = 10;

在oracle中操作数据-使用子查询更新数 据

使用update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可以使用子查询修改数据

希望员工scott的岗位、工资和补助与smith员工一样。

SQL> update emp set (job,sal,comm) = (select job,sal,comm from emp where ename = 'SMITH') where ename = 'SCOTT';