–列出pet所有的列\nselect * from pet\n–列出指定的列\nselect name, owner form pet\n–直接进行算术运算，对字段起别名\nselect sin(1+2) as sin\n–where条件\nselect * from pet where (birth>’1980′ and species=’dog’) or species=’bird’\n–对null的条件\nselect * from pet where sex is not null\n–所有名字第四位是n的宠物信息是\nselect * from pet where owner like ‘___n%’\n\n–所有主人名叫gwen或benny的宠物\nselect * from pet where owner in (‘gwen’ , ‘benny’)\n–查询出生日期在90年代是宠物，相当与 >= and <=\nselect * from pet where birth between ‘1990’ and ‘1999’\n–按主人姓名排序，相同的按宠物姓名倒序排列\nselect * from pet order by owner, name desc\n–查询性别为公的宠物，按生日倒序排列\nselect * from pet where sex=’m’ order by birth desc\n–char_lenngth()返回的字符的长度，length()返回字节长度\nSELECT owner,length(owner),char_length(owner) FROM pet p;\n\n–列出养有宠物狗的人名\nselect distinct owner from pet where species=’dog’\n–用两种方法查询出所有狗和猫的名字、出生年份、出生月份\nselect name, left(birth,4) as year, mid(birth, 6, 2) as month from pet\nwhere species=’dog’ or species=’cat’\nselect name, year(birth) as year, month(birth) as month from pet\nwhere species in(‘dog’,’cat’)\n–查询所有名字中存在字母’e’的人，将他们养的宠物按类别、年龄排序\nselect name, species, birth\nfrom pet\nwhere owner like ‘%e%’\norder by species,birth desc\n–数字函数\nselect round(2.345,2), truncate(2.345,2), mod(323,5)\n–日期函数\nselect now(), curdate(), curtime()\nselect adddate(‘2007-02-02’, interval 31 day)\n–求出所有宠物的年龄\nselect name,birth,\ntruncate(datediff(now(),birth)/365,0) as age1,\nyear(now())-year(birth) – (dayofyear(birth)>dayofyear(now())) as age2\nfrom pet\n–分组函数\nselect min(birth),max(birth),avg(birth),count(*),count(sex),\nsum(birth)\nfrom pet\n–每种宠物各有几只\nselect species,count(*)\nfrom pet\ngroup by species\n–查询年龄最大的宠物的信息\nselect * from pet where birth =\n(select max(birth) from pet)\n–每年各出生了几只宠物\nselect year(birth), count(*) from pet group by year(birth)\n–鸟和猫的性别比例\nselect species, sex, count(*)\nfrom pet\nwhere species in (‘cat’,’bird’)\ngroup by species, sex\n–各种宠物年龄的和\nselect species, sum(truncate(datediff(now(),birth)/365,0)) as SumAge\nfrom pet\ngroup by species\n–数量大于1的宠物种类\nselect species, count(*) as c\nfrom pet\ngroup by species\nhaving c>=2\n–基本双表关联\nselect a.name,a.species, a.sex,b.date, b.type, b.remark\nfrom pet a,event b\nwhere a.name = b.name\n–查询宠物产仔时的年龄\nselect a.name, a.species,\ntruncate(datediff(b.date,a.birth)/365,0) as age\nfrom pet a,event b\nwhere a.name = b.name and b.type=’litter’\n–90年代出生的狗的事件列表\nselect a.name,birth,species,sex,date,type,remark\nfrom pet a,event b\nwhere a.name=b.name and birth between ‘1990’ and ‘1999’\nand species=’dog’\n–活着的宠物按发生的事件类型分组，看各种事件发生的次数\nselect type, count(*)\nfrom pet a, event b\nwhere a.name=b.name and a.death is null\ngroup by type\n–记录的事件数量超过1条的宠物信息\nselect a.name,species,sex,count(*)\nfrom pet a, event b\nwhere a.name = b.name\ngroup by b.name\nhaving count(*)>=2\n–列出发生了两件事情的宠物的事件记录信息\nselect a.name,type,date,remark,b.species,b.sex,b.owner\nfrom event a, pet b\nwhere a.name=b.name and\nb.name in\n(\nselect name\nfrom event\ngroup by name\nhaving count(*)=2\n)\n\n–插入语句\ninsert into pet (name,species,birth)\nvalues (‘KKK’,’snake’,’2007-01-01′);\ninsert into pet\nvalues (‘KK’,’Diane’,’cat’,’f’,null,null);\ninsert into pet set name=’k’,owner=’Benny’\n\n–更新语句\nupdate pet set species=’snake’,sex=’f’,birth=now()\nwhere name=’k’\n–将事件表中生日的日期，更新到pet表中相应宠物的birth字段\nupdate pet a\nset birth = (\nselect date\nfrom event b\nwhere a.name=b.name and b.type=’birthday’\n)\nwhere a.name in (\nselect name\nfrom event\nwhere type=’birthday’\n)\n\n–删除语句\ndelete from pet where name like ‘k%’\n\n来源：http://www.lc365.net/blog/b/15250/