阿里云RDS-MariaDB从零到一：完整对接流程与SQL语法深度解析

mysql -h rm-bp1xxxxxxxxxxxxxx.mysql.rds.aliyuncs.com -P 3306 -u myaccount -p

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RDSMariaDBConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://rm-bp1xxxxxxxxxxxxxx.mysql.rds.aliyuncs.com:3306/mydb";
        String user = "myaccount";
        String password = "mypassword";
        
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT VERSION()");
            if (rs.next()) {
                System.out.println("Database version: " + rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

import pymysql
connection = pymysql.connect(
    host='rm-bp1xxxxxxxxxxxxxx.mysql.rds.aliyuncs.com',
    port=3306,
    user='myaccount',
    password='mypassword',
    database='mydb'
)
try:
    with connection.cursor() as cursor:
        cursor.execute("SELECT VERSION()")
        result = cursor.fetchone()
        print(f"Database version: {result[0]}")
finally:
    connection.close()

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status TINYINT DEFAULT 1,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 添加列
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;
-- 修改列
ALTER TABLE users MODIFY COLUMN age INT DEFAULT 18;
-- 重命名列
ALTER TABLE users CHANGE COLUMN age user_age INT DEFAULT 18;
-- 删除列
ALTER TABLE users DROP COLUMN user_age;
-- 添加索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);
-- 删除索引
ALTER TABLE users DROP INDEX idx_created_at;

DROP TABLE IF EXISTS users;
DROP DATABASE IF EXISTS mydatabase;

-- 单条插入
INSERT INTO users (username, email, age) VALUES ('alice', 'alice@example.com', 25);
-- 批量插入
INSERT INTO users (username, email, age) VALUES 
    ('bob', 'bob@example.com', 30),
    ('carol', 'carol@example.com', 28),
    ('dave', 'dave@example.com', 35);
-- 插入或更新（ON DUPLICATE KEY UPDATE）
INSERT INTO users (id, username, email, age) 
VALUES (1, 'alice_new', 'alice_new@example.com', 26)
ON DUPLICATE KEY UPDATE 
    username = VALUES(username),
    email = VALUES(email),
    age = VALUES(age);
-- 从其他表插入
INSERT INTO users_backup (id, username, email, age)
SELECT id, username, email, age FROM users WHERE status = 1;

-- 更新单条记录
UPDATE users SET age = 26 WHERE username = 'alice';
-- 批量更新
UPDATE users SET status = 0 WHERE age < 18;
-- 多表更新
UPDATE users u 
JOIN user_profiles p ON u.id = p.user_id 
SET u.status = 2, p.is_active = 0 
WHERE u.created_at < '2024-01-01';

-- 删除单条记录
DELETE FROM users WHERE username = 'alice';
-- 批量删除
DELETE FROM users WHERE status = 0;
-- 多表删除
DELETE u, p FROM users u 
JOIN user_profiles p ON u.id = p.user_id 
WHERE u.created_at < '2024-01-01';
-- 清空表（快速删除所有数据）
TRUNCATE TABLE users;

-- 查询所有列
SELECT * FROM users;
-- 查询指定列
SELECT id, username, email FROM users;
-- 条件查询
SELECT * FROM users WHERE age >= 18 AND status = 1;
-- 排序
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- 去重
SELECT DISTINCT status FROM users;
-- 聚合查询
SELECT 
    COUNT(*) AS total,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age,
    SUM(age) AS sum_age
FROM users WHERE status = 1;

SELECT 
    status,
    COUNT(*) AS count,
    AVG(age) AS avg_age
FROM users 
GROUP BY status 
HAVING COUNT(*) > 1
ORDER BY count DESC;

-- INNER JOIN
SELECT u.id, u.username, p.bio, p.location
FROM users u
INNER JOIN user_profiles p ON u.id = p.user_id
WHERE u.status = 1;
-- LEFT JOIN
SELECT u.id, u.username, p.bio
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id;
-- 自连接（查询用户及其上级）
SELECT 
    e.id AS employee_id,
    e.username AS employee,
    m.username AS manager
FROM users e
LEFT JOIN users m ON e.manager_id = m.id;
-- 子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users WHERE status = 1);
-- 子查询（EXISTS）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);

SELECT 
    id,
    username,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank,
    RANK() OVER (ORDER BY age DESC) AS rank_with_gaps,
    DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank,
    LAG(age, 1) OVER (ORDER BY id) AS prev_age,
    LEAD(age, 1) OVER (ORDER BY id) AS next_age
FROM users;
-- 分区窗口函数
SELECT 
    status,
    id,
    username,
    age,
    ROW_NUMBER() OVER (PARTITION BY status ORDER BY age DESC) AS rank_in_status
FROM users;

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
-- 删除用户
DROP USER 'app_user'@'%';
-- 修改密码
ALTER USER 'app_user'@'%' IDENTIFIED BY 'new_password';
-- 或使用（MySQL兼容方式）
SET PASSWORD FOR 'app_user'@'%' = PASSWORD('new_password');

-- 授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'app_user'@'%';
-- 授予所有权限
GRANT ALL PRIVILEGES ON mydatabase.* TO 'app_user'@'%';
-- 授予只读权限
GRANT SELECT ON mydatabase.* TO 'readonly_user'@'%';
-- 授予特定表的权限
GRANT SELECT, INSERT ON mydatabase.users TO 'app_user'@'%';
-- 撤销权限
REVOKE DELETE ON mydatabase.* FROM 'app_user'@'%';
-- 查看权限
SHOW GRANTS FOR 'app_user'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;
-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 保存点
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE id = 3;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- 创建视图
CREATE VIEW v_active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 1;
-- 查询视图（与查询表无异）
SELECT * FROM v_active_users WHERE age >= 18;
-- 修改视图
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, username, email, age, created_at
FROM users
WHERE status = 1 AND age >= 18;
-- 删除视图
DROP VIEW IF EXISTS v_active_users;

DELIMITER $$
CREATE PROCEDURE sp_get_users_by_age(IN min_age INT, IN max_age INT)
BEGIN
    SELECT id, username, email, age 
    FROM users 
    WHERE age BETWEEN min_age AND max_age 
    ORDER BY age;
END$$
CREATE PROCEDURE sp_update_user_status(
    IN user_id INT, 
    IN new_status TINYINT,
    OUT affected_rows INT
)
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
    SET affected_rows = ROW_COUNT();
END$$
DELIMITER ;
-- 调用存储过程
CALL sp_get_users_by_age(18, 30);
CALL sp_update_user_status(1, 2, @rows);
SELECT @rows;

-- 创建审计日志表
CREATE TABLE user_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(20),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50)
);
-- 创建UPDATE触发器
DELIMITER $$
CREATE TRIGGER tr_users_update_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit_log (user_id, action, old_data, new_data, changed_by)
    VALUES (
        NEW.id,
        'UPDATE',
        JSON_OBJECT('username', OLD.username, 'email', OLD.email, 'age', OLD.age, 'status', OLD.status),
        JSON_OBJECT('username', NEW.username, 'email', NEW.email, 'age', NEW.age, 'status', NEW.status),
        USER()
    );
END$$
-- 创建INSERT触发器
CREATE TRIGGER tr_users_insert_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit_log (user_id, action, new_data, changed_by)
    VALUES (
        NEW.id,
        'INSERT',
        JSON_OBJECT('username', NEW.username, 'email', NEW.email, 'age', NEW.age, 'status', NEW.status),
        USER()
    );
END$$
DELIMITER ;

-- 创建范围分区表（按年份分区）
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id, order_date)
) 
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- 创建列表分区表
CREATE TABLE products (
    id INT AUTO_INCREMENT,
    category VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id, category)
)
PARTITION BY LIST COLUMNS (category) (
    PARTITION p_electronics VALUES IN ('Electronics', 'Computers'),
    PARTITION p_clothing VALUES IN ('Clothing', 'Shoes', 'Accessories'),
    PARTITION p_food VALUES IN ('Food', 'Beverages')
);
-- 查询特定分区
SELECT * FROM orders PARTITION (p2024) WHERE total > 1000;
-- 添加新分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2026 VALUES LESS THAN (2027)
);
-- 删除分区
ALTER TABLE orders DROP PARTITION p2022;