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

前言

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

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

数据定义语言（DDL）

数据库操作

创建数据库

sql">CREATE DATABASE my_database;
CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

删除数据库

sql">DROP DATABASE my_database;
DROP DATABASE test_db;

选择数据库
```
sql">USE my_database;
USE test_db;
```

表操作

创建表

sql">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)
);

删除表

sql">DROP TABLE users;
DROP TABLE orders;

修改表

sql">ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
ALTER TABLE users DROP COLUMN age;

数据操作语言（DML）

插入数据

插入单条数据

sql">INSERT INTO users (name, email, age) VALUES ('John Doe', 'john.doe@example.com', 30);

插入多条数据

sql">INSERT INTO users (name, email, age) VALUES
('Jane Smith', 'jane.smith@example.com', 25),
('Robert Brown', 'robert.brown@example.com', 40);
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);

删除数据

删除特定数据

sql">DELETE FROM users WHERE name = 'John Doe';
DELETE FROM users WHERE age > 30;
DELETE FROM orders WHERE amount < 50;

更新数据

更新特定数据

sql">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）

基础查询

查询所有记录

sql">SELECT * FROM users;
SELECT * FROM orders;

查询特定列

sql">SELECT name, email FROM users;
SELECT user_id, amount FROM orders;

排序查询

按列排序

sql">SELECT * FROM users ORDER BY age ASC;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM orders ORDER BY amount ASC;

聚合函数

计算总数

sql">SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;

计算平均值

sql">SELECT AVG(age) FROM users;
SELECT AVG(amount) FROM orders;

计算总和
```
sql">SELECT SUM(amount) FROM orders;
```

计算最大值和最小值

sql">SELECT MAX(age) FROM users;
SELECT MIN(amount) FROM orders;

分组查询

按列分组

sql">SELECT age, COUNT(*) FROM users GROUP BY age;
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

分页查询

分页查询

sql">SELECT * FROM users LIMIT 10 OFFSET 10;
SELECT * FROM orders LIMIT 5 OFFSET 10;

连接查询

内连接查询

sql">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;

外连接查询

sql">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;

子查询

子查询结果为单行单列

sql">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));

子查询结果为多行单列

sql">SELECT *
FROM users
WHERE age > (SELECT age FROM users WHERE age = 30);
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders);

子查询结果为多行多列

sql">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）

管理用户

添加用户

sql">CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'securepass';

删除用户

sql">DROP USER 'username'@'localhost';
DROP USER 'admin'@'localhost';

权限管理

查询权限

sql">SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR 'username'@'localhost';

授予权限

sql">GRANT ALL PRIVILEGES ON test_db.* TO 'username'@'localhost';
GRANT SELECT, INSERT ON *.* TO 'admin'@'localhost';

撤销权限

sql">REVOKE ALL PRIVILEGES ON test_db.* FROM 'username'@'localhost';
REVOKE SELECT, INSERT ON *.* FROM 'admin'@'localhost';

最后

感谢大家，请大家多多支持！
欢迎大家交流，扣949574316