数据表结构相关
一、数据类型及选用
1、数值类型
数值类型：存放数字型数据的约定，包括整数和小数。
数值类型数据：指字面值具有数学含义，能直接参加数值运算（例如求和、求平均值等）的数据。
（1）整数类型
整数类型主要用于存放整数数据。
不同的数据类型提供了不同的取值范围。
无符号整数类型都是从0开始，最大值一般是有符号类型最大值的两倍。
tinyint类型：小整数，数据类型用于保存一些范围的整数数值范围。
有符号：
-128 ～ 127
无符号：
0 ～ 255
CREATE TABLE `test`.`tinyint-test` (
`tinyint` tinyint(255) NULL, # 有符号
`tinyint-unsigned` tinyint(255) UNSIGNED NULL # 无符号
);
# 分别插入最小值
INSERT INTO `test`.`tinyint-test`(`tinyint`, `tinyint-unsigned`) VALUES (-128, 0);
# 分别插入最大值
INSERT INTO `test`.`tinyint-test`(`tinyint`, `tinyint-unsigned`) VALUES (127, 255)
smallint类型：大整数，两个字节。
有符号：
-32768 ～ 32767
无符号：
0 ～ 65535
CREATE TABLE `test`.`smallint-test` (
`smallint` smallint(255) NULL,
`smallint-unsigned` smallint(255) UNSIGNED NULL
);
# 分别插入最小值
INSERT INTO `test`.`smallint-test`(`smallint`, `smallint-unsigned`) VALUES (-32768, 0);
# 分别插入最大值
INSERT INTO `test`.`smallint-test`(`smallint`, `smallint-unsigned`) VALUES (32767, 65535);
（2）小数类型
MySQL中用浮点数和定点数来表示小数。
浮点类型：单精度浮点类型（float）、双精度浮点类型(double)。
定点类型：decimal。
均使用(m,n)来表示，m表示总共的有效位数，n表示小数的位数。
此三种类型小数位最大值都是30，decimal的最大有效位数仅为65，float和double的最大有效位数是255。
# 验证精确度:decimal > double > float.
# 均按最大值创建的表：
CREATE TABLE `test`.`float` (
`float` float(255, 30) NULL,
`double` double(255, 30) NULL,
`decimal` decimal(65, 30) NULL
);
INSERT INTO `test`.`float`(`float`, `double`, `decimal`) VALUES (1.111111111111111111111111111111, 1.111111111111111111111111111111, 1.111111111111111111111111111111);
mysql> select * from `float`;
+----------------------------------+----------------------------------+----------------------------------+
| float | double | decimal |
+----------------------------------+----------------------------------+----------------------------------+
| 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111111111111111 |
+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.00 sec)
# 四舍五入案例
CREATE TABLE `malldb`.`dec` (
`float` float(255, 7) NULL,
`double` double(255, 15) NULL,
`decimal` decimal(65, 30) NULL
);
ALTER TABLE `malldb`.`dec`
MODIFY COLUMN `float` float(10, 3) NULL DEFAULT NULL FIRST,
MODIFY COLUMN `double` double(12, 6) NULL DEFAULT NULL AFTER `float`,
MODIFY COLUMN `decimal` decimal(15, 12) NULL DEFAULT NULL AFTER `double`;
# 输入数据
INSERT INTO `malldb`.`dec`(`float`, `double`, `decimal`) VALUES (3.14159265253, 3.14159265253, 3.14159265253)
# 显示的结果
float double decimal
3.142 3.141593 3.141592652530
精确度：float为小数点后7位，double为小数点后15位，decimal为小数点后30位。
不管定点类型还是浮点类型，指定的精度超过精度范围，都会进行四舍五入。
2、字符串类型
主要存储字符串和文本信息。
3、日期时间类型
用于表示时间值的日期和时间数据类型是 DATE、 TIME、 DATETIME、 TIMESTAMP和 YEAR。
mysql解释两位数的年份值用以下规则：
- 年份值在70-99之间解释为1970-1999；
- 年份值在00-69之间解释为2000-2069.
mysql> create table student(
-> id int,
-> name char(6), # 存储宽度
-> born_year year, # YEAR类型
-> class_time time, # TIME类型
-> birth_date date, # DATE类型
-> reg_time datetime, # DATETIME类型
-> timestamp_type timestamp # DATETIME类型
-> );
INSERT INTO time VALUES (1, 'hqs', now(), now(), now(), now(),now());
INSERT INTO time VALUES (2, 'hqs01', '2001', '09:00:00', '2001-11-11', '2003-9-11 09:09:09', '1312313');
mysql> select * from time;
+----+------+-----------+------------+------------+---------------------+---------------------+
| id | name | born_year | class_time | birth_date | reg_time | timestamp_type |
+----+------+-----------+------------+------------+---------------------+---------------------+
| 1 | hqs | 2022 | 19:11:54 | 2022-03-22 | 2022-03-22 19:11:54 | 2022-03-22 19:11:54 |
| 2 | hqs01 | 2001 | 09:00:00 | 2001-11-13 | 2003-09-11 09:09:09 | 2013-12-31 03:00:00 |
+----+------+-----------+------------+------------+---------------------+---------------------+
（1）DATETIME和TIMESTAMP区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。 下面就来总结一下两种日期类型的区别。
- DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
- DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器， 操作系统以及客户端连接都有时区的设置。
- DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
- DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP）， 如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
总结相对于节省的一点空间利用率，还是时间范围的影响更大，一般还是推荐使用DATETIME
（2）未解决问题：timestamp字段，会随其他字段修改自动刷新，以此来自动存储记录最后修改时间。
4、特殊数据类型
CREATE TABLE `test`.`Untitled` (
`id` int(0) NULL,
`enum_type` enum('男','女') NULL,
`set_type` set('篮球','足球','自行车','上网','唱歌') NULL DEFAULT '',
`bit_type` bit(64) NULL,
`blob_type` blob NULL
);
INSERT INTO `test`.`spec`(`id`, `enum_type`, `set_type`, `bit_type`) VALUES (1, '男', '篮球', b'0001111111111111')
INSERT INTO `test`.`spec`(`id`, `enum_type`, `set_type`, `bit_type`) VALUES (3, '男', '上网,唱歌', b'0111111111111111')
（1）Enum类型
Enum类型，就是指定数据只能取一个指定范围内的值，也称枚举类型。
（2）set类型
Set类型也称为集合类型，是一个字符串对象，可以有零个或多个值，Set字段最多可以有64个成员，其值为表创建时规定的一列值。
（3）bit类型
bit类型主要用来定义一个指定位数的数据，其取值范围为1～64，所占用的字节数是根据它的位数决定的。
（4）blob类型
blob类型是一个二进制大对象，用来存储可变数量的二进制字符串。
blob类型分为4种：tinyblob、blob、mediumblob和longblob。
它们可容纳的最大长度不同，分别为255个字符、65535个字符、16777215个字符、4294967295个字符。
5、数据类型选择
二、创建数据表
1、创表语法
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
注意：
- 在同一张表中，字段名是不能相同
- 宽度和约束条件可选
- 字段名和类型是必须的
2、创表实例
# 删除数据库
DROP DATABASE malldb;
# 创建数据库
CREATE DATABASE malldb CHARACTER SET utf8 COLLATE 'utf8_general_ci';
# 打开数据库
use malldb;
# 创建数据表
Create Table user(
ID int Not Null ,
UserNumber varchar(10) Null ,
Name varchar(30) Null ,
UserPassword varchar(15) Null
);
# 商品类型表
CREATE TABLE goods_type (
id int PRIMARY KEY,
name varchar(30) NOT NULL
);
# 商品信息
Create Table goods_info(
ID int PRIMARY KEY ,
goodsNumber varchar(12) NOT Null ,
Name varchar(100) NOT Null ,
goodsType varchar(9) NOT Null,
price decimal(8,2) NOT NULL,
brand varchar(15) NULL
);
# 图书信息
Create Table book_info(
ID int PRIMARY KEY ,
GoodsNumber varchar(12) NOT Null ,
Name varchar(100) NOT Null ,
goodsType varchar(9) NOT Null,
price decimal(8,2) NOT NULL,
publish varchar(16) NOT NULL,
ISBN varchar(20) NOT NULL,
writer varchar(30) NOT NULL,
edition smallint NOT NULL,
publish_date date,
img varchar(50),
introduction text
);
# 出版社信息
Create Table publish_info(
ID int PRIMARY KEY ,
Name varchar(16) NOT Null ,
shortName varchar(6),
addr varchar(50),
code char(6)
);
# 用户注册信息
Create Table user_info(
ID int PRIMARY KEY ,
userNumber varchar(6) NOT Null ,
Name varchar(16) NOT Null,
passwd varchar(20) NOT NULL,
level enum('A', 'B', 'C', 'D') NOT NULL DEFAULT 'D',
telNum varchar(11) NOT NULL,
userType enum('个人用户', '企业用户') NOT NULL DEFAULT '个人用户'
);
# 客户信息
Create Table custom_info(
ID int PRIMARY KEY ,
Name varchar(16) NOT Null,
addr varchar(50),
telNum varchar(11) NOT NULL,
code char(6)
);
# 订单信息
Create Table order_info(
ID int PRIMARY KEY ,
orderNumber char(12) NOT Null,
orderStartTime datetime NOT NULL,
orderEndTime datetime NOT NULL,
shippingMethod varchar(10) NOT Null,
customName varchar(20) NOT NULL,
receiver varchar(20) NOT NULL,
payMethod varchar(8) NOT NULL,
goodsPrice decimal(10,2) NOT NULL,
freight decimal(8,2) NOT NULL,
discount decimal(10,2) NOT NULL,
payPrice decimal(10,2) NOT NULL,
orderState varchar(6)
);
# 订购商品
Create Table order_goods(
ID int PRIMARY KEY ,
orderNumber char(12) NOT Null,
goodsNumber varchar(12) NOT Null ,
num smallint NOT NULL,
freight decimal(8,2) NOT NULL,
discount decimal(10,2) NOT NULL
);
三、表操作
1、语法
# 复制表结构
# create table '新表名' like '表名';
create table order_info2 like order_info;
# 查看表结构
desc order_info2;
desc order_info;
DESCRIBE goods_type;
# 查看库中所有表
use malldb;
show tables;
# 查看创表语句
show create table user;
# 改表
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…], # 添加多个字段
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 添加新字段到第一个
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 添加新字段到指定字段之后
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段————主要针对字段类型和字段名称
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…]; # 修改字段类型
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; # 修改字段名称
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5. 改引擎
ALTER TABLE <数据表名称> Engine=<更改后的存储引擎名> ;
6. 改字段顺序
ALTER TABLE 表名 MODIFY 字段名 数据类型[完整性约束] AFTER 字段名;
# 删除表
Drop Table 表名;
2、操作案例：
# 选择当前数据库
USE malldb;
# 查看当前数据库中的所有数据表
SHOW TABLES;
# 查看数据表的结构
# 完整写法
DESCRIBE USER;
DESCRIBE order_info;
# 简写
DESC USER;
DESC book_info;
# Show Create Table语句查看创表信息
SHOW CREATE TABLE book_info;
# 将数据库“MallDB”中“图书信息2”数据表的名称修改为“图书信息表”。
# 修改表名语法:ALTER TABLE 表名 RENAME 新表名;
ALTER TABLE book_info RENAME book_info_table;
SHOW TABLES;
# 将数据表“图书信息表”中的字段“出版社publish”的数据类型修改为“int”，将字段“封面图片img”的数据类型修改为“blob”。
# 只修改修改字段类型，不修改字段名
# 语法：ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
DESC book_info_table;
ALTER TABLE book_info_table MODIFY publish INT NOT NULL;
ALTER TABLE book_info_table MODIFY img BLOB;
# 将数据表“图书信息表”中的字段名“出版社”修改为“出版社ID”，其数据类型为“int”。
# 只修改字段名，不修改字段类型
# 只要改动字段名的语法： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧/新数据类型 [完整性约束条件…];
ALTER TABLE book_info_table CHANGE publish publish_id INT NOT NULL;
DESC book_info_table;
# 在数据表“图书信息表”中的“出版日期”字段之后增加一个字段“开本”，数据类型设置为“varchar”，长度设置为“3”，约束条件为不允许为空。
# 在某个字段后面新添加一个字段
# 添加新字段到指定字段之后语法：ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE book_info_table ADD fotmat VARCHAR(3) NOT NULL AFTER publish_date;
DESC book_info_table;
# 将数据表“图书信息表”的存储引擎由“InnoDB”修改为“MyISAM”。
# 修改数据表存储引擎语法：Alter Table 表名 Engine=引擎类型;
ALTER TABLE book_info_table ENGINE=MyISAM;
SHOW CREATE table book_info_table;
# 将数据表“图书信息表”中的 “商品类型”字段调整到“价格”字段之后。
# 修改字段排列顺序语法：Alter Table 表名 Modify 字段名 数据类型[完整性约束] AFTER 字段名;
ALTER TABLE book_info_table MODIFY goodsType VARCHAR(9) NOT NULL AFTER price; # 不加数据类型无法修改
DESC book_info_table;
# 将数据表“图书信息表”中新添加的字段“开本”删除。
ALTER TABLE book_info_table drop 开本;
ALTER TABLE book_info_table drop format;
# 将数据表“图书信息表”中字段“作者”的长度修改为“35”，字段“价格”的长度修改为“9,2”。
# 只修改修改字段类型，不修改字段名
# 语法：ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE book_info_table MODIFY writer VARCHAR(35) NOT NULL;
ALTER TABLE book_info_table MODIFY price DECIMAL(9,2) NOT NULL;
DESC book_info_table;
# 将数据库“MallDB”中数据表“图书信息表”的名称重新修改为“图书信息2”。
ALTER TABLE book_info_table RENAME book_info;
SHOW TABLES;
# 删除数据表（未关联的）
# 语法：DROP TABLE 表名;
# 删除一张表
DROP TABLE user;
# 同时删除多张表
DROP TABLE order_info, book_info;
SHOW TABLES;
