SQL Statements kya hote hain
SQL (Structured Query Language) statement ek query ya command hoti hai jo relational database mein data pe alag-alag operations perform karne ke liye use hoti hai. SQL statements ka use database ke saath interact karne ke liye hota hai — jaise data retrieve karna, records update karna, naya data insert karna, aur existing records delete karna.
SQL statements ko mainly 4 categories mein baanta gaya hai:
1. Data Definition Language (DDL):
Ye statements database ka structure define/modify karne ke kaam aate hain.
CREATE TABLE Employees (
ID int,
Name varchar(255),
Age int,
Department varchar(255)
);
ALTER TABLE Employees ADD COLUMN Salary int;
DROP TABLE Employees;
2. Data Manipulation Language (DML):
Ye actual data ke saath kaam karte hain — retrieve, insert, update, delete.
SELECT * FROM Employees;
INSERT INTO Employees (ID, Name, Age, Department) VALUES (1, 'John Doe', 30, 'HR');
UPDATE Employees SET Age = 31 WHERE ID = 1;
DELETE FROM Employees WHERE ID = 1;
3. Data Control Language (DCL):
Ye access control ke liye use hote hain — kaun user kya kar sakta hai, wo decide karte hain.
GRANT SELECT ON Employees TO User1;
REVOKE SELECT ON Employees FROM User1;
4. Transaction Control Language (TCL):
Ye transactions ko manage karne ke liye use hote hain.
COMMIT;
ROLLBACK;
SAVEPOINT sp1;
Ye sab statements milke users ko database structures create/modify karne, data insert/update/delete/query karne, data access control karne, aur transactions manage karne mein help karte hain.
GRANT Statement
GRANT statement ka use specific privileges users ya roles ko database objects (tables, views, procedures, etc.) pe dene ke liye hota hai. Ye decide karta hai ki user database objects pe kya operations perform kar sakta hai. Ye DCL (Data Control Language) ka part hai.
Common Privileges:
SELECT — User ko table ya view se data read karne deta hai.
INSERT — User ko naye records insert karne deta hai.
UPDATE — User ko existing records modify karne deta hai.
DELETE — User ko records delete karne deta hai.
ALL PRIVILEGES — User ko saare available privileges de deta hai.
GRANT privilege_name ON object_name TO user_name;
GRANT SELECT, INSERT, UPDATE ON Employees TO User1;
GRANT ALL PRIVILEGES ON Employees TO User1;
GRANT EXECUTE ON PROCEDURE Proc1 TO User1; --User ko stored procedure execute karne deta hai
Privileges Revoke Karna
Pehle diye gaye privileges ko remove karne ke liye REVOKE statement use hota hai:
REVOKE privilege_name ON object_name FROM user_name;
REVOKE SELECT ON Employees FROM User1;
SAVEPOINT Statement
SAVEPOINT statement ka use transaction ke andar ek named point create karne ke liye hota hai, jahan pe zarurat padne par baad mein rollback kiya ja sakta hai. Isse transactions pe zyada granular control milta hai — poore transaction ko affect kiye bina partial rollback possible hota hai.
--Syntax
SAVEPOINT savepoint_name;
1. Savepoint Create Karna:
Maan lo aap ek transaction ke andar multiple operations kar rahe ho aur beech mein ek savepoint banana chahte ho.
BEGIN TRANSACTION;
INSERT INTO Employees (ID, Name, Age, Department) VALUES (1, 'John Doe', 30, 'HR');
SAVEPOINT sp1;
INSERT INTO Employees (ID, Name, Age, Department) VALUES (2, 'Jane Smith', 25, 'Finance');
SAVEPOINT sp2;
UPDATE Employees SET Age = 26 WHERE ID = 2;
SAVEPOINT sp3;
2. Savepoint Tak Rollback Karna:
Agar aapko lagta hai ki last operation galat tha aur usse undo karna hai, to aap kisi specific savepoint tak rollback kar sakte ho.
ROLLBACK TO sp2;
--Ye command sp2 ke baad hue changes undo kar degi, lekin sp2 se pehle ke changes waise hi rahenge.
3. Transaction Commit Karna:
Savepoints create karne aur unme se kisi ek tak rollback karne ke baad, aap transaction ko commit karke saare changes permanent kar sakte ho.
COMMIT;
4. Pura Transaction Rollback Karna:
Zarurat padne par aap saare savepoints ko ignore karke pura transaction bhi rollback kar sakte ho.
ROLLBACK;
Example Scenario
Socho aap ek inventory system update kar rahe ho jahan ek single transaction ke andar multiple tables update karni hain. Agar kisi ek update ke dauraan error aa jaye, to aap chahoge ki previous successful updates discard kiye bina ek specific point tak rollback ho jaaye.
BEGIN TRANSACTION;
-- Step 1: Product quantities update karo
UPDATE Products SET Quantity = Quantity - 10 WHERE ProductID = 1;
SAVEPOINT sp1;
-- Step 2: Sales table mein record insert karo
INSERT INTO Sales (ProductID, Quantity, SaleDate) VALUES (1, 10, '2024-07-10');
SAVEPOINT sp2;
-- Step 3: Customer orders update karo
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1001;
-- Maan lo yahan error aa gaya
-- Error se pehle wale savepoint tak rollback karo
ROLLBACK TO sp2;
-- Agar baaki sab theek hai to transaction commit karo
COMMIT;
Is scenario mein, sp2 tak rollback karne se sirf last update hi undo hoti hai, aur usse pehle ke changes preserve rehte hain.