MySQL深入研究：操作表

江南才子

俗世游子：专注技术研究的程序猿

　　
说在前面的话


　　在之前已经聊完了MySQL的安装和用户管理，这一节我们开始进入真正的主题。
　　在业界我们一般只做这四件事情：

• C(创建)R(读取)U(修改)D(删除)
  

　　今天我们着重聊CUD，不过不是对数据，而是对表空间的操作

回顾前面的操作

　　我们不要通过root账户来操作了，还记得前面用户管理的操作么，我们现在先来创建一个完整的用户:mysqler，之后就通过创建的用户来进行操作

# 创建角色
create role 'mysqlApp'@'%';
# 创建库
create database mysqlApp;
# 角色授权
grant all on mysqlApp.* to 'mysqlApp'@'%';
# 创建用户
create user 'mysqler'@'%' identified with mysql_native_password by 'MYSQLmysql123!@#' default role mysqlApp;
# 刷新
flush privileges;

　　


　　好了，准备工作到此结束，现在开始进入正题

创建表空间

基础创建：CREATE TABLE

　　CREATE TABLE语句表示我们创建表的语句，想要执行成功该语句，必须拥有CREATE权限。
　　如果我们通过help create table查看该SQL语句的语法，我们会发现该语法真的很长，下面我们在实际操作中来分析

修饰限定符：TEMPORARY

　　该限定表示创建的是一张临时表，只有在当前会话中可见。如果退出当前会话，那么就会删除该临时表

　　没有实际用过，顺嘴一提

　　
修饰限定符：IF NOT EXISTS


　　这里防止如果表存在的时候发生错误，影响到后续SQL语句的执行，在删除表的时候也会用到该表示方式


　　还需要注意的是，在MySQL中是通过database来做隔离的，所以如果我们没有通过use到某一个database中且想要创建表的话，我们需要这样来操作：

create table mysqlApp.test(字段);
create table `mysqlApp`.`test`(字段);
# 切记不能通过如下方式来表示
create table `mysqlApp.test`(字段);

　　
字段类型


　　在MySQL中，每张表只能包含4096个字段，而且每个字段必须指定数据类型。
　　MySQL为我们提供了很多的数据类型，如何选择合适的数据类型，这里给大家一个忠告：

• 选择正确存储数据的最小数据类型，可以降低MySQL磁盘存储空间
  

　　关于字段的类型就不一一介绍了，我这里列出一张我们常用的类型，当做参考
数值类型
　　|    类型     | 范围                 | 对应表示      |
　　| :---------: | -------------------- | ------------- |
　　|   BIT   | 1 ~ 64               | bit           |
　　| TINYINT | -128 ~ 127           | tinyint       |
　　|   INT   | 对应Java中int        | int           |
　　| BIGINT  | 对应Java中long       | bigint        |
　　|  FLOAT  | 对应Java中float      | float         |
　　| DOUBLE  | 对应Java中double     | double(20,8)  |
　　| DECIMAL | 对应Java中BigDecimal | decimal(20,8) |

20是位数的总和（精度），*8*是小数点后的位数（小数位数）

　　时间类型

　　|     类型      | 范围                   |
　　| :-----------: | ---------------------- |
　　|   DATE    | 只保存日期             |
　　| DATETIME  | 可保存到毫秒           |
　　| TIMESTAMP | 1970-01-01到2038-01-19 |
字符串类型
　　|    类型     | 说明                                   | 对应表示        |
　　| :---------: | -------------------------------------- | --------------- |
　　|  CHAR   | 固定长度，最大为：255                  | char(255)       |
　　| VARCHAR | 可变长度                               | varchar(10)     |
　　|  TEXT**   | 表示内容，还有**LONGTEXT             | text            |
　　|  ENUM   | 枚举类型，数据值只能从设置的列表中获取 | enum('是','否') |
CHAR**和**VARCHAR的区别：

• char**在保存的时候会自动删除末尾的空格，且检索效率和写效率比**varchar高
  
• varchar可以使用满足需求的最小长度，而且在存储的时候会额外占用空间来存储保存长度：
  

　　- 小于等于255，占用1个字节存储
　　- 超过255，占用2个字节存储
ENUM的使用方式

• 我们可以通过枚举类型来代替固定格式的字符串类型，比如性别等
  
• MySQL中枚举下标索引值从1开始，在插入数据的时候可以直接传入索引下标，也可以传入枚举列表中的值
  

　　特殊字段

• IP
  

　　IP本质是32位无符号整数而不是字符串，可以使用INET_ATON()**和**INET_NTOA()在这两种表示方法之间转换

# 转换为整数
select inet_aton('1.1.1.1')
# 转换会IP
select inet_ntoa(16843009)

• 金额
  

　　金额我们在存储的时候默认单位是元，一般会采用DECIMAL**存储，但是在计算的时候某些数值还是会存在**精度缺失**的问题，针对这一现象，我们可以将元换算成分，然后通过**bigint来存储

　　计算机存储数据是通过二进制来存储，正常数据进行二进制转换的时候如果无法正常转换，那么计算的时候就会问题

　　
其他属性


DEFAULT
　　表示当前字段如果没有指定插入数据的默认值
AUTO_INCREMENT
　　自增，如果没有步长那么就会从1开始；每张表只能有一列作为自增列，且必须设置在索引列上，

　　一般将其设置在主键字段上

　　添加之后我们想检索值，可以通过 LAST_INSERT_ID()函数

select LAST_INSERT_ID();

　　COMMENT

　　表示每个字段的注释说明
ENGINE
　　MySQL中包含多种存储引擎，而其中我们常用的存储引擎包括：

• InnoDB
  
• MyISAM
  

　　MySQL默认情况下采用InnoDB，如果想要修改的话，通过该属性来修改

案例：新建User表

　　有了以上的铺垫，相信大家多少明白了，现在我们创建一张表出来吧，下面来个案例：

create table if not exists tb_user(
  id bigint primary key auto_increment comment '主键',
  login_name varchar(48) comment '登录账户',
  login_pwd char(36) comment '登录密码',
  account decimal(20, 8) comment '账户余额',
  login_ip int comment '登录IP'
) charset=utf8mb4 engine=InnoDB comment '用户表';

　　当前表的创建完全没有任何问题

• primary key表示主键索引，先记住，相关内容下一节介绍
  

　　创建完成之后，可以通过desc table_name的方式来查看表

desc tb_user;

　　



修改表字段

　　有这样一种场景，在工作中数据表已经创建好并且开始开发，开发过程中发现数据表中某些字段缺失或者类型设置出现问题，这种情况下我们只能操作指定字段，这时就用到了alter table。该语句可以修改表结构

help alter table

　　语法格式很长，慢慢来看吧

　　背景：已上面创建的tb_user为例

修改存储引擎

　　tb_user表中的存储引擎设置为了InnoDB，如果我们想要修改的话，可以这样

alter table tb_user engine = MyISAM;
alter table tb_user engine = InnoDB;

　　简单带一下，这个用的应该不多

添加/删除字段

　　ADD添加字段，DROP删除字段，比如：

# 添加字段create_time，设置默认值为当前时间
alter table tb_user add column create_time timestamp default CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP ：当前时间

　　

alter table tb_user drop column create_time;

　　这样就删除了create_time字段，如果想要多个添加或者删除的话，直接通过,分隔就行
　　这没什么好说的，下面还有一些操作也是比较常用的

重命名，修改字段类型

　　重命名字段的两种方式

• CHANGE
  

ALTER TABLE tb_user CHANGE account use_account BIGINT default 0;

　　这种方式在于，除了可以修改字段名称之外，还可以调整字段的类型，相当于以下两种方式的结合体
　　但是如何仅仅是想修改一下字段名称，那么还是推荐采用下面的这种方式

• RENAME COLUMN
  

ALTER TABLE tb_user RENAME COLUMN use_account TO account;

　　这样就可以做到单独修改字段名称
　　当前方式还有一个好玩的功能

alter table tb_user add column login_email varchar(88) default '' comment '登录邮箱';

　　这里添加了一个比较特别的字段，下面我们来做一个操作：

ALTER TABLE tb_user RENAME COLUMN login_name TO login_email, RENAME COLUMN login_email TO login_name;

　　


　　看到没有，rename column还可以交换表中的字段
修改字段类型的方式
　　除了通过CHANGE之外，还可以通过如下方式进行修改类型

alter table tb_user modify column login_name varchar(48)

　　这里如果使用了不存在的类，那么会报错，而且需要注意一点：


　　修改完类型之后，会冲掉之前设置的default、comment等的属性，如果想要继续存在的话，那么在modify后面要跟上相关信息，比如

alter table tb_user modify column login_name varchar(48) default '' comment '登录账号'

　　
删除表


　　如果我们想要删除某张表的话，那么可以通过drop table来操作，通过help drop table来查看其语法格式，相当简单

drop table tb_user;
drop table if exists tb_user;

　　如果我们删除的是一张不存在的表，那么语法1**的情况会出现错误，**语法2会删除表存在的情况，如果表不存在，那么MySQL会为当前操作生成一条警告，我们可以通过 SHOW WARNINGS来查看
　　以下为各种情况执行结果


最后的话

　　具体更多更精彩的内容，请通过一键翻译查看官网（英文好的人除外）：
创建表　　删除表
修改表数据类型