Mysql多表查询

一、一对多，多对一

CREATE TABLE COUNTRY(    COUNTRY_ID INT(10) NOT NULL AUTO_INCREMENT,    COUNTRY_NAME VARCHAR(32) NOT NULL,    PRIMARY KEY(COUNTRY_ID));INSERT INTO COUNTRY(COUNTRY_NAME) VALUES("中国"),("美国");CREATE TABLE AREA(    AREA_ID INT(10) NOT NULL AUTO_INCREMENT,    AREA_NAME VARCHAR(32) NOT NULL,    COUNTRY_ID INT(10),    PRIMARY KEY(AREA_ID));INSERT INTO AREA(AREA_NAME,COUNTRY_ID) VALUES("湖南",1),("四川",1),("NEW YARK",2),("LOS SAN",2);

        查询：

mysql> SELECT * FROM COUNTRY a,AREA b WHERE a.COUNTRY_ID = b.COUNTRY_ID;+------------+--------------+---------+-----------+------------+| COUNTRY_ID | COUNTRY_NAME | AREA_ID | AREA_NAME | COUNTRY_ID |+------------+--------------+---------+-----------+------------+|          1 | 中国         |       1 | 湖南      |          1 ||          1 | 中国         |       2 | 四川      |          1 ||          2 | 美国         |       3 | NEW YARK  |          2 ||          2 | 美国         |       4 | LOS SAN   |          2 |+------------+--------------+---------+-----------+------------+4 rows in set (0.00 sec)mysql> SELECT a.COUNTRY_NAME,b.AREA_NAME FROM COUNTRY a,AREA b WHERE a.COUNTRY_ID = b.COUNTRY_ID;+--------------+-----------+| COUNTRY_NAME | AREA_NAME |+--------------+-----------+| 中国         | 湖南      || 中国         | 四川      || 美国         | NEW YARK  || 美国         | LOS SAN   |+--------------+-----------+4 rows in set (0.00 sec)

二、自关联

CREATE TABLE DEPT2(    DEPT_ID INT(10) NOT NULL AUTO_INCREMENT,    DEPT_NAME VARCHAR(32) NOT NULL,    PARENT_DEPT_ID INT(10),    PRIMARY KEY(DEPT_ID));INSERT INTO DEPT2(DEPT_NAME,PARENT_DEPT_ID)VALUES("总裁办公室",null);INSERT INTO DEPT2(DEPT_NAME,PARENT_DEPT_ID)VALUES("质量",1);INSERT INTO DEPT2(DEPT_NAME,PARENT_DEPT_ID)VALUES("技术",1);INSERT INTO DEPT2(DEPT_NAME,PARENT_DEPT_ID)VALUES("IQC",2);INSERT INTO DEPT2(DEPT_NAME,PARENT_DEPT_ID)VALUES("OQC",2);INSERT INTO DEPT2(DEPT_NAME,PARENT_DEPT_ID)VALUES("前端",3);INSERT INTO DEPT2(DEPT_NAME,PARENT_DEPT_ID)VALUES("后端",3);

        查询

mysql> SELECT a.DEPT_NAME,b.DEPT_NAME FROM DEPT2 a,DEPT2 b WHERE a.DEPT_ID = 2 AND  b.PARENT_DEPT_ID = a.DEPT_ID;+-----------+-----------+| DEPT_NAME | DEPT_NAME |+-----------+-----------+| 质量      | IQC       || 质量      | OQC       |+-----------+-----------+2 rows in set (0.00 sec)

三、多对多

CREATE TABLE STUDENT(    STUDENT_ID INT(10) NOT NULL AUTO_INCREMENT,    STUDENT_NAME VARCHAR(32) NOT NULL,    PRIMARY KEY (STUDENT_ID));INSERT INTO STUDENT(STUDENT_NAME)VALUES("牧牛");INSERT INTO STUDENT(STUDENT_NAME)VALUES("遛马");CREATE TABLE COURSE(    COURSE_ID INT(10) NOT NULL AUTO_INCREMENT,    COURSE_NAME VARCHAR(32) NOT NULL,    PRIMARY KEY(COURSE_ID));INSERT INTO COURSE(COURSE_NAME) VALUES("JAVA"),("BIGDATA"),("PYTHON");CREATE TABLE STUDENT_COURSE_MAPPING(    STUDENT_ID INT(10) NOT NULL,    COURSE_ID INT(10) NOT NULL);INSERT INTO STUDENT_COURSE_MAPPING(STUDENT_ID,COURSE_ID) VALUES(1,1),(1,2),(2,2),(2,3);

        查询：

mysql> SELECT A.STUDENT_NAME,B.COURSE_NAME FROM STUDENT A,COURSE B,STUDENT_COURSE_MAPPING C WHERE A.STUDENT_ID = C.STUDENT_ID AND B.COURSE_ID = C.COURSE_ID;+--------------+-------------+| STUDENT_NAME | COURSE_NAME |+--------------+-------------+| 牧牛         | JAVA        || 牧牛         | BIGDATA     || 遛马         | BIGDATA     || 遛马         | PYTHON      |+--------------+-------------+4 rows in set (0.01 sec)