侧边栏壁纸
博主头像
GabrielxD

列車は必ず次の駅へ。では舞台は?私たちは?

  • 累计撰写 471 篇文章
  • 累计创建 108 个标签
  • 累计收到 9 条评论

目 录CONTENT

文章目录

【学习笔记】MySQL

GabrielxD
2022-02-04 / 0 评论 / 0 点赞 / 45 阅读 / 5,167 字 / 正在检测是否收录...

MySQL

1、初识MySQL

MySQL介绍

安装MySQL

  1. MySQL下载: MySQL :: Download MySQL Community Server (Archived Versions)

  2. 解压

  3. 配置环境变量

  4. 在mysql目录下新建 my.ini

    [mysqld]
    basedir=D:\Environment\mysql-5.7.19\
    datadir=D:\Environment\mysql-5.7.19\data\
    port=3306
    skip-grant-tables
    

    更多默认设置的写法

    [mysql]
    # 客户端默认字符集
    default-character-set=utf8
    [mysqld]
    # 服务端口为3306
    port=3306
    # 安装目录
    basedir=D:\Environment\mysql-5.7.19\
    # 数据库的数据存放目录
    datadir=D:\Environment\mysql-5.7.19\data\
    # 允许最大连接数
    max_connections=200
    # 服务端默认字符集
    character-set-server=utf8
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    
  5. 安装MySQL服务 初始化数据文件

    mysqld -install
    mysql --initialize-insecure --user=mysql
    
  6. 启动MySQL 进入MySQL管理界面

    net start mysql
    mysql -uroot -p
    
  7. 修改密码 刷新权限 退出MySQL

    UPDATE mysql.USER SET authentication_string = PASSWORD ( 'root' ) WHERE USER = 'root' AND HOST = 'localhost';
    FLUSH PRIVILEGES;
    EXIT;
    
  8. 删除或注释 my.ini 最后一行

    # skip-grant-tables
    
  9. 重启MySQL即可使用

    net stop mysql
    net start mysql
    

MySQL语句

SQL语句类型

  • DDL 数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
  • DML 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE\
  • DQL 数据库查询语言: 查询数据SELECT
  • DCL 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE

2、操作数据库

2.1、操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] `数据库名`;

2、删除数据库

DROP DATABASE [IF EXISTS] `数据库名`;

3、修改数据库

ALTER DATABASE `数据库名` 数据库属性;

4、使用数据库

USE `db1`;

5、查看数据库

SHOW DATABASES;

2.2、数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准的整数 4个字节
  • bigint 较大的数据 8个字节
  • float 单精度浮点数 4个字节
  • double 双精度浮点数 8个字节
  • decimal 字符串形式的浮点数 金融计算的时候 一般使用decimal

字符串

  • char 固定大小字符串 0~255
  • varchar 可变字符串 0~65535
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1

时间日期

  • date YYYY-MM-DD 日期
  • time HH:mm:ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳 1970.01.01到现在的毫秒数
  • year 年份

null

  • 没有值,未知
  • 注意,不要使用NULL进行运算

2.3、数据库的字段属性

UNSIGNED : 

  • 无符号的整数
  • 声明了该列不能为负数

ZEROFILL :

  • 0填充的
  • 不足的位数, 用0来填充

AUTO_INCREMENT :

  • 自增, 自动在上一条记录的基础上+1
  • 通常用来设置唯一的主键
  • 可以自定义设置主键自增的起始值和步长

NULL :

  • 如果设置NULL 不填写值 默认就是NULL

NOT NULL :

  • 非空
  • 如果设置NOT NULL 却不给其赋值 就会报错

DEFAULT :

  • 默认

2.4、创建数据库表

语法

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释],
    [设置主键]
) [表类型] [字符集] [注释];

官方文档

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [VISIBLE | INVISIBLE]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [ENGINE_ATTRIBUTE [=] 'string']
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
      [STORAGE {DISK | MEMORY}]
      [reference_definition]
      [check_constraint_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [VISIBLE | INVISIBLE]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
      [check_constraint_definition]
}

data_type:
    (see Chapter 11, Data Types)

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] 'string'
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTOEXTEND_SIZE [=] value
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)
}

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

query_expression:
    SELECT ...   (Some valid select or union statement)

常用命令

1、查看创建数据库的SQL语句

SHOW CREATE DATABASE `school`;

2、查看创建数据表的SQL语句

SHOW CREATE TABLE `student`;

3、显示表的结构

DESC `student`;

2.5、数据表的类型

数据引擎

MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大, 约为2倍

常规使用场景:

  • MYISAM 节约空间, 速度较快
  • INNODB 安全性高, 事务处理, 多表多用户操作

物理空间存在的位置

所有的数据库文件都存在 mysql/data/ 目录下

MySQL 引擎在物理文件上的区别

  • INNODB 在数据库中只有一个 *.frm 文件, 以及上级目录下的 ibdata1 文件
  • MYISAM 对应文件
    • *.frm - 表结构的定义文件
    • *.MYD - 数据文件(data)
    • *.MYI - 索引(index)

字符集编码

charset=utf8

不设置的话, 会默认为mysql的默认字符集编码 - Latin1 (不支持中文)

mysql/my.ini 中配置

[mysqld]
character-set-server=utf8

2.6、操作数据表

1、创建数据表 - 如上

2、删除数据表

DROP TABLE [IF EXISTS] `表名`;

3、修改数据表

  1. 修改表名

    ALTER TABLE `表名` RENAME AS `新表名`;
    
  2. 增加表的字段

    ALTER TABLE `表名` ADD `字段名` 列类型 [属性] [索引] [注释];
    
  3. 删除表的字段

    ALTER TABLE `表名` DROP `字段名`;
    
  4. 修改表的字段

    • MODIFY 修改表的字段类型和约束, 但不能重命名字段

      ALTER TABLE `表名` MODIFY `字段名` 列属性[];
      
    • CHANGE 修改表的字段名, 但不能修改表的字段类型和约束

      ALTER TABLE `表名` CHANGE `旧字段名` `新字段名` 列属性[];
      

4、删除数据表

DROP TABLE `表名`

所有创建和删除语句尽量加上判断, 以免报错

3、MySQL数据管理

3.1、外键

语法

在创建时添加
CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释],
    [设置主键]
    KEY `约束名` (`字段名`),
    CONSTRAINT `约束名` FOREIGN KEY (`字段名`) REFERENCES `从表` (`关联字段名`)
) [表类型] [字符集] [注释];
在创建后添加
ALTER TABLE `表名`
ADD CONSTRAINT `约束名` FOREIGN KEY (`字段名`) REFERENCES `从表` (`关联字段名`);

注意

  • 删除有外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(主表)。
  • 一般不使用数据库级别的外键,在应用层实现。

3.2、DML语言

1、插入数据

语法
INSERT INTO `表名`[(`字段名`, `字段名`, ...)]
VALUES ('值'[, '值', ...])[, ('值'[, '值', ...]), ...];
注意

主键自增可以省略,但如果不写表的字段,数据就要和已有字段一一对应

2、修改数据

语法
UPDATE `表名`
SET `字段名`='值'[, `字段名`='值', ...]
WHERE [条件];
WHERE子句条件运算符
条件运算符 含义 实例 结果
= 等于 5=6 false
<> 或 != 不等于 5!=6 true
> 大于 5>1 true
< 小于 5<1 false
>= 大于等于 5>=5 true
<= 小于等于 5<=5 true
BETWEEN…AND… […, …] [2, 5] -
AND 5>1 AND 1>2 false
OR 5>1 AND 1>2 true
注意
  • 不指定条件的情况下会改变所有字段。

3、删除数据

DELETE
语法
DELETE FROM `表名`
WHERE [条件];
注意
  • 不指定条件的情况下会删除所有字段。
TRUNCATE
作用

完全清空一个数据库表,表的结构和索引不会变。

语法
TRUNCATE TABLE `表名`;
DELETE和TRUNCATE的区别
  • 相同:都能删除数据,都不会删除表结构
  • 不同:
    • TRUNCATE 重新设置自增列 计数器会归零
    • TRUNCATE 不会影响事务

4、DQL查询数据

4.1、语法

SELECT ① FROM `表名` ②;

官方文档

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

4.2、①中常用

DISTINCT - 避免重复

±*/ - 四则运算

CONCAT - 用于连接字符串

CONCAT(字符串, 字符串[, 字符串, ...])

CONCAT_WS - 用分隔符连接字符串

CONCAT_WS(分隔符, 字符串, 字符串[, 字符串, ...])

聚合函数

  • COUNT - 求平均
  • MAX - 求最大值
  • MIN - 求最小值
  • SUM - 求和
  • AVG - 求平均值

4.3、②中常用

WHERE - 条件约束

WHERE子句中可使用:
  1. 比较运算符:> < >= <= <> !=
  2. 范围:BETWEEN … AND …
  3. 在…之中:IN (…, …, …)
  4. 模糊匹配:LIKE ‘’
    • % 表示任意多字符
    • _ 表示一个字符
  5. 逻辑运算符:AND或&&, OR或||, NOT或!
  6. 是否为空:IS NULL, IS NOT NULL
  7. 正则表达式:REGEXP

GROUP BY - 分组

经常和聚合函数一起用

HAVING - 过滤

在 HAVING 条件中可以使用聚合函数,在 WHERE 中不行

ORDER BY - 排序

  • ASC 升序 默认

  • DESC 降序

以","分割可以按多个字段排序

LIMIT - 限制

LIMIT m, n / LIMIT m offset n:从m+1开始取n项,不写m时默认为0

4.4、多表查询

交叉连接

不适用任何匹配条件,生成笛卡尔积。

SELECT 字段列表 FROM 表1, 表2 WHERE 条件;

内连接

只连接匹配的行

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件;

左连接

优先显示左表全部记录

SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件;

右连接

优先显示右表全部记录

SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 条件;

全外连接

显示左右两个表全部记录

SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件 UNION
SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 条件;

4.5、子查询

  1. 子查询是将一个查询语句嵌套在另一个查询语句中。
  2. 内层查询语句的查询结果,可以为外层查询语句提供查询条件。
  3. 子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字,还可以包含比较运算符:= 、 !=、> 、<等。

4.6、MySQL函数

  • MD5() - MD5加密

5、事务

5.1、事务原则

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

实例

针对同一个事务

image-20220204024627127

这个过程包含两个步骤

A:800 - 200 = 600
B:200 + 200 = 400

原子性表示,这两个步骤一起成功,或者一起失败,不能只发生其中一个动作

一致性(Consistency)

事务前后数据的完整性必须保持一致。

实例

image-20220204024627127

操作前A:800,B:200
操作后A:600,B:400

一致性表示事务完成后,符合逻辑运算

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

实例

表示事务结束后的数据不随着外界原因导致数据丢失

操作前A:800,B:200
操作后A:600,B:400
如果在操作前(事务还没有提交)服务器宕机或者断电,那么重启数据库以后,数据状态应该为
A:800,B:200
如果在操作后(事务已经提交)服务器宕机或者断电,那么重启数据库以后,数据状态应该为
A:600,B:400

持久性(Durability)

持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。

实例

image-20220204024650022

事务一:A向B转账200
事务二:C向B转账100

两个事务同时进行,其中一个事务读取到另外一个事务还没有提交的数据,执行步骤如图所示,按照数字顺序执行

image-20220204024654793

隔离性用于解决以上问题

关于隔离的一些问题
  • 脏读:指一个事务读取了另外一个事务未提交的数据
  • 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
  • 虚读(幻读):是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。

5.2、语法

手动处理事务

SET autocommit=0 -- 关闭事务自动提交
-- 事务开启
START TRANSACTION; -- 标记一个事务的开始,这句之后的sql都在同一个事务内
-- SQL语句
-- ...

-- 保存点
SAVEPOINT 保存点名; -- 设置事务保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 清除事务保存点

COMMIT; -- 提交:持有化(失败)
ROLLBACK; -- 回滚:回到事务执行前的样子(失败)
-- 事务结束
SET autocommit=1; -- 开启事务自动提交(默认)

6、索引

定义

索引(Index)是帮助MySQL高效获取数据的数据结构

6.1、分类

主键索引(PRIMARY KEY)

唯一的标识,主键不可重复,只能有一个列作为主键

唯一索引(UNIQUE KEY)

避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引。

常规索引(KEY/INDEX)

全文索引(FULLTEXT)

6.2、语法

创建表时增加索引

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释],
    PRIMARY KEY (`字段名`),
    UNIQUE `索引名` (`字段名`),
    INDEX [索引名] (`字段名`),
    FULLTEXT `索引名` (`字段名`)
) [表类型] [字符集] [注释];

创建表完成后增加索引

ALTER TABLE `表名` ADD PRIMARY KEY (`字段名`);
-- 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
CREATE UNIQUE INDEX `索引名` ON `表名` (`字段名`);
ALTER TABLE `表名` ADD UNIQUE `索引名` (`字段名`);
-- 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
CREATE INDEX `索引名` ON `表名` (`字段名`);
ALTER TABLE `表名` ADD INDEX `索引名` (`字段名`);
-- 添加普通索引,索引值可出现多次。
CREATE FULLTEXT INDEX `索引名` ON `表名` (`字段名`);
ALTER TABLE `表名` ADD FULLTEXT `索引名` (`字段名`);
-- 该语句指定了索引为 FULLTEXT ,用于全文索引。

删除索引

DROP [UNIQUE |FULLTEXT ]INDEX `索引名` ON `表名`;

显示所有索引信息

SHOW INDEX FROM `表名`;

分析SQL执行状况

EXPLAIN SQL语句;

全文索引

SELECT * FROM `表名` WHERE MATCH(`字段名`) AGAINST('字符串');

6.3、实例

测试索引

-- 创建用户表
CREATE TABLE `app_user` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
	`phone_number` VARCHAR(20) DEFAULT '' COMMENT '手机号码',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '姓名(0:男;1:女)',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

-- 插入100万条数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i < num DO
		SET i = i + 1;
		INSERT INTO `app_user`(`name`, `email`, `phone_number`, `gender`, `password`, `age`) VALUES (
			CONCAT('用户', i),
			CONCAT('39', FLOOR(RAND() * ((99999999 - 10000000) + 10000000)), '@qq.com'),
			CONCAT('15', FLOOR(RAND() * ((999999999 - 100000000) + 100000000))),
			FLOOR(RAND() * 2),
			MD5(RAND()),
			FLOOR(RAND() * 50)
		);
	END WHILE;
	RETURN i;
END $$
SELECT mock_data();

SELECT * FROM `app_user` WHERE `name`='用户19999'; -- 创建索引之前: 0.512s
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户19999';

CREATE INDEX `id_app_user_name` ON `app_user`(`name`);
SELECT * FROM `app_user` WHERE `name`='用户19999'; -- 创建索引之后: 0s

6.4、索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

索引的数据结构 - CodingLabs - MySQL索引背后的数据结构及算法原理

Hash 类型的索引

Btree:INNODB 的默认数据结构

7、权限管理

7.1、SQL命令操作

用户表:mysql.user

创建用户

CREATE USER `用户名` IDENTIFIED BY '密码';

修改密码

-- 修改当前用户密码
SET PASSWORD = PASSWORD('新密码');
-- 修改指定用户密码
SET PASSWORD FOR '用户名' = PASSWORD('新密码');

重命名用户

RENAME USER `原用户名` TO '新用户名';

用户授权

-- 授予所有权限
-- ALL PRIVILEGES 除了GRANT权限的所有权限
GRANT ALL PRIVILEGES ON *.* TO '用户名';
GRANT 权限 ON 数据库.表 TO '用户名'[@'主机名'] [WITH GRANT OPTION];

查询权限

SHOW GRANTS FOR '用户名'[@'主机名'];

撤销权限

REVOKE 权限 ON 数据库.表 FROM '用户名'[@'主机名'];

删除用户

DROP USER '用户名';
0

评论区