MySQL Basic Operations Guide

I. MySQL Connection & Disconnection

1. Connect to Local Database

mysql -u username -p

• Press Enter, then input the password (e.g., root password) to log in.
• Example:

mysql -u root -p

2. Connect to Remote Database

mysql -h hostIP -u username -p portnumber

• Example:

mysql -h 192.168.1.100 -u admin -p 3306

3. Exit Database

exit;  -- or quit;

II. Database Operations

1. View All Databases

SHOW DATABASES;

2. Create Database

CREATE DATABASE database_name [CHARACTER SET charset] [COLLATE collation];

• Example (supports emojis with utf8mb4):

CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3. Switch Database

USE database_name;

• Example:

USE test_db;

4. Delete Database

DROP DATABASE database_name;

• Example:

DROP DATABASE test_db;

⚠️ Caution: Deletion is irreversible.

III. Table Operations

1. View All Tables in Current Database

SHOW TABLES;

2. Create Table

CREATE TABLE table_name (​
  column1 data_type [constraint],​
  column2 data_type [constraint],​
  ...​
  [table-level constraint]​
);

• Example (create a user table):

CREATE TABLE user (​
  id INT PRIMARY KEY AUTO_INCREMENT,  -- Primary key, auto-increment​
  username VARCHAR(50) NOT NULL UNIQUE,  -- Not null, unique​
  age INT DEFAULT 0,  -- Default value 0​
  create_time DATETIME​
);

3. View Table Structure

DESC table_name;  -- or DESCRIBE table_name;

• Example:

DESC user;

4. Modify Table

• Add column:

ALTER TABLE table_name ADD column_name data_type [constraint];

Example:

ALTER TABLE user ADD email VARCHAR(100);

• Modify column type/constraint:

ALTER TABLE table_name MODIFY column_name new_data_type [new_constraint];

Example:

ALTER TABLE user MODIFY age TINYINT DEFAULT 18;

• Delete column:

ALTER TABLE table_name DROP column_name;

Example:

ALTER TABLE user DROP email;

• Rename table:

ALTER TABLE old_table_name RENAME TO new_table_name;

Example:

ALTER TABLE user RENAME TO t_user;

5. Delete Table

DROP TABLE table_name;

• Example:

DROP TABLE t_user;

IV. Data CRUD Operations

1. Insert Data (INSERT)

• Full-column insertion:

INSERT INTO table_name VALUES (value1, value2, ...);

Example:

INSERT INTO user VALUES (1, 'zhangsan', 20, '2023-01-01 10:00:00');

• Specified-column insertion:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example:

INSERT INTO user (username, age) VALUES ('lisi', 25);

• Bulk insertion:

INSERT INTO table_name (column1, column2) VALUES  (value1, value2),  (value3, value4);

2. Query Data (SELECT)

• Basic query:

SELECT column1, column2, ... FROM table_name [WHERE condition];

Examples:

• Query all users:

SELECT * FROM user;  -- * represents all columns

• Query usernames where age > 20:

SELECT username FROM user WHERE age > 20;

• Distinct query:

SELECT DISTINCT column FROM table_name;

Example:

SELECT DISTINCT age FROM user;

• Sorted query:

SELECT column FROM table_name ORDER BY column [ASC|DESC];  -- ASC (default) = ascending; DESC = descending

Example (sort by age descending):

SELECT * FROM user ORDER BY age DESC;

• Paged query:

SELECT column FROM table_name LIMIT start_index, number_of_records;  -- Start index starts at 0

Example (query records 11-20):

SELECT * FROM user LIMIT 10, 10;

3. Update Data (UPDATE)

UPDATE table_name SET column1=value1, column2=value2, ... [WHERE condition];

• Example (change age of user with id=1 to 22):

UPDATE user SET age=22 WHERE id=1;

⚠️ Caution: Omitting WHERE modifies all data in the table.

4. Delete Data (DELETE)

DELETE FROM table_name [WHERE condition];

• Example (delete user with id=2):

DELETE FROM user WHERE id=2;

⚠️ Note:

• Omitting WHERE deletes all data but retains the table structure.
• To empty the table and reset auto-increment ID:

TRUNCATE TABLE table_name;

V. Advanced Queries

1. Conditional Query (WHERE Clause)

Common operators: =, !=, >, <, >=, <=, BETWEEN…AND…, IN(…), LIKE, IS NULL

Examples:

• Age between 18-30:

SELECT * FROM user WHERE age BETWEEN 18 AND 30;

• Username contains ‘zhang’:

SELECT * FROM user WHERE username LIKE '%zhang%';  -- % matches any character

• Age is empty:

SELECT * FROM user WHERE age IS NULL;

2. Multi-Condition Query (AND/OR)

SELECT * FROM user WHERE age > 20 AND username LIKE 'l%';

3. Aggregate Query (GROUP BY + Aggregate Functions)

Common functions: COUNT(), SUM(), AVG(), MAX(), MIN()

Examples:

• Count total users:

SELECT COUNT(*) FROM user;

• Group by age and count users per group:

SELECT age, COUNT(*) FROM user GROUP BY age;

• Filter group results (HAVING):

SELECT age, COUNT(*) FROM user GROUP BY age HAVING COUNT(*) > 2;

4. Multi-Table Join Query

• Inner Join (only matching records):

SELECT u.username, o.order_noFROM user uINNER JOIN order o ON u.id = o.user_id;

• Left Join (all left table records + right table matches):

SELECT u.username, o.order_noFROM user uLEFT JOIN order o ON u.id = o.user_id;

VI. Common Constraints

• Example (add foreign key with cascade delete):

CREATE TABLE order (  id INT PRIMARY KEY AUTO_INCREMENT,  order_no VARCHAR(20) UNIQUE,  user_id INT,  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE);

VII. Common Functions

1. String Functions

• CONCAT(str1, str2): Concatenates strings.

Example:

SELECT CONCAT(username, '_', age) FROM user;

• LENGTH(str): Returns string length.
• UPPER(str)/LOWER(str): Converts to uppercase/lowercase.

2. Date Functions

• NOW(): Gets current date and time.

Example:

INSERT INTO user (username, create_time) VALUES ('wangwu', NOW());

• DATE_FORMAT(date, format): Formats date.

Example:

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') FROM user;

3. Math Functions

• ROUND(num, n): Rounds to n decimal places.
• ABS(num): Returns absolute value.

VIII. Index Operations

1. Create Index (Improves Query Efficiency)

• Normal index:

CREATE INDEX idx_age ON user(age);

• Unique index:

CREATE UNIQUE INDEX idx_username ON user(username);

2. View Indexes

SHOW INDEX FROM table_name;

3. Delete Index

DROP INDEX index_name ON table_name;