MySQL数据库表的相关操作（第4节）


1、MySQL数据库表简介

MySql数据库是一个程序，程序里面可以存放许多个不同种类的数据库，比如QQ用户数据库、微信用户数据库等等。

然后每个数据库又由许多表单组成，比如微信数据库中，可能会有用户身份信息表单、好友关系表单、聊天记录表单、聊天群组信息表单等。

而每个表单又由许多表单项组成，即我们常看见的表单有许多列，并且每一列都存储着不同的信息，比如用户信息表单，可能有用户昵称、性别、年龄、联系方式、地址等，如下图：

在某个数据库中，成千上万的用户信息就存储在各种表中，比如我的微信账号信息就可能存储在某张表单其中的一行中，包含了我的各种信息。

2、MySQL数据库创建表

在MySQL数据库中，可以通过SQL语句创建多个不同名称的数据库，其中的每一个数据库可以创建多个表。表是存储数据的基本结构。在表中，我们可以定义多个列，用来存储各种类型的数据。

在上一节教程中，我们已经通过指令“create database pyhint;”创建了一个名为“pyhint”的数据库，并可以通过指令“use pyhint;”使用该数据库。接下来，我们在该“pyhint”数据库中创建一张表“students”。例如：

动手练一练：

use pyhint;

# 输出结果：
Database changed

create table students (
    id int(6) unsigned auto_increment primary key,
    name varchar(30) not null,
    age int(3) not null
);

上面的例子中，我们首先输入“use pyhint;”命令，告诉MySQL程序我们接下来对“pyhint”数据库进行操作。接着，在“pyhint”的数据库里面，我们创建了一个名为“students”的表，并定义了三个列：id、name和age，三个列的解析如下：

（1）id列的数据类型是整数“int”，int(6)其中数字“6”是该整数的最大长度。“unsigned”表示“无符号”，即该数据类型只能存储非负数，使用“unsigned”修饰的整数类型可以存储正整数值，但无法存储负数值。“auto_increment”关键字用于创建一个自增长的列，这样在插入新记录时不需要手动指定id值。“primary key”关键字用来设置“id列”为表的主键，这意味着该列的值必须是唯一的，一个数据表中只能有一个字段是主键，我们一般通过主键来索引某一条数据。在MySQL中，主键约束（primary key）是最常用的约束之一，它确保了通过主键能够快速准确地查找到表中的每一行记录，从而提高了查询效率并增强了数据的完整性和安全性。

（2）name字段是一个可变字符串，最大长度为30个字符，varchar(30)定义一个可变长度的字符串，括号中的数字“30”是字符串的最大长度，并且通过“not null”定义不允许为空值。

（3）age字段为整数类型，并且不允许为空值。

3、表操作的数据类型

前面我们创建了一个名为“students”表的示例中，我们用到了“int”整数和“varchar”可变长度的字符串两种数据类型。MySQL中的数据类型还有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

（1）数值类型

MySQL中的数值类型主要包括整数类型和浮点类型：

整数类型：

类型 大小 (signed)有符号范围 (unsigned)无符号范围 描述
int或integer 4bytes -2147483648到2147483647 0到4294967295 一个标准的整数
tinyint 1byte -128到127 0到255 一个非常小的整数
smallint 2bytes -32768到32767 0到65535 一个小整数
mediumint 3bytes -8388608到8388607 0到16777215 一个中等大小的整数
bigint 8bytes -9223372036854775808到9223372036854775807 0到18446744073709551615 一个大整数

浮点类型：

类型 大小 (signed)有符号范围 (unsigned)无符号范围 描述
float 4bytes (-3.402823466 e+38， 3.402823466351 e+38) 0 和 (1.175494351 e38，3.402823466 e+38) 单精度浮点数
double 8bytes (-1.7976931348623157 e+308， 1.7976931348623157 e+308) 0 和 (2.2250738585072014 e-308， 1.7976931348623157 e+308) 双精度浮点数
decimal 依赖于m(精度)和d(标度) 的值 依赖于m(精度)和d(标度)的 值 小数值(精确定点数)

（2）字符串类型

类型 大小 描述
char 0-255 bytes 定长字符串(需要指定长度)
varchar 0-65535 bytes 变长字符串(需要指定长度)
tinyblob 0-255 bytes 不超过255个字符的二进制数据
tinytext 0-255 bytes 短文本字符串
blob 0-65535 bytes 二进制形式的长文本数据
text 0-65535 bytes 长文本数据
mediumblob 0-16777215 bytes 二进制形式的中等长度文本数据
mediumtext 0-16777215 bytes 中等长度文本数据
longblob 0-4294967295 bytes 二进制形式的极大文本数据
longtext 0-4294967295 bytes 极大文本数据

这里需要注意的是，Char和Varchar是两种不同的字符串数据类型，它们在存储方式、空间使用、性能等方面存在显著差异。

char(m)：

  • 这是一个固定长度的字符串。

  • 当你定义一个char字段并指定一个长度（例如 char(10)），无论你实际存储的字符串有多短，它都会占用10个字符的空间。

  • 例如，如果你存储字符串“abc”在一个char(10)字段中，那么它后面会有8个空格被填充，总共占据10个字符的空间。

  • 如果存储的字符串长度超过定义的长度，它会被截断。

  • 对于长度始终不变或变化不大的字段，使用char可能更高效，因为数据库可以更快速地访问固定长度的数据。

varchar(m)：

  • 这是一个可变长度的字符串。

  • 与char不同，varchar的长度是根据实际存储的字符串来决定的。

  • 例如，如果你存储字符串“abc” 在一个varchar(10) 字段中，它只会占用3个字符的空间（加上字符串结束的null字符，总共是4个字节）。

  • 如果存储的字符串长度超过定义的长度（例如 varchar(10)），它会被截断，但不会像char那样在后面填充空格。

  • 对于经常变化长度的字段，使用varchar可以节省空间，因为它只存储实际的数据，而不是额外的空格或截断的字符。

（3）日期时间类型

类型 大小 范围 格式 描述
date 3 1000-01-01 至 9999-12-31 yyyy-mm-dd 日期值
time 3 -838:59:59 至 838:59:59 hh:mm:ss 时间值或持续时间
year 1 1901 至 2155 yyyy 年份值
datetime 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 yyyy-mm-dd hh:mm:ss 混合日期和时间值
timestamp 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 yyyy-mm-dd hh:mm:ss 混合日期和时间值，时间戳

时间戳（Timestamp）是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。

4、表操作的实际应用

下面我们利用MySQL简单设计一张员工信息表，包括ID编号、员工工号、员工姓名、性别、年龄、身份证号、联系方式、家庭地址、部门和入职时间：

  1. 编号（纯数字）

  2. 员工工号 (字符串类型，长度不超过10位)

  3. 员工姓名（字符串类型，长度不超过10位）

  4. 性别（男/女，存储一个汉字）

  5. 年龄（正常人年龄，不可能存储负数）

  6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）

  7. 电话号码（字符串类型，长度不超过20位）

  8. 家庭地址（长文本数据）

  9. 部门（不允许为空，确保姓名和部门信息的完整性）

  10. 入职时间（取值年月日即可）

动手练一练：

create table employees(
 id int unsigned auto_increment primary key,
 work_number varchar(10) not null,
 name varchar(10) not null,
 gender char(1),
 age tinyint unsigned,
 idcard char(18),
 phone_number varchar(20),
 address text,
 department varchar(100) not null,
 entrydate date
);

5、表插入数据

前面我们已经创建了一个“pyhint”数据库，并已经在“pyhint”数据库中创建一张表“students”。现在我们可以使用“insert into”语句将数据插入到表中。例如：

动手练一练：

use pyhint;

# 输出结果：
Database changed

动手练一练：

insert into students (name, age)
values ('许某', 20), ('陈某', 22), ('林某', 18);

# 输出结果：
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

上面的例子中，我们向“students”表中插入了三条记录，每条记录都包含一个名字和一个年龄数字。

6、查询数据

前面我们已经通过简单的命令，向“students”表中插入了三条数据，接下来可以使用“select”语句来查询数据。例如：

动手练一练：

select * from students;

# 输出结果：
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 许某   |  20 |
|  2 | 陈某   |  22 |
|  3 | 林某   |  18 |
+----+--------+-----+
3 rows in set (0.00 sec)

在这里，我们使用“select”语句查询“students”表中的所有记录，我们也可以指定某些列来查询。例如：

动手练一练：

select name from students;

# 输出结果：
+--------+
| name   |
+--------+
| 许某   |
| 陈某   |
| 林某   |
+--------+
3 rows in set (0.00 sec)

上面的例子中，我们只查询“students”表中的“name”列。

7、更新数据

如果我们需要更新“students”表中的一些记录，可以使用“update”语句。

动手练一练：

update students set age = 15 where name = '许某';

# 输出结果：
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上面的例子中，我们使用“update”语句成功将“许某”的年龄从20更新为15。

8、删除数据

如果我们需要删除表中的记录，可以使用“delete”语句。

动手练一练：

delete from students where name = '许某';

# 输出结果：
Query OK, 1 row affected (0.01 sec)

上面的例子中，我们使用“delete”语句成功从“students”表中删除名字为“许某”的记录。

9、表相关指令

命令 作用
create table 表名(列名 类型 [属性],列名 类型 [属性]…); //属性可省略 创建表
show tables; 显示当前数据库所有表
desc 表名; 查看表结构
drop table 表名; 删除表
alter table 表名 add 列名 类型; 添加列
alter table 表名 drop column 列名; 删除列
show columns from 表名; 显示表中所有列
select * from 表名; 显示表中所有数据
insert into 表名 values(值,值,值…); //默认添加顺序为该表的列名顺序 添加数据
delete from 表名 where 条件; 删除数据
ipdate 表名 set 列名=值 where 条件; 修改数据

在本节教程中，我们介绍了MySQL基本操作,包括建立数据库、创建表、插入数据、查询数据、更新数据和删除数据。MySQL是一种强大而灵活的开源数据库，适用于各种应用场景。由于其稳定性、可靠性和易于使用的特性，MySQL已成为许多企业和开发者的首选数据库。通过使用MySQL，开发者可以轻松地创建、管理和查询数据，从而构建高效的数据驱动应用程序。