MySQL - DDL操作(库/表操作)
MySQL自带的数据库
先来看MySQL中自带的库有哪些:
各库解释如下:
-
information_schema
,虚拟库,不占用磁盘空间,存储的是数据库启动后的一些参数,如用户表信息、列信息、权限信息、字符信息等。 -
performance_schema
, MySQL 5.5开始新增一个数据库,主要用于收集数据库服务器性能参数,记录处理查询请求时发生的各种事件、锁等现象 。 -
mysql
,授权库,主要存储系统用户的权限信息。 -
sys
库所有的数据源来自performance_schema
,目标是把performance_schema
的把复杂度降低,让DBA能更好的阅读这个库里的内容。让DBA更快的了解数据库的运行情况。 -
test
,MySQL数据库系统自动创建的测试数据库。test库在MySQL中特殊存在,一般部署完mysql后应当删除该库,并规定不能创建以test
和test_
字符开头的数据库。因为在MySQL中,test库对任意用户都有管理员权限,因此,线上数据库不要用test。如果已有test,添加用户时想要禁止对test库的权限,可以在mysql.db表中添加这个新用户,禁止所有权限,或者删除该表中user为空的记录,并刷新权限select * from mysql.db where db='test' \G ;
创建数据库
创建数据库语法
一般的,在MySQL中,关键字等信息推荐大写.......
数据库的命名规范
- 可以由字母、数字、下划线、@、#、$组成。
- 区分大小写,库名不能大写,也不建议以大写字母开头。
- 首字母不能是数字。
- 数据库名具有唯一性,也就是不能重名。
- 应该避开关键字,如select等。
- 不能单独使用数字。
- 最长不超过128位。
- 一般库名应该和业务相关。
- 也不要创建以
test
火test_
开头的数据库,原因在前面说过了。
关于字符集
在MySQL中,数据库默认的字符集是拉丁语:
但我们用的更多的是utf8
或者是utf8mb4
,所以我们一般在创建数据库库的时候还需要指定字符集:
关于utf8和utf8mb4的区别
CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.
MySQL在 5.5.3 之后增加了 utf8mb4
字符编码,mb4即 most bytes 4。简单说 utf8mb4 是 utf8 的超集并完全兼容utf8,能够用四个字节存储更多的字符。
但抛开数据库,标准的 UTF-8 字符集编码是可以用 1~4 个字节去编码21位字符,这几乎包含了是世界上所有能看见的语言了。然而在MySQL里实现的utf8最长使用3个字节,也就是只支持到了 Unicode 中的 基本多文本平面(U 0000至U FFFF),包含了控制符、拉丁文,中、日、韩等绝大多数国际字符,但并不是所有,最常见的就算现在手机端常用的表情字符 emoji和一些不常用的汉字,如 “墅” ,这些需要四个字节才能编码出来。
注:QQ里面的内置的表情不算,它是通过特殊映射到的一个gif图片。一般输入法自带的就是。
也就是当你的数据库里要求能够存入这些表情或宽字符时,可以把字段定义为 utf8mb4,同时要注意连接字符集也要设置为utf8mb4,否则在 严格模式 下会出现 Incorrect string value: /xF0/xA1/x8B/xBE/xE5/xA2… for column 'name'
这样的错误,非严格模式下此后的数据会被截断。
create database & create schema
你在一些地方,可能会看到如下建库语句:
不要迷惑,关于create database
和create schema
语句,在MySQL中是等价的;下面摘自MySQL8.0官网的解释:
CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.
删除
生产中禁止删库~
修改
注意,修改字符集时,修改后的字符集一定是原字符集的严格超集。
查看数据库
表操作
MySQL中的表你可以理解为是一个有严格规范的excel表格:
表中的id
、name
、age
、gender
是字段,其余每一行称为记录。
创建表
基本语法:
注意:
- 在同一张表中,字段名是不能相同。
- 宽度和约束条件可选。
- 字段名和类型是必须的。
示例:
USE
语句相当于进入一个目录,然后CREATE TABLE
语句相当于创建一个excel表格,然后再这个表格中,针对每个字段有不同的规定和注释,最后再指定这个表格的文件系统(存储引擎)和字符编码。
注意,最后一个字段后无需跟逗号。
USE
语句相当于进入一个目录,然后CREATE TABLE
语句相当于创建一个excel表格,然后再这个表格中,针对每个字段有不同的规定和注释,最后再指定这个表格的文件系统(存储引擎)和字符编码。
注意,最后一个字段后无需跟逗号。
建表规范
- 表名小写
- 不能是数字开头
- 注意字符集和存储引擎
- 表名和业务有关
- 选择合适的数据类型
- 每个列都要有注释
- 每个列设置为非空,无法保证非空,用0来填充
查看表结构
首先,我们应该进入某个数据库中use database_name;
删除
注意,生产中禁止使用
修改
不知道好过知道
复制表
最后,再来看看复制表相关的操作:
- 复制一份一摸一样的表:
- 创建一个表,复制另一张表的指定字段和记录。
- 创建一个表,复制另一张表的指定字段和记录,但是创建的表字段又想不跟被复制表字段不一致:
记录操作(DML)
现在,就要对表中的记录进行增删改这些操作了。
还是那张表:
insert
update
首先来说一个不要轻易使用的命令,即对指定字段进行全表更新:
经过set之后,全表的sname都会变成李四,如果数据量较大,非常影响性能。
根据条件更新:
条件选择一般选择具有唯一性的,如果条件不唯一,则所有符合条件的记录都将受影响,如:
注意,update必须要加where条件,而且操作要慎重。
delete
危险命令!
注意,delete命令删除是逻辑删除,不会释放磁盘空间!
现在我们再说另一条命令:
truncate
命令清空表后会释放磁盘空间。
小结:
delete
命令(DML操作)只是在逻辑层面删除了记录,但是占用的磁盘空间并没有释放;另外该表的建表结构还在。truncate
命令(DDL操作)清空表的数据页,保留表的建表结构。drop
删表操作就是逻辑和磁盘上都会删除。
为了解决delete的缺点,我们采用伪删除来解决,即使用update来代替delete,思路是为表添加一个状态字段,如果有需要删除的,就改下状态,后续的查询也根据状态来查看: