1、初识 MySQL

JavaEE: 企业级 Java 开发 Web

前段（页面展示，数据）

后端（连接点：连接数据库 JDBC；链接前端：控制，控制反转，给前台传数据）

数据库（存数据）

1.1 数据库分类

关系型数据库 (SQL)：

• MySQL、Oracle、Sql Server、DB2、SQLlite

• 通过表和表、行和列之间的关系进行数据的存储

非关系型数据库 (NoSQL：Not Only SQL)：

• redis、MongDB

• 非关系型数据库，对象存储，通过对象自身的属性来决定

1.2 DBMS（数据库管理系统）

• 数据库的管理软件，科学有效的管理数据。维护和获取数据

• MySQL：关系型数据库管理系统

基本操作：

update mysql.user set authentication_string = password('') where user = '' and Host = 'localhost'; /修改用户密码/

show databases; /查看所有数据库/

use school; /使用数据库/

show tables; /查看表/

describe student; /查看表信息/

create database school; /创建一个数据库/

2、操作数据库

操作数据库 > 操作数据库中的表 > 操作表中的数据

2.1 操作数据库

1. 创建数据库

create database [if not exist] dataBaseName

2. 删除数据库

drop database [if exist] dataBaseName

3. 使用数据库

use `dataBaseName`  -- 关键词加 `

4. 查看数据库

show databases;

2.2 数据库的列类型

1. 数值

• tinyint 十分小的数据 1 个字节

• smallint 较小的数据 2 个字节

• mediumint 中等大小的数据 3 个字节

• int 标准的整数 4 个字节

• bigint 较大的数据 8 个字节

• float 单精度浮点数 4 个字节

• double 双精度浮点数 8 个字节

• decimal 字符串形式的浮点数，金融计算的时候一般使用 decimal

2. 字符串

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

• varchar 可变长的字符串 0-65535

• tingtext 微型文本 2^8-1

• text 文本串 2^16-1

3. 时间日期

• date YYYY-MM-DD 日期

• time HH🇲🇲ss 时间

• datetime YYYY-MM-DD HH🇲🇲ss 最常用的时间格式

• timestamp 时间戳，1970.1.1 到现在的毫秒数！也较为常用

• year 年份表示

4. null

• 没有值，未知

• 注意：不要使用 NULL 进行运算，结果为 NULL

2.3 数据库的字段属性

Unsigned:

• 无符号的整数

• 声明了该列不能为负数

zerofill：

• 0 填充的

• 不足的位数使用 0 来填充

自增：

• 通常理解为自增，自动在上一条记录的基础上 +1（默认）

• 通常用来设计唯一的主键 ~index，必须是整数类型

• 可以自定义设计主键的起始值和步长

非空：

• 假设设置为 not null，如果不给它赋值，就会报错

• null，如果不填写值，默认就是 null

默认：

• 设置默认的值！

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

show create database school; /* 查看创建数据库的语句 */
show create table student; /* 查看创建表的语句 */
desc student; /* 显示表的结构 */

2.4 数据表的类型

常规使用操作：

• MYISAM：节约空间，速度较快

• INNODB：安全性高，支持事务的处理，多表多用户操作

2.5 修改删除表

alter table name1 rename as name2; 修改表明
alter table name add ziduan shuxing; 新增字段
alter table name modify age varchar(11); 修改约束
alter table name change age age1 int(1); 修改字段名
alter table name drop age1; 删除表的字段
drop table if exists name; 如果表存在，则删除

3、MySQL 数据管理

3.1 外键

• 物理外键，数据库级别的外键，不建议使用 (避免数据库过多，造成困扰)

key `FK_name` (`name`),
constraint `FK_name` FOREIGN KEY (`name`) references `table`(`name`)    // 外键

alter table `tablename` add constraint `FK_name` foreign key(`name`) references 'tablename2'(`name`)

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行 (数据) 和列(字段)

• 我们想要使用多张表的数据，想使用外键 (程序来实现)

3.2 DML 语言

3.3 添加

-- 插入语句

insert into table () values(),()

3.4 修改

-- 更新语句

update table set column1 = value,column2 = value where [条件]
update school.student set birthday = current_time() where id = 1; 

3.5 删除

-- 删除语句

delete from table where [条件]

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

truncate table table; -- 自增会归零

4、DQL 查询数据

4.1 DQL

(Date Query Language：数据查询语言)

• 所有的查询都用它 Select

• 简单的查询，复杂的查询都能做

• 数据库中最核心语言，最重要的语句

• 使用频率最高的语句

4.2 指定查询字段

select * from table where [条件]

SELECT version(); -- 查看系统版本

-- 联表查询

leftjoin; -- 左联
innerjoin; -- 内联
rightjoin; -- 右联

-- 自连接：自己的表和自己的表连接，核心：一张表拆成两张一样的表

select a.name,b.name from table1 a,table1 b where a.param1 = b.param2

-- 分页

limit 0,5; -- 起始值，页面的大小
--【pageSize: 页面大小;(n-1)*pageSize: 起始值;n: 当前页】
-- 总页数: 数据总量 / 页面大小

-- 子查询

-- 本质：在 where 语句中嵌套一个子查询语句
where (select from)

-- 常用函数

ABS();-- 绝对值
CEILing();-- 向上取整
FLOOR();-- 向下取整
RAND();-- 返回 0~1 之间的随机数
SIGN();-- 判断一个数的符号, 负数返回 -1，整数返回 1

-- 字符串函数

CHAR_LENGTH();-- 字符串长度
CONCAT();-- 拼接字符串
INSERT("啊啊"，1，2,"熬熬");-- 查询，从某个位置开始替换某个长度
INSTR('aaabbbccd','d');-- 返回第一次出现子串的位置
REPLACE('aabbcc','bb','ww');-- 替换出现的指定字符串
SUBSTR('aabbcc',2,3);-- 返回指定的子字符串,(源字符串，截取的位置，截取的个数)
REVERSE();-- 反转

-- 时间和日期函数

CURRENT_DATE();-- 获取当前日期

-- 系统

SYSTEM_USER();
USER();
VERSION();

-- 聚合函数

COUNT();
AVG();
MAX();
MIN();

5、事务

5.1 什么是事务

要么都成功，要么都失败

事务原则：ACID（原子性，一致性，隔离性，持久性）

set autocommit = 0;-- 关闭自动提交
start transaction;-- 标记一个事务的开始，从这之后的 SQL 都在同一个事务内
commit;-- 提交，持久化 (成功！)
rollback;-- 回滚，回到原来的样子 (失败！)
set autocommit = 1;-- 开启自动提交
savepoint 保存点名;-- 设置一个事务的保存点
rollback to savepoint;-- 回滚到保存点
release savepoint 保存点名;-- 撤销保存点

5.2 事务流程举例

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
​
set autocommit=0;
start transaction
update account set money=money-500 where name = 'A';
update account set money=money+500 where name = 'B';
commit;
rollback;
set autocommit=1;

6、索引

MySQL 官方定义：索引 (Index) 是帮助 MySQL 高效获取数据的数据结构。

6.1 索引的分类

• 主键索引 (primary key)

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

• 唯一索引 (unique key)

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

• 常规索引 (key)

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

• 全文索引 (full text)

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

  • 快速定位数据

-- 索引的使用
-- 1. 在创建表的时候给字段增加索引
-- 2. 创建完毕后，增加索引
-- 显示所有索引信息
show index from `table`;
-- 增加一个索引
alter table `tableName` add fulltext index ` 索引名 `(` 列名 `);
--explain 分析 SQL 执行的状况
explain select * from tablename;-- 非全文索引
explian select * from tablename where MATCH('字段名') against('内容');

6.2 创建索引

-- id_ 表名 _ 字段名
-- create index 索引名 on 表 (字段)
create index id_student_name on student(`name`); /* 创建索引 */

6.3 索引原则

• 索引不是越多越好

• 不要对经常变动数据加索引

• 小数据量的表不需要加索引

• 索引一般加在常用来查询的字段

-- 索引的数据结构
-- Hash 类型的索引
-- Btree InnoDB 默认的数据结构

7、权限管理和备份

7.1 用户管理

-- 可视化管理
-- SQL 命令操作
-- 用户表:mysql.user
-- 本质: 对用户表的
create user testa identified by '123456';
rename testa to testb;
grant all privileges on *.* to testa; -- 授予全部的权限
show grants for testa; -- 查询指定用户的权限
show grants for root@localhost; -- 查看 root 管理员权限
revoke all privileges on *.* from testa; -- 撤销权限 revoke 哪些权限，在哪个库撤销，给谁撤销
drop user testa; -- 删除用户

7.2 MySQL 备份

为什么要备份：

• 保证重要数据不丢失

• 数据转移

MySQL 数据库备份方式：

• 直接拷贝物理文件

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

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

mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql -- 导出
source D:/a.sql -- 导入 登录的情况下 切到对应数据库

8、规范数据库设计

三大范式

• 1NF：要求数据库的每一列都是不可分割的原子数据项

• 2NF：在 1NF 的基础上，非码属性必须完全依赖于候选码（在 1NF 的基础上消除非主属性对主码的部分函数依赖）

• 3NF：在 2NF 的基础上，任何非主属性不依赖于其他非主属性（在 2NF 的基础上消除传递依赖）

规范和性能问题：

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

• 考虑商业化的需求和目标，（成本、用户体验）数据库的性能更加重要

• 在规范性能问题的时候，需要适当的考虑一下 规范性

• 故意给某些表增加一些冗余的字段（从多表查询变为单表查询）

• 故意增加一些计算列（从大数据量降低为小数据量的查询）

9、JDBC

9.1 JDBC

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

这些规范的实现由具体的厂商去做

对于开发人员来说 ，只需要掌握 JDBC 接口的操作即可

// 我的第一个 JDBC 程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");// 固定写法，加载驱动
        //2. 用户信息和 url
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String userName = "root";
        String passWord = "stujc0828";
        //3. 连接成功，返回数据库对象
        Connection con = DriverManager.getConnection(url,userName,passWord);
        //4. 执行 SQL 的对象
        Statement statement = con.createStatement();
        //5. 执行 SQL 的对象去执行 SQL，可能存在结果，查看返回结果
        String sql = "select * from users";
        ResultSet ret = statement.executeQuery(sql);
​
        while (ret.next()){
            System.out.println("id="+ret.getObject("id"));
            System.out.println("name="+ret.getObject("name"));
            System.out.println("password="+ret.getObject("password"));
            System.out.println("email="+ret.getObject("email"));
            System.out.println("birthday="+ret.getObject("birthday"));
        }
        //6. 释放连接
        ret.close();
        statement.close();
        con.close();
​
    }
}

9.1 工具类

public class JdbcUtil {
    private static String driver = null;
    private static String url = null;
    private static String userName = null;
    private static String passWord = null;
​
    static {
        try{
            InputStream in = JdbcUtil.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 (IOException | ClassNotFoundException 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();
            }
        }
    }
}

public class Test {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet ret = null;
        try {
            con = JdbcUtil.getConnection();
            st = con.createStatement();
            String sql = "select * from users";
            ret = st.executeQuery(sql);
            while (ret.next()){
                for(int i=1;i<6;i++){
                    System.out.println(ret.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.release(con,st,ret);
        }
    }
}

9.2 PrepareStatemen 对象

• 可以防止 SQL 注入

• 效率更高

public class TestPrepareStatement {
    public static void main(String[] args) {
        Connection  con = null;
        PreparedStatement pst = null;
        ResultSet ret = null;
        try {
            con = JdbcUtil.getConnection();
            String sql = "select * from studeng where id = ?";
            pst = con.prepareStatement(sql);    // 预编译 SQL，先写 SQL，然后不执行
            pst.setInt(1,1);
            ret = pst.executeQuery();
            while (ret.next()){
                for(int i=1;i<6;i++){
                    System.out.println(ret.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.release(con,pst,ret);
        }
    }
}

9.3 事务

隔离性问题：

• 脏读：一个事务读取了另一个没有提交的事务

• 不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变

• 幻读：在一个事务内，读到了别人插入的数据，导致前后读出来的结果不一致

public class TestTransaction {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtil.getConnection();
            // 关闭数据库的自动提交，自动会开启事务
            con.setAutoCommit(false);
            String sql1 = "update account set money = money-100 where name ='A';";
            st = con.prepareStatement(sql1);
            st.executeUpdate();
//            int x = 1/0;
            String sql2 = "update account set money = money+100 where name ='B';";
            st = con.prepareStatement(sql2);
            st.executeUpdate();
​
            con.commit();
            System.out.println("成功");
        } catch (SQLException e) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtil.release(con,st,rs);
        }
    }
}

10、数据库连接池

拓展

/* 每一个表，都必须存在以下 5 个字段！用来做项目用的，表示一个记录存在意义
​
id 主键
​
`version`  乐观锁
​
is_delete  伪删除
​
gmt_create  创建时间
​
gmt_update  修改时间
​
*/

-- 数据库级别的 MD5 加密

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

insert into table (pwd) values (MD5(''));
update table set pwd=MD5(pwd);