Storage data with Database MySql

What is DataBase(DB)

A database is a structured collection of information that is organized so that it can be easily accessed, managed and updated. In visualization, we can use a table to image about that. Example, I need to storage data about students in class, I need to make a table with some columns and some rows. In detail, I consume that each of student has name, age, class name, date to join into class. I will make a table with 4 columns

  • Name
  • Age
  • Class name
  • Date join class

And each row is one student. This is result of that

This a table in DB, which store data about student, and if you are an officer on window OS, or google sheet, you can use google sheet, window excel to do that. In here, I introduce you a new application DB look like that, but for developer to make a program.

MySql, relation DB

Mysql

MySQL is a database management system. To add, access, and process data stored in a database(collection of tables like above), you need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications

To install Mysql Server, follow this link https://dev.mysql.com/doc/refman/8.0/en/installing.html

I will not support detail how to install Mysql in this article, that is showed in other article and continue that you installed MySql successfully.

After installing MySql server, you need to install MySql client to connect Mysql Server. This is tool which use to access data in MySql, create table, update/insert rows, delete data …

We have many client to do that, this is some

  • MySql workbench(window, mac, ubuntu) here
  • Sequel Pro(Mac) here
  • Navicat MySql (window, mac, ubuntu) here

Get one and install, open it and connect to your local MySql server(default is 127.0.0.1:3306 root/root). In here, I used Sequel Pro

I’m ready to create a new database for MysqlServer with Sequel Pro tool

Database name: demo

Create a table student

Define some column

And you have a student table

Continue to create some row

Try and contact to me if you can not do it. If use other MySql client, you also easily find some feature of them which can help you do that like me.

Relation DB

MySQL databases are relational. A relational database stores data in separate tables rather than putting all the data in one big storeroom. That mean you can not put a lot of data into one table. You need to split into many table to store many collection of data.

Example, we have many classes and students in class. Students have some data about student, classes have some data about class. So you need to create 2 table student and class to store information of them and make a relation from class to student.

1/ For class table, I have columns: id, name, teacher

2/ For student table, I have column: id, name, age, datejoin, classid

Notice, student table has column classid, that is value of column id in class table, that mean I make a relation from class table to student table. By this way, you can know class of student or how many students in class

Use query to get data from relation DB

Mysql Client always provide view tool to show data of table, but you only use it to view single table. In case you want more, view data of many table, view data more flexible about student and class… We will study getting data with sql query string. Like above, because of limiting of this article, I will support you understand some simple query on MySql

  • Select data
  • Select many table data
  • Update data
  • Delete data
  • Insert data