MYSQL中相关约束

1.非空约束(NOT NULL)

--测试用表
CREATE TABLE STUDENT (
	ID INT,
	NAME VARCHAR (40),
	AGE INT,
	SCORE INT,
	MOBILE VARCHAR(20),
	BIRTHDAY DATE,
	EXCHANGE_TIME TIMESTAMP
);

增加、删除非空约束：

--增加非空约束
ALTER TABLE STUDENT MODIFY SCORE INT NOT NULL;
--删除非空约束
ALTER TABLE STUDENT MODIFY SCORE INT;

2.唯一约束(UNIQUE)

增加、删除唯一约束：

--增加表唯一约束
ALTER TABLE STUDENT MODIFY MOBILE VARCHAR(20) UNIQUE;
--删除表唯一约束
ALTER TABLE STUDENT DROP INDEX MOBILE;

3.主键约束(PRIMARY KEY)

主键：表中的唯一标识，非空且唯一，一张表中只能有一个主键：

--增加主键
ALTER TABLE STUDENT MODIFY ID INT PRIMARY KEY;
--删除主键
ALTER TABLE STUDENT DROP PRIMARY KEY;

主键中增加自增约束：

--增加自增
ALTER TABLE STUDENT MODIFY ID INT AUTO_INCREMENT;
--删除自增
ALTER TABLE STUDENT MODIFY ID INT;
--删除主键
ALTER TABLE STUDENT DROP PRIMARY KEY;

4.外键约束(FOREIGN KEY)

外键约束可以保证数据正确、完整性。如部门不存在，EMP表中DEPT_ID将无法识别，需将DEPT表中ID设置为EMP表外键。

--创建部门表
CREATE TABLE DEPT(
ID INT PRIMARY KEY AUTO_INCREMENT,
DEPT_NAME VARCHAR(20),
DEPT_LOCATION VARCHAR(20)
);
--创建员工表
CREATE TABLE EMP(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
AGE INT,
DEPT_ID INT,
--CONSTRAINT EMP_DEPT_FK可以省略系统会自动分配
CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID) 
);
--员工表插入测试数据
INSERT INTO EMP(NAME,AGE,DEPT_ID) VALUES ('张一', 20, 1);
INSERT INTO EMP(NAME,AGE,DEPT_ID) VALUES ('张二', 21, 1);
INSERT INTO EMP(NAME,AGE,DEPT_ID) VALUES ('张三', 22, 2);
INSERT INTO EMP(NAME,AGE,DEPT_ID) VALUES ('张四', 23, 2);
INSERT INTO EMP(NAME,AGE,DEPT_ID) VALUES ('张五', 24, 2);
--部门表插入测试数据
INSERT INTO DEPT VALUES (NULL, '研发部','广州');
INSERT INTO DEPT VALUES (NULL, '研发部','深圳');

添加、删除外键约束：

--创建表时候直接设置外键
--语法：CONSTRAINT 外键名称 FOREIGN KEY (COLUMN) REFERENCES 关联主表(COLUMN) 
--其中，CONSTRAINT 外键名称可以省略
CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID)
--删除外键
ALTER TABLE EMP DROP FOREIGN KEY EMP_DEPT_FK;
--修改表字段为外键
ALTER TABLE EMP ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID);

级联操作(更新、删除)：修改主表ID直接联动修改外键DEPT_ID，级联删除需要慎用

--删除外键
ALTER TABLE EMP DROP FOREIGN KEY EMP_DEPT_FK;
--增加级联更新关键字ON UPDATE CASCADE
ALTER TABLE EMP ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID) ON UPDATE CASCADE;
--删除外键
ALTER TABLE EMP DROP FOREIGN KEY EMP_DEPT_FK;
--增加级联删除关键字ON DELETE CASCADE
ALTER TABLE EMP ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID) ON DELETE CASCADE;

 

遇到问题：MYSQL5.7中使用Navicat查询报错[Err] 1055

问题原因：由于sql_mode中配置，对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，除非这个字段出现在聚合函数里面。

解决办法：修改my.ini配置文件中sql_mode取消ONLY_FULL_GROUP_BY配置，重启服务即可