Mune ino posvo, isu tichafukidza mabhesiki eSQL mirairo nepfupi uye yakapusa kunzwisisa mienzaniso.
Iyi rondedzero yeSQL yekuraira ndiyo iyo iwe yaungangodaro uri kushandisa, saka svika pakuvaziva zvakanaka.
Imwe neimwe SQL yekuraira inopihwa nerondedzero uye nemuenzaniso kodhi snippet.
SQL zvirevo zvinogona kuiswa mumapoka akasiyana.
Mune ino posvo, isu tichavhara mirairo yeDDL, DML uye DQL.
Chinhu chekutanga chatinofanira kuita kushanda neSQL kugadzira database. I CREATE DATABASE
chirevo chinoita chaizvo izvozvo.
Muenzaniso:
CREATE DATABASE testDB
I CREATE TABLE
chirevo chinogadzira tafura nyowani mudhatabhesi.
Muenzaniso:
CREATE TABLE Employees (
EmployeeID int,
FirstName varchar(255),
LastName varchar(255),
Department varchar(255) );
I INSERT INTO
chirevo chinoisa mitsara mitsva yedata patafura
Muenzaniso:
INSERT INTO Employees (FirstName, LastName, Department) VALUES ('Sam', 'Burger', 'IT');
SELECT
ndeimwe yeakakosha uye anonyanya kushandiswa SQL kuraira. Iyo inosarudza dhata kubva kudhatabhesi uye inodzosera tafura yemhedzisiro, inonzi mhedzisiro-seti.
Muenzaniso:
SELECT firstName, lastName FROM Employees;
I SELECT
raira paunoshandiswa neasterisk *
anoshanda, anosarudza zvese zvinyorwa kubva patafura yakatsanangurwa.
Muenzaniso:
SELECT * FROM Employees
SELECT DISTINCT
inodzosera chete iro data rakasarudzika; i.e. hachisanganisi zvakapetwa zvakapetwa.
Muenzaniso:
SELECT DISTINCT Department FROM Employees;
I SELECT INTO
chirevo chinosarudza yakatarwa data kubva patafura uye yoikopa kune imwe tafura.
Muenzaniso:
SELECT firstName, entryGraduated INTO StudentAlumni FROM Students;
Sarudza TOP inotsanangudza iyo yakakwira nhamba kana muzana yezvinyorwa zve data kudzoka mune mhedzisiro-set.
SELECT TOP 50 PERCENT * FROM Customers;
I WHERE
clause inoshandiswa kusefa mhedzisiro zvichibva pane yakatarwa mamiriro.
Muenzaniso:
SELECT * FROM Employees WHERE department = 'IT';
I GROUP BY
rairo inoronga yakafanana data kubva kumitsara dzakasiyana kuita mapoka, nekudaro ichigadzira mitsara yekupfupisa.
Muenzaniso:
SELECT COUNT(Department), Department FROM Employees GROUP BY Department;
I HAVING
chirevo chinoita zvakafanana ne WHERE
clause, asi mutsauko ndewekuti HAVING
inoshanda chete neakaunganidzwa mabasa. Saizvozvo, WHERE
clause haishande nemabasa akaunganidzwa.
Muenzaniso:
SELECT COUNT(Department), Department FROM Employees GROUP BY Department HAVING COUNT(Department) > 2;
I IN
opareta anosanganisira akawanda maitiro mune CHEMA chirevo.
Muenzaniso:
SELECT * FROM Employees WHERE Department IN ('IT', 'Graphics', 'Marketing');
BETWEEN
opareta anosefa mhedzisiro uye anodzosera chete iwo anokwana rondedzero yakatarwa.
Muenzaniso:
SELECT * FROM Employees WHERE JoiningDate BETWEEN '01-01-2015' AND `01-01-2020`;
I AND
uye OR
zvirevo zvine mamiriro. Mu AND
, ese mamiriro anofanira kusangana neyakajairwa nzira. Mu OR
chero emamiriro ezvinhu anogutsa yakapihwa nzira inodzosera mhedzisiro.
Semuenzaniso uye:
SELECT * FROM Employees WHERE Department = 'IT' AND JoiningDate > '01-01-2015';
Semuenzaniso OR:
SELECT * FROM Employees WHERE Department ='IT' OR Department = 'Graphics';
AS
inoshanda sa alias. Na AS
, isu tinogona kutumidza zvakare ikholamu kune chimwe chinhu chinonyanya kukosha kana chipfupi mumubvunzo, tisingachinje zita mudhatabhesi.
Muenzaniso:
SELECT FirstName AS fname, LastName AS lname FROM Employees;
INNER JOIN
inosanganisa mitsara kubva pamatafura akasiyana.
Muenzaniso:
SELECT Orders.ID, Customers.Name FROM Orders INNER JOIN Customers ON Orders.ID = Customers.ID;
LEFT JOIN
inotora zvinyorwa kubva patafura yekuruboshwe inoenderana nezvinyorwa mutafura yekurudyi.
Muenzaniso:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
Kupesana ne LEFT JOIN, iyo RIGHT JOIN
inotora zvinyorwa kubva patafura yekurudyi zvinowirirana zvinyorwa mutafura yekuruboshwe.
Muenzaniso:
SELECT Orders.OrderID, Employees.LastName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
FULL JOIN
inodzosera zvese zvinyorwa zvinoenderana kungave kuruboshwe kana kurudyi matafura.
Muenzaniso:
SELECT Customers.Name, CustomerOrders.ID FROM Customers FULL OUTER JOIN Orders ON Customers.ID = CustomerOrders.customerID ORDER BY Customers.Name;
I DELETE
chirevo chinobvisa mimwe mitsara kubva patafura inosangana nemamiriro akatsanangurwa.
Muenzaniso:
DELETE FROM Employees WHERE FirstName = 'Sam' AND LastName = 'Burger';
Isu tinoshandisa ALTER TABLE
kuwedzera kana kubvisa makoramu kubva patafura.
Muenzaniso:
ALTER TABLE Employees ADD JoiningDate date;
TRUNCATE TABLE
inobvisa zvinyorwa zvematafura kubva patafura mune dhatabhesi, asi inochengeta chimiro chetafura.
Muenzaniso:
TRUNCATE TABLE temp_table
DROP TABLE
chirevo chinobvisa iyo tafura yese neayo makoramu parameter uye dhatatype marongero.
Muenzaniso:
DROP TABLE temp_table
DROP DATABASE
inobvisa iro rese rakatsanangurwa dhatabhesi pamwe chete nezvayo zvese parameter uye data.
Chenjera zvakanyanya paunenge uchishandisa uyu murairo.
Muenzaniso:
DROP DATABASE temp_db
Inoenderana: