达内学习心得：Oracle

1、达内学习心得： Oracle_11g常用 SQL 语句(高级工程师必备) 参赛学员：吴贤志 获奖奖项：三等奖- 退出 SQLPLUSexit;- 修改 system(sys) 账号密码SQLPLUS /NOLOGCONN /AS SYSDBAALTER USER SYSTEM IDENTIFIED BY tarring;- 清除 SQLPLUS 屏幕CLEAR SCREEN;CL SCR;- 查看数据文件位置SELECT NAME FROM v$datafile;- 查看控制文件位置SELECT NAME FROM v$controlfile;- 查看日志文件位置SELECT MEMBER F

2、ROM v$logfile;- 建立表空间CREATE TABLESPACE ts01DA TAFILE 'D:DataBaseOracle11goradataorcltest_db01.dbf'SIZE 100MAUTOEXTEND ON NEXT 100M MAXSIZE 1024MDEFAULT STORAGE(INITIAL 10m NEXT 1M)PERMANENTONLINELOGGING;- 修改表空间ALTER TABLESPACE ts01NOLOGGING;- 表空间增加数据文件ALTER TABLESPACE ts01ADD DATAFILE 'D

3、:DataBaseOracle11goradataorcltest_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;-

4、查看表所属表空间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

5、 = '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_Objec

6、ts;- 查看用户拥有的 表 试图 序列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 v

7、archar2(20), s_age Number(3), s_birthday DATE )TABLESPACE ts01;- 增加一条记录INSERT INTOstudent (s_id, s_name, s_age, s_birthday)VALUES ('S000000001', 'Tarring01', 10, to_date('1982-10-06','yyyy-mm-dd');INSERT INTOstudent (s_id, s_name, s_age, s_birthday)VALUES ('S00000

8、0002', 'Tarring02', 10, Sysdate);- 使用替代变量时，输入字符串字段时一样要写上引号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 = &

9、#39;S000000002'- 截断表TRUNCATE TABLE student;- 事务处理COMMIT; - 提交事务INSERT INTO student (s_id, s_name) V ALUES ('S001', 'tarring1'); ROLLBACK; - 回滚，回滚到上一次提交过后的点- 带恢复点的事务COMMIT;INSERT INTO student (s_id, s_name) V ALUES ('S001', 'tarring1'); SAVEPOINT firstdate;INSERT I

10、NTO student (s_id, s_name) V ALUES ('S002', 'tarring2');SAVEPOINT seconddate;DELETE FROM student;ROLLBACK TO firstdate;SELECT * FROM student;- 约束条件说明- UNIQUE- PRIMARY KEY- NOT NULL- CHECK- FOREIGN KEY指定字段的值，必须是唯一的 主键，会为指定的字段作索引，并且也是唯一的值 不可以是空值【 '' NULL 】或 0（零） 检查，必须符合指定的条件 外键

11、，用来创建一个参考表之间的关系- 建表同时建立唯一约束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'- 作业：数据字典

12、【分类常用】- 建表同时建立主键 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 table_name = 'STUDENT'User_ConstraintsWHERE- 建表同时建立非空字段CREATE

13、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;INSERT INTO student (s_id, s_name) V ALUES ('S001', NULL); -插入一个 null- 查看非空约束SELECT table_name, constraint_name, constraint_type, User_Constrain

14、ts WHERE table_name = 'STUDENT'search_conditionFROM- 建表同时建立检查 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_ty

15、pe,search_conditionFROMUser_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

16、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; - 被引用表是不能删除的insert into team (t_id, t_name) values ('t001',

17、 '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 studen

18、t DROP CONSTRAINT s_team_id_fk;已创建的表增加一个约束ALTER TABLE student ADD CONSTRAINT s_team_id_fkFOREIGN KEY(team_id)REFERENCES team(t_id);/*SQL 语句 5 大类型| 命令| 说明| Data Retrieval 数 据 检 索 | select查询记录| Date Manipulation Language 【 DML 】 数 据 操 纵 语 言 |insert| update| delete添加记录| 修改记录| 删除记录| Data Definition Lang

19、uage 【 DDL 】 数 据 定 义 语 言 | create | 创 建| alter | 修 改| drop | 丢弃【删除】| rename | 重命名| truncate | 截 断*| Transaction Control 事 务 控 制| commit | 确 认 命 令|令或保存点 | rollback | 回退至前一次确认的命| savepoint | 设置保存点*| Data Control Language 【 DCL 】 数 据 控 制 语 言 | grant | 授 予 权 限| revoke | 撤消权限| 系统权限 | 说明|*| | create sessi

20、on| 连接数据库|*| | 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 | |

21、 | |*| 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 T