达内学习心得：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 FROM v$logfile;-- 建立表空间CREATE TABLESPACE ts01DA TAFILE 'D:\DataBase\Oracle11g\oradata\orcl\test_db01.dbf'SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1024MDEFAULT STORAGE(INITIAL 10m NEXT 1M)PERMANENTONLINELOGGING;-- 修改表空间ALTER TABLESPACE ts01NOLOGGING;-- 表空间增加数据文件ALTER TABLESPACE ts01ADD 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 V ARCHAR2(10),student_name V ARCHAR2(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 INTOstudent (s_id, s_name, s_age, s_birthday)V ALUES ('S000000001', 'Tarring01', 10, to_date('1982-10-06','yyyy-mm-dd'));INSERT INTOstudent (s_id, s_name, s_age, s_birthday)V ALUES ('S000000002', 'Tarring02', 10, Sysdate);-- 使用替代变量时，输入字符串字段时一样要写上引号INSERT INTOstudent (s_id, s_name, s_age, s_birthday)V ALUES (&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) V ALUES ('S001', 'tarring1');ROLLBACK; -- 回滚，回滚到上一次提交过后的点-- 带恢复点的事务COMMIT;INSERT INTO student (s_id, s_name) V ALUES ('S001', 'tarring1');SA VEPOINT firstdate;INSERT INTO student (s_id, s_name) V ALUES ('S002', 'tarring2');SA VEPOINT 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;INSERT INTO student (s_id, s_name) V ALUES ('S001', NULL); -- 插入一个null-- 查看非空约束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 V ARCHAR2(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; -- 被引用表是不能删除的insert 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);/****************************************************************************** **************|| SQL语句5大类型| 命令| 说明||****************************************************************************** **************|| Data Retrieval数据检索| select | 查询记录||****************************************************************************** **************|| Date Manipulation Language【DML】数据操纵语言| insert | 添加记录|| | update | 修改记录|| | delete | 删除记录|****************************************************************************** **************|| Data Definition Language【DDL】数据定义语言| create | 创建|| | alter | 修改|| | drop | 丢弃【删除】|| | rename | 重命名|| | truncate | 截断||****************************************************************************** **************|| Transaction Control事务控制| commit | 确认命令|| | rollback | 回退至前一次确认的命令或保存点|| | savepoint | 设置保存点||****************************************************************************** **************|| Data Control Language【DCL】数据控制语言| grant | 授予权限|| | revoke | 撤消权限||****************************************************************************** **************//*************************************|| 系统权限| 说明||*************************************|| 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 er1 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;。
