《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,2), 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(),birth)/365,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, a.species,
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,birth,species,sex,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,species,sex,count(*)
MYSQL入门from pet a, event b
MYSQL入门where a.name = b.name
MYSQL入门group by b.name
MYSQL入门having count(*)>=2
MYSQL入门-- 列出发生了两件事情的宠物的事件记录信息
MYSQL入门select a.name,type,date,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,species,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 //简单查询
SELECT id,uid FROM newdb.`test` WHERE 1 //查询ID、UID等字段
SELECT remark as r FROM `test` WHERE 1 //别名查询
SELECT * FROM `test` WHERE id=1,3 //条件查询,相等
SELECT * FROM `test` WHERE id<>2,3 //条件按查,不相等
SELECT * FROM `test` WHERE id in (1,2,4) //in查询,即查询ID为1,2,4的数据
SELECT * FROM `test` WHERE not in (2,3) //in查询,查询ID不是2,3的数据
SELECT * FROM `test` WHERE `uid` like '%王%' //like模糊查询,%*%前后匹配
SELECT * FROM `test` WHERE id BETWEEN 1 and 3 //条件查询,中间数据
SELECT * FROM `test` WHERE id NOT BETWEEN 1and3 //条件查询
SELECT * FROM `test` WHERE id=1 and `remark`='学生' //多个条件
SELECT * FROM `test` group by `remark` //查询排序
SELECT * FROM `test` order by `regdate` ASC //order by升序排序,放到limit之前
SELECT * FROM `test` order by `regdate` ASC,id DESC //order by按照注册时间升序,ID降序
ASC 升序、DESC降序.
MYSQL入门SELECT * FROM `test` limit 0,3 //数据条数限制,输出三条
SELECT count(*) FROM `test` WHERE 1 //统计查询,可以查询单个统计,例如count(name)
SELECT max(id) FROM `test` WHERE 1 //统计ID最大值是多少
以下三个和以上max用法类似
MIN(*)最小值函数
AVG(*)平均值函数
SUM(*)累计值函数
MYSQL入门基本插入语句：
MYSQL入门insert into test (`id`,`uid`,`regdate`,`remark`) values ('','PHP100','2008-07-26','工人') //ID自增,
insert into test (`id`,`uid`,`regdate`,`remark`) values ('','PHP100','now()','工人')
insert into test values ('','PHP200','now()','工人') //简便写法,但不提倡
MYSQL入门更新语句：
MYSQL入门update test set uid='php200' where id=6 //set 后是要改后的内容.where 后是更改位置
MYSQL入门删除语句：
MYSQL入门Delete from dbname.`test` where id=3
转载请注明本页网址：
http://www.vephp.com/jiaocheng/3268.html
