Basics of Databases and Setup

Learn the basics of database and set-up the environment to play with databases.

Basic Terminology

What is a database software?

There are so many database software available in the market (aka Database software management system (DBMS)) that basically help users to manage data in a database using a language called SQL (Structured Query Language).

  • Examples: MySQL, SQL Server, PostgreSQL, SQLite and many more...

So what's a database?

Simply put, a database is just a table built with rows and columns where each row contains a single record.

Setting up an environment for learning:

  • Install tasksel: sudo apt install tasksel

  • Next type in, sudo tasksel, to run it.

Select LAMP Server
  • Use navigation keys to go up and down and select LAMP Server by SPACE BAR, and Press Enter.

  • It will set up a LAMP Server and lastly type in sudo mysql to fire up MySQL. All set!

Verify if MySQL is running or not:

Command:

  1. sudo netstat -tlnp

  2. sudo netstat -tlnp | grep mysql

confirming whether MySQL is running or not.

Note:

  1. MySQL uses port 3306 by default.

  2. You are never asked for confirmation when working in the MySQL command line.

  3. We are using MySQL as root which is a poor practice.

  4. Good practice: create a user and use mysql -u username -p

  5. Usually, people type in SQL commands in capital letters.

‚Äč

SQL Commands:

1. show databases; Lists all the databases

2. create database mytestDB; creates a database named mytestDB

3. use mytestDB; selects a database to make modifications like creating tables, adding records, etc. And, select database(); confirms what database you are in. (think of like pwd ).

4. show tables; shows tables for a selected database. It shows empty in this case because we haven't created any table yet.

5. Let's create a table called users using this command: create table users(user_id int auto_increment primary key, name text, age int, bio text);

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.

6. desc users; describes the table

7. Let's modify the table to add a new column named gender under text datatype: alter table users add country text;

altering the table

8. Adding more columns to the table alter table users add city text, add village text;

9. Dropping tables alter table users drop village; and alter table users drop city;

10. Notice, we've made a mistake. The gender is int datatype rather than text or varchar, etc. To modify the column in the table, we need to do the following - alter table users modify gender text:

modify the column datatype

11. Inserted some sample values in the users table: insert into users(name, age, gender, country) values ('Bablu', 25, 'M', 'India');

Inserted sample values to the table

12. Using select command to select values. Remember, * indicates all.

simple select query

13. WHERE clause in the SELECT statement to filter rows from the result set based on the conditions.

More examples:

14. order by clause is used for sorting in the ascending order and used desc in the end to set in the descending order.

Sorting by age

15. updating the row based on the condition(s): update users set age=26, name='Bablu Kumar' where user_id=1;

More example:

16. Deleting a record delete table from users where user_id=11;

deleting a record

17. drop table users; Delete a table (be careful)