目录

sql常用命令

整理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. 只导出结构
1
mysqldump --opt -d db_name -u root -p > xxx.sql
  1. 只导出数据
1
mysqldump -t db_name -uroot -p > xxx.sql
  1. 导出特定表结构
1
mysqldump -uroot -p -B db_name --table table_name > xxx.sql
  1. 导出特定表数据
1
mysqldump -t database -u username -p --tables table_name1 table_name2 table_name3 > xxx.sql
  1. 导出数据和结构
1
mysqldump db_name -uroot -p > xxx.sql

数据还原

1
2
mysql 数据库名 < 文件名 
source 文件名  

数据统计

  1. 按年统计数量
1
SELECT YEAR(datetime_field) dateYear, COUNT(*) value FROM table_name GROUP BY YEAR(datetime_field);
  1. 按月统计某字段和
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. 按日统计某字段和
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;

支付宝打赏二维码
微信打赏二维码