8 පාඩම - Data Manipulation Language-DML

වගුවකින් දත්ත ලබා ගැනීම

SQL SELECT Statement

SELECT column1, column2, ...

Example - FROM table_name; SELECT * FROM table_name;

වගුවකින් තීරු කිහිපයක දත්ත ලබා ගැනීම

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example - SELECT CustomerName, City FROM Customers;

කොන්දේසි සහිතව දත්ත විමසීම

WHERE Syntax

නිශ්චිත කොන්දේසියක් සපුරාලන වාර්තා පමණක් උපුටා ගැනීම සඳහා WHERE වගන්තිය භාවිතා කරයි.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example - SELECT * FROM Customers   WHERE Country='Sri Lanka';

SQL AND, OR and NOT Operators

AND Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example -

SELECT * FROM Customers  WHERE Country='Germany' AND City='Berlin';

SELECT * FROM Customers WHERE City='Berlin' OR City='München';

SELECT * FROM Customers  WHERE Country='Germany' OR Country='Spain';

SELECT * FROM Customers  WHERE NOT Country='Germany';

Combining AND, OR and NOT

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';

දත්ත පිළිවෙලකට සකස් කිරීම

ORDER BY Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Example -

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM Customers
ORDER BY Country, CustomerName;

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

වගුවකට දත්ත ඇතුලත් කිරීම(INSERT)

INSERT INTO Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example-

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

තීරු කිහිපයකට දත්ත ඇතුලත් කිරිම

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

දත්තයක් නැවත සකස් කිරීම -UPDATE

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example-

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

දත්තයක් මකා දැමීම- DELETE

DELETE Syntax

DELETE FROM table_name WHERE condition;

Example-

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

වගුවක ඇතිදත්ත වලින් කිහිපයක් පමණක් ලබා ගැනීම- LIMIT

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Example-

SELECT * FROM Customers
LIMIT 3;

SELECT * FROM Customers
WHERE ROWNUM <= 3;

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;