数据库

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合

查询数据库

-- 查询数据库
SHOW DATABASES;
-- 过滤数据库
SHOW DATABASES LIKE 'c%';
-- 查询数据库使用大小
SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024),2)+ROUND(SUM(index_length)/(1024*1024),2),'MB') AS 'DB Size' 
FROM information_schema.`tables` WHERE table_schema='数据库名称';
-- 索引占用空间
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024),2),'MB') AS 'DB Size' 
FROM information_schema.`tables` WHERE table_schema='MES';
-- 显示数据库创建语句以及当前字符急
show create database [数据库名字]

创建数据库

-- 创建数据库 company 并设置数据库编码以及排序规则, 数据库必须使用UTF-8MB4支持现代互联网的表情存储, utf8mb4_general_ci 通用的排序规则
CREATE DATABASE IF NOT EXISTS [company] DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

修改数据库

-- 修改数据库 company; 不支持修改数据库名称
ALTER DATABASE [company] DEFAULT CHARACTER SET [数据库编码] DEFAULT COLLATE [排序规则];

删除数据库

-- 删除数据库 company 这是一个非常危险的操作请谨慎操作
DROP DATABASE IF EXISTS [company];

切换数据库

-- 进入数据库
use [company]

表

表是数据库最基本的组成对象，用来组织和存储数据。表由行和列组成，每个列包含特定类型的数据信息，一个列就是一个字段。一个数据库可以包含一个或多个表。

字段类型

数据类型（data_type）是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

数值类型

日期/时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型

二进制类型

查询库中的表

-- 显示所有表
SHOW TABLES;

-- 显示表的信息
SELECT TABLE_NAME AS "表",
TABLE_TYPE AS "类型",
`ENGINE` AS "引擎",
VERSION AS "版本",
TABLE_ROWS AS "行数",
CONCAT(ROUND(DATA_LENGTH/(1024*1024),2),'MB')  AS "数据空间",
CONCAT(ROUND(INDEX_LENGTH/(1024*1024),2),'MB') AS "索引空间",
TABLE_COLLATION AS "编码",
TABLE_COMMENT AS "备注",
CREATE_TIME AS "创建时间",
UPDATE_TIME AS "修改时间"
FROM information_schema.`tables` t WHERE table_schema='数据库名称';

创建表

基础

-- CREATE TABLE [表名] ( 字段类型 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 引擎和规则
-- INT VARCHAR 数据类型
CREATE TABLE [表名] ( 
  id INT(11),
  name VARCHAR(25),
  deptId INT(11), 
  salary FLOAT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

字段排序规则、默认值

对于 字符串类型 都是可以自定义排序的 对于所有类型都是可以设置默认值的, 默认值可以帮助我们生成默认数据

-- MySQL 对于VARCHAT CHAR 都是可以自定义排序规则的
-- COLLATE utf8mb4_general_ci 表示排序规则
-- DEFAULT '' 设置默认值
CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Not Null 、Unsigned

对于所有类型都可以使用Not Null 限制字段不能为Null 对于 数值类型 可以使用Unsigned 让数据不存储负数多一个字节存储数值类型

-- NOT NULL 表示字段不为NULL
-- int unsigned 表示无符号存储
CREATE TABLE `test` (
  `id` int unsigned NOT NULL,
  `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

主键与自增

每张表必须要有主键, 主键有且只有一个, 且主键字段类型不能太大（索引会包含主键存储） 复合主键：主键含有一个以上的字段组成, 不使用无业务含义的自增id作为主键。 联合主键：多个主键联合形成一个主键组合 PS: 自增主键的字段有且只有一个

-- 复合主键通过 PRIMARY KEY (`id`) 声明
CREATE TABLE `test` (
  `id` int unsigned NOT NULL,
  `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 复合主键, 在需要自增的列后面添加 AUTO_INCREMENT 表示自增主键
CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 联合主键, PRIMARY KEY (`id`,`value`) USING BTREE 语法表示
CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`value`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

修改表

后面完善 内容太多

移动表

移动表会导致

-- 操作: RENAME TABLE [旧表] TO [新表]
RENAME TABLE test TO test2

克隆表

-- 复制表结构
CREATE TABLE 新表 LIKE 旧表 ;

-- 复制数据
CREATE TABLE 新表 SELECT * FROM 旧表

索引

唯一索引

全文索引

冗余索引

表分区

表分区将单个表的各个部分分散到一个文件系统中。 表的不同行被分配给不同的物理分区, 称为水平分区，MySQL不支持垂直分区。 对一个表分区有很多种办法：

• RANGE; 根据落在特定的范围内的列值，将行分配给分区
• LIST; 基于一组离散值匹配选择列, 与RANCE类似，一组值列表中的一个列值的成员来定义和选择的
• HASH; MySQL HASH 函数计算对列值进行计算分区, 保证数据非常均匀的分布在一组分区上，不同值会出现相同的HASH
• KEY; 这种类似HASH规则，但是由数据库自己提供的PASSWORD()函数相同的算法，不同值不会出现一样的KEY.

**分区的列一定是表中所有主

创建分区

RANCE 分区时候会遇到非INT的数据类型，当遇到日期类型可以用函数转成天数分区，当遇到字符串类型可以计算HASH值进行分区,或者根据首尾字母分区

-- RANGE
-- 字段的值小于等于1分一个区
-- 大于1小于等于2的分一个区
-- 大于1小于等于100的分一个区
CREATE TABLE `test2` (
    ....
)
PARTITION BY RANGE (`value`)
(PARTITION [分区名 唯一] VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION [分区名 唯一] VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION [分区名 唯一] VALUES LESS THAN (100) ENGINE = InnoDB);

-- LIST
-- 通过指定的值进行分配
CREATE TABLE `test2` (
    ....
)
PARTITION BY LIST (`value`)
(PARTITION [分区名 唯一] VALUES IN (1, 2, 3) ENGINE = InnoDB,
 PARTITION [分区名 唯一] VALUES IN (4, 5, 6) ENGINE = InnoDB,
 PARTITION [分区名 唯一] VALUES IN (11, 12, 13) ENGINE = InnoDB
);

-- HASH
-- 通过指定的值进行分配
CREATE TABLE `test2` (
    ....
)
PARTITION BY HASH (`value`)；

-- KEY
-- 通过指定的值进行分配
CREATE TABLE `test2` (
    ....
)
PARTITION BY KEY ()；

修改分区

-- RANGE
-- 字段的值小于等于1分一个区
-- 大于1小于等于2的分一个区
-- 大于1小于等于100的分一个区
ALTER TABLE [表] PARTITION BY RANGE([字段])
(
  PARTITION [分区名 唯一] VALUES LESS THAN (1),
  PARTITION [分区名 唯一] VALUES LESS THAN (2),
  PARTITION [分区名 唯一] VALUES LESS THAN (100)
);


-- LIST
-- 通过指定的值进行分配
ALTER TABLE [表] PARTITION BY LIST([字段])
(
  PARTITION [分区名 唯一] VALUES IN (1, 2, 3),
  PARTITION [分区名 唯一] VALUES IN (4, 5, 6),
  PARTITION [分区名 唯一] VALUES IN (11, 12, 13)
);

-- HASH
-- 通过指定的值计算HASH动态进行分配
ALTER TABLE [表] PARTITION BY HASH([字段]);

-- KEY
-- 通过指定的值计算HASH动态进行分配
ALTER TABLE [表] PARTITION BY KEY();

数据库会物理切割表进行分区数据存储

删除分区

ALTER TABLE [表] DROP PARTITONING [分区名];

子分区

将每一个分区进一步划分为一个分区表，称为子分区或符合分区。

ALTER TABLE [表] PARTITION BY HASH([字段])
    SUBPARTITIONS 4 (
      PARTITION [分区名 唯一] VALUES LESS THAN (1),
      PARTITION [分区名 唯一] VALUES LESS THAN (2),
      PARTITION [分区名 唯一] VALUES LESS THAN (100),
      PARTITION [分区名 唯一] VALUES LESS THAN MAXVALUE,
  )

分区修剪

分区修建只使用查询语句, 但查询语句和许多的DML语句都支持显示地选择分区。

-- WHERE 指定了value = 1 的分区, 则MySQL会对特定分区进行扫描.
SELECT * FROM [表] WHERE value = 1;

指定分区

指定分区是显示指定分区进行查询 修改 删除, 插入不支持分区操作

-- 查询
SELECT * FROM [表] PARTITION(v2) WHERE id = 1;
-- 删除
DELETE FROM [表] PARTITION(v2) WHERE id = 1;
-- 修改
UPDATE [表] PARTITION(v2) SET id = 2 WHERE id = 1;

保留分区删除分区内数据

ALTER TABLE [表] TRUNCATE PARTITION [分区1];

优化HASH和Key分区

调制HASH或者key 分区数量控制分区的性能。

-- 减少多少个分区
ALTER TABLE [表] COALESCE PARTITION [数量];

-- 增加多少个分区
ALTER TABLE [表] ADD PARTITION PARTITION [数量];

分区注意事项

• RANGE分区如果分区中事先添加不足的数量就会导致插入的数据错误
• 对于过期的数据可以可以删除分区，与传统的DELETE FROM 相比速度是非常快的，在高效存档数据非常有效

分区信息

-- 查询分区信息
SHOW CREATE TABLE [表];

-- 查询分区状态
SHOW CREATE STATUS LIKE [表];

-- 更多信息
SELECT CREATE TABLE INFORMATION_SCHEMA.PARTITIONS\G
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 'test2'