MySQL基础

1 修改密码

mysql> set password for root@localhost = password('1234'); 

命令行连接

show database; -- 查看数据库
use database： -- 切换数据库

show tables; -- 查看数据库所有的表
describe table; -- 查看数据库表

exit; -- 退出

2 操作数据库

---

SQL不区分大小写

2.1 数据库操作

CTREATE DATABASE xx;
DROP DATABASE xx;

2.2 数据库类型

数值

类型	字节
tinyint	1字节
smallint	2字节
mediumint	3字节
int	4字节
bigint	8字节
float	4字节
double	8字节
decimal	字符串形式的浮点数（金融计算）

字符串

类型	描述	长度
char	固定大小	0~255
varchar	可变字符串	0~65535
tinytext	微型文本	2^8 - 1
text	文本串	2^16-1

时间日期

类型	格式
date	YYY-MM-DD
time	HH：mm:ss
datetime	YYYY-MM-DD HH:mm:ss
timestamp	时间戳
year	年份

null

2.3 字段属性

• Unsigned
• zerofill
• 自增
• 非空
• 默认值

2.4 规范：

每个表必须存在五个字段,一个字段存在的意义，保证健壮性和安全性

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

2.5 创建数据表

CREATE TABLE IF NOT EXISTS `user` (
	`id` INT(4) NOT NULL COMMENT '用户id',
	`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '用户名',
	`sex` VARCHAR(2) NOT NULL COMMENT '性别',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址信息',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

语法

CREATE TABLE [IF NOT EXISTS] `table` (
    `属性名` 类型 [属性] [索引] [注释],
    `属性名` 类型 [属性] [索引] [注释],
    ...
    `属性名` 类型 [属性] [索引] [注释]
)[引擎] [字符集] [表注释]

常用命令

显示创建语句
SHOW CREATE DATABASE xx -- 
SHOW CREATE TABLE xx
DESC xx

数据库引擎

	MYISAM	INODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下
本质还是文件的存储

字符集

默认的字符集不支持中文，需要更改默认设置
CHARSET=utf8

3 表操作

修改

-- 修改表名
ALter table student rename as students;
-- 增加列
alter table students add `addr` varchar(100);
-- 修改表字段
-- 仅修改列已有的属性
alter table students modify age varchar(3);
-- 修改列为一个新的列
alter table students change age age1 int(3);

删除

-- 删除表的字段
ALTER TABLE students DROP addr;

4 数据管理

4.1 外键

物理级的外键会造成数据库表之间的约束，不建议使用

使用建议

• 数据库尽量是单纯的表，只用来存储数据，只用行（数据）和列（字段）
• 想用多张表的数据，尽量用程序去实现

4.2 DML语言

数据库意义： 存储数据，数据管理

insert

INSERT INTO 表名 [列名、列名] VALUE (arg1,arg2),(arg1,arg2)...

update

UPDATE 表名 SET 列名=value1,[列名=value2] WHERE 条件

delete

DELETE FROM 表名 WHERE 条件

truncat

删除整表数据

与delete删除整个表的区别

• 重新设置自增列，计数器归零
• 不会影响事务

4.3 DQL语言

select 完整语法：

SELECT [ALL | DISTINCT ] 
{ * | table.* | [table.filed1[AS alias1][,table.filed2[AS alias2]][,...]]}
FROM `table_name` [AS table_alias1]
	[LEFT | RIGHT | INNER JOIN `table_name2` [AS table_alias2]
	[WHERE ...] -- 指定满足的条件
	[GROUP BY ...] -- 指定分组字段
	[HAVING ...] -- 指定分组的记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录的排序
	[LIMIT ...] -- 指定起始位置和记录条数
-- 按照顺序来

DISTINCT 去重

特殊用法

SELECT VERSION() -- 函数
SELECT 10*2-1 AS 结果 -- 公式
SELECT @@auto_increment_increment AS 自增量 -- 系统变量
SELECT `age1`+1 AS 年龄加1 FROM `students`  -- 计算

LIKE

% 表示0到多个，_ 表示1个

SELECT `name` FROM `students` WHERE `name` LIKE '张%';

IN

SELECT `name` FROM `students` WHERE `age1` IN (21,22,23);

join （表） on （条件）

七种JOIN理论

SELECT t1.id,属性,属性 
FROM 表1 AS t1 -- 表1和表2是同一张表就是自连接
INNER JOIN 表2 AS t2   -- left JOIN 左连接 Right JOIN 右连接 INNER JOIN 交集  
ON t1.id = t2.id

4.4 排序和分页

ORDER BY 属性 [ASC|DESC]

LIMIT 起始点,页面大小

SELECT `name`,g.`grade`
FROM `students` AS s
INNER JOIN `grade` AS g
ON s.`id` = g.`id`
WHERE `subject`='java'
ORDER BY `grade` DESC
LIMIT 0,10

4.5 子查询

每个select查询都是返回一张表

SELECT `name`
FROM `students`
WHERE `id` IN (
	SELECT `id` FROM `grade` WHERE `subject`='java'
)
ORDER BY `name` ASC

4.6 分组和过滤

配合聚合函数使用

SELECT `name`, AVG(`age`) FROM `students`
GROUP BY `name` -- 分组依据
HAVING AVG(`age`) > 1 -- 聚合函数判断

5 函数

5.1 常用函数

官网查询函数

-- 属性运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT RAND() -- 随机数

-- 字符串
SELECT CHAR_LENGTH('123') -- 字符串长度

-- 时间
SELECT LOCALTIME() -- 本地时间

-- 系统
SELECT USER() -- 用户
SELECT VERSION() -- 版本号

5.2 聚合函数

-- 聚合函数
SELECT COUNT(`id`) FROM `students`
-- count(字段)会忽略null值，以下两个不会，是统计行数
SELECT COUNT(*) FROM `students`
SELECT COUNT(1) FROM `students`

SELECT MAX(`age`) FROM `students`
SELECT MIN(`age`) FROM `students`
SELECT SUM(`age`) FROM `students`
SELECT `name`, AVG(`age`) FROM `students`

6 事务

6.1 什么是事务

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

原子性（Atomicity）

要么成功，要么失败

一致性（Consisitency）

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

隔离性（Isolation）

多用户并发访问时，数据库为每个事务提供隔离

持久性（Durability）

事务一旦提交就不可逆

隔离性会产生的问题

脏读

读取另一个未提交的数据

不可重复读

一个事务内多次读取的结果不一样

虚读

多次读取时，读到了别人插入的数据

执行事务

SET autocommit = 0 -- 关闭自动提交

START TRANSACTION -- 开启一个事务

UPDATE 。。。
UPDATE 。。。

COMMIT -- 提交
ROLLBACK -- 回滚

SET autocommit = 1 -- 开启自动提交

7 索引

[MySQL索引背后的数据结构及算法原理](http://blog.codinglabs.org/articles/theory-of-mysql-index.html)

7.1 索引分类

• 主键索引（primary key）
• 唯一索引（unique key）
• 普通索引（key）
• 全文索引（fulltext）

-- 显示表索引信息
SHOW INDEX FROM `students`

-- EXPLAIN分析sql执行的状况，可用于优化
EXPLAIN SELECT * FROM `students`

7.2 索引测试

-- 创建表
CREATE TABLE `app_user` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
	`name` VARCHAR(20) DEFAULT '' COMMENT '姓名',
	`email` VARCHAR(50) NOT NULL COMMENT '邮件',
	`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
	`gender` TINYINT(3) UNSIGNED DEFAULT '0' COMMENT '性别0女性1男性',
	`password` VARCHAR(20) NOT NULL COMMENT '密码',
	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`update_time` TIMESTAMP NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 插入100万数据
SET GLOBAL log_bin_trust_function_creators=TRUE;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 1;
	WHILE i < num DO
	    INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUE(CONCAT('user','0'),'12345@qq.com',CONCAT('1',FLOOR(RAND()*1000000000)),FLOOR(RAND()*2),UUID(),RAND()*100);
	    SET i = i + 1;
	 END WHILE;
	 RETURN i;
END;

SELECT mock_data();

-- 未建索引耗时
SELECT * FROM `app_user` WHERE `name`='user9999' -- 耗时 1.137 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='user9999' -- 查询rows 993135 

-- 建立索引
CREATE INDEX name_index ON `app_user`(`name`) -- 总耗时 : 5.576 sec

SELECT * FROM `app_user` WHERE `name`='user9999' -- 耗时 0.004 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='user9999' -- 查询rows 1 

索引在小数据量时用处不大，在大数据查询中，效果明显

7.3 索引使用原则

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

索引的数据结构

hash 类型的索引

Btree ： InnoDB默认使用索引结构

8 权限及备份

8.1 用户管理

数据库用户管理用于创建不同的用户，分不同的用户主要是为了分配不同的权限。

常用配置用户命令

-- 创建用户
CREATE USER user1 IDENTIFIED BY '123456'

-- 重命名
RENAME USER ...

-- 授权
GRANT ...

-- 查询权限
SHOW GRANTS FOR ...

-- 撤销
REVOKE ...

-- 删除用户
DROP USER ...

有可视化数据库管理软件操作起来会简单很多

8.2 备份

备份的作用：

• 保证数据不被丢失
• 数据转移

MySQL备份方式

• 直接拷贝物理文件（data文件）
• 在sqlyog等可视化工具中导出
• 通过命令行导出 -> mysqldump -h 主机 -u用户名 -p密码 数据库 表 > 位置

9 规范数据库设计

9.1 设计

糟糕的数据库设计

• 数据冗余
• 插入、删除麻烦
• 程序的性能差

良好的数据库设计

• 数据重复存储少
• 方便提取数据，数据库表字段设计合理
• 良好的性能

扩展：前端开发框架 Ant Design Pro

9.2 三大范式

第一范式

原子性：每一个列不可拆分

第二范式

前提：满足第一范式

一行数据值做一件事情，所有的属性完全依赖主键

第三范式

前提：第二范式

每一列都与主键直接相关，而非间接相关

规范性 和 性能

阿里巴巴数据库规范要求查询最多只能链接三张表

规范只是一种数据库结构的设计建议，具体的数据库表需要结合需求和性能。范式越严格，拆分的表就越多，在查询时需要链接的表越多，性能就会下降，具体实施时结合：需求>性能>规范 的思想设计数据库表

一些反规范式设计

• 增加冗余字段方便单表查询
• 增加计算列，减少查询时的统计

10 JDBC

10.1 什么是JDBC（Java Database Connectivity）

不同的数据库由不同的数据库厂商提供驱动，为了使java能够用一套方法操作所有的数据库，要求数据库厂商提供统一的操作接口，而JDBC就是SUN公司提出一个规范，这样就可以是java应用程序只能JDBC打交道，而不需要关心底层的数据库是哪种数据库了。

10.2 jdbc基本步骤

import java.sql.*;

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

        // 2. 连接数据库
        String url = "jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String name = "root";
        String pwd = "123456";

        Connection connect = DriverManager.getConnection(url,name,pwd);

        // 3. 创建操作对象
        Statement statement = connect.createStatement();

        // 4. 执行sql语句
        String sql = "SELECT * FROM `students`";
        ResultSet resultSet = statement.executeQuery(sql);

        StringBuilder builder = new StringBuilder();
        while (resultSet.next()){
            builder.append("name=" + resultSet.getObject("name") + "\n");
            builder.append("age=" + resultSet.getObject("age") + "\n");
            builder.append("==============================" + "\n");
        }

        System.out.printf(builder.toString());

        // 5. 关闭连接
        resultSet.close();
        statement.close();
        connect.close();
    }
}

代码解释

加载驱动

//DriverManager.registerDriver(new Driver());
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法
//使用下面即可，上面其实就是加载类进行静态初始化

连接数据库

String url = "jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=true";
// url = 协议：//主机地址:端口号/数据库名?参数1&参数2&...

//connect常用操作
connect.commit();
connect.rollback();

操作对象

 // 3. 创建操作对象
Statement statement = connect.createStatement();

//statement常用操作
statement.executeQuery();
statement.executeUpdate();
statement.execute();

结果集操作

ResultSet resultSet = statement.executeQuery(sql);
// 相当于一个光标，移动查询查询到的表
// 获取列数据
resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getArray();

// 移动光标
resultSet.previous();
resultSet.next();
resultSet.beforeFirst();
resultSet.absolute(int row);

关闭

resultSet.close();
statement.close();
connect.close(); // 耗资源，用完即关

maven 添加jar包

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.23</version>
</dependency>

10.3 封装jdbc工具类

一般会封装一个工具类，将固定操作放入工具类中：

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

// JdbcUtils
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static  String url ;
    private static  String username;
    private static  String password;

    static {

        InputStream in =  JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

        try {
            Properties properties = new Properties();

            properties.load(in);

            String 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){
        //close
    }

}

10.3 SQL注入

本质是通过设计输入额外的SQL语句，来绕过正常的检查过程，非法获取数据

本质是SQL拼接导致的

JDBC中使用PrepareStatement避免SQL注入

Connection connection = JdbcUtils.getConnection();

String sql = "select * from `students` where name = ?";
PreparedStatement ps = connection.prepareStatement(sql);

ps.setString(1,"Tom");

ResultSet resultSet = ps.executeQuery();

while (resultSet.next()){
    System.out.println("name = " + resultSet.getString("name"));
    System.out.println("age = " + resultSet.getInt("age"));
    System.out.println();
}

JdbcUtils.release(connection, ps, resultSet);

10.4 使用IDEA也可以连接数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gHu3WcpX-1612618771627)(C:\Users\zl\AppData\Roaming\Typora\typora-user-images\image-20210206191714345.png)]

10.5 JDBC使用事务

Connection connection = null;
PreparedStatement ps = null;
try {
    connection = JdbcUtils.getConnection();

    connection.setAutoCommit(false); // 自动提交时默认开启的，关闭自动提交

    String sql = "update `students` set `age`=? where `name` = ?";
    ps = connection.prepareStatement(sql);

    ps.setInt(1, 20);
    ps.setString(2,"Tom");

    int line1 = ps.executeUpdate();

    ps.setInt(1, 11);
    ps.setString(2,"cat");

    int line2 = ps.executeUpdate();

    connection.commit();

    System.out.printf("line:" + line1 + ":" + line2);

} catch (SQLException e) {
    //不加下面也会自动回滚
    try {
        connection.rollback();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    e.printStackTrace();
}
finally {
    JdbcUtils.release(connection, ps, null);
}

10.6 数据库连接池

每次需要数据都连接一次数据库，再关闭，非常耗费资源的，效率低下。为了提高效率，使用池化技术，即维持一些数据库连接，需要时就从连接池中取出一个连接，连接池的参数一般有最小连接数、最大连接数、最大空闲连接数。

开源的数据库连接池实现

在JDBC中，本质都是提供一个DataSource接口。

dbcp

public class DbcpUtils {
    private static  DataSource dataSource;

    static {

        InputStream in =  DbcpUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");

        try {
            Properties properties = new Properties();

            properties.load(in);

            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

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

c3p0

用户都是一样的，获取DataSource接口。

Druid

ry {
Properties properties = new Properties();

        properties.load(in);

        dataSource = BasicDataSourceFactory.createDataSource(properties);

    } catch (IOException | ClassNotFoundException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static Connection getConnection() throws SQLException {
    return dataSource.getConnection();
}

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

}

> c3p0

用户都是一样的，获取DataSource接口。

> Druid

阿里巴巴开源