Oracle学习四
使用DDL语句管理表
-
创建表空间
表空间是ORACLE数据库的逻辑单元。数据库–表空间一个表空间可以与多个数据文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。
语法：create tablespace 表空间名称 datafile 数据文件路径 size 100m autoextend on next 10m; -- 举例： create tablespace SyjSpace datafile 'c:/SyjSpace.dbf' size 100m autoextend on next 10m -- 解释： -- SyjSpacet为表空间名称 -- datafile 指定表空间对应的数据文件 -- size后定义的是表空间的初始大小 -- autoextend on 自动增长，当表空间存储都占满时，自动增长 -- next后指定的是一次自动增长的大小。 -
用户：
创建用户-- 创建syj用户 -- 用户和密码 create user syj identified by syj -- 默认表空间 default tablespace SyjSpace -- 解释： identified by 后边是用户的密码 default tablespace 后边是表空间名称 oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。新创建的用户没有任何权限，登陆后会提示权限不足：
-- Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。 -- CONNECT角色：一是授予最终用户的典型权利，最基本的ALTER SESSION-修改会话 CREATE CLUSTER --建立聚簇 CREATE DATABASE LINK--建立数据库链接CREATE SEQUENCE--建立序列 CREATE SESSION --建立会话 CREATE SYNONYM --建立同义词 CREATE VIEW --建立视图 -- RESOURCE角色：一是授予开发人员的CREATE CLUSTER--建立聚簇 CREATE PROCEDURE--建立过程 CREATE SEQUENCE--建立序列 CREATE TABLE--建表 CREATE TRIGGER--建立触发器 CREATE TYPE--建立类型 --DBA角色：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除为新创建的用户提升权限：
语法：grant 角色 to 用户名
比如：grant dba to syj
-
删除表空间和用户
只要是create出来的都可以使用drop进行删除
-- 删除表空间并将物理文件一并删除(并不会立马删除标记，下次重启服务的时候删除) -- 删除表空间（及下面的数据文件） DROP TABLESPACE SyjSpace INCLUDING CONTENTS AND DATAFILES; -- 删除用户 drop user syj cascade -
Oracel常见的数据类型
类型 描述 char 固定长度类型 varchar2 可变长度类型，可保存1333多个汉字 number(3) 999（最多三位） number(3,2) 999.99（整数最多三位，小数保留两位） date mysql中日月年,oracle中精确到时分秒,相当于MySQL中的datetime timestamp 精确到秒的后9位 long 大数据字符类型,2G Clob 存放字符类型,4G Blob 存放二进制类型,4G -
创建表
创建person表，字段为pid，pname
create table person( pid number, pname varchar2(50) ) -
Oracle表中的增删改查**（Oracle中的事务需要手动commit提交）**
--1.为person表新增一条数据 insert into person values('1','zsf'); commit; insert into person values('3','wzj'); commit; --2.修改person表中的一条数据 update person set pname='aql' where pid='1'; commit; --3.删除person表中的一条数据 delete from person where pid='3'; commit; -
修改表列的属性
--1.给person表增加sex性别列，类型为number(1) (add增加列) alter table person add sex number(1); --2.修改person表列sex类型为char(1) (modify修改列) alter table person modify sex char(1); --3.修改person表sex列为gender (rename重命名列) alter table person rename column sex to gender ; --4.删除person表的gender列 (drop删除列) alter table person drop column gender; --5.删除person表中所有数据 (逐条删除表中数据) delete from person where 1=1; --6.摧毁person表（truncate table 表名） -- 直接摧毁表结构后重构表，比delete要很快，但是没法按照条件删除 truncate table person; -
约束
-
主键约束
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。
--1.创建person表，pid为主键，pname，gender(主键约束primary key) --primary key方式 create table person ( pid number primary key, pname varchar2(50), gender number ) --constraint 主键名 primary key(字段)方式 --主键自己命名的方式 create table person( pid number, pname varchar2(50), gender number, constraint PK_PID primary key(pid) ) -
非空约束
使用非空约束，可以使指定的字段不可以为空。
create table person ( pid number primary key, pname varchar2(50) not null, gender number ) -- 正常插入 insert into person (pid,pname,gender) values('1','zhangsan','0'); commit; -- 非空约束报错 insert into person (pid,gender) values('1','0'); commit; select * from person; -
唯一约束
表中的一个字段的内容是唯一的
--3.创建person表，pid，pname是唯一，gender(唯一约束unique) (唯一约束和null值没有影响) create table person ( pid number primary key, pname varchar2(50) unique, gender number ) -- 正常插入 insert into person (pid,pname,gender) values (1,'zhangsanfeng',0); commit; -- 正常插入 insert into person (pid,pname,gender) values (2,'zhangwuji',1); commit; -- 非空约束报错 insert into person (pid,gender) values (4,1); commit; -
检查约束
使用检查约束可以来约束字段值的合法范围。
--4.创建person表，pid，pname，gender ---检查约束check(列名 in (值)) create table person ( pid number primary key, pname varchar2(40), gender number check (gender in (1,0)) ) -- 正常插入 insert into person (pid,pname,gender) values (1,'zhangsanfeng',1); commit; -- 正常插入 insert into person (pid,pname,gender) values (2,'zhangwuji',0); commit; --- 检查约束check报错 insert into person (pid,pname,gender) values (3,'wangzhaojun',3); commit; -
外键约束
外键是两张表的约束，可以保证关联数据的完整性。
-- constraint fk_order_orderid foreign key(外键) references 对应的表(对应的主键) --1.创建orders表,字段为order_id(主键),total_price create table orders ( order_id number primary key, total_price number(8,2) ) --2.创建order_detail表,字段为detail_id(主键),order_id(外键),item_name create table order_detail( detail_id number primary key, order_id number, item_name varchar2(50), constraint FK_ORDER_ORDER_ID foreign key(order_id) references orders(order_id) ) -- 两张表的增加，先增加主表记录，在增加从表记录 -- 两张表的删除，先删除从表的数据，在删除从表记录 --3.为orders表和order_detail表插入数据 -- 主表插入数据 insert into orders values(1,999.99); -- 从表插入数据 insert into order_detail values(1,1,'华为手机'); commit; insert into order_detail values(2,1,'荣耀手环'); commit; select * from person; select * from order_detail; --4.删除orders表和order_detail表中的数据 -- 先执行报错 delete from orders where order_id =1; commit; -- 应先执行从表的删除 delete from order_detail where order_id = 1; commit;
-
使用DML语句处理数据
-
插入数据
语法：INSERTINTO表名[（列名1，列名2，…JJVALUES（值1，值2，.…）
简单写法（不建议）：INSERT INTO表名VALUES（值1，值2，.）
注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用
-
更新数据
全部修改：UPDATE表名SET列名1=值1，列名2=值2，.…
局部修改：UPDATE表名SET列名1=值1，列名2=值2，… WHERE修改条件； -
删除数据
语法：DELETE FROM 表名 WHERE删除条件；
在删除语句中如果不指定删除条件的话就会删除所有的数据Truncate table 实现数据删除
比较truncate 与 delete实现数据删除？
1.delete 删除的数据可以rollback，也可以闪回2.delete删除可能产生碎片，并且不释放空间
3.truncate是先摧毁表结构，再重构表结构
注意：插入、更新和删除会引起数据的变化。我们就必须考虑数据的完整性。
-
Oracle的事务管理
这是因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，样就不会插入到数据库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollbackOracle中事务的保存点：
事务的隔离级别有4个由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。√: 可能出现 ×: 不会出现
隔离级别 脏读 不可重复读 幻读 Read uncommitted √ √ √ Read committed × √ √ Repeatable read × × √ Serializable × × × Oracle支持的3种事务隔离级别：READ COMMITED，SERIALIZABLE，READ ONLY.
Oracle 默认的事务隔离级别为：READ COMMITED--1.设置savepoint 回滚点，再次修改数据后用rollback to 回滚点，回滚数据 update person set pname='lyf' where pid = 1; savepoint s1; --回滚点 update person set pname='gyy' where pid = 1; rollback to s1; --回滚到回滚点 commit;
管理其他数据库对象
-
视图
什么是视图：
视图就是封装了一条复杂查询的语句。 视图是一个虚表。
最大的优点就是简化复杂的查询。
创建视图的语法：
视图：就是一张虚拟表，本身不存放数据，数据来源于原始表
创建 create [or replace] view 视图名 as sql查询语句；
不建议通过视图对表中的数据进行修改，因为会受到很多的限制。
--1.创建视图 create or replace view emp_view as select * from emp; -- 创建视图包含id，ename，deptno不包含工资（工资都是保密的） create or replace view emp_view as select empno,ename,deptno from emp; --2.查询视图 select * from emp_view; --3.修改视图中7369的名字为'smith' update emp_view set ename='smith' where dempno=7499; commit; -- 对于在视图中看不到的列我们不可以进行操作，比如sal列 update emp_view set sal=sal+1000 where empno=7369; commit; --4.删除视图 drop view emp_view; --但是却可以根据看到的列（主键）进行删除操作 delete from emp_view where empno=7369; -- 对于看不到的列能操作视图的情况下不可能是非空约束 --5.创建只读视图（关键字 with read only） create or replace view emp_view as select empno,ename,deptno from emp with read only; -
序列
在很多数据库中都存在一个自动增长的列，如果现在要想在oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。并且Oracle将序列值装入内存可以提高访问效率。
mysql的自增长是：AUTO_INCREMENT
-- Oracle没有自增长列 -- 只能使用sequence /* 序列：类似于MySql的自动增长 create sequence seq_test start with 5 increment by 2 maxvalue 20 cycle cache 5 */ -- 创建序列的完整格式 create sequence seq_test start with 5 increment by 2 maxvalue 20 cycle cache 5; -- 只有第一次是5是开始的，循环之后从1开始 select seq_test.nextval from dual; --1.创建序列 create sequence emp_seq; --2.如何查询序列（currval,nextval） -- 第一次只能调用nextval，currval没有值 select emp_seq.nextval from dual; select emp_seq.currval from dual; --3.删除序列 drop sequence emp_seq; --4.为emp表插入数据，用序列为id赋值 -- 即使插入失败，对于自增长也没有影响 insert into emp (empno) values(9999); select * from emp; insert into emp (empno) values (emp_seq.nextval); -
索引
目的是提高检索速度
语法：create index 索引名称 on 表名(列名);
原则： 1、大数据表才创建索引，
2、为经常用到的列创建索引，
3、索引的层数不要超过4层，也就是on 表名(列名1,列名2)这里的列名不要超过4个
4、主键是自带索引的-- 创建表 create table person( pid number primary key, pname varchar2(50) )； -- 删除表 drop table person； -- 创建序列 create sequence seq_person; -- 删除序列 drop sequence seq_person; --创建500万条的数据 begin for i in 1..5000000 loop insert into person values(seq_person.nextval,'测试数据'||i); end loop; commit; end; -- 无索引查询 select * from person where pname ='测试数据4704980'; --2.334 -- create index 索引名称 on 表名(列名); create index person_index on person(pname); -- 有索引查询 select * from person where pname ='测试数据4704980'; --0.076复合索引：
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：
Create index emp_idx1 on emp（ename，job）； Create index emp_idxl on emp（job，ename）；
-
同义词
使用同义词的作用？
1.可以很方便的访问其它用户的数据库对象
2.缩短了对象名字的长度
同义词和视图的区别：不需要dba权限就可以创建同义词
视图可以隐藏列，同义词不可以去掉列只是整张表的复制--查询syj的orders表 select * from syj.orders; -- 创建 -- create public synonym 同义词名 for 目标表名 create public synonym my_order for syj.orders; -- 删除 -- drop public synonym 同义词名 drop public synonym my_order
数据的导入导出
-
PLSqlDeveloper的导入导出方式参照Oracle导入导出.docx
全库导出:exp system/orcl full=y file=expdat.dmp 全库导入:imp system/orcl full=y file=expdat.dmp 按用户导出: exp scott/tiger file=expdat.dmp 按用户导入: imp scott/tiger file=expdat.dmp full=y
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
