sql常用命令

1
2

INSERT INTO table_name ( field1, field2,...fieldN ) 
VALUES ( value1, value2,...valueN ), ( value1, value2,...valueN );

1

DELETE FROM table_name [WHERE Clause]

1

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

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;