Sql ning ichki tillari sql ma`lumot toifalari

• REJA:
• SQL ning ichki tillari
• SQL ma`lumot toifalari
• SQL tilining SELECT (tanlash) operatorlari va uning paramentrlari

• 03.07.22

• DDL tili ma`lumotlar bazasi tuzilishini tasvirlash uchun ishlatiladi

• DML tili ma`lumotlar bazasidagi ma`lumot ustida amallar bajarish uchun ishlatiladi.

SQL da ma`lumot toifalari (Oracle 9i)

• Satr toifalari
• CHAR(n) – Belgilangan uzunlikdagi ma`lumotlar uchun, n ma`lumotdagi simvollar soni. Uning maksimal darajasi 2 Mbayt.
• VARCHAR2(n) – O`zgaruvchan uzunlikdagi ma`lumotlar uchun. Maksimal darajasi 4 Mbayt.
• LONG – O`zgaruvchan uzunlikdagi ma`lumotlar uchun. Maksimal darajasi 4GB. Bitta jadval uchun
• Raqamli toifalar
• NUMBER(p) – faqat raqamli ma`lumotlar
• INTEGER(p) – Belgilangan raqamlar
• FLOAT(p) – Haqiqiy sonlar uchun
• Vaqt/sana toifasi
• DATE – belgilangan uzunlikdagi vaqt/sana. dd-mm-yy formatda

SELECT operatori

• SELECT (tanlash) SQL tilining eng muxim va ko‘p ishlatiladigan opеratori hisoblanadi. U ma’lumotlar bazasi jadvalidan axborotlarni tanlab olish uchun mo‘ljallangan.
• SELECT opеratori sodda holda quyidagi ko‘rinishda yoziladi.
• SELECT [DISTINCT] <atributlar ro‘yxati>
• FROM <jadvallar ro‘yxati>
• [WHERE <tanlash sharti>]
• [ORDER BY < atributlar ro‘yxati >]
• [GROUP BY < atributlar ro‘yxati >]
• [HAVING <shart> ]
• [UNION <ON SELECT opеratorli ifoda> ] ;

PName	Price	Category	Manufacturer
Gizmo	$19.99	Gadgets	GizmoWorks
Powergizmo	$29.99	Gadgets	GizmoWorks
SingleTouch	$149.99	Photography	Canon
MultiTouch	$203.99	Household	Hitachi

• SELECT * FROM Product WHERE category=‘Gadgets’

• Product

PName	Price	Category	Manufacturer
Gizmo	$19.99	Gadgets	GizmoWorks
Powergizmo	$29.99	Gadgets	GizmoWorks

• “tanlash”

PName	Price	Category	Manufacturer
Gizmo	$19.99	Gadgets	GizmoWorks
Powergizmo	$29.99	Gadgets	GizmoWorks
SingleTouch	$149.99	Photography	Canon
MultiTouch	$203.99	Household	Hitachi

• SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100

• Product

PName	Price	Manufacturer
SingleTouch	$149.99	Canon
MultiTouch	$203.99	Hitachi

• “tanlash” and
• “solishtirish”

LIKE operatori

• % = Berkitilgan simvollar o`rnida qo`llaniladi.

• SELECT * FROM Products WHERE PName LIKE ‘%gizmo%’

PName	Price	Category	Manufacturer
Gizmo	$19.99	Gadgets	GizmoWorks
Powergizmo	$29.99	Gadgets	GizmoWorks
SingleTouch	$149.99	Photography	Canon
MultiTouch	$203.99	Household	Hitachi

• Product

PName	Price	Manufacturer
Powergizmo	$29.99	GizmoWork

• “tanlash” and
• “solishtirish”

• SELECT Category
• FROM Product
• ORDER BY PName

PName	Price	Category	Manufacturer
Gizmo	$19.99	Gadgets	GizmoWorks
Powergizmo	$29.99	Gadgets	GizmoWorks
SingleTouch	$149.99	Photography	Canon
MultiTouch	$203.99	Household	Hitachi

• ?

• SELECT DISTINCT category
• FROM Product
• ORDER BY category

• SELECT DISTINCT category
• FROM Product
• ORDER BY PName

• ?

• ?

PName	Price	Category	Manufacturer
Gizmo	$19.99	Gadgets	GizmoWorks
Powergizmo	$29.99	Gadgets	GizmoWorks
SingleTouch	$149.99	Photography	Canon
MultiTouch	$203.99	Household	Hitachi

• Product

• Company

CName	StockPrice	Country
GizmoWorks	25	USA
Canon	65	Japan
Hitachi	15	Japan

• Key

• Foreign key

PName	Price	Category	Manufacturer
Gizmo	$19.99	Gadgets	GizmoWorks
Powergizmo	$29.99	Gadgets	GizmoWorks
SingleTouch	$149.99	Photography	Canon
MultiTouch	$203.99	Household	Hitachi

• Product

• Company

Cname	StockPrice	Country
GizmoWorks	25	USA
Canon	65	Japan
Hitachi	15	Japan

PName	Price
SingleTouch	$149.99

• SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200

UPDATE командаси жадвал сатрлари ёки мавжуд сатрни баъзи бир ёки барча майдонларини қийматини тиклаш, яъни ўзгартириш имконини беради. Масалан: UNIVERSITET1 жадвалидаги, барча университетларини рейтингини 200 қийматга ўзгартириш учун қуйидаги конструкцияни ишлатиш мумкин:

• UPDATE командаси жадвал сатрлари ёки мавжуд сатрни баъзи бир ёки барча майдонларини қийматини тиклаш, яъни ўзгартириш имконини беради. Масалан: UNIVERSITET1 жадвалидаги, барча университетларини рейтингини 200 қийматга ўзгартириш учун қуйидаги конструкцияни ишлатиш мумкин:
• UPDATE UNIVERSITY1 SET RATING=200;