Mysql Create Database – How To Interact With A MySQL Database

In this post, we are going to learn about MySQL databases and how to interact and manipulate them. Specifically, we are going to look at MySQL create database and MySQL make database, MySQL rename database, and MySQL remove database. These commands are what a DBA uses to manipulate a database.

This post will look at standard MySQL database administration tasks that you can perform from the command line (on Windows) or Terminal (on Mac). For the purpose of this article, we are going to use command line.

MySQL Create Database

To make a new database in MySQL, you have a MySQL make database syntax to follow. But first, you need to log in to your MySQL server as root at the command line:

mysql -u root -p

The server will prompt you for your password, type it in and press enter. Once you are connected to the MySQL server, you can use the MySQL create database command to make a database. However, in some instances, before leveraging the right command for MySQL make dataset, you want to create a database user first. Use the command below to create a database user:

GRANT SELECT ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

By using the command above, the user is granted all permissions. You can still regulate database access by granting database users specific permissions:

GRANT SELECT ON *.* TO 'username'@'localhost';

The command above grants a database user only the permission to select but not alter a database. In both commands, replace the “username” and “password” with the name of the database user and the password they will be using.

Exit MySQL as root by typing /q and log back in as the user you create with all privileges. To log back in:

mysql -u username -p

make sure to replace the username with the name of the user you created above.

Follow the prompt to input password and press enter. Now you can create your first database using the MySQL create database command:

The ‘dbname’ in the MySQL create database command should be replaced with a database name. When naming your database, there should be no spaces between names. Instead, use an underscore to separate words in the ‘dbname’.

Now that you know how to use MySQL create database and you have a database, you can start using the database and populating it. To start using the database:

USE dbname;

In the command above, the ‘dbname’ should be the name of the database you created earlier. That command will allow you to add data to your table with commands such as

CREATE TABLE sample (id smallint unsigned not null auto-increment, name varchar (20) not null, constraint pk_sample primary key (id));

And

INSERT INTO sample (id, name) VALUES (null, 'Sample data');

But that is not the only way to create and populate a database in MySQL. Instead, you can streamline the process further by creating an SQL script file with all the commands above. Like above, the first process is creating a database user using the same procedure.

Once you have created a user, create a file and name it sample.sql. Open it in your text editor of choice. In the file, paste the commands below:

CREATE DATABASE dbname;
USE dbname;
CREAT TABLE tablename (id smallint unsigned not null auto-increment, name varchar(20) not null, constraint pk_example primary key(id));
INSERT INTO table name (id, name) VALUES (null, 'Sample data');

The only things to replace above are the ‘tablename’ and ‘dbname’ with the names you want. The benefit of using a script is that you can create as many databases and tables as you want by running a single script. As you can already tell from the sample above, the table you will create with this script has minimal data requirements.

Save sample.sql file and close the editor. Go back to the command prompt and log in as the database user you created and call the script as below:

mysql -u username -p < sample.sql

The statements in the script will run one by one starting with the first create table command to the last insert statement.

Renaming A Database In MySQL

If you are wondering how to rename databases and tables in MySQL below are the best steps for MySQL rename database you need to follow.

On the command line, start by logging into your server and then create a new database. To create a new database, use the command:

mysql -u username -p -e "CREATE DATABASE new_dbname"

The username should be the name of the user you created and the new dbname the name of the new database. The next step involves dumping the old database/exporting it to a file. To do so, type in the command:

mysqldump -u username -p old_dbname > dbexport.sql

After exporting data from the old database, you now need to import it to the new database:

mysql -u username -p new_dbname < dbexport.sql

Once you confirm the data has been migrated to the new database, you can choose to keep or delete the old database. Should you choose to delete the old database, here is a simple command:

mysql -u username -p -e "DROP DATABASE "old_name"

The dumping sequence and importing to a new database is the safest way of renaming a database. Previously, there was a different MySQL rename database (RENAME DATABASE) syntax but that MySQL rename database process proved dangerous and was phased out in MySQL 5.1.23.

You can also rename tables and move them across databases with InnoDB but that is only if your MySQL version is 5.5 and above. From version 5.5, MySQL uses the InnoDB storage engine, simplifying the process of renaming databases.

With InnoDB, you only need the RENAME TABLE command in a MySQL prompt. This will change a specific table’s dbname without altering the table name. Similarly, as with the dump sequence, you need to create a database with the new name first.

Assuming we have a database named INITIALS that we want to rename to NAME – the first step is to create the new database in the mysqladmin shell:

mysqladmin -u username -p"password" create NAME

Once the new database is created, connect to mysql prompt and issue the command below:

mysql> RENAME TABLE initial.identity TO name.identity;

The command above moves the table identity from the initial database to the newly created name database.

How To Remove A Database In MySQL

As long as you have used the MySQL make database commands before, chances are you will need to remove some of those databases later. The MySQL remove database process is a fairly straightforward one. However, to be able to remove any database from a MySQL server, you must have all relevant privileges including read and write. 

Ideally, the best user to remove MySQL databases is root although any other user with the right privileges can drop a database.

On the Windows command prompt or Mac Terminal, log into your server as root.

On logging in, the next step you want to take before you run the MySQL remove database command is to view a list off all the databases you have on your server:

mysql> show databases;

This is one step you don’t want to skip. By listing the existing database, you are able to locate the database you want to delete and verify its exact name as it appears in the server. If you are on a Mac, make sure you note whether the database names are in lower or upper case.

To delete a database in Mac, you have to capitalize the database name is it appears so on the server.

Once you have verified the database you want to drop, you can proceed with MySQL remove database. However, the command for removing a database from a MySQL server, just like that for MySQL make database (CREATE DATABASE dbname) is different. To delete a database named Staff in MySQL, use the command:

DROP DATABASE Staff;

After deleting the database, use the command show databases to confirm that your database has been purged.

With the commands above, you are now equipped to start manipulating databases in your server.