从菜鸟到大厨：MySQL数据库的美味指南

数据库的操作

创建数据库:

create database 数据库名;

​

数据库语句一般会放在文件中批量执行,但是遇到错误会无法执行,为了避免可能因为重名而造成数据库无法执行下去的情况,会在create database后面加上if not exists来避免报错

​

创建数据库的时候,可以手动指定一下字符集

character set 字符集名字/charset 字符集名字

咱们需要在数据库中保存中文,mysql默认的字符是拉丁文,不支持中文.必须要在创建数据库的时候,手动指定编码方式为支持中文的编码(GBK,UTF8)

​

查看数据库:

show databases;

列出当前mysql服务器上的数据库

选中数据库:

use 数据库名;

删除数据库:

drop database 数据库名;

MySQL表的增删改查

创建表:

create table 表名(列名 类型, 列名 类型…);

如果表名/列名是关键字,那么要用倒引号``引出来

查看所有表:

show tables;

查看当前数据库的所有表

查看指定表的结构:

desc 表名;

删除表:

drop table 表名;

新增数据:

insert into 表名 values(值,值…);

此处的值要与列相匹配

指定列插入:

insert into 表名(列名,列名…) values (值,值…);

一次插入多行记录:

insert into 表名 values (值,值…),(值,值…)…

全列查询:

select * from 表名;

把表中所有列和所有行都查询出来

datetime类型插入:

固定格式字符串来表示日期

‘2000-01-01 00:00:00’

如果要填写当前时刻,可以使用sql提供现成的函数now()

指定列查询:

select 列名,列名… from 表名;

​

创建表名为exam的表,放入数据

​

我们想看class与math,English,chinese的和,但是原数据没有和,我们就可以用到查询字段为表达式

​

别名

查询的时候,给列和表达式指定别名(也可以给表起别名)

select 表达式 as 别名 from 表名;

把刚才的和命名为sum

​

去重

使用distinct关键字对某列数据进行去重

排序

select 列名 from 表名 order by 列名 [asc/desc]

asc是升序,desc是降序

mysql是一个客户端服务器结构化的程序

客户端向服务器发出请求,服务器得到响应,得到数据,如果再对数据进行排序,

排序处理的数据是临时数据,对原有的存储数据没有影响

条件查询

会指定具体的条件,按照条件针对数据进行筛选

select 列名 from 表名 where 条件

遍历表中每一行的数据,带入条件,不符号就跳过,符合会放到结果集合

select在条件判断查询执行的顺序:

1.遍历表中的记录

2.进行条件判断进行筛选

3.对于筛选后的数据进行列上表达式的计算

(先进行条件判断,可以使用表达式,但是取别名在条件判断之后,所以where条件可以使用表达式,但是不能用别名)

4.如果存在order by语句,对数据再进行排序

between…and…是固定范围进行筛选,闭区间

​
between…and…的效果和>= and <=一样

​
in(值,值…)在提供的数值中相等会被筛选到

​
​

如果我们要对以八开头或者结尾等条件进行筛选,不是固定的值,而是一个模糊的

条件,此时就要进行模糊查询like;

_可以表示一个任意字符,%可以表示任意个任意字符

​

​
​
新建一个表

​

得到grades为null的行,但是用’‘=’'不安全

​

却没找到,因为在匹配过程中任何与null进行运算的结果都是null,也就是false,没有,

即便存在grades的值为null,返回的也是没有

为了防止这样的事情发生要用<=>来对null进行判断

​
也可以使用is [not] null来对null值进行筛选

区别在于:<=>可以对多个列进行筛选,is [not] null只针对一行

分组查询

使用 GROUP BY 进行分组查询：

SELECT 列名, ... FROM 表名 GROUP BY 列名, ...;
1

在分组之前先过滤数据

使用 WHERE 进行条件筛选：

SELECT 列名, ... FROM 表名 WHERE 条件 GROUP BY 列名, ...;
1

在分组之后进行过滤数据

先进行分组查询，然后使用 HAVING 来进行条件限制，HAVING 后面接要筛选的条件：

SELECT 列名, ... FROM 表名 GROUP BY 列名, ... HAVING 条件;
1

分页查询

select 列名 from 表名 limit N offset M;

N表示本次查询最多查询N条记录

M表示本次查询从M下标开始查询

多表查询

SELECT 列名,... FROM 表名,... WHERE 条件;

在多表查询中，可能不同表中会出现相同的列名，所以要在列名之前注明属于哪个表，如：表名.列名

• 外连接：分为左外连接和右外连接。
• 左外连接：返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，结果中该部分将显示为NULL。
• 右外连接：返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，结果中该部分将显示为NULL。

示例语法：
SELECT 列名,... 
FROM 表名1 
LEFT/RIGHT JOIN 表名2 
ON 条件 AND 其他条件;
1
2
3
4
5

• 自连接：在某些情况下，需要对同一张表的行与行之间进行比较筛选。进行自连接时，需要给该表起别名，以防止表中的列名混淆。

示例语法：
SELECT A.列名1, B.列名2 
FROM 表名 AS A, 表名 AS B 
WHERE A.条件 = B.条件;
1
2
3
4

子查询

子查询是一种嵌套在另一个查询中的查询，通常用于从多个表中获取数据或在单个查询中执行多个步骤。子查询可以用于提高查询效率、简化查询逻辑以及在特定条件下筛选数据。

修改

update 表名 set 修改操作 where 符合条件;

删除

delete from 表名 where 条件 / order by / limit

数据库约束

NULL约束

创建表时,约束某列不能置空

UNIQUE：唯一约束

保证某列的每行必须有唯一的值

DEFAULT：默认值约束

默认的默认值为NULL,可以通过default进行修改

PRIMARY KEY：主键约束

NOT NULL 和 UNIQUE 的结合

某列不能存储null值且唯一

对于整数类型的主键一般搭配anto_increment进行使用

​
但是此处插入null没有报错,primary key不是not null和unique的结合嘛

此处插入null值,是不给值,利用anto_increment来自动生成每一个新纪录的唯一标识

​

auto_increment会记录最大的标识,下一次不给值默认自动生成的话就是最大值+1并更新

最大值,如果给值且给的值大于最大的标识就会更新最大标识

​
面临数据量大,分布式处理时,保证数据唯一,分布式唯一id要结合时间戳和机房/主机编号以及

随机因子来保证唯一,其中概率虽然无法保证降低为0,但在工程中可以忽略不计

FOREIGN KEY：外键约束

外键约束用于确保关系型数据库中一张表中的数据参照另一张表中的数据。比如有一个学校表，里面存储了各个班级的信息，然后创建一个学生表，其中的班级信息必须与学校表中的班级信息一致。在这种情况下，学生表中的班级名信息来源于学校表，这就是外键约束。学校表称为父表，学生表则称为子表。

在MySQL中，创建包含外键约束的表可以使用以下语句：

CREATE TABLE aaa (
    classId INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE bbb (
    studentId INT,
    name VARCHAR(20),
    classId INT,
    FOREIGN KEY (classId) REFERENCES aaa(classId)
);

1
2
3
4
5
6
7
8
9
10
11
12

上述语句中，aaa表的classId列设置了主键约束，保证了classId列的每一行都有唯一的标识。子表bbb的classId列设置了外键约束，它引用了aaa表的classId列，这样就保证了bbb表中的classId必须对应aaa表中的某个classId。

当父表（例如学校表）中的数据发生变化时，如删除一个班级，如果子表（例如学生表）中有引用该班级的记录，则数据库会阻止这个删除操作，以避免数据不一致性。如果要删除父表中的记录，需要首先删除子表中相关的引用记录，或者先删除子表，再删除父表中的记录。

在电商场景中，如果电商要对某个商品进行下架处理，但该商品已经有订单，直接删除会导致订单数据不完整。这时，可以通过添加一个标记列来表示商品是否下架，而不是物理删除商品记录。在创建订单时，系统会先检查该商品是否下架。这种做法称为逻辑删除，它通过额外的列来标记记录的状态，而不是物理上删除它们。

表的设计

根据实际需求，首先要确定所需实体及其属性，以及实体间的关系类型，例如一对一、一对多、多对多或没有关系。

• 一对一关系（1-to-1）通常通过使两个实体共享一个唯一标识符来建立。这意味着两个实体中的每一个都直接与对方相关联，且在这个关系中，每个实体只对应一个实例。比如，一个员工实体可以对应一个唯一的工资账单实体。
• 一对多关系（1-to-N）中，一个实体（“一”）可以与多个实体（“多”）相关联，但每个实体（“多”）只能与一个实体（“一”）相关联。在这种情况下，通常在“一”方实体中设置一个外键，指向“多”方实体的主键。例如，一个班级可以有多个学生，但每个学生只能属于一个班级。如果不支持数组，我们可以在学生表中为每个班级创建一个单独的条目。
• 多对多关系（M-to-N）需要一个关联表来实施。这个关联表通常包含两个外键，分别指向两个相关联的主键。例如，学生和课程之间可能存在多对多关系，因为一个学生可以选择多门课程，而每门课程也可以被多个学生选择。关联表可以包含学生ID和课程ID，以及可能的额外属性，如学生选择的日期或成绩。
• 如果两个实体之间不存在以上任何关系，那么它们之间就是没有关系。这意味着它们在逻辑上是完全独立的，没有必要在数据库中进行任何形式的联系。

MySQL索引事务

索引

创建索引

create index 索引名 on 表名(列名);
1

查看索引

show index from 表名;
1

删除索引

drop index 索引名 on 表名;
1

使用 PRIMARY KEY、FOREIGN KEY 和 UNIQUE 修饰的列会自动加上索引。这些自动加上的索引不能被删除。PRIMARY KEY 和 UNIQUE 在创建时必须保证唯一性，因此需要进行查找并加上索引来提高效率。FOREIGN KEY 的删除和创建需要参考父表和子表之间的绑定关系，仍旧需要进行查找。

对于查找而言，单一遍历会降低查询效率，而且在硬盘上逐行查询数据的效率也很低。因此，使用数据结构建立索引可以显著提高查询效率。

线性结构不能解决问题，因为仍然需要线性遍历。使用哈希表可以进行精准查找，但无法进行范围搜索。二叉搜索树可能会出现单支树的情况，从而退化成链表。为了引入平衡策略，平衡二叉树和红黑树实现了平衡机制，但相比二叉树，多叉树的层数会更小，从而减少了I/O读取次数。因此，引入了B树。B树的一个节点包含n个数据，这个节点有n+1个分支。增加和删除操作会对节点进行分裂和合并。

为了优化B树，产生了B+树。B+树的一个节点有n个数据，会有n个分支。每个数据作为其分支的最大值。叶子节点包含了所有的数据，并且叶子节点直接使用链表进行连接。对于范围搜索，可以在叶子节点上获取中间值，而不需要遍历每个数据。B+树的非叶子节点存储索引值，叶子节点存储对应行的数据。先将非叶子节点的数据缓存到内存里，然后查找到相应叶子节点，再去硬盘查询对应行的数据，从而减少I/O次数。由于数据都在叶子节点上，所以查询时间具有稳定性，不会出现查询速度忽快忽慢的情况。

事务

在数据库操作中，事务确保了一系列操作的完整性和一致性。例如，在支付交易中，当您向某人支付金额时，该金额首先从您的账户中扣除，然后增加到接收方的账户中。如果此过程中断，事务的特性允许系统回滚到操作前的状态，避免例如扣款成功但收款失败的问题。

START TRANSACTION; // 开启事务

... // 中间操作

COMMIT; // 如果一切正常则提交事务

ROLLBACK; // 如果有错误发生则回滚事务
1
2
3
4
5
6
7

事务的四大特性（ACID） ：

1. 原子性（Atomicity） ：事务被视为最小的不可分割单元，要么全部操作成功完成，要么全部失败回滚，不会结束在中间某个环节。

2. 一致性（Consistency） ：事务确保数据库从一个一致的状态转变到另一个一致的状态。即使在并发操作中，事务完成后也不会违反数据的完整性和业务规则。

3. 隔离性（Isolation） ：事务的隔离性定义了事务可见性和可能影响其他事务的级别。主要是为了防止如下几种问题：

   • 脏读 ：一个事务读取到另一个事务未提交的数据。
   • 不可重复读 ：一个事务执行期间，另一个事务修改了数据，导致第一个事务重复读取相同的数据时得到不同的结果。
   • 幻读 ：一个事务执行期间，另一个事务插入或删除了一些数据，导致第一个事务查询到了额外的行（幻行）。

4. 持久性（Durability） ：一旦事务提交，则其所做的修改会永久保存在数据库中，即使系统发生故障也不会丢失。

事务隔离级别 ：

1. Read Uncommitted (读未提交) ：允许读取未提交的数据，可能导致脏读、不可重复读和幻读。
2. Read Committed (读已提交) ：允许从事务开始后其他事务提交的更改，可避免脏读，但可能遇到不可重复读。
3. Repeatable Read (可重复读) ：在一个事务内部多次读取相同记录的结果是一致的，避免不可重复读，但可能遇到幻读。
4. Serializable (可串行化) ：最严格的隔离级别，通过锁定访问的数据阻止了脏读、不可重复读及幻读，确保无并发事务的干扰。

JDBC

JDBC 主要的流程

JDBC 的主要流程如下：

1. 创建数据源 ：使用 MysqlDataSource 或其他数据源类来配置数据库连接参数。
2. 建立连接 ：通过数据源对象获取数据库连接。
3. 构造 SQL 语句 ：创建需要执行的 SQL 语句，例如插入、更新、删除或查询。
4. 发送给服务器执行 ：使用 PreparedStatement 对象执行 SQL 语句。
5. 关闭资源 ：关闭 PreparedStatement 和 Connection 以释放资源。

修改、删除、增加操作示例

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) throws SQLException {
        // 创建数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/gh?characterEncoding=utf8&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        // 建立连接
        Connection connection = dataSource.getConnection();

        // 构造 SQL 语句
        String sql = "INSERT INTO student VALUES (9, '1234', 'gh', '123.qq.com', 3)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 输出 SQL 语句
        System.out.println("SQL: " + preparedStatement);

        // 发送给服务器执行
        int rowsAffected = preparedStatement.executeUpdate();
        System.out.println("数据库更改了 " + rowsAffected + " 行");

        // 关闭资源
        preparedStatement.close();
        connection.close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

读取操作示例

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test2 {
    public static void main(String[] args) throws SQLException {
        // 创建数据源
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/gh?characterEncoding=utf8&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        // 建立连接
        Connection connection = dataSource.getConnection();

        // 构造 SQL 语句
        String sql = "SELECT * FROM student";
        PreparedStatement statement = connection.prepareStatement(sql);

        // 执行查询并获得结果集
        ResultSet resultSet = statement.executeQuery();

        // 处理每一行数据
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String sn = resultSet.getString("sn");
            String name = resultSet.getString("name");
            String qq_mail = resultSet.getString("qq_mail");
            int classes_id = resultSet.getInt("classes_id");
            System.out.println("id = " + id + " sn = " + sn + " name = " + name + " qq_mail = " + qq_mail + " classes_id = " + classes_id);
        }

        // 关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

​