sql查询简单总结

1、group by:

    使用group by ,那么select 显示的字段必须在group by 中出现，比如：

select year, month ,count(month), sum(account) from yearmonth group by year, month order by year, month;           正确

select year, month ,count(month), sum(account) from yearmonth group by year, month order by month;    就不可以了，报错提醒

2、delete操作：

delete from table student where in in (select id from student_score where score>=60)

3、复杂select 语句的使用逻辑

select * from (select * from tx ) x where..
select a.* from tb inner join ta on ...
再复杂的查询,原型都是这两种组合出来的.

4、复制表

select * into yearmonth2 from yearmonth where 1=1;  复制表和结构

select * into yearmonth3 from yearmonth where 1<>1;  复制表结构，不复制数据

5、查询最大的情况或最小，需和group by 结合(出现在select 中的字段必须在group by中出现)

select year, month max(account) from yearmonth group by year, month order by year, month;

select a.title, a.username, b.adddate from table a, (select max(adddate) adddate from table where table.title=a.title) b;

select a.title, a.username, b.adddate from table a, (select max(adddate), title adddate from comment group by title) b where a.title = b.title;

select a.title, a.username , b.adddate from title_table a , (select max(adddate) adddate from comment where a.title = comment.title) b;

6、distinct查询

select distinct(partner_id) from eroad_bracelet_doctor;

select count(distinct (partner_id)) from eroad_bracelet_doctor ；

distinct 好像不能和其他字段一起查出来了，比如：

select name, distinct(partner_id) from partner;   报错处理，那么如何查呢？

7、join操作

select * from eroad_bracelet_doctor a left join res_partern in a.partner_id=b.id

8、替换操作

两个表A、B，均有key和value两个字段，如果B的key也在A中有，就把B的value换位A中对应的value；

update B b set b.value = (select a.value from A a where a.value = b.value) where id in (select TB.id from B TB, A TA where TB.value = TA.value)