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...
Simply put, a database is just a table built with rows and columns where each row contains a single record.
Install tasksel: sudo apt install tasksel
Next type in, sudo tasksel
, to run it.
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!
Command:
sudo netstat -tlnp
sudo netstat -tlnp | grep mysql
Note:
MySQL uses port 3306 by default.
You are never asked for confirmation when working in the MySQL command line.
We are using MySQL as root which is a poor practice.
Good practice: create a user and use mysql -u username -p
Usually, people type in SQL commands in capital letters.
​
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;
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
:
11. Inserted some sample values in the users table: insert into users(name, age, gender, country) values ('Bablu', 25, 'M', 'India');
12. Using select command to select values. Remember, *
indicates all.
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.
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;
17. drop table users;
Delete a table (be careful)