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

来源：互联网发布：改图宝软件下载编辑：程序博客网时间：2024/06/05 23:01

管理员登陆：

C:\>sqlplus "system/manager@orcl"SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 1月 5 19:30:54 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options

显示当前连接用户：

SQL> show userUSER 为 "SYSTEM"

查看系统拥有哪些用户：

SQL> select * from all_users;USERNAME                          USER_ID CREATED------------------------------ ---------- --------------USER1                                  61 01-1月 -14BI                                     60 01-1月 -14PM                                     59 01-1月 -14SH                                     58 01-1月 -14IX                                     57 01-1月 -14OE                                     56 01-1月 -14HR                                     55 01-1月 -14SCOTT                                  54 30-8月 -05MGMT_VIEW                              53 30-8月 -05MDDATA                                 50 30-8月 -05SYSMAN                                 51 30-8月 -05USERNAME                          USER_ID CREATED------------------------------ ---------- --------------MDSYS                                  46 30-8月 -05SI_INFORMTN_SCHEMA                     45 30-8月 -05ORDPLUGINS                             44 30-8月 -05ORDSYS                                 43 30-8月 -05OLAPSYS                                47 30-8月 -05ANONYMOUS                              39 30-8月 -05XDB                                    38 30-8月 -05CTXSYS                                 36 30-8月 -05EXFSYS                                 34 30-8月 -05WMSYS                                  25 30-8月 -05DBSNMP                                 24 30-8月 -05USERNAME                          USER_ID CREATED------------------------------ ---------- --------------TSMSYS                                 21 30-8月 -05DMSYS                                  35 30-8月 -05DIP                                    19 30-8月 -05OUTLN                                  11 30-8月 -05SYSTEM                                  5 30-8月 -05SYS                                     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_STUDENTSTART WITH 1INCREMENT BY 1CACHE 20NOMAXVALUE;

CREATE OR REPLACE TRIGGER TRI_STUDENTBEFORE INSERT ON TB_STUDENT FOR EACH ROWBEGIN    SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;END;

存储过程（不带参数）：

CREATE OR REPLACE PROCEDURE PRO_PRINT ASSTUDENT_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)ASRANDOM_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;

执行：

DECLARERANDOM_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=-33085305RANDOM_NORMAL=-1RANDOM_VALUE=17RANDOM_STRING=MADGMQSZXJAHWPJNMWCSRANDOM_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 ASBEGIN    FOR I IN 1..10000 LOOP        INSERT INTO TB_STUDENT (STUDENT_NAME) VALUES ('aaa');    END LOOP;END;

执行：

BEGIN    PRO_STUDENT_INSERT;END;

用游标遍历数据：

DECLARECURSOR 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 ASRANDOM_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_NAME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);COUNT(STUDENT_NAME)-------------------              70301已用时间:  00: 00: 01.56SQL> SELECT COUNT(STUDENT_NAME) FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STU2);COUNT(STUDENT_NAME)-------------------              70301已用时间:  00: 00: 00.70SQL>

网上说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.45SELECT 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.90SELECT 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.53SELECT 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_STUDENTSTART WITH 1INCREMENT BY 1CACHE 20NOMAXVALUE;CREATE OR REPLACE TRIGGER TRI_STUDENTBEFORE INSERT ON TB_STUDENT FOR EACH ROWBEGIN    SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;END;CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT ASBEGIN    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.92SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID <= 100000;已用时间:  00: 00: 32.50SELECT * FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;已用时间:  00: 00: 25.12SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;已用时间:  00: 00: 24.45SELECT * FROM TB_STUDENT;已用时间:  00: 03: 34.21SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;已用时间:  00: 01: 53.95SELECT * FROM TB_STUDENT;已用时间:  00: 02: 14.85SELECT 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.29SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;已用时间:  00: 02: 47.64

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

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

SELECT * FROM TB_STUDENT;已用时间:  00: 02: 15.59SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;已用时间:  00: 02: 14.17SELECT * FROM TB_STUDENT;已用时间:  00: 02: 24.21SELECT 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.53SELECT ID, STUDENT_NAME, SEX, AGE, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 FROM TB_STUDENT;已用时间:  00: 06: 00.76SELECT * FROM TB_STUDENT;已用时间:  00: 05: 52.68SELECT 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万条。

0 0