墙裂推荐：狂神说Java
1. 初识MySQL
1.1 保存数据的容器 数组、集合等保存于内存中。潜在风险：断电全无
文件（如txt），缺点：不方便查找
…
为解决以上问题，数据库便诞生了，数据库的特点：1.实现了数据持久化；2.使用完整的管理系统统一管理，便于查询
1.2 概念

• DB：database，数据库。保存的数据有组织，比较规范
• DBMS：database management system。数据库管理系统，比如MySQL、sql server等
• SQL：结构化查询语言

1.3 SQL优点

• 几乎所有的DBMS都支持SQL
• 简单易学
• 语言强有力，使用灵活，可以构造非常复杂的数据库

1.4 关系型数据库 VS 非关系型数据库

• 关系型数据库：通过表和表之间，行和列之间的关系进行数据的存储，比如MySQL、Oracle等
• 非关系型数据库：使用对象存储，通过对象的自身的属性来决定，比如Redis、MongoDB等

1.5 MySQL优点

• 成本低
• 性能高
• 容易安装，使用简单

2. MySQL下载及安装
版本问题：使用较多的是5.7版本，最新的有8.0版本，两者在语言或者功能上可能有些差别
下载和安装的整个流程如下：
直接百度搜索：mysql5.7下载，第一个网页就是官网下载

文章图片

或者直接打开网页https://dev.mysql.com/downloads/mysql/5.7.html
选择自己的版本，（windows/linux/macos 32位/64位）
建议下载压缩包（下图），因为压缩包的安装和卸载非常简单。exe文件在安装时非常复杂（exe文件安装可以参考尚硅谷视频，大概有十几步）exe文件在卸载时更麻烦，包括卸载MySQL、删除注册表等等。

文章图片

下载完成后解压文件，修改文件名，只留下mysql和版本号。然后将整个文件夹剪切到自己平常安装软件的目录，比如我的是D:\APP\mysql-5.7.30。
添加环境变量：右击此电脑–>属性–>高级系统设置–>环境变量–>系统变量中找到path–>点击“编辑”–>点击“新建”–>添加mysql的bin目录(我的是D:\APP\mysql-5.7.30\bin)–>连续点击三个“确定”即可
在安装目录中添加my.ini文件，这是MySQL的配置文件

文章图片

打开my.ini文件（记事本就可以打开），输入以下命令：

[mysqld] basedir=D:\APP\mysql-5.7.30\ datadir=D:\APP\mysql-5.7.30\data\ port=3306 skip-grant-tables

basedir是MySQL的安装目录，datadir是数据的存放目录，这两个目录需要修改为自己的目录。另外，未初始化之前是没有data目录的，切记不要自己在文件夹中添加data文件夹，初始化时由系统自动生成，否则可能会出错。port是开放的端口号，默认就是3306；skip-grant-tables是跳过密码登录
打开管理员模式下的cmd命令窗口，将路径切换到MySQL的bin目录

文章图片

然后输入mysqld install，安装mysql，安装成功后会提示sucess

文章图片

再输入mysqld --initialize-insecure --user=mysql，初始化数据文件，初始化完成后，MySQL目录下会增加一个data文件夹，文件夹内包含了几种初始的数据库
使用net start mysql启动MySQL服务，然后使用命令mysql -u root -p进入MySQL管理界面（-u表示user，-p表示password，其中密码可为空）
修改密码，使用sql命令，这里将密码修改成了123456

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

提示Query OK即为修改成功

文章图片

最后输入flush privileges; 刷新权限，至此配置完成
使用#注释掉my.ini文件的最后一行，此时不再需要跳过密码登录
重启MySQL服务即可正常使用，exit命令退出连接

net stop mysql net start mysql

如果连接成功就可以正常使用了。
3. 可视化工具SQLyog下载及使用
直接在命令窗口使用MySQL数据库不太方便，我们可以借助于可视化工具辅助编辑，工具一般都会包括数据库的可视化、关键字的自动填充和高亮等功能。
常用的可视化工具有SQLyog、Navicat等，这里将会介绍SQLyog的安装和使用
直接搜索SQLyog下载或者进入官网就可以下载安装文件，这里提供了试用版的下载，安装完成后可以试用14天，然后就必须购买了。

文章图片

如果自己的电脑上有腾讯软件管理或者360软件商店，也可以直接下载安装，貌似这里可以试用30天。
个人用户也可以使用SQLyog社区版，这一版是免费的，下载链接：https://github.com/webyog/sqlyog-community 社区版不能使用收费版的一些功能，且会有一些广告，但是学习或者个人使用，已经足够了
下载后直接安装就可以，非常简单。
数据库的连接，一般进入软件后就会提示连接sql界面，当然也可以点击“新连接”

文章图片

连接时，SQL主机地址填入localhost或者127.0.0.1，用户填入root，密码填入自己设置的密码，端口使用3306，点击“连接”就可以连接本地MySQL数据库（连接前要确保打开MySQL服务）

文章图片

连接成功后就可以在询问窗口使用sql命令进行编辑了

文章图片

如SELECT VERSION()可以查询MySQL版本，点击“运行”，就会在结果窗口返回查询结果

文章图片


文章图片

4. MySQL常见命令

--是单行注释，/**/是多行注释 --记忆时推荐使用小写字母记忆，使用时SQLyog自动变成大写字母--查看当前所有的数据库 SHOW DATABASES --打开指定的库 USE 库名 --查看当前库的所有表 SHOW TABLES --查看其它库的所有表 SHOW TABLES FROM 库名 --创建表 CREATE TABLE 表名( 列名 列类型, 列名 列类型, ... 列名 列类型 ) --查看表结构 DESC 表名 --创建数据库 CREATE DATABASE 库名 --删除数据库 DROP DATABASE 库名--其它增删查改命令会在后续介绍

运行时使用鼠标标记运行命令，再点击运行，执行当前语句

文章图片

修改数据库或者表结构后都需要刷新一下数据库

文章图片

5. 数据库的数据类型
5.1 数值

类型	说明	大小
tinyint		1个字节
smallint		2个字节
int	（常用）	4个字节
mediumint		3个字节
bigint		8个字节
float		4个字节
double		8个字节
decimal	字符串形式的浮点数（适用于金融计算）

5.2 字符串

类型	说明	范围
char	固定大小	0~255
varchar	可变字符串（常用）	0~65535
tinytext	微型文本	2 8 ? 1 2^8-1 28?1
text	文本串	2 16 ? 1 2^{16}-1 216?1

5.3 时间日期

类型	形式	说明
date	YYYY-MM-DD	日期
time	hh:mm:ss	时间格式
datetime	YYYY-MM-DD hh:mm:ss	（最常用）
timestamp	时间戳	1970.1.1到现在的毫秒数
year	年份表示	（较常用）

6. 数据库的字段属性

• unsigned，无符号的整数，声明了值不能是负数
• zerofill，0填充，不足的位数使用0填充，如声明int(3)，输入值为5则显示005
• 自增(auto_increment)，自动在上一条的基础上+1(默认)，通常可以用来设计唯一的主键，且要求值是整数类型，当然也可以设置自增的起始值和步长
• 非空，设置为not null，如果不赋值，就会报错！！设置为null，如果不赋值，默认就是null
• 默认(default)，用来设置默认的值，如果不指定该列的值，就会使用默认值

7. 使用SQL命令创建一个表
首先创建一个school数据库，除了使用命令行外，也可以直接在任一数据库点击鼠标右键，选择创建数据库，设置如下

文章图片

在历史记录窗口中可以查看创建数据库的命令

文章图片

再创建一个student表

-- 表的名称和字段尽量使用 `` 括起来(Tab键上面的键) -- 字符串使用英文单引号括起来 -- 除最后一个外，语句后都要加逗号 -- 主键，PRIMARY KEY，一般一个表只有一个主键CREATE TABLE IF NOT EXISTS `student`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(30) 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(100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8--格式 create table [if not exists] `表名`( `字段名` 列类型 [属性] [注释], ... PRIMARY KEY(`字段名`) )[数据库引擎][字符集设置][注释]

运行完成后，school数据库中会增加一个student表

文章图片

关于数据库引擎，默认使用innodb，早些年使用myisam。总结二者区别，myisam节约空间，速度较快；innodb安全性高，支持事务的处理和多表多用户操作。
关于数据库表的字符集编码，如果不设置CHARSET=utf8，MySQL默认使用的是Latin1，不支持中文
8. 表的结构修改

-- 修改表名 ALTER TABLE 旧表名 RENAME 新表名 -- 增加表的字段 ALTER TABLE 表名 ADD 字段名 约束 -- 修改字段约束 ALTER TABLE 表名 MODIFY 字段名 约束 -- 重命名字段名 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 约束 /* 一些资料说： change用来字段重命名，不能修改字段类型和约束 modify不能重命名字段，只能修改字段类型和约束 但是貌似change也可以修改约束 */ -- 删除表的字段 ALTER TABLE 表名 DROP 字段名 -- 删除表 DROP TABLE [IF EXISTS] 表名

9. MySQL数据管理
9.1 外键 比如说student表中存在一个gradeid字段，这个字段和grade表中的gradeid有联系，就可以使用外键，表达一种引用关系

-- 增加外键关系 ALTER TABLE 引用的表名 ADD CONSTRAINT 约束名 FOREIGN KEY (`作为外键的列`) REFERENCES `被引用的表名`(`字段`)

这是物理外键，也就是数据库级别的外键，不建议使用，避免数据库过多造成麻烦（比如有外键关系的表不能随意删除）
实际使用中，数据库只用来存储数据，而使用程序建立外键关系。
9.2 DML语言 DML语言指数据库操作语言（database manipulation language），包含了添加、修改和删除
除此之外还有：

• DDL：数据库定义语言（define）
• DQL：数据库查询语言（query）
• DCL：数据库控制语言（control）

9.3 添加

insert

向上文创建的student表添加数据。

-- 格式：insert into 表名（字段名）values () -- 数据和字段要一一对应 -- values一般另起一行，结构更加清晰 -- values后一个括号代表了添加的一行内容 -- 添加数据时，除了设置了自增的字段，其它设置not null的字段必须添加值 -- 关于字段名的``，尽量带上，不带有时候会出问题INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','abcde','男'),('李四','qwert','女')

运行语句，可以看到表内增加了内容

文章图片

当然也可以不设置字段名，但添加的数据必须和表的字段名一一对应，不能少

INSERT INTO `student` VALUES (3,'王五','abcde','男','2020-01-01','北京','123@qq.com')

表增加的内容：

文章图片

9.4 修改

update

-- 格式：update 表名 set 字段=新值 where 条件 -- 切记一定要加上条件，否则整个表都会替换UPDATE `student` SET `name`='小明' WHERE id=1-- 修改多个字段，使用英文逗号隔开 UPDATE `student` SET `name`='小明',sex='女' WHERE id=1

修改的内容：

文章图片

条件也可以是一个范围

UPDATE `student` SET `name`='小红' WHERE id>=2

文章图片

大于号，小于号，小于等于这种没有必要说明，但是可以说一下不等号

-- <>和!=表示不等号 UPDATE `student` SET `sex`='男' WHERE id<>3

使用between表示范围

-- between ... and ... 表示一个值到另一个值的闭区间UPDATE `student` SET `pwd`='opqrst' WHERE id BETWEEN 1 AND 3

文章图片

多个条件使用and或or

-- and表示‘或且’，or表示‘或者’ UPDATE `student` SET `address`='上海' WHERE `pwd`='opqrst' AND `sex`='男'

文章图片

9.5 删除

delete

-- 格式：delete from 表名 where 条件 -- 如果不加条件就会删除整个表的数据，不建议这么做DELETE FROM `student` WHERE `id`=1

文章图片

TRUNCATE命令

完全清空一个表，但是表的结构和索引约束不会变（也就是还能用）

TRUNCATE TABLE `student`

运行后表的内容会删除

文章图片

delete VS TRUNCATE

delete也会删除表的数据
TRUNCATE的特点：

• 删除表后，会重新设置自增列，计数器归零。而delete不会改变计数器
• TRUNCATE不会影响事务（事务后续介绍）

关于delete，使用不同的引擎也会有不同的效果

• innodb，重启数据库，自增列会从1开始（存在内存中）
• myisam，继续从上一个增量开始（存在于文件中）

9.6 小结

-- 添加 insert into 表名（字段名）values () -- 修改 update 表名 set 字段=新值 where 条件 -- 删除 delete from 表名 where 条件

10. DQL查询数据
DQL：data query language 数据查询语言
数据库中最核心、最重要的语言，使用频率最高
10.1 创建测试数据 为了测试查询语句，需要做一些数据，数据库仍然使用school，只不过我把原来的表都清空了。
创建两个表（学生表和年级表）

-- 如果存在就删除 DROP TABLE IF EXISTS `student`-- 创建学生表 CREATE TABLE IF NOT EXISTS `student`(`studentNo` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `studentName` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `gradeId` INT(10) NOT NULL COMMENT '年级编号', `pwd` 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(`studentNo`) )ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建年级表 CREATE TABLE IF NOT EXISTS `grade`(`gradeId` INT(10) NOT NULL COMMENT '年级编号', `gradeName` VARCHAR(10) NOT NULL DEFAULT '大一' COMMENT '年级', PRIMARY KEY(`gradeId`) )ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建课程表 CREATE TABLE IF NOT EXISTS `subject`(`subjectId` INT(10) NOT NULL COMMENT '课程编号', `subjectName` VARCHAR(10) NOT NULL DEFAULT '大学物理' COMMENT '课程名', PRIMARY KEY(`subjectId`) )ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建成绩表 CREATE TABLE IF NOT EXISTS `result`(`subjectId` INT(10) NOT NULL COMMENT '课程编号', `subjectName` VARCHAR(10) NOT NULL DEFAULT '大学物理' COMMENT '课程名', `gradeId` INT(10) NOT NULL COMMENT '年级编号', `studentNo` INT(4) NOT NULL COMMENT '学号', `studentName` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `score` INT(10) NOT NULL DEFAULT 0 COMMENT '分数', KEY `subjectId`(`subjectId`) )ENGINE=INNODB DEFAULT CHARSET=utf8

向学生表中插入数据

INSERT INTO `student` VALUES (101,'张三',1,'abcde','男','2020-01-01','北京','123@qq.com'),(102,'李四',1,'78996','女','2016-01-01','北京','1789@qq.com'), (201,'王五',2,'cde','男','2018-01-01','天津','741@qq.com'),(202,'小明',2,'456','女','2018-07-01','大连','741@163.com'),(305,'小张',3,'15963','女','2018-09-30','青岛','qwer@163.com'),(307,'小红',3,'156','女','2018-07-01','廊坊','edf@163.com'), (409,'小王',4,'456','男','2018-12-01','保定','741@163.com')

向年级表中插入数据

INSERT INTO `grade` VALUES (1,'大一'),(2, '大二'),(3, '大三'),(4,'大四')

向课程表中插入数据

INSERT INTO `subject` VALUES (200,'大学物理'),(201, '高等数学'),(202, '离散数学'),(203,'大学英语'),(204,'操作系统')

向分数表中插入数据

INSERT INTO `result` VALUES (200,'大学物理',1,101,'张三',90),(201, '高等数学',1,101,'张三',90),(202, '离散数学',1,102,'李四',80),(203,'大学英语',1,102,'李四',90),(204,'操作系统',1,102,'李四',90)

10.2 简单查询

-- 格式 SELECT 字段 FROM 表名-- 查询所有学生信息 SELECT * FROM student -- 查询所有年级信息 SELECT * FROM grade -- 查询指定字段 SELECT `studentNo`,`studentName` FROM `student`

文章图片

别名

-- 使用别名（as） -- 别名不需要使用单引号 -- 也可以给表取别名 SELECT `studentNo` AS 学号,`studentName` AS 姓名 FROM `student`-- 也可以省略as SELECT `studentNo` 学号,`studentName` 姓名 FROM `student`

文章图片

concat函数

-- 函数 concat(a,b) SELECT CONCAT ('姓名：',studentName) AS 新名字 FROM `student`

文章图片

去重

查询全部成绩

SELECT * FROM result

文章图片

查询哪些学生参加了考试

SELECT `studentName` FROM result

文章图片

查询结果出现很多重复数据，因此我们可以使用去重操作(distinct)

SELECT DISTINCT `studentName` FROM result

文章图片

其它

函数

SELECT VERSION() --查询mysql版本

表达式：select还可以直接参与计算

SELECT 123*10 AS 计算结果

文章图片

因此可以使用这个特性批量修改数据，比如给每个人的成绩+1分

SELECT `studentName`,`score`+1 AS 新成绩 FROM `result`

文章图片

变量：查询自增步长

SELECT @@auto_increment_increment

10.3 where子句 前面很多地方都用到了where，where子句表示一种条件关系，一般连接多个表达式，返回一个布尔值，下面介绍一下有关where的一些运算符

逻辑运算符

逻辑运算符包含：与、或、非
与：and 和&&，a and b，a && b ，两者都为真，返回结果才为真
或：or和||，a or b，a || b，其中一个为真，结果就为真
非：Not和!，Not a，！a，若a为真，则非a为假；若a为假，则非a为真

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果a为空，结果为真
is not null	a is not null	如果a不为空，结果为真
between … and …	a between b and c	a在b和c之间，则结果为真
like	a like b	匹配，a去匹配b
in	a in (a1,a2,a3…)	如果a是a1,a2,a3…的一个值，则结果为真

这里着重介绍一下like，like通常与%和_一起使用，%表示任意多个字符，_表示一个任意字符
比如查询所有学生的学号和姓名

SELECT `studentNo`,`studentName` FROM `student`

文章图片

现在想要查询姓名以“小”为开头的学生

SELECT `studentNo`,`studentName` FROM `student` WHERE `studentName` LIKE '小%'

文章图片

当然这里也可以使用小_，因为名字都是两个字，但数据中包含三个字以上的。使用一个%也可以查出，但使用_就必须匹配字数了。这里以查询学号为例。

-- 查询数字2开头的可以使用2% SELECT `studentNo`,`studentName` FROM `student` WHERE `studentNo` LIKE '2%'

文章图片

-- 但如果使用_表达相同结果，就需要两个 SELECT `studentNo`,`studentName` FROM `student` WHERE `studentNo` LIKE '2__'-- 否则会返回无结果 SELECT `studentNo`,`studentName` FROM `student` WHERE `studentNo` LIKE '2_'

文章图片

in的用法

-- 查询地址是北京或天津的学生SELECT `studentNo`,`studentName`,`address` FROM `student` WHERE `address` IN ('北京','天津')

文章图片

10.4 联表查询

join…on…

假设想要查询参加考试的学生的学号、姓名、性别，考试科目和考试成绩，但是只查询一张表无法完成这个操作，这个时候就用到了联表查询
实际上联表查询包含7种方式，感兴趣的可以搜索“7种联表查询”，理解上也比较方便，就是对两个表的数据进行不同的取舍
这里只介绍其中的三种
inner join 取两个表的交集

-- 注意！！对于两表共有的内容，需要指定查询那一个表，如`student`.`studentNo`SELECT `student`.`studentNo`,`student`.`studentName`,`subjectName`,`sex`,`score` FROM `student` INNER JOIN `result` ON `student`.`studentNo`=`result`.`studentNo`

on后面跟多表连接所遵循的规则

文章图片

如果使用left join，就会根据左表的信息进行查询，在右表中没有的数据就会设为null

SELECT `student`.`studentNo`,`student`.`studentName`,`subjectName`,`sex`,`score` FROM `student` LEFT JOIN `result` ON `student`.`studentNo`=`result`.`studentNo`

文章图片

right join原理与left join类似，这里不再展示
也可以结合where子句，对查询结果做一个筛选，比如查询缺考的学生

SELECT `student`.`studentNo`,`student`.`studentName`,`subjectName`,`sex`,`score` FROM `student` LEFT JOIN `result` ON `student`.`studentNo`=`result`.`studentNo` WHERE `score` IS NULL

文章图片

可以连接多个表

select ... from ... ...join ... on ... ...join ... on ... ...

自连接

顾名思义，自连接就是自己和自己进行连接，把一张表看成两个表。当一张表格中出现层级结构时，（也可以理解为树状结构）就可以使用自连接。比如数码产品这个类别下面可以有手机、照相机、平板、电脑，手机下面也可以有各个品牌。自连接依赖于父类和子类之间的从属关系。
创建一个新表

CREATE TABLE `category` ( `categoryId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id', `parentId` INT(10) NOT NULL COMMENT '父id', `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字', PRIMARY KEY (`categoryId`) )ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

添加数据

INSERT INTO `category` VALUES(2,1,'信息技术'), (3,1,'软件开发'), (4,3,'数据库'), (5,1,'美术'), (6,3,'web开发'), (7,5,'PS技术'), (8,2,'办公信息')

之间的从属关系如下：

文章图片

如果进行自连接，就需要子类的父id等于类别id

SELECT p.`categoryName` AS 父类 ,s.`categoryName` AS 子类 FROM `category` AS p INNER JOIN `category` AS s ON p.`categoryId` = s.`parentId`

文章图片

10.5 排序

order by

排序一般是两种方式，升序：ASC，降序：DESC
比如查询成绩，如果数据量庞大，效果更显著

SELECT `studentNo`,`studentName`,`subjectName`,`score` FROM `result` ORDER BY `score` DESC

文章图片

10.6 分页

limit

适用于数据量足够大，进行分批显示，应用中结合前端工具，效果非常好
limit后接两个参数，第一个参数表示查询起始下标，第二个表示size

SELECT `studentNo`,`studentName`,`subjectName`,`score` FROM `result` LIMIT 0,3

文章图片

SELECT `studentNo`,`studentName`,`subjectName`,`score` FROM `result` LIMIT 3,3-- 这里的两个参数可以任意设置

文章图片

如果要实现类似于网页中分页的效果，定义当前页为第n页，第一个参数设置为(n-1)*size
10.7 子查询 子查询也是涉及到多个表之间的查询，子查询的特点是在where子句中又嵌套了一个查询
成绩表的所有数据如下：

文章图片

实际上，本表的subjectName不该有（失误），否则subject表里的内容都被包括了，我们需要删除这一列（复习一下表的结构修改）

ALTER TABLE `result` DROP `subjectName`

修改后的表结构：

文章图片

现在，我们希望查询考了大学英语的学生学号、姓名和分数，执行子查询

SELECT `studentNo`,`studentName`,`score` FROM `result` WHERE `subjectId`=( SELECT `subjectId` FROM `subject` WHERE `subjectName`='大学英语' )

查询结果：

文章图片

子查询的逻辑结构也比较简单，先在subject表里查询大学英语对应的id，再根据id在result表中查询出其它信息
10.8 分组

group by

现在给成绩表增加一些数据

INSERT INTO `result` VALUES (200,1,102,'李四',60),(200,2,201,'王五',75),(200,2,202,'小明',70), (201,1,101,'李四',70),(201,2,201,'王五',75),(201,3,305,'小张',70),(201,3,307,'小红',78),(201,4,409,'小王',88), (202, 1,101,'张三',86),(202,3,305,'小张',70),(202,3,307,'小红',78), (203,1,101,'张三',90),(203,2,201,'王五',75),(203,3,305,'小张',70),(203,3,307,'小红',78), (204,1,101,'张三',70),(204,2,201,'王五',75),(204,3,305,'小张',90)

可以看到增加了很多内容

文章图片

现在想要查询每个科目的平均分，就使用到了分组

SELECT r.`subjectId`,`subjectName`,AVG(`score`) 平均值 FROM `result` r INNER JOIN `subject` sub ON r.subjectId=sub.subjectId GROUP BY(r.subjectId)

文章图片

现在增加一个条件，查询平均分大于80的科目，如果使用where语句（分组后的筛选不能使用where，现在试试出现什么结果）

SELECT r.`subjectId`,`subjectName`,AVG(`score`) 平均值 FROM `result` r INNER JOIN `subject` sub ON r.subjectId=sub.subjectId WHERE AVG(`score`) >=80 GROUP BY(r.subjectId)

运行后直接报错

文章图片

正确的应该是使用having

SELECT r.`subjectId`,`subjectName`,AVG(`score`) 平均值 FROM `result` r INNER JOIN `subject` sub ON r.subjectId=sub.subjectId GROUP BY(r.subjectId) HAVING AVG(`score`) >=80

文章图片

10.9 小结 select查询有一个很重要的顺序：

select ... from ... [left | right | inner join ...] [where ...] [group by ...] [having ...] [order by ...] [limit ...]

写比较复杂的查询时，可选的部分顺序不能颠倒，否则会报错
11. MySQL函数
MySQL所有的函数可以参考官网文档
11.1 常用函数

数学运算

ABS：绝对值

SELECT ABS(-10)

文章图片

CEILING：向上取整
FLOOR：向下取整
RAND：随机数

字符串函数

CHAR_LENGTH：字符串长度

SELECT CHAR_LENGTH('数据库')

文章图片

CONCAT：拼接字符串

SELECT CONCAT('数','据','库')

文章图片

LOWER：转小写

SELECT LOWER('MYSQL')

文章图片

UPPER：转大写

时间函数

CURRENT_DATE：获取当前日期
CURDATE：获取当前日期（与上个函数功能相同）
NOW：获取当前时间（与前两者相比增加了时分秒）
也可以拆分时间

SELECT YEAR(NOW())--拆分出年 SELECT MONTH(NOW()) --拆分出月 SELECT DAY(NOW())--拆分出日 SELECT HOUR(NOW())--时 SELECT MINUTE(NOW())--分 SELECT SECOND(NOW())--秒

系统

SELECT USER()--用户信息 SELECT VERSION() --版本

11.2 聚合函数 count：计数
sum：求和
avg：平均值
max：最大值
min：最小值

SELECT COUNT(`studentNo`) FROM `student`--查询有多少位学生，会忽略null值 SELECT COUNT(*) FROM `student`-- 不会忽略null值 SELECT COUNT(1) FROM `student`-- 查询行数，不忽略null值

11.3 MD5加密（拓展） MD5码加密过程不可逆
创建一个新表

CREATE TABLE `test` ( `id` INT(10) NOT NULL, `name` VARCHAR(20) NOT NULL, `pwd` VARCHAR(50) NOT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8

插入数据

INSERT INTO `test` VALUES (1,'王宝','123456'),(2,'徐朗','45678'),(3,'高博','789456')

文章图片

转化为md5码

UPDATE `test` SET pwd=MD5(pwd)

文章图片

添加数据的时候也可以直接插入MD5码

INSERT INTO `test` VALUES (4,'冰冰',MD5('123456'))

文章图片

通过上图可以看出，使用同样的密码123456，加密后的结果也是相同的
如何校验？将用户传过来的值使用MD5加密，比对加密后的值

SELECT * FROM `test` WHERE `name`='王宝' AND `pwd`=MD5('123456')

文章图片

12. 事务
事务都遵循的原则：ACID原则
A代表事务的原子性(Atomicity)，指一个事务要么全部执行，要么不执行，也就是说一个事务不可能只执行了一半就停止了。经典的例子：取钱，一方面银行卡扣掉100的金额，就会给你100的现金，不可能只扣钱不给现金。
C代表事务一致性(Consistency)，指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。
I代表独立性(Isolation），事务的独立性也称作隔离性，是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致，更加具体的来讲，就是事务之间的操作是独立的。
D代表持久性(Durability）：事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。
MySQL自动开启了事务的自动提交

SET autocommit = 0 -- 关闭自动提交 SET autocommit = 1 -- 开启自动提交（默认的）/*手动处理事务的过程*/ -- 1. 关闭自动提交 SET autocommit = 0 -- 2. 开启事务 START TRANSACTION -- 3. 提交（持久化） COMMIT -- 4. 回滚 ROLLBACK

模拟使用场景，创建一个新的数据库

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

创建一个表

CREATE TABLE `account` ( `id` INT(5) NOT NULL AUTO_INCREMENT, `name` VARCHAR(10) 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',1000.00)

文章图片

模拟转账

-- 模拟转账 SET autocommit = 0 -- 关闭自动提交 START TRANSACTION-- 开启事务 UPDATE `account` SET `money`=`money`-1000 WHERE `name`='A' UPDATE `account` SET `money`=`money`+1000 WHERE `name`='B'

执行完更新操作，以下是新的数据

文章图片

如果执行回滚操作，那么就重新变回原来的数据

ROLLBACK -- 回滚

文章图片

如果执行完提交操作后，即使再执行回滚，也不会恢复数据

COMMIT -- 提交事务

测试完成后需要开启自动提交

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

13. 索引
索引是帮助MySQL高效获取数据的数据结构，详细的介绍（包括数据结构和算法原理）见大佬博客
13.1 索引的分类

• 主键索引：PRIMARY KEY，主键不可重复，只能有一个列作为主键
• 唯一索引：UNIQUE KEY，避免重复的列出现
• 常规索引：KEY
• 全文索引：FULLTEXT ，快速定位数据

13.2 测试索引 创建一个user表

CREATE TABLE `user` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL, `phone` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8

插入一百万条数据

-- 插入100万数据. DELIMITER $$-- 写函数之前必须要写，标志 CREATE FUNCTION create_data () RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i

现在先要查找一条比较靠后的数据

SELECT * FROM `user` WHERE `name`='用户999999'

测试用时0.059秒，还算可以
现在给name列加入索引

CREATE INDEX user_name ON `user`(`name`)

测试用时只有0.003秒，少用了非常多的时间
13.3 索引原则

1. 索引不是越多越好
2. 小数据量不需要建索引
3. 经常变动的数据不要加索引
4. 索引加在常用来查询的字段上

14 JDBC
本章介绍java程序与MySQL数据库的连接
14.1 JDBC使用示例 程序通过数据库驱动才能和数据库进行数据的交互，SUN公司提供了Java操作数据库的规范，就是jdbc，开发人员学习jdbc接口即可，不同的数据库厂商开发各自的数据库驱动
首先需要下载一个jar包：mysql-connector-java，不同版本的MySQL对应了不同的版本，我使用了一个5.1.49版本的

文章图片

首先把mysql-connector-java-5.1.49.jar导入项目中

文章图片

然后添加到项目的库中

文章图片

接下来写连接数据库的代码，先贴出所有代码

public class JdbcFirstDemo {public static void main(String[] args) throws ClassNotFoundException, SQLException { //1. 加载驱动 Class.forName("com.mysql.jdbc.Driver"); //固定写法//2. 用户信息和url String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false"; String username="root"; String password="123456"; //3. 连接成功，获取数据库对象 Connection connection = DriverManager.getConnection(url, username, password); //4. 获取执行sql的对象 Statement statement = connection.createStatement(); //5. 执行sql String sql="select * from student"; ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ System.out.println("id="+resultSet.getObject("studentNo")); System.out.println("name="+resultSet.getObject("studentName")); System.out.println("sex="+resultSet.getObject("sex")); System.out.println("==================="); }//6. 释放连接 resultSet.close(); statement.close(); connection.close(); } }

具体的有几个步骤：

加载驱动

Class.forName("com.mysql.jdbc.Driver");

这个是固定的写法

设置用户信息和url

用户信息这里不再介绍了，就是用户名和密码
url是连接数据库的依据

jdbc:mysql://主机:接口/连接的数据库名?其它设置-- 设置useUnicode=true就可以在数据库中使用中文 -- characterEncoding=utf8 字符类型设置为utf8 -- useSSL=false 一般开发中不需要使用SSL连接，可以使用false禁用，如果需要使用，则设置为true（但我设置为true报了错，这里不太懂）

获取数据库对象

使用管理者DriverManager，通过url、用户名和密码就可以连接数据库，获取数据库对象

获取执行sql命令的对象

connection.createStatement()

执行sql

执行``操作时，会返回查询的结果集，我们可以对它做拆分。而增、删、改只返回影响的行数。
注意做不同操作的区别：

statement.executeQuery--执行查询 statement.executeUpdate --执行增、删、改都用这个 statement.execute()--不指定操作，让它自己判断

另外对于查询的结果集的解析

resultSet.getObject(); --不指定文件类型 -- 指定解析类型会更精准 resultSet.getInt(); --指定解析int型 resultSet.getFloat(); --指定解析float型 resultSet.getDouble(); --指定解析double型 ...

释放连接

很重要，因为连接占用了大量内存
14.2 封装为类 通过示例可以看出，对于每次连接数据库，大部分代码是相同的，因此我们可以将有用的部分封装为工具类，我将其命名为TestJdbc

public class TestJdbc { private static String driver = "com.mysql.jdbc.Driver"; private static String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false"; private static String username = "root"; private static String password = "123456"; static { try { //1.加载驱动 Class.forName(driver); } catch (Exception e) { e.printStackTrace(); }}// 2.获取连接 public static Connection getConnextion() throws SQLException { //获取 return DriverManager.getConnection(url, username, password); }// 3. 释放资源 public static void release(Connection conn, Statement st, ResultSet res) { if (res!=null) { try { res.close(); } catch (SQLException e) { e.printStackTrace(); } } if (st!=null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

测试插入数据

public class Test {public static void main(String[] args) {Connection conn =null; Statement st = null; ResultSet res = null; try { conn = TestJdbc.getConnextion(); //获取连接 st = conn.createStatement(); String sql = "INSERT INTO student(`studentNo`,`studentName`,`gradeId`,`pwd`,`sex`)" + "VALUES('208','王宝',2,'123456','男')"; int i = st.executeUpdate(sql); if (i>0) { System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { TestJdbc.release(conn,st,res); } } }

执行显示“插入成功”

文章图片

也可以使用配置文件properties，再读取数据
db.properties文件

driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false username=root password=123456

解析db.properties文件

InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); //读取配置文件返回数据流 Properties properties = new Properties(); properties.load(in); //读取数据流driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password");

其它都与上面介绍的相似
14.3 SQL注入 什么是sql注入，我们可以看百度百科的解释：
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

代码演示

比如我们模拟系统登录，本质上是数据库的查询

SELECT studentNo,studentName,pwd FROM student WHERE studentNo='208' AND pwd='123456'

当我们使用正确的查询语句进行查询，返回正确的结果

文章图片

但我们把admin和1’or’1作为输入

SELECT studentNo,studentName,pwd FROM student WHERE studentNo='admin' AND pwd='1' OR '1'

【MySQL安装及使用笔记】此时返回了所有人的信息

文章图片

由于SQL语句中逻辑运算符具有优先级，=优先于and，and优先于or，且适用传递性。因此，此SQL语句在后台解析时，分成两句：

SELECT studentNo,studentName,pwd FROM student WHERE studentNo='admin' AND pwd='1''1'

前为假，后为真，根据or运算符，则整体为真

PreparedStatement对象

上文介绍的执行sql的对象是Statement，而PreparedStatement可以有效地防止SQL注入，附上二者的比较
在使用上，二者略有区别（还是以上文的插入为例）

public class TestInsert02 { public static void main(String[] args) {Connection conn =null; PreparedStatement st = null; ResultSet res = null; try { conn = jdbcUtils.getConnextion(); //获取连接//使用 ? 代替参数 String sql = "INSERT INTO student(`studentNo`,`studentName`,`gradeId`,`pwd`,`sex`) VALUES(?,?,?,?,?)"; st = conn.prepareStatement(sql); //预编译，不执行//手动给参数赋值 st.setString(1,"208"); st.setString(2,"王宝"); st.setInt(3,2); st.setString(4,"123456"); st.setString(5,"男"); int i = st.executeUpdate(); //这里也与原来的不同 if (i>0) { System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { jdbcUtils.release(conn,st,res); } }

使用PreparedStatement需要先写sql语句，再进行预编译，然后手动赋值，最后执行

推荐阅读

• 

  五味
• 

  激光能去除肥胖纹吗视频 激光能去除肥胖纹吗
• 

  原味烤肉制作方法 原味烤肉的制作方法
• 

  房间里面有老鼠怎么办
• 

  有没有好点收益高的副业项目？
• 

  年收入是指纯收入，年收入是什么意思
• 

  松下洗衣机烘干为什么出水,首先原因要搞清
• 

  读地图分析,如何读地图获取信息
• 

  3d|2千档高质价比手机推荐：骁龙870+三星屏+65W，真我真牛
• 

  刑事自诉案件的范围是什么
• 

  生理盐水洗鼻子可以预防新冠肺炎吗
• 

  胚芽燕麦粥
• 

  辰辉的寓意
• 

  健康工作远离疼痛现在要坐好
• 

  被子晒多久比较好
• 

  头孢料酒会有反应吗
• 

  鹌鹑鸟怎么养 鹌鹑鸟如何饲养
• 

  人成长分为哪些阶段
• 

  如何鉴别建兰国魂真假 如何鉴别建兰国魂真假
• 

  回归模型结果分析方法,ABC态度模型分析方法和运用

• 数据库|SQL行转列方式优化查询性能实践
• mysql|一文深入理解mysql
• 达梦数据库|DM8表空间备份恢复
• 数据技术|一文了解Gauss数据库（开发历程、OLTP&OLAP特点、行式&列式存储，及与Oracle和AWS对比）
• SqlServer|sql server的UPDLOCK、HOLDLOCK试验
• 谈灾难恢复指标（RTO与RPO是什么鬼（））
• RPO与RTO
• 数据库|效率最高的Excel数据导入---（c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】）...