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

原创 2017年07月28日 13:27:14

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

三种关联方式:左关联、右关联、内联

左关联(left join):以左表为主(左表保持完整,将右表中和左表有依赖关系的记录添加进来形成新的表)
右关联(right join):以右表为主(右表保持完整,将左表中和右表有依赖关系的记录添加进来形成新的表)
内联(inner join):将两个表中有联系的记录分别抽取出来形成新的表
示意图
左关联:
这里写图片描述

右关联
这里写图片描述

内联
这里写图片描述

以实际表 不同的车主有多辆车 为例
这里写图片描述

左关联生成的虚拟表如下
这里写图片描述

右关联生成的虚拟表如下
这里写图片描述

内联生成的虚拟表如下
这里写图片描述

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

现在有两个表。 车主信息表和车辆信息表,car里的pid是外键
person表
这里写图片描述
car表
这里写图片描述

需求1:查询 车辆数目大于等于二的车主编号

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 过程名(实参)

必须要注意的一个小细节: 要把默认的语句结束符”;”号改成其它如”$$”,这样存储过程中定义的分号就不被看成是语句结束(否则会直接被提交)。记得最后要改回分号

SQL代码实例
1.无参数的存储过程

delimiter $$
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
  • shuyeshangdemayi
  • 2014年12月17日 10:03
  • 597

数据库 --关联查询(多表查询)

需求:查询员工及其所在部门(显示员工姓名,部门名称) 1.交叉连接查询(笛卡尔积 有些是重复记录 不推荐) select empName ,deptName from employee,dept;...
  • qq_37248648
  • qq_37248648
  • 2017年11月07日 15:17
  • 91

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

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

【Hibernate九】HQL之多表查询(一对多和多对多)

一、一对多 以班级Classes和学生Student为例: 回忆sql语句: //内链接,两种方式效果一样,查询的是两边都有的数据 SELECT c.*,s.* FROM cl...
  • damogu_arthur
  • damogu_arthur
  • 2015年07月17日 11:18
  • 25462

Spring boot jpa 多表关联查询

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

spring data jpa 多表多条件查询

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

多表查询经典练习

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

spring data jpa 表不关联多表查询

直接上代码import com.supergk.core.project.bean.ProjectUser; import org.springframework.data.domain.Page; ...
  • qq_27153901
  • 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
  • u010445297
  • 2017年03月12日 23:13
  • 387

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

本篇进行Spring-data-jpa的介绍,几乎涵盖该框架的所有方面,在日常的开发当中,基本上能满足所有需求。这里不讲解JPA和Spring-data-jpa单独使用,所有的内容都是在和Spring...
  • mendeliangyang
  • mendeliangyang
  • 2016年08月30日 10:04
  • 25452
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库---多表查询、关联查询、不同情况下数据库表设计
举报原因:
原因补充:

(最多只允许输入30个字)