數據處理 - 877599949yan－IT工程師數位筆記本

文章出處

SQL> /*
SQL> SQL 的類型:
SQL> 1. DML : 數據操作語言 insert/update/delete/select
SQL> 2. DDL : 數據定義語言 create/alter/drop/truncate
SQL> 3. DCL : 數據控制語言 grant(授權) revoke(移除授權)
SQL> */
SQL> -- insert 向表里面添加一行記錄
SQL> desc emp;
名稱                                                                                                              是否為空? 類型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO                                                                                                             NOT NULL NUMBER(4)
ENAME                                                                                                                      VARCHAR2(10)
JOB                                                                                                                        VARCHAR2(9)
MGR                                                                                                                        NUMBER(4)
HIREDATE                                                                                                                   DATE
SAL                                                                                                                        NUMBER(7,2)
COMM                                                                                                                       NUMBER(7,2)
DEPTNO                                                                                                                     NUMBER(2)

SQL> insert into emp(empno,job,sal) values(2016,'SAL',8000);

已創建 1 行。

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      2016                      SAL                                                8000


      7369 SMITH                CLERK                    7902 17-12月-80            800                    20


      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500


      7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------


      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850


      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10


      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                    20

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------

      7839 KING                 PRESIDENT                     17-11月-81           5000                    10


      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30


      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                    20


      7900 JAMES                CLERK                    7698 03-12月-81            950                    30

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------


      7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20


      7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10

已選擇15行。

SQL> -- 按照表的順序插入所有的數據
SQL> desc emp;
名稱                                                                                                              是否為空? 類型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO                                                                                                             NOT NULL NUMBER(4)
ENAME                                                                                                                      VARCHAR2(10)
JOB                                                                                                                        VARCHAR2(9)
MGR                                                                                                                        NUMBER(4)
HIREDATE                                                                                                                   DATE
SAL                                                                                                                        NUMBER(7,2)
COMM                                                                                                                       NUMBER(7,2)
DEPTNO                                                                                                                     NUMBER(2)

SQL> insert into emp values(2015,'zhangsan','MANAGER',2016,'18-3月-16',5000,3000,20);

已創建 1 行。

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      2016                      SAL                                                8000


      2015 zhangsan             MANAGER                  2016 18-3月 -16           5000       3000         20
      7369 SMITH                CLERK                    7902 17-12月-80            800


      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20


      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850


      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10


      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                    20

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------


      7839 KING                 PRESIDENT                     17-11月-81           5000                    10


      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30


      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                    20

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7900 JAMES                CLERK                    7698 03-12月-81            950                    30


      7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20


      7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10

已選擇16行。

SQL> select * from v$_nls_parameters;
select * from v$_nls_parameters
*
第 1 行出現錯誤:
ORA-00942: 表或視圖不存在

SQL> select * from v$nls_parameters;

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE

NLS_TERRITORY
CHINA

NLS_CURRENCY
￥

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA

NLS_NUMERIC_CHARACTERS
.,

NLS_CALENDAR
GREGORIAN

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE

NLS_CHARACTERSET
AL32UTF8

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
￥

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

已選擇19行。

SQL> set linesize 200;
SQL> /

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA

NLS_NUMERIC_CHARACTERS
.,

NLS_CALENDAR
GREGORIAN

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE

NLS_CHARACTERSET
AL32UTF8

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
￥

PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

已選擇19行。

SQL> col parameter for a30;
SQL> /

PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE                   SIMPLIFIED CHINESE
NLS_TERRITORY                  CHINA
NLS_CURRENCY                   ￥
NLS_ISO_CURRENCY               CHINA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              SIMPLIFIED CHINESE
NLS_CHARACTERSET               AL32UTF8
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              ￥
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

已選擇19行。

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

會話已更改。

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      2016                      SAL                                            8000


      2015 zhangsan             MANAGER                  2016 2016-03-18       5000       3000         20
      7369 SMITH                CLERK                    7902 1980-12-17        800


      7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22       1250        500

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20


      7654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 1981-05-01       2850


      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10


      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------


      7839 KING                 PRESIDENT                     1981-11-17       5000                    10


      7844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         30


      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7900 JAMES                CLERK                    7698 1981-12-03        950                    30


      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20


      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已選擇16行。

SQL> -- create 創建一個表
SQL> -- 用模板快速的創建一個表
SQL> create table testemp as select * from emp where 1= 2;

表已創建。

SQL> desc testemp;
名稱                                                                                                              是否為空? 類型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO                                                                                                                      NUMBER(4)
ENAME                                                                                                                      VARCHAR2(10)
JOB                                                                                                                        VARCHAR2(9)
MGR                                                                                                                        NUMBER(4)
HIREDATE                                                                                                                   DATE
SAL                                                                                                                        NUMBER(7,2)
COMM                                                                                                                       NUMBER(7,2)
DEPTNO                                                                                                                     NUMBER(2)

SQL> -- 快速的插入數據
SQL> insert into testemp as select * from emp;
insert into testemp as select * from emp
*
第 1 行出現錯誤:
ORA-00926: 缺失 VALUES 關鍵字

SQL> insert into testemp values as select * from emp;
insert into testemp values as select * from emp
*
第 1 行出現錯誤:
ORA-00936: 缺失表達式

SQL> insert into tempemp values select * from emp;
insert into tempemp values select * from emp
*
第 1 行出現錯誤:
ORA-00936: 缺失表達式

SQL> insert into testemp values (select * from emp);
insert into testemp values (select * from emp)
*
第 1 行出現錯誤:
ORA-00936: 缺失表達式

SQL> insert into testemp from (select * from emp);
insert into testemp from (select * from emp)
*
第 1 行出現錯誤:
ORA-00926: 缺失 VALUES 關鍵字

SQL> insert into testemp select * from emp;

已創建16行。

SQL> -- 批量操作的時候不要加關鍵字
SQL> select * from emp;

已選擇16行。

SQL> /*
SQL> 對于大數據的導入操作:
SQL> 1. 數據泵
SQL> 2. SQL* Loader 安裝以后就有的,oracle默認提供的
SQL> 3. 外部表
SQL> */
SQL> -- 創建表續
SQL> -- 創建一個沒有模板的表
SQL> create table testemp1(
2   id number(5),
3   name varchar(20),
4   weight number(5,2)
5 );

表已創建。

SQL> desc testemp1;
名稱                                                                                                              是否為空? 類型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID                                                                                                                         NUMBER(5)
NAME                                                                                                                       VARCHAR2(20)
WEIGHT                                                                                                                     NUMBER(5,2)

SQL> -- varchar 和varchar2 都可以用來表示字符串,但是在oracle中推薦用varchar2,varchar是每一個字段固定占N個字節,不管內容是否足夠N個長度,而varchar2 會根據內容自己調整他的長度。
SQL> desc emp;
名稱                                                                                                              是否為空? 類型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO                                                                                                             NOT NULL NUMBER(4)
ENAME                                                                                                                      VARCHAR2(10)
JOB                                                                                                                        VARCHAR2(9)
MGR                                                                                                                        NUMBER(4)
HIREDATE                                                                                                                   DATE
SAL                                                                                                                        NUMBER(7,2)
COMM                                                                                                                       NUMBER(7,2)
DEPTNO                                                                                                                     NUMBER(2)

SQL> create table testemp2 (
2 id number,
3 riqi date
4 );

表已創建。

SQL> desc testemp2;
名稱                                                                                                              是否為空? 類型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID                                                                                                                         NUMBER
RIQI                                                                                                                       DATE

SQL> insert into testemp2 values(1,'2016-03-18');

已創建 1 行。

SQL> select * from testemp2;

        ID RIQI
---------- ----------
         1 2016-03-18

SQL> spool off;

文章列表