MySQL

1. 初识 MySQL

什么是数据库

1. 数据库（DB ，DataBase）
2. 概念：数据仓库，软件，安装在操作系统（windows，linux，mac，…）之上
3. 作用：存储数据、管理数据

数据库分类

关系型数据库：（SQL）

MySQL，Oracle、Sql server ， DB2，SQLlite

通过表和表之间，行和列之间的关系进行数据的存储

**非关系型数据库（NoSQL）**not only sql

Redis、MongDB

对象存储，通过对象的自身属性来决定存储方式

比如键对值结构

DBMS（数据库管理系统）

数据库管理系统 ( DataBase Management System )

数据库管理软件 , 科学组织和存储数据 , 高效地获取和维护数据

MySQL是一个数据库管理系统

MySQL 简介

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

mysql5.7 64位下载地址：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

安装 mysql5.7

尽量使用安装包安装，方便卸载

1. 解压安装包，放到相关路径
2. 添加环境变量 PATH

D:\Program Files\mysql-5.7.19-winx64\bin

值设置为 MySQL 下 bin 目录路径

3. 在 MySQL 根目录下新建配置文件 my.ini

[mysqld]
basedir=D:\Program Files\mysql-5.7.19-winx64\			# 基础路径
datadir=D:\Program Files\mysql-5.7.19-winx64\data\		# 数据路径，没有的话只写路径，不要新建
port=3306				# 端口
skip-grant-tables		# 登陆时跳过密码

4. 以管理员模式启动CMD，并将路径切换至 MySQL 下的 bin 目录

cd /d D:\Program Files\mysql-5.7.19-winx64\bin

相关脚本储存在 bin 目录中

5. 安装 MySQL 服务

mysqld -install

6. 初始化数据文件，即新建 data 文件夹

mysqld --initialize-insecure --user=mysql

7. 启动 MySQL 服务

net start mysql

进入 MySQL 管理界面

mysql –u root –p	# -p后不要有空格，会被当做密码

以下是MySQL 管理界面，注意结尾分号

重置密码

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

8. 刷新权限

flush privileges;

9. 修改 my.ini，注释掉最后一句 skip-grant-tables
10. 退出 MySQL 管理界面

exit;	# 没有括号

11. 重启 MySQL 服务

net stop mysql
net start mysql

2. 常用数据库操作命令

-- 连接 MySQL
mysql -u root -p123456
-- 修改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
-- 刷新权限
flush privileges;
-- 退出 MySQL
exit;

3. 操作数据库与表

结构化查询语句分类

数据库引擎

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

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

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

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

重启数据库后自增列

• innoDB 自增列会从1开始（存在内当中，断电即失）
• MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

① 操作数据库

创建数据库 : CREATE DATABASE [if not exists] dbname;

删除数据库 : DROP DATABASE [if exists] dbname;

查看所有数据库 : SHOW DATABASE;

使用(进入)数据库 : USE dbname;

查看所有表：SHOW TABLES;

查看某表结构：DESC dbname

② 表的列类型

注意后面带括号表示位数

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小 3个字节
• int 标准的整数 4个字节（常用）
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节 （精度问题）
• decimal 字符串形式的浮点数,金融计算的时候，一般用

字符串

• char 字符串固定大小 0-255
• varchar 可变字符串 0-65535(即：string）
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 (保存大文本)

时间日期

java.util.Date

• date YYYY-MM-DD，日期
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳
• year 年份表示

null

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

③ 表的字段类型

UNSIGENED

(unsigened )

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

ZEROFILL

• 0填充的
• 10的长度 1 – 0000000001 不足位数用0 填充

自增 AUTO_INCREMENT

(auto_increment)

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

非空 NOT NULL

• 假设设置为 NOT NULL，如何不给他赋值，就会报错
• NULL 如果不填写，默认为NULL

默认 DEFUALT

• 设置默认的值

④ 创建表 create

CREATE TABLE IF NOT EXISTS `user` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'id',
`username` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` VARCHAR(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

AUTO_INCREMENT：自增 auto-increment

COMMENT：注释

PRIMARY KEY：主键 primary key

注意：使用英文符号，表的名称和字段用` `括起来

表的“列”称为“字段”

格式：

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

常用命令

1. 查看创建数据库(已经存在)的语句

SHOW CREATE DATABASE `test`;

-- 结果
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */

2. 查看创建表(已经存在)的语句

SHOW CREATE TABLE `user`;

3. 查看表的结构

DESC `user`;

⑤ 修改表 alter

rename as，add，modify，change，drop

-- alter table
-- 修改表名 rename as
ALTER TABLE `student` RENAME  AS `student1`
-- 增加表的字段 add
ALTER TABLE `student1` ADD `age` INT(3)
-- 修改表的字段
	-- 修改约束 modify
ALTER TABLE `student1` MODIFY `age` VARCHAR(11)  
	-- 重命名 change
ALTER TABLE `student1` CHANGE `age` `age1` INT(3)  
-- 删除表的字段 drop
ALTER TABLE `student1` DROP `age1`

⑥ 删除表 drop

DROP TABLE IF EXISTS `student1`

4. 数据管理

① 外键

【原创】数据库中为什么不推荐使用外键约束

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

1. 在创建表的时候，增加约束
2. 创建表成功后添加外键

ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE  `表`  ADD CONSTRAINT    约束名     FOREIGN KEY （作为外键的列）REFERENCES 引用到哪个表的哪个字段

② DML 数据操作语言

insert

insert into … values …

insert into `表名` (`字段一`, `字段二`) values ('值1'),('值2');

INSERT INTO `grade` (`gradename`) VALUES ('大四');

-- 主键自增可以省略不写
INSERT INTO `grade` VALUES ('大三');	-- `gradename`

-- 如果不指定字段，将一一匹配
INSERT INTO `student` VALUES ('张三','123456','男');	-- `name`,`pwd`,`sex`

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('李四','123456','男'),('王五','23232','女');

update

update … set … where …

按指定条件修改字段

UPDATE `表名` SET `字段` = value WHERE 条件

UPDATE `user` SET username='changedName' WHERE id=1;

-- 同时修改多个字段
UPDATE `user` SET username='changedName',`password`='password' WHERE id=1;
UPDATE `user` SET `username`='Name' WHERE id BETWEEN 2 AND 4;

-- 不指定条件将修改所有字段
UPDATE `student` SET `name`='233'

操作符返回布尔值

操作符	含义
=	等于
!= <>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between … and …	在某个范围内，闭区间
… and …	&&
… or …	||

delete

delete from … where …

DELETE FROM `表名` WHERE 条件

DELETE FROM `student` WHERE id = 1

-- 不指定条件将删除所有字段 
DELETE FROM `student`

truncate

完全清空一张表，而表的结构和索引不会变

TRUNCATE TABLE `表名`

DELETE 与 TRUNCATE 区别

• 相同点： 都能删除数据，而不删除表结构
• 不同：
  • TRUNCATE 重新设置自增列 计数器会归零
  • TRUNCATE 不会影响事务

5. 数据查询 SQL

所有的查询操作都使用关键字 SELECT

SELECT [ALL | DISTINCT] 字段名s
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}];
    --  指定查询的记录从哪条至哪条

顺序不可错

① 查询指定字段 select

SELECT `字段` FROM `表`

SELECT `StudentNo`,`StudentName` FROM student

-- 别名，给结果起一个名字    
-- 可以给字段起别名 也可以给表起别名
SELECT `StudentNo` AS 学号 ,`StudentName`AS 学生姓名 FROM student AS S

-- 函数 concat(a,b)
-- 在查询结果中添加字段
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

② 去重 distinct

去除select语句查询结果中重复的语句，重复的语句只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentNo` FROM result 
-- 发现重复数据，去重
SELECT DISTINCT `studentNo` FROM result 

数据库的表达式

select 表达式 from 表

查询表，执行表达式

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

-- 函数
SELECT VERSION()  
-- 计算表达式
SELECT 100*3-1 AS 计算结果

SELECT `StudentResult`+1 AS '提分后' FROM result	-- 执行结束后 `StudentResult` 字段值加一

-- 变量
SELECT @@auto_increment_increment

③ where 条件子句

检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成！，结果为布尔值

逻辑运算符

运算符	语法	结果
and，&&	a and b，a&&b	逻辑与
or，||	a or b，a||b	逻辑或
Not， !	not a， !a	逻辑非

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

-- 模糊查询（区间）
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentNo = 1000
	-- 也可以写成
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentNo != 1000

模糊查询

like，in，is null，is not null

-- 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 '刘__';

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

-- ================================ IN ===================================

-- 查询1001 1002 1003 学员信息
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003

SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);

-- 查询在北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('安徽','河南洛阳');

-- ======================  IS NULL, IS NOT NULL ==========================

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

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

④ 连表查询

-- Join on 连接查询
-- where 等值查询

/*
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？
确定交叉点（这两个表中哪个数据是相同的）
判断的条件
*/

-- Inner Join
	   -- studentNo 字段同时存在于两张表中，此时要指明，否则会报：Column `studentNo` in field list is ambiguous
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo;

--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s		-- AS 可以用空格代替
RIGHT JOIN result r
ON s.studentNo = r.studentNo;

--Left Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNo = r.studentNo;

操作	描述
Inner join	返回两表中都匹配的值
left join	返回在左表中查到的所有值，即使右表中没有匹配
right jion	返回在右表中查到的所有值，即使左表中没有匹配

如果有一同学，在 student 表（左表）中有，而在 result 表（右表）中没有

使用 inner join 和 right join 查不到

而使用 left join 可以查到，存在于右表中的 SubjectNo,StudentResult 字段为空

-- 查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo	-- 查出了 student 表中的说有同学，并关联了 result 表
WHERE StudentResult IS NULL;		-- 添加一个 where 条件子句，筛选出缺考同学(result表中没有)

-- 连接多个表
-- 查询了参加考试同学的信息：学号，学生姓名，科目名，分数
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r		-- 以 result 表为主
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo;

-- 要查询哪些数据: SELECT ....
-- 从哪几个表中查: FROM 表 JOIN 连接的表 ON 交叉条件

-- 假设存在一中多张表查询，先查询两章表，然后再慢慢增加

--FROM a LEFT JOIN b    以左为准
--FROM a RIGHT JOIN b	以右为准

自连接（了解）

自己的表跟自己的表连接，核心：一张表拆为两张一样的表

-- 查询父子信息

SELECT a.`categoryName` AS `父栏目`,b.`categoryName` AS `子栏目`
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

⑤ 分页与排序

排序 ORDER BY

升序 ASC，降序 DESC

SELECT  ...
FROM ...
JOIN ...
ON ...
WHERE ...
ORDER BY `字段` ASC || DESC

SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL
ORDER BY StudentResult ASC;

分页 limit

LIMIT 起始字段下标，页面的大小

SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL
ORDER BY StudentResult ASC
LIMIT 0,5;

-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页  limit 5*（n-1）,5

⑥ 子查询

在where语句中嵌套一个查询语句

-- 1.查询 数据库结构-1 的所有考试结构（学号，科目编号，成绩） 降序
-- 方式一： 连接查询（关联两张表查询）
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC

-- 方式二：使用子查询(由里及外，先找出 数据库结构-1 的SubjectNo，再找结果)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE SubjectNo=(						-- 即使字段不在查询范围内也可使用（在表中存在）
	SELECT SubjectNo FROM  `subject` 
    WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC

⑦ 分组查询

GROUP BY 分组依据
HAVING 条件

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）

SELECT `SubjectName`,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `Subject` sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.SubjectNo 		-- 分组
HAVING AVG(StudentResult)>80;	-- 分组后过滤

-- 分组后过滤只能用 HAVING, 而不能用 WHERE

如果不分组则只会查出一个，因为每组中只能有一个最高和最低

而且，与

SELECT `StudentNo`,r.`SubjectName`,`StudentResult`

不同，`SubjectName` 字段与 聚合函数 AVG(StudentResult),MAX(StudentResult),MIN(StudentResult) 没有关联性

以下是按 subjectNo 分组查询的结果

6. MySQL 函数

常用函数

① 聚合函数

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

COUNT

查询表中记录数

SELECT COUNT(studentname) FROM `student`;	-- count(字段)
SELECT COUNT(*) FROM `student`;				-- count(*)
SELECT COUNT(1) FROM `student`;				-- count(1)

count(字段) 查询字段总行数，会忽略 NULL

而 count(*) count(1) 则返回总行数，不忽略 NULL

后两者在执行效率上略有差异

SELECT SUM(`studentResult`) AS 成绩总和 FROM `result`;

SELECT AVG(`studentResult`) AS 平均分 FROM `result`;

SELECT MAX(`studentResult`) AS 最高分 FROM `result`;

SELECT MIN(`studentResult`) AS 最低分 FROM `result`;

7. 事务

要么都成功，要么都失败

什么是事务

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

① ACID 原则

事务原则 ： ACID原则

原子性（Atomicity）

原子性是指事务是一个不可再分割的工作单元

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保持一致

隔离性（Isolation）

事务产生多并发时，互不干扰

持久性（Durability）

事务一旦提交就不可逆转，被持久化到数据库中

若事务未提交，服务器宕机或断电，数据仍保持原样

隔离产生的问题

脏读：

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

不可重复读：

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

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

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

-- 手动处理事务
SET autocommit =0 -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQP都在同一个事务内

INSERT XX
INSERT XX

-- 提交 ： 持久化(成功)
COMMIT 
-- 回滚：  回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 删除保存点

② 实例

CREATE DATABASE `bank` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE bank;
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`)
VALUE('A',1000),('B',10000);

SET autocommit = 0;
START TRANSACTION;
UPDATE account SET money = money + 100 WHERE `name` = 'A';
UPDATE account SET money = money - 100 WHERE `name` = 'B';
COMMIT;
-- ROLLBACK;
SET autocommit = 1;

8. 索引

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

① 分类

• 主键索引 (Primary Key)
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引 (Unique)
  • 避免重复的列出现，可将多个列标识为唯一索引
• 常规索引 (Index/Key)
  • 默认
• 全文索引 (FullText)
  • 在特点的数据库引擎下才有，MyISAM
  • 快速定位数据

② 索引的使用

-- 1.在创建表的时候给字段增加索引
CREATE TABLE IF NOT EXISTS `user` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'id',
`username` VARCHAR(20) NOT NULL COMMENT '用户名',
`Email` VARCHAR(20) NOT NULL COMMENT 'Email',
    -- 注意这种方式，索引名都带 KEY，与方法二不同，方法二只有索引名
PRIMARY KEY (`id`)							-- 添加主键索引
UNIQUE KEY(`username`)						-- 添加唯一索引
KEY(`Email`)								-- 添加普通索引
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 2.创建完毕后，增加/删除索引
-- 使用ALTER命令操作行
--添加索引
ALTER TABLE `表名` ADD PRIMARY KEY (`字段名`);		-- 主键，唯一，全文索引均无需索引名
ALTER TABLE `表名` ADD UNIQUE (`字段名`);			
ALTER TABLE `表名` ADD INDEX 索引名 (`字段名`);		  -- 普通索引需要索引名
ALTER TABLE `表名` ADD FULLTEXT (`字段名`);			
-- 删除索引
ALTER TABLE `表名` DROP INDEX 字段名

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

③ 索引原则

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

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

索引的数据结构

参考阅读

9. 其他相关

① 用户管理，数据库备份

用户管理

数据库备份

# 导出 mysqldump
# 1. 导出一张表 
# mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school student >D:/a.sql

# 2. 导出多张表 
# mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school student result >D:/a.sql

# 3. 导出所有表 
# mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school >D:/a.sql

# 4. 导出一个库 
# mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 -B school >D:/a.sql
 
可以-w携带备份条件
 
# 导入 source
# 1. 在登录mysql的情况下
# source  备份文件
source D:/a.sql
# 2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件

② 规范化数据库设计

规范化数据库设计

三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

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

10. JDBC

SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个Java操作数据库的)规范，即 JDBC

这些规范的实现由具体的厂商去做

对于开发人员来说，只需要握JDBC的接口操作即可

Non-static field 'password cannot be referenced from a static context

ps:为什么静态方法无法调用非静态成员（方法和变量）

① 基本使用

导入 jar 包：mysql-connector-java-5.1.48.jar

首先定义一个工具类utils来读取配置，加

载驱动，提供数据库连接与关闭方法

package club.xiongyi24.Demo1.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class utils {

    public static  String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    // static块在类被加载的时候执行且仅会被执行一次
    static {

        try{
            // 读取配置信息
            InputStream in = utils.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");

            // 载入驱动
            Class.forName(driver);

        } catch (Exception e) {;
            e.printStackTrace();
        }
    }

    // 数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    // 关闭
    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();
        }
        
    }

}

配置信息 db.properties

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

package club.xiongyi24.Demo1;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static club.xiongyi24.Demo1.utils.utils.getConnection;
import static club.xiongyi24.Demo1.utils.utils.release;

public class testDemo {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            // 获取连接，返回数据库对象Connection conn 即代表数据库
            conn = getConnection();
            // Statement对象用于执行 SQL 语句
            st = conn.createStatement();
            String sql_1 = "insert into `users`(`NAME`,`PASSWORD`,`email`,`birthday`) value ('xiongyi','123456','1308410708@qq.com','2002-03-24');";
            // Statement 用于执行 SQL 的方法常用的有两种
            // ① executeUpdate 用于执行 insert, update, delete 等非查询语句，返回受影响的行数
            int i = st.executeUpdate(sql_1);
            if (i != 0) {
                System.out.println("插入成功！");
            }
            // ② executeQuery 用于执行查询语句， 返回结果集 ResultSet 对象
            String sql_2 = "select `NAME` from `users` where id=1;";
            rs = st.executeQuery(sql_2);
            while(rs.next()) {
                System.out.println(rs.getObject("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            release(conn,st,rs);
        }
    }
}

载入驱动

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

连接数据库 获取connection

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";

DriverManager.getConnection

Connection conn = DriverManager.getConnection(url, username, password);

// Connection 对象代表数据库，用它来操作数据库
connection.rollback();		// 事务回滚
connection.commit();		// 事务提交
connection.setAutoCommit();	// 开启事务自动提交

创建SQL执行对象 statement

Statement st = conn.createStatement();

利用 Statement 执行 SQL

① executeUpdate 用于执行 insert, update, delete 等非查询语句，返回受影响的行数

String sql_1 = "insert into `users`(`NAME`,`PASSWORD`,`email`,`birthday`) value ('xiongyi','123456','1308410708@qq.com','2002-03-24');";

int i = st.executeUpdate(sql_1);
if (i != 0){
    System.out.println("插入成功");
}

② executeQuery 用于执行查询语句， 返回结果集 ResultSet 对象

String sql_2 = "select * from `users`";

ResultSet rs = st.executeQuery(sql_2);
while(rs.next()) {
    System.out.println(rs.getObject("name"));
}

ResultSet 结果集

查询的结果集，封装了所以的查询结果，以指针操作

resultSet.next(); //移动到下一个
resultSet.previous();//移动到前一行

resultSet.afterLast();//移动到最后
resultSet.beforeFirst();//移动到最前面

resultSet.absolute(row);//移动到指定行

以指定的数据类型获取查询结果

//如果知道则指定使用
resultSet.getString("name");
resultSet.getInt("age");
//在不知道则使用 Object
resultSet.getObject("password");	// 万物皆对象

释放连接

// 逆续关闭
rs.close();
st.close();
conn.close();

②PreparedStatement对象

SQL 注入

SQL 注入- 百度百科

package club.xiongyi24.SqlInject;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static club.xiongyi24.SqlInject.utils.JDBCUtils.getConnection;

public class inject {

    // 注意写成类方法
    public static void load(String username, String password) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            st = conn.createStatement();
            // 普通的SQL语句由SQL加变量拼接而成
            // SELECT `NAME`,`PASSWORD` from `users` where NAME='zhangsan' and PASSWORD='123456'
            String sql = "SELECT `NAME`,`PASSWORD` from `users` where NAME='"+username+"' and PASSWORD='"+password+"'";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                System.out.println(rs.getObject("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // 正常登录
        System.out.println("以下是正常登录：");
        String username = "zhangsan";
        String password = "123456";
        load(username,password);
        // SQL注入
        System.out.println("以下是SQL注入攻击：");
        // SELECT `NAME`,`PASSWORD` from `users` where NAME='' and PASSWORD='' or '1=1'
        // 直接增加一个 or 条件，跳过验证
        username = "";
        password = "' or '1=1";
        load(username,password);
    }
    
}

PreparedsStatement

可以利用 PreparedsStatement 对象防止注入攻击

PreparedStatement 继承自 Statement 类

// 注意写成类方法
public static void load(String username, String password) {

    Connection conn = null;
    PreparedStatement prst = null;
    ResultSet rs = null;

    try {
        conn = getConnection();
        // PreparedState 使用 ? 占位符来代替参数
        // SELECT `NAME`,`PASSWORD` from `users` where NAME='zhangsan' and PASSWORD='123456'
        String sql = "SELECT `NAME`,`PASSWORD` from `users` where NAME=? and PASSWORD=?";
        // 预编译SQL
        prst = conn.prepareStatement(sql);
        // 给占位符手动赋值
        prst.setString(1,username);
        prst.setString(2,password);
        // 执行SQL
        rs = prst.executeQuery();
        while(rs.next()) {
            System.out.println(rs.getObject("name"));
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

与 Statement 不同，Statement 是先创建执行对象，再执行SQL语句。而 PreparedStatement 是预编译SQL，赋值后执行

// Statement
st = conn.createStatement();
String sql = "SELECT `NAME`,`PASSWORD` from `users` where NAME='"+username+"' and PASSWORD='"+password+"'";
rs = st.executeQuery(sql);

// PreparedStatement
String sql = "SELECT `NAME`,`PASSWORD` from `users` where NAME=? and PASSWORD=?";
prst = conn.prepareStatement(sql);
prst.setString(1,username);
prst.setString(2,password);
rs = prst.executeQuery();

防SQL注入原理：

PreparedStatement不是将参数简单拼凑成sql，而是做了一些预处理，将参数转换为string，两端加单引号，将参数内的一些特殊字符（换行，单双引号，斜杠等）做转义处理，这样就很大限度的避免了sql注入

③ 事务

ACID原则（原子性，一致性，隔离性，持久性）

conn.setAutoCommit(false);
...
conn.commit();
conn.rollback();

package club.xiongyi24.Transaction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import static club.xiongyi24.Transaction.utils.utils.getConnection;
import static club.xiongyi24.Transaction.utils.utils.relese;

public class Transcation {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement prst = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            // 关闭数据库的自动提交功能， 自动开启事务
            conn.setAutoCommit(false);
            // 业务开始
            String sql_1 = "update `users` set money=money-50 where `NAME`='zhangsan'";
            prst = conn.prepareStatement(sql_1);
            prst.executeUpdate();
            String sql_2 = "update `users` set money=money-50 where `NAME`='lisi'";
            prst = conn.prepareStatement(sql_2);
            prst.executeUpdate();
            // 业务完毕，提交业务
            conn.commit();
            System.out.println("业务提交成功");
        } catch (Exception e) {
            try {
                if (conn != null) {
                    // 如果失败则回滚
                    conn.rollback();
                }
                System.out.println("发生错误，业务回滚成功");
            } catch (Exception exception) {
                exception.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                relese(conn,prst,rs);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

④ 数据库连接池

池化技术

常用连接池

DBCP

C3P0

Druid: 阿里巴巴

DBCP

导入 jar 包：commons-pool2-2.4.2.jar，commons-logging-1.2.jar，commons-dbcp2-2.1.1.jar

详细参见：JDBC 数据库连接池 配置

与前面的区别主要在于 数据库的连接配置上，即主要区别于 utils 工具类的编写

InputStream in = utils.class.getClassLoader().getResourceAsStream("dbcp.properties");
Properties properties = new Properties();
properties.load(in);

dataSource = BasicDataSourceFactory.createDataSource(properties);

Connection connection = dataSource.getConnection()

package club.xiongyi24.DBCP.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class utils {

    static private BasicDataSource dataSource = null;

    static {
        try {
            // 读取配置
            InputStream in = utils.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(in);

            // 创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        // 获取连接
        // 其他事项由DBCP帮我们完成了
        return dataSource.getConnection();
    }

    public static void relese(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (conn != null) {
            conn.close();
        }

    }

}

ClassLoader().getResourceAsStream(“dbcp.properties”);
Properties properties = new Properties();
properties.load(in);

dataSource = BasicDataSourceFactory.createDataSource(properties);

Connection connection = dataSource.getConnection()

```java
package club.xiongyi24.DBCP.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class utils {

    static private BasicDataSource dataSource = null;

    static {
        try {
            // 读取配置
            InputStream in = utils.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(in);

            // 创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        // 获取连接
        // 其他事项由DBCP帮我们完成了
        return dataSource.getConnection();
    }

    public static void relese(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (conn != null) {
            conn.close();
        }

    }

}