Oracle培训笔记二
2014-07-25
要点四：批量导入数据
从txt文本中导入数据到oracle
用到的命令为：sqlldr
准备工作：
- 数据源文件（data.txt）
- 制作ctl文件（1.ctl）
load data
infile 'c:\Test\data.txt' // 数据文件的位置
(append)replace into table stu1 // (追加)替换stu1表中的数据
fields terminated by "," optionally enclosed "" //用逗号作为列分隔符
(id,name,age,email) //字段名
- 运行命令
1.导入前
SQL> select * from stu1; //无数据
ID NAME EMAIL AGE
------- ------ -------- ------
2.导入操作
C:\>sqlldr test/123 control=c:\Test\1.ctl //命令语法：test/123为账户，然后是控制文件的路径
SQL*Loader: Release 11.2.0.1.0 - Production on 星期五 7月 25 11:11:22 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
达到提交点 - 逻辑记录计数 3
达到提交点 - 逻辑记录计数 4
3. 导入后
SQL> select * from stu1; //有数据
ID NAME EMAIL AGE
------- ------------- ------------------ -----------------
1 java1 java1@126.com 18
2 java2 java2@126.com 45
3 java3 java3@126.com 56
100 java100 java100@126.com 67
从excel表中导入数据
用到的软件：XlsToOra
ps：只能导入xls文件，xlsx文件需进行转换。
常用命令
复制表
SQL> create table emp as select * from scott.emp; // 该账户先要有resource权限和scott.emp的select权限
Table created
SQL> select * from emp; //有表结构和数据
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
仅复制表结构
SQL> create table dept as select * from scott.dept where 1=2;
Table created
SQL> select * from dept; //有表结构，无数据
DEPTNO DNAME LOC
------ -------------- -------------
查询是否空值（is null， is not null）
SQL> select * from emp where comm is not null; //查询comm字段不为空值的数据
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
别名
SQL> select dname bumenmc from dept; //dname别名为bumenmc，bumenmc名称前可加as，也可不加
BUMENMC
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
拼接
SQL> select dname||'CN' from dept;
DNAME||'CN'
----------------
ACCOUNTINGCN // 查询的值后面都追加了'CN'
RESEARCHCN
SALESCN
OPERATIONSCN
过滤重复（distinct）
SQL> select job from emp; // 直接查询，可以发现有很多重复值
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
14 rows selected
SQL> select distinct job from emp; //过滤查询，distinct关键字
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
oracle中的不等于（<>）
SQL> select * from emp where job <>'SALESMAN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
10 rows selected
区间（between and）
SQL> select * from emp where sal between 2450 and 3000; //是包含两端的边界值的
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
升降序（asc，desc）
SQL> select * from emp where sal between 2450 and 3000 order by sal; //默认是升序排列
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
SQL> select * from emp where sal between 2450 and 3000 order by sal desc; //按sal的降序排列
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
count函数
SQL> select count(\*) from emp; //查询emp共有多少条记录
COUNT(\*)
----------
14
SQL> select count(job) from emp;
COUNT(JOB)
----------
14
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
ps：count函数查询是不包含空值的。亦即不统计空值。
avg函数
例子：查询每个部门的平均薪水
SQL> select deptno,avg(sal) a from emp group by deptno order by a;
DEPTNO A
------ ----------
30 1566.66666
20 2175
10 2916.66666
ps：命令解析：先group分组，再算每个组的平均薪水，然后按平均薪水升序排列。
max函数
例子：查询工资最高的员工信息。
SQL> select * from emp where sal=(select max(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING PRESIDENT 1981-11-17 5000.00 10
ps：命令解析：从右往左看，先查出最高薪水，然后查出谁的薪水和最高薪水相等。用到了子查询。
min函数
例子：查询工资最低的员工信息
SQL> select * from emp where sal=(select min(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
sum函数
例子：查询工资总数目
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
nvl函数
- 用途：前面说的count函数是不统计空值的，如果想统计空值，需要nvl函数
- 语法：nvl(字段名,xxx)把该字段为空的数据，看作xxx处理
SQL> select count(comm) Num from emp;
NUM
----------
4 // 不统计空值，结果为4
SQL> select count(nvl(comm,999999)) Num from emp;
NUM
----------
14 // 统计空值，结果为14
表的多种连接方式（内连接、外连接、自连接）
参考文献：详解Oracle的多种表连接方式
例子
- 部门平均工资的最大值
SQL> select max(avg(sal)) from emp group by deptno;
MAX(AVG(SAL))
-------------
2916.66666666
-
子查询
- 查询工资高于jones员工的姓名和工资
select ename,sal from emp where sal>=(select sal from emp where ename='JONES');
ENAME SAL
---------- ---------
JONES 2975.00
SCOTT 3000.00
KING 5000.00
FORD 3000.00
- 查询比manager职位所有（all）的员工的工资都低的员工姓名和工资
SQL> select ename,sal from emp where sal < all(select sal from emp where job ='MANAGER');
ENAME SAL
---------- ---------
ALLEN 1600.00
TURNER 1500.00
MILLER 1300.00
WARD 1250.00
MARTIN 1250.00
ADAMS 1100.00
JAMES 950.00
SMITH 800.00
8 rows selected
- 查询比manager职位任一（any）员工的工资都低的员工姓名和工资
SQL> select ename,sal from emp where sal < ANY(select sal from emp where job ='MANAGER');
ENAME SAL
---------- ---------
SMITH 800.00
JAMES 950.00
ADAMS 1100.00
WARD 1250.00
MARTIN 1250.00
MILLER 1300.00
TURNER 1500.00
ALLEN 1600.00
CLARK 2450.00
BLAKE 2850.00
10 rows selected
- 查询平均工资最高的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal) =(select max(avg(sal)) from emp group by deptno);
DEPTNO AVG(SAL)
------ ----------
10 2916.66666
查询表
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
STU1
EMP
DEPT
T1
删除表
1.
SQL> drop table t1;
Table dropped
2.
SQL> truncate table t1; //截断表，快速删除
Table truncated
注释comment
- 给表添加注释
SQL> comment on table stu1 is '学生表';
Comment added
- 给字段加注释
SQL> comment on column stu1.id is '学生学号';
Comment added
视图
视图的本质是sql语句。
创建视图
SQL> create or replace view v1 //有v1视图则替换，无则创建
2 as
3 select ename,sal from emp
4 where sal>(select sal from emp where ename='JONES');
View created
SQL> select * from v1; //查询视图
ENAME SAL
---------- ---------
SCOTT 3000.00
KING 5000.00
FORD 3000.00
删除视图
SQL> drop view v1; //删除视图
View dropped
select * from v1
ORA-00942: 表或视图不存在
其他常用操作
1. 描述表结构
desc(ribe)
2. 插入数据
insert into t1 values(1,'java1');
3. 更新数据
update t1 set id = 2 where name ='java1';
update t1 set name = ''; // 删除一列数据
4. 给表添加字段
alter table t1 add age int;
5. 修改字段长度
alter table t1 modify name varchar(50);
6. 修改字段类型
alter table t1 modify age varchar(10);
7. 修改表中字段名称
alter table t1 rename column age to email;
8. 给表重命名
rename t1 to t2;
9. 删除表的一个字段
alter table t2 drop column email; //即使有该字段有数据，也会被删除
10. 修改用户密码
alter user test identified by 新密码;
11. 显示当前连接用户
show user;
12. 锁定用户和解锁用户
alter user test account lock; //锁定
alter user test account unlock; //解锁