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 used my.iniLinux is common 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

Data such as amounts that require accurate calculation should be given priority DECIMAL。

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 It belongs to the approximate numerical type and is suitable for scenarios where floating point errors can be accepted. It is not suitable for directly saving the amount that needs 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: Date saved.
• TIME: Save time.
• DATETIME: Save the date and time.
• TIMESTAMP: Save timestamp, range and time zone conversion behavior versus DATETIME Different.

binary type

BLOB 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

This column is omitted when inserting data, and the database uses the default value.

self-increasing attribute

AUTO_INCREMENT Commonly used for integer primary keys. Omit this column when inserting data or pass in NULL, 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. Data that violates constraints cannot be written successfully.

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 Associated data will be automatically affected, 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 NULL。If the business requires that this column must have a value, you also need to add it NOT NULL。

non-empty constraint

sname varchar(16) not null

check constraints

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

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

Modify table structure

ALTER TABLE 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 You can modify both column names and types,MODIFY Only column definitions are modified.

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 value:

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

implementation UPDATE Confirm before WHERE Conditions. Omitting conditions modifies the entire table.

delete data

delete from student
where sno = 5;

omitted WHERE The condition deletes all records in the table.DELETE Delete data but retain 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 compared with ordinary numerical values, the result is usually still NULL。

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

Connection strings can be used CONCAT()：

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

column aliases

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

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

Remove duplicate records

select distinct sname, birthday
from student;

DISTINCT The combination of selected columns is deduplicated.

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

LIKE of % Represents zero or more arbitrary characters,_ Represents an 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

Judgment null value must be used IS NULL or IS NOT NULL。

select *
from student
where tel is null;

select *
from student
where tel is not null;

cannot use tel = null Determine null values.

sort

select *
from student
order by height asc;

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

ASC Represents ascending order,DESC Represents descending order.ORDER BY You can use column aliases in query results toWHERE Generally, you 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;

addition to COUNT(*) In addition, most aggregation functions ignore NULL。

select count(*)
from employee;

COUNT(*) It 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 the GROUP BY medium. enabled ONLY_FULL_GROUP_BY MySQL will strictly check this rule.

HAVING

WHERE Filter rows before grouping,HAVING Filter 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 Limit the number of results.

select *
from employee
limit 5;

select *
from employee
limit 5, 5;

LIMIT offset, row_count The offset from 0 Start.

the pageNo Page, page pageSize When recording, 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 in FROM Later, a Cartesian product will be formed. 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 Grammar.

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 , common filtering criteria for final results are usually written in WHERE medium.

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 = '程序员';

Need to pay attention, if WHERE Setting a non-null condition on the right table column in may make the left connection effect 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 it FULL OUTER JOIN。You can use left connection, right connection, and UNION Simulate, but duplicate rows must be processed.

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 deletion, but the syntax is not as good as single table deletion. DELETE Different. 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 Wait for position.

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 often correspond to IN、ANY、ALL or EXISTS Use together.

IN

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

EXISTS

EXISTS Only determine 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 Not necessarily better than IN Come on. 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

FROM Subsequent subqueries 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 with UNION ALL

UNION and UNION ALL Used to merge 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 the weight,UNION ALL Without de-duplication, the latter 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
);

may be used UNION ALL 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 Operations after the savepoint are rolled back and transactions are not automatically ended.

Logging and transaction recovery

The following logs are often involved in InnoDB transactions:

• undo log: Saves old version information for rollback and multi-version concurrency control.
• redo log: Record page modifications for crash recovery and support persistence.
• binlog: Logical log for the MySQL Server layer, used for replication and point-in-time recovery.

It cannot simply be understood that all data is written to the database file at one time after committing. 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 reads 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;

Including aggregation, grouping,DISTINCTViews of structures such as, union, 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 will occur on the specified table INSERT、UPDATE or DELETE Executed automatically when the event occurs.

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 will be executed implicitly, and excessive use will increase the difficulty of troubleshooting. Scenarios suitable for database-level auditing or mandatory constraints should not put all business logic into triggers.