Mysql Basics In Ubuntu¶

Mysql Basics In Ubuntu

MySQL is a opensource database software it helps users to store, organise and retrieve data. To manage the MySQL database we can install MySQL Workbench software. Let's see installation process of MySQL

How to install MySQL on ubuntu ?¶

We can install the MySQL database on ubuntu with the below command

sudo apt-get install -y mysql-server mysql-client

After executing the above command on the terminal it ask for the root password. Do not forget to take a note of the root password. You will need it to login to MySQL shell.

How to access the MySQL shell ?¶

To login to MySQL shell we need to execute the below command

sudo mysql -u root

In the above command -u represents "username" and -p represents "password". We can get more detail about about command using another command "mysql --help". After executing the above command it will ask for root password, enter the password to login to the MySQL shell.

Show list of databases using MySQL shell¶

To show list of all available databases in MySQL execute the SHOW DATABASES; It will show the output like below

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

Create a database using MySQL shell¶

mysql> CREATE DATABASE test;

Use database "test"¶

mysql> USE DATABASE test;

Above command will use the database test for further queries.

Create a table in the database "test"¶

Let's create student table in the database with below SQL query

mysql> CREATE TABLE student (
    id int, last_name varchar(255),
    first_name varchar(255),
    address varchar(255),
    city varchar(255) );

List all available tables in the database "test"¶

Let's write a SQL query to list all available tables in the database

 mysql> SHOW TABLES;
+------------------+
| student          |
+------------------+
1 row in set (0.01 sec)

Insert data into table "student"¶

Let's write a query to insert data into our newly created table "student"

mysql> INSERT INTO student (id, first_name, last_name, address, city)
VALUES(1, 'John', 'B', 'Noth Venis', 'California');
mysql> INSERT INTO student (id, first_name, last_name, address, city)
VALUES(2, 'Shera', 'D', 'Missori', 'Missori');

List all available rows in a table "student"¶

Let's write a query to list all available rows in the table "student"

mysql> select * from student;
+------+-----------+------------+------------+------------+
| id   | last_name | first_name | address    | city       |
+------+-----------+------------+------------+------------+
|    1 | B         | John       | Noth Venis | California |
|    2 | D         | Shera      | Missori    | Missori    |
+------+-----------+------------+------------+------------+
2 rows in set (0.00 sec)

Update a row in in a table "student"¶

Let's write a query to update the row with id "1" in table "student"

mysql> update student set first_name = "Sam" where id=1;
Query OK, 1 row affected (0.19 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Delete a row in in a table "student"¶

Let's write a query to delete the row with id "1" in table "student"

mysql> delete from student where id=1;
Query OK, 1 row affected (0.12 sec);

That's it folks. We can do more with mysql. Stay tuned for more articles.