[大数据]MySQL笔记

MySQL

1. 数据库的列类型

数值

• tinyint 十分小的数据 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

• datetime yyyy-MM-dd HH:mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数

null

• 没有值，未知

2. 数据库的字段属性

Unsigned:

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

zerofill:

• 0填充的
• 不足的位数，使用0来填充，int(3),5–005

自增:

• 通常理解为自增，自动在上一条记录的基础上+1，（默认）
• 通常用来设计唯一主键~index，必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空 NULL not null

• 假设设置为not null，如果不给他赋值，就会报错
• NULL，如果不填写值，默认就是null

默认

• 设置默认的值
• sex，默认值为男，如果不指定该列的值，则会有默认的值

**拓展：**项目规范，必须要有这些字段

• id 主键
• version 乐观锁
• is_delete 伪删除
• gmt_create 创建时间
• gmt_update 修改时间

3. 数据表的类型

数据库引擎：

• INNODB 默认使用
• MYISAM 早些年使用

4. DML数据管理语言

4.1 外键

阿里JAVA规范：【强制】不得使用外键与级联，一切外键概念必须在应用层解决

4.2 DML语言

• insert
• update
• delete

4.3 添加

INSERT INTO student(username,pwd,sex) VALUES('zhangsan','123456','1');
INSERT INTO student(username,pwd,sex) VALUES('jack','123456','1'),('rose','123456','0');

4.4 修改

UPDATE student SET username='lisi' where id=2;
UPDATE student SET username='lisi',sex='0' where id=2;

4.5 删除

DELETE FROM student WHERE id=2

TRUNCATE与DELETE

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

• 相同点：都能删除数据，都不会删除表结构
• 不同点：TRUNCATE 重新设置自增列，计数器会归零，不会影响事务

了解即可：DELETE删除的问题，重启数据库后的现象

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

5. DQL数据查询语言

5.1 指定查询字段

-- 查询全部
SELECT * FROM tb_category

-- 查询指定列
SELECT name,parent_id FROM tb_category

-- 起别名AS
SELECT name AS 姓名,parent_id AS 父id FROM tb_category AS c

-- CONCAT
SELECT CONCAT('名字：',name) AS 新名字 FROM tb_category

-- 去重DISTINCT
SELECT DISTINCT parent_id FROM tb_category

-- 查询系统版本
SELECT VERSION()

-- 用来计算（表达式）
SELECT 100*3-1 AS 计算结果

-- 查询自增的步长（变量）
SELECT @@auto_increment_increment

-- 查看排序，并+1查看
SELECT sort+1 AS 新排序 FROM tb_category

5.2 where条件子句

-- AND
SELECT id,sort FROM tb_category WHERE sort>=1 AND sort<=3

-- 模糊查询（区间）
SELECT id,sort FROM tb_category WHERE sort BETWEEN 1 AND 3

-- != not
SELECT id,sort FROM tb_category WHERE sort!=1
SELECT id,sort FROM tb_category WHERE NOT sort=1

-- like	%(代表0到任意个字符)	_(一个字符)
SELECT id,name FROM tb_category WHERE name LIKE '%电子%'
SELECT id,name FROM tb_category WHERE name LIKE '电子_'

-- IN
SELECT id,name FROM tb_category WHERE parent_id IN (0,1,2,3)

-- NULL
SELECT id,name FROM tb_category WHERE name='' OR name IS NULL

-- NOT NULL
SELECT id,name FROM tb_category WHERE name!='' OR name IS NOT NULL

5.3 联表查询

-- inner join
SELECT
	s.studentno,
	studentname,
	subjectno,
	studentresult -- studentno前面要加点,要么从s表查,要么从r表查	
FROM
	student AS s -- 因为两个表都有studentno,所以给他们分别命名否则会报错
	INNER JOIN result AS r 
WHERE
	s.studentno = r.studentno 
	
-- right join
SELECT
	s.studentno,
	studentname,
	subjectno,
	studentresult 
FROM
	student s
	RIGHT JOIN result r ON s.studentno = r.studentno 
	
-- left join
SELECT
	s.studentno,
	studentname,
	subjectno,
	studentresult 
FROM
	student s
	LEFT JOIN result r ON s.studentno = r.studentno 
	
-- 查询缺考的同学
SELECT
	s.studentno,
	studentname,
	subjectno,
	studentresult 
FROM
	student s
	LEFT JOIN result r ON s.studentno = r.studentno 
WHERE
	studentresult IS NULL 
	
-- 思考题 (查询了参加考试的同学信息:学号,学生姓名,科目名,分数)
SELECT
	s.studentno,
	studentname,
	subjectname,
	studentresult 
FROM
	student s
	RIGHT JOIN result r ON r.studentno = s.studentno
	INNER JOIN SUBJECT sub ON r.subjectNo = sub.subjectNo 

自连接：自己的表和自己的表连接（一张表拆为两张一样的表即可）

SELECT
	a.NAME AS '父',
	b.NAME AS '子' 
FROM
	tb_category AS a,
	tb_category AS b 
WHERE
	a.id = b.parent_id

5.4 分页和排序

分页:limit

排序:order by 升序ASC 降序DESC

-- 分页,要求每页只显示5条数据
-- 语法: limit 起始值,页面的大小
-- limit 0,5 是1-5
-- 第一页 limit 0,5     (1-1)*5 
-- 第二页 limit 5,5    (2-1)*5
-- 第三页 limit 10,5    (3-1)*5
-- 第N页 limit          (N-1)*5
-- 规律:   (n-1)*pageSize,pageSize,n表示当前页
-- [数据总数/页面大小 = 总页数]
SELECT
	s.`StudentNo`,
	`StudentName`,
	`SubjectName`,
	`StudentResult` 
FROM
	`student` s
	INNER JOIN `result` r ON s.studentNo = r.StudentNo
	INNER JOIN `subject` sub ON r.`subjectNo` = sub.`SubjectNo` 
WHERE
	`subjectName` = '数据库结构-1' 
ORDER BY
	studentResult ASC -- 升序
LIMIT 0,5 


-- 查询,Java第一学年,课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名称,分数)
SELECT
	s.`StudentNo`,
	`StudentNmae`,
	`SubjectName`,
	`StudentResult` 
FROM
	`student` s
	INNER JOIN `result` r ON s.StudentNo = r.`SubjectNo` 
WHERE
	SubjectName = `Java第一学年` 
	AND StudentResult >= 80 
ORDER BY
	StudentResult DESC 
	LIMIT 0,10

5.5 子查询

/*
   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
   嵌套查询可由多个子查询组成,求解的方式是由里及外;
   子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/ 
-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT
	studentno,
	r.subjectno,
	StudentResult 
FROM
	result r
	INNER JOIN `subject` sub ON r.`SubjectNo` = sub.`SubjectNo` 
WHERE
	subjectname = '数据库结构-1' 
ORDER BY
	studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT
	studentno,
	subjectno,
	StudentResult 
FROM
	result 
WHERE
	subjectno =(
	SELECT
		subjectno 
	FROM
		`subject` 
	WHERE
		subjectname = '数据库结构-1' 
	) 
ORDER BY
	studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT
	s.studentno,
	studentname 
FROM
	student s
	INNER JOIN result r ON s.`StudentNo` = r.`StudentNo`
	INNER JOIN `subject` sub ON sub.`SubjectNo` = r.`SubjectNo` 
WHERE
	subjectname = '高等数学-2' 
	AND StudentResult >= 80 

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT
	r.studentno,
	studentname 
FROM
	student s
	INNER JOIN result r ON s.`StudentNo` = r.`StudentNo` 
WHERE
	StudentResult >= 80 

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT
	r.studentno,
	studentname 
FROM
	student s
	INNER JOIN result r ON s.`StudentNo` = r.`StudentNo` 
WHERE
	StudentResult >= 80 
	AND subjectno =(
	SELECT
		subjectno 
	FROM
		`subject` 
	WHERE
		subjectname = '高等数学-2' 
	) 

-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT
	studentno,
	studentname 
FROM
	student 
WHERE
	studentno IN (
	SELECT
		studentno 
	FROM
		result 
	WHERE
		StudentResult >= 80 
		AND subjectno =(
		SELECT
			subjectno 
		FROM
			`subject` 
		WHERE
			subjectname = '高等数学-2' 
		) 
	)

5.6 分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT
	`SubjectName`,
	AVG( StudentResult ),
	MAX( StudentResult ) 
FROM
	result r
	INNER JOIN `Subject` sub ON r.SubjectNo = sub.SubjectNo 
GROUP BY
	r.SubjectNo -- 通过什么字段来分组	
HAVING
	AVG( StudentResult )> 80

6. MySQL函数

6.1 常用函数

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度
SELECT UPPER('abc') 
SELECT LOWER('ABC')
SELECT REPLACE('坚持就能成功','坚持','努力')

-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'

-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME()  -- 本地时间
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()

6.2 聚合函数

6.3 数据库级别的MD5加密

什么是MD5

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

MD5不可逆，具体的MD5是一样的

MD5破解原理，背后有一个字典，MD5加密后的值，加密前的值

7. 事务

https://blog.csdn.net/weixin_46204056/article/details/122647681

ACID

• 原子性（Atomicity）：要么都成功，要么都失败
• 一致性（Consistency）：事务前后的数据完整性要保持一致
• 持久性（Durability）–事务提交：事务一旦提交就不可逆转，被持久化到数据库中
• 隔离性（Isolation）：事务产生多并发时，互不干扰

隔离产生的问题：

• 脏读：指一个事务读取了另外一个事务未提交的数据。
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
• 虚读(幻读)：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

-- mysql 自动开启事务提交
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启（默认的）

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

-- 事务开启

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

INSERT XX
INSERT XX

-- 提交 ： 持久化(成功)
COMMIT 

-- 回滚：  回到原来的样子（失败）
ROLLBACK

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

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

8. 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引在小数据的时候，用处不大，但是在大数据的时候，区别十分明显

8.1 索引的分类

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

• 主键索引 （PRIMARY KEY）
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引 （UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
• 常规索引（KEY/INDEX）
  • 默认的，index,key关键字来设置
• 全文索引（FULLTEXT）
  • 在特点的数据库引擎下才有，MyISAM
  • 快速定位数据

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

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

-- 增加一个索引
ALTER TABLE 表 ADD FULLTEXT INDEX 索引名（字段名）

-- EXPLAIN 分析sql执行状况
EXPLAIN SELECT * FROM student -- 非全文索引

8.2 索引原则

索引不是越多越好

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

索引的数据结构

Hash 类型的索引

Btree: 默认innodb 的数据结构

阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

9. MySQL备份

保证重要数据不丢失

MySQL数据库备份的方式

• 直接拷贝物理文件
• 在可视化工具中手动导出：在想要导出的表或者库中，右键选择备份和导出

10. 规范数据库设计

10.1 数据库设计

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计：

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

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

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

设计数据库的步骤（个人博客）

1、收集信息，分析需求

• 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
• 分类表（文章分类，谁创建的）
• 文章表（文章的信息）
• 友链表（友链信息）
• 自定义表（系统信息，某个关键的字，或者某些主字段）
• 说说表（发表心情…id ,content ,time）

2、标识实体（把需求落地到每个字段）

3、 标识实体之间的关系

• 写博客 user–>blog
• 创建分类 user–>category
• 关注 user–>user
• 友链–>links
• 评论 user–>user

10.2 三大范式

为什么需要数据规范化？

• 信息重复

• 更新异常

• 插入异常

• 删除异常

  • 无法正常显示异常

  • 丢失有效的信息

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

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

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。（规范数据库的设计）

规范性和性能的问题

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

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

11. JDBC

11.1 测试代码

package com.kuang.lesson01;
//我的第一个JDBC程序

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcFirstDemo {
    public static void main(String[] args) throws Exception {
        //1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法
        //2. 用户信息和url
        //useUnicode=true&characterEncoding=utf8&&useSSL=true
        String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
        String name = "root";
        String password = "123456";

        //3. 连接成功，返回数据库对象  connection代表数据库
        Connection connection= DriverManager.getConnection(url,name,password);
        //4. 执行SQL的对象 statement 执行SQL的对象
        Statement statement = connection.createStatement();
    
        //5. 执行SQL的对象 去执行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("password+"+resultSet.getObject("PASSWORD"));
            System.out.println("email+"+resultSet.getObject("email"));
            System.out.println("birthday+"+resultSet.getObject("birthday"));
        }
        //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

}

步骤总结：

1.加载驱动

2.连接数据库 DriverManager

3.获取执行SQL的对象 Statement

4.获得返回的结果集

5.释放连接

11.2 statement对象

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

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句， executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

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

11.3 SQL注入问题

sql存在漏洞，会被攻击导致数据泄露 SQL会被拼接 or

package com.thb.study2;

import com.thb.study2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static com.thb.study2.Utils.JdbcUtils.getConnection;

public class SQL {
    public static void main(String[] args) {

        //SQL注入
        login("' or '1=1","' or '1=1");
    }
    public static void login(String name,String password){
        
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;

        try {
            conn = getConnection();//获取连接
    
            st = conn.createStatement();//获取SQL执行对象
            String sql = "select * from users where `NAME`='"+ name +"'  AND `PASSWORD`='"+ password +"'" ;
            rs=st.executeQuery(sql);//查询完毕返回结果集
    
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("==========================================");
            }
            JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}

11.4 PreparedStatement对象

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

防止SQL注入本质，传递字符 带有“ ”，转义字符会被转义，使用?占位符

conn = getConnection();//获取连接            
String sql = "SELECT * FROM users WHERE `NAME`=? and `PASSWORD`=?";            
st = conn.prepareStatement(sql);//获取SQL执行对象             
st.setString(1, name);            
st.setString(2, password);             
rs = st.executeQuery();//查询完毕返回结果集

11.5 JDBC事务

• 原子性：要么全部完成，要么都不完成
• 一致性：结果总数不变
• 隔离性：多个进程互不干扰
• 持久性：一旦提交不可逆，持久化到数据库了

? 隔离性的问题：

• 脏读： 一个事务读取了另一个没有提交的事务
• 不可重复读：在同一个事务内，重复读取表中的数据，表发生了改变
• 虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

代码实现：

1. 开启事务conn.setAutoCommit(false);
2. 一组业务执行完毕，提交事务
3. 可以在catch语句中显示的定义回滚，但是默认失败会回滚

package com.thb.study3;

import com.thb.study2.Utils.JdbcUtils;

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

   public class Test03 {
   public static void main(String[] args) {
       Connection connection = null;
       PreparedStatement ps = null;
       try {
           connection = JdbcUtils.getConnection();
           connection.setAutoCommit(false);

           String sql = "update account set money=money-100 where `name`='A'";
           ps = connection.prepareStatement(sql);
           ps.executeUpdate();
           
           String sql2 = "update account set money=money+100 where `name`='B'";
           ps = connection.prepareStatement(sql2);
           ps.executeUpdate();
       
           connection.commit();
           System.out.println("成功d=====(￣▽￣*)b");


        } catch (SQLException e) {
            //默认失败会回滚
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,ps,null);
        }
    }

}

12. 数据库连接池

数据库连接–执行完毕–释放
连接–释放 十分浪费资源
池化技术： 准备一些预先的资源，过来就连接预先准备好的
编写连接池，实现一个接口 DateSource

• DBCP
• C3P0
• Druid: 阿里巴巴