Isalathiso esikhawulezayo se-SQL: Uninzi lweeMiyalelo eziqhelekileyo ze-SQL
Kule posi, siza kugubungela iziseko ze-SQL imiyalelo emifutshane kwaye kulula ukuyiqonda imizekelo.
Olu luhlu lwemiyalelo ye-SQL zezona uzakube usebenzisa zona, ke zazi kakuhle.
Umyalelo ngamnye we-SQL unikwa inkcazo kunye nomzekelo wekhowudi snippet.
Uninzi lwee-SQL Commands
Iingxelo ze-SQL zinokuhlelwa ngokwamaqela ahlukeneyo:
Idatha Inkcazo yoLwimi (DDL) Imiyalelo
- YENZA: yenza into entsha yedatha, njengetafile.
- UBUDALA: isetyenziselwe ukuguqula into yedatha
- I-DROP: isetyenziselwa ukucima izinto.
UkuLawulwa kweDatha yoLwimi (i-DML) Imiyalelo
- FAKA: isetyenziselwe ukufaka irekhodi yomqolo wedatha etafileni.
- UHLAZIYO: isetyenziselwe ukuguqula ingxelo esele ikho kwitafile.
- SUSA: Sebenzisa ukucima irekhodi kwitafile.
Imibuzo yoLwazi lweDatha (DQL) Imiyalelo
- KHETHA: ngumyalelo we-DQL ukukhetha idatha kwiziko ledatha.
Ulawulo loLwazi lweeLwimi (DCL) Imiyalelo
- ISibonelelo: isetyenziselwa ukunika imvume kubasebenzisi ukuba bafikelele kwiziko ledatha.
- Rhoxisa: isetyenziselwa ukukhanyela imvume kubasebenzisi yokufikelela kwizinto zedatha.
Ukudluliselwa kwedatha kuLwimi (DTL) Imiyalelo
- ZIBOPHE: isetyenziselwe ukugcina nayiphi na intengiselwano kwiziko ledatha ngokusisigxina.
- UMQULU: ibuyisela isiseko sedatha kwisimo sokugqibela esizinikeleyo.
Kule posi, siza kugubungela imiyalelo ye-DDL, i-DML kunye ne-DQL.
YENZA INGXELO YONYAKA
Into yokuqala ekufuneka siyenzile ukusebenza ne-SQL kukwenza isiseko sedatha. I CREATE DATABASE ingxelo yenza kanye loo nto.
Umzekelo:
CREATE DATABASE testDB YENZA ISICWANGCISO
I CREATE TABLE ingxelo yenza itafile entsha kwindawo yogcino lwedatha.
Umzekelo:
CREATE TABLE Employees (
EmployeeID int,
FirstName varchar(255),
LastName varchar(255),
Department varchar(255) ); FAKA KWI
I INSERT INTO Isiteyitimenti sifaka imiqolo emitsha yedatha etafileni
Umzekelo:
INSERT INTO Employees (FirstName, LastName, Department) VALUES ('Sam', 'Burger', 'IT'); KHETHA
SELECT yenye yezona zinto ziphambili kwaye zisetyenziswa kakhulu kwi-SQL. Ikhetha idatha kwiziko ledatha kwaye ibuyisele itafile yeziphumo, ebizwa ngokuba siseti yeziphumo.
Umzekelo:
SELECT firstName, lastName FROM Employees; KHETHA *
I SELECT umyalelo xa usetyenziswa nge asterisk * umsebenzisi, ukhetha konke iirekhodi ezivela kwitafile echaziweyo.
Umzekelo:
SELECT * FROM Employees KHETHA UKWAHLULELWA
SELECT DISTINCT ibuyisa kuphela idatha eyahlukileyo; Oko kukuthi akubandakanyi ungeniso oluphindwe kabini.
Umzekelo:
SELECT DISTINCT Department FROM Employees; KHETHA KU
I SELECT INTO Isiteyitimenti sikhetha idatha etafileni kwaye siyikope kwenye itafile.
Umzekelo:
SELECT firstName, entryGraduated INTO StudentAlumni FROM Students; KHETHA Phezulu
UKUKHETHA KWI-TOP kuchaza elona nani liphezulu okanye ipesenti yokungeniswa kwedatha ukubuyela kwiseti yeziphumo
SELECT TOP 50 PERCENT * FROM Customers; KUPHI
I WHERE Igatya lisetyenziselwa ukucoca iziphumo ngokusekwe kwimeko ethile.
Umzekelo:
SELECT * FROM Employees WHERE department = 'IT'; IQELA NGU
I GROUP BY Umyalelo uhlela idatha efanayo ukusuka kwimiqolo eyahlukeneyo ibe ngamaqela, ngaloo ndlela kudala ukushwankathela imiqolo.
Umzekelo:
SELECT COUNT(Department), Department FROM Employees GROUP BY Department; UKUBA
I HAVING Igatya lenza okufanayo WHERE Igatya, kodwa umahluko kukuba HAVING isebenza kuphela ngemisebenzi edibeneyo. Ngokufanayo, WHERE Igatya alisebenzi ngemisebenzi edityanisiweyo.
Umzekelo:
SELECT COUNT(Department), Department FROM Employees GROUP BY Department HAVING COUNT(Department) > 2; NGAPHAKATHI
I IN umqhubi ubandakanya amaxabiso amaninzi kwigatya LAPHO.
Umzekelo:
SELECT * FROM Employees WHERE Department IN ('IT', 'Graphics', 'Marketing'); PHAKATHI
BETWEEN umqhubi ucoca iziphumo kwaye abuyise kuphela ezo zilungele uluhlu oluchaziweyo.
Umzekelo:
SELECT * FROM Employees WHERE JoiningDate BETWEEN '01-01-2015' AND `01-01-2020`; KUNYE / OKANYE
I AND kunye OR ziingxelo ezinemiqathango. Kwi AND, Zonke iimeko kufuneka zihlangabezane neendlela ezichaziweyo. Kwi OR nayiphi na imiqathango eyanelisa iikhrayitheriya enikiweyo ibuyisa iziphumo.
Umzekelo KUNYE:
SELECT * FROM Employees WHERE Department = 'IT' AND JoiningDate > '01-01-2015'; Umzekelo OKANYE:
SELECT * FROM Employees WHERE Department ='IT' OR Department = 'Graphics'; AS (Igama)
AS isebenza njenge-alias. Nge AS, sinokuthiya kwakhona ikholamu kwenye into ebaluleke ngakumbi okanye emfutshane kumbuzo, ngaphandle kokutshintsha igama kwiziko ledatha.
Umzekelo:
SELECT FirstName AS fname, LastName AS lname FROM Employees; Joyina ngaphakathi
INNER JOIN idibanisa imiqolo evela kwiitafile ezahlukeneyo.
Umzekelo:
SELECT Orders.ID, Customers.Name FROM Orders INNER JOIN Customers ON Orders.ID = Customers.ID; JOYELA JOYINA
LEFT JOIN ufumana iirekhodi kwitafile yasekhohlo ehambelana neerekhodi kwitafile yasekunene.
Umzekelo:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; Joyina
Ngokuchasene ne-LEFT JOIN, i RIGHT JOIN ufumana iirekhodi kwitafile yasekunene ehambelana neerekhodi kwitafile yasekhohlo.
Umzekelo:
SELECT Orders.OrderID, Employees.LastName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID; Joyina ngokupheleleyo
FULL JOIN ibuyisa zonke iirekhodi ezihambelana kwitafile yasekhohlo okanye yasekunene.
Umzekelo:
SELECT Customers.Name, CustomerOrders.ID FROM Customers FULL OUTER JOIN Orders ON Customers.ID = CustomerOrders.customerID ORDER BY Customers.Name; SUSA
I DELETE Isiteyitimenti sisusa imiqolo ethile kwitafile ehlangabezana nemeko ethile.
Umzekelo:
DELETE FROM Employees WHERE FirstName = 'Sam' AND LastName = 'Burger'; NGANYE ISICWANGCISO
Sisebenzisa ALTER TABLE ukongeza okanye ukususa iikholamu kwitafile.
Umzekelo:
ALTER TABLE Employees ADD JoiningDate date; ITHEYIBHILE YOLAWULO
TRUNCATE TABLE isusa ungeniso lwedatha kwitafile kwindawo yogcino lwedatha, kodwa igcina ukwakheka kwetafile.
Umzekelo:
TRUNCATE TABLE temp_table ITHEYIBHILE
DROP TABLE Isiteyitimenti sisusa itafile yonke kunye neeparameter zekholamu kunye noseto lwedatha.
Umzekelo:
DROP TABLE temp_table IDATHA YOKUQALISA
DROP DATABASE isusa yonke idatha ekhankanyiweyo kunye nazo zonke iiparameter kunye nedatha.
Lumka ngakumbi xa usebenzisa lo myalelo.
Umzekelo:
DROP DATABASE temp_db Idibeneyo:
