sql常用命令
约 286 字
预计阅读 1 分钟
次阅读
整理sql常用命令及高级用法
基本命令
insert
1
2
|
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN ), ( value1, value2,...valueN );
|
delete
1
|
DELETE FROM table_name [WHERE Clause]
|
update
1
|
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
|
select
1
2
3
4
5
6
|
SELECT column_name,column_name
FROM table_name1 LEFT JOIN table_name2 ON table_name1.some_field = table_name2.some_field
[WHERE Clause]
GROUP BY field1, field2
ORDER BY field1, field2
[LIMIT N][ OFFSET M]
|
高级命令
数据备份
格式
1
2
3
|
mysqldump [OPTIONS] database [tables];
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...];
mysqldump [OPTIONS] --all-databases [OPTIONS];
|
常用示例
- 只导出结构
1
|
mysqldump --opt -d db_name -u root -p > xxx.sql
|
- 只导出数据
1
|
mysqldump -t db_name -uroot -p > xxx.sql
|
- 导出特定表结构
1
|
mysqldump -uroot -p -B db_name --table table_name > xxx.sql
|
- 导出特定表数据
1
|
mysqldump -t database -u username -p --tables table_name1 table_name2 table_name3 > xxx.sql
|
- 导出数据和结构
1
|
mysqldump db_name -uroot -p > xxx.sql
|
数据还原
1
2
|
mysql 数据库名 < 文件名
source 文件名
|
数据统计
- 按年统计数量
1
|
SELECT YEAR(datetime_field) dateYear, COUNT(*) value FROM table_name GROUP BY YEAR(datetime_field);
|
- 按月统计某字段和
1
|
SELECT YEAR(datetime_field) dateYear, MONTH(datetime_field) dateMonth, SUM(target_field) value, value FROM table_name GROUP BY YEAR(datetime_field), MONTH(datetime_field);
|
- 按日统计某字段和
1
|
SELECT YEAR(datetime_field) dateYear, MONTH(datetime_field) dateMonth, DAY(datetime_field) dateDay, SUM(target_field) value, value FROM table_name GROUP BY YEAR(datetime_field), MONTH(datetime_field), DAY(datetime_field) dateDay;
|
