一、函数

1.字符串函数

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

select lower('HELLoworLD');

select upper('helloworld');

select lpad('828866',10,'0');

select rpad('828866',10,'0');

select trim('  helloworld  ');

#索引从1开始，截取world
select substring('helloworld',6,5);

2.数值函数

3.日期函数

-- 查询所有员工的入职天数，并根据入职天数倒数排序

select name,DATEDIFF(curdate(),entrydate) days from emps order by days desc;

4.流程控制函数

-- 给数学成绩分等级

select name,
    (case when math>=85 then '优秀' when math>=60 then '及格' else '不及格') '数学成绩',
    english,
    chinese
from score;
    
    

二、约束

1.约束的基本概念和演示

-- 根据需求创建一张新表
CREATE TABLE reader(
	id int PRIMARY KEY auto_increment COMMENT '主键',
	name varchar(10) not null UNIQUE COMMENT '姓名',
	age int COMMENT '年龄',
	STATUS char(1) DEFAULT '1' COMMENT '状态',
	gender char(1) COMMENT '性别'
) COMMENT '用户表';


ALTER TABLE emp add CONSTRAINT fk_emps_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

2.外键约束

外键约束的概念

外键约束（Foreign Key Constraint）是关系型数据库中用于维护表间引用完整性的机制。它确保一个表（子表）中的列值必须匹配另一个表（父表）中的主键或唯一键值，从而建立表间的关联关系。

外键约束的作用

• 数据完整性：防止子表插入无效的父表引用值。
• 级联操作：支持自动更新或删除关联数据（如级联删除、级联更新）。
• 关系建模：明确表间的逻辑关系（如“订单”表引用“客户”表）。

创建外键约束的语法

以 MySQL 为例：

CREATE TABLE 子表 (
    子表列 数据类型,
    FOREIGN KEY (子表列) REFERENCES 父表(父表主键列)
    [ON DELETE CASCADE|SET NULL|NO ACTION]
    [ON UPDATE CASCADE|SET NULL|NO ACTION]
);

• ON DELETE 和 ON UPDATE 指定级联行为（默认为 NO ACTION）。

外键约束的常见操作

添加外键约束（已存在的表）

ALTER TABLE 子表 
ADD CONSTRAINT 约束名称 
FOREIGN KEY (子表列) REFERENCES 父表(父表主键列);

删除外键约束

ALTER TABLE 子表 DROP FOREIGN KEY 约束名称;

外键约束的限制

• 父表被引用的列必须是主键或唯一键。
• 子表和父表的数据类型必须兼容。
• 某些数据库（如 SQLite）默认禁用外键，需手动开启（PRAGMA foreign_keys = ON）。

级联操作示例

• CASCADE：父表记录删除时，自动删除子表关联记录。
• SET NULL：父表记录删除时，子表外键列设为 NULL（需允许 NULL 值）。
• NO ACTION：拒绝破坏引用完整性的操作（默认行为）。

实际应用场景

• 订单与客户：订单表的 customer_id 外键引用客户表的主键。
• 学生与班级：学生表的 class_id 外键引用班级表的主键，并配置级联删除。

注意事项

• 外键可能影响性能（需维护索引和检查约束）。
• 设计时需权衡引用完整性与灵活性（如是否允许 NULL 值）。

三、多表查询

1.分类

2.内连接

内连接查询的是两张表的交集部分：

-- 示例：查询企业员工关联的部门名称

select e.name,d.name from emps e,dept d where e.dept_id=d.id;

3.外连接

-- 查询所有员工对应的部门信息（左外连接实现）

select e.*,d.name from emps e left outer join dept d  on e.dept_id=d.id;

4.自连接：看作两张表

-- 查询员工及其所属领导的名字
-- y代表员工，l代表领导
select y.name,l.name from emps y left outer join emps l on y.managerid=l.id;

select y.name,l.name from emps y,emps l on y.managerid=l.id;

5.联合查询：将多次查询的结果合并

6.子查询：嵌套select语句

（1）标量子查询：

（2）列子查询：子查询返回结果是一列

-- 查询销售部和市场部所有员工信息

select id from dept where name = '销售部' or name= '市场部';

select * from emps where dept_id in(2,4);

select * from emps where dept_id in(select id from dept where name = '销售部' or name= '市场部');

(3)行子查询：

-- 查找与张三薪资和领导都相同的员工信息

select * from emps where (salary,manegerid)=(select salary,managerid from emps where name='zhangsan');

(4)表子查询：

-- 查找与张三或李四薪资和领导都相同的员工信息

select * from emps where (salary,manegerid) in(
    select salary,managerid from emps 
        where name='zhangsan' or name='lisi'
);

四、事务

MySQL 事务的基本概念

事务是数据库操作的最小逻辑单元，保证一组操作要么全部成功，要么全部失败。MySQL 支持事务的存储引擎包括 InnoDB 和 NDB，而 MyISAM 不支持事务。

事务的四大特性（ACID）

• 原子性（Atomicity）：事务是不可分割的工作单位，要么全部执行，要么全部回滚。
• 一致性（Consistency）：事务执行前后，数据库从一个一致状态变到另一个一致状态。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的操作不应影响其他事务。
• 持久性（Durability）：事务一旦提交，对数据的修改是永久性的。

事务的隔离级别

MySQL 支持以下四种隔离级别：

1. READ UNCOMMITTED：允许读取未提交的数据，可能导致脏读、不可重复读和幻读。
2. READ COMMITTED：只能读取已提交的数据，避免脏读，但仍可能出现不可重复读和幻读。
3. REPEATABLE READ（MySQL 默认）：确保同一事务内多次读取同一数据的结果一致，避免脏读和不可重复读，但仍可能出现幻读。
4. SERIALIZABLE：最高隔离级别，完全串行化执行，避免所有并发问题，但性能最低。

事务的基本操作

MySQL 通过以下语句控制事务：

-- 开启事务
START TRANSACTION;  -- 或 BEGIN;

-- 执行 SQL 操作
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1 WHERE condition;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务的保存点（Savepoint）

保存点允许在事务中设置标记，以便部分回滚：

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 释放保存点
RELEASE SAVEPOINT savepoint_name;

事务的自动提交

MySQL 默认启用自动提交模式（autocommit=1），每条 SQL 语句作为一个独立事务执行。关闭自动提交后需手动控制事务：

-- 查看自动提交状态
SHOW VARIABLES LIKE 'autocommit';

-- 关闭自动提交
SET autocommit = 0;

事务的并发问题

• 脏读：读取到其他事务未提交的数据。
• 不可重复读：同一事务内多次读取同一数据，结果不同（因其他事务修改）。
• 幻读：同一事务内多次查询，结果集的行数不同（因其他事务插入或删除）。

事务的最佳实践

• 尽量缩短事务执行时间，减少锁竞争。
• 避免在事务中进行耗时操作（如网络请求）。
• 合理选择隔离级别，平衡一致性与性能。
• 使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 显式加锁时需谨慎。

示例：完整事务流程

-- 关闭自动提交
SET autocommit = 0;

-- 开启事务
START TRANSACTION;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 检查是否出错
IF (一切正常) THEN
    COMMIT;  -- 提交事务
ELSE
    ROLLBACK;  -- 回滚事务
END IF;

-- 恢复自动提交
SET autocommit = 1;

以上便是该文章全部内容，您的点赞关注收藏是小编持续更新的动力！