A quick summary of the most popular MySQL queries.

Posted by

MySQL is the most prevalent Open Source Relational SQL Database Management System.

Open Source – Any application program for which the original source code is made freely available and may be redistributed and modified.

Relational –  A relational database fits more complicated systems by connecting information from two or more database tables that define relationships.

SQL– stands for Structured Query Language. It is a standard language to access and modify databases by executing queries against the database.

Database – A database is a collection of correlated data, usually stored and accessed electronically from a computer system.

Database Management System (DBMS) – A Database Management System is a software for storing, managing and retrieving users’ data by considering efficiency and suitable security measures.

Most commonly used MySQL commands:

Description Query Example
Create a database. Replace database_name with the name of the database that you want to create.

create database [database_name];

create database school;

List all the databases on the SQL server.

show databases;

show databases;

Select and use a particular database to work with.

use [database_name];

use school;

Create a new table in the current database.

create table [table_name](column_name_1 datatype(size), column_name_2 datatype(size),

….

column_name_n datatype(size)

);

create table student_details( Roll_no int(10), student_name VARCHAR(50), student_address VARCHAR(90), student_gender VARCHAR(10), student_marks int(10) );

Displays all the tables in the current database.

show tables;

show tables;

Displays a detailed description of the attributes/fields in the given table.

describe [table_name];

OR

SHOW COLUMNS FROM [table_name];

describe student_details;

OR

SHOW COLUMNS FROM student_details

Add new rows of data to the table.

INSERT INTO [table_name] (column_name_1,column_name_2,…column_name_n) VALUES (value1,value2,….valuen);

OR

INSERT INTO [table_name] VALUES (value1,value2,….valuen);

INSERT INTO student_details( Roll_no, student_name, student_address, student_gender, student_marks) VALUES(01,”John Doe”, “New York”,”Male”,85),(02, “Recky”, “L.A”, “Female”,90), (03, “Alex John”,”London”,”Male”,20);

Display all the data from the table.

SELECT * FROM [table_name];

SELECT * FROM student_details;

Display a selective column from the table.

SELECT [column_name] FROM [table_name];

OR

SELECT [column_name1],[column_name2] FROM [table_name];

SELECT student_name FROM student_details;

OR

SELECT student_name, student_marks FROM student_details;

Update existing records in a table

UPDATE [table_name] SET column_name =new_value,  column_name2= new_value2 [WHERE condition]

UPDATE student_details SET student_marks=60 WHERE Roll_no=4;

 To add, delete, or modify columns in an existing table

ALTER TABLE [table_name] ADD [column_name] [datatype];

OR

ALTER TABLE [table_name]
DROP COLUMN [column_name];

ALTER TABLE student_details ADD student_age int(10);

OR

ALTER TABLE student_details DROP COLUMN student_age;

Delete data from the table

DELETE FROM [table_name] [WHERE condition];

DELETE FROM student_details WHERE student_marks=20;

Delete the table data  but not the schema

TRUNCATE TABLE [table_name];

TRUNCATE TABLE student_details;

Delete a table contents and the schema from the current database.

drop table [table_name];

drop table student_details;

Delete the database contents and the schema.

drop database [database_name];

drop database student_details;

One comment

  1. My brother suggested I might like this web site. He was entirely right. This post truly made my day. You cann’t imagine simply how much time I had spent for this information! Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s