Oracle表连接查询和联合查询

查询结果排序

Oracle查询语句中使用Order by语句对查询结果进行排序

ASC设置自然排序(正序|升序)，可省略此关键字

DESC 设置倒序排序(倒序|降序)

select * from students order by birth ASC

提示：Oracle查询排序支持多列排序

表连接查询

表连接查询目的在于组合多表中的数据作为查询结果显示

自然连接，匹配查询表相同列名的数据去除重复项显示结果
内连接，设置连接条件，只匹配符合条件的数据进行组合
外连接，设置连接条件，以主连接表作为最终连接从参考

左外连接右外连接全连接

自然连接

select * from 表1 natural join 表2 natural join 表3

内连接

内连接使用inner join关键字实现;

内连接通常使用where或on关键字设置连接条件;

使用on关键字是标准方式。

如果使用where语句，必须在inner join之前使用

外连接

select * from 表1 left[outer] join 表2 on 表1.id = 表2.sid

无论是否匹配连接条件，确保左侧表中所有记录全部显示，outer关键字可以省略

select * from 表1 right[outer] join 表2 on 表1.id = 表2.sid

无论是否匹配连接条件，确保右侧表中所有记录全部显示，outer关键字可以省略

联合查询

UNION查询(并集联合)

UNION ALL 查询(全联合)

INTERSECT查询(交集联合)

MINUS 查询(最小联合)

Union联合查询

UNION对两个查询结果集进行合并操作，将完全重复的记录剔除(合并重复行)，相当于合并操作之后再执行一次DISTINCT操作

UNION可以对字段名不同但数据类型相同的结果集进行合并，UNION只允许对最终合并的结果集进行ORDER BY

Union查询的限制

两个数据源或者结果集必须具有相同的列数量；
依次按照2个数据源的字段顺序合并；
相互合并的字段名称可以不同，但是数据类型必须是相同或是兼容类型；
使用UNION前数据源字段名称作为最终结果集字段名称。

Intersect联合查询

Intersect查询用于获得两个结果集的交集，也就是只有都出现在两个表中的数据才会被查询返回，intersect多用于某个表对于另外一个表完全重复数据行的筛选。intersect和natural JOIN连接查询功能非常类似。

Minus联合查询

minus联合查询称之为减集，如A minus B则A 中和B中同时有的数据将被剔除而只保留A中有而B中没有的数据。

--查询哪些学生没有成绩

select id 学生编号 from student minus select scores.sid from scores;

————————————————

oracle 建立外键此列列表的唯一关键字或主键不匹配

在我建立外键的时候发生了这样的错误，猜测可能是跟主键有关系

我的语句是这么写的：

constraint for_score_students foreign key(studentid) references students( studentid)

错误提示在了红色s处

说明：

1 这句语句写在第三个表score里面，我想把score里的studentid作为score的外键

2 于是将问题定位于students表

出现这个错误的原因有两种：

1：students里的studentid不是主键（唯一关键字）

此时只需要对studentid加一个主键约束即可add primary key (studentid)

而我的问题出在创建students时用的是旧表复制而来的：create table students as select * from student

而复制旧表只能复制数据而不能复制约束，所以倒是students里的主键约束消失，出现了这样的错误。

2: studentid不是唯一主键，可能我的students里的主键设为了两个（联合主键）【 primary key (courseid,studentid)】

此时就需要设为联合外键作为主键，像这样 constraint foreign_name foreign key( courseid,studentid) references studentid( courseid,studentid)

提一句，尽量避免联合主键联合外键，这样不符合第三范式，可能会造成数据冗余。

————————————————

原文链接：https://blog.csdn.net/yzliang2016/article/details/79401689

————————————————

--查询结果的排序(Order by)
select * from emp_tab order by age asc; --自然排序，可以省略asc关键字
select * from emp_tab order by age desc; --自然排序，不可以省略desc关键字

--多列排序(永远考虑第一个排序完成后才会考虑依次后面的排序规则)
select * from emp_tab where address is null order by age desc,name desc; --倒序排序

--表连接查询
--自然连接
select * from emp natural join dept;

--内连接
select d.deptno,d.dname,d.loc from scott.dept d inner join scott.emp e on d.deptno = e.deptno;

--内连接显示部门及员工信息
select d.name 部门名称,e.id 员工ID,e.name 员工姓名 from dept d inner join emp e on d.id = e.depid;

--另一种内连接
--内连接显示部门及员工信息
select d.name 部门名称,e.id 员工ID,e.name 员工姓名 from dept d,emp e where d.id = e.depid;

--多表内连接查询
select d.name 部门名称,e.id 员工ID,e.name 员工姓名,t.address 员工地址 from dept d inner join emp e on d.id = e.depid 
inner join emp_tab t on e.address = t.address;

--左外连接
select e.ID,e.NAME,t.name,t.address from emp e left join emp_tab t on e.address = t.address;
select * from scott.dept left join scott.emp on scott.dept.deptno = scott.emp.deptno;
--右外连接
select e.ID,e.NAME,t.name,t.address from emp e right join emp_tab t on e.address = t.address;
select * from scott.dept right join scott.emp on scott.dept.deptno = scott.emp.deptno;

CREATE TABLE user2
(
 NAME VARCHAR2(64),
 age NUMBER,
 address VARCHAR2(64)
);

CREATE TABLE user3
(
 uNAME VARCHAR2(64),
 uage NUMBER,
 uaddress VARCHAR2(64)
);

INSERT INTO user2 values('白无瑕',23,'雪山');
INSERT INTO user2 values('令狐冲',29,'华山');
INSERT INTO user2 values('洪七公',62,'乞丐山');

INSERT INTO user3 values('白无瑕',23,'雪山');
INSERT INTO user3 values('李隆基',50,'凌霄宝殿');
INSERT INTO user3 values('洪七公',62,'乞丐山');

--联合查询
select name,age from user2 union select uname,uage from user3;

--联合查询排序
select name n,age a from user2 union select uname n,uage a from user3 order by n desc;

--联合全部查询
select name,age from user2 union all select uname,uage from user3 order by name desc;

select * from user2;
select * from user3;

--交集联合查询
select name,address from user2 intersect select uname,uaddress from user3;

select * from dep_table;
select * from emp_tab;

--减集联合查询
select ID 部门编号 FROM dep_table MINUS select dep_id from emp_tab;

--1 查询员工信息并定义中文列别名,显示列部门代码，部门名称，成立日期，员工姓名，生日和电话号码；
select d.code 部门代码,d.name 部门名称,d.newdate 成立日期, e.name 员工姓名,e.birth 生日,e.phone 电话号码 from dept d inner join emp e on d.id = e.depid;

--2 查询员工信息，显示部门名称，姓名，性别，地址和生日只限制性别是女的并安装生日倒序排列；
select d.name 部门名称,e.name 员工姓名,e.gender 性别,e.address 地址,e.birth 生日 from dept d inner join emp e on d.id = e.depid 
where e.gender = 0 order by e.birth desc;

--3 查询销售部所有员工信息显示姓名，地址，部门名称先安装生日正序排列再安装姓名倒序排列显示结果，要求使用左外连接实现；
select e.name 姓名,e.address 地址,d.name 部门名称 from emp e left join dept d on e.depid = d.id where d.name = '销售部' 
order by e.birth desc,e.name desc;

--4 使用union 自联合emp查询组合数据；
select e.address from emp e union select t.address from emp_tab t;

--5使用union all 自联合emp查询组合数据
select e.address from emp e union all select t.address from emp_tab t;

--6使用intersect 自联合emp查询组合数据；
select e.address from emp e intersect select t.address from emp_tab t;

--7使用minus 自联合emp查询组合数据；
select e.address from emp e minus select t.address from emp_tab t;

CREATE TABLE Products
(
 PID Number Primary key not null,
 PCode Varchar2(64) not null,
 PNmae Varchar2(64) not null,
 Price Date,
 Quantity Number,
 Descs Varchar2(1000)
);
alter table products rename column PNmae to PName;
alter table products modify(Price Number);
comment on column products.pid is '主键';
comment on column products.PCode is '产品代码';
comment on column products.PName is '产品名称';
comment on column products.Price is '单价';
comment on column products.Quantity is '库存数量';
comment on column products.Descs is '备注';

insert into products values(seq_sales.nextval,'DZ20170508','JP数码相机9X',6000,5000,'自定义');
insert into products values(seq_sales.nextval,'DZ20170610','CH摄像王xx5J',20500,1500,'自定义');
insert into products values(seq_sales.nextval,'DZ20195502','CH掌上宝MI9',2699,3500,'自定义');
insert into products values(seq_sales.nextval,'DZ20198807','JPX单反相机9XX',18000,2500,'自定义');

select * from products;
select * from emp;
select * from sales;

CREATE TABLE Sales
(
 ID number not null Primary key,
 salDate Date not null,
 PID number not null, 
 constraint fk_sales_pid foreign key(PID) references products(PID),
 EID varchar2(64) not null,
 constraint fk_sales_eid foreign key(EID) references emp(ID),
 SalQuantity Number, 
 constraint ck_sales_SalQuantity check(SalQuantity >=0),
 Price number,
 Descs varchar(1000)
)
comment on column sales.id is '主键';
comment on column sales.saldate is '销售日期(年月日)';
comment on column sales.PID is '产品ID，外键引用Product表主键值';
comment on column sales.EID is '员工id，外键，引用emp表主键值';
comment on column sales.SalQuantity is '销售数量';
comment on column sales.Price is '销售实际单价';
comment on column sales.Descs is '备注';

insert into sales values(seq_sales.nextval,to_date('2018-5','yyyy-MM'),100,'661',1000,6000，'测试数据');

posted @ 2021-09-12 00:08 伊万阅读(874) 评论(0) 编辑收藏举报

刷新页面返回顶部

伊万

Oracle表连接查询和联合查询

公告