SQL Command Categories
Taking advantage of the knowledge in the sources and our previous conversations, let’s dissect the different types of SQL commands. Relational databases are typically handled using SQL (Structured Query Language). MySQL executes its commands in accordance with SQL requirements. Based on their intended use, these commands are typically divided into four categories: Data Definition Language (DDL), Data Manipulation Language (DML), Transaction Control Language (TCL), and Data Control Language (DCL).
Revisiting DDL (Data Definition Language)
Statements in the Data Definition Language (DDL) are used to specify the schema or database structure. They specify how information ought to be kept in the database. Tables, views, and indexes can all be created, modified, and deleted using DDL commands. Data about data, or metadata, is found in the data dictionary, which is where DDL command output is stored.
Among the essential DDL commands are:
- CREATE: Databases and tables can be created using the CREATE function.
- CREATE DATABASE: Make a new database by selecting CREATE DATABASE.
- CREATE TABLE: In a database, the CREATE TABLE command creates a new table. You define data types, column names, and other factors.
Here is the development of the student table [from query] based on our previous conversations:
- Furthermore, the road_test table was created [from query, 20]:
- NOT NULL guarantees that a column cannot contain a null value. A row can be uniquely identified using the PRIMARY KEY. At the table level, a composite (multi-column) primary key needs to be specified.
- ALTER: The database’s structure is changed with ALTER. The ALTER TABLE command changes an existing table’s structure.
You may include a column:
- You can change a column that already exists. Changing the data type, size, or limitations is one example of this.
- Using the history of our conversations, changing the Persons table’s Age column:
- The NOT NULL constraint is added or enforced, and the data type is changed to int [from query explanation].
You can remove a column that already exists:
DROP: Gets rid of items from the database.
DROP DATABASE: A database can be deleted using the DROP DATABASE command.
DROP TABLE: This command removes a database table. This removes the table’s definition, structure, and data set forever.
TRUNCATE: A table’s entire contents, including all allotted space, are deleted using TRUNCATE. It removes the information within a table, but leaves the table intact. According to sources, DELETE is a DML command, but TRUNCATE is a DDL command. Additionally, TRUNCATE is faster than DELETE when it comes to deleting all rows.
Revisiting DML (Data Manipulation Language)
Data within schema objects is managed using statements written in the Data Manipulation Language (DML). Because DML deals with data manipulation, it contains the most popular SQL statements, including SELECT, INSERT, UPDATE, and DELETE. By using these instructions, users can access or modify data.
Among the essential DML commands are:
- SELECT: Information kept in the database can be retrieved using the SELECT command. It enables you to submit requests, or queries, to obtain records that are stored.
- The fundamental structure is SELECT followed by the names of the columns (or * for all columns) and FROM followed by the name of the table. A WHERE clause is a useful tool for filtering records.
- INSERT: This command adds new information to a database. In a table, it adds new records.
- UPDATE: This function is used to change the values of columns in records that are already in the table.
- All records in the table will be changed unless the WHERE clause is used to indicate which records need to be updated.
- DELETE: Used to get rid of data from a table.
Similar to UPDATE, the WHERE clause is essential. All records in the table will be deleted if the WHERE clause is left off. Use caution when doing this.
Introduction to TCL (Transaction Control Language)
Transactions are managed using commands in the Transaction Control Language (TCL). A transaction is a series of sequential database manipulation activities carried out as though they were a single work unit. The entire transaction ought to fail if any of its operations are unsuccessful. Atomicity guarantees that either nothing or all of the instructions in a transaction are recorded in the database. Additionally, transactions possess the ACID attributes of consistency, isolation, and durability.
Among the essential TCL commands are:
- COMMIT: Used to permanently document the modifications. It renders the modifications made to the transaction enduring and visible to subsequent transactions.
- ROLLBACK: When a transaction fails, ROLLBACK is used to restore the database to its initial state or retrieve (undelete) records.
- SAVEPOINT: Known as a TCL command, this one is used to establish a point in a transaction that you can subsequently roll back. There are no examples of code or detailed usage in the sources.
Transactions usually start with BEGIN WORK or START TRANSACTION and finish with ROLLBACK or COMMIT.
START TRANSACTION; -- or BEGIN WORK
-- SQL commands (SELECT, INSERT, UPDATE, DELETE)
-- ...
COMMIT; -- End transaction, make changes permanent If an error occurs or you decide not to save the changes, you would use ROLLBACK; instead of COMMIT;.
Introduction to DCL (Data Control Language)
Permits, grants, and Data Control Language (DCL) commands are related. With DCL commands, you may manage who can access particular database objects. The topic of DCL is security.
Among the essential DCL commands are:
- GRANT: A tool for granting rights or privileges to databases and their contents. Once a user account has been created, database administrators use this to specify its privileges.
- REVOKE: A tool for taking away MySQL account privileges.
These four categories cover the primary SQL operations used to manage the database structure and its contents, as well as to regulate transactions and user access.