MySQL
JavaEE：企业级Java开发，Web
前端（页面：展示，数据！）
后台（连接点：连接数据库JDBC，连接前端（控制，控制视图跳转和给前端传递数据））
数据库（存数据，Txt Excel word）
只会写代码，学好数据库，基本混饭吃
操作系统，数据结构与算法！当一个不错的程序员！
离散数学，数字电路，体系结构，编译原理 + 实战经验 高级程序员，优秀的程序员。
为什么学数据库
- 岗位需求
- 现在的世界，大数据时代，数据可以变现，得数据者得天下。
- 被迫需求：存数据
- 数据库是所有软件体系中最核心的存在 DBA
什么是数据库
数据库（DB，DataBase）
概念：数据仓库，软件，安装在操作系统（windows,linux,mac）之上。
作用：存储数据，管理数据
数据库分类
关系型数据库：（SQL）
- MySQL、Oracle、SQLserver、DB2、SQLlite
- 通过表与表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表
非关系型数据库（NoSQL） Not Only SQL - Redis，MongDB
- 非关系型数据库，对象存储，通过对象的自身的属性决定。
DBMS（数据库管理系统） - 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
MySQL（5.7 稳定 8.0）
安装建议：
- 尽量不要使用exe，注册表
- 尽可能使用压缩包安装
- 解压
- 把这个包放在自己的电脑环境目录下
- 配置环境变量
- 新建mysql配置文件 ini
[mysql]
# 目录一定要换成自己的
basedir=D:\Environment\mysql-5.7\
datadir=D:\Environment\mysql-5.7\data\
port=3306
skip-grant-tables
- 启动管理员模式下的CMD，运行所有的命令
- 安装mysql服务
- 初始化数据库文件
- 启动mysql，进去修改密码
- 进入mysql通过命令行（-p后面不要加空格），修改密码（sql语句后面一定要加分号！）
- 注释掉ini中的跳过密码
- 重启mysql 连接测试，如果来了解成功就OK了。
安装Navicat
连接数据库
命令行连接
mysql -uroot - p123456 --连接数据库
updatw mysql.user set authentication_string-password('123456') where user-'root' and Host = 'localhost'; --修改用户密码
flush privileges; -- 刷新权限
----------------------------------
-- 所有的语句都使用 分号 ； 结尾
show databases; -- 查看所有的数据库
mysql> use school --切换数据库 use 数据库名
Database changed
show tables; --查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息
create database pets；-- 创建一个数据库
exit; --退出连接
-- 单行注释（SQL注释）
/**/ （SQL）多行注释
DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言
CRUD 增删改查！
操作数据库语句
操作数据库》操作数据库中的表》操作数据库中表的数据
mysql关键字不区分大小写
操作数据库
- 创建数据库
- 删除数据库
- 使用数据库
- 查看数据库
CREATE DATABASE [IF NOT EXISTS] pets; -- 中括号表示可选
DROP DATABASE IF EXISTS pets; --删除数据库
USE `school` -- tab键上面的，如果你的表名或者字段名是一个特殊字符，就需要带 ``
SELECT `user` FROM student;
SHOW DATABASE; -- 查看所有的数据库
数据库的列类型
数值
- tinyuint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的数据 4个字节 常用
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal
字符串
- char 字符串 0~255
- varchar 可变字符串 0~65535 常用的变量 String
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本
时间日期
java.util.Date
- date YYYY-MM-DD 日期
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的ms数
- year 年份表示
null
- 没有值，未知
- ==注意，不要使用NULL进行运算，结果为NULL
数据库的字段属性（重点）
Unsigned：
- 无符号的整数
- 声明了该列不能声明为负数
zerofill： - 0填充的
- 不足的位数蛮实用0来填充， int(3) 5 --> 005
自增： - 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键~index，必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null - 假设设置为 not null ，如果不给他赋值，就会报错。
- NNUL ，如果不填写值，默认就是null。
默认： - 设置默认的值！
- sex 默认值为男，如果不指定该列的值，则会有默认的值。
-- 每一个表，都必须存在以下五个字段，未来做项目用的，表示一个记录存在的意义！
id 主键
`version` --乐观锁
is_delete --伪删除
gmt_create --创建时间
gmt_update --修改时间
创建数据库表（重点）
--注意将，使用英文() 表的名称和字段尽量使用``括起来
--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 '出生日期',
`addredd` 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 -- 显示表的结构
关于数据库引擎
INNODB 默认使用
MYISAM 早些年使用的
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为两倍 |
常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在屋里空间存在的位置：
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
本质还是文件的存储。
MYSQL引擎在物理文件上的区别：
- INNODB在数据库表中只有一个*.frm文件，以及三级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符级编码
- CHARSET=utf8
不设置的话，会是mysql默认的字符级编码 （不支持中文！）
MySQL的默认编码时Latin1，不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
修改删除表
ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1 -- 修改表名
ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11) --增加表字段
-- 修改表的字段（重命名，修改约束）
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名
ALTER TABLE teacher1 DROP age1 --删除表的字段
DROP TABLE IF EXISTS teacher1 --删除表
** 所有的创建和删除操作尽量加上判断，以免报错 **
MySQL数据管理
外键
方式一：在创建表的时候，增加约束（比较麻烦复杂）
-- grade 表
KEY `FK_gradeid` (`gradeid`)
-- 学生表的gradeid字段 要去应用年级表的gradid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
-- student表
KEY `FK_gradeid` (`gradeid`)
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）
方式二：
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`)
以上操作都是物理外键，数据库级别的外键，不建议使用。（避免数据库过多造成困扰）
最佳实践
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键（程序去实现）
DML语言（全部记住）
数据库意义：数据存储，数据管理
DML语言：数据操作语言
- insert
- update
- delete
添加
INSERT INTO 表名([字段1，字段2，字段3])values（'值1','值2','值3',...）
INSERT INTO `grade`(`gradename`) VALUES ('大四');
-- 由于主键自增，我们可以省略主键 （如果不写表的字段，就会一一匹配）
-- 一般写插入语句，一定要数据和字段一一对应！
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大四')('大一');
注意事项：
- 字段和制度啦之间使用英文逗号隔开
- 字段是可以省略的，但是后面的值必须要一一对应，不能少
- 可以同时插入多条数据，VALUES后面的值，需要使用，隔开
修改
update 修改谁 （条件） set 原来的值 = 新值
-- 修改学员名字
UPDATE `student` SET `name`='laomao' WHERE id = 1;
-- 不指定条件的情况下：会改动所有表！
UPDATE `student` SET `name`='laomao';
条件：where子句 运算符 id等于某个值，大于某个值，在某个区间内修改
-- 通过多个条件定位数据
UPDATE `student` SET `name`='laomao' WHERE `name`='piaobo' AND sex='女'
删除
delete命令
DELETE FROM student WHERE id=1
TRUNCATE命令
作用：完全清空一个数据库表，表的结构和索引约束不会变！
TRUNCATE student
delete 和 TRUNCATE区别
- 相同点：都能删除数据，都不会删除表结构
- 不同：
- TRUNCATE 重新设置自增列，计数器会归零
- TRUNCATE 不会影响事务
DELETE删除的问题，重启数据库，现象
- INNODB 自增列会从1开始（存在内存当中，断点就会丢失）
- MYISAM 继续从上一个自增量开始（存在文件中，不会丢失）
DQL查询数据（最重点）
Data Query LANGUAGE :数据查询语言
- 所有的查询语言都用它 SELECT
- 简单的查询，复杂的查询它都能做
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语言
指定查询字段
-- 查询全部的学生
SELECT * FROM `student`
-- 查询指定字段,可以给字段和表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM `student` AS `s`;
-- 函数 CONCAT(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student
有的时候，列名字不是那么见名知意，我们起别名 AS 字段名 AS 别名
去重distinct
-- 查询一下哪些同学参加了考试
SELECT * FROM `result`
SELECT `StudentNo` FROM `result`
-- 发现重复数据去重
SELECT DISTINCT `StudentNo` FROM `result`
特殊：
SELECT VERSION() -- 查询系统版本 （函数）
SELECT 100-1 AS 计算结果 --用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）
-- 学院考试成绩 +1查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM `result`
WHERE 条件子句
检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成，结果是布尔值。
逻辑运算符
尽量使用英文字母
AND OR NOT 与或非
SELECT `StudentNo`,`StudentResult` FROM result
--查询成绩在95-100分之间
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
--除了1000号学生之外的同学的成绩
SELECT studentNo,`StudentResult` FROM result
WHERE NOT student=1000;
模糊查询：比较运算符
IS NULL
IS NOT NULL
BETWEEN AND
LIKE a LIKE b SQL匹配，如果a匹配b，则结果为真
IN a IN (a1,a2,a3…)
======模糊查询========
-- 查询姓马的同学
-- like结合 %（代表0到任意个字符） _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '马%'
WHERE StudentName Like '马__'
WHERE StudentName Like '%猫%'
WHERE StudentNo IN (1001,1002,1003);
WHERE `address` IS NULL
链表查询
JOIN 【Left Join 、 inner Join 、 Right Join】
SELECT * FROM student
SELECT * FROM result
/*
1. 分析需求，分析查询的字段来自哪些表（连接查询）
2. 确定使用哪种连接查询
确定交叉点（这两个表哪个数据是相同的）
*/
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
FROM result AS r
WHERE s.studentNo = r.studentNo
INNER JOIN resultNo = r.studentNo
SELECT s.StudentNo,studentName,subejectName,SudentResult
FROM student s
RIGHT JOIN Result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
FROM 表 XXX JOIN 连接的表 ON 交叉条件
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
-- 查询父子信息
SELECT `categoryName` AS '父栏目',`categoryName` AS '子栏目'
FROM `categoryid` AS a, `categroyid` AS b
WHERE a.`categoryid` = b.`pid`
分页和排序
排序：升序ASC 降序DESC
根据查询的结果 成绩排序
ORDER BY StudentResult DESC
分页
缓解数据库压力，给人的体验更好，瀑布流
limit 查询起始值，页面大小
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN 'result' r
ON s.`studentNo` = r.`studentNo`
INNER JOIN `subject` sub
ON sub.`subjectNo` = r.`SubjectNo`
WHERE `StudentResult` > 80
AND `SubjectName` = 'JAVA第一学年'
ORDER BY `StudentResult` DESC
LIMIT 0, 10
子查询
WHERE （这个值是计算出来的）
本质：在WHERE语句中嵌套一个子查询语句
SELECT `StudentNo`,`StudentName`,`StudentResult` FROM `Student` s
WHERE SubjectNo = (
SELECT `SubjectNo` FROM `Subject`
WHERE `SubjectName` = 'C语言-1'
)
ORDER BY `StudentResult` DESC
LIMIT 0,5
分组和过滤
SELECT SubjectName，AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段来分组
HAVING 平均分>80
MYSQL函数
常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND（） -- 返回一个0-1的随机数
SELECT SIGN（） -- 判断一个数的符号 0-0 负数返回-1 正数返回 1
-- 字符串函数
SELECT CHAR_LENGTH('天天向上') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我爱编程HelloWorld',1,2,'超级') -- 插入，替换
SELECT LOWER('LaoMao') -- 小写
SELECT UPPER（'LaoMao'） --大写
SELECT INSTR('LaoMao','M') -- 返回第一次出现的子串的索引
SELECT REPLACE('天天向上','天天','好好') -- 替换出现的指定字符串
SELECT SUBSTR('天天向上',3,4) --返回指定的子字符串
SELECT REVERSE('天天向上') --反转
-- 时间和日期函数
SELECT CURRENT_DATE() -- 当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 当地时间
--系统
SELECT USER()
SELECT VERSION()
聚合函数(常用)
-- 都能够统计表中的数据
SELECT COUNT(studeentName) FROM student; -- COUNT（指定列）【会忽略所有的NULL值】
SELECT COUNT(*) FROM student; -- COUNT(*)【不会忽略NULL值】 本质计算行数
SELECT COUNT(1) FROM student; -- COUNT(1)【不会忽略所有的NULL值】 本质计算行数
SELECT SUM(`StudentResult`) AS 总分 FROM `result`
SELECT AVG(`StudentResult`) AS 平均分 FROM `result`
SELECT MAX(`StudentResult`) AS 最高分 FROM `result`
SELECT MIN(`StudentResult`) AS 最低分 FROM `result`
-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT SubjectName，AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段来分组
HAVING 平均分>80
数据库级别的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,'laomao','123456'),(2,'lao','123'),(3,'mao','456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码
--插入的时候加密
INSERT INTO `testmd5` VALUES(4,'meng',MD5('123456'))
--如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='meng' AND pwd=MD5('123456')
小结
顺序很重要
SELECT DISTINCT 要查询的字段 FROM 表名 （注意：表和字段可以取别名）
xxx join 要连接的表 on 等值判断
WHERE （具体的值，子查询语句）
GROUP BY （通过哪个字段来分组）
HAVING （过滤分组后的信息，条件 和WHERE是一样的，位置不同）
ORDER BY （通过哪个字段排序） [ASC|DESC]
LIMIT startindex,pageSize
业务层面
查询：跨表，跨数据库。。。
事务
要么都成功，要么都失败
将一组SQL放在一个批次中去执行
事务原则：ACID原则 原子性，一致性，隔离性，持久性
- 原子性（Atomicity）：要么都成功，要么都失败
- 一致性（Consistency）：事务前后的数据完整性要保证一致
- 持久性（Durability）：事务一旦提交则不可逆，被持久化到数据库中
- 隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库是，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离。
隔离所导致的问题：脏读、不可重复读，虚度（幻读）
脏读：指一个事务读取了另外一个未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，知识某些场合不对）
虚读：是指在一个事务内读取了别的事务插入的数据，导致前后读取不一致。
-- 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_INCREAMENT,
`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; -- 恢复自动提交
索引
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构。
索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
- 主键索引（PRIMARY KEY）
- 唯一标识，主键不可重复，只能有一个
- 唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为 唯一索引
- 常规索引（KEY、INDEX）
- 默认的，index、key关键字；来设置
- 全文索引（FULLTEXT）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据
基础语法：
--索引的使用
1. 在创建表的时候给字段增加索引
2. 创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(studentName) AGAINST('猫');
测试索引
MYSQL是可以编程的
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DELCARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'7456789@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
END;
SELECT * FROM app_user WHERE `name` = '用户8999';
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户8999';
SELECT * FROM student
-- id _ 表名 _ 字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
索引在小数量的时候，用户不大，但是在大数据的时候，区别十分明显。
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加载常用来查询的字段上！
索引的数据结构
Hash类型的索引
Btree：INNODB的默认数据结构
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
权限管理和备份
用户管理
SQL yog可视化管理
用户管理
SQL命令操作
用户表：mysql.user
本质：读这张表，增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER laomao IDENTIFIED BY '123456'
-- 修改当前用户密码
SET PASSWORD = PASSWORD('111111')
-- 修改指定用户密码
SET PASSWORD FOR laomao = PASSWORD('111111')
-- 重命名
RENAME USER laomao to laomao2
-- 用户授权
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO laomao2
--查看权限
SHOW GRANTS FOR laomao2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM laomao2
-- 删除用户
DROP USER laomao2
数据库备份
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式 - 直接拷贝物理文件
- 在SQlyog可视化工具中手动导出
- 在想要导出的表或者库中，右键选择备份或导出
- 使用命令行导出
# 导出
# mysqldump -h 主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot-p123456 school student >D:/a.sql
mysqldump: [warning] using a password on the command line interface can be insecure.
# 导入
mysql -uroot -p123456
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql
# mysql -u用户名 -p密码 库名< 备份文件
防止数据丢失
把数据库给朋友
规范数据库设计
当数据库比较复杂的时候，我们就需要设计。
糟糕的数据库设计
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计 - 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计 - 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图 ER图
设计数据库的步骤（个人博客）
- 收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息，某个关键字，或者一些主字段） key：value
- 标识实体（把需求落地到每个字段）
- 标识实体之间的关系
- user–>blog
- user–>category
- user->user
- links
- user-user-blog
三大范式
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
三大范式
- 第一范式
保证每一列不可再分 - 第二范式
前提：满足第一范式
每张表只描述一件事情 - 第三范式
前提满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（规范数据库的设计）
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性！
- 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
JDBC（重点）
数据库驱动
驱动：声卡，显卡，数据库
我们的程序会通过 数据库驱动，和数据库打交道！
JDBC
SUN公司为了简化 开发人员的（对数据库的统一）操作，提供了一个（JAVA操作数据库的）规范|JDBC。
这些规范的实现由具体的厂商去做。。。
对于开发人员来说，我们只需要掌握JDBC接口的操作即可。
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
第一个JDBC程序
- 创建测试数据库
- 创建一个普通项目
- 导入数据库驱动【创建lib目录、添加jar包、Add as Library】
- 编写测试代码
public class JdbcFirstDemo{
public static void main(string[] args) throws Exception{
// 1. 加载驱动
Class.forName("com.mysql.jdbc.Driver"); // 固定写法
// 2. 用户信息和URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
// 3. 连接成功，数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
// 4. 执行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("birth="+resultSet.getObject("birthday"));
}
// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
statement对象
JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的excuteUpdate()方法，用于向数据库发送增删改的SQL语句，excuteUpdate执行完后，将会返回一个整数（即：增删改语句导致了数据库几行数据发生了变化）。
Statement.executeQuery() 方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
代码实现：
- 提取工具类
package com.laomao.lesson01.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 (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();
}
}
}
}
- 编写增删改：excuteUpdate()
package com.laomao.lesson01;
import com.laomao.lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "INSERT INTO user(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
" VALUES(8,'laomao','456123','789456@qq.com','2024-06-01')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
- 编写查：excuteQuery()
package com.laomao.lesson01;
import com.laomao.lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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";
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存在漏洞，会被攻击导致数据泄露。
SQL会被拼接
PreparedStatement对象
PreparedStatement可以防止注入、效率更好！
package com.laomao.lesson02;
import com.laomao.lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
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`,`id`,`NAME`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql); // 预编译SQL，先写SQL，不执行
// 手动给参数赋值
st.setInt(1,4);
st.setString(2,"laomao");
st.setString(3,"123456");
st.setString(4,"123456@qq.com");
// 注意点：sql.Date 数据库
// util.Date Java new Date().getTime() 获得时间戳
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);
}
}
}
使用IDEA连接数据库
- 右侧边栏 Database，选择mysql
- 连接成功后，可以选择数据库【Schemas， 选中数据库】
- 查看数据库内容【双击数据库，修改，绿色提交】
- 有默认控制器【可以写SQL】
事务
要么都成功，要么都失败
package com.laomao.lesson02;
import com.laomao.lesson01.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 conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false); // 关闭数据库的自动提交，自动开启事务
String sql1 = "update account set money = money - 100 WHERE NAME = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update account set money = money + 100 WHERE NAME = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
// 业务完毕，提交事务
conn.commit();
System.out.println("成功！");
} catch (SQLException e) {
try {
conn.rollback(); // 若失败，默认回滚
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
数据库连接池
数据库连接–执行完毕–释放
连接–释放十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
根据最小连接数设置常用连接数
最大连接数： 15 业务最高承载上限
大于最大连接数，排队等待
等待超时：100ms
编写连接池，实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要写连接数据库的代码了
DBCP
需要用到的jar包
commons-dbcp-1.4/commons-pool-1.6
C3P0
需要用到的jar包
c3p0-0.9.5.5/mchange-commons-java-0.2.19
结论：
无论使用什么数据源，本质是一样的，DataSource接口不会变，方法就不会变。
扫一扫
6628
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
