# 数据库---多表查询、关联查询、不同情况下数据库表设计

1.三种简单的关联方式：左关联、右关联、内联
2.实例演示多表查询和关联查询
3.1对1数据库表设计、1对多数据库表设计、多对多数据库表设计
4.数据库—存储过程
5.数据库—-binary: 用该关键字指定是否区分大小写

## 实例演示多表查询和关联查询

person表

car表

SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;

SELECT person.pname,car.cname,car.cid,car.price FROM person,car WHERE person.pid IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2) AND person.pid=car.pid;

SELECT person.pname,car.cname,car.cid,car.price FROM person INNER JOIN car ON person.pid IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2) AND person.pid=car.pid;
SELECT person.pname,car.cname, car.cid,car.price FROM person INNER JOIN car ON person.pid= car.pid WHERE person.pid IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2);

SELECT person.pname,person.pid FROM person WHERE person.pid NOT IN(SELECT pid FROM car);

SELECT person.pname,person.pid FROM person LEFT JOIN car ON person.pid= car.pid WHERE car.pid IS NULL;

SELECT person.pname,person.pid FROM car RIGHT JOIN person ON person.pid = car.pid WHERE car.pid IS NULL;

SELECT person.pname,car.cname, car.price FROM person,car WHERE person.pid= car.pid;

SELECT person.pname,car.cname, car.price FROM person INNER JOIN car ON person.pid= car.pid;

## 1对1数据库表设计、1对多数据库表设计、多对多数据库表设计

1对1数据库表设计

☆法1:分开两个独立的表
create table wife(
id int primary key,
name varchar(10),
sex char(1)
);

create table husband(
id int primary key,
name varchar(10),
sex char(1),
wid int unique,
constraint husband_fk foreign key(wid) references wife(id)
); //通过constraint约束体现一对一

☆法2:合并在一个独立表
create table person(
id int primary key,
name varchar(10),
sex char(1),
wife int,
husband int
);
insert into person values(1,'小花','0', 0,3);
insert into person values(2,'玉芬','0', 0,4);
insert into person values(3,'张三','1', 1,0);
insert into person values(4,'李四','1', 2,0);
insert into person values(5,'王五','0', 0,0);

create view women as select * from person where sex='0';
create view men as select * from person where sex='1';


1对多数据库表设计

方案一(差的设计)

P001  Jack   男     25     C001      BMW    12L    80w
P001  Jack   男     25     C002      Benz   12L    100w
P001  Jack   男     25     C003      Benz   12L    100w
P002  Tom    男     26     C004      BMW    12L    80w
P002  Tom    男     26     C005      Benz   10L    60w
P003  Rose   女     24     C006      Adio   10L    70w
方案二(好的设计)
1)把"一方"单独建表

P001  Jack   男     25  ...
P002  Tom    男     26  ...
P003  Rose   女     24  ...

2)把”多方”也建个表(要依赖”一方”: 通过外键(补一个字段) )

汽车编号    车型   排量   价格   车主
C001      BMW    12L    80w    P001
C002      Benz   12L    100w   P001
C003      Benz   12L    100w   P001
C004      BMW    12L    80w    P002
C005      Benz   10L    60w    P002
C006      Adio   10L    70w    P003

3)SQL代码实现

create table person2(
id varchar(32) primary key,
name varchar(30),
sex char(1),
age int
);
//插入记录
insert into person2 values('P1001','小花','0',25);
insert into person2 values('P1002','玉芬','0',24);
insert into person2 values('P1003','Tom','1',25);
insert into person2 values('P1004','Rose','0',23);

create table car(
id varchar(32) primary key,
name varchar(30),
price numeric(10,2),
pid varchar(32),
constraint car_fk foreign key(pid) references person2(id)
);
insert into car values('C001','BMW',82.5, 'P1001');
insert into car values('C002','BMW',111.5, 'P1001');
insert into car values('C003','Benz',78.5, 'P1001');
insert into car values('C004','BMW',55.5, 'P1002');
insert into car values('C005','Audio',82.5, 'P1002');
insert into car values('C006','QQ',6.5, 'P1003');
insert into car(id,name,price) values('C007','ABC',6.6);

△需求引例: 学生–课程 之间的关系

1、数据库设计(差的设计方案)
1)学生表

编号   姓名  性别  年龄   电话    住址  ...
P001  Jack   男    23    135***  ***  ...
P002  Tom    男    24    139***  ***  ...

2)课程(选课)表

课程    名称    学生   出版社   价格   ...
S001   Java    P001   电子工业  40    ...
S001   Java    P002   电子工业  40    ...
...
S002   数据库   P001   清华大学 35    ...
S002   数据库   P002   清华大学 35    ...

2、数据库设计(好的设计方案)
1)学生表(实体表独立建,编号字段为主键)

编号   姓名  性别  年龄   电话    住址  ...
P001  Jack   男    23    135***  ***  ...
P002  Tom    男    24    139***  ***  ...

2)课程表(实体表独立建,编号字段为主键)

课程    名称    出版社   价格   ...
S001   Java   电子工业   40    ...
S002   数据库  清华大学  35    ...

3)选课表(专为两个实体之间的联系建立一个关系表, 建立联合主键)

S001      P001
S001      P002
S002      P001
S002      P002

┗━━-------━━━┛
┃
联合主键

3) 代码实现

create table stud2(
id varchar(32) primary key,
name varchar(30),
age int
);
create table ject(
id varchar(32) primary key,
name varchar(30)
);
create table sj(
studid varchar(32),
jectid varchar(32)
);

//建立联合主键: 先加外键再加联合主键，，不行的!!!
alter table sj add constraint fk_stud foreign key(studid)
references stud2(id);
alter table sj add constraint fk_ject foreign key(jectid)
references ject(id);
alter table sj add constraint pk_sj primary key(studid,jectid);

//删除外键
alter table sj drop foreign key fk_stud;
alter table sj drop foreign key fk_ject;

//建立联合主键的正确顺序: 先加联合主键，再添加外键

※※※存储过程※※※※

create procedure 过程名(形参)
begin
sql语句1;
sql语句2;
...
end

call 过程名(实参)

create procedure p1()
begin
INSERT INTO stud2 VALUES('P007','小王',23);
select * from stud2;
end$$delimiter ; call p1(); 2.有参数的存储过程 delimiter$$
create procedure p2( in id varchar(32), in nm varchar(30), in age int)
begin
INSERT INTO stud2 VALUES(id,nm,age);
select * from stud2;
end$$delimiter ; call p2('P008','小谢',22); 3.有返回值的存储过程 delimiter$$
create procedure p3( in id varchar(32), in nm varchar(30),
in age int, out num int)
begin
INSERT INTO stud2 VALUES(id,nm,age);
select * from stud2;
select count(*) into num from stud2;
end
delimiter ;

call p3('P009','Alice',23, @xx);

//显示用户变量
select @xx;

※※※binary: 用该关键字指定是否区分大小写※※※

MySQL查询时，默认是不区分大小写的，如:
select * from stud where sname like 'j%';
select * from sutd where sname like 'J%';

1)要让MySQL查询区分大小写，可以:
select * from stud where binary sname like 'j%';
select * from sutd where binary sname like 'J%';

2)也可以在建表时，加以标识
create table tbName(
aa varchar(20) binary
);


• 本文已收录于以下专栏：

## sqlserver数据库不同数据库表关联查询

﻿﻿ 在不同的sqlserver数据库中，需要进行不同数据库中的表在同一sql语句中进行关联查询，可以事先在服务器上的SqlServer数据库中先执行以下sql语句：         (1)exe...
• shuyeshangdemayi
• 2014年12月17日 10:03
• 597

## 数据库 --关联查询（多表查询）

• qq_37248648
• 2017年11月07日 15:17
• 91

## 09--MySQL自学教程：多表查询之内连接、外连接以及【子查询】

1.为什么要拆表？去除冗余数据 2.表与表之间的关系
• baidu_37107022
• 2017年05月22日 17:41
• 511

## 【Hibernate九】HQL之多表查询（一对多和多对多）

• damogu_arthur
• 2015年07月17日 11:18
• 25462

## Spring boot jpa 多表关联查询

Spring boot jpa 多表关联查询
• qq_34117825
• 2017年05月03日 15:24
• 6164

## spring data jpa 多表多条件查询

spring data jpa 多表多条件查询
• quwenzhe
• 2017年01月24日 09:32
• 12947

## 多表查询经典练习

--编写多表查询语句的一般过程 --（1）、分析句子要涉及到哪些表 --（2）、对应的表中要查询哪些关联字段 --（3）、确定连接条件或筛选条件 --（4）、写成完整的SQL查询语句 --1、查询出...
• cselmu9
• 2012年07月02日 23:19
• 4202

## spring data jpa 表不关联多表查询

• qq_27153901
• 2017年08月07日 18:59
• 925

## JDBC MySQL 多表关联查询查询

public static void main(String[] args) throws Exception{ Class.forName("com.mysql.jdbc.Drive...
• u010445297
• 2017年03月12日 23:13
• 387

## 转载 spring-data-jpa 介绍 复杂查询，包括多表关联，分页，排序等

• mendeliangyang
• 2016年08月30日 10:04
• 25452

举报原因： 您举报文章：数据库---多表查询、关联查询、不同情况下数据库表设计 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)