資料庫的集合，分頁及約束條件

1、集合操作
資料中的查詢語句得到的是一個結果集(ResultSet)
集合A和集合B
集合A：{1,2,3,4,5}
集合B：{1,3,5,7,9}
集合A和集合B合集:{1,2,3,4,5,7,9}
集合A和集合B交集:{1,3,5}
集合A和集合B差集:{2,4}
集合B和集合A差集:{7,9}
集合操作說明：
a.兩個結果集必須結構相同
當列的個數、列的順序、列的資料型別一致時，稱這
兩個結果集結構相同。只有結構相同的結果集才能
進行集合操作。
b.合集 union和union all
c.交集 intersect
d.差集 minus

//查詢10部門的員工姓名和薪水
select ename,salary from emp_xu 
where deptno=10;
張三丰       99999.99
張無忌           5000
楊過             8000

//查詢薪水大於6000的員工姓名和薪水
select ename,salary from emp_xu
where salary>6000;
張三丰       99999.99
楊過             8000
喬峰             8000
段譽            15000
孫悟空          50000
燕小六          12000
張無忌           8000

1)合集
union：去重，排序
union all：不去重，不排序
select ename,salary from emp_xu 
where deptno=10
union
select ename,salary from emp_xu
where salary>6000;

select ename,salary from emp_xu 
where deptno=10
union all
select ename,salary from emp_xu
where salary>6000;

2)交集 intersect
select ename,salary from emp_xu 
where deptno=10
intersect
select ename,salary from emp_xu
where salary>6000;

3)差集 minus
A-B:
select ename,salary from emp_xu 
where deptno=10
minus
select ename,salary from emp_xu
where salary>6000;

B-A:
select ename,salary from emp_xu
where salary>6000
minus
select ename,salary from emp_xu 
where deptno=10;

錯誤演示:
select ename,salary from emp_xu 
where deptno=10
intersect
select deptno,salary from emp_xu
where salary>6000;

select ename,salary from emp_xu 
where deptno=10
union
select position,salary from emp_xu
where salary>6000;

2、表間關聯查詢
1)內連線
 語法：
 表1 [inner] join 表2 on 條件
 //查詢員工的姓名和其部門的名字
 select emp_xu.ename,dept_xu.dname
 from emp_xu inner join dept_xu
 on emp_xu.deptno=dept_xu.deptno;

 select e.ename,d.dname
 from emp_xu e inner join dept_xu d
 on e.deptno=d.deptno;
注意：結果集中的資料一定是在兩個表中能找到匹配
資料的記錄

//查詢員工的姓名和他領導的名字
select e1.ename,e2.ename
from emp_xu e1 join emp_xu e2
on e1.leader=e2.empno;

select e1.ename,e2.ename
from emp_xu e2 join emp_xu e1
on e1.leader=e2.empno;

內連線：表1 inner join 表2 on 條件
說明：
a.表1為驅動表，表2為匹配表
b.當使用等值連線時，驅動表和匹配表可以互換的，
不會影響結果
c.執行過程：不論誰做驅動表，都會遍歷驅動表，在
匹配表中查詢匹配資料(匹配表中每條記錄都要進行
匹配)

內連線：等值連線和非等值連線，返回滿足條件的記錄
等值連線：條件是等值操作'='
非等值連線：條件不是等值操作'>、<、<>'

//查詢員工的姓名和其部門的名字，要求沒有部門的
員工也要查詢出來

員工=有部門的員工+沒有部門的員工
select e.ename,d.dname
from emp_xu e inner join dept_xu d
on e.deptno=d.deptno
union
select ename,'No Dept' from emp_xu 
where deptno is null;

2)外連線
一：左外連線(以左邊表為驅動表)
語法：
表1 left [outer] join 表2 on 條件

二：右外連線(以右邊表為驅動表)
語法：
表1 right [outer] join 表2 on 條件

三：全外連線
語法：
表1 full [outer] join 表2 on 條件

外連線特徵：
如果驅動表在匹配表中找不到匹配記錄，則匹配一行
空行(驅動表中資料全部出現在結果集當中)

外連線的結果集=內連線的結果集+驅動表在匹配表中
匹配不上的記錄和空值

//查詢員工的姓名和其部門的名字，要求沒有部門的
員工也要查詢出來

員工=有部門的員工+沒有部門的員工

外連線：員工全部被查詢出來，員工表要做驅動表
select e.ename,nvl(d.dname,'No Dept')
from emp_xu e left outer join dept_xu d
on e.deptno=d.deptno;

select e.ename,nvl(d.dname,'No Dept')
from dept_xu d right join emp_xu e
on e.deptno=d.deptno;//左、右外連線可以互換

//查詢員工的姓名和其部門的名字，要求沒有員工的
部門也要查詢出來
分析：需要將全部部門資料查詢出來，部門表作為
驅動表
select d.dname,e.ename
from dept_xu d left join emp_xu e
on d.deptno=e.deptno;

select d.dname,e.ename
from emp_xu e right join dept_xu d
on d.deptno=e.deptno;

//查詢哪些部門沒有員工
分析：部門表資料全部被查詢出來，部門表作為驅動
表。新增where條件過濾滿足題目要求記錄
select d.dname,e.ename
from dept_xu d left join emp_xu e
on d.deptno=e.deptno
where e.ename is null;

select d.dname,e.ename
from dept_xu d left join emp_xu e
on d.deptno=e.deptno
where e.empno is null;

//查詢員工的姓名和其部門的名字，要求沒有部門的
員工和沒有員工的部門全部查詢出來
select ename,dname
from emp_xu e full outer join dept_xu d
on e.deptno=d.deptno;

外連線的本質是驅動表中的資料全部出現在結果集中，
一條資料都不能少。

全外連線=內連線的結構集+驅動表在匹配表中匹配
不上的記錄和空值+匹配表在驅動表匹配不上的記錄
和空值

3、Oracle資料庫排名分頁
關鍵字：rownum
rownum是一個偽列，對查詢返回資料的行進行編號即
行號，由1開始。

//查詢員工表中前4條記錄
select empno,ename,rownum from emp_xu 
where rownum<5;

//查詢員工表中第四、第五、第六條記錄
select empno,ename from emp_xu
where rownum>3;//未選定，rownum不能使用'>'

select empno,ename from emp_xu
where rownum>3 and rownum<7;//錯誤的寫法

select empno,ename from emp_xu
where rownum<7
minus
select empno,ename from emp_xu
where rownum<4;

換種寫法：
select empno,ename,rownum rn from emp_xu
where rownum<7;

select empno,ename,rn,rownum
from (
select empno,ename,rownum rn from emp_xu
where rownum<7
)
where rn>3;

4、約束條件(constraint)
1)主鍵約束
主鍵:primary key 簡稱PK
主鍵約束=不能為空+不能重複
一張表中只能存在一個主鍵，主鍵可以是一列也可以
是多列組合(聯合主鍵)。
主鍵約束可以有兩種方式定義：列級約束和表級約束
列級：
create table stu(
id number(4) primary key,
name varchar2(10)
);

insert into stu values(null,'張三');//不能為空
insert into stu values(0001,'張三');
insert into stu values(0001,'張三');//不能重複

drop table stu;//徹底刪除
表級：
create table stu(
id number(4),
name varchar2(10),
constraint stu_id_pk primary key(id)
);

注意：表級定義約束建議命名規則
表名_列名_約束型別

//聯合主鍵
constraint stu_id_name_pk primary key(id,name)

2)非空約束
非空約束:not null 簡稱NN
注意：非空約束只能定義在列級
drop table stu;

create table stu(
id number(4) primary key,
name varchar2(10) not null
);

insert into stu values(0001,null);//不能為空
insert into stu values(0001,'張三丰');

//表示給'name'欄位新增非空約束並取一個名字
name varchar2(10) constraint stu_name_nn not null

3)唯一約束
唯一約束：unique 簡稱UK
列級：
drop table stu;

create table stu(
id number(4) primary key,
name varchar2(10) not null,
email varchar2(20) unique
);

insert into stu values(0001,'張三','123@qq.com');
insert into stu 
values(0002,'張三','123@qq.com');//不能重複

insert into stu values(0002,'張三',null);
insert into stu values(0003,'張三丰',null);
注意：NULL重複插入，唯一約束沒有影響

表級：
drop table stu;

create table stu(
id number(4),
name varchar2(10) not null,
email varchar2(20),
constraint stu_id_pk primary key(id),
constraint stu_email_uk unique(email)
);

4)檢查約束
檢查約束：check 簡稱CK
列級：
drop table stu;

create table stu(
id number(4) primary key,
name varchar2(10) not null,
email varchar2(20) unique,
sex char(1) check(sex in('F','M'))
);

insert into stu 
values(0001,'張三','123@qq','男');//超出指定長度

insert into stu 
values(0001,'張三','123@qq','A');//違反檢查約束

insert into stu 
values(0001,'張三','123@qq','F');

表級：
drop table stu;

create table stu(
id number(4),
name varchar2(10) not null,
email varchar2(20),
sex char(1),
constraint stu_id_pk primary key(id),
constraint stu_email_uk unique(email),
constraint stu_sex_ck check(sex in('F','M'))
);

5)外來鍵約束
外來鍵約束：foreign key 簡稱FK
外來鍵約束定義在兩個表的兩個欄位上(或者一張表的
兩個欄位)，用來保證兩個欄位的關係。

//先建立部門表再建立員工(有先後順序)
create table dept_temp(
deptno number(2) primary key,
dname varchar2(10) not null
);

create table emp_temp(
empno number(4) primary key,
ename varchar2(12) not null,
deptno number(2),
constraint emp_temp_deptno_fk 
foreign key(deptno) references dept_temp(deptno)
);

//員工插入一條記錄
insert into emp_temp values(1001,'張三',10);

//部門插入一條記錄
insert into dept_temp values(10,'研發部');

注意：需要先插入部門資料，然後再插入員工資訊。
否則會報違反完整約束條件

修改表時新增主鍵約束
add constraint constraint_name primary key(column_name1,...);
select constraint_name from user_contraint where table_name='';
更改約束的名稱
alter table rename contraint pk_id to new_pk_id;
刪除主鍵約束
alter table userinfo disable constraint new_pk_id;
select constraint_name,status from user_contraint where table_name'';
alter table userinfo drop constraint new_pk_id;
alter table userinfo_p drop primary key;

總結：五大約束

*主鍵約束是惟一的每張表只能有一個，但是可以由多個欄位構成

*外來鍵約束是唯一的涉及兩張表之間關係的約束

*建立表時設定約束，非空約束只能在列級設定，其他的可以在表級和列級設定，且非空約束沒有名字，所以不能改名

*修改表時新增約束，特殊的也是非空約束，其實是修改欄位的語句，然後在後面加上not null即可,刪除時加上null即可

*約束名稱可以通過資料字典檢視------user_constraints

暫時不用------disable  使用時------enable  徹底刪除------drop