• Mysql高级

    一、约束🍓

    (一)、概念🥝

    1) 约束的作用: 🍎

    对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中

    主键: 查询 唯一确定一条数据 编号 身份证号

    2) 常见的约束🍎

    在这里插入图片描述

    (二)、分类🥝

    1)主键约束🍎

    id1
    不可重复唯一非空，用来表示数据库中的每一条记录
    语法格式
    字段名 字段类型 primary key

    需求: 创建一个带主键的表

    # 方式1 创建一个带主键的表 
CREATE TABLE emp2(
	-- 设置主键 唯一 非空 
	eid INT PRIMARY KEY,
	ename VARCHAR(20), 
	sex CHAR(1)
);
-- 删除表
DROP TABLE emp2;

-- 方式2 创建一个带主键的表 
CREATE TABLE emp2(
	eid INT ,
	ename VARCHAR(20), 
	sex CHAR(1),
	-- 指定主键为 eid字段 
	PRIMARY KEY(eid)
);

-- 方式3 创建一个带主键的表 
CREATE TABLE emp2(
    eid INT ,
    ename VARCHAR(20),
    sex CHAR(1)
)

-- 创建的时候不指定主键,然后通过 DDL语句进行设置 
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

    • 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

    DESC 查看表结构

    -- 查看表的详细信息 
DESC emp2;


    • 1
    • 2
    • 3

    测试主键的唯一性 非空性

    # 正常插入一条数据
INSERT INTO emp2 VALUES(1,'宋江','男');

# 插入一条数据,主键为空
-- Column 'eid' cannot be null 主键不能为空 
INSERT INTO emp2 VALUES(NULL,'李逵','男');

# 插入一条数据,主键为 1
-- Duplicate entry '1' for key 'PRIMARY' 主键不能重复 
INSERT INTO emp2 VALUES(1,'孙二娘','女');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    哪些字段可以作为主键 ?

    • 通常针对业务去设计主键,每张表都设计一个主键id
    • 主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复 就好,比如 身份证就可以作为主键.

    删除主键约束
    删除表中的主键约束 (了解)

    -- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY; 
DESC emp2;


    • 1
    • 2
    • 3
    • 4

    主键的自增
    注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值.

    关键字:
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

    • 1
    • 2

    1.创建主键自增的表

    -- 创建主键自增的表 
CREATE TABLE emp2(
	-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型 
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	sex CHAR(1)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.添加数据 观察主键的自增

    INSERT INTO emp2(ename,sex) VALUES('张三','男'); 
INSERT INTO emp2(ename,sex) VALUES('李四','男'); 
INSERT INTO emp2 VALUES(NULL,'翠花','女'); 
INSERT INTO emp2 VALUES(NULL,'艳秋','女');



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    修改主键自增的起始值

    默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

    -- 创建主键自增的表,自定义自增其实值 
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
)AUTO_INCREMENT=100;

-- 插入数据,观察主键的起始值
INSERT INTO emp2(ename,sex) VALUES('张百万','男'); 
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');



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

    DELETE和TRUNCATE对自增长的影响

    删除表中所有数据有两种方式
    在这里插入图片描述

    测试1: delete 删除表中所有数据

    -- 目前最后的主键值是 101 
SELECT * FROM emp2;
-- delete 删除表中数据,对自增没有影响 
DELETE FROM emp2;
-- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES('张百万','男'); 
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    测试2: truncate删除 表中数据

    -- 使用 truncate 删除表中所有数据, 
TRUNCATE TABLE emp2;
-- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES('张百万','男'); 
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    2) 非空约束🍎

    • 非空约束的特点: 某一列不允许为空
      语法格式
    字段名 字段类型 not null

    • 1

    需求1: 为 ename 字段添加非空约束

    # 非空约束
CREATE TABLE emp2(
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	-- 添加非空约束, ename字段不能为空 
	ename VARCHAR(20) NOT NULL,
	sex CHAR(1)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    3) 唯一约束🍎

    • 唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )
      语法格式
    字段名 字段类型 unique

    • 1

    1.添加唯一约束

    #创建emp3表 为ename 字段添加唯一约束 
CREATE TABLE emp3(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20) UNIQUE,
    sex CHAR(1)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    2.测试唯一约束

    -- 测试唯一约束 添加一条数据
INSERT INTO emp3 (ename,sex) VALUES('张百万','男');

-- 添加一条 ename重复的 数据
-- Duplicate entry '张百万' for key 'ename' ename不能重复 
INSERT INTO emp3 (ename,sex) VALUES('张百万','女');


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    主键约束与唯一约束的区别:

    1. 主键约束 唯一且不能够为空
    2. 唯一约束,唯一 但是可以为空
    3. 一个表中只能有一个主键 , 但是可以有多个唯一约束

    4) 默认约束🍎

    默认值约束 用来指定某列的默认值
    语法格式

    字段名 字段类型 DEFAULT 默认值 

    • 1
    1. 创建emp4表
    -- 创建带有默认值的表 
CREATE TABLE emp4(
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	-- 为ename 字段添加默认值
	ename VARCHAR(20) DEFAULT '奥利给', 
	sex CHAR(1)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    1. 测试添加数据使用默认值
    -- 添加数据 使用默认值
INSERT INTO emp4(ename,sex) VALUES(DEFAULT,'男'); 
INSERT INTO emp4(sex) VALUES('女');

-- 不使用默认值
INSERT INTO emp4(ename,sex) VALUES('艳秋','女');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    5) 外键约束🍎

    FOREIGN KEY 表示外键约束，
    外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

    -- 创建表时添加外键约束 
CREATE TABLE 表名( 列名 数据类型, … [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) );

    • 1
    • 2
    -- 建完表后添加外键约束 
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);


    • 1
    • 2
    • 3

    删除外键约束

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

    • 1

    练习

    
-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    根据上述语法创建员工表和部门表，并添加上外键约束
    添加数据

    -- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','郑州'),('销售部', '北京');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

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

    此时删除 研发部 这条数据，会发现无法删除。
    删除外键

    alter table emp drop FOREIGN key fk_emp_dept;

    • 1

    重新添加外键

    alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);


    • 1
    • 2

    练习
    根据需求，为表添加合适的约束

     -- 员工表
 CREATE TABLE emp (
     id INT,  -- 员工id，主键且自增长
     ename VARCHAR(50), -- 员工姓名，非空且唯一
     joindate DATE,  -- 入职日期，非空
     salary DOUBLE(7,2),  -- 工资，非空
     bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
 );

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    上面一定给出了具体的要求，我们可以根据要求创建这张表，并为每一列添加对应的约束。建表语句如下：

     DROP TABLE IF EXISTS emp;
 ​
 -- 员工表
 CREATE TABLE emp (
   id INT PRIMARY KEY, -- 员工id，主键且自增长
   ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
   joindate DATE NOT NULL , -- 入职日期，非空
   salary DOUBLE(7,2) NOT NULL , -- 工资，非空
   bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
 );

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    通过上面语句可以创建带有约束的 emp 表，约束能不能发挥作用呢。接下来我们一一进行验证，先添加一条没有问题的数据

     INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);


    • 1
    • 2
    • 验证主键约束，非空且唯一
     INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

    • 1

    会报错

    column 'id' cannot be null

    • 1

    从上面的结果可以看到，字段 id 不能为null。那我们重新添加一条数据，如下：

     INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

    • 1

    会报错：

    duplicate entry '1' for key 'PRIMARY'

    • 1

    1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据

     INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

    • 1
    • 验证非空约束
     INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

    • 1

    从上面结果可以看到，ename 字段的非空约束生效了。

    • 验证唯一约束
    INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

    • 1

    执行结果如下：

    Duplicate entry '李四' for key 'ename'

    • 1

    从上面结果可以看到，ename 字段的唯一约束生效了。

    • 验证默认约束
    INSERT INTO emp(id,ename,joindate,salary) values (3,'王五','1999-11-11',8800);

    • 1

    执行完上面语句后查询表中数据，可以看到王五这条数据的bonus列就有了默认值0。

    注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。
    如下：

     INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);

    • 1

    执行完上面语句后查询表中数据，可以看到赵六这条数据的bonus列的值是null。

    • 验证自动增长： auto_increment 当列是数字类型 并且唯一约束
      重新创建 emp 表，并给id列添加自动增长
     -- 员工表
 CREATE TABLE emp (
   id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
   ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
   joindate DATE NOT NULL , -- 入职日期，非空
   salary DOUBLE(7,2) NOT NULL , -- 工资，非空
   bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
 );

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    接下来给emp添加数据，分别验证不给id列添加值以及给id列添加null值，id列的值会不会自动增长：

     INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
 INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
 INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

    • 1
    • 2
    • 3

    二、数据库设计🍓

    (一)、数据库设计简介🥝

    • 数据库设计概念
      • 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
      • 建立数据库中的表结构以及表与表之间的关联关系的过程。
      • 有哪些表？表里有哪些字段？表和表之间有什么关系？
    • 数据库设计的步骤

      • 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）

      • 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

      • ER图（entity relationship diagram) 实体关系图 ：提供了实体类型、属性和关系的方法，用来描述现实世界的概念模型

      • 如下图就是ER(Entity/Relation)图：

      • 实体用矩形表示，属性用椭圆表示，主键学号需要加下划线

      • 在这里插入图片描述

      • 绘图软件:Office visio,word,亿图图示，Enterprise Architect(EA),在线网站 processon

    • 表关系
      • 一对一 一夫一妻制
        • 如：用户（用户名字 密码 ） 和 用户详情 ()
        • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
      • 一对多
        • 如：部门 和 员工
        • 一个部门对应多个员工，一个员工对应一个部门。
      • 多对多
        • 如：商品 和 订单
        • 一个商品对应多个订单，一个订单包含多个商品。

    (二)、表关系(一对多)🥝

    • 一对多
      • 如：部门 和 员工
      • 一个部门对应多个员工，一个员工对应一个部门。
        实现方式
    • 在多的一方建立外键，指向一的一方的主键
      案例
      我们还是以 员工表 和 部门表 举例:
      在这里插入图片描述

    经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）：
    在这里插入图片描述

    建表语句如下：

    • 查看表结构模型图：
    -- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    (三)、表关系(多对多)🥝

    • 多对多
      • 如：商品 和 订单
      • 一个商品对应多个订单，一个订单包含多个商品
        实现方式
    • 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

    案例
    我们以 订单表 和 商品表 举例：

    经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键：

    建表语句如下：

     -- 删除表 
DROP TABLE IF EXISTS tb_order_goods; 
DROP TABLE IF EXISTS tb_order; 
DROP TABLE IF EXISTS tb_goods; ​ 
-- 订单表 
CREATE TABLE tb_order(     
id int primary key auto_increment,     
payment double(10,2),     
payment_type TINYINT,     
status TINYINT ); ​ 
-- 商品表
CREATE TABLE tb_goods(     
id int primary key auto_increment,     
title varchar(100),    
 price double(10,2) ); ​
-- 订单商品中间表 
CREATE TABLE tb_order_goods(     
id int primary key auto_increment,     
order_id int,     
goods_id int,     
count int ); ​ 
-- 建完表后，添加外键 
alter table tb_order_goods add 
CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id); 
alter table tb_order_goods add 
CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

    • 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

    (四)、 表关系(一对一)🥝

    • 一对一
      • 如：用户 和 用户详情
      • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
        实现方式
    • 在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

    案例
    我们以 用户表 举例：

    • 而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。

      建表语句如下：

    create table tb_user_desc (
     id int primary key auto_increment,
     city varchar(20),
     edu varchar(10),
     income int,
     status char(2),
     des varchar(100)
 );
 ​
 create table tb_user (
     id int primary key auto_increment,
     photo varchar(100),
     nickname varchar(50),
     age int,
     gender char(1),
     desc_id int unique,
     -- 添加外键
     CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)    
 );

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    三、多表查询🍓

    DQL: 查询多张表,获取到需要的数据
    比如 我们要查询家电分类下 都有哪些商品,那么我们就需要查询分类与商品这两张表

    1.创建db3_2 数据库

    -- 创建 db3_2 数据库,指定编码
CREATE DATABASE db3_2 CHARACTER SET utf8;
# 使用数据库
use db3_2;

    • 1
    • 2
    • 3
    • 4

    2)创建分类表与商品表

    #分类表 (一方 主表) 
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);

#商品表 (多方 从表) 
CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY ,
	pname VARCHAR(50),
	price INT,
	flag VARCHAR(2), #是否上架标记为:1表示上架、0表示下架 
	category_id VARCHAR(32),
	-- 添加外键约束
  	FOREIGN KEY (category_id) REFERENCES category (cid)
);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    3)插入数据

    #分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电'); 
INSERT INTO category(cid,cname) VALUES('c002','鞋服'); 
INSERT INTO category(cid,cname) VALUES('c003','化妆品'); 
INSERT INTO category(cid,cname) VALUES('c004','汽车');

#商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视 机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰箱',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋衣',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大宝',200,'1','c003');



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    (一)、笛卡尔积🥝

    交叉连接查询,因为会产生笛卡尔积,所以 基本不会使用

    语法格式

    SELECT 字段名 FROM 表1, 表2;

    • 1

    使用交叉连接查询 商品表与分类表

    SELECT * FROM category , products;

    • 1

    (二)、 内连接查询🥝

    通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示

    隐式内连接
    from子句后面直接写多个表名使用where指定连接条件的 这种连接方式是 隐式内连接.使用where条件过滤无用的数据

    语法格式

    SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;


    • 1
    • 2

    查询所有商品信息和对应的分类信息

    # 隐式内连接
SELECT * FROM products,category WHERE category_id = cid;

    • 1
    • 2

    查询商品表的商品名称 和 价格,以及商品的分类信息
    可以通过给表起别名的方式, 方便我们的查询(有提示)

    SELECT
    p.`pname`,
    p.`price`,
    c.`cname`
FROM products p , category c WHERE p.`category_id` = c.`cid`;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    查询 格力空调是属于哪一分类下的商品

    #查询 格力空调是属于哪一分类下的商品
SELECT p.`pname`,c.`cname` 
FROM products p , category c 
WHERE p.`category_id` = c.`cid` AND p.`pid` = 'p002';


    • 1
    • 2
    • 3
    • 4
    • 5

    显式内连接

    使用 inner join …on 这种方式, 就是显式内连接

    语法格式

    SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件 
-- inner 可以省略

    • 1
    • 2

    查询所有商品信息和对应的分类信息

    # 显式内连接查询
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;


    • 1
    • 2
    • 3

    查询鞋服分类下,价格大于500的商品名称和价格

    # 查询鞋服分类下,价格大于500的商品名称和价格
-- 我们需要确定的几件事
-- 1.查询几张表 products & category
-- 2.表的连接条件 从表.外键 = 主表的主键
-- 3.查询的条件 cname = '鞋服' and price > 500 -- 4.要查询的字段 pname price
SELECT
	p.pname,
	p.price
FROM products p 
INNER JOIN category c ON p.category_id = c.cid 
WHERE p.price > 500 AND cname = '鞋服';


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

    (三)、 外连接查询🥝

    左外连接

    • 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
    • 左外连接的特点
      • 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
      • 如果匹配不到, 左表中的数据正常展示, 右边的展示为null.

    语法格式

    SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件


    • 1
    • 2
    -- 左外连接查询
SELECT * 
FROM category c 
LEFT JOIN products p ON c.`cid`= p.`category_id`;


    • 1
    • 2
    • 3
    • 4
    • 5

    左外连接, 查询每个分类下的商品个数

    # 查询每个分类下的商品个数 
/*
	1.连接条件: 主表.主键 = 从表.外键 
	2.查询条件: 每个分类 需要分组 
	3.要查询的字段: 分类名称, 分类下商品个数
*/
SELECT
	c.`cname` AS '分类名称', 
	COUNT(p.`pid`) AS '商品个数'
FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;


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

    右外连接

    • 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
    • 右外连接的特点
      • 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
      • 如果匹配不到，右表中的数据正常展示, 左边展示为null

    语法格式

    SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

    • 1
    -- 右外连接查询
SELECT * 
FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

    • 1
    • 2
    • 3

    在这里插入图片描述

    (四)、 子查询🥝

    • 子查询概念
      • 一条select 查询语句的结果, 作为另一条 select 语句的一部分
    • 子查询的特点
      • 子查询必须放在小括号中
      • 子查询一般作为父查询的查询条件使用
    • 子查询常见分类
      • where型 子查询: 将子查询的结果, 作为父查询的比较条件
      • from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
      • exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
        子查询的结果作为查询条件
        语法格式
    SELECT 查询字段 FROM 表 WHERE 字段 = (子查询);

    • 1

    1.通过子查询的方式, 查询价格最高的商品信息

    # 通过子查询的方式, 查询价格最高的商品信息 
-- 1.先查询出最高价格
SELECT MAX(price) FROM products;

-- 2.将最高价格作为条件,获取商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.查询化妆品分类下的 商品名称 商品价格

    #查询化妆品分类下的 商品名称 商品价格 
-- 先查出化妆品分类的 id
SELECT cid FROM category WHERE cname = '化妆品';

-- 根据分类id ,去商品表中查询对应的商品信息 
SELECT
	p.`pname`,
    p.`price`
FROM products p
WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');


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

    3.查询小于平均价格的商品信息

    -- 1.查询平均价格
SELECT AVG(price) FROM products; -- 1866

-- 2.查询小于平均价格的商品
SELECT * FROM products
WHERE price < (SELECT AVG(price) FROM products);



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    子查询的结果作为一张表

    语法格式

    SELECT 查询字段 FROM (子查询) 表别名  WHERE 条件;

    • 1

    查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称

    -- 1. 先查询分类表的数据 
SELECT * FROM category;

-- 2.将上面的查询语句 作为一张表使用 
SELECT
    p.`pname`,
    p.`price`,
    c.cname
FROM products p
-- 子查询作为一张表使用时 要起别名 才能访问表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;

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

    注意: 当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。

    子查询结果是单列多行
    子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
    语法格式

    SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

    • 1

    查询价格小于两千的商品,来自于哪些分类(名称)

    # 查询价格小于两千的商品,来自于哪些分类(名称) 
-- 先查询价格小于2000 的商品的,分类ID
SELECT DISTINCT 
	category_id 
FROM products 
WHERE price < 2000;

-- 在根据分类的id信息,查询分类名称
-- 报错: Subquery returns more than 1 row
-- 子查询的结果 大于一行
SELECT * FROM category
WHERE cid = (SELECT DISTINCT category_id 
FROM products WHERE price < 2000);

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

    使用in函数, in( c002, c003 )

    -- 子查询获取的是单列多行数据
SELECT * FROM category
WHERE 
	cid IN 
(SELECT DISTINCT category_id FROM products WHERE price < 2000);


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    查询家电类 与 鞋服类下面的全部商品信息

    # 查询家电类 与 鞋服类下面的全部商品信息
-- 先查询出家电与鞋服类的 分类ID
SELECT cid FROM category WHERE cname IN ('家电','鞋服');

-- 根据cid 查询分类下的商品信息
SELECT * FROM products
WHERE 
	category_id IN 
(SELECT cid FROM category WHERE cname IN ('家电','鞋服'));

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    子查询总结

    1. 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
    2. 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).

    练习题

    • 环境准备：
     DROP TABLE IF EXISTS emp;
 DROP TABLE IF EXISTS dept;
 DROP TABLE IF EXISTS job;
 DROP TABLE IF EXISTS salarygrade;
 ​
 -- 部门表
 CREATE TABLE dept (
   did INT PRIMARY KEY, -- 部门id
   dname VARCHAR(50), -- 部门名称
   loc VARCHAR(50) -- 部门所在地
 );
 ​
 -- 职务表，职务名称，职务描述
 CREATE TABLE job (
   id INT PRIMARY KEY,
   jname VARCHAR(20),
   description VARCHAR(50)
 );
 ​
 -- 员工表
 CREATE TABLE emp (
   id INT PRIMARY KEY, -- 员工id
   ename VARCHAR(50), -- 员工姓名
   job_id INT, -- 职务id
   mgr INT , -- 上级领导
   joindate DATE, -- 入职日期
   salary DECIMAL(7,2), -- 工资
   bonus DECIMAL(7,2), -- 奖金
   dept_id INT, -- 所在部门编号
   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (did)
 );
 -- 工资等级表
 CREATE TABLE salarygrade (
   grade INT PRIMARY KEY,   -- 级别
   losalary INT,  -- 最低工资
   hisalary INT -- 最高工资
 );
                 
 -- 添加4个部门
 INSERT INTO dept(did,dname,loc) VALUES 
 (10,'教研部','北京'),
 (20,'学工部','上海'),
 (30,'销售部','广州'),
 (40,'财务部','深圳');
 ​
 -- 添加4个职务
 INSERT INTO job (id, jname, description) VALUES
 (1, '董事长', '管理整个公司，接单'),
 (2, '经理', '管理部门员工'),
 (3, '销售员', '向客人推销产品'),
 (4, '文员', '使用办公软件');
 ​
 ​
 -- 添加员工
 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
 (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
 (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
 (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
 (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
 (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
 (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
 (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
 (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
 (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
 (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
 (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
 (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
 (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
 (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
 ​
 ​
 -- 添加5个工资等级
 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
 (1,7000,12000),
 (2,12010,14000),
 (3,14010,20000),
 (4,20010,30000),
 (5,30010,99990);

    • 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
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 需求
      查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
     /*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
*/
-- 方式一 ：隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp,
	job
WHERE
	emp.job_id = job.id;

-- 方式二 ：显式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp
INNER JOIN job ON emp.job_id = job.id;

    • 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

    查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

    /*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/

-- 方式一 ：隐式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM
	emp,
	job,
	dept
WHERE
	emp.job_id = job.id
	and dept.did = emp.dept_id
;

-- 方式二 ：显式内连接
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.did = emp.dept_id

    • 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

    查询员工姓名，工资，工资等级

     /*
	分析：
		1. 员工姓名，工资 信息在emp 员工表中
		2. 工资等级 信息在 salarygrade 工资等级表中
		3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
	emp.ename,
	emp.salary,
	t2.*
FROM
	emp,
	salarygrade t2
WHERE
	emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary
查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
		6. 工资等级 信息在 salarygrade 工资等级表中
		7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	t2.grade
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

    • 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
    • 42

    查询出部门编号、部门名称、部门位置、部门人数

    /*
	分析：
		1. 部门编号、部门名称、部门位置 来自于部门 dept 表
		2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
		3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;

SELECT
	dept.id,
	dept.dname,
	dept.loc,
	t1.count
FROM
	dept,
	(
		SELECT
			dept_id,
			count(*) count
		FROM
			emp
		GROUP BY
			dept_id
	) t1
WHERE
	dept.id = t1.dept_id

    • 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

    四、事务🍓

    (一)、 概述🥝

    数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。
    事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。
    事务是一个不可分割的工作逻辑单元。

    这些概念不好理解，接下来举例说明

    张三和李四账户中各有1000块钱，现李四需要转换500块钱给张三，具体的转账操作为

    • 第一步：查询李四账户余额
    • 第二步：从李四账户金额 -500
    • 第三步：给张三账户金额 +500

    现在假设在转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了500，而张三金额并没有多500；这样的系统是有问题的。如果解决呢？使用事务可以解决上述问题

    (二)、 语法🥝

    开启事务

     START TRANSACTION; 或者   BEGIN;

    • 1

    提交事务

     commit;

    • 1

    回滚事务

     rollback;

    • 1

    (三)、 模拟转转操作🥝

    环境准备

    -- 创建账户表
CREATE TABLE account(
	-- 主键
	id INT PRIMARY KEY AUTO_INCREMENT, 
	-- 姓名
	NAME VARCHAR(10),
	-- 余额
	money DOUBLE
);
-- 添加两个用户
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);

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

    模拟tom 给 jack 转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句:

    -- tom账户 -500元
UPDATE account SET money = money - 500 WHERE NAME = 'tom';

-- jack账户 + 500元
UPDATE account SET money = money + 500 WHERE NAME = 'jack';


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    假设当tom 账号上 -500 元,服务器崩溃了。jack 的账号并没有+500 元，数据就出现问题了。 我们要保证整个事务执行的完整性,要么都成功, 要么都失败. 这个时候我们就要学习如何操作事务.

    (四)、 Mysql事务操作🥝

    • MYSQL 中可以有两种方式进行事务的操作:
      • 手动提交事务
      • 自动提交事务

    手动提交事务
    在这里插入图片描述

    START TRANSACTION
--这个语句显式地标记一个事务的起始点。

COMMIT
--表示提交事务，即提交事务的所有操作，具体地说，
--就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。

ROLLBACK
--表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，
--系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态    

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    手动提交事务流程

    • 执行成功的情况: 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
    • 执行失败的情况: 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

    案例演示
    模拟张三给李四转 500 元钱

    1. 开启事务
    start transaction;

    • 1
    1. tom账户 -500
    update account set money = money - 500 where name = 'tom'

    • 1
    1. jack账户 +500
    update account set money = money + 500 where name = 'jack';


    • 1
    • 2

    注：由于未提交事务，此时数据并未变化
    4) 在控制台执行 commit 提交事务

    commit;


    • 1
    • 2

    以上操作为提交事务后，才会真正执行，中间有异常也不会对数据有影响
    如是自动事务，每次update数据都会真实改变

    查看autocommit状态

    SHOW VARIABLES LIKE 'autocommit';
-- on :自动提交
-- off : 手动提交
SELECT @@autocommit;


    • 1
    • 2
    • 3
    • 4
    • 5

    把 autocommit 改成 off;

    SET @@autocommit=off;
-- 1表示自动 0 表示手动
set @@autocommit = 0; 

    • 1
    • 2
    • 3
    -- 修改数据
update account set money = money - 500 where name = 'jack';
-- 手动提交 
commit;

    • 1
    • 2
    • 3
    • 4

    (五)、 事务的四大特征🥝

    • 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
    • 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
    • 隔离性（Isolation） :多个事务之间，操作的可见性
    • 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

    (六)、 事务的隔离级别（了解）🥝

    MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。

    事务并发执行遇到的问题

    • 脏读(Dirty Read)
      如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读
      在这里插入图片描述

    如上图，Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务再去查询这条number为1的记录，如果读到列name的值为’关羽’，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

    • 不可重复读（ Non-Repeatable Read）
      如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读

    在这里插入图片描述

    如上图，我们在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了number列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。

    • 幻读（Phantom）

    如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读

    在这里插入图片描述

    如上图，Session A中的事务先根据条件number > 0这个条件查询表hero，得到了name列值为’刘备’的记录；之后Session B中提交了一个隐式事务，该事务向表hero中插入了一条新记录；之后Session A中的事务再根据相同的条件number > 0查询表hero，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。

    有的同学会有疑问，那如果Session B中是删除了一些符合number > 0的记录而不是插入新记录，那Session A中之后再根据number > 0的条件读取的记录变少了，这种现象算不算幻读呢？明确说一下，这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

    tips:

    那对于先前已经读到的记录，之后又读取不到这种情况，算啥呢？其实这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录

    SQL标准中的四种隔离级别

    根据以上严重程度，做一个排序

    脏读 > 不可重复读 > 幻读

    根据以上问题 ，SQL设置了隔离级别

    READ UNCOMMITTED：未提交读。
    READ COMMITTED：已提交读。 – mysql
    REPEATABLE READ：可重复读。 – oracle
    SERIALIZABLE：可串行化。

    存在的问题
    在这里插入图片描述

    也就是说

    READ UNCOMMITTED 隔离级别下，可能发生脏读、不可重复读和幻读问题。
    READ COMMITTED 隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
    REPEATABLE READ 隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
    SERIALIZABLE隔离级别下，各种问题都不可以发生。

    五、数据库设计三大范式🍓

    1. 第一范式（1NF）: 确保每一列的原子性（做到每列不可拆分）
    2. 第二范式（2NF）:在第一范式的基础上，每一行必须可以唯一的被区分，因此需要为表加上主键列
    3. 第三范式（3NF）:在第二范式的基础上，一个表中不包含已在其他表中包含的非主关键字信息（外键）

    反范式:
    有时候为了兼顾效率，可以不遵循范式，设计冗余字段，如订单（总价）和订单项（单价）

    六、数据库的备份和还原🍓

    备份

    mysqldump -u用户名 -p密码 数据库名 > 保存的路径
mysqldump -uroot -p123456 db1 > d://a.sql

    • 1
    • 2

    还原

    drop database db1;
create database db1;
use db1;
source d://a.sql

    • 1
    • 2
    • 3
    • 4

    七、MySQL存储引擎🍓

    什么是存储引擎：

    在这里插入图片描述

    sql ----> 解析器 ------>优化器 ----->存储引擎

    数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

    查看当前mysql默认引擎:

     show variables like '%engine%';

    • 1

    查看mysql支持哪些引擎：

     show engines;

    • 1

    修改默认存储引擎
    如果修改本次会话的默认存储引擎(重启后失效)，只对本会话有效，其他会话无效：

     set default_storage_engine=innodb;

    • 1

    修改全局会话默认存储引擎(重启后失效)，对所有会话有效

     set global default_storage_engine=innodb;

    • 1

    InnoDB🥝

    InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。

    InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

    优点:

    • 更新密集的表。 InnoDB存储引擎特别适合处理多重并发的更新请求。
    • 事务。 InnoDB存储引擎是支持事务的标准MySQL存储引擎。
    • 自动灾难恢复。 与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
    • 外键约束。 MySQL支持外键的存储引擎只有InnoDB。
    • 支持自动增加列AUTO_INCREMENT属性。
    • 从5.5开始innodb存储引擎成为默认的存储引擎。

    一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

    InnoDB是一个健壮的事务型存储引擎,这种存储引擎已经被很多互联网公司使用,为用户操作非常大的数据存储提供了一个强大的解决方案。
    InnoDB还引入了行级锁定和外键约東,在以下场合下,使用 InnoDB是最理想的选择

    优点

    • Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别
    • 支持多版本并发控制的行级锁,由于锁粒度小,写操作和更新操作并发高
    • 速度快
    • 支持自增长列。
    • 支持外键
    • 适合于大容量数据库系统,
    • 支持自动灾难恢复

    缺点

    • 它没有保存表的行数 SELECT COUNT(*) FROM TABLE 时需要扫描全表

    应用场景

    • 当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用 Innodb引擎会提升效率。更新密集的表, InnoDB.存储引擎特别适合处理多重并发的更新请求

    MyISAM🥝

    MyISam引擎不支持事务、也不支持外键,优势是访问速度快,对事务完整性没有要求或者以 select, Insert为主的应用基本上可以用这个引擎来创建表·

    优点

    • MyISAM表是独立于操作系统的,这说明可以轻松地将其从 windows服务器移植到Liux服务器
    • MyISAM存储引擎在查询大量数据时非常迅速,这是它最突出的优点
    • 另行大批量插入操作时执行速度也比较快

    缺点
    间隙锁
    行锁
    表锁

    • MyISAM表没有提供对数据库事务的支持。
    • 不支持行级锁和外键。
    • 不适合用于经常 UPDATE(更新)的表

    应用场景

    • 以读为主的业务,例如:图片信息数据库,博客数据库,商品库等业务
    • 对数据一致性要求不是非常高的业务(不支持事务)
    • 硬件资源比较差的机器可以用 MyISAM(占用资源少)

    MEMORY🥝

    MEMORY的特点是将表中的数据放在内存中,适用于存储临时数据的临时表和数据仓库中的纬度表

    优点

    • memory类型的表访问非常的快,因为它的数据是放在内存中的

    缺点

    • 一旦服务关闭,表中的数据就会丢失掉
    • 只支持表锁,并发性能差,不支持TEXT和BLOB列类型,存储 varchar时是按照char的方式

    应用场景

    • 目标数据较小,而且被非常频繁地访问。
    • 如果数据是临时的,而且要求必须立即可用,那么就可以存放在内存表中。
    • 存储在 Memory表中的数据如果突然丢失,不会对应用服务产生实质的负面影响

    存储引擎的选择
    不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：
    在这里插入图片描述

    • 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
    • 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
    • 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
    • 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

    使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

    Mysql索引数据结构 B-树和B+树🥝

    索引类型:

    • 哈希索引
      • 只能做等值比较，不支持范围查找
      • 无法利用索引完成排序
      • 如果存在大量重复键值的情况下，哈希索引效率会比较低，可能存在哈希碰撞
    • B-树/B+树
      • B 代表 balance 平衡的意思，是一种多路自平衡的搜索树
      • InnoDB 引擎 默认使用B+树，Memory默认使用 B树
      • B树所有节点都储存数据，B+树只在叶子节点储存，为了降低树的高度，每一层可以保存多个节点（>2个）
      • 为了支持范围查询，B+树在叶子节点之间加上了双向链表，减少IO次数

    在这里插入图片描述

  • 相关阅读:
    . NET Core Razor动态菜单实现
    【html5期末大作业】基于HTML+CSS+JavaScript管理系统页面模板
    ios 短信验证码自动填充时总是被复制两遍
    Github访问加速
    java毕业设计电影评论网站系统（附源码、数据库）
    IDEA 的 debug 怎么实现？出于这个好奇心，我越挖越深
    Spring：怨种的我和这玩意死磕到半夜12点20的这件事？
    启程，2022亚马逊云科技re:Invent Peter带来主题演讲
    闭包的优缺点
    MySQL的主从复制与读写分离详解
  • 原文地址：https://blog.csdn.net/2301_77444674/article/details/133375774