mysql支持多表多*
发布网友
发布时间：2022-04-26 09:23
共2个回答
懂视网
时间：2022-05-02 18:32
(
id INT(4) NOT NULL PRIMARY KEY,
name varchar(36)
);
CREATE TABLE student
(
sid INT(4) NOT NULL PRIMARY KEY,
sname VARCHAR(36),
gid INT(4) NOT NULL
);
1、外键
定义：外键是指引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一性约束，外键用于建立和加强两个表数据之间的连接。在已经建立的grade表和student表中，student表中的gid就是grade表中的id，那么gid就可以作为student表的外键。其中，被引用的表grade就是主表，引用外键的表，即student表就是从表，两个表示主从关系。
1.1 为表添加外键约束
语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY（外键字段名） REFERENCES 外表表名（主键字段名）；
举例：为student表添加外键约束
命令：ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY (gid) REFERENCES grade(id);
注意：定义外键名时不能加引号。
使用SHOW CREATE ABLE student命令查看student表：
结果：
说明名为“FK_ID”的外键已经成功添加。
注意：如果未出现此结果，需要先将 grade 表和 student 的 engine 改为 InnoDB ，命令为：ALTER TABLE grade ENGINE=InnoDB;ALTER TABLE student ENGINE=InnoDB;
1.2 删除外键约束
语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名；
举例：删除student表中的外键约束
命令：ALTER TABLE student DROP FOREIGN KEY FK_ID;
执行删除外键命令之后再使用 SHOW CREATE TABLE student;查看：
出现此结果说明外键已被成功删除。
2、操作关联表
2.1 关联关系
（1）多对一
数据表中最常见的一种关系，比如学生与班级的关系，一个班级可以有多个学生，但是一个学生不能属于多个班级。在多对一的关系中，应该将外键建在多的一方。
（2）多对多
比如学生与课程的关系，一个学生可以选择多门课程，一门课程也供多个学生选择
（2）一对一
比如一个人只有一张身份证，而一张身份证也只对应一个人。
2.2 添加数据
在表grade和表student中添加约束来建立两个表的关联关系：
ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY(gid) REFERENCES grade (id);
先为主表grade添加数据：
INSERT INTO grade(id,name) VALUES (1,‘软件一班‘),(2,‘软件二班‘);
此处若出现“1366错误”，可先执行如下命令：
ALTER TABLE grade CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci ;
查询插入数据后的grade表：
同理再在student表中插入数据，而且因为grade中添加的主键id只有1和2，所以student表中的gid字段值只能为1或者2，如下：
INSERT INTO student (sid,sname,gid) VALUES (1,‘王红‘,1),(2,‘李强‘,1),(3,‘赵四‘,2),(4,‘郝娟‘,2);
查询插入数据后的student表：
上述命令执行完之后，两个表之间就具有了关联性，假如要查询软件一班有哪些学生，就要先查询软件一班的 id ，再根据这个 id 在student表中查询有哪些学生。
步骤一：查出grade表中软件一班的 id ：
命令：SELECT id FROM grade WHERE name=‘软件一班‘;
结果：
步骤二：根据 id=1 ，在student表中查出对应的学生。
命令：SELECT name FROM student WHERE gid=1;
结果：
2.3 删除数据
因为grade表和student表具有关联关系，，而参照列的被参照值是不能被删除的，所以若想删除grade表中的软件一班，必须先删除student表中软件一班对应的所有学生。
步骤1：删除软件一班所有学生
命令：DELETE FROM student WHERE sname=‘王红‘ OR sname=‘李强‘;
或者 DELETE FROM student WHERE gid=1;
查询：SELECT * FROM student WHERE gid=1;
结果：
说明软件一班的学生已经全部删除。
步骤二：在grade表中将软件一班删除
命令：DELETE FROM grade WHERE id=1;
查询：SELECT * FROM grade;
结果：
可见软件一班已被成功删除。
现在我们来看看直接删除软件二班会出现什么结果：
命令：DELETE FROM grade WHERE id=2;
结果：
由此可以得出结论：在两个具有关联关系的表中删除数据时，一定要先删除从表中的数据，再删除主表中的数据。
3、连接查询
在进行下面的操作之前，先在test数据库中创建两个表：department表和employee表。
USE test;
CREATE TABLE department
(
did INT(4) NOT NULL PRIMARY KEY,
dname VARCHAR(36)
);
CREATE TABLE employee
(
id INT(4) NOT NULL PRIMARY KEY,
name VARCHAR(36),
age INT(2),
did INT(4) NOT NULL
);
再向两个表插入数据：
INSERT INTO department(did,dname)
VALUES (1,‘网络部‘),(2,‘媒体部‘),(3,‘研发部‘),(5,‘人事部‘);
INSERT INTO employee(id,name,age,did)
VALUES (1,‘王红‘,20,1),(2,‘李强‘,22,1),(3,‘赵四‘,20,2),(4,‘郝娟‘,20,4);
3.1 交叉连接
交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔集，也即返回第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数，例如，department表中有四个部门，employee表中有四个员工，那么交叉连接的结果就有4 * 4 = 16 条数据。
交叉连接的语法：SELECT * FROM 表1 CROSS JOIN 表2；
举例：使用交叉连接查询department表和employee表中的所有数据
命令：SELECT * FROM department CROSS JOIN employee;
结果：
3.2 内连接
内连接（Inner Join）又称简单连接或自然连接，是一种常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较，并列出与连接条件匹配的数据行，组成新的记录。
语法：SELECT 查询字段 FROM 表1 [ INNER ] JOIN 表2 ON 表1.关系字段=表2.关系字段
其中 INNER JOIN 用于连接两个表，ON 来指定连接条件
举例：在department表和employee表之间使用内连接查询。
命令：SELECT employee.name,department.dname FROM department JOIN employee ON department.did=employee.did;
结果：
从结果可以看出，只有department.did 与employee.did 相等的员工才会被显示。
此处还可以使用WHERE 语句来实现同样的功能：
SELECT employee.name,department.dname FROM department,employee WHERE department.did=employee.did;
3.3 自连接
如果在一个连接查询中涉及的两个表其实是同一个表，这种查询称为自连接查询，例如要查询王红所在的部门有多少个人，就可以用自连接查询。
命令：SELECT p1.* FROM employee AS p1 JOIN employee AS p2 ON p1.did=p2.did WHERE p2.name=‘王红‘;
结果：
从结果看来，王红所在的部门有两个员工，分别是王红和李强。
3.4 外连接
外连接分为左连接和右连接，当返回的查询结果不仅需要包含符合条件的数据，还需要包含其中一个表或者两个表的所有数据的时候，需要用到外连接查询。
语法：SELECT 所查字段 FROM 表1 LEFT | RIGHT [ OUTER ] JOIN 表2
ON 表1.关系字段=表2.关系字段
WHERE 条件
（1）LEFT JOIN ——左连接：返回包括左表中的所有记录和右表中符合条件的记录。
举例：SELECT department.did,department.dname,employee.name FROM department LEFT JOIN employee ON department.did=employee.did;
结果：
在此结果中，department中的记录全部显示，而employee中只显示了符合条件的数据（一共四条记录，只显示了符合条件的其中3条）,因为“人事部”没有人，所以相应的字段显示为NULL。
（2）RIGHT JOIN ——右连接：与左连接相反，返回包括右表中的所有记录和左表中符合条件的记录。
举例：SELECT department.did,department.dname,employee.name FROM department RIGHT JOIN employee ON department.did=employee.did;
结果：
在此结果中，employee中的记录全部显示，而department中只显示了符合条件的数据,因为“郝娟”没有对应部门，所以相应的字段显示为NULL。
3.5 复合条件连接查询
复合条件连接查询指在连接查询时，通过添加限制条件来过滤结果。
举例：在department表和employee表中使用内连接查询，并将结果按照年龄降序排列
命令：SELECT employee.name,employee.age,department.dname FROM department JOIN employee ON department.did=employee.did ORDER BY age DESC;
结果：
4、子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在执行时，首先执行子查询中的语句，然后将返回的结果作为外层查询的过滤条件。
4.1 带 IN 关键字的子查询
举例1：查询年龄为20岁的员工的部门
命令：SELECT * FROM department WHERE id IN (SELECT did FROM employee WHERE age=20);
结果：
举例2：查询不存在年龄为20岁的员工的部门
命令：SELECT * FROM department WHERE did NOT IN (SELECT did WHERE age=20);
结果：
4.2 带 EXISTS 关键字的子查询
EXISTS 关键字后面的参数可以是任意一个子查询，这个子查询不产生任何数据，只返回 TRUE 或 FALSE，当返回 TRUE 时，外层查询才会执行。
举例：查询employee表中是否存在年龄大于21岁的员工，若存在则查询department表中所有记录。
命令：SELECT * FROM department WHERE EXISTS (SELECT did FROM employee WHERE age >21 );
结果：
4.3 带 ANY 关键字的子查询
ANY 关键字表示只要满足内层子查询中的任意一个条件，就返回一个结果作为外层查询条件。
举例：使用带ANY 关键字的查询，查询满足条件的部门。
命令：SELECT * FROM department WHERE did >ANY (SELECT did FROM employee);
结果：
在此命令中，子查询会先将employee表中所有did查询出来，分别是1,1,2,4，然后将 department 中的 did 的值与之比较，只要大于employee.did中的任意一个值，就是符合查询条件的结果。由于employee.did的最小值为1，所以department中只要大于1的did都满足条件，即2,3,5。
4.4 带 ALL 关键字的子查询
ALL关键字类似于ANY ，只是ALL关键字的子查询返回的结果需要同时满足所有内查询条件。
举例：使用带 ALL 关键字的子查询，查询满足条件的部门。
命令：SELECT * FROM department WHERE did > ALL (SELECT did FROM employee);
结果：
在此命令中，子查询会先将employee表中所有did查询出来，分别是1,1,2,4，然后将 department 中的 did 的值与之比较，只有大于employee.did中的所有值，才是符合查询条件的结果。由于employee.did的最大值为4，所以department中只有大于4的did才满足条件，即5。
5.5 带 比较运算符的子查询
举例：使用带比较运算符的子查询，查询赵四属于哪个部门
命令：SELECT did,name FROM department WHERE did = (SELECT did FROM employee WHERE name=‘赵四‘);
结果：
MySQL之多表
标签：命令执行 人事 连接 database 主键约束 image 运算符 base word
热心网友
时间：2022-05-02 15:40
A,B两个表数据规模十几万，数据规模都不大，单机MySQL够用了，在单机的基础上要关联两表的数据，先说一个极端情况，A,B两个表都没有索引，并且关联是笛卡尔积，那关联结果会爆炸式增长，可能到亿级别，这个时候网络IO成了瓶颈，这个时候两次十万行结果集的拉去可能远小于1次亿级别的结果集的拉取，那么将关联合并拉到service层做更快。
但实际业务中一般不会有这么蠢的行为，一般关联会有连接条件，并且连接条件上会有索引，一般是有一个结果集比较小，拿到这个结果集去另一张表去关联出其它信息，如果放到service层去做，最快的方式是，先查A表，得到一个小的结果集，一次rpc，再根据结果集，拼凑出B表的查询条件，去B表查到一个结果集，再一次rpc，再把结果集拉回service层，再一次rpc，然后service层做合并，3次rpc，如果用数据库的join，关联结果拉回来，一次rpc，帮你省了两次rpc，当然数据库上做关联更快，对应到数据库就是一次blk nested loop join，这是业务常用情况。
但是确实大多数业务都会考虑把这种合并操作放到service层，一般是有以下几方面考虑：
第一：单机数据库计算资源很贵，数据库同时要服务写和读，都需要消耗CPU，为了能让数据库的吞吐变得更高，而业务又不在乎那几百微妙到毫秒级的延时差距，业务会把更多计算放到service层做，毕竟计算资源很好水平扩展，数据库很难啊，所以大多数业务会把纯计算操作放到service层做，而将数据库当成一种带事务能力的kv系统来使用，这是一种重业务，轻DB的架构思路
第二：很多复杂的业务可能会由于发展的历史原因，一般不会只用一种数据库，一般会在多个数据库上加一层中间件，多个数据库之间就没办法join了，自然业务会抽象出一个service层，降低对数据库的耦合。
第三：对于一些大型公司由于数据规模庞大，不得不对数据库进行分库分表，对于分库分表的应用，使用join也受到了很多*，除非业务能够很好的根据sharding key明确要join的两个表在同一个物理库中。而中间件一般对跨库join都支持不好。
举一个很常见的业务例子，在分库分表中，要同步更新两个表，这两个表位于不同的物理库中，为了保证数据一致性，一种做法是通过分布式事务中间件将两个更新操作放到一个事务中，但这样的操作一般要加全局锁，性能很捉急，而有些业务能够容忍短暂的数据不一致，怎么做？让它们分别更新呗，但是会存在数据写失败的问题，那就起个定时任务，扫描下A表有没有失败的行，然后看看B表是不是也没写成功，然后对这两条关联记录做订正，这个时候同样没法用join去实现，只能将数据拉到service层应用自己来合并了。。。
