Comprehensive MySQL Guide: Database Operations, Table Management, Data Manipulation, Functions, Procedures, Triggers, and Permissions

1. Summary

This article mainly uses MySQL as the basis to deeply summarize common SQL statements. The content is divided into several parts, including database operations, table operations, data operations, operators, views, functions, stored procedures, triggers, sequences, and user permissions.

2. Database Operations

2.1 Create Database

Creating a database is simple; you can specify character set and collation directly.

CREATE DATABASE IF NOT EXISTS `库名` default charset utf8mb4 COLLATE utf8mb4_unicode_ci;

Example:

CREATE DATABASE IF NOT EXISTS test_db default charset utf8mb4 COLLATE utf8mb4_unicode_ci;

2.2 Rename Database

Renaming a database depends on the storage engine. For MyISAM you can move the directory; for InnoDB it is not allowed.

Method 1

RENAME database olddbname TO newdbname

This syntax was added in mysql‑5.1.7 and removed in mysql‑5.1.23; it is not recommended because it may cause data loss.

Method 2

The idea is to create a new database, export the old one with mysqldump , then import the dump into the new database.

# Backup db1 to db1.sql file
mysqldump -u root -p db1 > /usr/db1.sql;

# Import the backup into new db2
mysql -u root -p db2 < /root/db1.sql;

# Drop the old database (if really needed)
DROP DATABASE db1;

Method 3

Run a shell script directly.

#!/bin/bash
# Assume db1 is renamed to db2
# MyISAM can change files directly
mysql -uroot -p123456 -e 'create database if not exists db2'
list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='db1'")
for table in $list_table
 do
    mysql -uroot -p123456 -e "rename table db1.$table to db2.$table"
 done

In the script, p123456 is a short form for the password value 123456 .

2.3 Drop Database

Dropping a database is straightforward.

DROP DATABASE db1;

2.4 Use Database

USE db2;

3. Table Operations

3.1 Create Table

CREATE TABLE ts_user (
  id bigint(20) unsigned NOT NULL COMMENT '编码',
  name varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户姓名',
  mobile varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '手机号',
  create_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  KEY idx_create_time (create_time) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

3.2 Rename Table

ALTER TABLE ts_user RENAME TO ts_new_user;

3.3 Drop Table

DROP TABLE ts_new_user;

3.4 Column Operations

3.4.1 Show Columns

show full columns from ts_user;

3.4.2 Add Column

ALTER TABLE ts_user add column gender tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别，1，男；2，女' AFTER mobile;

3.4.3 Modify Column

ALTER TABLE ts_user modify column mobile varchar(30) NOT NULL DEFAULT '' COMMENT '用户手机号';

3.4.4 Drop Column

ALTER TABLE ts_user drop column gender;

3.5 Index Operations

3.5.1 Show Indexes

SHOW INDEXES FROM ts_user;

3.5.2 Add Normal Index

alter table ts_user add index idx_id (id);

3.5.3 Add Unique Index

alter table ts_user add unique idx_id (id);

3.5.4 Add Primary Key Index

alter table ts_user add primary key idx_id (id);

3.5.5 Add Composite Index

alter table ts_user add index idx_id_name (id,name);

3.5.6 Add Fulltext Index

alter table ts_user add fulltext idx_id (id);

3.5.7 Drop Index

# Drop normal index
alter table ts_user drop index idx_id;

# Drop primary key
alter table ts_user drop primary key;

4. Data Operations

4.1 Query Operations

4.1.1 Single Table Query

select * from ts_user;

or

select id, name from ts_user;

4.1.2 Keyword Queries

AND query

select id, name from ts_user where name = '张三'

OR query

select id, name from ts_user where name = '张三' or name = '李四'

IN query (max 1000 parameters)

select id, name from ts_user where name in ('张三','李四')

LIKE fuzzy query ( % is wildcard )

select id, name from ts_user where name like '张%'

IS NOT NULL query

select id, name from ts_user where name is not null

Range query

select id, name, age from ts_user where age >= 18 and age <= 30

select id, name, age from ts_user where age between 18 and 30

Multiple condition case

select 
name,
(case
 when scope >= 90 then '优'
 when 80 <= scope < 90 then '良'
 when 80 > scope >= 70 then '中'
 else '差'
end) as judge
from ts_user

4.1.3 Join Queries

LEFT JOIN

select tu.id, tu.name, tr.role_name
from ts_user tu
left join ts_role tr on tu.id = tr.user_id

RIGHT JOIN

select tu.id, tu.name, tr.role_name
from ts_user tu
right join ts_role tr on tu.id = tr.user_id

INNER JOIN

select tu.id, tu.name, tr.role_name
from ts_user tu
inner join ts_role tr on tu.id = tr.user_id

FULL JOIN

select tu.id, tu.name, tr.role_name
from ts_user tu
full join ts_role tr on tu.id = tr.user_id

4.1.4 Group By Queries

Count rows

select count(id) from ts_user;

MAX age

select max(age) from ts_user;

MIN age

select min(age) from ts_user;

SUM scores per student

select id, sum(score) from ts_user group by id;

AVG scores per student

select id, avg(score) from ts_user group by id;

HAVING AVG > 100

select id, avg(score) from ts_user group by id having avg(score) > 100;

4.2 Insert Operations

4.2.1 Single Column Insert

INSERT INTO ts_user(id, name) VALUES ('1', '张三');

4.2.2 Multiple Row Insert

INSERT INTO ts_user(id, name)
VALUES
('1','张三'),
('2','李四'),
('3','王五');

4.3 Update Operations

update ts_user set name = '李四1', age = '18' where id = '1'

4.4 Delete Operations

# Delete all rows
delete from ts_user;

# Delete with condition
delete from ts_user where id = '1';

5. Operators

MySQL mainly includes arithmetic, comparison, logical, and bitwise operators.

5.1 Arithmetic Operators

Operator

Description

Example

+

Addition

select 1+2;

result 3

-

Subtraction

select 1-2;

result -1

*

Multiplication

select 2*3;

result 6

/

Division

select 6/3;

result 2

%

Modulo

select 10%3;

result 1

Note: Division or modulo by zero returns NULL.

5.2 Comparison Operators

Used in WHERE clauses; true returns 1, false returns 0, unknown returns NULL.

Operator

Description

Example

=

Equal

select * from t_user where user_id = 1

!=

Not equal

select * from t_user where user_id != 1

>

Greater than

select * from t_user where user_id > 1

>=

Greater or equal

select * from t_user where user_id >= 1

<

Less than

select * from t_user where user_id < 1

<=

Less or equal

select * from t_user where user_id <= 1

BETWEEN AND

Between two values

select * from t_user where user_id between 1 and 100

NOT BETWEEN AND

Not between

select * from t_user where user_id not between 1 and 100

IN

In set

select * from t_user where user_id in ('1','2')

NOT IN

Not in set

select * from t_user where user_id not in ('1','2')

LIKE

Pattern match, % is wildcard

select * from t_user where user_name like '%张%'

IS NULL

Is null

select * from t_user where user_name is null

IS NOT NULL

Is not null

select * from t_user where user_name is not null

Note: IN clause has no strict limit on number of elements, but the whole statement length is limited to 4 MB.

5.3 Logical Operators

Return 1 for true, 0 for false.

Operator

Description

Example

NOT or !

Logical NOT

select not 1;

result 0

AND

Logical AND

select 2 and 0;

result 0

OR

Logical OR

select 2 or 0;

result 1

XOR

Logical XOR

select null or 1;

result 1

5.4 Bitwise Operators

Operate on binary representations.

Operator

Description

Example

&

Bitwise AND

select 3&5;

result 1

I

Bitwise OR

select 3|5;

result 7

^

Bitwise XOR

select 3^5;

result 6

~

Bitwise NOT

select ~18446744073709551612;

result 3

>>

Right shift

select 3>>1;

result 1

<<

Left shift

select 3<<1;

result 6

5.5 Operator Precedence

From highest to lowest: !, unary -, ~, ^, *, /, %, +, -, >>, <<, &, |, comparison operators, BETWEEN/CASE/WHEN/THEN/ELSE, NOT, &&/AND, XOR, ||/OR, assignment (=, :=). Use parentheses () to override.

6. Views

A view is a virtual table defined by a SELECT statement.

6.1 Create View

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED|LOCAL] CHECK OPTION];

Key options: OR REPLACE replaces existing view; ALGORITHM chooses how MySQL materializes the view.

6.2 Show View

show create view v_user;

After creation, a view can be queried like a table:

select * from v_user;

6.3 Drop View

DROP VIEW [IF EXISTS] view_name;

7. Functions

7.1 Common Functions

Function

Description

Example

char_length(s)

Length of string in characters

select char_length('hello') as content;

concat(s1,s2...sn)

Concatenate strings

select concat('hello ', 'world') as content;

format(x,n)

Format number x to n decimal places

select format(500.5634, 2) as content;

lower(s)

Convert to lower case

select lower('HELLO');

current_timestamp()

Current date and time

select current_timestamp();

DATE_FORMAT(date,format)

Format date/time

select DATE_FORMAT(current_timestamp(),'%Y-%m-%d %H:%i:%s');

IFNULL(v1,v2)

Return v1 if not NULL, else v2

select IFNULL(null,'hello word');

7.2 User‑Defined Function Syntax

7.2.1 Create Function

CREATE FUNCTION fn_name(func_parameter[,...])
RETURNS type
[characteristic...]
routine_body

7.2.2 Alter Function

ALTER FUNCTION fn_name [characteristic...];

7.2.3 Drop Function

DROP FUNCTION [IF EXISTS] fn_name;

7.2.4 Show Function

SHOW FUNCTION STATUS [LIKE 'pattern'];

7.2.5 Show Create Function

SHOW CREATE FUNCTION fn_name;

7.3 Example Operations

7.3.1 Create Example Table

CREATE TABLE `t_user` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',
  `user_name` varchar(5) DEFAULT NULL COMMENT '用户名',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

7.3.2 Insert Data

INSERT INTO t_user (user_name, age)
VALUES('张三',24),('李四',25),('王五',26),('赵六',27);

7.3.3 Create Function Example

-- Create a function
DELIMITER $$
CREATE FUNCTION user_function(v_id INT)
RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE userName VARCHAR(50);
  SELECT user_name INTO userName FROM t_user WHERE user_id = v_id;
  RETURN userName;
END$$
DELIMITER ;

7.3.4 Call Function

SELECT user_function(1); -- Returns the name of user with id 1

7.3.5 Drop Function

DROP FUNCTION IF EXISTS user_function;

8. Stored Procedures

8.1 Create Procedure Syntax

CREATE PROCEDURE procedure_name([[IN|OUT|INOUT] param_name data_type...])

Define delimiter before body, e.g., DELIMITER $$ or DELIMITER // .

8.2 Call Procedure

CALL user_procedure(1, @out);

Result stored in @out variable.

8.3 Drop Procedure

DROP PROCEDURE [IF EXISTS] proc_name;

8.4 Differences Between Procedures and Functions

Functions return a single value (or table) via RETURN; procedures return values via OUT parameters.

Functions can be used inside SELECT statements; procedures cannot.

Functions have more restrictions (no temporary tables, etc.); procedures are more flexible.

Procedures usually implement more complex logic.

9. Triggers

9.1 Create Trigger

CREATE [DEFINER = {user|CURRENT_USER}] TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body;

trigger_time is BEFORE or AFTER; trigger_event is INSERT, UPDATE, or DELETE.

Example: after inserting into t_user , insert current time into t_time :

CREATE TRIGGER trig1 AFTER INSERT
ON t_user FOR EACH ROW
INSERT INTO t_time VALUES(NOW());

Multiple statements require BEGIN…END and a custom delimiter:

DELIMITER //
CREATE TRIGGER trig2 AFTER INSERT
ON t_user FOR EACH ROW
BEGIN
  INSERT INTO t_time VALUES(NOW());
  INSERT INTO t_time VALUES(NOW());
END//
DELIMITER ;

9.2 Show Triggers

SHOW TRIGGERS;

select * from information_schema.triggers where trigger_name='trig1';

9.3 Drop Trigger

DROP TRIGGER [IF EXISTS] schema_name.trigger_name;

9.4 Summary

Use triggers sparingly because they fire per row and can significantly impact performance, especially on tables with frequent DML operations.

10. Sequences

10.1 Auto‑Increment

Standard auto‑increment works for a single table but can cause duplicate IDs in sharded environments.

10.2 Custom Sequence Table

CREATE TABLE `sequence` (
  `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字',
  `current_value` int(11) NOT NULL COMMENT '序列的当前值',
  `increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Functions currval , nextval , and setval manage the sequence.

INSERT INTO sequence VALUES('testSeq',0,1);
SELECT SETVAL('testSeq',10);
SELECT CURRVAL('testSeq');
SELECT NEXTVAL('testSeq');

Works for sharding tables but not for sharding databases.

10.3 UUID() Function

Generates a globally unique 128‑bit identifier.

select uuid();

10.4 UUID_SHORT() Function

Returns a 64‑bit unsigned integer; requires server_id in 0‑255 range and cannot be used with STATEMENT‑based replication.

select UUID_SHORT();

11. User Permissions

11.1 User Management

Show all users: select * from mysql.user;

Create user: CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Change password: SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

Drop user: DROP USER 'username'@'host';

Refresh privileges: FLUSH PRIVILEGES;

11.2 Permission Management

Show grants: SHOW GRANTS FOR 'username'@'host';

Grant privileges: GRANT SELECT,INSERT,UPDATE,DELETE ON db_name.* TO 'username'@'host';

Grant all on all databases: GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

Grant with grant option: add WITH GRANT OPTION to allow the user to grant privileges to others.

Remote Access

Allow a user from a specific IP: GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'192.168.1.100';

Enable remote access for all users by commenting out bind-address = 127.0.0.1 in my.cnf .

11.3 Root User Access Settings

One‑line command to set root password, grant all privileges, and allow remote access:

grant all privileges on *.* to 'root'@'%' identified by '123456';

To restrict root to localhost:

grant all privileges on *.* to 'root'@'localhost' identified by '123456';

Finally, apply changes:

FLUSH PRIVILEGES;

12. Conclusion

The article systematically reviews common MySQL syntax, which is also applicable to other relational databases such as Oracle, SQL Server, and PostgreSQL. It covers database and table operations, data manipulation, operators, views, functions, procedures, triggers, sequences, and user permissions. Readers are encouraged to point out any omissions.