Sql 入门教程 - Githubissues

查询数据

1. 条件查询

查询 cus_user 表 7 月 28 以后注册的用户（use AND 与 OR）

SELECT
    *
FROM
    cus_user
WHERE
    create_time >= '2020-07-28 00:00:00'

查询 cus_user 表已知性别的用户(性别 0-未知 1-男性 2-女性),(use IN)

SELECT
    *
FROM
    cus_user
WHERE
    id BETWEEN 160
    AND 170

查询 cus_user 表 id 为 160-170 的用户的部分信息（use BETWEEN）

SELECT
    *
FROM
    cus_user
WHERE
    id BETWEEN 160
    AND 170

条件查询的关键字 and、or、in、between、not,多个条件查询，括号改变优先级

2.投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用 SELECT 列 1, 列 2, 列 3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

投影查询，并将结果列名重命名

SELECT
    nick_name,
    mobile phone,
    gender,
    avator avatar,
    create_time
FROM
    cus_user
WHERE
    id BETWEEN 160
    AND 170

3.排序查询

获取用户yanyue404的聊天记录信息并按 create_time降序排列（use ORDER BY）

SELECT
    user_type,
    to_user_type,
    to_nick_name,
    message,
    create_time
FROM
    msg_user
WHERE
    nick_name = 'yanyue404'
ORDER BY
    create_time DESC

DESC 是递减的意思，与之对应的是 ASC 递增, ORDER BY create_time DESC 意思是按照(BY)create_time 字段值递减(DESC)的顺序对查询结果排序

4.分页查询

查询所有的结果并加入行数序号列 1，2，3...

SET @rownum = 0;
SELECT
    @rownum := @rownum + 1 AS ROW,
    user_type,
    to_user_type,
    to_nick_name,
    message,
    create_time
FROM
    msg_user
WHERE
    nick_name = 'yanyue404'
ORDER BY
    create_time DESC

把结果页分页，每页 10 条记录，要获得第 1 页的记录,LIMIT <M> OFFSET <N>，索引从 0 开始

SET @rownum = 0;
SELECT
    @rownum := @rownum + 1 AS ROW,
    user_type,
    to_user_type,
    to_nick_name,
    message,
    create_time
FROM
    msg_user
WHERE
    nick_name = 'yanyue404'
ORDER BY
    create_time DESC
    LIMIT 10 OFFSET 0

查询第二页,跳过前 10 条记录，数据从 10 号开始采集，offset 设为 20

limit 10 OFFSET 10

分页查询的关键在于，首先要确定每页需要显示的结果数量 pageSize（这里是 10），然后根据当前页的索引 pageIndex（从 1 开始），确定 LIMIT 和 OFFSET 应该设定的值:

LIMIT 总是设定为 pageSize； OFFSET 计算公式为 pageSize * (pageIndex - 1)。 ......

查询第 5 页，offset 设为 40

limit 10 OFFSET 40

在 MySQL 中，LIMIT 10 OFFSET 20 还可以简写成 LIMIT 20, 10

5.聚合查询

查询 msg_user 表有多少条 yanyue404 发送的聊天记录

SELECT
    COUNT(*)
FROM
    msg_user
WHERE
    nick_name = 'yanyue404'

COUNT(*)
45

count() 表示查询表所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是 COUNT()。通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：

SELECT
    COUNT(*) num
FROM
    msg_user
WHERE
    nick_name = 'yanyue404'

num
45

除 count 以外的聚合函数函数说明 SUM 计算某一列的合计值，该列必须为数值类型 AVG 计算某一列的平均值，该列必须为数值类型 MAX 计算某一列的最大值 MIN 计算某一列的最小值

注意，MAX()和 MIN()函数并不限于数值类型。如果是字符类型，MAX()和 MIN()会返回排序最后和排序最前的字符。

6. 多表

SELECT
    c.id cid,
    a.id aid,
    a.nick_name aname,
    c.nick_name cname
FROM
    cus_user c,
    agt_user a

多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有 100 行记录的表进行笛卡尔查询将返回 1 万条记录，对两个各自有 1 万行记录的表进行笛卡尔查询将返回 1 亿条记录。

添加 WHERE 条件后结果集的数量大大减少了：

SELECT
    c.id cid,
    a.id aid,
    a.nick_name aname,
    c.nick_name cname
FROM
    cus_user c,
    agt_user a
WHERE
    c.account_id = a.account_id
    AND a.account_id = '172'

7.连接查询

表的连接分成好几种类型。

内连接（inner join）
外连接（outer join）
- 左连接（left join）
- 右连接（right join）
- 全连接（full join）

内连接 (INNER JOIN)

选出 cus_user与 agt_user 两张表 account_id 一致（自己是代理人，自己又是客户）

SELECT
    c.id cid,
    a.id aid,
    a.nick_name,
    a.agent_code agentCode
FROM
    cus_user c
    INNER JOIN agt_user a ON c.account_id = a.account_id

注意 INNER JOIN 查询的写法是：

先确定主表，仍然使用 FROM <表 1>的语法；
再确定需要连接的表，使用 INNER JOIN <表 2>的语法；
然后确定连接条件，使用 ON <条件...>，这里的条件是 s.class_id = c.id，表示 students 表的 class_id 列与 classes 表的 id 列相同的行需要连接；
可选：加上 WHERE 子句、ORDER BY 等子句。

右连接（RIGHT JOIN）

SELECT
    c.id cid,
    a.id aid,
    a.nick_name,
    a.agent_code agentCode
FROM
    cus_user c
    RIGHT JOIN agt_user a ON c.account_id = a.account_id

换成右连接比内连接多了一条数据，但是 cid 为 Null。

cid	aid	nick_name	agentCode
Null	77777	测试积分	11340100063

因为根据 ON 条件 c.account_id = a.account_id，当 a 表 account_id 为 '77777'的时候，c 表没有与之相对应的数据，cid 字段就被 Null 填充。

有 RIGHT JOIN，就有 LEFT JOIN，以及 FULL JOIN。它们的区别是：

只返回两张表匹配的记录，这叫内连接（inner join）。
返回匹配的记录，以及表 A 多余的记录，这叫左连接（left join）。
返回匹配的记录，以及表 B 多余的记录，这叫右连接（right join）。
返回匹配的记录，以及表 A 和表 B 各自的多余记录，这叫全连接（full join）。

下图就是四种连接的图示：

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充。

这四种连接，又可以分成两大类：内连接（inner join）表示只包含匹配的记录，外连接（outer join）表示还包含不匹配的记录。所以，左连接、右连接、全连接都属于外连接。

JOIN 查询仍然可以使用 WHERE 条件和 ORDER BY 排序。

8. 模糊查询

搜索以 资讯 开头的所有字符串

SELECT * FROM `sys_menu`  WHERE NAME LIKE '资讯%'

搜索以 资讯 结尾的所有字符串

SELECT * FROM `sys_menu`  WHERE NAME LIKE '%资讯'

搜索含有 资讯 的所有字符串

SELECT * FROM `sys_menu`  WHERE NAME LIKE '%资讯%'

修改数据

1. INSERT

当我们需要向数据库表中插入一条新记录时，就必须使用 INSERT 语句。

INSERT 语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

INSERT INTO `xfej-admin`.`cus_user` ( `account_id`, `user_code`, `nick_name`, `gender`, `avator`, `agt_flag`, `del_flag` )
VALUES
    ( 233, '2a73d53333e64509b4bcced82bf1210e', '测试', '1', 'https://thirdwx.qlogo.cn/mmopen/vi_32/Q3auHgzwzM6MrzD7a6kIs22ym8QAddMibVF7WLRFxN5O6R99RwUcTBk7SicPUjXLkPMq3Y49wE3ul5uINQKF1tRw/132', '0', '0' );

还可以一次性添加多条记录，只需要在 VALUES 子句中指定多个记录值，每个记录是由()(...此处已省略详细的新增数据项)包含的一组值:

INSERT INTO `xfej-admin`.`cus_user` ( `account_id`, `user_code`, `nick_name`, `gender`, `avator`, `agt_flag`, `del_flag` )
VALUES
    (...), (...);

2. UPDATE

如果要更新数据库表中的记录，我们就必须使用 UPDATE 语句。

UPDATE 语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

例如：我们修改刚才新建的 nick_name为"测试"的那条数据，查看发现，主键 id 已经新增为 199，让我们来更新它的 mobile与gender。

UPDATE cus_user
SET gender = 2,
mobile = '13112341234'
WHERE
    id = 199;

更新成功 MySQL 会返回: Affected rows: 1 时间: 0.008s，可以看到一行受到了影响，此外还可以借助 WHERE 一次更新多条数据。

如果 WHERE UPDATE 语句不会报错，也不会有任何记录被更新

更新 id 为 200，201，202 的数据

UPDATE cus_user
SET gender = 2,
mobile = '13112341234'
WHERE
    id >= 200
    AND id <= 202;

警告： UPDATE 语句可以没有 WHERE 条件，当直接执行的时候整个表的所有记录都会被更新。所以，在执行 UPDATE 语句时要非常小心，最好先用 SELECT 语句来测试 WHERE 条件是否筛选出了期望的记录集，然后再用 UPDATE 更新。

3.DELETE

如果要删除数据库表中的记录，我们可以使用 DELETE 语句。

DELETE 语句的基本语法是：

DELETE FROM <表名> WHERE ...;

例如：我们删除上面创建的 id 为 199 的数据：

DELETE
FROM
    cus_user
WHERE
    id = 199

与 UPDATE 的使用方法一样，借助 WHERE 我们就可以一次删除表中的一条或多条记录。

select COUNT(*) from agt_main_user where agt_id = 121
SELECT * from agt_user
where nick_name like '胖头鱼%'

yanyue404 / blog

Sql 入门教程 #163

目录