MySQL入门，这一篇就够了(2w字的详细讲解！！！)

一、MySql的简介

1.1 MySql概述

• MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），它是目前最常用的数据库之一。MySQL 使用 SQL（结构化查询语言）来管理和操作数据。
  
• MySQL 提供了各种功能和工具，使其成为许多网站和应用程序的首选数据库解决方案。它具有可靠性、稳定性、高性能、易于使用和扩展性等特点。
  
• MySQL 可以在多个操作系统上运行，并支持许多编程语言，包括 PHP、Python、Java 等。它提供了丰富的功能，包括事务处理、并发控制、数据复制、负载平衡、安全性等。
  

1.2 MySql的历史

MySQL 的历史可以追溯到 1994 年，由瑞士的两位开发者 Michael Widenius （芬兰人，业界通常称呼他“Monty”）和 David Axmark 创建。最初他们开发了一个名为 MySQL的小型数据库项目，用于支持他们的网站应用程序。MySQL其实是以Monty大女儿的名字“My”命名的。

David Axmark和Michael Widenius

MySQL 项目从一开始就很好地考虑到了商务价值，虽然 MySQL 数据库是免费开源的，但为了实现商业化，Monty 与 David Axmark 和 Allan Larsson 很快成立了 MySQL AB 公司，凭借着高效、稳定、可靠的性能和明确的产品定位，加之借着互联网兴起的“东风”，MySQL 一跃成为 IT 世界里的“明星”。

随着互联网的迅速发展，MySQL 获得了更多的关注和用户。它成为许多网站和应用程序的首选数据库，包括一些知名的互联网公司，如Facebook、Twitter、YouTube 等。

MySQL 受欢迎程度越来越高，有不少公司对 MySQL 生出了觊觎之心。2008 年，为了让 MySQL 有更好的发展，Monty 和其他几位创始人决定将 MySQL AB 出售 Sun Microsystems。只是没想到，Sun 公司一年被 Oracle 公司收购，MySQL 成为 了Oracle 公司旗下的一个重要产品，随后Oracle大幅抬高了 MySQL 的商业版价格，但仍然有许多人去使用MySQL，数据显示，直至今日，也有 46.85% 的人仍在使用 MySQL。

二、MySql的安装与卸载

2.1 安装

MySQL详细安装与登录步骤在这

2.2 卸载

如果你想卸载MySQL，也很简单。

右键开始菜单，选择命令提示符(管理员)，打开cmd。

1. 敲入net stop mysql，回车。

2. 再敲入mysqld -remove mysql，回车。

4. 最后删除MySQL目录及相关的环境变量。

至此，MySQL卸载完成！

三、环境配置

3.1 添加环境变量的目的

环境变量里面有很多选项，这里我们只用到Path这个参数。为什么在初始化的开始要添加环境变量呢？

在CMD中输入一个可执行程序的名字，Windows会先在环境变量中的Path所指的路径中寻找一遍，如果找到了就直接执行，没找到就在当前工作目录找，如果还没找到，就报错。我们添加环境变量的目的就是能够在任意一个cmd直接调用MySQL中的相关程序而不用总是修改工作目录，大大简化了操作。

3.2 步骤

1. 右键此电脑→属性，点击高级系统设置
   
2. 点击环境变量

3. 在系统变量中新建MYSQL_HOME

4. 在系统变量中找到并双击Path

5. 点击新建

最后点击确定。

3.3 验证

以管理员身份打开cmd，敲入mysql，回车。

如果提示Can't connect to MySQL server on 'localhost'则证明添加成功；

如果提示mysql不是内部或外部命令，也不是可运行的程序或批处理文件则表示添加添加失败，请重新检查步骤并重试。

四、MySQL的数据类型

MySQL 支持多种类型，大致可以分为三类：

4.1 数值

tinyint : 小整数型，占一个字节
int ： 大整数类型，占四个字节
 eg ： age int
double ： 浮点类型
  使用格式： 字段名 double(总长度,小数点后保留的位数)
  eg ： score double(5,2)

4.2 日期

date ： 日期值。只包含年月日
  eg ：birthday date ： 
datetime ： 混合日期和时间值。包含年月日时分秒

4.3 字符串

char ： 定长字符串。
  优点：存储性能高
  缺点：浪费空间
  eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间
varchar ： 变长字符串。
  优点：节约空间
  缺点：存储性能底
  eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

五、MySQL的数据模型

5.1 关系型数据库

5.1.1 概述

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的 二维表 组成的数据库

如下图，订单信息表 和 客户信息表 都是有行有列的二维表，我们将这样的称为关系型数据库。

5.1.2 关系型数据库的优点

• 都是使用表结构，格式一致，易于维护。
• 使用通用的 SQL 语言操作，使用方便，可用于复杂查询。

• 关系型数据库都可以通过SQL进行操作，所以使用方便。
• 复杂查询。现在需要查询001号订单数据，我们可以看到该订单是1号客户的订单，而1号订单是李聪这个客户。以后也可以在一张表中进行统计分析等操作。

• 数据存储在磁盘中，安全。

5.2 数据模型

如上图，我们通过客户端可以通过数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹。比如可以通过SQL语句创建一个数据库（数据库名称为db1）。

我们可以在数据库安装目录下的data目录下看到多了一个 db1 的文件夹。所以，在MySQL中一个数据库对应到磁盘上的一个文件夹。

而一个数据库下可以创建多张表，我们到MySQL中自带的mysql数据库的文件夹目录下：

而上图中的 db.frm 是表文件，db.MYD 是数据文件，通过这两个文件就可以查询到数据展示成二维表的效果。

小结：

• MySQL中可以创建多个数据库，每个数据库对应到磁盘上的一个文件夹
• 在每个数据库中可以创建多个表，每张都对应到磁盘上一个 frm 文件
• 每张表可以存储多条数据，数据会被存储到磁盘中 MYD 文件中

六、MySQL数据引擎

6.1 概述

• MySQL数据引擎是指MySQL数据库系统用于存储和管理数据的底层软件组件。它负责处理数据的物理存储和读写操作，并提供数据库操作的各种功能和性能特点。
  
• MySQL支持多种不同的数据引擎，每个引擎都有其自身的特点和适用场景。常见的MySQL数据引擎包括InnoDB、MyISAM、MEMORY、NDB等。
  
• 这些数据引擎可以管理表的结构、存储数据、处理事务、提供并发控制、实现崩溃恢复等功能。不同的数据引擎在处理事务、并发性能、崩溃恢复和存储空间等方面具有不同的特点，因此在选择数据引擎时需要考虑应用需求和性能要求。MySQL的核心就是存储引擎
  

6.2 InnoDB(MySQL默认引擎)

InnoDB是事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种隔离级别，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发情形）及外键支持（所有数据库引擎中独一份，仅有它支持外键）该引擎的设计目标便是处理大容量数据的数据库系统，MySQL在运行时InnoDB会在内存中建立缓冲池，用于缓存数据及索引。

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。InnoDB是默认的MySQL引擎。

• 四种隔离级别
  1.读未提交（Read Uncommitted）：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）
  2.读已提交（Read Committed）：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（NonrepeatableRead），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果
  3.可重复读（Repeatable Read）:这是MySQL的默认事务隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
  4.可串行化（Serializable）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争
  

何为脏读，幻读，不可重复读？

• 脏读

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

T2时刻，事务B把原来张三的成绩由原数据80改为了70，此后又被T3时刻的事务A读取到了，但是T4时刻事务B发生异常，进行了回滚操作。这个过程，我们称70为脏数据，事务A进行了一次脏读。

• 不可重复读

不可重复读，有时候也会说成“读已提交”。什么意思呢，就是在一个事务内，多次读取同一个数据，却返回了不同的结果。实际上，这是因为在该事务间隔读取数据的期间，有其他事务对这段数据进行了修改，并且已经提交，就会发生不可重复读事故。

图示中事务A在T1和T4查询同一语句，却得到了不同的结果，这是因为T2~T3时刻事务B对该数据进行了修改，并提交。这个过程，出现了在一个事务内两次读到的数据却是不一样的，我们称为是不可重复读。

不可重复读和脏读的区别：前者是“读已提交”，后者是“读未提交”

• 幻读

幻读是指当事务不独立执行时，插入或者删除另一个事务当前影响的数据而发生的一种类似幻觉的现象。举个例子，某事务在检查表中的数据数count时，是10，过一段时间之后再查是11，这就发生了幻读，之前的检测获取到的数据如同幻觉一样。

出现幻读和不可重复读的原因很像，都是在多次操作数据的时候发现结果和原来的不一样了，出现了其他事务干扰的现象。但是，幻读的偏重点是添加和删除数据，多次操作数据得到的记录数不一样；不可重复读的偏重点是修改数据，多次读取数据发现数据的值不一样了。

事务B向表中新插入了一条数据，事务A在T3时刻后查询数据的时候，突然发现数据和以前查询的时候多出了一项，像产生了幻觉一样。

6.3 MyISAM

• 相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表Insert与update时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈，但MyIsam引擎独立与操作系统，可以在windows及linux上使用。
  
• 不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。
  

缺点：

• 不能在表损坏后恢复数据

适用场景：

• MyIsam极度强调快速读取
• MyIsam表中自动存储了表的行数，需要时直接获取即可
• 适用于不需要事物支持、外键功能、及需要对整个表加锁的情形

6.4 MEMORY（Heap）

• 使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。
  
• HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。
  
• HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。 Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。
  

缺点：

• 要求存储的数据是数据长度不变的格式，Blob和Text类型数据不可用（长度不固定）
  
• 用完表格后表格便被删除
  

适用场景：

• 那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果
  
• 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小
  
• 数据是临时的，而且必须立即能取出用到，于是可存放在内存中
  
• 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系
  

6.5 存储引擎查看

• 查询存储引擎

show engines;#显示可用的数据库引擎和默认引擎

Support列的值表示某种引擎是否能使用

• 查看默认引擎

SHOW VARIABLES LIKE 'default_storage_engine';

• 创建表指定存储引擎

在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

CREATE TABLE `t_mvc_book` (
  `bid` int(11) NOT NULL,
  `bname` varchar(64) DEFAULT NULL,
  `price` float DEFAULT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; #指定存储引擎，例如：MyISAM、InnoDB等等

七、账号管理

7.1.创建用户

MySQL 在安装时，会默认创建一个名为 root 的用户，该用户拥有超级权限，可以控制整个 MySQL 服务器。

7.1.1.创建用户并设置登陆密码

MySQL 提供了以下 3 种方法创建用户。

1. 使用 CREATE USER 语句创建用户
2. 在 mysql.user 表中添加用户
3. 使用 GRANT 语句创建用户

• 使用 CREATE USER 语句创建用户

可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：

CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

参数说明如下：

1. 用户：指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。
   
2. IDENTIFIED BY子句：用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。
   
3. PASSWORD ‘password’：PASSWORD 表示使用哈希值设置密码，该参数可选。
   

示例：

CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';

7.1.2.修改密码

SET PASSWORD 语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。使用 SET 语句修改密码的语法结构如下：

SET PASSWORD = PASSWORD ("rootpwd");

7.2.查看用户

#切换数据库
use mysql;
#查询用户信息
select host,user,authentication_string from user;

MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication_string。

host参数值说明：

7.3.删除用户

使用 DROP USER 语句删除用户的语法格式如下：

DROP USER <用户1> [ , <用户2> ]…

注意：用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象。

7.4.用户授权

在 MySQL 中，拥有 GRANT 权限的用户才可以执行 GRANT 语句，其语法格式如下：

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

参数说明：

• priv_type 参数表示权限类型；
• columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
• database.table 用于指定权限的级别；
• user 参数表示用户账户，由用户名和主机名构成，格式是“‘username’@‘hostname’”；
• IDENTIFIED BY 参数用来为用户设置密码；
• password 参数是用户的新密码。

WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：

• GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
• MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
• MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
• MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
• MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

示例：

GRANT SELECT,INSERT ON *.* TO 'zking'@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;

7.5.用户撤权

删除用户某些特定的权限，语法格式如下：

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中：

• priv_type 参数表示权限的类型；
• column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上；
• user 参数由用户名和主机名构成，格式为“username’@‘hostname’”。

示例1：取消用户的某些权限

REVOKE INSERT ON *.* FROM 'zking'@'localhost'

示例2：取消用户的所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'zking'@'localhost'

7.6.查看用户权限

使用 SHOW GRANTS FOR 语句查看权限。其语法格式如下：

SHOW GRANTS FOR 'username'@'hostname';

其中，username 表示用户名，hostname 表示主机名或主机 IP。

示例：

SHOW GRANTS FOR 'zking'@'localhost';

MySQL5.7

#命令：create user 用户名 identified by '密码';
create user test01 identified by 'test01';
#MySQL5.7
select * from user;
#5.设置权限(Grant)
#语法：grant privileges on databasename.tablename to username@'host';
#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 查询权限
grant SELECT on db_xiaoli.t_p1_user to zs@'%';
#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 修改权限
grant UPDATE on db_xiaoli.t_p1_user to zs@'%';
#给 zs用户 赋予 数据库db_xiaoli中所有表 查询权限
grant SELECT on db_secondhandmarket.* to test01@'%';
#给 zs用户 赋予 数据库db_xiaoli中所有表 所有权限
grant ALL on db_secondhandmarket.* to test01@'%';
6.撤销权限(Revoke)
#语法：revoke privileges on databasename.tablename from username@'host';
#啥也不能回收,不会对GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`有任何影响
revoke DELETE on db_xiaoli.t_p1_user from zs@'%';
#可以回收GRANT SELECT, UPDATE ON `db_xiaoli`.`t_p1_user` TO `zs`@`%`这条权限语句
revoke all on db_xiaoli.t_p1_user from zs@'%';
#可以回收GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`这条赋权语句带来的权限
revoke all on db_secondhandmarket.* from test01@'%';
#注：revoke只能回收grants列表中更小的权限；
SELECT VERSION();

MySQL8

#命令：create user 用户名 identified by '密码';
create user test01;
ALTER USER 'test01'@'%' IDENTIFIED WITH mysql_native_password BY 'test01';
#MySQL8
select * from user;
#5.设置权限(Grant)
#语法：grant privileges on databasename.tablename to username@'host';
#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 查询权限
grant SELECT on db_xiaoli.t_p1_user to zs@'%';
#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 修改权限
grant UPDATE on db_xiaoli.t_p1_user to zs@'%';
#给 zs用户 赋予 数据库db_xiaoli中所有表 查询权限
grant SELECT on db_secondhandmarket.* to test01@'%';
#给 zs用户 赋予 数据库db_xiaoli中所有表 所有权限
grant ALL on db_crm.* to test01@'%';
6.撤销权限(Revoke)
#语法：revoke privileges on databasename.tablename from username@'host';
#啥也不能回收,不会对GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`有任何影响
revoke DELETE on db_xiaoli.t_p1_user from zs@'%';
#可以回收GRANT SELECT, UPDATE ON `db_xiaoli`.`t_p1_user` TO `zs`@`%`这条权限语句
revoke all on db_xiaoli.t_p1_user from zs@'%';
#可以回收GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`这条赋权语句带来的权限
revoke all on db_secondhandmarket.* from test01@'%';
#注：revoke只能回收grants列表中更小的权限；
SELECT VERSION();

八、MySQL的基础操作

8.1 DDL:操作数据库

我们先来学习DDL来操作数据库。而操作数据库主要就是对数据库的增删查操作。

8.1.1 查询

查询所有的数据库

SHOW DATABASES;

运行上面语句效果如下：

上述查询到的是的这些数据库是mysql安装好自带的数据库（除了db1），我们以后不要操作这些数据库。

8.1.2 创建数据库

• 创建数据库：

CREATE DATABASE 数据库名称;

运行语句效果如下：

而在创建数据库的时候，我并不知道db2数据库有没有创建，直接再次创建名为db2的数据库就会出现错误。

为了避免上面的错误，在创建数据库的时候先做判断，如果不存在再创建。

• 创建数据库(判断，如果不存在则创建)

CREATE DATABASE IF NOT EXISTS 数据库名称;

运行语句效果如下：

从上面的效果可以看到虽然db2数据库已经存在，再创建db2也没有报错，而创建db3数据库则创建成功。

8.1.3 删除数据库

• 删除数据库

DROP DATABASE 数据库名称;

• 删除数据库(判断，如果存在则删除)

DROP DATABASE IF EXISTS 数据库名称;

运行语句效果如下：

8.1.4 使用数据库

数据库创建好了，要在数据库中创建表，得先明确在哪儿个数据库中操作，此时就需要使用数据库。

• 使用数据库

USE 数据库名称;

• 查看当前使用的数据库

SELECT DATABASE();

运行语句效果如下：

8.2 DDL:操作表

操作表也就是对表进行增（Create）删（Retrieve）改（Update）查（Delete）。

8.2.1 查询表

• 查询当前数据库下所有表名称

SHOW TABLES;

• 查询表结构

DESC 表名称;

查看mysql数据库中func表的结构，运行语句如下：

8.2.2 创建表

• 创建表

CREATE TABLE 表名 (
  字段名1  数据类型1,
  字段名2  数据类型2,
  …
  字段名n  数据类型n
);

注意：最后一行末尾，不能加逗号

-- 学生表
create table tb_stu(
  id int ,-- 编号
  name varchar(10),-- 姓名
  gender char(1),-- 性别
  birthday date,-- 生日
  score double(5,2) ,-- 分数
  email varchar(64),-- 邮箱
  tel varchar(20),-- 手机号
  status tinyint-- 状态
);

8.2.3 删除表

• 删除表

DROP TABLE 表名;

• 删除表时判断表是否存在

DROP TABLE IF EXISTS 表名;

8.2.4 修改表

• 修改表名
• 关键字 rename

ALTER TABLE 表名 RENAME TO 新的表名;
-- 将表名student修改为stu
alter table tb_stu rename to tb_student;

• 添加一列
• 关键字 add

ALTER TABLE 表名 ADD 列名 数据类型;
-- 给stu表添加一列address，该字段类型是varchar(50)
alter table tb_student add address varchar(50);

• 修改数据类型
• 关键字 modify

ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 将stu表中的address字段的类型改为 char(50)
alter table tb_student modify address char(50);

• 修改列名和数据类型
• 关键字 change

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table tb_student change address addr varchar(50);

• 删除列
• 关键字 drop

ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table tb_student drop addr;

九、约束

上面表中可以看到表中数据存在一些问题：

• id 列一般是用标示数据的唯一性的，而上述表中的id为1的有三条数据，并且 马花疼 没有id进行标示
  
• 柳白 这条数据的age列的数据是3000，而人也不可能活到3000岁
  
• 马运 这条数据的math数学成绩是-5，而数学学得再不好也不可能出现负分
  
• 柳青 这条数据的english列（英文成绩）值为null，而成绩即使没考也得是0分
  

针对上述数据问题，我们就可以从数据库层面在添加数据的时候进行限制，这个就是约束。

9.1 概念

• 约束是作用于表中列上的规则，用于限制加入表的数据

• 例如：我们可以给id列加约束，让其值不能重复，不能为null值

• 约束的存在保证了数据库中数据的正确性、有效性和完整性

• 添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的完整性。

9.2 分类

• 非空约束： 关键字是 NOT NULL

• 保证列中所有的数据不能有null值。
  
• 例如：id列在添加 马花疼 这条数据时就不能添加成功。
  

• 唯一约束：关键字是 UNIQUE

• 保证列中所有数据各不相同。
  
• 例如：id列中三条数据的值都是1，这样的数据在添加时是绝对不允许的。
  

• 主键约束： 关键字是 PRIMARY KEY

• 主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。
  
• 例如：上图表中id就可以作为主键，来标识每条数据。那么这样就要求数据中id的值不能重复，不能为null值。
  

• 检查约束： 关键字是 CHECK

• 保证列中的值满足某一条件
  
• 例如：我们可以给age列添加一个范围，最低年龄可以设置为1，最大年龄就可以设置为300，这样的数据才更合理些。
  

注意：MySQL不支持检查约束。

这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。

• 默认约束： 关键字是 DEFAULT
  保存数据时，未指定值则采用默认值。
  例如：我们在给english列添加该约束，指定默认值是0，这样在添加数据时没有指定具体值时就会采用默认给定的0。
  
• 外键约束： 关键字是 FOREIGN KEY

• 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

9.3 非空约束

• 概念
  非空约束用于保证列中所有数据不能有NULL值
  
• 语法

• 添加约束

-- 创建表时添加非空约束
CREATE TABLE 表名(
   列名 数据类型 NOT NULL,
   …
); 
alter modify

-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

• 删除约束

ALTER TABLE 表名 MODIFY 字段名 数据类型;

9.4 唯一约束

• 概念
  唯一约束用于保证列中所有数据各不相同
  
• 语法

• 添加约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
   列名 数据类型 UNIQUE [AUTO_INCREMENT],
   -- AUTO_INCREMENT: 当不指定值时自动增长
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [约束名称] UNIQUE(列名)
);

-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

• 删除约束

ALTER TABLE 表名 DROP INDEX 字段名;

9.5 主键约束

• 概念

• 主键是一行数据的唯一标识，要求非空且唯一,一张表只能有一个主键

• 语法

• 添加约束

-- 创建表时添加主键约束
CREATE TABLE 表名(
   列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

• 删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

9.6 默认约束

• 概念
  保存数据时，未指定值则采用默认值
  
• 语法

• 添加约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
   列名 数据类型 DEFAULT 默认值,
   …
);

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

• 删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

9.7 约束实例

根据需求，为表添加合适的约束

-- 员工表
CREATE TABLE emp (
  id INT,  -- 员工id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且唯一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);

上面一定给出了具体的要求，我们可以根据要求创建这张表，并为每一列添加对应的约束。建表语句如下：

DROP TABLE IF EXISTS emp;
-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

通过上面语句可以创建带有约束的 emp 表，约束能不能发挥作用呢。接下来我们一一进行验证，先添加一条没有问题的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

• 验证主键约束，非空且唯一

INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

执行结果如下：

从上面的结果可以看到，字段 id 不能为null。那我们重新添加一条数据，如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

执行结果如下：

• 验证非空约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，ename 字段的非空约束生效了。

• 验证唯一约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，ename 字段的唯一约束生效了。

• 验证默认约束

INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

执行完上面语句后查询表中数据，如下图可以看到王五这条数据的bonus列就有了默认值0。

注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。

如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);

执行完上面语句后查询表中数据，如下图可以看到赵六这条数据的bonus列的值是null。

• 验证自动增长： auto_increment 当列是数字类型 并且唯一约束

重新创建 emp 表，并给id列添加自动增长

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

接下来给emp添加数据，分别验证不给id列添加值以及给id列添加null值，id列的值会不会自动增长：

INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

9.8 外键约束

• 概述

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

如何理解上面的概念呢？如下图有两张表，员工表和部门表：

员工表中的dep_id字段是部门表的id字段关联，也就是说1号学生张三属于1号部门研发部的员工。现在我要删除1号部门，就会出现错误的数据（员工表中属于1号部门的数据）。而我们上面说的两张表的关系只是我们认为它们有关系，此时需要通过外键让这两张表产生数据库层面的关系，这样你要删除部门表中的1号部门的数据将无法删除。

• 语法

1. 添加外键约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
);

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

2. 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

• 实例

根据上述语法创建员工表和部门表，并添加上外键约束：

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
-- 部门表
CREATE TABLE dept(
  id int primary key auto_increment,
  dep_name varchar(20),
  addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int,
  -- 添加外键 dep_id,关联 dept 表的id主键
  CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)  
);

添加数据

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

此时删除 研发部 这条数据，会发现无法删除。

删除外键

alter table emp drop FOREIGN key fk_emp_dept;

重新添加外键

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

好啦，本篇博客就到此为止啦！希望你看完本篇文章有所收获，祝你变得更强！！!,下篇博客继续介绍MySql😊