From Basic to Advanced MySQL Commands│MySQL & MariaDBの世界

1. Introduction

1.1 Overview of MySQL and the Importance of the Command Line

MySQL is a widely used open-source relational database management system (RDBMS). Its main advantages include efficient data management and flexible data operations using SQL (Structured Query Language). Many web applications and enterprise systems rely on MySQL, and its powerful features can be fully utilized through the MySQL command line.

1.2 Purpose of This Article

This article focuses on MySQL command-line operations, covering everything from creating and managing databases to setting user permissions and executing advanced commands. It aims to provide practical knowledge for beginners and intermediate users to master MySQL effectively.

1.3 Target Audience

This guide is designed for beginners and intermediate users interested in MySQL. It is ideal for those with basic database knowledge who want to use MySQL for data management or web development.

2. Basic MySQL Commands

2.1 Connecting to and Disconnecting from a Database

To access MySQL, you must first connect to a database. The most commonly used command to log in to a MySQL server is:

mysql -u root -p

After entering this command, you will be prompted to enter your password. Once you provide the correct password, you will gain access to the MySQL command line.

To disconnect from the server, use either the exit or quit command:

exit

This command logs you out of the MySQL server and returns you to the command prompt.

2.2 Creating and Viewing Databases

To create a new database, use the CREATE DATABASE command. The following example creates a database named mysqldemo:

CREATE DATABASE mysqldemo;

After executing this command, a “Query OK” message confirms that the database was successfully created.

To display a list of existing databases, use the SHOW DATABASES command:

SHOW DATABASES;

This command displays all databases currently available on the server.

2.3 Selecting a Database

If multiple databases exist, you must specify which one you want to work with. Use the USE command to select a database for operations:

USE mysqldemo;

This command sets mysqldemo as the active database, meaning that all subsequent commands will apply to it.

3. Basic Table Operations

3.1 Creating Tables

To store data in a database, you first need to create tables. Use the CREATE TABLE command to define a new table. For example, the following command creates a table named users:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255),
    PRIMARY KEY (id)
);

This command defines three columns (id, name, and email) in the users table. The id column is an integer with auto-increment enabled and serves as the primary key.

3.2 Viewing Tables

After creating a table, you can check the list of tables within the currently selected database. Use the SHOW TABLES command:

SHOW TABLES;

To check the structure of a specific table, use the DESCRIBE command. This command displays information about the table’s columns, data types, and attributes.

DESCRIBE users;

The above command displays the column details of the users table, including data types and constraints.

3.3 Inserting and Viewing Data

To add data to a table, use the INSERT INTO command. The following example inserts a new user into the users table:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

This command adds a new record to the users table with the specified values for name and email.

To display all data from the table, use the SELECT command:

SELECT * FROM users;

This command retrieves all records from the users table.

4. User Management and Security

4.1 Creating Users and Assigning Permissions

In MySQL, it is essential to create users and assign appropriate permissions to control database access. Use the CREATE USER command to create a new user. The following example creates a user named user1 on localhost with the password password123:

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

This command creates a new user named user1, who can only access the database from the local server.

To grant permissions to the user, use the GRANT command. For example, to give user1 full privileges on the mysqldemo database, use the following command:

GRANT ALL PRIVILEGES ON mysqldemo.* TO 'user1'@'localhost';

This grants user1 full access to all tables within the mysqldemo database. To apply the changes, execute the FLUSH PRIVILEGES command:

FLUSH PRIVILEGES;

4.2 Changing User Passwords

To change an existing user’s password, update the mysql database’s user table using the UPDATE command. The following example changes the password for the root user:

UPDATE mysql.user SET authentication_string = PASSWORD('newpassword') WHERE User = 'root';
FLUSH PRIVILEGES;

This updates the password for the root user to newpassword. Running FLUSH PRIVILEGES ensures that the changes take effect.

4.3 Best Practices for Security Enhancement

To improve MySQL security, follow these best practices:

Remove Unnecessary Anonymous Users: Delete default anonymous users to ensure that only authenticated users can access the database.
Disable Remote Root Login: To enhance security, disable remote login for the root user.
Use Strong Passwords: Create strong, unpredictable passwords and update them regularly.

By implementing these measures, you can enhance database security and prevent unauthorized access.

5. Advanced MySQL Commands

5.1 Updating and Deleting Data

To update data in a table, use the UPDATE command. For example, to update the name column in the users table, use the following command:

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

This command updates the name field to Jane Doe for the record where id is 1. Be careful when using UPDATE without a WHERE clause, as it will update all records in the table.

To delete data from a table, use the DELETE command. For example, to delete the record where id is 1:

DELETE FROM users WHERE id = 1;

This command removes the record with id = 1 from the users table.

5.2 Backup and Restore

To create a database backup, use the mysqldump command. This command exports the entire database into an SQL file. For example, to back up the mysqldemo database:

mysqldump -u root -p mysqldemo > mysqldemo_backup.sql

To restore the database from the backup, use the source command. The following example restores the database from mysqldemo_backup.sql:

mysql -u root -p mysqldemo < mysqldemo_backup.sql

This imports the contents of mysqldemo_backup.sql into the mysqldemo database.

5.3 Starting and Stopping the Server

To start the MySQL server from the command line, use the mysqld command. In a Windows environment, you can start MySQL with:

"C:Program FilesMySQLMySQL Server 5.7binmysqld"

To stop the server, use the mysqladmin command:

"C:Program FilesMySQLMySQL Server 5.7binmysqladmin" -u root -p shutdown

This command safely shuts down the MySQL server. Command-line start and stop operations are especially useful in environments where GUI tools are unavailable.

6. Troubleshooting

6.1 Common Errors and Solutions

One common MySQL error is the “Access denied for user” error. This occurs when the specified username or password is incorrect. To resolve this, verify the credentials and try logging in again.

Another common error is “Unknown database,” which occurs when the specified database does not exist. Use the SHOW DATABASES command to check if the database exists, and create it if necessary.

SHOW DATABASES;
CREATE DATABASE db_name;

6.2 Best Practices and Tips for Database Operations

When working with databases, consider the following best practices:

Always Take Backups: Before making changes, always back up your database to prevent data loss in case of an error.
Use Transactions: When executing multiple queries as a single operation, use transactions to maintain data integrity. Use START TRANSACTION, COMMIT, and ROLLBACK commands.
Use Precise WHERE Conditions: When using UPDATE or DELETE commands, always include a specific WHERE condition to avoid unintended modifications.

By following these precautions, you can minimize potential issues and safely manage your MySQL databases.

7. Conclusion

The MySQL command line is a powerful tool for efficiently handling both basic and advanced database operations. This guide has covered fundamental MySQL commands, including creating databases, managing tables, handling users, and performing data modifications.

In database management, security and data integrity are critical. Therefore, it is essential to follow best practices such as setting user permissions, managing passwords securely, and regularly taking backups. Additionally, understanding troubleshooting techniques will help you quickly resolve potential issues.

By mastering MySQL commands, you can efficiently and securely manage databases. Keep practicing and applying your knowledge to enhance your database management skills.

Prev

Mastering MySQL Backup and Restore: How to Use mysqldump and Best Practices
Next
記事がありません

From Basic to Advanced MySQL Commands