大概内容:数据库表关系—多对多
数据库表关系–多对多
三、多对多的关系
△需求引例: 学生–课程 之间的关系
法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%';