MySQL database

database Foundation

Classification of databases

Relational databases use tables to store data and describe the relationships between tables through mechanisms such as primary keys and foreign keys. Common relational databases include MySQL, Oracle, DB2, and SQL Server.

Non-relational databases usually do not use a fixed two-dimensional table model. Common types include key-value databases, document databases, and column databases. Redis is a common key-value database.

“Non-relational” does not mean that there must be no relationship between the data, but rather that it is not organized using traditional relational models as its primary way.

basic concepts

A project can use one or more databases, and the same database can also serve multiple business modules. The specific division depends on the system architecture.

MySQL Installation and Catalog

When installing MySQL in a Windows environment, avoid using computer names and installation paths that contain special characters. The directories may differ for different versions, installation methods, and operating systems.

Common catalogs include:

• Program installation directory: Store MySQL Server, clients and related tools.
• Data directory: saves database files, logs and configuration data.
• Configuration file: Windows is commonly called my.ini, Linux is commonly called my.cnf.

When uninstalling an old version, you should first back up important data before stopping and deleting the corresponding service. Do not delete the data directory directly without confirming the purpose of the data.

SQL statement classification

SQL is a structured query language for manipulating relational databases.

MySQL common data types

integer type

Common integer types include TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. The type should be selected based on the business scope to avoid meaningfully using too large types.

Fixed point numbers and floating point numbers

DECIMAL should be given priority to data such as amounts that require accurate calculation.

salary decimal(9, 2)

DECIMAL(9, 2) represents a total of up to nine decimal digits, two of which are decimal digits.

FLOAT and DOUBLE are approximate numerical types, which are suitable for scenarios where floating point errors can be accepted, and are not suitable for directly storing the amount that requires accurate calculation.

string type

• CHAR(n): Fixed length string, suitable for data with basically fixed length.
• VARCHAR(n): Variable-length string, suitable for text with large changes in length.
• TEXT: Used to save longer text.

String literals usually use single quotes.

date and time types

• DATE: Storage date.
• TIME: Storage time.
• DATETIME: Save date and time.
• TIMESTAMP: Saving time stamps, range and time zone conversion behaviors are different from DATETIME.

binary type

BLOB is used to store binary data. Large pictures, audio, and videos are often better suited to being stored in object storage or file systems, and databases hold file addresses and metadata.

DDL data definition statement

create tables

basic syntax

create table 表名 (
    列名 数据类型 列属性,
    列名 数据类型 列属性
);

Table names and column names should clearly express the business meaning and maintain a unified naming style.

Create a student table

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11)
);

It is recommended to end each SQL with a semicolon.

column properties

default value

status tinyint not null default 0

Omit this column when inserting data, and the database uses the default value.

self-increasing attribute

AUTO_INCREMENT is often used for integer primary keys. When inserting data, omit this column or pass in NULL, and the database will generate the next sequence number.

create table student (
    sno int auto_increment,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11),
    classno int,
    constraint pk_student primary key (sno)
);

Self-added columns must be indexed, and a table can only have one self-added column. It is usually used with primary keys.

constraint

Constraints are used to ensure data integrity and consistency. Constraint violation data cannot be successfully written.

primary key constraint

The primary key is used to uniquely identify a record. The primary key value must be unique and cannot be NULL. A table can only have one primary key, but a primary key can contain multiple columns.

Column level writing:

create table student (
    sno int primary key,
    sname varchar(16),
    birthday date
);

Table-level writing:

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    constraint pk_student primary key (sno)
);

foreign key constraint

Foreign keys are used to ensure referential integrity. Non-null values in the foreign key column must be found among the candidate keys of the referenced table.

You should create the referenced table first and then create the referenced table.

drop table if exists student;
drop table if exists classes;

create table classes (
    classno int,
    classname varchar(32),
    constraint pk_classes primary key (classno)
);

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11),
    classno int,
    constraint pk_student primary key (sno),
    constraint fk_student_classno
        foreign key (classno)
        references classes(classno)
);

When inserting data, you should insert the referenced table first and then the referenced table. When deleting data, you should process the referenced record first, and then delete the referenced record.

Foreign keys can set reference actions:

constraint fk_student_classno
    foreign key (classno)
    references classes(classno)
    on delete set null
    on update cascade

CASCADE and SET NULL will automatically affect associated data, and business semantics should be confirmed before use. Logical deletes typically mark records with a status field rather than performing physical deletes.

unique constraint

Unique constraints restrict columns or combinations of columns from having duplicate values.

constraint uk_student_tel unique (tel)

In MySQL, a unique index usually allows multiple NULLs to appear. If the business requires that this column must have a value, NOT NULL also needs to be added.

non-empty constraint

sname varchar(16) not null

check constraints

constraint ck_student_birthday
    check (birthday < '2026-02-05')

MySQL 8 implements CHECK constraints. Earlier versions may parse but not execute, so you should pay attention to the database version.

Modify table structure

ALTER TABLE is used to modify table names, columns, and constraints.

modify the table name

alter table student rename to student2;
alter table student2 rename to student;

add columns

alter table student
add address varchar(255);

Modify column names and types

alter table student
change address addr varchar(255);

alter table student
modify addr varchar(32);

CHANGE can modify column names and types at the same time, while MODIFY only modifies column definitions.

delete columns

alter table student
drop column addr;

add constraints

alter table student
add constraint pk_student primary key (sno);

alter table student
add constraint fk_student
foreign key (classno)
references classes(classno);

alter table student
add constraint uk_student_tel unique (tel);

delete table

drop table if exists student;

Deleting a table will delete both the table structure and data, and the backup should be confirmed before operating.

DML data manipulation statements

insert data

basic syntax

insert into 表名 (列名, 列名)
values (值, 值);

The number, order and corresponding value of column names must match. It is recommended to clearly write column names to avoid changes in table structure affecting the code.

Insert a record

insert into student(sname, birthday)
values('Jack', '2026-01-01');

use the default value

insert into student
values(default, 'Rose', '2025-01-01', 1.70, '13312345678', null);

Insert multiple records

insert into student(sname, birthday)
values
    ('Rose1', '2025-01-01'),
    ('Rose2', '2025-01-02');

Don’t rely on dangerous or unclear implicit type conversions. For example, an invalid date string should not be inserted as a legal date.

modify data

update student
set height = 1.77,
    tel = '13312345678'
where sno = 3;

Fields can be updated based on original values:

update student
set height = height + 0.03
where sno = 3;

WHERE condition shall be confirmed before executing UPDATE. Omitting the condition modifies the entire table.

delete data

delete from student
where sno = 5;

Omitting the WHERE condition will delete all records in the table. DELETE deletes data but retains the table structure.

SELECT query statement

simple query

select *
from student;

It is recommended to query only the columns required by the business:

select sno, sname, birthday
from student;

The query results are called a result set.

Expression and null processing

NULL represents an unknown or missing value. After it is calculated with ordinary numerical values, the result is usually still NULL.

select ifnull(lowest_sal, 0) + 100,
       highest_sal + 500
from job_grades;

CONCAT() can be used to connect strings:

select concat('86', tel)
from student;

column aliases

select ifnull(lowest_sal, 0) + 100 as 最低工资,
       highest_sal + 500 as 最高工资
from job_grades;

AS can be omitted, but it is easier to read clearly.

Remove duplicate records

select distinct sname, birthday
from student;

DISTINCT will de-duplicate the combination of selected columns.

conditional expression

select case
           when lowest_sal < 3000 then '低工资'
           when lowest_sal between 3000 and 5000 then '中等工资'
           else '高工资'
       end as 工资等级,
       highest_sal
from job_grades;

conditional query

comparison condition

select *
from student
where sname = 'Rose';

select *
from student
where sname <> 'Rose';

select *
from student
where height >= 1.70;

logical conditions

select *
from student
where height >= 1.70
  and birthday < '2025-01-03';

select *
from student
where height >= 1.70
   or birthday > '2025-01-01';

AND has a higher priority than OR. Complex conditions should use parentheses to clarify the logic.

range condition

BETWEEN contains boundaries at both ends.

select *
from student
where height between 1.70 and 1.72;

fuzzy matching

% in LIKE represents zero or more arbitrary characters, and _ represents one arbitrary character.

select *
from student
where sname like 'J%';

select *
from student
where sname like 'J_c%';

select *
from student
where sname like '%a%';

collection condition

select *
from student
where sname in ('Rose', 'Jack', 'Tom');

null condition

IS NULL or IS NOT NULL must be used to determine null values.

select *
from student
where tel is null;

select *
from student
where tel is not null;

tel = null cannot be used to determine null values.

sort

select *
from student
order by height asc;

select *
from student
order by birthday desc,
         height desc;

ASC indicates ascending order, and DESC indicates descending order. ORDER BY can use column aliases in query results, but WHERE usually cannot use aliases defined by queries at the same level.

aggregate function

Common aggregation functions include SUM(), AVG(), MAX(), MIN() and COUNT().

select sum(salary),
       avg(ifnull(salary, 0)),
       max(salary),
       min(salary)
from employee;

Except for COUNT(*), most aggregation functions ignore NULL.

select count(*)
from employee;

COUNT(*) will count the number of rows in the result set, which is not recommended. Whether to use COUNT(*), COUNT(1) or COUNT(非空列) should be based on semantics and execution plans.

grouping query

GROUP BY

select department_id,
       avg(salary) as avg_salary,
       count(*) as employee_count
from employee
group by department_id;

Columns in the query list that do not participate in aggregation should appear in GROUP BY. When ONLY_FULL_GROUP_BY is enabled, MySQL will strictly check this rule.

HAVING

WHERE filters rows before grouping, and HAVING filters grouping results after grouping.

select department_id,
       avg(salary) as avg_salary
from employee
where department_id in (5001, 5002)
  and salary > 5000
group by department_id
having avg(salary) > 6000
order by avg_salary;

pagination query

MySQL uses LIMIT to limit the number of results.

select *
from employee
limit 5;

select *
from employee
limit 5, 5;

The offset in LIMIT offset, row_count starts from 0.

When recording pageSize entries on page pageNo and each page, the offset is:

(pageNo - 1) * pageSize

The above variable expressions cannot be directly written to ordinary static SQL. The application should calculate the offset first and then pass it in through parameters.

multi-table query

Cartesian product and connection condition

Multiple tables are written directly after FROM to form a Cartesian product. The correct combination should be retained by connection conditions.

select *
from employee e,
     departments d
where e.department_id = d.department_id;

Modern SQL recommends explicit JOIN syntax.

inner connection

select e.first_name,
       e.department_id,
       d.department_name
from employee e
inner join departments d
    on e.department_id = d.department_id
where e.department_id = 5001;

The connection relationship should be written in ON, and common filtering conditions for the final result are usually written in WHERE.

multi-table connection

Check the names of employees working in Beijing, department names and cities:

select e.first_name,
       d.department_name,
       loc.city
from employee e
inner join departments d
    on e.department_id = d.department_id
inner join locations loc
    on d.location_id = loc.location_id
where loc.city = '北京';

unequal connection

select e.first_name,
       e.salary,
       j.grade_level
from employee e
inner join job_grades j
    on e.salary >= j.lowest_sal
   and e.salary < j.highest_sal;

outer connection

The left outer connection retains all records in the left table. When there is no matching record in the right table, the right table column returns NULL.

select e.first_name,
       e.department_id,
       d.department_name
from employee e
left join departments d
    on e.department_id = d.department_id;

Check all programmers and their working cities:

select e.first_name,
       loc.city
from employee e
left join departments d
    on e.department_id = d.department_id
left join locations loc
    on d.location_id = loc.location_id
where e.job_id = '程序员';

It should be noted that if a non-null condition is set for the right table column in WHERE, the left connection effect may be close to that of the inner connection.

self-join

Self-connection is when the same table participates in connection with different aliases.

select e.first_name as employee_name,
       m.first_name as manager_name
from employee e
left join employee m
    on e.manager_id = m.employee_id;

Check the working city of employees and managers:

select e.first_name as employee_name,
       employee_location.city as employee_city,
       m.first_name as manager_name,
       manager_location.city as manager_city
from employee e
left join employee m
    on e.manager_id = m.employee_id
left join departments employee_department
    on e.department_id = employee_department.department_id
left join locations employee_location
    on employee_department.location_id = employee_location.location_id
left join departments manager_department
    on m.department_id = manager_department.department_id
left join locations manager_location
    on manager_department.location_id = manager_location.location_id;

fully connected

MySQL does not directly support FULL OUTER JOIN. Left connection, right connection, and UNION simulation can be used according to business needs, but duplicate rows must be handled.

multi-table update

MySQL supports the use of connections in update statements.

update employee e
left join departments d
    on e.department_id = d.department_id
left join locations loc
    on d.location_id = loc.location_id
set e.salary = e.salary + 5
where loc.city = '北京';

MySQL also supports multiple table deletions, but the syntax is different from single table DELETE. Care should be taken to confirm the scope of impact before implementation.

subquery

Subqueries are queries nested in other SQL. It can appear in WHERE, FROM, SELECT, etc.

single-line subquery

When a subquery returns only one value, ordinary comparison operators can be used.

select first_name, salary
from employee
where salary > (
    select salary
    from employee
    where first_name = 'Rose'
);

If a subquery returns multiple rows, the above writing will report an error.

multi-line subquery

Multi-line subqueries are often used with IN, ANY, ALL, or EXISTS.

IN

select *
from employee
where department_id in (
    select department_id
    from departments
    where manager_id = 100
);

EXISTS

EXISTS only determines whether a subquery returns at least one row.

select *
from employee e
where exists (
    select 1
    from departments d
    where d.department_id = e.department_id
      and d.manager_id = 100
);

EXISTS may not always be faster than IN. The optimizer may rewrite the query and should combine the index, data distribution, and execution plan judgment.

ANY

select first_name, salary
from employee
where salary > any (
    select salary
    from employee
    where department_id = 5001
);

Indicates that the salary is greater than at least one value in the subquery results.

ALL

select first_name, salary
from employee
where salary > all (
    select salary
    from employee
    where department_id = 5001
);

Indicates that the salary is greater than each value in the subquery results.

FROM subquery

Subqueries after FROM will form a derived table and must be set with aliases.

select max(department_avg_salary)
from (
    select avg(salary) as department_avg_salary
    from employee
    group by department_id
) department_salary;

The optimizer may merge or materialize derived tables, but cannot simply assume that it will always be “executed only once.”

result set union

UNION and UNION ALL

UNION and UNION ALL are used to merge the result sets vertically. The number of columns in each query must be the same, and the data types of the corresponding columns should be compatible.

UNION will remove weight, UNION ALL will not remove weight, which usually costs less.

select first_name as name
from employee
union
select city as name
from locations;

select first_name
from employee
union all
select first_name
from employee;

Add summary rows

select first_name,
       salary
from employee
union all
select '总计',
       sum(salary)
from employee;

Example of column and column structure conversion

The old table uses accounts as columns:

create table score (
    sname varchar(16),
    shuxue int,
    yuwen int,
    yingyu int
);

The new table uses accounts as rows:

create table score2 (
    sname varchar(16),
    kemu varchar(32),
    chengji int
);

You can use UNION ALL to convert data for specified students:

insert into score2(sname, kemu, chengji)
select sname, 'shuxue', shuxue
from score
where sname = 'Tom'
union all
select sname, 'yuwen', yuwen
from score
where sname = 'Tom'
union all
select sname, 'yingyu', yingyu
from score
where sname = 'Tom';

database backup

cold backup

Cold backup is copying data files after database services are stopped. It is simple to operate, but it will cause downtime and you cannot copy the data directory you are using at will.

hot backup

Hot backup is a backup completed while the database service is running. Common methods include logical export and using tools that support online backup.

Whether the backup is valid should be verified through a recovery drill. It is not enough to just create backup files without verifying the recovery process.

database transaction

transaction concept

A transaction is a set of operations that are performed as a logical unit. All operations in a transaction are either committed successfully or rolled back after failure.

Operations in local transactions are usually completed in the same database resource. Distributed transactions involve multiple databases or other resources and require additional coordination mechanisms.

ACID properties

SQL transaction control

MySQL turns on automatic submission by default. After turning off automatic commit, you can manually control transactions.

set autocommit = 0;

insert into departments(department_id, department_name)
values(5005, '行政部');

insert into employee(first_name, phone_number, department_id)
values('Jack', '13312346788', 5005);

commit;

You can roll back when an error occurs:

rollback;

savepoint

set autocommit = 0;

insert into departments(department_id, department_name)
values(5019, '行政部2');

savepoint after_department;

insert into employee(first_name, phone_number, department_id)
values('Jack', '13312346788', 5019);

rollback to after_department;
commit;

ROLLBACK TO will roll back operations after the savepoint and will not automatically end transactions.

Logging and transaction recovery

The following logs are often involved in InnoDB transactions:

• undo log: Save old version information for rollback and multiversion concurrency control.
• redo log: Log page modification for crash recovery and support for persistence.
• binlog: Logical logs for MySQL Server layer for replication and point-in-time recovery.

It cannot simply be understood as writing all data to the database file at once after submission. Data pages, log buffering, and disk swiping are coordinated by the database in accordance with mechanisms such as WAL. Successful commit usually requires that the relevant log meets configuration requirements, and the data page can be written back to disk later by the background thread.

Concurrent read issues

dirty reads

One transaction read data that another transaction had not yet committed. If the latter rolls back, the data read by the former is invalid.

Non-repeated reading

The same row is read multiple times in the same transaction with different results, usually because other transactions committed modifications.

Fantasy reading

If multiple queries are made under the same conditions in the same transaction, the set of records returned changes. The reason is usually that other transactions have inserted or deleted rows that meet the conditions.

transaction isolation level

The SQL standard defines four isolation levels:

The default isolation level for InnoDB is usually REPEATABLE READ. It handles consistent and current reads through MVCC and locking mechanisms. Avoiding phantom reading is not the same as “always locking the entire table.” InnoDB may use mechanisms such as record locks, gap locks, and critical locks.

Basic concept of lock

• Shared lock: Allow other transactions to continue reading, but restrict conflicting writes.
• Exclusive locks: Used to modify data and restrict conflicting access by other transactions to the same resource.
• Row-level lock: Locks index records or ranges, usually with high concurrency.
• Table-level lock: Lock the entire table, usually with low concurrency.

The actual scope of the lock is related to the index, SQL condition, and isolation level. The lack of suitable indexes may widen the scope of scanning and locking.

view

Views are virtual tables based on query definitions. Ordinary views usually do not save a separate piece of result data. When querying the view, the database processes its definition.

create view v_emp as
select e.employee_id,
       e.first_name,
       e.salary,
       e.department_id
from employee e;

select *
from v_emp;

Simple single table views can be updated when conditions are met:

update v_emp
set salary = 8100
where employee_id = 100;

Views containing aggregation, grouping, DISTINCT, federation, etc. are usually not directly updatable.

The role of views

• Encapsulate complex queries.
• Provide a stable data access structure externally.
• Restrict user access to only some rows and columns.
• Streamline the query code of the upper-level program.

Views cannot completely replace table structure version management. When incompatible changes occur to the underlying table, the view itself may also need to be modified.

WITH CHECK OPTION

create view v_beijing_employee as
select *
from employee
where department_id = 5001
with check option;

When adding or modifying data through this view, the results must still meet the view conditions.

stored procedure

A stored procedure can encapsulate a set of SQL and process control statements and receive input or return output through parameters.

drop procedure if exists proc_get_user_info;

delimiter //

create procedure proc_get_user_info(
    in p_user_id int,
    in p_include_address boolean,
    out p_result_code int
)
begin
    declare v_error int default 0;
    declare continue handler for sqlexception set v_error = 1;

    set p_result_code = 0;

    if p_include_address then
        select id,
               username,
               age,
               address,
               create_time
        from t_user
        where id = p_user_id;
    else
        select id,
               username,
               age,
               create_time
        from t_user
        where id = p_user_id;
    end if;

    if v_error = 1 then
        set p_result_code = 1;
        select '查询用户信息失败' as error_msg;
    end if;
end //

delimiter ;

Call example:

call proc_get_user_info(1, true, @result_code);
select @result_code;

custom function

Custom functions receive parameters and return a value, which is suitable for encapsulating reusable calculation logic.

drop function if exists get_salary_level;

delimiter //

create function get_salary_level(p_salary decimal(10, 2))
returns varchar(20)
deterministic
begin
    if p_salary < 3000 then
        return '低工资';
    elseif p_salary <= 5000 then
        return '中等工资';
    else
        return '高工资';
    end if;
end //

delimiter ;

select first_name,
       get_salary_level(salary)
from employee;

trigger

Triggers are automatically executed when a INSERT, UPDATE, or DELETE event occurs in the specified table.

The following example limits data to be written between 8

drop trigger if exists trg_employee_insert_time;

delimiter //

create trigger trg_employee_insert_time
before insert on employee
for each row
begin
    if current_time() < '08:00:00'
       or current_time() > '17:00:00' then
        signal sqlstate '45000'
            set message_text = '当前时间不允许新增员工数据';
    end if;
end //

delimiter ;

Triggers are implicitly executed, and excessive use increases the difficulty of troubleshooting. Scenarios suitable for database-level auditing or enforcement constraints should not put all business logic into triggers.