Master MySQL: Essential Database Operations, Table Management, and Index Optimization

1. Database Operations

View existing databases: SHOW DATABASES; Typical default databases include mysql (user privileges), test (sample data), and information_schema (metadata).

Create a new database with specific character set:

CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

CREATE DATABASE db_name DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

Select a database for subsequent operations: USE db_name; User management commands:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

DROP USER 'username'@'host';

RENAME USER 'old'@'host' TO 'new'@'host';

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

Query user information:

SELECT USER();

SELECT host, user FROM mysql.user;

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

SHOW GRANTS FOR 'nick'@'%';

Grant and revoke privileges (common privileges listed later):

GRANT SELECT, INSERT ON db.table TO 'user'@'host';

REVOKE SELECT ON db.table FROM 'user'@'host';

Grant remote access for a LAN host:

GRANT ALL ON *.* TO 'test'@'192.168.200.%' IDENTIFIED BY 'test123';

GRANT ALL ON *.* TO 'test'@'192.168.200.0/255.255.255.0' IDENTIFIED BY 'test123';

FLUSH PRIVILEGES;

mysql -utest -ptest123 -h 192.168.200.96

2. Table Operations

Create a table (basic syntax):

CREATE TABLE table_name (
    column1 TYPE NOT NULL DEFAULT 1 AUTO_INCREMENT,
    column2 TYPE,
    PRIMARY KEY (column1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Column attributes: NOT NULL – column cannot be null DEFAULT value – default value when omitted AUTO_INCREMENT – automatically incremented integer (only one per table, must be indexed) PRIMARY KEY – unique, non‑null identifier CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(col) – foreign key

Delete a table: DROP TABLE table_name; Empty a table (structure retained):

DELETE FROM table_name;

TRUNCATE TABLE table_name;

Alter table – add, drop, modify, or rename columns:

ALTER TABLE table_name ADD column_name TYPE;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name MODIFY COLUMN column_name TYPE;

ALTER TABLE table_name CHANGE old_name new_name TYPE;

Manage primary keys:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

ALTER TABLE table_name DROP PRIMARY KEY;

Manage foreign keys:

ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(parent_col);

ALTER TABLE child DROP FOREIGN KEY fk_name;

Change default values:

ALTER TABLE tbl ALTER column SET DEFAULT 1000;

ALTER TABLE tbl ALTER column DROP DEFAULT;

Rename a table:

RENAME TABLE old_name TO new_name;

3. Table Content Operations

Insert data:

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

INSERT INTO table_name (col1, col2) VALUES (val1, val2), (val3, val4);

INSERT INTO table_name (col1, col2) SELECT col1, col2 FROM other_table;

Delete data:

DELETE FROM table_name;

DELETE FROM table_name WHERE id=1;

Update data: UPDATE table_name SET name='nick' WHERE id>1; Select data:

SELECT * FROM table_name;

SELECT * FROM table_name WHERE id>1;

SELECT col1, col2 AS alias FROM table_name WHERE id>1;

Conditional queries, wildcards, limits, ordering, grouping, joins, and unions are demonstrated with examples such as:

SELECT * FROM table_name WHERE name LIKE '_n%';

SELECT * FROM table_name LIMIT 9,5;

SELECT * FROM table_name ORDER BY col1 DESC, col2 ASC;

SELECT col FROM table_name GROUP BY col;

SELECT * FROM a INNER JOIN b ON a.id=b.id;

SELECT * FROM a UNION SELECT * FROM b;

4. Miscellaneous Commands

Show table creation statement: SHOW CREATE TABLE table_name; Describe table structure: DESC table_name; Check whether a query uses an index:

EXPLAIN SELECT * FROM table_name WHERE name='nick'\G

5. Data Types

MySQL data types are grouped into numeric, string, and temporal categories. The article includes reference images for the full type list.

6. Indexes

Index overview: indexes act as a directory to speed up data lookup. Types include ordinary, unique, primary, composite, full‑text, index merge, and covering indexes.

Creating and dropping ordinary indexes:

CREATE INDEX idx_name ON table_name(column_name);

DROP INDEX idx_name ON table_name;

Viewing indexes: SHOW INDEX FROM table_name; Unique index:

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

DROP INDEX idx_name ON table_name;

Primary key index:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

ALTER TABLE table_name DROP PRIMARY KEY;

Composite index (multi‑column): CREATE INDEX idx_name ON table_name(col1, col2); Best‑practice tips for index usage include left‑most prefix rule, avoiding leading wildcards in LIKE, ensuring data type consistency, and preferring joins over sub‑queries.

Additional optimization advice: avoid SELECT *, prefer COUNT(1) or COUNT(col), use CHAR over VARCHAR when possible, place fixed‑length columns first, and keep indexes short.

7. Execution Plan

Use EXPLAIN to view query execution details (id, select_type, table, type, possible_keys, key, rows, Extra). This information helps identify optimization opportunities such as missing indexes or inefficient joins.