Comprehensive MySQL Core Commands Tutorial

Hello, I am mikechen. MySQL is a fundamental database for large‑scale architectures and a must‑have skill for major tech companies.

Connecting to MySQL

mysql -uroot -p

Connecting to a remote MySQL host

mysql -h 127.0.0.1 -uroot -p123456

Note: the flag -u and root can be written without a space; the same applies to other flags.

Creating and dropping databases

create database mydb; // create a database

use mydb; // select the database

drop database mydb; // delete the database without confirmation

Changing the MySQL root password

mysqladmin -u root password "newpwd";

Adding a user and granting privileges

create user mikechen;

grant all on *.* to 'mikechen'@'localhost' identified by 'password'; // give all privileges to mikechen with password

Showing MySQL version

select version();

Listing tables in a database

use mysql; // switch to the mysql database

show tables;

Deleting data from a table

DELETE FROM student WHERE id=7;

Dropping an entire table

drop table tablename;

Updating table data

UPDATE student SET name='cao', grade=50 WHERE id=1;

Selecting data

select * from user;

Pagination query

SELECT * FROM users LIMIT 0,10; // page 1

SELECT * FROM users LIMIT 10,10; // page 2

SELECT * FROM users LIMIT 20,10; // page 3

Aggregation queries

SELECT COUNT(*) FROM users; // row count

SELECT MAX(age) FROM users; // maximum age

SELECT MIN(age) FROM users; // minimum age

SELECT AVG(age) FROM users; // average age

SELECT SUM(age) FROM users; // sum of ages

Join queries

SELECT u.name, o.total_price FROM users u JOIN orders o ON u.id = o.user_id; // inner join

SELECT u.name, o.total_price FROM users u LEFT JOIN orders o ON u.id = o.user_id; // left join

SELECT u.name, o.total_price FROM users u RIGHT JOIN orders o ON u.id = o.user_id; // right join

Creating indexes

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE);

CREATE UNIQUE INDEX idx_email ON users(email);

CREATE INDEX idx_name ON users(name);

CREATE INDEX idx_name_age ON users(name, age);

CREATE FULLTEXT INDEX idx_content ON articles(content);

Viewing and dropping indexes

SHOW INDEX FROM users;

DROP INDEX idx_name ON users;

ALTER TABLE users DROP INDEX idx_email;

Performance tips

EXPLAIN SELECT * FROM users WHERE name='mikechen'; // use index

SELECT * FROM users WHERE age=30; // may not use index if leftmost prefix not satisfied

SELECT * FROM users WHERE LEFT(name,3)='mike'; // index ineffective

SELECT * FROM users WHERE name LIKE 'mikechen%'; // index usable

Database backup

mysqldump -u user_name -p123456 database_name > outfile.sql // export entire database

mysqldump -u user_name -p database_name table_name > outfile.sql // export single table

mysqldump -u user_name -p -d --add-drop-table database_name > outfile.sql // export only schema

Promotional material

At the end of the article, the author offers a free 300,000‑word Alibaba architecture specialist collection and a comprehensive Java interview question set, inviting readers to add him on WeChat with the note “资料”.