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)