Mysql数据库

常用语句

概念：客户端CMD链接服务器，操作mysql服务器里的很多数据库里的数据

SQL语句

SQL:

structured query language 结构化查询语言，操作任意一个DBMS,

1.1: DDL data defination language 数据定义语言

      -- 创建/删除库。表    CRETAE/DROP

                --ALRER

                --TRUNCATE

1.2: DML 数据操作语言（更新操作）

                -- INSERT

               -- DELETE

                --UPDATE

1.3: DQL 数据查询语句

                --SELECT

1.4: DCL  数据控制语言

                --GRANT

                --事务管理

                --BEGIN  COMMIT  ROLLBACK

接下来让我们看一下DDL

DDL

DDL.操作库

-- 1.创建新的数据库

-- 语法:

CREATE DATABASE [IF NOT EXISTS] 数据库名称;

CREATE DATABASE 数据库名称; -- 数据库名称一旦创建  无法更改

-- mysql> CREATE DATABASE mydb;

mysql> SHOW CREATE DATABASE mydb;  -- 查询创建库的完整信息

-- 编码格式: utf8mb4   存储中文

-- 2.删除指定数据库(所有的表+全部删除)

mysql> DROP DATABASE mydb; 

-- 3.修改指定数据库的编码格式

-- mysql> ALTER DATABASE mydb  CHARACTER SET utf8mb4;

操作表

-- 1.创建新的表

-- 1.1 表名规范(等价于程序类名)  表名全部小写，有多个单词， _

-- t_user  tb_user  user  sys_user  sys_role

-- 列名1: 自定义、

-- MySQL服务器数据类型

CREATE TABLE [IF NOT EXISTS] 表名(

  列名1 数据类型 [约束],

  列名2 数据类型 [约束],

  ....

  列名n 数据类型 [约束]  

);

-- 需求: 使用DDL语句  创建student表  指定特定列以及列对应的数据类型

-- 规则: 必不可少的3个字段   id  create_time  update_time

CREATE TABLE student(

   id INT,

   stu_name VARCHAR(20),

   gender TINYINT(1),

   age  TINYINT(2) unsigned,

   score FLOAT(4,1),

   stu_image VARCHAR(200),

   create_time  DATETIME,

   update_time  DATETIME

);

-- 2.删除指定表

mysql> DROP TABLE a;

-- 删除表  表的数据也会被删除 无法回滚

-- 3.修改表结构  ALTER
-- 3.1 新增新的列
ALTER TABLE 表名 ADD 新的列名 数据类型 [约束];
mysql> ALTER TABLE student ADD money DECIMAL(4,1);
-- 3.2 删除指定的列
ALTER TABLE 表名 DROP 列名;
mysql> ALTER TABLE student DROP MONEY;
-- 3.3 修改列的数据类型
mysql> ALTER TABLE student MODIFY gender char(1);
mysql> ALTER TABLE student CHANGE gender gender varchar(2);
-- 3.4 修改列的名称
mysql> ALTER TABLE student CHANGE gender stu_gender char(2);

-- 3.5 修改表名
mysql> ALTER TABLE student RENAME tb_student;

7. 数据类型

-- java程序里面提供的数据类型
-- 1.1 基本数据类型
--   四类八种
-- 整数类型 byte short int long
-- 浮点类型 float  double  -----> java.math.BigDecimal
-- 字符类型 char
-- 布尔类型 boolean
-- 1.2 引用数据类型

MySQL服务器常用的数据类型:

7.1 整数类型

维护任意一个整数数据。

-- n: 限定查询时候的列宽。随意编写  int(5) 1 100 10000000
-- 1. tinyint(n)  "byte/Byte" 
--    tinyint(1) 列的数据  0  1  “boolean”
-- 2. int(n)   "int/Integer"   
-- 3. bigint(n) "long/Long" 

-- 要求数据不能为负数： mysql里面提供的属性  unsigned 
tinyint(n) unsigned  0-255

-- 场景:
-- tinyint(n)  年龄 
-- int(n) 年龄  库存  id
-- bigint(n)  库存  id  时间的毫秒数  

7.2 浮点类型

- 1. float(m,n)   "float/Float"
--  120.5  float(4,2)
-- 2. double(m,n)  “double/Double”

-- m: 规定浮点数值总长度(小数点之前+小数点之后的数字的个数总和)
-- n: 小数点之后的数字的个数总和

-- 使用场景:  成绩  单价

-- 3. decimal(m,n)  实现浮点数据数据进行算术运算  “BigDecimal”
-- 使用场景: 余额  购物车总金额

7.3 字符类型

-- char  String
​
-- 1. char(n) 255个字符
-- 2. varchar(n)  65535个字符
​
-- n: 限定存储字符个数。
char(5) hello
​
char(3) VS varchar(3)
-- 最多存储3个字符数据。
-- 'a'  char(3)-----> 存储内存还是存储3个字符。定长。’a__‘  查询的时候 先trim 再展示
-- 'a'  varchar(3)----> 存储内存还是存储1个字符  可变长度
使用场景:
char(3)  性别  手机号 身份证
varchar(3) 姓名 简介  路径 ....

7.4 日期时间类型

-- java.util.Date  jdk1.8- 
-- java.time.LocalDate  '年月日'
-- java.time.LocalTime  '时分秒'
-- java.time.LocalDateTime '年月日 时分秒' 
​
-- 1. date '年月日'  "等价于java.util.Date/java.time.LocalDate" 
-- 2. time '时分秒'  "等价于java.util.Date/java.time.LocalTime" 
-- 3. datetime '年月日 时分秒' "等价于java.util.Date/java.time.LocalDateTime
-- 4. timestamp 2038年  不建议使用  '年月日 时分秒'
​
-- 时间很重要

==9. DML==

数据操作语言。 INSERT UPDATE DELETE

以student表为例，实现对表数据进行增删改。

9.1 INSERT

-- 一次新增一个
-- 1.语法1
INSERT INTO 库名.表名 VALUES (数据1,....,数据n);-- 对指定表的所有的列赋值  实现新增
-- 表名有n个列，就需要再()新增n个数据 
INSERT INTO student VALUES (1,'张三','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL);
​
-- INSERT INTO student VALUES (2,'李四','男',20,100,'/user/a.jpg',SYSDATE(),NULL);
-- INSERT INTO student VALUES (3,'wangwu','男',20,100,'/user/a.jpg',SYSDATE(),NULL);
​
SELECT NOW(),SYSDATE();

-- 2.语法2
INSERT INTO 库名.表名 (列名1,....,列名n) VALUES (数据1,....,数据n);
-- 指定列新增新的记录
INSERT into student (id,stu_name,create_time) VALUES (4,'jim',NOW());

-- 3.语法3  批处理
INSERT INTO student VALUES 
(11,'张三1','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL),
(12,'张三2','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL),
(13,'张三3','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL),
(14,'张三4','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL);
​
​
INSERT into student (id,stu_name,create_time) VALUES 
(41,'jim1',NOW()),
(42,'jim2',NOW()),
(43,'jim3',NOW()),
(44,'jim4',NOW());

9.2 DELETE

- 一次删除一个 (唯一匹配 根据id删除)
-- 批量删除

-- 语法1:
DELETE FROM 表名;  -- 清空表数据。

-- 语法2:(在满足一定条件下删除)
DELETE FROM 表名 WHERE 条件1 AND/OR 条件2....
-- 条件1： 表里面的列是要参与

DELETE  FROM student;
-- DELETE: 底层都是遍历式删除 一行行删除 
-- 优点: DELETE 属于DML语句  在开启事务前提下  数据可以回滚的
-- 缺点: 性能低

-- 
-- TRUNCATE TABLE student;
-- TRUNCATE: 清空表记录
-- 属于DDL语句。 在开启事务前提下  数据也不可以回滚
-- 性能高。不是遍历式删除。先DROP TABLE 然后CREATE TABLE

	SQL	事务	操作	性能
delete from a;	DML	与事务有关，可以回滚	遍历式删除	低
truncate table a;	DDL	完全无关	先drop table, 再create table	高

## 9.3 UPDATE

```mysql
-- 一次修改一个/行(根据id修改)，修改的某些列的数据。
-- 语法1:
UPDATE 表名 SET 列名1=新的数据1,....,列名n=新的数据n;
-- 修改表里面所有的记录
-- UPDATE student SET stu_gender='女',update_time=NOW();

-- 语法2:
UPDATE 表名 SET 列名1=新的数据1,....,列名n=新的数据n WHERE 条件1 AND/OR 条件2....;

-- UPDATE

-- UPDATE student SET stu_gender='女',update_time=NOW();
-- UPDATE student SET stu_gender='男',update_time=NOW() WHERE stu_name='jim';

-- 修改age=18   条件：  score为null的数据
-- UPDATE student SET age=18 WHERE score  IS  NOT NULL; 

-- SELECT * FROM student WHERE score IS NULL;
```

10. 约束

就是一些规则。限制列的数据。

如果使用一些约束修饰列，列的数据不能随意赋值。

-- 1. 行级约束(对某个列修饰)
    1.1 非空约束 NOT NULL
    1.2 唯一性约束 UNQIUE
    1.3 默认约束  DEFAULT
    -- 以上3个约束，可以在一张表里面使用多次修饰多个列。
    1.4 主键约束  PRIMARY KEY 
    -- 一张表，主键约束只能修饰一个列(主键列)，只能使用1次。
    
-- 2. 表级约束(维护多表关系)
   2.1 主键约束  PRIMARY KEY 
   --  位置上区分。联合主键。
   2.2 外键约束  FOREIGN KEY 
   -- 维护多表数据。   

10.1 NOT NULL

非空约束。使用非空约束修饰列，列的数据不能为null。

-- 1. NOT NULL
-- CREATE TABLE a(
--   id INT,
--  name VARCHAR(20),
--  age INT
-- );
-- DESC a;
-- INSERT INTO a (id) VALUES (1);
-- INSERT INTO a (id,name) VALUES (1,'aaa');
-- 需求: id,name必须不能为null
-- 在创建表成功之后，修改指定列的约束。
-- ALTER TABLE a MODIFY id INT NOT NULL;
-- CREATE TABLE b(
--   id INT NOT NULL, -- 行级约束
--  name VARCHAR(20) NOT NULL,
--  age INT
-- );

10.1 NOT NULL

非空约束。使用非空约束修饰列，列的数据不能为null。

-- 1. NOT NULL
-- CREATE TABLE a(
--   id INT,
--  name VARCHAR(20),
--  age INT
-- );
-- DESC a;
-- INSERT INTO a (id) VALUES (1);
-- INSERT INTO a (id,name) VALUES (1,'aaa');
-- 需求: id,name必须不能为null
-- 在创建表成功之后，修改指定列的约束。
-- ALTER TABLE a MODIFY id INT NOT NULL;
-- CREATE TABLE b(
--   id INT NOT NULL, -- 行级约束
--  name VARCHAR(20) NOT NULL,
--  age INT
-- );

10.3 DEFAULT

默认约束。使用默认约束修饰列，必须给列设置==默认数据==

CREATE TABLE a(
​
id INT NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL,
gender CHAR(1) DEFAULT '男'
);
​
INSERT INTO a (id,name) VALUES (2,'张三1');
SELECT * FROM a;
-- 需求: 用户注册  提交数据  未设置性别 设置默认值 '男'
ALTER TABLE a MODIFY gender CHAR(1)  DEFAULT '男';

==10.4 PRIMARY KEY==

主键约束。行级约束/表级约束

-- 主键约束自带index(Btree)。
-- 使用主键约束修饰的列，称为"主键列"。
-- 在一张表中，主键列有且只有1个。
-- 在一张表中，只能使用1次主键约束。
-- 理论上说，任意类型的1个列都可以使用PRIMARY KEY修饰。
-- 正常开发中，一般使用的列的数据类型: int bigint varchar
-- 使用PRIMARY KEY修饰列，这个列的数据"不能为null，且唯一"
​
-- 在开发中，只使用not null+unique修饰id  理论上也可以
-- 整数类型: id的值必须要唯一。id要自增的---> auto_increment --> id 必须是主键列
​
-- 从表设计的3大范式:
-- 第1范式: 保证列的原子性。列不可再分的原则。
--  sys_user:  address  '河南省郑州市高新区'  这个地址信息可以拆分的。
--  provinceCode  cityCode  areaCode
-- 第2范式: 满足第一范式，保证每行记录唯一性。 id 必须要用“主键约束”。
-- 第3范式: 满足第2范式，避免列、数据冗余的问题。外键列的数据除外。
-- order ----> buy_num * prod_price 
-- id buy_num  prod_price money   这样设计有问题的
​
 1  10 5.0 10*5
-- product
-- id prod_name  prod_store prod_price ....

==1. auto_increment==

CREATE TABLE b(
  id INT PRIMARY KEY , -- 行级约束
    name VARCHAR(20),
    age int
);
​
-- DESC b;
-- INSERT INTO b (id,name) VALUES (1,'a');
-- INSERT INTO b (id,name) VALUES (2,'a');
​
-- id主键列  唯一不能null  
-- id是整数类型: 每次累增+1  
-- 使用自增的特性。----> 列必须是主键列，整数类型
ALTER TABLE b MODIFY id INT auto_increment;
-- id设置了自增的特性  对与程序员 不用管id
-- 自增初始值1  步长+1
-- INSERT INTO b (name) VALUES ('f'); 
-- 上一次的id+1
​
-- 获得上一次的自增的id的数据
SELECT LAST_INSERT_ID();
​
ALTER TABLE b auto_increment 1001;
SET GLOBAL auto_increment_increment=1;

2. uuid

-- PRIMARY KEY
-- CREATE TABLE a(
--   id VARCHAR(255),
--  name VARCHAR(20),
--  age int,
--  PRIMARY KEY(id)  -- 表级约束
-- );
​
-- 保证字符数据的唯一性。
-- 获得唯一的字符串数据
-- 1. 获得当前时间戳
-- long mills = System.currentTimeMills(); 
-- long nanos = System.nanoTime(); 
-- 2. UUID
-- String uuidStr = UUID.randomUUID().toString();
​
-- 3. mysql服务器 UUID() UUID_SHORT()
-- INSERT INTO a (id,name) VALUES (UUID(),'f'); 
-- INSERT INTO a (id,name) VALUES (UUID_SHORT(),'f'); 
​
​
-- SELECT * FROM A WHERE ID='df852482-5115-11ee-b1cf-581122cc10a3'

3. 联合主键

-- 联合主键(中间表: 维护多表关系的)
-- 多个列联合起来  作为一个主键列体现。
-- 必须使用表级约束
​
-- CREATE TABLE b(
--  id VARCHAR(255),
--  name VARCHAR(20),
--  age int,
--  PRIMARY KEY(id,name)
-- );
INSERT INTO b (id,name) VALUES ('1','f');
INSERT INTO b (id,name) VALUES ('1','a');
INSERT INTO b (id,name) VALUES ('2','a');

10.5 外键约束 FOREIGN KEY

表级约束。外键约束。

-- 核心作用: 保证数据安全。(存储引擎是InnoDB)
-- 弊端: 性能低。
​
-- 1.使用FOREIGN KEY修饰的列  称为"外键列"
-- 2.一张表里面可以使用多次FOREIGN KEY修饰多个列
-- 3.外键列在的表一般称为 “从表/子表”
-- 4.外键列的数据严格参照"主表/基表"的主键列的数据。
-- 5.外键列的数据类型与主表的主键列类型一致。
-- 6.外键列服务/维护多表之间的关系。

-- 创建外键约束
-- 对cart表里面uid新增外键约束  保证数据的安全性
-- 创建表之后的情况下:
-- ALTER TABLE cart ADD CONSTRAINT FOREIGN KEY(uid) REFERENCES  sys_user(id);

-- 外键约束的特性: RESTRICT
-- 性能低:
-- 操作主表/子表
-- 新增/查询/修改主表记录 ok  删除主表记录 子表没有关联这行记录 ok  反之: 无法删除 
-- 查询/删除子表记录  ok    新增/修改可能有问题  外键列的数据要严格参照主表的数据

-- 外键约束的特性: CASCADE (级联)
-- 性能低:
-- 操作主表/子表
-- 新增/查询/修改主表记录 ok     
-- 删除主表记录 先查询子表是否关联使用这行记录 有 遍历删除子表记录  再删除主表的记录
-- 查询/删除子表记录  ok    新增/修改可能有问题  外键列的数据要严格参照主表的数据

-- 外键约束的特性： SET NULL(外键列可以为null)
-- 性能低:
-- 操作主表/子表
-- 新增/查询/修改主表记录 ok   
-- 删除主表记录  先查询子表是否关联使用这行记录 有 将外键列的数据都设置为null
​
-- 查询/删除子表记录  ok    新增/修改可能有问题  外键列的数据要严格参照主表的数据

在正常的开发中，禁止使用外键约束。一切表的数据维护需要再业务层面(页面/后端代码)上解决。==外键列正常创建，这些列不新增外建约束。仅仅是一个普通列作为外键列体现。==

毕业设计，练习，无所谓的。正常的使用外键约束。

-- 查询所有的存储引擎
-- SHOW ENGINES;
-- CREATE TABLE cart(
--   id INT PRIMARY KEY auto_increment,
--  total_money DECIMAL(20,2),
--  uid INT ,
--  FOREIGN KEY (uid) REFERENCES  sys_user(id)
-- );
​
-- 自带索引
-- SHOW INDEX FROM cart;

==12. 表设计==

1. 用户信息表 sys_user
2. 购物车表 cart
3. 购物项表 cart_item
4. 商品表 product_info
5. 类型表 type

-- 关系: 一对一 一对多
-- 一对一
-- 用户表与购物车  一个用户一个购物方 
-- 购物项与商品  一个购物项项 对一个商品。 一个商品对应很多购物项。

-- 一对多
-- 购物车 与 购物项  一个购物车有很多购物项
-- 类型与商品  一个类型下有很多商品  一个商品对一个类型

-- 学生与老师(多对多)

12.1 一对一

用户表与购物车

CREATE TABLE `sys_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(20) DEFAULT NULL COMMENT '用户名称',
  `age` tinyint unsigned DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL COMMENT '用户加密之后密码',
  `phone` char(11) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  `balance` decimal(20,3) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL COMMENT '存储服务器资源路径',
  `status` int DEFAULT NULL COMMENT '用户状态 0 禁用 1 可用  2 删除',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
​
CREATE TABLE `cart` (
  `id` int NOT NULL AUTO_INCREMENT,
  `total_money` decimal(20,2) DEFAULT NULL COMMENT '购物车金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;    

-- 创建外键约束
-- 对cart表里面uid新增外键约束  保证数据的安全性
-- 创建表之后的情况下:
-- ALTER TABLE cart ADD CONSTRAINT FOREIGN KEY(uid) REFERENCES  sys_user(id);

12.2 一对多

购物车与购物项

CREATE TABLE `cart` (
  `id` int NOT NULL AUTO_INCREMENT,
  `total_money` decimal(20,2) DEFAULT NULL,
  `uid` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `cart_item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `buy_num` int DEFAULT NULL,
  `money` decimal(10,2) DEFAULT NULL COMMENT '小计',
  `cid` int DEFAULT NULL COMMENT '外键列  严格参照cart表里面的id数据',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

购物项与商品

CREATE TABLE `cart` (
  `id` int NOT NULL AUTO_INCREMENT,
  `total_money` decimal(20,2) DEFAULT NULL,
  `uid` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `cart_item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `buy_num` int DEFAULT NULL,
  `money` decimal(10,2) DEFAULT NULL COMMENT '小计',
  `cid` int DEFAULT NULL COMMENT '外键列  严格参照cart表里面的id数据',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `cart_item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `buy_num` int DEFAULT NULL,
  `money` decimal(10,2) DEFAULT NULL COMMENT '小计',
  `cid` int DEFAULT NULL COMMENT '外键列  严格参照cart表里面的id数据',
  `pid` int DEFAULT NULL COMMENT '外键列  严格参照product_info里面的id的数据',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `product_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `prod_name` varchar(255) DEFAULT NULL,
  `prod_price` decimal(10,2) DEFAULT NULL,
  `prod_store` bigint DEFAULT NULL,
  `prod_image` varchar(255) DEFAULT NULL,
  `prod_desc` varchar(255) DEFAULT NULL,
  `prod_status` int DEFAULT NULL COMMENT '1 在售  0 下架  ',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

类型与类型

-- 一个父类型有很多子级类型
CREATE TABLE `type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type_name` varchar(255) DEFAULT NULL COMMENT '类型名称',
  `parent_type_id` int DEFAULT NULL COMMENT '外键列 数据要严格参照type表里面的id   0 一级类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

商品与类型

CREATE TABLE `product_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `prod_name` varchar(255) DEFAULT NULL,
  `prod_price` decimal(10,2) DEFAULT NULL,
  `prod_store` bigint DEFAULT NULL,
  `prod_image` varchar(255) DEFAULT NULL,
  `prod_desc` varchar(255) DEFAULT NULL,
  `prod_status` int DEFAULT NULL COMMENT '1 在售  0 下架  ',
  `type_id` int DEFAULT NULL COMMENT '外键列  严格参照type的id的数据',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

12.3 多对多

-- 创建student
-- 创建teacher
-- 经典的使用场景:  用户  角色  菜单

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(20) DEFAULT NULL,
  `stu_gender` char(2) DEFAULT NULL,
  `age` tinyint unsigned DEFAULT NULL,
  `score` float(4,1) DEFAULT NULL,
  `stu_image` varchar(200) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stu_name` (`stu_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `teacher_student` (
  `tid` int DEFAULT NULL COMMENT '外键列 严格参照teacher表的id的数据',
  `sid` int DEFAULT NULL COMMENT '外键列 严格参照student表的id的数据'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

==13. DQL==

SELECT 都是有结果。查询的结果都在一张==虚拟表==中。

1. 语法

SELECT
 [DISTINCT]列1,...,列n  -- 这些列可能是多张表的列
FROM
 表1,...,表n
[WHERE 条件1 OR/AND....] -- 根据指定的条件对记录过滤
[GROUP BY 列1] -- 根据指定的列分组查询
[HAVING 条件1 OR/AND....] -- 对分组之后的记录进行过滤
[ORDER BY 列 ASC/DESC] -- 根据指定的列进行升序(默认 ASC)或者降序排序 
[LIMIT ?/?,?] -- 限定查询的结果集。分页查询

2. 建库建表

CREATE TABLE stu (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

CREATE TABLE emp(
    empno INT, 
    ename VARCHAR(50),
    job VARCHAR(50),
    mgr INT,
    hiredate DATE,
    sal DECIMAL(7,2),
    comm decimal(7,2),
    deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE dept(
    deptno INT,
    dname varchar(14),
    loc varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

CREATE TABLE `salgrade` (
 `GRADE` int(11) NOT NULL DEFAULT '0' COMMENT '⼯资的等级',
 `LowSAL` decimal(7,2) DEFAULT NULL COMMENT '此等级的最低⼯资',
 `HISAL` decimal(7,2) DEFAULT NULL COMMENT '此等级的最⾼⼯资'
) ;
INSERT INTO `salgrade` VALUES
(1,700.00,1200.00),(2,1201.00,1400.00),
(3,1401.00,2000.00),(4,2001.00,3000.00),(5,3001.00,9999.00);

3. 基础查询

-- 1. 基础查询
-- * 是一个通配符  统配的是所有的列
-- sql优化角度: 一张表里面列的数量>=10 不要使用* 
-- 指定列名去查询
-- SELECT sid,sname,age,gender FROM stu;
-- SELECT sid,sname FROM stu;

4. WHERE

-- 过滤记录
条件查询就是在查询时给出WHERE⼦句，在WHERE⼦句中可以使⽤如下运算符及关键字：
=、!=、<>不等于、<、<=、>、>=；
BETWEEN…AND；是否满⾜⼀个区间范围 >=  <=
IN(set)；条件的集合
IS NULL；
AND； 连接多个条件的查询
OR；or 满⾜其中⼀个条件就可以
NOT；
​
-- 1. 查询学生性别为女，并且年龄50的记录
-- SELECT * FROM stu WHERE (gender='female' AND age=15);
-- SELECT * FROM stu WHERE (gender='female') OR age=50;
​
-- 2、查询学号为S_1001，S_1002，S_1003的记录
-- SELECT * FROM stu WHERE sid='s_1001' OR sid='s_1003' OR sid='s_1004';
-- SELECT * FROM stu WHERE sid  IN ('s_1001','s_1003','s_1004');
-- 3、查询学号不是S_1001，S_1002，S_1003的记录
-- SELECT * FROM stu WHERE sid!='s_1001' AND sid<>'s_1003' AND sid<>'s_1002';
-- SELECT * FROM stu WHERE sid NOT IN ('s_1001','s_1003','s_1004');
​
-- 4、查询年龄为null的记录
-- SELECT * FROM stu WHERE age IS NULL;
-- SELECT * FROM stu WHERE age IS NOT NULL;
-- 
-- 5、查询年龄在20到40之间的学生记录
-- SELECT * FROM stu WHERE age>=20 AND age<=40;
-- SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
-- 6、查询性别非男的学生记录
-- SELECT * FROM stu WHERE gender!='male' OR  gender IS NULL;

5. 模糊查询 LIKE

与where一起使用。模糊查询/搜索。

-- 任意一个字符： _ 
-- 任意数量的字符: %
-- 1、查询姓名由5个字符构成的学生记录
-- SELECT * FROM stu WHERE sname LIKE '_____';
-- 2、查询姓名以“z”开头的学生记录
-- SELECT * FROM stu WHERE sname LIKE 'z%';
-- 3、查询姓名中第2个字符为“i”的学生记录
-- SELECT * FROM stu WHERE sname LIKE '_i%';
-- 4、查询姓名中包含“a”字符的学生记录
-- SELECT * FROM stu WHERE sname LIKE '%a%';

6. 字段控制查询

针对于某个列/某些列而言的，来做一些特殊的处理。

DISTINCT

-- DISTINCT  去除重复的行记录
-- 查询stu表里面的性别的数据
SELECT DISTINCT sname, gender FROM stu; -- 去重

IFNULL函数

-- ifnull(列名,默认的数据);
-- 查询emp的信息  展示员工薪金之和(薪水+奖金)
-- 列的数据如果是null，与null值做运算，结果都是null
-- IFNULL(列名,默认数据)
-- 列的数据为null  使用默认数据参与运算 否则还是使用自身的数据
SELECT empno,ename,sal,comm, sal+ IFNULL(comm,0) FROM emp;

AS 可以省略。不建议省略。 起别名(起一个新的列名)

-- 对复杂的表名，列名都有起别名的需求
-- 尤其后面的多表的关联查询中 建议起别名
-- 别名: 可以是中文  建议英文单词
​
SELECT empno,ename,sal,comm, sal+ IFNULL(comm,0) AS '薪金之和' FROM emp;
SELECT empno,ename,sal,comm, sal+ IFNULL(comm,0) AS money FROM emp AS e;

7. 聚合函数

- 还可以称为组函数、分组函数
- ⽤作统计使⽤，⼜称为聚合函数或者统计函数或者组函数。
- 聚合函数是⽤来做纵向运算的函数：
​
- COUNT(字段/列)：统计指定列不为NULL的记录⾏数；⼀般使⽤count（*）统计⾏数
-- 统计表的记录数。
​
- MAX(字段/列)：计算指定列的最⼤值，如果指定列是字符串类型，那么使⽤字符串排序运算；
- MIN(字段/列)：计算指定列的最⼩值，如果指定列是字符串类型，那么使⽤字符串排序运算；
- SUM(字段/列)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
- AVG(字段/列)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
- SUM、AVG⼀般处理数值型
- MAX、MIN、COUNT可以处理任意数据类型
分组函数都忽略了NULL值，可以和DISTINCT搭配使⽤
注意点：组函数可以出现多个，但是不能嵌套；如果没有GROUP BY ⼦句，结果集中所有⾏数作为⼀组

1、查询emp表中记录数：
2、查询emp表中有佣⾦的⼈数：注意，因为count()函数中给出的是comm列，那么只统计comm列⾮NULL的⾏数。
3、查询emp表中⽉薪⼤于2500的⼈数：
4、统计⽉薪与佣⾦之和⼤于2500元的⼈数：
5、查询有佣⾦的⼈数，以及有领导的⼈数：
6、查询所有雇员⽉薪和：
7、查询所有雇员⽉薪和，以及所有雇员佣⾦和：
8、查询所有雇员⽉薪+佣⾦和：
9、统计所有员⼯平均⼯资：
10、查询最⾼⼯资和最低⼯资
​
-- COUNT(列名)
-- 1. 统计stu表里面所有的学生数量(表里面所有的记录数量 主键列)
-- SELECT COUNT(sid) FROM stu; -- stu的记录分成一组 统计
-- SELECT COUNT(*) FROM stu;
​
-- 2. 统计stu表里面男生/女生学生数量
-- SELECT COUNT(*) FROM stu WHERE gender='male';
​
-- 3. 统计emp表里面有奖金的人数
-- SELECT COUNT(comm) FROM emp;  
​
-- 4. 统计emp最高薪水，最低薪水，所有的员工薪水总和，平均薪水
-- SELECT MAX(sal) AS '最高薪水', MIN(sal), SUM(sal),AVG(sal),SUM(sal)/COUNT(*) FROM emp;
-- SELECT * FROM emp WHERE sal = (SELECT MAX(sal) AS '最高薪水' FROM emp);
​

8. 分组查询GROUP BY

1、查询每个部⻔的部⻔编号和每个部⻔的⼯资和：
2、查询每个部⻔的部⻔编号以及每个部⻔的⼈数：
3、查询每个部⻔的部⻔编号以及每个部⻔员⼯⼯资⼤于1500的⼈数：
4、查询每个性别的学⽣的数量，根据gender统计学⽣的数量
​
-- 分组查询  GROUP BY
-- 查询stu 展示gender以及不同性别的人数
-- SELECT COUNT(*) FROM stu WHERE gender='male';
-- SELECT COUNT(*) FROM stu WHERE gender='female';
-- 使用1条sql实现 
SELECT gender, COUNT(*) FROM stu WHERE gender IS NOT NULL GROUP BY gender;
​
-- 查询emp 统计不同部门的人数。以及展示不同部门的员工薪资总和
SELECT deptno,COUNT(*),SUM(sal) FROM emp GROUP BY deptno;
-- GROUP BY 自带去重

9. 分组过滤HAVING

1.查询⼯资总和⼤于9000的部⻔编号以及⼯资和
​
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
​
​
-- HAVING
-- 功能与where一致。
​
-- 查询sal=1500的员工信息
-- SELECT * FROM emp WHERE sal=1500;
-- SELECT * FROM emp HAVING sal=1500;
​
-- 需求: 对分组之后的数据进行过滤
-- 查询每个部门的员工人数  展示员工人数>=5  
SELECT deptno,COUNT(*) AS cou FROM emp  GROUP BY deptno HAVING COUNT(*)>=5 ;
​
-- WHERE不能与分组函数一起使用  HAVING 可以一起使用
-- WHERE 必须在group by之前 HAVING 必须在group by 之后

10.排序 ORDER BY

-- 默认升序  ASC 可以省略
-- 降序: DESC
-- 查询员工信息  根据sal进行升序/降序排序
-- 遇见了sal相同 根据empno进行降序排序
​
SELECT * FROM emp ORDER BY sal ASC;
SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC , empno DESC;
SELECT deptno,sum(sal) AS sum FROM emp GROUP BY deptno ORDER BY sum ASC;

11. 分页查询LIMIT

-- LIMIT  限定查询的结果集(分页查询)
-- 一张表里面肯定有很多行记录  不可能查询所有的记录进行展示
-- 除非这张表里面的数据 不怎么改变 数量也不多 (查询所有)
​
-- 分页查询emp表的信息
-- 每一页展示5条记录  pageSize=5
-- 一共有多少页
-- totalCount=19
-- SELECT COUNT(*) FROM emp;
-- totalPage = totalCount/pageSize;
-- totalPage = totalCount%pageSize==0 ?totalPage:totalPage+1 ;
-- totalPage=4
​
-- LIMIT pageSize; 从第一行记录查询 查询pageSize条  查询第一页
-- LIMIT startRowIndex,pageSize; 
-- startRowIndex: 从指定的行记录的index开始查询  0  查询pageSize条
-- 获得用户请求查询的第page页
-- 查询第1页的数据
 SELECT * FROM emp LIMIT 0,5;
-- 查询第2页的数据
 SELECT * FROM emp LIMIT 5,5;
-- 查询第3页的数据
 SELECT * FROM emp LIMIT 10,5;
-- 查询第4页的数据
 SELECT * FROM emp LIMIT 15,5;
​
-- 通用的分页查询的模板sql
-- 在java程序请求的时候
SELECT * FROM emp LIMIT (page-1)*pageSize,pageSize;

12. 多表查询

12.1 关联查询

-- 1、查询员工信息，要求显示: 员工号，姓名，月薪，部门名称
-- 2、查询员工信息，要求显示：员工号，姓名，月薪，薪资的级别
-- 3、查询员工信息，要求显示：员工号，姓名，月薪，部门名称,薪资的级别
-- 1.1 关联查询
-- 1、查询员工信息，要求显示: 员工号，姓名，月薪，部门名称
-- 14行记录
-- 56= 14*4  x*y 笛卡尔积里面有一部分数据是有误的
-- 过滤不符合要求的数据  找表的关系: 外键列
-- 规则: 多表连接查询 2 至少1个  3 至少2
SELECT
 e.empno,e.ename,e.sal,d.dname
FROM
emp AS e,dept AS d
WHERE e.deptno=d.deptno; -- 等值连接
​
-- 2、查询员工信息，要求显示：员工号，姓名，月薪，薪资的级别
SELECT
 e.empno,e.ename,e.sal,s.GRADE
FROM
emp AS e,salgrade AS s
WHERE
e.sal BETWEEN s.LowSAL AND s.HISAL  -- 不等值连接
ORDER BY e.sal;
​
-- 3、查询员工信息，要求显示：员工号，姓名，月薪，部门名称, 薪资的级别
SELECT
  e.empno,e.ename,e.sal,d.dname,s.GRADE
FROM
emp AS e, dept AS d,salgrade AS s
WHERE
 e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL;
 
-- 查询指定用户购物车信息:  用户id=1006
-- 显示: 商品的图片 名称  单价 购买数量 小计  商品类型
-- SELECT
--  p.id,p.prod_name,p.prod_image,p.prod_price, ci.buy_num, ci.money,t.type_name
-- FROM
--  mydb.product_info AS p, 
--  mydb.cart_item AS ci,
--  mydb.type AS t,
--  mydb.cart AS c
-- WHERE 
--  c.uid = 1006 AND  c.id=ci.cid AND ci.pid=p.id AND p.type_id=t.id;

12.2 连接查询

-- 1、查询员工信息，要求显示员工号，姓名，月薪，部门名称 使内连接和关联查询
-- 2、查询所有部门的名称以及每个部门的员工数量
​
-- 1.2 连接查询
-- 1. 内连接  表1 INNER JOIN 表2  ON 条件1 INNER JOIN 表3 ON 条件2......
--  表1 INNER JOIN 表2  ON/WHERE 条件1
-- 内连接的查询的结果与普通的关联查询语法结果是完全一致的。
-- 内连接几乎不用
​
-- 2. 外连接
-- 2.1 左外连接  表1  LEFT  JOIN  表2  ON 条件1 LEFT JOIN 表3 ON 条件2....
-- 以左边的表为基准表，右边的表没有的数据使用null/0填充
​
-- 2.2 右外连接  表1  RIGHT JOIN  表2  ON 条件1 RIGHT JOIN 表3 ON 条件2....
-- 以右边的表为基准表，左边的表没有的数据使用null/0填充
​
-- 1. 查询员工信息，要求显示员工号，姓名，月薪，部门名称  使内连接和关联查询
-- SELECT 
--  e.empno,e.ename,e.sal,d.dname
-- FROM 
-- emp AS e,dept AS d
-- WHERE e.deptno=d.deptno;
​
-- SELECT 
--  e.empno,e.ename,e.sal,d.dname
-- FROM 
-- emp AS e INNER JOIN dept AS d
-- ON e.deptno=d.deptno  WHERE 1=1;
​
-- 2. 查询所有部门的名称以及每个部门的员工数量
-- 显示: 部门编号  部门名称  部门里面的员工数量 count()
-- 4行记录
-- SELECT
--   d.deptno,d.dname, COUNT(*) AS '每个部门的员工数量'
-- FROM
-- dept AS d,emp AS e
-- WHERE d.deptno=e.deptno
-- GROUP BY d.deptno
-- ORDER BY d.deptno;
-- 普通关联/内连接不能满足需求   d.deptno=e.deptno 将40部门筛掉
SELECT
  d.deptno,d.dname, COUNT(e.empno) AS '每个部门的员工数量'
FROM
 emp AS e  RIGHT JOIN  dept AS d  ON d.deptno=e.deptno
GROUP BY d.deptno
ORDER BY d.deptno;

12.3 自连接

-- 1、查询员工信息，员工的上级的名称
-- 2、查询员工信息，展示部门名称 薪资级别 上级领导名
-- 1.3 自连接
-- 本表与本表进行关联查询。把一张表看成多张表操作， 核心: "对表起别名"。
-- 1、 查询员工信息，员工的上级的名称
-- 显示员工基本信息，还要显示员工上级的领导的名称
-- 14行记录
-- SELECT
--   e.empno,e.ename,e.sal,e.mgr, e1.empno, e1.ename AS '领导的名称'
-- FROM 
-- emp AS e,emp AS e1
-- WHERE  e.mgr=e1.empno;
-- SELECT
--   e.empno,e.ename,e.sal,e.mgr, e1.empno, e1.ename AS '领导的名称'
-- FROM 
-- emp AS e LEFT JOIN emp AS e1 ON e.mgr=e1.empno;
​
-- 2. 查询员工信息，展示部门名称 薪资级别 上级领导名
-- 外连接可以与普通关联混合使用
​
-- SELECT
--   e.empno,e.ename,e.sal,e.mgr, d.dname,s.GRADE, e1.empno, e1.ename AS '领导的名称'
-- FROM 
-- emp AS e,emp AS e1,dept AS d,salgrade AS s
-- WHERE  e.mgr=e1.empno AND e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL;
​
-- SELECT
--   e.empno,e.ename,e.sal,e.mgr,d.dname,s.GRADE, e1.empno, e1.ename AS '领导的名称'
-- FROM 
-- emp AS e LEFT JOIN emp AS e1 ON e.mgr=e1.empno 
-- LEFT JOIN dept AS d ON e.deptno=d.deptno
-- LEFT JOIN salgrade AS s ON e.sal BETWEEN s.LowSAL AND s.HISAL ;
​
​
-- SELECT
--   e.empno,e.ename,e.sal,e.mgr,d.dname,s.GRADE, e1.empno, e1.ename AS '领导的名称'
-- FROM 
-- emp AS e LEFT JOIN emp AS e1 ON e.mgr=e1.empno,dept AS d,salgrade AS s
-- WHERE 
-- e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL;
-- 

12.4 集合查询

-- UNION VS UNION ALL
-- 1.4 集合查询
-- 语法：将多个结果集合并成一个结果集
-- 要求: 查询的列的数量必须一致
-- SELECT empno,ename FROM emp
-- UNION ALL
-- SELECT deptno,dname FROM dept
-- UNION ALL
-- SELECT * FROM stu;
​
-- 场景: 分表的时候
-- 把学生信息拆分成很多表维护
-- CREATE TABLE stu_1 AS SELECT * FROM stu;
-- stu  stu_1  stu_3
-- 查询所有学生的记录
-- SELECT * FROM stu;
-- SELECT * FROM stu_1;
-- SELECT * FROM stu_3;
​
-- UNION ALL  把所有的结果全部合并到一起  不会去重
-- UNION  把所有的结果全部合并到一起 会对行记录去重
​
-- SELECT * FROM stu
-- UNION 
-- SELECT * FROM stu_1
-- UNION 
-- SELECT * FROM stu_3;
​
-- MySQL里面实现去重的方式:
-- 1. DISTINCT
-- 2. UNION
-- 3. GROUP BY 

12.5 子查询

-- 子查询
-- SELECT语句中嵌套多个SELECT
-- 原因: 查询的条件是未知的
​
-- 查询员工的sal=30号部门平均薪资的员工信息
-- SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno=30)

14. 数据库事务

数据库的事务(序列): 
 transaction.
 概念:
   在功能实现中，需要一系列sql语句实现一个功能，这个功能里面的一些列sql要么是全部成功的，要么是全部失败的。不能出现一些sql成功了，一些sql失败了。
   这个序列就是一个一个不可分割的工作单位。
   事务由事务开始与事务结束之间执行的全部数据库操作组成。
   
目的:
  "保证数据的一致性"。

==14.1 四大特性 ACID==

-- 1. A atomic 原子性
-- 不可分割的工作单位。要么是全部成功的，要么是全部失败的。
-- 转账: 张三 balance 5000 给李四 1000 转账  转1000
-- UPDATE sys_user SET balance=balance-1000 WHERE id = 张三的id
-- UPDATE sys_user SET balance=balance+1000 WHERE id = 李四的id
​
-- 2. C consistentency 一致性
-- 事务开始之前 以及事务提交之后 数据正确的。
-- 事务开始之前: 张三的balance=5000  李四的balance=1000
-- 事务提交之后: 张三的balance=5000-1000  李四的balance=1000+1000
​
-- 3. I isolation 隔离性
-- 张三给李四转账   王五也可以给李四转账
-- 2个客户端同时操作  2个事务  事务与事务之间是相互隔离的。
-- 不同的DBMS软件使用不同的隔离级别机制，在事务并发的环境下，可能会出现数据安全的问题。
-- 问题:  脏读  不可重复读   幻读
​
-- 4. D durability 持久性
-- 事务提交之后 数据可以持久化保存。

14.2 DCL

-- 事务
-- 实现转账功能
BEGIN; -- 手动开启事务
SELECT * FROM sys_user WHERE id=1007;
SELECT * FROM sys_user WHERE id=1008;
-- 张三 id=1007转账1000给李四 id=1008
UPDATE sys_user SET balance=balance-1000 WHERE id=1007;
-- 还有一系列逻辑需要执行
-- 只要更新sys_user记录  都需要再user_log新增一行记录
INSERT INTO user_log (type,remark) VALUES ('修改','更新了1007的余额-1000');
UPDATE sys_user SET balance=balance+1000 WHERE id=1008;
INSERT INTO user_log (type,remark) VALUES ('修改','更新了1008的余额+1000');
​
ROLLBACK;-- 事务回滚 回滚到事务开始之前的数据上
COMMIT; -- 提交事务 将虚拟表中数据  更新到物理表  满足事务的D
-- 以上的操作属于一个"转账"的功能
-- 问题: 没有满足事务的要求 
-- 需求: 一系列的sql 全部成功或者全部失败
-- 根本原因: 没有在一个事务内执行  mysql的事务自动提交
-- 实现方式: 关闭mysql事务的自动的提交(手动控制事务)  保证在一个事务内执行
-- 查询mysql的事务提交
SHOW VARIABLES LIKE '%autocommit%';
SET autocommit = on;  -- 关闭事务的自动提交

==14.3 隔离级别机制==

并发的环境下，DBMS使用不同的隔离级别机制，会对数据造成不同的问题。

-- 1. 读未提交(未提交读)  read UNCOMMITTED
-- 2. 读已提交 READ COMMITTED 
-- 3. 可重复读  REPEATABLE READ
-- 4. 串行化   SERIALIZABLE
​
-- SQLServer Oracle使用的隔离级别机制是: 读已提交
-- MySQL使用的隔离级别机制是： 可重复读
-- 查询MySQL的隔离级别机制
SHOW VARIABLES LIKE '%isolation%';
​
-- 产生的问题:  2个事务
-- 1. 脏读
-- A事务会读取B事务未提交的数据。
​
-- 2. 不可重复读
-- 在事务提交之后，先后多次读取的数据不是一致的。 update insert delete
​
-- 3. 幻读
-- 在事务提交之后, 出现了很多新的记录。  insert 

机制	脏读	不可重复读	幻读
读未提交	y	y	y
读已提交	n	y	y
可重复读	n	n	y
串行化	n	n	n

14.4 演示

演示MySQL在不同的隔离级别机制下产生的问题。

略。

-- 修改MySQL的隔离级别机制(学习期间  开发中 不会修改的)
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

15. 索引 INDEX

15.1 概念

索引就是MySQL软件提供的数据结构。用来提高查询/检索数据的效率。(SELECT)
索引依赖于物理表的。

15.2 索引结构

根据MySQL软件使用的存储引擎的不同，支持的数据结构也是不一样。
BTREE
HASH
RTree
FULLTEXT

索引结构	描述
B+Tree索引	最常见的索引类型，大部分存储引擎都支持B+树索引。
Hash索引	底层数据结构是用hash实现，只有精确匹配索引的列才会生效，不支持范围查询。
R-tree(空间索引)	是MyIsam引擎的一个特殊索引类型，主要用户地理空间数据类型，使用很少
Full-text(全文索引)	是一种通过建立倒排索引，快速匹配文件的方式。类型后面学习的solr，es

索引结构类型	InnoDB	MyISAM	Memory
B+tree索引	Y	Y	Y
hash索引	N	N	Y
R-tree索引	N	Y	N
Full-text索引	5.6+ Y	Y	N

15.3 索引分类

-- 1. 唯一性索引  新增unique
-- 2. 主键索引    primary key 
-- 3. 外键索引    foreign key
-- 4. 普通索引    index
-- 5. 全文索引    fulltext

15.4 优缺点

-- 优点:  提高查询的性能。
-- 缺点:
-- 索引也是占用空间的
-- 更新操作是不友好的(insert update)

15.5 语法

-- 索引  INDEX
-- SHOW INDEX FROM a;
-- 操作index的语法
-- 1. 创建索引
-- CREATE  INDEX 索引名 ON 表名(列1,列2....);
-- CREATE UNIQUE INDEX 索引名 ON 表名(列1,列2....);
-- CREATE FULLTEXT INDEX 索引名 ON 表名(列1,列2....);
-- 创建index
CREATE UNIQUE INDEX idx_sys_user_2 ON sys_user (email);
​
-- 删除index
DROP INDEX idx_sys_user ON sys_user;
​
SELECT * FROM sys_user WHERE username like 'a%';
​
-- 查询index
SHOW INDEX FROM sys_user;
​
SHOW INDEX FROM product_info;

15.6 性能优化

-- SQL性能优化
-- 哪些sql执行的时间是比较久?
-- 需要对哪些sql创建index进行优化，提升性能?
-- 1. sql的执行频率
-- insert  delete  update  select
-- SHOW GLOBAL STATUS LIKE 'COM_______';
​
-- 2. 以上指定 得到 SELECT语句执行的频率比较高的
-- 肯定会出现某些select语句比较耗时的。
-- 知道哪些select查询语句比较耗时。
-- 2.1 慢查询日志 
-- 记录了这些操作超过指定时间的select语句(long_query_time 10s)
SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE '%long_query_time%';
-- SET long_query_time=1;
-- SELECT * FROM sys_user WHERE username LIKE '%a%';
​
-- 2.2 PROFILE
-- 记录了sql的耗时时间的具体的位置
-- 保证PROFILE开启的
-- SHOW VARIABLES LIKE '%PROFILE%';
-- SELECT @@HAVE_PROFILING; 
-- SHOW PROFILE FOR QUERY 292;
-- SELECT * FROM sys_user;
-- SHOW PROFILES;
​
-- 2.3 EXPLAIN
EXPLAIN SELECT * FROM sys_user WHERE id BETWEEN 1000 AND 3000;
EXPLAIN SELECT * FROM sys_user WHERE SUBSTRING(username,0,3) ='abc';
​
EXPLAIN SELECT * FROM sys_user WHERE phone='110';
​
CREATE INDEX username_idx ON sys_user (username);
CREATE UNIQUE INDEX phone_idx ON sys_user (phone);
​
SHOW INDEX FROM sys_user;

==16. 数据备份==

在开发中，项目是要迭代升级。
问题: 在上线的过程中，新版本有bug，长时间都没有成功解决。
回退版本。----> 数据要回退。
-- 1. 物理备份
-- C:\ProgramData\MySQL\MySQL Server 8.0\Data
​
-- 2. 命令行备份
-- 备份mydb库里面所有的表，索引，数据等。
-- 导出
mysqldump -uroot -proot  mydb > d:\\a.sql 
mysqldump -uroot -proot  mydb sys_user product_info > d:\\b.sql 
​
-- 导入 在连接成功前提下
-- source sql的文件路径
-- source d:\\a.sql
​
-- 3. 可视化客户端工具
-- 导出: 转储sql文件(表结构，表数据)
-- 导入: 运行指定位置的sql文件
​
-- 4. navicat----> 备份
-- 4.1 新建备份
-- 4.2 还原备份