SQL COMMANDS

SQL

SQL commandalarai ishlab relational databaseda saqlangan ma’lumotlarni manipulyatsiya qilish uchun foydalanadigan maxsus kalit so’zlar yoki SQL statement laridir.

SQL commandalarni quyidagi turkumlarga ajratishimiz mumkin:

Data definition language(DDL):

DDL — ma’lumotlar bazasini lohashtiruvchi SQL commandalaridir. developerlar biznes talablari asosida ma’lumotlar bazasi ob’ektlarini yaratish va o’zgartirish uchun DDL-dan foydalanadilar.

CREATE : Bu buyruq ma’lum bir ob’ektni yaratish uchun ishlatiladi. Masalan, table, indeks, funksiya, protsedura yoki boshqa ma’lumotlar bazasi ob’ektlari.

-- create table elgilangan ustunlar va ma'lumotlar turlari bilan yangi table yaratadi.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

-- create index
CREATE INDEX idx_department ON employees (department_id);

ALTER: Bu buyruq mavjud ma’lumotlar bazasi ob’ektini o’zgartirish uchun ishlatiladi. Bu ob’ekt strukturasi, tables va boshqa xususiyatlari o’zgartirilishi mumkin.

-- add column: mavjud table ustunlarini ochirish, o'zgartirish yoki yangi ustun qo'shish uchun ishlatiladi.
ALTER TABLE 
  Employees
ADD COLUMN 
  Salary DECIMAL(10, 2);

DROP : mavjud objectlarni o’chirb yuborish

DROP TABLE employees;

DROP INDEX idx_department;

Data query language (DQL)

DQL relational databaselarda saqlangan ma’lumotlarni olish uchun ko’rsatmalardan iborat. Dasturiy ta’minot ilovalari SQL jadvalidan aniq natijalarni filtrlash va qaytarish uchun SELECT buyrug’idan foydalanadi.

SELECT 
  first_name, 
  last_name 
FROM 
  employees 
WHERE 
  department_id = 1;

Data manipulation language (DML)

DML relational databaselarga yangi ma’lumot yozish yoki mavjud ma’lumotlarni o’zgaritish commandalaridan iborat

INSERT INTO: bu kommanda tablelarga yangi malumotlar yozish uchun ishlatiladi:

INSERT INTO 
  employees (
    first_name, 
    last_name, 
    department_id
  ) 
VALUES (
      'John', 
      'Doe', 
       1
);

UPDATE: bu kommanda tableda mavjud bo’lgan datalarni o’zgartirish uchun ishlatiladi:

UPDATE 
  employees  
SET 
  salary = 60000 
WHERE 
  department_id = 2;

DELETE FROM: bu komanda tabledagi maxsus conditionga mos kelgan yoki kelmagan datalarni o’chirish uchun ishlatiladi:

DELETE FROM 
  customers 
WHERE 
  last_purchase_date < '2023-01-01';

TRUNCATE TABLE: bu commada table structurasini o’zgartimagan holda tabledagi barcha ma’lumotlarini ochirib yuboradi:

TRUNCATE TABLE 
  logs;

SELECT INTO: bu komanda orqali bir table dan datalar olinib boshqa tablega yoziladi:

SELECT 
  first_name, 
  last_name 
INTO 
  new_employees 
FROM 
  employees 
WHERE 
  hire_date > '2023-01-01';

MERGE (or UPSERT): Bu komanda ma’lum shartlar asosida qo’shish, yangilash yoki o’chirish amallarini bajaradi, ko’pincha ma’lumotlarni sinxronlashtirish uchun ishlatiladi:

MERGE INTO 
  target_table AS target
USING 
  source_table AS source
  ON target.id = source.id
WHEN 
  MATCHED THEN
    UPDATE SET 
      target.value = source.value
  WHEN NOT 
    MATCHED 
  THEN INSERT (
          id, 
          value
       ) VALUES (
          source.id, 
          source.value
      );

WITH (Common Table Expression, CTE): Ushbu clause bitta SQL bayonoti kontekstida foydalanish mumkin bo’lgan vaqtinchalik natijalar to’plamini aniqlash imkonini beradi.

WITH high_salary_employees AS (
    SELECT * FROM employees WHERE salary > 80000
)
SELECT * FROM high_salary_employees;

Data control language(DCL):

Database administratorlari boshqa foydalanuvchilar uchun ma’lumotlar bazasiga kirishni boshqarish yoki ruxsat berish (DCL)dan foydalanadilar.

Get perfectogo’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

GRANT: This command is used to provide specific privileges or permissions to users or roles. Example:

GRANT 
  SELECT, 
  INSERT 
ON 
  employees 
TO 
  user1;

REVOKE: This command is used to revoke previously granted privileges from users or roles. Example:

REVOKE 
  UPDATE, 
  DELETE 
ON 
  orders 
FROM 
  user2;

Transaction control language (TCL):

SQL standards are a set of formally defined guidelines of the structured query language (SQL). The American National Standards Institute (ANSI) and International Organization for Standardization (ISO) adopted the SQL standards in 1986. Software vendors use the ANSI SQL standards to build SQL database software for developers.

COMMIT: This command is used to permanently save the changes made during the current transaction, making them a permanent part of the database:

COMMIT;

ROLLBACK: This command is used to undo all changes made during the current transaction and restore the database to its state before the transaction started:

ROLLBACK;

SAVEPOINT: This command is used to set a named savepoint within a transaction. Savepoints allow you to roll back to a specific point within a transaction:

SAVEPOINT before_update;

ROLLBACK TO SAVEPOINT: This command is used to undo changes made since a specific savepoint within a transaction and restore the database to that savepoint:

ROLLBACK TO SAVEPOINT before_update;

RELEASE SAVEPOINT: Description: This command is used to remove a previously set savepoint within a transaction:

RELEASE SAVEPOINT before_update;

SET TRANSACTION: This command is used to set properties related to a transaction, such as isolation level or read/write mode:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Begin the transaction
BEGIN;

-- Step 1: Deduct $100 from Account A
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'A123';

-- Step 2: Add $100 to Account B
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'B456';

-- Insert a record of the transaction into the transactions table
INSERT INTO transactions (from_account, to_account, amount, transaction_date)
VALUES ('A123', 'B456', 100, NOW());

-- Commit the changes to the database
COMMIT;

-- End the transaction