经典查询语句

1、查询a表有而b表没有

select a1 from a minus select b1 from b

2、

select * from talbe a where id in(select top 3 where wp=a.wp order by price desc)


3、Orcle中的分页

select numrow * from (select numrow as num* from talbe where numrow <12) where num>3

select * from table where rownum<12
minus
select * from table where rownum<3

select * from(select * from table where rownum<9
minus
select * from table where rownum<5
)order by month desc

4、删除表中重复的数据

delete * from table a where id not in (select max(id) from table where a.name=name)

5、日程安排提前5分钟提醒
select * from table datediff('minute',startTime,getdate())>5
6、删除主表中，子表没有的数据

delete from info where not exists(select * from infoz where infoz.id=inf.id)

select top 3 from( select * from group by class) as a order by scroa,




select name,case when yuwen='语文' then='语文',
when shuxue='数学' then='数学'，
else '外语',score
end
from stu_mark order by name;


1、select sid,sname from student where sname like '李__' //__2个下划线代表2个任意的字符
2、select dept,count(sid),avg(sage) as a from student group by dept having a>100 //group by 后面接的条件关键字符是having
3、select sid,sname from student wher sid in(select sid from score group by sid having sscore>80)


3种数据库的默认端口
SQLSErver 1433
Oracle 1521
Mysql 3306


删除重复记录

delete form emp e where e.rowid>(select min(x.rowid) from emp x where x.emp_NO=e.emp_NO)




SELECT @cdate := DATE_ADD(@cdate, INTERVAL +1 DAY) cday
FROM( SELECT @cdate := DATE_ADD('2018-08-30', INTERVAL -1 DAY) FROM develop_task_info) t0
WHERE date(@cdate) <= DATE_ADD(NOW(), INTERVAL -1 DAY)