Oracle基本命令

ibaobei

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

　　>　　五、主-外键约束(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>　　用户授权:
　　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 用户名>
　　ALTER USER lixing>　　ALTER USER 用户名 PASSWORD EXPIRE;                用户第一次登陆要求修改密码
　　ALTER USER lixing PASSWORD EXPIRE;               要求用户第一次登陆必须修改密码
　　ALTER USER lixing ACCOUNT LOCK;                  锁定用户lixing
　　ALTER USER lixing ACCOUNT UNLOCK;                解除锁定用户
　　数据备份与还原
　　数据库备份：exp
　　数据库还原:imp