MySQL基础操作之数据约束与关联查询

共计 3453 个字符，预计需要花费 9 分钟才能阅读完成。

一、MySQL 约束

1. 默认值约束，当字段没有插入值的时候，mysql 自动给该字段分配默认值。
默认值的字段允许为空。
对默认值字段也可以插入 null。

 1 CREATE TABLE STUDENT(2     ID INT,
3     NAME VARCHAR(20),
4     ADDRESS VARCHAR(20) DEFAULT '京口区'
5 );
6 INSERT INTO STUDENT (ID, NAME) VALUES (1,'张三');
7 INSERT INTO STUDENT (ID , NAME,ADDRESS) VALUES (2,'李四' ,NULL);

MySQL 基础操作之数据约束与关联查询

2. 非空约束

 1 CREATE TABLE STUDENT(2     ID INT,
3     NAME VARCHAR(20),
4     GENDER VARCHAR(2) NOT NULL
5 );

2.1 非空字段必须赋值 ( 错误显示)

INSERT INTO STUDENT (ID , NAME) VALUES(1, '李四');

MySQL 基础操作之数据约束与关联查询

2.2 不能插入 null（错误显示）

INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三' ,NULL);

MySQL 基础操作之数据约束与关联查询

3. 唯一约束

 1 CREATE TABLE STUDENT (2     ID INT UNIQUE,
3     NAME VARCHAR(20)
4 );
5 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

下面语句执行发生错误

INSERT INTO STUDENT (ID , NAME) VALUES (1, '李四');

错误提示

MySQL 基础操作之数据约束与关联查询

4. 主键约束(非空 + 唯一)，通常每张表都会设置一个主键字段。用于标注表记录的唯一性。
主键一般都是没有业务含义的。

 1 CREATE TABLE STUDENT(2     ID INT PRIMARY KEY,
3     NAME VARCHAR(20)
4 );
5 
6 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

下面语句执行显示错误

INSERT INTO STUDENT (ID , NAME) VALUES (NULL , '李四');

错误提示

MySQL 基础操作之数据约束与关联查询

下面错误也是一种错误

INSERT INTO STUDENT (ID ,NAME) VALUES (1, '李四');

MySQL 基础操作之数据约束与关联查询

5. 自增长约束
可以自动的递增

 CREATE TABLE STUDENT(ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO STUDENT (NAME) VALUES ('张三');
INSERT INTO STUDENT (NAME) VALUES ('李四');

MySQL 基础操作之数据约束与关联查询

整表数据删除，不影响自增的删除语句

DELETE FROM STUDENT;

删除表之后再次（不用再创建表），添加插入语句如图所示

MySQL 基础操作之数据约束与关联查询

删除表中所有数据, 删除外键也是用这个语句。

DELETE FROM STUDENT;

再次插入数据

MySQL 基础操作之数据约束与关联查询

6. 外键约束

被约束的表为副表，外键设置在副表上

eg: 创建员工表（副表），创建部门表（主表）, 先创建主表，再创建副表

 CREATE TABLE DEPT(ID INT PRIMARY KEY,
    DEPTID VARCHAR(20)
);
 
INSERT INTO DEPT (ID , DEPTNAME) VALUES (1, '软件设计部门');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (2, '人事部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (3, '财务部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (4, '运营部');
 
CREATE TABLE EMPLOYEE(ID INT PRIMARY KEY AUTO_INCREMENT,
    EMPNAME VARCHAR(20),
    DEPTID INT,
    CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID)
);
 
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

查询两张表

MySQL 基础操作之数据约束与关联查询

7. 下面进行级联关联，意味着，当主表修改时，副表也修改

修改以上表的创建语句，并重新插入新的语句

  1 DROP TABLE EMPLOYEE;
 2 
 3 CREATE TABLE EMPLOYEE( 4     ID INT PRIMARY KEY AUTO_INCREMENT,
 5     EMPNAME VARCHAR(20),
 6     DEPTID INT,
 7     CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID) ON UPDATE CASCADE ON DELETE CASCADE
 8 );
 9 
10 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
11 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
12 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
13 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

查询如图所示

MySQL 基础操作之数据约束与关联查询

修改主表的一条记录

UPDATE DEPT SET ID=5 WHERE ID=4;

主表的运行结果

MySQL 基础操作之数据约束与关联查询

副表的运行结果

MySQL 基础操作之数据约束与关联查询

————————————————————————————–

二、关联查询

1. 交叉查询

SELECT * FROM EMPLOYEE,DEPT;

MySQL 基础操作之数据约束与关联查询

2. 内连接查询

多表查询规则：1）查询哪些表 2）确定哪些字段 3）表与表之间的查询条件（连接表的数量 -1）

 1 SELECT EMPNAME , dept.DEPTNAME
2         FROM EMPLOYEE
3         INNER JOIN DEPT
4         ON EMPLOYEE.DEPTID = DEPT.ID;

MySQL 基础操作之数据约束与关联查询

3. 使用别名

 1 SELECT EMPNAME AS '员工姓名 ', D.DEPTNAME AS ' 部门' -- 注意这里的 D 
2                 FROM EMPLOYEE AS E
3                 INNER JOIN DEPT AS D
4                 ON E.DEPTID = D.ID;

MySQL 基础操作之数据约束与关联查询

4. 左外连接查询，左边的数据一定会完全显示。查询时先写左表

首先我们改造一下副表

UPDATE EMPLOYEE SET DEPTID = NULL WHERE  ID= 4;

MySQL 基础操作之数据约束与关联查询

如果是内连接查询则显示

MySQL 基础操作之数据约束与关联查询

如果左连接查询

 1  SELECT    D.DEPTNAME, E.EMPNAME
2                  FROM DEPT AS D -- 左表
3                  LEFT OUTER JOIN EMPLOYEE AS E -- 右表
4                  ON D.ID = E.DEPTID;

MySQL 基础操作之数据约束与关联查询

右连接其实和左连接一样，只是查询表的位置不同，下面是右连接，显示的和左连接的相同

 1  SELECT D.DEPTNAME,E.EMPNAME
2                 FROM EMPLOYEE AS E-- 左表
3                 RIGHT JOIN DEPT AS D -- 右表
4                 ON E.ID = D.ID;

MySQL 基础操作之数据约束与关联查询

5. 自连接查询，自连接查询一般应用于表数据为树状结构。

首先我们创建表并查询

  1 CREATE TABLE PERSON( 2     ID INT PRIMARY KEY AUTO_INCREMENT,
 3     NAME VARCHAR(20),
 4     BOSSID INT
 5 );
 6 
 7 INSERT INTO PERSON (NAME,BOSSID) VALUES ('张三',NULL);
 8 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李四',1);
 9 INSERT INTO PERSON (NAME,BOSSID) VALUES ('王五',2);
10 INSERT INTO PERSON (NAME,BOSSID) VALUES ('赵六',3);
11 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李七',3);
12 
13 SELECT P.NAME AS '老板 ' ,B.NAME AS ' 员工'
14     FROM PERSON AS P
15     RIGHT JOIN PERSON AS B
16     ON P.ID = B.BOSSID;

显示结果

MySQL 基础操作之数据约束与关联查询

注意上面创建表的 BOSSID 的结构。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-10/136368.htm

MySQL基础操作之数据约束与关联查询

申请腾讯混元的API Key并且使用LobeChat调用混元AI

Docker部署搭建一个开源强大的图书管理系统

基于Docker快速搭建一个开源的IT人员在线工具箱-it-tools

让每个人都可以轻松使用Git-腾讯自研Git客户端

使用Docker部署开源的WPS-Office

shell字符串比较、判断是否为数字入门案例

细述使用 Python 获取 Linux 系统信息

轻量教程：阿里云轻量应用服务器开放端口（防火墙添加规则）

Docker下部署socks5和pptp服务端，实现代理上网

如何才能快速发现钓鱼邮件？

	1 CREATE TABLE STUDENT(2 ID INT,
	3 NAME VARCHAR(20),
	4 ADDRESS VARCHAR(20) DEFAULT '京口区'
	5 );
	6 INSERT INTO STUDENT (ID, NAME) VALUES (1,'张三');
	7 INSERT INTO STUDENT (ID , NAME,ADDRESS) VALUES (2,'李四' ,NULL);

	1 CREATE TABLE STUDENT(2 ID INT,
	3 NAME VARCHAR(20),
	4 GENDER VARCHAR(2) NOT NULL
	5 );

	1 CREATE TABLE STUDENT (2 ID INT UNIQUE,
	3 NAME VARCHAR(20)
	4 );
	5 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

	1 CREATE TABLE STUDENT(2 ID INT PRIMARY KEY,
	3 NAME VARCHAR(20)
	4 );
	5
	6 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

	CREATE TABLE STUDENT(ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
	);
	INSERT INTO STUDENT (NAME) VALUES ('张三');
	INSERT INTO STUDENT (NAME) VALUES ('李四');

	CREATE TABLE DEPT(ID INT PRIMARY KEY,
	DEPTID VARCHAR(20)
	);

	INSERT INTO DEPT (ID , DEPTNAME) VALUES (1, '软件设计部门');
	INSERT INTO DEPT (ID , DEPTNAME) VALUES (2, '人事部');
	INSERT INTO DEPT (ID , DEPTNAME) VALUES (3, '财务部');
	INSERT INTO DEPT (ID , DEPTNAME) VALUES (4, '运营部');

	CREATE TABLE EMPLOYEE(ID INT PRIMARY KEY AUTO_INCREMENT,
	EMPNAME VARCHAR(20),
	DEPTID INT,
	CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID)
	);

	INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
	INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
	INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
	INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

	1 DROP TABLE EMPLOYEE;
	2
	3 CREATE TABLE EMPLOYEE( 4 ID INT PRIMARY KEY AUTO_INCREMENT,
	5 EMPNAME VARCHAR(20),
	6 DEPTID INT,
	7 CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID) ON UPDATE CASCADE ON DELETE CASCADE
	8 );
	9
	10 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
	11 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
	12 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
	13 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

	1 SELECT EMPNAME , dept.DEPTNAME
	2 FROM EMPLOYEE
	3 INNER JOIN DEPT
	4 ON EMPLOYEE.DEPTID = DEPT.ID;

	1 SELECT EMPNAME AS '员工姓名 ', D.DEPTNAME AS ' 部门' -- 注意这里的 D
	2 FROM EMPLOYEE AS E
	3 INNER JOIN DEPT AS D
	4 ON E.DEPTID = D.ID;

	1 SELECT D.DEPTNAME, E.EMPNAME
	2 FROM DEPT AS D -- 左表
	3 LEFT OUTER JOIN EMPLOYEE AS E -- 右表
	4 ON D.ID = E.DEPTID;

	1 SELECT D.DEPTNAME,E.EMPNAME
	2 FROM EMPLOYEE AS E-- 左表
	3 RIGHT JOIN DEPT AS D -- 右表
	4 ON E.ID = D.ID;

	1 CREATE TABLE PERSON( 2 ID INT PRIMARY KEY AUTO_INCREMENT,
	3 NAME VARCHAR(20),
	4 BOSSID INT
	5 );
	6
	7 INSERT INTO PERSON (NAME,BOSSID) VALUES ('张三',NULL);
	8 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李四',1);
	9 INSERT INTO PERSON (NAME,BOSSID) VALUES ('王五',2);
	10 INSERT INTO PERSON (NAME,BOSSID) VALUES ('赵六',3);
	11 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李七',3);
	12
	13 SELECT P.NAME AS '老板 ' ,B.NAME AS ' 员工'
	14 FROM PERSON AS P
	15 RIGHT JOIN PERSON AS B
	16 ON P.ID = B.BOSSID;