Basic common MySQL Query

MySQL is an open-source relational database management system. Let’s look at the advantages of using MySQL and some commonly used queries.

Advantages of MySQL

1. Open Source
2. High Performance
3. Low Cost
4. Cross platform compatibility
5. Usability
6. Scalability
7. Reliability
8. Easy to use and maintain

Let’s look at some of the commonly used queries.

1. SHOW DATABASES;

Displays all the database names

2. USE hr;

“hr” is the database name, using this query you select the database that you want to work with;

3. SHOW TABLES;

Displays the table names that is present in the selected database

4. SELECT NOW();

It returns the data and time with the format of “yyyy-mm-dd hh:mm:ss”.

DateTime

5. Comments

There are three ways to comment in MySQL

# This is comment 1

- - This is comment 2

/* This is comment 3*/

6. CREATE DATABASE new_db;

“new_bd” is the database name. That will be created using this query.

7. DROP DATABASE new_db;

“new_db” database will be delete.

8. CREATE TABLE new_tab(column1,column2,etc.,)

CREATE TABLE new_tab(
id INT,
name VARCHAR(50)
)

The table name is “new_tab” and column id as numbers and name as string

9. DESC new_tab;

Describe the table. “new_tab” is the table name.

Table description

10. ALTER TABLE

Alter the existing table details. Currently our table accepts the NULL value and it doesn’t increase the value automatically.

Lets add auto increment using alter function

ALTER TABLE new_tab
MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

Now lets look at the description after altering the table.

Altered the table with primary key

Now the table won’t accept the null value and the column “id” will increment automatically as you keep inserting the data.

11. DROP TABLE

DROP TABLE new_tab;

It will delete the table from the database.

12. INSERT

INSERT INTO new_tab(name) values(“John”);

This query will insert name as “John” and id will be taken 1 as default. Because the id assigned as AUTO_INCREMENT so we don’t need to mention in the insert statement.

INSERT statement

13. UPDATE

UPDATE new_tab
SET name=”Mick” WHERE id=1;

This query updates the data in the table. It checks where the id is 1 and it replaces the name “John” with “Mick”. Now the name will be “Mick” for id no:1.

UPDATE statement

14. SELECT statement

To get the data from the table you need to use select statement. You have multiple ways to use it.

SELECT * FROM new_tab;

SELECT * FROM new_tab

It returns all the column from the table.

SELECT name FROM new_tab;

SELECT name FROM new_tab

It returns only the name. If you need to get only particular column from the table, use column name with comma (Ex: Select id,name from new_tab;).

15. DELETE

Delete data from the table where id is equal to 2.

DELETE FROM new_tab WHERE id=2;

It will delete the 2nd row and remaining only 2 rows will be available.

Deleted the row where the id is 2