MySQL8.0数据库出现的问题——外码创建方式、外键约束两个引用列不兼容问题、check约束问题、用触发器代替check约束、关键字DELIMITER、删除添加索引、删除添加外键约束、和一些数据库方面的操作

时间:2024-11-29 13:36:01

一、首先先说一下我们都需要建立那些表

mysql> CREATE TABLE IF NOT EXISTS `student`(
-> `sno` CHAR(8) NOT NULL,
-> `sname` CHAR(4) NOT NULL,
-> `ssex` enum('男','女') not null default '男',
-> `sage` INT,
-> `sdept` CHAR(10),
-> PRIMARY KEY ( `sno` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec) mysql>
mysql> CREATE TABLE IF NOT EXISTS `course`(
-> `cno` CHAR(2) NOT NULL,
-> `cname` CHAR(30) NOT NULL,
-> `credit` INT,
-> `cpno` CHAR(3),
-> PRIMARY KEY ( `cno` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> CREATE TABLE `sc`(
-> `sno` CHAR(8) not null,
-> `cno` CHAR(2) not null,
-> `grade` INT check(grade>='0' and grade<='100'),
-> PRIMARY KEY ( cno , sno),
-> FOREIGN KEY (sno) REFERENCES student(sno),
-> FOREIGN KEY (cno) REFERENCES course(cno)
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

上面是建立了三个表,分别是student、course、sc。上面字段我使用 `字段名` 的形式,其实也可以不加这个符号 ` 。

二、先说一下外码我的错误方式

mysql> CREATE TABLE IF NOT EXISTS `sc`(
-> `sno` CHAR(8) foreign key references student(sno),
-> `cno` CHAR(2) foreign key references course(cno),
-> `grade` INT,
-> PRIMARY KEY ( cno , sno),
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foreign key references student(sno),
`cno` CHAR(2) foreign key references cou' at line 2

作为新手怎么也找不到。。。就只能换种方式了,就是上面创建sc表的外键方式

三、外键约束两个引用列不兼容问题

mysql> CREATE TABLE `sc`(
-> `sno` CHAR(8) not null,
-> `cno` CHAR(2) not null,
-> `grade` INT check(grade>='0' and grade<='100'),
-> PRIMARY KEY ( cno , sno),
-> FOREIGN KEY (sno) REFERENCES student(sno),
-> FOREIGN KEY (cno) REFERENCES course(cno));
ERROR 3780 (HY000): Referencing column 'sno' and referenced column 'sno' in foreign key constraint 'sc_ibfk_1' are incompatible.

一般出现这个问题,就是你从其他表引用的 字段的类型 和这个表的 字段类型 不一样(就比如一个是int类型,另一个是char类型);我得错误方式是在student表中后面有一句

ENGINE=InnoDB DEFAULT CHARSET=utf8;

而现在这个表没有指定,然后就错了,加上这一句就可以了

四、check约束问题

mysql所有的存储引擎均不支持check约束,MySQL会对check子句进行分析,但是在插入数据时会忽略,因此check并不起作用,因此实现对数据约束有两种方法:

1.在mysql种约束,如使用enum类型或者触发器等。
2.在应用程序里面对数据进行检查再插入。

五、用触发器代替check约束和关键字DELIMITER

DELIMITER \\;

这个语句就表示之后数据库的结束标志就变成了 \\ .

我上面规定成绩的范围是[0,100]用的是check约束,可是check约束无用,下面给出触发器约束

mysql> DELIMITER //
mysql> CREATE TRIGGER t_insert2 after INSERT ON sc FOR EACH ROW
-> BEGIN
-> DECLARE msg VARCHAR(200);
-> IF (new.grade < 0 or new.grade>100) THEN
-> SET msg = "成绩不符合要求";
-> signal sqlstate 'HY000' SET message_text = msg;
-> END IF;
-> END//
Query OK, 0 rows affected (0.03 sec) mysql> DELIMITER ;

查看所有触发器:

另一种查询触发器命令:    show triggers;

删除触发器命令: drop trigger trigger_name;

六、删除添加索引

建立降序索引语法:

create index 索引名 on 表名(字段名 desc);

注:desc换成asc则是升序索引。

删除索引语法:

drop index 索引名;

但是我的MySQL8.0不支持这种删除索引方式,下面是从student表中删除索引sname_index报错

drop index sname_index;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

正确方式:

drop index sname_index on student;
Query OK, 0 rows affected (0.04 sec)

七、删除添加外键约束

当我们在一个表中添加字段约束的时候:

ALTER TABLE 表名 ADD CONSTRAINT 约束名 KEY(本表内要加以外键约束的字段) REFERENCES 其他表(要被其他表用以外键约束的字段);

然后当我们想删除时:

ALTER TABLE 表名 DROP FOREIGN KEY外键约束名;

但是像我之前建立sc表的时候,我并没有给外键约束创建名字,那应该怎么删除呢?

我可以先输入

 SHOW CREATE TABLE sc;

这样表结构就会出来,同样如果你没有给外键约束起名字,系统也会给它起一个

mysql> SHOW CREATE TABLE sc;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sc | CREATE TABLE `sc` (
`sno` char(8) NOT NULL,
`cno` char(2) NOT NULL,
`grade` int(11) DEFAULT NULL,
PRIMARY KEY (`cno`,`sno`),
KEY `sno` (`sno`),
CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

这个关键字

CONSTRAINT

后面的就是这个约束的名字,然后就可以删除了,下面给出删除和添加的示例

mysql> alter table sc drop foreign key sc_ibfk_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE sc ADD CONSTRAINT sc_ibfk_1 FOREIGN KEY(sno) REFERENCES student(sno);
Query OK, 1 row affected (0.15 sec)
Records: 1 Duplicates: 0 Warnings: 0

八、数据库操作其他操作

查询表runoob_tb1的所有内容

select * from runoob_tbl;

向表sc插入数据

insert into sc values('1','3',105);

切换数据库至dbs(dbs是一个数据库的名字)

use dbs

展示所有数据库

 show databases;

其他更多可见:https://www.runoob.com/mysql/mysql-select-query.html