SQL - Quick reference

Useful Commmands of SQL at a glance 
----------------------------------------------------------------------------------
Read Introduction to DBMS / RDBMS before proceeding.. because ,RDBMS is the basis for SQL, and for all modern database systems 
I am already given the SQL commands in Starting SQL Commands . But it will be very useful for you , if all the important commands are given in a single page.

Before proceeding , let us have a look at MySQL introduction  and SQL .

=======================================================================
Ok , let us start..
Refer WAMP server , to write SQL commands.
That is , select MySQL from WAMP .

• We can't create a database  ,if there is already one with the same name . To view already created databases, use the command "SHOW DATABASES" .It will show you all the previously created databases , as shown below : 

1 : Show databases ;

• To delete a database , use DROP command. That is , if we want to delete the database "test1" from the above list ,use the given command ,

2 : drop database databasename;

That is , drop database test1;

And , now again use "show databases" to check whether the database is deleted or not.

See test1 is deleted from the list .

• Now we want to create a new database named "country". To do so , use the command as,

3 : create database databasename;

That is , "create database country . And after that again use "show database" command to check whether there is a database called "country" is created or  not.

We can see that , the database "country" is created.

• When you have multiple databases in your SQL Schema (like photo,test,country etc. ), then before starting your operation, you would need to select a database where all the operations would be performed.

In such a case , we are using USE Statement.

4 : USE databasename;

If we want to select the database "country" , give the command as,

use country .

Now the "country" database is ready to use. Now , we can create tables in "country" database.

• To create tables we can use the following syntax : 

5 : CREATE TABLE table_name(    column1 datatype,    column2 datatype,    column3 datatype,    .....    columnN datatype,    PRIMARY KEY( one or more columns ) );

Here, we are creating a table "city list"  in the database named "country" , and 4 fields also will be added .See below :

(press "enter" to insert as each rows ).

• To see the created table under the database named " country " , use the following syntax :

6 : Show tables ;

Then we can see the table named "citylist" , as given below : 

• To delete a table , use "DROP" command. In order to drop a table , I again created another table named"list" using the create table syntax , See below :

Now , I am going to delete the newly created table "list". For this , the syntax is ,

7 : drop table tablename ;

Here , the table we are going to delete is "list". So ,use  drop table list ; 

(Note that I am again selected the "show tables" command  to know the table "list" is deleted.

• To insert values into the table "citylist" , at first we have to know the fields we created in "citylist".

Use the following command to list the various fields of the table.

8 : desc tablename ;

Here , use "desc citylist "and see the result,

• Now , we are going to insert  values into the table "citylst".Note that , the Field order given in the above picture should be maintained while inserting values in to the tables .

The syntax is .

9 : Insert into tablename values ();

• To view the entered data , use the syntax ;

10 : Select * from tablename ;

which gives the entered values like this : 

That is , we can see the column values.

In syntax number 9 , we insert only one value at a time . If we want to insert more than one value in a single command , use this :

And ,to view all the entered data , use syntax number 10 ,

• Now , if we made a mistake in entering a column value , for example , we want to correct the spelling from 'trivandrum" to "thiruvanandapuram" , we are using the following method : 

11 : UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

And see the result : 

The used command is " update citylist SET city='thiruvanandapuram' where population='500000';

Here , I used the 'population' because it is the unique field among the row.That is , language,continent etc are repeating.If we select any of that fields, say continent='asia' , all the cities having the continent 'asia' will be replaced by 'thiruvanandapuram' city.

Note the changed field.

• If we want to select a particular column only ,use the following syntax : 

12 : SELECT columnname FROM  tablename ;

If we want to select the column named "city" from the table (citylist)  , use this .

• We can see that some field values (example , language) is repeating. To see the column values with out repetition , use this syntax : 

13 : SELECT  DISTINCT columnname FROM  tablename ;

9 rows are there , note that  the column named "continent" : In syntax 12, if we add a word "distinct" also , it will display the column values without repetition , as given below : 

• Some column values (example,city) is not repeating ,but continent-language etc values are repeating.

To see only the column values having repetition , and how many times they are repeating, use the following syntax :

14 : SELECT  columnname ,  count (*) as cnt FROM tablename GROUP BY columnname HAVING cnt>1  ;

Here , I used 2 commands for "language" and "continent" as COLUMNNAME .See the output :

• If you want to see column only (with out COUNT) , then use this command : 

15 : SELECT  columnname FROM tablename GROUP BY columnname HAVING count (*) >1  ;

And , the output is : 

Note that , in NO:14 , the languages are displayed with its repetition number (See NO:11).

• Note the NO: 10.

Now , if we want to see all the entered values row by row , then use the following Syntax:

16 : SELECT  * from  tablename\G  ;

See I used both commands here (with and without using "\G" .And the output for both the commands are shown below : 

(Both commands are shown in red line).

• We can see that , we used 4 columns (city,continent,language,population) . But if there are more than 4 columns , then the remaining column can seen in the next line , by using the following Syntax : 

17 : (Find out, and reply as Commands)

• If you want to get help from SQL , use this commands : 

18 : help contents  ;

The output is : 

Example : " help update " will show you the commands about "update".

• If we want to see the values in ascending order,

 

19 : select * from tablename order by columnname  ;

And the output is ; 

(Default setting is "ascending order" ).

That is , if you want to display any value in ascending order, there is no need to give the command "ascending".

Instead if we want to display the value is descending order, we must specify it. See below : 

• If we want to see the values in descending order,

20 : select * from tablename order by columnname desc ;

Both population and continent are displayed in descending order.

• See in above list , the continent "Asia" is repeating 6 times . So in that case , it will sort by population .

21 : select * from tablename order by columnname ,columnname desc ;

Note that, "Asia" is sorted as per population's descending order.

• Now , suppose we want to know the number of persons who speaks the same language.Then ,use the following commands.

Will continue.....