Referensi Cepet SQL: Prentah SQL Paling Umum

Ing kiriman iki, kita bakal nutupi dhasar prentah SQL kanthi conto sing ringkes lan gampang dingerteni.

Dhaptar prentah SQL iki sing paling kamungkinan sampeyan gunakake, dadi ngerteni luwih becik.

Saben printah SQL diwenehake karo deskripsi lan conto kode cuplikan.




Prentah SQL Paling Umum

Pranyatan SQL bisa diklumpukake ing macem-macem kategori:

Dhawuhe Dhéfinisi Dhata (DDL)

  • Gawe: nggawe obyek database anyar, kayata tabel.
  • Umur: digunakake kanggo ngowahi obyek database
  • DROP: digunakake kanggo mbusak obyek kasebut.

Dhawuhe Basa Manipulasi Data (DML)

  • INSERT: digunakake kanggo masang data baris data anyar ing tabel.
  • UPDATE: digunakake kanggo ngowahi rekaman sing wis ana ing tabel.
  • PUTUS: digunakake mbusak rekaman saka tabel.

Dhawuhe Dhasar Query Data (DQL)

  • PILIH: minangka prentah DQL kanggo milih data saka basis data.

Prentah Ngontrol Data (DCL)

  • GRANT: digunakake kanggo menehi ijin kanggo pangguna kanggo ngakses obyek database.
  • REVOKE: digunakake kanggo nolak idin kanggo pangguna ngakses obyek database.

Dhawuhe Basa Transfer Data (DTL)

  • KOMITUT: digunakake kanggo nyimpen transaksi menyang database kanthi permanen.
  • ROLLBACK: mulihake basis data menyang negara sing setya pungkasan.

Ing kiriman iki, kita bakal nutupi prentah DDL, DML lan DQL.


Gawe DATABASE

Babagan pisanan sing kudu ditindakake kanggo nggarap SQL yaiku nggawe database. Sing CREATE DATABASE pratelan ora persis sing.

Tuladha:

CREATE DATABASE testDB

Gawe Tabel

Sing CREATE TABLE pernyataan nggawe tabel anyar ing basis data.

Tuladha:


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

ING IKI

Sing INSERT INTO pernyataan masang baris data anyar menyang tabel

Tuladha:

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

PILIH

SELECT minangka salah sawijining prentah utama SQL sing paling utama. Milih data saka basis data lan ngasilake tabel asil, diarani asil asil.

Tuladha:


SELECT firstName, lastName FROM Employees;

PILIH *

Sing SELECT printah nalika digunakake kanthi tanda bintang * operator, milih kabeh cathetan saka tabel sing ditemtokake.

Tuladha:

SELECT * FROM Employees

PILIH KABEH

SELECT DISTINCT mung ngasilake data sing beda; yaiku ora kalebu entri duplikat.

Tuladha:


SELECT DISTINCT Department FROM Employees;

PILIHEN ING

Sing SELECT INTO pratelan milih data sing ditemtokake saka tabel lan nyalin menyang tabel liyane.

Tuladha:

SELECT firstName, entryGraduated INTO StudentAlumni FROM Students;

PILIH TOP

SELECT TOP nemtokake jumlah utawa persentasi entri data sing maksimal kanggo bali ing asil sing disetel.

SELECT TOP 50 PERCENT * FROM Customers;

ING NGISI

Sing WHERE klausa digunakake kanggo nyaring asil adhedhasar kahanan sing ditemtokake.


Tuladha:

SELECT * FROM Employees WHERE department = 'IT';

KELOMPOK BY

Sing GROUP BY printah ngatur data sing padha saka macem-macem larik dadi klompok, mula nggawe rangkuman baris.

Tuladha:

SELECT COUNT(Department), Department FROM Employees GROUP BY Department;

Duwe

Sing HAVING klausa nindakake padha karo WHERE klausa, nanging bedane yaiku HAVING mung bisa digunakake kanthi fungsi agregat. Semono uga, WHERE klausa ora bisa digunakake kanthi fungsi agregat.

Tuladha:

SELECT COUNT(Department), Department FROM Employees GROUP BY Department HAVING COUNT(Department) > 2;

IN

Sing IN operator kalebu macem-macem nilai menyang klausa WHERE.

Tuladha:

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

ANTARA

BETWEEN operator nyaring asil lan ngasilake mung sing cocog karo sawetara sing ditemtokake.

Tuladha:

SELECT * FROM Employees WHERE JoiningDate BETWEEN '01-01-2015' AND `01-01-2020`;

LAN / UTAWA

Sing AND lan OR yaiku pratelan kahanan. Ing AND, kabeh kahanan kudu memenuhi kriteria sing ditemtokake. Ing OR samubarang kahanan sing memenuhi kriteria tartamtu ngasilake asil.

Tuladha LAN:

SELECT * FROM Employees WHERE Department = 'IT' AND JoiningDate > '01-01-2015';

Tuladha UTAWA:

SELECT * FROM Employees WHERE Department ='IT' OR Department = 'Graphics';

AS (Alias)

AS makarya dadi alias. Kanthi AS, kita bisa ngganti jeneng kolom dadi luwih migunani utawa luwih cendhek ing pitakon, tanpa kudu ngganti jeneng ing basis data.

Tuladha:

SELECT FirstName AS fname, LastName AS lname FROM Employees;

INNER NGGABUNGA

INNER JOIN nggabungake larik saka macem-macem tabel.

Tuladha:

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

NGiwa Gabung

LEFT JOIN njupuk cathetan saka tabel kiwa sing cocog karo cathetan ing tabel sisih tengen.

Tuladha:

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;

NGGABUNGA KENE

Kosok baline GABUNG kiwa, ing RIGHT JOIN njupuk cathetan saka tabel sisih tengen sing cocog karo cathetan ing tabel kiwa.

Tuladha:

SELECT Orders.OrderID, Employees.LastName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;

NGGABUNGA LENGKAP

FULL JOIN ngasilake kabeh cathetan sing cocog ing tabel kiwa utawa tengen.

Tuladha:

SELECT Customers.Name, CustomerOrders.ID FROM Customers FULL OUTER JOIN Orders ON Customers.ID = CustomerOrders.customerID ORDER BY Customers.Name;

Mbusak

Sing DELETE pratelan mbusak baris tartamtu saka tabel sing cocog karo kahanan sing ditemtokake.

Tuladha:

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

GANTI TABEL

Kita nggunakake ALTER TABLE kanggo nambah utawa mbusak kolom saka tabel.

Tuladha:

ALTER TABLE Employees ADD JoiningDate date;

TABEL TRUNCATE

TRUNCATE TABLE mbusak entri data saka tabel ing basis data, nanging tetep nggawe struktur tabel.

Tuladha:

TRUNCATE TABLE temp_table

TABEL DROP

DROP TABLE pratelan mbusak kabeh tabel kanthi parameter kolom lan setelan datatype.

Tuladha:

DROP TABLE temp_table

DROP DATABASE

DROP DATABASE mbusak kabeh database sing ditemtokake bebarengan karo kabeh paramèter lan data.

Ati-ati banget nalika nggunakake printah iki.

Tuladha:

DROP DATABASE temp_db

Gegandhengan: