数据库mysql的常见操作

数据库操作

• 创建数据库：sqlCopy code
  CREATE DATABASE database_name;
• 删除数据库：sqlCopy code
  DROP DATABASE database_name;
• 选择数据库：sqlCopy code
  USE database_name;

表操作

• 创建表：sqlCopy code
  CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
• 删除表：sqlCopy code
  DROP TABLE table_name;
• 显示所有表：sqlCopy code
  SHOW TABLES;
• 显示表结构：sqlCopy code
  DESCRIBE table_name;
• 修改表结构（如添加列）：sqlCopy code
  ALTER TABLE table_name ADD column_name datatype;

数据操作

• 插入数据：sqlCopy code
  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
• 更新数据：sqlCopy code
  UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
• 删除数据：sqlCopy code
  DELETE FROM table_name WHERE condition;
• 查询数据：sqlCopy code
  SELECT column1, column2, ... FROM table_name WHERE condition;
• 排序查询结果：sqlCopy code
  SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

索引操作

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

用户和权限管理

• 创建用户：sqlCopy code
  CREATE USER 'username'@'host' IDENTIFIED BY 'password';
• 授予权限：sqlCopy code
  GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host';
• 撤销权限：sqlCopy code
  REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host';
• 删除用户：sqlCopy code
  DROP USER 'username'@'host';

事务管理

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