零基础入门学习MySQL全套完整学习笔记（包含DML，DQL基本语法，常用函数，索引，JDBC以及完整案例）

本篇学习笔记总结自bilibiliup主【狂神说Java】系列视频：【狂神说Java】MySQL最新教程通俗易懂
视频作者公众号：狂神说

1、初始MySQL

1.1、数据库分类

关系型数据库(SQL)：

• MySQL, Oracle, Sql Server, DB2
• 通过表与表之间的关系，行和列之间的关系进行数据的存储

非关系型数据库（NoSQL）：

• Redis, MongDB
• 非关系型数据库使用对象存储，通过对象的自身属性来决定

1.2、MySQL安装配置

1.2.1、安装

• 在MacOS环境下，从MySQL官网上找到下载，并且保存初始密码

• 修改初始密码

  用SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');修改密码。

1.2.2、解决错误ERROR 1045 (28000)

1. 关闭MySQL服务

2. 在终端输入命令行

   cd /usr/local/mysql/bin
   sudo su
   

3. 终端命令行开头变为sh-3.2#后，输入如下命令

   ./mysqld_safe --skip-grant-tables &
   

4. 打开新终端，输入

   mysql -u -root
   

5. 现在命令行变为mysql开头，已经无密码登陆数据库，输入以下语句

   UPDATE mysql.user SET authentication_string = PASSWORD('123456') WHERE User = 'root' AND Host = 'localhost';
   

6. 启动MySQL，输入

   mysql -u root -p
   

7. 可以使用新密码（123456）登陆了

1.2.3 终端连接数据库

打开终端

mysql -u root -p /*连接数据库*/
flush privileges; /*刷新权限*/

show tables;  /*查看表*/
describe student; /*显示数据库中表的信息*/

2、MySQL使用

2.1、MySQL的SQL语言分类

SQL语言一共分为4大类：数据定义语言DDL，数据操纵语言DML，数据查询语言DQL，数据控制语言DCL

2.1.1、数据定义语言DDL

• 英文：Data Definition Language

• 对象： 数据库和表

  • 关键词： create alter drop truncate; (删除当前表再新建一个一模一样的表结构)
  • 创建数据库：create database school;
  • 删除数据库：drop database school;
  • 切换数据库：use school;
  • 查看数据库里存在的表：show tables;

2.1.2、数据操纵语言DML

• 英文：Data Manipulation Language

• 对象：纪录(行)

  • 关键词：insert update delete
  • 插入：insert into student values(01,'tonbby',99); (插入所有的字段)
  • 更新：update student set name = 'tonbby',score = '99' where id = 01;
  • 删除：delete from tonbby where id = 01;

• 开发中很少使用delete,删除有物理删除和逻辑删除，其中逻辑删除可以通过给表添加一个字段(isDel)，若值为1，代表删除；若值为0，代表没有删除。此时，对数据的删除操作就变成了update操作了

• truncate和delete的区别：

  • truncate是删除表，再重新创建这个表。属于DDL，delete是一条一条删除表中的数据，属于DML。

2.1.3、数据查询语言DQL

• 英文：Data Query Language

  select ... from student where 条件 group by 分组字段 having 条件 order by 排序字段
  

  • 执行顺序：from->where->group by->having->order by->select
  • 注意：group by通常和聚合函数(avg(),count()...)一起使用 ,经常先使用group by关键字进行分组，然后再进行集合运算。
  • group by与having 一起使用，可以限制输出的结果，只有满足条件表达式的结果才会显示。

• having和where的区别：

  两者起作用的地方不一样，where作用于表或视图，是表和视图的查询条件。having作用于分组后的记录，用于选择满足条件的组。

2.1.4、数据控制语言DCL

• 英文：Data Control Language
• 数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视，用户，权限，事务等。
  • grant：授权
  • rollback：回滚
  • commit：提交

2.1、操作数据库

1. 创建数据库

   create database [if not exists] db;
   

2. 删除数据库

   drop database [if exists] db;
   

3. 使用数据库

   use `db`;   /*如果数据库名是一个特殊字符，需要加``框起来*/
   

4. 查看数据库

   show database; /*查看所有数据库*/
   

2.2、数据库的列类型

2.2.1、整数类型

• tinyint ：一个字节
• smallint：两个字节
• mediumint 三个字节
• int：四个字节 标准的整数（常用）
• bigint ：八个字节

2.2.2、浮点数类型

• float ：浮点数 四个字节
• double：浮点数 八个字节
• decimal：字符串形的浮点数（金融计算一般使用，避免精度问题）

2.2.3、字符串类型

• char ：字符串固定大小 0～255 字节
• varchar：可变字符串 0~65535 字节（对应Java的String类型，常用）
• tinytext：微型文本 2^8 - 1 比特
• text： 文本串 2^16 - 1 比特（保存大文本）

2.2.4、时间类型

• date：YYYY-MM-DD 日期
• time：HH:MM:SS 时间格式
• datetime： YYYY-MM-DD HH:MM:SS 最常用的时间格式
• timestamp：1970.1.1 到现在的毫秒数 全世界时间地区统一，较为常用
• year：年份表示

2.2.5、Null类型

• 没有值，未知
• 不要使用Null进行运算，结果一定为Null

2.3、数据库的字段属性

• unsigned：无符号的整数，不能声明为负数
• zerofill：默认进行零填充，不足的位数使用0进行填充
• AutoIncreasment：自增，自动在上一条记录上加一，可以自定义主键子增的起始值和步长
• NotNull：非空，如果不赋值会报错
• default：设置默认值

/*每一个表都必须存在以下5个字段，用来保证安全性和健壮性*/
`id` 主键
`version` 乐观锁
`is_delete` 伪删除
`gmt_create` 创建时间
`gmt_update` 修改时间

2.4、创建数据库表

2.4.1、语法格式

/*表的名称和字段用``框起来*/
create table if not exists `student`(
    `id` int(4) not null auto_increment comment '主键自增',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(30) 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 Table `student`   /*查看创建时的语句*/
desc `student`   /*显示表的结构*/

2.4.2、数据库引擎分类

• InnoDB：MySQL 5.8默认使用

• MYISAM：MySQL 早期使用

  	MYISAM	InnoDB
  事务支持	不支持	支持
  数据行锁定	不支持	支持
  外键约束	不支持	支持
  全文索引	支持	现在仅支持英文
  表空间大小	较小	较大，约等于MYISAM两倍

常规使用操作

• MYISAM：节约空间，速度较快
• InnoDB：安全性比较高，支持事务处理，支持多表对用户操作

2.4.3、在物理空间的位置

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

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

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

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

Charset = utf8

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

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

character-set-server=utf8

2.6、修改数据库表

2.6.1、语法格式

修改

/*修改表名：ALTER TABLE 旧表名 RENAME AS 新表名*/
alter table teacher rename as teacher1

/*增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性*/
alter table teacher add Email varchar(10)

/*修改表的字段（重命名，修改约束）*/
alter table teacher modify age varchar(11) /*修改约束*/
alter table teacher change age age1 varchar(11) /*字段重命名*/

/*删除表的字段*/
alter table teacher1 drop age1

删除表

drop table if exists teacher1

3、MySQL数据管理

3.1、外键

• 创建

  • 创建外键可以在创建表的时候创建（非常麻烦）

  • 在创建两个不相关的表之后通过添加约束创建

  /*ALTER TABLE 表 
    ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 引用到哪个表的哪个字段
  */
  alter table `student` 
  add constraint `fk_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
  
  

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

以上的操作都是物理外键，数据库级别的外键，不建议使用！避免数据库过多造成困扰

• 最佳实践
  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
  • 我们想使用外键，使用程序级别实现

3.2、DML语言

• Insert

  /*插入语句（添加）*/
  /*insert into 表名([字段名1，字段名2，字段名3)) value ('值1','值2','值3') */
  insert into `student`(gender, birthday, address, email)value ('男','1996-2-28','重庆','303333549@qq.com'),('女','1996-2-18','悉尼','303333549@qq.com');
  

  • 字段与字段使用英文逗号隔开
  • 可以同时插入多条数据，value后面的值需要隔开，如value (),(),()

• update

  /**UPDATE 表名 set colnum_name = value where [条件]**/
  update `student` set gender = '女' where `id` = 1;
  update `student` set gender = '男',email = '32232@qq.com' where `id` = 1;
  

  条件：where 子句 运算符

  操作符	含义	范围	结果
  =	等于	5	false
  <> 或者!=	不等于	5 <> 6	true
  beteen…and…	在某个范围内	a[]	[2,5]
  and	&&两个条件都成立	条件a&&条件b	符合a,b条件
  or	||两个条件其中一个成立	条件a||条件b	符合a,b条件之一

• delete

  /*删除指定数据*/
  delete from student where id = 1;
  
  /*清空一个表*/
  truncate `student`
  

  • 相同点：都能删除数据，都不会删除表结构
  • 不同：
    • truncate 会重新设置自增链，delete会影响自增链
    • truncate不会影响事务

3.2、DQL语言

• 所有的查询操作 select
• 数据库中最核心的语言
• 使用频率最高

基本语法

select [distinct] {* | table.* | [table.field1[as something1],table.field2[as something2],...]}
from table_name1
/*联表查询 如果链接条件，加 on table_name1.something = table_name2.something*/
[left | right | inner join table_name2] 
/*指定结果需要满足的条件*/
[where ..]
/*指定结果按照哪几个字段分组,不可以使用聚合函数*/
[group by]
/*过滤分组的记录必须满足的次要条件*/
[having]
/*指定查询记录按照一个或者多个条件排序*/
[order by]
/*指定查询的条件从哪里到哪里*/ 
[limit {[offset.]row_count | row_countOFFSET offset}];

注意：[]括号代表可选，{}代表必选，并且子句顺序不能换

3.2.1、简单查询

/*查询所有学生*/
select * from student;

/*查询指定字段 (可以用别名)*/
select 'studentNo' as 学号,'StudentName' as 学生姓名 from student as s;

/*函数concat进行拼接*/
select concat('姓名：',StudentName) as 新名字 from student;

3.2.2、表达式

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…

/*查询哪些学生参加了考试，成绩*/
/*去除select语句中所有重复的数据，重复数据只查询一条*/
select distinct `studentNo` fromn result;

select version()  /*查询系统版本(函数)*/
select 100*3 as 计算结果 /*用来计算（表达式）*/
select @@auto_increment_increment /*查询自增的步长（变量）*/
select 'StudentNo','StudentResult' + 1 as '提分后' from result /*学生考试成绩+1分后查看*/ 

select 表达式 from 表

3.2.3、where条件子句

所有的where语句的返回结果都要为布尔值

select stduentNo from result where StudentResult > 90 && StudentResult < 100

/*除了1000号学生之外的成绩*/
select studentNo, 'StudentResult' from result where studentNo != 1000;

模糊查询

• like： a like b SQL匹配，如果a匹配到b，则结果为真

  /*查询姓刘的同学*/
  /*like结合 %（代表0到任意个字符）*/
  select `studentNo`,`studentName` from `student` where StudentName like '刘%';
  
  /*查询姓刘的同学，名字后面只有一个字 _（代表0到一个）*/
  select `studentNo`,`studentName` from `student` where StudentName like '刘_';
  
  /*查询姓刘的同学，名字后面只有两个字 __（代表0到任意个字符）*/
  select `studentNo`,`studentName` from `student` where StudentName like '刘__';
  
  /*查询名字中带有某个字的同学*/
  select `studentNo`,`studentName` from `student` where StudentName like '%某%';
  

• in：a in (a1,a2,a3…) 假设a在a1,或者a2…其中的某一个值中，只能是具体的一个值，结果为真

  /*查询1001,1002,1003号学员*/
  select `studentNo`,`studentName` from `student` where StudentNo in(1001,1002,1003)
  

3.2.4、联表查询

/*查询参加了考试的同学（学号，姓名，科目编号，分数）*/
select * from student;
select * from result;
/*
思路：1、分析需求，分析哪些字段来自哪些表
		 2、确定使用哪种连接查询？一共有7种
		 3、确定交叉点（两张表中哪个数据是相同的）
		 
		 判断条件，学生表中的studentNo 等于 成绩表中的 studentNo
		 
*/

/*Inner join 实例*/
select s.studentNo, studentName, SubjectNo, StudentResult 
from student as s inner join result as r 
where s.studentNo = r.studentNo

/*Right join 实例*/
/*比如在student表中有一个同学没有参加考试，在result信息表中没有该同学*/
/*这个查询不能查到该同学（因为他没有出现在result表）*/
select s.studentNo, studentName, studentResult
from student as s right join result r on s.studentNo = r.studentNo;

/*left join实例 包括左边的表result的所有东西*/
/*比如在student表中有一个同学没有参加考试，在result信息表中没有该同学*/
/*这个查询可以查到该同学（因为他出现在student表）*/
select s.studentNo, studentName, studentResult
from student as s left join result r on s.studentNo = r.studentNo;

/*查询缺考的同学*/
/*比如在student表中有一个同学没有参加考试，在result信息表中没有该同学*/
/*该查询可以查到所有缺考的同学，即存在在student表，却没有存在在result表的所有同学*/
select s.studentNo, studentName, studentResult
from student as s left join result r on s.studentNo = r.studentNo
where studentResult is null;

/*查询参加了考试的同学的 学号，学生姓名，科目名，科目分数*/
/*分别来源于 student表，student表，subject表，result表*/
/*student表 result表 用 studentNo 链接*/
/*result表 subject表 用 studentNo 链接*/
select s.studentNo, studentName, subjectName, studentResult
from student as s right join result as r on s.studentNo = r.studentNo
where studentResult is null
inner join `subject` sub on r.subjectNo = sub.subjectNo

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

• join + [链接的表]） 一般和 on一起使用，表示连接查询
• where表示 等值查询

3.2.5、自链接

自己的表和自己的表链接，核心：一张表拆成两张一样的表

• 自链接案例表（pid为父id）

categoryId	pid	categoryName
3	1	软件开发
5	1	美术设计
4	3	数据库
8	2	办公信息
2	1	信息技术
6	3	web开发
7	5	ps技术

• 父类表

categoryId	categoryName
2	信息技术
3	软件开发
5	美术设计

• 子类表

categoryId	pid	categoryName
4	3	数据库
8	2	办公信息
6	3	web开发
7	5	ps技术

• 操作父类对应子类的关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

/*查询父子信息*/
select a.`categoryName` as '父栏目', b.`categoryName` as '子栏目' from 'category' as a, 'category' as b where a.`categoryId` = b.`pid`;

3.2.6、分页和排序

• order by：排序

  升序 （asc） 降序（desc）

  /*查询某一科成绩结果，成绩按照一定顺序排序*/
  /*查询到的结果按照 成绩降序 排序*/
  select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
  from student as s
  Inner join result as r
  on s.`StudentNo` = r.`studentNo`
  Inner join subject as sub
  on r.`subjectNo` = sub.`subjectNo`
  where subjectName = '数据结构'
  order by studentResult desc;
  

• limit ：分页

  /*查询某一科成绩结果，成绩按照一定顺序排序*/
  /*查询到的结果按照 成绩降序 排序*/
  
  /*分页，每页只显示五条数据*/
  /*语法：limit 起始值,页面的大小*/
  /*网页应用：当前页，总页数，页的大小*/
  
  /*第一页 limit 0,5  (1-1) * 5 */
  /*第二页 limit 5,5  (2-1) * 5 */
  /*第三页 limit 10,5  (3-1) * 5 */
  /*第四页 limit x,PageSize  x = (n-1) * pageSize, pageSize */
  /*【页面大小：PageSize】*/
  /*【页面起始值：(n-1) * pageSize】*/
  /*【当前页：n】*/
  /*【总页数 = 总数/页面大小】*/
  
  
  
  /*第一页为：limit 0,5   查找为第一到第五的数据*/
  /*第二页为：limit 5,5  查找为第六到第十的数据*/
  select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
  from student as s
  Inner join result as r
  on s.`StudentNo` = r.`studentNo`
  Inner join subject as sub
  on r.`subjectNo` = sub.`subjectNo`
  where subjectName = '数据结构'
  order by studentResult desc;
  limit 1,5
  

3.2.7、子查询

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

/*查询数据结构的所有考试结果（学号，科目编号，成绩），降序排列*/

/*方式一：使用链接查询*/
select `studentNo`,r.`subjectNo`,`StudentResult`
from `result` as r
inner join `subject` as sub
on r.`subjectNo` = sub.`subjectNo`
where subjectName = '数据结构'
order by desc

/*方式二：使用子查询*/
/*第一步，找到所有考试的学生学号，科目编号，成绩*/
select `studentNo`,r.`subjectNo`,`StudentResult`
from `result` as r where `studentNo` = （）

/*第二部：子查询中，找到参加了数据结构的学生学号*/
select StudentNo from `subject` where SubjectName = '数据结构'

/*第三部：放进第一步，由子查询开始查，链接到主语句*/
select `studentNo`,r.`subjectNo`,`StudentResult`
from `result` as r where `studentNo` = （
	select StudentNo from `subject` where SubjectName = '数据结构'
）

4、MySQL函数

4.1、常用函数

/*数学运算*/
select abs(-8)   /*绝对值*/
select ceilling(6.33) /*向上取整*/
select floor(6.32)  /*向下取整*/
select rand(); /*返回一个随机数*/
select sign(23)   /*判断一个数的符号，负数返回-1，正数返回1*/

/*字符串函数*/
select char_length('Neillll');  /*字符串的长度*/
select concat('Neil','Join','mmm')  /*拼接字符串*/
select insert('I love helloword',1,1,'you') /*从第一个位置替换一个长度*/

/*查询姓周的同学，并替换成邹*/
select replace(studentname,'周','邹') from student;

/*时间和日期函数*/
select current_date() /*获取当前日期*/
select now()   /*获取当前时间*/
select localdate(); /*获取当地时间*/
select sysdate();  /*获取系统时间*/
select version();  /*MySQL当前版本*/

4.2、聚合函数

函数名称	描述
count()	计数
sum()	求和
avg()	求平均值
max()	求最大值
min()	求最小值

/*查询不同课程的平均分，最高分，最低分，平均分大于80*/
select subejctName,AVG(studentResult) as 平均分 ,MAX(studentResult),MIN(studentResult)
from result r
inner join subject sub
on r.subjectNo = sub.subjectNo
group by r.SubjectNo     /*通过学科进行分组*/
having 平均分 > 80

4.3、数据库级别的MD5加密

/*假设现在有一个user表，里面的字段为id, user_name, pwd*/

/*明文密码*/
insert into `user` value(1,'zhangsan','12333');

/*加密*/
update `user` set pwd = MD5(pwd);  /*所有密码MD5加密*/

/*插入时加密*/
insert into `user` value (1,'zhangsan',MD5('12333'))

/*校验*/
select * from `user` where `user_name`='zhangsan' and pwd=MD5('12345');

5、事务

5.1、基本概念

5.1.1、事务的ACID

谈到事务一般都是以下四点

• 原子性（Atomicity）
  原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）
  事务前后数据的完整性必须保持一致。
• 隔离性（Isolation）
  事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• 持久性（Durability）
  持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响 （一旦提交不可逆）

具体案例分析：https://blog.csdn.net/dengjili/article/details/82468576

###5.1.2、事务隔离级别

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

5.1.3、隔离级别设置

5.2、事务的操作

MySQL 默认开启事务自动提交的

set autocommit = 0：关闭提交

set autocommit = 1：开启（默认）

• 事务开启

  1. set autocommit = 0：关闭提交

  2. start tansaction：标记一个事务的开始，从这个之后的SQL都在同一个事物内

  3. 执行若干操作，可以使用savepoint设置事务一个保存点

  4. commit：提交：持久化（成功）

     • rollback：回滚
     • rollback to savepoint 保存点：来回滚到保存点
     • release savepoint 保存点名：删除保存点

  5. set autocommit = 1：事务结束之后开启自动提交

• 案例

  /*创建数据库以及表*/
  create database shop charset 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 ('Neil',2000.00),('Andy',10000);
  
  /*模拟转账操作*/
  set autocommit = 0;  /*关闭自动提交*/
  start transaction;  /*开启一个事务*/
  /*模拟Neil给Andy进行转账*/
  update account set  money = money - 500 where name = 'Neil';
  update account set  money = money + 500 where name = 'Andy';
  /*提交*/
  commit ;
  /*回滚*/
  rollback ;
  /*恢复默认值*/
  set autocommit = 1;
  

6、索引

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

索引的本质是数据结构

6.1、索引的分类

• 主键索引（primary key）
  • 唯一的标识，主键不可重复，只有一个列
• 唯一索引（unique key）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引。
• 常规索引（key/index）
  • 默认的，可以用index关键字和key关键字来设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有，可以快速定义

6.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('刘');

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` 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` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,          /*自动设置创建时间*/
      `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  /*自动设置更新时间*/
      primary key (`id`)
  )engine = InnoDB default charset =utf8mb4 comment ='app用户表';
  
  /*插入100万条数据*/
  
  /*写函数之前必须要写*/
  DELIMITER $$
  
  create function mock_data2()
  returns int
  begin
      declare num int default 1000000;
      declare i int default 1;
      while i < num do
          insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
          values (concat('用户',i),'303333@qq.com',concat('18',floor(rand()*((999999999-100000000) + 100000000))),floor(rand()*2),UUID(),floor(rand()*100));
          set i = i + 1;
      end while;
      return i;
  end;
  /*有点像执行函数的意思*/
  select mock_data2();
  

• 测试没有索引的查询，并且使用explain查看效率

  /*在MacBook Pro16环境下测试查询效率*/
  select * from app_user where name = '用户559999' /*用时400 ms*/
  /*使用explain 查看效率，发现rows一览993160*/
  

• 创建索引

  /*create index 索引名 on 表(字段名)*/
  /*索引命名规则：id_表名_字段名*/
  create index id_app_user_name on app_user(`name`);
  /*使用explain 查看效率，发现rows一览为1*/
  

• 再次查询

  select * from app_user where name = '用户559999' /*用时68 ms*/
  

• 索引在100万条数据下，查询效果明显提升

6.4、索引原则

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

InnoDB默认数据结构为B树

索引背后的数据结构与原理 ：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

8、权限管理与备份

8.1、用户管理

一般通过IDE可以可视化操作用户,实际上是对系统用户表进行操作，以下为一些常用命令

/*创建用户*/
create user neil identified by '123123';

/*修改密码*/
set password = PASSWORD('1231321');

/*重命名 rename user 原来的名字 to 新的名字*/
rename user neil to neillll;

/*用户授权 all privileges 全部的权限，库，表，除了grant权限*/
grant all privileges on *.* to neillll;

/*查看权限*/
show grants for neillll;

/*撤销权限，在哪个库撤销，给谁撤销*/
revoke all privileges on *.* from neilll;

8.2、MySQL备份

备份的原因：

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

MySQL数据备份的方式

• 直接拷贝物理文件
• 在可视化工具手动导出
• 使用终端mysqldump命令行使用

9、规范数据库设计

9.1、数据库设计

• 糟糕的数据库设计
  • 数据冗余，浪费空间
  • 数据库插入和删除会麻烦和异常
  • 程序性能差
• 良好的数据库设计
  • 节省内存空间
  • 保存数据库完整性
  • 方便开发系统

9.2、三大范式

• 第一范式（原子性）：要求数据库表的每一行每一类都是不可分割的原子数据项
• 第二范式：第一范式基础上，每张表只描述一件事情
• 第三范式：每一列直接数据都与主键直接相关，而不是间接相关

规范性和性能冲突的问题：

**阿里文档要求：**根据商业化需求，关联查询表不得超过三张

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

10、JDBC

10.1、创建驱动手动链接数据库

• 创建数据库

  /*创建数据库*/
  create database jdbsStudy character set utf8 collate utf8_general_ci;
  
  use jdbsStudy;
  
  create table users(
      id int primary key,
      name varchar(40),
      password varchar(40),
      email varchar(60),
      birthday date
  );
  
  insert into users(id,name,password,email,birthday)
  values(1,'Neil','123456','asdas12@qq.com','1980-12-23'),
       (2,'andy','123456','asdas12@qq.com','1977-2-12'),
         (3,'Dwan','123456','356489@qq.com','1996-2-14')
  

• java代码

  public class jdbcDemo01 {
      public static void main(String[] args) throws ClassNotFoundException, SQLException {
          //1、加载驱动，固定写法
          Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
          //2、用户信息和url
          // useUnicode=true&characterEncoding=utf8&useSSL=true  支持中文编码，设置字符集，安全链接(这里改成True链接失败了，查阅资料发现在Mac上会失败，windows不会，所以变成False)
          String url = "jdbc:mysql://localhost:3306/jdbsStudy?useUnicode=true&characterEncoding=utf8&useSSL=False";
          String userName = "root";
          String userPwd = "123456";
          //3、链接成功，数据库对象  connection代表数据库
          Connection connection = DriverManager.getConnection(url, userName, userPwd);
          //4、执行sql对象
          Statement statement = connection.createStatement();
          //5、执行sql对象去执行sql，可能存在结果，查看结果
          String sql = "select * from users";
  
          ResultSet resultSet = statement.executeQuery(sql);
  
          while(resultSet.next()){
              System.out.println("id" + resultSet.getObject("id"));
              System.out.println("name" + resultSet.getObject("name"));
              System.out.println("password" + resultSet.getObject("password"));
              System.out.println("email" + resultSet.getObject("email"));
              System.out.println("birthday" + resultSet.getObject("birthday"));
          }
          //6、释放链接
          resultSet.close();
          statement.close();
          connection.close();
      }
  }
  

• 详解

  • Url

    // mysql: 3306
    //jdbc:mysql://主机地址:端口号/数据库?参数1&参数2参数3
    String url = "jdbc:mysql://localhost:3306/jdbsStudy?useUnicode=true&characterEncoding=utf8&useSSL=False";
    

  • DriverManager

    // connection 代表数据库
    //数据库设置自动提交回滚
    Connection connection = DriverManager.getConnection(url, userName, userPwd);
    //如：
    connection.rollback();
    connection.commit();
    connection.setAutoCommit();
    

  • Statement

    //statemnt :执行SQL的对象
    statement.executeQuery();  //查询返回一个ResultSet，返回所有信息
    statement.execute();    //可以执行任何sql
    statement.executeUpdate;  //执行增删改，返回受影响的行数
    

  • ResultSet

    resultSet.getObject(); //在不知道类型的情况下使用
    //如果知道类型就使用指定类型
    resultSet.getString(); 
    resultSer.getInt();
    resultSer.getDate();
    ...
    
    resultSet.next()  //遍历
    resultSet.absolute(row) //移动到指定行
    
    

10.2、PreparedStatement对象

PreparedStatemnt 可以防止SQL注入，并且效率更高

/*statement 对象有可能存在sql注入问题*/
import java.sql.*;

public class jdbcDemo02 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        PreparedStatement st = null;
      	//通过?代替参数
        String sql = "insert into users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";

        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2、用户信息和url
        // useUnicode=true&characterEncoding=utf8&useSSL=true  支持中文编码，设置字符集，安全链接(这里改成True链接失败了，查阅资料发现在Mac上会失败，windows不会，所以变成False)
        String url = "jdbc:mysql://localhost:3306/jdbsStudy?useUnicode=true&characterEncoding=utf8&useSSL=False";
        String userName = "root";
        String userPwd = "123456";
        //3、链接成功，数据库对象  connection代表数据库
        conn = DriverManager.getConnection(url, userName, userPwd);     
        st  = conn.prepareStatement(sql);//预编译sql
        //手动给st赋值
        st.setInt(1,5);//给id赋值
        st.setString(2,"Neil");//给名字
        st.setString(3,"123132");//密码
        st.setString(4,"30564968@qq.com");
        st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
       //执行
        st.executeUpdate();
        //释放
        st.close();
        conn.close();
    }
}

10.3、数据库连接池

数据库连接 – 执行完毕 – 释放

连接 - 释放 十分浪费资源

池化技术：准备一些预先的资源，过来就连接准备好的资源

常用连接数：10个左右

最小连接数：10

最大连接数：100（业务最高承载上限）

超过最大连接数，就排队等待–> 等待超时：100ms

编写连接池：实现一个接口：DateSource

• 开源数据源实现：
  • DBCP
  • C3P0
  • Druid：阿里巴巴

使用数据库连接池之后，我们在项目开发中不需要编写连接数据库的代码

===========================================================

参考文献：本篇学习笔记主要总结于bilibili up主 “狂神学Java” 的系列视频— 【狂神说Java】MySQL最新教程通俗易懂

若有侵权请联系我删除