最全的mysql查詢語句整頓。本站提示廣大學習愛好者：（最全的mysql查詢語句整頓）文章只能為提供參考，不一定能成為您想要的結果。以下是最全的mysql查詢語句整頓正文
-- 根本查詢
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.345,2), truncate(2.345,2), mod(323,5)
-- 日期函數
select now(), curdate(), curtime()
select adddate('2007-02-02', interval 31 day)
-- 求出一切寵物的年紀
select name,birth,
truncate(datediff(now(),birth)/365,0) as age1,
year(now())-year(birth) - (dayofyear(birth)>dayofyear(now())) as age2
from pet
-- 分組函數
select min(birth),max(birth),avg(birth),count(*),count(sex),
sum(birth)
from pet
-- 每種寵物各有幾只
select species,count(*)
from pet
group by species
-- 查詢年紀最年夜的寵物的信息
select * from pet where birth =
(select max(birth) from pet)
-- 每一年各出身了幾只寵物
select year(birth), count(*) from pet group by year(birth)
-- 鳥和貓的性別比例
select species, sex, count(*)
from pet
where species in ('cat','bird')
group by species, sex
-- 各類寵物年紀的和
select species, sum(truncate(datediff(now(),birth)/365,0)) as SumAge
from pet
group by species
-- 數目年夜於1 的寵物品種
select species, count(*) as c
from pet
group by species
having c>=2
-- 根本雙表聯系關系
select a.name,a.species, a.sex,b.date, b.type, b.remark
from pet a,event b
where a.name = b.name
-- 查詢寵物產仔時的年紀
select a.name, a.species,
truncate(datediff(b.date,a.birth)/365,0) as age
from pet a,event b
where a.name = b.name and b.type='litter'
--90 年月出身的狗的事宜列表
select a.name,birth,species,sex,date,type,remark
from pet a,event b
where a.name=b.name and birth between '1990' and '1999'
and species='dog'
-- 在世的寵物按產生的事宜類型分組，看各類事宜產生的次數
select type, count(*)
from pet a, event b
where a.name=b.name and a.death is null
group by type
-- 記載的事宜數目跨越1 條的寵物信息
select a.name,species,sex,count(*)
from pet a, event b
where a.name = b.name
group by b.name
having count(*)>=2
-- 列動身生了兩件工作的寵物的事宜記載信息
select a.name,type,date,remark,b.species,b.sex,b.owner
from event a, pet b
where a.name=b.name and
b.name in
(
select name
from event
group by name
having count(*)=2
)
-- 拔出語句
insert into pet (name,species,birth)
values ('KKK','snake','2007-01-01');
insert into pet
values ('KK','Diane','cat','f',null,null);
insert into pet set name='k',owner='Benny'
-- 更新語句
update pet set species='snake',sex='f',birth=now()
where name='k'
-- 將事宜表中誕辰的日期，更新到pet 表中響應寵物的birth 字段
update pet a
set birth = (
select date
from event b
where a.name=b.name and b.type='birthday'
)
where a.name in (
select name
from event
where type='birthday'
)
-- 刪除語句
delete from pet where name like 'k%'
根本查詢語句
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降序。
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(*)累計值函數
根本拔出語句：
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()','工人') //輕便寫法，但不倡導
更新語句：
update test set uid='php200' where id=6 //set 後是要改後的內容。where 後是更改地位
刪除語句：
Delete from dbname.`test` where id=3
