删除mysql多个实例_mysql多表删除实例
mysql多表删除
删除某学生基本信息的同时，删除该学生的所有成绩
mysql> select * from stu_inf;
+----+----------+--------+-------+-------+--------+
| id | stu_xh | stu_xm | xb_id | xy_id | stu_bj |
+----+----------+--------+-------+-------+--------+
| 1 | 07150101 | 小明 | 1 | 1 | 1504 |
| 2 | 07150240 | 谢育政 | 1 | 2 | 1502 |
| 3 | 12341231 | 猴子 | 1 | 2 | 1515 |
| 4 | 01591261 | 大大 | 1 | 3 | 1515 |
| 5 | 07160202 | 小红 | 2 | 4 | 1602 |
| 6 | 07150225 | 黄河 | 1 | 3 | 1315 |
| 7 | 01050001 | 华容 | 1 | 7 | 1612 |
| 8 | 02050002 | 黄月英 | 2 | 8 | 1608 |
| 9 | 01510003 | 欢欢 | 2 | 9 | 1601 |
| 10 | 05261005 | 大康 | 1 | 10 | 1304 |
| 13 | 0001 | xxx | 1 | 2 | 1555 |
+----+----------+--------+-------+-------+--------+
11 rows in set (0.02 sec)
mysql> select * from cj_inf;
+-------+----------+---------+----------+
| cj_id | cj_stuid | cj_kmid | cj_score |
+-------+----------+---------+----------+
| 1 | 3 | 1 | 87 |
| 2 | 1 | 2 | 65 |
| 3 | 4 | 1 | 77 |
| 5 | 3 | 9 | 76 |
| 7 | 1 | 1 | 90 |
| 15 | 13 | 9 | 90 |
| 16 | 13 | 8 | 67 |
| 13 | 13 | 1 | 88 |
+-------+----------+---------+----------+
8 rows in set (0.02 sec)
删除语句：
mysql> DELETE stu_inf,cj_inf from stu_inf LEFT JOIN cj_inf ON stu_inf.id=cj_inf.cj_stuid WHERE stu_inf.id=13;
Query OK, 4 rows affected (0.00 sec)
执行语句后：
mysql> select * from stu_inf;
+----+----------+--------+-------+-------+--------+
| id | stu_xh | stu_xm | xb_id | xy_id | stu_bj |
+----+----------+--------+-------+-------+--------+
| 1 | 07150101 | 小明 | 1 | 1 | 1504 |
| 2 | 07150240 | 谢育政 | 1 | 2 | 1502 |
| 3 | 12341231 | 猴子 | 1 | 2 | 1515 |
| 4 | 01591261 | 大大 | 1 | 3 | 1515 |
| 5 | 07160202 | 小红 | 2 | 4 | 1602 |
| 6 | 07150225 | 黄河 | 1 | 3 | 1315 |
| 7 | 01050001 | 华容 | 1 | 7 | 1612 |
| 8 | 02050002 | 黄月英 | 2 | 8 | 1608 |
| 9 | 01510003 | 欢欢 | 2 | 9 | 1601 |
| 10 | 05261005 | 大康 | 1 | 10 | 1304 |
+----+----------+--------+-------+-------+--------+
10 rows in set (0.00 sec)
mysql> select * from cj_inf;
+-------+----------+---------+----------+
| cj_id | cj_stuid | cj_kmid | cj_score |
+-------+----------+---------+----------+
| 1 | 3 | 1 | 87 |
| 2 | 1 | 2 | 65 |
| 3 | 4 | 1 | 77 |
| 5 | 3 | 9 | 76 |
| 7 | 1 | 1 | 90 |
+-------+----------+---------+----------+
5 rows in set (0.02 sec)