MySQL---表关系、关联与存储过程

程序员文章站 2024-02-19 22:24:52

...

一对一关系

方式一：两张物理表，通过外键关联，外键被UNIQUE修饰

CREATE TABLE wm(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10) NOT NULL,
    sex CHAR(1)
);

CREATE TABLE mm(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10) NOT NULL,
    sex CHAR(1),
    wid INT UNIQUE,
    CONSTRAINT mm_fk FOREIGN KEY(wid) REFERENCES wm(id)
);

查询夫妻关系

SELECT mm.NAME 丈夫,wm.name 妻子 FROM mm,wm WHERE mm.wid = wm.id;

MySQL---表关系、关联与存储过程

方式二：一张物理表+两张视图

CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10) NOT NULL,
    sex CHAR(1),
    husband INT UNIQUE ,
    wife INT UNIQUE,
    CONSTRAINT person_husband_fk FOREIGN KEY(husband) REFERENCES person(id),
    CONSTRAINT person_wife_fk FOREIGN KEY(wife) REFERENCES person(id)
);

CREATE VIEW wife AS SELECT id,NAME,sex,husband FROM person WHERE sex='0';
CREATE VIEW husband AS SELECT id,NAME,sex,wife FROM person WHERE sex='1';

通过视图查询夫妻关系

SELECT * FROM husband,wife WHERE wife.id=husband.wife;

MySQL---表关系、关联与存储过程

删除外键

ALTER TABLE person DROP FOREIGN KEY person_fk;

添加外键

ALTER TABLE person ADD CONSTRAINT person_wife_fk FOREIGN KEY(wife) REFERENCES person(id);

查看表元数据

SHOW CREATE TABLE person;

关联

MySQL---表关系、关联与存储过程

INNER JOIN 以关系为主

查询夫妻关系

SELECT mm.NAME 丈夫, wm.name 妻子 FROM mm INNER JOIN wm ON mm.wid=wm.id;

MySQL---表关系、关联与存储过程

LEFT JOIN 以左表为主（左表的信息是全的）

查询男光棍

SELECT mm.NAME 丈夫, wm.name 妻子 FROM mm LEFT JOIN wm ON mm.wid=wm.id WHERE wm.id IS NULL;

RIGHT JOIN 以右表为主（右表的信息是全的）

查询女光棍

SELECT mm.NAME 丈夫, wm.name 妻子 FROM mm RIGHT JOIN wm ON mm.wid=wm.id WHERE mm.id IS NULL;

一对多

数据库设计分析
案例：一个人可以拥有多辆汽车，要求查询出某人所拥有的所有汽车。

CREATE TABLE person2(
   id varchar(32) primary key,
   NAME VARCHAR(30),
   sex CHAR(1),
   age INT
);
INSERT INTO person2 VALUES('P1001','小花','0',25);
INSERT INTO person2 VALUES('P1002','张三','1',22);
INSERT INTO person2 VALUES('P1003','Jack','1',24);
INSERT INTO person2 VALUES('P1004','Rose','0',25);

CREATE TABLE car(
   id varchar(32) primary key,
   NAME VARCHAR(30),
   price numeric(10,2),
   pid varchar(32),  
   /*为字段pid定义一个外键约束(来自person2表的字段id)*/
   constraint car_fk foreign key(pid) references person2(id)
);
INSERT INTO car VALUES('C001','BMW',80.5,'P1001');
INSERT INTO car VALUES('C002','Benz',100,'P1001');
INSERT INTO car VALUES('C003','BMW',120.05,'P1001');
INSERT INTO car VALUES('C004','Benz',88.5,'P1002');
INSERT INTO car VALUES('C005','QQ',8.5,'P1002');
INSERT INTO car VALUES('C006','BIKE',0.5,'P1003');

哪些人有哪些车

/* 哪些人有哪些车   */
/* 新技术： 采用关联查询一张表的方式 table1 inner join table2 on ... */
SELECT person2.NAME 人名, car.NAME 车名, car.price 价格 
FROM person2 INNER JOIN car ON person2.id=car.pid;

/* 老技术 采用查询两张表的方式 */
SELECT person2.NAME 人名, car.NAME 车名, car.price 价格 
FROM person2,car 
WHERE person2.id=car.pid;

MySQL---表关系、关联与存储过程

Mike的车辆信息

SELECT person2.id ID号, person2.NAME 人名, car.NAME 车名, car.price 价格
FROM person2 LEFT JOIN car ON person2.id = car.pid
WHERE person2.NAME='Mike';

MySQL---表关系、关联与存储过程

哪些人没有车

SELECT person2.id ID号, person2.NAME 人名, car.NAME 车名, car.price 价格
FROM person2 LEFT JOIN car ON person2.id = car.pid
WHERE car.id IS NULL;

MySQL---表关系、关联与存储过程

哪些人至少有两辆车

SELECT person2.id ID号, person2.NAME 人名, car.NAME 车名, car.price 价格
FROM car RIGHT JOIN person2 ON car.pid = person2.id
WHERE person2.id IN(
	SELECT pid FROM car GROUP BY pid HAVING COUNT(1)>1 
);

MySQL---表关系、关联与存储过程

多对多

数据库设计分析
案例：一个人可以选择多门课程，一门课程又可以被很多人选择。

CREATE TABLE student(
   id VARCHAR(32) PRIMARY KEY,
   NAME VARCHAR(30),
   age INT
);
CREATE TABLE course(
   id VARCHAR(32) PRIMARY KEY,
   NAME VARCHAR(30)
);
CREATE TABLE sc(
   studid VARCHAR(32),
   courid VARCHAR(32)
);
/*
 外键       外键  ---->后
   |         |
   |----|----|  
        |
     联合主键     ---->先

*/
/* 注意 建立联合主键时，需要先创建 联合主键，再创建外键关联*/
/*添加主键约束 先*/
ALTER TABLE sc ADD CONSTRAINT sc_pk PRIMARY KEY(studid,courid);
/*添加外键约束 后*/
ALTER TABLE sc ADD CONSTRAINT sc_s_fk FOREIGN KEY(studid) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT sc_c_fk FOREIGN KEY(courid) REFERENCES course(id);

/*删除主键约束*/
ALTER TABLE sc DROP PRIMARY KEY;
/*删除外键约束*/
ALTER TABLE sc DROP FOREIGN KEY sc_s_fk;
ALTER TABLE sc DROP FOREIGN KEY sc_c_fk;

/*查看表元数据*/
SHOW CREATE TABLE sc;

插入数据

INSERT INTO student VALUES('P001','小花',25);
INSERT INTO student VALUES('P002','Jack',23);
INSERT INTO student VALUES('P003','Tom',24);
INSERT INTO student VALUES('P004','张三',24);
INSERT INTO student VALUES('P005','赵子龙',26);

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

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

查询哪些人选了哪些课

SELECT student.NAME 学生,course.NAME 课程
FROM student INNER JOIN sc ON sc.studid = student.id
			 INNER JOIN	course ON sc.courid = course.id;

MySQL---表关系、关联与存储过程

查询哪些人没有选课

SELECT student.id, student.NAME 学生,course.NAME 课程
FROM student LEFT JOIN sc ON sc.studid = student.id
			 LEFT JOIN	course ON sc.courid = course.id
WHERE course.id IS NULL;

MySQL---表关系、关联与存储过程

查询哪些课程没人选

SELECT student.NAME 学生,course.NAME 课程
FROM student RIGHT JOIN sc ON sc.studid = student.id
			 RIGHT JOIN	course ON sc.courid = course.id
WHERE student.id IS NULL;

MySQL---表关系、关联与存储过程

设置区分大小写

默认，两个查询效果是一样的

SELECT * FROM student WHERE NAME = 'jack'; /* 默认不区分大小写 */
SELECT * FROM student WHERE NAME = 'JACK'; /* 默认不区分大小写 */

MySQL---表关系、关联与存储过程

设置查询时区分大小写，关键字 binary

SELECT * FROM student WHERE BINARY NAME = 'jack'; 
SELECT * FROM student WHERE BINARY NAME = 'Jack';

MySQL---表关系、关联与存储过程

建表时设置区分大小写

/* 两个字段都设置数据唯一性 */
CREATE TABLE word(
	letters1 VARCHAR(50) UNIQUE,
	letters2 VARCHAR(50) BINARY UNIQUE
);

插入数据观察结果

INSERT INTO word VALUES('a','a');
INSERT INTO word VALUES('A','A');

MySQL---表关系、关联与存储过程

/* 删除表 */
DROP TABLE word;
/* 清空表数据 */
TRUNCATE TABLE word;

存储过程

存储过程是保存在数据库上的一段可执行代码。

定义：

create procedure 过程名(参数)
begin
  多条sql语句
end

调用：

call 过程名(实参)

小细节：

要把默认的语句结束“;”号改成其它如“$$”，这样存储过程中定义的分号就不被看成是语句结束(否则会直接被提交)。最后再把“;”号还原成默认的结束符。

实例1--无参的存储过程

DELIMITER $$  /* 修改结束符 为 $$ */
CREATE PROCEDURE p1()
BEGIN
	INSERT INTO student VALUES('p101','Jack',22);
	SELECT * FROM student;
END $$ /* 因为修改了结束符 所以这里才是结束 */
DELIMITER ;  /*  把结束符还原为 ; */

CALL p1();

MySQL---表关系、关联与存储过程

实例2--带参的存储过程

DELIMITER $$
CREATE PROCEDURE p2( IN id VARCHAR(32), IN NAME VARCHAR(30),IN age INT )
BEGIN
	INSERT INTO student VALUES(id,NAME,age);
	SELECT * FROM student;
END $$
DELIMITER ;

CALL p2('p103','Tom',23);

MySQL---表关系、关联与存储过程

实例3--带参带返回值的存储过程

DELIMITER $$
CREATE PROCEDURE p3( IN id VARCHAR(32), IN NAME VARCHAR(30),IN age INT, OUT len INT )
BEGIN
	INSERT INTO student VALUES(id,NAME,age);
	SELECT COUNT(*) INTO len FROM student;
END $$
DELIMITER ;

/*
系统变量名称：@@变量名
用户变量名称：@变量名
*/
CALL p3('p104','Tom',23,@len); /*调用且用len接收结果*/
SELECT @len;  /*显示用户变量len*/

MySQL---表关系、关联与存储过程

MySQL---表关系、关联与存储过程

一对一关系

方式一：两张物理表，通过外键关联，外键被UNIQUE修饰

方式二：一张物理表+两张视图

关联

INNER JOIN 以关系为主

LEFT JOIN 以左表为主（左表的信息是全的）

RIGHT JOIN 以右表为主（右表的信息是全的）

一对多

多对多

设置区分大小写

存储过程

MySQL---表关系、关联与存储过程

存储过程传递参数与表中字段属性相同导致错误的问题_MySQL

存储过程传递参数与表中字段属性相同导致错误的问题_MySQL

PostgreSQL数据库中，使用存储过程实现自关联表的树形递归遍历。

PostgreSQL数据库中，使用存储过程实现自关联表的树形递归遍历。

十五 cascade 级联操作与inverse表是否放弃维护关联关系