经典查询语句

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)