Oracle增删查改 day05
复习：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]