MySQL

1、初识数据库

1.1、学习数据库的原因

• 市场岗位需求（目前行业内的java招聘要求都需要收敛掌握一种数据库）
• 世界现在的飞速发展，已经成为一个大数据时代，只有拥有自己的数据库才可以稳定生存。
• 企业拥有大量的用户数据，都需要存储。
• 数据库是所有软件体系中最核心的存在

1.2、什么是数据库

数据库（DB,Database），是按照数据结构来组织、存储和管理数据的仓库。它是一个软件，安装在操作系统（windows，Linux，mac……）之上。
数据也可以存储在文件中，但是在文件中读写数据速度比较慢。

1.3、数据库的分类

关系型数据库

存储的结构可以直观反应实体间的关系。通过表和表之间、行和列之间的关系进行数据的存储。
常见的关系型数据库：MySQL、Oracle、Sql Server、DB2……

非关系型数据库（NoSQL）

非关系型数据不适合这样的表格存储方式，通常以数据集的方式，大量的数据集中存储在一起，类似于键值对、图结构或者文档。
常见的关系型数据库：Redis、MongDB……

关系型数据库管理系统（RDBMS）

现在我们通常使用关系型数据库管理系统（RDBMS，Relational Database Management System）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

• 关系型数据库管理系统的特点：
• 数据以表格的形式出现
• 每行为各种记录名称
• 每列为记录名称所对应的数据域
• 许多的行和列组成一张表单
• 若干的表单组成Database

1.4、MySQL简介

MySQL是一个关系型数据库管理系统。由瑞典MySQL AB公司开发，现在是Oracle旗下的产品。
MySQL是最好的RDBMS应用软件之一。

• MySQL是开源的额。
• MySQL支持大型的数据库，支持5000万条记录的数据仓库。
• MySQL使用标准的SQL数据语言形式。
• 体积小、速度快、灵活。

1.5、安装MySQL

1. 首先解压文件
   
2. 配置环境变量
3. 新建mysql.ini配置文件
   
4. 启动管理员模式下的CMD，运行命令
5. ……
6. 其余的安装步骤就不一一展示，不了解的可去网上查找
7. 安装出现问题时，可输入 sc delete mysql命令，清空前面的信息，重新输入命令安装

1.6、基本命令

mysql -uroot -p123456  --连接数据库
show databases;  --查看所有数据库
use school  --切换数据库  
show tables;  --查看数据库中的所有的表
describe 表名; --显示数据库中所有表的信息
create database 数据库名; --创建一个数据库
exit; --退出数据库

1.7、查询语句分类

名称	解释	命令
DDL（数据库定义语言）	定义和管理数据对象，如数据库、表等	create、drop、alter
DML（数据库操作语言）	用于操作数据库对象中所包含的数据	INSERT、UPDATE、DELETE
DQL（数据库查询语言）	用于查询数据库	SELECT
DCL（数据库控制语言）	用于管理数据库中的语言，包括管理权限及数据更改	GRANT、COMMIT、ROLLBACK

2、操作数据库

mysql关键字不区分大小写。

2.1、操作数据库

通过命令行操作数据库的命令

1. 创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名

2. 删除数据库

DROP DATABASE IF EXISTS 数据库名

3. 使用数据库

USE 数据库名

4. 查看所有数据库

SHOW DATABASES

建议：可以通过SQLyog的可视化历史记录查看sql语句来学习

2.2、数据库的列类型

• 数值类型

类型	说明	字节数
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的数据	8个字节
float	单精度浮点数	4个字节
double	双精度浮点数	8个字节
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.1.1到现在的毫秒数
year	年份时间

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

2.3、数据库的字段属性

Unsigned（无符号的）：

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

zerofile：

• 0填充的
• 不足的位数，使用0来填充，int（3），则5会显示为005

自增：

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

非空（NULL/not null）：

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

默认：

• 设置默认的值。
• 例如性别：sex，默认值为：男。如果不给指定该列的值，则就会有默认的值：男。

2.4、创建数据库表（重点）

-- 目标：创建一个school数据库
-- 创建学生表
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住 址,email


-- 使用英文括号，表的名称和字段最好使用``反引号括起来
-- AUTO_INCREMENT:自增
-- 默认的：DEFAULT
-- 字符串使用单引号括起来
-- 所有的语句后面加分号(英文的)，最后一句不用加
-- PRIMARY KEY：主键， 一般一个表只有一个唯一的主键
CREATE DATABASE `school`
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '登录密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT  NULL COMMENT '出生日期',
    `address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式模板：

-- 中括号[]  括起来的都是可选项
CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列的类型 [属性] [索引] [注释],
    `字段名` 列的类型 [属性] [索引] [注释],
    …………
    `字段名` 列的类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令：

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

2.5、数据表的类型

数据库表的类型

-- 数据库的表的引擎
-- INNODB    默认使用
-- MYISAM    早些年使用的

数据表引擎的区别：

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

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

在物理空间的存在位置

所有的数据库文件都存在daata目录下，一个文件夹就对应一个数据库。数据库的本质还是文件的存储。

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

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

数据库表的字符集编码

CHARSET=utf8

如果不设置字符集编码，会是mysql默认的字符集编码。（不支持中文）
MySQL的默认编码是Latin1，不支持中文
在my.ini中可以配置默认的编码：

character-set-server=utf8

2.6、修改删除表

修改

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段  ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(3)


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

/*
MODIFY和CHANGE的区别：
MODIFY：只可以修改字段类型和约束，不能对字段名进行重命名
CHANGE：可以对字段进行重命名，不能修改字段类型和约束
*/


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

删除

-- 删除表：加一个判断条件：IF EXISTS。 如果表存在，删除
DROP TABLE IF EXISTS teacher1

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

注意：sql关键字不区分大小写，建议小写

3、MySQL数据管理

3.1、外键（了解）

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

注：图片来自搜狗百科。

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

CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
   PRIMARY KEY (`gradeid`)  -- 定义主键
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的  gradeid  字段   要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）
-- constraint：约束     references：引用
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` 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 '年纪id',
    `address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid`(`gradeid`),  -- 定义外键
    CONSTRAINT `FK_gradeid` FOREIGN  KEY (`gradeid`) REFERENCES `grade` (`gradeid`) -- 给外键添加约束
)ENGINE=INNODB DEFAULT CHARSET=utf8

注意：删除有外键关系表的时候，必须先删除引用别人的表（从表，也就是上面的 student 表），再删除被引用的表（主表，也就是上面的 grade 表）

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

CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
   PRIMARY KEY (`gradeid`)  -- 定义主键
)ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` 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 '年纪id',
    `address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- alter 改变

-- 创建表的时候没有外键关系
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(哪个字段)
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

以上的操作都是物理外键，数据库级别的外键，不建议使用。了解即可

3.2、DML语言（重点）

数据库意义：数据存储，数据管理
DML语言：数据库操作语言

• insert（添加，插入）
• update（修改）
• delete（删除）

3.2.1、insert（添加）

-- 插入语句
-- 语法：insert into 表名 ([字段名1,字段名2,字段名3]) values （'值1'）,（'值2'），（'值3'），
INSERT INTO grade (`gradename`) VALUES ('大三')
-- 由于主键自增，可以省略不写（如果不写表的字段名，他就会一一匹配）
-- 一般写插入语句，我们要数据和字段一一对应


-- 插入多个字段
INSERT INTO `grade` (`gradename`) VALUES ('大一'),('大二')

ALTER TABLE `student` MODIFY `gradeid` DEFAULT NULL
ALTER TABLE `student` DROP `gradeid`
ALTER TABLE `student` ADD `gradeid` INT(10) DEFAULT NULL COMMENT '年纪id'

INSERT INTO `student`(`name`) VALUES('李四')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('王五','112233','男')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('小明','112233','男'),('小芳','112233','女')

注意事项：
1、字段和字段之间使用英文逗号隔开
2、字段可以省略，但是后面的值必须要意义对应，不能少
3、可以同时插入多条语句，values后面的值需要逗号隔开

3.2.2、修改（update）

-- 修改学员名字
-- 语法: update 表名 set colnum_name = value  where [条件
UPDATE `student` SET `name`='小星星' WHERE `id`=1;

-- 如果不指定条件，会改动所有的表
UPDATE `student` SET `name`='星哥' 

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='杨幂11',`email`='987654' WHERE `id`=1

条件：where子句 写一些运算符，例如：id等于某个值，大于某个值，在某个区间内修改……
操作符会返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或！=	不等于	5<>6	true
>
<
<=
>=
netween… and…	在某个范围内	[2,5]
and	我和你 &&	5>1 and 1>2	false
or	我或你	5>1 or 1>2	true

4、DQL查询数据（重中之重）

4.1、DQL

（DQL：Data Query LANGUAGE）数据查询语言

• 所有的查询操作都用它，Select
• 简单的查询，复杂的查询它都可以做
• 数据库中最核心的语言，最重要的语言
• 使用频率最高的语句

select完整语法

4.2、指定查询字段

-- 查询全部的学生   SELECT 字段名 FROM 表
SELECT *FROM student

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM `student`

-- 别名，给结果起名字
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM `student`

-- 函数
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM `student`

语法： SELECT 字段，字段，字段…… FROM 表
有的时候列名字不是很见名知意。我们就起别名

去重
作用：去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条

 -- 发现重复数据，去重。查询的结果中有重复数据，只显示一个
SELECT DISTINCT `StudentNo` FROM result

数据库的列（表达式）

SELECT VERSION() -- 查询系统版本
SELECT 100*3-5 AS 计算结果  -- 计算
SELECT @@auto_increment_increment   -- 查询自增的步长
-- 学员考试成绩+1分查看
SELECT `StudentNo`,`StudentResult`+1 AS '加分后' FROM result

4.3、where条件子句

作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成！结果布尔值
逻辑运算符

-- 查询考试成绩在95~100分之间的
SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE `StudentResult`>=95 AND `StudentResult`<=100


-- and  &&
SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE `StudentResult`>= 95 && `StudentResult`<=100

-- 模糊查询
SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE `StudentResult`BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE `StudentNo` !=1000

-- !=  not
SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE  NOT `StudentNo` =1000

模糊查询

运算符	语法	描述
IS NULL	a is null	如果操作符为null，结果为真
IS NOT NULL	a is not null	如果操作符不为null，结果为真
BETWEEN	a between b and c	若a 在 b 和c 之间，则结果为真
Like	a like b	SQL 匹配，如果a匹配b，则结果为真
In	a in (a1,a2,a3….)	假设a在a1，或者a2…. 其中的某一个值中，结果为真

-- ============模糊查询===========
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符)  _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '刘%'

-- 查询姓刘的同学
-- 名字后面只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '刘_'

-- 查询名字中间有嘉字的同学  %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '%嘉%'


-- in（具体的一个或多个值）
-- 查询 1001,1002,1003号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentNo` IN (1001,1002,1003)

-- 查询地址为空的学生 null''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address`='' OR `Address` IS NULL

-- 查询有出生日期的同学  不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

-- 查询没有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS  NULL

4.4、联表查询

join

-- ==========联表查询======
-- 查询不同表中的数据
-- 查询参加了考试的同学信息（学号，姓名，科目编号，分数）
SELECT *FROM `student`
SELECT *FROM `result`

/*
思路：
1、分析需求：分析查询的字段来自那些表，（连接查询）
2、确定使用哪种连接查询？共七种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的`StudentNo`=成绩表中的`StudentNo`
*/
-- INNER JOIN
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo`= r.`StudentNo`

-- RIGHT JOIN 
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`StudentNo`= r.`StudentNo`

-- LEFT JOIN 
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`StudentNo`= r.`StudentNo`

以上三种的结果：
学生表中的李锦阳同学没有考试成绩（只存在于学生表中，不在resurt表中）
INNER JOIN的结果

Right join

LEFT join

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- 查询参加了考试的同学信息（学号，姓名，科目名，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
RIGHT JOIN `result` r 
ON r.`StudentNo`=s.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo`=r.`SubjectNo`

4.5、分页和排序

排序

-- 排序：升序 ASC  降序 DESC
-- 语法：ORDER BY 通过哪个字段排序 怎么排
-- 需求：查询的结果根据 成绩排序 降序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
LEFT JOIN `result` r 
ON r.`StudentNo`=s.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo`=r.`SubjectNo`
WHERE `SubjectName`='数据库结构-1'
ORDER BY `StudentResult` DESC

分页

分页可以缓解数据库压力，给人体验视觉好

-- 分页 每页只显示5个数据
-- 语法：limit 起始值，页面的大小
-- LIMIT 0,5  1~5
-- LIMIT 1,5  2~6
-- LIMIT 2,5  3~7
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
LEFT JOIN `result` r 
ON r.`StudentNo`=s.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo`=r.`SubjectNo`
WHERE `SubjectName`='数据库结构-1'
ORDER BY `StudentResult` DESC
LIMIT 0,5

• 第一页：limit 0,5 （1-1）*5,5
• 第二页：limit 5,5 （2-1）*5,5
• 第三页：limit 10,5 （3-1）*5,5
• 第n页： （n-1）*pageSize,pageSize
• pageSize：页面的大小
• （n-1）*pageSize：起始值
• n：当前页
• 数据总数/页面大小 = 总页数

4.6、子查询

本质：在where语句中嵌套一个子查询语句

 -- 1、查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列 
 -- 方式一： 使用连接查询 
 SELECT `StudentNo`,r.`SubjectNo`,`StudentResult` 
 FROM `result` r 
 INNER JOIN `subject` sub 
 ON r.SubjectNo = sub.SubjectNo 
 WHERE SubjectName = '数据库结构-1' 
 ORDER BY StudentResult DESC
  -- 方式二： 使用子查询（由里及外）
   -- 查询所有数据库结构-1 的学生学号 
   SELECT `StudentNo`,`SubjectNo`,`StudentResult` 
   FROM `result` 
   WHERE SubjectNo = ( 
         SELECT SubjectNo
         FROM `subject`
         WHERE SubjectName = '数据库结构-1' 
    )
  ORDER BY StudentResult DESC

4.7、分组和过滤

-- 分组查询
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT `SubjectName`,AVG(`StudentResult`) AS 平均分,MAX(`StudentResult`) AS 最高分,MIN(`StudentResult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sub
ON r.`StudentNo`=sub.`SubjectNo`
GROUP BY r.`StudentNo`  -- 通过什么字段来分组
HAVING 平均分 >80

4.8、小结

5、MySql函数

5.1、常用函数

-- ==================== 常用函数 =============================
-- 数学运算 
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整 
SELECT RAND() -- 返回一个 0~1 之间的随机数 
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1，正数返回 1 

-- 字符串函数 
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度 
SELECT CONCAT('我','爱','你们') -- 拼接字符串 
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('KuangShen') -- 小写字母 
SELECT UPPER('KuangShen') -- 大写字母 
SELECT INSTR('kuangshen','h') -- 返回第一次出现的子串的索引 
SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 替换出现的指定字符串 
SELECT SUBSTR('狂神说坚持就能成功',4,6) -- 返回指定的子字符串 (源字符串,截取的位置，截取的长度) 
SELECT REVERSE('清晨我上马') -- 反转 
-- 查询姓 周的同学，名字 邹 
SELECT REPLACE(studentname,'周','邹') FROM student 
WHERE studentname LIKE '周%' 
-- 时间和日期函数 （记住） 
SELECT CURRENT_DATE() -- 获取当前日期 
SELECT CURDATE() -- 获取当前日期 
SELECT NOW() -- 获取当前的时间 
SELECT LOCALTIME() -- 本地时间 
SELECT SYSDATE() -- 系统时间 
SELECT YEAR(NOW()) 
SELECT MONTH(NOW()) 
SELECT DAY(NOW()) 
SELECT HOUR(NOW()) 
SELECT MINUTE(NOW()) 
SELECT SECOND(NOW()) 

-- 系统 
SELECT SYSTEM_USER() -- root@localhost
SELECT USER() -- root@localhost
SELECT VERSION() -- 5.7.19 版本号

5.2、聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

--  =======聚合函数====
-- 都能够统计 表中的数据 （ 想查询一个表中有多少个记录，就使用这个count() ）
SELECT COUNT(`StudentNo`) FROM `student` -- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM `student`  -- count（*） ，不会忽略null值，本质就是计算行数
SELECT COUNT(1) FROM `student`  -- count（1） ，不会忽略null值，本质就是计算行数

SELECT SUM(`StudentResult`) AS 总和 FROM `result`
SELECT AVG(`StudentResult`) AS 平均分 FROM `result`
SELECT MAX(`StudentResult`) AS 最高分 FROM `result`
SELECT MIN(`StudentResult`) AS 最低分 FROM `result`

5.3、数据库级别的MD5加密

MD5：主要增强算法复杂度和不可逆性。
MD5 不可逆，具体的值的 md5 是一样的
MD5 破解网站的原理，背后有一个字典， MD5加密后的值 ，加密的前值

INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','123123')

-- 加密
UPDATE `testmd5` SET pwd =MD5(pwd) WHERE id = 1

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(3,'wangwu',MD5('123456'))

6、事务

要么都成功，要么都失败。
事务的原则：ACID原则。

• 原子性（Atomicity）
  要么都成功，要么都失败。
• 一致性（Consistency）
  事务前后的数据完整性要保持一致。
• 持久性（Durability） ----事务提交
  事务一旦提交则不可逆，被持久化到数据库中。
• 隔离性（Isolation）
  事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离所导致的问题

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

执行事务

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

-- 手动处理事务
SET autocommit = 1  -- 关闭自动提交
-- 开启事务
START TRANSACTION  -- 标记一个事务的开始，从这个之后的sql都在一个事务内

INSERT xxx
INSERT xxx

-- 提交：持久化，（成功）
COMMIT

-- 回滚：回到原来的样子（失败）
ROLLBACK

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

事务的处理过程：
1、关闭事务自动提交
2、开启事务
3、执行事务
4、提交
5、回滚
6、事务结束

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE account(
     `id` INT(5) NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(20) NOT NULL,
     `money` DECIMAL(9,2) NOT NULL,
     PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


DROP TABLE account -- 删除表
INSERT INTO account(`name`,`money`) VALUE('A',200.00),('B',800.00)


-- 模拟转账：事务
SET autocommit = 0  -- 关闭自动提交事务
START TRANSACTION  -- 开启事务

UPDATE account SET money=money-200 WHERE `name`='A'  -- A减200
UPDATE account SET money=money+200 WHERE `name`='B'   -- B加200

COMMIT;  -- 提交事务，就被持久化了
ROLLBACK -- 回滚

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

7、索引

MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。

7.1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个。

• 主键索引（PRIMARY KEY）
  唯一的标识，主键不可重复，只能有一个列作为主键。
• 唯一索引（UNIQUE KEY）
  避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引。
• 常规索引（KEY/INDEX）
  默认的，index、key关键字来设置
• 全文索引（FULLText）
  在特定的数据库引擎下才有，MyISAM
  快速定位数据
  基础语法

-- 索引的使用 
-- 1、在创建表的时候给字段增加索引 
-- 2、创建完毕后，增加索引 
-- 显示所有的索引信息 
SHOW INDEX FROM student 

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

-- EXPLAIN 分析sql执行的状况 

EXPLAIN SELECT * FROM student;  -- 非全文索引 
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

7.2、测试索引

• 未增加索引

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.098 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

- 增加索引

-- 添加索引
-- create index 索引名 on 表（字段）
-- 索引命名规则   id _ 表名 _ 字段名
CREATE INDEX id_app_user_name ON app_user(`name`)

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001 sec 
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显

7.3、索引的使用原则

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

8、权限管理和备份

8.1、用户管理

用户表：mysql.user
本质：读这张表进行增删改查

-- 创建用户   CREATE USER 用户名 IDENTIFIED BY '密码'
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 全部的权限 ， 库.表 
-- ALL PRIVILEGES 除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
-- ROOT用户权限：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

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

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

-- 删除用户
DROP USER kuangshen

8.2、MySQL备份

为什么备份：

• 保证重要的数据不丢失
• 数据转移
  MySQL数据备份的方式
• 直接拷贝物理文件
• 在sqlyog这种可视化工具中手动导入
  在想要导出的表或者库中，右键，选择备份或者导出
  
• 使用命令行导出 mysqldump 黑窗口使用命令

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

# 导入 
# 登录的情况下，切换到指定的数据库 
# source 备份文件 
source d:/a.sql 

mysql -u用户名 -p密码 库名< 备份文件