数据库表关系(二)

本文探讨了数据库设计中多对多关系的两种方法,并详细介绍了较好的设计方案:通过创建独立的实体表和联系表来实现学生与课程间的多对多关联。此外,还提供了具体的SQL代码实现。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

大概内容:数据库表关系—多对多

数据库表关系–多对多

三、多对多的关系
△需求引例: 学生–课程 之间的关系

法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 stud1(
    id VARCHAR(32) PRIMARY KEY,
    NAME VARCHAR(30),
    age INT 
    );

//创建课程表

CREATE TABLE project(
    id VARCHAR(32),
    NAME VARCHAR(30)
    );

//创建选课表

CREATE TABLE choice(
    stuid VARCHAR(32),
    subid VARCHAR(30)
    );

//建立联合主键: 必须先添加联合主键再加外加,否则不行!

     alter table choice add constraint pk_choice primary      key(studid,subid);
    alter table choice add constraint fk_stud1 foreign key(studid) references stud1(id);
    alter table choice add constraint fk_project foreign key(subid) references project(id);

//插入数据

INSERT INTO stud1 VALUES('P001','小花',22);
INSERT INTO stud1 VALUES('P002','Jack',23);
INSERT INTO stud1 VALUES('P003','Tom',24);
INSERT INTO stud1 VALUES('P004','张三',25);
INSERT INTO stud1 VALUES('P005','李四',21);

INSERT INTO project VALUES('S001','Java');
INSERT INTO project VALUES('S002','JavaEE');
INSERT INTO project VALUES('S003','XML');
INSERT INTO project VALUES('S004','数据库');
INSERT INTO project VALUES('S005','Oracle');

INSERT INTO choice VALUES('P001','S001');
INSERT INTO choice VALUES('P001','S003');
INSERT INTO choice VALUES('P002','S001');
INSERT INTO choice VALUES('P002','S002');
INSERT INTO choice VALUES('P002','S003');
INSERT INTO choice VALUES('P003','S001');
INSERT INTO choice VALUES('P004','S002');
INSERT INTO choice VALUES('P004','S003');

//查询
//i.查询哪些人选了哪些课
//1.基本方法

    SELECT stud1.name AS 学生姓名,project.name AS 课程名称 FROM stud1,project,choice WHERE stud1.id=choice.stuid AND project.id=choice.subid;

//2.左关联

SELECT stud1.name,project.name FROM stud1
        LEFT JOIN choice ON stud1.id=choice.stuid   
        LEFT JOIN project ON choice.subid=project.id;

//ii.查询哪些人没有选课
//左关联

SELECT stud1.name AS 学生姓名,project.id FROM stud1
        LEFT JOIN choice ON stud1.id=choice.stuid
        LEFT JOIN project ON choice.subid=project.id    
        WHERE project.id IS NULL;

//右关联

SELECT stud1.name AS 学生姓名,project.id FROM project
        RIGHT JOIN choice ON project.id=choice.subid        
        RIGHT JOIN stud1 ON choice.stuid=stud1.id
        WHERE project.id IS NULL;

//3.哪些课程没人选
//左关联

SELECT project.name AS 课程名称,stud1.id FROM project
        LEFT JOIN choice ON project.id=choice.subid
        LEFT JOIN stud1 ON choice.stuid=stud1.id
        WHERE stud1.id IS NULL;

//右关联

SELECT project.name AS 课程名称,stud1.id FROM stud1
        RIGHT JOIN choice ON choice.stuid=stud1.id
        RIGHT JOIN project ON choice.subid=project.id
        WHERE stud1.id IS NULL;

关于存储过程

//小细节:在存储过程之前要把系统的默认语句结束符';'改成其他字符,否则存储过程中的语句遇到';'就会自动结束
         通过DELIMITER $$可以暂时将默认结束符改为'&&',存储过程结束之后再通过--DELIMITER ;--修改回来。

//1.无参数的存储过程

DELIMITER $$ 
CREATE PROCEDURE p1()
BEGIN
	INSERT INTO stud1 VALUES('P007','小王',24);
	SELECT * FROM stud1;
END$$
DELIMITER ;
CALL p1;

//2.有参数的存储过程

DELIMITER $$
CREATE PROCEDURE p2(IN id VARCHAR(32), IN NAME VARCHAR(30), IN age INT)
BEGIN
	INSERT INTO stud1 VALUES(id,NAME,age);
	SELECT * FROM stud1;
END$$
DELIMITER ;
CALL p2('P006','小吴',25);

//3.有返回值的存储过程

DELIMITER $$
CREATE PROCEDURE p3(IN id VARCHAR(32), IN nm VARCHAR(30), IN age INT,OUT num INT)
BEGIN
	INSERT INTO stud1 VALUES(id,NAME,age);
	SELECT * FROM stud1;
	SELECT COUNT(*) INTO num FROM stud1;
END$$
DELIMITER ;
CALL p3('P008','小谢',22,@xx);

//可以查询返回值
SELECT @xx;

//Mysql查询时,不区分大小写,BINARY可以用来指定是否区分大小写

//上面两句的效果一样
SELECT * FROM stud WHERE sname LIKE '%e%';
SELECT * FROM stud WHERE sname LIKE '%E%';
//下面两句可以区别大小写
SELECT * FROM student WHERE stud BINARY LIKE '%e%';
SELECT * FROM student WHERE stud BINARY LIKE '%E%';
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值