《Mysql應(yīng)用最全的mysql查詢語(yǔ)句整理》要點(diǎn)：
本文介紹了Mysql應(yīng)用最全的mysql查詢語(yǔ)句整理，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
MYSQL入門-- 基本查詢
MYSQL入門select * from pet
MYSQL入門-- 列出指定的列
MYSQL入門select name, owner form pet
MYSQL入門-- 直接進(jìn)行算術(shù)運(yùn)算,對(duì)字段起別名
MYSQL入門select sin(1+2) as sin
MYSQL入門--where 條件
MYSQL入門select * from pet where (birth>'1980' and species='dog') or species='bird'
MYSQL入門-- 對(duì)null 的條件
MYSQL入門select * from pet where sex is not null
MYSQL入門-- 所有名字第四位是n 的寵物信息是
MYSQL入門select * from pet where owner like '___n%'
MYSQL入門-- 所有主人名叫g(shù)wen 或benny 的寵物
MYSQL入門select * from pet where owner in ('gwen' , 'benny')
MYSQL入門-- 查詢出生日期在90 年代是寵物,相當(dāng)與 >= 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() 返回的字符的長(zhǎng)度,length() 返回字節(jié)長(zhǎng)度
MYSQL入門SELECT owner,length(owner),char_length(owner) FROM pet p;
MYSQL入門-- 列出養(yǎng)有寵物狗的人名
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' 的人,將他們養(yǎng)的寵物按類別、年齡排序
MYSQL入門select name, species, birth
MYSQL入門from pet
MYSQL入門where owner like '%e%'
MYSQL入門order by species,birth desc
MYSQL入門-- 數(shù)字函數(shù)
MYSQL入門select round(2.345,2), truncate(2.345,2), mod(323,5)
MYSQL入門-- 日期函數(shù)
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入門-- 分組函數(shù)
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入門-- 鳥(niǎo)和貓的性別比例
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入門-- 數(shù)量大于1 的寵物種類
MYSQL入門select species, count(*) as c
MYSQL入門from pet
MYSQL入門group by species
MYSQL入門having c>=2
MYSQL入門-- 基本雙表關(guān)聯(lián)
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入門-- 查詢寵物產(chǎn)仔時(shí)的年齡
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入門-- 活著的寵物按發(fā)生的事件類型分組,看各種事件發(fā)生的次數(shù)
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入門-- 記錄的事件數(shù)量超過(guò)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入門-- 列出發(fā)生了兩件事情的寵物的事件記錄信息
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入門-- 插入語(yǔ)句
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入門-- 更新語(yǔ)句
MYSQL入門update pet set species='snake',sex='f',birth=now()
MYSQL入門where name='k'
MYSQL入門-- 將事件表中生日的日期,更新到pet 表中相應(yīng)寵物的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入門-- 刪除語(yǔ)句
MYSQL入門delete from pet where name like 'k%'
MYSQL入門基本查詢語(yǔ)句
MYSQL入門SELECT * FROM `test` WHERE 1 //簡(jiǎn)單查詢
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的數(shù)據(jù)
SELECT * FROM `test` WHERE not in (2,3) ??? //in查詢,查詢ID不是2,3的數(shù)據(jù)
SELECT * FROM `test` WHERE `uid` like '%王%' //like模糊查詢,%*%前后匹配
SELECT * FROM `test` WHERE id BETWEEN 1 and 3 ? //條件查詢,中間數(shù)據(jù)
SELECT * FROM `test` WHERE id NOT BETWEEN 1and3 //條件查詢
SELECT * FROM `test` WHERE id=1 and `remark`='學(xué)生' ???? //多個(gè)條件
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按照注冊(cè)時(shí)間升序,ID降序
ASC 升序、DESC降序.
MYSQL入門SELECT * FROM `test` limit 0,3?????????????????????????????????????????????? //數(shù)據(jù)條數(shù)限制,輸出三條
SELECT count(*) FROM `test` WHERE 1????????????????????????????????? //統(tǒng)計(jì)查詢,可以查詢單個(gè)統(tǒng)計(jì),例如count(name)
SELECT max(id) FROM `test` WHERE 1?????????????????????????????????? //統(tǒng)計(jì)ID最大值是多少
以下三個(gè)和以上max用法類似
MIN(*)最小值函數(shù)
AVG(*)平均值函數(shù)
SUM(*)累計(jì)值函數(shù)
MYSQL入門基本插入語(yǔ)句：
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()','工人') //簡(jiǎn)便寫(xiě)法,但不提倡
MYSQL入門更新語(yǔ)句：
MYSQL入門update test set uid='php200' where id=6 //set 后是要改后的內(nèi)容.where 后是更改位置
MYSQL入門刪除語(yǔ)句：
MYSQL入門Delete from dbname.`test` where id=3
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/3268.html
