MySQL Commands: Creating Indexes, Views, Triggers, and Common Operations

To create a unique index on an existing table, use CREATE UNIQUE INDEX index_name ON table_name (field_name [(index_length)] [ASC|DESC]) or ALTER TABLE table_name ADD UNIQUE INDEX index_name (field_name [(index_length)] [ASC|DESC]) .

To create a full‑text index when creating a table, include FULLTEXT INDEX|KEY [index_name] (field_name [(index_length)] [ASC|DESC]) in the table definition.

To add a full‑text index to an existing table, use CREATE FULLTEXT INDEX index_name ON table_name (field_name [(index_length)] [ASC|DESC]) or ALTER TABLE table_name ADD FULLTEXT INDEX|KEY index_name (field_name [(index_length)] [ASC|DESC]) .

Multi‑column indexes are defined similarly, listing each column and optional length, e.g., index_name (field1 [(len)] [ASC|DESC], …, fieldN [(len)] [ASC|DESC]) .

Delete an index with DROP INDEX index_name ON table_name .

Create a view using CREATE VIEW view_name AS select_query , view information can be shown with SHOW TABLE STATUS [FROM database_name] [LIKE 'pattern'] or DESC[RIBE] view_name , and a view can be dropped via DROP VIEW view_name[, view_name] or modified with CREATE OR REPLACE VIEW view_name AS select_query or ALTER VIEW view_name AS select_query .

Define a trigger with CREATE TRIGGER trigger_name BEFORE|AFTER DELETE|INSERT|UPDATE ON table_name FOR EACH ROW trigger_statement ; list triggers using SHOW TRIGGERS and drop them with DROP TRIGGER trigger_name .

Insert data using INSERT INTO table_name (field1, field2, …) VALUES (value1, value2, …) . Table structure can be inspected with commands such as DESC table_name , SHOW COLUMNS FROM table_name , DESCRIBE table_name , SHOW CREATE TABLE table_name , or by querying the information_schema database.

Pagination example: SELECT * FROM userdetail WHERE userid LIMIT 0,20 .

MySQL supports transactions only with storage engines like InnoDB; start a transaction with START TRANSACTION , execute statements, then COMMIT or ROLLBACK .

Key characteristics of MySQL include handling tens of millions of rows, compliance with common SQL standards, portability, simple installation, good performance, and relatively easy debugging, management, and optimization.

To resolve Chinese character encoding issues, set the character set during installation, adjust configuration files, and specify the charset when creating databases and tables.

Current time is obtained with NOW() and formatted with DATE_FORMAT(date, format) . Service status can be checked with service mysql status (Debian) or service mysqld status (RedHat), and the service can be started or stopped with service mysqld start / service mysqld stop . Login via shell using mysql -u root -p , list databases with SHOW DATABASES , select a database with USE database_name , list tables with SHOW TABLES , and describe a table with DESCRIBE table_name .