Getting started with Database, SQL and MySQL installation! Chapter 2

When anyone would like to start learning SQL few questions keep bothering them; which software should be installed? which database should I choose? And this kind of questions might be provoking them to start it.

In this blog series I will consider MySQL database, the reason is it is a very famous database. Additionally SQL queries would be same for all the other databases, only minon syntax changes will be required. Let me tell you one thing, SQL is not heavily programming language, SQL itself requires less coding so you really don’t have to worry about programming.

Before jumping on SQL queries let's try to understand few terminologies of SQL.

1. Database: It is a collection of data stored in the form of tables.
2. Table: Data is being stored in form of rows and column, as an example consider any data stored in EXCEl file.
   You might be wondering if we already have excel to store a data, why do we need SQL? Imagine you’ve 500+ excel sheets and if we would like to merge or compare multiple sheets do you think this is manageable? Database not only stores data but it gives you capability to access and manipulate multiple tables with a high speed. It is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc. The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data.
3. Database Categories: Relational and NoSQL
   Relational database: Data is stored in rows and columns like excel sheets. Additionally multiple tables have relations between them. What databases comes under this category; MySQL, SQL Server, Oracle, PostgreSQL, SQLite, MariaDB etc
   NoSQL database: Here we stored unstructured data; for ex. document, or GraphDB. What databases comes under this category; MangoDB, Hbase, cassandra, Neo4j.
4. What is DBMS: Database management system is a software which is used to manage the database. For example: MySQL, Oracle, etc are a very popular commercial database which is used in different applications. It provides protection and security to the database. In the case of multiple users, it also maintains data consistency. Example of DBMS is again as mentioned above.
5. Advantages of DBMS:

• Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database.
• Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.
• Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.
• Reduce time: It reduces development time and maintenance need.
• Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required.
• Multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces

6. Disadvantages of DBMS:

• Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software.
• Size: It occupies a large space of disks and large memory to run them efficiently.
• Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever.

Twenty years ago, choosing a database was much easier. In most cases, you could have chosen one of the Relational Database Systems and you were done. But in modern Software Development, choosing the right database is one of the most challenging tasks. There are a whopping 343 databases at present. They don’t really differ only based on SQL queries rather its about features each database provides, Security for instance.

In this blog series doing further we will discuss MySQL database.

In earlier blog we’ve discussed about what is SQL and many more, Just to summarise SQL is a language through which you can interact with a database for ex. MySQL. Using SQL you may write a queries to create a database, or update the record. So MySQL is a database and SQL is way to interact with MySQL database.

Fundamentals of SQL | Deep dive into SQL theory! Chapter 1

What is MySQL:

In 1995, two Software Engineers, Michael and David , created the Open Source Relational Database Management System (RDBMS) MySQL. Since its inception, MySQL quickly became popular in the industry and community for its enterprise-grade features and free, flexible (GPL) community license, and upgraded commercial license. Among the open-source Databases, while PostgreSQL focuses on innovation and advanced features, MySQL focuses on robustness, stability, and maturity. Today, MySQL is one of the most popular and widely used SQL databases.

Installation:

Sometimes installation is tricky part due to system configuration or local machine issues, Let’s discuss easy way to start learning SQL without installing software locally. Although I will give you way to install the software in a while. Like python their are many open source web IDE’s are available, one of them is GromIDE.
In order to start with this IDE, you’ve to login to this site, for simplicity login with any google account once done, create a new container, choose nodejs stack and click on install mysql to start with a container. Once done write a following command in a terminal window.

mysql-ctl cli

This will start your mysql prompt and now you can start writing your sql queries. Let’s write some basic queries.

-- To write comments sql we use '--' 

-- See what databases are present already 
show databases;

-- create your own database with name visionnlp
create database visionnlp;

-- delete database
drop database visionnlp;
 
-- use your choice of database out of listed ones
use visionnlp;

-- see which database you're using 
select database();

-- create a table inside selected database
create table students
(
name varchar(50),
age INT,
marks INT
);

-- show list of tables attached to visionnlp database
show tables;

-- see structure of the table
describe students; 

-- drop a table
drop table students;

-- play with additional sql queries 
drop database visionnlp;
select database() -- output will be null
-- create table without connecting to a database will give you an error 

-- obiviouly need to create a database that we've dropped earlier
create database visionnlp;

-- alternative way to create table without connecting to a database
-- now intead of using command use database we'll just create table inside visionnlp database
create table visionnlp.students
(
name varchar(50),
age INT,
marks INT
);

-- now connect to database and show database and tables as earlier we were not able to see it
use visionnlp;
show tables;

MySQL Local installation:

Download MySQL: The simplest and recommended method is to download MySQL Installer for Windows from https://dev.mysql.com/downloads/installer/ and execute it.

Select download option mysql-installer-community-.. , this will redirect you in new login window, If you’re first time user create your account and verify your email. or you can click on — ‘No thanks, just start my download’

After downloading, double click the MSI installer .exe file.

Then follow the steps below:

1. “Choosing a Setup Type” screen: Choose “Full” setup type. This installs all MySQL products and features. Then click the “Next” button to continue.

2. “Check Requirements” screen: The installer checks if your pc has the requirements needed. If there is some failing requirements, click on each item to try to resolve them by clicking on the Execute button that will install all requirements automatically. Click “Next”.

3. “Installation” screen: See what products that will be installed. Click “Execute” to download and install the Products. After finishing the installation, click “Next”.

4. “Product Configuration” screen: See what products that will be configured. Click the “MySQL Server 8.0.23” option to configure the MySQL Server. Click the “Next” button. Choose the “Standalone MySQL Server/Classic MySQL Replication” option and click on the “Next” button. In page “Type and Networking” set Config Type to “Development Computer” and “Connectivity” to “TCP/IP” and “Port” to default. Then, click the “Next” button.

5. Server file permissions : select default settings and click ‘Next’

6. “Authentication Method” screen: Choose “Use Strong Password Encryption for Authentication”. Click “Next”.

7. “Accounts and Roles” screen: Set a password for the root account. Click “Next”.

8. “Windows Service” screen: Here, you configure the Windows Service to start the server. Keep the default setup, then click “Next”.

9. “Apply Configuration” screen: Click the “Execute” button to apply the Server configuration. After finishing, click the “Finish” button.

10. “Product Configuration” screen: See that the Product Configuration is completed. Keep the default setting and click on the “Next” and “Finish” button to complete the MySQL package installation.

11. In the next screen, you can choose to configure the Router. Click on “Next”, “Finish” and then click the “Next” button.

12. “Connect To Server” screen: Type in the root password (from step 6). Click the “Check” button to check if the connection is successful or not. Click on the “Next” button.

13. “Apply Configuration” screen: Select the options and click the “Execute” button. After finishing, click the “Finish” button.

14. “Installation Complete” screen: The installation is complete. Click the “Finish” button.

In next blog series we will discuss more about How to write SQL syntax in right way, things that needs to take care while writing a sql syntax and many more.

Reference:
For installation reference — https://www.w3schools.com/mysql/mysql_install_windows.asp