影刀RPA开发拓展--SQL常用语句全攻略

前言

SQL（结构化查询语言）是数据库管理和操作的核心工具，无论是初学者还是经验丰富的数据库管理员，掌握常用的 SQL 语句对于高效管理和查询数据都至关重要。本文将系统性地介绍最常用的 SQL 语句，并为每个语句提供详细注释和实际案例，帮助大家在实际项目中得心应手地使用它们。

对于影刀RPA中级开发者，我们需要掌握基本SQL语句，增删改查，将数据插入到数据库存放，比传统的Excel文本存放要好的多，我们可以把数据集中管理，同时便于查询，不需要繁琐的合并数据！

数据定义语言（DDL） 数据库操作 创建数据库CREATE DATABASE my_database; CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 删除数据库DROP DATABASE my_database; DROP DATABASE test_db; 选择数据库USE my_database; USE test_db; 表操作 创建表CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL ); CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, amount DECIMAL(10, 2), FOREIGN KEY (user_id) REFERENCES users(id) ); 删除表DROP TABLE users; DROP TABLE orders; 修改表ALTER TABLE users ADD COLUMN age INT; ALTER TABLE users MODIFY COLUMN email VARCHAR(150); ALTER TABLE users DROP COLUMN age; 数据操作语言（DML） 插入数据 插入单条数据INSERT INTO users (name, email, age) VALUES ('John Doe', 'john.doe@example ', 30); 插入多条数据INSERT INTO users (name, email, age) VALUES ('Jane Smith', 'jane.smith@example ', 25), ('Robert Brown', 'robert.brown@example ', 40); INSERT INTO orders (user_id, amount) VALUES (1, 99.99); 删除数据 删除特定数据DELETE FROM users WHERE name = 'John Doe'; DELETE FROM users WHERE age > 30; DELETE FROM orders WHERE amount < 50; 更新数据 更新特定数据UPDATE users SET age = 31 WHERE name = 'John Doe'; UPDATE users SET age = age + 1; UPDATE orders SET amount = amount * 1.1 WHERE user_id = 1; 数据查询语言（DQL） 基础查询 查询所有记录SELECT * FROM users; SELECT * FROM orders; 查询特定列SELECT name, email FROM users; SELECT user_id, amount FROM orders; 排序查询 按列排序SELECT * FROM users ORDER BY age ASC; SELECT * FROM users ORDER BY age DESC; SELECT * FROM orders ORDER BY amount ASC; 聚合函数 计算总数SELECT COUNT(*) FROM users; SELECT COUNT(*) FROM orders; 计算平均值SELECT AVG(age) FROM users; SELECT AVG(amount) FROM orders; 计算总和SELECT SUM(amount) FROM orders; 计算最大值和最小值SELECT MAX(age) FROM users; SELECT MIN(amount) FROM orders; 分组查询 按列分组SELECT age, COUNT(*) FROM users GROUP BY age; SELECT user_id, SUM(amount) FROM orders GROUP BY user_id; 分页查询 分页查询SELECT * FROM users LIMIT 10 OFFSET 10; SELECT * FROM orders LIMIT 5 OFFSET 10; 连接查询 内连接查询SELECT users.name, orders.amount FROM users, orders WHERE users.id = orders.user_id; SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id; 外连接查询SELECT users.name, orders.amount FROM users LEFT JOIN orders ON users.id = orders.user_id; SELECT users.name, orders.amount FROM users RIGHT JOIN orders ON users.id = orders.user_id; 子查询 子查询结果为单行单列SELECT * FROM users WHERE age = (SELECT MAX(age) FROM users); SELECT * FROM users WHERE id = (SELECT user_id FROM orders WHERE amount = (SELECT MAX(amount) FROM orders)); 子查询结果为多行单列SELECT * FROM users WHERE age > (SELECT age FROM users WHERE age = 30); SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); 子查询结果为多行多列SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)); 数据控制语言（DCL） 管理用户 添加用户CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; CREATE USER 'admin'@'localhost' IDENTIFIED BY 'securepass'; 删除用户DROP USER 'username'@'localhost'; DROP USER 'admin'@'localhost'; 权限管理 查询权限SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR 'username'@'localhost'; 授予权限GRANT ALL PRIVILEGES ON test_db.* TO 'username'@'localhost'; GRANT SELECT, INSERT ON *.* TO 'admin'@'localhost'; 撤销权限REVOKE ALL PRIVILEGES ON test_db.* FROM 'username'@'localhost'; REVOKE SELECT, INSERT ON *.* FROM 'admin'@'localhost'; 最后

感谢大家，请大家多多支持！