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_I
D = 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 A
ND  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_COURS
E_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)