达内学习心得：Oracle_11g常用SQL语句(高级工程师必备)_测试常用sql常用语句
达内学习心得：Oracle_11g常用SQL语句(高级工程师必备)由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“测试常用sql常用语句”。
达内学习心得：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:DataBaseOracle11goradataorcltest_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: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;
--查看表所属表空间
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 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 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),FROM FROM 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 seion
| 连接数据库
| |*************************************| |
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;
达内IT培训中心学习心得在达内为期两周的实习时间已悄然离去，刚来时的那种新鲜好奇早飞到了九霄云外，更多的是离开时的那种不舍和对自己及所学的反思。当我第一天来这里时，和其......
达内IT培训中心学习心得(（锦集8篇））由网友“哦可哦类”投稿提供，以下是小编精心整理的达内IT培训中心学习心得，供大家参考借鉴，希望可以帮助到有需要的朋友。篇1：达内IT实习学习心......
在达内为期两周的实习时间已悄然离去，刚来时的那种新鲜好奇早飞到了九霄云外，更多的是离开时的那种不舍和对自己及所学的反思。当我第一天来这里时，和其他同学一样怀着兴奋得......
达内IT培训中心的学习心得......
达内IT培训中心的学习心得在达内为期两周的实习时间已悄然离去，刚来时的那种新鲜好奇早飞到了九霄云外，更多的是离开时的那种不舍和对自己及所学的反思。当我第一天来这里时，和......
