数据库基础
用数据库(与其它保存方式对比)
- 数据保存在内存中
- 优点:存取速度快。
- 缺点:数据不能永久保存。
- 数据保存在文件中
- 优点:数据永久保存。
- 缺点:
- 速度比内存操作慢,频繁的IO操作。
- 查询数据不方便。
- 数据保存在数据库中
- 数据永久保存。
- 查询方便、效率高(使用SQL语句)。
- 管理数据方便。
SQL是什么
- 定义
结构化查询语言(StructuredQueryLanguage),简称SQL,是一种数据库查询语言。- 作用
用于存取数据、查询、更新和管理关系数据系统。
MySQL是什么
- 是一个关系型数据库管理系统,由瑞典MySQLAB公司开发,属于Oracle旗下产品。
- 是最流行的关系型数据库管理系统之一。
- 在WEB应用方面,是最好的RDBMS(Relational Data Base Management System,关系数据库管理系统)应用软件之一。
- 在java企业级开发中非常常用,因为它是开源免费的,并且方便扩展。
数据库的三大范式是什么
- 第一范式
每一列都不可以拆分的原子数据项。- 第二范式
非主属性不存在部分依赖于主键,非主属性列完全依赖于主键,不能是依赖于主键的一部分。(基于第一范式)- 第三范式
非主属性之间不存在传递依赖,非主属性必须直接依赖于主键,不能是间接依赖主键。(基于第二范式)
数据库都要严格按照三范式要求吗
- 不一定。尽量遵守三范式的要求(如果不遵守,必须有足够的理由,比如性能)。事实上会经常为性能而妥协数据库的设计。
MySQL有关权限的表是什么
- 存放位置
名为“mysql”的数据库里。- 作用
控制用户对数据库的访问。- 初始化方法
由mysql_install_db脚本初始化。- 分类
- user权限表
- 记录允许连接到服务器的用户账户信息。(权限为全局级)
- db权限表
- 记录各个账号在各个数据库上的操作权限。
- table_priv权限
- 记录数据表级的操作权限。
- cloumns_priv权限
- 记录数据列级的操作权限。
- host权限表
- 配合db权限表对给定主机上数据库级操作权限做更加细致的控制。(这个权限表不受GRANT和REVOKE语句的影响。)
MySQL的binlog有几种录入格式以及它们的区别
- statement模式
- 原理
- 每一条修改数据的sql都会记录在binlog中。
- 优点
- 不需要记录每一行的变化,减少binlog日志量,节约了IO,提高性能。
- 缺点
- 保存时需要保存相关信息。(因为执行sql是有上下文的)
- 一些使用的函数之类的语句无法被记录复制。
- row模式
- 原理
- 仅保存哪条记录被修改。
- 优点
- 不记录sql的上下文。
- 缺点
- 记录每一行的改动,文件保存的信息太多,日志量太大。(因为基本上记录下来的的很多操作会导致大量行的改动,比如altertable)
- mixed模式
一种折中的方案。普通操作使用statement记录,当无法使用statement时使用row。- 注意
新版的MySQL中对row级别也做了一些优化,当表结构变化的时候,会记录语句而不是逐行记录。
数据类型
MySQL有哪些数据类型
分类 | 类型名称 | 说明 | 字节 |
---|---|---|---|
整数类型 | |||
tinyint | 很小的整数(8位进制) | 1字节 | |
smallint | 小的整数(16位进制) | 2字节 | |
mediumint | 中等大小的整数(24位进制) | 3字节 | |
int(integer) | 普通大小的整数(32进制) | 4字节 | |
bigInt | 很大的整数(64位进制) | 8字节 | |
小数类型 | |||
float | 单精度浮点数 | 4字节 | |
double | 双精度浮点数 | 8字节 | |
decimal | 压缩严格的定点数 | ||
日期类型 | |||
year | YYYY1901-2155 | ||
time | HH:MM:SS-838:59~838:59:59 | ||
date | YYYYMM-DD1000-01-01~9999-12-3 | ||
datetime | YYYYMM-DDHH:MM:SS1000-01-0100:00:00~9999-12-3123:59:59 | ||
timestamp | YYYYMM-DDHH:MM:SS1970010100:00:01UTC~2038-01-1903:14:07UTC | ||
文本、二进制类型 | |||
char(M) | M为0~65535之间的整数 | ||
varchar(M) | M为0~65535之间的整数 | ||
tinyblob | 允许长度0~255字节 | ||
blob | 允许长度0~65535字节 | ||
mediumblob | 允许长度0~167772150字节 | ||
longblob | 允许长度0~4294967295字节 | ||
tinytext | 允许长度0~255字节 | ||
text | 允许长度0~65535字节 | ||
mediumtext | 允许长度0~167772150字节 | ||
longtext | 允许长度0~4294967295字节 | ||
varbinary | 允许长度0~M个字节的变长字节字符串 | ||
binary | 允许长度0~M个字节的定长字节字符串 |
关于小数类型的使用说明
- 数值精度
MySQL允许使用非标准语法(注意其它数据库未必支持,所以迁移时不建议使用)。
- 格式
float(M,D) 、double(M,D) 或 decimal(M,D);- 说明
M 称为精度,D 称为标度。(M,D) 中 M = 整数位 + 小数位,D = 小数位。 D <= M <= 255,0 <= D <= 30)- 示例
float(5,2)
则表示-999.99 - 999.99范围内(一旦超过就会报错)。float(3,2) UNSIGNED
则表示 0 - 9.99(这表示数据无符号,即非负)
整数超出
-- 创建测试表,范围是 -9999.99 - 9999.99 CREATE TABLE test1 ( f1 FLOAT(6, 2), f2 double(6, 2), f3 decimal(6,2) ); -- 插入整数范围内的数字,可以正常插入 insert into test1 value (1234.56, 1234.56,1234.56); -- 插入范围外的数字,异常 insert into test1 value (12345.67, 1234.56,1234.56); -- [22003][1264] Out of range value for column 'f1' at row 1
小数超出
-- 创建测试表,范围是 -999.9 - 999.9 CREATE TABLE test1 ( f1 FLOAT(6, 2), f2 double(6, 2), f3 decimal(6,2) ); -- 插入整数范围内的数字,可以正常插入,小数超过范围 insert into test1 value (1234.567, 1234.567,1234.567); --f1 存储为 1234.57 --f2 存储为 1234.57 --f3 存储为 1234.57 -- 小数超过范围,四舍五入后,整数也会超过范围,返回异常 insert into test1 value (9999.995, 9999.995, 9999.995); -- [22003][1264] Out of range value for column 'f1' at row 1
注意
在 MySQL 8.0 版本,已经计划取消指定位数,及 UNSIGNED 小数类型,但创建时会有警告信息。
Specifying number of digits for floating point data types is deprecated and will be removed in a future release. UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
- 使用场景
- 三种类型都可以存储小数。
- float、double 无法保证计算精度。
- 计算时,float和double比decimal的效率要高。
- 存储钱相关的字段建议使用 decimal(16, 4) 精确到 角、分、厘、毫。
关于字符串类型的使用说明
- 存储长度
- varchar、char、text、blobvarchar都可用于存储可变长字符串,它比定长类型更节省空间。
- char其实存储的指定固定长度的字符串。(这是因为在存储不足指定位数的字符串时,会使用空格占位,当取出时会使用trim()函数去除多余空格)
- varchar是根据字符串长度来分配空间,。(比如
varchar(50)
,使用时插入只有20个字符,则实际存储的字符串有21个,这是因为它会自动包含一个字符串结束符号,所以在值保存和检索时,末尾的空格会依旧存在)。- 注意
- char 适合很短的字符串或存入的值接近一个长度。
- varchar 修改数据时,易产生碎片,使用也需要注意,可以执行数据库导出导入作业,来消除碎片。
- 避免使用 text/blob,,查询时会使用临时表,导致严重的性能开销。