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: