50个MySQL语句带你玩转数据库(5o个mysql语句)

50个MySQL语句带你玩转数据库

MySQL是一种流行的关系型数据库管理系统,它被广泛应用于各种企业级应用和互联网应用中。MySQL提供了许多强大的功能和灵活的数据组织方式,使得它成为许多开发者和数据管理人员的首选。以下是50个MySQL语句,带你深入了解MySQL数据库的使用。

1. 建立数据库

CREATE DATABASE database_name;

2. 删除数据库

DROP DATABASE database_name;

3. 使用指定数据库

USE database_name;

4. 建立表

CREATE TABLE table_name (

column_name1 data_type1,

column_name2 data_type2,

);

5. 增加列

ALTER TABLE table_name ADD column_name column_data_type;

6. 删除列

ALTER TABLE table_name DROP COLUMN column_name;

7. 修改列

ALTER TABLE table_name MODIFY column_name new_column_data_type;

8. 插入数据

INSERT INTO table_name (col1, col2, col3, …) VALUES (val1, val2, val3, …);

9. 修改数据

UPDATE table_name SET col1 = val1, col2 = val2, col3 = val3, … WHERE condition;

10. 删除数据

DELETE FROM table_name WHERE condition;

11. 聚合函数:计算总数

SELECT COUNT(*) FROM table_name;

12. 聚合函数:计算平均数

SELECT AVG(column_name) FROM table_name;

13. 聚合函数:计算最大值

SELECT MAX(column_name) FROM table_name;

14. 聚合函数:计算最小值

SELECT MIN(column_name) FROM table_name;

15. 聚合函数:计算总和

SELECT SUM(column_name) FROM table_name;

16. 选择所有列

SELECT * FROM table_name;

17. 选择特定列

SELECT column_name1, column_name2, … FROM table_name;

18. 选择不同的值

SELECT DISTINCT column_name FROM table_name;

19. 限制结果集

SELECT * FROM table_name LIMIT 10;

20. 按条件选择

SELECT * FROM table_name WHERE column_name = value;

21. 模糊搜索

SELECT * FROM table_name WHERE column_name LIKE ‘%value%’;

22. 按排序选择

SELECT * FROM table_name ORDER BY column_name ASC|DESC;

23. 分组选择

SELECT column_name1, COUNT(column_name2) FROM table_name GROUP BY column_name1;

24. 多表连接

SELECT * FROM table_name1 JOIN table_name2 ON table_name1.column_name = table_name2.column_name;

25. 内联接

SELECT * FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name;

26. 外联接

SELECT * FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name;

27. 子查询

SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM table_name2 WHERE condition);

28. 自连接

SELECT * FROM table_name t1 JOIN table_name t2 ON t1.column_name = t2.column_name;

29. 选择不在另一个表中的数据

SELECT * FROM table_name1 WHERE column_name NOT IN (SELECT column_name FROM table_name2);

30. 分页

SELECT * FROM table_name LIMIT 10 OFFSET 20;

31. 合并查询结果

SELECT * FROM table_name1 UNION SELECT * FROM table_name2;

32. 异常处理

BEGIN

— SQL statements here

EXCEPTION

WHEN condition1 THEN

— code to execute if condition1 is true

WHEN condition2 THEN

— code to execute if condition2 is true

END;

33. 创建视图

CREATE VIEW view_name AS SELECT column_name1, column_name2, … FROM table_name WHERE condition;

34. 修改视图

ALTER VIEW view_name AS SELECT column_name1, column_name2, … FROM table_name WHERE condition;

35. 删除视图

DROP VIEW view_name;

36. 查询视图

SELECT column_name1, column_name2, … FROM view_name WHERE condition;

37. 数据备份

mysqldump -u username -p database_name > backup.sql;

38. 数据恢复

mysql -u username -p database_name

39. 导入CSV文件

LOAD DATA INFILE ‘filename.csv’ INTO TABLE table_name FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

40. 导出CSV文件

SELECT column_name1, column_name2, … FROM table_name INTO OUTFILE ‘filename.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

41. 创建存储过程

CREATE PROCEDURE procedure_name(IN arg1 data_type1, IN arg2 data_type2, OUT arg3 data_type3)

BEGIN

— SQL statements here

END;

42. 调用存储过程

CALL procedure_name(arg1, arg2, @arg3);

SELECT @arg3;

43. 修改存储过程

ALTER PROCEDURE procedure_name(IN arg1 data_type1, IN arg2 data_type2, OUT arg3 data_type3)

BEGIN

— SQL statements here

END;

44. 删除存储过程

DROP PROCEDURE procedure_name;

45. 创建触发器

CREATE TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name FOR EACH ROW

BEGIN

— SQL statements here

END;

46. 修改触发器

ALTER TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name FOR EACH ROW

BEGIN

— SQL statements here

END;

47. 删除触发器

DROP TRIGGER trigger_name;

48. 创建索引

CREATE INDEX index_name ON table_name (column_name);

49. 删除索引

DROP INDEX index_name ON table_name;

50. 查看数据库结构

SHOW DATABASES;

SHOW TABLES;

DESCRIBE table_name;

总结:

MySQL是一种强大的关系型数据库管理系统,它提供了许多丰富的功能和灵活的数据组织方式。通过50个MySQL语句的学习,你可以更深入地了解MySQL数据库的使用,掌握更多的技能和技巧。希望这篇文章能够对你的开发或管理工作有所帮助。

版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《50个MySQL语句带你玩转数据库(5o个mysql语句)》
文章链接:https://zhuji.vsping.com/207660.html
本站资源仅供个人学习交流,请于下载后24小时内删除,不允许用于商业用途,否则法律问题自行承担。