View vs Table
Saib thiab cov ntxhuav, ob qho tib si yog ob hom khoom siv database. Hauv cov lus yooj yooj yim, Views khaws cia lossis npe xaiv cov lus nug. Lawv tuaj yeem tsim tau raws li qhia hauv qab no.
Create or replace view_name
As
Select_statement;
Cov ntxhuav yog ua los ntawm kab thiab kab. Ib kem yog ib pawg ntawm cov ntaub ntawv, uas belongs rau tib hom ntaub ntawv. Ib kab yog ib ntu ntawm qhov tseem ceeb, uas tuaj yeem yog los ntawm ntau hom ntaub ntawv. Kem raug txheeb xyuas los ntawm cov npe kab, thiab txhua kab yog cim tshwj xeeb los ntawm lub ntsiab lus tseem ceeb. Cov lus raug tsim siv "tsim rooj" DDL lus nug.
Create table_name (Column_name1 datatype (ntev), Column_name2 datatype (length)
….
….
….);
Views
Raws li tau hais ua ntej, txhua qhov kev pom lub cev yog SELECT nqe lus. Views hu ua "Virtual tables" ntawm lub database. Txawm hais tias cov kev xav tau khaws cia rau hauv cov ntaub ntawv, lawv tsis khiav mus txog thaum lawv raug hu siv lwm nqe lus SELECT. Thaum lawv raug hu siv SELECT nqe lus, lawv cov lus nug SELECT khaws tseg raug tua thiab qhia cov txiaj ntsig. Txij li cov kev xav tsuas muaj SELECT cov lus nug raws li lawv lub cev, lawv tsis xav tau qhov chaw loj. Ntawm no, yog qee qhov txiaj ntsig ntawm kev pom,
- Thaum qhov pom tau tsim, nws tuaj yeem hu rov qab siv nws lub npe, tsis tas sau cov lus nug SELECT ob peb zaug.
- Vim cov kev pom no yog cov khoom ua ntej muab tso ua ke, nws lub sijhawm ua tiav tsawg dua li ua nws cov lus nug SELECT (Lub cev ntawm qhov saib) nyias.
- Views tuaj yeem siv los txwv cov ntaub ntawv nkag mus. Yog li ntawd, lawv tuaj yeem ua lub luag haujlwm tseem ceeb hauv kev ruaj ntseg cov ntaub ntawv, ib yam nkaus.
Tables
Table yog ib qho kev sau ntawm kab. Kab tuaj yeem muaj cov ntaub ntawv los ntawm ntau hom ntaub ntawv. Txhua kab ntawm lub rooj yuav tsum raug txheeb xyuas los ntawm kev siv tus cim tshwj xeeb (Primary key). Cov ntxhuav yog qhov chaw uas peb khaws cov ntaub ntawv. INSERT, Hloov Kho, thiab DELETE cov lus nug tuaj yeem siv los ntxig kab tshiab, hloov kho tus nqi kab uas twb muaj lawm thiab tshem tawm ib kab ntawm lub rooj. SELECT cov lus nug yuav tsum tau siv los muab cov ntaub ntawv los ntawm cov ntxhuav. Cov qauv rooj kuj tuaj yeem hloov pauv (yog tias xav tau) tom qab nws tsim. ALTER TABLE queries yuav tsum tau siv los hloov lub rooj qauv. Cov ntxhuav xav tau qhov chaw ntau dua li kev pom los khaws nws cov ntaub ntawv cov ntsiab lus. Muaj ntau ntau hom rooj hauv databases.
- Internal tables
- Cov rooj sab nraud
- rooj ib ntus
Dab tsi yog qhov txawv ntawm Saib thiab Cov Rooj?
Views yog cov rooj virtual, uas xa mus rau SELECT cov lus nug, tab sis cov ntxhuav yeej muaj nyob hauv database.
Views tsis xav tau qhov chaw loj los khaws nws cov ntsiab lus, tab sis cov ntxhuav xav tau qhov chaw loj dua li kev pom los khaws nws cov ntsiab lus.
Views tuaj yeem tsim siv "tsim lossis hloov" syntax. Tab sis cov ntxhuav tsis tuaj yeem tsim siv "tsim lossis hloov", nws yuav tsum yog "tsim rooj" syntax. Vim lub rooj tsim DDL tsis pub hloov.
Cov kab lus tuaj yeem txheeb xyuas. Tab sis saib kab ntawv tsis tuaj yeem txheeb xyuas. Vim cov views yog cov rooj virtual.
Cov qauv rooj tuaj yeem hloov kho los ntawm kev siv ALTER nqe lus, tab sis cov qauv ntawm kev saib tsis tuaj yeem hloov kho los ntawm kev siv ALTER nqe lus. (Saib yuav tsum tau tsim dua tshiab los hloov kho nws cov qauv)
DML cov lus txib tuaj yeem siv los INSERT, Hloov Kho thiab DELETE cov ntaub ntawv ntawm cov ntxhuav, tab sis DMLs tsuas yog tso cai rau cov kev pom zoo dua qub, uas tsis muaj cov hauv qab no hauv cov lus SELECT.
Tsim cov neeg ua haujlwm (INTERSECT, MINUS, UNION, UNION ALL)
DISTINCT
Pab Pawg Sib Sau Ua Haujlwm (AVG, COUNT, MAX, MIN, SUM, thiab lwm yam)
GROUP BY Clause
ORDER BY Clause
Txuas los ntawm Clause
pib nrog Clause
Cia li nthuav qhia hauv ib Daim Ntawv Xaiv
Sub lus nug hauv Ib Daim Ntawv Xaiv
Qu lus nug
