mysql的基础命令
本文所用mariadb版本
sql语句的分类
- DDL【data definition language】 数据定义语言,用来维护存储数据的结构。代表指令:
create, drop, alter
- DML【data manipulation language】 数据操纵语言,用来对数据进行操作。代表指令:
insert,delete,update
- DML中又单独分了一个DQL,数据查询语言,代表指令:
select
- DCL【Data Control Language】 数据控制语言,主要负责权限管理和事务。 代表指令:
grant,revoke,commit
0.连接mysql
默认情况下,我们的mysql没有密码,直接使用如下命令,就能链接上mysql
mysql数据库文件路径
mysql的语句是大小写不敏感的,大小写都可以。
0.1 查看有谁连接了
该命令可以查看当前有谁连接了,所在数据库是什么,正在执行的cmd是什么
0.2 查看引擎
mysql支持多种存储引擎
存储引擎是:数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
每个引擎各有其优势。可以用下面的语句查询当前支持的引擎
1.数据库
1.1 创建
上面的命令创建了一个名为hello
的数据库。对于mysql服务端而言,其实际上是在数据文件路径中,帮我们创建了一个对应的文件夹
如果是mysql5.7
,在创建语句之前新增一个show
,可以看到当前执行语句的详细信息。我当前使用的是高版本的mariadb,不支持这个操作
创建数据库或者表的时候,我们还可以给名称带上如下符号作为分割符
带上该符号后,可以用于区分你的表名和数据库的内置语句。建议创建、删除操作的时候,都给名字带上这个符号。
当然,创建的数据库、表名最好不要和mysql
内置语句冲突,这和写代码的时候不要用编程语言的关键字/库函数名来命名变量一样。
1.2 查看已有
查看当前已有数据库
1.3 进入
进入这个数据库
1.4 删除
如下,我先是创建了一个test1
数据库
用下面这个命令删除该数据库
成功删除
数据文件路径中,test1文件夹也被对应删除
1.5 字符集和校验规则
字符集会有自己对应的校验规则
- 字符集用于将数据以特定编码存入数据库
- 校验规则用于取数据时对数据进行校验
修改字符集的时候,mysql会为我们自动指定对应的校验规则
1.5.1 字符集
一个数据被存放的时候,是会有其所用的编码的。编码不相同,会导致数据在不同软件中显示出不同的结果。
比如 我们使用VS写的C语言代码,文件内的中文注释放入
devc++
之中,有可能会乱码,这就是字符编码不匹配的原因
show variables like
语句可以查看mysql的内置变量,其中如下变量就是数据库字符集的编码格式
可以看到,为utf-8
在配置mariadb的时候,就已经修改过了数据库的编码规则,所以所有相关字符集编码都是utf-8
使用如下命令可以查看数据库支持的所有编码
1.5.2 校验规则
如下可以看到,当前的校验规则都是utf-8
相关的
查看系统中所有校验集
结果很长,就不全贴出来了。能看到每一个编码集都对应了一个字符集
1.5.3 创建库时指定
其中[]
代表是可选项
- CHARACTER SET 指定数据库采用的字符集
- COLLATE 指定数据库字符集的校验规则
如果在创建数据库时没有指定字符集和校验规则,则会采用数据库默认的。
在数据库的文件夹中,有一个db.opt
文件,其中就包含了当前数据库使用的字符集和校验规则
如果在创建的时候指明字符集编码
查看db.opt
,可以看到系统自动指定了gbk字符集对应的校验和
同时指定字符集和编码
执行成功后,查看本地文件
成功设置了字符集和编码规则
虽然在创建的时候可以这么做,但我们保持默认配置的utf8即可
1.5.4 校验的影响
通过指定不同的编码,我创建了两个校验规则不同的数据库
其中一个的校验规则是utf8_general_ci
,另外一个是utf8_bin
utf8_bin
先进入test_bin
数据库
创建一个student表,只有一个字符串类型的name字段
插入如下数据
查看已有数据,分别是大小写的abc
查询数据,可以看到,返回的结果是只有小写a的
执行排序(升序)后输出,得到如下结果。我们知道,小写字母的ASCII是在大写字母的之后的,所以这个排序结果是正确的。
utf8_general_ci
在这个数据库中进行相同的插入工作,创建表,插入大小写abc(命令同上不再重复)
执行查询语句,发现当我们查询小a的时候,同时返回了A和a的结果
排序的时候,大小a没有按ASCII码顺序排,而是放到了一起
结论
- utf8_bin:查询的时候区分大小写
- utf8_general_ci:查询时忽略大小写
在实际场景中,我们就可以根据需求,选择这两个校验规则中的其一作为我们数据库的校验规则。
这两个只是众多校验规则之一,更多编码的区别,还得等后续慢慢探寻。在一般场景中,使用utf8是完全足够的。
1.6 修改
一般情况下,我们修改的是数据库的字符集和校验规则。
示例如下,修改数据库test_ci
的字符集为gbk
修改后查看配置文件,字符集和校验规则确实变化了(系统自动查找对应的校验规则)
也可以同时修改校验规则,这里和新建数据库时的操作是一样的
1.7 备份数据库
1.7.1 备份一个数据库
如下操作是在linux命令行中执行的
示例
这会在当前路径中出现了一个sql
文件,其内容如下。
我们会发现这里面的内容其实都是sql语句,如果将这个备份导入到另外一个数据库中,实际上就是将历史的所有sql语句全部执行一遍!
除此之外,复制
/var/lib/mysql
下的数据库文件也是可行的一种备份方案。但并不推荐这么做!
1.7.2 备份数据库中的多张表
1.7.3 同时备份多个数据库
1.7.4 还原
如下命令需要在mysql中执行
数据库中的内容和原有数据库是完全相同的
1.7.5 化简
如果你看过了上面对校验影响的验证,其中我往test_bin数据库中使用6条语句插入了大小写的abc。
但当我们执行备份的时候,可以看到备份出来的sql文件,六个语句被合并了!
这也是mysql备份的智能之处之一!
2.表
如下命令需要先进入database才能执行
2.1 创建
基础的创建操作如下。其中末尾的字符集、校验规则、存储引擎的设置项可以留空不填
- field 列名
- datatype 列的类型
示例如下,创建了一个student表,并配置了两个键值name和age,类型分别为字符串和int
除了基础操作之外,我们还可以给这个表新增一个comment作为备注
用下面的语句可以查看创建表时候的操作
这部分和备份的时候的sql也是一样的,mysql会帮我们自动规整语句,使其语法规则更规整;
如下语句也能看到创建表时所用命令
二者结果是类似的
2.2 查看已有表
2.3 查看表的属性/结构
2.4 删除该表
2.5 修改表的结构
注意,这里是对表的属性的修改,并不是对数据的修改!
比如我想新增字段,或者修改已有字段的属性,或者删除字段
2.5.0 注意事项
请注意:修改表的字段是一个很麻烦的操作,其可能会涉及到已有数据的有效性问题
- 请在生产环境上线前的测试阶段,完成表字段的定义!
说人话就是,前期设计要想的尽量到位,避免后期表中已有数据的时候修改表的属性!
2.5.1 新增字段
比如我给上方2.5
中出现过的stu_test
表新增一列,可以用如下的命令
此时可以看到,已有数据都多了一个avatar
字段,且这些已有数据的新字段都为NULL
说明新增字段并不会影响旧数据的旧字段。我们可以在新增字段之后,给旧的数据修改默认值
2.5.2 删除字段
如果表中的数据只剩一列(只有一个字段)是不能删除这个字段的(只能删除这个表)
使用如下命令,删除刚刚新增的avatar
字段
可以看到avatar列被删除了
在具体场景中,如果需要给已有用户新增头像
,一般存放的是头像的文件路径(而不是图片),此时我们就可以设计一个默认头像,并在新增字段的时候将默认值(默认的头像文件路径)修改为这个默认头像图片的路径。
比如我现在的头像存储的文件路径是
此时存放的用户头像文件就应该放到这个目录中,而mysql中只需要存放文件的路径。
比如用户上传了一个头像图片,我们将头像图片保存到这个路径,再往sql中的avatar字段插入路径
设计一个默认头像,也是放在这个路径中
这时候新增avatar字段的时候,就可以指定默认值
这时候就能看到,旧数据的头像都是默认头像了!
默认头像总好过没有图片嘛!
2.5.3 修改字段类型
在设计stu_test
表的时候,只给name分配了30个字节的空间。此时如果来了个名字特别特别长的新同学(有些地方的人名确实很长)
我们发现30个字节存不下这个名字了,就可以考虑将名字字段的长度增加
如下,成功修改!
因为是增加字段的长度,所以也不影响已有数据
如果是减小字段长度,就需要确认已有数据是否有超过新长度的内容了!比如从60减小到30,那么长度为40的数据就会出现错误!
那如果是将varchar直接改成int,或者将int改成char,会发生什么呢?
表中的id字段是一个int,尝试将其修改成char
可以看到,其不仅成功变成了字符串类型,已有的值也没有出错。毕竟int数字改成字符串是可行的。那反过来呢?
此时我往表中插入了一个id为kb
的数据,再尝试将id从varchar改回int类型
此时就会报错,kb
不符合int类型;修改失败了!
所以,修改字段的类型,需要保证原有类型和目标类型之间是可以正常双向转换的!
2.5.4 修改字段名字
如下使用change
修改字段id -> num
,同时还需要注明属性(也可以修改属性)
这里可以看到 0 rows affected
,因为我们修改的是字段名字而已,所以不会影响到数据
2.6 修改表名
可以看到修改成功了
3.数据
3.1 插入
往刚刚创建的students表里面插入两个数据
查看表中数据
3.2 查看表中已有值
该语句作用为查看student表中的所有值