navicate下的sql问题
- 一、navicate使用技巧
- 1.1 利用Navicat,使用sql语句创建表格(非命令行界面
- 1.2 sql批量插入多条记录
- 1.3 创建视图with check option
- 1.4使用alter table 语句修改表的约束关系
- 二、sql报错
- 2.1 sql报错:1050 - Table ××× already exists
- 2.2 sql报错:1452 - Cannot add or update a child row: a foreign key constraint fails
- 2.3 sql报错:1044 - Access denied for user 'root'@'localhost' to database 'information_schema'
- 2.4 sql报错:1248 - Every derived table must have its own alias
- 2.5 sql报错:1068 - Multiple primary key defined
- 2.6 sql删除主键报错:1553 - Cannot drop index 'PRIMARY': needed in a foreign key constraint
- 2.7 sql删除外键报错:1091 - Can't DROP 'course_id'; check that column/key exists
- 2.8 sql添加主键时报错1138 - Invalid use of NULL value
- 2.9 sql报错:1822 - Failed to add the foreign key constraint. Missing index for constraint 'student_ibfk_2' in the referenced table 'login'
- 2.10 sql报错:1054 - Unknown column in 'where clause' /1054 - Unknown column 'semester' in 'field list'
- 2.11 插入数据报错1364 - Field 'year' doesn't have a default value
- 三、sql基础知识
- 3.1 主码,外码等约束问题
- 3.2 sql查询语句
- 3.3 Mysql级联操作,添加外键和删除外键(sql中)
- 3.4 主键与外键/主表与从表
一、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的数目都必须正确。如果不提供列名,则必须给每个表列提供一个值;如果提供列名,则必须给列出的每个列一个值。否则,就会产生一条错误消息,相应的行不能成功插入。
二、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
, CONSTRAINTcourse_ibfk_1
FOREIGN KEY (course_pid
) REFERENCEScourse
(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;
删除成功!
删除后依旧报错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)带有主键的那张表称为父表,含外键的是子表,必须先删除外键约束才能删除父表。
原文链接:
解决:
1.要检查外键与主键中,字段的数据类型是否一致;检查外键字段覆盖是否完全。
2.检查子表外键是否是主表主键。
3.该问题可能是由于主表中设置了两个主键,而在子表中建立外键约束时,只选中了一列作为外键约束,导致不匹配。
三、sql基础知识
3.1 主码,外码等约束问题
●主键约束(Primary Key constraint):要求主键列数据唯一,并且不允许为空。
●唯一约束(Unique constraint):要求该列唯一,允许为空,但只能出现一个空值。
●检查约束(Check constraint):某列取值范围限制,格式限制等,如有关年龄、邮箱(必须有@)的约束。
●默认约束(Default constraint):某列的默认值,如在数据库里有一项数据很多重复,可以设为默认值。
●外键约束(Foreign Key constraint):用于在两个表之间建立关系,需要指定引用主表的哪一列。
3.2 sql查询语句
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 ;
3.4 主键与外键/主表与从表
外键取值规则:空值或参照的主键值。
(1)插入非空值时,如果主键表中没有这个值,则不能插入。
(2)更新时,不能改为主键表中没有的值。
(3)删除主键表记录时,你可以在建外键时选定外键记录一起级联删除还是拒绝删除。
(4)更新主键记录时,同样有级联更新和拒绝执行的选择。
简而言之,SQL的主键和外键就是起约束作用。
什么值可以成为主键
关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键。
比如:
学生表(学号,姓名,性别,班级)
其中每个学生的学号是唯一的,学号就是一个主键;
课程表(课程编号,课程名,学分)
其中课程编号是唯一的,课程编号就是一个主键;
成绩表(学号,课程号,成绩)
成绩表中单一一个属性无法唯一标识一条记录,学号和课程号的组合才可以唯一标识一条记录,所以,学号和课程号的属性组是一个主键。
注:成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键;同理,成绩表中的课程号是课程表的外键。
什么是主表和父表
(1)主表(父表):
在数据库中建立的表格即Table,其中存在主键(primary key)用于与其它表相关联,并且作为在主表中的唯一性标识。被作为外键引用的表。
(2)从表(子表):
以主表的主键(primary key)值为外键 (Foreign Key)的表,可以通过外键与主表进行关联查询。从表与主表通过外键进行关联查询。有外键引用的表。