常用mysql 命令

1. 数据库基础
   • 创建数据库:

     CREATE DATABASE example_db;

   • 删除数据库:

     DROP DATABASE example_db;

2. 表操作
   • 创建表:

     CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

   • 删除表:

     DROP TABLE users;

   • 修改表（添加列）:

     ALTER TABLE users ADD COLUMN age INT;

3. 数据操作
   • 插入数据:

     INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');

   • 查询数据:

     SELECT * FROM users; SELECT username, email FROM users WHERE age > 18;

   • 更新数据:

     UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

   • 删除数据:

     DELETE FROM users WHERE id = 1;

4. 函数和聚合
   • 计数:

     SELECT COUNT(*) FROM users;

   • 最大值/最小值:

     SELECT MAX(age) FROM users; SELECT MIN(age) FROM users;

   • 求和:

     SELECT SUM(age) FROM users;

   • 平均值:

     SELECT AVG(age) FROM users;

5. 索引
   • 创建索引:

     CREATE INDEX idx_username ON users(username);

   • 删除索引:

     DROP INDEX idx_username ON users;

6. 连接查询
   • 内连接:

     SELECT users.username, orders.order_date FROM users INNER JOIN orders ON users.id = orders.user_id;

   • 左连接:

     SELECT users.username, orders.order_date FROM users LEFT JOIN orders ON users.id = orders.user_id;

   • 右连接:

     SELECT users.username, orders.order_date FROM users RIGHT JOIN orders ON users.id = orders.user_id; 

   • 子查询
     • 使用子查询:

       SELECT username FROM users WHERE id IN (SELECT user_id FROM orders WHERE order_total > 100);

   • 事务管理
     • 开始事务:

       START TRANSACTION;

     • 提交事务:

       COMMIT;

     • 回滚事务:

       ROLLBACK;

     • 事务举例：

       START TRANSACTION; -- 开始事务
       -- 假设有两个账户，账户A的ID为1，账户B的ID为2
       -- 从账户A扣除100元
       UPDATE accounts SET balance = balance - 100 WHERE id = 1;
       -- 检查账户A扣款后的余额是否足够
       SELECT balance INTO @A_balance FROM accounts WHERE id = 1;
       IF @A_balance < 0 THEN
           -- 如果余额不足，回滚事务
           ROLLBACK;
       ELSE
           -- 否则，向账户B增加100元
           UPDATE accounts SET balance = balance + 100 WHERE id = 2;
           -- 提交事务
           COMMIT;
       END IF;

   • 用户和权限
     • 创建用户:

       CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

     • 授权:

       GRANT SELECT, INSERT ON example_db.* TO 'newuser'@'localhost';

     • 撤销权限:

       REVOKE INSERT ON example_db.* FROM 'newuser'@'localhost';

     • 删除用户:

       DROP USER 'newuser'@'localhost';