Mysql从入门到放弃

已于 2024-08-27 11:30:25 修改
阅读量998 收藏 24
点赞数 22
文章标签： mysql 数据库
于 2024-08-26 00:58:31 首次发布
本文链接：https://blog.csdn.net/2301_76154806/article/details/141537702
版权

文章目录

第一章：数据库的基本概述

1. 基本概念

  1. 数据库（Database），简称DB
  2. 用于存储和查询数据的仓库
  3. 特点
    • 持久化存储数据，本质上是一个文件系统
    • 便于数据的存储和管理
    • 使用统一的方式操作数据库（SQL：Structured Query Language）

2. 常见的数据库

  • 排行

    https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537127.png

    MySQL的优势

    1. 国内市场占有率远高于Oracle

    2. 开源免费，避免技术依赖，适合国产化

    3. 支持二次开发，可基于MySQL进行定制

    Oracle的优势

    1. 技术支持全面

    ClickHouse（俄罗斯）

    性能比MySQL快约1000倍

  • 常见的数据库

    https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537129.png

  • 各数据库特点说明

    名称描述
    MySQL开源免费，适合小型数据库应用
    OracleOracle公司开发的收费大型数据库，价格昂贵，一般小企业难以承担
    DB2IBM公司产品，收费，常用于银行系统
    SQL Server微软公司开发的收费中型数据库
    Sybase市场份额已大幅减少，但提供了专业的数据库建模工具：PowerDesigner
    SQLite嵌入式数据库，常用于移动设备
    ClickHouse数据仓库，适用于TB级大规模数据存储

  • 我们将学习哪个？

    • MySQL
      • 开源免费
    • Oracle（后期学习）
      • 学习时免费，商用需付费
      • 价格昂贵

3. 安装与配置（Windows环境）

  1. 查看服务

    # Win + R，输入 cmd，回车
# 输入 services.msc，回车
# 以管理员权限打开即可

  2. 启动

    1. 手动方式
    2. 命令行方式
    net start mysql # 启动，如遇权限问题，请尝试以管理员身份打开 CMD
net stop mysql  # 关闭

第二章：MySQL的基本操作

1. 登录与退出

  1. 登录

注意：按下Windows键，输入"服务"，打开服务管理器，搜索MySQL，右键点击MySQL服务并选择"启动"。

# 1. 本地登录（学习阶段使用，生产环境不推荐）
mysql -uroot -p密码
# root为账号，-p表示密码

# 2. 连接远程数据库
mysql -h 主机IP -uroot -p连接目标的密码

# 3. 使用完整参数连接
mysql --host=主机IP --user=root --password=连接目标的密码

  1. 退出

    1. exit
2. quit

2. 目录结构

1. 安装目录

https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537130.png

2. 数据目录

  • 关键概念
    • 数据库（database）：类似文件夹
    • 表（table）：类似文件夹中的文件
    • 数据（核心要素）：实际存储的信息

第三章：SQL概述

1. SQL的概念

Structured Query Language：结构化查询语言，简称"SQL"。简而言之，它定义了操作关系型数据库的规则。

  • 不同数据库的操作方式略有差异。

2. SQL的通用语法

所有内容、符号和注释均使用英文状态下的半角输入符号。

  1. SQL 语句可单行或多行书写，以分号结尾（使用英文状态下的半角分号）。

  2. 使用空格和缩进可提高语句的可读性。

  3. MySQL 数据库的 SQL 语句不区分大小写，但建议将关键字大写。

  4. 注释

    注释表示该语句不会被执行。

    单行注释: -- 注释内容 或 # 注释内容（MySQL 特有）
多行注释: /* 注释 */

3. SQL的分类

DL [data language]

DDL：Data Definition Language（数据定义语言）

DQL：Data Query Language（数据查询语言）

DML：Data Manipulation Language（数据操作语言）

  1. DDL（Data Definition Language）数据定义语言

    用于定义数据库对象：数据库、表、列等。关键字包括 CREATE、DROP、ALTER 等。

  2. DML（Data Manipulation Language）数据操作语言

    用于对数据库中表的数据进行增删改。关键字包括 INSERT、DELETE、UPDATE 等。

  3. DQL（Data Query Language）数据查询语言

    用于查询数据库中表的记录（数据）。关键字包括 SELECT、WHERE 等。

  4. DCL（Data Control Language）数据控制语言（了解）

    用于定义数据库的访问权限和安全级别，以及创建用户。关键字包括 GRANT、REVOKE 等。

第四章：DDL（数据定义语言）- 操作数据库（概述）

MySQL数据库的存储结构层次：

  1. 最上层结构：数据库

  2. 数据库包含多个数据表

  3. 每个数据表包含多个行记录

  4. 每条行记录包含多个列

    1. 创建数据库
    • 前提条件：必须先连接到数据库服务器
    • 建议使用大写关键字，如CREATE
    # 1. 创建数据库
CREATE DATABASE 数据库名称;

# 查看所有数据库
SHOW DATABASES;

# 如果数据库已存在，会报错：
# Can't create database '数据库名称'; database exists

# 2. 创建数据库（如果不存在）：
CREATE DATABASE IF NOT EXISTS 数据库名称;

# 3. 创建数据库，指定字符集（推荐）
CREATE DATABASE db4 CHARACTER SET utf8mb4;

# 注：MySQL的utf8字符集存在一些限制，可能导致某些汉字无法正常显示。
# 官方后来推出了utf8mb4作为补充。

    MySQL支持多种字符集，但我们通常使用utf8mb4。使用【SHOW COLLATION】命令可查看MySQL支持的所有排序规则和字符集。

    示例：

    • utf8_polish_ci：UTF-8字符集的波兰语比较规则，ci表示忽略大小写。
    • utf8_general_ci：通用的忽略大小写的UTF-8字符集比较规则。
    后缀英文描述
    _aiaccent insensitive不区分重音（如è、é、ê和ë）
    _asaccent sensitive区分重音
    _cicase insensitive不区分大小写
    _cscase sensitive区分大小写
    _binbinary以二进制形式比较

    utf8和utf8mb4的区别：

    • utf8mb3（即utf8）：使用1~3个字节表示字符。
    • utf8mb4：使用1~4个字节表示字符，是utf8的超集，可存储emoji表情。MySQL 8.0默认使用utf8mb4字符集。

2. 查询操作

# 1. 查询所有数据库名称
SHOW DATABASES;

# 2. 查询特定数据库的字符集和创建语句
SHOW CREATE DATABASE 数据库名称;

# 注：performance_schema、mysql和information_schema是MySQL的核心系统数据库。
# 修改这些数据库可能导致MySQL崩溃，请谨慎操作。

3. 修改数据库

通常不建议修改数据库的字符集，但如果必要，可以使用以下命令：

# 修改数据库的字符集
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

# 修改数据库字符集和排序方式（了解）
ALTER DATABASE java21 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

4. 删除数据库

# 删除数据库
DROP DATABASE 数据库名称;

# 删除数据库（如果存在）
DROP DATABASE IF EXISTS 数据库名称;

5. 使用数据库

# 查询当前使用的数据库
SELECT DATABASE();

# 切换到指定数据库
USE 数据库名称;

第五章：DDL（操作数据表 - 了解）

1. 创建表（Create）

-- 创建表的前提是先选择一个数据库
USE 「自己的库名称」;

-- 语法
CREATE TABLE 表名称 (
    列名1 数据类型1(宽度),
    列名2 数据类型2(宽度),
    列名3 数据类型3(宽度),
    ...
    列名n 数据类型n
);

-- 创建表示例
CREATE TABLE student (
    id INT,
    name VARCHAR(32),
    age TINYINT,
    score DOUBLE(4,1),
    birthday DATE,
    insert_time TIMESTAMP
);

-- 复制表（慎用）
CREATE TABLE 表名 LIKE 被复制的表名;

特别注意：

  1. 最后一列不要加逗号。

1. 数据类型

Untitled

  • 数值类型

    https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537137.png

  • 时间和日期类型

    https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537138.png

    • TIMESTAMP：如果不给这个字段赋值，或者赋值为NULL，则TIMESTAMP类型会自动使用当前的系统时间。

时间戳数据类型本质上使用数字来存储，但在查询时MySQL会将其转换为可读格式，方便用户阅读。

注意：TIMESTAMP类型在插入数据时格式与DATETIME相同，但不能存储1970年以前的日期。

  • 字符串类型
    在这里插入图片描述

    必须掌握的数据类型：

    • 整型数据类型
      • INT
      • TINYINT
      • BIGINT
      • DOUBLE
    • 字符串
      • VARCHAR
      • TEXT
      • MEDIUMBLOB
      • MEDIUMTEXT
    • 日期
      • DATETIME
      • TIMESTAMP

2. 查询表

-- 查询某个数据库中所有的表名称
SHOW TABLES;

-- 查询表结构
DESC 表名;
-- 显示如下:
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(64)  | YES  |     | NULL    |       |
| gender   | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
-- Field：表示字段或列名
-- Type：建表时填写的数据类型
-- Null：该字段是否可以为NULL
-- Key：该字段是否是主键或外键（后面会详细讲解）
-- Default：表示该字段的默认值
-- Extra：扩展信息，暂时没有用

-- 查看建表语句及所用的字符集
SHOW CREATE TABLE 表名称;
-- 可以看到具体的建表语句和表的字符集：
| t1    |

CREATE TABLE `student` (
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

-- 系统自动添加的部分：
-- DEFAULT NULL：表示默认值
-- ENGINE=InnoDB：表示存储引擎，默认是这个
-- CHARSET=utf8mb4：表示表的字符集，如果建表时没有指明字符集，默认采用当前数据库的字符集

3. 修改/更新（Update）

ALTER 对库/表 库名/表名 + 具体执行的动作;

-- 1. 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 示例:
mysql> ALTER TABLE t1 RENAME TO t_classmate;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_student |
+-------------------+
| t2                |
| t_classmate       |
+-------------------+

-- 2. 修改表的字符集（一般不建议修改）
ALTER TABLE 表名 CHARACTER SET 字符集名称;
-- 示例: utf8mb4
ALTER TABLE t_classmate CHARACTER SET utf8;

-- 3. 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;

mysql> ALTER TABLE t2 ADD score INT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(64)  | YES  |     | NULL    |       |
| gender   | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
| score    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

-- 4. 修改列名称和类型
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型;
mysql> ALTER TABLE t2 CHANGE gender sex INT;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(64)  | YES  |     | NULL    |       |
| sex      | int          | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
| score    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

-- 仅修改列的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;

mysql> ALTER TABLE t2 MODIFY sex VARCHAR(1);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(64)  | YES  |     | NULL    |       |
| sex      | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
| score    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

-- DROP（删除）、ALTER（修改）、CREATE（创建）、SHOW（查看）
-- 这四个操作都是针对表结构的增删改查

-- 5. 删除列
ALTER TABLE 表名 DROP 列名;
mysql> ALTER TABLE t2 DROP score;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| username | varchar(64)  | YES  |     | NULL    |       |
| sex      | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

4. 删除表操作（Delete）

DROP 库名称/表名称 库名/表名;

DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;

第六章：DML 增删改表中的数据（Modify：新增、删除、修改）

1. 数据的添加

INSERT INTO + 表名(所有列名) VALUES(值…)

# 1. 向已经存在的表中插入数据
INSERT INTO 表名(列名1, 列名2, 列名3, ...列名n) VALUES (值1, 值2, 值3, ... 值n)

# 2. 此种方式可以只对某几个字段进行赋值操作
INSERT INTO 表名(第一列, 第二列) VALUES(值1, 值2)

# 3. 可以一次性插入多条记录（批量插入）
INSERT INTO 表名(列名1, 列名2, 列名3, ...列名n) VALUES
(值1, 值2, 值3, ... 值n),
(值1, 值2, 值3, ... 值n),
(值1, 值2, 值3, ... 值n)
...

# 特别注意：
# 1. 列名与值必须一一对应
# 2. 如果表名后没有指定列名，则默认给所有列赋值（不写等于全写）
INSERT INTO 表名 VALUES(值1, 值2, 值3, ... 值n)

# 3. 除数字类型外，其他类型的值需要使用引号（单引号或双引号）

注意：在插入语句中，最后一列不需要加逗号。例如：
INSERT INTO 表名(第一列, 第二列) VALUES(值1, 值2)

让我们复习一下如何建表和添加数据。创建一张善知科技员工表，包含三个属性：员工名、员工职位、员工薪水

CREATE TABLE staff (
    name VARCHAR(100),
    position VARCHAR(100),
    salary BIGINT
)

插入几条数据 🤣

INSERT INTO staff VALUES
('马赢', '董事长', 10), 
('向辉', '刺客', 1000000), 
('若辉', '财务', 6000000),
('王池', '开发工程师', 10000),
('占峰', '辅助', 600), 
('祎婕', '开发工程师', 10000),
('予安', '门卫', 600),
('振洋', '段子手', 6666)

2. 删除表中的数据

DELETE FROM 表名 WHERE 约束条件

语法格式
DELETE FROM 表名 [WHERE 约束条件]

查看现有数据

SELECT * FROM staff;

将董事长开除 😵‍💫
DELETE FROM staff WHERE position=‘董事长’;

查看员工表
SELECT * FROM staff;

在某些情况下需要删除表中所有数据，可以使用以下两种方式（了解即可）：

  1. DELETE FROM 表名;
    • 不推荐使用，会执行多次删除操作（每条记录一次）。效率低
    • 尽量避免使用
  2. TRUNCATE TABLE 表名; 删除所有数据，推荐使用此方式
    • 推荐使用，效率高。实际上是先删除整个表，然后再创建一个相同结构的空表

3. 修改表中的数据（更新操作）

基础语法
UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, … [WHERE 约束条件]

示例：

职位变更：不干了，改行卖鱼

UPDATE staff SET position=‘卖鱼’ WHERE name=‘薛董’;

Untitled

加薪 🥰

UPDATE staff SET salary = salary + 100 WHERE name = ‘嘉欣’;

如果不加约束条件，则默认更新整张表：

UPDATE staff SET salary = salary + 55;

使用UPDATE语句实现软删除

大多数公司在删除表数据时实际上是使用UPDATE操作来实现软删除

优点1：数据容易恢复
优点2：MySQL执行DELETE操作消耗的性能比UPDATE高，DELETE操作会锁表，而UPDATE操作的限制相对较少

重要提示：

不管是更新还是删除，如果不添加约束条件，就会更新/删除整表数据，这是非常危险的操作。

必须添加约束条件

使用WHERE子句添加条件

  • DDL（数据定义语言）
    • 用于对数据库/表结构进行操作（了解）
  • DML（数据操作语言）「重点内容」
    • 用于对表中的数据进行新增/更新/删除操作

第七章：查询表中的记录

1. 基本语法

Untitled

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段
HAVING
    分组之后的条件
ORDER BY
    排序
LIMIT
    分页限定

2. 基础查询

  • 多个字段进行查询

    SELECT 字段名1, 字段名2, ... FROM 表名
    • 如果要查询所有字段，可以使用*来替代字段列表
    • 实际工作中，不要使用SELECT *；即使查询所有列，也应该明确列出所有字段名
    # 推荐做法
mysql> SELECT id, name, note FROM t1;
+------+-------+------------+
| id   | name  | note       |
+------+-------+------------+
|    2 | dong  | yunfeng    |
|   10 | liyao | qian liyao |
+------+-------+------------+

小结：

  • 概述

    • MySQL
    • SQL

  • DDL操作

    • 对库、表本身进行操作；不针对数据

  • DML操作

    • 针对数据进行增加/删除/修改

  • DQL操作

    SELECT * FROM 表名 WHERE 约束条件;
SELECT * FROM 表名;

  • 去除重复

    DISTINCT

  • 别名

    AS 或者空格都可以

3. 条件查询

WHERE条件可以跟在DML中的UPDATE和DELETE语句后面，也可以跟在DQL查询语句后面

  • WHERE子句后跟限定条件

  • 运算符

    > 、< 、<= 、>= 、= 、<> !=

# 推荐使用AND OR NOT，不推荐&&、||、!，但它们可以使用
AND 或 &&
OR  或 ||
NOT 或 !
------------
BETWEEN...AND  # 范围查询。某一个范围，可以取边界值；[Between , and]，闭区间
IN(集合) # 写在括号里，出现一个即满足条件

LIKE：模糊查询
  占位符：
      _：单个任意字符，有一个_就是有一个字符；严格匹配
      %：多个任意字符，0个、1个或多个都可以
IS NULL
  是一个整体，不能拆分使用。除了IS NOT NULL

4. 综合练习[课后作业]

1. 新建一个数据库，名称为dbstudy
2. 创建一张表，名称为student
    1. 要求字段名称是：
        name
        age
        gender
        英文成绩
        语文成绩
3. 根据条件查询
    1. 查询所有数据
    2. 查询年龄大于21岁的学生信息
    3. 查询年龄等于20岁的学生信息
    4. 查询年龄不等于20岁的学生信息
    5. 查询年龄大于等于20，小于等于30岁的学生信息
    6. 查询年龄20岁、22岁、25岁的学生信息
    7. 查询英文成绩为NULL的学生信息
    8. 查询英文成绩不为NULL的学生信息
    9. 查询姓王的学生信息
    10. 查询名字包含"衣"的学生信息
  • SQL示例
SELECT * FROM student;

# 注释
-- 注释
/*
    多行注释
*/
INSERT INTO student(id, username, english, computer, chinese) VALUES(5, "董杰", 40, 60, 100);

# 累加列
SELECT username, english, computer, chinese, english + computer + chinese
FROM student;

DESC student;

INSERT INTO student(id, username, english, computer, chinese) VALUES(6, "刘刚", NULL, 76, 98);

SELECT * FROM student;

# IFNULL
# IF(exp1, exp2)
#   exp1：表示可能为NULL的字段
#   exp2：表示如果exp1 = NULL，将exp1值替换为什么
SELECT
    username,
    english,
    computer,
    chinese,
    IFNULL(english, 0) + IFNULL(computer, 0) + IFNULL(chinese, 0)
FROM
    student;

-- 使用别名
-- AS关键字。xxx AS 起个名称
-- 使用空格隔开。xxx 起个名称
SELECT
    username AS 姓名, # 使用AS
    english AS 英语成绩,
    computer 计算机成绩, # 使用空格隔开
    chinese 汉语成绩,
    IFNULL(english, 0) + IFNULL(computer, 0) + IFNULL(chinese, 0) AS 总分
FROM
    student;

-- 给表起别名
SELECT
    s.username AS 姓名, # 使用AS
    s.english AS 英语成绩,
    s.computer 计算机成绩, # 使用空格隔开
    s.chinese 汉语成绩,
    s.id AS 用户id,
    IFNULL(s.english, 0) + IFNULL(s.computer, 0) + IFNULL(s.chinese, 0) AS 总分
FROM
    student AS s;

# 综合练习
########## 条件查询
DESC student;

# 删除一列
ALTER TABLE student DROP computer;
TRUNCATE TABLE student;
SELECT * FROM student;
# 插入一些数据
DESC student;

-- 查询所有数据
INSERT INTO student VALUES
(1, '云峰', 25, '男', 50, 80),
(2, '振源', 23, '男', 70, 50),
(3, '佳琪', 21, '男', 90, 60),
(4, '丕琪', 20, '男', 90, 60),
(5, '董杰', 21, '女', 70, 70),
(6, '乔石', 23, '男', 90, 80);

-- 查询年龄大于21岁的学生信息
-- > 、< 、<= 、>= 、= 、<>
-- <>表示不等于
SELECT
    *
FROM
    student AS s
WHERE
    s.age > 21;

SELECT
    *
FROM
    student AS s
WHERE
    s.age <> 23;

-- 5. 查询年龄大于等于20，小于等于30岁的学生信息
/*
 连接WHERE后边的子句操作：
 AND 或 &&
 OR  或 ||
 NOT 或 !

 &&、||、!不建议写在SQL中，建议使用AND OR NOT
*/

SELECT
 *
FROM
    student AS s
WHERE
    s.age >= 21
    &&
    s.age <= 23;

SELECT
 *
FROM
    student AS s
WHERE
    s.age >= 21
    AND
    s.age <= 23;

SELECT
    s.username AS 姓名,
    s.english 英文成绩
FROM
    student AS s
WHERE
    s.english >= 80
    AND
    s.gender = '男';

SELECT
    s.username AS 姓名,
    s.english 英文成绩,
    s.chinese AS 语文成绩
FROM
    student AS s
WHERE
    s.english >= 80
    OR
    s.chinese >= 60;

SELECT * FROM student;

SELECT
    *
FROM
    student AS s
WHERE
    s.english >= 60
    AND
--  NOT s.gender = '男';
--  s.gender != '男';
    s.gender = '女';

------------------------------
-- 6. 查询年龄20岁、22岁、25岁的学生信息
-- IN(....)
SELECT
    *
FROM
    student AS s
WHERE
    s.age = 20
    OR
    s.age = 22
    OR
    s.age = 25;

SELECT
    *
FROM
    student AS s
WHERE
    s.age IN(20, 22, 25); # 表示当前的Age出现20、22、25中的一个值就可以了

/*
    7. 查询英文成绩为NULL的学生信息
    8. 查询英文成绩不为NULL的学生信息
    IS NULL --> 判断某一个字段（列）的值为NULL
*/
SELECT * FROM student;
INSERT INTO student VALUES
(21, '董小姐', 28, '女', 90, 90),
(22, '董二千', 26, '男', 78, 70),
(23, '董不懂啊', 26, '男', 78, 70),
(24, '西门吹雪', 23, '男', 98, 60),
(25, '董', 23, '男', 98, 60),
(26, '易阳千喜', 21, '男', 98, 60);

SELECT
    *
FROM
    student AS s
WHERE
    s.english = NULL; # 不能这样写

SELECT
    *
FROM
    student AS s
WHERE
    s.english IS NULL;

# 不为NULL
# IS NOT NULL，某个字段（列）的值不是NULL的数据
# 顺序不能更改。只能使用IS NOT NULL

SELECT
    *
FROM
    student AS s
WHERE
    s.english IS NOT NULL;

/*
    9. 查询姓王的学生信息
        LIKE：模糊查询
    占位符：
            _：单个任意字符，_表示一个字符，__表示两个字符。匹配的是字符的个数
            %：多个任意字符
    10. 查询名字第二个字是"小"的学生信息
    11. 查询名字包含"衣"的学生信息
    13. 查询名字是四个字的学生信息
        LIKE '____'，表示名字是四个字的
*/

SELECT * FROM student;
SELECT
    *
FROM
    student AS s
WHERE
    s.username LIKE '___';

SELECT
    *
FROM
    student AS s
WHERE
    s.username LIKE '_琪';

SELECT
    *
FROM
    student AS s
WHERE
    s.username LIKE '董_'
    OR
    s.username LIKE '董__'
    OR
    s.username LIKE '董___'
    OR
    s.username LIKE '董____'
    OR
    s.username LIKE '董_____';

SELECT
    *
FROM
    student AS s
WHERE
    s.username LIKE '_董%';

-- BETWEEN ... AND ...
-- 5. 查询年龄大于等于20，小于等于30岁的学生信息

SELECT
    *
FROM
    student AS s
WHERE
    s.age BETWEEN 23 AND 28;

ALTER TABLE student RENAME TO stu1;
SHOW TABLES;

第八章：DQL 查询语句 [重要]

重点内容

0. 建表语句 [准备数据]

create database itszt25 character set utf8mb4;

use itszt25;

CREATE TABLE student(
    id int,
    username VARCHAR(32),
    age int,
    sex varchar(1) DEFAULT '男',
    math DOUBLE(4,1),
    java DOUBLE(4,1),
    create_time TIMESTAMP
);

-- DEFAULT '男'：如果插入这张表中没有填上sex这个值，将会默认为'男'
-- TINYINT：1个字节，范围(-128~127)
-- DOUBLE(m,d)：m总个数，d小数位
-- TIMESTAMP：时间戳，等同于Java中的System.currentTimeMillis()方法返回的数字

-- CURRENT_TIMESTAMP：MySQL识别到这个关键词会获取当前时刻的时间戳，相当于Java中的System.currentTimeMillis()

-- MySQL特有的语法，一条SQL语句插入多行数据
INSERT INTO student(id, username, age, math, java, create_time) VALUES
(1, '张三丰', 120, 39, 65, CURRENT_TIMESTAMP),
(2, '张无忌', 22, 23, 38, CURRENT_TIMESTAMP),
(3, '赵敏', 21, 67, 89, CURRENT_TIMESTAMP),
(4, '小招', 24, 32, 80, CURRENT_TIMESTAMP),
(5, '金毛狮王', 98, 33, 76, CURRENT_TIMESTAMP),
(6, '紫衫龙王', 45, 89, 67, CURRENT_TIMESTAMP),
(7, '齐天大圣', 89, 43, 12, CURRENT_TIMESTAMP),
(8, '宝儿姐', 100, 96, 78, CURRENT_TIMESTAMP),
(9, '古月娜', 34, 65, 33, CURRENT_TIMESTAMP),
(10, '唐三', 98, 64, 77, CURRENT_TIMESTAMP),
(11, '云韵', 21, 90, 29, CURRENT_TIMESTAMP),
(12, '蜡笔小新', 12, 98, 32, CURRENT_TIMESTAMP),
(13, '唐僧', 125, 45, 45, CURRENT_TIMESTAMP),
(14, '唐小红', 32, 12, 65, CURRENT_TIMESTAMP),
(15, '唐三藏', 36, 56, 98, CURRENT_TIMESTAMP);

-- DEFAULT '男'：这个表示默认值为'男'
-- 设置id为偶数的学生的性别为女
UPDATE student SET sex = '女' WHERE id % 2 = 0;

1. 排序查询

  • 语法

    ORDER BY 子句
ORDER BY 排序字段1 排序方式1, 排序字段2 排序方式2, ...

  • 排序方式

    • ASC：升序排序（默认值）
    • DESC：降序

  • 特别注意：

    如果有多个排序条件，则当前面的条件值相同时，才会判断第二个条件。

    每一个字段都可以单独指定排序方式。

    SELECT
  *
FROM
  student AS s
ORDER BY
  age DESC, math ASC;

2. 聚合函数

  • 将一列数据作为一个整体，进行纵向计算
  • 常用聚合函数
    • COUNT：计算个数
      • 一般选择非空的列
      • COUNT(*)
    • MAX
    • MIN
    • SUM
    • AVG
  • 特别注意：
    • 聚合函数的计算会排除NULL值。

3. 分组查询 [DQL语法中较复杂的] GROUP BY、WHERE、HAVING

  • 语法

    GROUP BY 分组字段;

  • 特别注意：

    • 分组之后查询的字段：分组字段、聚合函数

  • WHERE 与 HAVING 的区别

    • WHERE 在分组之前进行限定，如果不符合条件则不进行分组；HAVING 在分组之后对结果集进行限定，如果不满足限定条件，则不会返回结果集。
    • WHERE 后不可以跟聚合函数，HAVING 可以使用聚合函数进行判断。

注意：GROUP BY 需要写在 WHERE 条件的后面

  • 示例

    1. 按照性别分组，分别查询出男/女同学的数学平均分、数学总分。
2. 按照性别分组，分别查询出男女同学的数学平均分、人数。
3. 按照性别分组，分别查询男、女同学的数学平均分、人数。要求：分数低于70分的人不参与分组。
4. 按照性别分组，分别查询男、女同学的数学平均分、人数。要求：分数低于70分的人不参与分组，分组后人数要大于2个。

4. 分页查询

  • 基础语法

    LIMIT 开始索引（需要计算），每页查询的条数（固定值）

  • 公式

    开始的索引 = (当前页码 - 1) * 每页显示的条数

    • 当前页码是固定值
    • 每页显示的条数也是固定的
    • 一般当前页码会由前端传递过来，这样可以计算出开始的索引值

  • 示例

  • LIMIT 是 MySQL 特有的，其他数据库都有自己的实现方式

第九章: 约束constraint

1. 基本概念

约束: 对表中的数据进行限定, 保证数据的正确性、有效性和完整性;

2. 分类

Untitled

  • 主键约束: primary key
  • 非空约束 not null
  • 唯一约束 unique
  • 外键约束 foreign key

3. 非空约束

4. 唯一约束「unique」

  • 设置某一列的值不能重复

  • 在创建表的时候可以添加唯一约束

    create table 表名(
    id int primary key auto_increment, # 给id添加主键约束, 并且设置主键自增长
    name varchar(32) not null, # 设置非空约束
    phone_number varchar(20) unique # 添加非空约束
)

  • 在表创建完成之后添加唯一约束

    alter table 表名 modify phone_number varchar(20) unique;

  • 删除唯一约束

    alter table 表名 drop INDEX phone_number;

  • 代码示例

    SELECT
  *
FROM
  student;

DESC student;

SELECT
  COUNT(id) as 总量,
  s.gender
FROM
  student as s
WHERE
  s.age > 20
GROUP BY
  s.gender;

SELECT
  COUNT(id) as 总人数,
  SUM(s.age) 总年龄数,
  AVG(s.age) 平均年龄,
  s.gender
FROM
  student as s
WHERE
  s.age > 20
GROUP BY
  s.gender
HAVING
  COUNT(id) > 80;

# 分页
SELECT
  *
FROM
  student as s
WHERE
  email LIKE '%qq%'
LIMIT 0, 20;
DESC student1;

INSERT INTO student1 VALUES(234, null, 18, 89, 23, '2021-05-11', '男');
INSERT INTO student1 VALUES(234, '建宁', 18, 89, 23, '2021-05-11', '女');

SELECT * FROM student1;

# 创建一张表, 并且指定主键
CREATE TABLE score(
  id int PRIMARY key, # 添加主键primary KEY
  math INT ,
  chineses int
);

INSERT INTO score VALUES(1, 40, 50), (2, 30, 20);
SELECT * from score;

SHOW tables;
DESC score;

CREATE TABLE t1(
  id int,
  name VARCHAR(16),
  age INT
);

DESC t1;
INSERT INTO t1 VALUES(1, '小红', 20), (1, '小明', 20);

# 给已经创建好的表,添加主键约束;
ALTER TABLE t1 MODIFY id int PRIMARY key;
ALTER TABLE t1 MODIFY id int; # 无法删除主键约束;错误姿势;
ALTER TABLE t1 DROP PRIMARY KEY;

CREATE TABLE t2(
  id int PRIMARY KEY auto_increment, # 添加主键, 并且设置主键自增;
  username VARCHAR(32),
  age int
);

desc  t2;
insert INTO t2(username, age) VALUES('小红', 20), ('小明', 21);

SELECT * FROM t2;

CREATE TABLE t3(
  id int PRIMARY KEY auto_increment, # 添加主键, 并且设置主键自增;
  username VARCHAR(32),
  age int auto_increment #  不是每个字段都可以使用auto_increment的. 必须有字段上有key;
);

CREATE TABLE t4(
  id int PRIMARY KEY, #
  username VARCHAR(32),
  age int #
);

DESC t4;

ALTER TABLE t4 MODIFY id int auto_increment; # 添加自增;

ALTER TABLE t4 MODIFY id int ; #  删除自增长

# 非空约束
CREATE TABLE t5(
  id int PRIMARY KEY auto_increment,
  username VARCHAR(32) NOT NULL,
  age int
);

DESC t5;
INSERT INTO t5(username, age) VALUES('小红', 20), ('小明', 22);

SELECT * FROM t5;

CREATE TABLE t6(
  id int PRIMARY KEY auto_increment,
  username VARCHAR(32),
  age int
);

DESC t6;
# 建完表之后添加非空约束
ALTER TABLE t6 MODIFY username VARCHAR(32) NOT NULL;
# 删除非空约束
ALTER TABLE t6 MODIFY username VARCHAR(32);

# 唯一约束,就是字段「列的值」 的值不能重复;
CREATE TABLE t7(
  id int PRIMARY KEY auto_increment,
  username VARCHAR(32) NOT NULL,
  phone INT(5) UNIQUE # unique , 表示唯一约束, 加了它,字段的值不能重复了;
);

DESC t7;
INSERT INTO t7(username, phone)VALUE('小红', 12345), ('小明', 12345);

INSERT INTO t7(username, phone)VALUE('小红', 12345), ('小明', 12343);
SELECT * FROM t7;

CREATE TABLE t8(
  id int PRIMARY KEY auto_increment,
  username VARCHAR(32) NOT NULL,
  phone INT(5)
);

DESC t8;
ALTER table t8 MODIFY phone int(5) UNIQUE;

# 删除唯一约束;
ALTER table t8 MODIFY phone int(5); # 错误的姿势,此种方式虽然不报错,但是无法删除掉唯一约束;
ALTER TABLE t8 DROP INDEX phone; -- 删除唯一约束的正确姿势;

5. 主键约束「primary key」

1. 概念

  • 非空且唯一
    • 主键不能为null, 主键不能重复;
  • 一张表只能有一个字段为主键
  • 主键就是表中唯一的标识 例如每个人都有自己身份的唯一标识(身份证号码, 学号)

2. 创建表时添加主键约束[几乎都是建表的时候创建约束]

create table 表名(
    id int primary key
)

3. 删除主键约束 (用的比较少)

alter table 表名 drop primary key

6. 主键自动增长「auto_increment」

  • 概念: 如果表中的某一个字段是数值类型的, 使用auto_increment可以来实现值的自动增长;

  • 是mysql中独特的概念, 有的数据库里面是没有自增的.

  • 在创建表中, 添加主键约束, 可以设置主键自增

    create table 表名(
  id int primary key auto_increment # 给id添加主键约束, 并且设置主键自增长
)

  • 删除主键的自增长(基本用不到 了解)

    alter table 表名 modify id int

5. 非空约束「not null」

  1. 概念

    设置某一列的值不能为null;

  2. 创建表的时候添加非空约束

    create table 表名(
 id int primary key auto_increment, # 给id添加主键约束, 并且设置主键自增长
    name varchar(32) not null # 设置非空约束
)

  3. 创建表完后，添加非空约束(了解)

    ALTER TABLE 表名 MODIFY NAME VARCHAR(32) not null;
# 删除非空约束
ALTER TABLE t6 MODIFY name VARCHAR(32);

7**.默认约束 [default 值]**

8. 外键约束「foreign key」

  • 让表于表产生关系，从而保证数据的正确性。

  • 在创建表时，可以添加外键

    create table 表名(
  id int primary key auto_increment, # 给id添加主键约束, 并且设置主键自增长
    name varchar(32) not null, # 设置非空约束
    phone_number varchar(20) unique # 添加非空约束
    # 外键字段
      外键字段 int;
      # []表示可选, 可以不写;
    [constraint 外键名称] foreign key(外键的字段名称) references 主表(主键的名称「主键的字段名称」)

  • 创建表之后，添加外键

    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

  • 删除外键

    alter table 表名 drop foreign key 外键名称;

  • 场景

    • 创建一个员工表, 包括(id, name, age, dep_name, dep_location)字段;

      # 创建一张员工表.
CREATE TABLE emp(
    id int PRIMARY KEY AUTO_INCREMENT,
    account VARCHAR(32) NOT NULL,
    age int,
    dep_name VARCHAR(32), # 部门名称
    dep_location VARCHAR(32) # 部门地址
);

# 插入一些测试数据
INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('张小三', 20, '研发部', '北京');
INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('李小四', 21, '研发部', '北京');
INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('王小五', 20, '研发部', '北京');
INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('马小六', 20, '销售部', '燕郊');
INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('田小七', 22, '销售部', '燕郊');
INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('王小八', 18, '销售部', '燕郊');

SELECT * FROM emp;

    • 解决办法

      拆分成两部分, 这里涉有了数据库设计的范式. 后面讲;

      • 拆分表

        • 部门表, department
        • 员工表, employee

      • 部门表, 主表.

        # 创建部门表
CREATE TABLE department(
  id int PRIMARY KEY AUTO_INCREMENT,
  dep_name VARCHAR(32),
  dep_location VARCHAR(32)
);

# 测试测试数据
insert into department values(1, '研发部','北京'),(2, '销售部', '燕郊');

SELECT * FROM department;

      • 员工表, 从表

        # 创建员工表
CREATE TABLE employee(
  id int PRIMARY KEY AUTO_INCREMENT,
  account VARCHAR(32) not null,
  age TINYINT,
  dep_id int # 外键对应的主键
);

        问题:

        • 在员工表中可以添加部门表中没有的部门id. 实际开发当中不应该出现此种问题.
          • employee中的dep_id中的数据只能是department表中的已经存在的id值;

        解决办法:

        • 必须让两张表产生某种关系才可以.
        • 使用外键约束

  • 使用外键约束

    • 删除掉员工表:

      drop table employee;

    • 在创建从表employee的时候添加外键约束.

      # 创建员工表
CREATE TABLE employee(
    id int PRIMARY KEY AUTO_INCREMENT,
    account VARCHAR(32) not null,
    age TINYINT,
    dep_id int # 外键对应的主键
    # 员工表employee创建外键约束.
  constraint emp_depid_fk foreign key (dep_id) references department(id);
);

# 正常添加数据
INSERT INTO employee (account, age, dep_id) VALUES ('张小三', 20, 1);
INSERT INTO employee (account, age, dep_id) VALUES ('李小四', 21, 1);
INSERT INTO employee (account, age, dep_id) VALUES ('王小五', 20, 1);
INSERT INTO employee (account, age, dep_id) VALUES ('马小六', 20, 2);
INSERT INTO employee (account, age, dep_id) VALUES ('田小七', 22, 2);
INSERT INTO employee (account, age, dep_id) VALUES ('王小八', 18, 2);

# 查询一下
select * from employee;

# 添加一些在department表中id列中不存在的数据;
INSERT INTO employee (account, age, dep_id) VALUES ('苏小九', 18, 3);

#INSERT INTO employee (account, age, dep_id) VALUES ('苏小九', 18, 3)
#> 1452 - Cannot add or update a child row: a foreign key constraint fails (`db0`.`employee`, CONSTRAINT `emp_depid_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

9. 级联操作「了解」

  • 概念: 在修改或者删除主表的主键时, 同时更新或者修改副表外键的值. 称为级联操作;

  1. 添加级联操作

    # 语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

  2. 分类

    • 级联更新: ON UPDATE CASCADE
    • 级联删除: ON DELETE CASCADE

10. 总结一波

约束名称关键字描述
主键primary key唯一,且非空,每张表只能有一个主键
默认default如果这一列没有值,则使用设置的默认值
非空not null这一列必须有值
唯一unique这一列不能有重复的值
外键foreign主表中的主键列, 副表中的外键列

第十章: 数据库的表之间的关系

1. 分类

1. 一对一

Untitled

desc description
举例: 像人和身份证的关系.
一个人只能有一个身份证号, 一个身份证号只能对应一个人;

2. 一对多「多对一」 [实际工作中使用频率非常多]

Untitled

举例: 部门和员工 班级和学生
一个部门可有多个员工, 但是一个员工只能对应一个部门;

3. 多对多

Untitled

举例: 学生和课程
一个学生可以选择多门课程, 一个课程也可以对应多个学生;

2. 实现关系「重点」

1. 一对多「多对一」

部门「和」和员工
实现方式: ==在多的一方建立外键 指向一的一方的主键==

https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537140.png

image-20201204213909310

2. 多对多 [关系表]

学生和课程
多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

Untitled

3. 一对一

人和身份证号
实现方式1：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。  和多对一一样的写法
实现方式2: 人表和身份证表使用同一个id

第十一章: 多表查询[重要且复杂]

1. 前置准备

  • 知识回顾

    select
  需要查询的字段
from
  表名 别名
where
  各种条件
group by
  分组字段
order by
  排序字段
limit
    • DQL查询
      • 主/外键
      • 非空
      • 唯一
    • 约束
    • 查询语法

2. 数据准备

SHOW TABLES;

# 创建部门表department
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL
);

# 插入一些数据
INSERT INTO department(name) VALUES
("市场部"), ("研发部"), ("公关部");

# 创建员工表 employee
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    gender VARCHAR(1) NOT NULL,  -- 性别
    salary DOUBLE NOT NULL, -- 工资
    create_date DATE, -- 入职日期
    dept_id INT
);

# 添加一些数据
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张无忌','男',7200,'2013-02-24',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张三丰','男',3600,'2010-12-02',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('赵敏','女',9000,'2008-08-08',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('周芷若','女',5000,'2015-10-07',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐三','男',4020,'2017-03-14',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞麟','男',4530,'2016-04-14',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞桐','女',5700,'2014-08-14',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('古月','女',5100,'2016-03-12',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('伍六七','男',6300,'2014-04-15',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('云韵','女',7500,'2011-03-16',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('美杜莎','女',2500,'2010-03-14',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('孙悟空','男',9500,'2020-05-14',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('朱八戒','男',5500,'2019-03-11',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('西门吹雪','男',2500,'2013-03-14',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('天山童姥','女',4300,'2011-05-14',1);

# 查看数据
SELECT * FROM employee;

3. 笛卡尔积

排列组合 我有两个大袋子, 一个袋子里面装的是球, 三角形和正方形; 另一个袋子里面装的是红色笔, 绿色笔

4. 多表查询

Untitled

  1. 内连接查询

    1. 隐式内连接 笛卡尔积查询方式
    2. 显示内连接 使用inner join语法
      1. 等值连接
      2. 不等值连接 员工 - 薪资级别
      3. 自连接 员工 - 领导

  2. 外连接查询

    1. 左外连接查询
    2. 右外连接查询

  3. 子查询

    1. 查询中嵌套查询，称嵌套查询为子查询。

      ## 1. 查询最高工资是是多少钱
## 2. 查询出最高工资的是谁.
## 3. 合并成一条.

5. 内连接查询 - 隐式内连接(用的很少)

  • 使用where条件消除无用数据

    -- 查询所有员工的信息和对应部门的信息;
-- 查询员工的姓名,性别和部门名称

6. 内连接查询 - 显示内连接

  • 语法格式

    select 字段列表 from 表名1 inner join 表名2 on 查询条件;
-- 查询所有员工的信息和对应部门的信息;
-- 查询员工的姓名,性别和部门名称

# 内连接操作步骤
# 1. 先确定从哪些表中查询数据
# 2. 给定查询的条件
# 3. 最后找出查询的字段即可;

7. 外连接查询 - 左外连接查询

  • 语法格式

    # 1. 左外连接
# 语法: SELECT 字段列表 FROM 表1 LEFT [outer] JOIN 表2 on 条件;
# 查询结果是: 左边的所有数据 + 与连接表交集的部分;

-- 例: 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称;

8. 外连接查询 - 右外连接查询

  • 语法格式

    # 2. 右外连接
# 语法: SELECT 字段列表 FROM 表1 RIGHT [outer] JOIN 表2 on 条件;
# 查询结果是: 右边的所有数据 + 与连接表交集的部分;

# 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称;

9. 子查询 - 结果是单行单列

  • 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

     -- 查询员工最高工资的人
 -- 查询员工工资小于平均工资的人

10. 子查询 - 结果是多行单列

  • 子查询可以作为条件，使用运算符in来判断

     -- 查询'财务部'和'市场部'所有的员工信息

11. 子查询 - 结果是多行多列

  • 子查询可以作为一张虚拟表参与查询

    -- 查询员工入职日期是2013--03-14日之后的员工信息和部门信息

- 查询高于平均薪资的员工的信息及其部门信息

使用union联合数据

使用前提, 两个被连接的sql语句的查询结果必须列数量和名称完全相同

12. 参考代码

-- 1. 查询所有员工的信息和对应部门的信息;
SELECT * FROM employee AS emp, department AS dep WHERE emp.dept_id = dep.id;

-- 2. 查询员工的姓名,性别和部门名称
SELECT
    emp.`name` 姓名, -- 员工姓名
    emp.gender 性别, -- 员工性别,
    dep.`name` 所在部门-- 部门名称
FROM
    employee AS emp, -- 员工表
    department AS dep -- 部门表
WHERE
    emp.dept_id = dep.id;

#############显示内连接################
-- 1. 查询所有员工的信息和对应部门的信息;
SELECT
*
FROM
    employee AS emp
INNER JOIN
    department AS dep
ON
    emp.dept_id = dep.id;
-- 2. 查询员工的姓名,性别和部门名称
SELECT
    emp.`name` 姓名, -- 员工姓名
    emp.gender 性别, -- 员工性别,
    dep.`name` 所在部门-- 部门名称
FROM
    employee AS emp
INNER JOIN
    department AS dep
ON
    emp.dept_id = dep.id;

# 内连接操作步骤
# 1. 先确定从哪些表中查询数据
# 2. 给定查询的条件
# 3. 最后找出查询的字段即可;

################## 外连接查询#################
# 1. 左外连接
# 语法: SELECT 字段列表 FROM 表1 LEFT [outer] JOIN 表2 on 条件;
# 查询结果是: 左边的所有数据 + 与连接表交集的部分;
# 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称;
SELECT
    emp.*,
    dep.`name`
FROM
    employee AS emp
LEFT OUTER JOIN
    department AS dep
ON
    emp.dept_id = dep.id;

# 2. 右外连接
# 语法: SELECT 字段列表 FROM 表1 RIGHT [outer] JOIN 表2 on 条件;
# 查询结果是: 右边的所有数据 + 与连接表交集的部分;

# 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称;
SELECT
    *
FROM
    employee AS emp
RIGHT OUTER JOIN
    department AS dep
ON
    emp.dept_id = dep.id;

SELECT
    *
FROM
    department AS dep
RIGHT OUTER JOIN
    employee AS emp
ON
    emp.dept_id = dep.id;
############$$$$$$$$$$$$$$$$$$$$$$# 子查询###########
--  查询员工工资小于平均工资的人
# 查出平均工资
SELECT
    MAX(emp.salary)
FROM
    employee AS emp;

# 查询最高工资的人
SELECT
    emp.`name`
FROM
     employee AS emp
WHERE
    emp.salary = 9500;

# 合并成一句
SELECT
    emp.`name`
FROM
    employee AS emp
WHERE
    emp.salary = (SELECT MAX(emp.salary) FROM employee AS emp)

## 查询员工工资小于平均工资的人
# 1. 查询到平均工资.
# 返回的是一行一列. 可以把查询结果用作查询条件;
SELECT
    AVG(emp.salary)
FROM
    employee AS emp;

# 2. 根据获取的查询条件. 获取最终的查询结果;
SELECT
    emp.`name`,
    emp.salary
FROM
    employee AS emp
WHERE emp.salary < (SELECT
    AVG(emp.salary)
FROM
    employee AS emp);

# 查询'财务部'和'市场部'所有的员工信息
# 获取id, 查询结果是多行单列的.可以用作查询条件.使用in来判断;
SELECT
    dep.id
FROM
    department as dep
WHERE
    dep.`NAME` = '市场部'
OR
    dep.`NAME` = '研发部';

SELECT
*
FROM
    employee AS emp
WHERE
    emp.dept_id
IN
    (SELECT
    dep.id
FROM
    department as dep
WHERE
    dep.`NAME` = '市场部'
OR
    dep.`NAME` = '研发部');

# 子查询的结果是多行多列的
# 查询员工入职日期是2013-03-14日之后的员工信息和部门信息
# 查询入职日期2013-03-14之后的员工信息
# 返回结果是多行多列的. 子查询可以作为一张虚拟表参与查询
SELECT
    *
FROM
    employee AS emp
WHERE
    emp.create_date > "2013-03-14";
SELECT
    *
FROM
    department AS dep,
    (SELECT * FROM employee AS emp WHERE emp.create_date > "2013-03-14") AS t
WHERE
    t.dept_id = dep.id;

# 使用普通内连接
# 查询员工入职日期是2013-03-14日之后的员工信息和部门信息
SELECT
    *
FROM
    employee AS emp,
    department AS dep
WHERE
    emp.dept_id = dep.id
AND
    emp.create_date > "2013-03-14";
  • 二次参考
# 创建部门表departmentDROP TABLE IF EXISTS department;CREATE TABLE department(    id INT PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(32) NOT NULL);# 插入一些数据INSERT INTO department(name) VALUES("市场部"), ("研发部"), ("公关部");# 创建员工表 employeeDROP TABLE IF EXISTS employee;CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    name VARCHAR(32) NOT NULL,    gender CHAR(1) NOT NULL,  -- 性别    salary DOUBLE NOT NULL, -- 工资    create_date DATE, -- 入职日期    dept_id INT NOT NULL, -- 外键字段    FOREIGN KEY(dept_id) REFERENCES department(id) -- 添加外键, 关联department表中的主键);# 添加一些数据INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张无忌','男',7200,'2013-02-24',1);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张三丰','男',3600,'2010-12-02',2);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('赵敏','女',9000,'2008-08-08',2);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('周芷若','女',5000,'2015-10-07',3);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐三','男',4020,'2017-03-14',1);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞麟','男',4530,'2016-04-14',1);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞桐','女',5700,'2014-08-14',3);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('古月','女',5100,'2016-03-12',3);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('伍六七','男',6300,'2014-04-15',2);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('云韵','女',7500,'2011-03-16',1);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('美杜莎','女',2500,'2010-03-14',2);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('孙悟空','男',9500,'2020-05-14',2);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('朱八戒','男',5500,'2019-03-11',1);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('西门吹雪','男',2500,'2013-03-14',3);INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('天山童姥','女',4300,'2011-05-14',1);SELECT * FROM employee;SELECT * FROM department;# 查询多张表.SELECT    *FROM    department as d,    employee as e;SELECT    *FROM        employee as e,        department as d;# 笛卡尔基# 内连接之隐式内连接查询-- 查询所有员工的信息和对应部门的信息;-- 查询员工的姓名,性别和部门名称SELECT    *FROM    employee as e,    department as dWHERE# 员式表中的dept_id和部门表中的主键是对应的.换句话说, 员式表中填写的dept_id都是来自于部门表的主键;    e.dept_id = d.id;-- 查询员工的姓名,性别和部门名称SELECT    e.name AS 员工姓名,    e.gender AS 员工性别,    d.name AS 部门信息FROM    employee e,    department AS dWHERE    e.dept_id = d.id;# 内连接, 显示的内连接#  []是可以省略掉的;# select 字段列表 from 表名1 [inner] join 表名2 on 查询条件;# 内连接操作步骤# 1. 先确定从哪些表中查询数据# 2. 给定查询的条件# 3. 最后找出查询的字段即可;-- 查询所有员工的信息和对应部门的信息;-- 查询员工的姓名,性别和部门名称SELECT*FROM    employee as eINNER JOIN    department AS dON    e.dept_id = d.id;# inner可以省略掉;SELECT*FROM    employee as eJOIN    department AS dON    e.dept_id = d.id;# 查询指定的字段SELECT    e.name AS 员工姓名,    e.gender AS 员工性别,    d.name AS 部门信息FROM    employee as eINNER JOIN    department AS dON    e.dept_id = d.id;######################### 外连接 ######################## 1. 左外连接# 语法: SELECT 字段列表 FROM 表1 LEFT [outer] JOIN 表2 on 条件;# 查询结果是: 左边的所有数据 + 与连接表交集的部分;DESC employee;DESC department;INSERT INTO department(name) VALUES('销售部');SELECT * FROM department;SELECT    *FROM    department as dLEFT OUTER JOIN    employee as eON    e.dept_id = d.id;-- 例: 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称;-- 反过来. 试一下;-- INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('云锋','男',4300,'2021-05-14',NULL);SELECT    *FROM    employee as eLEFT JOIN    department as dON    e.dept_id = d.id;# 右外连接SELECT*FROM    employee AS eRIGHT JOIN    department as dON    e.dept_id = d.id;## 子查询-- 查询员工工资小于平均工资的人SELECT * from employee;SELECT    AVG(e.salary)FROM    employee as e; #  结果是单行单列的.才可以做为一个查询条件;# 5483.333333333333SELECT *FROM    employee as eWHERE    e.salary < 5483.333333333333;
# 使用子查询,就是把单行单列的查询结果, 作为另一个查询的条件;
SELECT    *FROM    employee as eWHERE    e.salary < (SELECT AVG(e1.salary) FROM employee as e1);
# -- 查询'财务部'和'市场部'所有的员工信息# 查询出市场部的id和公关部的idSELECT    d.idFROM    department as dWHERE    d.name = '市场部'    OR    d.name = '公关部';SELECT    *FROM    employee as eWHERE    e.dept_id = 1    OR    e.dept_id = 3;
SELECT    *FROM    employee as eWHERE    e.dept_id IN(1,3);SELECT    *FROM    employee as eWHERE    e.dept_id IN(            SELECT                d.id            FROM                department as d            WHERE                d.name = '市场部'                OR                d.name = '公关部' 
# 查询出的结果是单列多行的,可以作为一个in ()    );#### 多行多列的情况-- 查询员工入职日期是2013--03-14日之后的员工信息和部门信息DESC employee;SELECT   * FROM employee;-- 先查询出, 入职日期是2013年3月14号之后的所有员工信息
SELECT    *FROM    employee as eWHERE    e.create_date > '2013-3-14';# 查询员工所对应的部门信息
SELECT    *FROM    (        SELECT            *        FROM            employee as e        WHERE            e.create_date > '2013-3-14'    ) as e,    department as dWHERE    e.dept_id = d.id;# 子查询SELECT    *FROM    employee as e,    department as dWHERE    e.dept_id = d.id;################ 强烈推荐此种写法;SELECT    *FROM    employee as e,    department as dWHERE    e.dept_id = d.id    AND    e.create_date > '2013-3-14';# 子查询# 能不用就不用子查询.# 可以把子查询拆分成两长或者多条查询语句, 最终把结果查询出来;# 分三种情况:# 1. 结果是单行单列的,可以作为查询条件,可以使用 >=, and or 进行查询操作;# 2. 结果是单列多行的,可以作为 IN(子查询结果是单列多行的)# 3. 结果是多行多列的可以作为一张虚拟表继续查询;# 外边接# 记住一种, 左外连接.# SELECT * FROM tab1 left [outter] join tab2 on 条件;# 内连接# 显示/隐匿# SELECT * FROM tab1 [inner]join tab2 on 条件;########################### dql查询. 「非常重要的内容」# 约束

13.练习题

第十二章: 事务与隔离级别「事务重要但是简单易学」 [隔离级别 了解性内容 底层复杂, 不适合初学者学习]

1. 概念

  • 如果一个包含多个步骤的业务操作事务管理,那么这些操作要么同时成功,要么同时失败;

2. 基本操作

# 1. 开启事务: start transaction;   (begin也可以, 老版本的语法)
# 2. 回滚事务: rollback
# 3. 提交: commit

3. 使用举例

  1. 转账的例子;

4. 事务提交的两种方式

  1. 自动提交, 这也是mysql当中默认的方式;

    1. 一条dml语句(增删改)会自动提交一次事务.

  2. 手动提交

    1. 需要先手动开启事务, 再进行提交;

  3. 修改事务的提交方式

    • 查看事务的提交方式

      查看事务的默认提交方式：
SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交

    • 修改提交方式

      set @@autocommit = 0;

  4. 事务的原理

    事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

    注意: 在start transaction之后立即执行select 查表数据, 可以查到未commit的记录, 除此之外的任何地方都查不到了

    image.png

  • 事务的步骤

    1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
    2. 开启事务以后，所有的操作都会先写入到临时日志文件中
    3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回
    4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件

    注意: START TRANSACTION;其后执行的dml语句都会被写入到临时日志文件中,无论此时@@autocommit被设置为1或者0

  • 回滚点

    在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

    • 操作语句

      回滚点的操作语句语句
      设置回滚点savepoint 名字
      回到回滚点rollback 名字

    • 实际操作

      # 转账的案例
# 1. 将所有的数据恢复到1000块钱;
# 2. 开启事务
# 3. 操作张小三的账号, 每次减10块钱. 操作2次;
# 4. 设置回滚点. savepoint two_times;
# 5. 继续操作张小三的账号, 操作2次.
# 6. 手动事务的回滚, rollback to two_times 回滚到回滚点.

      示例代码:

      建表account, 列为id和money

SELECT * FROM account;
UPDATE account set money = 1000;
START TRANSACTION;
UPDATE account set money = money - 10 WHERE id = 1;
UPDATE account set money = money - 10 WHERE id = 1;
# 设置回滚点
SAVEPOINT two_times;
UPDATE account set money = money - 10 WHERE id = 1;
UPDATE account set money = money - 10 WHERE id = 1;
# 回滚到回滚点
ROLLBACK TO two_times;

      设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

5. 事务的四大特征「了解」

  1. 原子性「Atomicity」：每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。
  2. 持久性「Durability」：当事务提交后，数据库会持久化的保存数据。
  3. 隔离性「Isolation」：事务与事务之间不应该相互影响，执行时应该保持隔离的状态。
  4. 一致性「Consistency」：事务操作前后，数据总量不变

6. 事务的隔离级别 纯了解

概念：多个事务之间隔离的，相互独立的。但是如果多个事务同时操作(有写行为)同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

  1. 存在的问题
    1. 脏读：一个事务，读取到另一个事务中没有提交的数据
    2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
    3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改;

7. 隔离级别 纯了解

  1. read uncommitted：读未提交
    1. 产生的问题：脏读、不可重复读、幻读
  2. read committed：读已提交 （Oracle）
    1. 产生的问题：不可重复读、幻读
  3. repeatable read：可重复读 （MySQL默认）
    1. 产生的问题：不可重复读, 幻读
  4. serializable：串行化
    1. 可以解决所有的问题

MySQL 中事务的隔离级别一共分为四种，分别如下：

  • 序列化（SERIALIZABLE）
  • 可重复读（REPEATABLE READ）
  • 提交读（READ COMMITTED）
  • 未提交读（READ UNCOMMITTED）

四种不同的隔离级别含义分别如下：

  1. SERIALIZABLE

如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

2.REPEATABLE READ

在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。

3.READ COMMITTED

READ COMMITTED 隔离级别的安全性比 REPEATABLE READ 隔离级别的安全性要差。处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。

4.READ UNCOMMITTED

READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。

在 MySQL 数据库中，默认的事务隔离级别是 REPEATABLE READ

查询隔离级别:

select @@tx_isolation; # 5.7版本
select @@transaction_isolation; # 8.0版本的命令;

查看隔离级别

通过如下 SQL 可以查看数据库实例默认的全局隔离级别和当前 session 的隔离级别：

MySQL8 之前使用如下命令查看 MySQL 隔离级别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

可以看到，默认的隔离级别为 REPEATABLE-READ，全局隔离级别和当前会话隔离级别皆是如此。

MySQL8 开始，通过如下命令查看 MySQL 默认隔离级别：

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

就是关键字变了，其他都一样。

通过如下命令可以修改隔离级别（建议开发者在修改时修改当前 session 隔离级别即可，不用修改全局的隔离级别）：

设置数据库的隔离级别:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

上面这条 SQL 表示将当前 session 的数据库隔离级别设置为 READ UNCOMMITTED，设置成功后，再次查询隔离级别，发现当前 session 的隔离级别已经变了

注意，如果只是修改了当前 session 的隔离级别，则换一个 session 之后，隔离级别又会恢复到默认的隔离级别，所以我们测试时，修改当前 session 的隔离级别即可。

8. 演示事务的隔离级别

1. 准备测试数据

READ UNCOMMITTED 是最低隔离级别，这种隔离级别中存在脏读、不可重复读以及幻象读问题，所以这里我们先来看这个隔离级别，借此大家可以搞懂这三个问题到底是怎么回事。

下面分别予以介绍。

首先创建一个简单的表，预设两条数据，如下：

CREATE TABLE account (    
id BIGINT UNSIGNED primary key,    
name varchar(100) NULL,    
balance BIGINT UNSIGNED NULL,    
CONSTRAINT account_un UNIQUE KEY (name));

https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537142.png

表的数据很简单，有 javaboy 和 itboyhub 两个用户，两个人的账户各有 1000 人民币。现在模拟这两个用户之间的一个转账操作。

2. 脏读

一个事务读到另外一个事务还没有提交的数据，称之为脏读。具体操作如下：

  1. 首先打开两个SQL操作窗口，假设分别为 A 和 B，在 A 窗口中输入如下几条 SQL （输入完成后不用执行）：

A窗口

START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
UPDATE account set balance=balance-100 where name='itboyhub';
COMMIT;

.在 B 窗口执行如下 SQL，修改默认的事务隔离级别为 READ UNCOMMITTED，如下：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

接下来在 B 窗口中输入如下 SQL，输入完成后，首先执行第一行开启事务（注意只需要执行一行即可）：

START TRANSACTION;
SELECT * from account;
COMMIT;

接下来执行 A 窗口中的前两条 SQL，即开启事务，给 javabody这个账户添加 100 元。

进入到 B 窗口，执行 B 窗口的第二条查询 SQL（SELECT * from account;），结果如下：

https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537143.png

可以看到，A 窗口中的事务，虽然还未提交，但是 B 窗口中已经可以查询到数据的相关变化了。

3. 不可重复读

不可重复读是指一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

具体操作步骤如下（操作之前先将两个账户的钱都恢复为1000）：

**1.**首先打开两个查询窗口 A 和 B ，并且将 B 的数据库事务隔离级别设置为 READ COMMITTED。 执行下面语句

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

**2.**在 B 窗口中输入如下 SQL，然后只执行前两条 SQL 开启事务并查询 javaboy 的账户：

START TRANSACTION;
SELECT * from account where name='javaboy';
COMMIT;

3.在 A 窗口中执行如下 SQL，给 javaboy这个账户添加 100 块钱，如下：

START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
COMMIT;

**4.**再次回到 B 窗口，执行 B 窗口的第二条 SQL 查看 嘉欣的账户，结果如下：

javaboy 的账户已经发生了变化，即前后两次查看 javaboy账户，结果不一致，这就是不可重复读。

和脏读的区别在于，脏读是看到了其他事务未提交的数据，而不可重复读是 同一次事务中查询两次得到的结果不相同。

4.幻像读

构造测试数据

create table new_table
(idx int,
name varchar(10),
age int
)

insert into new_table(idx,name,age)
values(3,'b',12)

开启两个事务窗口;

Untitled

a窗口

begin;
select * from new_table;	-- T1

select * from new_table;	-- T3

select * from new_table;	-- T4
update new_table set name='z' where age = 12;
select * from new_table;	-- T5

b窗口

begin;
insert into new_table values(5, 'c', 12);
select * from new_table;	-- T2

commit;

T1,T3,T4,T5是在同一个事务中，按照RR可重复读的定义，他们读出的数据该是一样的。但T5出现了不一样的数据，即出现了幻读。

5. READ COMMITTED [读已提交]

和 READ UNCOMMITTED 相比，READ COMMITTED 主要解决了脏读的问题，对于不可重复读和幻象读则未解决。

将事务的隔离级别改为 READ COMMITTED 之后，重复上面关于脏读案例的测试，发现已经不存在脏读问题了；重复上面关于不可重复读案例的测试，发现不可重复读问题依然存在。

上面那个案例不适用于幻读的测试，我们换一个幻读的测试案例。

还是两个窗口 A 和 B，将 B 窗口的隔离级别改为 READ COMMITTED，

b窗口执行如下语句

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

然后在 A 窗口输入如下测试 SQL：

START TRANSACTION;insert into account(id,name,balance) values(3,'zhangsan',1000);
COMMIT;

在 B 窗口输入如下测试 SQL：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;SELECT * from account;
insert into account(id,name,balance) values(4,'zhangsan',1000);
COMMIT;

测试方式如下：

  1. 首先执行 B 窗口的前两行 SQL，开启事务并查询数据，此时查到的只有 javaboy 和 itboyhub 两个用户。
  2. 执行 A 窗口的前两行 SQL，插入一条记录，但是并不提交事务。
  3. 执行 B 窗口的第二行 SQL，由于现在已经没有了脏读问题，所以此时查不到 A 窗口中添加的数据。
  4. 执行 B 窗口的第三行 SQL，由于 name 字段唯一，因此这里会无法插入。此时就产生幻觉了，明明没有 zhangsan 这个用户，却无法插入 zhangsan。

6. REPEATABLE READ [可重复读]

和 READ COMMITTED 相比，REPEATABLE READ 进一步解决了不可重复读的问题，但是幻象读则未解决。

REPEATABLE READ 中关于幻读的测试和上一小节基本一致，不同的是第二步中执行完插入 SQL 后记得提交事务。

由于 REPEATABLE READ 已经解决了不可重复读，因此第二步即使提交了事务，第三步也查不到已经提交的数据，第四步继续插入就会出错。

注意，REPEATABLE READ 也是 InnoDB 引擎的默认数据库事务隔离级别

7. SERIALIZABLE [串行化]

SERIALIZABLE 提供了事务之间最大限度的隔离，在这种隔离级别中，事务一个接一个顺序的执行，不会发生脏读、不可重复读以及幻象读问题，最安全。

如果设置当前事务隔离级别为 SERIALIZABLE，那么此时开启其他事务时，就会阻塞，必须等当前事务提交了，其他事务才能开启成功，因此前面的脏读、不可重复读以及幻象读问题这里都不会发生。

8. 总结

总的来说，隔离级别和脏读、不可重复读以及幻象读的对应关系如下：

https://selton-1257770752.cos.ap-beijing.myqcloud.com/img/202304131537144.png

第十三章：数据库设计（了解前3范式）

1. 范式的概述

范式源自英文Normal Form，简称NF。设计良好的关系数据库需满足特定的约束条件，这些约束已形成规范，分为几个等级，每一级都比前一级要求更严格。符合这些规范的数据库结构简洁明晰，不会出现插入（insert）、删除（delete）和更新（update）操作异常。反之，数据库结构将混乱不堪，不仅给数据库程序员带来麻烦，还可能存储大量不必要的冗余信息。

2. 三大范式

关系数据库共有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。第一范式（1NF）是最基本的要求。在此基础上，满足更多规范要求的是第二范式（2NF），其他范式依此类推。通常，数据库只需满足第三范式（3NF）即可。

3. 1NF（第一范式）

数据库表的每一列都必须是不可分割的原子数据项，不能是集合、数组等非原子数据项。如果表中某列包含多个值，必须将其拆分为不同的列。

简而言之，第一范式要求每一列不可再拆分，这就是所谓的原子性。

4. 2NF（第二范式）

在满足1NF的基础上，非主键属性必须完全依赖于主键（消除非主属性对主键的部分函数依赖）。

更简单地说：每张表必须有一个主键，能唯一确定每一行的值。

换句话说，在满足第一范式的前提下，表中的每个非主键字段都必须完全依赖于主键。

5. 重要概念

  • 函数依赖

    函数依赖：A→B，如果通过A属性（或属性组）的值，可以唯一确定B属性的值，则称B依赖于A。例如：学号→姓名，（学号，课程名称）→分数，身份证号→人。

  • 完全函数依赖（针对属性组）

    完全函数依赖：A→B，如果A是一个属性组（多个属性的集合），则B属性值的确定需要依赖于A属性组中所有的属性值。

    例如：（学号，课程名称）→成绩，（身份证号，性别）→人，（A+B）→C。

  • 部分函数依赖（针对属性组）

    A→B，如果A是一个属性组，而B属性值的确定只需要依赖于A属性组中的部分属性值。例如：（学号，课程名称）→姓名。

  • 传递函数依赖

    A→B，B→C。如果通过A属性（属性组）的值，可以唯一确定B属性的值，再通过B属性（属性组）的值可以唯一确定C属性的值，则称C传递函数依赖于A。

    例如：学号→系名，系名→系主任。

  • 码

    如果在一张表中，一个属性或属性组被其他所有属性完全依赖，则称这个属性（属性组）为该表的码。

    例如：该表中的码为（学号，课程名称）。

    主属性：码属性组中的所有属性。

    非主属性：除码属性组之外的属性。

6. 3NF（第三范式）

在2NF基础上，任何非主属性不依赖于其它非主属性（消除传递依赖）。

简单概括：每张表中的数据只能由主键唯一确定，不能通过非主键属性确定。

要点：
理解码、主属性、非主属性、完全依赖、部分依赖、传递依赖等概念。

第十四章：重点内容

  • DQL 最重要
  • DML 次重要
  • 约束 一般
  • DDL(了解)
  • 事务 次重要
    • 四在特征 纯鸡肋
    • 隔离级别[了解]
      • 每种隔离级别所产生的问题
      • 脏读, 不可重复读「虚读」, 幻读

索引 记着如何使用就ok了, 为什么以后高级篇讲解

第十五章：DCL操作 [课外了解]

1. 概念

DCL：管理用户、授权访问

2. 管理用户

# 1. 添加用户：
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

# 2. 删除用户：
DROP USER '用户名'@'主机名';

# 3. 修改用户密码：
# 方法一：
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
# 示例：
UPDATE USER SET PASSWORD = PASSWORD('000') WHERE USER = 'tom';

# 方法二：
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
# 示例：
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

3. 忘记用户密码的解决方法

1. 以管理员身份运行cmd，执行：net stop mysql
2. 使用无验证方式启动MySQL服务：mysqld --skip-grant-tables
3. 打开新的cmd窗口，直接输入mysql命令并回车登录
4. 执行：use mysql;
5. 执行：update user set password = password('新密码') where user = 'root';
6. 关闭两个cmd窗口
7. 打开任务管理器，手动结束mysqld.exe进程
8. 启动MySQL服务
9. 使用新密码登录

4. 查询用户

1. 切换到mysql数据库：USE mysql;
2. 查询user表：SELECT * FROM user;
# 注：通配符 % 表示可以在任意主机使用该用户登录数据库

5. 权限管理

# 1. 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
# 示例：
SHOW GRANTS FOR 'root'@'%';

# 2. 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
# 示例：给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

# 3. 撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
# 示例：
REVOKE UPDATE ON db3.account FROM 'lisi'@'%';

第十六章：数据库的备份和还原 [linux篇中，使用docker数据卷完成]

1. 后期知识点：使用docker数据卷备份与还原（推荐）
   mysqldump -- 不推荐使用

第十七章：索引、数据库SQL、优化 [分初级和高级，MySQL高级篇讲解]

繁星-赵老师
关注
详解MySQL从入门到放弃-安装
12-16
MySQL下载链接：MySQL   下载第一个，之后解压缩。 我的目录在C:\Users\Public\MySQL，之后在该文件夹配置my.ini 接下来启动MySQL，在cmd中操作 cd C:\Users\Public\MySQL\bin mysqld --initialize --c
MySQL从入门到放弃(一)
热门推荐
MANKVIS的博客
04-10 1万+
1.数据库基础(1)什么是数据库        数据库(DataBase，DB)是一个长期存储在计算机内的，有组织的，有共享的，统一管理的数据集合。        数据库的发展大致划分为如下几个阶段:人工管理阶段、文件系统阶段、数据库系统阶段、高级数据库阶段、其种类大概有3种：层次式数据库、网络式数据库、关系是数据库，不同种类的数据库按照不同的数据库结构来联系和组织。(2)数据库数据类型     ...
MySQL从入门到放弃第一章：初涉
王小贱的博客
01-15 716
初涉mysql 本系列将会从0开始学习Mysql，大部分是理论加部分实例 学习目录 - mysql默认端口 3306 - mysql超级用户 root - 创建数据库 CREATE DATABASES - 修改数据库 ALTERE DATABASE - 删除数据库 DROP DATABASE 启动服务 mysql -uroot -p123456 -
《MySql从入门到放弃》学习记录.zip
02-22
总的来说，《MySQL从入门到放弃》的学习涵盖了数据库的基础知识，对于理解和应用MySQL有着极大的帮助。通过实践这些概念，你可以更好地掌握如何利用MySQL来存储、管理和检索数据，为你的项目或工作带来便利。
MyCat从入门到放弃
04-24
【MyCat从入门到放弃】 MyCat是一款开源的分布式数据库中间件，它的设计目标是让大数据分布式处理变得简单。在大型互联网应用中，面对高并发、海量数据的挑战，单机数据库往往无法满足需求，因此MyCat应运而生。本...
java web 从入门到放弃
04-22
### Java Web 从入门到放弃 #### 一、基本概念 ##### 1.1 Web 开发 - **静态 Web**：静态网页是指那些内容固定、不会根据用户的不同操作或访问而变化的网页。例如，纯 HTML 页面就是典型的静态网页。这类网页在...
【mysql相关总结】
Charles D
09-30 257
数据库小的表,全表扫描效率更高，不用建索引。索引的类型1.普通索引：基本的索引，没有任何约束限制2.唯一索引：类似普通索引,有唯一约束性3.主键索引：特殊的唯一索引,不允许有空值4.组合索引：多个列组合创建索引5.外键索引：只有innodb可以使用6.全文索引：只有myisam可以使用，且只能对英文进行全文检索注意事项1.复合索引遵循前缀原则（最左匹配原则）2.like查询,%号不能在前面，否则索引失效3.如果or前面的条件有索引,也会失效。
Python 3 和 MySQL(PyMySQL) 的完美结合
最新发布
wjs2024的博客
10-02 329
PyMySQL 是一个纯 Python 编写的 MySQL 客户端库，它提供了与 MySQL 数据库进行交互的接口。PyMySQL 设计轻量、易于安装和使用，且完全兼容 MySQLdb 接口，使得从 MySQLdb 迁移变得非常简单。Python 3 和 PyMySQL 库的结合为开发人员提供了一种高效、简洁的方式来与 MySQL 数据库进行交互。通过本文的介绍，您应该能够理解如何安装 PyMySQL、建立数据库连接、执行 SQL 语句、处理查询结果以及处理可能出现的错误。
docker容器mysql数据备份 mysql容器无法启动备份数据
zhangxu1998lq的博客
09-30 309
mysql是旧容器名 因为mysql的配置文件my.cnf的路径没有映射到服务器，我修改了配置文件，配置文件有错误导致，容器启动不了，一直restarting。nmysql是新容器名 mysql是旧容器名 mysql:5.7是我的镜像名。新容器正常启动，旧容器的数据都还在。
滚雪球学MySQL[1.3讲]：MySQL客户端工具：详解与实践
**My Coding Family**
10-01 1167
在上期文章中，我们深入探讨了MySQL的安装与配置，这为我们顺利使用MySQL打下了基础。具体来说，我们介绍了如何正确安装MySQL、配置环境变量、调整字符集，并确保数据库可以正常运行。这些基础配置是后续操作的前提，理解并掌握这些技能能够帮助你在未来的开发工作中减少不必要的麻烦。然而，安装与配置仅仅是迈出了使用MySQL的第一步。真正的工作是如何高效地与数据库交互，并对数据进行增删改查。这时候就需要借助强大的MySQL客户端工具。
基于SpringBoot+Vue+MySQL的考勤管理系统
程序员大金的博客
09-30 806
基于SpringBoot+Vue+MySQL的考勤管理系统，附源码。
hive/impala/mysql几种数据库的sql常用写法和函数说明
biqidaoer的专栏
09-27 400
做大数据开发的时候，会在几种库中来回跳，同一个需求，不同库函数和写法会有出入，在此做汇总沉淀。
Python对数据库（MySQL，redis、MongoDB）的操作
wu73guang5jian的博客
09-27 1491
python对MySQL、Redis，MongoDB的操作
MySQL—索引机制详解
m0_74293254的博客
09-27 1002
MySQL 官方定义索引为一种帮助数据库高效获取数据的结构，其本质是排好序的快速查找数据结构。数据库除了存储表中的数据，还维护一些额外的满足特定查找算法的数据结构，这些数据结构以某种方式指向实际的数据，从而加快查询的速度。这种额外维护的数据结构就是索引。由于索引是在存储引擎层实现的，不同的存储引擎对索引的实现方式可能不尽相同，因此没有统一的索引标准。索引的使用数据库中的索引可以类比为书籍的目录，它指向实际的数据，帮助快速定位。下图展示了索引的工作原理：在图中，左边是一张数据表，包含两列和七条记录。
【MySQL】内置函数
2401_84568462的博客
09-28 640
注意：length函数返回字符串字节数时，单个字母和数字是1字节；(与字符集编码有关)注意interval后的数值单位可以是：year、day、minute、second。
DC00021基于springboot问卷调查管理系统web项目调查问卷管理系统MySQL（附源码）
黄昏下的黎明的博客
09-28 651
DC00021基于springboot问卷调查管理系统web项目调查问卷管理系统MySQL。
MySQL：进阶巩固-触发器
MMF博客园
09-29 342
1.2.6 MySQL：进阶巩固-触发器
一文理解mysql 联合索引和各种SQL语句分析
csdn问鼎
09-28 269
联合索引有两个rule要记一下，1.左到右，中间不能有skip,2.中间是range,后面不能用索引了联合索引的顺序非常重要，即使上面走了索引，也可能效果不好，正确的顺序是根据业务场景把最能区分的列放在前面，按照这样的顺序从左到右。
MySQL入门教程：从零开始到精通
网博课程研发部的MySQL入门到精通学习课件提供了一套全面的MySQL教学体系，旨在帮助学员掌握从基础到进阶的知识。课程主要涵盖以下几个关键部分： 1. **课程总目标**： - 安装与配置：学习如何安装和配置MySQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

繁星-赵老师

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值