Analytics Vidhya
Published in

Analytics Vidhya

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

--

--

--

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Recommended from Medium

Matic Nodes as a Service

Configuring Keycloak as an identity Provider in WSO2 Identity Server

Shoot your shot with Dart

Interfaces are Connectors

Automate everything to save yourself

The Elder Scrolls V: Skyrim Special Edition — Analysis of Dialogues

Kanban Operation in the First Joint Development

Preparing for Python Jobs? Here are some interview tips.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Elanthirayan

Elanthirayan

Virtual reality Developer and pursuing MTech in Data Science and Machine Learning having 5+ years of industrial experience.

More from Medium

The case for a hybrid data organization

Property-based testing #1: What is it anyway?

Pipeline of Automatic Speech Recognition System

Tried to implement MAML with PyTorch