In the last posts, we have seen how to create databases, tables and how to add, update and delete the data within them. In this post, we will see how we manage our database with the different-different operations provided by XAMPP’s phpMyAdmin.
Click on the database name to open it. I have opened our codetej database if you click on the structure tab you will get to see the tables inside that database. We can execute different-different operations on each database like inserting data inside the table, truncating the table(deleting just table data, not the whole table), or dropping the table, etc.
So these were the operations on the table. Now click on the operations tab to see the operations for the database.
So, as you can see there are many operations which we can perform on our database. There is a Database comment, as the name suggests we can give comments on our database. Next is Create Table which allows us to give the name to our desired table and the number of columns. Further, we can see there is Rename database to an operation that allows us to rename our database. After that Remove database operation is there which will be used to drop the whole database, if you execute this operation then your database will be deleted along with your data.
Below the Remove database operation, you will get to see the Copy database to operation in here you can copy your database into another. Write the name of the database in the textbox, select the appropriate options and click on Go. You will get to see the new database with the name you provided and the data will be the same as that of codetej. In Collation, you can define the collation for all the tables in the database.
Next is a designer tab, here you can see the design of your database in the form of ER Diagram. You can see the data like columns name, columns datatype and their values, also you can see the primary key and foreign keys. Also, you can add tables from here.
Now, this is the most important operation related to the database. Suppose you have created the database and it contains a lot of data, and you need to move it on the phpMyAdmin database of the server. In this case, you will not be able to use Copy to or Move to operations since you want your database on a different machine. In this situation, phpMyAdmin’s Export-Import feature comes into the picture. Click on the database you want to export and go to the export tab as shown below. There is one option to create a template that is optional. You can use it to save the export settings. So next time you export any database you don’t need to select each setting, you select this template and the related setting to that template will be applied automatically.
In the existing template select the template you have created and it will show you the Template was loaded. In the Export method, you can select Quick or Custom. In the Quick method, it will let you choose the File format to be exported. And in the custom method, you can select the options which you want.
Following are some of the options you will get if you select Custom as an export method.
Next is File Format, which lets you choose a variety of formats to export the database file. SQL is the preferred format while exporting the database.
After selecting the File Format as SQL click on Go. Your file will be downloaded as database_name.sql.
Now, this file contains the database structure and its data. To import this file you need to make an empty database on your desired machine. For the sake of example, I am doing it on the same machine in your case it will be the phpMyAdmin console of your server. I have created a new database as Codetej_V_2.
Click on the Import button present on the upper horizontal menu. You will get to see the Browse button. Click on it to select the databse_name.sql file. You can change the options which are below and click on Go. phpMyAdmin will import the content of the files in the new database. You will get to see the previous database contents in the new database.
As you can see the employee table with the same content. Also, check the database name it is the same as we have created earlier.
So these were some of the operations related to a database in phpMyAdmin of XAMPP.