1、初始MySQL



2、操作数据库



2.1、操作数据库

-- 创建数据库
CREATE DATABASE [IF EXISTS] westos

-- 删除数据库
DROP DATABASE [IF EXISTS] westos

-- 使用数据库
-- 若表明或字段名是特殊字符就需要加 `
USE `school`

-- 查看所有的数据库
SHOW DATABASES



2.2、数据库列类型

数值

  • tinyint 十分小的数据 一个字节
  • smallint 较小的数据 两个字节
  • mediumint 中等大小的数据 三个字节

  • int 标准的整数 四个字节
  • bigint 较大的数据 八个字节
  • float 浮点数 四个字节
  • double 浮点数 八个字节 （精度问题）
  • decimal 字符串形式的浮点数 金融计算的时候使用

字符串

  • char 字符串固定大小的 0~255

  • varchar 可变字符串 0~65535
  • tinytext 微型文本 2^8 – 1

  • text 文本串 2^16 – 1

    保存大文本

时间日期

java.util.data

  • date YYYY-MM-DD，日期
  • time HH:mm:ss，时间格式

  • datetime YYYY-MM-DD HH:mm:ss 最长用的时间格式

  • timestamp 时间戳 1970.1.1到现在的毫秒数
  • year 年份表示

null

  • 没有值
  • 注意不要使用null进行运算



2.3、数据库字段属性

Unsigned：

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

zerofill：

  • 0填充的
  • 不足的位数，使用0来填充 int 长度3 5 005

自增：

  • 自动在上一条记录的基础上加一
  • 通常用来设置唯一的主键 index ,必须是整数类型
  • 可以自定义设置起始值和步长

非空 NULL not null

  • 设置为非空如果不给赋值便会报错
  • null，如果不填写值就默认为null

默认：

  • 设置默认的值 
-- 拓展（未来做项目用，表示一个记录存在的意义）


id			-- 主键

 version     -- 乐观锁

is_delete    -- 伪删除

gmt_create    -- 创建时间

gmt_update    -- 修改时间



2.4、创建数据库表

-- 使用英文的括号，表名称 和 字段 尽量使用 `` 括起来
-- AUTO_INCREMENT自增
-- 字符串  使用但引号括起来
-- 所有语句后面加英文的 ,  
-- PRIMARY KEY主键，一般一个表只有一个唯一的主键

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) default NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

格式 

CREATE TABLE [IF NOT EXISTS] `表明`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    .....
    '字段名' 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令 

SHOW CREATE DATABASE school -- 查看创建数据库语句
SHOW CREATE TABLE student -- 查看数据表的定义语句
DESC student -- 显示表的结构



2.5、数据库表的类型

-- 关于数据库的引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为MYISAM两倍

常规使用操作：

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

在物理空间存的位置

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

本质还是文件存储

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

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

  • MYISAM对应文件

    • *.frm 表结构的定义文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件

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

CHARSET=utf8

不设置的话，会是mysql默认的Latin1(不支持中文)



2.6、修改删除表

修改 

-- 修改表名:ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段:ALTER TABLE 表名 add 字段名 列属性
ALTER TABLE student1 ADD age INT(11)

-- 修改表的字段(重命名,修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE student1 CHANGE age age1 INT(1)  -- 字段重命名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE student1 DROP age1

-- 删除表   (如果不存在)
DROP TABLE IF EXISTS student1


所有创建和删除操作尽量加上判断



3、MySQL数据管理



3.1、外键

constraint 约束

方式一、在创建表的时候，增加约束(比较复杂) 

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键 key
-- 给外键添加约束 (执行引用) reference 引用
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除外键关系的时候，必须先删除引用别人的表(从表)，再删除被引用的表(主表)

方式二：创建表成功后添加外键约束 

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

-- alter table 表 add constraint 约束名 foreign key(作为外键的列) references 哪个表(哪个字段)

以上的操作都是物理外键，数据库级别的外键，不建议使用！(避免数据库过多造成困扰)


最佳方式

  • 数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
  • 想使用多张表的数据，使用外键(程序实现)



3.2、DML语言

DML语言：数据操作用语言

  • insert
  • update
  • delete



3.3、添加

insert 

-- 插入语句(添加)-- insert into 表名 ([字段名1，字段名2...])values('值1'),('值2'),(...)INSERT INTO `grade`(`gradename`) VALUES('大四')-- 一般写插入语句，数据和字段要保证一一对应-- 插入多个字段INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一') INSERT INTO `student`(`name`) VALUES('张三')INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','aaaaa','男'),('李四','bbbb','男'),('王五','aaaaa','女')

语法 :

insert into 表名 ([字段名1，字段名2...])values('值1'),('值2'),...

注意事项：

  1. 字段和字段之间用英文逗号隔开
  2. 字段名可以省略，但是后面的值必须要一一对应



3.4、修改

update 修改 

-- 修改学员的名字
UPDATE `student` SET `name`='阳光就在天上' WHERE id=1;

-- 不指定条件的情况下，会改变所有的表
UPDATE `student` SET `name`='阳光就在天上'

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='阳光就在天上',`email`='123456.com' WHERE id=1;

语法:

UPDATE 表名 SET colnum_name = VALUE WHERE 条件

colnum_name 是数据库的列

条件：where子句



3.5 删除

delete 删除

语法：

delete from 表名 [where 条件]
 

-- 删除指定数据
DELETE FROM `student` WHERE id = 1

TRUNCATE 命令

作用：完全清空数据库表，表的结构和索引约束不会变

delete 和 TRUNCATE 的区别

  • 相同点：都不会删除表结构
  • 不同点：

    • TRUNCATE 会重新设置 自增列 计数器会清零
    • TRUNCATE 不会影响事务 
-- delete 和 TRUNCATE 的区别CREATE TABLE `test`(	`id` INT(4) NOT NULL AUTO_INCREMENT,	`coll` VARCHAR(20) NOT NULL,	PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')-- 不会影响自增DELETE FROM `test`-- 自增会清零TRUNCATE TABLE `test`


delete删除的问题

,重启数据库，现象

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



4、DQL查询数据



4.1、DQL

Data Query Language:数据查询语言（select）

select 完整语法： 

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



4.2、查询指定字段

语法：

SELECT 字段 FROM 表
 

SELECT * FROM student-- 别名，给结果起一个名字  ASSELECT `student` AS 学号,`name` AS 学生姓名 FROM student-- 函数 concat(a,b)SELECT CONCAT('姓名:',`name`) AS 新名字 FROM student

去重 distinct

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

SELECT `gradeid` FROM student
-- 发现重复数据 去重
SELECT DISTINCT `gradeid` FROM student

数据库的列(表达式) 

-- 查询系统的版本(函数)
SELECT VERSION()
-- 用来计算(表达式)
SELECT 100*3-1 AS 计算结果
-- 查询自增的步长(变量)
SELECT @@auto_increment_increment

-- 学员成绩加分
SELECT `gradeid`+1 AS '加分后' FROM student

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

select

表达式

from 表



4.3、where条件子句

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

逻辑运算符

运算符 语法 描述
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
not ! not a !a 逻辑非 
SELECT `name` ,`pwd` FROM student WHERE `name`='张三' AND `pwd`='aaaaa'SELECT `name` ,`pwd` FROM student WHERE `name`='张三' && `pwd`='aaaaa'-- 模糊查询(区间范围)SELECT `name`,`gradeid` FROM student WHERE `gradeid` BETWEEN 2 AND 3SELECT `name`,`gradeid` FROM student WHERE `gradeid`!=3

模糊查询：比较运算符

运算符 语法 描述
IS NULL a is null 若操作符结果为NULL,结果为真
IS NOT NULL a is not null 若操作符结果不为NULL,结果为真
BETWEEN a between b and c 若a在b和c之间，则结果为真
LIke a like b SQL 匹配，如果a匹配b，则结果为真
IN a in (a1,a2,a3…) 若a在其中的某一个，则结果为真 
-- 查询姓阳的同学-- like 结合 %(代表0到任意个字符)  _(一个字符)SELECT `name`,`gradeid` FROM student WHERE `name` LIKE '阳%'-- 名字后面只有一个字的SELECT `name`,`gradeid` FROM student WHERE `name` LIKE '阳_'-- 查询名字中间有光字的同学SELECT `name`,`gradeid` FROM student WHERE `name` LIKE '%光%'-- in(具体的一个或多个值)-- 查询范围内学生SELECT `gradeid`,`name` FROM student WHERE `gradeid` IN (2,5,3)-- 查询地址为空的学生 null ''SELECT `name`,`birthday` FROM student WHERE `birthday`='' OR `birthday`IS NULL



4.4、联表查询

-- 联表查询
/*
分析需求，分析查询的字段来自哪些表(连接查询)
确定交叉点(两个表的数据哪里是相同的)
判断条件
*/

SELECT s.gradeid FROM student AS s
INNER JOIN grade AS g
WHERE s.gradeid = g.gradeid

SELECT s.gradeid FROM student AS s
RIGHT JOIN grade AS g
ON s.gradeid = g.gradeid

SELECT s.gradeid FROM student AS s
LEFT JOIN grade AS g
ON s.gradeid = g.gradeid
操作 描述
Inner join 如果表中至少有一个匹配，便返回
left join 会从左表中返回所有的值，即使右表中没有匹配
reght join 会从右表中返回所有的值，即使左表中没有匹配



4.5、自连接

自己的表和自己的表连接，一张表拆为两张表 

SELECT a.`gradeid` AS '父栏目',b.`gradeid` AS '子栏目'
FROM `student` AS a,`student` AS b
WHERE a.`gradeid` = b.`gradeid`



4.6、分页和排序

排序 

 -- 分页 limit 和排序 order by  -- 排序 升序 asc  降序 desc  SELECT `gradeid` FROM  student ORDER BY `gradeid` DESC  SELECT `gradeid` FROM  student ORDER BY `gradeid` ASC

分页 

-- 分页：缓解数据库压力，更好的体验-- limit 起始值 页面的大小(pagesize)-- 【第N页 limit (n-1)*pagesize】-- 【pagesize 页面大小】-- 【(n-1)*pagesize 起始值 】-- 【n当前页】-- 【数据总数/页面大小 = 总页数】 SELECT `gradename` FROM  `grade`   LIMIT 3,2



4.7、子查询

where (值是固定的)

本质：在where语句中嵌套一个子查询语句



5、常用函数



5.1、常用函数

SELECT ABS(-2)  -- 绝对值
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()  -- 0-1之间随机数
SELECT SIGN(-1)  -- 判断一个数的符号

-- 字符串函数
SELECT CHAR_LENGTH('阳光就在天上')  -- 字符串长度
SELECT CONCAT('我','高')  -- 拼接字符串
SELECT INSERT('阳光就在天上',1,2,'天上')  -- 查询,替换
SELECT LOWER('YangGuang')  -- 小写字母
SELECT UPPER('YangGuang')  -- 大写字母
SELECT INSTR('yangguang','h')  -- 返回第一次出现的字串的索引
SELECT REPLACE('阳光就在天上','坚持','努力')  --  替换出现的指定字符串
SELECT SUBSTR('阳光就在天上',4,6)  -- 返回指定字符串(源字符串)
SELECT REVERSE('阳光就在天上')  -- 反转


SELECT REPLACE(`name`,'李','大') FROM student
WHERE  `name` LIKE '李%'

-- 时间和日期函数
SELECT CURRENT_DATE()  -- 获取当前日期
SELECT CURDATE()
SELECT NOW()
SELECT LOCALTIME()  -- 本地时间
SELECT SYSDATE()  -- 系统时间 

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()



5.2、聚合函数

函数名称 描述
count() 计数
sum() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
… … 
-- 聚合函数

SELECT COUNT(`name`) FROM student  -- count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student	-- count(*),不会忽略null值，本质计算行数
SELECT COUNT(1) FROM student	-- count(1),不会忽略null值，本质计算行数

SELECT SUM(`gradeid`) AS 总和 FROM `student`
SELECT AVG(`gradeid`) AS 平均分 FROM `student`
SELECT MAX(`gradeid`) AS 最高分 FROM `student`
SELECT MIN(`gradeid`) AS 最低分 FROM `student`



5.3、数据库级别的MD5加密

MD5：MD5信息摘要算法

主要增强算法复杂度和不可逆性

MD5不可逆,具体的值的MD5是一样的

MD5 破解网站的原理，MD5加密后的值，加密前的值 

-- 测试MD5
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

-- 明文密码
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,'xioaming',MD5('123456'))

-- 如何校验：将用户传递的密码进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='xiaoming' AND pwd=MD5('123456')



6、事务

将一组SQL放在一个批次中执行

事务原则：ACID原则 原子性(Atomicity)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability) （脏读，幻读）


原子性(Atomicity)

要么都成功，要么都失败


一致性(Consistency)

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


持久性(Durability)

–事务提交

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


隔离性(Isolation)

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

隔 离所导致的问题：


脏读：

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


不可重复读：

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


虚读(幻读)：

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



6.1、执行事务

-- 事务
-- mysql 是默认开启事务自动提交的
SET autocommit = 0  /* 关闭  */
SET autocommit = 1  /* 开启(默认)  */

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

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

INSERT xx
INSERT xx

-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到原来的样子（失败！）
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 设置一个事务的保存点
SAVEPOINT 保存点名称
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称  -- 删除保存点

模拟场景 

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

USE shop

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`)
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;  -- 恢复默认值



7、索引

索引(index)是帮助MySQL高效获得数据的数据结构



7.1、索引的分类

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

  • 主键索引 (PRIMARY KEY)

    • 唯一表示，主键不可重复，只能有一个列作为主键

  • 唯一索引 (UNIQUE KEY)

    • 避免重复列出现，唯一索引可以重复，多个列都可以表示唯一索引

  • 常规索引 (KEY/INDEX)

    • 默认的，index，key关键字设置

  • 全文索引 (FullText)

    • 在特定的数据库引擎下才有，MyISAM
    • 快速定位数据 
-- 索引的使用

-- 1、创建表的时候增加索引
-- 2、创建完毕后，增加索引

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

-- 增加一个索引
ALTER TABLE school.student ADD FULLTEXT INDEX `name`(`name`)

-- EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM student;  -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(`name`) AGAINST('阳')



7.2、测试索引

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 '年龄',
	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'


-- 插入100万条数据
DELIMITER $$ -- 写函数前必写，标志
CREATE FUNCTION mock_data()
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),'123456',
		CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
		FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i = i+1;
	
	END WHILE;
	RETURN i;
END;

SELECT mock_data();


SELECT * FROM app_user WHERE `name`='用户9999';  -- 执行耗时: 0.552 sec

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

-- creat index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name`='用户9999'; -- 执行耗时: 0.004 sec



7.3、索引原则

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

索引的数据结构 (https://blog.codinglabs.org/articles/theory-of-mysql-index.html)

Hash类型的索引

Btree:InnoDB默认数据结构



8、数据库备份



8.1、用户管理

SQL yog 可视化管理

  • 通过用户管理器添加用户

SQL 命令操作

  • 用户表：mysql.user

    • 本质：对这张表增删改查 
-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER xiong IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR xiong = PASSWORD('123456')

-- 重命名  RENAME USER 原来名字 TO 新名字
RENAME USER xiong TO xiaoxiong

-- 用户授权  GRANT ALL PRIVILEGES 全部的权限(给他人授权除外)，库.表
GRANT ALL PRIVILEGES ON *.* TO xiaoxiong

-- 查看权限
SHOW GRANTS FOR xiaoxiong
SHOW GRANTS FOR root@localhost  -- root用户的权限

-- 撤销权限 revoke
REVOKE ALL PRIVILEGES ON *.* FROM xiaoxiong

-- 删除用户
DROP USER xiaoxiong



8.2、MySQL备份

MySQL数据库备份的方式：

  • 拷贝物理文件

  • 在可视化工具中手动导出

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

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


# 导入
# source 备份文件
source D:/a.sql



9、规范数据库设计



9.1、设计的优略

糟糕的数据库设计:

  • 数据冗余，浪费空间

  • 数据插入删除麻烦，（屏蔽使用物理外键）

  • 程序的性能差

良好的数据库设计：

  • 节省内存空间
  • 保证数据库完整性
  • 方便开发系统

软件开发中，关于数据库的设计：

  • 分析需求：分析业务和需要处理的数据库的需求
  • 概要设计：设计关系图E-R图



9.2、三大范式

数据不规范的后果：

  • 信息重复
  • 更新异常
  • 插入异常

    • 无法正常显示信息
  • 删除异常

    • 丢失有效信息

三大范式


第一范式(1NF)

  • 原子性：要求数据库表每一列都是不可再分


第二范式(1NF)

前提：满足第一范式

  • 每张表只描述一件事情


第三范式(1NF)

前提：满足第一第二范式

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


规范性和性能的问题

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

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



10、JDBC



10.1、数据库驱动

我们的程序会通过

数据库驱动

和数据库进行交互！！



10.2、JDBC

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



10.3、第一个JDBC的程序

创建测试数据库 

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
);

INSERT INTO users(id,`name`,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisii','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');

步骤：

  1. 加载驱动
  2. 连接数据库 DriverManager
  3. 获得执行sql的对象 Statement
  4. 获得返回的结果集
  5. 释放连接 
import java.sql.*;

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        //连接成功，返回数据库对象  Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);


        //执行SQL的对象   statement 执行SQL的对象
        Statement statement = connection.createStatement();

        //执行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("birth = " + resultSet.getObject("birthday"));
        }


        //断开数据库
        resultSet.close();
        statement.close();
        connection.close();


    }
}

DriverManager 

//DriverManager.registerDriver(new Driver());
Class.forname("com.mysql.jabc.Driver");//加载驱动

Connection connection = DriverManger.getConnection(url,username,password);

//connection 代表数据库

connection.rollback(); //数据库设置回滚
connection.commit();  //事务提交
connection.setAutoCommit();  //数据库设置自动提交

URL 

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


//mysql -- 3306
//协议://主机地址:端口号/数据库名?参数1&参数2

//oralce -- 1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement 执行SQL的对象 

statement.executeQuery();  //查询操作
statement.execute();  //执行任何SQL
statement.executeUpdat();  //更新，插入，删除  返回受影响的行数

ResultSet 查询的结果集，封装了所有的查询结果 

resultSet.getObject(); //在不知道类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

遍历：指针 

resultSet.beforeFirst();  //移动到最前面
resultSet.afterLast();  //移动到最后面
resultSet.next();  //移动到下一个数据
resultSet.previous();  //移动到前一行

释放资源 

resultSet.close();
statement.close();
connection.close();



10.4、statement对象

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

Statement对象的executeUpdate方法，用于向数据库发送增，删，改的sql语句，executeUpdated执行完成后，将返回一个整数（该操作执行完成后几行数据发生了变化）。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

提取工具类 

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");

            //驱动只需要加载一次
            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) {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

//其中的db.properties文件内容
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

CRUD操作-create

使用executeUpdate(String sql)方法完成数据的添加操作： 

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

        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = "INSERT INTO users(id,`name`,`password`,`email`,`birthday`)" +
                    "VALUES(4,'xiong','123456','12348886@.com','2020.04.12')";

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

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }


    }
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作: 

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

        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = "DELETE FROM users WHERE id=4";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }


    }
}

CRUD操作-update

使用executeUpdate(String sql)方法完成数据修改操作: 

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

        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = "UPDATE users SET `name` = 'xiong' WHERE id=2";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }


    }
}

CRUD操作-read

使用executeQuery(String sql)方法完成数据查询操作: 

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

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from users where id = 1";

            rs = st.executeQuery(sql);

            while(rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }


    }
}

SQL注入问题

sql存在漏洞，会导致数据泄漏。 

public class SqlInto {
    public static void main(String[] args) {
        //正确的登陆login("xiong","123456");
        login(" ' or '1=1"," 'or'1=1");
    }

    public static void login(String username,String password){

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

        try {
            conn  = JdbcUtils.getConnection();
            st = conn.createStatement();

            rs = st.executeQuery("select * from users where name = '" + username + "' and password = '"+ password+"'");
            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

}



10.5、PreparedStatement对象

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

防止SQL注入的本质：

  • 将传递进来的参数当作字符，假设其中有转义字符，就直接忽略。
  1. 增加 
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;



        try {
            conn = JdbcUtils.getConnection();

            //使用？占位符代替参数
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译SQL，先写但是不执行

            st.setInt(1,4);
            st.setString(2,"yuan");
            st.setString(3,"16515661");
            st.setString(4,"+64654894@.com");
            //sql.Date 数据库
            //utils.Date JAVA
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = st.executeUpdate();

            if(i>0){
                System.out.println("插入成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
  1. 删除 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //使用？占位符代替参数
            String sql = "delete from users where id = ?";

            st = conn.prepareStatement(sql);//预编译SQL，先写但是不执行

            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();

            if(i>0){
                System.out.println("删除成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
  1. 更新 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;



        try {
            conn = JdbcUtils.getConnection();

            //使用？占位符代替参数
            String sql = "update users set `name` = ?  where id = ?;";

            st = conn.prepareStatement(sql);//预编译SQL，先写但是不执行

            st.setString(1,"dapangyuan");
            st.setInt(2,1);
            //执行
            int i = st.executeUpdate();

            if(i>0){
                System.out.println("更新成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
  1. 查询 
public class TestSelect {
    public static void main(String[] args) {

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


        try {
            conn = JdbcUtils.getConnection();

            String sql = "select * from users where id = ?";

            st = conn.prepareStatement(sql);

            st.setInt(1,1);//传递参数

            //执行
            rs = st.executeQuery();

            if(rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}



10.6、数据库连接池

数据库的连接–释放耗费系统资源

池化技术：准备一些预先的资源

编写连接池：实现一个接口 DataSource

开源数据源实现

DBCP

C3P0

Druid:阿里巴巴

使用数据库连接池之后，在项目开发中就不需要编写连接数据库的代码


DBCP

需要用到的jar包：

commons-dbcp-1.4、commons-pool-1.6

dbcpconfig.properties //配置文件 

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#!-- 初始化连接 --
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --
maxWait=60000
# JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意：user 与 password 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

# 指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

# driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

# driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

加载配置文件 

import com.xiong.lesson02.utils.JdbcUtils;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_DBCP {
    private static DataSource dataSource =null;

    static {
        try{
            InputStream in = JdbcUtils.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 conn, Statement st, ResultSet rs) {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

测试 

import java.sql.*;

public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils_DBCP.getConnection();

            //使用？占位符代替参数
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译SQL，先写但是不执行

            st.setInt(1,4);
            st.setString(2,"yuan");
            st.setString(3,"16515661");
            st.setString(4,"+64654894@.com");
            //sql.Date 数据库
            //utils.Date JAVA
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            //执行
            int i = st.executeUpdate();

            if(i>0){
                System.out.println("插入成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}


C3P0

c3p0-config.xml //配置文件 

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

    <!--
    C3P0的缺省（默认）配置，
    如果在代码中“ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();”
    这样写表示用的是C3P0的默认配置
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">
            jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;serverTimezone=GMT%2B8&amp;useSSL=false
        </property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </default-config>

    <!--
    C3P0的命名配置：
    如果在代码中“ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(“mySource”);”
    这样写表示使用的是name为mySource的配置
    -->
    <named-config name="mySource">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/school1</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </named-config>
</c3p0-config>

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class JdbcUtils_C3P0 {
    private static DataSource dataSource =null;

    static {
        try{
            
            //配置文件配置
            dataSource = new ComboPooledDataSource("MySQL");


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }


    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

代码测试 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils_C3P0.getConnection();

            //使用？占位符代替参数
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译SQL，先写但是不执行

            st.setInt(1,4);
            st.setString(2,"yuan");
            st.setString(3,"16515661");
            st.setString(4,"+64654894@.com");
            //sql.Date 数据库
            //utils.Date JAVA
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            //执行
            int i = st.executeUpdate();

            if(i>0){
                System.out.println("插入成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(conn,st,null);
        }
    }
}


本质：DataSource接口


版权声明：本文为m0_53323393原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/m0_53323393/article/details/120978423