-
- select * from pet
-
-
- select name, owner form pet
-
-
- select sin(1+2) as sin
-
-
- select * from pet where (birth>'1980' and species='dog') or species='bird'
-
-
- select * from pet where sex is not null
-
-
- select * from pet where owner like '___n%'
-
-
-
- select * from pet where owner in ('gwen' , 'benny')
-
-
- select * from pet where birth between '1990' and '1999'
-
-
- select * from pet order by owner, name desc
-
-
- select * from pet where sex='m' order by birth desc
-
-
- SELECT owner,length(owner),char_length(owner) FROM pet p;
-
-
-
-
- select distinct owner from pet where species='dog'
-
-
- select name, left(birth,4) as year, mid(birth, 6, 2) as month from pet
- where species='dog' or species='cat'
-
- select name, year(birth) as year, month(birth) as month from pet
- where species in('dog','cat')
-
-
- select name, species, birth
- from pet
- where owner like '%e%'
- order by species,birth desc
-
-
- select round(2.345,2), truncate(2.345,2), mod(323,5)
-
-
- select now(), curdate(), curtime()
-
- select adddate('2007-02-02', interval 31 day)
-
-
- select name,birth,
- truncate(datediff(now(),birth)/365,0) as age1,
- year(now())-year(birth) - (dayofyear(birth)>dayofyear(now())) as age2
- from pet
-
-
- select min(birth),max(birth),avg(birth),count(*),count(sex),
- sum(birth)
- from pet
-
-
- select species,count(*)
- from pet
- group by species
-
-
- select * from pet where birth =
- (select max(birth) from pet)
-
-
- select year(birth), count(*) from pet group by year(birth)
-
-
- select species, sex, count(*)
- from pet
- where species in ('cat','bird')
- group by species, sex
-
-
- select species, sum(truncate(datediff(now(),birth)/365,0)) as SumAge
- from pet
- group by species
-
-
- select species, count(*) as c
- from pet
- group by species
- having c>=2
-
-
- select a.name,a.species, a.sex,b.date, b.type, b.remark
- from pet a,event b
- where a.name = b.name
-
-
- select a.name, a.species,
- truncate(datediff(b.date,a.birth)/365,0) as age
- from pet a,event b
- where a.name = b.name and b.type='litter'
-
-
- select a.name,birth,species,sex,date,type,remark
- from pet a,event b
- where a.name=b.name and birth between '1990' and '1999'
- and species='dog'
-
-
- select type, count(*)
- from pet a, event b
- where a.name=b.name and a.death is null
- group by type
-
-
- select a.name,species,sex,count(*)
- from pet a, event b
- where a.name = b.name
- group by b.name
- having count(*)>=2
-
-
- select a.name,type,date,remark,b.species,b.sex,b.owner
- from event a, pet b
- where a.name=b.name and
- b.name in
- (
- select name
- from event
- group by name
- having count(*)=2
- )
-
-
-
- insert into pet (name,species,birth)
- values ('KKK','snake','2007-01-01');
-
- insert into pet
- values ('KK','Diane','cat','f',null,null);
-
- insert into pet set name='k',owner='Benny'
-
-
-
- update pet set species='snake',sex='f',birth=now()
- where name='k'
-
-
- update pet a
- set birth = (
- select date
- from event b
- where a.name=b.name and b.type='birthday'
- )
- where a.name in (
- select name
- from event
- where type='birthday'
- )
-
-
-
- delete from pet where name like 'k%'
posted @
2010-12-09 10:59
张 开
阅读(
249)
评论()
编辑
收藏
举报