mysql数据库查询.doc

最全的mysql查询语句(审精)
null, 名字, 宠物
--基本查询
select * from pet
--列出指定的列
select name, owner form pet
--直接进行算术运算,对字段起别名
select sin(1+2) as sin
--where条件
select * from pet where (birth>'1980' 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='cat'
select 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), truncate(,2), mod(323,5)
--日期函数
select now(), curdate(), curtime()
select adddate('2007-02-02', interval 31 day)
--求出所有宠物的年龄
select name,birth,
truncate(datediff(now(),