学习视频来自B站，感谢狂神的分享：B站视频地址
参考博客：狂神说MySQL01：初识MySQL
1. 初识 MySQL
基本概念
数据库 : 按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
数据库管理系统（DBMS）：( DataBase Management System )为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
关系型数据库（SQL）：关系型数据库，存储的格式可以直观地反映实体间的关系。大多数都遵循SQL（结构化查询语言，Structured Query Language）标准。 常见的操作有查询，新增，更新，删除，求和，排序等。
常见的有：MySQL , Oracle , SQL Server
非关系型数据库（NoSQL）：指的是分布式的、非关系型的、不保证遵循ACID原则的数据存储系统。
常见的有：MongoDB、Redis、Memcache
mysql安装
MySQL官网：https://www.mysql.com/
MySQL 下载地址：https://dev.mysql.com/downloads/installer/
MySQL 压缩版下载地址：https://dev.mysql.com/downloads/mysql/
- 下载之后，解压到安装位置，在根目录创建my.ini文件
- my.ini文件的内容，如下所示（my.ini 记事本打开，保存格式为ANSI）：
[mysqld]
#设置mysql验证格式
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
# 设置端口
port=3306
# 设置mysql的安装目录（---注意替换成自己的路径---）
basedir=D:\mysql\mysql-8.0.28-winx64
# 设置 mysql数据库存放目录（---注意替换成自己的路径---）
datadir=D:\mysql\mysql-8.0.28-winx64\\data
# 允许最大连接数
max_connections=10
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置 mysql 客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端默认端口与字符集
port=3306
default-character-set=utf8
-
添加环境变量：我的电脑–>属性–>高级系统设置–>环境变量
将mysql的bin目录，添加到path变量下 -
以管理员身份打开CMD窗口，将路径切换到 \mysql-8.0.29\bin 目录下
-
安装mysql服务：（不传服务名，默认为 mysql）
mysqld -install [服务名] -
初始化数据库，指令：
mysqld --initialize-insecure --user=mysql -
启动mysql： net start mysql (mysql是服务名，在第5步指定)
-
修改密码：
- 进入mysql管理界面修改密码（第一次输入密码回车即可）
- 输入以下第一条命令进入管理界面（p后面没有空格！！）
- 在输入第二条命令修改密码（最后一定要有分号）
mysql -u root -p #让你输入密码时直接回车即可
ALTER USER 'root'@'localhost' IDENTIFIED BY '你的密码';
# 如果安装两个mysql，cmd命令连接时，可指定端口号
# mysql -P 端口号 -h mysql主机名ip -u root （用户） -p password (密码)
- 重启mysql，即可正常使用
#退出mysql管理界面
exit
#停止mysql服务
net stop mysql
#开始mysql服务
net start mysql
缺少 140.dll：微软常用运行库合集
参考博客：
https://www.jb51.net/article/193127.htm
https://blog.csdn.net/Fioman_GYM/article/details/120970318
连接数据库
打开MySQL命令窗口
- 在DOS命令行窗口进入 安装目录\mysql\bin
- 可设置环境变量，设置了环境变量，可以在任意目录打开！
连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !
几个基本的数据库操作命令 :
update user set password=password('123456')where user='root'; 修改密码
flush privileges; 刷新数据库权限
show databases; 显示所有数据库
use dbName; 打开某个数据库
show tables; 显示某个数据库中，所有的表
describe user; 显示user表的列信息
create database name; 创建数据库
exit; 退出Mysql
? 命令关键词 : 寻求帮助
-- 单行注释
/*多行注释*/
2. 操作数据库
MySQL数据库关键字，不区分大小写
2.1 结构化查询语句（SQL）分类
| 名称 | 解释 | 命令 |
|---|---|---|
| DDL （数据定义语言） | 定义和管理数据对象，如数据库，数据表等 | CREATE、DROP、ALTER |
| DML （数据操作语言） | 用于操作数据库对象中所包含的数据 | INSERT、UPDATE、DELETE |
| DQL（数据查询语言） | 用于查询数据库数据 | SELECT |
| DCL （数据控制语言） | 用于管理数据库的语言，包括管理权限及数据更改 | GRANT、commit、 rollback |
命令行操作数据库
- 创建数据库 : create database [if not exists] 数据库名;
- 删除数据库 : drop database [if exists] 数据库名;
- 查看数据库 : show databases;
- 使用数据库 : use 数据库名;
2.2 数据类型
数值类型
字符串类型
日期类型
NULL值
- 理解为 “没有值” 或 “未知值”
- 不要用NULL进行算术运算 , 结果仍为NULL
2.3数据字段属性
UnSigned
- 无符号的
- 声明该数据列不允许负数 .
ZEROFILL
- 0填充的
- 不足位数的用0来填充 , 如int(3)，5则为005
Auto_InCrement
- 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
- 通常用于设置主键 , 且为整数类型
- 可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL
- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
DEFAULT
- 用于设置默认值
- 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
-- 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
-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;
-- 设置严格检查模式(不能容错了)
SET sql_mode='STRICT_TRANS_TABLES';
2.4 表的类型
-- ENGINE 数据库引擎，在表定义的最后指定：
CREATE TABLE 表名(
-- 省略创建表的代码
)ENGINE = MyISAM (or InnoDB)
-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;
MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等
2.5数据的存储
- MySQL 的表类型由存储引擎（Storage Engines）决定，主要类型 ：
- 事务安全型（transaction-safe）：BDB、InnoBDB
- 非事务安全型（non-transaction-safe）：HEAP（Memory）、Archive、MYISAM
查看mysql 支持的引擎：
show engines;
- MySQL数据表以文件方式存放在磁盘中
- 包括表文件 , 数据文件 , 以及数据库的选项文件
- 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
- 注意
- * . frm – 表结构定义文件
- * . ibd – 数据文件
- * . MYD – 数据文件 ( data )
- * . MYI – 索引文件 ( index )
InnoDB类型数据表：对应 *.frm 、*.ibd 文件
MyISAM类型数据表：对应 * . frm、* . MYD、* . MYI 三个文件 :
设置数据表字符集
我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :
- 创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
- 如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定
2.6 修改数据库
修改表 ( ALTER TABLE )
修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]
修改字段 :
-- MODIFY 修改字段属性、CHANGE 修改字段名
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名
删除表
语法：DROP TABLE [IF EXISTS] 表名
3. MySQL数据管理
3.1 外键
外键概念
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
外键作用
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。
创建外键
建表时指定外键约束
-- 创建外键的方式一 : 创建子表同时创建外键
-- 年级表 (id\年级名称)
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 `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
-- ------ 创建外键，并设置关联关系： ------
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
建表后创建：
-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
注意 : 删除具有主外键关系的表时 , 要先删子表 , 后删主表
3.2 DML语言（重要）
数据库意义 ： 数据存储、数据管理
管理数据库数据方法：
- 通过SQLyog等管理工具管理数据库数据
- 通过DML语句管理数据库数据
DML语言 ：数据操作语言
- 用于操作数据库对象中所包含的数据
- INSERT (添加数据语句)
- UPDATE (更新数据语句)
- DELETE (删除数据语句)
3.3 添加
添加数据： INSERT命令
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
注意 :
- 字段或值之间用英文逗号隔开 .
- ’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
- 可同时插入多条数据 , values 后用英文逗号隔开 . VALUES( ),( )
-- 使用语句如何增加数据?
-- 语法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
INSERT INTO `grade`(`gradename`) VALUES ('大一');
-- 主键自增,那能否省略呢?
INSERT INTO `grade` VALUES ('大二');
-- 结果:INSERT INTO grade VALUE ('大二')错误代码：1136
-- Column count doesn`t match value count at row 1;
-- vvv结论:'字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.
-- 一次插入多条数据
INSERT INTO `grade`(`gradename`) VALUES ('大三'),('大四');
3.4 修改
UPDATE命令
-- column_name 为要更改的数据列
-- value 为修改后的数据 , 可以为变量 , 具体值, 表达式或者嵌套的SELECT结果
-- condition 为筛选条件 , 如不指定则修改该表的所有列数据
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
where条件语句：有条件地从表中筛选数据
| 运算符 | 含义 | 举例 | 结果 |
|---|---|---|---|
| = | 等于 | 6=6 | true |
| <> 或 != | 不等于 | 5!=6 | true |
| > | 大于 | 5>6 | false |
| < | 小于 | 5<6 | true |
| >= | 大于等于 | 5>=6 | false |
| <= | 小于等于 | 5<=6 | true |
| BETWEEN | 在某个范围之间 | 6 BETWEEN 5 AND 10 | true |
| AND | 并且 | 5 > 1 AND 1 > 2 | false |
| OR | 或 | 5 > 1 OR 1 > 2 | true |
3.5 删除
delete命令
-- condition为筛选条件 , 如不指定则删除该表的所有列数据
DELETE FROM 表名 [WHERE condition]
TRUNCATE命令：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;
TRUNCATE [TABLE] table_name;
-- 清空年级表
TRUNCATE grade
TRUNCATE 与 DELETE：
- 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
- 不同 :
- 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
- 使用TRUNCATE TABLE不会对事务有影响
-- 创建一个测试表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入几个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
-- delete (不带where条件的delete)
DELETE FROM test;
-- 结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.
-- truncate
TRUNCATE TABLE test;
-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.
-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)
4. 使用DQL查询数据
4.1 DQL 语言
DQL( Data Query Language 数据查询语言 )
-- 注意 : [ ] 括号代表可选的 , { }括号代表必选得
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}];
-- 指定查询的记录从哪条至哪条
4.2 指定查询字段
-- 查询表中所有的数据列结果 , 采用 **" \* "** 符号; 但是效率低，不推荐 .
-- 查询所有学生信息
SELECT * FROM student;
-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;
as 关键字
- 可给数据列取一个新别名
- 可给表取一个新别名
- 可把经计算或总结的结果用另一个新名称来代替
-- 这里是为列取别名(当然as关键词可以省略)
SELECT student_no AS 学号,studentname AS 姓名 FROM student;
-- 使用as也可以为表取别名
SELECT student_no AS 学号,studentname AS 姓名 FROM student AS s;
-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',student_name) AS 新姓名 FROM student;
DISTINCT关键字的使用
作用：去掉SELECT查询返回的记录中重复的记录( 返回所有列的值都相同 ) , 只返回一条
-- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result;
-- 查看考试成绩
SELECT studentno FROM result;
-- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result;
-- 了解:DISTINCT 去除重复项 , (默认是ALL)
使用表达式的列
数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成
应用场景 :
- SELECT 语句返回结果列中使用
- SELECT 语句中的ORDER BY , HAVING等子句中使用
- DML语句中的 where 条件语句中使用表达式
-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式
-- 学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;
4。3 where条件语句
作用：用于检索数据表中 符合条件 的记录
搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假
逻辑操作符
| 操作符名称 | 语法 | 描述 |
|---|---|---|
| AND 或 && | a AND b 或 a && b | 逻辑与，同时为真结果才为真 |
| OR 或 || | a or b 或 a||b | 逻辑或，只要一个为真，则结果为真 |
| NOT 或 ！ | NOT a 或 ！a | 逻辑非，若操作数为假，则结果为真！ |
-- 查询考试成绩在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… 中的某一个，则结果为真 |
- 数值类型的记录之间，才能进行算术运算 ;
- 相同数据类型的数据之间，才能进行比较 ;
-- 模糊查询 between and \ like \ in \ null --
-- ---------------- LIKE ----------------
-- 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 '刘__';
-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':' --
-- ---------------- IN ----------------
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');
-- ---------------- NULL 空 ----------------
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的。 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;
4.4 连接查询
join 对比
| 操作符名称 | 描述 |
|---|---|
| INNER JOIN | 如果表中有至少一个匹配，则返回行 |
| LEFT JOIN | 即使右表中没有匹配，也从左表中返回所有的行 |
| RIGHT JOIN | 即使左表中没有匹配，也从右表中返回所有的行 |
查询原理：MySQL内部采用了一种叫做 nested loop join（嵌套循环连接）的算法
Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
/*连接查询
如需要对多张表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join
(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接 right join
(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
=================================================
注意：如果从表有多个数据与主表匹配，则主表返回多行重复数据
=================================================
等值连接和非等值连接
自连接 *
*/
SELECT * FROM student;
SELECT * FROM result;
-- --------- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数) ---------
/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接) */
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r ON r.studentno = s.studentno
-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r ON r.studentno = s.studentno
-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r ON r.studentno = s.studentno
-- ---------- 查一下缺考的同学(左连接应用场景) ----------
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r ON r.studentno = s.studentno
INNER JOIN `subject` sub ON sub.subjectno = r.subjectno
自连接
/*自连接数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中 查询父栏目名称和其他子栏目名称 */
-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` 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` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'), ('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'), ('6','3','web开发'),
('7','5','ps技术'), ('8','2','办公信息');
-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b WHERE a.`categoryid`=b.`pid`
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r ON r.studentno = s.studentno
INNER JOIN `subject` sub ON sub.subjectno = r.subjectno
-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s INNER JOIN grade g ON s.`GradeId` = g.`GradeID`
-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub INNER JOIN grade g ON sub.gradeid = g.gradeid
-- 查询 数据库结构-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'
4.5 排序和分页
/*============== 排序 ================
语法 : ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
可以使用 DESC 关键字，进行降序排序 */
-- 查询 数据库结构-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'
ORDER BY StudentResult DESC
/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
推导:
第一页 : limit 0,5
第二页 : limit 5,5 ......
第N页 : limit (pageNo-1)*pageSzie,pageSzie [pageNo:页码,pageSize:单页面显示条数] */
-- 每页显示5条数据
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'
ORDER BY StudentResult DESC , studentno LIMIT 0,5
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
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='JAVA第一学年'
ORDER BY StudentResult DESC LIMIT 0,10
4.6 子查询
/*============== 子查询 ================
什么是子查询?
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字; */
-- 查询 数据库结构-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;
-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno in
( SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1' )
ORDER BY studentresult DESC;
-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80
-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname
FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname
FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
AND subjectno=
(SELECT subjectno FROM `subject`WHERE subjectname = '高等数学-2' )
-- 方法三:使用子查询： 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname
FROM student WHERE studentno IN
(
SELECT studentno FROM result
WHERE StudentResult>=80 AND subjectno =(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
4.7 sql优化（待完善）
explain 关键字：https://blog.csdn.net/wang5701071/article/details/117782061
InnoDB存储引擎：聚集索引
sql编写的顺序：
select distinct ... from ... join ... on ...
where ...
group by ... having ...
order by ...
limit ...
sql执行的顺序：
from ... on ... join ...
where ...
group by ... having ...
select distinct ...
order by ...
limit ...
5. MySQL函数
5.1 常用函数
数据函数
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
字符串函数
/* CHAR_LENGTH：返回字符串包含的字符数：
1、计算单位：字符
2、不管汉字还是数字或者是字母都算是一个字符
*/
SELECT CHAR_LENGTH('狂神说坚持就能成功'); -- 结果： 9
/* LENGTH：
1、计算单位：字节
2、utf8编码：一个汉字三个字节，一个数字或字母一个字节。
3、gbk编码：一个汉字两个字节，一个数字或字母一个字节。
*/
SELECT LENGTH('狂神说坚持就能成功'); --结果：27
/*
str是要查找的字符串。
strlist是要搜索的 逗号分隔的 字符串列表。
FIND_IN_SET()函数根据参数的值返回一个整数或一个NULL值：
1. 如果str或strlist为NULL，则函数返回NULL值。
2. 如果str不在strlist中，或者strs是空字符串，则返回零。
3. 如果str在strlist中，则返回一个正整数。
*/
FIND_IN_SET(str, strlist); //返回一个整数，或null
SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/ --结果： 我爱程序
/*替换字符串,从某个位置开始,替换某个长度
-- 超级热爱编程helloworld
*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/ --结果： hello
SELECT RIGHT('hello,world',5); /*从右边截取*/ --结果： world
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/ --结果：坚持就能成功
SELECT REVERSE('狂神说坚持就能成功'); /*反转
日期和时间函数
/*
CURRENT_DATE()与CURDATE()作用相同，都返回 yyyy-MM-dd格式
CURRENT_DATE()+0 返回格式：yyyyMMdd
*/
SELECT CURRENT_DATE(); --结果：2022-05-07
SELECT CURDATE(); --结果：2022-05-07
SELECT CURRENT_DATE(); --结果：20220507
/*获取当前日期和时间 ：
主要有NOW()和SYSDATE()两种 （yyyy-MM-dd hh:mm:ss），使用上有一点点区别：
NOW()取的是语句开始执行的时间，SYSDATE()取的是动态的实时时间。
因为NOW()取自mysql的一个变量”TIMESTAMP”，
而这个变量在语句开始执行的时候就设定好了，
因此在整个语句执行过程中都不会变化
NOW()还有3个同义词：
LOCALTIME()，LOCALTIMESTAMP()，CURRENT_TIMESTAMP()
*/
SELECT NOW();
SELECT SYSDATE();
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
系统信息函数
SELECT VERSION(); /*查询版本*/
SELECT USER(); /*查询mysql用户*/
5.2 聚合函数
| 函数名称 | 描述 |
|---|---|
| COUNT() | 返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】 |
| SUM() | 统计数字字段或表达式列，返回一列的总和。 |
| AVG() | 统计数值字段或表达列，返回一列的平均值 |
| MAX() | 可以统计 数值、字符或表达式列，返回最大的值。 |
| MIN() | 可以统计 数值、字符或表达式列，返回最小的值。 |
-- 聚合函数
/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student; /*推荐*/
-- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询，结果上没有区别。
-- count(字段) 会统计该字段在表中出现的次数，不统计字段为null 的记录。
-- count(*) 包括了所有的列，相当于行数，包含字段为 null 的记录；
-- count(1) 不统计所有的列，用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
/*
很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。
其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，它们的效率可以说是相差无几。
而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
下面它们之间的一些对比：
1）在表没有主键时，count(1)比count(*)快
2）有主键时，主键作为计算条件，count(主键)效率最高；
3）若表格只有一个字段，则count(*)效率较高。
*/
5.3 小结
-- ================ 内置函数 ================
-- ---------- 数值函数 ----------
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数
-- ---------- 时间日期函数 ----------
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间
-- ---------- 字符串函数 ----------
length(string) -- string的字节数
char_length(string) -- string的字符个数
substring(str, position [,length]) -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
instr(string ,substring) -- 返回substring首次在string中出现的位置
concat(string [,...]) -- 连接字串
charset(str) -- 返回字串字符集
lcase(string) -- 转换成小写
left(string, length) -- 从string2中的左边起取length个字符
load_file(file_name) -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
ltrim(string) -- 去除前端空格
repeat(string, count) -- 重复count次
rpad(string, length, pad) --在str后用pad补充,直到长度为length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比较两字串大小
-- ---------- 聚合函数 ----------
count()
sum();
max();
min();
avg();
group_concat()
-- ---------- 其他常用函数 ----------
md5();
default();
6. 事务和索引
6.1 事务
参考博客：https://blog.csdn.net/weixin_55613232/article/details/118279077
事务的概念
- 事务一个操作序列，包含了一组数据库操作命令。这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
- 在数据库系统上执行并发操作时，事务是最小的控制单元。
- 通过整体性保证数据的一致性
- MySQL 的表类型由存储引擎（Storage Engines）决定，MySQL 数据表主要支类型 ：
- 事务安全型（transaction-safe）：BDB、InnoBDB（新版MySQL已经移除对BDB的支持）
- 非事务安全型（non-transaction-safe）：HEAP（Memory）、Archive、MYISAM
参考博客：https://www.csdn.net/tags/NtDacgwsOTk2NjUtYmxvZwO0O0OO0O0O.html
事务的ACID原则
原子性（Atomicity）
- 事务的所有的操作是一个不可分割的单元。要么全部成功，事务提交；任何一个操作失败，所有操作都撤销，恢复到原始状态
一致性(Consist)
- 在事务开始之前和结束以后，数据库的完整性约束没有被破坏，数据处于一致状态。在正在进行的事务中，数据可能处于不一致的状态。
案例：对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。
隔离性(Isolated)
- **在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。**对数据进行修改的所有并发事务是彼此隔离的，事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
持久性(Durable)
在事务完成以后，该事务所对数据库的更改，便持久的保存在数据库之中，并不会被回滚。一旦事务被提交，事务的效果会被永久地保留在数据库中。
基本语法
/*
1. 如果没有开启自动提交，当前会话连接的mysql的所有操作，都会当成一个事务，
直到你输入rollback|commit ，当前事务才算结束。
当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
2. 如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。
3. 当然无论开启与否，begin; commit|rollback; 都是独立的事务。
*/
-- 使用set语句来改变自动提交模式
SHOW VARIABLES LIKE 'AUTOCOMMIT'; #查看Mysql中的AUTOCOMMIT值
SET autocommit = 0; /*关闭自动提交*/
SET autocommit = 1; /*开启自动提交*/
-- 注意:
-- 1.MySQL中默认是自动提交
-- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
BEGIN 或 START TRANSACTION
-- 提交一个事务给数据库
COMMIT [WORK]
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK [WORK]
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO [SAVEPOINT] 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
sql 测试
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
-- ---------- 创建数据库 shop ----------
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
-- ---------- 创建 account 表 ----------
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- ---------- 插入数据 ----------
INSERT INTO account (`name`,`cash`) VALUES('A',2000.00),('B',10000.00)
-- ---------- 转账实现 ----------
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- ---------- 提交事务 ----------
# rollback; -- ---------- 事务回滚 ----------
SET autocommit = 1; -- 恢复自动提交
事务之间的相互影响
1、脏读：一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。
2、不可重复读：一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。
3、幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。
4、丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。
MySQL事务隔离级别
- read uncommitted : 读取尚未提交 ：不解决脏读
- read committed：读取已经提交 ：可以解决
脏读(Oracle默认的) - repeatable read：可重复读：可以解决
脏读和不可重复读(mysql默认的) - serializable：串行化：可以解决
脏读 、不可重复读 和 幻读(相当于锁表)
mysql默认的事务处理级别是 repeatable read ，而Oracle和SQL Server是 read committed 。
查询和设置事务隔离级别
-- ----------- 查询全局事务隔离级别 -----------
show global variables like '%isolation%';
SELECT @@global.tx_isolation;
-- ----------- 查询会话事务隔离级别 -----------
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
-- ----------- 设置全局事务隔离级别 -----------
set global transaction isolation level read committed;
-- ----------- 设置会话事务隔离级别 -----------
set session transaction isolation level read committed;
6.2 索引
MySQL索引详解：https://blog.csdn.net/guorui_java/article/details/118558095
MySQL进阶：索引的进阶及理解
MySQL之详解索引：https://blog.csdn.net/qq_44129924/article/details/115333658
MySQL索引背后的数据结构及算法原理：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
索引的概念
概念：
索引（Index）是 帮助MySQL高效获取数据的 数据结构。
优点:
- 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
- 通过索引列对数据进行排序，降低数据排序的成本，减少cpu的消耗
缺点:
- 创建索引和维护索引需要耗费额外的时间, 所以更新表的时候会慢一点
- 占用物理存储空间
索引的分类
1.1、单列索引：单列索引中包含(普通,唯一,主键,前缀)这四个索引. 一个索引只包含单个列，但一个表中可以有多个单列索引。
- 普通索引：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
- 唯一索引：索引列中的值必须是唯一的，但是允许为空值，
- 主键索引：是一种特殊的唯一索引，不允许有空值。
- 前缀索引 ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
1.2、组合索引: 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
1.3、全文索引: 全文索引主要是为了检索大文本数据中的关键字的信息，是搜索引擎数据库使用的一种技术，只能用于 CHAR 、VARCHAR 、 TEXT 数据列类型。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。
1.4、空间索引: 是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。 在创建空间索引时，使用SPATIAL关键字。
innoDB存储引擎下的索引分类
回表查询
思考题: 已知name是二级索引,通过name查询的时候是怎么查询的.
答: 由于数据innodb底层是b+树实现的, 所以查询的时候 会通过二叉树的查找模式来进行查找。 b+树的每个叶子节点都会存放数据, 而二级索引存放的是这行数据的id值, 所以拿到id后,需要再查找一下聚集索引。聚集索引的叶子节点存放的是这行数据的所有信息。这个行为也称为：回表查询。
创建索引的sql语句
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE|FULLTEXT|SPATIAL]
INDEX|KEY [索引名] (字段名[(长度)] [ASC|DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：
DROP INDEX 索引名 ON 表名字;
#删除主键索引:
ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息:
SHOW INDEX FROM student;
-- 1.添加PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
-- 2.添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
-- 3.添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
-- 4.添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
-- 5.添加多列索引（组合索引、联合索引）
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
*/
索引的底层数据结构
1: 哈希索引:
对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
2:BTree树索引
是一种很普遍的数据库索引结构。其特点是定位高效、利用率高、自我平衡，理论上，使用Btree在亿条数据与100条数据中定位记录的花销相同。
3:索引提高查询速度的原因：
要从mysql的存储说起：mysql的存储形式是页。
| 分类 | 步骤 |
|---|---|
| 没有使用索引 | 1. 定位到记录所在的页：需要遍历双向链表，找到所在的页 2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了,这种查找时间复杂度为O(n) |
| 使用索引 | 将无序的数据变成有序(相对)，通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)） |
最左前缀原则
如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边一列或连续几列，则此索引就可以被用到。如下：
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
例如组合索引（a,b,c），组合索引的生效原则是 ：
从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
比如：
1. where a=3 and b=45 and c=5 ....（命中） 这种三个索引顺序使用中间没有断点，全部发挥作用；
2. where a=3 and c=5...（部分命中） 这种情况下b就是断点，a发挥了效果，c没有效果
3. where b=3 and c=4...（未命中） 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
4. where b=45 and a=3 and c=5 ....（命中） 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关
使用索引的注意事项
- 经常需要搜索的列上，可以加快搜索的速度；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
- 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
- 在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；
- 避免 where 子句中对字段施加函数，这会造成无法命中索引。
- 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
- 删除长期未使用的索引
索引测试
建表app_user：
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` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
批量插入数据：100w
-- 使用函数，批量插入数据
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$ -- 定义函数的格式
CREATE FUNCTION mock_data() -- 函数名称
RETURNS INT -- 定义返回值类型
BEGIN -- 函数体开始
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
-- while循环开始：
WHILE i < num DO
INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
VALUES(CONCAT('用户', i), '24736743@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_data();
索引效率测试：
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
-- 查看sql执行的信息：
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
-- 创建索引：
CREATE INDEX idx_app_user_name ON app_user(name);
-- 再次查看sql执行的信息：
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'
7. 权限及设计数据库
7.1 用户管理
用户及权限管理
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
/* 增加用户：
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
- 只能创建用户，不能赋予权限。
- 用户名，注意引号：如 'user_name'@'192.168.1.1'
- 密码也需引号，纯数字密码也要加引号
- 如果在纯文本中指定密码，需忽略PASSWORD关键词。
如果把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
*/
CREATE USER kuangshen IDENTIFIED BY '123456'
-- 重命名用户 RENAME USER old_user TO new_user
RENAME USER kuangshen TO kuangshen2
-- ----------------- 设置密码 -----------------
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR 用户名
SHOW GRANTS FOR root@localhost;
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名
权限解释
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
/* 表维护 */
-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
7.2 数据库备份
- 直接拷贝数据库文件和相关配置文件
- 数据库管理工具，如SQLyog、Navicat
- mysqldump备份工具：
-- ----------------- 导出 -----------------
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
格式：mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
格式：mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
格式：mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件
-- ----------------- 导入 -----------------
1. 在登录mysql的情况下（推荐）：-- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
7.3 三大范式
三大范式举例：https://blog.csdn.net/weixin_44355591/article/details/106194714
1NF 原子性(确保字段不可分)
- 表中的所有字段值都是不可分解的原子值
不符合第一字段的例子：
表：字段1， 字段2（字段2.1，字段2.2）， 字段3
字段2可以拆分成字段2.1和字段2.2，不符合第一范式。
2NF 唯一性 (一张表 只说明一个事物）
- 在1NF基础上，要求所有的非主键字段完全依赖主键，不能只依赖于主键的⼀部分。
不符合第二范式的例子
表：学号， 姓名， 年龄， 课程名称， 成绩， 学分
这个表明显说明了两个事务：学生信息， 课程信息。
存在以下问题：
a、数据冗余：每条记录都含有相同信息；
b、删除异常：删除所有学生成绩，就把课程信息全删除了；
c、插入异常：学生未选课，无法记录进数据库；
d、更新异常：调整课程学分，所有行都调整。
修正：
学生表：学号， 姓名， 年龄
课程表：课程名称，学分
选课关系表：学号， 课程名称， 成绩
第三范式(确保每列都和主键列直接相关，而不是间接相关)
- 在2NF基础上，所有非主键只能依赖于主键，不能产生传递依赖。即不能存在：⾮主键列 A 依赖于⾮主键列 B，⾮主键列 B 依赖于主键的情况。
不符合第三范式的例子
表：学号， 姓名， 年龄， 所在学院， 学院联系电话
其中关键字为单一关键字"学号"。存在依赖传递:：(学号) → (所在学院) → (学院联系电话) 。
存在问题:：
a、数据冗余：有重复值；
b、更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况
c、删除异常
修正：
学生表：学号， 姓名， 年龄， 所在学院；
学院表：学院， 电话
8.JDBC
8.1 数据库驱动
SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库）规范，俗称JDBC。这些规范的实现有具体的厂商去做。对于开发人员，只需要掌握JDBC接口的操作！
8.2 第一个JDBC程序
创建数据库，插入数据
-- 创建数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
-- 创建user表
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,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
添加mysql驱动
创建lib文件夹，将mysql驱动考进去；右键lib文件夹 ----> add as library（导入jar包）
编写JDBC程序
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2.用户信息
// useUnicode = true 支持中文编码
// characterEncoding = utf8 设定字符集
// useSSL = true 安全连接
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 && useSSL = true";
String user = "root";
String password = "lcp5211314..";
//3.连接成功，数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, user, password);
//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();
}
8.3 对象说明
DriverManager
Jdbc程序中的DriverManager用于加载驱动，并创建与数据库的链接，这个API的常用方法：
DriverManager.registerDriver(new Driver()) //注册驱动
DriverManager.getConnection(url, user, password) //连接数据库
注意：在实际开发中并 不推荐采用registerDriver方法注册驱动。原因有二：
- 查看Driver的源代码可以看到，如果采用此种方式，会导致驱动程序注册两次，也就是在内存中会有两个Driver对象。
- 程序依赖mysql的api，脱离mysql的jar包，程序将无法编译，将来程序切换底层数据库将会非常麻烦。
推荐方式：Class.forName(“com.mysql.jdbc.Driver”);
- 采用此种方式不会导致驱动对象在内存中重复出现，并且采用此种方式，程序仅仅只需要一个字符串，不需要依赖具体的驱动，使程序的灵活性更高。
URL
通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：
常用数据库URL地址的写法：
| 数据库 | URL写法 |
|---|---|
| Oracle写法 | jdbc:oracle:thin:@localhost:1521:sid |
| SqlServer写法 | jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid |
| MySql写法 | jdbc:mysql://localhost:3306/sid |
如果连接的是本地的Mysql数据库，并且端口是3306，那么的url地址可以简写为
jdbc:mysql:///数据库
Connection （数据库连接对象）
Jdbc程序中的Connection，用于代表数据库的链接。客户端与数据库所有交互都是通过connection对象完成的，这个对象的常用方法：
| 用法 | 说明 |
|---|---|
| createStatement() | 创建向数据库发送sql的statement对象 |
| prepareStatement(sql) | 创建向数据库发送预编译sql的PrepareSatement对象 |
| setAutoCommit(boolean) | 设置事务是否自动提交 |
| commit() | 在链接上提交事务 |
| rollback() | 在此链接上回滚事务 |
Statement （执行sql对象）
Jdbc程序中的Statement对象，用于向数据库发送SQL语句，常用方法：
| 用法 | 说明 |
|---|---|
| execute(String sql) | 用于向数据库发送任意sql语句 |
| executeQuery(String sql) | 用于向数据发送查询语句 |
| executeUpdate(String sql) | 用于向数据库发送insert、update、delete语句 |
| addBatch(String sql) | 把多条sql语句放到一个批处理中 |
| executeBatch() | 向数据库发送一批sql语句执行 |
ResultSet （查询的结果集）
Jdbc程序中的ResultSet用于代表Sql语句的执行结果。Resultset封装执行结果时，采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前，调用ResultSet.next() 方法，可以使游标指向具体的数据行，进行调用方法获取该行的数据。ResultSet既然用于封装执行结果的，所以该对象提供的都是用于获取数据的get方法：
ResultSet提供了对结果集进行滚动的方法：
- next()：移动到下一行
- Previous()：移动到前一行
- absolute(int row)：移动到指定行
- beforeFirst()：移动resultSet的最前面。
- afterLast() ：移动到resultSet的最后面。
获取任意类型的数据
- getObject(int index)
- getObject(string columnName)
获取指定类型的数据，例如：
- getString(int index)
- getString(String columnName)
释放资源
- Jdbc程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象，这些对象通常是ResultSet, Statement和Connection对象，特别是Connection对象，它是非常稀有的资源，用完后必须马上释放。
- 如果Connection不能及时、正确的关闭，极易导致系统宕机。Connection的使用原则是尽量晚创建，尽量早释放。
- 为确保资源释放代码能运行，资源释放代码也一定要放在finally语句中。
//释放连接
resultSet.close();
statement.close();
connection.close();//耗资源
8.4 statement对象
jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的CRUD，通过这个对象向数据库发送sql语句即可。
| 方法 | 作用 |
|---|---|
| executeUpdate | 用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化） |
| executeQuery | 用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象 |
CRUD操作-create
Statement st = conn.createStatement();
String sql = "insert into user(….) values(…..) ";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功！！！");
}
CRUD操作-delete
Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println(“删除成功！！！");
}
CRUD操作-update
Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){ System.out.println(“修改成功！！！"); }
CRUD操作-read
Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeUpdate(sql);
while(rs.next()){ //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中 }
创建JDBCUtils工具类
// ------------------ properties信息： ------------------
driver=com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/aa_studay?characterEncoding=utf8&useSSL=true&useUnicode=true
username = root
password = 123456
jdbc工具类信息：
public class JDBCUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = JDBCUtils.class.getClassLoader()
.getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(in);
//读取配置文件
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.获取数据库连接
public static Connection getConnection() throws Exception{
return DriverManager.getConnection(url, username, password);
}
//3.释放资源
public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
if(rs!=null){ rs.close(); }
if (st!=null){ st.close(); }
if(conn!=null){ conn.close(); }
}
}
8.5 preparedStatement对象
PreperedStatement是Statement的子类，它的对象可以通过调用
Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避免SQL注入的问题。
- Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。
- PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。
package com.jdbc.lesson03;
import com.jdbc.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class testInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into users(id,name,birthday)" + "values(?,?,?)";
// -------------------- 区别 --------------------
//使用？占位符代替参数
pstm = conn.prepareStatement(sql); // 预编译sql， 先写sql。然后不执行
//手动给参数赋值
pstm.setInt(1,5);
pstm.setString(2,"qingjiang");
//sql中的日期，是java.sql.Date。可通过 java中的 时间戳 转换
pstm.setString(3,new java.sql.Date(new Date().getTime()));
//执行
int i = pstm.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pstm,null);
}
}
}
8.6 JDBC处理事务
创建数据库表，并插入数据
/*创建账户表*/
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40),
money FLOAT
);
/*插入测试数据*/
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);
当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列的JDBC控制事务语句
- //开启事务--
关闭自动提交，会自动开启事务(start transaction)
-Connection.setAutoCommit(false); - Connection.rollback(); //
回滚事务(rollback) - Connection.commit(); //
提交事务(commit)
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
//通知数据库开启事务(start transaction)
conn.setAutoCommit(false);
String sql1 = "update account set money=money-100 where name='A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update account set money=money+100 where name='B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//上面的两条SQL执行Update语句成功之后就通知数据库提交事务 (commit)
conn.commit();
System.out.println("成功！！！"); //log4j
}catch (Exception e) {
//如果失败，显式定义回滚（不写也行，默认出现异常就回滚）
conn.rollback();
e.printStackTrace();
}finally{
//释放资源（此处省略代码）
JdbcUtils.release(conn, st, rs);
}
}
8.7数据库连接池
每次请求都需要向数据库获得链接，而数据库创建连接需要消耗相对较大的资源，创建时间也较长。这极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出、拓机。
数据库连接池的基本概念：
- 数据库连接 是一种
关键、有限、昂贵的资源，对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正式针对这个问题提出来的。 - 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
- 数据库连接池 在初始化时将创建一定数量的数据库连接放到连接池中，连接的数量是由
最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池一直保证至少拥有这么多数量的连接。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
数据库连接池的 最小连接数 和 最大连接数 的设置要考虑到以下几个因素:
- 最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。
- 最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中，这会影响以后的数据库操作
- 如果最小连接数与最大连接数相差很大：那么最先连接请求将会获利，之后超过最小连接数量的 连接请求 等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，他将被放到连接池中等待重复使用或是空间超时后被释放。
编写连接池需实现java.sql.DataSource接口 ！！！
开源数据库连接池
现在很多WEB服务器(Weblogic, WebSphere, Tomcat)都提供了DataSoruce的实现，即连接池的实现。通常我们把DataSource的实现，按其英文含义称之为数据源，数据源中都包含了数据库连接池的实现。
也有一些开源组织提供了数据源的独立实现：
- DBCP 数据库连接池
- C3P0 数据库连接池
在使用了数据库连接池之后，在项目的实际开发中就不需要编写连接数据库的代码了，直接从数据源获得数据库的连接。
DBCP数据源
DBCP 是 Apache 软件基金组织下的开源连接池实现，要使用DBCP数据源，需要应用程序应在系统中增加如下两个 jar 文件：
- commons-dbcp.jar：连接池的实现
- commons-pool.jar：连接池实现的依赖库
Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。
1、导入相关jar包
2、在类目录下加入dbcp的配置文件：dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
3、编写工具类 JdbcUtils_DBCP
public class JDBCUtils_DBCP {
private static DataSource dataSource = null;
static{
try {
InputStream resourceAsStream = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcp.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
// 创建数据源 工厂模式 ----> 创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
// 从数据源中获取连接
return dataSource.getConnection();
}
// 释放资源
public static void release(Connection conn, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) { resultSet.close(); }
if (statement != null) { statement.close(); }
if (conn != null) { conn.close(); }
} catch (Exception e) {
e.printStackTrace();
}
}
}
4、测试类：
public class testDBCP {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = JdbcUtils_DBCP.getConnection();
// 使用“？”占位符 来代替参数
String sql = "INSERT INTO users (`id`,`NAME`,`birthday`) VALUES(?,?,?)";
// 预编译sql，
preparedStatement = conn.prepareStatement(sql);
// 手动给参数赋值
preparedStatement.setInt(1,4);
preparedStatement.setString(2,"no4");
// sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() ----> System.currentTimeMillis()
preparedStatement.setDate(3, new java.sql.Date(System.currentTimeMillis()));
// 执行
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn, preparedStatement, null);
}
}
}
C3P0
C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。C3P0数据源在项目开发中使用得比较多。
c3p0与dbcp区别
- dbcp没有自动回收空闲连接的功能，c3p0有自动回收空闲连接功能
1、导入相关jar包
- c3p0.jar
- mchange-commons-java.jar
2、在类目录下加入C3P0的配置文件：c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
C3P0的缺省（默认）配置，
如果在代码中“ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();”
这样写表示用的是C3P0的默认配置
-->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">
jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
</default-config>
<!--
C3P0的命名配置：
如果在 java 代码中“ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(“mySource”);”
这样写表示使用的是name为mySource的配置
-->
<named-config name="mySource">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/school1</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
</named-config>
</c3p0-config>
3、创建工具类
public class JdbcUtils_c3p0 {
private static DataSource dataSource = null;
static{
try {
//-------- 配置方法分两种 --------
//代码版配置 (不建议使用这种)
/*
dataSource = new ComboPooledDataSource();
//dataSource.setDriverClass();
//dataSource.setUser();
...
*/
// 创建数据源 工厂模式 ----> 创建
dataSource = new ComboPooledDataSource();
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
// 从数据源中获取连接
return dataSource.getConnection();
}
// 释放资源
public static void release(Connection conn, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) { resultSet.close(); }
if (statement != null) { statement.close(); }
if (conn != null) { conn.close(); }
} catch (Exception e) {
e.printStackTrace();
}
}
}
9. 生产问题
9.1 新增自增列，并初始化
如果表已存在，且有数据。现在要新增一列编号（数字），并且初始化原来的数据，则顺序执行下面两条sql：
update table_name set num = 100000 limit 1;
alter table table_name change num num int(6) unique AUTO_INCREMENT;
扫一扫
298
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
