MySQL常用语句

MySQL 是一个非常流行的开源关系型数据库管理系统。下面是一些MySQL常用的SQL语句及其简要说明：

数据库操作

1. 创建数据库

   CREATE DATABASE database_name;
   

2. 选择数据库

   USE database_name;
   

3. 删除数据库

   DROP DATABASE database_name;
   

表操作

4. 创建表

   CREATE TABLE table_name (
       column1 datatype,
       column2 datatype,
       ...
   );
   

5. 查看表结构

   DESC table_name;
   

6. 修改表

   ALTER TABLE table_name
   ADD column_name datatype;
   

7. 删除表

   DROP TABLE table_name;
   

数据操作

8. 插入数据

   INSERT INTO table_name (column1, column2,...)
   VALUES (value1, value2,...);
   

9. 查询数据

   SELECT column1, column2,...
   FROM table_name
   WHERE condition;
   

10. 更新数据

    UPDATE table_name
    SET column1 = value1, column2 = value2,...
    WHERE condition;
    

11. 删除数据

    DELETE FROM table_name
    WHERE condition;
    

其他常用操作

12. 排序

    SELECT * FROM table_name
    ORDER BY column_name [ASC|DESC];
    

13. 分组

    SELECT column_name, COUNT(*)
    FROM table_name
    GROUP BY column_name;
    

14. 连接

    SELECT *
    FROM table1
    INNER JOIN table2
    ON table1.column_name = table2.column_name;
    

15. 子查询

    SELECT column_name
    FROM table_name
    WHERE column_name IN (SELECT column_name FROM other_table);
    

16. 聚合函数

• COUNT: 计算行数

  SELECT COUNT(*) FROM table_name;
  

• SUM: 求和

  SELECT SUM(column_name) FROM table_name;
  

• AVG: 平均值

  SELECT AVG(column_name) FROM table_name;
  

• MAX: 最大值

  SELECT MAX(column_name) FROM table_name;
  

• MIN: 最小值

  SELECT MIN(column_name) FROM table_name;
  

17. DISTINCT: 获取唯一值

SELECT DISTINCT column_name FROM table_name;

18. LIMIT: 限制结果集的数量

SELECT * FROM table_name LIMIT 10;

19. OFFSET: 结合 LIMIT 使用，跳过指定数量的行

SELECT * FROM table_name LIMIT 10 OFFSET 20;

20. LIKE: 模糊匹配

SELECT * FROM table_name WHERE column_name LIKE '%search_string%';

21. BETWEEN: 范围查询

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

22. IN: 匹配列表中的值

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);

23. JOIN:

• INNER JOIN: 返回两个表中都有匹配的数据

  SELECT * FROM table1
  INNER JOIN table2
  ON table1.key_column = table2.key_column;
  

• LEFT JOIN: 返回左表所有记录及右表匹配的记录

  SELECT * FROM table1
  LEFT JOIN table2
  ON table1.key_column = table2.key_column;
  

• RIGHT JOIN: 返回右表所有记录及左表匹配的记录

  SELECT * FROM table1
  RIGHT JOIN table2
  ON table1.key_column = table2.key_column;
  

• FULL OUTER JOIN: 返回两个表所有记录

  SELECT * FROM table1
  FULL OUTER JOIN table2
  ON table1.key_column = table2.key_column;
  

24. UNION: 合并两个或多个 SELECT 语句的结果集

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

25. GROUP BY:

    SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
    

26. HAVING: 过滤分组后的结果

    SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 10;
    

27. CASE WHEN THEN ELSE END: 条件表达式

    SELECT column_name, 
           CASE WHEN column_name > value THEN 'High'
                WHEN column_name < value THEN 'Low'
                ELSE 'Medium'
           END AS result
    FROM table_name;
    

28. 视图

    • 创建视图

      CREATE VIEW view_name AS
      SELECT column_name(s) FROM table_name
      WHERE condition;
      

    • 删除视图

      DROP VIEW view_name;
      

29. 存储过程

    • 创建存储过程

      DELIMITER //
      CREATE PROCEDURE procedure_name(IN in_param_type)
      BEGIN
          -- SQL statements
      END //
      DELIMITER ;
      

    • 调用存储过程

      CALL procedure_name(value);
      

30. 触发器

    • 创建触发器

      CREATE TRIGGER trigger_name
      BEFORE INSERT ON table_name
      FOR EACH ROW
      BEGIN
          -- SQL statements
      END;
      

    • 删除触发器

      DROP TRIGGER trigger_name;
      

31. 索引

    • 创建索引

      CREATE INDEX index_name ON table_name (column_name);
      

    • 删除索引

      DROP INDEX index_name ON table_name;
      

这些语句涵盖了数据库管理中的一些高级功能，希望对你有帮助。