【技术积累】Mysql中的SQL语言【一】

故乡的亲情

建表语句

后续所有内容建立在这些SQL语句上，数据根据需要自行补充

1. CREATE TABLE users (
2.   id INT PRIMARY KEY,
3.   name VARCHAR(50),
4.   age INT
5. );
7. CREATE TABLE orders (
8.   id INT PRIMARY KEY,
9.   user_id INT,
10.   product_name VARCHAR(50),
11.   price DECIMAL(10,2),
12.   FOREIGN KEY (user_id) REFERENCES users(id)
13. );

复制代码

1. INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
2. INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30);
3. INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35);
5. INSERT INTO orders (id, user_id, product_name, price) VALUES (1, 1, 'Product A', 10.99);
6. INSERT INTO orders (id, user_id, product_name, price) VALUES (2, 1, 'Product B', 15.99);
7. INSERT INTO orders (id, user_id, product_name, price) VALUES (3, 2, 'Product C', 20.99);
8. INSERT INTO orders (id, user_id, product_name, price) VALUES (4, 2, 'Product A', 10.99);
9. INSERT INTO orders (id, user_id, product_name, price) VALUES (5, 3, 'Product B', 15.99);

复制代码

学习要点

知识点学习

查询单个列的数据

1. SELECT column_name FROM table_name;

复制代码

查询多个列的数据

1. SELECT column1, column2 FROM table_name;

复制代码

带条件查询数据

1. SELECT column1, column2 FROM table_name WHERE condition;

复制代码

插入数据

1. INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

复制代码

更新数据

1. UPDATE table_name SET column1=value1, column2=value2, ... WHERE condition;

复制代码

删除数据

1. DELETE FROM table_name WHERE condition;

复制代码

根据某列升序排序：

1. SELECT * FROM table_name ORDER BY column_name ASC;

复制代码

根据某列降序排序：

1. SELECT * FROM table_name ORDER BY column_name DESC;

复制代码

计算某列的总和：

1. SELECT SUM(column_name) FROM table_name;

复制代码

计算某列的平均值：

1. SELECT AVG(column_name) FROM table_name;

复制代码

按某列分组：

1. SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name;

复制代码

联结
内连接是一种用于联接两个或多个表的方法。它只返回满足连接条件的行，也就是两个表中具有相同值的行。内连接使用关键字"INNER JOIN"来连接两个或多个表，它可以包含一个或多个连接条件。
例如，假设有两个表A和B，表A中有列a，表B中有列b。通过内连接在表A和表B之间进行连接，只会返回那些同时满足"a = b"条件的行。内连接可以帮助我们从两个或多个相关表中获取相关数据。

1. SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.id = table2.id;

复制代码

左外连接两个表：
左外连接是一种用于联接两个或多个表的方法，在MySQL中使用"LEFT JOIN"关键字来实现。左外连接返回两个表中满足连接条件的所有行，以及左表中不满足连接条件的行。
假设有两个表A和B，通过左外连接在表A和表B之间进行连接，查询将返回满足连接条件的行以及左表A中不满足连接条件的行。右表B中不满足连接条件的行的值将被设置为NULL。
这个查询将返回表A中所有行的a列的值，以及在表B中具有相同值的b列的值。如果在表B中没有具有相同值的行，b列的值将为NULL。
左外连接适用于在两个表中寻找关联数据，即使在右表中没有匹配的行时也可以返回左表的数据。这对于获取主表所有数据以及与之关联的次要表的数据很有用。

1. SELECT table1.column1, table2.column2 FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

复制代码

右外连接两个表：
右外连接是一种用于联接两个或多个表的方法，在MySQL中使用"RIGHT JOIN"关键字来实现。右外连接返回右表中满足连接条件的所有行，以及右表中不满足连接条件的行。
假设有两个表A和B，通过右外连接在表A和表B之间进行连接，查询将返回满足连接条件的行以及右表B中不满足连接条件的行。左表A中不满足连接条件的行的值将被设置为NULL。
以下是一个右外连接的示例查询：

1. SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

复制代码

这个查询将返回表B中所有行的b列的值，以及在表A中具有相同值的a列的值。如果在表A中没有具有相同值的行，a列的值将为NULL。
右外连接适用于在两个表中寻找关联数据，即使在左表中没有匹配的行时也可以返回右表的数据。它可以用于获取右表所有数据以及与之关联的主表的数据。在实际应用中，左外连接更常见，而右外连接往往用左外连接完成同样的任务。
子查询
使用子查询：

1. SELECT column_name1 FROM table_name WHERE column_name2 IN (SELECT column_name3 FROM table_name2 WHERE condition);

复制代码

案例列举

问题1: 统计每个用户的订单总数
需要统计每个用户的订单总数，以便了解用户的购买情况。

1. SELECT users.id AS user_id, users.name AS user_name, COUNT(orders.id) AS order_count
2. FROM users
3. LEFT JOIN orders ON users.id = orders.user_id
4. GROUP BY users.id;

复制代码

以上SQL语句中，首先通过左外连接将用户表和订单表关联起来，然后使用GROUP BY子句按用户进行分组，使用COUNT聚合函数统计每个用户的订单数量。
问题2: 查询每个用户的最高订单金额
需要查询每个用户的最高订单金额，以了解用户的购买能力。

1. SELECT users.id AS user_id, users.name AS user_name, MAX(orders.price) AS max_order_amount
2. FROM users
3. LEFT JOIN orders ON users.id = orders.user_id
4. GROUP BY users.id;

复制代码

以上SQL语句中，通过左外连接将用户表和订单表关联起来，然后使用GROUP BY子句按用户进行分组，使用MAX聚合函数找到每个用户的最高订单金额。
问题3: 查询订单数量最多的用户
需要找出订单数量最多的用户，以了解谁是最活跃的用户。

1. SELECT users.id AS user_id, users.name AS user_name, COUNT(orders.id) AS order_count
2. FROM users
3. LEFT JOIN orders ON users.id = orders.user_id
4. GROUP BY users.id
5. ORDER BY order_count DESC
6. LIMIT 1;

复制代码

以上SQL语句中，通过左外连接将用户表和订单表关联起来，然后使用GROUP BY子句按用户进行分组，并使用COUNT函数统计每个用户的订单数量。最后使用ORDER BY子句将结果按订单数量降序排序，并使用LIMIT限制只返回第一条结果，即订单数量最多的用户。
问题4: 查询最近一个月内的订单数量
需要查询最近一个月内的订单数量，以了解近期的订单情况。

1. SELECT COUNT(id) AS order_count
2. FROM orders
3. WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);

复制代码

以上SQL语句中，使用COUNT函数统计最近一个月内的订单数量。通过将当前日期减去一个月的时间间隔来计算出一个月前的日期，并使用WHERE子句筛选出指定日期范围内的订单。
问题5: 查询购买最多的产品
需要查询购买数量最多的产品，以了解最受欢迎的产品。

1. SELECT product_name, COUNT(id) AS sales_count
2. FROM orders
3. GROUP BY product_name
4. ORDER BY sales_count DESC
5. LIMIT 1;

复制代码

以上SQL语句中，使用COUNT函数统计每个产品的销售数量，并使用GROUP BY子句按产品名称进行分组。最后使用ORDER BY子句将结果按销售数量降序排序，并使用LIMIT限制只返回第一条结果，即销售数量最多的产品。
问题6: 查询每个用户的平均订单金额
需要查询每个用户的平均订单金额，以了解用户的平均消费水平。

1. SELECT users.id AS user_id, users.name AS user_name, AVG(orders.price) AS avg_order_amount
2. FROM users
3. LEFT JOIN orders ON users.id = orders.user_id
4. GROUP BY users.id;

复制代码

以上SQL语句中，通过左外连接将用户表和订单表关联起来，然后使用GROUP BY子句按用户进行分组，使用AVG聚合函数找到每个用户的平均订单金额。
问题7: 查询未购买任何产品的用户
需要查询未购买任何产品的用户，以了解哪些用户还没有进行购物。

1. SELECT users.id, users.name
2. FROM users
3. LEFT JOIN orders ON users.id = orders.user_id
4. WHERE orders.id IS NULL;

复制代码

以上SQL语句中，通过左外连接将用户表和订单表关联起来，然后使用WHERE子句筛选出未购买任何产品的用户，即订单ID为空的记录。
问题8: 查询订单金额超过平均订单金额的用户
需要查询订单金额超过平均订单金额的用户，以了解哪些用户的消费能力高于平均水平。

1. SELECT users.id AS user_id, users.name AS user_name, orders.price AS order_amount
2. FROM users
3. JOIN orders ON users.id = orders.user_id
4. WHERE orders.price > (SELECT AVG(price) FROM orders);

复制代码

以上SQL语句中，使用子查询找到订单表中的平均订单金额，然后通过JOIN将用户表和订单表连接起来，使用WHERE子句筛选出订单金额超过平均订单金额的用户。
问题9: 查询每个用户的首次购买日期
需要查询每个用户的首次购买日期，以了解用户的注册后多久开始购物。

1. SELECT users.id AS user_id, users.name AS user_name, MIN(orders.date) AS first_purchase_date
2. FROM users
3. JOIN orders ON users.id = orders.user_id
4. GROUP BY users.id;

复制代码

以上SQL语句中，通过JOIN将用户表和订单表连接起来，然后使用GROUP BY子句按用户进行分组，使用MIN函数找到每个用户的最早购买日期。
问题10: 查询购买过某个产品的用户
需要查询购买过某个产品的用户，以了解对于某个指定的产品，都有哪些用户购买过。

1. SELECT users.id AS user_id, users.name AS user_name
2. FROM users
3. JOIN orders ON users.id = orders.user_id
4. WHERE orders.product_name = '指定产品名称';

复制代码

以上SQL语句中，通过JOIN将用户表和订单表连接起来，然后使用WHERE子句筛选出购买过指定产品的用户，即产品名称与指定名称匹配的记录。

来源:https://www.cnblogs.com/yyyyfly1/p/17513296.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！