MySQL

1.初识MySQL

概念 : 是现在流行的开源的,免费的 关系型数据库

历史 : 由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。

特点 :

• 免费 , 开源数据库
• 小巧 , 功能齐全
• 使用便捷
• 可运行于Windows或Linux操作系统
• 可适用于中小型甚至大型网站应用

官网 : https://www.mysql.com/

安装MySQL

软件下载

mysql5.7 64位下载地址:

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

1.1安装步骤

1、下载后得到zip压缩包.

2、解压到自己想要安装到的目录，本人解压到的是D:\Environment\mysql-5.7.19

3、添加环境变量：我的电脑->属性->高级->环境变量

选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹
D:\Environments\mysql-5.7.19\bin

4、编辑 my.ini 文件 ,注意替换路径位置

[mysqld]
basedir=D:\Environments\mysql-5.7.19\
datadir=D:\Environments\mysql-5.7.19\data\
port=3306
skip-grant-tables  #跳过密码验证

5、启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)

6、再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件

7、然后再次启动mysql (net start mysql), 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）

D:\Environments\mysql-5.7.19\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

8、进入界面后更改root密码

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

9、刷新权限

flush privileges;

10、修改 my.ini文件,注释或删除最后一句skip-grant-tables

#skip-grant-tables

11、重启mysql即可正常使用

net stop mysql
net start mysql

12、连接上测试出现以下结果就安装好了

1.2连接数据库

打开MySQL命令窗口

• 在DOS命令行窗口进入 安装目录\mysql\bin
• 可设置环境变量，设置了环境变量，可以在任意目录打开！

连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码

注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !

打开dos窗口，输入：

几个基本的数据库操作命令 :

mysql -uroot -p[密码]  --连接数据库

update mysql.user set authentication_string=password('[密码]') where user='root';  --修改密码
flush privileges; --刷新权限
show databases; --查看所有数据库
use dbname；--切换使用某个数据库
show tables; --显示数据库mysql中所有的表
describe user; --显示user表的列信息
create database name; --创建数据库
use databasename; --选择数据库

exit; --退出Mysql
? 命令关键词 : --寻求帮助
-- 表示注释

数据库xxx语言

DDL: 数据库定义语言 （Database Definition Language）

DML: 数据库操纵语言（Database Manipulation Language）

DQL: 数据库查询语言（Database QueryLanguage）

DCL: 数据库控制语言（Database Control Language）

2.操作数据库

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

2.1 操作数据库

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] demo;

2.删除数据库

DROP DATABASE [IF EXISTS] demo;

3.使用数据库

USE demo;

4.查看所有数据库

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 常用的 对应Java中的string
• tinytext 微型文本 2^8 - 1
• text 文本串 2^16 - 1
• longtext 大文本串 2^32-1

时间日期

java.util.Date;

• date YYYY-MM-DD 日期格式
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD hh:mm:ss 常用的时间格式
• timestamp 时间戳 1970到现在的毫秒数 ，也较为常用
• year 年份表示YYYY

null

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

2.3 数据库的字段属性(重点)

Unsigned:

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

zerofill

• 0填充
• 不足的位数，使用0填充， 例：int(3) 5为：005

自增 AUTO INCREMENT

• 通常理解为自增，自动上一条记录的技术上加一
• 通常用来设计唯一的主键–index，必须为整数类型
• 可定义为设计主键的起始值和步长

非空

• Not Null: 设置为Not Null ，插入数据时，如果不给它赋值，就会报错
• Null: 可以为空

默认 default

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

拓展，阿里巴巴规范

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

2.4 创建数据库表

例子：

--创建一个student表
--学号id 姓名name    密码password 性别gender 出生日期birthday 地址address   邮箱email
-- int  varchar(30) varchar(20) varchar(2) DATETIME       VARCHAR(100) VARCHAR(50)


--注意：表的名称和字段尽量使用 ``  括起来
--字符串使用 '' 
--PRIMARY KEY 主键，一个表只有一个主键
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 '密码',
`gender` 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;

格式

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

常用命令

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

2.5 数据表的类型

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

常规使用操作：

• MYISAM 节约时间，速度较快
• INNODB 安全性高，事务的处理，多表多用户

在物理空间存在的位置

所有的数据库文件都纹在data目录下

本质还是文件存储

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

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

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

CHARSET=utf8

不设置的话，mysql默认的字符集编码 (不支持中文)

默认的编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

建议还是在建表的时候设置字符集编码。

2.6 修改删除表

修改

--修改表名：  ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE student RENAME AS student1;
--增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性; 
ALTER TABLE student1 ADD age INT(2);
--修改表的字段：
--ALTER TABLE 表名 MODIFY 字段名 列属性[];
ALTER TABLE student1 MODIFY age VARCHAR(2);
--ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[];
ALTER TABLE student1 CHANGE age age1 INT(2);

--modify 可以修改字段属性，但不能修改字段名
--change 既可以修改字段属性又可以修改字段名

--删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE student1 DROP age;

删除

--删除表
DROP TABLE IF EXISTS student1; --如果表存在就删除

【注】

• 所有的创建和删除尽量加上判断，以免报错。
• `` 表明和字段名，使用这个包裹
• 注释 –
• sql关键字大小写不敏感
• 所有的符号用英文

3.MySQL的数据管理

3.1 外键

3.1.1 物理外键(了解)

方式一、在创建表的时候，增加约束

CREATE TABLE IF NOT EXISTS `grade`(
   `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `name` VARCHAR(10) NOT NULL COMMENT '年级名字',
   PRIMARY KEY (`id`)
   
)ENGINE=INNODB DEFAULT CHARSET=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 '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
 PRIMARY KEY (`id`) ,
 KEY `fk_gradeid` (`gradeid`),
 CONSTRAINT  `fk_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

方式二

CREATE TABLE IF NOT EXISTS `grade`(
   `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `name` VARCHAR(10) NOT NULL COMMENT '年级名字',
   PRIMARY KEY (`id`)
   
)ENGINE=INNODB DEFAULT CHARSET=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 '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
 PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;


-- alter table 表名 add constraint [约束名] foreign key (作为外键字段名) references 引用表名 (字段名);
ALTER TABLE `student` 
ADD CONSTRAINT `fk_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`id`);

物理外键不建议使用。当需要删除表或者数据时需要考虑外键约束。

3.2 DML语言(重点)

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

DML语言：数据库操作语言

• insert
• update
• delete

3.2.1 添加

insert into

-- 插入语句
-- insert into 表名([字段名1,字段名2,字段名3...]) values ('值1','值2','值3',...);

INSERT INTO `grade` (`name`) VALUE('一年级');
INSERT INTO `grade` (`name`) VALUE('一年级'),('二年级'),...;

3.2.2 修改

-- 修改语句
-- update 表名 set 字段名=值 [条件语句]

UPDATE `grade` SET `name`='二年级' WHERE id = 1;  --修改单个值
UPDATE `grade` SET `name`='二年级',address='找不到地点' WHERE id = 1;  --修改多个值

语法：update 表名 column_name = value,[column_name = value,...] where [条件]

3.2.3 删除

-- 删除数据

DELETE FROM `student`;  --清空数据表，保留表结构
DELETE FROM `student` WHERE id = 1;

truncate 命令

TRUNCATE TABLE `student`;  --自增会归0

完全清空数据表，保留表结构，不影响事务；

4.DQL查询数据(重点)

data query language：数据查询语言

• 所有的查询操作都用它 select
• 简单的查询，复杂的查询
• 数据库中最核心的语言
• 使用频率最高的语言

4.1 指定查询字段

--查询全部学生
SELECT * FROM student;
--查询指定字段
SELECT `studentno`,`studentname` FROM student;
-- 起别名, as可以省略
SELECT studentno 学号,studentname AS 姓名 FROM student AS s;

-- 函数 Concat(a,b) 拼接ab
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student;

语法：select 字段,... from 表名

distinct 去重

SELECT DISTINCT studentno FROM result; -- 去掉学号相同的重复数据

使用表达式

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

--成绩+1 
select studentno,studentresult + 1  from result; 

4.2 where条件子句

作用：检索数据中符合条件的值

搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假.

测试

-- 满足条件的查询(where)
SELECT Studentno,StudentResult FROM result;

-- 查询考试成绩在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;

模糊查询

运算符	语法	描述
like	字段 like b	SQL匹配，例如：小明 匹配 小明子，王小明
in	字段 in (a,b, c,…)	只查询集合中的信息

-- ===like 查询姓名===
-- like结合 %(代表0~任意字符) _（代表只有一个字符）

-- 查询姓张的学生信息，后面任意字符
SELECT * FROM `student` WHERE studentname LIKE '张%';

-- 查询姓张的学生信息，后面只有一个字符
SELECT * FROM `student` WHERE studentname LIKE '张_';

-- 查询姓张的学生信息，后面只有两个字符
SELECT * FROM `student` WHERE studentname LIKE '张__';

-- 查询名字中带有明的学生信息
SELECT * FROM `student` WHERE studentname LIKE '%明%';

-- === in ===
-- 查询学号为 1001 1003的学生信息
SELECT * FROM `student` WHERE `studentno` IN (1001,1003);

-- 查询出生日期为空的学生信息
SELECT * FROM `student` WHERE `borndate`='';
SELECT * FROM `student` WHERE `borndate` IS NULL;

-- 查询出生日期不为空的学生信息
SELECT * FROM `student` WHERE `borndate` IS NOT NULL;

4.3 联表查询

join 对比

-- === 联表查询 join ===

-- 内联接
SELECT DISTINCT s.studentno,studentname,subjectno,studentresult FROM student s INNER JOIN result r 
WHERE s.studentno = r.`studentno`;

-- 右联结
SELECT DISTINCT s.studentno,studentname,subjectno,studentresult FROM student s RIGHT JOIN result r 
ON s.studentno = r.`studentno`;

-- 左联接
SELECT DISTINCT s.studentno,studentname,subjectno,studentresult FROM student s LEFT JOIN result r 
ON s.studentno = r.`studentno`;

操作符名称	描述
inner join	也称为等值连接，返回两张表都满足条件的部分
left join	取左边的表的全部，右边的表按条件，符合的显示，不符合则显示null
right join	取右边的表的全部，左边的表按条件，符合的显示，不符合则显示null

-- 通过三张表查询学生的姓名，科目名称，分数

SELECT s.studentno,studentname,subjectname,studentresult 
FROM student s
LEFT JOIN result r ON s.`studentno` = r.studentno 
INNER JOIN `subject` su ON r.`subjectno` = su.`subjectno`; 

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'

自联结(了解)

一张表假设为 两张一样的表。

SELECT a.categoryName AS '父级名字',b.categoryName AS '子集名字'
FROM category  a,category  b
WHERE a.`categoryid`=b.`pid`

4.4 排序和分页

4.4.1 排序

语法：order by 字段值 asc/desc

-- === 排序 order by === 
-- asc 升序(默认) desc 降序 
SELECT DISTINCT s.studentno,studentname,subjectno,studentresult 
FROM student s 
LEFT JOIN result r 
ON s.studentno = r.`studentno` 
order by s.studentno asc;

4.4.2 分页

语法：limit pageIndex pageSize

-- === 分页 limit ===
-- 为什么要分页
-- 缓解数据库压力， 比瀑布流较好

-- 语法 liuyuyufa mit pageIndex pageSize 
SELECT DISTINCT s.studentno,studentname,subjectno,studentresult 
FROM student s 
LEFT JOIN result r 
ON s.studentno = r.`studentno` 
ORDER BY s.studentno ASC
LIMIT 0,2;

• pageIndex：起始值
• pageSize：查询条数

公式：limit (pageIndex-1)*pageSize , pageSize

4.5 子查询

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

SELECT studentno, subjectno,studentresult FROM result 
WHERE studentno 
= (SELECT studentno FROM SUBJECT WHERE subjectname='C语言-1');

1. 表关联的效率要高于子查询，因为子查询走的是笛卡尔积
2. 表关联可能有多条记录，子查询只有一条记录，如果需要唯一的列，最好走子查询

4.6 分组和过滤

  -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;

5.mysql函数

5.1 常用函数

官网地址：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

-- ====== 常用函数 ======

-- 数学运算
SELECT ABS(-1);  -- 绝对值
SELECT CEIL(9.5);  -- 向上取整
SELECT RAND();   -- 返回一个0~1之间的随机数
SELECT SIGN(10);  -- 返回参数的符号  负数返回 -1 ，正数返回 1，0 返回 0

-- 字符串
SELECT CHAR_LENGTH('字符串长度');  -- 字符串长度  返回5
SELECT CONCAT('hello','world');   -- 字符串拼接
SELECT INSERT('张三',1,2,'san');  -- 插入和替换 ，在指定位置插入字符串并替换指定长度的字符
SELECT LOWER('ZHangSan');  -- 转为小写
SELECT UPPER('zhangsan');  -- 转为大写
SELECT REPLACE('人工智能','智能','智障');   -- 替换出现的指定字符串
SELECT SUBSTR();  -- 截取字符串，类似java中的substring

-- 时间和日期
SELECT CURRENT_DATE(); -- 获取当前日期,2020-11-23
SELECT NOW(); -- 获取当前时间，2020-11-23 15:18:20
SELECT LOCALTIME();  -- 获取本地时间 2020-11-23 15:19:00
SELECT SYSDATE();  -- 获取系统时间2020-11-23 15:19:40

 -- 获取年月日,时分秒
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

系统信息函数

 SELECT VERSION();  /*版本*/
 SELECT USER();     /*用户*/

5.2 聚合函数

函数名称	描述
COUNT()	返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和。
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值。
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值。

 --  ==== 聚合函数 ====
SELECT COUNT(studentno) FROM student;  -- count(指定列)，会忽略所有的null值
SELECT COUNT(*) FROM student;   -- count(*), 不会忽略所有的null值
SELECT COUNT(1) FROM student;   -- count(1), 不会忽略所有的null值
  
 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 /*
 下面它们之间的一些对比：
 
 1）在表没有主键时，count(1)比count(*)快
 2）有主键时，主键作为计算条件，count(主键)效率最高；
 3）若表格只有一个字段，则count(*)效率较高。
 */

5.3 数据库级别的MD5加密

MD5信息摘要算法(MD5 Message-Digest Algorithm)，一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。

-- 加密
UPDATE testMD5 SET pwd=MD5(pwd) WHERE id =1;
UPDATE testMD5 SET pwd=MD5(pwd);

-- 插入时加密
INSERT INTO `testMD5`(`id`,`name`,`pwd`) VALUES(1,'张三',MD5('12345'));

-- 如何校验: 将用户传递进来的密码进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name` = '张三' AND pwd = MD5('12345'); 

6.事务

6.1 什么是事务

一般是指要做的或所做的事情。

• 事务就是将一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

事务的ACID原则

原子性(Atomic)

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

一致性(Consist)

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

隔离性(Isolated)

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

持久性(Durable)

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

隔离所导致的问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

6.2 执行事务

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

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

-- 提交：持久化
COMMIT
-- 回滚：回到原来的的样子
ROLLBACK;
-- 事务结束 
SET autocommit = 1;  -- 开启自动提交


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

模拟场景

-- 创建一个数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;

USE shop;

CREATE TABLE `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('张三',4000.00),('李四',5000.00);

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

UPDATE account SET money=money-500 WHERE `name`='张三';  -- 张三给李四转账500
UPDATE account SET money=money+500 WHERE `name`='李四';  -- 李四收到张三的转账500

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

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

7.索引

7.1 索引的分类

• 主键索引(primary key)

  • 唯一的标识，主键不可重复

• 唯一索引(unique key)

  • 避免重复的列出现，唯一索引可以重复(多个列都可以表示为唯一索引)

• 常规索引(key)

  • 默认的，index,key关键字来设置

• 全文索引(fulltext)

  • 在特定的数据库引擎下才有，myisam

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


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

-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的状况 

EXPLAIN SELECT * FROM student;  -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张'); 

7.2 测试索引

CREATE TABLE `app_user`(
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
 `email` VARCHAR(50) DEFAULT '' COMMENT '用户邮箱',
 `phone` VARCHAR(11) 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

-- 插入一百万条数据
DELIMITER $$ 

CREATE FUNCTION mock_data()-- 创建函数名 mock_data

RETURNS INT  -- 返回值

BEGIN
    DECLARE num INT DEFAULT 1000000; -- 声明 num 值为1000000
    DECLARE i INT DEFAULT 0; -- 声明 i 循环变量
    
    WHILE i<num DO
     INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'23322211@qq.com',CONCAT('13',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i=i+1;
    END WHILE;
    RETURN i;
END;
-- 调用函数
SELECT mock_data();

SELECT * FROM app_user WHERE NAME='用户9999'; -- 1.018 sec

-- id_表名_字段名
-- create index 索引名 on 表名(字段)
CREATE INDEX id_app_user_name ON app_user(`name`); 

SELECT * FROM app_user WHERE NAME='用户9998';  -- 0 sec

EXPLAIN SELECT * FROM app_user WHERE NAME='用户9998';

以上可以看出：有索引执行时比没有索引要快。

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

7.3 索引原则

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

文章阅读：https://blog.codinglabs.org/articles/theory-of-mysql-index.html

8.权限管理和备份

8.1 用户管理

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

-- 创建用户 
-- 语法 create user 用户名 identified by '密码'
CREATE USER xuexintai IDENTIFIED BY '123456';

-- 重命名
RENAME USER xuexintai TO xxt;

-- 修改密码 修改当前用户密码
SET PASSWORD = PASSWORD('12356');

-- 修改指定用户密码
SET PASSWORD FOR xuexintai=PASSWORD('123456');

-- 用户授权 all privileges 全部权限
GRANT ALL PRIVILEGES ON *.* TO xuexintai;  -- 授予xuexintai全部的权限 其中*.* 是库和表

-- 查看权限
SHOW GRANTS FOR xuexintai;
SHOW GRANTS FOR root@localhost; -- 查看root权限

-- 撤销权限 revoke 
REVOKE  ALL PRIVILEGES ON *.* FROM xuexintai;

-- 删除用户
DROP USER xuexintai;

8.2 备份

为什么要备份？

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

MySQL数据库备份的方式

• 直接拷贝物理文件
• 在SQLyog这种可视化工具中手动到处
• 使用命令行导出 mysqldump

语法：mysqlbump -h 主机 -u 用户名 -p 密码 数据库 [表名,...] > 物理磁盘位置:/文件名

不添加表名表示将整个库全部导出，添加一个表名或多个表名，表示导出一张或多张表

#`mysqlbump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置:/文件名`
C:\Users>mysqldump -hlocalhost -uroot -p******* school student > d:/1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

C:\Users>mysqldump -hlocalhost -uroot -p******* school student result> d:/1.sql

C:\Usersmysqldump -hlocalhost -uroot -p******* school> d:/1.sql

导入

• 在登陆数据库的情况下,切换到指定数据库

  use school;
  source d:/a.sql
  

• 没有登陆情况下

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

9.数据库设计规范

9.1 为什么需要设计

当数据库比较复杂的时候，需要设计

糟糕的数据库设计 :

• 数据冗余,存储空间浪费
• 数据更新和插入的异常
• 程序性能差

良好的数据库设计 :

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

• 需求分析阶段: 分析客户的业务和数据处理需求
• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库的步骤：个人博客

• 收集信息，分析需求
  • 用户表(用户登录注销，用户的个人信息，写博客，创建分类)
  • 分类表(文章分类，谁创建的)
  • 文章表(文章的信息)
  • 评论表
  • 自定义表(系统信息，某个关键的字，或者一些主字段)
• 标识实体

9.2 三大范式

三大范式

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

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

第二范式（2NF）

前提：满足第一范式前提下

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式前提下

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

规范化和性能的关系

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