sql数据库学习过程中出现的报错

一、navicate使用技巧

1.1 利用Navicat，使用sql语句创建表格（非命令行界面

选中查询，右键即可。

1.2 sql批量插入多条记录

数据库的增删查改等应用总结：数据库之三SQL
建表

-- 用户信息
create Table if not exists yonghu(
     Yid     varchar(30)PRIMARY KEY, -- 用户id
		 Ypw	   varchar(25)  -- 用户密码
	 
);

添加数据
第一种：省事，不安全，要按列的次序插入

insert into yonghu values
('20201','202011'),
('20202','202022'),
('20203','202033'),
('20204','202044'),
('20205','202055');

第二种：不安全，要按列的次序插入

insert into yonghu values('20201','202011');
insert into yonghu values('20202','202022');
insert into yonghu values('20203','202033');
insert into yonghu values('20204','202044');
insert into yonghu values('20205','202055');

第三种：繁琐，安全，在表的列次序变化的情况下也适用

insert into yonghu (Yid,Ypw)values
('20201','202011',),
('20202','202022'),
('20203','202033'),
('20204','202044'),
('20205','202055');

注意：不管使用哪种INSERT语法，VALUES的数目都必须正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

1.3 创建视图with check option

SQL 视图的with check option
SQL中的with check option

1.4使用alter table 语句修改表的约束关系

（数据库-MySQL）修改表——添加列、修改列、删除列（名）
https://blog.csdn.net/weixin_30340617/article/details/95690508
https://blog.csdn.net/lixwjava/article/details/19919881
如何删除表的外键约束？
数据库MySQL之如何删除主键、唯一和外键约束？

二、sql报错

2.1 sql报错：1050 - Table ××× already exists

在create table后添加if not exists。
也即如何让表格和插入同时创建
正常情况下需要选中后点击 运行已选择的 先创建表格再插入。

为方便，可在create table后添加if not exists，完成如果表格不存在就创建的情况，即可直接运行。

2.2 sql报错：1452 - Cannot add or update a child row: a foreign key constraint fails

无法添加子行或更新子行：外键约束失败
更新内容时报错

1452 - Cannot add or update a child row: a foreign key constraint fails (universitydb.course, CONSTRAINT course_ibfk_1 FOREIGN KEY (course_pid) REFERENCES course (course_id))

添加的外键列与另一个表的唯一索引列（一般是主键）的数据类型不同（其实就是外键列引用的不是索引列（主键）的数据内容。

2.3 sql报错：1044 - Access denied for user ‘root’@‘localhost’ to database ‘information_schema’

突然出现权限问题

1044 - Access denied for user ‘root’@‘localhost’ to database ‘information_schema’

删掉数据库重新创建，自己好了。

2.4 sql报错：1248 - Every derived table must have its own alias

翻译：每个派生表必须有自己的别名。
也就是在多级查询的过程中，需要给派生表一个别名。
区别只在于最后有没有as abc
正确：

-- 2022学年第二学期开设的课程中，找出课程最多的选课人数；
	select Max(cnt) 
	from ( 
	select count(ID) as cnt
	from   section natural join SC
	where semester = '二' and year = 2022 group by course_id
	) as abc;

错误：

-- (5)	2022学年第二学期开设的课程中，找出课程最多的选课人数；
	select Max(cnt) 
	from ( 
	select count(ID) as cnt
	from   section natural join SC
	where semester = '二' and year = 2022 group by course_id
	);

2.5 sql报错：1068 - Multiple primary key defined

翻译：定义了多个主键。
错误1：定义了两个主键，导致出错，删去一个即可；

错误2：在已有主键的表section上添加新的列并设为主键报错

 create Table if not exists section	(
     course_id		varchar(8),
     semester		varchar(6),
	   year			numeric(4,0), 
	   building		varchar(15),
	   room_number		varchar(7),
	   time		varchar(4),
		 PRIMARY KEY(course_id,semester,year),
		 FOREIGN KEY(course_id) references course(course_id) 
);

alter table section add series int not Null primary key;
出现这种问题是因为已经有了主键。
解决：删除原有主键：alter table 表名 DROP PRIMARY KEY;
再进行主键操作，结果出现如下2.6的报错

2.6 sql删除主键报错：1553 - Cannot drop index ‘PRIMARY’: needed in a foreign key constraint

1553 - Cannot drop index ‘PRIMARY’: needed in a foreign key constraint

翻译：不能删除索引“PRIMARY”:在外键约束中需要
解决：删除外键alter table section drop foreign key course_id;
出现如下2.8的报错

2.7 sql删除外键报错：1091 - Can’t DROP ‘course_id’; check that column/key exists

1091 - Can’t DROP ‘course_id’; check that column/key exists

翻译：1091 -不能删除’ course_id’;检查列/键是否存在

这个问题出在建表代码和删除外键的代码上。由于在当初建表的时候，没有为这个外键约束起一个名称，所以 MySQL 为此自动生成了一个名称，这个自动生成的名称不同于受到这个外键约束的列名。而删除外键的时候，需要提供的是这个外键约束名，而不是列名。

可以印证这一点。在 Navicat Premium 中可以看到关于这个表的建表信息。（也可以使用代码 show create TABLE section; 来查看。）

CREATE TABLE `section` (
  `course_id` varchar(8) NOT NULL,
  `semester` varchar(6) NOT NULL,
  `year` decimal(4,0) NOT NULL,
  `building` varchar(15) DEFAULT NULL,
  `room_number` varchar(7) DEFAULT NULL,
  `time` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`course_id`,`semester`,`year`),
  CONSTRAINT `section_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci  

可以看到，MySQL 自动为列 course_id 的外键约束生成了一个名称 section_ibfk_1。因此，删除外键时应使用这个名称。即删除外键的代码应如下：ALTER TABLE section DROP FOREIGN KEY section_ibfk_1;
删除成功！

解决MySQL删除外键时报错Error Code: 1091. Can‘t DROP ‘XXX‘； check that column/key exists
删除后依旧报错1553，原因是不仅要删除当前表的外键，还要删除所有以当前表主键字段为外键的外键。
案例：下表以section的course_id,semester,year为外键，故需要同时加入ALTER TABLE teach DROP FOREIGN KEY teach_ibfk_1;

 create Table if not exists teach	(
   ID			varchar(5), 
	 course_id		varchar(8),
	 semester		varchar(6),
	 year			numeric(4,0),
	 PRIMARY KEY (ID,course_id,semester,year),
	 FOREIGN KEY(course_id,semester,year) references section(course_id,semester,year) 
);

实例：修改section表，增加series列，smallint，表示课程的课头序号，比如数据库课程的课程号为’C001’,有两个课头，则第一个课头series值为1，第二个课头series值为2；如果该课程只有一个课头，则series值默认为1；

-- 删除外键
ALTER TABLE section DROP FOREIGN KEY section_ibfk_1;
ALTER TABLE teach DROP FOREIGN KEY teach_ibfk_1;
ALTER TABLE sc DROP FOREIGN KEY sc_ibfk_1;
-- 删除主键
alter table  section DROP PRIMARY KEY;
-- 添加列
alter table  section  add column series smallint;
-- 为即将称为主码的列插入内容
update section set series=1 where course_id ='001';
update section set series=1 where course_id ='002';
update section set series=1 where course_id ='003';
update section set series=1 where course_id ='004';
update section set series=1 where course_id ='005';
-- 添加主键
alter table  section add primary key (course_id,series,semester,year);
-- 为section添加外键
alter table  section add FOREIGN KEY(course_id) references course(course_id);

2.8 sql添加主键时报错1138 - Invalid use of NULL value

1138 - Invalid use of NULL value

因为主键不能为空，所以要先给新建的列series补充内容

2.9 sql报错：1822 - Failed to add the foreign key constraint. Missing index for constraint ‘student_ibfk_2’ in the referenced table ‘login’

设置外键的时候需要注意以下几点：
（1）外键是用于两个表的数据之间建立连接，可以是一列或者多列，即一个表可以有一个或多个外键。

（2）这个表里面设置的外键必须是另外一个表的主键！

（3）外键可以不是这个表的主键，但必须和另外一个表的主键相对应（字段的类型和值必须一样）。

（4）带有主键的那张表称为父表，含外键的是子表，必须先删除外键约束才能删除父表。
原文链接：https://blog.csdn.net/Nurbiya_K/article/details/101560650
解决：
1.要检查外键与主键中，字段的数据类型是否一致；检查外键字段覆盖是否完全。
2.检查子表外键是否是主表主键。
3.该问题可能是由于主表中设置了两个主键，而在子表中建立外键约束时，只选中了一列作为外键约束，导致不匹配。
将父表中的两列主键均加入到外键约束中，该问题就会解决。
mysql外键设置问题 Failed to add the foreign key constraint.

2.10 sql报错：1054 - Unknown column in ‘where clause’ /1054 - Unknown column ‘semester’ in ‘field list’

1.1054 - Unknown column in ‘where clause’ 报错异常
很好的思路，最终解决是发现sql的语句标点符号写错
2. 1054 - Unknown column ‘semester’ in ‘field list’
MySQL使用报错：1054 - Unknown column ‘sno‘ in ‘field list‘（表中字段编码与SQL语句中编码不统一）

2.11 插入数据报错1364 - Field ‘year’ doesn’t have a default value

数据库插入数据时报错 1364 - Field ‘house_name‘ doesn‘t have a default value

三、sql基础知识

3.1 主码，外码等约束问题

●主键约束（Primary Key constraint）：要求主键列数据唯一，并且不允许为空。
●唯一约束（Unique constraint）：要求该列唯一，允许为空，但只能出现一个空值。
●检查约束（Check constraint）：某列取值范围限制，格式限制等，如有关年龄、邮箱（必须有@）的约束。
●默认约束（Default constraint）：某列的默认值，如在数据库里有一项数据很多重复，可以设为默认值。
●外键约束（Foreign Key constraint）：用于在两个表之间建立关系，需要指定引用主表的哪一列。

3.2 sql查询语句

SQL Server 查询选修了学分为3或4课程的学生名字(5种方法)
group by 语句什么意思
sql学习教程：https://www.w3school.com.cn/sql/index.asp
查询被选最多的课程的课程名（course、selectcourse）
sql 中的with 语句使用
小白做题 ——数据库系统概念（第六版）第三章习题
SQL中 with函数的详解与用法
ＳＱＬ中　EXCEPT和INTERSECT用法

3.3 Mysql级联操作，添加外键和删除外键（sql中）

级联操作：删除或修改主表的内容，从表会随之改变。

案例：CONSTRAINT student_fk1 FOREIGN KEY(Yid) references login(id) ON UPDATE CASCADE ON DELETE CASCADE

-- 用户信息
create Table if not exists student(
     Yid     varchar(30)PRIMARY KEY, -- 用户id
		 Ypw	   varchar(25),  -- 用户密码
		 Yname    varchar(25),-- 用户姓名
		 Yaddress	 varchar(25), -- 用户地址
		 Ytel	 varchar(25),-- 用户电话
		 CONSTRAINT  -- 声明
         student_fk1 -- 外键名称 
         FOREIGN KEY  -- 外键作用在 
         (Yid)  -- 从表的Yid字段
         REFERENCES   -- 关联
         login(id) --  主表的主键id
         ON UPDATE CASCADE  -- 级联修改
         ON DELETE CASCADE  -- 级联删除		  
);

        -- 实例：修改主表的数据,1号部门---3号部门 (级联修改) 
        -- 将id从1改为3
        UPDATE login SET id = 3 WHERE id = 1; 
		-- 从表的所有id为1的变成了3
		
		-- 删除主表的数据,从表数据随之删除(级联删除)
		DELETE FROM login WHERE id = 2 ;
		-- 删除主表id=2的记录从表随之删除

删除外键和添加外键
删除外键语法:

-- sql删除外键: alter table 表名 drop FOREIGN KEY 外键名称;
ALTER TABLE student DROP FOREIGN KEY student_fk1;

添加外键语法:

-- alter table 表名 add 
-- 			constraint 外键名称 foreign key (从表的某个id)
-- 			references 主表(主表id) 
--  		级联修改
-- 			级联删除
	
ALTER TABLE student 
ADD CONSTRAINT student_fk1 FOREIGN KEY (Yid) 
			REFERENCES login(id) 
			ON UPDATE CASCADE 
			ON DELETE CASCADE ;

https://blog.csdn.net/m0_67499084/article/details/124806332

3.4 主键与外键/主表与从表

外键取值规则：空值或参照的主键值。

(1)插入非空值时，如果主键表中没有这个值，则不能插入。
(2)更新时，不能改为主键表中没有的值。
(3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。
(4)更新主键记录时，同样有级联更新和拒绝执行的选择。
简而言之，SQL的主键和外键就是起约束作用。

什么值可以成为主键

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键。

比如：
学生表(学号，姓名，性别，班级)
其中每个学生的学号是唯一的，学号就是一个主键；

课程表(课程编号,课程名,学分)
其中课程编号是唯一的,课程编号就是一个主键；

成绩表(学号,课程号,成绩)
成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以，学号和课程号的属性组是一个主键。

注：成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键；同理，成绩表中的课程号是课程表的外键。


什么是主表和父表

（1）主表（父表）：
在数据库中建立的表格即Table，其中存在主键(primary key)用于与其它表相关联，并且作为在主表中的唯一性标识。被作为外键引用的表。
（2）从表（子表）：
以主表的主键（primary key）值为外键 (Foreign Key)的表，可以通过外键与主表进行关联查询。从表与主表通过外键进行关联查询。有外键引用的表。
https://blog.csdn.net/weixin_48135624/article/details/115212815