如何通过练习精通MySQL数据库关联操作？

MySQL数据库的关联练习涉及多个表之间的数据查询和整合，以下是详细的步骤和小单元表格，帮助你理解如何进行多表关联查询：

创建数据库和数据表

1、创建数据库：

   CREATE DATABASE test;
   USE test;

2、创建DEPT表（部门表）：

   CREATE TABLE DEPT (
       DEPTNO INT(2) PRIMARY KEY,
       DNAME VARCHAR(14),
       LOC VARCHAR(13)
   );
   INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
   INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
   INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
   INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

3、创建EMP表（员工表）：

   CREATE TABLE EMP (
       EMPNO INT(4) PRIMARY KEY,
       ENAME VARCHAR(10),
       JOB VARCHAR(9),
       MGR INT(4),
       HIREDATE DATE,
       SAL DECIMAL(7, 2),
       COMM DECIMAL(7, 2),
       DEPTNO INT(2),
       FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
   );
   INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '19801217', 800, NULL, 20);
   INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981220', 1600, 300, 30);
   INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981222', 1250, 500, 30);
   INSERT INTO EMP VALUES (7566, 'MARTIN', 'SALESMAN', 7698, '1981228', 1250, 1400, 30);
   INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981315', 1500, 1400, 30);
   INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981115', 2850, NULL, 30);
   INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981115', 2450, NULL, 10);
   INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982129', 3000, NULL, 20);
   INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '19811117', 5000, NULL, 10);
   INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '198198', 1500, 0, 30);
   INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983112', 1100, NULL, 20);
   INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '1981123', 950, NULL, 30);
   INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '1981123', 3000, NULL, 20);
   INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '1982123', 1300, NULL, 10);

单表查询练习

多表关联查询练习

| `SELECT e.ENAME, e.JOB, d.DNAME, d.LOC FROM EMP e, DEPT d WHERE e.DEPTno = d.DEPTno AND e