Do you want to learn about SQL query commands? Many organizations throughout the world use MySQL and PostgreSQL relational database management systems with the standard use of SQL query language. More specifically, if you want to operate with databases effectively, you need to get to know about SQL queries.
The important SQL queries or commands related to creating databases and tables, inserting data or rows, selecting and updating data, and removing rows, tables, and databases, will be covered in this article.
So, keep reading to discover more about SQL query commands and how they can facilitate effective data management and manipulation!
1. Create Database SQL Query
In SQL, the
CREATE DATABASE query is used for creating or adding a new database. The newly created database can then be utilized for storing data in the form of tables, indexes, views, and other objects.
CREATE DATABASE database_name;
According to the given syntax:-
CREATE DATABASEis the keyword that indicates that it is required to create or add a new database.
database_nameis the new database name. Note that, the specified database name must be unique and should not already exist in the database server.
To create a new database named
database1, we will now run the following command.
CREATE DATABASE database1;
As a result, a new database will be created successfully.
2. Show Database SQL Query
SHOW DATABASE query is used for showing or displaying all of the already existing databases on the server.
For the corresponding purpose, write out the following command in the terminal.
It can be observed that the given command printed the list of existing databases, which also comprises the database1.
3. Use Database SQL Query
Want to switch to a different database? SQL offers the
USE DATABASE command for switching or connecting to the specified database. After doing so, you will be permitted to execute the required queries in that database context.
In the given syntax:
USEkeyword signifies that we need to switch between databases.
database_namerepresents the database name for the connection.
Let’s connect to the newly created
database1 in MySQL with the help of the
The displayed message indicates that the database has been changed successfully.
4. Alter Database SQL Query
To alter or modify an existing database, the
ALTER DATABASE command be utilized. Moreover, it allows you to update the database name or its relevant collation or character set.
Here is the syntax you should follow for altering the database.
ALTER DATABASE database_name [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name
In the above command.
ALTER DATABASEis the required command that we need to execute.
database_namerefers to the database name that is required to alter.
COLLATEare the optional parameters. By utilizing them, you can alter the character set or collation of the mentioned database.
The given code will alter the database names
database1 and modify its character set as
utf8 and the relevant collation to
ALTER DATABASE database1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
5. Create Table SQL Query
In a database, the CREATE TABLE can be utilized for creating or defining a new table. It also enables you to specify the table structure comprising the required columns and their data types.
CREATE TABLE table_name ( col1 datatype [optional_parameters], col2 datatype [optional_parameters], ...[table_constraints] );
CREATE TABLEcommand is utilized for a new table.
table_namerefers to the table name that is required to create.
clo2…, etc, represent the tables columns.
[optional_parameters]signifies the use of additional column attributes, such as AUTO_INCREMENT, DEFAULT, or
NOT NULL, etc.
[table_constraints]represents the table constraints, such as UNIQUE, FOREIGN KEY, or PRIMARY KEY, etc.
Create a table named
authors comprising three
CREATE TABLE authors (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255));
According to the given command, the data type of the
id column has been defined as an
integer, and its length will be auto-incremented.
Moreover, it is specified as the
PRIMARY key of the specified table. Other than this, there are two columns
6. Show Tables SQL Query
The SHOW TABLES query in MYSQL can be utilized when you need to list out the tables of a certain database.
The given command will fetch the table names of the default or current database.
SHOW TABLES [FROM database_name] [LIKE 'pattern'];
FROMclause is used with the
database_nameto refer to the particular database. In case, if it is not defined, the default or current database.
LIKEclause with pattern is an optional parameter for filtering the tables list based on specified requirements.
In the ongoing scenario, the
SHOW TABLES query will display the tables of the
database1 with which we are connected.
7. Insert Into SQL Query
The INSERT INTO command is used for inserting or adding single or multiple rows to a table in MySQL.
Here is the syntax you need to follow for inserting into a MySQL table.
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);
According to the given syntax.
INSERT INTOis the MySQL command.
table_namerefers to the table where it is required to insert records or data.
(col1, col2, ...)are the column names where the data is going to be inserted.
VALUES (val1, val2, …)indicates the values that will be inserted.
- Note that you have to specify the values in the same sequence in which the column names have been defined.
Example 1 – Inserting a Single Record Into a Table
In this example, we will insert a single record including the name Ravi and the email as email@example.com into the authors table.
INSERT INTO authors (name, email) VALUES ('Ravi', 'firstname.lastname@example.org');
Example 2 – Inserting Multiple Records Into a Table
For the purpose of inserting multiple records into the authors table, we will specify the names and emails and separate both values set by comma, as follows.
INSERT INTO authors (name, email) VALUES ('Kennedy', 'email@example.com'), ('Sharqa', 'firstname.lastname@example.org');
8. SELECT SQL Query
To retrieve or fetch the records from single or multiple tables in a MySQL database, utilize the SELECT query.
To do so, check out the given syntax.
SELECT col1, col2, ... FROM table_name WHERE condition;
SELECTis the specified MySQL query.
col1, col2, …,are the column names whose records are going to be fetched.
table_namerefers to the required table name.
WHEREclause with the specified condition is an optional parameter that can be added if you need to filter the record.
Example 1 – Selecting All Records From a Table
As the first example of a
SELECT query, we will fetch all of the records from the
SELECT * FROM authors;
It can be observed that all of the records from the
authors table have been retrieved successfully.
Example 2 – Selecting a Specific Record From a Table
In the second example, we will only retrieve the record
WHERE the name value equals
SELECT * FROM authors WHERE name = 'Sharqa';
As you can see, the
SELECT query displayed the id, name, and email accordingly.
9. Update SQL Query
To modify or alter the existing data of a table in MySQL, the UPDATE query can be used. It can be utilized in scenarios where it is required to modify the accidentally added record.
For updating records in MySQL, follow the provided syntax.
UPDATE table_name SET col1 = val1, col2 = val2, ... WHERE condition;
UPDATEquery is added for updating records.
table_namerefers to the table name.
col1 = val1, col2 = val2, are the column names with the respective new values.
WHEREclause is added with the specific condition to update the rows that meet the given criteria.
Let’s modify or update the email id of the author named
Sharqa with the one that is mentioned below.
UPDATE authors SET email = 'email@example.com' WHERE name = 'Sharqa';
The output indicates that the update operation has been done successfully.
10. Delete SQL Query
While managing the database and the relevant tables, you may need to omit or delete unnecessary records or data that is no longer required. In such a scenario, execute the DELETE query. This query can be utilized for deleting single or multiple rows or records from a selected table.
To DELETE records, specify the table_name and define the condition.
DELETE FROM table_name WHERE condition;
As a result, the given
DELETE query removes the arrows that fulfill the condition specified with the
WHERE clause. In case, the
WHERE clause is not added, all of the table rows will get deleted.
In the example, we will utilize the
DELETE query for deleting the record from the
authors table where the name equals
DELETE FROM authors WHERE name = 'Kennedy';
12. Alter Table SQL Query
In order to modify, update, or alter the structure of an existing table in MySQL database, utilize the ALTER TABLE query. This query can be utilized in scenarios where you tend to make a scalable database management system.
To utilize the
ALTER TABLE command, follow the stated syntax.
ALTER TABLE table_name action;
In the above-code block :-
ALTER TABLEcommand is defined for modifying or altering the table.
table_namerefers to the table that needs to be modified.
actionspecifies the action or alteration that is required to perform. For instance, it can be adding or dropping columns, modifying or renaming columns, etc.
In this example, we will alter the
authors table and add a new column named
age with the integer data type.
ALTER TABLE authors ADD age INT;
12. Drop Table SQL Query
To drop or delete an existing table of a database along with its data, run the DROP TABLE query. This query is required to execute when you want to remove a whole table all at once.
DROP TABLE command will delete the specified
DROP TABLE table_name;
Now, let’s drop the
authors table from our current database.
DROP TABLE authors;
13. Drop Database SQL Query
The DROP DATABASE query in MySQL can be utilized for dropping or deleting a database along with the table and the relevant table.
database_name which you need to drop or delete.
DROP DATABASE database_name;
Lastly, let’s drop our current database
database1 which we created for the demonstration purpose,
DROP DATABASE database1;
To List out the databases for the verification run the following command.
It can be observed that
database1 is no longer in the list which means it has been dropped successfully.
In this post, we have covered the 13 essential SQL commands that every MySQL or PostgreSQL user should know. However, to take your learning to new heights, check out other dedicated tutorials as well.
As you have seen in this blog, SQL query commands offer a robust toolset for managing your database and the relevant data. From creating and altering tables to inserting, modifying, and dropping records, tables, or databases, these commands offer a reliable and flexible way to handle various data-related tasks in a database server.