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

332人阅读 评论(0)

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
);

0
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：42621次
• 积分：3094
• 等级：
• 排名：第11717名
• 原创：275篇
• 转载：2篇
• 译文：0篇
• 评论：20条
阅读排行
评论排行
最新评论