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

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

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