前端学习mysql的详细图文记录

我是直接官网下载安装包的，选择的是社区版5.7.27，你可以自行去下载自己要的版本，5.7版下载地址：传送门。

安装

下载安装包后直接双击安装，一路回车，需要注意的就是当安装好后会有一个弹窗提示，这样的：

红框里的是mysql默认登录密码，需要保存。如果忘记保存，在右侧的系统通知栏里也会有，如图：rQn2d1Xr%L9*

启动

安装完毕，打开系统偏好设置，最下方会有一个mysql设置，点击进去启动mysql：

设置环境变量

为了方便在命令行操作mysql，需要先设置一下环境变量。

1. 进入目录/usr/local/mysql/bin/，查看是否有mysql（默认安装的路径）
2. 执行vim ~/.bash_profile
3. 在 .bash_profile文件中添加PATH=$PATH:/usr/local/mysql/bin语句，然后在英文输入法下按 esc，然后输入 wq 保存退出
4. 最后在命令行输入source ~/.bash_profile

修改mysql登录密码

默认密码不方便记，修改自己的密码，由于已经设置过环境变量，所以直接执行以下命令：

mysqladmin -u root -p 123456

// 123456 是我设置的新密码，替换成你想要设置的密码
// 回车后会提示你输入密码，这个密码就是上面说的安装后默认给的密码

输入密码后没报错就是修改密码成功了，但也可能报下面这个错误：

如果出现此错误，我的做法是用默认密码登录一次mysql，然后再设置新密码，步骤：

1. 执行 mysql -uroot -p，这个是登录命令，回车后输入默认密码
2. 登录成功后，执行 SET PASSWORD = PASSWORD('123456');设置新密码

此时新密码也设置成功了。

登录mysql

修改密码后，用新密码登录测试以下，登录命令是：

mysql -uroot -p

回车后输入新密码即可。

登录的两种方法

1. 命令行执行mysql -uroot -p，然后回车输入密码
2. 或者直接执行 mysql -uroot -p123456，123456是我的密码，替换成你的密码

退出的三种方法

在mysql交互界面输入 exit 或输入 quit或输入\q，回车

常规创建数据库

执行以下命令：

CREATE DATABASE db_name;
或者
CREATE SCHEMA db_name;

// db_name 是数据库名，不能包含特殊字符和 MYSQL 关键字，最好是有意义的。

注意：sql语句是可以小写的，如：create database db_name。后面我为了区分自定义的库名和表名，所有sql命令都使用大写。

例子：

注意：sql语句后要以 `;` 结尾，或者 `\g`结尾

如果数据库名非要使用 MYSQL 关键字命名，可以使用 `` 来包括，例如创建一个名为database的数据库：

检测库名创建数据库

如果重复创建数据库是会报错的，如：

如果不想报错，可以带检测库名的去创建数据库，命令：

CREATE DATABASE IF NOT EXISTS db_name;

此时重复创建就不会报错了，而是给出警告：

查看警告，可执行命令：SHOW WARNINGS;：

指定编码创建数据库

在创建数据库时可以同时指定编码，使用命令：

CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset;

// [] 中是可选语句，charset 是你要指定的编码类型，如‘UTF8’,'GBK'等

例子：

要想修改数据库的编码，可使用命令：ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset; // charset 是要修改的编码方式

查看数据库

创建数据库后，需要查询操作。

查看当前服务器下全部数据库

SHOW DATABASES;
或者 SHOW SCHEMAS;

查看指定数据库的详细信息

SHOW CREATE DATABASE db_name;

例子：

使用数据库

当要具体操作一个数据库时，比如插入数据，需要先使用这个数据库，使用命令：

USE db_name;

此时就可以针对当前这个数据库进行操作了。而有时可能会忘记了当前使用的是哪个数据库，那么可以使用下面这个命令来查看：

SELECT DATABASE();
或者 SELECT SCHEMA();

删除数据库

删除数据库使用命令：

DROP DATABASE [IF EXISTS] db_name;

例子：

数值类型

数值类型又可分为： 整数型和浮点型。

整数型

浮点型

字符串类型

日期类型

这些类型都会在后面使用到，并且文章后面还有一个 补充 章节，会介绍一些没用到的类型的使用。

创建数据表

创建数据表需按照下面的方式创建：

CREATE TABLE [IF NOT EXISTS] tbl_name(
    字段名称 字段类型 [完整性约束条件],
    字段名称 字段类型 [完整性约束条件],
    ...
)[ENGINE=存储引擎 CHARSET=编码方式];

来看一下各个点的含义：

• tbl_name：表示数据表名称，不要包含特殊字符和 MYSQL 关键字，最好是有意义的；
• 字段名称：不用多说，就是我们前端经常从接口里获取的字段；
• 字段类型：即字段的数据类型，前面已经列举了；
• 完整性约束条件：可加可不加，后面会详细说，暂时不管；
• 存储引擎：mysql有两种存储引擎，分别是InnoDB和MyISAM。当不指定存储引擎时，默认是InnoDB。两种存储引擎的区别由于我也不是很熟，所以就不说了，默认的即可；
• 编码方式：一般设置为UTF8，默认也为UTF8。

现在来尝试创建一个表：

CREATE TABLE IF NOT EXISTS user(
    id INT,
    username VARCHAR(20),
    password CHAR(32),
    email VARCHAR(50),
    addr VARCHAR(100),
    sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;

将这段代码拷贝到mysql命令行：

会发现没有成功，报错 No database selected。这是因为表要建立在数据库里，所以我们要先创建一个数据库，并使用这个数据库，如下：

-- 创建数据库 user_demo（后面的例子都使用这个数据库）
CREATE DATABASE IF NOT EXISTS user_demo DEFAULT CHARACTER SET = UTF8;

-- 使用数据库
USE user_demo;

再次执行上面的创建表操作就可以了：

查看数据表

查看当前数据库下已有数据表使用命令：

SHOW TABLES;

可以看到第一行提示 Tables_in_user_demo，表示这是在数据库user_demo里的所有数据表。所以查看数据表之前也需要使用 USE 命令先打开某一个数据库。

查看数据表也可以不用先打开数据库，使用如下命令：

SHOW [FULL] TABLES [{FROM | IN} db_name];

除了这个命令外，其他对于表的操作都需要先 USE 一下某数据库。

查看指定数据表的详细信息

查看某一个数据表的详细信息，使用命令：

SHOW  CREATE TABLE tbl_name;

查看表结构

查看表结构有以下三种命令方式，效果都是一样的：

DESC tbl_name;

DESCRIBE tbl_name;

SHOW COLUMNS FROM tbl_name;

删除指定的数据表

删除命令：

DROP TABLE [IF EXISTS] tbl_name;

什么是完整性约束？为什么使用？

完整性约束是为了防止不合规范的数据进入数据库。当建表时指定了约束条件，那么用户在进行增删改等操作时会自动按照约束条件进行检查，从而让不合规范的数据不能进入到数据库，保证数据的完整和一致性，也能提高程序运行效率，降低程序复杂性。

约束类型

常用约束条件如下：

• UNSIGNED：无符号，没有负数，从0开始，应用于数值类型。
• ZEROFILL：0填充，当数值类型的数据长度不够时，使用前补0的方式填充至指定长度，自动添加 UNSIGNED。
• NOT NULL：非空约束，插入值的时候这个字段必须要给值，值不能为空值。
• DEFAULT：默认值，如果插入记录的时候没有给字段赋值，则使用默认值。
• PRIMARY KEY：主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空。
• UNIQUE KEY：唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但NULL值除外。
• AUTO_INCREMENT：自动增长，用于数值列，默认从1开始。
• FOREIGN KEY：外键约束（外键部分本文先不涉及）。

测试 UNSIGNED 和 ZEROFILL

UNSIGNED 和 ZEROFILL 主要应用在整型上，下面以创建整型数值举例。

UNSIGNED 无符号约束

建立如下数据表：

CREATE TABLE test_unsigned(
    a tinyint,
    b tinyint UNSIGNED
);

a 和 b 都是 tinyint 类型，b 加上了无符号约束，即不可为负数。现在插入第一组数据：

INSERT test_unsigned(a,b) VALUES(-15,-15)

-- 提前认识以下插入数据的命令，本章节会一直使用此条命令，后面会详细说添加数据相关操作
-- 字段名称 跟 值 一一对应
INSERT tbl_name(字段名称,...)VALUES(值,...)

很明显数据没有插入成功，因为b被约束不可为负数，但插入的值是负数，所以提示 b 超出了范围。

现在把 b 改为正数：

INSERT test_unsigned(a,b) VALUES(-15,15);

这次就成功插入了数据。

ZEROFILL 零填充约束

建立如下表格：

CREATE TABLE test_int(
    a tinyint ZEROFILL,
    b smallint ZEROFILL,
    c mediumint ZEROFILL,
    d int ZEROFILL,
    e bigint ZEROFILL
);

表中所有字段是不同类型的整型，他们的区别是值的范围，表现效果上就是显示的宽度不一样，可以看看它们的宽度：

图中红线框住的就是默认宽度，注意宽度指的是显示长度，不是存储长度。tinyint的默认宽度本来应该是4，因为它的值范围最小是 -128，加上-就是4位了，但是加上ZEROFILL约束会自动加上无符号约束，所以现在宽度是3，其他的整型同理。

来插入一组数据：

INSERT test_int(a,b,c,d,e) VALUES(1,1,1,1,1);

如图所示，我们插入的数值都是1，但是结果都前补0了，使得宽度达到默认宽度。这就是加上 ZEROFILL 约束后的效果，当数据的长度不够它的默认宽度时，会前补0填充至默认宽度。

其实宽度是可以自定义的，比如：

CREATE TABLE test_int1(
    a tinyint(2),
    b tinyint(2)
);

但其实此时插入的数据长度并没有被限制，如：

INSERT test_int1(a,b) VALUES(12,123);

可见，b的长度已经超过了设定的宽度，但依旧可以被插入，只要没有超过类型的存储长度就行。

测试非空约束 NOT NULL

创建如下数据表：

-- 给b字段加上非空约束

CREATE TABLE test_not_null(
    a VARCHAR(20),
    b VARCHAR(20) NOT NULL
);

查看表结构，可以看到字段b是不允许为 NULL 的。

现在再依次插入以下数据：

-- 注意 '' 不是空值，不等于 null，所以可以插入此条数据
INSERT test_not_null(a,b) VALUES('','');

-- Column 'b' cannot be null. b不可以为null，此条数据插入报错
INSERT test_not_null(a,b) VALUES(NULL,NULL);

-- a 字段没有限制不可为空，所以此条数据插入插入成功
INSERT test_not_null(a,b) VALUES(NULL,'b');

-- b 没有设置默认值，也没有插入数据，因不能为空，所以此条数据插入报错
INSERT test_not_null(a) VALUES('a');

来看一下结果：

可见只成功插入了第一条和第三条数据。这么看可能不明显，我们用 * 来标记下：

-- 在字段及数据两边加上 * 显示
SELECT CONCAT('*',a,'*'),CONCAT('*',b,'*') FROM test_not_null;

测试默认值 DEFAULT

创建表格如下：

CREATE TABLE test_default(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 18
);

可以看到age字段有我们设置的默认值18。现在依次插入几条数据：

-- 可以只插入 username 数据，age 有默认值，id是自增的主键无需主动插入值，后面会说
INSERT test_default(username) VALUES('A');

-- age 的默认值可以被覆盖
INSERT test_default(username,age) VALUES('B',25);

-- 没有限制不可为空，age 可以设置为空值 NULL
INSERT test_default(username,age) VALUES('C',NULL);

结果：

测试主键 PRIMARY KEY

建立数据表如下：

-- 设置字段 id 为主键
CREATE TABLE test_primary_key(
    id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(20)
);

来依次插入下面数据：

-- 正常数据插入
INSERT test_primary_key(id,username) VALUES(1,'aaa');

-- 主键值重复，报错Duplicate entry '1' for key 'PRIMARY'
INSERT test_primary_key(id,username) VALUES(1,'aaa123');

-- 主键没有默认值，不可不插值，报错 Field 'id' doesn't have a default value。
-- id 加上 AUTO_INCREMENT 或 DEFAULT 时，可以只插入username值
INSERT test_primary_key(username) VALUES('bbb');

主键可以简写成 KEY，如：

-- 简写成 KEY 表示主键
CREATE TABLE test_primary_key1(
    id INT UNSIGNED KEY,
    username VARCHAR(20)
);

还可以这么写：

-- 使用PRIMARY KEY() 的方式添加主键
CREATE TABLE test_primary_key2(
    id INT UNSIGNED,
    username VARCHAR(20),
    PRIMARY KEY(id)
);

一个表中只能有一个主键，如果设置两个主键会报错：

图中创建表，设置了两个主键，创建不成功。

虽然不能设置多个主键，但是可以设置复合主键，如下：

-- 使用 PRIMARY KEY() 方式添加复合主键
CREATE TABLE test_primary_key4(
    id INT UNSIGNED,
    userid INT UNSIGNED,
    username VARCHAR(20),
    PRIMARY KEY(id,userid)
);

可以看到id和userid都属于主键。来插入一条值试试：

INSERT test_primary_key4(id,userid,username) VALUES(1,1,'aaa');

可以看到插入成功了，说明同一条数据的复合主键值相同是不会冲突的，再试试：

INSERT test_primary_key4(id,userid,username) VALUES(1,2,'aaa');

结果也是可以的，并不会因为id字段有 1 这个值了而不能再插值 1，主要这里要联系 userid一起看，现在再插入一次相同的值试试，即id=1 userid=2：

这次没有成功，因为 1-2 组合已经有了，应该是很好理解的。

测试唯一性 UNIQUE KEY

建立数据表如下：

-- username 和 password 都加上唯一性约束，UNIQUE KEY 可以简写为 UNIQUE
CREATE TABLE test_unique(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    password VARCHAR(20) UNIQUE
);

依次插入下列数据：

-- 正常数据插入成功
INSERT test_unique(username,password) VALUES('A','12345');

-- 字段username插入相同的值，因唯一性约束，报错 Duplicate entry 'A' for key 'username'。
INSERT test_unique(username,password) VALUES('A','12345678');

-- 唯一性约束可以插入 NULL 值
INSERT test_unique(username,password) VALUES('B',NULL);

-- 唯一性约束不包括对 NULL 值的约束
INSERT test_unique(username,password) VALUES('C',NULL);

测试自动增长 AUTO_INCREMENT

上面的例子已经用过自动增长了，这里再详细说下。建立数据表如下：

CREATE TABLE test_auto_increment(
    id INT UNSIGNED KEY AUTO_INCREMENT,
    username VARCHAR(20)
);

自动增长顾名思义就是无需我们主动插值，会从 1 开始自动增长，用于数值列。例子：

-- 只添加 username 的值，测试 id 是否自动增长
INSERT test_auto_increment(username) VALUES('A');
INSERT test_auto_increment(username) VALUES('B');
INSERT test_auto_increment(username) VALUES('C');

可以看到，id 的值是从 1 开始自动增长的。

添加和删除字段

对已存在的数据表，我们可以执行添加和删除字段的操作。命令如下：

-- 添加字段
ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束条件] [FIRST | AFTER 字段名称]

-- 删除字段
ALTER TABLE tbl_name DROP 字段名称

添加字段

为了演示，新创建一个数据表，只包含id字段：

CREATE TABLE IF NOT EXISTS user1(
    id INT UNSIGNED AUTO_INCREMENT KEY
);

现在分别添加两条数据：

-- 添加用户名字段 username，类型是 VARCHAR(20)
ALTER TABLE user1 ADD username VARCHAR(20);

-- 添加用户密码字段 password，类型是 VARCHAR(32)，不可为空
ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL;

结果：

使用 AFTER 控制添加的字段在指定字段后面，比如添加邮箱字段email在username后面：

-- 添加 email 字段，类型 VARCHAR(50)，约束 NOT NULL UNIQUE，在username后面
ALTER TABLE user1 ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;

结果：

使用 FIRST 控制字段添加在第一行，比如：

-- 添加测试字段 test TINYINT(1) NOT NULL DEFAULT 0;加到首位
ALTER TABLE user1 ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;

结果：

删除字段

删除操作就比较简单，删掉测试字段 test：

ALTER TABLE user1 DROP test;

结果：

还可以把多个添加和删除操作一起执行，比如：

-- 添加age addr 字段 ，删除email字段
ALTER TABLE user1 
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP email;

结果：

添加和删除默认值

对已有字段，我们可以执行添加和删除默认值操作。命令如下：

-- 添加默认值 (折行显示是为了便于观看)
ALTER TABLE tbl_name
ALTER 字段名称 SET DEFAULT 默认值;

-- 删除默认值 (折行显示是为了便于观看)
ALTER TABLE tbl_name
ALTER 字段名称 DROP DEFAULT;

添加默认值

为了演示，新建数据表如下：

CREATE TABLE user2(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED NOT NULL DEFAULT 18,
    email VARCHAR(50) NOT NULL
);

给email字段添加默认值 12345@qq.com：

ALTER TABLE user2 ALTER email SET DEFAULT '12345@qq.com';

结果：

删除默认值

删除上表中的age字段的默认值：

ALTER TABLE user2 ALTER age DROP DEFAULT;

结果：

修改字段类型和字段属性

我们可以修改字段的类型和属性，命令如下：

-- (折行显示是为了便于观看)
ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [字段属性][FIRST | AFTER 字段名称]

新建一个数据表：

CREATE TABLE user3(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(5) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    email VARCHAR(10) NOT NULL
);

此时表结构是这样的：

现在依次做以下字段修改：

-- (折行显示是为了便于观看)

-- 将 username 修改为 VARCHAR(20) 类型
ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL;

-- 将 password 长度修改为40
ALTER TABLE user3
MODIFY password CHAR(40) NOT NULL;

-- 将 email 字段改为 VARCHAR(50) NOT NULL FIRST
ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;

结果：

可见修改成功了。

修改字段名称，字段类型和字段属性

上面说了修改字段类型和字段属性，如果要修改字段名称，就要用关键字CHANGE了，命令如下：

ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]

在表user3上依次执行如下操作：

-- 将 username 改为 user   
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL;

-- 将 password 改为 pwd
ALTER TABlE user3
CHANGE password pwd CHAR(40) NOT NULL;

-- 将 email 改成userEmail 类型改成 VARCHAR(100) DEFAULT 'imooc@qq.com'
ALTER TABLE user3
CHANGE email userEmail VARCHAR(100) DEFAULT 'imooc@qq.com';

结果：

添加和删除主键

便于演示，建立新表：

CREATE TABLE user4(
    id INT UNSIGNED,
    username VARCHAR(20) NOT NULL
);

此时表中没有主键的：

接下来对此表进行添加和删除主键操作。

添加主键

使用命令：

ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称);

例如给字段 id 加上主键：

ALTER TABLE user4
ADD PRIMARY KEY(id);

现在再来看看表结构：

可见添加主键成功。

删除主键

使用命令：

ALTER TABLE tbl_name DROP PRIMARY KEY;

删除表user4中的主键：

ALTER TABLE user4 DROP PRIMARY KEY;

删除主键成功。

删除有自动增长约束的主键

主键有时是跟自动增长配合使用的，所以要删除带有自动增长的主键前，要先去除自动增长的约束。举例说明，先建个表：

-- id 是主键，也有自动增长约束
CREATE TABLE user5(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL
);

如果此时删除主键是会失败的，如下：

去除id的自动增长约束，可以使用上面说到的修改字段属性方法：

ALTER TABLE user5 MODIFY id INT UNSIGNED;

现在就可以正常删除主键了：

ALTER TABLE user5 DROP PRIMARY KEY;

添加和删除唯一索引

建新表：

CREATE TABLE user6(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL
);

此时表中username字段含有唯一性约束：

删除唯一索引

使用命令：

ALTER TABLE tbl_name DROP INDEX 唯一索引名（默认是字段名）

如删除表user6中的唯一索引username：

ALTER TABLE user6 DROP INDEX username;

如图，唯一索引已经成功删除。

添加唯一索引

使用命令：

ALTER TABLE tbl_name ADD UNIQUE KEY(字段名称);

或者使用：

ALTER TABLE tbl_name ADD UNIQUE 自定的索引名(字段名称);

分别使用两种方法给username和password字段加上唯一性约束：

-- 使用字段名作为索引名
ALTER TABLE user6 ADD UNIQUE KEY(username);

-- 自定义索引名，定义为 pass
ALTER TABLE user6 ADD UNIQUE pass(password);

查看索引名是否符合预期，使用命令SHOW CREATE TABLE user6;，结果：

添加唯一索引成功。

修改数据表名称

修改表名，使用命令：

ALTER TABLE tbl_name RENAME TO new_tbl_name;

-- 或者使用 AS 关键字
ALTER TABLE tbl_name RENAME AS new_tbl_name;

将表 user6 改名为 user666：

再将 user666 改回 user6：

修改 AUTO_INCREMENT 的值

AUTO_INCREMENT是自增长的值，使用命令SHOW CREATE TABLE tbl_name可以查看此值。有时候当我们删除表中数据时，自增值是不会重置的，比如常用的id字段，哪怕删除了前面的数据，id还是在原基础上自增的，要是想修改此值，可以使用命令：

ALTER TABLE tbl_name AUTO_INCREMENT=值;

此命令可以先了解，有个印象，需要用到的时候自然就明白了。

添加（插入）数据

插入数据操作在之前说过一个，即：

INSERT tbl_name(字段名称,...)VALUES(值,...);

现在再来补充说明一些操作。避免混淆，也为了复习前面的知识，我们新建一个数据库，并在库中建一个表：

-- 新建数据库 data_demo
CREATE DATABASE IF NOT EXISTS data_demo DEFAULT CHARACTER SET 'UTF8';

-- 使用新数据库
USE data_demo;

-- 新建数据表 user
CREATE TABLE IF NOT EXISTS user(
    id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
    email VARCHAR(50) NOT NULL DEFAULT '123@qqcom' COMMENT '邮箱'
)ENGINE=INNODB CHARSET=UTF8;

整体复制上面语句粘贴到命令行中执行，建库建表成功，查看数据表：

不指定字段名称插入数据

我们不需要指定每一个字段名，可以按字段顺序依次插入数据：

-- 在 VALUE 后面的括号中依次写入数据，按照表中字段的顺序
-- 也可以使用关键字 VALUES
INSERT tbl_name VALUE(value...)

测试在user中插入一条数据：

INSERT user VALUE(1,'瓶子',18,'000@qq.com');

结果：

插入成功，数据正确对应到字段上。由于id字段是自动增长的，所以可以不指定具体数值，写法如下：

-- 关键字可以用 VALUES

-- 使用 NULL 填充
INSERT user VALUES(NULL,'tom',20,'123@qq.com');

-- 使用 DEFAULT 填充
INSERT user VALUES(DEFAULT,'lily',16,'163@qq.com');

可以看到依旧成功插入数据，且id是自动增长的。

指定字段插入数据

这种就是之前说过的：

INSERT tbl_name(字段名称,...) VALUES(值,...);

由于user表中，id是自动增长，age 和 email 都是有默认值的，所以我们可以只指定 username 来插入数据：

INSERT user(username) VALUES('a');

结果：

一次插入多条数据

有时候需要一次操作插入多条数据，可以使用命令：

-- 字段名称可以不写
INSERT tbl_name[(字段名称,...)] VALUES(值,...),
(值,...),
(值,...);

我们一次插入三条数据试试：

-- 注意标点符号
INSERT user(id,username,age,email) VALUES(NULL,'b',22,'b@qq.com'),
(NULL,'c',23,'c@qq.com'),
(NULL,'d',24,'d@qq.com');

结果：

INSERT ... SET 的形式插入数据

命令：

INSERT tbl_name SET 字段名称=值,字段名称=值,...;

测试插入一条数据：

INSERT user SET username='abc';

INSERT ... SELECT 的形式插入数据

此种形式是可以将 别的表中查到的数据 插入 到当前表中，命令如下：

INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name2;

我们可以测试下，先建一个数据表，并插入一些数据：

-- 建表
CREATE TABLE test(
    a VARCHAR(10)
);

-- 插入三条数据
INSERT test VALUES('AA'),('BB'),('CC');

上述语句执行完之后，我们来尝试使用INSERT...SELECT方式给user插入数据：

INSERT user(username) SELECT a FROM test;

结果：

至此，你掌握了好几种插入数据的方式。

修改数据

修改数据的命令很简单：

UPDATE tbl_name SET 字段名称=值,字段名称=值,...[WHERE 条件]

其中，WHERE是条件，修改数据时如果不指定条件，会将表中所有数据都修改了，所以使用条件是很必要的，后面会详细说到 WHERE。

在修改数据前，我们先看看 user 表中先有的数据：

我们来修改第一条数据的年龄，将 18 修改为 20，此条数据的唯一标识就是id字段，所以条件是 id=1：

UPDATE user SET age=20 WHERE id=1;

如果我们不指定 WHERE，那么将修改所有的数据，比如：

UPDATE user SET age=100;

删除数据

删除数据的命令为：

DELETE FROM tbl_name [WHERE 条件];

同样，删除操作也应该加上条件，不然就删除了所有的数据，所以删除需谨慎，一定要先写好条件。

删除 id=1 的数据：

DELETE FROM user WHERE id=1;

可以看到 id=1 的数据被删除了。

删除表中所有的数据：

DELETE FROM user;

此时，表中所有数据被清空。这时候有一个需要注意的地方，那就是所有数据被清空后，AUTO_INCREMENT 的值是不会被重置的，执行命令 SHOW CREATE TABLE user 结果如下：

可以看到即使数据被清空，AUTO_INCREMENT 的值还是保持之前的，再添加的数据的 id 就是 13。在上面表结构章节中最后说到了怎么重置此值，可以往上查阅，这里来重置一下值为 1：

ALTER TABLE user AUTO_INCREMENT=1;

彻底清空数据表

还有一个命令干脆直接，直接清空所有数据，并重置 AUTO_INCREMENT 的值，方法：

TRUNCATE [TABLE] tbl_name;

由于上面清空来数据表user，所以再重新添加几条数据（不要怕麻烦，一步步实践加深印象）

-- 添加几条数据
-- 再强调一遍，自增值可以使用 NULL 或 DEFAULT 填充；默认值可以使用 DEFAULT 代替
INSERT user VALUES(NULL,'AA',18,DEFAULT),(NULL,'BB',19,DEFAULT),(NULL,'CC',20,DEFAULT);

查看表结构，SHOW CREATE TABLE user：

此时，数据有了，AUTO_INCREMENT 值为4，再使用 TRUNCATE 来清空所有：

TRUNCATE user;

结果：

查询数据

查询命令 SELECT，前面我们一直有在使用，其实查询操作是灵活多变的，它的语法结构可以总结如下：

-- 这是一个总结性的语法结构，当你需要使用时可以参考，[] 表示可选。后面每一个细节都会说到。
SELECT 表达式(或字段),... FROM tbl_name
[WHERE 条件] -- 条件
[GROUP BY {字段|位置} HAVING 二次筛选]  -- 分组
[ORDER BY {字段|位置|表达式}[ASC|DESC]] -- 排序
[LIMIT 限制结果集的显示条数] -- 限制显示条数

使用 * 查询所有字段

这也是我们前面一直用的命令：

SELECT * FROM tbl_name;

这里也就不多说了，补充一个知识点就是可以直接查询某库中的某表：

SELECT * FROM db_name.tbl_name;

比如我们查询 data_demo 数据库中的 user 表，就可以这样：

-- 注意你的库名和表名可能和我的不一样，如果一步步跟我步骤操作的，应该是一样的
SELECT * FROM data_demo.user;

注意：我提前在user表中插入了数据，如果你跟我步骤做的，记得也提前插入几条测试数据。查询结果： 这样查询的好处就是不需要先 USE 数据库。

查询指定字段的信息

使用命令：

SELECT 字段名称,... FROM tbl_name;

例如：

SELECT username,age FROM user;

给字段起别名查询显示

命令：

SELECT 字段名称 [AS] 别名名称,... FROM [db_name.]tbl_name;

例如：

SELECT id AS '编号', username AS '用户名',email AS '邮箱' FROM user;

给数据表起别名查询

命令：

SELECT 字段名称,... FROM tbl_name [AS] 别名;

例如：

SELECT id,username,email FROM user AS a;

给表名起别名在这里看不出啥特别的效果，先记住有这种方法。

表名.字段名 查询

命令：

SELECT tbl_name.col_name,... FROM tbl_name;

例如：

SELECT user.id,user.username FROM user;

同样看不出有啥特别的效果，先记住有这种方法。

查询数据之 WHERE 详解

WHERE 属于查询操作的重要关键字，这里单独一章节说明。WHERE会筛选出符合条件的数据，使用它的方式如下：

SELECT 字段名或表达式,... FROM tbl_name WHERE 条件

其中条件是有几种方式的。

通过比较运算符来筛选数据

前面说到的WHERE id=1;就是运用的比较运算符，可以用的比较运算符有：>、>=、<、<=、!=、<> 和 <=>，前面五个不用多说，我主要来说说后面这两个。

<>是不等于的意思，跟!=作用是一样的；<=>是等于的意思，跟=作用类似，但有一个区别就是<=>可以检测 NULL 值。举个例子，我们先改造user表的数据如下：

即为了测试增加两个NULL值，我使用了更新命令UPDATE user SET age=NULL WHERE id>2;，请根据自己实际情况改造数据。现在分别使用 = 和 <=> 来查询：

-- 使用 =
SELECT * FROM user WHERE age=NULL;

-- 使用 <=>
SELECT * FROM user WHERE age<=>NULL;

结果：

可见使用 <=> 可以检测到 NULL 值，其实检测 NULL 值还有另外一个方法，就是使用 IS [NOT] NULL，如下：

指定范围来筛选数据

我们也可以对某个字段指定值的范围来筛选数据，语法如下：

WHERE 字段名 [NOT] BETWEEN ... AND ... 

比如我们筛选 id 值在 2 和 4 的数据：

SELECT * FROM user WHERE id BETWEEN 2 AND 4;

从结果可以看出，查询结果包含了首位和末尾的数据。

指定集合来筛选数据

指定集合查询数据，将在集合内的值筛选出，语法：

WHERE 字段名 [NOT] IN(值,...);

比如查 id 值为 1 和 3 的数据：

SELECT * FROM user WHERE id IN (1,3);

再比如我们查 username 值为 BB 和 YY 的数据：

SELECT * FROM user WHERE username IN('BB','YY');

没有 username=YY 的值，所以只返回 username=BB 的数据。

使用逻辑运算符筛选数据

可以使用两个逻辑运算符：

• AND 逻辑与
• OR 逻辑或

举例：

-- 查询 id>3 并且 age>20 的数据
SELECT * FROM user WHERE id>3 AND age>20;

-- 查询 id<3 或者 age>20 的数据
SELECT * FROM user WHERE id<3 OR age>20;

结果：

此处仅演示作用，可自行建立数据更丰富的表来测试。

模糊查询

模糊查询很有用，它的语法很简单：

WHERE 字段名 [NOT] LIKE ...

它通常要结合占位符来使用：

• % 表示任意长度的字符串
• _ 表示任意一个字符

查询 username 值中含字母 B 的:

SELECT * FROM user WHERE username LIKE '%B%';

从结果知道，查询时是不区分大小写的。

再查询 username 值长度为 4 的：

SELECT * FROM user WHERE username LIKE '____';

查询数据之 GROUP BY 分组

分组是把值相同的放到一个组中，语法如下：

GROUP BY 字段名

为了演示方便，我新建一个表并插入数据：

CREATE TABLE user1(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 18,
    sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;

INSERT user1 VALUES(NULL,'张三',45,'男'),
(NULL,'李四',18,'男'),
(NULL,'王五',24,'保密'),
(NULL,'小丽',24,'女'),
(NULL,'小红',18,'女');

现在按照年龄来分组：

SELECT * FROM user1 GROUP BY sex;

结果却报错了：

这里的错误意思是说，要查询的列名必须出现在GROUP BY后面，由于我们查所有的列，分组是按照sex来的，所以报错了。另外这个错误跟mysql版本有关系，可能你安装的版本不会有这个错误，如果有错误，可以修改语句：

SELECT sex FROM user1 GROUP BY sex;

现在就查询成功了。如果就想查询很多列也是有办法的，自行搜索关键词ERROR 1055 (42000)，网上有详细的说明，我就不多说了。再来看看搜索结果，只显示了组中的一条记录，这样很不直观，我们需要每个组中具体有哪些数据，这时候就利用GROUP_CONCAT()这个函数来解决。

分组配合 GROUP_CONCAT() 函数使用

为了查看每个分组中的具体数据详情，我们需要使用 GROUP_CONCAT()函数。 比如我们想要知道每一组中所有的username详情，可以这样写：

SELECT GROUP_CONCAT(username),sex FROM user1 GROUP BY sex;

这样就能清晰的知道了每个分组里的情况，同时发现使用GROUP_CONCAT()函数后，上面的那个限制错误就不会出现了，所以可以查看所有详情：

SELECT GROUP_CONCAT(id),
GROUP_CONCAT(username),
GROUP_CONCAT(age),
sex FROM user1 GROUP BY sex;

分组配合聚合函数使用

聚合函数包括：

• COUNT() 统计总数
• SUM() 求和
• MAX() 求最大值
• MIN() 求最小值
• AVG() 求平均值

需要注意的是聚合函数不是只能用在分组操作中的，只是这里讲分组时可以配合一起用。后续的进阶篇会详细说mysql中的函数操作

举个例子，按照 sex 分组，查看username详情，并且得到每个分组中的总人数，可以这样写：

-- 使用 COUNT(*) 统计分组数据总数
SELECT GROUP_CONCAT(username),sex,COUNT(*) FROM user1 GROUP BY sex;

也可以直接在COUNT()函数中传入字段名，如COUNT(username)，结果也能实现：

区别在于：使用 COUNT(*) 可以识别出 NULL 数据，而使用 COUNT(字段名) 识别不出 NULL

其他的几个函数使用方式一样，一起来使用一下：

-- 按性别分组，查看用户名详情，查看年龄详情，统计数据总数量，求年龄的和，求年龄的最大值，求年龄的最小值，求年龄的平均值；
-- 使用 AS 可以起别名
SELECT sex,
GROUP_CONCAT(username) AS username,
GROUP_CONCAT(age) AS age,
COUNT(*) AS total,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1 GROUP BY sex;

结果：

分组配合 WITH ROLLUP 关键字使用

使用 WITH ROLLUP 可以在每条记录末尾添加一条记录，是上面所有记录的总和。 例如：

SELECT GROUP_CONCAT(username),
GROUP_CONCAT(age),
COUNT(*)
FROM user1 GROUP BY sex
WITH ROLLUP;

注意最后一行是自动添加的，如果是数字就相加，如果是集合就列出所有。

HAVING 子句对分组结果进行二次筛选

什么意思呢？举个例子，我们先来一个分组：

-- 使用 AS 可以起别名
SELECT GROUP_CONCAT(username) AS detail,
COUNT(*) AS total
FROM user1
GROUP BY sex;

现在我们要筛选出分组中总数大于等于 2 的分组，那么可以在上面的语句最后加上一句：

SELECT GROUP_CONCAT(username) AS detail,
COUNT(*) AS total
FROM user1
GROUP BY sex
HAVING COUNT(*) >=2;

现在显示的就是总数大于等于 2 的分组了。由于我使用了AS起别名，所以也可以直接使用别名，即 HAVING total>=2，结果同样正确：

查询数据之 ORDER BY 排序

使查询结果按照某一顺序排列，排序的命令如下：

-- ASC 升序；DESC 降序。默认是升序 ASC
ORDER BY 字段名称 ASC|DESC

先来看一个表的默认显示顺序：

具体请以你自己本地的数据为准，现在可以试试按照年龄升序排列：

-- 因为默认使用的 ASC，所以升序时可以不加 ASC 后缀
SELECT * FROM user1 ORDER BY age [ASC];

现在结果是按照年龄升序排列的。有一个小问题就是前两条数据的年龄是一样的，这样的就以id升序再排列，我们也可以让他们再按照id降序排列，即多字段排序：

SELECT * FROM user1 ORDER BY age ASC,id DESC;

结果如图所示，前两条在年龄相同的情况下，按照 id 降序再排列。

结合条件查询排序

在条件查询的基础上也可以排序，比如查询年龄大于 18 的，且按照id降序排列的：

SELECT * FROM user1 WHERE age>18 ORDER BY id DESC;

随机排序

随机排序使用到 RAND()，每次查询结果顺序都不一样：

SELECT * FROM user1 ORDER BY RAND();

结果每次都不相同，就不展示了。

查询数据之 LIMIT 限制结果集显示条数

但数据量很大的时候，我们就需要限制每次查询到的数据量，常见的场景就是分页效果。使用关键字 LIMIT 就可以实现这种操作，它的使用方式有两种：

• LIMIT count 这种形式表示显示结果集的前 count 条数据
• LIMIT offset,count 表示从 offset 开始，显示 count 条数据，offset 从 0 开始，表示偏移量

例如，我的 user1 表中一共五条数据，现在我只想查看前三条数据，我可以：

SELECT * FROM user1 LIMIT 3;

又例如我想看从第二条到第四条的数据，那就是偏移了 1，看 3 条数据：

SELECT * FROM user1 LIMIT 1,3;

其实 LIMIT 很灵活，可以结合很多语句一起使用，下面给出几个例子，可自行尝试：

-- 更新 user1 表中前三条数据，将 age 都加 3
UPDATE user1 SET age=age+3 LIMIT 3;

-- 将 user1 表数据按照 id 降序排列，再更新前三条数据，将 age 都减 5
-- 这里需要注意，结果是先排序后做减的
UPDATE user1 SET age=age-5 ORDER BY id DESC LIMIT 3;

-- 删除 user1 表中前两条数据
DELETE FROM user1 LIMIT 2;

-- 删除 user1 表中后面两条数据（默认 id 是升序的）
-- 删除后面的数据，可以先按 id 降序排列，再删除
DELETE FROM user1 ORDER BY id DESC LIMIT 2;

枚举类型（ENUM）的使用

创建表 test_enum ：

-- 枚举类型需要将可能的值全部枚举出来
CREATE TABLE test_enum(
    sex ENUM('男','女','保密')
);

测试下面几种插入数据的方式：

-- 男 属于枚举中的一个，可以正常插入
INSERT test_enum(sex) VALUES('男');

-- male 不在枚举值中，插入错误
INSERT test_enum(sex) VALUES('male');

-- 可以插入 NULL 值
INSERT test_enum(sex) VALUES(NULL);

-- 可以用枚举值的序号代替值，序号从 1 开始，超出范围则报错
INSERT test_enum(sex) VALUES(1);

集合类型（SET）的使用

创建表 test_set ：

CREATE TABLE test_set(
    a SET('A','B','C','D','E','F')
);

测试下面操作：

-- A，C 都在集合中，正常插入
INSERT test_set(a) VALUES('A');
INSERT test_set(a) VALUES('C');

-- 可以一次插入多个成员，顺序不影响
INSERT test_set(a) VALUES('C,D,E');
INSERT test_set(a) VALUES('C,F,A');

-- H 不在集合中，报错
INSERT test_set(a) VALUES('C,F,A,H');

-- 可以用序号代替集合值，序号从 1 开始
INSERT test_set(a) VALUES(2);

时间类型的使用

TIME 类型

创建表 test_time ：

CREATE TABLE test_time(
    a TIME
);

测试下面操作：

-- TIME 的存储格式是 HH:MM:SS
INSERT test_time(a) VALUES('10:10:10'); -- 结果：10:10:10

-- 支持加入天数，格式为 D HH:MM:SS，D代表天数，范围是 0～34
-- 下面结果是 58:10:10，计算方式是 (2*24+10):10:10
INSERT test_time(a) VALUES('2 10:10:10'); -- 结果：58:10:10

-- 如果插入两个值（指的是带冒号的），表示时分 HH:MM
INSERT test_time(a) VALUES('10:10'); -- 结果：10:10:00

-- 如果只插入一个值，表示秒 SS
INSERT test_time(a) VALUES('10'); -- 结果：00:00:10

-- 插入两个值，不带冒号的，表示天数和时 D HH
INSERT test_time(a) VALUES('2 10'); -- 结果：58:00:00

-- 可以省略冒号，HHMMSS
INSERT test_time(a) VALUES('101010'); -- 结果：10:10:10

-- 插入 0 或者 '0'，最后都会转换为 00:00:00
INSERT test_time(a) VALUES('0'); -- 结果：00:00:00
INSERT test_time(a) VALUES(0); -- 结果：00:00:00

-- 分 和 秒 的范围不得大于 59
INSERT test_time(a) VALUES('80:60:59'); -- 分 超出范围
INSERT test_time(a) VALUES('80:59:60'); -- 秒 超出范围

-- 使用 NOW() 和 CURRENT_TIME 可以转换为当前时间
INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME);

DATE 类型

创建表 test_date ：

CREATE TABLE test_date(
    a DATE
);

测试下面操作：

-- DATE的存储格式是 YYYY-MM-DD，而插入数据的格式不必严格遵循这个

-- 下面两种插入方式都支持
INSERT test_date(a) VALUES('2019-02-02'); -- 结果：2019-02-02
INSERT test_date(a) VALUES('2019-2-2'); -- 结果：2019-02-02

-- 可以不指定分隔符，即 YYYYMMDD
INSERT test_date(a) VALUES('20191020'); -- 结果：2019-10-20

-- 甚至可以自定义分隔符
INSERT test_date(a) VALUES('2019#10#20'); -- 结果：2019-10-20
INSERT test_date(a) VALUES('2019@10@20'); -- 结果：2019-10-20
INSERT test_date(a) VALUES('2019.10.20'); -- 结果：2019-10-20

-- 月份和日期要遵循正常的日期规则，比如月份不得超过 12，日期不得超过 31，同时9月不能有31号等等
-- 下面三个都报错
INSERT test_date(a) VALUES('2019.13.25');
INSERT test_date(a) VALUES('2019.10.32');
INSERT test_date(a) VALUES('2019.9.31');

-- 使用 NOW() 和 CURRENT_TIME 可以转换为当前日期
INSERT test_date(a) VALUES(NOW());
INSERT test_date(a) VALUES(CURRENT_TIME);

DATE 类型还支持插入两位数的年份数据，即 YY-MM-DD 或者 YYMMDD，规则是 70~99 之间的转换为1970～1999，00～69 之间的转换为2000～2069，如下：

INSERT test_date(a) VALUES('190506'); -- 结果：2019-05-06
INSERT test_date(a) VALUES('730506'); -- 结果：1973-05-06

DATETIME 类型

DATETIME 类型是 DATE 和 TIME 的结合体。创建表 test_datetime ：

CREATE TABLE test_datetime(
    a DATETIME
);

测试下面操作：

-- 完整格式 YYYY-MM-DD HH:MM:SS
INSERT test_datetime(a) VALUES('2019-07-08 12:10:45'); -- 结果：2019-07-08 12:10:45

-- 只写两位数年份，不写分隔符等规则同样适用
INSERT test_datetime(a) VALUES('191020121212'); -- 结果：2019-10-20 12:12:12

-- 使用 NOW() 得到当前日期
INSERT test_datetime(a) VALUES(NOW());

TIMESTAMP 类型

TIMESTAMP 类型和 DATETIME 很类似，但它们的时间范围不一样，而且 TIMESTAMP 会识别时区的。

创建表 test_timestamp ：

CREATE TABLE test_timestamp(
    a TIMESTAMP
);

测试下面操作：

-- 跟 DATETIME 一样的插入格式
INSERT test_timestamp(a) VALUES('1988-10-20 12:12:12'); -- 结果：1988-10-20 12:12:12

-- 也可以只写两位数年份，不加分隔符
INSERT test_timestamp(a) VALUES('191020121212'); -- 结果：2019-10-20 12:12:12

-- 插入 CURRENT_TIMESTAMP 或者 NULL，会得到当前系统的日期
INSERT test_timestamp(a) VALUES(CURRENT_TIMESTAMP);
INSERT test_timestamp(a) VALUES(NULL);

YEAR 类型

创建表 test_year ：

CREATE TABLE test_year(
    a YEAR
);

测试下面操作：

-- 存储年份 YYYY 或者 YY 
-- YY 格式遵循：70~99 之间的转换为1970～1999，00～69 之间的转换为2000～2069
INSERT test_year(a) VALUES(2019); -- 结果：2019
INSERT test_year(a) VALUES(20); -- 结果：2020

-- YEAR 范围是 1901～2155，超出范围报错
INSERT test_year(a) VALUES(1900); -- 报错
INSERT test_year(a) VALUES(2156); -- 报错

-- 有趣的插入 0 和 '0' 的结果不一样
INSERT test_year(a) VALUES(0); -- 结果：0000 （不在范围内也不会报错）
INSERT test_year(a) VALUES('0'); -- 结果：2000