数据库操作
字符集与校验规则
当我们创建数据库没有指定字符集和校验规则时,系统使用默认字符集:utf8
,校验规则
是:utf8_ general_ ci
,这个校验规则中的 ci
就是Case insensitive意为不区分大小写
创建一个使用utf8
的字符集,并带校对规则为utf8_general_ci
的数据库。
1 | create database DBName charset=utf8 collate utf8_general_ci; |
查看系统默认字符集、默认校验规则
1 | show variables like 'character_set_database'; |
支持的字符集、支持的校验规则
1 | show charset; |
创建数据库
1 | mysql> create database myDB; |
MySQL 建议我们关键字使用大写,但是不是必须的。
数据库名字的反引号,是为了防止使用的数据库名刚好是关键字/*!40100 DEFAULT CHARACTER SET utf8 */
这个不是注释,表示当前Mysql版本大于4.01版本,就执行这句话
修改数据库
对数据库的修改主要指的是修改数据库的字符集,校验规则
例如把 mytest 数据库字符集改为 gbk
1 | alter database mytest charset=gbk |
删除数据库
不要随意删除数据库,否则还是容易从删库到跑路的
1 | drop databse [if exists] 数据库名称 |
如果加上if exists
那么删除一个不存在的数据库也不会出错,但是会有警告(查看警告就使用show warnings;
),如果不加if exists
去删除一个不存在的数据库就会报错
1 | mysql> drop database mydbs; |
备份和恢复
1 | mysqldump -P3306 -uroot -p -B 数据库名称 > 路径+数据库名称.sql |
使用示例
1 | mysql> show databases; |
备份后会生成mydb1.sql文件,通过执行这个sql脚本就会恢复数据库:
1 | mysql> source C:/Users/15291/Desktop/mydb1.sql; |
如果备份的不是整个数据库,而是其中的一张表:
1 | mysqldump -u root -p 数据库名 表名1 表名2 > ./mytest.sql |
同时备份多个数据库:
1 | mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路路径 |
如果备份一个数据库时,没有带上-B参数, 在恢复数据库时,需要先创建空数据库,然后使用数据库,再使用source来还原
查看连接情况
可以告诉我们当前有哪些用户连接到我们的MySQL,如果查出某个用户不不是你正常登陆的,很有可能数据库被人入侵了。如果发现数据库比较慢时,可以用这个指令来查看数据库连接情况:
1 | mysql> show processlist; |
表的操作
创建表
1 | CREATE TABLE table_name ( |
不同的存储引擎,创建表的文件不一样,这个在Myisam
存储引擎中说到过
假设users表存储引擎是Myisam
,在数据目中有三个不同的文件,分别是:
users.frm:表结构
users.MYD:表数据
users.MYI:表索引
查看表结构
1 | mysql> desc users; |
修改表/表结构
1 | ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...); |
使用示例:
在users表的password字段后添加assets字段,类型为varchar(50),备注为图片路径
1 | mysql> alter table users add assets varchar(50) comment '图片路径' after password; |
修改users表中name字段的类型为varchar(30)
1 | mysql> alter table users modify name varchar(30); |
删除users表中assets字段
1 | mysql> alter table users drop assets; |
对user表的名字进行重命名
1 | mysql> alter table users rename to user; |
对user表中的name字段进行重命名并更改字段类型
1 | mysql> alter table user change name xingming varchar(50); |
删除user表
1 | mysql> drop table user; |
Mysql数据类型
数值类型
tinyint-bigint
这是有符号的范围,无符号的返回自行推导,和C的无符号是一致的
在MySQL中,整型可以指定是有符号的和无符号的,默认是有符号的。
可以通过UNSIGNED来说明某个字段是无符号的,但是一般遇到存储类型不足以存储数据的大小时,应该换成更大的类型,而不是换成对应的无符号类型!
bit
bit[(M)]
: 位字段类型。M表示每个值的位数,范围从1到64。如果M被忽略,默认为1
注意bit类型,bit字段在显示时,是按照ASCII码对应的值显示!
如果我们有这样的值:只存放0或1,这时可以定义bit(1),这样可以节省空间!
float
float[(m, d)] [unsigned]
: M指定显示长度,d指定小数位数,占用空间4个字节!
decimal
浮点数float与定点数decimal的存储方式的不同决定了他们的用途不同:《浮点数的存储方式》
decimal用于保存必须为确切精度的值,很显然底层是用字符串来存储的
1 | decimal(m, d) [unsigned] |
定点数m指定长度,d表示小数点的位数
float表示的精度大约是7位。decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略,默认为0.如果m被省略,默认是10。
建议:如果希望小数的精度高,推荐使用decimal(对于银行这种对小数要求非常高的业务,decimal还是大有用处的)
字符串类型
char
1 | char(L) |
固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以为255,这里需要注意的是一个汉字和一个字母均被视为一个字符!
varchar
1 | varchar(L) |
可变长度字符串,L表示字符长度,最大长度65535个字节
关于varchar(len),len到底是多大,这个len值,和表的编码密切相关:varchar长度可以指定为0到65535之间的值,但是有两个字节用于记录数据大小,所以说有效字节数65532。
当我们的表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844[因为utf中,一个字符占用3个字节],如果编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符占用2字节)
char和varcahr比较
两者如何选择?
如果数据确定长度都一样,就使用定长(char),比如:身份证,手机号,md5
如果数据长度有变化,就使用变长(varchar), 比如:名字,地址,但是你要保证最长的能存的进去
定长的磁盘空间比较浪费,但是效率高
变长的磁盘空间比较节省,但是效率
日期和时间类型
常用的日期有如下三个:
- datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从1000到9999,占用八字节
- date:日期 ‘yyyy-mm-dd’,占用三字节
- timestamp:时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss格式和datetime完全一致,占用四字节
使用示例:
1 | mysql> create table birthday (t1 date, t2 datetime, t3 timestamp); |
enum和set
语法:
enum:枚举,“单选”类型;
1 | enum('选项1','选项2','选项3',...); |
该设定只是提供了若干个选项的值,最终一个单元格中,实际只存储了其中一个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,3,….最多65535个;当我们添加枚举值时,也可以添加对应的数字编号。
set:集合,“多选”类型;
1 | set('选项值1','选项值2','选项值3', ...); |
该设定只是提供了若干个选项的值,最终一个单元格中,设计可存储了其中任意多个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,4,8,16,32,…. 最多64个。
不建议在添加枚举值,集合值的时候采用数字的方式,因为不利于阅读。
使用示例:
1 | mysql> create table votes( |
集合查询使用find_ in_ set
函数:
1 | find_in_set(sub,str_list) |
如果sub 在str_list 中,则返回下标;如果不在,返回0; str_list 用逗号分隔的字符串。
1 | mysql> insert into votes values('Juse','登山',2); |
- 本文作者: Tim
- 本文链接: https://zouchanglin.cn/498041550.html
- 版权声明: 本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 许可协议。转载请注明出处!