In this article, we will explore some of the key SQL commands that serve as essential building blocks for querying, updating, and managing information.
Creating a Database
To create a database, we use the "CREATE" command.
Syntax
After creating the database, or if it already exists, we use the "USE" command to select the database we want to use.
Syntax
Creating a Table
To create a table in SQL, you use the "CREATE TABLE" command. This command allows you to define the table's structure, specifying column names, data types for those columns, and other properties.
Syntax
In the command below, a table is being created where the "ID" field is defined as a primary key and therefore cannot contain null values. However, the other fields are allowed to contain null values.
Syntax
If you want to create a table with more than one column as a primary key, you can do so as follows:
Syntax
Altering a Table
If the table already exists, you can make changes to it using the "ALTER" command.
To add a column to the table, we need to define the table to which we want to add the new column, the column name, and the data type.
The process of adding columns to tables allows for the expansion and adaptation of the table's structure to accommodate new types of information or requirements.
Syntax
To remove a column from a table, you simply need to choose the table from which you want to remove the column and specify which column you want to delete. This allows you to eliminate a specific column from the table's structure, adjusting its structure and content as needed.
Syntax
To change the name of a column in SQL, you use the "ALTER TABLE" command along with "RENAME COLUMN".
Syntax
To change the data type of a column, the syntax is very similar to the syntax for adding a column to the table. You just need to replace "ADD" with "ALTER COLUMN". See below!
Syntax
If you have created the table, but forgot to create the primary key, don't worry, you can alter the table structure and create one. This primary key can consist of a single column or be composed of multiple columns. To indicate multiple columns, simply list them within parentheses, separating them with commas.
Syntax
Inserting Data into a Table
After creating the table, we will insert data into it using the "INSERT" command.
To insert a single record:
Syntax
It is also possible to insert multiple records at the same time:
Syntax
Selecting Data from a Table
After the data has been added to the table, we can retrieve it using the "SELECT" command.
If you want to retrieve all records and all columns from the table:
Syntax
To select specific columns in an SQL query, you use the "SELECT" command followed by the column names you want to retrieve. This allows you to narrow the query's output to the information that is relevant to you.
Syntax
If it's necessary to filter a record in the table, you can use the "SELECT" command with the "WHERE" clause. In the example below, only records with an "ID" equal to 1 will be returned.
Syntax
Updating Data in a Table
If you already have a table with information and want to modify only a specific record, you can perform this update using the "UPDATE" command with the "WHERE" clause. This allows you to modify the data of a specific record within the table while keeping the other records unchanged.
Syntax
Additionally, you can modify the values of multiple columns simultaneously. In the example below, we are changing the values of the columns "AGE" and "NAME".
Syntax
Deleting Data from a Table
To delete a specific record from the table, we use the following command:
Syntax
You might want to delete all data from a table. For this, you can simply use the command:
Syntax
Be very cautious with this command, as it is irreversible and will result in the complete loss of data in the table. Therefore, it is extremely important to be sure that you want to delete all records before executing this command, and it is highly recommended to back up the data before performing a large-scale deletion.
To prevent the accidental deletion of all records, many Database Management Systems (DBMSs) require you to include a "WHERE" clause as part of the "DELETE" command unless you have special privileges to perform such a critical operation.
Remember that the exact syntax may vary slightly depending on the Database Management System (DBMS) you are using, but the fundamental concepts of using these commands remain the same.
Top comments (0)