Basic MySQL Commands: Creating and Managing Databases

Managing databases in MySQL can be a straightforward process once you familiarize yourself with some core commands. In this section, we will delve into essential MySQL commands that allow you to create, drop, and manage databases effectively. With a bit of practice, these commands will become second nature, guiding you through basic database management tasks.

Creating a Database

To begin your journey into the world of MySQL databases, you need to create one. The command to create a database is straightforward:

CREATE DATABASE database_name;

Example

CREATE DATABASE employee_db;

In this example, we create a database named employee_db. It’s a good practice to follow proper naming conventions, using lowercase letters and underscores instead of spaces. Once you’ve executed this command, you can confirm that the database has been created successfully by using the following command to list all databases:

SHOW DATABASES;

This will display a list of all databases currently available on your MySQL server, and you should see employee_db included in that list.

Selecting a Database

After creating your database, you need to select it in order to perform further operations. You can use the USE command for this:

USE database_name;

Example

USE employee_db;

Executing this command sets employee_db as the current database, and any subsequent commands you run will be executed within the context of this database.

Dropping a Database

If you need to remove a database for any reason, perhaps because it's no longer necessary or you want to start fresh, the DROP command is your go-to. Be cautious, however—this command permanently deletes the database and all of its contents!

DROP DATABASE database_name;

Example

DROP DATABASE employee_db;

After you run this command, the database employee_db will no longer exist on the MySQL server. If you try to select or show this database afterward, you will see an error message stating that it can’t be found.

Managing Database Permissions

In a multi-user environment, it’s important to manage who has access to your databases. You can use the GRANT command to assign privileges to users.

Granting Access

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Example

GRANT ALL PRIVILEGES ON employee_db.* TO 'john'@'localhost';

In this example, we are granting user john full access to all tables in the employee_db database. The host specifies where the user can connect from—localhost means they can only connect from the same machine as the MySQL server.

Revoking Access

If you need to remove permissions previously granted, you can use the REVOKE command:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

Example

REVOKE ALL PRIVILEGES ON employee_db.* FROM 'john'@'localhost';

After running this command, user john will no longer have access to the employee_db database.

Viewing Database Information

To check the current database and display information about it, you can run the SELECT DATABASE() command. This will show you the name of the database you are currently using.

Example

SELECT DATABASE();

Creating Tables Within a Database

Once you have your database set up, it’s time to create tables within it to store your data. The basic syntax for creating a table is:

CREATE TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);

Example

Here’s how you might create a simple employees table within your employee_db:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

In this example, we define an id column that automatically increments with each new row, a name column for employee names, a position column for job titles, and a salary column to store the employee's income.

Managing Tables

Dropping a Table

Similar to dropping a database, you can drop a specific table with the following command:

DROP TABLE table_name;

Example

DROP TABLE employees;

This command will permanently delete the employees table and all the data contained within it. Always exercise caution with DROP commands!

Altering a Table

If you want to modify an existing table — for example, adding a new column — you can use the ALTER TABLE command:

ALTER TABLE table_name
ADD column_name data_type;

Example

ALTER TABLE employees
ADD email VARCHAR(100);

In this case, we’re adding an email column to the employees table.

Retrieving Table Information

To see the structure of your tables, including the columns and their data types, you can use:

DESCRIBE table_name;

Example

DESCRIBE employees;

This command will provide a detailed rundown of the employees table structure, helping you understand each column's attributes.

Conclusion

With these fundamental MySQL commands at your disposal, you’re well on your way to creating and managing databases effectively. Whether you are setting up a new project or maintaining an existing system, mastering these commands will empower you to handle your database tasks with confidence.

Remember, always be cautious when using destructive commands like DROP. Regularly back up your databases and keep user permissions tightly controlled for safe and efficient database management. Happy querying!

Databases - MySQL