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;

image-20210405080910542

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

创建表空间

基础创建:CREATE TABLE

CREATE TABLE语句表示我们创建表的语句,想要执行成功该语句,必须拥有CREATE权限。

如果我们通过help create table查看该SQL语句的语法,我们会发现该语法真的很长,下面我们在实际操作中来分析

修饰限定符:TEMPORARY

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

没有实际用过,顺嘴一提

修饰限定符:IF NOT EXISTS

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

image-20210405082206775

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

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

字段类型

在MySQL中,每张表只能包含4096个字段,而且每个字段必须指定数据类型。

MySQL为我们提供了很多的数据类型,如何选择合适的数据类型,这里给大家一个忠告:

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

关于字段的类型就不一一介绍了,我这里列出一张我们常用的类型,当做参考

数值类型
类型范围对应表示
BIT1 ~ 64bit
TINYINT-128 ~ 127tinyint
INT对应Java中intint
BIGINT对应Java中longbigint
FLOAT对应Java中floatfloat
DOUBLE对应Java中doubledouble(20,8)
DECIMAL对应Java中BigDecimaldecimal(20,8)

*20是位数的总和(精度),8*是小数点后的位数(小数位数)

时间类型
类型范围
DATE只保存日期
DATETIME可保存到毫秒
TIMESTAMP1970-01-01到2038-01-19
字符串类型
类型说明对应表示
CHAR固定长度,最大为:255char(255)
VARCHAR可变长度varchar(10)
TEXT表示内容,还有LONGTEXTtext
ENUM枚举类型,数据值只能从设置的列表中获取enum(‘是’,‘否’)

CHARVARCHAR的区别:

  • 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;

image-20210407065559078

修改表字段

有这样一种场景,在工作中数据表已经创建好并且开始开发,开发过程中发现数据表中某些字段缺失或者类型设置出现问题,这种情况下我们只能操作指定字段,这时就用到了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 :当前时间

image-20210407070955058

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;

image-20210408072322873

看到没有,rename column还可以交换表中的字段

修改字段类型的方式

除了通过CHANGE之外,还可以通过如下方式进行修改类型

alter table tb_user modify column login_name varchar(48)

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

image-20210408072745776

修改完类型之后,会冲掉之前设置的defaultcomment等的属性,如果想要继续存在的话,那么在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来查看

以下为各种情况执行结果

image-20210409074055540

最后的话

具体更多更精彩的内容,请通过一键翻译查看官网(英文好的人除外):
创建表

删除表

修改表

数据类型

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值