SQL, MySQL

Theeradet
ติดตาม ผู้ติดตาม 
ติดต่อ

selet table.field

from table

where เงื่อนไข

group by field

having เงื่อนไข

order by field

limit index start,จำนวน record

------------------------------------------------------------------

select p.products_id,

p.products_price,

p.products_price*35

from products as p

-------------------------------------------------------------------

select count(*) as total,

min(p.products_price) as min,

max(p.products_price) as max

from products as p

-------------------------------------------------------------------

select p.products_id,

p.products_model,

p.products_price

from products as p

where p.products_price>=50

-------------------------------------------------------------------

select p.products_id,

p.products_model,

p.products_price

from products as p

where p.products_price between 50 and 100

-------------------------------------------------------------------

select p.products_id,

p.products_model,

p.products_price

from products as p

where p.products_id not in(1,3,5)

-------------------------------------------------------------------

select p.products_id,

p.products_model,

p.products_price

from products as p

where p.products_model like '%speed%'

-------------------------------------------------------------------

select pi.*

from products_images as pi

where pi.htmlcontent is not null

-------------------------------------------------------------------

select p.manufacturers_id, count(*) as total,

min(p.products_price) as min,

max(p.products_price) as max,

avg(p.products_price) as avg

from products as p

group by p.manufacturers_id

having count(*)>=3

order by desc

-------------------------------------------------------------------

select p.manufacturers_id, count(*) as total,

min(p.products_price) as min,

max(p.products_price) as max,

avg(p.products_price) as avg

from products as p

group by p.products_price desc

-------------------------------------------------------------------

select *

from products

limit 0,10

-------------------------------------------------------------------

=====================================

select p.products_id,

p.products_model,

p.products_price,

pd.products_name

from products as p,

products_description as pd

where p.products_id=pd.products_id

and pd.language_id=1

order by p.products_id

---------------------------------------------------------

select p.products_id,

p.products_model,

p.products_price,

pd.products_name,

m.manufacturers_name

from products as p,

products_description as pd,

manufacturers as m

where p.products_id=pd.products_id

and p.manufacturers_id=m.manufacturers_id

and pd.language_id=1

order by p.products_id

---------------------------------------------------------

select p.products_id,

p.products_model,

p.products_price,

pd.products_name,

m.manufacturers_name

from products as p

join products_description as pd

on p.products_id=pd.products_id

left join manufacturers as m

on p.manufacturers_id=m.manufacturers_id

where p.products_id=pd.products_id

and pd.language_id=1

order by p.products_id

---------------------------------------------------------

select m.manufacturers_id,

m.manufacturers_name,

(select count(*) from products

where manufacturers_id=m.manufacturers_id)

as total

from manufacturers as m

order by m.manufacturers_id

----------------------------------------------------------

select p.products_id,

p.products_model,

p.products_price,

pd.products_name,

m.manufacturers_name

from products as p

join products_description as pd

on p.products_id=pd.products_id

left join manufacturers as m

on p.manufacturers_id=m.manufacturers_id

where p.products_id=pd.products_id

and pd.language_id=1

and p.products_id in (select products_id

from products_to_categories

where categories_id=12)

order by p.products_id

----------------------------------------------------------

บันทึกนี้เขียนที่ GotoKnow โดย  ใน สมุดโน้ตคนจำยาก



ความเห็น (0)