数据库表关系(二)

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

数据库表关系–多对多

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

法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%';