MySQL is a top and popular database used by large to small businesses in the corporate world. It is an easy peasy database and beginner’s first choice as it is simple to maintain and handle. The commands to query the database are very straightforward. This database software is quite efficient and faster than other databases, due to which the statistics of this MySQL is increasing almost daily.
Types of MySQL Queries
In the MySQL database, there are two types of statements in which the commands are segregated accordingly.
1. DDL (Data Definition Language)
Data Definition Language statements are nothing but the set of statements used to construct a schema.
Some examples: CREATE, ALTER, DROP, TRUNCATE.
2. DML (Data Manipulation Language)
The Data Manipulation Language is used to retrieve, insert and modify database information.
Here is a list of commonly used queries to command the database:
1) MySQL Create Database
mysql> CREATE DATABASE database_name;
2) MySQL Select/Use Database
mysql> USE database_name;
3) MySQL Create Query
mysql> CREATE TABLE table_name ( column_name1 data-type1, column_name2 data-type2, column_name3 data-type3, . . , column_nameN data-typeN);
4) MySQL Alter Query
mysql> ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name DROP COLUMN column_name: ALTER TABLE table_name MODIFY COLUMN column_name datatype; ALTER TABLE table_name CHANGE COLUMN oldcolumn newcolumn datatype; ALTER TABLE table_name RENAME TO new_table_name;
5) MySQL Insert Query
mysql> INSERT INTO table_name VALUES (value list); -------> single value into the table INSERT INTO table_name VALUES (& value1, &value2,....,valueN);
6) MySQL Update Query
mysql> UPDATE table_name SET value WHERE condition;
7) MySQL Delete Query
mysql> DELETE FROM table_name WHERE condition;
8) MySQL Select Query
mysql> SELECT field1, field2,....fieldN FROM table_name1, table_name2 WHERE condition;
To retrieve all the data at a time
mysql> SELECT * FROM table_name;
9) MySQL Truncate Table Query
mysql> TRUNCATE TABLE table_name;
10) MySQL Drop Query
mysql> DROP TABLE table_name;
These are a few frequently used commands and basic operations that are performed on databases or tables.
Examples of Important MySQL Queries
The following is a list of commonly used MySQL queries for creating databases, using databases, creating tables, inserting records, updating records, deleting records, selecting records, truncating tables, and dropping tables.
The INSERT statement in MySQL is used to store or add data to a MySQL table. In MySQL, we can insert entries in one of two ways using a single query:
- Insert a single row of records.
- Insert several rows of records
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
Use the following statement if we wish to insert numerous records in a single command:
INSERT INTO table_name VALUES ( value1, value2,...valueN ) ( value1, value2,...valueN ) ........... ( value1, value2,...valueN );
INSERT into customers values(101,'raju','Mumbai');
INSERT INTO People VALUES (102, 'Joseph', 'Developer', 30), (103, 'Mike', 'Leader', 28), (104, 'Stephen', 'Scientist', 45);
The following is a generic syntax for the UPDATE command, which is used to edit data in MySQL.
UPDATE table_name SET column_name1 = new-value1, column_name2=new-value2, ... [WHERE Clause] ;
A MySQL update query is used to update table records.
UPDATE trainer SET email = 'firstname.lastname@example.org' WHERE course_name = 'Java';
We won’t be able to recover the records we deleted with this query. As a result, it’s a good idea to back up your database before deleting any records from the table. We can restore data from database backups anytime we need it in the future.
The syntax that demonstrates how to perform the DELETE command is as follows:
DELETE FROM table_name WHERE condition;
mysql> DELETE FROM Employees WHERE emp_id=107;
In MySQL, the SELECT command is used to get data from one or more tables. Using this statement, we can obtain records from all fields or specified fields that match defined criteria. It also supports a variety of programming languages, including PHP, Ruby, and others.
SELECT field_name1, field_name 2,… field_nameN
FROM table_name1, table_name2…[WHERE condition] [GROUP BY field_name(s)] [HAVING condition] [ORDER BY field_name(s)] [OFFSET M ][LIMIT N];
SELECT * FROM table_name;
mysql> SELECT Name, Email, City FROM employee_detail;
mysql> SELECT * FROM employee_detail;