数据库：（DB，DateBase）
概念：数据仓库，软件，安装在操作系统上，SQL,可以储存大量的数据，500万。
作用：存储数据，管理数据
数据库分类：
关系型数据库：（SQL）
非关系型数据库：（No SQL）
DBMS（数据库管理系统）
MySQL简介：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
连接数据库
命令行连接（cmd）
mysql -uroot -p --连接数据库
ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘新密码‘; --更改密码
flush privileges; --刷新权限
-------------------------------------------------------
--所有的语句使用‘;‘结尾
show databases; --查看所有的数据库
use xxx --切换数据库
show tables; --查看数据库中所有的表
describe xxx; --显示数据库中所有表的信息
create database xxx; --创建一个数据库
exit； --退出连接
-- (单行注释)
/*
xxx (多行注释)
*/
数据库语言
操作数据库>操作数据库中的表>操作数据库表中的数据
Mysql关键字不区分大小写
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] XXX;
2、删除数据库
DROP DATABASE [IF EXISTS] XXX;
3、使用数据库
USE XXX --如果是特殊字符则需要``
4、查看数据库
SHOW DATABASES; --查看所有数据库
数值
字符串
时间日期
java.util.Date
? null
Unsigned:
zerofill：
自增：
非空 NOT NULL
默认
每一个表，都要存在五个字段：
/*
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_creat 创建时间
gmt_update 修改时间
*/
-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL创建
-- 学号int 登陆密码varchar(16) 姓名，性别varchar(2)
-- 出生日期(datetime) 家庭住址，email
-- 字符串用‘‘，所有语句后面加‘,‘ 最后一个不用加
USE school
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
`name` VARCHAR(10) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
`password` VARCHAR(16) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
`sex` VARCHAR(2) NOT NULL DEFAULT ‘男‘ COMMENT ‘性别‘,
`birthday` DATETIME DEFAULT NULL COMMENT ‘出生日期‘,
`address` VARCHAR(50) DEFAULT NULL COMMENT ‘家庭住址‘,
`email` VARCHAR(20) DEFAULT NULL COMMENT ‘邮箱‘,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
SHOW CREATE DATABASE XXX; --查看创建数据库的语句
SHOW CREATE TABLE XXX; --查看创建表的语句
DESC XXX; --显示表的结构
--关于数据库引擎
/*
INNODB 默认使用
MYISAM 以前使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持（全英文支持） |
| 表空间的大小 | 较小 | 较大，约为2倍 |
常规使用操作：
? 在物理空间存在的位置
所有的数据库文件都存在data目录下 一个文件夹对应一个数据库
本质还是文件的存储
MySQL引擎在物理文件上的区别
? 设置数据库表的字符集编码
CHARSET=utf8
? 修改
-- 修改表名
ALTER TABLE student RENAME AS class
-- 增加表的字段 列属性
ALTER TABLE class ADD age INT(11)
-- 修改表的字段（重命名，修改约束）
ALTER TABLE class MODIFY age VARCHAR(10) -- 修改约束
ALTER TABLE class CHANGE age agee INT(1) -- 字段重命名
-- 删除表的字段
ALTER TABLE class DROP age
? 删除
-- 删除表
DROP TABLE IF EXISTS class
所有的创建和删除尽量加上判断，以免报错
? 方式一、在创建表的时候增加约束（麻烦复杂）
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT‘年级id‘,
`gradename` VARCHAR(10) NOT NULL COMMENT ‘年级名称‘,
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的gradeid字段 要去引用grade表中的gradeid
-- 定义外键key
-- 给这个外键添加约束 （执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
`name` VARCHAR(10) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
`password` VARCHAR(16) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
`sex` VARCHAR(2) NOT NULL DEFAULT ‘男‘ COMMENT ‘性别‘,
`birthday` DATETIME DEFAULT NULL COMMENT ‘出生日期‘,
`address` VARCHAR(50) DEFAULT NULL COMMENT ‘家庭住址‘,
`email` VARCHAR(20) DEFAULT NULL COMMENT ‘邮箱‘,
`gradeid` INT(10) NOT NULL ,
PRIMARY KEY(`id`),
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);
以上的操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）
最佳实践
- 插入数据(添加)
-- INSERT INTO 表名 ([字段名1，字段名2...])values (‘值1‘),(‘值2‘)...
INSERT INTO `grade`(`gradename`) VALUES(‘大一‘)
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES(‘大二‘),(‘大三‘)
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(‘4‘,‘大四‘)
? update
UPDATE `grade` SET `gradename`=‘硕士‘，‘‘ WHERE `gradeid`=1
-- 若没有where，则会改变所有的值
? delete命令
delete from 表名 [where 条件]
-- 删除指定数据
DELETE FROM `grade` WHERE `gradeid`=1
? TRUNCATE 命令
作用：完全清空一个数据库表，表的结构和索引约束不会变
TRUNCATE `grade`
? truncate 和 delete 的区别
了解：detele删除的问题，重启数据库，现象：
Date Query Language：数据查询语言
-- 查询全部的科目
SELECT * FROM `subject`
-- 查询指定字段
SELECT `subjectname` FROM `subject`
-- 给结果起一个名字 也可以给表起别名
SELECT `subjectname` AS 学科 FROM `subject`
-- 函数 Concat(a,b) 拼接字符串
SELECT CONCAT(‘学科：‘,subjectname) AS 新名字 FROM `subject`
去重 distinct
-- 发现重复数据，去重
SELECT DISTINCT `classhour` FROM `subject`
? 数据库的列（表达式）
--查询系统版本
SELECT VERSION()
-- 学号+1 再查看
SELECT `subjectno`+1,`subjectname` AS ‘号码‘ FROM `subject`
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量....
作用：检索数据中符合条件的值
? 逻辑运算符
? 模糊查询：比较运算符
% ：代表0到任意个字符
_ ：代表一个字符
Like
-- c后任意位
SELECT `subjectname`,`gradeid` FROM `subject`
WHERE `subjectname` LIKE ‘c%‘
-- c后有两位
SELECT `subjectname`,`gradeid` FROM `subject`
WHERE `subjectname` LIKE ‘c___‘
in(具体的值)
SELECT `subjectname`,`gradeid` FROM `subject`
WHERE `gradeid` IN (1,2)
? JOIN
创建一个a表与b表
CREATE DATABASE `test`
CREATE TABLE `a`(
`id` INT(2),
`name` VARCHAR(10),
PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
CREATE TABLE `b`(
`id` INT(2),
`age` INT(2),
PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
UPDATE `a` SET `name`=‘ming‘ WHERE `id` = 1
UPDATE `a` SET `name`=‘tian‘ WHERE `id` = 2
INSERT INTO `b`(`id`,`age`) VALUES(‘1‘,‘12‘),(‘5‘,‘23‘)
a表：
b表：
-- 左连接
SELECT * FROM a LEFT JOIN b ON a.`id`=b.`id`
-- 右连接
SELECT * FROM a RIGHT JOIN b ON a.`id`=b.`id`
-- 左连接之后去除共有部分
SELECT * FROM a LEFT JOIN b ON a.`id`=b.`id` WHERE b.`id` IS NULL
-- 内连接
SELECT * FROM a INNER JOIN b ON a.`id`=b.`id`
-- 右连接之后去除共有部分
SELECT * FROM a RIGHT JOIN b ON a.id = b.id WHERE a.id IS NULL
-- 全连接
SELECT * FROM a LEFT JOIN b ON a.id = b.id UNION SELECT * FROM a RIGHT JOIN b ON a.id = b.id
-- 由于mysql不支持全连接写法，所以使用上面的sql语句替代下面的全连接sql语句：
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id(mysql不支持)
-- 全连接之后去除共有部分
SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL UNION SELECT * FROM a RIGHT JOIN b ON a.id = b.id WHERE a.id IS NULL
-- 由于mysql不支持全连接写法，所以使用上面的sql语句替代下面的全连接sql语句：
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL(mysql不支持)
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
demo：
CREATE TABLE `school`.`category`( `categoryid` INT(3) NOT NULL COMMENT ‘id‘, `pid` INT(3) NOT NULL COMMENT ‘父id 没有父则为1‘, `categoryname` VARCHAR(10) NOT NULL COMMENT ‘种类名字‘, PRIMARY KEY (`categoryid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES (‘2‘, ‘1‘, ‘信息技术‘);
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values (‘3‘, ‘1‘, ‘软件开发‘);
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values (‘5‘, ‘1‘, ‘美术设计‘);
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES (‘4‘, ‘3‘, ‘数据库‘);
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values (‘8‘, ‘2‘, ‘办公信息‘);
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values (‘6‘, ‘3‘, ‘web开发‘);
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS (‘7‘, ‘5‘, ‘ps技术‘);
SELECT a.`categoryname` AS ‘父栏目‘,b.`categoryname` AS ‘子栏目‘
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`
排序：ASC为升序，DESC为降序
SELECT * FROM `subject` ORDER BY `gradeid` ASC
SELECT * FROM `subject` ORDER BY `gradeid` DESC
分页：缓解数据库压力，体验更好
-- limit 起始值，页面的大小
-- pageSize:页面大小，
-- (n-1)*pageSize：起始值，
-- n：当前页
-- 数据总数/页面大小=总页数
where (这个值是计算出来的)
本质：在where语句中嵌套一个子查询语句
SELECT DISTINCT s.StudentNo,StudentName
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult >=80 AND SubjectNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName = ‘高等数学‘
)
GROUP BY
HAVING
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 0~1之间随机数
SELECT SIGN(10) -- 返回数的符号，负为-1，正为1，0=0
-- 字符串函数
SELECT CHAR_LENGTH(‘waiting for tomorrow‘)-- 字符串长度
SELECT CONCAT(‘你‘,‘好‘) -- 拼接字符串
SELECT INSERT(‘rains keep falling‘,1,5,‘tears‘) -- 插入(可以替换)字符串，str,pos,len,str
SELECT LOWER(‘WHO AM I‘) -- 转小写
SELECT UPPER(‘you decide‘) -- 转大写
SELECT INSTR(‘juice‘,‘i‘) -- 返回第一次出现的子串的索引
SELECT REPLACE(‘hate the other side‘,‘hate‘,‘love‘) -- 替换指定字符串
SELECT SUBSTR(‘waiting for tomorrow‘,1,7) -- 返回指定的字符串 str,poi,len
SELECT REVERSE(‘ko‘) -- 反转字符串
-- 时间和日期函数
SELECT CURRENT_DATE()
SELECT CURDATE() -- 当前日期
SELECT NOW() -- 当前具体时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| ... | ... |
SELECT COUNT(subjectname) FROM `subject`; -- Count(指定列)，会忽略所有的null
SELECT COUNT(*) FROM `subject`; -- Count(*)，不会忽略null
SELECT COUNT(1) FROM `subject`; -- Count(1)，不会忽略null
SELECT SUM(classhour) AS 总和 FROM `subject`
SELECT AVG(classhour) AS 平均 FROM `subject`
SELECT MAX(classhour) AS 最大 FROM `subject`
SELECT MIN(classhour) AS 最小 FROM `subject`
MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由美国密码学家罗纳德·李维斯特（Ronald Linn Rivest）设计，于1992年公开，用以取代MD4算法。这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。
MD5不可逆，具体的MD5值是一样的
MD5破解网站的原理，有一个字典
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(10) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,‘zhangsan‘,‘123456‘),(2,‘lisi‘,‘123456‘),(3,‘wangwu‘,‘123456‘)
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,‘xiaoming‘,MD5(‘123456‘))
? 事务原则：ACID原则 ：原子性，一致性，隔离性，持久性 （脏读，幻读...）
一、原子性（atomicity)
一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性
二、一致性（consistency)
事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态
三、隔离性（isolation）
事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。
在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：未授权读取，授权读取，可重复读取和串行化
1、读未提交（Read Uncommited），该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取
2、授权读取也称为已提交读（Read Commited），授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。
3、可重复读（Repeatable Read)
就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20；
4、串行化
是最严格的事务隔离级别，它要求所有事务被串行执行，即事务只能一个接一个的进行处理，不能并发执行。
四、持久性（durability）
一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。--即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态
? 执行事务
-- mysql是默认开启事务自动提交的
SET autocommit = 0 /*关闭*/
SET autocommit = 1 /*开启，默认*/
-- 手动处理事务
SET autocommit = 0 -- 关闭自动条件
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql就在同一个事务内
-- 提交：持久化
COMMIT
-- 回滚：回到原来的样子
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT -- 撤销保存点
? 模拟转账场景
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL, /* 9位有效数字,小数点后2位*/
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `account`(`name`,`money`)
VALUES (‘A‘,2000.00),(‘B‘,10000.00)
-- 模拟转账
SET autocommit = 0
START TRANSACTION -- （一组事务）
UPDATE account SET money=money-500 WHERE `name`=‘A‘ -- A减500
UPDATE account SET money=money+500 WHERE `name`=‘B‘ -- B加500
COMMIT -- 提交 持久化！
ROLLBACK -- 回滚
SET autocommit = 1 -- 恢复默认值
索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
创建索引
普通索引
ALTER TABLE 表名 ADD INDEX 索引名 (column_list);
唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名(column_list);
主键索引
ALTER TABLE 表名 ADD PRIMARY KEY 索引名(column_list);
全文索引(字符串数据类型)
ALTER TABLE 表名 ADD fulltext 索引名(column_list);
查看索引
show index from student \G
这里用\G 代替分号结束符；可以格式化输出数据信息。
删除索引
① 修改表的方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
② 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
索引列为一列时为单一索引；
多个列组合在一起创建的索引叫做联合索引
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = ‘app用户表‘
--------------------------------------------------------------------------
-- 插入一百万数据
DELIMITER $$ -- 写函数前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT(‘用户‘,i),‘897009391@qq.com‘,CONCAT(‘19‘,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‘ -- 总耗时 : 0.4 sec
-- id_表名_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM app_user WHERE `name` = ‘用户9999‘ -- 总耗时 : 0.001 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = ‘用户9999‘
1. 选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
2. 为经常需要排序、分组和联合操作的字段建立索引
经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3. 为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
4. 限制索引的数目
索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。
如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。
5. 尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。
6. 数据量小的表最好不要使用索引
由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
7. 尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
8. 删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
? 索引的数据结构
本质是对MySql.user表的修改
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY ‘密码‘
CREATE USER XXX IDENTIFIED BY ‘123456‘
-- 修改密码（当前用户）
SET PASSWORD = PASSWORD(‘11111111‘)
-- 修改密码（指定用户）
SET PASSWORD FOR XXX = PASSWORD(‘111111111‘)
-- 重命名
RENAME USER XXX TO YYY
-- 用户授权 全部权限,库.表
GRANT ALL PRIVILEGES ON *.* TO XXX
-- 查询权限
SHOW GRANTS FOR XXX
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM XXX
-- 删除用户
DROP USER XXX
备份方式：
直接拷贝物理文件
在可视化工具中手动导出
使用命令行 mysqldump (cmd)
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表2 表3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# 导入 ， 登录状态下，切换到指定数据库
source 备份文件
规范
MYSQL数据库设计规范
1、数据库命名规范
采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线‘_‘组成;
命名简洁明确(长度不能超过30个字符);
例如：user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;
除非是备份数据库可以加0-9的自然数：user_db_20151210;
2、数据库表名命名规范
采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线‘_‘组成;
命名简洁明确,多个单词用下划线‘_‘分隔;
例如：user_login, user_profile, user_detail, user_role, user_role_relation,
user_role_right, user_role_right_relation
表前缀‘user_‘可以有效的把相同关系的表显示在一起;
3、数据库表字段名命名规范
采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线‘_‘组成;
命名简洁明确,多个单词用下划线‘_‘分隔;
例如：user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;
每个表中必须有自增主键,add_time(默认系统时间)
表与表之间的相关联字段名称要求尽可能的相同;
4、数据库表字段类型规范
用尽量少的存储空间来存数一个字段的数据;
例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);
IP地址最好使用int类型;
固定长度的类型最好使用char,例如：邮编;
能使用tinyint就不要使用smallint,int;
最好给每个字段一个默认值,最好不能为null;
5、数据库表索引规范
命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;
为每个表创建一个主键索引;
为每个表创建合理的索引;
建立复合索引请慎重;
6、简单熟悉数据库范式
第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);
例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;
第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;
备注：必须先满足第一范式;
第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段;
备注：必须先满足第二范式;
三大范式
第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式); 例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段; 第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分; 备注：必须先满足第一范式; 第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段; 备注：必须先满足第二范式;
原则
1、核心原则
不在数据库做运算;
cpu计算务必移至业务层;
控制列数量(字段少而精,字段数建议在20以内);
平衡范式与冗余(效率优先；往往牺牲范式)
拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch);
2、字段类原则
用好数值类型(用合适的字段类型节约空间);
字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
少用text类型(尽量使用varchar代替text字段);
3、索引类原则
合理使用索引(改善查询,减慢更新,索引一定不是越多越好);
字符字段必须建前缀索引;
不在索引做列运算;
innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);
不用外键(由程序保证约束);
4、sql类原则
sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);
简单的事务;
避免使用trig/func(触发器、函数不用客户端程序取而代之);
不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);
OR改写为IN(or的效率是n级别);
OR改写为UNION(mysql的索引合并很弱智);
select id from t where phone = ’159′ or name = ‘john’;
=>
select id from t where phone=’159′
union
select id from t where name=’jonh’
避免负向%;
慎用count(*);
limit高效分页(limit越大，效率越低);
使用union all替代union(union有去重开销);
少用连接join;
使用group by;
请使用同类型比较;
打散批量更新;
5、性能分析工具
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
程序会通过数据库驱动和数据库连接
Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
在mysql官网下载 mysql.connector.jar
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
email VARCHAR(60),
birthday DATE
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,‘zhangsan‘,‘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‘)
package com.X;
import java.sql.*;
public class jdbc01 {
//1、加载驱动
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动
//2、用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?Unicode=true & characterEncoding=utf8 & useSSL=true";
String username = "root";
String password = "123456";
//3、连接成功，数据库对象 connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4、Statement执行sql的对象
Statement statement = connection.createStatement();
//5、执行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("pwd="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("====================================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一行
resultSet.previous();//移动到上一行
resultSet.absolute(1);//移动到具体指定行
jdbc中的statement对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可；
Statement对象的executeUpdate()方法，用于向数据库发送增、删、改的sql语句，执行完后，将会返回一个整数（几行数据发生了变化）
Statement.executeQuery()方法用于向数据库发送查询语句，返回ResultSet对象
1、在src下新建文件db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?Unicode=true & characterEncoding=utf8 & useSSL=true
username=root
password=123456
2、新建utils文件夹，新建JdbcUtils.java
package com.X.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
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("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");
//1、驱动只用加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试：
package com.X;
import com.X.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" + "VALUES(4,‘X‘,‘123456‘,‘897009391@qq.com‘,‘2021-4-16‘)";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
示例：
package com.X;
import com.X.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLInjection {
public static void main(String[] args) {
login(" ‘or ‘1=1"," ‘or ‘1=1");
}
public static void login(String username, String password){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT * FROM users WHERE `NAME`=‘"+username+"‘ AND `password`=‘"+password+"‘";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("NAME"));
System.out.println(resultSet.getString("PASSWORD"));
System.out.println("======================================");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
preparedStatement可以防止sql注入，并且效率更高
preparedStatement用法
package com.X;
import com.X.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test2 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//使用‘？’占位符代替参数
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);//预编译sql，先写sql，不执行
//手动给参数赋值
preparedStatement.setInt(1,4);
preparedStatement.setString(2,"X");
preparedStatement.setString(3,"666666");
preparedStatement.setString(4,"897009391@qq.com");
preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
mysql数据库产商，在实现PreparedStatement接口的实现类中的setString(int parameterIndex, String x)函数中做了一些处理，把单引号做了转义(只要用户输入的字符串中有单引号，那mysql数据库产商的setString()这个函数，就会把单引号做转义)
防止sql注入示例
package com.X;
import com.X.utils.JdbcUtils;
import java.sql.*;
public class SQLInjection {
public static void main(String[] args) {
login(" ‘or ‘1=1"," ‘or ‘1=1");
}
public static void login(String username, String password){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "SELECT * FROM users WHERE `NAME`=? AND `password`=?";//Mybatis
statement = connection.prepareStatement(sql);
statement.setString(1,username);
statement.setString(2,password);
resultSet = statement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("NAME"));
System.out.println(resultSet.getString("PASSWORD"));
System.out.println("======================================");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
package com.X;
import com.X.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//关闭数据库的自动提交，自动会开启事务
connection.setAutoCommit(false);
String sql1 = "update account set money = money-100 where name = ‘A‘";
PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement1.executeUpdate();
String sql2 = "update account set money = money+100 where name = ‘B‘";
PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
preparedStatement2.executeUpdate();
//提交事务
connection.commit();
System.out.println("提交成功");
} catch (SQLException e) {
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
数据库连接--执行完毕--释放
这种过程非常浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
编写连接池，实现一个接口 DataSource
? 开源数据源实现
DBCP
C3P0
Druid : 阿里巴巴
下载jar包放在lib下
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了
? DBCP
新建 dbcpcondig.properties文件
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
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
JdbcUtils_DBCP
package com.X.utils_DBCP;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
public static DataSource dataSource = null;
static {
try {
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式-> 创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
test
package com.X;
import com.X.utils_DBCP.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDBCP {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
connection = JdbcUtils_DBCP.getConnection();
statement = connection.createStatement();
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(4,‘X‘,‘123456‘,‘897009391@qq.com‘,‘2021-4-16‘)";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils_DBCP.release(connection,statement,null);
}
}
}
...
原文：https://www.cnblogs.com/PxxxJ/p/14876502.html