mysql 3nf_mysql中的第三范式

※多表操作 (凡是多表，都要用到关联技术(把多表合并成一个新表): 左关联、右关联、内关联。还有一个外(全)关联，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)

);

//DROP TABLE woman;

INSERT INTO man VALUES('1','张三');

INSERT INTO man VALUES('2','李四');

INSERT INTO man VALUES('3','王五');

INSERT INTO woman VALUES('1','小花','1');

INSERT INTO woman VALUES('2','玉芬','3');

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

//◇查询夫妻信息(内联)

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

INSERT INTO car VALUES('C004','QQ',5.5,'P02');

//外键字段值可以为“NULL”

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 car.NAME,car.price,person2.name FROM car

INNER JOIN person2 ON car.pid=person2.id ;

//◇查询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;

//◇查询哪些人没有车

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;

//学生表

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

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

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;

--------------------------------------------------------------------------------------------

//额外补充：

1)在java中如果要对mysql进行操作时要导入mysql的jar包，倒入之后点右键中的bulid path进行导包；

2)外键可以保证两个表数据的一致性