mysql数据库sql语句整理

数据库操作

• 显示所有数据库：

  SHOW DATABASES;
  

• 查看当前数据库中的所有表：

  SHOW TABLES;
  

• 创建数据库：

  CREATE DATABASE database_name;

• 删除数据库：

  DROP DATABASE database_name;

• 选择数据库：

  USE database_name;

• 显示当前所选择数据库：

  SELECT DATABASE();
  

表操作

• 创建表：

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

• 删除表：

  DROP TABLE table_name;

• 插入数据：

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

• 选择数据：

  SELECT column1, column2, ... FROM table_name;

• 修改表结构：

  ALTER TABLE table_name [ADD|DROP|MODIFY] column_name datatype;

• 连接表：

  SELECT column1_name, column2_name, ...
  FROM table1
  INNER JOIN table2 ON table1.column1_name = table2.column2_name;
  

• 显示所有表的状态：

  SHOW TABLE STATUS;

• 显示特定表的状态：

  SHOW TABLE STATUS LIKE 'your_table_name';

• 查看表的结构：

  DESCRIBE table_name;
  

• 修改表的名称：

  RENAME TABLE old_table_name TO new_table_name;
  

数据操作

• 更新数据：

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

• 删除数据：

  DELETE FROM table_name WHERE condition;

• 排序数据：

  SELECT column1_name, column2_name, ...
  FROM table_name
  ORDER BY column_name ASC|DESC;
  

• 分组数据：

  SELECT column1_name, column2_name, ...
  FROM table_name
  GROUP BY column_name;
  

• 过滤数据：

  SELECT column1_name, column2_name, ...
  FROM table_name
  WHERE condition;
  

• 限制数据：

  SELECT column1_name, column2_name, ...
  FROM table_name
  LIMIT number;
  

• 偏移数据：

  SELECT column1_name, column2_name, ...
  FROM table_name
  LIMIT number OFFSET offset;
  

权限管理

• 授权：

  GRANT privileges ON database_name.table_name TO 'username'@'host';

• 撤销权限：

  REVOKE privilege ON database_name.table_name FROM 'username'@'host';

• 刷新权限：

  FLUSH PRIVILEGES;
  

• 查看特定用户的权限：

  SHOW GRANTS FOR 'username'@'hostname';

• 查看当前登录用户的权限：

  SHOW GRANTS;

• 允许指定用户从任何主机连接：

  UPDATE mysql.user SET host = '%' WHERE user = 'username';
  

索引

• 创建索引：

  CREATE INDEX index_name ON table_name (column_name);
  

• 删除索引：

  DROP INDEX index_name ON table_name;
  

• 查看索引:

  SHOW INDEXES FROM table_name;
  

• 优化索引:

  OPTIMIZE TABLE table_name;
  

• 重建索引:

  ALTER TABLE table_name REBUILD INDEX index_name;

• 使用索引提示:

  SELECT * FROM table_name USE INDEX (index_name) WHERE column_name = value;
  

• 避免使用覆盖索引:

  SELECT column_name FROM table_name WHERE index_column_name = value;
  

• 避免使用冗余索引:

  CREATE INDEX index_name ON table_name (column_name1, column_name2);
  CREATE INDEX index_name2 ON table_name (column_name1);
  

• 避免使用过长的索引:

  CREATE INDEX index_name ON table_name (column_name(255));
  

• 避免使用不必要的索引:

  CREATE INDEX index_name ON table_name (column_name) WHERE column_name IS NOT NULL;
  

键

• 创建主键：

  CREATE TABLE table_name (
    column_name1 datatype PRIMARY KEY,
    column_name2 datatype,
    column_name3 datatype
  );
  

• 创建外键:

  CREATE TABLE table_name (
    column_name1 datatype,
    column_name2 datatype,
    column_name3 datatype,
    FOREIGN KEY (column_name2) REFERENCES other_table (column_name)
  );
  

• 创建唯一键:

  CREATE TABLE table_name (
    column_name1 datatype UNIQUE,
    column_name2 datatype,
    column_name3 datatype
  );
  

• 删除主键:

  ALTER TABLE table_name DROP PRIMARY KEY;
  

• 删除外键:

  ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;
  

• 删除唯一键:

  ALTER TABLE table_name DROP INDEX unique_index_name;
  

• 查看主键：

  SHOW KEYS FROM table_name WHERE Key_name = 'PRIMARY';
  

• 查看外键：

  SHOW CREATE TABLE table_name;
  

• 查看唯一键：

  SHOW KEYS FROM table_name WHERE Key_name = 'UNIQUE';
  

• 优化键：

  OPTIMIZE TABLE table_name;
  

• 重建键：

  ALTER TABLE table_name REBUILD KEY key_name;
  

• 使用键提示：

  SELECT * FROM table_name USE KEY (key_name) WHERE column_name = value;
  

• 避免使用覆盖键：

  SELECT column_name FROM table_name WHERE key_column_name = value;
  

• 避免使用冗余键：

  CREATE UNIQUE INDEX index_name ON table_name (column_name1, column_name2);
  CREATE UNIQUE INDEX index_name2 ON table_name (column_name1);
  

• 避免使用过长的键：

  CREATE UNIQUE INDEX index_name ON table_name (column_name(255));
  

• 避免使用不必要的键：

  CREATE UNIQUE INDEX index_name ON table_name (column_name) WHERE column_name IS NOT NULL;
  

多表查询

• 内连接：

  SELECT column_name1, column_name2
  FROM table1
  INNER JOIN table2
  ON table1.column_name1 = table2.column_name2;
  

• 左连接：

  SELECT column_name1, column_name2
  FROM table1
  LEFT JOIN table2
  ON table1.column_name1 = table2.column_name2;
  

• 右连接：

  SELECT column_name1, column_name2
  FROM table1
  RIGHT JOIN table2
  ON table1.column_name1 = table2.column_name2;
  

• 全连接：

  SELECT column_name1, column_name2
  FROM table1
  FULL JOIN table2
  ON table1.column_name1 = table2.column_name2;
  

• 自然连接：

  SELECT column_name1, column_name2
  FROM table1
  NATURAL JOIN table2;
  

• 使用别名：

  SELECT t1.column_name1, t2.column_name2
  FROM table1 AS t1
  JOIN table2 AS t2
  ON t1.column_name1 = t2.column_name2;
  

• 使用连接条件：

  SELECT column_name1, column_name2
  FROM table1
  JOIN table2
  ON table1.column_name1 = table2.column_name2
  AND table1.column_name3 = table2.column_name4;
  

• 使用连接子查询：

  SELECT column_name1, column_name2
  FROM table1
  JOIN (SELECT column_name3, column_name4 FROM table2) AS t2
  ON table1.column_name1 = t2.column_name3;
  

• 使用多表连接：

  SELECT column_name1, column_name2, column_name3
  FROM table1
  JOIN table2 ON table1.column_name1 = table2.column_name2
  JOIN table3 ON table2.column_name3 = table3.column_name3;
  

• 使用UNION操作符：

  SELECT column_name1, column_name2
  FROM table1
  UNION
  SELECT column_name3, column_name4
  FROM table2;
  

• 使用INTERSECT操作符：

  SELECT column_name1, column_name2
  FROM table1
  INTERSECT
  SELECT column_name3, column_name4
  FROM table2;
  

• 使用EXCEPT操作符：

  SELECT column_name1, column_name2
  FROM table1
  EXCEPT
  SELECT column_name3, column_name4
  FROM table2;
  

其他

• 创建用户：

  CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
  

• 修改用户密码：

  SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

  or

  ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

• 查看用户和用户信息：

  SELECT user, host FROM mysql.user;
  

• 删除用户：

  DROP USER 'username'@'host';

• 事务提交：

  COMMIT;

• 事务回滚：

  ROLLBACK;

• 子查询：

  SELECT column1_name, column2_name, ...
  FROM table1
  WHERE column1_name IN (SELECT column2_name FROM table2);
  

• 视图：

  CREATE VIEW view_name AS
  SELECT column1_name, column2_name, ...
  FROM table_name
  WHERE condition;
  

• 存储过程：

  CREATE PROCEDURE procedure_name (
    parameter1_name parameter1_type,
    parameter2_name parameter2_type,
    ...
  )
  BEGIN
    -- Code to be executed
  END;
  

• 函数：

  CREATE FUNCTION function_name (
    parameter1_name parameter1_type,
    parameter2_name parameter2_type,
    ...
  )
  RETURNS return_type
  BEGIN
    -- Code to be executed
  END;
  

• 触发器：

  CREATE TRIGGER trigger_name
  ON table_name
  FOR INSERT|UPDATE|DELETE
  AS
  BEGIN
    -- Code to be executed
  END;