数据库原理学习记录2

数据库原理学习记录2

问题1：You can’t specify target table ‘student’ for update in FROM clause

通过百度翻译：

参考博客：
https://blog.csdn.net/qq_29672495/article/details/72668008

该文提出“将SELECT出的结果再通过中间表SELECT一遍”

模仿尝试：

例1：

delete from student
where 姓名=any(
select student.姓名
from (select student.姓名
from student left join score
on student.学号=score.学号
where score.课号 is null) student
);
Query OK, 8 rows affected (0.01 sec)

例2：

mysql> delete from teacher      //失败
    -> where 工资<(
    -> select avg(工资)
    -> from teacher
    -> );
ERROR 1093 (HY000): You can't specify target table 'teacher' for update in FROM clause
mysql> delete from teacher          //失败
    -> where 工资<any(
    -> select avg(工资)
    -> from teacher
    -> );
ERROR 1093 (HY000): You can't specify target table 'teacher' for update in FROM clause
mysql> delete from teacher           //成功
    -> where 工资<(
    -> select avg(工资)
    -> from (select avg(工资) from teacher)teacher
    -> );
Query OK, 0 rows affected (0.00 sec)

老师提示：

“这个语句是完全正确的，但是在Mysql中会出问题，这是mysql的问题，mysql不准在下一级嵌套中使用同一名称的表。”

“注意我说了关键哦，下一级嵌套，仅仅是下一级嵌套，也就是其它级的嵌套是可以的，你品。”

问题2：Subquery returns more than 1 row

通过百度翻译：

资料查找：
https://zhidao.baidu.com/question/327417776459413245.html

mysql> delete from student
    -> where 姓名=(
    -> select student.姓名
    -> from (select student.姓名
    -> from student left join score
    -> on student.学号=score.学号
    -> where score.课号 is null) student
    -> );
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> delete from student
    -> where 姓名=any(        //此处添加 any
    -> select student.姓名
    -> from (select student.姓名
    -> from student left join score
    -> on student.学号=score.学号
    -> where score.课号 is null) student
    -> );
Query OK, 8 rows affected (0.01 sec)

问题3：Every derived table must have its own alias

通过百度翻译：每个派生表都必须有自己的别名
在解决问题1，进行再select时容易出现
解决方法：取别名

问题4：Cannot delete or update a parent row: a foreign key constraint fails

百度翻译：无法删除或更新父行：外键约束失败
原因：老师故意挖的坑
解决方法：取消外键，迅速删除，接上外键，一气呵成

参考博客：
https://blog.csdn.net/qq_39403545/article/details/86649026