mysql2.7.23_MySQL 数据类型和操作数据库（二）

2、 数据类型与操作数据表

数据类型：指的是列、存储过程中参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

2.1、myql 数据类型之整型

有符号位的范围大致是无符号位范围的 2 倍，无符号位只有正数，选择合适的数据类型很重要，尤其是要存储的数据量特别大的时候，不同的数据类型占的字节不同。

2.2、mysql 数据类型之浮点型

有些数据适合用整型，而有些数据是小数，使用浮点型更合适，双精度大约是单精度范围的 10 倍。

2.3、mysql 数据类型之日期时间型

日常项目开发中很少使用 日期时间型，大多使用数字的形式存储，主要是因为存在时区的问题。

date：1000年1月1日--9999年12月31日

datetime：1000年1月1日00：00点--9999年12月31日23：59分

timestamp(时间戳)：1970年1月1日00：00点---2017年(现在)的一个值

time：-8385959--8285959

year：可以存储 2 位也可以是 4位，默认 4 位(可以70-69，表示19070-2069年)

2.4、mysql 数据类型之字符型

2.5、mysql 创建数据表

数据表(或称表)是数据库最重要的组成部分之一，是其他对象的基础。

打开数据库命令：

# USE 数据库名称

mysql> USE test;

Database changed

mysql>

检查当前打开的数据库是否为我们刚打开的数据库：

# SELECT DATABASE();

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| test |

+------------+

1 row in set (0.00 sec)

2.5.1、创建数据表

语法结构：

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type，

...

DROP TABLE table_name # 删除数据表

)

# column_name(列名称) 事先需要经过分析计算出需要多少列，而不是凭空想象

# data_type(数据类型)

示例：

# 创建一个名为 tb1 的数据表，其中包含名字、年龄、工资(salary)

mysql> CREATE TABLE tb1(

-> username VARCHAR(20), # VARCHAR(20) 表示名字长度最长为20

-> age TINYINT UNSIGNED, # 因为年龄是正的，所有是无符号位(UNSIGNED)

-> salary FLOAT(8,2) UNSIGNED # 最后一个字段，不需要在最后➕逗号

-> );

Query OK, 0 rows affected (0.07 sec)

2.5.2、查看数据表

在当前数据库下查看别的数据库下的数据表，并不会改变数据库。

语法结构：

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

示例：

# 查看当前数据库下的数据表

mysql> SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| tb1 |

+----------------+

1 row in set (0.00 sec)

# 不仅可以查看当前数据库下的数据表，还可以查看别的数据库下的数据表

mysql> SHOW TABLES FROM mysql;

2.5.3、查看数据表结构

语法结构：

SHOW COLUMNS FROM tbl_name;

示例：

# 查看 tb1 的数据表结构

mysql> SHOW COLUMNS FROM tb1;

+----------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------------+------+-----+---------+-------+

| username | varchar(20) | YES | | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

| salary | float(8,2) unsigned | YES | | NULL | |

+----------+---------------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

2.6、mysql 记录的插入与查找

通过上面的创建，我们创建了数据表的列，行就是(记录)。

2.6.1、数据表的记录(行)插入

语法结构：

INSERT [INTO] tbl_name [ (col_name,...)] VALUES(CAL,...)

# col_name 表示数据表中哪几个列要赋值，可省略，省略的时候表示所有的列都必须赋值，否则出错

# VALUES 为要赋的值

示例：

# 省略 col_name时，需要给每个列度都赋值

mysql> INSERT tb1 VALUES('Tom',25,7863.25);

Query OK, 1 row affected (0.01 sec)

# 没有给全部列赋值，程序出错

mysql> INSERT tb1 VALUES('Tom',25);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

# 也可以只给其中一个或几个列赋值

mysql> INSERT tb1(username,salary) VALUES('John',4500.69);

Query OK, 1 row affected (0.01 sec)

2.6.2、查看记录

语法结构：

# expr 表达式

SELECT expr,...FROM tbl_name

示例：

# * 在这里表示的是字段的过滤(可以理解为全部字段的过滤)，这里只是比较简单的表示

mysql> SELECT * FROM tb1;

+----------+------+---------+

| username | age | salary |

+----------+------+---------+

| Tom | 25 | 7863.25 |

| John | NULL | 4500.69 |

+----------+------+---------+

2 rows in set (0.00 sec)

2.7、mysql 空值与非空

在上述列子中，我们给数据表 tb1 插入记录时，John 没有指定年龄，在我们查看数据表结构时，发现年龄的位置为空(NULL)，而大多数情况下，我们在网站用户名创建时，往往有些地方不能为空，或者说是显示的不是空，为此我们可以更加深入地完善空。

NULL：字段值可以为空(默认为可以为空)

NOT NULL：字段值禁止为空

示例：

# 创建一个数据表 tb2，将列(username 设置为非空(NOT NULL), age 设置为可以为空(NULL)，可省略不写)

mysql> CREATE TABLE tb2(

-> username VARCHAR(20) NOT NULL,

-> age TINYINT UNSIGNED NULL

-> );

Query OK, 0 rows affected (0.04 sec)

# 查看数据表结构

mysql> SHOW COLUMNS FROM tb2;

+----------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------------+------+-----+---------+-------+

| username | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+----------+---------------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

# 插入记录，将年龄设置为 NULL，显示可以

mysql> INSERT tb2 VALUES('TOM',NULL);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb2;

+----------+------+

| username | age |

+----------+------+

| TOM | NULL |

+----------+------+

1 row in set (0.00 sec)

# 插入记录，将用户名(username设置为空)出错，因为在创建时，设置它不能为空

mysql> INSERT tb2 VALUES(NULL,26)；

ERROR 1048 (23000): Column 'username' cannot be null

2.8、mysql 自动编号

为了保证记录的唯一性，避免在数据表中，记录不能出现重复，可以为某个字段添加 AUTO_INCREMENT(自动编号) 属性。

自动编号，且必须与主键组合使用

默认起始值为1，每次增量为1

示例：

# 创建一个数据表，id 设置为自动编号

mysql> CREATE TABLE tb3(

-> id SMALLINT UNSIGNED AUTO_INCREMENT,

-> username VARCHAR(30) NOT NULL

-> );

# 出错内容意思是必须与主键配合(it must be defined as a key)

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

2.8.1、初涉主键(PRIMARY KEY)约束

主键可以理解为一个表格的类别(自我理解的)

主键约束

每张数据表只能存在一个主键

主键保证记录的唯一性

主键自动为 NOT NULL

可以不用赋值

示例：

# 创建一个数据表 tb3

mysql> CREATE TABLE tb3(

-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, # 将id 定义为主键(AUTO_INCREMENT PRIMARY KEY)

-> username VARCHAR(30) NOT NULL

-> );

Query OK, 0 rows affected (0.05 sec)

# 查看数据表结构

mysql> SHOW COLUMNS FROM tb3; # 查看这个结构，发现 id 为主键(PRI)，还有一个 Extra(增量)

+----------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| username | varchar(30) | NO | | NULL | |

+----------+----------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

# 插入 4 条记录(Tom、John、Rose、Alice)

mysql> INSERT tb3(username) VALUES('John');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT tb3(username) VALUES('Tom');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT tb3(username) VALUES('Rose');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT tb3(username) VALUES('Alice');

Query OK, 1 row affected (0.00 sec)

# 查看记录

mysql> SELECT * FROM tb3; # 发现数据表结构自动编号，起始值为1，每次递增为1

+----+----------+

| id | username |

+----+----------+

| 1 | John |

| 2 | Tom |

| 3 | Rose |

| 4 | Alice |

+----+----------+

4 rows in set (0.00 sec)

AUTO_INCREMENT 字段必须定位为主键，但主键不一定与 AUTO_INCREMENT 字段使用

可以理解为，AUTO_INCREMENT 为递增量，它必须定义为主键才能使用，如果没有给 第一个列(id)插入值，那么起始值为 1，插入记录时，那么第 2 条记录的编号则为2，依次增加1，但是主键可以自己使用，当把 第一个列(id )定义为主键时，插入记录时，每条记录都需要插入值(编号)，它没有递增量。

示例：

# 创建一个数据表 tb4

mysql> CREATE TABLE tb4(

-> id SMALLINT UNSIGNED PRIMARY KEY, # 将 id 定义为主键，但没有设置 AUTO_INCREMENT 属性

-> age VARCHAR(20) NOT NULL

-> );

Query OK, 0 rows affected (0.05 sec)

# 查看数据表结构

mysql> SHOW COLUMNS FROM tb4; # 发现 Extra 位置，id 为空，并没有出现 AUTO_INCREMENT(增量)

+-------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------------------+------+-----+---------+-------+

| id | smallint(5) unsigned | NO | PRI | NULL | |

| age | varchar(20) | NO | | NULL | |

+-------+----------------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

# 插入两条记录

mysql> INSERT tb4 VALUES(4,'Tom');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT tb4 VALUES(20,'Rose');

Query OK, 1 row affected (0.01 sec)

# 查看插入记录

mysql> SELECT * FROM tb4; # 第一条记录的编号为 4，第二条为 20，如果再插入 20 则会出错

+----+------+

| id | age |

+----+------+

| 4 | Tom |

| 20 | Rose |

+----+------+

2 rows in set (0.00 sec)

2.8.2、初涉唯一约束(UNIQUE KEY)

每张表中只能有一个主键，而往往主键与 AUTO_INCREMENT 使用了，要保证记录的唯一性，也可以使用唯一约束。

唯一约束

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空(NULL)

每张数据表可以存在多个唯一约束

示例：

# 创建一个数据表 tb5

mysql> CREATE TABLE tb5(

-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, # 将 id 定位为主键

-> username VARCHAR(20) NOT NULL UNIQUE KEY, # 将 username 定义为UNIQUE KEY

-> age TINYINT UNSIGNED

-> );

Query OK, 0 rows affected (0.04 sec)

# 查看数据表结构

mysql> SHOW COLUMNS FROM tb5;

+----------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| username | varchar(20) | NO | UNI | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+----------+----------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

# 插入两条一样的记录

mysql> INSERT tb5(username,age) VALUES('Tom',22);

Query OK, 1 row affected (0.00 sec)

# 插入第二条时，发生错误，提示 Tom 重复，也就是说唯一约束的唯一性

mysql> INSERT tb5(username,age) VALUES('Tom',22);

ERROR 1062 (23000): Duplicate(重复的) entry 'Tom' for key 'username'

# 查看插入记录

mysql> SELECT * FROM tb5;

+----+----------+------+

| id | username | age |

+----+----------+------+

| 1 | Tom | 22 |

+----+----------+------+

1 row in set (0.00 sec)

2.8.3、初涉默认约束(DEFAULT)

默认值

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

示例：

# 创建一个数据表 tb6

mysql> CREATE TABLE tb6(

-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

-> username VARCHAR(20) NOT NULL UNIQUE KEY,

-> sex ENUM('男','女','保密') DEFAULT '保密' # 性别列设置默认值(DEFAULT)为保密

-> );

Query OK, 0 rows affected (0.04 sec)

# 仓库数据表结构

mysql> SHOW COLUMNS FROM tb6;

+----------+------------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+------------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| username | varchar(20) | NO | UNI | NULL | |

| sex | enum('男','女','保密') | YES | | 保密 | |

+----------+------------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

# 插入记录

mysql> INSERT tb6(username) VALUES('Tom'); # 性别没有赋值，将使用默认值

Query OK, 1 row affected (0.01 sec)

# 查看插入记录

mysql> SELECT * FROM tb6;

+----+----------+------+

| id | username | sex |

+----+----------+------+

| 1 | Tom | 保密 |

+----+----------+------+

1 row in set (0.00 sec)

2.9、总结

数据类型：字符型、整型、浮点型、日期时间型

数据表操作：插入记录、查找记录

记录操作：创建数据表、约束的使用