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 “资料”.