MySQL：MySQL基础（二）

1、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/functions.html
常用函数、

• 数学运算
  • select abs(-8) — 绝对值
  • select ceiling(9.4) —向上取整
  • select floor(9.4) — 向下取整 9
  • rand() — 0-1之间的随机数
  • sign() — 判断一个数的符号(-1 0 1)
• 字符串函数
  • select char_length(‘我的长度’) — 字符串长度
  • select concat(‘我’,’爱’,’你’) — 拼接字符串
  • select insert(‘我爱编程’,1,2,’超级热爱’) — 添加，替换（超级热爱编程）
  • select lower(‘KUANGSHEN’) — 小写
  • select upper(‘kuangshen’) — 大写
  • select instr(‘kuangshen’,’h’) — 返回第一次出现的子串的索引
  • select replace(‘狂神’,’神’,’努力’) — 替换出现的指定字符串
  • select substr(‘狂神说坚持就是胜利’,4,6) — 返回指定的子字符串（源字符串，截取位置，截取的长度）
  • select reverse(‘狂神说坚持就是胜利’) — 反转
• 时间和日期函数（记住）
  • select current_date() — 获取当前日期
  • select curdate() — 获取当前日期
  • select now() — 获取当前的时间
  • select localtime() — 本地时间
  • 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() — 版本

聚合函数（常用）

   - count()
   - sum()
   - avg()
   - max()
   - min()

-- 统计表中的数据
    -- count(字段)，会忽略所有的null值
    -- count(*),不会忽略null值
    -- count(1),不会忽略null值
select count(studentname) from student;
select sum(`studentResult`) as 总和 from result
select avg(`studentResult`) as 平均分 from result
select max(`studentResult`) as 最高分 from result
select min(`studentResult`) as 最低分 from result
-- 查询不同课程的平均分，最高分，平均分大于80
-- 核心：（根据不同的课程分组）
select subjectName, avg(studentResult) as 平均分,max(studentResult) as 最高分, min(studentResult) as 最低分
from result r
inner join `subject` sub
on r.`subjectNo` = sub.`subjectNo`
group by r.subjectNo -- 通过什么字段来分组
having 平均分>80

2、 数据库级别的MD5加密（扩展）

MD5: 主要增强算法复杂度和不可逆性
MD5不可逆，具体的值的md5是一样的

update 表名 set pwd = md5(pwd);
-- 插入的时候加密
insert into 表名 values(4,'小明',md5('123456'))
-- 校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
select * from 表名 where `name`='xiaoming' and pwd=md5('123456')

3、事务（重点）

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个原则（ACID）：

• 原子性（Atomicity，或称不可分割性）：一个事务（transaction）中的所有操作，要么都成功，要么都失败，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性（Isolation，又称独立性）：多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离导致的问题：

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

四种隔离级别

• 读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，可能会产生脏读、不可重复读、幻读等情况。
• 读已提交就是只能读到已经提交的内容，可以避免脏读的产生，属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server），但如果想要避免不可重复读或者幻读，就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句（我会在进阶篇里讲加锁）。
• 可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的，可以避免不可重复读和脏读，但无法避免幻读。MySQL 默认的隔离级别就是可重复读。
• 可串行化，将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是最高级别的隔离等级，可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

测试事务

1. 关闭自动提交
2. 开启一个事务
3. 提交 回滚
4. 开启自动提交 回到开始事务

-- mysql 默认开启事务自动提交
set autocommit = 0 /*关闭*/
set autocommit = 1 /*开启（默认的）*/
-- 手动处理事务
set autocommit = 0 -- 关闭自动提交
-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
-- 提交： 持久化（成功）
commit
-- 回滚：回到了原来的样子（失败）
rollback
-- 事务结束
set autocommit = 1 -- 开启自动提交
savepoint 保存点名 --设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存点

模拟场景

-- 转账
-- 创建数据库
create database shop character set utf8 collate utf8_general_ci use shop
-- 模拟转账：事务
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; -- 恢复默认值

总结：

隔离级别越低，意味着系统吞吐量（并发程度）越大，但同时也意味着出现异常问题的可能性会更大。在实际使用过程中我们往往需要在性能和正确性上进行权衡和取舍，没有完美的解决方案，只有适合与否。

4、索引

MySQL官方对索引的定义为：**索引（Index）是帮助MySQL高效获取数据的数据结构。**提取句子主干，就可以得到索引的本质，索引的本质是数据结构。

索引的分类：

• 主键索引 （primary key）
  • 唯一标识，主键不可重复，只能有一个列作为主键
• 唯一索引 （unique key）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • unique key indetitycard (indetitycard)
• 常规索引 （key/index）
  • 默认的，index/key 关键字来设置
• 全文索引 （fulltext）
  • 在特定的数据库引擎下才有，mysql

-- 显示所有的索引信息
show index from student
-- 增加一个全文索引
-- 索引名（列名）
alter table `student` add fulltext index `studentName`(`studentName`);
-- explain 分析SQL执行的状况
explain select * from student
explain select * from student 
-- 函数
delimiter $$ -- 写函数前必须要写，标志
create function mock_data()
returns int
begin
    declare num int default 10000;
    declare i int default 0;
    while i < num do
        -- 插入语句
        insert into app_user(`name`,`email`,`phone`)
        values(concat('用户',i),'24567@qq.com',concat('18',floor(rand()*((9999999-100000) +1000000000))))
        set i = i + 1;
    end while;
    return i;
end;
-- 使用函数
select mock_data();
-- id_表名——字段名
-- create index 索引名 on 表（字段）
create index id_app_user_name on app_user(`name`);
select * from app_user where `name` = '用户999';

添加索引

1. 创建表时添加索引
2. alert
3. create index id_app_user_name on app_user(name);

索引原则

codingLabs-Mysql索引背后
http://blog.codinglabs.org/articles/theory-of-mysql-index.html

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

• Hash类型的索引
• Btree: innodb默认数据结构

5、数据库备份

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

mysql数据库备份的方式

• 直接拷贝物理文件
• 在sqlyog可视化工具中手动导出
  • 右键数据库-> 导出 ->sql转储、
• 使用命令行导出 mysqldump
  • mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
    • mysqldump -h主机 -u用户名 -p密码 数据库 表名1, 表名2 表名3 >物理磁盘位置/文件名
    • mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
  • mysqldump -hlocalhost -uroot -p123456 school student >D:/A.sql
• 使用命令行导入 source
  • mysql -uroot -p123456
  • use school;
  • source d:/a.sql
  • mysql -u用户名 -p密码 库名< 备份文件

6、权限管理

用户管理

-- 创建用户
create user kuangshen identified by '123456'
-- 修改密码（修改当前用户密码）
set password = password('111111')
-- 修改密码（修改指定用户密码）
set password for kuangshen = password('11223');
-- 重命名
rename user 原来名字 to 新的名字
rename user kuangshen to kuangshen2
-- 用户授权
all privileges 全部的权限 库.表 
all privileges 除了给别人授权，其他都能够使用
grant all privileges on *.* to kuangshen2
-- 查看权限
show grants for kuangshen2
-- 查看管理员的权限
show grants for root@loaclhost
-- 撤销权限
revoke 哪些权限，在哪个库撤销，给谁撤销
revoke all privileges on *.* from kuangshen2
-- 删除用户
drop user kuangshen

7、数据库设计

分析需求：分析业务和需要处理的数据库需求
概要设计: 设计关系图E-R图
数据库中的字段用下划线命名

数据库的归约，三大范式

三大范式

第一范式(1NF)

• 要求数据库表的每一列都是不可分割的原子数据项
• 保证每一列不可再分

第二范式（2NF）

• 在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
• 每张表只描述一件事情

第三范式（3NF）

• 在2NF基础上，任何非主属性不依赖与其它非主属性（在2NF基础上消除传递依赖）
• 确保数据表中的每一列数据都和主键直接相关，而不能间接先关

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

8、JDBC(重点)

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。
步骤总结：

1. 加载驱动
2. 连接数据库DrverManager
3. 获得执行sql的对象Stament
4. 获得返回的结果集
5. 释放连接

package com.jin.lesson01;

import java.sql.*;

/**
 * Create with IntelliJ IDAE
 *
 * @Author: JINLEI
 * @Description: JDBC
 * @Date: 2022/3/4
 * @Time: 19:47
 **/
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2、用户信息和url
        String url = "jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "root";
        //3、连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
        //4、执行SQL的对象
        Statement statement = connection.createStatement();
        //5、执行SQL的对象去执行SQL，查看返回结果
        String sql = "SELECT * FROM category";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,封装了我们查询的出来的结果
        while (resultSet.next()){
            System.out.println("cid"+ resultSet.getObject("cid"));
            System.out.println("category"+ resultSet.getObject("category"));
        }
        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

url
    - jdbc:mysql://主机地址：端口号/数据库名？参数1&参数2&参数3
    - oralce -- 1521
    - jdbc:oracle:thin:@localhost:1521:sid
connection 代表数据库
数据库设置自动提交
    - connection.setAutoCommit();
事务提交
    - connection.commit();
事务回滚
    - connection.rollback();
Statement执行SQL的对象  PrepareStatement执行SQL的对象
    - statement.executeQuery(); //查询 返回ResultSet
    - statement.execute(); // 执行任何SQL
    - statement.executeUpdate(); //更新、插入、删除,返回一个受影响的行数
ResultSet查询的结果集：封装了所有的查询结果
    - 获取指定的数据类型
        - resultSet.getObject(); //不知道列类型的情况使用
        - resultSet.getString()\getInt()\getFloat()
遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();

Statement对象

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

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

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

9、SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

package com.t.lesson2;
import com.t.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlPour {
    public static void main(String[] args) {
        //login("lisi","123456");
        // sql注入
        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(); //获取SQL的执行对象
            String sql = "select * from users where `name`='"+ username+"' and `password`='"+ password+"'";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

preparedStatement对象

preparedStatement可以防止SQL注入，效率更好

package com.t.lesson3;
import com.t.lesson2.utils.JdbcUtils;
import java.sql.*;
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            //区别
            //使用？占位符代替参数
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
            st = conn.prepareStatement(sql); //预编译SQL，先写SQL，然后不执行
            // 手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"QINJIANG");
            st.setString(3,"12345678");
            st.setString(4,"234567@qq.com");
            // 注意点： sql.Date 数据库         java.sql.Date()
            //          util.Date java          new Date().getTime() 获得时间戳
            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.release(conn,st,rs);
        }
    }
}