MySql

数据库基础

什么是数据库

数据库（Database）是一种用于高效存储、管理和组织数据的系统。

它通过特定的数据结构将数据有序存储，并提供强大的查询、修改、删除和安全控制功能，确保数据的一致性、完整性和可访问性。

数据库是“数据的仓库”，但远不止于简单的存储，更强调对数据的高效管理与利用。

常见数据库类型

1. 关系型数据库（RDBMS, Relational Database Management System）

• 数据模型：基于数学中的“关系理论”，数据以二维表格（关系表）形式存储，表之间通过“键”（如主键、外键）关联。
• 特点：严格的结构化、支持复杂查询（如多表关联）、强一致性。
• 典型产品：MySQL、Oracle、PostgreSQL、SQL Server。
• 适用场景：需要精确数据关联和事务的场景（如银行交易、财务系统、电商订单管理）。

类似于 excel 存储数据的方式

1. 非关系型数据库（NoSQL, Not Only SQL）

• 数据模型：打破传统表结构，采用更灵活的模型（如键值对、文档、列族、图），适合存储非结构化或半结构化数据（如JSON文档、日志、社交关系）。
• 特点：高扩展性、高吞吐量、弱一致性（支持最终一致性），适合处理海量数据和高并发场景。
• 典型类型与产品：
  • 键值（Key-Value）：Redis（缓存）、Memcached；
  • 文档（Document）：MongoDB（存储JSON格式的用户资料）；
  • 列族（Column-Family）：HBase（大数据场景下的日志存储）；
  • 图（Graph）：Neo4j（社交关系网络分析）。
• 适用场景：实时推荐、日志分析、物联网设备数据、高并发的互联网应用（如短视频APP的用户行为记录）。

类似于 word/ppt 存储数据的方式

什么是SQL

SQL是结构化查询语言（Structured Query Language)的简称，是用于存取数据以及查询、更新和管理数据的编程语言；

命名规范

基本规则

见名知意的情况下，尽量遵守如下规则

1. 下划线命名法 (snake_case)：【推荐】
   1. 示例：user_account, order_details
   2. 特点：单词间用下划线连接，全小写
2. 驼峰命名法 (CamelCase)：
   1. 示例：userAccount, orderDetails
   2. 特点：单词首字母小写，无分隔符

建议规范

表(Table)命名

• 使用复数形式或单数形式（保持一致）
• 示例：users 或 user
• 关联表：user_role（多对多关系表）

列(Column)命名

• 使用单数形式
• 主键：id 或 表名_id（如 user_id）； 全表唯一的字段才能是主键；
• 外键：关联表名_id（如 department_id）
• 布尔字段：is_active, has_permission
• 日期字段：created_at, updated_at

索引(Index)命名

• 前缀：idx``_ 或 ix_
• 示例：idx_user_email, ix_order_date

视图(View)命名

• 前缀：v_ 或 vw_
• 示例：v_user_summary, vw_sales_report

存储过程(Stored Procedure)命名

• 前缀：sp_ 或 proc_
• 示例：sp_calculate_totals, proc_generate_report

函数(Function)命名

• 前缀：fn_ 或 func_
• 示例：fn_calculate_age, func_format_date

触发器(Trigger)命名

• 前缀：trg_ 或 t_
• 示例：trg_user_audit, t_order_update

SQL语言

DDL – Data Definition Language（数据定义语言）

语法

-- 创建数据库
create database database_name;

-- 创建表
create table table_name(
	column1 datatype constraint
);

-- 创建索引
create [unique] index index_name
on table_name(column);

-- 添加列
alter table table_name
add column column_name datatype;

-- 修改列
alter table table_name
modify column_name new_datatype

-- 删除列
alter table table_name
drop column_name;

-- 重命名
alter table table_name
rename to new_table_name;

-- 删除表
drop table table_name;
truncate [table] table_name;

-- 删除数据库
drop database database_name;

库操作

-- 创建数据库
create database company_db
character set utf8mb64
collate utf8mb64_unicode_ci;

-- 查看所有数据库
show database;

-- 使用数据库
use company_db;

-- 删除数据库
drop database if exists old_company_db;

表操作

创建表

-- 创建部门表
create table departments(
	dept_id int primary key auto_increment,
    dept_name varchar(50) not null,
    location varchar(100),
    budget decimal(12, 2) default 100000.00,
    established_date DATE,
    constraint chk_budget check (budget >= 0)
);
-- 创建员工表
create table employees(
	emp_id int primary key auto_increment,
    emp_name varchar(100) not null,
    email varchar(100) unique,
    salary decimal(10, 2),
    hire_date DATE default (current_date),
    dept_id int,
    manager_id int,
    constraint fk_dept foreign key (dept_id)
   		references department(dept_id),
    constraint fk_mamager foreign key (manager_id)
    	references employees(emp_id),
    constraint chk_salary check (salary >= 0)
);

修改表

-- 添加新列
alter table employees
add column phone varchar(20);

-- 修改列定义
alter table employees
modify column email varchar(150) null;

-- 重命名列
alter table employees
change column emp_name full_name varchar(20);

-- 添加约束
alter table employees
add constraint uk_phone unique (phone);

-- 删除列
alter table employees
drop column phone;

-- 重命名表
alter table employees
rename to staff;

删除表

-- 删除键
drop table if exits temp_table

索引操作

-- 创建索引
create index idx_employees_name on employees(emp_name);

-- 创建复合索引
create index idx_dept_salary on employees(dept_id, salary desc);

-- 创建唯一索引
create unique index idx_employees on employees(email);

-- 删除索引
drop index idx_employees on employees;

DML – Data Manipulation Language（数据操作语言）

-- 插入单行, 可以省略列名
insert into table_name(column1, column2, ...)
values (value1, value2, ...);

-- 插入多行
insert into table_name (column1, column2, ...)
values (value1, value2, ...)
values (value1, value2, ...)
values (value1, value2, ...)
...


-- 从其它表插入
insert into table_name (column1, column2, ...)
select column1, column2, ...
from another_table
where condition;


-- 更新表
update table_name
set column=value1, column2=value2, ...
from another_table
where condition;

-- 删除表: 条件删除
delete from table_name
where condition

-- 清空表: 只删除数据, 保留表结构
truncate table table_name;

-- 删除表: 删除表结构和数据
drop table table_name;

DCL – Data Control Language（数据控制语言）

语法

-- 授权
grant privilege1, privilege2, ...
on object_name
to user1, user2, ...
with grant option;

-- 移除权限
revoke privilege1, privilege2, ...
on object_name
to user1, user2, ...
with grant option;

SQL核心

DQL（Data Query Language）

语法结构

(9)  SELECT
        (10) DISTINCT column,
        (6)  AGG FUNC(column or expression), ...
(1)  FROM left tab1
        (3)  JOIN right tab2
        (2)  ON tab1.column = tab2.column
(4)  WHERE constraint_expression
(5)  GROUP BY column
(7)  WITH CUBE|ROLLUP
(8)  HAVING constraint_expression
(11) ORDER BY column ASC|DESC
(12) LIMIT count OFFSET count;
(13) [FOR UPDATE | LOCK IN SHARE MODE]]

1. FROM：首先执行的是FROM子句，它从指定的表中获取数据，并执行笛卡尔积（交叉连接），生成虚拟表VT1。
2. ON：接下来对虚拟表VT1应用ON筛选器，筛选出满足ON条件的行，生成虚拟表VT2。
3. JOIN：如果指定了OUTER JOIN，会将未匹配的行添加到虚拟表VT2中，生成虚拟表VT3。
4. WHERE：对虚拟表VT3应用WHERE筛选器，筛选出满足条件的行，生成虚拟表VT4。
5. GROUP BY：将虚拟表VT4中的行按GROUP BY子句中的列进行分组，生成虚拟表VT5。
6. AGG_FUNC：对虚拟表VT5计算聚合函数（如SUM、AVG等）。生成虚拟表 VT6
7. WITH CUBE|ROLLUP：对虚拟表 VT5应用ROLLUP或CUBE选项，生成虚拟表 VT6。
   1. 注意：MySQL只支持 WITH ROLLUP语法； 很少使用，一般忽略
8. HAVING：对虚拟表VT6应用HAVING筛选器，筛选出满足条件的行，生成虚拟表VT7。
9. SELECT：从虚拟表VT7中选出指定的列，生成虚拟表VT8。
10. DISTINCT：去除虚拟表VT8中的重复行，生成虚拟表VT9。
11. ORDER BY：将虚拟表VT9中的行按ORDER BY子句中的列进行排序，生成游标VC10。
12. LIMIT/OFFSET：从VC10中选择指定数量的行，生成虚拟表VT11，并返回给调用者
13. FOR UPDATE：锁定符合条件的行；
    1. FOR UPDATE 是排他锁（所有情况都有锁）
    2. LOCK IN SHARE MODE 是共享锁（读读模式相当于无锁，其他情况有锁）

单表查询

-- 基础查询
SELECT * FROM order_detail
WHERE sku_name='香蕉'

-- 字段别名；  注意每个字段之间用 逗号 分割； 别名只是显示效果，查询还是用原来名字
SELECT `pay_time` AS `支付时间`,
  buyer_name AS 买家,
  pay_amt AS `支付金额`
  FROM order_detail
WHERE sku_name='香蕉';

比较运算符

排序、分页

分页

#查询订单表的数据，分页显示，每页显示5条记录
#第1页
SELECT * FROM order_detail LIMIT 0,5;
#第2页
SELECT * FROM order_detail LIMIT 5,5;
#第3页
SELECT * FROM order_detail LIMIT 10,5;
#第4页
SELECT * FROM order_detail LIMIT 15,5;
#第5页
SELECT * FROM order_detail LIMIT 20,5;
#第6页
SELECT * FROM order_detail  LIMIT 25,5;

子查询

在SQL语句中，一句被括号包住的完整查询语句可以视为一个虚拟的表，这个表的内容是这句SQL的查询

结果，别人可以基于这张虚拟表继续查询，这种查询语句被称为一个子查询

子查询中的语句不需要写英文分号，

子查询内部如果进行了使用AS的字段重命名，则在外层查询的SELECT或者WHERE条件中，需要写重命名后的字段

-- from 后子查询
SELECT * FROM (
  SELECT order_id,
    `pay_time` AS `支付时间`,
    buyer_name AS `买家`,
    pay_amt AS `支付金额`,
    sku_name AS 商品名称
    FROM order_detail
  WHERE sku_name IN ('香蕉','椰子')
) AS tmp_order WHERE 支付时间 > '2025-01-30'

练习

查询出没有被张三购买过的商品，其商品编码、商品名称、商品价格和原产国

思路：

1. 明确要查询的表和字段。根据描述，我们要查询商品
2. 明确递进的查询条件。根据描述，我们先要去订单明细表中，找到张三购买的所有商品
3. 组合查询层次；递进条件为子查询内容，外层查询为最终的目标数据

select * from sku_info
where sku_code not in
(select sku_code form order_detail where buyer_name = "张三");

HAVING

-- 找出订单数超过5个的客户
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

关联查询（JOIN）

CREATE TABLE `t_dept` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
);
 
CREATE TABLE `t_emp` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT DEFAULT NULL,
 `deptId` INT DEFAULT NULL,
`empno` INT NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
);

INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

select * from t_emp, t_dept
-- 等同于
select * from t_emp
corss join t_dept

LEFT JOIN：左连接

select * from t_emp a
left join t_dept b on a.deptid = b.id;

RIGHT JOIN：右连接

SELECT b.*,a.*
FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id;

INNER JOIN：内连接

SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptid = b.id;

常用函数

略

窗口函数

row_number()
rank()
dense_rank()