mysql数据库查询.docVIP

最全的mysql查询语句（审精） null, 名字, 宠物 --基本查询select * from pet--列出指定的列select name, owner form pet--直接进行算术运算，对字段起别名select sin(1+2) as sin--where条件select * from pet where (birth1980 and species=dog) or species=bird--对null的条件select * from pet where sex is not null--所有名字第四位是n的宠物信息是select * from pet where owner like ___n%--所有主人名叫gwen或benny的宠物select * from pet where owner in (gwen , benny)--查询出生日期在90年代是宠物，相当与? ?= and??=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--char_lenngth()返回的字符的长度，length()返回字节长度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=catselect name, year(birth) as year, month(birth) as month from pet where species in(dog,cat)--查询所有名字中存在字母e的人，将他们养的宠物按类别、年龄排序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 age2from pet--分组函数select min(birth),max(birth),avg(birth),count(*),count(sex),sum(birth)from pet--每种宠物各有几只select species,count(*)from petgroup 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 petwhere species in (cat,bird)group by species, sex--各种宠物年龄的和select species, sum(truncate(datediff(now(),birth)/365,0)) as SumAgefrom petgroup by species--数量大于1的宠物种类select species, count(*) as c