關(guān)于MySQL數(shù)據(jù)庫 增刪改查語句集錦

CRUD操作：  create?創(chuàng)建（添加）  read?讀取  update?修改  delete?刪除

insert?into?Info?values('p009','張三',1,'n001','2016-8-30?12:9:8')?;?  給特定的列添加數(shù)據(jù)  insert?into?Info?(code,name)?values('p010','李四');  自增長列的處理  insert?into?family?values('','p001','數(shù)據(jù)','T001','數(shù)據(jù)',1);  insert?into?表名?values(值)

刪除所有數(shù)據(jù)  delete?from?family  刪除特定的數(shù)據(jù)  delete?from?Info?where?code='p001'  delete?from?表名?where?條件

修改所有數(shù)據(jù)  update?Info?set?name='徐業(yè)鵬'?  修改特定數(shù)據(jù)  update?Info?set?name='呂永樂'?where?code='p002'?  修改多列  update?Info?set?name='呂永樂',sex=1?where?code='p003'?  update?表名?set?要修改的內(nèi)容?where?條件??tno?=

(1)簡單讀取,查詢所有列(*)??所有行（沒有加條件）  select?*?from?Info  (2)讀取特定列  select?code,name，class?from?Info  (3)條件查詢  select?*?from?Info?where?code='p003'  (4)多條件查詢  select?*?from?Info?where?code='p003'?or?nation='n002'?#或的關(guān)系  select?*?from?Info?where?sex=0?and?nation='n002'?#與的關(guān)系  (5)關(guān)鍵字查詢（模糊查詢）  查所有包含奧迪的汽車  select?*?from?car?where?name?like?'%奧迪%';?#百分號%代表任意多個字符?  查以'皇冠'開頭的所有汽車  select?*?from?car?where?name?like?'皇冠%';  查詢汽車名稱中第二個字符是'馬'的  select?*?from?car?where?name?like?'_馬%';?#下劃線_代表任意一個字符  (6)排序查詢  select?*?from?car?order?by?powers??#默認升序排列  select?*?from?car?order?by?powers??#升序asc?降序?desc  先按brand升序排，再按照price降序排  select?*?from?car?order?by?brand,price?desc

select?*?from?car?where?price9()&gt;40?and?price<p>(8)離散查詢<br></p><pre class="brush:sql;toolbar:false">select?*?from?car?where?price=30?or?price=40?or?price=50?or?price=60;  select?*?from?car?where?price?in(30,40,50,60)取出數(shù)據(jù)  select?*?from?car?where?price?not?in(30,40,50,60)去掉數(shù)據(jù)

select?count(*)?from?car  select?count(code)?from?car?#取所有的數(shù)據(jù)條數(shù)  select?sum(price)?from?car?#求價格總和  select?avg(price)?from?car?#求價格的平均值  select?max(price)?from?car?#求最大值  select?min(price)?from?car?#求最小值

select?*?from?car?limit?0,10??#分頁查詢，跳過幾條數(shù)據(jù)(0)取幾條(10)  規(guī)定一個每頁顯示的條數(shù)：m  當前頁數(shù)：n]  select?*?from?car?limit?(n-1)*m,m

select?distinct?brand?from?car

select?brand,count(*)?from?car?group?by?brand

select?brand?from?car?group?by?brand?having（加條件）?avg(price)>40

select?brand?from?car?group?by?brand?having?max(oil)>8

SELECT?t1.Name,t2.Brand_Name?FROM?brand?t2,car?t1?--?笛卡爾乘積  WHERE?t2.Brand?=?t1.Brand

SELECT?t1.Name,t2.Brand_Name,t3.prod_name??FROM?car?t1?LEFT?JOIN?brand?t2?ON?t1.Brand?=?t2.Brand  LEFT?JOIN?productor?t3?ON?t2.Prod?=?t3.Prod

SELECT?`Name`,Price?FROM?car?  UNION  ?SELECT?Brand_Name,Brand_Memo?FROM?brand  --?子查詢（***）  SELECT?*?FROM?car?WHERE?car.brand?in?(SELECT?Brand?FROM?brand?WHERE?Prod?=?'p001')