SQL (Мова структурованих запитів)

База даних

• База даних — це організована колекція даних, до якої можна легко отримати доступ, керувати нею та оновлювати.
• Вона зберігає дані в структурованому вигляді, що забезпечує швидкий доступ і ефективне управління.

Типи баз даних:

• Реляційна база даних (RDB): Зберігає дані в таблицях (рядки та стовпці). Приклад: MySQL, Oracle.
• Нереляційна база даних (NoSQL): Зберігає дані у форматах, таких як документи, пари ключ-значення тощо. Приклад: MongoDB, Cassandra.

. СУБД (Система управління базами даних)

• СУБД (DBMS) — це програмне забезпечення, яке допомагає створювати, керувати та маніпулювати базою даних._
• Вона забезпечує безпеку даних, цілісність і дозволяє багатокористувацький доступ.
• СУБД може бути:
• РСУБД (RDBMS): Працює з структурованими, таблицями даними. Приклад: MySQL, PostgreSQL.
• Нереляційна СУБД: Працює з неструктурованими або напівструктурованими даними. Приклад: MongoDB.

Ключові функції СУБД:

1. Зберігання даних: Ефективно зберігає та витягує дані.
2. Цілісність даних: Забезпечує точність та узгодженість даних.
3. Безпека даних: Захищає дані за допомогою автентифікації та керування доступом.
4. Контроль за одночасним доступом: Дозволяє кільком користувачам одночасно отримувати доступ до даних.

СПИСОК ТИПІВ ДАНИХ SQL

• Числові типи даних:

• Типи даних рядків:

• Типи даних для дати і часу, бінарні, булеві:

Операції CRUD в SQL

• CRUD означає Create, Read, Update, Delete — це чотири основні операції, що виконуються над даними в базі даних. В SQL ці операції виконуються за допомогою специфічних команд.

CRUD

Загальні команди SQL:

команди

Мова визначення даних (DDL):

• CREATE, ALTER, DROP, TRUNCATE, RENAME (Для визначення та зміни структури бази даних.)

Мова маніпулювання даними (DML):

• INSERT, UPDATE, DELETE (Для маніпулювання даними.)

Мова запитів до даних (DQL):

• SELECT (Для отримання даних з бази даних.)

Мова керування даними (DCL):

._
GRANT , REVOKE (Для керування доступом до даних.)

Мова керування транзакціями (TCL)

• SAVEPOINT
• COMMIT
• ROLL BACK

Мова визначення даних (DDL):

-- створити базу даних   
CREATE DATABASE Movies;  

-- використовувати базу даних   
use Movies;

-- створити таблицю  
CREATE TABLE movies (  
 movie_id INT PRIMARY KEY auto_increment,  
 title VARCHAR(100) ,  
 release_date DATE,  
 genre VARCHAR(50),  
 director VARCHAR(50),  
 lead_actor VARCHAR(50),  
 language VARCHAR(30));   

-- змінити таблицю   
 alter table movies   
 add column box_office_collection DECIMAL(10,2);

-- DROP   
alter table movies  
drop column genre;

-- перейменувати  
alter table movies  
rename column title to movie_title;

- Truncate  
truncate table movies;

Мова маніпулювання даними (DML):

-- вставити   
INSERT INTO movies (title, release_date, genre, director, lead_actor, language, box_office_collection)   
VALUES   
( 'salaar', '2023-12-21', 'drama', 'neel', 'prabhas', 'telugu', 75000000.50),  
( 'devara', '2024-11-24', 'drama', 'koritala shiva', 'ntr', 'telugu', 42000000.00),  
( 'puspa 2', '2024-12-5', 'action', 'sukumar', 'allu arjun', 'telugu', 55000000.75),  
( 'saripodha sanivaram', '2024-08-18', 'action adventure', 'odela', 'nani', 'telugu', 30000000.25),  
('guntur karam', '2024-1-1', 'comedy', 'trivikram', 'mahesh babu', 'telugu', 88000000.00);

-- оновити   
update movies set director = "prasanth_neel" where title = "salaar";

оновлення

- - DELETE  
drop table movies   
;

Мова запитів до даних (DQL):

-- select (відображення записів)   
select * from movies;

select title ,lead_actor from movies; (отримання стовпців)

Типи обмежень у SQL:

1. Обмеження NOT NULL
2. Унікальні обмеження
3. Первинний ключ
4. Зовнішній ключ
5. Обмеження CHECK
6. Обмеження за замовчуванням
   7.
   Унікальні обмеження

приклад

create table movies_list (  
 moviesID INT primary key auto_increment, ## первинний ключ (parenttable)  
 actor VARCHAR(50) NOT NULL, ## немає значень NULL   
 side_actor VARCHAR(50) unique, ## немає повторюваних значень  
 female_lead varchar(50) unique);

create table movies_list1 (  
 movie_id INT,  
 producer VARCHAR(50),  
 release_date date default "2024-8-9", ## якщо немає значення, заповнюється значенням за замовчуванням  
 foreign key (movie_id) references movies_list (moviesID)); ## зовнішній ключ, що acts as child table

Запит SQL :

Оператори порівняння:

= рівно,

> більше, < менше,

=> більше або рівно,

=< менше або рівно,

<> не рівно ..

приклад:

select title ,lead_actor,box_office_collection from movies  
where box_office_collection > 55000000.75 ; ## більше ніж  


select title ,lead_actor,box_office_collection from movies  
where box_office_collection <= 55000000.75 ; ## менше або рівно

Оператор Like:

• Оператор Like використовується для виконання запитів до рядків.
• Цей оператор особливо корисний у операторі where для вибірки всіх рядків, що відповідають заданому шаблону.
• % знак процента (представляє нуль або більше символів)
• підкреслення (представляє один символ)

select * from movies  
where title like "%ar"; ## % представляє нуль або більше символів)  

select lead_actor,title from movies  
where lead_actor like "p_a_h_s"; ## представляє один символ

Логічні оператори:

• Ми можемо виконувати запити на основі кількох умов.
• AND (виконуються дві або більше умови)
• OR (виконується хоча б одна з заданих умов)
• NOT

select title,box_office_collection from movies  
 where language = "telugu" and box_office_collection >= "30000000.25";  
 select * from movies; ## оператор AND  

select title,box_office_collection from movies ## оператор OR  
where language = "telugu" or box_office_collection >= "30000000.25";

select title,box_office_collection from movies  
where not title like "salaar"; # оператор NOT  
 Оператор Between:

Оператори In та Between:

• Вибирає відповідні рядки з таблиці, якщо значення стовпця входить у задані значення.

select * from movies  
where title in ("devara");

Оператор Between:

• Оператор Between використовується для вибірки значень, що знаходяться в заданому діапазоні.

select title,box_office_collection from movies   
where box_office_collection between "42000000.00" and "82000000.00";

Order By та Distinct:

• Сортує дані в порядку зростання або спаду.

select * from movies   
order by box_office_collection desc; ## порядок спадання   

select * from movies ## порядок зростання  
order by box_office_collection ;  


select * from movies ## порядок зростання  
order by lead_actor ;

Limit:

• Клауза Limit використовується для вказівки кількості рядків.

select * from movies   
limit 3;

Offset:

• Вказує кількість рядків, які потрібно пропустити перед тим, як почати повертати рядки.

select * from movies   
limit 3 offset 4;

Псевдоніми:

• За допомогою ключового слова AS можна задати тимчасові альтернативні імена для стовпців у результатах.

SELECT SUM(salary) AS total_salary FROM employees_ofc; ## сума заробітної плати

Порядок виконання SQL-запитів:

Віконні функції:

• Віконні функції, також відомі як windowing functions або analytic functions, використовуються в SQL для виконання обчислень на наборі рядків, що відносяться до поточного рядка.
  Ці функції часто використовуються в аналізі даних для обчислення агрегатів, рейтингів і тенденцій без групування даних._

Ранг (Rank):

• Присвоює ранг кожному рядку в розділі, з пропусками в рангу для однакових значень.
• Якщо два рядки мають однаковий ранг, наступний ранг пропускається.

select roll_num ,std_name ,total_marks,  
 rank () over (order by total_marks desc) as ranks_of_students  
from students;

Щільний ранг (Dense Rank):

• Схожий на RANK, але не залишає пропусків у рангах для однакових значень.
• Консистентні ранги присвоюються, навіть якщо існують однакові значення.

select roll_num ,std_name ,total_marks,  
dense_rank () over (order by total_marks desc) as dense_ranks_of_students  
from students;

Агрегати та Group By:

• Ми використовуємо агрегування в таких випадках для об'єднання кількох значень в одне ...

Функції агрегації:

• Count (підрахунок)
• Sum (сума)
• Average (середнє)
• Min (мінімум)
• Max (максимум)

select count(*)from employees_ofc; ## підрахунок

SELECT SUM(salary) AS total_salary FROM employees_ofc; ## сума зарплати

SELECT min(salary) AS lowest_salary FROM employees_ofc; ## мінімальна зарплата

SELECT max(salary) AS highest_salary FROM employees_ofc; ## максимальна зарплата

SELECT avg(salary) AS avg_salary FROM employees_ofc; ## середнє значення зарплати

Group By:

• Клауза group by в SQL використовується для групування рядків за певними атрибутами.

select department_id,count(*) as empployee_count from employees_ofc   
group by department_id; ;

select department_id,avg(salary) as dept_avg from employees_ofc   
group by department_id; ;

Group By з Where:

• Ми можемо використовувати клауза Where для фільтрації даних перед виконанням агрегації.

select department_id , employee_name , avg(salary) from employees_ofc  
where department_id = "103"  
group by department_id , employee_name ;

Group BY з having:

Клауза Having використовується для фільтрації рядків після застосування group by...

select department_id, sum(salary) as total_salary   
from employees_ofc  
group by department_id  
having SUM(salary) > 100000;

Зв'язки (Relationships):

• Використання SQL joins, включаючи INNER JOIN, LEFT JOIN, і RIGHT JOIN.

Join

Таблиця 1

CREATE TABLE movies (  
 movie_id INT PRIMARY KEY auto_increment,  
 title VARCHAR(100) NOT NULL,  
 release_date DATE ,  
 genre VARCHAR(50),  
 director VARCHAR(50),  
 lead_actor VARCHAR(50),  
 language VARCHAR(30));  

INSERT INTO movies (title, release_date, genre, director, lead_actor, language, box_office_collection)   
VALUES   
( 'salaar', '2023-12-21', 'drama', 'neel', 'prabhas', 'telugu', 75000000.50),  
( 'devara', '2024-11-24', 'drama', 'koritala shiva', 'ntr', 'telugu', 42000000.00),  
( 'puspa 2', '2024-12-5', 'action', 'sukumar', 'allu arjun', 'telugu', 55000000.75),  
( 'saripodha sanivaram', '2024-08-18', 'action adventure', 'odela', 'nani', 'telugu', 30000000.25),  
('guntur karam', '2024-1-1', 'comedy', 'trivikram', 'mahesh babu', 'telugu', 88000000.00);

Таблиця 2

CREATE TABLE movies_1 (  
 movie_id INT PRIMARY KEY auto_increment,  
 title VARCHAR(100) NOT NULL,  
 release_date DATE ,  
 genre VARCHAR(50),  
 director VARCHAR(50),  
 lead_actor VARCHAR(50),  
 language VARCHAR(30),  
 box_office_collection DECIMAL(10,2),  
 foreign key (movie_id) references movies(movie_id));

INSERT INTO movies_1 (title, release_date, genre, director, lead_actor, language, box_office_collection)   
VALUES   
( 'pataan', '2024-12-21', 'action', 'sidharth', 'sharukh khan', 'hindi', 95000000.50),  
( 'kanguva', '2024-11-24', 'timemachine', 'shiva', 'surya', 'tamil', 10000000.00),  
( 'drushyam', '2021-11-25', 'thriller', 'jhoseph', 'mohanlal', 'malayalam', 35000000.75),  
( 'baby', '2023-06-28', 'love story', 'skn', 'anandh', 'telugu', 30000000.25),  
('goat', '2024-1-1', 'action', 'venkat prabhu', 'vijay', 'tamil', 7000000.00);

Таблиця 2

Внутрішнє з'єднання (Inner Join):

SELECT movies.title AS movie_title, directors.director_name FROM movies  
INNER JOIN directors  
ON movies.director_id = directors.director_id;

Ліве з'єднання (Left Join):

SELECT movies.title AS movie_title, directors.director_name  
FROM movies  
LEFT JOIN directors  
ON movies.director_id = directors.director_id;

Праве з'єднання (Right Join):

SELECT movies.title AS movie_title, directors.director_name  
FROM movies  
right JOIN directors  
ON movies.director_id = directors.director_id;

DCL (Мова керування даними):

• Мова керування даними (DCL) зосереджена на управлінні правами доступу, дозволами та питаннями безпеки в системах баз даних.
• Команди DCL використовуються для контролю того, хто може отримувати доступ до даних, змінювати їх або виконувати адміністративні завдання в межах бази даних.
• DCL є важливою частиною безпеки бази даних, що забезпечує захист даних і гарантує, що доступ мають лише авторизовані користувачі з необхідними правами.
• Існують дві основні команди DCL в SQL: GRANT та REVOKE.

Grant:

• Команда GRANT використовується для надання конкретних привілеїв або дозволів користувачам або ролям.
  Привілеї можуть включати можливість виконувати різні дії над таблицями, уявленнями, процедурами та іншими об'єктами бази даних._

Створення таблиці :

select * from employees;  
CREATE TABLE employees_ofc (  
 employee_id INT PRIMARY KEY,  
 employee_name varchar(50),  
 department_id INT,  
 salary DECIMAL(10, 2),  
 job_title VARCHAR(50)  
);  


INSERT INTO employees_ofc (employee_id,employee_name, department_id, salary, job_title)  
VALUES   
(1,"mahesh", 101, 50000, 'Software Engineer'),  
(2,"prabhas", 101, 60000, 'Software Engineer'),  
(3,"samrat", 102, 45000, 'Analyst'),  
(4,"srinish", 102, 70000, 'Analyst'),  
(5, "sohail",103, 55000, 'Manager'),  
(6,"mujeeb", 103, 50000, 'Manager'),  
(7, "sunraj",104, 80000, 'Senior Manager'),  
(8,"maharaj" , 104,90000,'Senior Manager');

create view se_view as select * from employees_ofc  
 where job_title = "SoftWare Engineer"; ## створення уявлення тільки для 
 програмістів




 create view analyst_view as select * from employees_ofc  
 where job_title = "Analyst";  
 ## створення уявлення тільки для аналітиків   



 create view manager_view as select * from employees_ofc  
 where job_title = "Senior manager"; ## створення уявлення тільки для старших менеджерів

select * from analyst_view; # можемо переглядати тільки для ролі аналітика   
 select * from manager_view; #можемо переглядати тільки для ролі менеджера  
select * from se_view; #можемо переглядати тільки для ролі програміста

REVOKE:

• Команда REVOKE використовується для видалення або скасування конкретних привілеїв або дозволів, які раніше були надані користувачам або ролям.

Збережена процедура (Stored Procedure):

• Збережена процедура (Stored Procedure) — це попередньо компільований набір з одного або кількох SQL-запитів, який зберігається в базі даних і може виконуватися як єдине ціле. Збережені процедури покращують продуктивність, сприяють повторному використанню та підвищують безпеку, інкапсулюючи складну логіку.

Створення процедури:

Виклик процедури:

вихід

TCL (Мова керування транзакціями):

• Мова керування транзакціями (TCL) займається управлінням транзакціями в базі даних. Команди TCL використовуються для контролю ініціації, виконання та завершення транзакцій, які є послідовностями одного або кількох SQL-запитів, що виконуються як єдине ціле.
  Транзакції гарантують консистентність, цілісність і надійність даних у базі даних, об'єднуючи пов'язані операції разом і або зберігаючи, або скасовуючи зміни в залежності від успіху або невдачі цих операцій._
• Існує три основні команди TCL в SQL: COMMIT, ROLLBACK і SAVEPOINT.

COMMIT:

• Команда COMMIT використовується для постійного збереження змін, зроблених під час транзакції.

start transaction;  
insert into employees_ofc (employee_id, employee_name, department_id, salary, job_title)  
values (9, "raj", 105, 60000, "HR Manager");  


COMMIT;

Roll Back:

• Скасовує всі зміни, зроблені в поточній транзакції з моменту останнього COMMIT або SAVEPOINT.

update employees_ofc set salary = salary * 2 where department_id = 101;  

select * from employees_ofc;

Оновлена зарплата * 2 перед скасуванням

rollback;  
select * from employees_ofc;

Після скасування

Savepoint:

• Створює точку в середині транзакції, до якої можна повернутися пізніше без впливу на всю транзакцію.

Оператори множин:

• Операції множин у SQL використовуються для комбінування або маніпулювання результатами кількох SELECT-запитів.

Є чотири основні операції множин у SQL:

• UNION
• INTERSECT
• EXCEPT (або MINUS)
• UNION ALL

Union:

• Оператор UNION об'єднує результати двох або більше SELECT-запитів в один набір результатів.
• За замовчуванням він видаляє дублікати, тобто якщо в наборах результатів є однакові рядки, в остаточний результат потрапить лише один екземпляр кожного рядка.

create table customer(  
customer_id int,  
customername varchar(30)); ##таблиця 1  

insert into customer  
values(1,"mahesh"),  
(2,"ramesh"),  
(2,"naresh");  

create table order_table(  
order_id int, ## таблиця 2  
ordername varchar(30));  

insert into order_table  
values(101,"redmi note" ),  
(102,"vivo");

select * from customer   
union  
select * from order_table

Union All:

• Оператор UNION ALL виконує ту ж саму функцію, що і оператор UNION, але не видаляє дублікати з результатів. Він просто об'єднує всі рядки з різних результатів.

select * from customer   
union all  
select * from order_table

Intersect:

• Оператор INTERSECT повертає спільні рядки, які існують у наборах результатів двох або більше SELECT-запитів.
  Він повертає тільки унікальні рядки, які з'являються в усіх наборах результатів._

select * from customer   
intersect  
select * from order_table

EXCEPT

• Оператор EXCEPT (також відомий як MINUS в деяких базах даних) повертає унікальні рядки, які присутні в наборі результатів першого SELECT-запиту, але відсутні в наборі результатів другого SELECT-запиту.

select * from customer   
expect   
select * from order_table

Підзапити:

• Підзапити, також відомі як вкладені запити або внутрішні запити, дозволяють використовувати результат одного запиту (внутрішнього запиту) як вхідні дані для іншого запиту (зовнішнього запиту).
• Підзапити часто використовуються для отримання даних, які будуть використовуватися для фільтрації, порівняння або обчислення в контексті більшого запиту.

create table customer(  
customer_id int primary key ,  
customername varchar(30));  

insert into customer ##таблиця 1  
values(1,"mahesh"),  
(2,"ramesh"),  
(3,"naresh");  

create table order_table(  
order_id int,  
ordername varchar(30),  
order_mny int, ##таблиця 2  
Quantity int,  
foreign key (order_id) references customer(customer_id));  


insert into order_table  
values(1,"redmi note",55000 ,2),  
(2,"vivo",80000,1),  
(3,"iphone",110000,3);

select customername from customer ##підзапити   
where customer_id in (select order_id from order_table);

select customername from customer  
where customer_id = (select max(customer_id) from customer);

Перекладено з: SQL(Structured Query Language)