前言
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.com', 30); - 插入多條數據
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);
刪除數據
- 刪除特定數據
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';
最后
感謝大家，請大家多多支持！
歡迎大家交流，扣949574316
)
)
