# 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;

# 四、子查询

更新于 : 7/8/2024, 10:21:14 AM