美国上市公司

亿元级外企IT培训企业

  • 全国服务监督电话400-827-0010
IT培训 > 学习笔记 > Oracle_11g常用SQL语句(高级工程师必备)
  • Oracle_11g常用SQL语句(高级工程师必备)

    发布:IT培训 来源:达内 时间:2014-12-29

  • Oracle_11g常用SQL语句(高级工程师必备)

  • 参赛学员:吴贤志

    获奖奖项:三等奖

      -- 退出 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 ('S000000002', '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 = '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;
    
    INSERT INTO student (s_id, s_name) VALUES ('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 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;  -- 被引用表是不能删除的
    
    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 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;

  • 上一篇:推荐:《web安全篇之SQL注入攻击》原创文章,达内首发!

    下一篇:杂谈javaEE重点,分享交流经验

相关资讯
2001-2016 达内国际公司(TARENA INTERNATIONAL,INC.) 版权所有 京ICP证08000853号-56