前端玩转mysql

站长

2023年09月01日 13:44 · 阅读数 70

这篇文章会详细的介绍Mysql的使用，和一些常见的坑，以及如何使用Nodejs连接数据库，后续会开源一套基于Mysql数据库的CRM系统

0.0、 CRM系统的node框架打算用Eggjs 或者 Nestjs, 敬请期待。

0.1、前置需求

文章中使用的数据库可视化软件为 NavicatPremium 安装上的问题请找度娘或者谷爹寻求帮助
安装mysql数据库并启动，我这边的版本是8.0
这是我mac电脑上的Mysql信息

1、SQL的介绍

SQL(Structured Query Language): 结构化查询语言。其实就是定义了操作所有关系型数据库的一种规则。
通用语法规则
- SQL语句可以单行或多行书写，已分号结尾
- 可以使用空格和缩颈来增强语句的可读性
- Mysql数据库的 SQL 语句不区分大小写，关键字建议使用大写
- 单行注释： --注释内容 #注释内容(这个是mysql独有的注释方式)
- 多行注释： /※注释内容※/
SQL分类
- DDL(Data Definition Language): 数据定义语言，用来操作数据库、表、列等
- DML(Data Manipulation Language): 数据操作语言。用来对数据库中表的数据进行增、删、改
- DQL(Data Query Language): 数据查询语言。用来查询数据库中表的记录
- DCL(Data Control Language): 数据控制语言。用来定义数据库的访问权限和安全级别,以及创建用户

2、DDL操作数据库和数据表

2.1、DDL查询和创建数据库

查询所有数据库
```
  SHOW DATABASES;
```
- 如图所示
查询某一个数据库的创建语句
```
  SHOW CREATE DATABASE 数据库的名称;
```
- 如图所示
创建数据库
```
  CREATE DATABASE 数据库的名称;
```
- 如图所示如果提示数据库创建成功，左侧数据库没显示出来，那么鼠标右键链接名(test1)点击刷新即可
创建数据库(判断,如果数据库不存在则创建)
```
  CREATE DATABASE IF NOT EXISTS 数据库的名称;
```
- 如图所示

创建数据库(判断,如果数据库不存在则创建,并且指定字符集)

  CREATE DATABASE IF NOT EXISTS 数据库的名称 CHARACTER SET 字符集名称;

如图所示

前端玩转mysql

2.2、DDL修改、删除、使用数据库

修改数据库(修改字符集utf8 --> gbk)

  ALTER DATABASE 数据库名称 CHARACTER SET GBK;

删除数据库
```
  DROP DATABASE 数据库名称;
```
删除数据库(判断，如果数据库存在则删除)
```
  DROP DATABASE IF EXISTS 数据库名称
```
使用数据库
```
  USE 数据库名称;
```
查看当前使用数据库
```
 SELECT DATABASE();
```
- 如图所示

2.3、DDL操作数据库和数据表

DDL查询数据表
- 查询数据库中所有的数据表
```
  SHOW TABLES;
```
- 如图所示
- 查询表结构
```
    DESC 表名;
```
- 如图所示
  
  字段解释
  - FieId: 字段
  - Type: 字段对应的类型
  - Null: 当前字段是否为空
  - Key: 键、经过优化器评估最终使用的索引
  - Default: 默认值
  - Extra: 额外的说明信息
- 查询数据表中的字符集
```
    SHOW TABLE STATUS FROM mysql LIKE 'user';
```
DDL创建数据表
- 创建数据表
```
CREATE TABLE 表名(
    列名 数据类型 约束,
    列名 数据类型 约束,
    ...
    列名 数据类型 约束
);
```
  - 类型
    - INT 对应js中的number
    - VARCHAR 对应js中的 string
    - DATE 对应js中Date的 YYYY-MM-DD
    - TIME 对应js中Date的 HH:MM:SS
    - DATETIME 对应js中Date的 YYYY-MM-DD HH:MM:SS
    - TIMESTAMP 对应js中 new Date().getTime() 时间戳
  - 更多数据类型请参考 Mysql数据类型
  - 创建多列数据表的时候，每一列以逗号作为分隔，最后一列不要写分号，不然会报错
- 修改表名
```
    ALTER TABLE 表名 RENAME TO 新表名; 
```
- 修改表的字符集
```
    ALTER TABLE 表名 CHARACTER SET 字符集;
```
- 添加一列到表中
```
    ALTER TABLE 表名 ADD 列名 数据类型;
```
- 修改某列的数据类型
```
    ALTER TABLE 表名 MODIFY 列名 数据类型;
```
- 修改列名和数据类型
```
    ALTER TABLE 表名 CHANGE 列名 新列名 新列名的数据类型;
```
- 删除某一列
```
    ALTER TABLE 表名 DROP 列名;
```
- 如图所示
DDL删除数据表
```
   DROP TABLE 表名; 
```
DDL删除数据表(判断，如果数据表存在则删除)
```
  DROP TABLE IF EXISTS 表名;
```

3、DML表数据的增、删、改(这部分截图中的数据创建语句在 `DDL创建数据表`章节有截图书明)

DML新增表数据
- 给指定列添加数据,值的数量要和列的数量相同，且数据类型也要一一对应
```
    INSERT INTO 表名(列名,...) VALUES(值,...);
```
  - 这个时候我们就可以看到一条id 为1，username为张三，的数据被添加到了数据库
  - 截图中涉及到了 select 语句，会在DQL部分作出说明
- 给全部列添加数据
```
    INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...);
```
- 给全部列添加数据(简写，值的数量要和表中列的数量相同，且数据类型也要一一对应)
```
    INSERT INTO 表名 VALUES(值1,值2,...);
```
- 批量添加数据到表中(多个值之间要用逗号分隔，以分号结尾)
```
    INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),...;
```
```
    INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),...;
```
DML修改表数据
```
  UPDATE 表名 SET 列名1=值1, 列名2=值2,... [WHERE条件];
```
修改表数据的时候一定要写 WHERE语句，否则会修改全部列的数据
DML删除表数据
```
  DELETE FROM 表名 [WHERE条件]; 
```
删除表数据的时候一定要写 WHERE语句，否则会删除表中所有数据

------------- 数据库的`DDL、DML`常用语句、常踩的坑基本都叨叨完了，下面即将进入`DQL`部分----------------

3、DQL查询数据

前置说明: 所有的 `[]` 都代表可选语句

3.1、查询前的数据准备，我们在DDL阶段创建的数据不足以支撑我们接下来的讲解。所以需要新创建表数据

    -- 数据准备；
    -- 使用数据库；
    -- USE db1;
    -- -- 创建数据表；
    CREATE TABLE product(
            id INT, -- 商品编号，唯一标识；
            name VARCHAR(30), -- 商品名称;
            price DOUBLE, -- 商品价格;
            brand VARCHAR(30), -- 商品品牌;
            stock INT,	-- 商品库存;
            insert_time DATE -- 商品入库时间
    );
    INSERT INTO product VALUES
    (1, '华为手机',4999.99, '华为', 30, '2020-05-07'),
    (2, '诺基亚手机',999.1, '诺基亚', 31, '2020-05-08'),
    (3, '苹果手机',5999.99, '华为', 32, '2020-05-09'),
    (4, '华为电脑',8999.99, '华为', 33, '2020-06-07'),
    (5, '诺基亚电脑',3999.99, '诺基亚', 34, '2020-07-07'),
    (6, '苹果电脑',12990.99, '苹果', 35, '2020-08-07'),
    (7, '华硕电脑',6999.99, '华硕', null, '2020-09-07');
    SELECT * FROM product;

前端玩转mysql

3.2、DQL主要查询语法、顺序关系如下

    SELECT 
        字段列表
    FROM
        表名列表
    WHERE
        条件列表
    GROUP BY
        分组列表
    HAVING
        分组后的列表
    ORDER BY
        排序 升序、降序(asc,desc)
    LIMIT
        分页

查询全部
```
  SELECT * FROM 表名;
```
查询指定列数据,也可以通过制定的列查询全部数据
```
  SELECT 列1,列2,... FROM 表名;
```

去除重复查询

  SELECT DISTINCT 列1,列,... FROM 表名;

前端玩转mysql

计算列的值
```
  SELECT 列1 运算符(+-*/) 列名2 FROM 表名;
```
- 如果 sql语句写成 SELECT name, stock+10 FROM product; 那么华硕电脑的库存（stock）依旧是 null;

起别名查询

  SELECT 列1 AS 别名1, 列2 FROM 表名;

前端玩转mysql

起别名查询(简写，列名和别名之间留空格)
```
  SELECT 列1 别名1, 列2 FROM 表名;
```

3.2.1、DQL表数据查询---条件查询

语法

  SELECT 列名 FROM 表名 [WHERE 条件];

查询条件类别请参考
- Mysql WHERE句子1
- Mysql WHERE句子2

接下来我们会通过一些栗子🌰来对工作中常用的一些WHERE条件语句做出说明

1、查询库存大于33的商品；

    SELECT * FROM product WHERE stock>33;

2、查询品牌为诺基亚的商品；

    SELECT * FROM product WHERE brand='诺基亚';

3、查询金额在5000-10000之间的商品；

方法一
    SELECT * from product WHERE price>5000 AND price <1000;
方法二
    SELECT * FROM product WHERE price BETWEEN 5000 AND 10000;

4、查询库存为 30，33，35的商品信息

方法一
    SELECT * FROM product WHERE stock = 30 OR stock = 33 OR stock = 35; 
方法二
    SELECT * FROM product WHERE stock IN(30,33,35);

5、查询库存为 null的商品

    SELECT * FROM product WHERE stock IS NULL;

6、查询库存不为null的商品

    SELECT * FROM product WHERE stock IS NOT NULL;

7、查询商品名称已诺基亚开头的商品

    SELECT * FROM product WHERE name LIKE '诺基亚%';

8、查询商品名称包含为的商品；

    SELECT * FROM product WHERE name LIKE '%为%';

9、-- 查询商品名称为5个字符的商品(结果是诺基亚手机和诺基亚电脑俩条数据)；
```
    SELECT * FROM product WHERE name LIKE '_____';
```

3.2.2、DQL表数据查询---聚合函数查询

什么是聚合函数，他能做什么
- 聚合函数就是将一列数据作为一个整体，进行纵向计算

语法(WHERE是可选条件)

  SELECT 函数名(列名) FROM 表名 [WHERE条件]

聚合函数方法功能分类
- Mysql 聚合函数
接下来我们依旧会通过一些栗子🌰来对工作中常用的一些聚合函数做出说明
- 1. 查询product中的商品总条数
```
SELECT COUNT(*) FROM product;
```
- 1. 获取商品中最高价格的商品； `` SELECT MAX(price) FROM product;
- 1. 获取最低库存；
```
SELECT MIN(stock) FROM product;
```
- 1. 获取总库存(对商品库存进行求和)
```
SELECT SUM(stock) FROM product;
```
- 1. 获取品牌为诺基亚的商品库存总和；
```
SELECT SUM(stock) FROM product WHERE brand='诺基亚';
```

3.2.3、DQL表数据查询---排序查询

什么是排序查询，他能做什么
- 主要是将表中列的数据进行升序降序的排列
语法
```
    SELECT 列1,列2,... FROM 表名 [WHERE条件] ORDER BY 列名1 排序方式, 列名2 排序方式,...
```
- WHERE是可选条件
- 排序只有俩种方式，升序(ASC)降序(DESC),默认的排序方式是升序(ASC)
- 在同一张表中可以对多个列进行排序,若果有多个排序条件，只有当前面的条件一样时，才会判断第二个条件
接下来我们依旧会通过一些栗子🌰来对工作中常用的一些排序查询做出说明
- 1. 按照商品的升序进行排序
```
    SELECT * FROM product ORDER BY stock ASC;
```
- 1. 查询商品名称中包含手机的信息，对金额进行降序排序；
```
SELECT * FROM product WHERE name like '%手机%' ORDER BY price DESC; 
```
- 1. 按照金额进行升序排序，如果金额相同，那么就按库存降序进行排序；
```
SELECT * FROM product ORDER BY price ASC, stock DESC;
```

3.2.4、DQL表数据查询---分组查询

语法

  SELECT 列名列表 FROM 表名 [WHERE条件] GROUP BY 分组列名 [HAVING分组后的条件筛选] [ORDER BY 分组后的排序]

栗子时间到，接下来我们依旧会通过一些栗子🌰来对工作中常用的一些分组查询做出说明
- 1. 按照品牌分组，获取每组商品的总金额；
```
SELECT brand, SUM(price) FROM product GROUP BY brand;
```
- 1. 对金额大于5000元的商品进行品牌分组，获取每组商品的总金额；
```
SELECT brand, SUM(price) FROM product WHERE price>5000 GROUP BY brand;
```
- 1. 对金额大于5000元的商品进行品牌分组，获取每组商品的总金额，只显示总金额大于8000元的商品
```
SELECT brand, SUM(price) getSum FROM product WHERE price >5000 GROUP BY brand HAVING getSum >8000
```
- 1. 对金额大于5000元的商品进行品牌分组，获取每组商品的总金额，只显示总金额大于8000元的商品,并按照总金额降序进行排序；
```
SELECT brand, SUM(price) getSum FROM product WHERE price>5000 GROUP BY brand HAVING getSum>8000 ORDER BY getSum DESC;
```

3.2.4、DQL表数据查询---分页查询

语法

  SELECT 列名列表 FROM 表名 [WHERE条件] [GROUP BY 分组列名] [HAVING 分组后的过滤条件] [ORDER BY 排序(ASC、DESC)] LIMIT N,M 从第N条记录开始，返回M条记录;

栗子时间到，接下来我们依旧会通过一些栗子🌰来对工作中常用的一些分页查询做出说明
- 1. 每页显示多少条-- 显示三条数据;
```
SELECT * FROM product LIMIT 0 , 3;
```
  - 现在product表中一共有7条数据，当我们给 limit 传入0,3时，他会显示前三条数据
  - 当我们给 limit 传入3,3时，他会显示第四-第六这三条数据
  - 当我们给 limit 传入6,3时，他只会显示第七条数据
  - 所以limit的第一个参数是从0开始的
  - 如果limit查询条数大于总条数，那么只显示剩余的总条数

------------- 数据库的`DQL`常用语句、常踩的坑基本也都叨叨完了，下面即将进入`约束`部分----------------

4、约束

什么是排序查询
- 约束主要是针对表中的列做出一系列限制。保证数据的正确性、有效性以及完整性！！！
约束的分类
- PRIMARY KEY 主键约束每张表只允许一个主键且列不允许重复，也不允许出现空值
- AUTO_INCREMENT 自增约束，搭配 PRIMARY KEY AUTO_INCREMENT时生效，在添加数据的时候，如果改列写入null，那么它会从1开始自增
- UNIQUE 唯一约束，表中改列的数据不能重复
- NOT NULL 非空约束表中的列数据不能为null
- FOREIGN KEY 外键约束用来加强两个表（主表和从表）的一列或多列数据之间的连接
- FOREIGN KEY ON UPDATE CASCADE 外键联级更新
- FOREIGN KEY ON DELETE CASCADE 外键联级删除

栗子时间到，接下来我们依旧会通过一些栗子🌰来对工作中常用的一些约束做出说明

主键约束
- 创建学生表(id、name、age) age id设为主键
```
CREATE TABLE student(
 id INT PRIMARY KEY,
 name VARCHAR(30),
 age INT
);
```
  - INSERT INTO student VALUES(null, "张三",33); 这样写会报错的，列添加了主键后，值不允许为null
- 删除主键
```
ALTER TABLE student DROP PRIMARY KEY;
```
- 给列单独添加组主键
```
ALTER TABLE student MODIFY id INT PRIMARY KEY; 
```
主键自增约束
- 创建学生表(id、name、age) age id设为主键自增
```
CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30),
 age INT
);
```
  - INSERT INTO student VALUES(null, "张三",33); 这样写是可以的，因为设置了主键自增约束，最后结果是 1, 张三, 22
- 删除自增约束
```
ALTER TABLE student MODIFY id INT;
```
- 给列单独添加自增约束
```
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
```
唯一约束
- 创建学生表(id、name、age) age id设为主键自增,age设置唯一
```
CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30),
 age INT UNIQUE
);
```
  - INSERT INTO student VALUES(null, "张三",33);,INSERT INTO student VALUES(null, "张三",33); 当对唯一约束age添加相同的数据，数据会添加失败
- 删除唯一约束
```
ALTER TABLE student DROP INDEX age;
```
- 给列单独添加唯一约束
```
ALTER TABLE student MODIFY age INT UNIQUE;
```
  - 在删除唯一约束后给在表中的列添加了相同的值，那么再对这个列设置唯一约束了
非空约束
- 创建学生表(id、name、age) age id设为主键自增,age设置唯一,name设置非空
```
CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30) NOT NULL,
 age INT UNIQUE
);
```
  - INSERT INTO student VALUES(null, null,33);这样写会报错
- 删除非空约束
```
ALTER TABLE student MODIFY name varchar(30);
```
- 给列单独添加非空约束
```
ALTER TABLE student MODIFY name  varchar(30) NOT NULL;
```
  - 在删除非空约束后添加null值，继续对null列添加非空约束在Mysql5.7版本不会报错，会有警告提示，在mysql8.x中添加会报错

外键约束

什么是外键约束，它能做什么
- 当表与表之间有关联的时候，如果没有相关的数据约束，那么就无法保证数据的准确性
- 比如人和人的指纹分别属于俩张表，我们需要把人和人的指纹关联起来，这个时候就需要约束来把俩张表关联起来，从而保证数据的准确性

语法

  CREATE TABLE 表名(
      列名 数据类型 约束,
      CONSTRAINT 外键名 FOREIGN KEY(本表外键列名) REFERENCES 主表名(主表主键列名)
  );
  -- 创建用户表；
   CREATE TABLE user(
           id INT PRIMARY KEY AUTO_INCREMENT,
           name VARCHAR(30) NOT NULL UNIQUE,
           age INT NOT NULL UNIQUE
   );
   -- -- 添加数据；
   INSERT INTO user VALUES(null, "张三",33), (null, "李四", 23);

   -- 创建指纹表，并且添加外键；
   CREATE TABLE fingerprint(
           id INT PRIMARY KEY AUTO_INCREMENT,
           number VARCHAR(40),
           uid INT,
           CONSTRAINT fu_fk1 FOREIGN KEY (uid) REFERENCES user(id)
   );
   INSERT INTO fingerprint VALUES(null, '12234',1),(null, "2234",2);
   SELECT * FROM fingerprint;

前端玩转mysql

图中的uid对应的就是 user表中的id，也就是说 fingerprint 表中id为1的数据已经和user表中id为1的数据关联起来了。
如果在 fingerprint 表中添加一条数据关联一个user表中没有的主键，那么会报错
如果在 user表中删除id为2的数据，会报错，因为 id为2的数据已经被 fingerprint 表关联了

删除外键

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名
  ALTER TABLE fingerprint DROP FOREIGN KEY fu_fk1;

单独添加外键

  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(本表外键列名) REFERENCES 主表名(主表主键列名)
  ALTER TABLE fingerprint ADD CONSTRAINT fu_fk1 FOREIGN KEY (uid) REFERENCES user(id);

------------- 数据库的`约束`常用语句、常踩的坑基本也都叨叨完了，下面即将进入`多表操作`部分----------------

5、多表操作

什么是多表操作
- 就是有多张表，表与表之间有一定的关联关系，这种关联关系需要外键来约束
多表操作的类型
- 一对一
- 一对多
- 多对多

5.1、一对一

适用场景
- 比如人和人的指纹，一个人只能有一套指纹，一套指纹只能对应一个人
- 再比如人和身份证，一个人只能有一个身份证，一个身份证只能对应一个人
建表原则
- 在任意一个表建立一个外键，去关联另一张表的主键

举个栗子🌰

  -- 创建person表；
  CREATE TABLE person(
          id INT PRIMARY KEY AUTO_INCREMENT, -- 唯一标识 编号；
          name VARCHAR(30) NOT NULL					-- 姓名
  );
  INSERT INTO person VALUES (null, "张三"), (null, "李四");

  -- 创建身份证表 card 并且设置外键关联person表中的id；
  CREATE TABLE card(
          id INT PRIMARY KEY AUTO_INCREMENT, -- 唯一标识 编号；
          naem VARCHAR(200) NOT NULL UNIQUE, -- 身份证号码是唯一的
          pid INT,													-- 外键列， pid代表 person表中的id
          CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
  );
  INSERT INTO card VALUES (null, "12345",1), (null,"22345",2);

这个时候一对一的关联就创建出来了，这个时候你会说我咋知道他们关联起来了呐，不慌，NavicatPremium提供了一个可视化的表关系图

5.2、一对多

适用场景
- 用户和订单，一个用户可以有多个订单
- 产品分类和产品，一个产品分类下可以有多个产品
建表原则
- 在多的一方建立外键，关联一的一方的主键

举个栗子🌰

  -- 创建用户表；
  CREATE TABLE user(
  id INT PRIMARY KEY AUTO_INCREMENT, -- 唯一标识，编号
  name VARCHAR(30)									-- 用户名称
  );
  -- 用户表中添加数据；
  INSERT INTO user VALUES (null, '张三'), (null, '李四');
  -- 创建订单表；
  CREATE TABLE orderList(
          id INT PRIMARY KEY AUTO_INCREMENT,  -- 唯一标识，编号
          name VARCHAR(30),										-- 商品名称
    uid INT, 													  -- 外键列， uid表示关联 user表中的id;	
          CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES user(id)
  );
  -- 订单表中添加数据
  INSERT INTo orderList VALUES (null, '华为手机',1),(null, '华为电脑',1),(null, '爱疯手机',2),(null, '锤子手机',2);

接下来我们瞅瞅一对多的关系图

5.3、多对多

适用场景
- 学生和课程，一个学生可以选择多门课程，同时课程也可被多个学生选择
建表原则
- 多对多的情况下只能依托第三张中间表来关联需要关联的表，中间表至少要包含俩列，这俩列作为中间表的外键，去关联对应表的主键

举个栗子🌰

  -- 创建db2数据库；
  CREATE DATABASE db2;
  -- 使用 db2数据库；
  USE db2;
  -- 创建person表；
  CREATE TABLE student(
          id INT PRIMARY KEY AUTO_INCREMENT, -- 唯一标识，编号；
          name VARCHAR(30)									 -- 用户名
  );
  -- -- 添加数据；
  INSERT INTO student VALUES (null, "张三"), (null, "李四");

  -- 创建课程表；
  CREATE TABLE course(
          id INT PRIMARY KEY AUTO_INCREMENT, -- 唯一标识，编号；
          name VARCHAR(30)									 -- 课程名称；
  );
  INSERT INTO course VALUES (null, "语文"), (null, "数学");

  -- 创建第三章中间表；
  CREATE TABLE stu_course(
          id INT PRIMARY KEY AUTO_INCREMENT,	-- 唯一标识，编号；
          sid INT,        -- 外键列 sid表示关联 student 表的主键id
          cid INT,				-- 外键列 cid表示关联 course 表的主键id
          CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
          CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
  );
  -- 添加数据
  INSERT INTO stu_course VALUES (null, 1,1),(null, 1,2),(null, 2,1),(null, 2,2);

中间表stu_course的关联结构是这样滴
- 张三 --> 语文
- 张三 --> 数学
- 李四 --> 语文
- 李四 --> 数学
关系图如下

5.4、多表查询

先准备一波数据

  CREATE TABLE user (
          id INT PRIMARY KEY auto_increment,
          NAME VARCHAR(20) NOT NULL,
          age INT NOT NULL
   ); 
   INSERT INTO user VALUES 
   (null, "张三", 23),
   (null, "李四", 24),
   (null, "王五", 25),
   (null, "赵六", 26);



   CREATE TABLE orderlist (
          id INT PRIMARY KEY auto_increment,
          number VARCHAR(30),
          uid INT,
          CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES user(id)
   );
  -- -- 添加数据；
   INSERT INTO orderlist VALUES 
   (NULL, "001", 1),
   (NULL, "002", 1),
   (NULL, "003", 2),
   (NULL, "004", 2),
   (NULL, "005", 3),
   (NULL, "006", 3),
   (NULL, "007", null);
  -- 
   CREATE TABLE category (
   id	 INT PRIMARY KEY auto_increment,
   NAME VARCHAR(20) NOT NULL
   );
   INSERT INTO category VALUES 
   (null,"手机数码"),
   (null,"电脑办公"),
   (null,"烟酒茶糖"),
   (null,"鞋靴箱包");

   CREATE TABLE product (
          id INT PRIMARY KEY auto_increment,
          NAME VARCHAR(30) NOT NULL,
          cid INT,
          CONSTRAINT pc_fk1 FOREIGN KEY(cid) REFERENCES category(id)
   ); 
   INSERT INTO product VALUES 
   (null,"华为手机", 1),
   (null,"小米手机", 1),
   (null,"联想电脑", 2),
   (null,"苹果电脑", 2),
   (null,"中华香烟", 3),
   (null,"玉溪香烟", 3),
   (null,"计生用品", null);

  -- 用户 -- 商品 中间表对应关系;
   CREATE TABLE us_pro (
          upid INT PRIMARY KEY auto_increment,
          uid INT, -- 和用户表建立关系；
          pid INT,	-- 和商品表建立关系；
          CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES user(id),
          CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
   );
   INSERT INTO us_pro
   VALUES
          ( null, 1, 1 ),
          ( null, 1, 2 ),
          ( null, 1, 3 ),
          ( null, 1, 4 ),
          ( null, 1, 5 ),
          ( null, 1, 6 ),
          ( null, 1, 7 ),
          ( null, 2, 1 ),
          ( null, 2, 2 ),
          ( null, 2, 3 ),
          ( null, 2, 4 ),
          ( null, 2, 5 ),
          ( null, 2, 6 ),
          ( null, 2, 7 ),
          ( null, 3, 1 ),
          ( null, 3, 2 ),
          ( null, 3, 3 ),
          ( null, 3, 4 ),
          ( null, 3, 5 ),
          ( null, 3, 6 ),
          ( null, 3, 7 ),
          ( null, 4, 1 ),
          ( null, 4, 2 ),
          ( null, 4, 3 ),
          ( null, 4, 4 ),
          ( null, 4, 5 ),
          ( null, 4, 6 ),
          ( null, 4, 7 );

结构关联图是这样的

5.4.1、内连接查询

查询原理
- 内连接查询是查询有交集部分的数据

举个栗子🌰

查询用户信息和对应的订单信息

  -- 显示内连接
  SELECT * FROM user u INNER JOIN orderlist o ON u.id = o.uid;
  -- 隐示内连接
  SELECT * FROM user u, orderlist o WHERE u.id = o.uid;

前端玩转mysql

5.4.2、外连接查询

左外连接查询
- 查询原理
  - 查询左张表的全部数据，以及俩张表有交集的数据
右外连接查询
- 查询原理
  - 查询右张表的全部数据，以及俩张表有交集的数据

举个栗子🌰

  -- 查询用户表全部的数据以及订单表中有交集的数据;    
  -- 左外连接查询
  SELECT * FROM USER u LEFT OUTER JOIN orderlist o ON u.id=o.uid;
  -- 右外连接查询
  SELECT * FROM orderlist o RIGHT OUTER JOIN USER u ON u.id=o.uid;

前端玩转mysql

5.4.3、子查询

查询原理
- 就是把当前表的查询结果继续当做查询条件进行查询

举个栗子🌰

  -- 单行单列
  -- 比如我们要查询用户表中年龄最大的用户
  SELECT * FROM user WHERE age=(SELECT MAX(age) FROM user);
  
  -- 多行单列；
  -- 查询张三和李四的订单表；
  SELECT * FROM orderlist WHERE uid in (SELECT u.id FROM user u WHERE id in (1,2));
  
  -- 多行多列；
  -- 查询订单表中id>4的订单信息和对应的用户信息；
  -- 外连接查询
   SELECT * FROM user u RIGHT JOIN (SELECT * FROM orderlist WHERE id>4) o  on u.id = o.uid;
  -- 内连接查询
   SELECT * FROM user u ,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id = o.uid;

-----------工作中涉及到的数据库CRUD知识大概就这些吧，欢迎大家提问------------

-----------更新------------

转载自:https://juejin.cn/post/6959851582984814623

前端玩转mysql

这篇文章会详细的介绍Mysql的使用，和一些常见的坑，以及如何使用Nodejs连接数据库，后续会开源一套基于Mysql数据库的CRM系统

0.0、 CRM系统的node框架打算用Eggjs 或者 Nestjs, 敬请期待。

0.1、前置需求

1、SQL的介绍

2、DDL操作数据库和数据表

2.1、DDL查询和创建数据库

2.2、DDL修改、删除、使用数据库

2.3、DDL操作数据库和数据表

字段解释

3、DML表数据的增、删、改(这部分截图中的数据创建语句在 DDL创建数据表章节有截图书明)

------------- 数据库的DDL、DML常用语句、常踩的坑基本都叨叨完了，下面即将进入DQL部分----------------

3、DQL查询数据

前置说明: 所有的 [] 都代表可选语句

3.1、查询前的数据准备，我们在DDL阶段创建的数据不足以支撑我们接下来的讲解。所以需要新创建表数据

3.2、DQL主要查询语法、顺序关系如下

3.2.1、DQL表数据查询---条件查询

3.2.2、DQL表数据查询---聚合函数查询

3.2.3、DQL表数据查询---排序查询

3.2.4、DQL表数据查询---分组查询

3.2.4、DQL表数据查询---分页查询

------------- 数据库的DQL常用语句、常踩的坑基本也都叨叨完了，下面即将进入约束部分----------------

4、约束

------------- 数据库的约束常用语句、常踩的坑基本也都叨叨完了，下面即将进入多表操作部分----------------

5、多表操作

5.1、 一对一

5.2、一对多

5.3、多对多

5.4、 多表查询

5.4.1、内连接查询

5.4.2、外连接查询

5.4.3、子查询

-----------工作中涉及到的数据库CRUD知识大概就这些吧，欢迎大家提问------------

-----------更新------------

3、DML表数据的增、删、改(这部分截图中的数据创建语句在 `DDL创建数据表`章节有截图书明)

------------- 数据库的`DDL、DML`常用语句、常踩的坑基本都叨叨完了，下面即将进入`DQL`部分----------------

前置说明: 所有的 `[]` 都代表可选语句

------------- 数据库的`DQL`常用语句、常踩的坑基本也都叨叨完了，下面即将进入`约束`部分----------------

------------- 数据库的`约束`常用语句、常踩的坑基本也都叨叨完了，下面即将进入`多表操作`部分----------------

5.1、一对一

5.4、多表查询