Mysql应用最全的mysql查询语句整理
《Mysql应用最全的mysql查询语句整理》要点: MYSQL入门-- 基本查询 MYSQL入门select * from pet MYSQL入门-- 列出指定的列 MYSQL入门select name,owner form pet MYSQL入门-- 直接进行算术运算,对字段起别名 MYSQL入门select sin(1+2) as sin MYSQL入门--where 条件 MYSQL入门select * from pet where (birth>'1980' and species='dog') or species='bird' MYSQL入门-- 对null 的条件 MYSQL入门select * from pet where sex is not null MYSQL入门-- 所有名字第四位是n 的宠物信息是 MYSQL入门select * from pet where owner like '___n%' MYSQL入门-- 所有主人名叫gwen 或benny 的宠物 MYSQL入门select * from pet where owner in ('gwen','benny') MYSQL入门-- 查询出生日期在90 年代是宠物,相当与 >= and?? <= MYSQL入门select * from pet where birth between '1990' and '1999' MYSQL入门-- 按主人姓名排序,相同的按宠物姓名倒序排列 MYSQL入门select * from pet order by owner,name desc MYSQL入门-- 查询性别为公的宠物,按生日倒序排列 MYSQL入门select * from pet where sex='m' order by birth desc MYSQL入门--char_lenngth() 返回的字符的长度,length() 返回字节长度 MYSQL入门SELECT owner,length(owner),char_length(owner) FROM pet p; MYSQL入门-- 列出养有宠物狗的人名 MYSQL入门select distinct owner from pet where species='dog' MYSQL入门-- 用两种方法查询出所有狗和猫的名字、出生年份、出生月份 MYSQL入门select name,left(birth,4) as year,mid(birth,6,2) as month from pet MYSQL入门where species='dog' or species='cat' MYSQL入门select name,year(birth) as year,month(birth) as month from pet MYSQL入门where species in('dog','cat') MYSQL入门-- 查询所有名字中存在字母'e' 的人,将他们养的宠物按类别、年龄排序 MYSQL入门select name,species,birth MYSQL入门from pet MYSQL入门where owner like '%e%' MYSQL入门order by species,birth desc MYSQL入门-- 数字函数 MYSQL入门select round(2.345,2),truncate(2.345,mod(323,5) MYSQL入门-- 日期函数 MYSQL入门select now(),curdate(),curtime() MYSQL入门select adddate('2007-02-02',interval 31 day) MYSQL入门-- 求出所有宠物的年龄 MYSQL入门select name,birth, MYSQL入门truncate(datediff(now(),birth)/365,0) as age1, MYSQL入门year(now())-year(birth) - (dayofyear(birth)>dayofyear(now())) as age2 MYSQL入门from pet MYSQL入门-- 分组函数 MYSQL入门select min(birth),max(birth),avg(birth),count(*),count(sex), MYSQL入门sum(birth) MYSQL入门from pet MYSQL入门-- 每种宠物各有几只 MYSQL入门select species,count(*) MYSQL入门from pet MYSQL入门group by species MYSQL入门-- 查询年龄最大的宠物的信息 MYSQL入门select * from pet where birth = MYSQL入门?? (select max(birth) from pet) MYSQL入门-- 每年各出生了几只宠物 MYSQL入门select year(birth),count(*) from pet group by year(birth) MYSQL入门-- 鸟和猫的性别比例 MYSQL入门select species,sex,count(*) MYSQL入门from pet MYSQL入门where species in ('cat','bird') MYSQL入门group by species,sex MYSQL入门-- 各种宠物年龄的和 MYSQL入门select species,sum(truncate(datediff(now(),0)) as SumAge MYSQL入门from pet MYSQL入门group by species MYSQL入门-- 数量大于1 的宠物种类 MYSQL入门select species,count(*) as c MYSQL入门from pet MYSQL入门group by species MYSQL入门having c>=2 MYSQL入门-- 基本双表关联 MYSQL入门select a.name,a.species,a.sex,b.date,b.type,b.remark MYSQL入门from pet a,event b MYSQL入门where a.name = b.name MYSQL入门-- 查询宠物产仔时的年龄 MYSQL入门select a.name, MYSQL入门truncate(datediff(b.date,a.birth)/365,0) as age MYSQL入门from pet a,event b MYSQL入门where a.name = b.name and b.type='litter' MYSQL入门--90 年代出生的狗的事件列表 MYSQL入门select a.name,date,type,remark MYSQL入门from pet a,event b MYSQL入门where a.name=b.name and birth between '1990' and '1999' MYSQL入门and species='dog' MYSQL入门-- 活着的宠物按发生的事件类型分组,看各种事件发生的次数 MYSQL入门select type,count(*) MYSQL入门from pet a,event b MYSQL入门where a.name=b.name and a.death is null MYSQL入门group by type MYSQL入门-- 记录的事件数量超过1 条的宠物信息 MYSQL入门select a.name,event b MYSQL入门where a.name = b.name MYSQL入门group by b.name MYSQL入门having count(*)>=2 MYSQL入门-- 列出发生了两件事情的宠物的事件记录信息 MYSQL入门select a.name,remark,b.species,b.sex,b.owner MYSQL入门from event a,pet b MYSQL入门where a.name=b.name and MYSQL入门?? b.name in MYSQL入门?? ( MYSQL入门select name MYSQL入门from event MYSQL入门group by name MYSQL入门having count(*)=2 MYSQL入门?? ) MYSQL入门-- 插入语句 MYSQL入门insert into pet (name,birth) MYSQL入门values ('KKK','snake','2007-01-01'); MYSQL入门insert into pet MYSQL入门values ('KK','Diane','cat','f',null,null); MYSQL入门insert into pet set name='k',owner='Benny' MYSQL入门-- 更新语句 MYSQL入门update pet set species='snake',sex='f',birth=now() MYSQL入门where name='k' MYSQL入门-- 将事件表中生日的日期,更新到pet 表中相应宠物的birth 字段 MYSQL入门update pet a MYSQL入门set birth = ( MYSQL入门?? ?? ?? ??? select date MYSQL入门?? ?? ?? ??? from event b MYSQL入门?? ?? ?? ??? where a.name=b.name and b.type='birthday' MYSQL入门?? ?? ?? ) MYSQL入门where a.name in ( MYSQL入门?? ?? ?? ?? ?? select name MYSQL入门?? ?? ?? ?? ?? from event MYSQL入门?? ?? ?? ?? ?? where type='birthday' MYSQL入门?? ?? ?? ?? ) MYSQL入门-- 删除语句 MYSQL入门delete from pet where name like 'k%' MYSQL入门基本查询语句 MYSQL入门SELECT * FROM `test` WHERE 1 //简单查询 MYSQL入门SELECT * FROM `test` limit 0,3?????????????????????????????????????????????? //数据条数限制,输出三条 MYSQL入门基本插入语句: MYSQL入门insert into test (`id`,`uid`,`regdate`,`remark`) values ('','PHP100','2008-07-26','工人') //ID自增, MYSQL入门更新语句: MYSQL入门update test set uid='php200' where id=6 //set 后是要改后的内容.where 后是更改位置 MYSQL入门删除语句: MYSQL入门Delete from dbname.`test` where id=3 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |