QQ账号登录	立即注册>>

您现在的位置：论坛 › 盖世程序员(我猜到了开头却没有猜到结局) › 盖世程序员 > Oracle增删查改 day05

总共48087条微博

动态微博

发表新帖

查看: 1681|回复: 0

Oracle增删查改 day05

45 主题	5 听众	119 金钱

三袋弟子

该用户从未签到

电梯直达

楼主

发表于 2014-06-03 21:43:49 |只看该作者 |倒序浏览

复习：SQL语句
select
DML: insert / update / delete

CRUD
C：Create
R: Retrive
U: Update
D: Delete

DDL: create / drop / truncate / alter

TCL: commit / rollback / savepoint

DCL: grant / revoke

grant: 赋予权限
revoke: 剥夺权限
数据库中的用户: openlab hr scott
               emp

假设现在的用户是openlab
SQL>grant select on emp to scott;
SQL>revoke select on emp from scott;

scott的会话：
SQL>select * from openlab.emp;
SQL>select * from emp;

Oracle数据库的用户：
sys
system

scott/tiger
openlab/open123
ninglj/******
exam/exam123

C:>sqlplus scott/tiger@192.168.0.26:1521/tarena
SQL>

今天的内容：
1、约束条件
2、数据库的其他对象

一、约束条件 Constraint
1。主键约束：Primary key, 简称PK
--建表时增加主键约束条件
create table dept_ning1(
deptno number(2) primary key, --列级约束条件
dname varchar2(20),
location varchar2(40)
);
insert into dept_ning1
values(10,'developer','beijing');
insert into dept_ning1
values(10,'market','shenzhen');
--如果插入重复编码，会提示：
ORA-00001: unique constraint (NINGLJ.SYS_C00634053) violated
其中，SYS_C00634053是数据库自定义的主键名
--约束条件如果没有显式命名，
数据库给约束条件命名：SYS_C*****

--在建表时自定义约束条件名
--建议命名规则：表名_列名_约束条件的类型
create table dept_ning2(
deptno number(2),
dname varchar2(20),
location varchar2(40),
constraint dept_ning2_deptno_pk
primary key (deptno) --表级约束条件
);
--当插入重复编码时，会提示具体的约束条件名字错误。
--方便定位出错的原因

主键约束：primary key = 不能重复 + 不能为空

2、非空约束: not null，简称NN
学生姓名必须提供，但是可以重复
--只能定义在列级
create table student_ning(
id number(4) primary key,
name varchar2(10) not null,
age number(2)
);
insert into student_ning
values(1, 'zhangwei', 20);
--名字可以重复
insert into student_ning
values(2, 'zhangwei', 19);
--提示name列不能为NULL
insert into student_ning
values(3, null, 18);

3、唯一约束:Unique，简称UK
create table student_ning1(
id number(4) primary key,
name varchar2(10) not null,
email varchar2(30) unique,
age number(2)
);
insert into student_ning1
values(1,'amy','amy@doctor.com', 19);
--ORA-00001: 唯一约束条件被违反
insert into student_ning1
values(2,'rory','amy@doctor.com', 19);

--唯一约束建立在表级
--主键约束建立在表级
create table student_ning2(
id number(4),
name varchar2(10) not null,
email varchar2(30),
age number(2),
constraint student_ning2_id_pk
primary key (id),
constraint student_ning2_email_uk
unique (email)
);

insert into student_ning2
values(1,'amy','amy@doctor.com',19);

--unique约束只要求不能重复，可以为NULL
insert into student_ning2
values(2, 'rory', null, 20);

--不管是insert还是update，email都不能重复。
update student_ning2 set email = 'amy@doctor.com'
where id = 2;

4、检查约束 check 简称 CK
create table student_ning3(
id number(4),
name varchar2(10) not null,
email varchar2(30),
age number(2),
gender char(1), --'F':女生; 'M':男生
constraint student_ning3_id_pk
primary key (id),
constraint student_ning3_email_uk
unique (email),
constraint student_ning3_age_ck
check (age > 10),
constraint student_ning3_gender_ck
check (gender in ('F', 'M', 'f', 'm'))
);
insert into student_ning3
values(1,'amy',null,19,'F');
insert into student_ning3
values(2,'rory',null,8,'M'); --违反check约束 age > 10
insert into student_ning3
values(3,'doctor',null,50,'A'); --违反check约束 gender in ('F','M')

PK / NN / UK / CK / FK

5.外键. Foreign key, 简称FK
create table major_ning (
id number(2) primary key,
name char(20)
);
insert into major_ning values(1, 'computer');
insert into major_ning values(2, 'history');
insert into major_ning values(3, 'music');
insert into major_ning values(4, 'sing');
commit;
create table student_ning4(
sid number(3),
name varchar2(20) not null,
email varchar2(30),
gender char(1),
majorid number(2),
constraint stu_n4_sid_pk primary key(sid),
constraint stu_n4_email_uk unique (email),
constraint stu_n4_g_ck check (gender in ('F','M')),
constraint stu_n4_mid_fk foreign key
  (majorid) references major_ning(id)
);

insert into student_ning4
values(101,'amy',null,'F',1);
--新增数据，不存在9这个专业
insert into student_ning4
values(102,'river',
'river@sina.com','F', 9);
--提示错误：
ORA-02291: integrity constraint (NINGLJ.STU_N4_MID_FK)
violated - parent key not found
insert into student_ning4
values(102,'river',
'river@sina.com','F', null);

--有学生属于专业1(computer)
delete from major_ning where id = 1;
--ORA-02292: child record found

create table student_ning5(
sid number(3),
name varchar2(20) not null,
email varchar2(30),
gender char(1),
majorid number(2),
constraint stu_n5_sid_pk primary key(sid),
constraint stu_n5_email_uk unique (email),
constraint stu_n5_g_ck check (gender in ('F','M')),
constraint stu_n5_mid_fk foreign key
  (majorid) references major_ning(id)
  on delete set null);

insert into student_ning5
values(101,'amy',null,'F',4); --amy是4专业的学生
--删除编码为4的专业
delete from major_ning where id = 4;
--amy的专业被设置为NULL
select * from student_ning5;

create table student_ning6(
sid number(3),
name varchar2(20) not null,
email varchar2(30),
gender char(1),
majorid number(2),
constraint stu_n6_sid_pk primary key(sid),
constraint stu_n6_email_uk unique (email),
constraint stu_n6_g_ck check (gender in ('F','M')),
constraint stu_n6_mid_fk foreign key
  (majorid) references major_ning(id)
  on delete cascade);

--复制表，不复制约束条件。
create table 表名 as 查询语句

--建立约束条件的时机
--建表同时建立约束条件：
create table student(
id number(3),
name char(20) not null,
majorid number(2),
constraint stu_id_pk primary key(id),
constraint stu_mid_fk foreign key(majorid)
references major(id)
);
--在创建完表以后创建约束
create table student(
id number(3),
name char(20) not null,
majorid number(2));
alter table student
  add constraint stu_id_pk primary key(id);
alter table student
  add constraint stu_mid_fk foreign key(majorid)
  references major(id);

-----------脚本文件begin---------
alter table student drop constraint stu_mid_fk;
drop table student;
drop table major;
create table major(....);
create table student(....);
alter table student add constraint ....
-----------脚本文件end------------

user_tables  :用户所有的数据表
user_constraints:用户所有的约束条件
user_objects :用户所有的对象(表、视图、索引...)

all_tables  :用户能访问的数据表，
         包括自己的和别的用户允许自己访问的
all_constraints:用户能访问的约束条件
all_objects :用户能访问的对象(表、视图、索引...)

PK / FK
NOT NULL / UNIQUE
CHECK
其中：CHECK和NOT NULL可以在程序级别控制

二、数据库的其他对象
表 Table
视图 View
索引 Index
序列 Sequence
过程 Procedure
函数 Function
包 Package
触发器 Trigger
同义词 Synonym
....

1.视图View
create view v_emp_ning
as
select empno, ename, job from emp_ning
where deptno = 20;
--使用和表相同
desc v_emp_ning
select * from v_emp_ning;
--视图的好处：简化查询；隐藏数据表的列

create view v_emp_count
as
select deptno, count(*) emp_num
from emp_ning
group by deptno;
--修改基表数据
update emp_ning set deptno = 10
where deptno is null;
--视图查询到的是修改后的数据。
--视图不包含任何数据。是基表数据的投影。
select * from v_emp_count;

--创建或修改视图
create or replace view v_emp_count
as
select deptno, count(*) emp_num,
sum(salary) sum_s,
avg(nvl(salary,0)) avg_s,
max(salary) max_s,
min(salary) min_s
from emp_ning
group by deptno;

--查询视图的定义
select text from user_views
where view_name = 'V_EMP_COUNT';
--如果视图对应的sql语句显示不全
set long 1000

2.索引 Index ：用来提高查询效率的机制。
全表扫描: Full Table Scan: 查询效率极低
索引查询：比全表扫描快。

索引的结构：数据 + 地址
         张三 + Room203

对于数据变更频繁(DML操作频繁)的表，
索引会影响性能。

如果数据表有PK/Unique两种约束，索引自动创建
除此以外，索引必须手动创建。
create table student_ning7(
id number(4),
name char(20),
email char(40),
constraint stu_n7_id_pk primary key(id),
constraint stu_n7_email_uk unique(email)
);
select constraint_name
from user_constraints
where table_name = 'STUDENT_NING7';

--查询student_ning7表上的索引，
和主键/唯一约束条件同名，数据库自动创建的索引。
select index_name from user_indexes
where table_name = 'STUDENT_NING7';

--凡是id或email上的查询，会使用索引
select * from student_ning7
where id = 1001;

--这种查询用不到索引：全表扫描
select * from student_ning7
where name = 'zhangsan';

--创建基于名字字段的索引,索引名自定义
create index 索引名 on 表名(列名);
create index idx_stu7_name
on student_ning7(name);

索引：
1)了解索引的工作原理
2)pk/uk自动创建索引
3)根据查询情况决定手动创建哪些索引。

3、序列 Sequence --Oracle独有的
--产生从1开始的数字值，步进是1
create sequence myseq_ning;
select myseq_ning.nextval from dual;

序列的特性：产生连续的不同的数字值
用来作为数据表的主键。

--使用序列产生的值作为表的主键值
insert into student_ning7(id,name)
values(myseq_ning.nextval, 'amy');

student_ning7和序列myseq_ning的关系:
是数据库中的独立对象
--表可以用序列产生的值作为主键，也可以不用
--序列可以为一个或多个表产生主键，也可以不用
insert into student_ning7(id,name)
values(100,'river');
insert into dept_ning
values(myseq_ning.nextval,'market','bj');

--建议：一个序列为一个表产生主键

希望主键值从1000开始，步进是2？
create sequence myseq_ning1
start with 1000
increment by 2;

insert into student_ning7
values(myseq_ning1.nextval, 'song', null);

--删除序列，对曾经产生过的数据没有任何影响。
drop sequence myseq_ning1;

SQL> edit

科帮网 1、本主题所有言论和图片纯属会员个人意见，与本社区立场无关
2、本站所有主题由该帖子作者发表，该帖子作者与科帮网享有帖子相关版权
3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和科帮网的同意
4、帖子作者须承担一切因本文发表而直接或间接导致的民事或刑事法律责任
5、本帖部分内容转载自其它媒体，但并不代表本站赞同其观点和对其真实性负责
6、如本帖侵犯到任何版权问题，请立即告知本站，本站将及时予与删除并致以最深的歉意
7、科帮网管理员和版主有权不事先通知发贴者而删除本文