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