MYSQL-sql语句示例

1. 数据库操作：

   • 创建数据库：CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   • 删除数据库：DROP DATABASE IF EXISTS database_name;
   • 切换数据库：USE database_name;

2. 表的管理：

   • 创建表：CREATE TABLE table_name (column1 datatype constraint, column2 datatype constraint, ...);
   • 修改表结构：
     • 添加列：ALTER TABLE table_name ADD column_name datatype constraint;
     • 修改列数据类型：ALTER TABLE table_name MODIFY column_name new_data_type;
     • 修改列名：ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
     • 删除列：ALTER TABLE table_name DROP COLUMN column_name;
   • 删除表：DROP TABLE IF EXISTS table_name;
   • 查看表结构：DESCRIBE table_name;

3. 数据操作：

   • 插入数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
   • 更新数据：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
   • 删除数据：DELETE FROM table_name WHERE condition;
   • 查询数据：
     • 基本查询：SELECT column1, column2, ... FROM table_name WHERE condition;
     • 聚合函数：SELECT COUNT(column_name) FROM table_name;
     • 分组和排序：SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 ORDER BY column1 ASC;
     • 连接查询：SELECT t1.column1, t2.column2 FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;

4. 索引操作：

   • 创建索引：CREATE INDEX index_name ON table_name (column1, column2, ...);
   • 删除索引：DROP INDEX index_name ON table_name;

5. 视图操作：

   • 创建视图：CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
   • 修改视图定义：ALTER VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
   • 删除视图：DROP VIEW IF EXISTS view_name;

6. 存储过程操作：

   • 创建存储过程：CREATE PROCEDURE procedure_name (IN parameter1 datatype, OUT parameter2 datatype, ...) BEGIN ... END;
   • 调用存储过程：CALL procedure_name(parameter1, @parameter2);
   • 删除存储过程：DROP PROCEDURE IF EXISTS procedure_name;

7. 事务操作：

   • 开始事务：START TRANSACTION;
   • 提交事务：COMMIT;
   • 回滚事务：ROLLBACK;

8. 数据库备份与恢复：

   • 备份数据库：mysqldump -u username -p password database_name > backup.sql;
   • 恢复数据库：mysql -u username -p password database_name < backup.sql;

9. 用户管理：

   • 创建用户：CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
   • 授予权限：GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO 'username'@'localhost';
   • 撤销权限：REVOKE SELECT, INSERT, UPDATE, DELETE ON database_name.table_name FROM 'username'@'localhost';
   • 删除用户：DROP USER 'username'@'localhost';

10. 性能监测和调优：

    • 启用慢查询日志：SET GLOBAL slow_query_log = 1;
    • 分析查询语句执行计划：EXPLAIN SELECT column1, column2, ... FROM table_name WHERE condition;

这是更加详细的MySQL SQL语句示例，涵盖了数据库和表的管理、数据操作、高级功能以及常见查询等方面。