常用sql整理

选择：select * from table1 where 范围;

插入：insert into table1(field1,field2) values(value1,value2);

删除：delete from table1 where 范围;

更新：update table1 set field1=value1 where 范围;

查找：select * from table1 where field1 like ’%value1%’ ;

排序：select * from table1 order by field1,field2 [desc];

总数：select count as totalcount from table1;

求和：select sum(field1) as sumvalue from table1;

平均：select avg(field1) as avgvalue from table1;

最大：select max(field1) as maxvalue from table1;

最小：select min(field1) as minvalue from table1;

查询某字段条数大于2的sql：SELECT COUNT(字段) AS COUNT,字段 FROM 表名 GROUP BY 字段 HAVING COUNT >= 2;

 

查询多个字段条数大于2的sql：

SELECT *
FROM orders
WHERE (customer_id, order_date) IN (
SELECT customer_id, order_date
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1
);

 

在数据库原有基础上增加一列：ALTER TABLE table_name ADD column_name datatype after 原字段;

将表名为table_name的字段column_name的数据类型从原来的datetime修改为timestamp类型：ALTER TABLE table_name MODIFY column_name timestamp;