Basics of Databases and Setup
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 taskselNext type in,
sudo tasksel, to run it.
Use navigation keys to go up and down and select
LAMP Serverby SPACE BAR, and Press Enter.It will set up a LAMP Server and lastly type in
sudo mysqlto fire up MySQL. All set!
Command:
sudo netstat -tlnpsudo 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 -pUsually, 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)
