Here, we go with MySql Server. Firstly, we need to get it clear briefly about MySql but to know about it we should understand database and Sql.
Structured Query Language:
SQL stands for Structured Query Language i.e. it is a language of database. It is a server for data storage and MySql is similar to the Sql used to retrieve,
store, modify and insert data. The name of MySQL is the combination of My and SQL, MySQL. The MySQL is a database management system that allows you to manage
relational databases. It is open source software backed by Oracle. If you want, you can change its source code to suit your needs. It is pretty easy to master in
comparison with other database software like Oracle Database or Microsoft SQL Server. It can run on various platforms UNIX, Linux, Windows, etc. MySQL is reliable,
scalable and fast. The official way to pronounce MySQL is ‘My Ess Que Ell’. If you develop websites or web applications, MySQL is a good choice. It is an important
component of LAMP stack which includes Linux, Apache, MySQL, and PHP.
Data is a raw factor of numbers, alphabets or alphanumeric characters and the processed data is known as ‘Information’. When the data is logically arranged as
well as accessed to form a collection of processed data is known as ‘Database’. It is a sort of container that collect, store and manage the data.
A database is a software system that manages the database in sense of storing, deleting, updating, etc. It is simply a structured collection of data. The data
relates to each other by nature, for example, a product belongs to a product category and associated with multiple tags. There are various database models but mostly
used and easy to access is Relational Database model that defines logical design of a database in rows and columns, i.e., we use most popular model, relational
database. In the relational database, we model data like products, categories, tags, etc. using tables. A table contains columns and rows. It is like a spreadsheet. A
table may relate to another table using a relationship, for example, one-to-one, one-to-many relationship etc. Because we deal with a large amount of data we need a
way to define the databases, tables, etc. and process data more effectively. In addition, we want to turn the data into information this is where SQL comes to play.
The server secures data in form of tables using rows and columns, thus, we need a brief knowledge regarding these-
In terms of technical manner, the table is known as “Relation” in database management system. Important points-
-> Entity set: Set of objects or entities. Table is made for an entity set.
-> Attributes: Features of an entity. Column of the table is used for attributes.
-> Tuple: Row of a table. Row is also called a “Record”. It is a collection of fields.
-> Field: Cell of a table. Smallest unit or a block of a table.
-> Cardinality: Total number of rows.
-> Degree: Total number of columns.
-> Domain: Collection of all possible values that an attribute can have.
Now, back to know further about MySql server and how to create and manage the database in it.
Firstly, we need to install MySql on our windows, linux or what you are using. After installation is done, secondly, we have to complete the verification process. When
we are done with this too, last step is to post-install and check the MySql commands.
After completing all this, the user needs to administer the server by giving ‘root’ name, ‘host’ name and a secured ‘password’. That is all are some steps or we can
say rules to manage the access and control of the database to use the server.
Let’s start with creating database on MySql, opening database, creating table and inserting data into them. The statement used to give any command are known as “Query”
in MySql. There are simple queries to give commands as needed are as follows:
- To create a database-
create database database_name ;
This is the syntax of creating a database and each query is followed by a semi-colon at the end. For example, we need to create a database of any company and wanted to
name the database for the convenience i.e. “GURUKUL”. Query for creating GURUKUL database is
create database gurukul ;
- To open the created database-
use database_name ;
After giving this query, the details about the database can be used. For example, for GURUKUL database
use gurukul ;
- To create a table in the database-
create table table_name (column_name1 value_type, column_name2 value_type) ;
This query create a table in the database and provide the required columns with their names and datatype i.e. in which form data will be stored in these columns. For
example, for GURUKUL database, we need a table for details of employees including employee id, employee name, their age and their department with the table named as
create table employees (ID int(10), Name varchar(20), Age int(2), Department varchar(15)) ;
- To get the details of the table-
describe table_name ;
Whenever, we need to see the details of the table created in the database, we use this command to retrieve all the data. For example, retrieving the details of table
describe employees ;
- Add values in the table-
insert into table_name values (value1, value2) ;
By using this query, we insert the data into the table. While inserting we must remember the datatype we declared for the specific column as well as the sequence of
the columns in the table to add data to it. Taking example to add data in table employees.
insert into employees values (101, Sushil Rastogi, 29, Marketing) ;
Sometimes, we didn’t remember the sequence of the columns as we inserted in the table. So for that we can use a different syntax to add the details of the table.
insert into table_name (column_name2, column_name1) values (value2, value1) ;
Thus, we can insert the details into the table according to the sequence we provided in the syntax and data will store in their specific columns in the database. For
example, inserting data in employees table without knowing the sequence of the columns of the table.
insert into employees (Name, ID, Department, Age) values (Akash Jain, 102, Management, 32) ;
- Check table details-
select * from table_name ;
Use retrieve full table with it’s data from the database, we use this syntax. For example, to retrieve table employees from the database GURUKUL
select * from employees ;
- Retrieve specific data from table-
select column_name from table_name ;
We can retrieve one or more specific column details from the table by using this syntax. For example, for employees table, if we need to extract name of employees from
select name from employees ;
If we want to extract more than one details, like ID and age of employees
select ID, age from employees ;
- Retrieve data from table with conditions-
select column_name from table_name where condition_1 ;
This is used just to extract the detail of some specific column with related to some condition that helps to identify the data we need to collect from the database.
For example, if we want to retrieve column of ID with condition that the ID having age = 32, then this syntax help in identifying the details of ID that having age 32
so that if there are any two similar IDs we can retrieve a single one we needed.
select ID from employees where age = 32 ;
- Retrieve full data with conditions-
select * from table_name where condition2 ;
To retrieve complete data of some specific identity, we use this query by applying conditions about the specific identity regarding which data is to be drawn from the
table. For example, if we need to retrieve complete data of marketing department from table employees
select * from employees where department = 'marketing' ;
Alteration and Editing Queries:
- Rename the existing table-
alter table table_name to new_name ;
If we need to change the name of the table then we can use this syntax. For example, we need to rename ’employees’ table name into ‘candidates’
alter table employees to candidates ;
- Remove table from the database-
drop table table_name ;
This syntax is used to remove the table from the database completely. For example, we need to remove candidates table from database
drop table candidates ;
- Clear data of the table-
truncate table table_name ;
If we need to clear the complete data of the table in the database but not the existence of the table then we use truncate query to drain out the data. For example, in
candidates table, to clear the data from the table
truncate table candidates ;
- Clear specific data of the table-
delete from table_name where condition2 ;
Sometimes, we need to delete the data of the table in the database according to the condition i.e. we need to delete any specific data but not the complete data of the
table nor the complete table, then we use this query. For example, we need to remove the details of person having ID = 101 from the table employees
delete from employees where ID = 101 ;
- Arrange order by-
select column_name from table_name order by decending ;
It is used to arrange the list in decending order. For example, if we need to arrange the ID of table employees in decending order
select ID from employees order by decending ;
select column_name from table_name order by ascending ;
It is used to arrange the list in ascending order. For example, if we need to arrange the ID of table employees in ascending order
select ID from employees order by ascending ;
- Arrange with limit-
select column_name from table_name limit_value ;
It is used to apply limits to the column values. The limit query is applied to retrieve the limited values i.e. 1,2,3,4.. and so on, from the top of the list of values
or attributes in the column. For example, if we want to get the list of only one employee from the name column, the query used will give the name of the first employee
from the top.
select name from employees limit1 ;
- Arrange order by with limits-
select column_name from table_name order by decending limit_value ;
It is just the join venture of the query limit and order by as used individually. It is for arranging the list in decending order and draw the values using limit from
the top of the new list formed by decending order arrangement. For example, if we need to arrange the ID of table employees in decending order with the list of only
one employee from the ID column, the query used will give the ID of the first employee from the decending order from the top.
select name from employees order by decending limit1 ;
select column_name from table_name order by ascending limit_value ;
It is just the join venture of the query limit and order by as used individually. It is for arranging the list in ascending order and draw the values using limit from
the top of the new list formed by ascending order arrangement. For example, if we need to arrange the ID of table employees in ascending order with the list of only
one employee from the ID column, the query used will give the ID of the first employee from the ascending order from the top.
select name from employees order by ascending limit1 ;
Key is the most important phenomenon in the server based management system. It is used to manage the identification as well as uniqueness of the data or details in the
list of attributes of columns. There are various types of keys, decribes hereby-
- Super key-
It is defined as a set of attributes within a table that uniquely identifies each record within a table. Super key is a superset of Candidate key.
- Candidate key-
It is a minimal super key in the entity set. It is defined as the set of fields from which primary key can be selected. It is an attributes or set of attributes that
can act as a primary key for a table to uniquely identify each record in that table.
- Primary key-
It is used to identify a record uniquely in a database.
- Secondary key-
The candidate key which are not selected for primary key are known as secondary keys. It is also known as “alternative keys”.
- Composite key-
The key that consist of two or more attributes that uniquely identify an entity occurence is called composite key.
- Foreign key-
It is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second.