Introduction to Relational Database - SQL - Basic SQL and Relational Database - Whypress

Contents

Introduction to Relational Database, SQL and JDBC

JDBC stands for Java Database Connectivity, which is a Java Application Programming Interface for a client to access databases. So at first we should know about databases, especially relational databases.

A database is a collection of data. A relational database is a database that stores data in tables, each table consists of rows and columns. For example, we have two tables, one is the Customers table:

id	name
1	Krisha
2	Prisha

The other table is Sales table:

id	customer_id	Qty	Price
1	2	5	5
2	1	3	6
3	2	9	3

Each table has a key column, that is the primary key, the value in the primary key column must be unique. In the Sales table we can find the name of the customer very quickly by querying the customer_id in Customers table. The relational database uses SQL for querying and manipulating data. (SQL stands for Structure Query Language)

There are a lot of relational databases, the most famous one is the Oracle Database. Some other popular relational databases:

– MySQL

– Microsoft SQL Server

– PostgreSQL

Of those, PostgreSQL is totally free. MySQL has two versions, the MySQL Community version is free, the MySQL Enterprise Edition (MySQL EE) is not.

MySQL installation

In our example, we will install MySQL Community Edition. Go to the web page:

https://dev.mysql.com/downloads/mysql/

Here we can choose our OS system version and download the installer. After downloading just run the installer and we are good to move next. Here, I will install the MySQL Community Server version 5.7.19, to download it, we have to go to archive page:

https://downloads.mysql.com/archives/community/

Installing MySQL Community Server on Windows

1. Extract the zip file to a folder

2. Create my.ini file under MySQL root directory.

[mysqld]
# set basedir to your installation path
basedir="D:\database\mysql"
# set datadir to the location of your data directory
datadir="D:\database\mysql\data"

3. Navigate to bin folder and run:

mysqld.exe --initialize-insecure

4. Start MySQL: Navigate to bin folder and run:

mysql -u root

Creating demo user and database

Now, after installing MySQL Community Server, open a terminal (or CMD in window):

sudo mysql

We will enter mysql command prompt with root user:

mysql>

Next we will create a database named test, note that in MySQL a command line will end with a semicolon ;

mysql> create database test;

Then we create a user that is testuser with password Test@12345:

mysql> create user 'testuser'@'localhost' identified by 'Test@12345';

We will grant the testuser access permission to the test database:

mysql> grant all on test.* to 'testuser'@'localhost';

Now we quit the root user:

mysql> quit;

Still in the terminal, we will login MySQL with testuser:

mysql -u testuser -p

Enter the password Test@12345 and we will be at the MySQL command console. We can use Alt + L (or Option + L on Mac) to clear the screen.

Basic SQL commands

At the mysql> command prompt we can use the SQL commands to query and manipulate our test database. Note that each SQL statement ends with a semi-colon ;

show databases

This query will show all the databases that the testuser has the access permission. Run the line:

mysql> show databases;

And we have the output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

The database information_schema is the default database and we should ignore it.

use

We use this command to choose a database:

mysql> use test;

Run this and we have the output:

Database changed

Java, SQL

Introduction to Relational Database – SQL