Database Basics

In the journey of being a full-stack developer, it is crucial to understand at least the basics of database management.

So… what’s a database again??

Simply put, it is a place where you store all related information from many sources in an organized structure.

OK…then what is Database Management System(DBMS)??

It is simply a software that helps us interact (store and manage) with the database.

Introduction to Database Queries

Using queries, we can access databases. Queries can request data results, perform calculations, and add, change or delete data from databases.

Note: The queries we discuss in this blog will be based on MySQL. The commands might change a bit depending on the type of RDBMS used, but the concept remains the same.

Types of Queries

SQL queries can be classified into:

DDL	Data Definition Language
DML	Data Manipulation Language
DCL	Data Control Language
TCL	Transaction Control Language

Data Definition Language

DDL is used to define the database structure.

Command	Description
create	helps to create objects in the database
alter	helps to alter the structure of database objects
drop	helps to drop objects from the database
truncate	deletes all records from the table permanently keeping the structure intact
rename	helps to rename an object

Let’s check the working of each of the DDL commands:

1. create
   • let’s see how to create a database using the create command
     create database database_name;
   • now let’s create a table in the database
     create table table_name(
attribute1 datatype,
attribute2 datatype);
2. alter
   • let’s add a column to an existing table
     alter table table_name add column column_name datatype;
   • let’s change the datatype of a column in a table
     alter table table_name modify column column_name datatype;
   • let’s rename the table
     alter table table_name rename to new_tabe_name;
   • let’s rename a column in the table
     alter table table_name rename column column_name to new_column_name;
   • Let’s drop a column in the table
     alter table table_name drop column column_name;
3. drop
   • let’s drop a table from the database
     drop table table_name;
   • let’s drop the database
     drop database database_name;
4. truncate
   • it deletes the data from the database
     truncate table table_name;
5. rename
   • let’s rename a table
     rename table table_name to new_table_name;

Data Manipulation Language

DML can be used to manipulate data in tables.

Commands	Description
insert	helps to insert data into a table
update	helps to update existing data within a table
delete	helps to delete unwanted records/all records from a table

Let’s check the working of each of the DDL commands:

1. insert
   let’s see how to insert values into a table
   insert into table_name(attribute1,attribute2) values(value1,value2);
2. update
   let’s update the value of a column to value2 which was previously value1
   update table_name set column_name=value2 where column_name=value1;
3. delete
   • let’s delete specific rows from a table
     delete from table_name where column_name=value;
   • let’s delete all content in the table but still maintain its structure
     delete from table_name;

Data Control Language

DML is used to grant or revoke access.

Commands	Description
grant	helps to give users access privileges to the database
revoke	helps to revoke access privileges from users

Transaction Control Language

TCL commands are used to maintain the consistency of databases.

Commands	Description
commit	helps to save data permanently
rollback	helps to restore data to the last savepoint or last commit
savepoint	helps to save data at a particular point temporarily

Summing up…

In this blog, we have discussed the basics of Databases.
In the coming blogs, I’ll be discussing more advanced concepts of Databases and how to connect Databases to Java applications using JDBC and Hibernate.
If Databases interest you and you want to pursue a professional certification for Databases you can refer to this course by Udacity.

Related Topics

• Transactions in DBMS
• JDBC