使用过的sql语句总结

oracle数据库sql：
1)oracle时间函数：
select * from dme_formal.dme_doc_info i where i.app_id='182'and i.created_date between
to_date('2015-6-1 00:00:01','yyyy-mm-dd hh24:mi:ss') AND to_date('2015-6-30 23:59:59','yyyy-mm-dd hh24:mi:ss') order by i.doc_size desc;
2)

mysql数据库slq：
1）myslq的时间函数：
SELECT AWARDS_TYPE_NAME,LAST_UPDATE_BY,date_format(LAST_UPDATE_DATE, '%Y-%m-%d %T') AS LastUpdateTime,GIFT_NAME,GIFT_IMAGE,SENDER,STATUS,GIFT_NUM,GIFT_SORT_NAME,date_format(TRdGift.SEND_DATE, '%Y-%m-%d %T') AS SendDate, VALID_DATE,ID AS PK FROM T_RD_GIFT WHERE 1=1 and TRdGift.STATUS in (0, 1, 2)

create table score(id int primary key auto_increment,name varchar(20),subject varchar(20),score int);

insert into score values
(null,'张三','语文',81),
(null,'张三','数学',75),
(null,'李四','语文',76),
(null,'李四','数学',90),
(null,'王五','语文',81),
(null,'王五','数学',100),
(null,'王五 ','英语',90);

-- 用一条SQL语句查询出每门课都大于80分的学生姓名
select distinct name from score where name not in (select distinct name from score where score<=80);
select name from  (select name, min(score) as min_score from score group by name) t where t.min_score > 80;

drop table if exists sales;
create table sales(id int auto_increment primary key,year varchar(10), month varchar(10), amount float(2,1));
insert into sales values
(null,'1991','1',1.1),
(null,'1991','2',1.2),
(null,'1991','3',1.3),
(null,'1991','4',1.4),
(null,'1992','1',2.1),
(null,'1992','2',2.2),
(null,'1992','3',2.3),
(null,'1992','4',2.4);

select sales.year ,
(select t.amount from sales t where t.month='1' and t.year= sales.year) as '1',
(select t.amount from sales t where t.month='1' and t.year= sales.year) as '2',
(select t.amount from sales t where t.month='1' and t.year= sales.year) as '3',
(select t.amount from sales t where t.month='1' and t.year= sales.year) as '4'
from sales group by year;

create table student2(id int auto_increment primary key,code varchar(20),name varchar(20));
insert into student2 values(null,'2005001','张三'),(null,'2005002','李四'),(null,'2005001','张三');
delete from student2 where id not in(select min(id) from student2 group by name);-- 去重报错
select * from student2 where id not in(select min(id) from student2 group by name);