SQL Yekukurumidza Referensi: Kazhinji Yakajairika SQL Mirairo

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.




Kazhinji Akajairika SQL Mirairo

SQL zvirevo zvinogona kuiswa mumapoka akasiyana.

Data Dudziro Mutauro (DDL) Mirairo

  • Gadzira: inogadzira chinhu chitsva che database, senge tafura.
  • ZERA: yakashandiswa kugadzirisa dhatabhesi chinhu
  • DONHEDZA: yakashandiswa kudzima zvinhu.

Dhata Manipulation Mutauro (DML) Mirairo

  • Isa: yaishandiswa kuisa nyowani data rekodhi rekodhi mutafura.
  • ZVIMWE: inoshandiswa kugadzirisa rekodhi riripo mutafura.
  • Bvisa: yakashandiswa kudzima rekodhi kubva patafura.

Dhata Query Mutauro (DQL) Mirairo

  • Sarudza: iwo mutemo weDQL kusarudza data kubva kudhatabhesi.

Dhata Kudzora Mutauro (DCL) Mirairo

  • BATSIRA: inoshandiswa kupa mvumo kune vashandisi kuti vawane zvinhu zve database.
  • REVOKE: yaigara kuramba mvumo kune vashandisi kuti vawane zvinhu zve database.

Dhata Yekutamisa Mutauro (DTL) Mirairo

  • COMMIT: yakashandiswa kusevha chero kutengeserana mu database zvachose.
  • ROLLBACK: inodzosera dhatabhesi kune yekupedzisira yakazvipira nyika.

Mune ino posvo, isu tichavhara mirairo yeDDL, DML uye DQL.


Gadzira Dhatabhesi

Chinhu chekutanga chatinofanira kuita kushanda neSQL kugadzira database. I CREATE DATABASE chirevo chinoita chaizvo izvozvo.

Muenzaniso:

CREATE DATABASE testDB

Gadzira tafura

I CREATE TABLE chirevo chinogadzira tafura nyowani mudhatabhesi.

Muenzaniso:


CREATE TABLE Employees (
EmployeeID int,
FirstName varchar(255),
LastName varchar(255),
Department varchar(255) );

PINDA

I INSERT INTO chirevo chinoisa mitsara mitsva yedata patafura

Muenzaniso:

INSERT INTO Employees (FirstName, LastName, Department) VALUES ('Sam', 'Burger', 'IT');

Sarudza

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;

Sarudza *

I SELECT raira paunoshandiswa neasterisk * anoshanda, anosarudza zvese zvinyorwa kubva patafura yakatsanangurwa.

Muenzaniso:

SELECT * FROM Employees

Sarudza DISTINCT

SELECT DISTINCT inodzosera chete iro data rakasarudzika; i.e. hachisanganisi zvakapetwa zvakapetwa.

Muenzaniso:


SELECT DISTINCT Department FROM Employees;

Sarudza KUPINDA

I SELECT INTO chirevo chinosarudza yakatarwa data kubva patafura uye yoikopa kune imwe tafura.

Muenzaniso:

SELECT firstName, entryGraduated INTO StudentAlumni FROM Students;

Sarudza TOP

Sarudza TOP inotsanangudza iyo yakakwira nhamba kana muzana yezvinyorwa zve data kudzoka mune mhedzisiro-set.

SELECT TOP 50 PERCENT * FROM Customers;

KUPI

I WHERE clause inoshandiswa kusefa mhedzisiro zvichibva pane yakatarwa mamiriro.


Muenzaniso:

SELECT * FROM Employees WHERE department = 'IT';

GROUP BY

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;

KUVA

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;

IN

I IN opareta anosanganisira akawanda maitiro mune CHEMA chirevo.

Muenzaniso:

SELECT * FROM Employees WHERE Department IN ('IT', 'Graphics', 'Marketing');

PAKATI PEDU

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`;

Uye / kana

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 (Alias)

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;

MUKATI JOIN

INNER JOIN inosanganisa mitsara kubva pamatafura akasiyana.

Muenzaniso:

SELECT Orders.ID, Customers.Name FROM Orders INNER JOIN Customers ON Orders.ID = Customers.ID;

BHUKU JOIN

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;

KODZERO JOINA

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;

YEMAHARA JOINA

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;

Bvisa

I DELETE chirevo chinobvisa mimwe mitsara kubva patafura inosangana nemamiriro akatsanangurwa.

Muenzaniso:

DELETE FROM Employees WHERE FirstName = 'Sam' AND LastName = 'Burger';

ALTER TABLE

Isu tinoshandisa ALTER TABLE kuwedzera kana kubvisa makoramu kubva patafura.

Muenzaniso:

ALTER TABLE Employees ADD JoiningDate date;

CHINONYANYA TABLE

TRUNCATE TABLE inobvisa zvinyorwa zvematafura kubva patafura mune dhatabhesi, asi inochengeta chimiro chetafura.

Muenzaniso:

TRUNCATE TABLE temp_table

Donhedza tafura

DROP TABLE chirevo chinobvisa iyo tafura yese neayo makoramu parameter uye dhatatype marongero.

Muenzaniso:

DROP TABLE temp_table

DONhedza Dhata

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: