Simple MySQL database functions and operations

This tutorial will teach you several database operations which will be needed in day to day life. This must be read by all those who want to learn working with MySql database. MySql is very popular database which is used as a backend in website development.

Here we are going to discuss:

1. Create a new database
2. Create a new table
3. Insert a value to the table
4. Alter a table
5. update a value in table
6. Select query
7. Functions like sum,count,avg which makes the work easy.

8. delete a row from table
9. Drop a table

Now, we discuss this one by one:

Listing 1: Creating a new database

Create database article;
use article;

The above command will create a new database named article. Now for using this database we write use article which tells mysql that we want to make use of article database for sql operations.

Now, we will learn how to make a table in this newly made database

Listing 2: Creating a new table

create table article1 (Author varchar(16)); 

Here , we have made a new table article1 , we have added a column named Author and the size of this column is set to be 16.

Now, when we added a table we will learn how to add a row to this table.

Listing 3 : Inserting a row on table

insert into article1 values ('csanuragjain');
 

Here , this will insert a new row to table article1 and the column Author of table article1 is filled with value csanuragjain.

Now, we discuss how you may alter a table by adding a new column

Listing 4: Alter a table (Adding a new column)

alter table article1 ADD id int;

Here we have added a new column to the table article which is id. We define the datatype of place as int since it is going to contain an integer. Since,we have filled one row without this column so id of that row is filled as null.

Now, we will discuss how you may update a value in the table. This is really important functionality. Here in our case we use it to update the id value for the row containing csanuragjain as author.

Listing 5: Update a table p>

update article1 set id=1 where author='csanuragjain';
 

Now we have updated article1 and set value of column id as 1 and this change take place where author is csanuragjain.

Now we make use of select query which is used to select a particular row with particular condition or even we make it select whole rows of a table.

Listing 6: Select a row from the table

Select * from article1;
 

This will select all rows from table article1.Now if we want to select a particular row then we make use of where clause like here we may write this as :

Listing 7: Select a particular row from the table

Select * from article1 where id=1;
 

This will show the row which has id set as 1 in the table article1

Now we learn to use functions like sum, count, avg.These functions reduces a lots of work and give really important results with lot less hardwork.

Listing 8: Use of Count

Select count(*) from article1;
 

The above query will reveal the total number of rows in the table. Now if you want to check number of row which satisfy particular condition then make use of where clause

Listing 9: Use of Sum

Select sum(id) from article1;
 

The above query will calculate the sum of all row values in field id

Listing 10: Use of Average

Select avg(id) from article1;
 

The above query will calculate the average of field id for all rows in table article1

Now we learn how we may delete a row from table

Listing 11: Delete table row p>

delete from article1;
 

This will delete all rows from table article1.Remember if you want to delete only specific rows then make use of where clause.

Listing 12: Drop table

drop table article1;
 

This will drop the table article1. Make sure you use this with proper care because it completely remove your table. Also remember that drop will only work when table has no rows left inside it.

So we have learned basic sql queries. This queries are really important for all beginners.

Hope you liked the article. See you next time with something new. And you may always give me suggestions for article topics via comments