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

原创 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
);
版权声明:本文为博主原创文章,大家一起学习进步。

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

本篇博客知识点 1.三种简单的关联方式:左关联、右关联、内联 2.实例演示多表查询和关联查询 3.1对1数据库表设计、1对多数据库表设计、多对多数据库表设计三种关联方式:左关联、右关联、内...

关联数据库字典表的多表查询

学习数据库查询的时候对多表连接查询的有些概念还比较模糊。而连接查询是在数据库查询操作的时候肯定要用到的。对于此概念  我用通俗一些的语言和例子来进行讲解。这个例子是我讲课的时候经常采用的例子。  ...

Mysql基础知识(创建库、表、对表的增删改查、多表设计、多表查询、数据库的备份和恢复)

Mysql基础知识(创建库、表、对表的增删改查、多表设计、多表查询、数据库的备份和恢复) 操作表记录(也就是我们通常所说的对数据库中表的增删改查---CRUD)...

JAVA-20-数据约束、数据库设计三大范式、多表查询、存储过程、触发器、数据库权限

一、数据约束 给表添加数据约束,从而约束用户操作表数据的行为。 1.1默认值约束(default) 注意: 当前没有插入默认值字段的时候,默认值才会起作用CREATE TABLE test( ...

数据库多表查询

  • 2014年05月27日 13:31
  • 556KB
  • 下载

\Access数据库的多表查询

  • 2011年05月02日 23:13
  • 360KB
  • 下载

在只有MySQL数据库的情况下,如何把SQL Server 数据脚本(.sql文件)导入Mysql的表中。

先看对比。 SQL Server脚本文件的表和数据 MySQL脚本文件的表和数据 通过对比我们会发现,他们的语法不一样。所以要把前者的语法改成后者,接下来就是手动...

C++多表查询(Access数据库)

  • 2012年09月24日 11:13
  • 7.11MB
  • 下载

Delphi数据库多表查询组件

  • 2006年02月23日 09:05
  • 174KB
  • 下载

数据库多表查询之 where & INNER JOIN

在多表查询中,一些SQL开发人员更喜欢使用WHERE来做join,比如:SELECT a.ID, b.Name, b.Date FROM Customers a, Sales b WHERE a.ID...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库---多表查询、关联查询、不同情况下数据库表设计
举报原因:
原因补充:

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