MySQL 学习笔记（基础）

MySQL 学习笔记(基础)

• 基础
  • 查询数据 Select
    • 连接表
    • 使用一个查询获取数据库中所有表的MySQL行计数
      • 比较两个表不同的数据
    • case表达式
  • 插入数据(Insert)
    • 具有SELECT子句的MySQL INSERT
  • 更新表数据（UPDATE）
  • 删除表数据(DELETE)
  • 修改表结构
    • ALTER TABLE语句更改列
    • 将新的列添加到表中
    • 重命名表

基础

查询数据 Select

SELECT语句的语法：

SELECT
    column_1, column_2, ...
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

SELECT语句由以下列表中所述的几个子句组成：

• SELECT之后是逗号分隔列或星号(*)的列表，表示要返回所有列
• FROM指定要查询数据的表或视图
• JOIN根据某些连接条件从其他表中获取数据
• WHERE过滤结果集中的行
• GROUP BY将一组行组合成小分组，并对每个小分组应用聚合函数
• HAVING过滤器基于GROUP BY子句定义的小分组
• ORDER BY指定用于排序的列的列表
• LIMIT限制返回行的数量

连接表

• INNER JOIN: MySQL INNER JOIN子句将一个表中的行与其他表中的行进行匹配，并允许从两个表中查询包含列的行记录

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;

对于t1表中的每一行，INNER JOIN子句将它与t2表的每一行进行比较，以检查它们是否都满足连接条件。当满足连接条件时，INNER JOIN将返回由t1和t2表中的列组成的新行。

• LEFT JOIN: MySQL LEFT JOIN子句允许您从两个或多个数据库表查询数据

SELECT 
    t1.c1, t1.c2, t2.c1, t2.c2
FROM
    t1
        LEFT JOIN
    t2 ON t1.c1 = t2.c1;

LEFT JOIN子句允许您从匹配的左右表中查询选择行记录，连接左表(t1)中的所有行，即使在右表(t2)中找不到匹配的行也显示出来，但使用NULL值代替。相当于扩展了列

• CROSS JOIN： CROSS JOIN子句从连接的表返回行的笛卡儿乘积。

SELECT
    *
FROM
    T1
        CROSS JOIN
    T2;

假设使用CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。如果每个表有1000行，那么结果集中就有1000 x 1000 = 1,000,000行，那么数据量是非常巨大的。

使用一个查询获取数据库中所有表的MySQL行计数

获取数据库中所有表的行计数的快速方法是直接从information_schema数据库中查询数据：

SELECT
    table_name,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'db_name'
ORDER BY table_rows desc;

比较两个表不同的数据

假设有两个表：t1和t2。使用以下步骤比较两个表，并确定不匹配的记录：
首先，使用UNION语句来组合两个表中的行; 仅包含需要比较的列。返回的结果集用于比较。

SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2

第二步，根据需要比较的主键和列分组记录。如果需要比较的列中的值相同，则COUNT(*)返回2，否则COUNT(*)返回1。
请参阅以下查询：

SELECT pk, c1
FROM
 (
   SELECT t1.pk, t1.c1
   FROM t1
   UNION ALL
   SELECT t2.pk, t2.c1
   FROM t2
)  t
GROUP BY pk, c1
HAVING COUNT(*) = 1
ORDER BY pk

case表达式

MySQL CASE表达式是一个流控制结构，允许您在查询中构造条件，例如：SELECT或WHERE子句。

SELECT 
    customerName, state, country
FROM
    customers
ORDER BY (CASE
    WHEN state IS NULL THEN country
    ELSE state
END);

SELECT
    SUM(CASE
        WHEN status = 'Shipped' THEN 1
        ELSE 0
    END) AS 'Shipped',
    SUM(CASE
        WHEN status = 'On Hold' THEN 1
        ELSE 0
    END) AS 'On Hold',
    SUM(CASE
        WHEN status = 'In Process' THEN 1
        ELSE 0
    END) AS 'In Process',
    SUM(CASE
        WHEN status = 'Resolved' THEN 1
        ELSE 0
    END) AS 'Resolved',
    SUM(CASE
        WHEN status = 'Cancelled' THEN 1
        ELSE 0
    END) AS 'Cancelled',
    SUM(CASE
        WHEN status = 'Disputed' THEN 1
        ELSE 0
    END) AS 'Disputed',
    COUNT(*) AS Total
FROM
    orders;

插入数据(Insert)

具有SELECT子句的MySQL INSERT

在MySQL中，可以使用SELECT语句返回的列和值来填充INSERT语句的值。此功能非常方便，因为您可以使用INSERT和SELECT子句完全或部分复制表，如下所示:

INSERT INTO table_1
SELECT c1, c2, FROM table_2;

更新表数据（UPDATE）

UPDATE语句的语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET
    column_name1 = expr1,
    column_name2 = expr2,
    ...
WHERE
    condition

在上面UPDATE语句中：

• 首先，在UPDATE关键字后面指定要更新数据的表名。
• 其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。如果省略WHERE子句，则UPDATE语句将更新表中的所有行。
• 请注意，WHERE子句非常重要，所以不应该忘记指定更新的条件。有时，您可能只想改变一行; 但是，可能会忘记写上WHERE子句，导致意外更新表中的所有行。
• MySQL在UPDATE语句中支持两个修饰符。
  • LOW_PRIORITY修饰符指示UPDATE语句延迟更新，直到没有从表中读取数据的连接
  • LOW_PRIORITY对仅使用表级锁定的存储引擎(例如MyISAM，MERGE，MEMORY)生效。即使发生错误
  • IGNORE修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新

删除表数据(DELETE)

DELETE语句的语法：

DELETE FROM table_name
WHERE condition

在上面删除语句中

• 首先，指定删除数据的表(table_name)
• 其次，使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件，这些行记录将被删除

修改表结构

ALTER TABLE语句更改列

ALTER TABLE table1 CHANGE COLUMN c_1_name c_1__new_name INT(11) NOT NULL AUTO_INCREMENT;

将新的列添加到表中

ALTER TABLE table1 ADD COLUMN new_c DECIMAL(2,1) NULL AFTER c_1_name

重命名表

ALTER TABLE tasks RENAME TO work_items;