MySQL第四天---多表操作（1对1、1对多、多对多）
※多表操作 (凡是多表，都要⽤到关联技术(把多表合并成⼀个新表): 左关联、右关联、内关联。还有⼀个外(全)关联，MySQL不⽀持，为考虑软件兼容，我们开发⼀般不⽤。)
※表与表之间的关系:1对1，1对多，多对多
⼀、1对1
※第三范式: 1⽅建主表(id为主键字段), 多⽅建外键字段(husband--参考主表的主键id，加unique)
CREATE TABLE man(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
CREATE TABLE woman(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
husband VARCHAR(32) UNIQUE,
CONSTRAINT wm_fk FOREIGN KEY(husband) REFERENCES man(id)
);
注：husband这⾥要加unique约束，不加则是⼀对多关系
加unique才是1对1关系
//DROP TABLE woman;
INSERT INTO man VALUES('1','⼩明');
INSERT INTO man VALUES('2','⼩聪');
INSERT INTO man VALUES('3','⽼王');
INSERT INTO woman VALUES('1','⼩花','2');
INSERT INTO woman VALUES('2','⼩静','1');
INSERT INTO woman VALUES('3','⼩红','1');//Error：违反1对1
INSERT INTO woman VALUES('3','⼩红','10');//Error：违反外键--主表必须存在该外键值
INSERT INTO man VALUES('10','王六');
INSERT INTO woman VALUES('3','⼩⽟','10');//OK
受unique约束，husband不能重复不然
husband是外键，同时受man中的id约束
//◇查询夫妻信息(内联)
SELECT man.name AS 丈夫, woman.name AS 妻⼦ FROM man
INNER JOIN woman ON man.id=woman.husband;
SELECT m.name AS 丈夫, w.name AS 妻⼦ FROM man AS m
INNER JOIN woman AS w ON m.id=w.husband;
⼆、1对多
※第三范式: 1⽅建主表(id为主键字段), 多⽅建外键字段(pid--参考主表的主键id，不加unique) CREATE TABLE person2(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
sex CHAR(1)
);
DROP TABLE car2;
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 person2 VALUES('P01','Jack','1');
INSERT INTO person2 VALUES('P02','Tom','1');
INSERT INTO person2 VALUES('P03','Rose','0');
INSERT INTO car VALUES('C001','BMW',30,'P01');
INSERT INTO car VALUES('C002','BEnZ',40,'P01');
INSERT INTO car VALUES('C003','Audi',40,'P01');mysql语句多表查询
INSERT INTO car VALUES('C004','QQ',5.5,'P02');
//外键字段值可以为“NULL”表⽰该车还未卖出为什么说php世界第一
INSERT INTO car(id,NAME,price) VALUES('C005','ABC',10);
INSERT INTO car(id,NAME,price) VALUES('C006','BCD',10);
//◇查询哪些⼈有哪些车
SELECT *  FROM car INNER JOIN person2 ON car.pid=person2.id;
SELECT person.NAME,car.name FROM person
INNER JOIN car ON person.id=car.pid;
//◇查询Jack有哪些车
SELECT car.NAME,car.price,person2.name  FROM car
INNER JOIN person2 ON car.pid=person2.id where person2.name='Jack';
//◇查询哪些⼈有两辆或两辆以上的车
//失败品:--每组只显⽰第⼀条
SELECT car.NAME,car.price,person2.name  FROM car
INNER JOIN person2 ON car.pid=person2.id GROUP BY person2.id HAVING COUNT(car.pid)>=2;
//过渡版
SELECT car.NAME,car.price,person2.name  FROM car
INNER JOIN person2 ON car.pid=person2.id WHERE person2.id IN('P01','P02');
SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2; //把上⾯写死的pid写成活的
//OK版
SELECT car.NAME,car.price,person2.name  FROM car
INNER JOIN person2 ON car.pid=person2.id WHERE person2.id IN(
SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2
)
;
//演⽰左关联: SELECT * FROM person2 LEFT JOIN car ON car.pid=person2.id;//由全表可知只需条件是car.id或NAME或price或pid为空即可查出谁没有车
//◇查询哪些⼈没有车
SELECT person2.name FROM person2 LEFT JOIN car ON car.pid=person2.id
WHERE car.id IS NULL;
其实右关联跟左关联⼀样，只需要把左关联的表调换⼀下位置便成了右关联的结果，所以只要会了左关联，右关联也是⼀样的。
//◇补⼀个外键的概念（默认是约束): 删除主键信息时，当该主键字段值在外键表中存在时，该记录是不能删除的。---要把外键表是的相关信息删除之后，才能删除。 ---更新同理
DELETE FROM person2 WHERE id='P01';//car表中存在pid='P01'的车，所以主表不能删除'P01'这条记录
三、多对多（ 3个表= 2个实体表 + 1个关系表 )
※第三范式: 两个实体都建成独⽴的主表, 另外再单独建⼀个关系表（采⽤联合主键）
1、分别建议两个实体表(没有外键，但有⾃⼰的主键, 没有冗余信息)
//DROP TABLE stud;unity3d网页游戏
//学⽣表
CREATE TABLE stud(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
//课程表
CREATE TABLE ject(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
2、另外补建⼀个关系表
CREATE TABLE sj(
studid VARCHAR(32) NOT NULL,
jectid VARCHAR(32)
);
/
/注意，要先建联合主键，再添加外键。顺序不能反了。
ALTER TABLE sj ADD CONSTRAINT sj_pk PRIMARY KEY(studid,jectid);
ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studid) REFERENCES stud(id); ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectid) REFERENCES ject(id);
//删除外键约束
//ALTER TABLE sj DROP FOREIGN KEY sj_fk1;
//ALTER TABLE sj DROP FOREIGN KEY sj_fk2;
3、添加⼀些演⽰数据
//实体表1
INSERT INTO stud VALUES('S001','Jack');
INSERT INTO stud VALUES('S002','Rose');企业直播一直数据加载中
INSERT INTO stud VALUES('S003','Tom');
//实体表2
INSERT INTO ject VALUES('J001','Java');
js实现鼠标停留图片放大INSERT INTO ject VALUES('J002','Oracle');
INSERT INTO ject VALUES('J003','XML');
INSERT INTO ject VALUES('J004','JSP');
INSERT INTO ject VALUES('J005','Game');
//关系表
INSERT INTO sj VALUES('S001','J001');
INSERT INTO sj VALUES('S001','J003');
INSERT INTO sj VALUES('S001','J004');
INSERT INTO sj VALUES('S002','J002');
INSERT INTO sj VALUES('S002','J003');
数控go是什么意思INSERT INTO sj VALUES('S002','J004');
//查询哪些⼈选了哪些课
//SQL组织的1992标准，可⽤，但效率不⾼
SELECT stud.name, ject.NAME FROM stud,ject,sj WHERE stud.id=sj.studid AND ject.id=sj.jectid;
//SQL组织的1996标准，效率⾼，推荐使⽤---关联
SELECT stud.name, ject.NAME FROM stud INNER JOIN sj ON stud.id=sj.studid
INNER JOIN ject ON ject.id=sj.jectid;
//查询哪些⼈没选课
SELECT stud.name FROM stud LEFT JOIN sj ON stud.id=sj.studid
LEFT JOIN ject ON ject.id=sj.jectid where ject.id is NULL;
//查询哪些课没⼈选
SELECT ject.name FROM stud RIGHT JOIN sj ON stud.id=sj.studid
RIGHT JOIN ject ON ject.id=sj.jectid where stud.id is NULL;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。