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数据库的使用，掌握更多的技能和技巧。希望这篇文章能够对你的开发或管理工作有所帮助。
