In this chapter, we learn to manage databases: create them, delete them, rename them and switch from one to another.

The two basic commands

-- Create a database
CREATE DATABASE database_name;

-- Delete a database
DROP DATABASE database_name;

You can do the same with a GUI (phpMyAdmin, Workbench…). Here, though, we use the command line: the terminal on Linux, the Command Prompt (CMD) on Windows.

Connecting to MySQL from the command line

Before running any command, you need to connect to the MySQL server:

mysql -u root -p -h 127.0.0.1
OptionRole
-uThe user, here root
-pPrompts for the password (WAMP has none by default: just press Enter)
-hThe server host, here 127.0.0.1 since we’re working locally

On Windows, if the mysql command isn’t recognized, run it from the MySQL bin folder inside WAMP, or add that folder to your PATH.

Creating and deleting a database

CREATE DATABASE test_safe;
-- Query OK, 1 row affected

DROP DATABASE test_safe;

Don’t forget the semicolon at the end of every command.

Create the database again, then refresh phpMyAdmin: test_safe shows up, empty. Delete it with DROP DATABASE, refresh, and it’s gone.

With phpMyAdmin

  • Create: click New database, give it a name and confirm. Keep the default collation for now.
  • Delete: open the database, go to the Operations tab, then Drop the database (DROP).

Renaming a database

There is no RENAME DATABASE command in MySQL. We go through a few steps instead:

# 1. Export the old database into a SQL file
mysqldump -u root -p old_database > backup.sql

# 2. Create the new database
mysql -u root -p -e "CREATE DATABASE new_database"

# 3. Import the content into the new database
mysql -u root -p new_database < backup.sql

# 4. Once everything is checked, delete the old database
mysql -u root -p -e "DROP DATABASE old_database"
  • > sends the output of mysqldump into the backup.sql file.
  • < does the opposite: it feeds the file’s content to MySQL.

Delete the old database last, after checking that the import worked. That way you lose nothing if a step fails.

With phpMyAdmin, it’s faster: open the database, go to the Operations tab, then Rename database to.

My advice: learn the commands first, then use GUIs to save time. That way you always know what’s happening behind the scenes.

Listing databases and picking one

-- Show every database on the server
SHOW DATABASES;

-- Work on a specific database
USE test_safe_1;
-- Database changed

On a server with several databases, USE tells MySQL which one you want to work on: every following command applies to it.

Recap

CommandRole
CREATE DATABASE name;Create a database
DROP DATABASE name;Delete a database
SHOW DATABASES;List databases
USE name;Pick the database to work on
mysqldump + importRename (or back up) a database

Exercise · A database for a blog

From the command line: create a blog database in utf8mb4, check that it shows up in the list, select it, then delete it.

Show the solution
CREATE DATABASE blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE blog;
DROP DATABASE blog;

What’s Next

In the next chapter, we fill our database: tables, relationships and data manipulation.


This is Chapter 3 of the SQL from Zero to Pro course. Follow along to learn SQL with MySQL, from the basics to joins.