MySQL基础乱序版总结+知识补充

参考：www.bilibili.com/video/BV1NJ…
内容不全，只是大致的总结和一些基础没有讲到的知识，详细内容可以参考本专栏里的文章

1、初识数据库

1.1、什么是数据库

数据库（DB， DateBase）
概念：数据仓库，软件，安装在操作系统上（Windows，linux，mac…）之上，可以存储大量数据。500万左右！
作用：存储数据，管理数据

1.2、数据库分类

关系型数据库（SQL）：Mysql, Oracle, SQl server, DB2, SQLLlite，通过表和表之间，行和列之间的关系进行数据的存储
非关系型数据库（NoSQL） Not Only SQl，Redis, MongDB，非关系型数据库，对象存储，通过对象自身的属性来决定

1.3、DBMS(数据库管理系统)

数据库的管理软件，管理、维护和获取数据，比如MySQl数据库管理系统

2、MySQL简介

2.1、Mysql特点

MySQl是一个关系型数据库管理系统

前世：瑞典Oracle旗下产品
今生：属于Oracle旗下产品
MySQl是最好的RDBMS（Relational Database Management System，关系数据型数据库）之一。
开源的数据库软件
体积小、速度快
中小型网站、或者大型网站，集群！

2.2、Mysql安装

略，参考前面文章

2.3、基本命令行操作

命令行连接

#常用命令

mysql -uroot -p123456 --连接数据库
flush privileges -- 刷新权限
-----------------------------------
--所有的语句都使用；结尾
show databases; --查看所有的数据库
mysql> ues school; --切换数据库 ues 数据库名
Database changed
-----------------------------------
show tables; --查看数据库中所有的表
describe student; --显示数据库中的指定表的信息
------------------------------------
create database westos; --创建一个数据库
exit; --退出连接
------------------------------------
--单行注释（SQL本来的注释）
/* 多行注释
dasj
*/

2.4、数据库xxx语言 CRUD增删改查（业务程序猿）

DDl 定义
DMl 操作
DQL 查询
DCL 控制

3、操作数据库

操作数据库->操作数据库中的表->操作数据库中的数据

mysql关键字不区分大小写

1.创建数据库
CREATE DATABASES [ IF NOT EXISTS] 数据库名

2.删除数据库
DROP DATABASES IF EXISTS 数据库名

3.使用数据库
如果你的表名或者字段名是一个特殊字符，就需要带 ``
USE school
SELECT user FROM school

4.查看数据库
SHOW DATABASES --查看所有的数据库

5.对比，sqlyog/Navicat的可视化操作
	对照可视化历史记录查看sql
	固定的语法或关键字必选要强行记住

3.1、可视化工具使用

可以帮我们代替命令和sql语言的方式完成一些工作

可以在历史记录看到以前使用的命令

4、数据库的字段类型

4.1、字段类型

1、数值

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中度大小的数据 3个字节
int 标准的数据 4个字节 INT(M) ，M表示最大显示宽度，与存储大小或类型包含的值的范围无关
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数（金融计算的时候，一般使用decimal）

2、字符串

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

3、时间日期

Java：java.unti.Data

data YYYY-MM-DD ，日期格式
time HH：mm ：ss 时间格式
datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
timestamp 时间戳， 1970.1.1到现在的毫秒数！
year 年份表示

4、null

没有值，未知
注意，不要使用NULL进行数值运算，结果为NULL

4.2、数据库的字段属性（重点）

Unsigned:

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

zerofill：

0填充
不足的位数，用0填充
用于数值性，比如int(7)
数值类型声明的长度和数据类型范围没啥关系，具体情况参考前面文章，已经很详细了

自增

通常理解为自增，自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键~index，必须是整数类型
可以自定义设计主键自增的起始值和步长

非空 NUT NULL

假设设置为not null ，如果不给他赋值，就会报错！
NULL，如果不填写值，默认就是NULL

默认

设置默认的值
sex，默认值为男，如果不指定该列的值，就会有默认的值

阿里巴巴规范

/* 每一个表，都必须存在以下五个字段!未来做项目用的，表示一个记录存在的意义
id 主键
`version`  乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
*/

5、创建数据库和表及可视化查看

1、建议MySQL 建表时给表和字段加上注释

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL创建
-- 学号int 登录密码varchar（20） 姓名，性别varchar（2），出生日期（datatime），家庭住址，email

-- 注意点，使用英文（）， 表的名称 和 字段尽量使用 `` 括起来
-- 字符串使用 单引号括起来
-- AUTO_INCREMENT 自增
-- 所有的语句后面加， （英文的），最后一个不用加
-- DELETE 默认值 COMMENT 注释
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
--引擎使用INNODB。默认编码utf8
CREATE TABLE if not EXISTS `student`(
	`id` INT(4) NOT NUll AUto_increment COMMENT '学号',
	`name` VARCHAR(30) NOT NUll DEFAULT '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULl COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

desc student查看表结构

SHOW FULL COLUMNS FROM student查看更加详细的结构，可以看见注释

格式

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

常用命令

SHOW CREATE DATABASE school -- 查看创建数据库的语句 
SHOW CREATE TABLE student -- 查看数据库表的定义语句
DESC student -- 显示表的结构

6、INNODB和MYISAM

INNODB：默认使用

MYISAM：早些年使用的

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

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下
本质还是文件的存储！

MySQl引擎在物理磁盘文件上的区别【去安装目录查看】

InnoDB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
MYISAM 对应文件 *.frm -表结构的定义文件 *.MYD 数据文件（data） *.MYI 索引文件

7、设置数据库表的字符集编码

CHARSET=utf8 不设置的话，会是MySQL默认的字符集编码~（不支持中文）

mysql的默认编码是Latin1，不支持中文，在my.ini中配置默认的编码使得永久生效

character-set-server=utf8

8、删除和修改表

修改表

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE sys_student RENAME As sys_student_info
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE sys_student_info ADD age INT(11)

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[] modify
ALTER TABLE sys_student_info MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE sys_student_info CHANGE age age1 INT(1) -- 修改字段名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名 
ALTER TABLE sys_student_info DROP age1

删除表

-- 删除表（如果存在）
-- DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS sys_student_info

所有的创建和删除操作尽量加上判断，以免报错

注意点：

字段名，使用着重号这个包裹！防止得到关键字
注释 – /**/
sql关键字大小写不敏感
所有的符号用英语

9、MySQL数据管理

9.1、多表-外键（不推荐）

方式一：在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradeName` VARCHAR(10) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 学生表的gradeid 字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 应用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN key (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）（即先删除school表，再删除grade表）

方式二：创建表成功后，添加外键约束

CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradeName` VARCHAR(10) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

SHOW CREATE TABLE student

-- 学生表的gradeid 字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 应用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL COMMENT '年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `Fk_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)

以上操作都是物理外键，数据库级别的外键，不建议使用！（避免数据库最多造成困扰）【阿里巴巴强制】

最佳实践

数据库就是单纯的表，只用来存数据，只有行（数据）列（字段）
我们想使用多张表的数据，想使用外键（程序-应用层去实现）

9.2、DML语言（全部记住）

数据库意义： 数据存储，数据管理

DML语言：数据操作语言

insert
update
delete

9.2.1、添加

-- 插入语句（添加）
-- INSERT INTO 表名（{字端名1，字段2，字段3}） VALUES（'值1','值2','值3',...)
INSERT INTO `grade`(`gradeName`) VALUES('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade` VALUES ('大三') -- 报错，字段不匹配,相当于下面
INSERT INTO `grade`(`gradeid`,`gradeName`)  VALUES ('大三',null)

-- 一般写插入语句,数据一定和字段一一对应

-- 插入多个字段,值值用()隔开
INSERT INTO `grade`(`gradeName`) VALUES('大二'),('大一')


INSERT INTO `student`(`name`) VALUES ('张三')

INSERT INTO `student`(`name`, `password`) VALUES ('张三', '2434')

INSERT INTO `student`(`name`, `password`, `sex`) VALUES ('张三', '2434','女')

语法：INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

注意事项：

字段和字段之间使用英文逗号隔开
字段是可以省略，但是后面的值必须要一一对应，不能少
可以同时插入多条语句，VALUES后面的值，需要用**，**隔开即可 valuse(),()

9.2.2、修改

-- 修改学员名字
UPDATE `student` SET `name` = '漠殇' WHERE id = 1;

-- 不指定条件的情况下，会改动所有表！
UPDATE `student` SET `name` = '1001001'

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name` = '漠殇', email = '1243242@qq.com' WHERE id = 1

-- 语法：
-- UPDATE 表名 SET colnum_name = value,[colnum_name = value, ....] where[条件]

9.2.3、where条件和操作符

条件：where子句运算符 id等于某个值，大于某个值，在某个区间内修改…

操作符	含义
=	等于	5=6	false
<>或！=	不等于	5<>6	true

<
<=
>=
BETWEEN…AND…	闭合区间内	[2,5]	[2,5]
AND	&&	5>1 and 1>2	false
OR	\|\|	5>1 or 1>2	true

注意：

colnum_name 是数据库的列，尽量带上``
条件：筛选的条件，如果没有指定，则会修改所有的列
value，是一个具体的值，也可以是一个变量
多个设置的属性之间，使用英文逗号隔开

UPDATE student SET birthday = CURRENT_TIME WHERE name = '1001001' AND sex = '女'

9.2.4、删除

delete命令

语法 delete from 表名 [where 条件]

-- 删除数据 （避免这样写）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE `id` = 1

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空表 
TRUNCATE `grade`

DELETE 和 TRUNCATE 区别--对自增的影响

相同点：都能删除数据，都不会删除表结构
不同：
- TRUNCATE重新设置自增列计数器会归零
- TRUNCATE 不会影响事务

-- 测试delete 和TRUNCATE区别
CREATE TABLE `test` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(10) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO `test`(`coll`) VALUES ('1'),('2'),('3')

DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零

了解即可：DELETE删除的问题，重启数据库，现象

innoDB 自增列会重1开始（存在内存当中，不会丢失）
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

9.3、DQL查询数据（最重点）

记住查询所用的关键词的顺序：

顺序很重要:
select 去重要查询的字段 from 表(注意: 表和字段可以取别名)
XXX join 要连接的表 on 等值判断
where (具体的值，子查询语句)
Group By (通过哪个字段来分组)
Having (过滤分组后的信息， 条件和where是一样的，位置不同)
Order By.. (通过哪个字段排序) [升序/降序]
Limit startIndex, pagesize
业务层面:
查询:跨表，跨数据....

9.4、DDL

10、函数-数据库级别的MD5加密（扩展）

什么是MD5？

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的[密码散列函数]，可以产生出一个128位（16[字节]的散列值（hash value）

主要增强算法复杂度和不可逆性

MD5不可逆，具体的值的MD5是一样的

MD5破解网站的原理：背后有一个字典，MD5加密后的值，加密的前值

-- =============== 测试MD5 加密=================
CREATE TABLE `testmd5` (
	`id` INT(3) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUES(1, '张三', '123456'),(2, '李四', '123456'),(3, '王五 ', '123456')

-- 加密
UPDATE `testmd5` SET pwd = MD5(`pwd`) -- 加密所有密码

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4, '小明', MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT *FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')

11、事务

要么都成功commit，要么都失败回滚

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

隔离所导致的一些问题

脏读：指一个事务读取了另外一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

使用对应的隔离级别来解决了这些问题

执行事务

-- mysql是默认开启事务自动提交的 
SET autocommit = 0 -- 关闭

-- 手动处理事务
SET autocommit = 0 -- 关闭自动条件

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

-- 提交：持久化（成功）
COMMIT 
-- 回滚：回到原来的样子（失败）
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

SAVEPOINT 保存点名  --  设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE  SAVEPOINT 保存点		-- 撤销保存点

在Java中如何实现

转账例子测试事务

-- 转账

CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8 */

CREATE TABLE `account` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `money` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

-- 模拟转账 ： 事务
SET autocommit = 0; -- 关闭自动事务，不会自动提交，这样两个sql语句就可以一起执行了
START TRANSACTION -- 开启一个事务

UPDATE account SET money = money - 500 WHERE `name` = 'A'
UPDATE account SET money = money + 500 WHERE `name` = 'B' 

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

SET autocommit = 1;
SHOW CREATE DATABASE shop
SHOW CREATE TABLE account

12、索引

详细内容参考mysql高级部分内容

12.1、索引的概念

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

参考博客：blog.codinglabs.org/articles/th…

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。[mysql底层基于主键索引创建B+树的结构]

12.2、索引的分类

主键索引（PRIMARY KEY）：唯一的标识，主键不可重复，只能有一个列作为主键(创建主键默认生成)

唯一索引（UNIQUE KEY）：避免重复的列出现，唯一索引可以重复，多个列都可以标识位唯一索引

常规索引（KEY / INDEX)：默认的，index，key 关键词来设置

全文索引（FullText)：在特定的数据库引擎下才有MyISAM，快速定位数据

12.3、基础语法

参考高级部分

-- 索引的使用
-- 1.在创建表的时候给字段添加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 添加一个全文索引 （索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 常规索引， 

--非全文索引
SELECT * FROM student WHERE MATCH(studentname) AGAINST('张')

创建索引后查询数据消耗时间显著降低，索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显

12.4、索引原则

索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加索引
索引一般加在常用查询的字段上
索引的数据结构
- Hash类型的索引
- Btree：InnoDB的默认数据结构

13、权限管理和备份、恢复

MySQL 是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定不同权限。在前面的章节中我们使用的是 root 用户，该用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户密码等管理权限。为了实际项目的需要，可以创建拥有不同权限的普通用户。

13.1、MySQL user权限表

参考：c.biancheng.net/view/7249.h…

MySQL 在安装时会自动创建一个名为 mysql 的数据库，mysql 数据库中存储的都是用户权限表。用户登录以后，MySQL 会根据这些权限表的内容为每个用户赋予相应的权限。

user 表是 MySQL 中最重要的一个权限表，用来记录允许连接到服务器的用户账号信息。需要注意的是，在 user 表里启用的所有权限都是全局级的，适用于所有数据库。

13.2、MySQL创建用户（3种方式）

MySQL 在安装时，会默认创建一个名为 root 的用户，该用户拥有超级权限，可以控制整个 MySQL 服务器。在对 MySQL 的日常管理和操作中，为了避免有人恶意使用 root 用户控制数据库，我们通常创建一些具有适当权限的用户，尽可能地不用或少用 root 用户登录系统，以此来确保数据的安全访问。

MySQL 提供了以下 3 种方法创建用户：

使用 CREATE USER 语句创建用户
在 mysql.user 表中添加用户
使用 GRANT 语句创建用户
创建以后就可以在user表里面看见我们创建的用户

13.3、用户管理

sqlyog/navicat 可视化管理

我们可以通过图形化界面的方式来添加或删除新用户，添加用户名和密码并赋予相应的权限，然后使用该创建的用户登录，比如sqlyog，就可以在他的权限范围内执行相应的操作。

SQL命令操作【用在Linux下】

前面提到可以在mysql.user 表中添加用户，因此我们增加或删除用户的本质就是对mysql.user这张表进行增删改查，查看这张表，可以看到有如下的一些默认用户和其权限，这些权限就相当于上面图中的可以选择的权限，还可以发现与其他系统用户不同的是，root用户的权限全是yes，说明root用户的权限最大。

可以发现：用户管理的命令可以分为：

创建用户
授权
设置密码
删除用户

--创建用户CREATE USER用户名IDENTIFIED BY '密码'，可以在user表中发现该用户
CREATE USER kuangshen IDENTIFIED BY ' 123456'
--修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
--修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD('123456')
--重命名RENAME USER 原来名字TO新的名字
RENAME USER kuangshen TO kuangshen2

--用户授权：ALL PRIVILEGES 全部的权限(和root权限一样)，库.表
-- ALL PRIVILEGES ：除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

--查询权限
SHOW GRANTS FOR kuangshen2 --查看指定用户的权限
SHOW GRANTS FOR root@localhost --查看root用户的权限

--ROOT用户权限: GRANT ALL PRIVILEGES ON *.* TO 'root'@ 'localhost' WITH GRANT OPTION

--撒销权限REVOKE 哪些权限，在哪个库撤销，给谁撒销
REVOKE ALL PRIVILEGES ON *. * FROM kuangshen2

--删除用户
DROP USER kuangshen

创建以后，就可以使用设置的密码和用户去登录sqlyog进行操作了

13.2、MySql备份

为什么要备份：

保证重要的数据不丢失
数据转移

MySQl数据库备份的方式

直接拷贝物理磁盘上的文件
在可视化软件直接导出【数据库和表都可以导出，使用的时候直接将导出文件拖进sqlyog就行】

使用命令行导出 mysqldump 命令行使用

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名1 表名2 表名3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# 导入
# 登录的情况下，切换到指定数据库
# source 备份文件
source d:/a.sql
或
mysql -u用户名 -p密码 库名 < 备份文件

14、规范数据库设计

14.1、为什么需要设计

当数据比较大的时候，就需要设计

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
程序的性能差

良好的数据库设计

节省内存空间
保证数据库的完整型
方便我们开发系统

14.2、E-R图

软件开发中，关于数据库的设计

分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系E—R图（建议使用visio绘制）

14.3、设计数据库的步骤：（个人博客为例）

收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友情链接信息）
- 评论表
- 自定义表（系统消息，某个关键的子，或者一些主子段） key ：value
标示实体集（把需求落地到某个字段）【见第一章ER模型】

用户表

文章表

博客表

评论表

友链表

关注表

标识实体集之间的关系(关联-关系)

写博客：user–> blog
创建分类：user–>category

15、三大范式

为什么需要数据规范化

信息重复
更新异常
插入异常
- 无法正常显示信息
删除异常
- 丢失有效的信息

15.1、关系型数据库设计：三大范式的通俗理解

参考：www.cnblogs.com/wsg25/p/961…

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是本文要讲的“三大范式”。

15.2、第一范式（1NF）

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

原子性：保证每一列不可再分

可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

15.3、第二范式（2NF）

前提：在满足1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

15.3、第三范式（3NF）

前提：满足第一范式和第二范式，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

这样以来，就满足了第三范式的要求。

ps：如果把上表中的班主任姓名改成班主任教工号可能更确切，更符合实际情况，不过只要能理解就行。

16、规范性和性能的问题

关联查询的表不得超过三张表

考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
在规范性能的问题的时候，需要适当的考虑一下规范性
故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
故意增加一些计算列（从大数据量降为小数据量的查询：索引）

17、回表

参考：blog.csdn.net/weixin_4620…

参见高级部分

Mysql查询过程：

在InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

当mysql查询时，使用非聚簇索引（也叫二级索引，辅助索引）检索其B+树查到相应的叶子节点获取主键值，然后通过主键索引（聚簇索引） 检索它对应的B+树再查到相应的数据行信息，找到主键后通过聚簇索引找到相应数据行的过程叫做回表。

如上图表A，主键是id，辅助索引是serial_no

查询：select * from A where id=7，这里直接通过聚簇索引查询，可以直接获取到id为的数据行.
查询 select * from A where serial_no=000124 ，即使用辅助索引进行查询，则会先检索辅助索引中的 B+ 树的 serial_no，找到对应的叶子节点，获取主键值5，然后再通过聚簇索引中的 B+ 树检索到对应的叶子节点，然后获取整行数据。（回表）

如何避免回表--优化方法

优化方法1：覆盖索引

如果只是查询商品名称，价格，我们可以建立一个组合索引，即serial_no,name,unit_price 组合索引，这样在查询时第一次就可以获取这三个信息，而不用回表，可以减少大量IO 操作

通常在 InnoDB 中，除了查询部分字段可以使用覆盖索引来优化查询性能之外，统计数量也会用到。例如，SELECT COUNT(*) 时，如果不存在辅助索引，此时会通过查询聚簇索引来统计行数，如果此时正好存在一个辅助索引，则会通过查询辅助索引来统计行数，减少 I/O 操作。

优化方法2：自增字段作主键优化查询

上面我们讲了 InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+ 树的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为不需要重新移动数据，因此这种插入数据的方法效率非常高。如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。

优化方法3：最佳左缀原则

优化方法4. 避免索引失效

18、JDBC（重点）

参考本专栏：JDBC

MySQL基础乱序版总结+知识补充

MySQL基础乱序版总结+知识补充

1、初识数据库

1.1、什么是数据库

1.2、数据库分类

1.3、DBMS(数据库管理系统)

2、MySQL简介

2.1、Mysql特点

2.2、Mysql安装

2.3、基本命令行操作

2.4、数据库xxx语言 CRUD增删改查 （业务程序猿）

3、操作数据库

3.1、可视化工具使用

4、数据库的字段类型

4.1、字段类型

4.2、数据库的字段属性（重点）

5、创建数据库和表及可视化查看

6、INNODB和MYISAM

7、设置数据库表的字符集编码

8、删除和修改表

9、MySQL数据管理

9.1、多表-外键（不推荐）

9.2、DML语言（全部记住）

9.2.1、添加

9.2.2、修改

9.2.3、where条件和操作符

9.2.4、删除

9.3、DQL查询数据（最重点）

9.4、DDL

10、函数-数据库级别的MD5加密（扩展）

11、事务

12、索引

12.1、索引的概念

12.2、索引的分类

12.3、基础语法

12.4、索引原则

13、权限管理和备份、恢复

13.1、MySQL user权限表

13.2、MySQL创建用户（3种方式）

13.3、用户管理

13.2、MySql备份

14、规范数据库设计

14.1、为什么需要设计

14.2、E-R图

14.3、设计数据库的步骤：（个人博客为例）

15、三大范式

15.1、关系型数据库设计：三大范式的通俗理解

15.2、第一范式（1NF）

15.3、第二范式（2NF）

15.3、第三范式（3NF）

16、规范性和性能的问题

17、回表

18、JDBC（重点）

2.4、数据库xxx语言 CRUD增删改查（业务程序猿）