Mysql

以下是一些常见的MySQL命令：

连接到MySQL服务器:

mysql -u <用户名> -p

在提示符后输入密码即可连接到MySQL服务器。

显示当前可用的数据库列表:

SHOW DATABASES;

创建数据库:

CREATE DATABASE <数据库名>;

切换到使用特定数据库:

USE <数据库名>;

在这之后的所有操作都将在指定的数据库上执行。

显示数据库中的表列表:

SHOW TABLES;

创建表:

CREATE TABLE <表名> (
  <列1定义>,
  <列2定义>,
  ...
);

插入数据 into 表:

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

查询表中的数据:

SELECT <列1>, <列2>, ... FROM <表名> WHERE <条件>;

去重

SELECT DISTINCT column1, column2, ...FROM table_name;

模糊查询：

在MySQL中，可以使用LIKE运算符进行模糊查询。LIKE运算符允许在查询中使用通配符来匹配符合特定模式的数据。

以下是模糊查询的示例：

  1. 查询以特定字符串开头的数据：
SELECT * FROM table_name WHERE column_name LIKE 'prefix%';

这将返回column_name列以prefix开头的所有行。

  1. 查询以特定字符串结尾的数据：
SELECT * FROM table_name WHERE column_name LIKE '%suffix';

这将返回column_name列以suffix结尾的所有行。

  1. 查询包含特定字符串的数据：
SELECT * FROM table_name WHERE column_name LIKE '%keyword%';

这将返回column_name列中包含keyword的所有行。

  1. 查询指定长度的字符串：
SELECT * FROM table_name WHERE LENGTH(column_name) = length_value;

这将返回column_name列中长度为length_value的所有行。

  1. 使用多个通配符进行模糊查询：
SELECT * FROM table_name WHERE column_name LIKE 'prefix%suffix';

这将返回column_name列以prefix开头且以suffix结尾的所有行。

注意，模糊查询可能会对查询性能产生一定的影响。尽量避免使用通配符%作为模式的开头，以充分利用数据库的索引优化查询。

范围查询：

在MySQL中，可以使用比较运算符进行范围查询，以筛选满足特定条件的数据。

以下是MySQL中常见的范围查询示例：

  1. 查询大于某个值的数据：
SELECT * FROM table_name WHERE column_name > value;

这将返回column_name列中大于value的所有行。

  1. 查询小于某个值的数据：
SELECT * FROM table_name WHERE column_name < value;

这将返回column_name列中小于value的所有行。

  1. 查询大于等于某个值的数据：
SELECT * FROM table_name WHERE column_name >= value;

这将返回column_name列中大于等于value的所有行。

  1. 查询小于等于某个值的数据：
SELECT * FROM table_name WHERE column_name <= value;

这将返回column_name列中小于等于value的所有行。

  1. 查询某个范围内的数据：
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

这将返回column_name列中在value1和value2之间的所有行（包括value1和value2）。

  1. 查询不在某个范围内的数据：
SELECT * FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;

这将返回column_name列中不在value1和value2之间的所有行。

在范围查询中，可以根据具体需求使用不同的比较运算符来设定条件。记得在查询语句中使用正确的列名和表名。

group

group and group_concat

GROUP BY 子句和 GROUP_CONCAT 函数都与对数据进行分组操作相关。

GROUP BY 子句用于按照一个或多个列对结果进行分组。它可以将数据集根据指定的列进行分组，并对每个组进行聚合计算。常用的聚合函数如 SUM、AVG、COUNT 等可以与 GROUP BY 结合使用，以对每个组的数据进行聚合计算。

示例：

SELECT column1, column2, SUM(column3)
FROM table_name
GROUP BY column1, column2;

上述查询将根据 column1 和 column2 的值对结果进行分组，并对每个分组内的 column3 进行求和计算。例子：

image-20230731030941553

GROUP_CONCAT 函数用于将分组内的多个值连接成一个字符串，并通过指定的分隔符进行分隔。它通常用于将多个相关值合并成一个字符串。

示例：

SELECT column1, GROUP_CONCAT(column2 SEPARATOR ', ')
FROM table_name
GROUP BY column1;

上述查询将根据 column1 的值对结果进行分组，并将每个分组内的 column2 值连接成一个字符串，使用逗号加空格作为分隔符。

示例：

GROUP BY 子句和 GROUP_CONCAT 函数可以结合使用，以对数据集进行分组并连接分组内的值。

having（相当于分组里面的where）

使用 GROUP BY 时，你可以结合 HAVING 子句来过滤分组后的结果。

以下是 GROUP BY 结合 HAVING 的语法示例：

SELECT column1, COUNT(column2) AS count
FROM table_name
GROUP BY column1
HAVING count > 10;

在上述示例中，我们使用 GROUP BY 对 column1 进行分组，并计算每个组中的行数（使用 COUNT(column2)）。然后，我们使用 HAVING 子句筛选出行数大于 10 的组。

with rollup

在 MySQL 中，使用 WITH ROLLUP 关键字可以在 GROUP BY 查询的结果中添加额外的行，用于汇总分组数据。

以下是使用 GROUP BY 结合 WITH ROLLUP 的语法示例：

SELECT column1, column2, SUM(column3) AS total
FROM table_name
GROUP BY column1, column2 WITH ROLLUP;

在上述示例中，我们使用 GROUP BY 来按 column1 和 column2 进行分组，并计算每个组中的 column3 的总和（使用 SUM(column3)）。然后，通过添加 WITH ROLLUP，我们可在结果集中生成额外的行，用于在每个分组和总计之间提供汇总信息。

请注意，使用 WITH ROLLUP 可能会导致结果集中的某些行包含 NULL 值表示汇总信息。你可以使用 COALESCE 函数将这些 NULL 值替换为自定义的标签，以提供更可读的结果。

希望以上信息对你有帮助！如果还有其他问题，请随时提问。

in

在MySQL中，IN是用于在查询中指定一个条件范围的操作符。IN操作符可以让你指定一个值列表，并将其与查询结果进行匹配。

以下是使用IN操作符的一些示例：

  1. 查询满足多个值的数据：
SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);

这将返回column_name列中值为value1、value2或value3的所有行。

  1. 查询满足子查询返回的结果的数据：
SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

这将返回column_name列中的值与子查询返回的结果匹配的所有行。

  1. 查询满足一个列的值列表的数据：
SELECT * FROM table_name WHERE (column1, column2) IN (SELECT column1, column2 FROM another_table WHERE condition);

这将返回具有相同(column1, column2)组合的行，其中(column1, column2)的值存在于子查询返回的结果中。

使用IN操作符可以方便地在查询中指定多个条件值，使查询语句更简洁。确保在查询中使用正确的列名和表名，并根据需要选择合适的条件和值列表。

分页

在 MySQL 中实现分页查询，你可以使用 LIMIT 关键字来限制返回的结果集的行数，并使用 OFFSET 关键字来指定起始位置。

以下是在 MySQL 中进行分页查询的语法示例：

SELECT column1, column2, ...
FROM table_name
LIMIT offset, limit;

其中，offset 是起始位置，表示要跳过的行数，limit 是要返回的行数。

例如，要查询第 5 到第 10 行的数据，可以这样编写 SQL 查询：

SELECT column1, column2, ...
FROM table_name
LIMIT 4, 6;

在上述示例中，4 表示从第 5 行开始，6 表示返回 6 行数据。

通常，为了方便使用，分页查询还可以结合页号和每页行数来进行计算：

-- 每页10行，查询第3页数据
SELECT column1, column2, ...
FROM table_name
LIMIT 20, 10;

在上述示例中，我们假设每页显示 10 行，查询第 3 页的数据，因此起始位置为 20，返回 10 行数据。

连接查询

连接查询是一种在关系型数据库中使用多个表进行查询的技术。在 MySQL 中，可以使用多种连接类型进行连接查询，包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）等。

以下是使用不同连接类型进行连接查询的示例：

  1. 内连接（INNER JOIN）：返回两个表中匹配的行。
SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
  1. 左连接（LEFT JOIN）：返回左表中的所有行，以及右表中匹配的行。
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
  1. 右连接（RIGHT JOIN）：返回右表中的所有行，以及左表中匹配的行。
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
  1. 全连接（FULL JOIN）：返回左表和右表中的所有行。
SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

这只是连接查询的基本语法示例，你可以根据具体需要选择适当的连接类型，并根据实际情况调整表名、列名和连接条件。

需要注意的是，连接查询可能会导致结果集中的行数增加，因此要谨慎使用，并确认连接条件的准确性和逻辑正确性。

子查询

子查询（Subquery）是指在查询语句中嵌套一个查询作为子查询，子查询可嵌套多层，用于获取更复杂的数据结果。

以下是一些常见的在 MySQL 中使用子查询的示例：

  1. 子查询作为条件：
SELECT column1
FROM table1
WHERE column2 IN (SELECT column3 FROM table2);

这个例子中，子查询 (SELECT column3 FROM table2) 返回一个列值列表，作为外部查询中 WHERE 条件的一部分。

  1. 子查询作为表：
SELECT column1, column2
FROM (SELECT column3, column4 FROM table1) AS subquery;

这个例子中，子查询 (SELECT column3, column4 FROM table1) 返回一个临时表，作为外部查询的源表，可以对该表进行查询操作。

  1. 子查询的其他用途： 子查询还可以用于连接查询、聚合函数中等复杂查询场景，多层嵌套的子查询也是可行的。

需要注意的是，子查询可能会影响查询的性能，因此在使用子查询时应谨慎考虑查询的复杂性和数据库的性能要求，以确保查询的效率。

更新表中的数据:

UPDATE <表名> SET <列1>=<值1>, <列2>=<值2> WHERE <条件>;

删除表中的数据:

DELETE FROM <表名> WHERE <条件>;

删除表:

DROP TABLE <表名>;

删除数据库:(删库跑路，bushi)

DROP DATABASE <数据库名>;

显示表结构:

DESC <表名>;

或者

SHOW COLUMNS FROM <表名>;

这些是一些常见的MySQL命令，可用于管理数据库和执行查询操作。

优先级

ddde8149aa822af43bd0281578c8c7e

排序

在MySQL中，可以使用ORDER BY子句对查询结果进行排序。ORDER BY子句允许你指定一个或多个列作为排序的基准，并可以选择升序或降序排序。

以下是一些常见的使用ORDER BY子句进行排序的示例：

  1. 按单个列进行升序排序：
SELECT * FROM table_name ORDER BY column_name ASC;

这将按照column_name列的值进行升序排序。

  1. 按单个列进行降序排序：
SELECT * FROM table_name ORDER BY column_name DESC;

这将按照column_name列的值进行降序排序。

  1. 按多个列进行排序：
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

这将首先按照column1列的值进行升序排序，然后对于相同的column1值，再按照column2列的值进行降序排序。

  1. 使用表达式进行排序：
SELECT * FROM table_name ORDER BY column_name + 10 ASC;

这将根据column_name列和10的总和进行排序。

请注意，ORDER BY子句应该在SELECT语句的末尾使用，并且可以与其他子句（如WHERE、GROUP BY等）组合使用。

使用ORDER BY子句可以对查询结果根据指定的列进行排序，使得数据直观且易于理解。

聚合函数

在 MySQL 中，聚合函数用于对数据进行聚合计算，并返回结果。下面是常见的聚合函数：

  1. COUNT：用于计算指定列或行的数量。
SELECT COUNT(column_name) FROM table_name;
  1. SUM：用于计算指定列的数值总和。
SELECT SUM(column_name) FROM table_name;
  1. AVG：用于计算指定列的数值平均值。
SELECT AVG(column_name) FROM table_name;
  1. MAX：用于获取指定列的最大值。
SELECT MAX(column_name) FROM table_name;
  1. MIN：用于获取指定列的最小值。
SELECT MIN(column_name) FROM table_name;

这些聚合函数可以与其他 SQL 语句（如 WHERE、 GROUP BY 和 HAVING）结合使用，以便对特定条件下的数据进行聚合计算。可以通过给聚合函数传递星号（*）来计算整个表中的数据，或通过指定特定列名来计算特定列的数据。

例如，计算某个表中某一列的总和和平均值：

SELECT SUM(column_name), AVG(column_name) FROM table_name;

需要注意的是，聚合函数通常会将结果分组为一行，而不是单独的行。如果需要按特定条件分组数据并进行聚合计算，可以结合使用 GROUP BY 子句。

聚合函数在数据分析和报表生成等方面非常有用，能够提供有关数据集的汇总信息。

分页

在MySQL中进行分页查询可以使用LIMIT和OFFSET语句。

LIMIT用于限制返回结果的行数，而OFFSET用于指定开始返回结果的偏移量。

通常，分页查询的基本语法是：

SELECT * 
FROM table_name
LIMIT {offset}, {limit};

其中，table_name是要查询的表名，offset是偏移量，limit是限制返回结果的行数。

例如，如果要返回从第10行开始的5行数据，可以使用以下语句：

SELECT * 
FROM table_name
LIMIT 10, 5;

这将返回从第10行开始的5行数据。

请注意，LIMIT子句中的偏移量是从0开始的。在上面的例子中，偏移量为10意味着要跳过前面的10行数据，并从第11行开始返回结果。

另外，分页查询还可以与ORDER BY语句结合使用，以对结果进行排序。例如：

SELECT *
FROM table_name
ORDER BY column_name
LIMIT {offset}, {limit};

这将对表中的数据按照指定的列进行排序，并返回指定偏移量和行数的结果。

mysql实际应用

一个实验用的数据库

-- 1
-- 显示所有数据库

show databases;

-- 2
-- 使用指定的数据库

use 数据库名称;

-- 3
-- 显示指定数据库中的所有表

show tables;

-- 4 
-- 创建一个名为tb_areas的表，包含三个列：aid(INT类型，主键)，atitle(VARCHAR类型，最大长度为20)，pid(INT类型)

CREATE TABLE tb_areas(
    aid INT PRIMARY KEY, -- aid列作为主键
    atitle VARCHAR(20), -- atitle列存储最大长度为20的字符串
    pid INT -- pid列用于关联父区域的aid值
);

-- 5
-- 导入tb_areas.sql文件中的数据，tb_areas.sql文件的路径为datas	b_areas.sql

source datas	b_areas.sql;

-- 6 查询有多少个省

image-20230731120335536

查询广东省所有城市

image-20230731121616936

查询汕头市所有区

image-20230731121808663

查询学生平均年龄

image-20230731122059194

视图

MySQL视图是一个虚拟的表，它是基于查询结果的结果集。视图在MySQL中起到如下作用：

  1. 数据隐藏：视图可以过滤敏感或不必要的数据，只暴露给用户需要的部分。

  2. 简化查询：通过使用视图，可以将复杂的查询逻辑封装在视图中，简化对数据的查询操作。

  3. 数据可重用：视图可以多次使用，避免在多个查询中重复编写相同的查询逻辑。

  4. 数据逻辑修改：在不影响底层表结构的情况下，可以通过修改视图来调整数据的展现形式和处理逻辑。

要创建视图，可以使用MySQL的CREATE VIEW语句。以下是创建视图的基本语法：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

其中，view_name为视图的名称，column1, column2, ...为视图中的列，table_name为查询的基础表，condition为视图的筛选条件。

以下是一个创建视图的示例：

CREATE VIEW customer_details AS
SELECT customer_name, contact_number, address
FROM customers
WHERE status = 'active';

上述示例创建了一个名为customer_details的视图，该视图从customers表中选取了客户名、联系电话和地址信息，且仅包括状态为"active"的客户。

创建完视图后，可以像查询表一样使用视图。例如，可以对视图进行SELECT查询操作：

SELECT * FROM customer_details;

此查询将返回customer_details视图中的所有列和行。

需要注意的是，视图只是一个虚拟的表，不包含实际的数据。当从视图查询时，MySQL会动态地生成结果集。

image-20230731175930081

MySQL事务

MySQL事务是指一组数据库操作，作为一个单独的执行单元，要么全部执行成功，要么全部回滚。事务可以确保数据的一致性和完整性。

在MySQL中，可以使用以下语句来开始和结束事务：

  1. 开始事务：

    begin

    或者

    START TRANSACTION;

  2. 提交事务：

    COMMIT;

  3. 回滚事务：

    ROLLBACK;

以下是执行事务的基本步骤：

  1. 使用START TRANSACTION语句开始一个事务。

  2. 执行一系列的SQL操作，包括插入、更新或删除数据等。

  3. 如果所有操作都成功执行，可以使用COMMIT语句提交事务，将操作结果永久保存到数据库。

  4. 如果在事务过程中遇到错误或异常，可以使用ROLLBACK语句回滚事务，撤销之前的所有操作。

在MySQL中，除了显式地使用事务语句外，还可以通过设置autocommit模式来控制事务的行为。当autocommit模式打开时，每个SQL语句将自动作为一个单独的事务执行；当autocommit模式关闭时，需要使用事务语句来开始、提交或回滚事务。

以下是一个使用事务的示例：

import pymysql

# 连接数据库
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             database='database_name')

# 创建游标对象
cursor = connection.cursor()

try:
    # 开始事务
    connection.begin()

    # 执行事务操作
    sql1 = "INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')"
    cursor.execute(sql1)

    sql2 = "UPDATE table_name SET column1='new_value' WHERE column2='value2'"
    cursor.execute(sql2)

    # 提交事务
    connection.commit()
    print("事务已提交")

except Exception as e:
    # 回滚事务
    connection.rollback()
    print("事务已回滚")

finally:
    # 关闭连接
    connection.close()

在上述示例中，首先开始一个事务，然后执行插入和更新操作。如果所有操作都成功执行，事务将被提交，并打印"事务已提交"；如果遇到错误，事务将被回滚，并打印"事务已回滚"。最后关闭数据库连接。

请注意，MySQL中的事务功能要求数据库引擎支持事务，如InnoDB引擎。若使用其他引擎，事务特性可能不可用。

PyMySQL

要使用PyMySQL库，首先需要安装它。可以使用以下命令使用pip来安装PyMySQL：

pip install PyMySQL

安装完成后，可以通过以下代码来连接到MySQL数据库和执行查询：

import pymysql

# 连接数据库
connection = pymysql.connect(host='localhost',
                             user='username',# 你自己的用户名
                             password='password',# 你自己的密码
                             database='test1')

# 创建游标对象
cursor = connection.cursor()

# 执行查询a
sql = "SELECT * FROM students"
cursor.execute(sql)

# 获取查询结果
result = cursor.fetchall()
for row in result:
    print(row)

# 关闭连接
connection.close()

在上述代码中，需要用实际的数据库连接信息替换host、user、password和database参数，还需将table_name替换为实际的表名。

此外，您还可以使用PyMySQL执行插入、更新和删除等操作。具体的用法可以参考PyMySQL库文档和示例代码。

运行结果：

image-20230801003905846