DQL（单表）

一、数据查询

1、环境准备

CREATE TABLE IF NOT EXISTS `products` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    brand VARCHAR(20),
    title VARCHAR(100) NOT NULL,
    price DOUBLE NOT NULL,
    score DECIMAL(2,1),
    voteCnt INT,
    url VARCHAR(100),
    pid INT
);

2、数据准备

https://lencamo.github.io/data_hub/phone.json

const mysql = require('mysql2')
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: 'mysql666.',
  database: 'db_2'
})

const statement = `INSERT INTO products SET ?;`
const phoneJson = require('./phone.json')

for (let phone of phoneJson) {
  connection.query(statement, phone, (err, results, fields) => {
    if err throw err
    console.log('数据插入成功！')
  })
}

3、select 汇总

mysql 官方文档：SELECT Statement (opens new window)

  中括号[]中的内容可以省略，花括号{}中的内容是任选其一的，不同选项以竖线|分隔。

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

二、单表查询

select 大纲

  中括号[]中的内容可以省略，花括号{}中的内容是任选其一的，不同选项以竖线|分隔。

下面是我抽取的基础知识 select 大纲

SELECT
    select_expr [, select_expr] ...
    [FROM table_references]
    [WHERE where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ...]
    [HAVING where_condition]

1、基础查询

-- 查看所有字段
SELECT * FROM products;
-- 查看指定字段
SELECT id, brand, title, price FROM products;

-- 字段别名（AS关键字是可以省略的）
SELECT id AS phoneId, brand phoneBrand, title, price FROM products;

2、条件查询（where）

• 比较运算符

SELECT * FROM products WHERE price < 1000;
SELECT * FROM products WHERE brand != '苹果';

• 逻辑运算符 if( …… )

SELECT * FROM products WHERE brand = '华为' && price > 7000;
SELECT * FROM products WHERE brand = '华为' AND price > 7000;

SELECT * FROM products WHERE brand = '华为' || price > 7000;
SELECT * FROM products WHERE brand = '华为' OR price > 7000;

• 区间 和 枚举

-- 区间（单字段）
SELECT * FROM products WHERE price BETWEEN 6000 AND 7000;
-- 枚举（多字段）
SELECT * FROM products WHERE brand IN ('小米','oppo');

• 模糊查找

  模糊查询 like 类似与 正则 regex，可以筛选出目标数据

% 表示匹配任意多个字符；_表示匹配任意一个字符

SELECT * FROM products WHERE title LIKE 'v%';
SELECT * FROM products WHERE title LIKE '__m%';

3、结果排序（order by）

这肯定是有一定用处的

SELECT * FROM products WHERE price BETWEEN 3000 AND 5000
  ORDER BY score DESC;

4、展示限制（limit）

这是真实开发中一个非常常见的需求，也叫“分页查询”

-- 方式1
-- LIMIT size OFFSET (page-1)*size
SELECT * FROM products WHERE brand = '华为'
  LIMIT 10 OFFSET 0;
SELECT * FROM products WHERE brand = '华为'
  LIMIT 10 OFFSET 10;
SELECT * FROM products WHERE brand = '华为'
  LIMIT 10 OFFSET 20;

-- 方式2
-- LIMIT (page-1)*size,size
SELECT * FROM products WHERE brand = '华为'
  LIMIT 0, 10;
SELECT * FROM products WHERE brand = '华为'
  LIMIT 10, 10;
SELECT * FROM products WHERE brand = '华为'
  LIMIT 20, 10;

三、聚合查询

  聚合函数通常与 GROUP BY 子句一起使用，将值分组为子集

  HAVING 通常在 GROUP BY 值分组的子集结果的后面使用，对子集结果进行条件查询

1、定义 🎈

  聚合函数 表示对 值 🎈 的集合 进行的 组（集合）函数 —— 结果统计。

简单的说就是利用 mysql 提供的“聚合函数”对查询结果进行快速处理（虽然 js 中也可以处理）

-- 值的集合（单字段）
SELECT price FROM products WHERE brand = '苹果';
-- 函数处理
SELECT AVG(price) AS '平均值' FROM products WHERE brand = '苹果';

2、常见函数

mysql 官方文档：Aggregate Function Descriptions (opens new window)

函数	作用		函数	作用
COUNT()	集合元素数目
AVG()	平均值		JSON_ARRAYAGG()	返回 json 数组（5.7.22）
SUM()	总和		JSON_OBJECTAGG()	返回 json 对象（5.7.22）
MAX()、MIN()	最大/小值

3、使用示例

  在使用聚合函数时，其结果是一个统计的值。

  不同的是，MAX()、MIN()可以有附属字段，并且值为最先匹配到的记录 record（即使匹配到了多条）。

SELECT count(*) AS '统计' FROM products WHERE brand = '小米';

-- 本质理解
SELECT  price, brand, title FROM products WHERE price = 9999; # 两个结果
SELECT MAX(price) AS '最贵手机', brand, title FROM products; # 一个结果

4、分组统计 ✨

  不同于前面的直接统计只有一个结果，使用 group by 后再统计会得到多个结果（每个组的统计结果）。

• 常规使用

-- 集合分组
SELECT * FROM products GROUP BY brand;
SELECT brand FROM products GROUP BY brand;
-- 分组统计
SELECT brand, MAX(price) AS '最贵手机', ROUND(AVG(price),2) AS '平均价格' FROM products
  GROUP BY brand;

• 约束条件

  HAVING 通常在 GROUP BY 值分组的子集结果的后面使用，对子集结果进行条件查询

SELECT brand, MAX(price) _maxPrice, ROUND(AVG(price),2) _avgPrice FROM products
  GROUP BY brand;

-- 分组前约束（where）
SELECT brand, MAX(price) _maxPrice, ROUND(AVG(price),2) _avgPrice FROM products WHERE brand NOT IN ("苹果")
  GROUP BY brand;

-- 分组后约束（having）
SELECT brand, MAX(price) _maxPrice, ROUND(AVG(price),2) _avgPrice FROM products
  GROUP BY brand
  HAVING _avgPrice > 2000;

四、子查询

略