Interview :: MySQL
How to delete a table in MySQL?
We can delete a table in MySQL using the Drop Table statement. This statement removes the complete data of a table, including structure and definition from the database permanently. Therefore, it is required to be careful while deleting a table. After using the statement, we cannot recover the table in MySQL. The statement is as follows:
How to add foreign keys in MySQL?
The foreign key is used to link one or more tables together. It matches the primary key field of another table to link the two tables. It allows us to create a parent-child relationship with the tables. We can add a foreign key to a table in two ways:
- Using the CREATE TABLE Statement
- Using the ALTER TABLE Statement
Following is the syntax to define a foreign key using CREATE TABLE OR ALTER TABLE statement:
How to connect to the MySQL database?
MySQL allows us to connect with the database server in mainly two ways:
Using Command-line Tool
We can find the command-line client tool in the bin directory of the MySQL's installation folder. To invoke this program, we need to navigate the installation folder's bin directory and type the below command:
Next, we need to run the below command to connect to the MySQL Server:
Finally, type the password for the selected user account root and press Enter:
After successful connection, we can use the below command to use the:
Using MySQL Workbench
We can make a connection with database using MySQL Workbench, simply clicking the plus (+) icon or navigating to the menu bar -> Database -> Connect to Database, the following screen appears. Now, you need to fill all the details to make a connection:
Once we finished this setup, it will open the MySQL Workbench screen. Now, we can double click on the newly created connection to connect with the database server
How to change the MySQL password?
We can change the MySQL root password using the below statement in the new notepad file and save it with an appropriate name:
Next, open a Command Prompt and navigate to the MySQL directory. Now, copy the following folder and paste it in our DOS command and press the Enter key.
Next, enter this statement to change the password:
Finally, we can log into the MySQL server as root using this new password. After launches the MySQL server, it is to delete the C:\myswl-init.txt file to ensure the password change.
How to create a database in MySQL Workbench?
To create a new database in MySQL Workbench, we first need to launch the MySQL Workbench and log in using the username and password. Go to the Navigation tab and click on the Schema menu. Right-click under the Schema menu and select Create Schema or click the database icon (red rectangle), as shown in the following screen.
A new popup screen appears where we need to fill all the details. After entering the details, click on the Apply button and then the Finish button to complete the database creation.
How to create a table in MySQL Workbench?
Launch the MySQL Workbench and go to the Navigation tab and click on the Schema menu where all the previously created databases are shown. Select any database and double click on it. It will show the sub-menus where we need to select the Tables option.
Select Tables sub-menu, right-click on it and select Create Table option. We can also click on create a new table icon (shown in red rectangle) to create a table. It will open the new popup screen where we need to fill all the details to create a table. Here, we will enter the table name and column details. After entering the details, click on the Apply button and then the Finish button to complete the table creation.
How to change the table name in MySQL?
Sometimes our table name is non-meaningful. In that case, we need to change or rename the table name. MySQL provides the following syntax to rename one or more tables in the current database:
If we want to change more than one table name, use the below syntax:
Sometimes we need to change or rename the database name because of its non-meaningful name. To rename the database name, we need first to create a new database into the MySQL server. Next, MySQL provides the mysqldump shell command to create a dumped copy of the selected database and then import all the data into the newly created database. The following is the syntax of using mysqldump command:
Now, use the below command to import the data into the newly created database:
How to import a database in MySQL?
Importing database in MySQL is a process of moving data from one place to another place. It is a very useful method for backing up essential data or transferring our data between different locations. For example, we have a contact book database, which is essential to keep it in a secure place. So we need to export it in a safe place, and whenever it lost from the original location, we can restore it using import options.
In MySQL, we can import a database in mainly two ways:
- Command Line Tool
- MySQL Workbench.
While creating a table, we have kept one of the column names incorrectly. To change or rename an existing column name in MySQL, we need to use the ALTER TABLE and CHANGE commands together. The following are the syntax used to rename a column in MySQL:
Suppose the column's current name is S_ID, but we want to change this with a more appropriate title as Stud_ID. We will use the below statement to change its name: