前言：

Java后端 第一步 认识SQL

1.认识SQL

1.1mysql 启动方法

mysql 环境变量配置正确后，要使用管理员身份启动 cmd

cmd1 :

net start mysql
service mysql restart

cmd2 :

mysql -u root -p
mysql -u root -p****** --或者直接输入密码

cmd3 : pwd = mysql_native_password

更改环境变量后配置MySQL

1. 把当前MySQL目录\bin添加到系统变量，非用户变量

2. 进入mysql基础目录，修改 my.ini 文件，base,data目录均修改，可以不跳过密码检查

3. 管理员模式进入cmd，\bin目录下 mysqld -install

   

4. \bin目录下 net start mysql

5.mysql -uroot -p**

可能需要重复配置比较费力，不建议改变原始路径

1.2 sql 基本语句

1.2.1基本操作

sc delete mysql    --删除所有配置 慎用
create database xxx ; --创建一个数据库
drop database xxxxx ;  --删除数据库的所有内容,包括原数据库
show databases;     --查看所有的数据库
use xxxxxx; --切换xxxxxx数据库
show tables;  --查看数据库中所有的表
describe xxx  ;  --查看xxxxxx数据库中xxx表单的信息

sqlyog operations : 选中ctrl+a---执行语句 左上角按钮执行

飘号引用 sqlName.tableName

1.2.2创建表单 主键必须非空

CREATE TABLE `school`.`techer` ( `id` INT(10) NOT NULL COMMENT '教职工号', `name` VARCHAR(10) COMMENT '姓名', `age` INT(3) COMMENT '年龄', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
----初始化一个数据库的表形状

学习sql语法妙招:先用GUI 然后看GUI操作后的sql语句

1.3 sql基本数据类型

sql数值类型

tinyint   1 bit
smallint  2 bit 
mediumint 3 bit
int       4 bit   
bigint    8 bit
float     8 bit
decimal	  字符型串浮点数 精确,精确，商用

sql字符串类型

char 				0~255
varchar 可变字符 	0~65535 
tinytext 			2^8-1
text 				2^16-1

sql时间日期

date YYYY-MM-DD 
time HH:mm:ss
datetime YYYY-MMM-DD HH:mm:ss 
timestamp 1970.1.1-now 
year

sql null

无值 null 运算 null

1.4 sql字段属性

Unsigned :

• 无符号整数
• 声明非负

zerofill :

• 不足的位数用0补全到 限定位数 eg.INT(10) 1->0000000001

自增

• 自动在上一条记录上+1
• 唯一的主键 index 要子增 整数类型

非空 :

NULL

NOT NULL :如果不赋值 报错

1.5 sql表单语句

show create database school  --查看创建数据库语句
show create table student --查看 student 数据表的定义语句
desc student --显示表的结构

1.6 sql 中ENGINE INNODB和MYISAM 的区别

物理空间的存储位置 :

所有的数据库文件都存在data目录下

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

CHARSET=utf8

不设置的情况，会是mysql默认的字符集编码 （不支持中文）

MySQL 的默认编码是 Latin1

在my.ini 中配置 默认的编码

character-set-server=utf8

1.7 sql的表单修改 (修改范式)

1.7.1表单内操作

改名规则 ：

ALTER TABLE 旧表名 RENAME AS 新表名

添加规则 :

ALTER TABLE 表名 ADD 字段名 列属性 [属性]

修改规则 :

ALTER TABLE 表名 MODIFY 字段名 列属性[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除规则（删除列） :

ALTER TABLE 表名 DROP 字段名

修改主键

ALTER TABLE Skills DELETE PRIMARY KEY
ALTER TABLE Skills ADD PRIMARY KEY ( SkillID, SkillName );

添加外键

alter table user add key `outside_KEY` (`id`);

1.7.2 表单整体操作

整体删除

DROP TABLE IF EXISTS `XXX`

其他语法规则

( ` )单反号 （飘号） 用来 标识 库名；表名；字段名；索引；别名

一个DATABASE 有一个 db.opt 来保存当前的options 选项表

注释 # ；/* */ ；--；

SQL大小写不敏感

2.MySQL数据管理

2.1 外键

2.1.1 创建表时候直接添加外键

建表 grade

CREATE TABLE `grade`(
 `gradeID` INT(10) NOT NULL  AUTO_INCREMENT COMMENT '年级号',
 `gradeName` VARCHAR(20) NOT NULL DEFAULT 'name' COMMENT '年级名',
  PRIMARY KEY (`gradeID`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

建表 student 并添加外键

CREATE TABLE IF NOT EXISTS `student` (
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生学号' ,
`otherInformation` VARCHAR(50) NOT NULL DEFAULT 'info' COMMENT '其他信息' ,
`gradeID` INT(10) NOT NULL COMMENT '学生的年级',
PRIMARY KEY (`id`) ,--主键
KEY `FK_gradeID` (`gradeID`), --添加外键
CONSTRAINT  `FK_gradeID` FOREIGN KEY (`gradeID`)  REFERENCES `grade`(`gradeID`)  --添加约束
)ENGINE =INNODB CHARSET=utf8

逻辑关系 :

grade 不可被直接删除 由于student 定义了对于grade 的外部键

删除有外键的表 ，不可直接删除依赖的表

2.1.2 创建表后添加外键

ALTER TABLE `student`  --修改表名
ADD CONSTRAINT `FK_gradeID` FOREIGN KEY (`gradeID`)  REFERENCES `grade` (`gradeID`) --添加约束 增加外键字段的位置

不用外键 ：

阿里内部（业界）强制规范 ：不得使用外键与内部相关联 ，一切外键概念必须再应用层解决

2.2 DML语言 (修改数据)

数据库的意义 ：大规模数据存储 建议全文背诵

2.2.1 添加

插入单个字段

INSERT INTO `grade` VALUES(content) -- 内容和列数据 一一匹配

插入多个字段

INSERT INTO `grade` (`gradeName`) --形容 表.列
VALUES('大四') ,('研一'); --值

INSERT INTO `student` VALUES(1,'info1',1),(2,'info2',2); --外键值 必须对应于索引中的匹配

2.2.2修改

uodate 修改 (状态)

修改一个属性

UPDATE `student` set `name`='狂神' WHERE id=1;

修改多个属性 , 逗号隔开

UPDATE `student` SET `name`='老八',`age`= 35 ,`otherInformation`='老八蜜汁小憨包' WHERE id=2;

WHERE 附加运算符

2.2.3 删除

删除单条数据

DELETE FROM `student` WHERE id=9;

清空表单 (不建议使用)

DELETE FROM `student`

建议使用的清空数据库操作

TRUNCATE `student`

二者的相同点:都能删数据 都不会删除表单结构

区别:

• TRUNCATE 重新设置 ，自增列 计数器会归零
• TRUNCATE 不会影响事务，索引从一开始
• DELETE 影响事务 ，新索引设置为上次删除的最后一位加一

而且DELETE删除后 重启数据库 的现象

引擎	问题
INNODB	自增列从一开始 (存储在内存中，断电即失)
MYISAM	自增从上一个自增开始(存在文件中 断电不失)

2.2.4刷新数据库

flush privileges

2.2.5 DML小结

INSERT INTO `student` VALUES(5,'网银',3,'老铁',30,CURRENT_TIME); --严格按列 否则失败
UPDATE `student` SET `name`='王银' ,age=18 WHERE id=5; 
DELETE FROM `student` WHERE id=5;

SELECT

3.DQL查询数据

3.1 认识DQL

3.1.1重要性

• Data QUERY LANGUAGE : 数据查询语言

• 支持所有类型的查询

• SQL最核心的语言规范

• SQL使用频率最高的语言规范

  3.1.2 SELECT完整语法

  SELECT [ALL | DISTINCT]
  {* | TABLE.* | [TABLE.field1][AS alias1],[TABLE.field2][AS alias2] ,...}
  FROM `tableName` as [TABLE_alias]
  [LEFT | RIGHT | INNER JOIN table_name] -- 联表查询
  [WHERE ... ] -- 指定结果满足条件
  [GROUP BY ... ] -- 指定结果按照字段分组
  [HAVING] -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ... ] -- 指定查询记录按一个或者多个排序
  [LIMIT {[OFFSET]ROW_COUNT | ROW_COUNTOFFSET OFFSET}];
  -- 指定查询的记录从那条至哪条
  

   

3.2 查询

3.2.1 基本查询操作

1.查询表的所有数据

SELECT * FROM student; --不带飘号 
SELECT * FROM `grade`; --带飘号  

2.查询指定字段

SELECT `gradeName` FROM grade ; 
SELECT `name` ,`age` FROM student; 

3.查询指定字段，指定别名

SELECT `name` AS 学生姓名,`age` AS 学生年龄 FROM student;

3.2.2 DQL函数

1.连接函数

SELECT CONCAT ('姓名 : ',`name`) AS 标准名 FROM `student`;

3.2.3 普通的查询操作

1.去重操作

SELECT DISTINCT `name` FROM `student` ;

2.所有数据 +1

SELECT `totalScore`+1 FROM `student`; 

注意:只是显示出来的数据+1 不会改变原表的内容

3.2.4 DQL (SELECT)的其他功能

1.查询SQL版本号

SELECT VERSION()

2.计算表达式的结果

SELECT 100*3-1 AS

3.查询自增步长

SELECT @@auto_insrement_increment 

3.3 进阶查询

3.3.1where条件子句

作用 : 检索数据中的符合条件的值

对于区间查询的三种写法

1. &&

SELECT studentNo ,studentRes FROM result WHERE studentRes >=80 && studentRes <=95;

1. BETWEEN AND

SELECT studentNo ,studentRes FROM result WHERE studentRes BETWEEN 80 AND 95;

1. AND

SELECT studentNo ,studentRes FROM result WHERE studentRes >=80 AND studentRes <=95;

或 ,非 操作类似 不多冗述

3.3.2模糊查询

本质 ： 比较运算符

查询通配符 :

% 匹配任何数量的任意字符

SELECT studentRes FROM result where studentName Like '刘%'
-- 可以匹配如刘洋 刘建伟 刘行赵丽 刘大苏打大大啊sdadasa 
-- 匹配名字中有 嘉的 Like '%嘉%'

_ 匹配一个任意字符

SELECT studentRes FROM result where studentName Like '刘_'
-- 可以匹配如 刘洋 刘备 刘兵 刘申 
-- '刘__'匹配两个任意字符
-- '刘___'匹配三个任意字符 以此类推....

3.4 JOINON联表查询

3.4.1七种理论

3.4.2三个例子

3examples :

1.INNER JOIN 相交查询

SELECT s.gradeID ,s.`name` -- 查询内容
FROM `student` AS s        -- 起别名
INNER JOIN `grade` AS g    -- 内联查询
WHERE s.gradeID=g.gradeID; -- 条件

2.Right JOIN 即使左表没有匹配也会从右表返回所有的值 以右表为基准

SELECT s.gradeID,s.name
FROM `student` AS s -- 左表
RIGHT JOIN `grade` AS g -- 右表  
ON s.gradeID=g.gradeID; 

3.Left JOIN 即使右表没有匹配也会返回左表的所有值 以左表为基准

SELECT s.gradeID,s.name
FROM `student` AS s   -- 左表
LEFT JOIN `grade` AS g -- 右表
ON s.gradeID=g.gradeID; 

语句思路:

1.分析需求 ，分析查询的字段来自哪些表 student result subject （连接查询）

2.确定使用哪种的连接查询

3.判断交叉点 :相同的数据

4.判断交叉条件:WHERE 子句

注意:

ON 连接查询

WHERE 条件查询

3.4.3多表查询

SELECT s.gradeID,s.name ,sc.totalScore  -- student的年级 学生姓名 总分
FROM student AS s -- 
LEFT JOIN grade AS g 
ON s.gradeID=g.gradeID
RIGHT JOIN score AS sc
ON sc.id=s.gradeID;

3.5 自查询，自连接

自己和自己连接

把一张表拆成两张表

复习:

1.建表

CREATE TABLE IF NOT EXISTS`school`.`学习清单`(
`pid` INT(10) NOT NULL DEFAULT 1 COMMENT '父亲id',
`selfid` INT(10) NOT NULL DEFAULT 2 COMMENT '自己id',
`content` VARCHAR(20) COMMENT '内容',
PRIMARY KEY (`selfid`)
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

2.插值

INSERT INTO `学习清单`
VALUES(1,1,'软件开发'),(2,2,'人工智能'),
(1,3,'Java编程思想'),(1,4,'编译原理'),
(2,5,'机器学习'),(2,6,'数据挖掘');

3.自连接,自分区

SELECT a.`content` AS `父类`,b.`content` AS `子类`
FROM `学习清单` AS a,`学习清单` AS b
WHERE a.`selfid`=b.`pid`;

结果:

定义自己的父亲是自己 ,讲一个父类分为几个子类的过程

修改后

UPDATE `学习清单` SET `pid`=0 WHERE content='软件开发' OR content='人工智能';

3.6 分页和排序

起始页下标 [i ,pagesize] 容纳量 (i-1)*pagesize

SELECT a.`content` AS `父类` ,b.`content` AS `子类`
FROM `学习清单` AS a ,`学习清单` AS b
WHERE b.pid=a.selfid
ORDER BY `父类` ASC 
LIMIT 0,6;

limit 语法:

limit 查询起始下标 pageSize

排序 语法 :

ORDER BY `key` ASC  -- 升序
ORDER BY `KEY` DESC -- 降序

3.7.子查询 (嵌套查询)

where 语句中再嵌套一个 select 语句

1.原始方法 ： 联表查询方法

SELECT s.otherInformation AS `爱好`,s.id AS `身份号码`,sc.totalScore AS `总分`
FROM `student` AS s
INNER JOIN `score` AS sc
ON s.id=sc.id
ORDER BY sc.totalScore DESC

2.新方法 ： 子查询(嵌套where方法) 子查询不能多于一行

SELECT DISTINCT s.otherInformation AS `爱好`,s.id AS `身份号码`,s.`name` -- DISTINCT 去重 
FROM `student` AS s 
WHERE s.id IN (
	SELECT id FROM `score` WHERE `totalScore` BETWEEN 600 AND 700  -- where in 查询范围
)
ORDER BY s.id ASC ;

3.8 SELECT小结

某宝，某东，某夕夕千人千面

不仅是 mysql 还有Redis ,cloud ,SequoialDB 等非关系/缓存储型，比较复杂

4.MySQL函数

具体请参考官网文档 : https://dev.mysql.com/doc/refman/5.7/en/introduction.html 用的话直接查找即可

4.1常用函数

SELECT 为先 永远SELECT

ABS()|CEILING()|FLOOR()|RAND()|SIGN() .....都是见名知意的

4.2聚合函数

忽略所有的NULL值

select count(name) from `student`

select * 1 null的辨析 请上网参考各路博文和官方DOC 只有纳百家之长 才能清晰理解，这里不粘链接

4.3DQL查询分组

eg. 查找一个科目的 平均，最大，最小分，并分类统计

SELECT  s.name AS `姓名`,s.age AS `年龄`,AVG(`高等数学`) AS `平均分` ,MAX(`高等数学`) AS `最高分`, MIN(`高等数学`) `最低分`
FROM `score` AS sc
INNER JOIN student AS s
ON s.id=sc.id 
GROUP BY s.id

4.4数据库级别的MD5查询加密

4.4.1定义

来自baidu ：

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由美国密码学家罗纳德·李维斯特（Ronald Linn Rivest）设计，于1992年公开，用以取代MD4算法。这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。

4.4.2加密代码(mysql实现)

1.建表

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

2.插值

INSERT INTO `testmd5` VALUES (1,'zhangsan','123456'),(2,'wangwei','123456'),(3,'lisi','1233456');

3.MD5方式加密

UPDATE testmd5 SET pwd=MD5(pwd)

或者在插入时就加密

INSERT INTO `testmd5` VALUES (4,'sange',MD5('pop3'));

4.检验用户传过来的密码正确性

select * from testmd5 where `name`='sange' and pwd=md5('pop3')

5.事务和业务

5.1什么是事务

要么都成功，要么都失败

eg1.定义某个事务为 : A给B转账 x元，保证A 拥有大于等于x元.

事务成功为 : A的账户记录减少x元 ，并且B的账户记录增加x元 ，两者必须都成功 才事务成功，否则事务失败

绝对满足表格，事务成功 Transaction success

5.2ACID原则

事务原则 : ACID 原则 原子性，一致性，隔离性，持久性

1.原子性 Atomicity

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

2.一致性 Consistency

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

3 隔离性 Isolation

事物的隔离是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他的事务的操作数据干扰，多个并发事务之间要相互隔离

4.持久性 Durability

持久性是一个事务一旦被提交，他对数据库中的数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

隔离产生的一些问题

脏读:

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

不可重复读：

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

虚读 （幻读）：

是指在一个事务内读取到了别的事务插入的数据 导致前后读取不一致

5.3测试事务

5.3.1 内容

1. 设置事务自动提交到数据库的状态

SET autocommit=0; -- 设置事务 关闭 
SET autocommit=1; -- 设置事务 开启(默认)

 

2.手动处理事务

标记一个事务的开始，从此时起sql都在同一个事务内

set autocommit=0;
start transaction

增加事务记录

insert xxx

提交

commit 

事务失败 ，回滚

ROLLBACK

事务结束 回复到默认状态

set autocommit=1;

注 :

保存点 ：设置一个事务的保存点

SAVEPOINT 

回滚到保存点

rollback to savepoint

撤销保存点

release savepoint

事务逻辑图 :原创

5.3.2事务实现

只有代码

转账事务实现

一次执行一条,这很重要

CREATE DATABASE  IF NOT EXISTS `shop` ;
USE shop;
CREATE TABLE IF NOT EXISTS account(
id INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘账户名‘,
name VARCHAR(10) NOT NULL COMMENT ‘姓名‘,
money DECIMAL(9,4) NOT NULL COMMENT ‘账户金额‘,

PRIMARY KEY (id)

)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci
INSERT INTO account VALUES(1,‘Roky‘,1000.00),(2,‘Jane‘,5000.00),(3,‘Pony‘,10000.00);
SET autocommit=0;
START TRANSACTION
UPDATE account SET money=money-200 WHERE name=‘Jane‘

UPDATE account SET money=money+200 WHERE name=‘Roky‘
COMMIT

ROLLBACK
SET autocommit=1;

6.索引原理

推荐博客:https://blog.codinglabs.org/articles/theory-of-mysql-index.html

从操作系统，计算机底层架构和数据结构，算法讲解的索引原理。是一篇质量极其高的博文,博主写了半个月，成文时间2011年，年代较远，但没有影响它的价值,个人看了3天。顺便说两句，从计算机底层总结的东西，都值得我们仔细钻研;程序不仅仅是事务逻辑和框架，从来都是数据结构和算法，不懂底层原理，只能成为附庸

6.1 阅读博文和官方Doc 心得笔记

1.索引本质

我们想更加快速的找到数据，我们都知道mysql是根据索引查找数据，但是普通的查找可能不会高效，依靠数据结构才能更加高效，所以所谓索引是一种数据结构

2.为什么选择数据结构

解决海量用户对于海量数据的高并发问题

3.MySQL选择的数据结构

我们已经知道 BST 在查找方面的性能非常优越，但会造成退化，AVL和 R-B这两个变种对于退化做了一些优化，R-B相对于AVL来说Rotate没那么多，增加了结点的parent和color,已经非常优越了，于是，Java的HashMap选择了这种结构，但是MySQL并没有选择这两种，而是更为复杂的 B-Tree 和 B+Tree ，MySQL和HashMap都是以Key-Value方式查找Value。而HashMap是程序运行时有效的，非持久化，就是在RAM中断电即失，而MySQL是持久性的，存储在磁盘的分区，说白了，HashMap性能再高，存储的数据也要依靠内存大小，而MySQL是廉价的硬盘,数据量大到几百亿还可以运行。在Sahni博士所著作的数据结构与算法 树的末尾章节有详细讲述B-树的原理，但是没有代码描述,总体来说，这种结构相对复杂.

2.1 B- /B+树 概括结构

一切树的本质 : 对于用链表描述的结构来讲，永远是递归的.

一些定义

根节点 ：根

内部节点 ： 非空非根的结点

外部结点：叶子节点的儿子节点 ,就是NULL

抽象结构：一个B-树 是一个m叉搜索树，可以理解为有序数组和树形的结合。

结点概况：一个节点数为m的B树可以有[m/2,m]个，这个结点是一个长度为m的数组，这个数组是有绝对不降序的，(绝对不降序是指对于数组的整体元素来讲，可以有相同的元素，但是MySQL不允许这种情况)对于内部的结点来讲,根节点数组长度至少为2，所有的外部节点都在同一个层，保证了树高h可以绝对一致化

排列顺序：一个结点的数组最头部结点的孩子结点的Key都小于该节点，一个结点数组尾部结点Key都大于该节点，一个结点数组中第i个元素Key 大于他的第i子Key，小于他的第i+1子的Key。

查找方式：所以我们在查找一个元素时候，先在根节点中间比较，找到适合的区间，这个过程是二分，对于合适区间进行跳跃，这个过程是树形.

B+树的优化：B+树对于 原结点的数组做了优化，每个结点增加了 一个指向下一个元素的指针，这个可以优化WHERE语句的区间查阅行为。

3.底层原理

对于计算机底层原理不敢多说，笔者是大一小白一枚，计算机导论有讲述，不过涉及过少，因为竞赛和做项目原因提前学习MySQL.

6.2 索引分类

在一个表中主键索引只有一个 ，唯一索引可以有多个

• 主键索引 PRIMARY KEY

  • 唯一标识，主键不可重复，只能有一个列作为主键索引

• 唯一索引 UNIQUE KEY

  • 避免重复的列出现，唯一索引可以重复 ，多个都可以标识位 唯一索引

• 常规索引 KEY/INDEX

  • 默认的

• 全文索引 FULLTEXT

  • 在特定的数据库引擎下才有 (MyISAM)

索引的添加方式

• 创建表初期，给索引增加字段 ，在字段后面添加 xxx KEY (xxx字段)
• 创建完毕，增加索引 ALTER TABLE xxx ADD xxx KEY xxx (xxx)

显示索引信息

SHOW INDEX FROM `account`

增加全文索引

ALTER TABLE `shop`.`account` ADD FULLTEXT INDEX `fullindex` (`name`) 

分析 sql 的执行情况

EXPLAIN SELECT * FROM `account`

SELECT * FROM `account` WHERE MATCH(`name`) AGAINST('Pony');

mysql之explain相关博文 ：

https://blog.csdn.net/jiadajing267/article/details/81269067

6.3 测试百万级数据量

MySQL 不仅存储数据,增删改查 而且是可以编程的

6.3.1插入随机的百万数据

定义函数

DELIMITER $$
CREATE FUNCTION analog()

RETURNS INT

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),CONCAT(FLOOR(RAND()(9999999-1000000)+1000000),‘@qq.com‘),CONCAT(‘18‘,FLOOR(RAND()((999999999-10000000)+10000000))),FLOOR(RAND()2),UUID(),FLOOR(RAND()100));

SET i=i+1;

END WHILE;

RETURN i;

END;

执行函数

SELECT analog()

6.3.2测试查询

测试

SELECT * FROM `app_user` WHERE `name`='用户9999'

解释

explain SELECT * FROM `app_user` WHERE `name`='用户9999'

我们给名字添加普通索引试一下

CREATE INDEX id_app_user_name ON app_user(`name`)

添加索引耗费时间10~15s左右

可以看到时间和效率大大提升了

 

查询时间由2.718 sec ->0.001 sec

查询列数由 992787 row -> 1 row

这在解决高并发问题上很有效的

6.4添加索引的原则

• 索引不是越多越好，多则繁琐，你也记不住
• 不要对进程变动数据加索引，一致性
• 小数据量的表不需要索引，画蛇添足，多此一举
• 索引一般加在经常常用来查询的字段上

7.权限管理与数据库备份

权限管理的目的：

在公司中，一个业务或服务有一个数据库，这个数据库管理员享有最高的权限，而普通员工只有一般权限，不可能所有人都最高权限，不然你删库跑路怎么办

7.1 SQLYog可视化管理

无脑点击左上角即可

7.2 SQL命令管理

1.创建用户

create user juminiy identified by '123456' -- pwd

2.修改密码

当前用户

set password =password('123456')

其他用户

set password for juminiy =password('123456')

重命名用户

rename user juminiy to kkk03

授权 对于某个用户的全部权限

grant all privileges on *.* to juminiy

但是不能给别人授权， root可以，唯一的和ROOT不同处就是GRANT

查看权限

show grant for juminiy

查看root权限

show grant for root@localhost

撤销权限

revoke all privileges on *.* from juminiy  

删除用户

drop user juminiy

7.3 SQL备份

1.为什么备份 :

• 保证重要的数据不丢失
• 数据的转移

2.MySQL数据库备份的方式

• 直接拷贝

• 可视化工具手动导出

• 命令行导出 mysqldump 命令行使用

   

  数据库导出

先 net start mysql

直接在 cmd 或者 bash上操作即可

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1，表2，表3 > 物理磁盘的位置 /文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

数据库导入

-- 先登录数据库
mysql -uroot -p123456
-- 执行导入
source d:/a.sql
-- 不登录数据库
mysql -uroot -pxxxxxx 库<备份文件

保证这个数据库名存在，数据库存在，才能导入成功

8.数据库的规约，三大范式

8.1 为什么需要数据规范化？

• 信息重复

• 更新异常

• 删除异常

  • 无法正常删除信息

• 插入异常

  • 无法正常显示信息

8.2 三大范式 :

8.2.1 第一范式 1NF：

原子性:保证每一列不可分

8.2.2 第二范式 2NF：

前提：满足1NF

每张表只描述一件事务

8.2.3 第三范式 3NF：

前提：满足 2NF

第三范式需要确保数据表中的每一列数据都PRIMARY KEY直接相关，不能间接相关

那么在实际的应用中呢，范式虽然是范式，但是是有问题的，有时候为了性能，可以不满足1NF 2NF 3NF

8.4 规范性和性能的问题

阿里规约：关联查询的表不可超过三张

• 考虑商业化的需求和目标 （成本，用户体验）数据库的性能更重要
• 在规范性能的问题的时候，需要适当放的考虑一下规范性
• 故意给某些表增加一些冗余的字段 （从多表联查变为单表查询）
• 故意增加一些计算列 (从大数据量降低为小数据量的查询)

9.JDBC

Java操作数据库

9.1 数据库驱动

驱动：声卡，显卡，数据库

eg.数据库需要驱动程序才能运行

9.2JDBC

SUN公司为了简化开发人员的（对数据库统一的）操作，提供了一个（Java）操作，按照具体的规范去做

导入依赖的包

java.sql

javax.sql

需要的jar包，不然加载不了

9.3 java操作数据库

1.加载驱动

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

2.用户信息和URL

mysql版本

String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false";
String name="root";
String password="123456";

oracle版本

String url="jdbc:oracle:thin@localhost1521:sid";

3.连接

Connection c=DriverManager.getConnection(url,name,password);

4.SQL的对象：操作SQL

和mySQL操作相同，见名知意

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

5.执行SQL的对象：执行SQL

和SQL操作相同，也是见名知意的

Statement s=c.createStatement();
s.executeQuery();
s.execute();
s.executeUpdate();

6.获得结果集

不知道什么类型就用Object就可了

ResultSet rs=s.executeQuery(s1);
rs.getObject();

7.释放连接

rs.close();
s.close();
c.close();

9.4 SQL注入

使用statement对象（不安全的）用java操作数据库

jdbc中的statement对象用于向数据库发送SQL语句，想完成对于数据库的增删改查，只需要通过这个对象向数据库发送增删改查的信号即可。

Statement对象的executeUpdate 方法，用于向数据库发送增，删，改，查的SQL语句，executeUpdate执行完成后，将会返回一个整数（告知我们几行发生变化，提示信息和SQL相同）

1.预备工作:封装一个工具类

public class sqlIn {
 private Connection c;
 private Statement s;
 private ResultSet rs;
 public void drive() throws ClassNotFoundException, SQLException {
     Class.forName("com.mysql.jdbc.Driver");
     String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false";
     String name="root";
     String password="123456";
     c= DriverManager.getConnection(url,name,password);
     s=c.createStatement();
 }
 public void login(String name,String pwd) throws SQLException {
     String opr="select * from `login` where `name`='"+name+"' and password='"+pwd+"'";
     rs=s.executeQuery(opr);
     while(rs.next()){
         System.out.println(rs.getObject("name"));
         System.out.println(rs.getObject("password"));
     }
 }
}

2.正常查询

public static void main(String[] args) throws SQLException, ClassNotFoundException {
     sqlIn p=new sqlIn();
     p.drive();
     p.login("Tom","123456");
 }

3.SQL注入

欺骗服务器的规范

sql存在漏洞 ，违法的字符串拼接

select * from `tableName` where `rowName1`=' 'or 1=1' `rowName2`=' 'or 1=1'

代码实现

public static void main(String[] args) throws SQLException, ClassNotFoundException {
     sqlIn p=new sqlIn();
     p.drive();
     p.login(  " 'or '1=1"  ,   " 'or '1=1"  );
 }

9.5 安全的对象

java一个专门防止SQL注入的类： PreparedStatement

接上边的9.4的类

java操作数据库

1.添加数据

public void insert(int id,String name,String pwd) throws SQLException {
   String opr="insert into login(`id`,`name`,`password`) values(?,?,?)";
   ps=c.prepareStatement(opr);//c->connection 对象
   ps.setInt(1,id);
   ps.setString(2,name);
   ps.setString(3,pwd);
   ps.executeUpdate();//执行更新 否则无效
}
public static void main(){
p.insert(13,"ppd1234","91881qq");插入
}

2.删除数据

public void delete(int id) throws SQLException {
   String opr="delete from `login` where id="+Integer.valueOf(id);
   ps=c.prepareStatement(opr);
   ps.executeUpdate();
}
public static void main(String args[]){
p.delete(1);
}

3.更新数据和查找数据不多解释，opr字符串就是SQL中的操作，Java执行excuteUpdate()就能成功

9.6使用IDEA连接数据库

Community版本：右边栏

Education版本：https://blog.csdn.net/cnds123321/article/details/102854508

踩了好多坑才连接上 呜呜呜///

9.7 Java执行事务

java操作事务和SQL操作事务是一样的同样遵循ACID原则

如果不了解什么是事务 请回到 5事务和业务章节学习

eg.用户转账

public void accountPay(String fromAccount,String toAccount,int Money) throws SQLException {
   try{
       c.setAutoCommit(false);
       StringBuilder sb1=new StringBuilder();
       sb1.append("update account set money=money-").append(Integer.valueOf(Money).toString()).append(" where `name`= '").append(fromAccount).append("'");
       ps=c.prepareStatement(sb1.toString());
       ps.executeUpdate();
       StringBuilder sb2=new StringBuilder();
       sb2.append("update account set money=money+").append(Integer.valueOf(Money).toString()).append(" where `name` = '").append(toAccount).append("'");
       ps=c.prepareStatement(sb2.toString());
       ps.executeUpdate();
   }catch (SQLException e1){
       try{
           c.rollback();//失败了 先回滚 钱不能没 ...
       }catch(SQLException e2){
           e1.printStackTrace();
           e2.printStackTrace();
       }
   }finally{
       c.setAutoCommit(true);
       System.out.println("Successful!");
   }
public static void main(String args[]){
p.accountPay("Weiwei","Liping",500);
}

9.8 数据库连接

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

连接--释放 十分的浪费资源

通俗解释：就是你去银行，只有一个服务员，你进去，为你服务，你出去了，银行关闭，下一个人再进来，在连接，服务完，再关闭。而连接池的作用就是设置多个服务员，每个服务员都在等待，银行不会关闭。

可以用别人写好的，例如：DBCP,C3P0,Tomcat ,Druld:Alibaba

也可以自己用java手动实现一个(DataSource)接口

导入依赖：

DBCP :

commons-dbcp2-2.7.0.jar

commons-pool2-2.8.0.jar

使用封装的工具类，测试代码和原来的没有任何的区别

10.基础完结散花，撒花，撒欢，撒欢儿

MySQL