CREATE DATABASE It is used to create a new SQL database. CREATE DATABASE database_name;

By DIBYAJYOTI PANDA

CREATE DATABASE

It is used to create a new SQL database.

CREATE DATABASE database_name;
CREATE DATABASE test;


SHOW DATABASES

It displays all the existing databases.

SHOW DATABASES;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+


DROP DATABASE

It deletes an existing database.

DROP DATABASE database_name;
DROP DATABASE test;


USE

It selects an existing database and allow users to perform any kind of operation like creating a table etc. It is something like selecting a folder in your computer before you do any kind of further operations inside that folder. So it is one of the important statements in SQL.

USE database_name;
USE test;


CREATE TABLE

It creates a table inside an existing database. See the syntax below, data type is compulsory and constraint is optional. To see all the common data types available in SQL, read my previous post.  For constraints, check out this post.

Note: you have to always select an existing database using USE statement and then do further operation like create table etc inside that database.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);
CREATE TABLE student( 
   Id int, 
   Course varchar(255) 
);


DESCRIBE

It displays all the attributes ( Column names) of an existing table with respective properties.

DESCRIBE table_name;
DESCRIBE student;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| Id     | int(11)      | YES  |     | NULL    |       |
| Course | varchar(255) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+


DROP TABLE

It deletes an existing table.

DROP TABLE table_name;
DROP TABLE student;


INSERT INTO

It adds a new row with values to an existing table. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

INSERT INTO table_name (column1, column2,.....)
VALUES (value1, value2,.....);
INSERT INTO student(Id, Course)
VALUES (1, 'cs');


SELECT

It helps selecting row from an existing table. You can also use wildcard character (*) to select all the columns without explicitly define column names (Example-2). Note: SELECT can be used without FROM also (Example-3). 

SELECT (column1, column2, column3,...) FROM table_name;
SELECT Id, Course FROM student;
+------+--------+
| Id   | Course |
+------+--------+
| 1    | cs     |
+------+--------
SELECT * FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    1 | cs     |
+------+--------+
SELECT 'HELLO WORLD';
+-------------+
| HELLO WORLD |
+-------------+
| HELLO WORLD |
+-------------+


SELECT DISTINCT

This statement is used to return only distinct (unique) values. Inside a table, a column often contains many duplicate values; and sometimes you want to remove those duplicate rows and list the unique values only.

SELECT (column1, column2, column3,...) FROM table_name;
SELECT Id, Course FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    1 | cs     |
|    2 | mech   |
|    1 | cs     |
+------+--------+
SELECT DISTINCT Id, Course FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    1 | cs     |
|    2 | mech   |
+------+--------+


UPDATE

The statement is used to modify the existing records in a table. Without where condition it will update all the records. So be careful.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
SELECT * FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    1 | cs     |
|    2 | mech   |
+------+--------+

UPDATE student
SET Id = 9, Course = 'civil'
WHERE Id = 1;

SELECT * FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    9 | civil  |
|    2 | mech   |
+------+--------+

UPDATE student
SET Id = 5, Course = 'eee'

SELECT * FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    5 | eee    |
|    5 | eee    |
+------+--------+


DELETE

The statement is used to delete the existing records from a table. Without where condition it will delete all the records. So be careful.

DELETE FROM table_name
WHERE condition;
SELECT * FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    1 | cs     |
|    2 | mech   |
+------+--------+

DELETE FROM student WHERE Id = 2;

SELECT * FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    1 | cs     |
+------+--------+


ORDER BY

This is used to sort the data in ascending or descending order, based on one or more columns. If you don’t include an option i.e ASC/ DESC then by default it will sort the query results in an ascending order. If there are multiple columns included then it will order based on first column initially and if some rows in first column have the same values then again order those results based on second column. It continues like that in case of several columns. See the examples below.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
SELECT * FROM student;
+------+--------+
| Id   | Course |
+------+--------+
|    3 | civil  |
|    2 | mech   |
|    6 | cs     |
|    4 | eee    |
|    1 | cs     |
+------+--------+

SELECT * FROM student ORDER BY Id;
+------+--------+
| Id   | Course |
+------+--------+
|    1 | cs     |
|    2 | mech   |
|    3 | civil  |
|    4 | eee    |
|    6 | cs     |
+------+--------+

SELECT * FROM student ORDER BY Course;
+------+--------+
| Id   | Course |
+------+--------+
|    3 | civil  |
|    6 | cs     |
|    1 | cs     |
|    4 | eee    |
|    2 | mech   |
+------+--------+

SELECT * FROM student ORDER BY Course,Id;
+------+--------+
| Id   | Course |
+------+--------+
|    3 | civil  |
|    1 | cs     |
|    6 | cs     |
|    4 | eee    |
|    2 | mech   |
+------+--------+

 

Spread the words

Share on facebook
Share on google
Share on twitter
Share on linkedin