Setting up MariaDB, Heidi SQL

If we want to manage and oversea a database, we can use MariaDB to do just that.

https://mariadb.com/downloads/

MariaDB Products & Tools Downloads | MariaDB

1.) select mariaDB community and select version 11.4.2-GA and windows 64 version before clicking download

2.) start the install and create root password> next > install

 

For Macs: we'll have to download the homebrew mariadb(find on google) and DBeaver (for SQL)

For windows: MariaDB and HeidiSQL

 

download HeidiSQL: https://www.heidisql.com/download.php

then click and set up by using the exe file.

1.) open heidiSQL and click new and type name and relevant data

 

If we ust want to access the data we just need heidiSQL, we do not need to install MariaDB.

---

Installing DBeaver in Eclipse

We can also alternatively install DBeaver in Eclipse as it is compatible to use within.

 

1.) Go to help> eclipse marketplace > type in DBeaver > install

2.) change perspectives from JAVA EE to DBeaver by clicking the icon in the top right corner after restarting

 

3.) connect to server of choice

 

Unlike HeidiSQL DBeaver lets you create and edit ERD (entity relationship diagram):

https://www.lucidchart.com/pages/er-diagrams

---

Protip!!

In SQL we need to define the type and size of the objects we will be using. Here are some handy ones:

• int(n) : holds up to number length inside bracket in binary form: int(11) = 1000000000
• varChar(n) : holds up the number of characters inside, it also shrinks and lengthens in relation to size
• enum: holds an if or else value:  (gender: M/F)

 

---

using HeidiSQL

There are alot of different sql software out there but the grammar and language to use them is all pretty much the same!

 

Here are some basic ones to remember:

 

SELECT: this keyword is used to fetch data 

SELECT * FROM employees; => fetching all (*) data from employees

SELECT emp_no,gender FROM employees; => only retrieving emp_no and gender from employees

 

CREATE: this creates something new. The example below creates a table

1.) select a database to use

2.) open Query tab and type:

 

DROP: deleting a table

command: DROP TABLE table1;

 

 

Running the SQL command

we can either use the f9 key or the arrow button on the Query tab.

If we just want to run a selection, we highlight it and press ctrl + f9 or rightclick > select run selection

 

Creating a new tab

press above icon to create a new Query tab

 

---

Excercise: Creating a table

 

some useful info:

• auto-increment : numbers automatically increasing by 1
• not null : not allowed to be empty
• current_timestamp(): gets the time data was entered
• default : assigning default value in ‘ ’
• Primary key: setting primary key > this is important as the b_no we set as index can be changed, this doesn't
• In DB all strings are surrounded by  '  ' not  "  " 
• If we use - - we can block out the text behind.

---

Excercise 2: adding values into a table

• think of each line as a row of a table!
• we define what each column of each line will contain first by adding it into the () brackets
• then we enter each value in a row.

Once a table is created we can edit the contents directly in within the data tab