MySQL的使用

---

前言

MySQL安装：https://blog.csdn.net/weixin_43287895/article/details/126350052

一、MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。（百度百科）

SQL数据库

分类

关系型数据库（sql）

• ​ Mysql，Oracle，sqlServer，DB2，SQLlite
• 通过表与表之前，行与列之间的关系进行数据的存储

非关系型数据库（NoSQL，no only）

• ​ Redis，MongoDB
• 非关系数据库，对象存储，通过对象的自身属性来决定

其他概念

DBMS数据库管理系统

数据库管理软件，科学有效的管理我们的数据，维护和获取数据。
像MySQL就是关系型数据库管理系统

SQL语句执行顺序

来源于其他文章

from->
on->
join->
where->
group by（开始使⽤select中的别名，后⾯的语句中都可以使⽤别名）->
sum、count、max、avg->
having->
select->
distinct->
order by->
limit

1、FROM 执行笛卡尔积
FROM 才是 SQL 语句执行的第一步，并非 SELECT 。对FROM子句中的前两个表执行笛卡尔积(交叉联接），生成虚拟表VT1，获取不同数据源的数据集。
FROM子句执行顺序为从后往前、从右到左，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，即最后的表为驱动表，当FROM 子句中包含多个表的情况下，我们需要选择数据最少的表作为基础表。
2、ON 应用ON过滤器
对虚拟表VT1 应用ON筛选器，ON 中的逻辑表达式将应用到虚拟表 VT1中的各个行，筛选出满足ON 逻辑表达式的行，生成虚拟表 VT2 。
3、JOIN 添加外部行
如果指定了OUTER JOIN保留表中未找到匹配的行将作为外部行添加到虚拟表 VT2，生成虚拟表 VT3。保留表如下：

• LEFT OUTER JOIN把左表记为保留表
• RIGHT OUTER JOIN把右表记为保留表
• FULL OUTER JOIN把左右表都作为保留表
  在虚拟表 VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表 VT3。
  如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1~3，直到处理完所有的表为止。

4、WHERE 应用WEHRE过滤器
对虚拟表 VT3应用WHERE筛选器。根据指定的条件对数据进行筛选，并把满足的数据插入虚拟表 VT4。

• 由于数据还没有分组，因此现在还不能在WHERE过滤器中使用聚合函数对分组统计的过滤。
• 同时，由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的。

5、GROUP BY 分组
按GROUP BY子句中的列/列表将虚拟表 VT4中的行唯一的值组合成为一组，生成虚拟表VT5。如果应用了GROUP BY，那么后面的所有步骤都只能得到的虚拟表VT5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。
同时，从这一步开始，后面的语句中都可以使用SELECT中的别名。
6、AGG_FUNC 计算聚合函数
计算 max 等聚合函数。SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。常用的 Aggregate 函数包涵以下几种：

• AVG：返回平均值
• COUNT：返回行数
• FIRST：返回第一个记录的值
• LAST：返回最后一个记录的值
• MAX： 返回最大值
• MIN：返回最小值
• SUM： 返回总和

7、WITH 应用ROLLUP或CUBE
对虚拟表 VT5应用ROLLUP或CUBE选项，生成虚拟表 VT6。
CUBE 和 ROLLUP 区别如下：

• CUBE 生成的结果数据集显示了所选列中值的所有组合的聚合。
• ROLLUP 生成的结果数据集显示了所选列中值的某一层次结构的聚合。

8、HAVING 应用HAVING过滤器
对虚拟表VT6应用HAVING筛选器。根据指定的条件对数据进行筛选，并把满足的数据插入虚拟表VT7。
HAVING 语句在SQL中的主要作用与WHERE语句作用是相同的，但是HAVING是过滤聚合值，在 SQL 中增加 HAVING 子句原因就是，WHERE 关键字无法与聚合函数一起使用，HAVING子句主要和GROUP BY子句配合使用。
9、SELECT 选出指定列
将虚拟表 VT7中的在SELECT中出现的列筛选出来，并对字段进行处理，计算SELECT子句中的表达式，产生虚拟表 VT8。
10、DISTINCT 行去重
将重复的行从虚拟表 VT8中移除，产生虚拟表 VT9。DISTINCT用来删除重复行，只保留唯一的。
11、ORDER BY 排列
将虚拟表 VT9中的行按ORDER BY 子句中的列/列表排序，生成游标 VC10 ，注意不是虚拟表。因此使用 ORDER BY 子句查询不能应用于表达式。同时，ORDER BY子句的执行顺序为从左到右排序，是非常消耗资源的。
12、LIMIT/OFFSET 指定返回行
从VC10的开始处选择指定数量行，生成虚拟表 VT11，并返回调用者。

数据库的列类型

数值

数据类型	描述
tinyint	十分小的数据，一个字节大小
smallint	较小的数据，两个字节大小
int	标准整数，四个字节大小（常用），int（3），后面的三是显示范围（002），与数据范围无关，如果添加1000，也是能添加的
mediumint	中等大小的数据，三个字节大小
bigint	较大的数据，八个字节大小
float	单精度浮点数，四个字节
double	双进度浮点数，八个字节
decimal(位数，小数点个数)	字符串形式的浮点数，金融计算的时候一般使用
smallint	较小的数据，两个字节大小

字符串

数据类型	描述
char	字符串固定大小0-255
varchar	可变长字符串0-65535（常用）
tinytext	微型文本，大小2^8-1
mediumint	中等大小的数据，三个字节大小
text	文本串，大小2^16-1

时间日期

数据类型	描述
date	yyyy-MM-dd，日期格式
time	HH:mm:ss，时间格式，小写h是12小时制，大写H是24小时制
datetime	yyyy-MM-dd HH:mm:ss 常用的时间格式
timestamp	时间戳，1970/1/1到现在的毫秒数，较为常用
year	年份表示

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

字段属性

数据类型	描述
unsigned	无符号整数，不能声明为负数
zerofill	0填充的，不足的位数使用0来填充，int（3），001
自增	通常理解为自增，自动在上一条的基础上+1，用来设置唯一的主键，必须是整数类型，可以自定义设计主键自增的起始值和步长
非空	假设设置为非空，如果不赋值，就会报错，如果不设置，值空，默认为null
默认	设置默认值，如sex，默认为男，除非手动设置为女，如果不指定该列的值，默认为男

规范

表中必须需要有的字段

1. id，主键
2. version，用于进行乐观锁的判定
3. is_delete 伪删除，当值为0时表示存在，当值为1时表示删除，实际应用就是数据是很珍贵的，删除就无法找回了，所以一般不会进行数据的删除，而是将他隐藏起来。
4. gmt_creat 这条记录的创建时间
5. gmt_update 这个记录的修改时间

数据库语言

数据库语言一共分为四种

• DDL 数据库定义语言
• DML 数据库操作语言
• DQL 数据库查询语言
• DCL 数据库控制语言

DDL 数据库定义语言

DDL是用来创建数据库中的表、视图、索引等表的名称和字段，常用的语句关键字主要包括 create、drop、alter等
尽量使用单引号，防止关键字问题字符串使用单引号
所有的语句后面加逗号(英文)，最后一个不用加
主键设置加在最后面，主键一般一个表只有一个

CREATE TABLE IF NOT EXISTS `student` (
`id` int(3) NOT NULL COMMENT '学号',
`name` varchar(20) 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 '家庭地址',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT charset=utf8

一般来讲如果拿到别人的数据库可以先使用DDL语言，来进行学习

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

操作数据库

======查询所有数据库======
SHOW DATABASES;
======查询当前数据库======
SELECT DATABASE();
======创建数据库======
CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名;
======删除数据库======
DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;
======使用数据库======
USE 数据库名;
======展示创建数据的语句======
SHOW CREATE DATABASE 数据库名;

操作数据表

======查询当前数据库======
SHOW TABLES;
======显示表的结构======
DESC 表名称;
======展示创建表的语句======
SHOW CREATE TABLE 表名;
======创建表======
CREATE TABLE [IF NOT EXISTS] '表名'(
    '字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    PRIMARY KEY('字段')
)ENGINE=INNODB DEFAULT CHARSET=utf8

======删除表======
DROP TABLE IF EXISTS 表名;
======修改表名======
ALTER TABLE student RENAME AS student1
======增加表的字段======
ALTER TABLE 表 ADD test 属性
ALTER TABLE student ADD test INT(11)
======修改表的字段======
	-- 重命名(只修改列属性)
	ALTER TABLE student MODIFY age VARCHAR(11)
	-- 修改约束(不仅可以修改名字，还可以修改列属性)
	ALTER TABLE student CHANGE age age1 INT(3)
======删除表的字段======
ALTER TABLE 表 DROP 字段
ALTER TABLE student DROP age1

DML 数据库操作语言

数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 。(增添改）

insert插入

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

insert into `grade`(`gradename`)VALUES('小学'),('中学')

update修改

UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

UPDATE `student` set `name`='haa' where id = 1;
-- 增加条件只会修改，符合条件的行
UPDATE `student` set `name`='haa'
-- 不添加条件会修改，所有的行
UPDATE `student` set `birthday`=CURRENT_TIME where id = 1;
UPDATE `student` set `birthday`=SYSDATE() where id = 1;
UPDATE `student` set `birthday`=CURRENT_TIMESTAMP() where id = 1;
-- 修改时间可以用SYSDATE()函数或CURRENT_TIMESTAMP()函数，也可以用变量CURRENT_TIME

delete删除

DELETE FROM 表名 [WHERE 条件]

DELETE FROM student WHERE id=1

delete删除对于不同的数据库引擎，会造成的情况不一样。当数据库重启后：
引擎为InnoDB，自增列会从1开始（因为保存在内存中）
引擎为MyISAM，自增列会从上一个开始（存在文件中的）

删除还有另外一种形式

TRUNCATE `student`
-- 完全清空表，表的结构和索引约束不会变化

相同点

都能删除数据，都不会删除表结构

不同点

TRUNCATE 重新设置自增列，计数器会归零，TRUNCATE执行后无法回滚；delete是DML语句，可回滚。

DQL 数据查询语言

语法结构

select [distinct] [* | 字段] from 表名 as 别名
[left|right|inner join 表名] -- 联表查询
[where ...] -- 判断查询
[group by] -- 分组
[having] -- 过滤分组的记录满足的的次要条件
[order by[asc desc]] -- 指定查询记录排序
[limit 起始值,页面大小pageSize]

select查询

SELECT 查询列表 FROM 表名;

select `id`AS 学号,`name` AS 学生姓名 FROM `student` AS s
-- 将查询的栏目，用别名显示，as可以省略，也可以给表起别名
select CONCAT('学号:',`id`) as 学号,`name` AS 学生姓名 FROM `student`as s
-- 函数Concat(a,b)

去重

去除select查询出来的结果重复的数据，只显示一条

select distinct `id` from student

数据库的列（表达式）

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

查询数据库版本(函数)

--查询数据库版本
select VERSION()

计算(表达式)

select 100*3-8 as 计算结果

查询自增的步长(变量)

select @@auto_increment_increment

整列增加

select `id`,`result`,`result`+1 as 提分 from result

模糊查询

运算符	语法	描述
IS NULL	a is null	如果操作符为null，为真
IS NOT NULL	a is not null	如果操作符不为null，为真
BETWEEN AND	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

-- between
select `id`,`result`,`result`+1 as 提分 from result where `result` between 95 and 100

like

-- like
-- %(代表0到任意个字符)
-- _(代表一个字符)
-- 查询姓 刘 的同学
select `id`,`name`from student where `name`='刘%'
-- 查询姓 刘 且只有2个字的同学，例如刘某
select `id`,`name`from student where `name`='刘_'
-- 查询姓 刘 且只有3个字的同学，例如刘某某
select `id`,`name`from student where `name`='刘__'
-- 查询名字中间有 佳 的同学，例如某佳某，某某佳某
select `id`,`name`from student where `name`='%佳%'

in

-- in (具体的一个或多个值，%_不适用)
-- 查询id是1001,1002,1003的同学
select `id`,`name`from student where `id` in (1001,1002,1003)

联表查询

分析需求，分析查询的字段来自哪几个表
确定使用哪种连接查询
先查询两个，再慢慢添加
3种操作

操作	描述
Inner Join xxx on xxx	查询两个表都存在的，且条件符合的
Right Join xxx on xxx	包含右表所有，且符合条件的
Left Join xxx on xxx	包含左表所有，且符合条件的

七种连接（百度图片上的图）

两种查询方式
一般join on配合使用

-- 连接查询，on是在生成临时表时使用的条件，不管on中是否为真，都会返回左表的记录，还会返回on条件为真的记录
join (连接的表)on(判断的条件)
-- 等值查询，先连接查询出所有符合条件的值，然后where判断筛选
where

-- 查询学员所属的年级（学号，学生姓名，年级）
SELECT `num`,`name`,`gradeName`
FROM student s
INNER JOIN grade g
ON s.gradeid=g.gradeid;
where gradeName='高中';

自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表
意思为：把两张表的数据写成了一张表，当查询的时候需要我们自己把相关的数据自连接查询，才能得到数据，简单来讲就是两张表偷懒成一张表

分页limit

缓解数据库压力，给人的体验更好
limit在所有的语句的最后

select * from result order where gradeId = 1 by stuResult ASC limit 0,5

格式

limit 起始值 , 显示个数pageSize

页面公式

页面中数据个数:pagesize
当前页：n
第n页起始位置：(n-1)*pagesize
总页数：数据总数/页面大小

排序order by

一般where判断后的结尾
升序ASC，ascending

select * from result order where gradeId = 1 by stuResult ASC

降序DESC，descending

select * from result order where gradeId = 1 by stuResult DESC

子查询

例子：where(select * from student)
where(值是固定的，是计算出来的)
本质：在where语句中嵌套一个子查询，也就是说几条sql语句，嵌套为一个语句

SELECT num,subjectNum,stuResult 
from result
WHERE subjectNum=(
	SELECT subjectNum FROM `subject`
	WHERE subjectName='数学'
)

分组group by和having

根据不同科目来的进行分组

select name,AVG(`result`),MAX('result'),MIN('result') from `result` as r
INNER JOIN `subject` sub
ON r.`subjectNo` = sub.`subjectNo`
GROUP BY r.`subjectno`
HAVING AVG(`result`)>80

having子句是筛选满足条件的组，在分组之后进行过滤，条件中常常包含聚合函数
对函数进行过滤需要用having

where条件

检索数据中符合条件的值

操作符，尽量使用英文符号

where会在group by执行之前执行

操作符	含义	范围	结果
=	等于	5=6	true
<>或!=	不等于	5<>6	false
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN AND	在某个范围内	between 2 and 3	[2,5]
AND	和&&	5>1 and 1>2	false
OR	或||	5>1 or 1>2	true
Not	非！	!a

-- not 
select `id`,`result`,`result`+1 as 提分 from result 
where not `result`=100
-- !=
select `id`,`result`,`result`+1 as 提分 from result 
where `result`!=100

常用函数

数学运算

ABS() -- 绝对值	(-8)	8
CEILING() -- 向上取整	(8.4)	9
FLOOR() -- 向下取整		(8.4)	8
RAND() -- 返回0-1之间的随机数
SIGN() -- 判断一个数的符号 		(0)		0	(-10)	-1		(10)	1

字符串函数

CHAR_LENGTH() -- 字符串长度 		('你好')		2
CONCAT() -- 拼接字符串	('1','2','3')
INSERT() -- 插入字符串(替换)	('你好',1,2,'世界')		世界好
LOWER() -- 转换小写
UPPER() -- 转换大写
REPLACE() -- 替换字符串		('你好','你','我')		我好
SUBSTR()  -- 截取字符串		('你好啊',1,2) 	你好	('你好啊',2,2) 好呀
REVERSE() -- 反转字符串

时间和日期函数

current_date() -- 获取当前日期 年月日
curdate() -- 获取当前日期
NOW() -- 获取当前的时间 精确到时分秒
LOCALTIME() -- 本地时间 精确到时分秒
SYSDATE() -- 系统时间 精确到时分秒
YEAR() -- 年 (now()) 
MONTH() -- 月 (now()) 
DAY() -- 日 (now()) 
HOUR() -- 时 (now()) 
MINUTE() -- 分 (now()) 
SECONDE() -- 秒 (now()) 

系统

SYSTEM_USER() -- 获得用户
USER() -- 获得用户
VERSION() -- 版本

聚合函数

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

例子

-- 能够做统计表中的数据
-- 指定列，会忽略所有的null值
select COUNT(studentname) from student
-- * 不会忽略所有的null值
select COUNT(*) from student
-- 1 不会忽略所有的null值
select COUNT(1) from student
-- 当表的数据量大的时候，对表做分析，使用count(1) 比count(*)用时多
-- 列名为主键count(列名)比count(1)快
-- 列名不为主键count(1)比count(列名)快
-- 如果表多个列并且没有主键，则count(1)比count(*)快
-- 如果有主键，则count(主键)的执行效率最高
-- 如果表只有一个字段，则count(*)最优
-- count(1) count(*)都是计算行数

select SUM(`result`) as 总和 from result
select AVG(`result`) as 平均分 from result
select MAX(`result`) as 最高分 from result
select MIN(`result`) as 最低分 from result

函数(不重要)

DELIMITER $$   -- 写函数之前必须写的标志
CREATE 	FUNCTION test_data()
RETURNS INT
BEGIN 
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
			-- 插入语句
			SET i = i +1;
			INSERT INTO 表(值);
	END WHILE;
	RETURN i;
END;

-- 执行
select test_data()

MD5加密

MDK5，信息摘要算法，加密后不可逆，但具体值的MD5是一样的
MDK5破解原理，是有一个字典，MD5加密后的值，复杂的无法破解

-- 明文
INSERT INTO test VALUES(1,'zhangsan','abcdefg')
-- 加密
UPDATE test SET `pwd`=MD5(pwd) where id = 1
-- 插入的时候加密
INSERT INTO test VALUES(1,'LISI',MD5('123456'))
-- 校验，将传进来的数值进行md5加密，在进行比较
SELECT id FROM test WHERE `name`='zhangsan' AND `pwd`=MD5('abcdefg');

DCL数据控制语言

用来管理数据库用户、控制数据库访问权限
可以使用可视化操作来进行用户的增加，权限的更改，出于安全的考虑。
命令方面可以不用学习，了解即可
本质：mysql数据库中的用户表：mysql.user，对用户表来进行修改

-- 创建用户
CREATE USER jin  IDENTIFIED BY '123456'
-- 修改 当前 用户密码
SET PASSWORD = PASSWORD('111111')
-- 修改 指定 用户密码
SET PASSWORD FOR test = PASSWORD('123456')
-- 重命名 RENAME USER 原名字%端口号 TO 新名字%端口号 ，端口号在端口默认为%时不用
RENAME USER test TO test1
-- 授权		授予所有的权限(除了授权的权限) ON 库.表 TO 用户
GRANT ALL PRIVILEGES ON *.* TO test1
-- 查询权限
SHOW GRANT FOR test1
-- 查询root 需要加主机名字
SHOW GRANT FOR root@localhost
-- 撤销权限 ROVOKE哪些权限，在哪个库，给谁撤销
-- ROVOKE 权限 ON 库.表 FROM 用户
ROVOKE ALL PRIVILEGES ON *.* FROM test1
-- 删除用户
DROP USER test1

事务

事务，Tranction
要么都成功，要么都失败
将一组SQL放在一个批次中去执行
最新版本的MYISM支持事务
Mysql是默认自动提交事务的

SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 默认开启
start TRANCTION -- 标记事务的开始
SQL...
-- 回滚 回到原来的样子
ROLLBACK
-- 事务的结束
-- 提交 持久化
COMMIT
SET autocommit = 1 -- 恢复默认值
SAVEPOINT 保存点名-- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名-- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

事务原则ACID

原子性(Atomicity)：几个SQL一起成功，或者一起失败，不会只发生一种情况

一致性(Consistency)：事务操作前，和后，数据，状态一致。

隔离性(Isolation)：针对多个用户进行同时操作，排除其他事务对本次事务的影响

持久性(Durability)：事务结束后的数据不会随着外界变化而消失。如果事务的SQL之间断电了，事务不会提交，会恢复到原状。只要提交了，就会持久化到数据库，不可逆。

四种隔离级别

未提交读（脏读)READ UNCOMMITTED：指一个事务读取了另外一个事务未提交的数据

提交读（不可重复读）READ COMMITTED：在一个事务内读取表的某一行数据，多次读取的结果不同

可重复读（幻读）REPEATABLE READ：在一个事务内读取到了别的事务插入的数据，导致前后读取结果不一样

可串行化 SERIALIZABLE ：最高隔离级别，会在读取的每一行加锁，所以可能导致大量超时和锁争用

隔离级别	脏读	不可重复读	幻读	加锁读
READ UNCOMMITTED （未提交读）	YES	YES	YES	NO
READ COMMITTED （提交读）	NO	YES	YES	NO
REPEATABLE READ （重复读）	NO	NO	YES	NO
SERIALIZABLE （可串行化）	NO	NO	NO	YES

索引

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

提取句子主干，就可以得到索引的本质：索引是数据结构。

索引小数据量的时候，用处不大，但是数据量大的时候非常明显

索引的分类

主键索引

PRIMARY KEY

唯一标识，不可重复，只能有一个列作为主键，不能为空值

-- 第一种
-- 创建表的时候指定主键索引
CREATE TABLE 表名(
  ......
  PRIMARY INDEX (字段名)
)
-- 第二种
-- 修改表结构添加主键索引
ALTER TABLE 表名 PRIMARY INDEX (字段名)

唯一索引

UNIQUE KEY
大致意思为，这个列作为唯一索引，列的值不能重复，而且可以标很多列作为唯一索引，允许有空值

-- 直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名)

-- 创建表的时候指定唯一索引
CREATE TABLE 表名(  
	......
	UNIQUE INDEX [索引名] (字段名)
);  

-- 修改表结构添加唯一索引
ALTER TABLE 名 ADD UNIQUE INDEX [索引名] (字段名)

常规索引

KEY/INDEX

默认的，可以用INDEX/KEY关键字

-- 创建增加一个索引
CREATE INDEX 索引名 on 表名(`字段名`)
-- 修改表结构添加普通索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名)

全文索引

FullText

在特定的数据库引擎下才有，MyISAM，目前应该都有

快速定位数据，只能用于CHAR , VARCHAR , TEXT数据列类型

-- 通过修改增加一个全文索引(索引名) 列名
ALERT TABLE 表名
ADD FULLTEXT INDEX `索引名`(`字段名`)


-- 全文索引特殊的匹配格式
SECLECT * FROM student WHERE MATCH(studentname) AGAINST('李')
-- 分析SQL执行
EXPLAIN SECLECT * FROM student WHERE MATCH(studentname) AGAINST('李')

索引的使用

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

创建表的时候增加索引

CREATE TABLE `student`(	
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生时间',
	`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY (`StudentNo`),
	UNIQUE KEY `IdentityCard` (`IdentityCard`),
	KEY `birthday` (`birthday`)
)ENGINE=INNODB DEFAULT charset=utf8

创建完毕后增加索引

-- 通过修改增加一个全文索引(索引名) 列名
ALERT TABLE school.student 
ADD FULLTEXT INDEX `studentname`(`studentname`)
-- 创建增加一个索引
CREATE INDEX id_表名_字段名 on student(`studentuser`)

原则

经常变动的数据不要加索引

小数据量不需要加索引

索引一般加在常用来查询的字段上

索引的数据结构

InnoDB存储引擎中的B+树即存储了索引又存储了所有数据，
MyISAM中的B+树中只存储了索引的Key值地址，实际的数据存储在其他地方。

数据库用户权限管理

可以使用可视化操作来进行用户的增加，权限的更改，出于安全的考虑。

命令方面可以不用学习，了解即可

本质：mysql数据库中的用户表：mysql.user，对用户表来进行修改

-- 创建用户
CREATE USER jin  IDENTIFIED BY '123456'
-- 修改 当前 用户密码
SET PASSWORD = PASSWORD('111111')
-- 修改 指定 用户密码
SET PASSWORD FOR test = PASSWORD('123456')
-- 重命名 RENAME USER 原名字%端口号 TO 新名字%端口号 ，端口号在端口默认为%时不用
RENAME USER test TO test1
-- 授权		授予所有的权限(除了授权的权限) ON 库.表 TO 用户
GRANT ALL PRIVILEGES ON *.* TO test1
-- 查询权限
SHOW GRANT FOR test1
-- 查询root 需要加主机名字
SHOW GRANT FOR root@localhost
-- 撤销权限 ROVOKE哪些权限，在哪个库，给谁撤销
-- ROVOKE 权限 ON 库.表 FROM 用户
ROVOKE ALL PRIVILEGES ON *.* FROM test1
-- 删除用户
DROP USER test1

数据库备份

目的：数据转移和数据不丢失

MySQL数据库备份的方式

1. 直接复制MySQL目录下的Data文件夹，拷贝物理文件
2. 在可视化工具中手动导出
   1. 在想要的导出的表或者库中，右键SQL转储
   2. 一般转储，结构和数据
3. 使用命令行导出 mysqldump，cmd中

# mysqldump -h主机 -u用户名 -p密码 数据库 1表名 2表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
mysqldump -hlocalhost -uroot -p123456 数据库 表名 >D:/a.sql
# 不带表名就是数据库
# 出现waring就成功

导入数据库

-- 导入
-- 先登录
-- 切换，使用相应的数据库
-- source 备份文件
source D:/a.sql
-- cmd命令行也可以
-- mysql -u用户名 -p密码 库 < 备份文件

三大范式

第一范式

原子性：每一个列不可再分

要求数据库表的每一项都是不可分割的原子数据项，也就是每一项信息不能再次分割

第二范式

前提满足：第一范式

每张表只做一件事情，只描述一件事情

有太多事情的话就需要拆分

第三范式

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

每一列数据与主键相关，不相关的拆分表

规范性和性能问题

关联查询的表不能超过三张表

一般来讲，数据库的性能更加重要，在规范性能的时候，需要考虑规范性

有时会估计增加一些冗余字段，从多表查询变为单表查询

有时会估计增加一些计算列，每次COUNT，时间较长。从大数据量降低为小计算量
降低为小计算量

JDBC

public class JdbcDemo{
    public static void main(String [] arg)throws ClassNotFoundException{
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
		//用户信息和url
       String url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&userSSL=true";
        String username="root";
        String password="123456";
		//连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
		//执行sql的对象
        Statement statement = connection.createStatement();
		//用对象去执行sql，结果，查看返回结果
        String sql = "select * from student";
        ResultSet result = statement.executeQuery(sql)
        while(result.next()){
            //由类型决定
            result.getObject("id");
        }
		//释放连接
        result.close();
        statement.close();
        connection.close();
    }
}

对象

驱动

Class.forName("com.mysql.jdbc.Driver");
// 8.0 以上com.mysql.cj.jdbc.Driver
// Driver类是一个静态代码块，只需要加载类就可以了，所以用反射实现，默认进行类初始化
//若不使用反射，需要
//DriverManager.registerDriver(new Driver());
//显得多余

url

String url='jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&userSSL=true';
//mysql默认3306
//mysql写法
//jdbc:mysql://主机地址:3306/数据库命?参数1&参数2
//oralce默认1521
//jdbc:oracle:thin:@localhost:1521:表
//oracle没有库，只有表

Connection

Connection connection = DriverManager.getConnection(url,username,password);
//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚

Statement

Statement statement = connection.createStatement();
//执行sql对象
//执行查询excuteQuery返回结果集
//执行更新插入删除excuteUpdate，返回受影响的个数
//excuteBatch执行多个sql
connection.getPrepareStatement()

ResultSet

ResultSet result = statement.executeQuery(sql)
    //查询的结果集，封装了所有的查询类型
    getObject，getInt，...
    //是个指针，可以向前向后
    absolute(row)//指定行
    beforeFirst()//最前面
    afterLast()//最后面
    next()//下一个
    previous()//上一个

释放资源

result.close();
statement.close();
connection.close();

Statement

不安全，有可能SQL注入

用于向数据库发送Sql

增删改，用excuteUpdate

查用，excuteQuery

PreparedStatement

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

预编译SQL

防止SQL注入的本质，把传入的参数当做字符，如果其中转义字符就直接转义，比如’ ’

使用？占位符代替参数

String sql = "insert into user(id,`name`) values(?,?,?,?)"
    PrepareStatement st = conn.prepareStatement(sql);
    st.setInt(1,2)//给ID赋值，以此类推，，第一个参数，值
    st.executeUpdate()//不需要传参，增删改
    st.excuteQuery()//查

PreparedStatement是一个接口，他是怎么执行方法的？

CallableStatement

用来调用SQL中的编写的自定义函数，几乎不用

SQL注入问题

在原来的SQL上添加额外的SQL，在管理员不知道的情况下欺骗数据库执行

String sql = 'select * from user where name='+name+"'";
//但是如果调用的时候加上
//" '  or '1 =1 "
//"' or 1 #"注释掉后面的条件
//变成  select * from user where name='' or '1=1'
//就会出现SQL注入问题

数据库连接池

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

连接----释放十分浪费系统资源

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

最小连接数：常用连接数

最大连接数：最大业务上限

超过最大连接数会排队等待

等待超时：超时多少秒后，断掉，或者有异常

编写连接池，实现DataSource接口

开源数据源实现：DBCP，C3P0，Druid

使用了这些数据库连接池后，在项目开发中不需要编写连接数据库代码

DBCP

需要导入jar包

common-dbcp

common-pool

需要properties配置

C3P0

需要导入jar包

c3p0

mchange-commons-java

需要xml配置

数据库引擎

INNODB

默认使用

好处：安全性高，支持事务的处理，多表多用户操作

MYISAM

早些年使用

好处：节约空间，速度较快

区别

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

数据行锁定：2个sql查同一个表，会把表现锁住

数据库存的位置

在物理空间存在的位置，数据库文件都存在data目录下，一个文件夹对应一个数据库

本质还是文件存储

MySQL引擎区别

• INNODB

  在数据库表中只有一个*.frm，以及上级目录下的ibdata1文件

• MYISAM

  *.frm 表结构的定义文件

  *.MYD 数据文件

  *.MYI 索引文件

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

1.在创建表的时候加上

CHARSET=utf8

2.在my.ini文件中配置默认编码(不建议)
因为这是在你电脑上配置的编码，别人的数据库可能没有配置这个，创建表的时候就会发生编码问题

character-set-server=utf8

不设置，会是MySQL默认的字符集编码Latin1，不支持中文