oracle10g培训日志☆4

---------------创建表
create table 表名
(
列名  数据类型,
列名  数据类型,
列名  数据类型,
....
);

char()      固长字符类型（查询效率高）
varchar2()  变长字符类型
number
number(5,2) 代表5个有效数字，小数占2位
date

---------------约束
primary key 主键约束
foreign key 外键约束
not null    非空约束(只能写在列级)
unique      唯一约束
check       检查约束

 

create table student
(
SNO     varchar2(10),
SNAME   varchar2(20) not null,
SSEX    varchar2(2),
SAGE    number,
SDEPT   varchar2(40),
email   varchar2(40),
primary key(sno),
unique(email),
check(ssex in ('男','女'))
);

create table course
(
cno    varchar2(10),
cname  varchar2(20) not null,
primary key(cno)
);

create table sc
(
sno   varchar2(10),
cno   varchar2(10),
grade number,
primary key(sno,cno),
check(grade between 0 and 100),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);

------------------维护表
----------添加列
alter table student
add (bir date);
----------修改列
------列名
alter table student
rename column bir to birthday;---把bir列改成birthday

------数据类型
alter table student
modify (birthday  varchar2(40));

SQL> alter table student
  2  modify (birthday  date);

----------删除列
alter table student
drop (birthday);
等价于
alter table student
drop column birthday;

------------------维护约束
---------添加约束
---------添加非空约束
alter table student
modify (sname not null);

alter table course
modify (cname not null);
--------添加唯一约束
alter table student
add unique(email);

--------添加检查约束
alter table student
add check(ssex in ('男','女'));

alter table sc
add check(grade between 0 and 100);
--------添加主键
alter table student
add primary key(sno);

alter table course
add primary key(cno);

alter table sc
add primary key(sno,cno);

--------添加外键约束
alter table sc
add foreign key(sno) references student(sno);

alter table sc
add foreign key(cno) references course(cno);

---------删除约束
alter table 表名
drop constraint 约束名;

alter table 表名
drop constraint 约束名 cascade;---强制删除约束

select * from user_constraints;---查询当前用户下所有约束

在user_constraints表中
P  主键约束
R  外键约束
U  唯一约束
C  检查约束或者非空约束

 

------------------删除表
SQL>drop table 表名;------删除表可以闪回

SQL> drop table  表名  purge;----删除表不能闪回

 

----------------闪回
SQL> drop table sc;

表已删除。

SQL> drop table student;

表已删除。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COURSE                         TABLE
BIN$Lgw/Enc4Q72YNT4rrgrzPQ==$0 TABLE
BIN$ML1+EK/fR6GWj4HPP7BDMA==$0 TABLE

 

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
SC               BIN$ML1+EK/fR6GWj4HPP7BDMA==$0 TABLE        2009-07-17:11:08:25
STUDENT          BIN$Lgw/Enc4Q72YNT4rrgrzPQ==$0 TABLE        2009-07-17:11:08:27

 

SQL> flashback table student to before drop;

闪回完成。

SQL> flashback table sc to before drop;

闪回完成。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COURSE                         TABLE
SC                             TABLE
STUDENT                        TABLE

SQL> show recyclebin;
SQL>

SQL> purge recyclebin;

回收站已清空。

 

SQL> purge table sc;----清空回收站中的sc表

SQL> flashback table "BIN$2DaB3OPWSZSN8IvMkgzg3w==$0" to before drop;--用回收站的名称闪回

SQL> flashback table "BIN$H743EKgMQZ645vfqUOP9qQ==$0" to before drop rename to old_a;--闪回的同时重新定义表名

---------必须有dba角色
select * from dba_users;---查看所有用户

---------------修改表名
SQL> rename a to b;--把表a改为表b

------------------注释（表级，列级）

SQL> comment on table student is '学生表';

注释已创建。

SQL> comment on table course is '课程表';

注释已创建。

SQL> comment on table sc is '选课表';

注释已创建。

select * from user_tab_comments-----查看表级数据字典

SQL> comment on column student.sno is '学号';

注释已创建。

SQL> comment on column student.sname is '姓名';

注释已创建。

SQL> comment on column student.sage is '年龄';

注释已创建。

 

select * from user_col_comments-----查看列级数据字典

 

SQL> select * from user_col_comments
  2  where table_name='STUDENT';------查看数据字典中学生表的列注释

 

-------------------------其他数据库对象
---------------视图
create view 名
as
select ....

create view stu
as
select sname,cname,grade
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno;

SQL> select * from stu;-----查询视图

select * from user_views;-----查看竟有哪些视图

视图:虚表
虚:因为他不包含数据
表:因为多视图的查询和表一样

视图的本质：就是在数据字典的一句select语句

------------创建视图，查询每个部门的平均工资
create view avg_salary
as
select department_id 部门id,avg(salary) 平均工资
from employees
group by department_id;

注意:如果创建视图时，select语句中有函数，必须定义别名

------------删除视图
drop view 名字;

----------------视图能不能做DML（数据操纵语言，insert,update,delete）操作
不一定

数据来至于一个表，不带函数，不带分组，一般能
数据来至于多个表，带函数，带分组，不能

--------------------内联视图

select * from (select * from student where ssex='男')

---------------------top-n分析
----伪列（在表中没有，但是可以在sql语句中使用）

SQL> select rownum,sno,sname
  2  from student;

注意:rownum行号，如果用rownum过滤只能写<和<=

SQL> select * from student
  2  where rownum<4;

---------------年龄最大的前三个人
select * from (select * from student order by sage desc)
where rownum<4;

--------------序列
序列:发唯一整数的数据库对象

create table temp
(
id   number,
name varchar2(20),
primary key(id)
);
-----------创建序列s1,起始值1，每次增长1
create sequence s1
increment by 1
start with 1;

--------------nextval下一个值
insert into temp(id,name) values(s1.nextval,'张3');
insert into temp(id,name) values(s1.nextval,'张4');
insert into temp(id,name) values(s1.nextval,'张5');
insert into temp(id,name) values(s1.nextval,'张6');

SQL> select s1.currval from dual;----查看序列当前值。

drop sequence s1;----删除序列

select * from user_sequences;----查询序列

--------------索引
作用:提高查询速度，减少磁盘I/O

create index 名 on 表名(列名);

当你在表中创建主键，唯一约束的时候会自动创建一个唯一索引。

 

下面的情况通常不值得创建索引，如果：
很小数据量的表
在查询中不常用来作为查询条件的列
查询最终得到的结果集很大
频繁更新的表（索引对于DML操作是有部分负面影响的）
索引列作为表达式的一部分被使用时（比如常查询的条件是SALARY*12，此时在SALARY列上创建索引是没有效果的）

 

你应该创建索引，如果：
?一个列包含一个大范围的值
?一个列包含很多的空值
?一个或多个列经常同时在一个WHERE子句中或一个连接条件中被使用
?表很大，并且经常的查询期望取回少于百分之2 到4 的行

SQL>insert into student
  2 select * from student;

SQL> update student
  2  set sno=rownum;

已更新2097152行。

SQL> commit;

提交完成。

SQL> set timing on
SQL> select * from student
  2  where sno='2500';

SNO      SNAME    SS       SAGE SDEPT
-------- -------- -- ---------- --------------------
2500     王林     男         19 计算机

已用时间:  00: 00: 05.82
SQL> create index i1 on student(sno);

索引已创建。

已用时间:  00: 00: 31.35
SQL> select * from student
  2  where sno='2500';

SNO      SNAME    SS       SAGE SDEPT
-------- -------- -- ---------- --------------------
2500     王林     男         19 计算机

已用时间:  00: 00: 00.12
SQL>

select * from user_indexes;

drop index i1;
 

-------------同义词

表的另一种的访问方式（表的别名）

select * from student;

create synonym s for student;

select * from s;

drop synonym s;

select * from user_synonyms;

SQL> select * from user_objects;----查看当前用户下所有对象

----------------