Wednesday, July 10, 2019

Basics of DBMS | Database Management System

Basics of DBMS | Database Management  System: 
The Internet is only based on the data. so to store the data and for searching for anything on the internet we need a Database management system. 

so today we are going to learn about the basics of DBMS and some important terms and concepts of the Database Management System.
Basics of DBMS | Database Management System
Basics of DBMS

What is Database?

A database is a collection of data which retrieve the data from a computer and store the data in a manner. all the websites and other technical stuff that can store the user's name, password and other stuff as well as YouTube, StackOverflow, Twitter, etc. it's made up of tables that store relevant information.
Let's understand it with an example

Example: for example if we sign up on any website like facebook. so when we fill up the information like our username, email address, password and date of birth. and then when we login in after some time how we login using our email and password.

so basically it's only possible using DBMS. there is a database management system in the background that handles these all pieces of information. in DBMS there is a database and in the database, there is a database table in the table information stored when we submit the information that we filled up. 

so anything like software, website, application which required a piece of information has a background database management system. 
so that's why the DBMS is important 

Note the first database developed by Edger F. Codd. in 1960.


What is Database Management System?

A database management system is a software for creating and managing databases. it provides developers and programmers to easily create, update and manage the database.


What is SQL?

SQL stands for Structured Query Language. it's used to access and manipulate the database. it's an ANSI standard. 
it's a query language that is used to develop a database and database table in the database. 


Basics of DBMS | Database Management System


Database Table :
A database table store and displays data in a format of columns and rows like an Excel sheet.
a database can have multiple tables. and a table can have a specific number of columns but have any number of rows.


Primary Key :
A primary key is a field or column which used to uniquely identifies the table records. it always contains a value and always be not null. and for each row, the values for the primary key should be different.


Unique key :
A unique key is a field or column which used to uniquely identifies the table. but it may be content the null value.


Create table :
This statement is used to create a database table.


Show database :
This command is used to display the table of databases.


Show tables :
This command is used to display all the tables which are created in the current database.


Show columns :
This command is used to display the columns in a given table.


Int :
It can be signed or unsigned and it holds the numeric nondecimal values.


Float :
It cannot be unsigned and it holds the floated or decimal values. we can also define the length of the decimal number.


Char :
This data type is used to store the sequence of characters in the database table.


Varchar :
This data type is used to store the string and sequence of characters in the database table. we can also specify the length of the string.


Text :
This is the largest data type. it's used to store a large amount of the text data.

Date :

This data type is used to store the Date in the database.

Datetime :

This data type is used to store a combination of data and time.

Time :

This data type is used to store the time in the database.

Select :

This command is used to select specific data and columns in a table.

Create a database :

This command is used to create a database.

Use database :

This command is used to select a specific database to use.

Distinct :

This command is used to select the distinct data and eliminate all duplicate data. it only displays unique data.

Limit :

This command is used to select limited data in a table.

Order by :

This command is used to sort the data using a specific column. by default, the result is printed in ascending order.

Where :

This clause is used to select only those conditions which can full fill the condition of the where statement.

Operators :

These operators are used to perform logical and arithmetic operations.

Between :

This operator is used to select the values within a given range.

In :

This operator is used to compare a column with value.

Not in :

This operator is used to select a list of specific values that are not in the given range. 

Concat :

This function is used to add two columns in a single column and prints it's value.

As :

This keyword is used to give a new name to the concat column.

Upper :

This function is used to print all the string in uppercase latter.

Lower :

This function is used to print all the string in a lower case letter.

Sqrt :

This function is used to print the square root of the given value.

Avg :

This function is used to print the average of the given column.

Sum :

This function is used to sum for a column is value.

Like :

This function is used to specifies a search condition within a clause.

Min :

This function is used to print out the minimum value of an expression in a statement.

Join tables :

To join the two tables, we select both tables and separate them as a comma-separated list.

Inner join :

This join returns the rows when there is a match between the tables.

Left join :

This join returns all rows from the left table, even if there are no matches in the right table.

Right join :

This join returns all the rows from the right table, even if there are no matches in the left table.

Union :

This statement combines multiple data sets into a single dataset and removes any existing duplicates.

Union all :

This statement combines multiple data sets into a single dataset but not removes any existing duplicates.

Insert into :

This statement is used to add the new rows of data into a database table.

Update :

This statement is used to update the records in a database table.

Delete :

This statement is used to remove the data from a database table.

Constraints :

In the DBMS, the constraints are used to specify rules for the database table.

Not null :

This indicates that a column cannot contain any null value.

Auto Increment :

This indicates that the value for the column is autoincremented by one.

Check :

This keyword is used to validate the value.

Default :

This keyword is used to specified a default value for a column.

Alter table :

This keyword is used to add, delete, or modify columns and add/drop various constraints on an existing table. 

Drop table :

This keyword is used to drop the table in a database table. 

Drop column :

This keyword is used to drop the column in a database table. 

View :

It's a virtual table that is based on the result-set of an SQL statement. 


Some important terms and points to focus.

  • A database is made up of tables that store pieces of information. 
  • we can run multiple commands and queries at the same time. 
  • to complete a SQL statement we need to add a semicolon.
  • it's good practice to write all SQL commands in the upper-case. 
  • white space and multiline are ignored in SQL.
  • SQL is a case insensitive. 
  • in SQL, the asterisk (*) means all. 
if you like this information then please share this post with your friends and all of your social media handles. and never forget to subscribe to this blog using your email address. 
Previous Post
Next Post

Yash is a Full Stack web developer. he always will to help others. and this approach takes him to write this page.

0 comments: