Sql commands(dml, ddl, dcl ,tcl) for data analysis

Предоставлять, отзывать и запрещать разрешения для базы данных

Язык управления данными (DCL) является подмножеством языка структурированных запросов (SQL) и позволяет администраторам баз данных настраивать безопасный доступ к реляционным базам данных. Он дополняет язык определения данных (DDL), который используется для добавления и удаления объектов базы данных, и язык манипулирования данными (DML), используемый для извлечения, вставки и изменения содержимого базы данных.

DCL является самым простым из подмножеств SQL, поскольку он состоит только из трех команд: GRANT, REVOKE и DENY. В совокупности эти три команды предоставляют администраторам возможность гибко устанавливать и удалять разрешения для базы данных.

Добавление разрешений с помощью команды GRANT

Команда GRANT используется администраторами для добавления новых разрешений пользователю базы данных. У него очень простой синтаксис, определенный следующим образом:

 GRANT   ON   TO

Вот краткое описание каждого из параметров, которые вы можете указать с помощью этой команды:

Привилегия – может быть ключевым словом ALL (для предоставления широкого спектра разрешений) или определенным разрешением базы данных или набором разрешений. Примеры включают CREATE DATABASE, SELECT, INSERT, UPDATE, DELETE, EXECUTE и CREATE VIEW.

Объект – может быть любым объектом базы данных. Допустимые параметры привилегий зависят от типа объекта базы данных, который вы включаете в это предложение. Как правило, объект будет либо базой данных, функцией, хранимой процедурой, таблицей или представлением.

Пользователь – может быть любым пользователем базы данных. Вы также можете заменить роль для пользователя в этом пункте, если хотите использовать безопасность баз данных на основе ролей.

Если вы добавите необязательное условие WITH GRANT OPTION в конце команды GRANT, вы не только предоставите указанному пользователю разрешения, определенные в операторе SQL, но и дадите пользователю возможность предоставить те же разрешения. другим пользователям базы данных

По этой причине используйте этот пункт с осторожностью.

Например, предположим, что вы хотите предоставить пользователю Джо возможность извлекать информацию из таблицы сотрудников в базе данных под названием HR. Вы можете использовать следующую команду SQL:

 ВЫБРАТЬ ГРАНТ  НА HR.employees  TO Джо

Теперь у Джо будет возможность извлекать информацию из таблицы сотрудников. Однако он не сможет предоставить другим пользователям разрешение на извлечение информации из этой таблицы, поскольку вы не включили условие WITH GRANT OPTION в оператор GRANT.

Отмена доступа к базе данных

Команда REVOKE используется для удаления доступа к базе данных у пользователя, ранее предоставившего такой доступ. Синтаксис этой команды определяется следующим образом:

 REVOKE    ON   FROM

Вот краткое описание параметров команды REVOKE:

Разрешение – указывает разрешения для базы данных, которые необходимо удалить для указанного пользователя. Команда отменяет оба утверждения GRANT и DENY, ранее сделанные для указанного разрешения.
Объект – может быть любым объектом базы данных. Допустимые параметры привилегий зависят от типа объекта базы данных, который вы включаете в это предложение. Как правило, объект будет либо базой данных, функцией, хранимой процедурой, таблицей или представлением.
Пользователь – может быть любым пользователем базы данных. Вы также можете заменить роль для пользователя в этом пункте, если хотите использовать безопасность баз данных на основе ролей.
Предложение GRANT OPTION FOR устраняет возможность указанного пользователя предоставлять указанное разрешение другим пользователям. Примечание . Если вы включите условие GRANT OPTION FOR в оператор REVOKE, основное разрешение будет не отменено. Этот пункт отменяет только возможность предоставления.
Параметр CASCADE также отменяет указанное разрешение у всех пользователей, которым указанный пользователь предоставил разрешение.

Например, следующая команда отзывает разрешение, предоставленное Джо в предыдущем примере:

 ОТМЕНИТЬ ВЫБРАТЬ  НА HR.employees  ОТ Джо

Явный отказ в доступе к базе данных

Команда DENY используется для явного запрета пользователю получать определенное разрешение. Это полезно, когда пользователь является участником роли или группы, которой предоставлено разрешение, и вы хотите запретить этому отдельному пользователю наследовать разрешение путем создания исключения. Синтаксис этой команды следующий:

 DENY   ON   TO

Параметры для команды DENY идентичны параметрам, используемым для команды GRANT.Например, если вы хотите, чтобы Мэтью никогда не получал возможность удалять информацию из таблицы сотрудников, введите следующую команду:

 УДАЛЕНИЕ ДЕНИ  НА HR.employees  TO Matthew

В каких базах данных используют SQL

Все БД можно поделить на два вида: реляционные и нереляционные. Язык SQL нужен для работы с первыми.

SQL настолько тесно связан с реляционными БД, что все нереляционные БД в противовес стали называть NoSQL. Вот и получилось, что SQL — это язык программирования, а NoSQL — тип баз данных.

Про реляционные БД часто говорят, что это набор двумерных таблиц. Прямо как в Excel: со столбцами, строками и ячейками. Это понятная визуализация, хотя и не совсем точная.

Представим, что мы создаём базу данных для небольшой строительной фирмы. Она проектирует загородные дома и передаёт проекты подрядчикам, которые занимаются самим строительством:

Примерная база данных воображаемой строительной фирмыСкриншот: Skillbox Media

Чем же база данных отличается от таблицы? Тем, что в базе:

У столбцов и строк нет определённого положения. Нельзя сказать, что столбец status находится до или после столбца num_floors, а имя Анастасии Романиной — до или после имени Дмитрия Пожарова.
Каждый столбец диктует свой домен, то есть тип данных, к которому могут относиться его значения. Например, в столбцах cost и num_floors могут храниться только числа, а в столбце client — только строки.
Каждая строка должна быть уникальной и не может повторять какую-то другую строку.

Transaction Control Language (TCL)

Transaction control language (TCL) commands are used to handle the transaction within the database. Transactions combine a collection of tasks into a single execution unit.

Each transaction starts with some specific task and finishes when all of the tasks in the group are completed successfully.

The transaction fails if any of the tasks fails. As a result, a transaction has only two outcomes, i.e., either success or failure.

We use the following TCL commands to control the execution of a transaction:

Command	Description
COMMIT	It is a TCL command that is used to save the data permanently. Any DML command, such as -INSERT, DELETE, or UPDATE, can be rolled back if the data is not permanently stored. To be on the safe side, we use the COMMIT command.Basic Syntax:commit;
ROLLBACK	The ROLLBACK command retrieves or restores data to the most recent savepoint or committed state. Suppose the data inserted, removed, or altered is incorrect for any reason.In that case, you can roll back the data to a specific savepoint, or if no savepoint is available, then the rollback is done from the last committed state.Basic Syntax:rollback
SAVEPOINT	This command temporarily saves data at a specific point so it can be rolled back to that point if required.Basic Syntax:savepoint A;
SET TRANSACTION	This command specifies the transaction’s characteristics.Basic Syntax:SET TRANSACTION [Read Write / Read Only];

Conclusion:

SQL Server provides various DDL (Data Definition Language) commands to create and manipulate the database objects such as CREATE, ALTER, TRUNCATE, and DROP
Using these commands a user can change the structure of the database objects.
The DML (Data Manipulation Language) commands in SQL Servers are used to manipulate or modify the data stored in the database.
These commands can be used to INSERT, UPDATE, or DELETE the records from the database.
The DCL (Data Control Language) commands in SQL Server mainly deal with the right, permissions, and other security-related jobs. These commands are GRANT and REVOKE.

I hope you enjoyed it and found it helpful. Please leave a comment and share this post with others.

References: SQL Commands

1) What Is DDL?

DDL, or data definition language, is a programming language for creating and modifying databases. It’s termed a language, but it’s more like syntax in and of itself. Alternatively, a sequence of statements allows the user to define or edit data structures and objects like data tables.

How to Use DDL Commands?

As previously stated, DDL is the component of the SQL syntax that deals with the elements of a database by executing commands (also known as statements) such as:

CREATE
ALTER
DROP
RENAME
TRUNCATE

1) CREATE Statement

It’s only natural to begin from the beginning, with the CREATE command. It’s used to create complete databases and objects, as the name implies:

CREATE object_type object_name;

For example, if we wish to make a table, we must follow the rules as follows:

CREATE TABLE object_name (column_name data_type);

2) ALTER Statement

The CREATE command demonstrates how the ALTER command works, which is what we use to change existing objects. For example, we can use this DDL statement to change the columns in a table by adding, removing, or renaming them.

To add a column called “date of purchase,” start with the same structure, but instead of using CREATE, use ALTER before naming the object’s type and name. Following that, you write out the specific change:

ALTER TABLE sales

ADD COLUMN date_of_purchase DATE;

3) DROP Statement

What happens if you need to remove a database object? Use the DROP statement in that case:

DROP object_type object_name;

For example, suppose you wish to delete the entire “customers” database using a single line of code:

DROP TABLE customers;

4) RENAME Statement

Another useful DDL tool is RENAME, which allows you to rename an object, such as a database table:

RENAME object_type object name TO new_object_name;

To give you an example, if we hadn’t dropped the “customers” table, we could have renamed it “customer data” as follows:

RENAME TABLE customers TO customer_data;

5) TRUNCATE Statement

If we wish to keep using the table as a database object, we can remove data without using DROP. TRUNCATE is the ideal DDL command to use in this situation:

TRUNCATE object_type object_name;

In the context of our example, if we only want to delete the values in our “customers” table, we can use the following code:

TRUNCATE TABLE customers;

DDL (Data Definition Language) Command in SQL

DDL or Data definition language is actually the definition or description of the database structure or schema, it won’t change the data inside the database. Create, modify, and delete the database structures, but not the data. Only These commands are not done by all the users, who have access to the database via an application.

Find more about DDL Command in SQL: DDL Statements in SQL Server

CREATE Command in SQL

SQL Create the database or its object (ie table, index, view, function, etc.).

Syntax

CREATE DATABASE databasename

Example

Syntax

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
….
);

Example

DROP Command in SQL

Drop command helps to delete the object from the database (ie table, index, view, function, etc.).

Syntax

DROP object object_name

Example

Syntax

DROP DATABASE database_name

Example

ALTER Command in SQL

Alter command is helpful to change or modify the structure of the database or its object.

Syntax

ALTER TABLE table_name
ADD column_name datatype

Example

Syntax

ALTER TABLE table_name
DROP COLUMN column_name

Example

1) SQL Server / MS Access

Syntax

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Example

2) My SQL / Oracle (prior version 10G)

Syntax

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Example

3) Oracle 10G and later

Syntax

ALTER TABLE table_name
MODIFY column_name datatype

Example

TRUNCATE Command in SQL

SQL Truncate command helps to remove all records from a table.

Syntax

TRUNCATE TABLE table_name

Example

COMMENT Command in SQL

SQL Comment is helpful to add comments to the data dictionary.»—» is used to comment on the notes.

Syntax

—(notes,examples)

Example

RENAME Command in SQL

SQL Rename is helpful to rename an object existing in the database.

1) PostgreSQL

Syntax

ALTER DATABASE «Old_DatabaseName» RENAME TO «New_DatabaseName»;

Example

2) MySQL

Example

SQL Command for Dump copy

SQL Command for creating new DB

SQL Command for Import

Also for Unix, database names are case-sensitive

3) SQL Server

In SQL Server we can rename the database through server application, by right click the existing database and renaming it.

Где применяют SQL

В индексе TOPDB популярность СУБД определяется по тому, как часто их гуглят. В декабре 2022 года первые пять мест в нём занимают именно реляционные СУБД — вместе они дают больше 70% поисковых запросов.

Скриншот: Top Database index / GitHub

Рейтинг DB-Engines даёт похожие цифры. В декабре 2022 года доля реляционных СУБД составляет 71,7%.

Скриншот: DB-Engines

Без баз данных не будет ни сайтов, ни сетевых приложений, ни крупных информационных систем — нужно же где-то хранить всю информацию. При этом реляционных БД — большинство, а чтобы управлять ими, нужен SQL. Поэтому мало какая вакансия бэкенд-разработчика обходится без требования владеть SQL. По крайней мере, мы такой не нашли.

Но умение работать с базами данных пригодится не только программисту.

Аналитики данных напрямую работают с «сырой» информацией. Чем лучше и свободнее они общаются с БД, тем проще им добывать и обрабатывать нужные данные в нужном виде.

Маркетологам SQL тоже будет полезен для решения аналитических задач.

Тестировщикам понадобится обращаться к БД, потому что это важный компонент любого информационного продукта.

Примеры простых запросов SQL к базам данных.

Рассмотрим основные запросы SQL.

SELECT

1) Выведем все имеющиеся у нас БД:

SELECT name, database_id, create_date FROM sys.databases;

2) Выведем все таблицы в созданной нами ранее БД «b_library»:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’

3) Выводим еще раз имеющиеся у нас записи по авторам книг из созданной выше «tAuthors»:

SELECT * FROM tAuthors;

4) Выведем информацию о том, сколько у нас имеется записей строк в «tAuthors»:

SELECT count(*) FROM tAuthors;

5) Выведем из «tAuthors» две записи, начиная с четвертой. Используя ключевое слово OFFSET, пропустим первые три записи, а благодаря использованию ключевого слова FETCH – обозначим выборку только следующих 2 строк (ONLY):

SELECT * FROM tAuthors ORDER BY AuthorId OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

6) Выведем из «tAuthors» все записи с сортировкой в алфавитном порядке по первой букве имени автора:

SELECT * FROM tAuthors ORDER BY AuthorFirstName;

7) Выведем из «tAuthors данные, предварительно по AuthorId отсортировав их по убыванию:

SELECT * FROM tAuthors ORDER BY AuthorId DESC;

Выберем записи из «tAuthors», значение AuthorFirstName у которых соответствует имени «Александр»:

SELECT * FROM tAuthors WHERE AuthorFirstName=’Александр’;

9) Выберем из «tAuthors» записи, где имя автора AuthorFirstName начинается с «се»:

SELECT * FROM tAuthors WHERE AuthorFirstName LIKE ‘се%’;

10) Выберем из «tAuthors» записи, в которых имя автора (AuthorFirstName) заканчивается на «ат»:

SELECT * FROM tAuthors WHERE AuthorFirstName LIKE ‘%ат’ ORDER BY AuthorId;

Видео курсы по схожей тематике:

SQL Базовый. Разбор ДЗ

Владимир Дымчук

MySQL Базовый

Андрей Бондаренко

How to SQL Базовый

Владимир Дымчук

11) Сделаем выборку всех строк из «tAuthors», значение AuthorId в которых равняется 2 или 4:

SELECT * FROM tAuthors WHERE AuthorId IN (2,4);

12) Выберем в «tAuthors» такую запись AuthorAge, значение которой — наибольшее:

SELECT max(AuthorAge) FROM tAuthors;

13) Проведем выборку из «tAuthors» по столбцам AuthorFirstName и AuthorLastName:

SELECT AuthorFirstName, AuthorLastName FROM tAuthors;

14) Получим из «tAuthors» все строки, у которых AuthorId не равняется трем:

SELECT AuthorId, AuthorFirstName, AuthorLastName FROM tAuthors WHERE AuthorId!=’3′;

INSERT

INSERT – это вид запроса SQL, при применении которого СУБД выполняет добавление новых записей в БД. Добавим в «tAuthors» нового автора – Уильяма Шекспира, 51 год. Соответственно в поле AuthorFirstName добавится Уильям, в AuthorLastName добавится Шекспир, в AuthorAge – 51. В AuthorId, в нашем случае, автоматически добавится значение, инкрементированное от предыдущего на 1.

INSERT INTO tAuthors VALUES (‘Уильям’, ‘Шекспир’, ’51’);

Проверим:

SELECT * FROM tAuthors;

UPDATE

UPDATE – SQL запрос, позволяющий внести изменения или дописывать новую информацию в те записи, которые уже существуют.

Внесем корректировки в шестую запись (AuthorId = 6). Значения изменим для полей имени, фамилии и возраста автора.

UPDATE tAuthors SET AuthorFirstName = ‘Лев’, AuthorLastName=’Толстой’, AuthorAge = ’82’ WHERE AuthorId = ‘6’;

Затем, обратимся к БД, чтобы вывести все имеющиеся записи:

SELECT * FROM tAuthors;

Мы видим изменения информации в записи автора под номером 6.

DELETE

DELETE – SQL запрос, выполняя который в СУБД производится операция удаления определенной строки из таблицы в БД.

Обратимся к «tAuthors» с командой на удаление строки, где AuthorId = 5:

DELETE FROM tAuthors WHERE AuthorId = ‘5’;

Чтобы увидеть изменения, снова обратимся к базе для вывода всех записей:

SELECT * FROM tAuthors;

Мы видим, что запись автора под номером 5 теперь отсутствует в «tAuthors» и, соответственно, не выводится с другими записями.

DROP

DROP – ключевое слово в SQL, применяемое для удаления данных с помощью запроса. К примеру удаление некоторой таблицы из БД.

После рассмотрения ряда простых запросов к БД мы можем полностью удалить нашу таблицу «tAuthors целиком, выполнив простой SQL запрос:

DROP TABLE tAuthors;

Далее рассмотрим сложные запросы SQL.

What Does a DBMS Do?

DBMS stands for Database Management System, as we mentioned above. SQL stands for Structured Query Language.

If you have lots of data that you need to store, you don’t just want to keep it anywhere – then there would be no sense of what that huge amount of data means or can tell you. That’s why we use a DBMS.

A database is basically where we store data that are related to one-another – that is, inter-related data. This inter-related data is easy to work with.

A DBMS is software that manages the database. Some of the commonly used DBMS (software) are MS ACCESS, MySQL, Oracle, and others.

Suppose you have some data like different names, grades, and ID numbers of students. You’d probably prefer to have that data in a nice table where a particular row consists of students’ names, grades, and ID numbers. And to help you organize and read that data efficiently, you’ll want to use a DBMS.

Using a DBMS goes hand in hand with SQL. This is because when you store data and want to access and alter it, you’ll use SQL.

A database stores data in various forms like schemas, views, tables, reports, and more.

Команды языка управления транзакциями

Команды языка управления транзакциями ( TCL (Тгаnsасtiоn Соntrol Language) ) команды позволяют определить исход транзакции. Команды управления транзакциями управляют изменениями в базе данных, которые осуществляются командами манипулирования данными.Транзакция (или логическая единица работы) – неделимая с точки зрения воздействия на базу данных последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации) такая, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует.COMMIT — заканчивает («подтверждает») текущую транзакцию и делает постоянными (сохраняет в базе данных) изменения, осуществленные этой транзакцией. Также стирает точки сохранения этой транзакции и освобождает ее блокировки. Можно также использовать эту команду для того, чтобы вручную подтвердить сомнительную распределенную транзакцию.ROLLBACK — выполняет откат транзакции, т.е. отменяет все изменения, сделанные в текущей транзакции. Можно также использовать эту команду для того, чтобы вручную отменить работу, проделанную сомнительной распределенной транзакцией. Понятие транзакции имеет непосредственную связь с понятием целостности базы данных. Очень часто база данных может обладать такими ограничениями целостности, которые просто невозможно не нарушить, выполняя только один оператор изменения БД. Например, невозможно принять сотрудника в отдел, название и код которого отсутствует в базе данных. В системах с развитыми средствами ограничения и контроля целостности каждая транзакция начинается при целостном состоянии базы данных и должна оставить это состояние целостными после своего завершения. Несоблюдение этого условия приводит к тому, что вместо фиксации результатов транзакции происходит ее откат (т.е. вместо оператора COMMIT выполняется оператор ROLLBACK), и база данных остается в таком состоянии, в котором находилась к моменту начала транзакции, т.е. в целостном состоянии. В связи со свойством сохранения целостности БД транзакции являются подходящими единицами изолированности пользователей, т.е., если с каждым сеансом работы с базой данных ассоциируется транзакция, то каждый пользователь начинает работу с согласованным состоянием базы данных, т.е. с таким состоянием, в котором база данных могла бы находиться, даже если бы пользователь работал с ней в одиночку.

Булевы операторы и простые операторы сравнения

AND	логическое И. Ставится между двумя условиями (условие1 AND условие2). Чтобы выражение вернуло True, нужно, чтобы истинными были оба условия
OR	логическое ИЛИ. Ставится между двумя условиями (условие1 OR условие2). Чтобы выражение вернуло True, достаточно, чтобы истинным было только одно условие
NOT	инвертирует условие/логическое_выражение. Накладывается на другое выражение (NOT логическое_выражение) и возвращает True, если логическое_выражение = False и возвращает False, если логическое_выражение = True

Условие	Значение
=	Равно
<	Меньше
>	Больше
<=	Меньше или равно
>=	Больше или равно
<> !=	Не равно

IS NULL	Проверка на равенство NULL
IS NOT NULL	Проверка на неравенство NULL

DDL (Data Definition Language)

DDL commands are used to create, drop and alter the databases, aliases, indexes, tables etc.
Once a DDL statement is executed, it takes effect immediately in the database. Which means any changes done to the database using DDL command is permanent.
Popular DDL commands are: CREATE, DROP, ALTER and TRUNCATE.

a) CREATE: This command is used to create database and tables inside a database.

Syntax for creating database:

Syntax for creating table:

b) DROP: This command is used to drop database and tables.

Syntax for dropping database:

Syntax for dropping table:

c) ALTER: This command is used to add, delete, or modify columns in an existing table.

Syntax: To add a new column in the existing table

Example: The following ALTER TABLE statement will add a new column to the existing table .

d) TRUNCATE: This statement deletes all the rows from the table. This is different from the DROP command, the DROP command deletes the entire table along with the table schema, however TRUNCATE just deletes all the rows and leaves an empty table.

Syntax:

Example: Deletes all the rows from the table .