当前位置：首页 > article >正文

数据库基础（MySQL）

article 2024/11/15 15:13:40

1. 概述

2. MySQL安装准备

3. SQL概述

3.1 什么是SQL

3.2 SQL通用语法

3.3 SQL分类

4. DDL

4.1 操作数据库

4.1.1 C(Create)创建

4.1.2 R(Retrieve)查询

4.1.3 U(Update):修改

4.1.4 D(Delete):删除

4.1.5 使用数据库

4.2 操作表

4.2.1 C(Create):创建

4.2.2 R(Retrieve)查询

4.2.3 U(Update):修改

4.2.4 D(Delete):删除

5. DML

5.1 添加数据

5.2 删除数据

5.3 修改数据

6. DQL

6.1 语法

6.2 基础查询

6.3 条件查询

6.4 模糊查询

6.5 排序查询

6.6 聚合函数

6.7 分组查询:

6.8 分页查询

7. 约束

7.1 概念

7.2 分类

7.3 非空约束

7.4 唯一约束

7.5 主键约束

7.6 自动增长

7.7 外键约束

7.8级联操作

8. 数据库的设计

8.1 多表之间的关系

8.2 实现关系

9. 多表查询

9.1 多表查询分类

9.2 内连接查询

9.2.1 隐式内连接

9.2.2 显示内连接

9.3 外连接查询

9.3.1 左外连接

9.3.2 右外连接

9.4 子查询

10. 事务

10.1 事务概述

10.2 事务提交方式

10.3 事务四大特征

10.4 事务隔离级别

10.5 数据库的备份和还原

1. 概述

什么是数据库
- 数据库就是用来存储和管理数据的仓库，数据库的英文单词 DataBase，简称 DB
数据库存储数据的优点
- 可存储大量数据；
- 方便检索；
- 保持数据的一致性、完整性；
- 安全，可共享；
- 通过组合分析，可产生新数据。
- 持久化
数据库特点
- 持久化存储数据的。其实数据库就是一个文件系统，就是个.db文件
- 方便存储和管理数据
- 使用了统一的方式操作数据库 -- SQL
常见的数据库
- 关系型数据库
  - Oracle：甲骨文公司，收费大型数据库
  - DB2：IBM公司的数据库产品, 收费的。常应用在银行系统中
  - SQL Server：微软公司收费的中型的数据库。C#、dot net framework，等语言常使用
  - SQL Lite：嵌入式的小型数据库，应用在手机端，如：Android
  - MySQL：甲骨文，开源免费的数据库，已经被 Oracle 收购了，后来 Sun公司收购了 MySQL，而 Sun 公司又被 Oracle 收购
- 非关系型数据库
  - redis

2. MySQL安装准备

MySQL安装-CSDN博客https://blog.csdn.net/weixin_63314150/article/details/142252162

3. SQL概述

3.1 什么是SQL

Structured Query Language：结构化查询语言

定义了操作所有关系型数据库的规则，可以使用sql语句语法操作任何关系型数据库。每一种数据库操作的方式存在不一样的地方，称为“方言”。不同数据库在使用sql操作的时候会有细微的不一样地方。

3.2 SQL通用语法

1. SQL 语句可以单行或多行书写，以分号结尾。

2. 可使用空格和缩进来增强语句的可读性。

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

4. 有3 种注释

单行注释: -- 注释内容 --和注释内容之间需要加空格

#注释内容(MySQL 特有)，# 和注释内容之间不需要加空格
多行注释: /* 注释 */

3.3 SQL分类

DDL(Data Definition Language)数据定义语言

用来定义数据库对象数据库，表，列等。关键字：create, drop, alter 等

DML(Data Manipulation Language)数据操作语言

用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

DQL(Data Query Language)数据查询语言

用来查询数据库中表的记录(数据)。关键字：select, where 等

DCL(Data Control Language)数据控制语言

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

4. DDL

4.1 操作数据库

4.1.1 C(Create)创建

创建数据库
```
create database 数据库名称;
```

创建数据库，判断不存在，再创建

create database if not exists 数据库名称;

创建数据库，同时并指定字符集

create database 数据库名称 character set 字符集名

4.1.2 R(Retrieve)查询

查询所有数据库的名称:
```
show databases;
```
查询某个数据库的字符集:查询某个数据库的创建语句
```
show create database 数据库名称;
```

4.1.3 U(Update):修改

修改数据库的字符集

 alter database 数据库名称 character set 字符集名称

4.1.4 D(Delete):删除

删除数据库
```
drop database 数据库名称;
```

判断数据库存在，存在再删除

drop database if exists 数据库名称;

4.1.5 使用数据库

查询当前正在使用的数据库名称
```
select database();
```
使用数据库
```
use 数据库名称;
```

4.2 操作表

4.2.1 C(Create):创建

数据类型

MySQL与Java一样，也有数据类型。

MySQL中数据类型主要应用在列上。

常用类型
- int：整型 int(11) 默认11位长度;
- double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
- char：固定长度字符串类型；最大长度255字节；
- varchar：可变长度字符串类型；最大长度65532字节；
  - name varchar(20):姓名最大20个字符， zhangsan 8个字符张三 2个字符
- char和varchar区别：char 表示定长，长度固定，varchar表示变长，即长度可变。
  
  char如果插入的长度小于定义长度时，则用空格填充；
  
  例如定义char(20)，实际上存储长度为2，其余位置用空格填充。
  
  varchar小于定义长度时，还是按实际长度存储，插入多长就存多长；
  
  例如定义varchar(20)，实际存储长度为2，那就按照长度为2存储。
- date：日期类型，格式为：yyyy-MM-dd，默认就是这个格式；
- time：时间类型，格式为：hh:mm:ss；
- datetime：日期时间类型,包含年月日时分秒 yyyy-MM-dd HH:mm:ss;
- timestamp: 时间戳类型, 包含年月日时分秒 yyyy-MM-dd HH:mm:ss;
  
  datetime和timestamp区别:
  - 存储方式不同
    
    对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。
    
    查询时，将其又转化为客户端当前时区进行返回。
    
    而对于DATETIME，不做任何改变，基本上是原样输入和输出。
  - 时间范围
  timestamp所能存储的时间范围为'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。
  
  datetime所能存储的时间范围为'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。
创建表

CREATE TABLE 表名(
  列名 列类型,
  列名 列类型,
  ......
);

例如

CREATE TABLE stu(
    sid     CHAR(6),
    sname   VARCHAR(20),
    age     INT,
    gender  VARCHAR(10)
);

注意最后一列，不需要加逗号（,）

复制表

create table 表名 like 被复制的表名;

4.2.2 R(Retrieve)查询

查询某个数据库中所有的表名称

show tables;

查询表结构

desc 表名;

查询某个表的字符集: 查询建表语句

show create table 表名;

4.2.3 U(Update):修改

修改表名

alter table 表名 rename to 新的表名;

修改表的字符集

alter table 表名 character set 字符集名称;

添加一列

alter table 表名 add 列名 数据类型;

修改列名称、类型

既改了列名称也改了列类型

alter table 表名 change 列名 新列名 新数据类型;

只是改了列类型没有改列名称

alter table 表名 modify 列名 新数据类型;

5. 删除列

alter table 表名 drop 列名;

4.2.4 D(Delete):删除

删除表

drop table 表名;

存在则删除（先判断后删除）

drop table  if exists 表名;

5. DML

DML：增删改表中数据

5.1 添加数据

语法

INSERT INTO 表名(列名1,列名2, ...) VALUES(值1, 值2,...)

例如

INSERT INTO stu(sid, sname, age, gender) VALUES('s_1001', 'zs', 23, '男');
INSERT INTO stu(sid, sname) VALUES('s_1001', 'zs');

注意

列名和值要一一对应。
如果表名后，不定义列名，则默认给所有列添加值
```
INSERT INTO 表名 VALUES(值1,值2,...)
```
因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值
```
INSERT INTO stu VALUES('s_1002', 'lisi', 32, '女');
```
除了数字类型，其它类型需要使用引号(单双都可以)引起来

5.2 删除数据

语法

DELETE FROM 表名 [WHERE 条件]

例如

DELETE FROM stu WHERE sid='s_1001';
DELETE FROM stu WHERE sname='zhangsan' OR age > 30;
DELETE FROM stu;

注意

如果不加条件，则删除表中所有记录。

如果要删除所有记录
1. 不推荐使用。有多少条记录就会执行多少次删除操作
```
delete from 表名;
```
2. 推荐使用，效率更高先删除表，然后再创建一张一样的表。
```
TRUNCATE TABLE 表名; 
```

5.3 修改数据

语法

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

例如

UPDATE stu SET sname='zhansgan', age='32', gender='女' WHERE sid='s_1001';
UPDATE stu SET sname='liSi', age='20' WHERE age>50 AND gender='男';
UPDATE stu SET sname='wangWu', age=’30’ WHERE age>60 OR gender='女';
UPDATE stu SET gender='女' WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE sname='wangwu';

注意
- 如果不加任何条件，则会将表中所有记录全部修改。

6. DQL

数据查询语言

6.1 语法

select
        字段列表
from
        表名列表
[where]
        条件列表
[group by]
        分组字段
[having]
        分组之后的条件
[order by]
        排序
[limit]
        分页限定

6.2 基础查询

查询所有列

SELECT * FROM 表名;

例如

SELECT * FROM stu;

多个字段的查询

SELECT 字段名1，字段名2... FROM 表名;

例如

SELECT id, name, age FROM stu;

去除重复

SELECT DISTINCT NAME FROM stu;      

/*去重复的是结果集 必须列name 和 address一模一样 才可以去重复*/
SELECT DISTINCT  address,NAME FROM stu;

计算列

一般可以使用四则运算(+ - * / )计算一些列的值,（一般只会进行数值型的计算）
```
/*计算math 和 english 成绩之和*/
SELECT NAME,math,english, math+english FROM stu;
```

ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null

/* english有成绩为null，计算结果都是null */
SELECT NAME,math,math + english FROM stu;

表达式1：哪个字段需要判断是否为null，如果该字段为null后的替换值。

/*如果有null参与运算  计算结果默认都是都是null  name如何能让null参与运算时默认是0呢 */
SELECT NAME,math,english,(math + IFNULL(english,0) FROM stu;

起别名

/*起别名 as*/
SELECT NAME ,math,english,IFNULL(math,0) + IFNULL(english,0) AS 总分 FROM stu;    

/*起别名 as可以省略*/
SELECT NAME,math,english,IFNULL(math,0)+IFNULL(english,0) 总分 FROM stu;

6.3 条件查询

where子句后跟条件

运算符

> 、< 、<= 、>= 、= 、<>  
BETWEEN...AND  
and  或 &&
or  或 || 
IN( 集合)
IS NULL  是  空
not 或 !

-- 查询年龄大于20岁
SELECT * FROM student2 WHERE age > 20;

-- 查询年龄大于等于20岁              
SELECT * FROM student2 WHERE age >= 20;

-- 查询年龄等于20岁的人有哪些
SELECT id,NAME,age FROM student2 WHERE age = 20;

-- 查询年龄不等于20岁  <> !=,    <>和!=用法一样，没什么具体区别，<>出现的早，!=出现的晚，更好理解
SELECT id,NAME,age FROM student2 WHERE age <> 20;
SELECT id,NAME,age FROM student2 WHERE age != 20;

-- 查询年龄大于等于20 小于等于30
SELECT * FROM student2 WHERE age >= 20 && age <=30 ;-- 并且&& sql中 不推荐使用
SELECT * FROM student2 WHERE age >= 20 AND age <=30 ;-- 并且and
SELECT * FROM student2 WHERE age BETWEEN 20 AND 30; -- 包含20 和30

-- 查询年龄22岁，18岁，55岁的信息
SELECT * FROM student2 WHERE age = 18 OR age =22 OR age=55 ;
SELECT * FROM student2 WHERE age = 18 || age =22 || age=55 ;
SELECT * FROM student2 WHERE age IN(18,20,55);-- in 范围集合

-- 查询英语成绩为null的所有数据     错误写法演示
SELECT * FROM student2 WHERE english = NULL; -- 不对的。null值不能使用 = （!=） 判断

-- 查询英语成绩为null的所有数据  使用is  
SELECT * FROM student2 WHERE english IS NULL;

-- 查询英语成绩不为null  IS NOT
SELECT * FROM student2 WHERE english  IS NOT NULL;

6.4 模糊查询

LIKE：模糊查询必须使用like关键字
占位符
- _ : 单个任意字符
- % 多个任意字符

-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';

-- 查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";

-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
                
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

6.5 排序查询

语法：order by 子句

order by 排序字段1 排序方式1,  排序字段2 排序方式2...

排序方式

ASC：升序，默认的
DESC：降序

-- 升序 / 降序排列
SELECT * FROM student4 ORDER BY math;-- 默认升序
SELECT * FROM student4 ORDER BY math ASC;-- 默认升序
SELECT * FROM student4 ORDER BY math DESC;-- 降序

如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

-- 如果数学成绩是一样的 那就按照英语成绩排名
-- 第一种排序方式是一样的， 就按照第二种方式排列
-- 只有第一种成绩是一样的   第二种排序才会起作用
SELECT * FROM student4 ORDER BY math ASC,english DESC;

6.6 聚合函数

聚合函数将一列数据作为一个整体，进行纵向的计算
- COUNT()统计指定列不为NULL的记录行数；
- MAX()计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
- MIN()计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
- SUM()计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
- AVG()计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

COUNT：计算个数

一般选择非空的列主键
COUNT(*)

-- 查询一共总人数
SELECT COUNT(1) FROM student5; -- * 一般不推荐去写
SELECT COUNT(id) FROM student5; -- 选择非空的列去计算 一般选主键
SELECT COUNT(id) AS 总人数  FROM student5;-- 别名
SELECT COUNT(english) FROM student5; -- 聚合函数 不包含null值，因为英语成绩有null

-- 在count中尽可能不要加 null列  选择非null的列
SELECT COUNT(IFNULL(english,0)) FROM student5; -- 聚合函数 不包含null值,虽然是null 但是得算一个人啊

MAX：计算最大值

-- 最大值
SELECT MAX(math) FROM student5;

MIN：计算最小值

-- 最小值
SELECT MIN(math) FROM student5; 
-- 查询最高数学成绩 和 最低英语成绩
SELECT MAX(math), MIN(english) FROM emp;

SUM：计算和

-- SUM：计算和
SELECT SUM(math) FROM student5;

AVG：计算平均值

-- AVG：平均值
SELECT AVG(english) FROM student5; -- 排除了null 然后计算的结果 81.42

聚合函数的计算，排除null值。

解决方案

1. 选择不包含非空的列进行计算

2. IFNULL函数

-- 如果英语是null name就按照0分计算 求平均值  /8人算平均成绩的
SELECT AVG(IFNULL(english,0)) FROM student5; -- 71.25

6.7 分组查询:

语法：group by 分组字段；

注意
分组之后查询的字段分组字段、聚合函数

where 和 having 的区别:

where 在分组之前进行限定，如果不满足条件，则不参与分组
having在分组之后进行限定，如果不满足结果，则不会被查询出来
where 后不可以跟聚合函数，having可以进行聚合函数的判断

-- 按照性别分组 分别查找男 女 成绩平均分
-- sex 分组之后字段  聚合函数
SELECT sex, AVG(math) FROM student6 GROUP BY sex;

-- 查询男的 女的 各有多少  数学分别平均分是多少
SELECT sex, AVG(math),COUNT(id) FROM student6 GROUP BY sex;

--  按照性别分组。分别查询男、女同学的平均分,人数 要求分数低于70分的人，不参与分组
SELECT sex, AVG(math),COUNT(id) FROM student6 WHERE math > 70 GROUP BY sex; 

--  按照性别分组。分别查询男、女同学的平均分,人数 要求分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex, AVG(math),COUNT(id) FROM student6 WHERE math > 70  GROUP BY sex HAVING COUNT(id)>=2;

-- 别名写法
SELECT sex 性别,AVG(math) AS 数学,COUNT(id) AS 人数 FROM student6 WHERE math >70 GROUP BY sex HAVING 人数>2;

6.8 分页查询

语法：limit 开始的索引 , 每页查询的条数;
公式开始的索引 = （当前的页码 - 1） * 每页显示的条数

每页显示3条记录

SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页     
SELECT * FROM student LIMIT 6,3; -- 第3页
前端分页发送请求2个参数
offset：第几页
count：每页显示数量
SELECT * FROM student LIMIT (offset -1)*count,10; --

limit 是一个MySQL"方言" 只是MySQL关键字，在其它数据库中用不了

7. 约束

7.1 概念

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的正确性、完整性、有效性、唯一性。

7.2 分类

not null非空约束，指定某列不为空。
unique唯一约束，指定某列和几列组合的数据不能重复。
primary key主键约束，指定某列的数据不能重复、唯一、非空。
foreign key外键，指定该列记录属于主表中的一条记录，参照另一条数据。

7.3 非空约束

听名字就能理解，被非空约束的列，在插入值时必须非空。

创建表时添加约束

CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name不能为空
);

创建表完后，添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;-- -- 如果表中含有null 则添加失败

删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

7.4 唯一约束

当为字段指定唯一约束后，那么字段的值必须是唯一的。

唯一约束可以有NULL值，但是只能有一条记录为null

在创建表时，添加唯一约束

CREATE TABLE stu(
    id INT,
    phone_number VARCHAR(20) UNIQUE -- 手机号
);

删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

在表创建完后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; ---- 如果表中含有相同数据 则添加失败

7.5 主键约束

主键(PRIMARY KEY)是用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。

主键要求这一行的数据不能有重复且不能为空。

注意
- 含义非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识

在创建表时，添加主键约束

create table stu(
    id int primary key,-- 给id添加主键约束
    name varchar(20)
);

删除主键

ALTER TABLE stu DROP PRIMARY KEY; --注意如果id有null值 则删除失败

创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

7.6 自动增长

MySQL提供了主键自动增长的功能，这样用户就不用再为是否有主键是否重复而烦恼了。

当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。

使用 auto_increment 可以来完成值得自动增长，自动增长的列必须是主键。

创建表时，添加主键约束，并且完成主键自增长

create table stu(
id int primary key auto_increment,--给id添加主键约束和自动增长
name varchar(20)
);

删除自动增长
```
ALTER TABLE stu MODIFY id INT;
```

添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

7.7 外键约束

一个表的一列（a列）指向另一个表的b列（主键）那么这一列（a列）就是外键约束。

比如人和车，把两者保存在一张表里显然是不合理的；

而保存成两张表，则需要想办法表示出二者的关系，即人拥有车，而车属于人。

create table person (
    id int primary key,
    pname varchar(30)  
);

create table car(
    id int primary key,
    cname varchar(30),
    pid int ,
    foreign key(pid) references person(id)
);

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

外键可以为null

创建表时，可以添加外键

create table 表名(
外键列,
-- constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 定义外健名称是为了 后期方便删除外健约束

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- 尝试删除主表 id=1的部门   发现删除失败
DELETE  FROM department WHERE id =1;

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 删除 员工表中 没有对应的部门表信息 否则无法添加外键约束
DELETE FROM employee WHERE id IN(1,2,3)

-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

7.8级联操作

添加级联操作语法

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

ON UPDATE CASCADE 
ON DELETE CASCADE;

2. 分类

1. 级联更新：ON UPDATE CASCADE

2. 级联删除：ON DELETE CASCADE

级联操作慎用，危险，ABCD四张表都是级联的 A一改 BCD都跟着改，但是B却和M表有依赖关系

8. 数据库的设计

8.1 多表之间的关系

一对一(了解)
- 如人和身份证
- 分析一个人只有一个身份证，一个身份证只能对应一个人
一对多(多对一)
- 如部门和员工
- 分析一个部门有多个员工，一个员工只能对应一个部门
多对多
- 如学生和课程
- 分析一个学生可以选择很多门课程，一个课程也可以被很多学生选择

8.2 实现关系

一对多(多对一)
- 如部门和员工
- 一个员工只能选一个部门，一个部门可以被多个员工选择。
- 实现方式在多的一方建立外键，指向一的一方的主键。

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

一对一(了解)
- 如人和身份证
- 分析主体
实现方式一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

联合主键

CREATE TABLE students(
sid int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(5),
age INT
);

CREATE  TABLE course(
cid int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(5)
);

CREATE  TABLE t_students_course(
sid int,
cid int,
PRIMARY KEY(sid,cid),
FOREIGN KEY(sid) REFERENCES students(sid),
FOREIGN KEY(cid) REFERENCES class(cid)
);

INSERT INTO students VALUES(null,'张三',17),(null,'李四',20);
INSERT INTO class VALUES(null,'java'),(null,'前端');-- 

INSERT INTO t_students_class VALUES(1,1);
INSERT INTO t_students_class VALUES(2,2);

SELECT * FROM students;
SELECT * FROM class;
SELECT * FROM t_students_class;

9. 多表查询

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;

9.1 多表查询分类

内连接查询
- 隐式内连接
- 显式内连接
外连接查询
- 左外连接
- 右外连接
子查询

9.2 内连接查询

查询多张表的交集信息。

9.2.1 隐式内连接

使用where条件清除无用数据即可。

sql语句;

-- 内连接查询
-- 隐式内连接   显式内连接

-- 隐式内连接使用where条件清除无用数据。
-- 1.查询所有员工信息和对应部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;-- 提示出来的引号 可加可不加

-- 2.1 查询员工表名称，性别，部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 2.2 简化写法 给表名起别名  t1 是别名
SELECT 
    t1.`name`,
    t1.`gender`,
    t2.`name` 
FROM 
    emp t1,
    dept t2 
WHERE
    t1.`dept_id` = t2.`id`;

9.2.2 显示内连接

语法 select 字段列表 from 表名1 [inner] join 表名2 on 条件
[inner] 可选关键字

显式内连接sql语句

-- 1 查询所有员工信息和对应的部门信息
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
-- 1.1 INNER 可以省略
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.id;
-- 1.2 也可以起别名
SELECT * FROM emp t1 JOIN dept t2 ON t1.`dept_id` = t2.`id`;

内连接查询注意事项

1.从哪些表中查询数据，

2.条件是什么，判断什么是有效数据，什么是无效数据

3.查询哪些字段，我们会选择我们需要的字段来查询

9.3 外连接查询

概念

若干个表中，查询某个表的全部信息的同时在查询另一张表的交集信息。

9.3.1 左外连接

语法

select 字段列表 from 表1 left [outer] join 表2 on 条件；-- 表1是左表，查询的是左表所有数据以及和表2其交集部分。

例如

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.`dept_id` = dept.`id`

[OUTER]关键字可选

SELECT * FROM emp  LEFT      JOIN dept ON emp.`dept_id` = dept.`id`;

9.3.2 右外连接

语法

select 字段列表 from 表1 right [outer] join 表2 on 条件；-- 表2是右表 查询的是右表所有数据以及和表1其交集部分。

例如

SELECT * FROM emp  RIGHT OUTER JOIN dept ON emp.`dept_id` = dept.`id`;-- dept是右表

SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.`dept_id` = dept.`id`;-- emp是右表

9.4 子查询

概念
- 查询中嵌套查询，称嵌套查询为子查询
- 一个select中嵌套另一个select，也属于子查询

子查询基本使用

查询最高工资的员工信息
- 第一步先查询最高工资是多少 9000
```
SELECT MAX(salary) FROM emp;-- 先查询最高工资是多少  9000
```
- 第二步再查询员工信息并且薪资=9000的
```
SELECT * FROM emp WHERE emp.`salary` = 9000;--
```
子查询完成上述操作

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

代码示例

-- 子查询可以作为条件，使用运算符去判断。 运算符 > >= < <= =
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);-- 查询员工工资小于平均工资的人

10. 事务

10.1 事务概述

概念如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。没有事务前会遇到的问题张三和李四各有1000元，张三给李四转500，那么张三应该剩余500，李四剩余1500。但是如果在张三给李四刚转完钱之后，程序出问题了，会导致李四收不到这笔500元的款项造成张三损失了500，不翼而飞了，而李四余额还是1000，不变

-- 举例
CREATE TABLE account (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (10),
  balance DOUBLE
) ;

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

SELECT * FROM account;

-- 张三给李四转500
-- 1.张三账户-500
UPDATE account SET balance = balance -500 WHERE NAME ='zhangsan';

出错啦...

-- 2. 李四账户+500
UPDATE account SET balance = balance + 500 WHERE NAME ='lisi';

UPDATE account SET balance = 1000; -- 数据还原到1000

操作
- 开启事务 start transaction
- 回滚：rollback
- 提交：commit
解决之后的操作

-- 开启事务
START TRANSACTION;
-- 张三给李四转500
-- 1. 张三账户-500
UPDATE account SET balance = balance -500 WHERE NAME ='zhangsan';
-- 出错啦...
-- 2. 李四账户+500
UPDATE account SET balance = balance + 500 WHERE NAME ='lisi';

-- 如果没有问题 那么就提交
COMMIT;
-- 发现问题了  就回滚事务
ROLLBACK;
UPDATE account SET balance = 1000;-- 数据还原到1000
SELECT * FROM account;

10.2 事务提交方式

事务提交的两种方式
- 自动提交
  - MySQL就是自动提交的，你用MySQL写完sql语句执行就自动被提交了
    - 一条DML(增删改)语句会自动提交一次事务。
- 手动提交
  - 修改事务的默认提交方式
    - 查看事务的默认提交方式：SELECT @@autocommit; 1代表自动提交 0代表手动提交
    - 修改默认提交方式 set@@autocommit = 0; 如果你设置了手动提交，那么每次执行完sql语句都需要commit手动提交一次，否则不会持久化存储如果你设置了自动提交，那么每次执行一些sql语句，就不需要手动特意去提交一次。自动会持久化存储

10.3 事务四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间。相互独立。
一致性：事务操作前后，数据总量不变

10.4 事务隔离级别

概念多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题
1. 脏读：一个事务，读取到另一个事务中没有提交的数据。
2. 虚读(不可重复读)：在同一个事务中，两次读取到的数据不一样，A事务读到了B事务提交之后的数据(更新操作)。
3. 幻读：
  1. 事务A在可重复读隔离级别下执行了一个查询操作，得到了一组数据。
  2. 事务B在同样的隔离级别下插入一条数据。
  3. 事务A再次插入一条数据，发现插入失败，出现了幻读现象。
隔离级别
- read uncommitted：读未提交
  - 产生的问题脏读、虚读、幻读
- read committed：读已提交
  - 产生的问题虚读、幻读
- repeatable read：可重复读（MySQL默认）
  - 产生的问题幻读
- serializable：串行化
  - 可以解决所有的问题
    
    当前事务可以将表锁上，其它事务访问不到该表，只有当前事务提交或回滚了，其它事务才可以访问该表。效率极低。
    
    注意隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别
- select @@tx_isolation; -- MySQL5.7之前使用这种方式查询
  - select @@transaction_isolation; -- MySQL5.7以后使用这种方式查询

数据库设置隔离级别

set global transaction isolation level 级别字符串;

set  global transaction isolation level read committed
set  global transaction isolation level repeatable read

事务级别演示

脏读演示
```
set global transaction isolation level read uncommitted;
```
转账操作

start transaction; 两个事务窗口都开启事务
```
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
```
一般不会修改隔离级别，了解即可，我们使用MySQL默认的隔离级别即可。

10.5 数据库的备份和还原

命令行

语法

-- 备份 mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
mysqldump -uroot -proot db02  > C://t3.sql

还原

1. 登录数据库

2. 创建新数据库 create database m2;

3. 使用新数据库 use m2;

4. 执行文件 source 文件路径 (source c://t3.sql;)

图形化工具
- 转储t3.sql文件
- 执行sql文件，前提是事先创建好数据库才可以导入成功

查看全文

http://www.kler.cn/a/308089.html

深度学习和图像处理

【Visual Studio】使用VS调试(Debug)

Linux——GPIO输入输出裸机实验

Ubuntu配置阿里云docker apt源

认识一下Unicorn

边缘的检测

【C++】——string类的模拟实现

【网络】DNS，域名解析系统

Vue Application exit (SharedArrayBuffer is not defined)

数据结构与算法-17高级数据结构_图论（迪杰斯特拉算法）

5分钟熟练上手ES的具体使用

Python数据分析-Steam 收入排名前 1500 的游戏

克隆虚拟机，xshell无法传文件，windows无法ping克隆虚拟机，已解决

idea2024 Safe Mode解决、配置git出现Can‘t run a Git command in the safe mode、取消受信任项目功能

Python | Leetcode Python题解之第409题最长回文串

JDBC与MyBatis：数据库访问技术的变迁【后端 15】

VScode 怎么缩放界面大小

Spring Boot 3项目使用Swagger3教程

Web 安全基础教程：从零基础入门到精通

chatgpt个人版ssrf漏洞

[Unity Demo]从零开始制作空洞骑士第三集之导入插件2D toolkit和使用playmaker制作敌人状态机以及扩展FSM脚本

CSP-J初赛每日题目2

深度学习Day-33：Semi-Supervised GAN理论与实战

【电脑组装】✈️从配置拼装到安装系统组装自己的台式电脑

华为eNSP使用详解

【编程底层原理】亿级数据表查询最后10条记录limit 99999990,10性能为啥特慢，而且数据库都被查宕机了

1. 概述

2. MySQL安装准备

3. SQL概述

3.1 什么是SQL

3.2 SQL通用语法

3.3 SQL分类

4. DDL

4.1 操作数据库

4.1.1 C(Create)创建

4.1.2 R(Retrieve)查询

4.1.3 U(Update):修改

4.1.4 D(Delete):删除

4.1.5 使用数据库

4.2 操作表

4.2.1 C(Create):创建

4.2.2 R(Retrieve)查询

4.2.3 U(Update):修改

4.2.4 D(Delete):删除

5. DML

5.1 添加数据

5.2 删除数据

5.3 修改数据

6. DQL

6.1 语法

6.2 基础查询

6.3 条件查询

6.4 模糊查询

6.5 排序查询

6.6 聚合函数

6.7 分组查询:

6.8 分页查询

7. 约束

7.1 概念

7.2 分类

7.3 非空约束

7.4 唯一约束

7.5 主键约束

7.6 自动增长

7.7 外键约束

7.8级联操作

8. 数据库的设计

8.1 多表之间的关系

8.2 实现关系

9. 多表查询

9.1 多表查询分类

9.2 内连接查询

9.2.1 隐式内连接

9.2.2 显示内连接

9.3 外连接查询

9.3.1 左外连接

9.3.2 右外连接

9.4 子查询

10. 事务

10.1 事务概述

10.2 事务提交方式

10.3 事务四大特征

10.4 事务隔离级别

10.5 数据库的备份和还原

相关文章：