淘先锋技术网

首页 1 2 3 4 5 6 7

oracle常用sql语句（不断更新中）

管理员登陆：

C:\>sqlplus "system/manager@orcl"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 1月 5 19:30:54 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

显示当前连接用户： 

SQL> show user
USER 为 "SYSTEM"

查看系统拥有哪些用户： 

SQL> select * from all_users;

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------------
USER1                                  61 01-1月 -14
BI                                     60 01-1月 -14
PM                                     59 01-1月 -14
SH                                     58 01-1月 -14
IX                                     57 01-1月 -14
OE                                     56 01-1月 -14
HR                                     55 01-1月 -14
SCOTT                                  54 30-8月 -05
MGMT_VIEW                              53 30-8月 -05
MDDATA                                 50 30-8月 -05
SYSMAN                                 51 30-8月 -05

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------------
MDSYS                                  46 30-8月 -05
SI_INFORMTN_SCHEMA                     45 30-8月 -05
ORDPLUGINS                             44 30-8月 -05
ORDSYS                                 43 30-8月 -05
OLAPSYS                                47 30-8月 -05
ANONYMOUS                              39 30-8月 -05
XDB                                    38 30-8月 -05
CTXSYS                                 36 30-8月 -05
EXFSYS                                 34 30-8月 -05
WMSYS                                  25 30-8月 -05
DBSNMP                                 24 30-8月 -05

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------------
TSMSYS                                 21 30-8月 -05
DMSYS                                  35 30-8月 -05
DIP                                    19 30-8月 -05
OUTLN                                  11 30-8月 -05
SYSTEM                                  5 30-8月 -05
SYS                                     0 30-8月 -05

已选择28行。

新建用户并授权： 

SQL> CREATE USER USER1
  2  IDENTIFIED BY USER1
  3  DEFAULT TABLESPACE USERS
  4  TEMPORARY TABLESPACE TEMP
  5  PROFILE DEFAULT;

用户已创建。

SQL> GRANT CONNECT,RESOURCE TO USER1;

授权成功。

连接到用户user1： 

SQL> CONN USER1/USER1;
已连接。

创建表：

SQL> CREATE TABLE TB1(ID INTEGER PRIMARY KEY);

表已创建。

查询表结构： 

SQL> DESC TB1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)

修改用户密码： 

SQL> ALTER USER USER1 IDENTIFIED BY USER1;

用户已更改。

增加字段： 

SQL> ALTER TABLE TB1 ADD (USERNAME VARCHAR2(10) NULL);

表已更改。

SQL> DESC TB1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)
 USERNAME                                           VARCHAR2(10)

修改字段： 

SQL> ALTER TABLE TB1 MODIFY (USERNAME VARCHAR2(20));

表已更改。

创建索引： 

SQL> CREATE INDEX TB1_USERNAME_INDEX ON TB1 (USERNAME);

索引已创建。

删除索引： 

SQL> DROP INDEX TB1_USERNAME_INDEX;

索引已删除。

删除字段： 

SQL> ALTER TABLE TB1 DROP (USERNAME);

表已更改。

删除表： 

SQL> DROP TABLE TB1;

表已删除。

删除用户： 

SQL> CONN SYSTEM/MANAGER
已连接。
SQL> DROP USER USER1;

用户已删除。

创建主键自增长的表：

CREATE TABLE TB_STUDENT (
    ID INTEGER PRIMARY KEY,
    STUDENT_NAME VARCHAR2(20) NULL
);


CREATE SEQUENCE SE_STUDENT
START WITH 1
INCREMENT BY 1
CACHE 20
NOMAXVALUE;


CREATE OR REPLACE TRIGGER TRI_STUDENT
BEFORE INSERT ON TB_STUDENT FOR EACH ROW
BEGIN
    SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;
END;


存储过程（不带参数）：

CREATE OR REPLACE PROCEDURE PRO_PRINT AS
STUDENT_COUNT INTEGER;
BEGIN
    SELECT COUNT(1) INTO STUDENT_COUNT FROM TB_STUDENT;
    IF STUDENT_COUNT>1 THEN
        DBMS_OUTPUT.PUT_LINE(STUDENT_COUNT);
    ELSE
        DBMS_OUTPUT.PUT_LINE('no student found');
    END IF;
END;

执行存储过程： 

BEGIN
    PRO_PRINT;
END;


或者用CALL，一定要加括号：

CALL PRO_STUDENT_INSERT();


随机数函数：

1、dbms_random.normal

这个函数不带参数，能返回normal distribution的一个number类型，所以基本上随机数会在-1到1之间。

2、dbms_random.random

    这个也没有参数，返回一个从-power(2,31)到power(2,31)的整数值

3、dbms_random.value

    这个函数分为两种，一种是没有参数，则直接返回0-1之间的38位小数

    第二种是加上两个参数a、b，则返回值在a、b之间的38位小数

4、dbms_random.string

    这个函数必须带有两个参数，前面的字符指定类型，后面的数值指定位数(最大60)

    类型说明：

    'u','U' : upper case alpha characters only

    'l','L' : lower case alpha characters only

    'a','A' : alpha characters only (mixed case)

    'x','X' : any alpha-numeric characters (upper)

    'p','P' : any printable characters


随机数函数测试：

CREATE OR REPLACE PROCEDURE PRO_RANDOM(RANDOM_FROM IN INTEGER, RANDOM_TO IN INTEGER, RANDOM_STRING OUT VARCHAR2)
AS
RANDOM_RANDOM INTEGER;
RANDOM_NORMAL INTEGER;
RANDOM_VALUE INTEGER;
BEGIN
    SELECT DBMS_RANDOM.RANDOM INTO RANDOM_RANDOM FROM DUAL;
    SELECT DBMS_RANDOM.NORMAL INTO RANDOM_NORMAL FROM DUAL;
    IF RANDOM_FROM > RANDOM_TO THEN
        SELECT DBMS_RANDOM.VALUE(RANDOM_TO, RANDOM_FROM) INTO RANDOM_VALUE FROM DUAL;
    ELSE
        SELECT DBMS_RANDOM.VALUE(RANDOM_FROM, RANDOM_TO) INTO RANDOM_VALUE FROM DUAL;
    END IF;
    SELECT DBMS_RANDOM.STRING('U', 20) INTO RANDOM_STRING FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('RANDOM_RANDOM='||RANDOM_RANDOM);
    DBMS_OUTPUT.PUT_LINE('RANDOM_NORMAL='||RANDOM_NORMAL);
    DBMS_OUTPUT.PUT_LINE('RANDOM_VALUE='||RANDOM_VALUE);
    DBMS_OUTPUT.PUT_LINE('RANDOM_STRING='||RANDOM_STRING);
END;

执行： 

DECLARE
RANDOM_FROM INTEGER;
RANDOM_TO INTEGER;
RANDOM_STRING_OUT VARCHAR2(20);
BEGIN
    RANDOM_FROM:=1;
    RANDOM_TO:=100;
    PRO_RANDOM(RANDOM_FROM,RANDOM_TO, RANDOM_STRING_OUT);
    DBMS_OUTPUT.PUT_LINE('RANDOM_STRING_OUT='||RANDOM_STRING_OUT);
END;


结果（每次都不同）： 

RANDOM_RANDOM=-33085305
RANDOM_NORMAL=-1
RANDOM_VALUE=17
RANDOM_STRING=MADGMQSZXJAHWPJNMWCS
RANDOM_STRING_OUT=MADGMQSZXJAHWPJNMWCS


获得系统时间： 

SELECT SYSDATE FROM DUAL

输出： 

2014-1-13 22:42:38

格式化时间： 

SELECT TO_CHAR(SYSDATE, 'yyyy-MM-dd HH24:mi:ss') FROM DUAL

输出： 

2014-01-13 22:43:29


SELECT TO_DATE('2013-01-04 12:12:12', 'yyyy-MM-dd HH24:mi:ss') FROM DUAL;

输出： 

2013-1-4 12:12:12


 存储过程循环插入数据：

CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
BEGIN
    FOR I IN 1..10000 LOOP
        INSERT INTO TB_STUDENT (STUDENT_NAME) VALUES ('aaa');
    END LOOP;
END;

执行： 

BEGIN
    PRO_STUDENT_INSERT;
END;

用游标遍历数据： 

DECLARE
CURSOR C1 IS SELECT STUDENT_NAME FROM TB_STUDENT;
BEGIN
    FOR STUDENT_ROW IN C1 LOOP
        DBMS_OUTPUT.PUT_LINE(STUDENT_ROW.STUDENT_NAME);
    END LOOP;
END;


IN和EXISTS性能比较：

插入100万条数据：

CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
RANDOM_STRING VARCHAR2(20);
BEGIN
    FOR I IN 1..1000000 LOOP
        SELECT DBMS_RANDOM.STRING('U', 20) INTO RANDOM_STRING FROM DUAL;
        INSERT INTO TB_STUDENT (STUDENT_NAME) VALUES (RANDOM_STRING);
    END LOOP;
END;


BEGIN
    PRO_STUDENT_INSERT;
END;


显示执行时间：


set timing on

另外创建两张表： 

CREATE TABLE TB_STU1 AS SELECT * FROM TB_STUDENT WHERE ID >= 32500 AND ID <= 102800;

CREATE TABLE TB_STU2 AS SELECT * FROM TB_STUDENT WHERE ID >= 32500 AND ID <= 102800;

不创建索引，执行： 

SQL> SELECT COUNT(STUDENT_NAME) FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_N
AME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

COUNT(STUDENT_NAME)
-------------------
              70301

已用时间:  00: 00: 01.56
SQL> SELECT COUNT(STUDENT_NAME) FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT ST
UDENT_NAME FROM TB_STU2);

COUNT(STUDENT_NAME)
-------------------
              70301

已用时间:  00: 00: 00.70
SQL>

网上说EXISTS比IN好，可是结果IN更快，后来多测试了几次： 

CREATE TABLE TB_STU1 AS SELECT * FROM TB_STUDENT WHERE ID >= 150000 AND ID <= 260000;

CREATE TABLE TB_STU2 AS SELECT * FROM TB_STUDENT WHERE ID >= 150000 AND ID <= 260000;

结果： 

SELECT STUDENT_NAME FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

已用时间:  00: 00: 22.45

SELECT STUDENT_NAME FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STU2);

已用时间:  00: 00: 15.64

有几次，时间是差不多的，但是IN快的次数更多。

现在创建索引：

CREATE INDEX IDX1 ON TB_STU1(STUDENT_NAME);

CREATE INDEX IDX2 ON TB_STU2(STUDENT_NAME);

CREATE INDEX IDX ON TB_STUDENT(STUDENT_NAME);


测试结果：

当外面是TB_STUDENT的时候，IN比EXISTS快。

SELECT STUDENT_NAME FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

已用时间:  00: 05: 11.90

SELECT STUDENT_NAME FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STU2);

已用时间:  00: 02: 24.50


当里面是TB_STUDENT的时候，EXISTS比IN快。


SELECT STUDENT_NAME FROM TB_STU1 A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STUDENT B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

已用时间:  00: 01: 43.53

SELECT STUDENT_NAME FROM TB_STU2 WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STUDENT);

已用时间:  00: 03: 56.59

TB_STUDENT的数据多，其他表数据少，不过，经过多次测试，并非每次都这样。


性能测试：SELECT * 和SELECT 字段名

插入100万条数据

CREATE TABLE TB_STUDENT (
    ID INTEGER PRIMARY KEY,
    STUDENT_NAME VARCHAR2(20) NOT NULL,
    SEX VARCHAR2(1) NOT NULL,
    AGE INTEGER NOT NULL
);

CREATE SEQUENCE SE_STUDENT
START WITH 1
INCREMENT BY 1
CACHE 20
NOMAXVALUE;

CREATE OR REPLACE TRIGGER TRI_STUDENT
BEFORE INSERT ON TB_STUDENT FOR EACH ROW
BEGIN
    SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;
END;

CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
BEGIN
    FOR I IN 1..1000000 LOOP
        IF I <= 10000 THEN
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('aaaaa', 'B', 11);
        ELSIF I<=100000 THEN
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('bbbbb', 'G', 12);
        ELSIF I<=500000 THEN
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('ccccc', 'B', 13);
        ELSE
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('ddddd', 'G', 14);
        END IF;
    END LOOP;
    COMMIT;
END;

CALL PRO_STUDENT_INSERT();

测试结果：

SELECT * FROM TB_STUDENT WHERE ID <= 100000;

已用时间:  00: 00: 32.92

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID <= 100000;

已用时间:  00: 00: 32.50

SELECT * FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;

已用时间:  00: 00: 25.12

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;

已用时间:  00: 00: 24.45

SELECT * FROM TB_STUDENT;

已用时间:  00: 03: 34.21

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

已用时间:  00: 01: 53.95

SELECT * FROM TB_STUDENT;

已用时间:  00: 02: 14.85

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

已用时间:  00: 02: 04.62

上面看到查100万条记录，第二次时间远远比第一次少，这是因为缓存的关系，下面清理一下缓存： 

ALTER SYSTEM FLUSH BUFFER_CACHE;


再次测试：


SELECT * FROM TB_STUDENT;

已用时间:  00: 03: 22.29

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

已用时间:  00: 02: 47.64

看到直接写出字段名和用*，显然是直接写字段名快，但是快了没多少。

多测试几次，每次都清缓存，如下：

SELECT * FROM TB_STUDENT;

已用时间:  00: 02: 15.59

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

已用时间:  00: 02: 14.17

SELECT * FROM TB_STUDENT;

已用时间:  00: 02: 24.21

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

已用时间:  00: 02: 18.48

然后增加10列再测试一下： 

ALTER TABLE TB_STUDENT ADD (
    COL1 VARCHAR2(10) NULL,
    COL2 VARCHAR2(10) NULL,
    COL3 VARCHAR2(10) NULL,
    COL4 VARCHAR2(10) NULL,
    COL5 VARCHAR2(10) NULL,
    COL6 VARCHAR2(10) NULL,
    COL7 VARCHAR2(10) NULL,
    COL8 VARCHAR2(10) NULL,
    COL9 VARCHAR2(10) NULL,
    COL10 VARCHAR2(10) NULL
);

测试结果：

SELECT * FROM TB_STUDENT;

已用时间:  00: 06: 16.53

SELECT ID, STUDENT_NAME, SEX, AGE, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 FROM TB_STUDENT;

已用时间:  00: 06: 00.76

SELECT * FROM TB_STUDENT;

已用时间:  00: 05: 52.68

SELECT ID, STUDENT_NAME, SEX, AGE, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 FROM TB_STUDENT;

已用时间:  00: 06: 18.56

所以，感觉两条SQL语句，并没有很明显的时间上的差别，也就30秒左右的差别。测试数据量是100万条。