MySQL基础(初学者必看，强烈推荐！！！)

最新推荐文章于 2025-08-20 20:10:02 发布

原创最新推荐文章于 2025-08-20 20:10:02 发布 · 1.4k 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

数据库专栏收录该内容

1 篇文章

订阅专栏

本文介绍了MySQL数据库的基本操作，包括创建、删除和修改表，使用DDL、DML语句，以及外键的概念。此外，还详细讲解了DQL查询语言，如SELECT语句的使用，涉及子查询、联表查询、分组和过滤等。同时，文章提到了事务处理的重要性，解释了ACID原则，并给出转账的事务管理实例。最后，讨论了索引的创建、类型和对查询性能的影响，以及数据库用户管理和备份。

1、初识MySQL

什么是MySQL?

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

2、语句基本使用方法

2.1、DDL

DDL(Data Definition Language):数据库模式定义语言，是用于描述数据库中要存储的现实世界实体的语言。

关键字：CREATE，DROP，ALTER。

①操作数据库：

SHOW DATABASES;-- 查看所有数据库
CREATE DATABASE IF NOT EXISTS 数据库名;-- 创建数据库
 DROP DATABASE IF EXISTS 数据库名;  -- 删除数据库
 USE `school` -- 使用数据库
 SHOW DATABASES;-- 查看所有数据库
 -- if not exists 用来判断创建的数据库是否存在,如果不存在则创建数据库.存在则warning
 -- if exists	 用来判断删除的数据库是否存在,如果存在则删除数据库.不存在则warning

②创建表：

-- 例如创建一个student表
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(50) NOT NULL DEFAULT '张三' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

Tips:

创建一张表的格式：

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

列类型：①数值②字符串③时间日期④null

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

②字符串
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	2021	年份数

④null
没有值未知	使用null进行运算结果还是null

列属性：

Unsigned
- 无符号的整数，一旦声明不能为负数
zerofill
- 0填充的，不足的位数用0来填充
AUTO_INCREMENT
- 必须是整数类型
- 通常理解为自增，自动在上一条记录的基础上加一
- 一般用来设计唯一的主键
NOT NULL
- 假设设置为NOT NULL ，如果不给它赋值，就会报错
- NULL，如果不填写值，默认就是NULL，不会报错
- 一般将住建设置为not null
DEFAULT
- 设置默认的值

列索引：
详情请看本文章的第四部分

列注释：

COMMENT
- 注解，字段说明

数据表类型（数据库引擎）：①INNODB(默认使用)②MYISAM(早些年使用)

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

拓展知识：

在这里插入图片描述

字符集设置：CHARSET=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文），MySQL的默认编码是Latin1，不支持中文
可以在my.ini中配置默认的编码 “character-set-server=utf8”，也可以在创建数据库表的时候设置字符集编码格式，尽量在创建数据库表的时候设置编码，保证SQL语句的可移植性

③偷懒创建数据库和表：


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

④修改、删除和增加表的字段：

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student2
-- 删除表  DROP TABLE IF EXISTS 表名	
DROP TABLE IF EXISTS student
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student2 ADD age INT(10)
-- 删除表的字段	ALTER TABLE 表名 DROP 字段名
ALTER TABLE student2 DROP age
-- 修改表的字段 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性	
			 ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE student2 CHANGE age2 age INT(8)
ALTER TABLE student2 MODIFY age INT(10)
注意：所有的创建和删除操作尽量加上判断，以免报错
	 change用来字段重命名，不能修改字段类型和约束
     modify不用来字段重命名，只能修改字段类型和约束

2.2、外键

①创建表的时候没有外键关系

CREATE TABLE IF NOT EXISTS `course`(
	`courseid` INT(4) NOT NULL COMMENT '课程id',
	`coursename` VARCHAR(40) NOT NULL COMMENT '课程名字',
	PRIMARY KEY (`courseid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(50) NOT NULL DEFAULT '张三' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`courseid` INT(4) NOT NULL COMMENT '课程id',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8	
ALTER TABLE student 
ADD CONSTRAINT `FK_courseid` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`); 
/*
	alter table 表名 add constraint 约束名 foreign key(作为外键的列) refrences 哪个表(哪个字段);
*/

②创建表的时候有外键关系

CREATE TABLE IF NOT EXISTS `course`(
	`courseid` INT(4) NOT NULL COMMENT '课程id',
	`coursename` VARCHAR(40) NOT NULL COMMENT '课程名字',
	PRIMARY KEY (`courseid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(50) NOT NULL DEFAULT '张三' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`courseid` INT(4) NOT NULL COMMENT '课程id',
	PRIMARY KEY (`id`),
	KEY `FK_courseid` (`courseid`),
	CONSTRAINT `FK_courseid` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`)  
)ENGINE=INNODB DEFAULT CHARSET=utf8	

/*
	①定义外键key			key `FK_courseid` (`courseid`)
	②给这个外键添加约束		constraint `FK_courseid` foreign key (`courseid`) references `course` (`courseid`)
*/

2.3、DML

DML(Data Manipulation Language)：数据操纵语言。

关键字：INSERT、UPDATE、DELETE。

2.3.1、增

-- 语法：insert INTO `表名` (`字段名1`,`字段名2`,`字段名3`) VALUES ('值1','值2','值3'),('值1','值2','值3')  数据和字段一一对应
INSERT INTO `course` (`coursename`) VALUES ('yu')
INSERT INTO `course` (`courseid`,`coursename`)
VALUES (2,'李四'),(3,'王五')

/*
	注意事项：
	1.字段和字段之间使用英文逗号隔开
	2.字段是可以省略的，但是后面的值必须一一对应，不能少
	3.可以插入多条数据，VALUES后面的值，需要使用，隔开即可 VALUES(),(),......
*/

2.3.2、删

/*
	deleta命令
	语法：DELETE FROM `表名` WHERE [条件]（注意：避免使用	DELETE FROM `表名`  这样会导致数据全部删除）
	作用：删除一个表的全部数据及指定数据
	truncate命令
	语法：TRUNCATE FROM `表名` 
	作用：只能完全清空一个数据库表,表的结构和索引约束不会变
*/
-- eg
DELETE FROM `course` WHERE courseid=1
TRUNCATE TABLE `course`
/*
	delete和truncate的区别
*/
-- eg
-- a.创建一个student表
CREATE TABLE `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT ,
	`name` VARCHAR(40) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- b.向student表中添加数据
INSERT INTO `student` (`name`) VALUE('张三'),('李四'),('王五')
-- c.两种方法清空student表
DELETE FROM `student`	--不会影响自增
TRUNCATE TABLE`student`	--自增清零
/*
	扩展：
	Delete 删除一个表所有数据,然后重启数据库,表的自增会有两种情况
	①如果是INNODB 自增会重0开始(存在内存当中，断电即失)
	②如果是MYISAM 继续从上一个自增量开始(存在文件当中，不会丢失)
*/

2.3.3、改

-- ①修改一个表的单个属性值 （注意：不指定条件的情况下，会改动所有的表）
UPDATE `course` SET `coursename`='数据结构' WHERE courseid=1
-- ②修改一个表的多个属性,逗号隔开。
UPDATE `course` SET `coursename`='大数据',`teacher`='关羽' WHERE courseid=2
/*
	语法：UPDATE `表名` SET `列名1`='value',`列名2`='value',[`列名3`='value'] WHERE [条件]
	注意事项：
	①条件如果没有指定，则会修改所有的列
	②value是一个具体的值，也可以是一个变量
	③多个属性之间，使用英文逗号隔开
	④表名列名用``包裹起来
*/

2.4、DQL

DQL(Data QueryLanguage): 数据查询语言

关键字：SELECT

标准语法：

SELECT [ ALL  |  DISTINCT ]
{  * |  table. *|  [ table.field1[as alias1][,table.field2[as alias2]][,...] ] }
FROM table_name [as table_alias]
	[left | right | inner join table_name2 ] --联合查询
	[WHERE...]--指定结果满足的条件
	[GROUP BY...]--指定满足哪几个字段来分组
	[HAVING]--过滤分组的记录必须满足的次要条件
	[ORDER BY...]--指定查询记录一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}]
	注意：[]代表可选 {}代表必选的

下面通过一个具体实例来简单了解下DQL

2.4.1、创建一个简单school数据库

CREATE DATABASE IF NOT EXISTS `school`
USE `school`
-- 学生表
CREATE TABLE IF NOT EXISTS `student`(
	`sno` BIGINT(11) NOT NULL DEFAULT '20183033501' COMMENT '学生学号',
	`sname` VARCHAR(50) COMMENT '学生姓名',
	`grade` VARCHAR(50) DEFAULT '2018' COMMENT '年级/届',
	PRIMARY KEY (`sno`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 教师表
CREATE TABLE IF NOT EXISTS `teacher`(
	`teacherID` INT(4) NOT NULL COMMENT '教师编号',
	`teacherName` VARCHAR(50)  COMMENT '教师姓名',
	`phone` BIGINT(11) COMMENT '教师电话',
	PRIMARY KEY (`teacherID`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8 
-- 课程表
CREATE TABLE IF NOT EXISTS `course` (
	`courseID` INT(4) NOT NULL COMMENT '课程ID',
	`courseName` VARCHAR(50) COMMENT '课程名称',
	`credit` INT(1) COMMENT '课程学分',
	`teacherID` INT(4) NOT NULL COMMENT '教师编号',
	PRIMARY KEY (`courseID`),
	KEY `FK_sno` (`sno`),
	KEY `FK_teacherID` (`teacherID`),
	CONSTRAINT `FK_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
	CONSTRAINT `FK_teacherID` FOREIGN KEY(`teacherID`) REFERENCES `teacher` (`teacherID`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 成绩表
CREATE TABLE IF NOT EXISTS `score`(
	`sno` BIGINT(11) NOT NULL COMMENT '学生学号',
	`courseID` INT(4) NOT NULL COMMENT '课程ID',
	`point` INT(3) COMMENT '课程成绩',
	KEY `FK_sno` (`sno`),
	KEY `FK_courseID` (`courseID`),
	CONSTRAINT `FK_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
	CONSTRAINT `FK_courseID` FOREIGN KEY (`courseID`) REFERENCES `course` (`courseID`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

小提示：使用SQL语句先创建school数据库，不要一次性创建所有表格，先创建student表，再创建teacher、course表，最后创建score表(顺便回顾下之前的语句使用，加深巩固)，表中数据就直接在SQLyog中添加即可。

2.4.2、简单查询语句

-- 查询一个表的全部信息	（select * FROM `表名`）
SELECT * FROM student
-- 查询一个表的指定字段 （select `字段名` FROM `表名`）
SELECT `sname` FROM `student`
-- 将查询结果中的字段名换一个易理解的字段名(SELECT ·`字段名1` AS `新字段名1`,`字段名2` AS `新字段名2` FROM `表名`)
SELECT `sname` AS `姓名`,`sno` AS `学号` FROM `student`
-- concat函数 CONCAT(a,b) (SELECT CONCAT('附加信息',`字段名`) AS '新字段名' FROM `表名`)
SELECT CONCAT('姓名：',`sname`) AS '新名字' FROM `student`

SELECT * FROM `score`	--查询全部的考试成绩
SELECT `sno` FROM `score`	--查询哪些人参加了考试
SELECT  DISTINCT`sno` FROM `score`	--查询哪些人参加了考试优化（一个学生可能参加了多门考试,所以查询结果可能出现重复数据，需要对查询结果去重，重复数据只显示一条，关键字 distinct）
SELECT VERSION()	--查询系统的版本(函数)
SELECT 100*7 AS '计算结果'	--计算(表达式)
SELECT @@auto_increment_increment	--查询自增的步长(变量)
SELECT `sno` AS '学号',`point`+4 AS '加分后' FROM `score`	--加分
/*
	数据库中的表达式:文本值，列，NULL,函数,计算表达式,系统变量
	select 表达式 from 表
*/

2.4.3、where关键字

/*
	where 关键字
*/
-- 查询考试成绩在95~100之间
-- ①and
SELECT `sno`,`point` FROM `score`
WHERE `point` >=95 AND `point`<=100
-- ②&&
SELECT `sno`,`point` FROM `score`
WHERE `point`>=95 && `point`<=100
-- ③模糊查询（区间）
SELECT `sno`,`point` FROM `score`
WHERE `point` BETWEEN 95 AND 100
-- ④查询除了'20183033510'之外的学生成绩
-- 1. ！=
SELECT `sno`,`point` FROM `score`
WHERE `sno`!=20183033510
-- 2. not
SELECT `sno`,`point` FROM `score`
WHERE NOT `sno`=20183033510

2.4.4、模糊查询

/*
	模糊查询	关键字like,in
*/
-- ①like
-- 查询姓陈的学生	'%'代表0到任意个字符	'_'代表一个字符
SELECT `sno` ,`sname` FROM `student`
WHERE `sname` LIKE '陈%'
-- 查询姓陈的学生并且名字只有一个字
SELECT `sno`,`sname` FROM `student`
WHERE `sname` LIKE '陈_'
-- 查询姓陈的学生并且名字只有两个字
SELECT `sno`,`sname` FROM `student`
WHERE `sname` LIKE '陈__'
-- 查询名字中间是个'亚'字的学生
SELECT `sno`,`sname` FROM `student`
WHERE `sname` LIKE '%亚%'
-- ②in
-- 查询学号为'20183033505','20183033516','20183033501'的学生
SELECT `sno`,`sname` FROM `student`
WHERE `sno` IN ('20183033505','20183033516','20183033501')
-- 查询地址在江西,陕西的学生
SELECT `sname`,`address` FROM `student`
WHERE `address` IN ('江西赣州','江西九江','江西宜春','江西上饶','江西抚州')
-- ③null NOT NULL
-- 查询地址为空的学生
SELECT `sname`,`address` FROM `student`
WHERE `address` ='' OR `address` IS NULL
-- 查询姓名不为空的学生
SELECT `sname` FROM `student`
WHERE `sname` IS NOT NULL

2.4.5、联表查询

/*
	联表查询Join On
	select ... from a inner join b on ...  只返回左右两表相同字段属性值相等的列
	select ... from a left  join b on ...  以左表为基准,查询的左表字段名所有值全部列出,若右表有值匹										   配,则对应匹配，反之NULL
	select ... from a right join b on ...  以右表为基准,查询的右表字段名所有值全部列出,若左表有值匹										   配,则对应匹配，反之NULL
*/
-- ① INNER JOIN ON
-- 注意：如果将关键字设置为字段的话，使用该字段就要用 `` 括起来
SELECT s.sno,sname,courseId,`point` 
FROM student AS s 
INNER JOIN score AS sc
ON s.sno=sc.sno
-- ② LEFT JOIN ON
SELECT s.sno,sname,courseId,`point` 
FROM student AS s 
LEFT JOIN score AS sc
ON s.sno=sc.sno
-- ③ RIGHT JOIN ON
SELECT s.sno,sname,courseId,`point` 
FROM student AS s 
RIGHT JOIN score AS sc
ON s.sno=sc.sno
-- ④ 综合
SELECT s.sno,sname,courseName,`point`
FROM student AS s
LEFT JOIN score AS sc
ON s.sno=sc.sno
INNER JOIN course AS c
ON sc.courseId=c.courseId

2.4.6、分组查询和分页

/*
	排序 Order By	
	分组 limit
	语法：
	Order By `需要排序的字段名` ASC(升序) DESC(降序)
	limit     起始下标,页面大小
*/
-- 查询courseId等于4的学生成绩，降序排序显示排名前三的学生
SELECT s.sno,sname,courseName,`point`
FROM student AS s
INNER JOIN score AS sc
ON s.sno=sc.sno
INNER JOIN course AS c
ON c.courseId=sc.courseId
WHERE c.courseId=4
ORDER BY `point` DESC
LIMIT 0,3

2.4.7、子查询和嵌套查询

-- 例一：查询课程名字为'数据结构'的所有考试结果(学号，课程编号，成绩) 降序排列
-- ①使用连接查询
SELECT sno,sc.courseID,`point`
FROM score AS sc
INNER JOIN  course AS c
ON c.courseID=sc.courseID
WHERE courseName='数据结构'
ORDER BY `point` DESC
-- ②使用子查询
SELECT sno,sc.courseID,`point`
FROM score AS sc
WHERE sc.courseID=(
	SELECT courseID 
	FROM course 
	WHERE courseName='数据结构'
)
ORDER BY `point` DESC

-- 例二：查询'软件测试'分数大于90分的学生的学号和姓名
-- ①使用连接查询
SELECT DISTINCT  s.sno,sname
FROM student AS s
INNER JOIN score AS sc
ON s.sno=sc.sno
INNER JOIN course AS c
ON sc.courseID=c.courseID
WHERE sc.`point`>90 AND courseName='软件测试'
-- ①使用子查询(初级)
SELECT DISTINCT  s.sno,sname
FROM student AS s
INNER JOIN score AS sc
ON s.sno=sc.sno
WHERE sc.`point`>90 AND courseID=(
	SELECT courseID 
	FROM course
	WHERE courseName='软件测试' 
)
-- ③使用子查询(终极)
SELECT sno,sname FROM student WHERE sno IN(
	SELECT sno FROM score WHERE `point` >90 AND courseID=(
		SELECT courseID FROM course WHERE courseName='软件测试'
	) 
)

2.4.8、常用函数

/*
	MySQL常用函数
*/
-- ①数学运算函数
SELECT ABS(-13)-- 取绝对值
SELECT CEILING(13.2)-- 返回最小整数值不小于13.2
SELECT FLOOR(13.2)-- 返回最大整数不大于13.2
SELECT RAND()-- 返回一个0—1的随机数
SELECT SIGN(0)-- 判断一个数的符号 0-0 正数-1 负数- -1
-- 字符串函数
SELECT CHAR_LENGTH('篮网总冠军')-- 字符串长度
SELECT CONCAT('死神','杜兰特')-- 拼接字符串
SELECT INSERT('哈登真的太屌了',3,2,'牛逼')-- 从某个位置开始替换某个长度
SELECT UPPER('harden')-- 转换成大写字母
SELECT LOWER('HARDEN')-- 转换成小写字母
SELECT INSTR('harden','h')-- 返回第一次出现子串的索引
SELECT SUBSTR('篮网总冠军',3,3)-- 返回指定的字符串(源字符串,截取的位置,截取的长度)
SELECT REPLACE('哈登总冠军','总冠军','FMVP')-- 替换出现的指定字符串
SELECT REVERSE('篮网总冠军')-- 倒序
-- 常用案例 将姓喻的学生改成姓鱼
SELECT REPLACE(sname,'喻','鱼') FROM student WHERE sname LIKE '喻%'
-- 时间日期函数(记住)
SELECT CURRENT_DATE()-- 获取当前日期
SELECT CURDATE()-- 获取当前日期
SELECT NOW()-- 获取当前时间
SELECT LOCALTIME()-- 获取本地时间
SELECT SYSDATE()-- 获取系统时间

2.4.9、聚合函数

/*
	聚合函数
*/
-- ①count() 统计表中的记录
SELECT COUNT('address') FROM student-- count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM course-- count(*) 不会忽略null值
SELECT COUNT(1) FROM student-- count(1) 不会忽略null值
-- ②sum() 、avg()、max()、min() 统计数据
SELECT SUM(`point`) AS 总和 FROM score
SELECT AVG(`point`) AS 平均分 FROM score 
SELECT MAX(`point`) AS 最高分 FROM score
SELECT MIN(`point`) AS 最低分 FROM score

2.4.10、分组和过滤

/*
	分组和过滤
	group by 
	having
*/
-- 查询不同课程的平均分，最高分，最低分
SELECT courseName AS 课程名,AVG(`point`) AS 平均分,MAX(`point`) AS 最高分,MIN(`point`) AS 最低分
FROM score AS sc
INNER JOIN course AS c
ON sc.courseID=c.courseID
GROUP BY c.courseID
HAVING 平均分>93

2.4.11、测试md5

/*
	测试MD5
*/
CREATE TABLE `testmd5`(
	`id` INT(4) 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,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密指定数据
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
-- 机密所有密码
UPDATE testmd5 SET pwd=MD5(pwd) 
-- 插入数据时加密密码
INSERT INTO testmd5 VALUES(4,'yu',MD5('123456'))
-- 校验传来的用户名及密码是否正确
SELECT * FROM testmd5 WHERE `name`='yu' AND `pwd`=MD5('123456')

3、事务

3.1、基本概念

事务原则：ACID原则(原子性,一致性,隔离性,持久性)

具体详细内容参考博客：MySQL数据库的四大特性及隔离级别

/*
	事务:
*/
-- mysql默认开启事务且自动提交
SET autocommit=0/* 关闭自动提交 */
SET autocommit=1/* 开启自动提交(默认) */
-- 事务一般处理过程
-- ①关闭事务自动提交
SET autocommit=0
-- ②事务开启 标记着一个事务的开始，从这个之后的sql都在同一个事务内
START TRANSACTION
-- ③事务处理具体过程
INSERT 过程1
INSERT 过程2
......
-- ④提交:持久化(成功)
COMMIT
-- ⑤回滚:回到事务处理前的样子(失败)
ROLLBACK
-- ⑥事务结束 开启事务自动提交
SET autocommit=1
-- 扩展知识
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

3.2、具体实例

/*
	实例:转账
*/
-- 创建shop数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
-- 创建account表
CREATE TABLE IF NOT EXISTS `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入两条数据
INSERT INTO `account`(`name`,`money`) VALUES('A',5000.00),('B',2000.00)
-- 模拟转账(注意:一条一条SQL语句执行)
SET autocommit=0 -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET money=money-1000 WHERE `name`='A' -- A减500
UPDATE `account` SET money=money+1000 WHERE `name`='B' -- B加500
COMMIT -- 提交事务
ROLLBACK -- 回滚 
SET autocommit=1 -- 开启自动提交

4、索引

4.1、索引介绍

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

4.2、索引原则

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

4.3、索引的数据结构

Btree: InnoDB的默认数据结构

索引的数据结构这里不做多的介绍，有兴趣的可以看下这篇文章：MySQL索引背后的数据结构及算法原理

4.4、索引分类

普通索引

仅加速查询
唯一索引

加速查询 + 列值唯一（可以有null）
主键索引

加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引

对文本的内容进行分词，进行搜索，在特定的数据库引擎中才能使用如MyISAM

4.5、创建删除索引语法

创建索引语法1：CREATE INDEX/UNIQUE INDEX/FULLTEXT INDEX 索引名 on `表名`(`列名`);
创建索引语法2：ALTER TABLE `表名` ADD PRIMARY KEY/INDEX/UNIQUE/FULLTEXT 索引名(`列名`);
删除索引语法：drop index 索引名 on `表名`

注意：

1.主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

2.FULLTEXT 全文索引虽然能在使用了INNODB数据库引擎创建的数据库表中创建成功，但是查询速度与没有创建索引时查询速度几乎差不多，因为INNODN不支持全文索引，只有MyISAM支持。

4.6、测试索引对查询速度的影响

/*
	创建100万条数据测试索引的作用
*/
-- 创建一个用户表
CREATE TABLE `user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
	`password` VARCHAR(100) NOT NULL COMMENT '用户密码',
	`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户昵称',
	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
	`phone` VARCHAR(11) DEFAULT '' COMMENT '手机号',
	`sex` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0：男，1：女)',
	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄'  ,
	PRIMARY KEY(`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入一百万条数据(SQL语句了解即可)
DELIMITER $$ 
CREATE FUNCTION mock_data()
RETURNS INT 
BEGIN 
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `user`(`name`,`password`,`email`,`phone`,`sex`,`age`) VALUES
		(CONCAT('用户',i),UUID(),'1989166742@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END ;
-- 未创建索引时查找数据的速度
SELECT * FROM `user` WHERE `name`='用户13'; 	-- 1.231 sec
SELECT * FROM `user` WHERE `name`='用户7';  	-- 1.212 sec
SELECT * FROM `user` WHERE `name`='用户99999';	-- 1.218 sec
-- 给'name'字段添加一个普通索引
CREATE INDEX user_name ON `user`(`name`);
-- 创建了'普通索引'查找数据的速度
SELECT * FROM `user` WHERE `name`='用户13';	-- 0.008 sec
SELECT * FROM `user` WHERE `name`='用户7'; 	-- 0.003 sec
SELECT * FROM `user` WHERE `name`='用户99999';	-- 0.007 sec
EXPLAIN SELECT * FROM `user` WHERE `name`='用户99999';
-- 查看指定表中的所有索引
SHOW INDEX FROM `user`

4.7、索引机制

1.为什么添加完索引后查询速度为变快？
传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍
在我们添加完索引之后，在mysql中一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历 (折半查找大幅查询效率)，找到相应的键从而获取数据。

2.添加索引的代价

创建索引是为产生索引文件的，占用磁盘空间
索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降

3.一般在哪些字段上使用索引？

较频繁的作为查询条件字段应该创建索引
在where子句中的字段创建索引
唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
更新非常频繁的字段不适合作为索引

5.数据库用户管理

/*
	数据库用户管理
*/
-- 创建用户 create user 用户名 identified by '密码'
CREATE USER 'yyw' IDENTIFIED BY '123456'
-- 修改当前用户密码
SET PASSWORD=PASSWORD('111111')
-- 修改指定用户密码
sET PASSWORD for yyw=PASSWORD('000000')
-- 重命名 rename user 旧名字 to 新名字
RENAME USER yyw TO YYW
-- 用户授权 只有root用户才能授予权限
GRANT ALL PRIVILEGES ON *.* TO YYW
-- 查询权限
SHOW GRANTS FOR YYW
SHOW GRANTS FOR root@localhost
-- 撤销权限 revoke 哪些权限 on 哪个库哪个表 from 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM YYW
-- 删除用户
DROP USER YYW

6.MySQL备份

为什么需要备份数据库？
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份方式
- 直接拷贝物理文件
- 在Sqlyog可视化软件手动导出
- 使用命令行mysqldump导出
  
  mysqldump -h主机名 -u用户名 -p密码数据库名表名 >物理磁盘位置/文件名
使用命令行将sql文件导入指定数据库中

①登录MySQL

mysql -u用户名 -p密码

②切换到指定数据库

use 数据库名

③source sql文件路径地址

7.数据库的三大范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。
在这里插入图片描述

第一范式(1NF)

数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式(2NF)

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。第（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

第三范式(3NF)

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖），第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。

规范化的目的

规范化目的是使结构更合理，消除存储异常，使数据冗余尽量小。便于插入、删除和更新。有时故意保留部分冗余可能更方便数据查询。尤其对于那些更新频度不高，查询频度极高的数据库系统更是如此。

规范化的优点

避免了大量的数据冗余，节省了存储空间，保持了数据的一致性。当一个库里的数据经常发生变化时，达到3NF的库可以使用户不必在超过两个以上的地方更改同一个值。

规范化太高的缺点

范式越高意味着表的划分更细，一个数据库中需要的表也就越多，用户不得不将原本相关联的数据分摊到多个表中。当用户同时需要这些数据时只能采用连接表的形式将数据重新合并在一起。同时把多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。

规范性和性能的问题

考虑商业化的需求和目标(成本，用户体验)，数据库的性能更加重要
在规范性能的问题的时候，需要适当的考虑一下规范性
故意给某些表增加一些冗余的字段。(从多表查询变成单表查询)
故意增加一些计算列(从大数据量降低为小数据量的查询：索引)
依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。第（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。