本文共 6257 字，大约阅读时间需要 20 分钟。
退出SQLPLUS
exit;修改 system(sys) 账号密码
SQLPLUS /NOLOG CONN /AS SYSDBA ALTER USER SYSTEM IDENTIFIED BY tarring;清除 SQLPLUS 屏幕
CLEAR SCREEN; CL SCR;查看数据文件位置
SELECT NAME FROM v$datafile;查看控制文件位置
SELECT NAME FROM v$controlfile;查看日志文件位置
SELECT MEMBER FROM v$logfile;建立表空间
CREATE TABLESPACE ts01 DATAFILE 'D:\DataBase\Oracle11g\oradata\orcl\test_db01.dbf’SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1024M DEFAULT STORAGE(INITIAL 10m NEXT 1M) PERMANENT ONLINE LOGGING;修改表空间
ALTER TABLESPACE ts01 NOLOGGING;表空间增加数据文件
ALTER TABLESPACE ts01 ADD DATAFILE 'D:\DataBase\Oracle11g\oradata\orcl\test_db02.dbf’SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;删除表空间
DROP TABLESPACE ts01;删除表空间同时删除数据文件
DROP TABLESPACE ts01 INCLUDING CONTENTS AND DATAFILES;表空间中建表
CREATE TABLE student( student_id VARCHAR2(10), student_name VARCHAR2(20) )TABLESPACE ts01;查看表所属表空间
SELECT TABLE_NAME, TABLESPACE_NAME FROM tabs WHERE TABLE_NAME = ‘STUDENT’;查看表结构
DESCRIBE student; DESC student;增加表注释
COMMENT ON TABLE student IS ’ 学生信息表 ';查看表注释
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = ‘STUDENT’; SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = ‘STUDENT’;表字段增加注释
COMMENT ON COLUMN STUDENT.STUDENT_ID IS ’ 学生编号 ';查看表字段注释
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = ‘STUDENT’; SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ‘STUDENT’;查看用户所有表
SELECT * FROM User_Tables;查看用户拥有的所有对象
SELECT * FROM User_Objects;查看用户拥有的 表 试图 序列
SELECT * FROM User_Catalog;表字段修改
ALTER TABLE student MODIFY(student_id CHAR(15));表字段增加
ALTER TABLE STUDENT ADD(AGE NUMBER(2));删除表字段
ALTER TABLE STUDENT DROP COLUMN student_name;修改表名称
RENAME STUDENT TO STU;删除表
DROP TABLE STUDENT;增加一条记录
CREATE TABLE student( s_id Varchar2(10), s_name varchar2(20), s_age Number(3), s_birthday DATE )TABLESPACE ts01; INSERT INTO student (s_id, s_name, s_age, s_birthday) VALUES (‘S000000001’, ‘Tarring01’, 10, to_date(‘1982-10-06’,‘yyyy-mm-dd’));使用替代变量时，输入字符串字段时一样要写上引号
INSERT INTO student (s_id, s_name, s_age, s_birthday) VALUES (&s_id, &s_name, 10, Sysdate);修改记录
UPDATE student SET s_name = ’ 陶川 ', s_age = 20 WHERE s_id = ‘S000000002’;删除记录
DELETE FROM student WHERE s_id = ‘S000000002’;截断表
TRUNCATE TABLE student;事务处理
COMMIT; – 提交事务 INSERT INTO student (s_id, s_name) VALUES (‘S001’, ‘tarring1’); ROLLBACK; – 回滚，回滚到上一次提交过后的点带恢复点的事务
COMMIT; INSERT INTO student (s_id, s_name) VALUES (‘S001’, ‘tarring1’); SAVEPOINT firstdate; INSERT INTO student (s_id, s_name) VALUES (‘S002’, ‘tarring2’); SAVEPOINT seconddate; DELETE FROM student; ROLLBACK TO firstdate; SELECT * FROM student;| 约束条件 | 说明 |
|---|---|
| UNIQUE | 指定字段的值，必须是唯一的 |
| PRIMARY KEY | 主键，会为指定的字段作索引，并且也是唯一的值 |
| NOT NULL | 不可以是空值【 NULL 】或 0 （零） |
| CHECK | 检查，必须符合指定的条件 |
| FOREIGN KEY | 外键，用来创建一个参考表之间的关系 |
建表同时建立唯一约束
CREATE TABLE student( s_id Varchar2(10), s_name varchar2(20), s_age Number(3), s_birthday DATE, CONSTRAINT s_name_uk UNIQUE(s_name) )TABLESPACE ts01;查看唯一约束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = ‘STUDENT’;建表同时建立主键
CREATE TABLE student( s_id Varchar2(10), s_name varchar2(20), s_age Number(3), s_birthday DATE, CONSTRAINT s_id_pk PRIMARY KEY (s_id) )TABLESPACE ts01;查看主键约束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = ‘STUDENT’;建表同时建立非空字段
CREATE TABLE student( s_id Varchar2(10), s_name varchar2(20) NOT NULL, s_age Number(3), s_birthday DATE, CONSTRAINT s_id_pk PRIMARY KEY (s_id) )TABLESPACE ts01;查看非空约束
SELECT table_name, constraint_name, constraint_type, search_condition FROM User_Constraints WHERE table_name = ‘STUDENT’;建表同时建立检查
CREATE TABLE student( s_id Varchar2(10), s_name varchar2(20), s_age Number(3), s_birthday DATE, CONSTRAINT s_age_ck CHECK (s_age BETWEEN 1 AND 100) – 端点值可以使用 )TABLESPACE ts01;查看检查约束
SELECT table_name, constraint_name, constraint_type, search_condition FROM User_Constraints WHERE table_name = ‘STUDENT’;外键的使用
CREATE TABLE team( t_id Varchar2(10), t_name Varchar2(20), CONSTRAINT t_id_pk PRIMARY KEY (t_id) )TABLESPACE ts01;CREATE TABLE student(
s_id Varchar2(10), team_id VARCHAR2(10), s_name varchar2(20), CONSTRAINT s_id_pk PRIMARY KEY (s_id), CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team(t_id) )TABLESPACE ts01;查看表的外键约束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = ‘STUDENT’;被引用表是不能删除的
drop table team;被引用的记录是不能删除的
nsert into team (t_id, t_name) values (‘t001’, ‘lansene’); insert into student(s_id, s_name, team_id) values (‘s001’,‘tarring’, ‘t001’); delete from team;关闭一个约束
ALTER TABLE student DISABLE CONSTRAINT s_team_id_fk;启用一个约束
ALTER TABLE student ENABLE CONSTRAINT s_team_id_fk;删除一个约束
ALTER TABLE student DROP CONSTRAINT s_team_id_fk;已创建的表增加一个约束
ALTER TABLE student ADD CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team(t_id);| 系统权限 | 说明 |
|---|---|
| create session | 连接数据库 |
| create table | 创建表 |
| create sequence | 创建序列 |
| create view | 创建视图 |
| create proceduer | 创建程序 |
| 权限\对象 | 表【table】 | 视图【view】 | 程序【procedure】 |
|---|---|---|---|
| insert | Y | Y | |
| alter | Y | ||
| update | Y | Y | |
| delete | Y | Y | |
| select | Y | Y | |
| index | Y | ||
| execute | Y |
创建用户
CREATE USER u01 IDENTIFIED BY p01;创建用户并制定默认表空间
CREATE USER u01 IDENTIFIED BY p01 DEFAULT TABLESPACE QUOTA 2M ON ts01;quota
表空间中可使用的配额修改用户密码
ALTER USER u01 IDENTIFIED BY p001;修改用户表空间配额
ALTER USER u01 QUOTA 20M ON ts01; ALTER USER u01 QUOTA UNLIMITED ON ts01; – 用户对表空间没有配额限制回收 unlimited tablespace 权限
REVOKE UNLIMITED TABLESPACE FROM ts01;删除用户
DROP USER u01;切换连接数据库的用户
CONNECT u01/p01; conn u01/p01;授权用户连接数据库的权限
GRANT CREATE SESSION TO u01;授权用户创建序列 (sequence) 的权限
GRANT CREATE sequence TO u01;授权用户创建表的权限
GRANT CREATE TABLE TO u01;授权用户查表的权限
grant select on ts01.user1 to wangkai ；授权用户修改表的权限
grant alter on user1 to wangkai ；授权用户删除表的权限 ( 没有此权限 )
grant drop on user1 to wangkai ； ( 错误 )授权用户对一个表的所有权限
grant all on user1 to wangkai;授权所有用户对一个表的所有权限
grant all on user1 to public;收回用户创建表的权限
REVOKE CREATE TABLE FROM u01;收回用户查表的权限
REVOKE select on user1 FROM u01;收回用户修改的权限
REVOKE select on user1 FROM u01;收回用户对一个表的所有权限
revoke all on user1 from wangkai;收回所有用户对一个表的所有权限
revoke all on user1 to public;创建角色
CREATE ROLE r01;角色授权
GRANT CREATE SESSION, CREATE TABLE TO r01;收回角色权限
REVOKE CREATE TABLE FROM r01;查看角色权限
SELECT ROLE,PRIVILEGE FROM role_sys_privs WHERE ROLE=‘R01’角色赋给用户
GRANT r01 TO u01;查看当前用户角色
SELECT * FROM user_role_privs;删除角色
DROP ROLE r01;转载地址：http://ojsqb.baihongyu.com/