MySQL-数据表操作（一）

备注：环境CentOS8/mysql-8.0.23

1.创建数据表

语法：

CREATE TABLE [IF NOT EXISTS]表名（
字段1数据类型 [约束调件] [默认值]，
字段2数据类型 [约束调件] [默认值]，
字段3数据类型 [约束调件] [默认值]，
...
[表约束条件]
）；

简单语法：

create table 表名（
列名1 数据类型（宽度） 约束条件，
列名2 数据类型（宽度） 约束条件，
列名3 数据类型（宽度） 约束条件，
）

在创建数据表时，必须指定数据表的表名称，表名称在win系统不区分大小写，在Linux操作系统上区分，如果需要在Linux操作系统上不区分大小写，则需要在配置文件my.cnf添加：

lower_case_table_name=1

示例：
在名称为xiaohuagou创建数据表：

mysql> create table if not exists xiaohuagou_pixel( 
    -> id int(6),
    -> recommend char(20),
    -> follow varchar(100)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

到这里表创建完成，虽然这里没有报错，但是有一个警告信息（1 warning（0.01 sec））使用SQL语句进行查看MySQL的警告信息

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

是因为在MySQL的8.x版本中，不再推荐为int类型指定显示长度，并在未来的版本中可能去掉这样的语法，因此这里去掉int类型的显示长度，重新创建名称为xiaohuagou——pixel的数据表

mysql> create table xiaohuagou_pixel(
    -> id int,
    -> t_threshold varchar(20),
    -> t_edge varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

这里不再显示警告信息，因此在MySQL 8.x版本中创建数据表时，建议不要为int类型指定显示长度，接下来再次查看xiaohuagou数据库中存在的数据表

mysql> show tables;
+----------------------+
| Tables_in_xiaohuagou |
+----------------------+
| xiaohuagou_pixel     |
+----------------------+
1 row in set (0.00 sec)

2.创建数据表时指定主键

在MySQL中创建数据表时，可以为数据表指定主键（主码），包含表中的一列或者多列，能够唯一标识表中的一行记录。主键列的数据必须唯一且非空，主键可以分为单列主键和多列主键。

2.1单列主键

只包含数据表中的一个字段，可以在定义数据列的同时指定主键，也可以在定义完数据表中的所有列的同时指定主键

• 在定义列的同时指定主键
  语法：字段 数据类型 primary key [默认值]
  示例：

mysql> create table xhg_data(
    -> id int primary key,
    -> gray_t varchar(200),
    -> threshold_t varchar(1000)
    -> );
Query OK, 0 rows affected (0.01 sec)

• 定义完数据表中的所有列之后指定主键
  语法：[constratint 约束条件名] primary key [字段名]
  示例：

mysql> create table xhg_l(
    -> id int,
    -> gray_t varchar(200),
    -> threshold_t varchar(1000),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

• 查看表：desc table name;

mysql> desc xhg_data;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 复合主键

mysql> create table danhuatang(
    -> id int,
    -> gray_ts varchar(200),
    -> threshold varchar(1000),
    -> bottom_t varchar(1000),
    -> primary key(id,gray_ts)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc danhuatang;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   | PRI | NULL    |       |
| gray_ts   | varchar(200)  | NO   | PRI | NULL    |       |
| threshold | varchar(1000) | YES  |     | NULL    |       |
| bottom_t  | varchar(1000) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

• 追加主键：alter table table_name add primary key(filed,...);
• 追加多列联合（复合）主键：alter table table_name add primary key(number,course);

mysql> desc xiaohuagou_pixel;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int          | YES  |     | NULL    |       |
| t_threshold | varchar(20)  | YES  |     | NULL    |       |
| t_edge      | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table xiaohuagou_pixel add primary key(id,t_edge);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xiaohuagou_pixel;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int          | NO   | PRI | NULL    |       |
| t_threshold | varchar(20)  | YES  |     | NULL    |       |
| t_edge      | varchar(100) | NO   | PRI | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 追加主键:alter table tab_name modify 字段primary key;

mysql> alter table xhg_l modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 追加主键：alter table table_name add constraint filed primary key(filed);

mysql> alter table xhg_l add constraint gray_t primary key(gray_t);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   |     | NULL    |       |
| gray_t      | varchar(200)  | NO   | PRI | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 追加主键：alter table table_name add constraint primary key(filed);

mysql> alter table xhg_l add constraint primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | NO   |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 删除主键：alter table table_name drop primary key;

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table xhg_l drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc xhg_l;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   |     | NULL    |       |
| gray_t      | varchar(200)  | YES  |     | NULL    |       |
| threshold_t | varchar(1000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

2.2外键

• 定义：外键可以关联数据库中的两张表，对应的是数据库中的参照完整性，一张表的外键可以为空也可以非空，则每一个外键的值必须等于另一张表的主键的某个值。一张表的外键可以不是本表的主键，但其对应着另一张表的主键，在一张表中定义了外键之后不允许删除另一张表中具有关联关系的行数据
• 作用：用来和其他表建立联系用的
• 个数：一个表可以有多个外键
  由外键引申出两个概念，分别是主表（父表）和从表（子表）
• 主表：两个表具有关联关系时，关联字段中主键所在的表为主表
• 从表：两个表具有关联关系时，关联字段中外键所在的表为从表
  语法：[constraint 外键名] foreign key filed1[filed2,flied3,…]
  语法：references 主表名 主键列1[主键列2，主键列3，…]
• 外键名：定义外键是为数据表指定的外键名称，在同一张数据表中，外键的名称必须是唯一，也就是说，在同一张数据表中，不能有相同名称的外键名称
• FOREIGN KEY：指定外键包含在那些字段，可以是同一字段,也可以是多个字段的组合。
• REFERENCES:指定关联的主表名称
• 主表名：主键所在的表名称
• 主键列：主表中定主键字段，可以是一个字段，也可以是多个字段的组合
• 创建表时增加外键语法：constraint 外键名 foreign key (从表列名)references 主表名（主表列）；
• 示例：

mysql> create table dht_6( id int, t_stock varchar(20), t_create_time datetime, t_crany int, constraint t_crany_1 foreign key(t_crany) references dht(id) );
Query OK, 0 rows affected (0.01 sec)

mysql> desc dht_6;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int         | YES  |     | NULL    |       |
| t_stock       | varchar(20) | YES  |     | NULL    |       |
| t_create_time | datetime    | YES  |     | NULL    |       |
| t_crany       | int         | YES  | MUL | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

• 创建表后添加外键语法：alter table table_name(从表)add constraint外键名（也叫约束名）foreign key(外键列（从表）)references 表名（主表）（列名（主表的列））；
• 示例：

mysql> show tables;
Empty set (0.00 sec)

mysql> create table dht(
    -> id int primary key,
    -> name varchar(50),
    -> number varchar(100)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> create table xhm(
    -> id int primary key,
    -> name_t varchar(50),
    -> number_t varchar(100),
    -> pixel int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table xhm add constraint zoo foreign key(pixel) references dht(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

被引用的表不能直接删除，要先删除引用的表；
以上都的操作都是物理外键，数据库级别的外键不建议使用，避免数据库过多的困扰

• 查询多表信息：desc table_name;desc table_name;
• 查看数据库有多少表：show tables;
• 查看新建表信息：show create table table_name;
• 查看表结构：desc table_name;
• 查看表的所有字段内容：select * from table_name;
• 查看host表的id字段的内容：select id from host;
• 查看host表的id字段和port字段的内容：

select id,port from host;

2.3创建数据表时指定默认值

在MySQL中，创建数据表时可以为字段设置默认值。比如将int型的默认值可以设置为0，将varchar型的默认值可以设置为空字符串，将datetime型的默认值可以设置为系统当前时间等。如果为字段指定了默认值，则在向数据表插入数据时，如果没有为当前字段指定任何值，则MySQL会自动为该字段赋值为指定的默认值

• 语法：字段名称 数据类型 default 默认值，定义字段时，在字段后边使用关键字default，后边跟默认值，既可为当前字段设置默认值
• 示例：

mysql> create table dht(
    -> name int,
    -> id int default 2,
    -> t_remark varchar(100) not null,
    -> t_class varchar(50)
    -> );
Query OK, 0 rows affected (0.02 sec)

2.4创建数据表时指定主键默认递增

MySQL支持将整数类型的主键设置为默认递增类型，这样在向数据表插入数据时，可以不用指定整数类型主键的值，MySQL会将该表的整数类型的主键自动加1

• 语法：字段名称 数据类型 auto_increment
  整数类型的主键字段后边加上auto_increment关键字，既可将挡墙整数类型的主键值设置为自动递增
• 示例：

mysql> create table dht_1(
    -> id int primary key auto_increment,
    -> t_remark varchar(30),
    -> t_class varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

主键id已经设置了自增类型，在向数据表dht_1中插入数据时，主键id的值默认从1开始每次插入一条新数据时，id字段的值会自动加1

2.5创建数据表时指定存储引擎

MySQL支持在创建数据表时为该出具表指定相应的引擎，根据具体业务务求，一个数据库中可以包含不同存储引擎的数据表

• 语法：engine=存储引擎名称，在创建表语句后面使用“ENGINE=存储引擎名称”及可以为当前表指定相应的存储引擎
• 示例：

mysql> create table dht_(
    -> id int primary key auto_increment,
    -> t_name  varchar(20),
    -> t_remark varchar(100)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

2.6创建数据表时指定编码

MySQL支持在创建数据表时为数据表指定编码格式，也可以在创建数据表后修改数据表的编码格式

• 语法：default character set 编码 collate 校对规则或default charset=编码 collate=校对规则
• 示例：

mysql> create table dht_3(
    -> id int not null auto_increment,
    -> t_name varchar(20),
    -> t_remark varchar(100),
    -> primary key(id)
    -> )engine=InnoDB default character set utf8mb4 collate utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)

3.查看数据表结构

在MySQL中创建好数据表之后，可以查看数据结构，支持使用describe/desc语句查看数据表结构，也支持使用show cretae table

3.1使用describe/desc语句查看表结构

• 语法1：describe table_name;
• 语法2：desc table_name;

3.2使用show create table语句查看表结构

• 语法1：show create table table_name \G('G’区分大小写)
• 语法2：show create table table_name;
• 示例：

mysql> desc dht;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | int          | YES  |     | NULL    |       |
| id       | int          | YES  |     | 2       |       |
| t_remark | varchar(100) | NO   |     | NULL    |       |
| t_class  | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> describe dht;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | int          | YES  |     | NULL    |       |
| id       | int          | YES  |     | 2       |       |
| t_remark | varchar(100) | NO   |     | NULL    |       |
| t_class  | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table dht \G
*************************** 1. row ***************************
       Table: dht
Create Table: CREATE TABLE `dht` (
  `name` int DEFAULT NULL,
  `id` int DEFAULT '2',
  `t_remark` varchar(100) NOT NULL,
  `t_class` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> show create table dht;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dht   | CREATE TABLE `dht` (
  `name` int DEFAULT NULL,
  `id` int DEFAULT '2',
  `t_remark` varchar(100) NOT NULL,
  `t_class` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

附录MySQL数据类型

类	名	存储需求（取值范围）	说明
整型 (integer)	tinyint	1字节（[0~255],[-128 ~127]）；255=2^ 8-1;127=2^7-1	很小整数 (8位2进制)
	smallint	2字节（0~65535、-32768 ~32767）；65535=2^16-1	短整型 (小整数16位2进制)
	mediumint	3字节（0~16777215）；16777215=2^24-1	中整型（24位2进制整型)
	int	4字节（0~4294967295）；4294967295=2^32-1	普通整型（32位2进制）
	bignit	8字节（0~18446744073709551615）；18446744073709551615=2^64	大整数（64位2进制整数）
浮点数&定点数（Decimal）	float	4字节	单精度浮点数
	double	8字节	双精度浮点数
	decimal（m,d）	M+2字节	压缩严格的定点数
时间/日期型（date）	year	YYYY 1901-2155
	time	HH:MM:SS -838:59:59~838:59:59
	date	YYYY-MM-DD 1000-01-01~9999-12-3
	datetime	YYYY-MM-DD HH:MM:SS:1000-01-01 00:00:00~9999-12-31 23:59:59
	timestamp	YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
字符串类型（text binary）	char	0~255字节	定长
	varchar	0~255字节	变长
	tinyblob	0~255字节	二进制短文本字符串
	TINYTEXT	0~255字节	短文本数据
	BLOB	0~65535字节	二进制形式的长文本数据
	MEDIUMBLOG	0~167772150字节	中等二进制文本
	LONGBLOB	0~4294967295字节	极长二进制文本
	TEXT	0~65535字节	长文本数据
	MEDIUMTEXT	0~167772150字节	中等长度文本
	LONGTEXT	0~4294967295字节	极长文本
	VARBINARY(M)	0~M个字节的变长字节字符串	max-length 值的最大长度（以字节为单位）；如果长度未指定，则其为 1，长度必须在 1 到 32767 范围内
	BINARY(M)	0~M个字节的定长字节字符串	max-length值的最大长度（以字节为单位）；如果长度未指定，则其为 1，长度必须在 1 到 32767 范围内