阅读 670

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

让我唠叨

本来写了一大堆废话,想想还是删了。

本文主要记录学习 sql 语句的使用,看完这些也不能让你成为数据库高手,只是会对数据库有了一个清晰的概念,会用原始的 sql 语句去操控数据库,仅此而已。学习是没有尽头的,坚持下去也许能看到期望的风景(自我鸡汤)。

右侧目录没显示全,只有展开所有的内容才会显示全~

mysql安装

本来不想啰嗦这部分,毕竟网上太多安装mysql的教程了,但最后还是决定写上我的安装步骤以当作参考。但因为我的平台是 mac,所以此安装步骤只适用于 mac,需要的可以看下。

展开查看安装详情

我是直接官网下载安装包的,选择的是社区版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
复制代码

回车后输入新密码即可。

mysql的登录及退出

从这里开始,需要你已经正确安装好了mysql。

查看mysql的登录退出

登录的两种方法

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

退出的三种方法

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

创建数据库

登录mysql后,就可以执行SQL语句来操作数据库了,从创建数据库开始。

查看创建数据库详情

常规创建数据库

执行以下命令:

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;
复制代码

例子:

数据类型

保存在数据库中的数据都有自己的类型,选用合适的字段类型对数据库的优化非常重要。MYSQL中的数据类型大致可以分为三类:数值字符串日期,赶紧来了解下吧。

查看数据类型

数值类型

数值类型又可分为: 整数型浮点型

整数型

数据类型 存储范围 字节
TINYINT 有符号值:-128 到 127
无符号值:0 到 255
1
SMALLINT 有符号值:-32768 到 32767
无符号值:0 到 65535
2
MEDIUMINT 有符号值:-8388608 到 8388607
无符号值:0 到 16777215
3
INT 有符号值:-2147483648 到 2147483647
无符号值:0 到 4294967295
4
BIGINT 有符号值:-9223372036854775808 到 9223372036854775808
无符号值:0 到 18446744073709551615
8
BOOL,BOOLEAN 等价于TINYINT(1),0为false,其余为true 1

浮点型

数据类型 存储范围 字节
FLOAT[(M,D)] 负数取值范围为-3.40E+38 到 -1.17E-38、0和1.175E-38 到 3.40E+38。
M是数字总位数,D是小数点后面的位数。如果M和D被省略,根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。
4
DOUBLE[(M,D)] -1.79E+308 到 -2.22E-308、0和2.22E-308 到 1.79E+308。 8
DECIMAL[(M,D)] 和DOUBLE一样,内部以字符串形式存储值 M+2

字符串类型

数据类型 存储范围 用途
CHAR(M) 0<=M<=255 定长字符串
VARCHAR(M) 0<=M<=65535 变长字符串
TINYTEXT 0~255字节 短文本字符串
TEXT 0~65535字节 长文本数据
MEDIUMTEXT 0~16777215字节 中等长度文本数据
LONGTEXT 0~4294967295字节 极大文本数据
ENUM('value1','value2',...) 最多65535个值 枚举
SET('value1','value2',...) 最多64个成员 集合

日期类型

数据类型 存储范围 格式
TIME '-838:59:59'/'838:59:59' HH:MM:SS
DATE 1000-01-01/9999-12-31 YYYY-MM-DD
DATETIME 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS
TIMESTAMP 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 YYYYMMDD HHMMSS
YEAR 1901~2155 YYYY

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

数据表

数据表是数据库最重要的组成部分之一,数据是保存在数据表中的;数据表由行(row)和列(column)组成,每个数据表中至少有一列,行可以有零行一行和多行;数据表名是唯一的,不可包含特殊字符,最好含义明确。

查看数据表相关

创建数据表

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

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

来看一下各个点的含义:

  • tbl_name:表示数据表名称,不要包含特殊字符和 MYSQL 关键字,最好是有意义的;
  • 字段名称:不用多说,就是我们前端经常从接口里获取的字段;
  • 字段类型:即字段的数据类型,前面已经列举了;
  • 完整性约束条件:可加可不加,后面会详细说,暂时不管;
  • 存储引擎:mysql有两种存储引擎,分别是InnoDBMyISAM。当不指定存储引擎时,默认是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

UNSIGNEDZEROFILL 主要应用在整型上,下面以创建整型数值举例。

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)
);
复制代码

可以看到iduserid都属于主键。来插入一条值试试:

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 控制添加的字段在指定字段后面,比如添加邮箱字段emailusername后面:

-- 添加 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 自定的索引名(字段名称);
复制代码

分别使用两种方法给usernamepassword字段加上唯一性约束:

-- 使用字段名作为索引名
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是自动增长,ageemail 都是有默认值的,所以我们可以只指定 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 类型是 DATETIME 的结合体。创建表 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
复制代码

进阶操作

关于进阶的部分,我准备另开一文记录,主要这里太长了,不方便写作。进阶部分主要涉及多表联查,外键约束,函数的使用等等,需要的可以关注一波哟~

文中有不对的地方欢迎指出。

关注下面的标签,发现更多相似文章
评论