MySQL簡單查詢

標籤：

1.普通查詢

select * from info; #查詢所有內容

select Code,Name from Info #查詢某幾列

2.條件查詢

select * from Info where Code=‘p001‘ #條件查詢

select * from Info where Nation =‘n001‘ and Sex= true #條件之間並的關係
select * from Info where Nation = ‘n001‘ and sex = ‘1‘

select * from Info where Sex = false or Nation = ‘n002‘#條件之間或者的關係

3.模糊查詢（搜尋索引鍵）

select * from Chinastates where areaname like ‘中%‘ #查詢以中開頭的

select * from Chinastates where Areaname like ‘%城%‘ #查詢包含 城的資訊

select * from Chinastates where Areaname like ‘_城%‘ #查詢城 在第二個位置出現的資料

4.排序

select * from Car order by Code desc #desc降序 asc升序

select * from Car order by Brand

select * from Car order by Brand,Powers #按照兩個列排序

5.統計查詢（彙總函式）

select count(*) from Car #查詢總條數 * 代表所有列

select count(Code) from Car #查詢某一列

select max(Price) from Car #查詢 price列裡的最大值

select min(price) from Car #查詢最小值

select avg(price) from Car #查詢平均值

select sum(price) from Car #查詢總和

6.分組查詢
select Brand,count(*) from Car group by Brand #根據系列(Brand)分組(group)查看每組的資料條數

select Code,Brand,count(*) from Car group by Brand #根據Brand分組查看Code，Brand的資料條

select * from Car group by Brand having count(*) >2 #查詢分組之後資料條數大於2的,group後面不能加 where

7.分頁查詢
select * from Car limit 0,5 #查詢跳過0條 取5條

select * from Car limit 5,5 #跳過幾條取幾條資料

 

MySQL簡單查詢