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.
Pranyatan SQL bisa diklumpukake ing macem-macem kategori:
Ing kiriman iki, kita bakal nutupi prentah DDL, DML lan DQL.
Babagan pisanan sing kudu ditindakake kanggo nggarap SQL yaiku nggawe database. Sing CREATE DATABASE
pratelan ora persis sing.
Tuladha:
CREATE DATABASE testDB
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) );
Sing INSERT INTO
pernyataan masang baris data anyar menyang tabel
Tuladha:
INSERT INTO Employees (FirstName, LastName, Department) VALUES ('Sam', 'Burger', 'IT');
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;
Sing SELECT
printah nalika digunakake kanthi tanda bintang *
operator, milih kabeh cathetan saka tabel sing ditemtokake.
Tuladha:
SELECT * FROM Employees
SELECT DISTINCT
mung ngasilake data sing beda; yaiku ora kalebu entri duplikat.
Tuladha:
SELECT DISTINCT Department FROM Employees;
Sing SELECT INTO
pratelan milih data sing ditemtokake saka tabel lan nyalin menyang tabel liyane.
Tuladha:
SELECT firstName, entryGraduated INTO StudentAlumni FROM Students;
SELECT TOP nemtokake jumlah utawa persentasi entri data sing maksimal kanggo bali ing asil sing disetel.
SELECT TOP 50 PERCENT * FROM Customers;
Sing WHERE
klausa digunakake kanggo nyaring asil adhedhasar kahanan sing ditemtokake.
Tuladha:
SELECT * FROM Employees WHERE department = 'IT';
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;
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;
Sing IN
operator kalebu macem-macem nilai menyang klausa WHERE.
Tuladha:
SELECT * FROM Employees WHERE Department IN ('IT', 'Graphics', 'Marketing');
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`;
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
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 JOIN
nggabungake larik saka macem-macem tabel.
Tuladha:
SELECT Orders.ID, Customers.Name FROM Orders INNER JOIN Customers ON Orders.ID = Customers.ID;
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;
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;
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;
Sing DELETE
pratelan mbusak baris tartamtu saka tabel sing cocog karo kahanan sing ditemtokake.
Tuladha:
DELETE FROM Employees WHERE FirstName = 'Sam' AND LastName = 'Burger';
Kita nggunakake ALTER TABLE
kanggo nambah utawa mbusak kolom saka tabel.
Tuladha:
ALTER TABLE Employees ADD JoiningDate date;
TRUNCATE TABLE
mbusak entri data saka tabel ing basis data, nanging tetep nggawe struktur tabel.
Tuladha:
TRUNCATE TABLE temp_table
DROP TABLE
pratelan mbusak kabeh tabel kanthi parameter kolom lan setelan datatype.
Tuladha:
DROP TABLE temp_table
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: