MySQL is one of the most commonly used relational database management system (DBMS). It allows you to work very efficiently with large bulks of data. The most important entity of any database is a table and there are multiple different operations associated with this entity with which you can manipulate your data. Therefore, today we will learn to work with tables in MySQL and MariaDB in Ubuntu 20.04.
Working with Tables (Select, Update, Delete, Create Table, Alter Table, and Drop Table) in MySQL in Ubuntu 20.04:
To work with tables in MySQL in Ubuntu 20.04, you can go through all the steps described below:
Step # 1: Ensure that a MySQL compatible database is installed on your Ubuntu 20.04 System
When you intend to work with tables in MySQL, you must have MySQL or MariaDB installed and working on your Ubuntu 20.04 system. To check if MySQL is installed on our Ubuntu 20.04 system or not, we will execute the following command in our terminal:
mysql --version
If MySQL will be installed on your Ubuntu 20.04 system, then you will be able to see its version after executing this command as shown in the image below:
However, just in case, MySQL is not installed on your Ubuntu 20.04 system, then before proceeding further, you can conveniently install it by following our tutorial on the installation of MySQL on Ubuntu 20.04.
Step # 2: Enter MySQL Shell from Ubuntu 20.04 Terminal:
Once you are sure of the existence of MySQL on your Ubuntu 20.04 system, you can access the MySQL shell for running commands in it by executing the following command:
sudo mysql
When you will run the above-mentioned command, you will immediately enter MySQL shell as shown in the image below:
Step # 3: Create a Database in MySQL in Ubuntu 20.04:
Now when we are inside the MySQL shell, the first thing that we need to do is to create a database so that we can create tables inside it to perform different operations. A database in MySQL can be created with the following command:
mysql> create database DBName;
Here, you have to replace DBName with any name that you want for your database. We have named it MyDB.
Once your database is created, you will receive a message on MySQL shell similar to the one shown in the image below:
Step # 4: Switch to the Newly Created Database in MySQL in Ubuntu 20.04:
When a database with your desired name has been created, you need to switch to that database so that when you will create tables in MySQL, they will be created inside this database. Without selecting a specific database, you are not allowed to create tables in MySQL. For switching to our newly created database, we will run the following command:
mysql> use MyDB
When this database will be selected successfully, you will receive the message shown in the image below on the MySQL shell.
Step # 5: Create a Table in MySQL in Ubuntu 20.04:
After switching to our desired database, we can create a table in MySQL by running the following command:
mysql> CREATE TABLE TableName(Col1 Datatype, Col2 Datatype, ….);
Here, you have to replace TableName with any name that you want for your table. We have named it Student. Col1, Col2, etc. represent the names of the columns of your database whereas Datatype refers to the data types of each of these columns respectively. You can create as many columns or attributes for your table as you want. We created three columns named StudentID, StudetName, and StudentAge with the data types int, varchar(255), and int respectively.
If this command is executed successfully, you will receive the message shown in the image below on the MySQL shell.
Step # 6: Insert Records into the Newly Created Table in MySQL in Ubuntu 20.04:
Once a table has been created in the MySQL database, we can insert records to this table by executing the following command:
mysql> INSERT INTO Student VALUES (1, ‘Ayesha’, 24);
You can change these values according to your own choice.
When our new record will be successfully added to our table, we will receive the message shown in the image below on the MySQL shell.
In the very same manner, we have inserted another record as shown in the images that follow:
Step # 7: Display the Entries of your Table in MySQL in Ubuntu 20.04:
When we have added a few records to our table, it is the right time to view these records. The SELECT statement is used for this purpose. If you want to display all the records or entries of your table in MySQL at once, then you can execute the following command:
mysql> SELECT * From Student;
Executing this command will display all the records of your table in a nice tabular form as shown in the image below:
In the very same manner, if you want to display the values of a specific column or columns, then you can replace “*” in the command above with the name of that specific column.
Step # 8: Update a Record in your Table in MySQL in Ubuntu 20.04:
The UPDATE command in MySQL is used to modify the values of any specific record. For example, if you want to change the values of any record of your table, then you have to execute the following command:
mysql> UPDATE Student SET StudentName=’Saad’, StudentAge=24 WHERE StudentID=2;
Here, you can replace the value of StudentID with the ID of the record that you want to change.
After executing this command, you will receive the message shown in the image below in the MySQL shell.
We can verify if the said changes have taken place or not by using the same SELECT statement once again to view the records of our table. You can see from the following image that our specified record has been updated successfully.
Step # 9: Alter your Table in MySQL in Ubuntu 20.04:
The ALTER command in MySQL is used to Add or Drop a specified column from a table. For example, if we want to delete any specific column from our table, then we will execute the following command:
mysql> ALTER TABLE Student Drop COLUMN StudentAge;
Here, you can replace StudentAge with any column of your choice that you want to drop.
After the successful execution of this operation, you will receive the message shown in the image below on the MySQL shell.
To check if the specified column has been dropped or not, we will run the SELECT command once again and its results are shown in the following image which confirms that the said column has been dropped successfully.
In the same manner, you can even add a new column to your table by replacing the DROP keyword with ADD and StudentAge with the name of your new column to be added.
Step # 10: Delete a Specific Record from your Table in MySQL in Ubuntu 20.04:
The ALTER statement in MySQL is used to Add or Drop columns. However, if you want to delete a specific record from a table in MySQL, then you have to execute the following command:
mysql> DELETE FROM Student WHERE StudentID=1;
If the specified record is deleted successfully, you will receive the message shown in the image below on the MySQL shell.
We can verify it by running the SELECT command once again and its results are shown in the following image which confirms that our desired record has been deleted from the table.
Step # 11: Delete all Records from your Table in MySQL in Ubuntu 20.04:
Now, if you want to get rid of all the records of your table in MySQL at once, then you can execute the following command:
mysql> DELETE FROM Student;
When this command will execute successfully, you will receive the message shown in the image below on the MySQL shell.
Ideally, this command should clear all the entries of your table which can be verified by running the SELECT command yet again as shown in the following image. The receipt of an Empty Set message indicates that all the records from our specified table have been deleted successfully.
Step # 12: Drop your Table in MySQL in Ubuntu 20.04:
Finally, we can drop a table by executing the following command:
mysql> DROP Table Student;
The successful execution of this command will drop the selected table and the message shown in the image below confirms this deletion.
Conclusion
Today’s article was basically a beginner’s tutorial for working with tables in MySQL in Ubuntu 20.04. We showed you the basic operations of tables in MySQL with very simple examples. However, once a good understanding of all these commands is established, then you can even perform more complex operations.
