Oracle基本命令

SET LINESIZE 300;    设置行高

SET PAGESIZE 50;     设置每页显示的数目

CONN SYSTEM/manager AS SYSDBA;   以SYSTEM登陆

SHOW USER;           显示当前的用户

SELECT * FROM tab;   显示当前数据库所有的表

DESC 表名;            显示表的结构

CLEAR SCR;           清除屏幕

/                    上次查询

字符串函数:

UPPER()              将所有字母转换为大写     SELECT UPPER(ENAME) FROM EMP;

LOWER()              将的有字母转换为小写     SELECT LOWER(ENAME) FROM EMP;

INITCAP()            将所有首写字母转换为大小,其它字母为小写    SELECT INITCAP(ENAME) FROM EMP;

CONCAT()             字符串相连　　          SELECT CONCAT('HELLO',' WORLD') FROM DUAL; 

SUBSTR()             截取字符串             SELECT SUBSTR('HELLO',1,3) FROM DUAL;

LENGTH()             获得字符串的长度        SELECT LENGTH('HELLO') FROM DUAL;

REPLACE()            字符串替换             SELECT REPLACE('HELLO','L','X');

数值函数

ROUND()              四舍五入     ROUND(789.536)---[790]      ROUND(789.536,2)---[789.54]

TRUNC()              截断小数位    TRUNC(789.536)---[789]     TRUNC(789.536,2)---[789.53]

MOD()                取模          SELECT MOD(10,3) FROM DUAL;---[1]

日期函数

SYSDATE              取出当前日期

MONTHS_BETWEEN()     求出给定日期范围的月数      

ADD_MONTHS()         在指定的日期上加上指定的月数    SELECT TO_CHAR(ADD_MONTHS(SYSDATE,5),'yyyy-mm-dd') FROM DUAL;

NEXT_DAY()           下一个的今天是哪一个日期        

LAST_DAY()           求出给定日期的最后一天日期       

转换函数

TO_CHAR()           将数字转换成字符串    SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM DUAL;

TO_NUMBER()         将字符串转换成数字    SELECT TO_NUMBER('2010-01-11') FROM DUAL;

TO_DATE()           将字符串转换成日期    SELECT TO_DATE('2009-12-31','yyyy-mm-dd') FROM DUAL;

通用函数

NVL()               将一个指定的NULL变为指定的内容(计算Null值,必须使用NVL)     SELECT NVL(NULL,0) FROM DUAL;

DECODE()                  

常用数据类型:

NUMBER(M)--INT    |　　NUMBER(M,N)--FLOAT   Ｍ:表示整数   Ｎ:表示小数位数                    

VARCHAR()   |   VARCHAR2()     表示字符串,有长度限制,为255字符

DATE()                         表示日期类型

CLOB                           表示大的对象(表示:文本数据　一般可以存放4Ｇ的文本) 

BLOB                           表示大的对象(表示:二进制数据　一般可以存放4Ｇ的文本　电影,歌曲,图片)

创建表

CREATE TABLE myemp AS SELECT * FROM emp      创建emp表的副本

CREATE TABLE table_name(

   column_name1    DATA_TYPE   [DEFAULT]

   column_name2    DATA_TYPE   [DEFAULT]

   columb_name3    DATA_TYPE   [DEFAULT]

);                                           创建新表

删除表

DROP TABLE table_name;                       删除表

修改表

ALTER TABLE table_name ADD (column_name DATA_TYPE DEFAULT);       添加列 

ALTER TABLE MODIFY(column_name DATA_TYPE DEFAULT);                修改列(类型长度只能增,不能少) 

RENAME oldTable TO newTable                  重命名表

TRUNCATE TABLE table_name                    截断表(不产生日志)

数据约束

一、主键约束：

1.PRIMARY KEY(系统分配约束)

CREATE TABLE person(

   pid NUMBER(18) PRIMARY KEY,

   name VARCHAR2(20) NOT NULL

);

2.PRIMARY KEY(指定约束名称)

CREATE TABLE person(

   pid NUMBER(18) PRIMARY KEY,

   name VARCHAR2(20) NOT NULL,

   CONSTRAINT person_id_pk PRIMARY KEY (pid)

);

ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY (pid);      修改person表,为表添加person_pid_pk主键

二、非空约束(NOT NULL)

1.CREATE TABLE person(

   pid NUMBER(5),

   name VARCHAR2(20) NOT NULL,

   CONSTRAINT person_pid_pk PRIMARY KEY (pid)

);

2.ALTER TABLE person MODIFY(name VARCHAR2(20) NOT NULL);

三、唯一约束(UNIQUE)

1.CREATE TABLE person(

   pid NUMBER(5),

   name VARCHAR2(20)  NOT NULL UNIQUE,

);

2.CREATE TABLE person(

   pid NUMBER(5),

   name VARCHAR2(20) NOT NULL UNIQUE,

   CONSTRAINT person_name_uk UNIQUE (name)

);

3.ALTER TABLE person ADD CONSTRAINT person_name_uk UNIQUE (name);       修改唯一约束

四、检查约束(CHECK)

1.CREATE TABLE person(

   pid NUMBER(5),

   name VARCHAR2(20) NOT NULL UNIQUE,

   age NUMBER(2) NOT NULL CHECK(age BETWEEN 0 AND 100),

   sex VARCHAR2(2) NOT NULL DEFAULT '男' CHECK(sex IN('男','女'))

);

2.CREATE TABLE person(

   pid NUMBER(5),

   name VARCHAR2(20) NOT NULL UNIQUE,

   age NUMBER(2) NOT NULL,

   sex VARCHAR2(2) NOT NULL DEFAULT '男',

   CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 100),

   CONSTRAINT person_sex_ck  CHECK(sex IN('男','女'))

);

3.ALTER TABLE person ADD CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 100);

  ALTER TABLE person ADD CONSTRAINT person_sex_ck CHECK(sex IN('男','女'));

五、主-外键约束(FOREIGN KEY)

1.CREATE TABLE book(

   bid NUMBER(3) NOT NULL,

   person_id NUMBER(5) NOT NULL,

   name VARCHAR2(50) NOT NULL,

   price NUMBER(5,2) NOT NULL,

   CONSTRAINT person_book_personid_fk FOREIGN KEY (price) REFERENCES person(pid) ON DELETE CASCADE

);

六、删除约束

ALTER TABLE table_name DROP CONSTRAINT constraint_name     根据提供的table_name表删除constraint_name约束

ROWNUM      Oracle每个表中都会有一个ROWNUM列

视图管理:

CREATE VIEW view_name AS SELECT * FROM emp;         创建视图

DROP VIEW view_name                                 删除视图

CREATE OR REPLACE VIEW view_name AS SELECT ....       创建或替换视图

CREATE OR REPLACE VIEW view_name AS SELECT ....

   WITH CHECK OPTION  不能更新创建视图时列的条件

   WITH READ ONLY 只准许视图能读

序列管理:

CREATE SEQUENCE myseq;

INCREMENT BY 2　指定步长　            CREATE SEQUENCE myseq INCREMENT BY 2;     步长为:2

START WITH  0　 指定起始位置

MAXVALUE 10    指定最大值

CYCLE          while

DROP SEQUENCE myseq;    删除序列

同义词:

CREATE SYNONYM 同义词名称FOR 用户名.表名称;       创建同义词

CREATE SYNONYM emp FOR SCOTT.emp;

DROP SYNONYM emp;

用户管理:

创建用户:

CREATE USER 用户名　IDENTIFIED BY　密码　　　　    创建用户和密码

CREATE USER lixing IDENTIFIED BY lixing        创建用户和密码

用户授权:

GRANT 权限1,权限2.....　TO 用户名                   给用户授权

GRANT CREATE SESSION TO lixing;                  为lixing用户创建session权限

GRANT CONNECT,RESOURCE TO lixing;                为lixing用户赋CONNECT RESOURCE权限

GRANT SELECT,DELETE,UPDATE ON scott.emp TO lixing;      把scott.emp表的增、删、改的权限赋给lixing用户

REVOKE SELECT,DELETE,UPDATE ON scott.emp FROM lixing;        回收scott.emp表赋给用户lixing的权限

修改用户：

ALTER USER 用户名IDENTIFIED BY 新密码;           修改密码

ALTER USER lixing IDENTIFIED BY newlixing;      修改用户lixing密码为newlixing

ALTER USER 用户名PASSWORD EXPIRE;                用户第一次登陆要求修改密码

ALTER USER lixing PASSWORD EXPIRE;               要求用户第一次登陆必须修改密码

ALTER USER lixing ACCOUNT LOCK;                  锁定用户lixing

ALTER USER lixing ACCOUNT UNLOCK;                解除锁定用户

数据备份与还原

 数据库备份：exp

 数据库还原:imp

本文出自 “李新的博客” 博客