mysql常见错误

mysql 插入外键时出错

ALTER TABLE 表A ADD CONSTRAINT 约束名 FOREIGN KEY(约束字段) 
                                REFERENCES 表B(对应表的主键);

错误代码： 1822

Failed to add the foreign key constraint. Missing index for constraint。。。。

可能是有两个原因：

1）：主键错了

也就是后面的主键不是主键，我这里的问题就是之前我取消了前面表B的主键，忘记了，后面还接着把它当主键，所以自然就报错了。所以解决方法是直接赋一个主键就行了：

alter table  表B add primary key (xx);  -- 先添加主键

2）：主键不全

这里的意思是表B的主键不只有一个，但是你只用了其中一个，这里的解决方法就是：把表B的主键全部加进来，而且约束的键个数也要和表B的主键个数相同。

只是我目前遇到的两种情况，如果还有其他情况我没说到烦请大佬在评论区一起交流一下~

MySQL中UPDATE语句里SET后使用AND无法更新

在实际使用update给多个字段赋值时发现无法更新，而且第一个赋值的字段变成了0或者是1.这是因为set后面加and是另一个过程：

过程解析

因此我们发现加and，原本的赋值变成一个逻辑值，因此解决方法是把and改成','即可

 Duplicate column name 'Sno'

这是我原先的代码：

CREATE VIEW view_male_student AS 
SELECT student.Sno,student.Sname,course.Cname,sc.Grade 
from student,course,sc where  
student.Ssex='男' AND student.Sno=sc.Sno 
and sc.Cno=course.Cno;

一开始是直接select * ，结果出现报错Duplicate column name 'sno'，原因是因为最后的结果student表和sc表里面都含有sno,所以他才说我们重复字段名了。

因此解决方法是要么取重名，要么就对搜索范围进行修改（不用*,换成其他不会重复的字段）。

Can not delete from join view 'csl.jsj_2_student'

首先一定要强调一点：修改视图时，原表的数据也会被改动

因此再来看这个问题，之所以删不了视图中的数据是因为你现在的这个视图，是由多个表内连接组合成的，因此一旦你删除了这条数据，几个表就会被同时改动，这违背了完整性。因此数据库不允许你这么操作。

具体的解决方法是：回到原表中找对应的数据，依次删除即可（确实蛮笨的，但是我目前没想到其他更好的方法）

Cannot delete or update a parent row: a foreign key constraint fails xxx

这次删不了数据是因为，有外键约束。

解决方法也比较简单，先解除外键约束，删除完之后再开启即可：

SET foreign_key_checks = 0;  -- 关闭外键约束检查


DELETE FROM compute_student where Sno='991201';		-- 删数据


SET foreign_key_checks = 1; -- 再开启外键约束检查

对student表创建update 触发器，当对student表进行修改时，显示被修改的记录

这是一个问题，但是我花了很久才得到我想要的答案，因此特地记录一下：

（不太了解触发器语法的小伙伴可以先看这个：这个）

首先我最开始的代码是这样的

create TRIGGER t2 AFTER UPDATE on student
for EACH ROW select * from student where Sno=old.Sno ;	

这个时候开始报第一个错：Not allowed to return a result set from a trigger，

原因是MYSQL5以后，不允许触发器返回任何结果，我用的是mysql8点多，所以才会出现这个问题。当然解决方法是使用into @变量名，把结果放入到变量之中，最后再查看。改进之后代码是这样：

create TRIGGER t2 AFTER UPDATE on student
for EACH ROW select * from student where Sno=old.Sno into @pd1;		

好的，现在开始报第二个错误：the used select statements have a different number of columns。

关于这个问题网上很多都是说UNION 关键字用错了什么的，但是我这个词都没出现过，显然不是这个原因，而是我这个select * 实际上会返回5个字段，但是我只设置了一个变量，所以当然会报错，解决方法只要多设置几个变量就行了：最终的代码：

drop TRIGGER t2;			
-- 先删除之前的触发器

create TRIGGER t2 AFTER UPDATE on student
for EACH ROW 
begin
select * from student where Sno=old.Sno into @pd1,@pd2,@pd3,@pd4,@pd5;		
end;
-- 这里只能把结果都存在变量里

SELECT  @pd1,@pd2,@pd3,@pd4,@pd5;        
--这个时候都是none,因为还没执行更新操作，触发器未激活

UPDATE student SET Sno='000101' where Sno='000101' ;		
-- 测试一下（更新一定要执行，触发器才会被激活）
 
SELECT  @pd1,@pd2,@pd3,@pd4,@pd5;        
-- 触发器激活，有值了

顺便提一下中断触发器的用法：

create TRIGGER t3 BEFORE INSERT on sc
for EACH ROW 
begin
	if new.Sno NOT IN (SELECT Sno FROM student) THEN
    signal sqlstate '45000' set message_text='Update Error';
	END IF;
END;

INSERT sc(Sno,Cno) VALUES('5631','5');		-- 测试
--显示update error即为成功

计算非重复字段的行的数量

一开始可能会想到用group by但是写到后面会比较复杂，其实可以直接用distinct关键字，

如计算某班的人数以及平均成绩

SELECT count(DISTINCT Sno)as '总人数',avg(Grade)as '平均成绩' 
FROM sc GROUP BY Sno HAVING Sno LIKE '9911%'

除了嵌套查询可以用子句，其实在select的一开始也可以用子句查询，如：

SELECT `姓名`,`客户ID`,(SELECT COUNT(*) FROM orders o WHERE o.`客户ID`=c.`客户ID`)
 AS '数量'FROM customers c