mysql数据库命令大全

1. 创建数据库

CREATE DATABASE database_name;

2. 删除数据库

DROP DATABASE database_name;

3. 创建表

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

4. 删除表

DROP TABLE table_name;

5. 插入数据

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

6. 修改数据

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

7. 删除数据

DELETE FROM table_name WHERE condition;

8. 查询数据

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

9. 查询所有数据

SELECT * FROM table_name;

10. 查询不重复的数据

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

11. 排序查询结果

SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 ASC/DESC, column2 ASC/DESC, ...;

12. 查询指定条数的数据

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

13. 查询指定范围的数据

SELECT column1, column2, ... FROM table_name WHERE condition LIMIT start, number;

14. 查询满足条件的数据条数

SELECT COUNT(*) FROM table_name WHERE condition;

15. 查询数据的最大值、最小值、平均值、总和

SELECT MAX(column1), MIN(column2), AVG(column3), SUM(column4) FROM table_name WHERE condition;

16. 连接查询

SELECT column1, column2, ... FROM table_name1 JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE condition;

17. 左连接查询

SELECT column1, column2, ... FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE condition;

18. 右连接查询

SELECT column1, column2, ... FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE condition;

19. 内连接查询

SELECT column1, column2, ... FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE condition;

20. 外连接查询

SELECT column1, column2, ... FROM table_name1 OUTER JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE condition;

21. 分组查询

SELECT column1, COUNT(*) FROM table_name WHERE condition GROUP BY column1;

22. 分组查询并限制结果数量

SELECT column1, COUNT(*) FROM table_name WHERE condition GROUP BY column1 LIMIT number;

23. 分组查询并排序

SELECT column1, COUNT(*) FROM table_name WHERE condition GROUP BY column1 ORDER BY COUNT(*) ASC/DESC;

24. 分组查询并过滤结果

SELECT column1, COUNT(*) FROM table_name WHERE condition GROUP BY column1 HAVING COUNT(*) > number;

25. 子查询

SELECT column1, column2, ... FROM table_name1 WHERE column_name IN (SELECT column_name FROM table_name2 WHERE condition);

26. 更新表结构

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name DROP column_name;

ALTER TABLE table_name MODIFY column_name datatype;

ALTER TABLE table_name RENAME new_table_name;

27. 创建索引

CREATE INDEX index_name ON table_name (column_name);

28. 删除索引

DROP INDEX index_name ON table_name;

29. 创建视图

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

30. 删除视图

DROP VIEW view_name;

31. 创建存储过程

CREATE PROCEDURE procedure_name (IN input_parameter datatype, OUT output_parameter datatype)
BEGIN
-- 存储过程内容
END;

32. 调用存储过程

CALL procedure_name(input_parameter);

33. 删除存储过程

DROP PROCEDURE procedure_name;

34. 创建触发器

CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN
-- 触发器内容
END;

35. 删除触发器

DROP TRIGGER trigger_name;

36. 创建用户

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

37. 删除用户

DROP USER 'username'@'localhost';

38. 授权用户

GRANT privilege_type ON database_name.table_name TO 'username'@'localhost';

39. 撤销用户权限

REVOKE privilege_type ON database_name.table_name FROM 'username'@'localhost';

40. 修改用户密码

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

41. 查看用户权限

SHOW GRANTS FOR 'username'@'localhost';

42. 批量导入数据

LOAD DATA INFILE 'file_path' INTO TABLE table_name;

43. 批量导出数据

SELECT * INTO OUTFILE 'file_path' FROM table_name;

44. 退出mysql命令行

exit;