MySQL 基础知识
基础知识
默认端口：3306
数据库连接格式：
基础语法：
-- CRUD
-- 添加数据
insert into [表名] ([字段], [字段], ...) values ([值], [值], ...);
-- 删除数据
delete from [表名] where ...;
-- 修改数据
update [表名] set [字段]=[值], [字段]=[值], ... where ... ;
-- 查询数据
select * from [表名] where ...;
-- 库操作
-- 创建库
create database [数据库名];
-- 删除库
drop database [数据库名];
-- 表操作
-- 创建表
create table [表名] ([字段名] [字段类型]);
-- 例子：
create table if not exists `表名` ( -- if not exists 判断表是否已存在
`runoob_id` INT UNSIGNED AUTO_INCREMENT, -- AUTO_INCREMENT 定义列的自增属性
`runoob_title` VARCHAR(100) NOT NULL, -- NOT NULL 字段不为NULL
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` ) -- 定义列为主键 可以设置多主键 逗号隔开
)ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ENGINE 设置存储引擎 CHARSET 设置编码
-- 删除表
drop table [表名] ;
-- 字段操作
-- 添加字段
alter table [表名] add [新字段名] [数据类型] [约束条件];
-- 删除字段
alter table [表名] drop [字段名];
-- 修改字段
alter table [表名] change [旧字段名] [新字段名] [新数据类型]；
内置函数：
这里的内置函数 只整理了字符串和时间的 因为一般也就是需要对字符串和时间数据进行处理
-- 字符串函数
LENGTH(s) 计算字符串长度函数，返回字符串的字节长度
CONCAT(s1,s2...,sn) 合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
LOWER(str) 将字符串中的字母转换为小写
UPPER(str) 将字符串中的字母转换为大写
LEFT(str,x) 返回字符串str中最左边的x个字符
RIGHT(str,x) 返回字符串str中最右边的x个字符
TRIM(str) 删除字符串左右两侧的空格
REPLACE 字符串替换函数，返回替换后的新字符串
SUBSTRING 截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE(str) 返回颠倒字符串str的结果
-- 时间函数
CURDATE 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE 两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROMUNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIXTIMESTAMP互为反函数
MONTH 获取指定日期中的月份
MONTHNAME 获取指定日期中的月份英文名称
DAYNAME 获取指定曰期对应的星期几的英文名称
DAYOFWEEK 获取指定日期对应的一周的索引位置值
WEEK 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR 获取年份，返回值范围是 1970〜2069
TIMETOSEC 将时间参数转换为秒数
SECTOTIME 将秒数转换为时间，与TIMETOSEC 互为反函数
DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME 时间加法运算，在原始时间上添加指定的时间
SUBTIME 时间减法运算，在原始时间上减去指定的时间
DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值
WEEKDAY 获取指定日期在一周内的对应的工作日索引
聚合函数：
AVG() 求平均值
COUNT() 统计行的数量
MAX() 求最大值
MIN() 求最小值
SUM() 求累加和
流程控制：
IF(test,t,f) 如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
CASE
WHEN[test1] THEN [result1]
...
ELSE [default]
END 如果testN是真，则返回resultN，否则返回default
CASE [test]
WHEN[val1] THEN [result]
...
ELSE [default]
END 如果test和valN相等，则返回resultN，否则返回default
分页实现：
数据类型：
数值类型：
| 型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
时间类型：
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型：
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
系统变量：
变量分为系统变量和用户自定义变量
-
系统变量以 @@ 开头
-
全局变量：全局变量影响 MySQL 服务的整体运行方式
-
会话变量：会话变量影响具体客户端连接的操作
-- 所有的全局变量信息 SHOW GLOBAL VARIABLES; -- 当前会话相关的所有会话变量以及全局变量 SHOW SESSION VARIABLES;
-
-
用户自定义变量以 @ 开头
MySQL结构：
存储引擎：
| 存储引擎 | 描述 |
|---|---|
| ARCHIVE | 用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。 |
| CSV | 在存储数据时，会以逗号作为数据项之间的分隔符。 |
| BLACKHOLE | 会丢弃写操作，该操作会返回空内容。 |
| FEDERATED | 将数据存储在远程数据库中，用来访问远程表的存储引擎。 |
InnoDB |
具备外键支持功能的事务处理引擎 |
| MEMORY | 置于内存的表 |
| MERGE | 用来管理由多个 MyISAM 表构成的表集合 |
MyISAM |
主要的非事务处理存储引擎 |
| NDB | MySQL 集群专用存储引擎 |
InnoDB：
简介：
InnoDB是事务型数据库的首选引擎 InnoDB是默认的MySQL引擎
支持行锁定和外键 支持事务安全表（ACID）
文件结构：
存储结构：InnoDB存储表时 会将表的定义和数据索引等信息分开存储
- .frm 文件：
- .ibd 文件：
持久化：
- MySQL的表数据是存放在磁盘上的 大量的存取操作是非常消耗性能的
- 为了提升性能InnoDB提供了缓冲池(Buffer Pool)
- 读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取再放入缓冲池
- 写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中（这一过程称为刷脏） 刷脏是随机IO 性能较差
逻辑存储结构：
- 表空间：
- 系统表空间： 包含数据字典、双写缓冲、变更缓冲区、undo日志
以及在系统表空间创建的表的数据和索引 - 独立表空间：5.6.6版本之后InnoDB会为每张表单独建立表空间
表空间中 - ...
- 系统表空间： 包含数据字典、双写缓冲、变更缓冲区、undo日志
- 段：
段分为索引段、数据段、回滚段等
索引段：B+树的非叶子结点部分
数据段：B+树的叶子结点部分
回滚段：用于数据的回滚和多版本控制。一个段包含256个区(256M大小)。 - 区：区是页的集合 一个区包含64个连续的页 默认大小为 1MB (64*16K)
- 页：
- InnoDB中页为基本单位 来管理存储空间
默认大小：16KB - InnoDB中B+树的每个节点都是一个数据页 数据页之间不必要是物理连续的
因为数据页之间有双向链表来维护着这些页的顺序 - 常见类型有FSP_HDR、INODE、INDEX等类型
所有类型页结构都一样 文件头、页数据、文件尾
页数据根据类型不一样
- InnoDB中页为基本单位 来管理存储空间
- 行：
图示：
MyISAM：
MyISAM基于ISAM存储引擎 并对其进行扩展
它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一
MyISAM拥有较高的插入、查询速度 但不支持事务
