MySQL常用语法--单表和多表约束关系

MySQL中的约束关系

-- 创建学生表(编号 姓名 年龄) 编号设为主键
CREATE TABLE student(
	id INT PRIMARY KEY,
	NAME VARCHAR(30),
	age INT
);
-- 查询学生表的详细信息
DESC student;

-- 添加数据
INSERT INTO student VALUES (1,'张三',23);
INSERT INTO student VALUES (2,'李四',22);

-- 删除主键
ALTER TABLE  student DROP PRIMARY KEY;

-- 建表后单独添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;



-- 创建学生表(编号 姓名 年龄) 编号设为主键自增
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT
);
-- 查询学生表的详细信息
DESC student;

-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23);
INSERT INTO student VALUES (NULL,'李四',22);

-- 删除自增约束
ALTER TABLE  student MODIFY id INT;
-- 建表后单独添加自增约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
-- 查看表结构
DESC student;

--  编号为主键自增  年龄设为 唯一约束
CREATE TABLE student1(
 	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT UNIQUE
);

-- 查询学生表的详细信息
DESC student1;

-- 添加数据
INSERT INTO student1 VALUES (NULL,'张三',23);
INSERT INTO student1 VALUES (NULL,'李四',23);

-- 删除唯一约束
ALTER TABLE student1 DROP INDEX age;
INSERT INTO student1 VALUES (NULL,'李四',23);
DESC student1;


--  编号设为主键自增姓名为非空 年龄设为 唯一
CREATE TABLE student2(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30) NOT NULL,
	age INT UNIQUE
);
DESC student2;
-- 添加数据
INSERT INTO student2 VALUES (NULL,'张三',23);
-- 删除非空约束
ALTER TABLE student2 MODIFY NAME VARCHAR(30);
DESC student2;	   
INSERT INTO student2 VALUES (NULL, NULL,21);
-- 创建表后添加非空约束
ALTER TABLE student2 MODIFY NAME VARCHAR(30) NOT NULL;

唯一约束
unique key 唯一约束

不能重复
一张表上可有多个唯一约束
可以为空  也可以为多个null

默认约束 一般和非空约束配合使用

默认约束表示当不指定值时 会使用默认值

CREATE TABLE t18(
	a VARCHAR(20),
	b VARCHAR(20) NOT NULL DEFAULT 'ok'  -- 当对b没有进行赋值的时候 默认则是ok
)ENGINE INNODB CHARSET utf8;

主键约束和唯一约束

二者都要求字段值 唯一 
	同一张表只能有一个主键  但能有多个唯一约束
	主键字段值不能为null  唯一约束字段值可以为null
	主键字段可以做为其他表的外键  唯一约束字段不可以作为其他表的外键
	SQL server 默认任务主键创建聚集索引 唯一约束字段创建非聚集索引

主键，唯一，但是不能为空；唯一约束，唯一，但是可以为空

外键两表之间的约束

-- 创建db2数据库
CREATE DATABASE db2;

-- 使用db2数据库
USE db2; 

-- 建表时添加外键约束
-- 创建user用户表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL, '张三'),(NULL,'李四');
-- 创建orderlist 订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NUMBER VARCHAR(20) NOT NULL,
	uid INT,
--  产生连接关系 uid 标识客户和订单的关系
	CONSTRAINT OU_fkl FOREIGN KEY (uid) REFERENCES USER(id)
);
INSERT INTO orderlist VALUES (NULL ,'001',1),(NULL,'002',1),
(NULL,'003',2),(NULL,'004',2);

-- 添加一个 订单 但是没有真实用户  外键约束 无三号用户  所以不能增加这个订单
INSERT INTO orderlist VALUES (NULL,'005',3);

-- 删除李四用户 删除失败
DELETE FROM USER WHERE NAME='李四';

-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fkl;

-- 创建表之后  添加外键约束
ALTER TABLE orderlist ADD CONSTRAINT ou_fkl FOREIGN KEY (uid) REFERENCES USER (id);

-- 添加外键约束 同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fkl FOREIGN KEY (uid) REFERENCES USER (id)
ON UPDATE CASCADE ON DELETE CASCADE;

-- 将李四这个用户的id修改为3
UPDATE USER SET id =3 WHERE id=2;


UPDATE USER SET id =98 WHERE id=1;
-- 将李四这个用户删除  在订单表中的该用户也被删除
DELETE FROM USER WHERE id=3;