Lihb +

Oracle培训笔记二

要点四:批量导入数据

从txt文本中导入数据到oracle

用到的命令为:sqlldr

准备工作:

数据源

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函数

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

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

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

注释1

SQL> comment on column stu1.id is '学生学号';

Comment added

注释2

视图

视图的本质是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;    //解锁
点击查看评论

Blog

Knowledge

Project