# MySQL2511笔记 **Repository Path**: nieps/my-sql2511-notes ## Basic Information - **Project Name**: MySQL2511笔记 - **Description**: No description available - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2026-01-04 - **Last Updated**: 2026-01-12 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # MySQL笔记 ## 常用数据库 * mysql * sqlserver (不用) * oracle ( 知识点 分享 部分公司使用 ) * postgresql (安装 了解一下) * 达梦（国产数据库） 常用版本： * 5.7 * 8.x mysql 是 c/s结构 客户端/服务器 ## 数据库连接 ~~~shell mysql -u用户名 -h主机地址 -P端口号 -p #回车输入密码 ~~~ > 本机地址： 127.0.0.1 ---localhost ~~~shell mysql -uroot -p #登录成功后 设置密码 set password='1357'; #提交 commit; #退出 exit #用新账号重新登录 ~~~ ## 配置文件 * window my.ini * linux my.cnf ~~~shell #查看编码 show variables like '%char%'; ~~~ ## 命令 ![image-20260104111940405](assets/image-20260104111940405.png) ## 客户端 * navicat * dbeaver mysql数据库--user表： 重要的字段： * host 允许连接的主机 如果允许所有机器 连接 可以使用 % 注意：不要将root设置为% * user 用户名 * plugin 密码加密方式 * authentication_string 密码 ## 存储引擎 面试题：innodb myisam 两种的区别 ？ ![image-20260104114429215](assets/image-20260104114429215.png) ## 数据库操作 > 笔试： 要求使用DDL语句 定义表格 ，然后执行相关的查询 DDL: data define languag ~~~shell #简单 create database 数据库名称 ; #加上判断 如果数据库不存在 创建 create database if not exists 数据库名称 #在mysql中 通过help查询创建命令 (了解) help create database; #切换数据库 use 数据库名称; #显示数据库 show databases; #记住 #扩展 show databases like '%m%'; # %代表任意字符串 数据名包含m #查看创建数据库结构（不多 了解）、 show create database 数据名称; #删除数据库 drop database if exists 数据名称； ~~~ 总结： * 创建 create database * 查看 show * 删除 drop database * 修改 alter ## 注释 应用场景： * 生成的sql脚本 说明 * 定义存储过程 函数等 说明 ~~~sql #单行注释 #查询user -- 查询用户列表 select * from user; /* sql的多行注释 ..... */ ~~~ ## 数据类型 ### 数字类型 应用场景： * id 值 * 年龄 * 金额 #### 整数类型 * 有符号 (有负数) * 无符号 ![image-20260104151148470](assets/image-20260104151148470.png) 示例： * 年龄 tinyint 常用的整数类型： * tinyint * int (最常用的) * bigint #### 小数类型 ![image-20260104151708572](assets/image-20260104151708572.png) 示例： * 金额 money decimal(10,2) 99999999.99 ![image-20260104152109735](assets/image-20260104152109735.png) 系统实现删除逻辑： * 物理删除 真实删除 CRM 客户关系管理系统 ### 字符串类型 > 非二进制以字符形式存储 > > 二进制以字节形式存储 常用类型： * char 固定长度的字符串 如手机号11位 id使用uuid()产生的36位字符串 身份证号 存储时 长度不够 填补空白 ，查询时自动去掉 1个字节 0-255 * **varchar** 可变长度的字符串 2个字节 0-2的16次方-1 **优先选择** 直接存储的是字符数 * text 文本 存储的是字节 如长度为9 编码是utfmb3 只能存储3个汉字 ![image-20260104161108867](assets/image-20260104161108867.png) 了解： ![image-20260104162216005](assets/image-20260104162216005.png) ### 日期类型 ![image-20260104172207757](assets/image-20260104172207757.png) 应用： * Date 年月日 出生日期 会员到期日 * time 时分秒 考勤打卡 设计表通用字段 ： * 创建时间 不会变 指定系统默认时间 * 更新时间 数据类型： * datetime 存储的是日期 一旦保存过 不变了 * timestamp 时间戳 (从1970.1.1 0时0分0秒到现在过了去了多少**秒**) > 面试题： 上面两种类型的区别 ？ > > 1. 存储占用空间 不同 datetime 8byte timestamp 4byte > > 2. 存储范围： > > > * datetime 1000-9999 > > * timestamp 2038问题 （高版本已解决） | 特性 | DATETIME | TIMESTAMP | | :----------- | :----------------------------------------- | :------------------------------------------------ | | **存储范围** | 1000-01-01 00:00:00 ～ 9999-12-31 23:59:59 | 1970-01-01 00:00:01 ～ 2038-01-19 03:14:07 | | **时区处理** | **与时区无关** 原样存储，原样返回 | **与时区有关** 存储时转为 UTC，读取时转回当前时区 | | **存储空间** | 8 字节 | 4 字节 | MySQL 高版本中的“2038年问题”，情况有所改善，但**不能简单地说“已经彻底解决”**。 > - **理论支持：** MySQL 8.0.28 及之后的版本提供了一个解决方案，即 `TIMESTAMP` 类型支持将内部存储格式改为 **64 位整数**。 > - **依赖环境：** 在 64 位系统和 64 位 MySQL 版本上，理论上可以避免 2038 年问题。 > - **配置要求：** 这通常需要通过设置系统变量 `explicit_defaults_for_timestamp` 来启用，或者依赖于 MySQL 未来版本的默认配置改变。 > - **现实挑战：** 即使数据库支持了 64 位时间戳，还需要确保操作系统（OS）、C 运行库以及周边的应用中间件、ORM 框架等组件都适配了 64 位时间处理，否则仍可能存在兼容性风险 获取系统时间： ![image-20260104175157361](assets/image-20260104175157361.png) ## DDL-表操作 ### 常用创建方式 (掌握) ~~~sql CREATE TABLE [IF NOT EXISTS] tbl_name ( 列名 data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] ) ~~~ > * data_type 数据类型 > > * [NOT NULL | NULL] 可选 的 not null 代表该列不允许为空 NULL允许为空 > > * [DEFAULT {literal | (expr)} ] default 代表默认值 > > > 两种写法: > > > > 1. literal 常量 如字段price 默认值为0.0 > > 2. (expr) 代表一个函数或表达式 如设置id类型为36位字符串，默认值 (**uuid()**) > > * [AUTO_INCREMENT] 可选 代表自增 一般用于整数类型的主键 > > * [UNIQUE [KEY]] 代表唯一 如用户名唯一 > > > 上面有两种写法： > > > > 1. ​ UNIQUE > > 2. ​ UNIQUE KEY > > * [[PRIMARY] KEY] 代表当前列是主键 > > > 用法： > > > > 1. ​ key > > 2. ​ primary key > > * [COMMENT '*string*'] 字段说明 示例： ~~~sql # 创建客户表 create table if not EXISTS customer( id int UNSIGNED AUTO_INCREMENT key comment 'id唯一标识', `name` VARCHAR(100) not null comment '客户名称', source VARCHAR(10) not null comment '客户来源', contact varchar(10) not null comment '联系人', mobile char(11) not null comment '联系电话', qq varchar(15) not null comment 'qq', createTime datetime DEFAULT CURRENT_TIMESTAMP comment '创建时间', updateTime datetime DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP comment '更新时间' ) ~~~ ### 单独声明主键 ~~~sql # 创建角色 create table role ( id int auto_increment comment 'id', roleName varchar(10) not null comment '角色名称', status bit DEFAULT 1 comment '状态 0 禁用 1 启用 ', PRIMARY KEY (id) ) ~~~ ### 根据查询结果创建表 （了解） 语法 ： ~~~sql CREATE TABLE [IF NOT EXISTS] tbl_name [AS] query_expression ~~~ > [AS] query_expression 根据查询结果转换为表 示例： ~~~sql create table tmp (id int auto_increment key) as select c.`name`,c.contact,c.mobile from customer c ~~~ ### 克隆表结构 ~~~sql #克隆客户表 create table tmp_customer like customer; ~~~ ### 修改表结构 #### 增加列 ~~~sql ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] ~~~ > * ADD 代表增加列 > * [COLUMN] 可选 代表列 > * col_name 要增加的列名 > * column_definition 新的列的定义 参照 创建表时列的定义 > * FIRST 将该 列增加到最前面 既第一列 > * AFTER col_name 将新列增加到col_name 列的后面 #### 删除列 ~~~sql DROP [COLUMN] col_name #删除指定列 ~~~ #### 修改字段 1. 修改列的名称及定义 ~~~sql CHANGE [COLUMN] old_col_name new_col_name column_definition[FIRST | AFTER col_name] ~~~ > * CHANGE 代表修改列 > * old_col_name 老的列名 > * new_col_name 新的列名 > * column_definition 新的列的定义 参照 创建表时列的定义 > * FIRST 将该 列增加到最前面 既第一列 > * AFTER col_name 将新列增加到col_name 列的后面 2. 只修改列的定义 ~~~sql MODIFY [COLUMN] col_name column_definition[FIRST | AFTER col_name] ~~~ > * MODIFY 代表的只修改列的定义 列的名称不变 > * col_name 要修改的列名 > * column_definition 新的列的定义 参照 创建表时列的定义 > * FIRST 将该 列增加到最前面 既第一列 > * AFTER col_name 将新列增加到col_name 列的后面 3. 修改字段名 其它不变 ~~~sql RENAME COLUMN old_col_name TO new_col_name ~~~ > * RENAME 重命名 > * COLUMN 代表表 不能省略 > * old_col_name 老的列名 > * TO 要改成的新的名称 > * new_col_name 新的列名 #### 增加主键约束 ~~~sql ADD [CONSTRAINT [symbol]] PRIMARY KEY (key_part,...) ~~~ > * ADD 增加 > * [CONSTRAINT [symbol]] CONSTRAINT 约束 symbol约束名称 可以省略 > * PRIMARY KEY 主键约束标识 > * (key_part,...) 主键字段 可以是一个或多个 #### 删除主键约束 ~~~sql DROP PRIMARY KEY ~~~ #### 增加唯一约束 ~~~sql ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] (key_part,...) ~~~ 示例： ~~~sql # 在客户表中增加一个逻辑字段 isdel 默认增加到最后一列 alter table customer add isdel bit default 0 comment '是否删除 0未删除 1已删除'; # 删除isdel列表 alter table customer drop isdel; # 在qq字段后面增加isdel alter table customer add isdel bit default 0 comment '是否删除 0未删除 1已删除' after qq ; #将客户表中的name 改为company alter table customer CHANGE name company varchar(30) comment '公司名称'; #将客户表中source字段长度改为20 alter table customer MODIFY source varchar(20) comment '客户来源'; #删除客户表主键 #去掉自动增长 alter table customer MODIFY id int comment 'id'; alter table customer DROP PRIMARY KEY; #给客户表增加主键 alter table customer add PRIMARY key (id); #将contact 改为user alter TABLE customer rename column contact to user; ~~~ ### 删除表 ~~~sql DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... ~~~ > * drop 删除 > * [TEMPORARY] 代表临时表 如果创建表时指定 删除时也要指定 > * TABLE 关键字 > * [IF EXISTS] 如果表格存在 删除 > * tbl_name [, tbl_name] ... 可以一次删除 一个或多个表格 多个用逗号隔开 示例: ~~~sql drop table tmp ,tmp_customer; ~~~ ### 查看 ~~~sql # 查看表结构 show create table customer; # 查看 当前数据库中所有的表 show tables; #查看mysql数据库中的表 show tables from mysql; show tables in mysql; show tables from mysql like 'u%'; #查看表中字段 DESCRIBE customer; #简化 desc customer; ~~~ ### 表约束 * 主键 默认唯一 不允许为空 > 1. 一个表只有一个主键，建议主键是使用数字类型（比如自动增长、雪花算法） > > 2. 主键可以是一个或多个字段 > > > * 一个字段 单一主键 > > * 多个 复合主键 （多对多关系中使用） * 外键 > 1. 增加外键 > > ~~~sql > ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY (col_name,...) REFERENCES tbl_name (key_part,...) > [ON DELETE reference_option] > [ON UPDATE reference_option] > ~~~ > > > * [CONSTRAINT [symbol]] 指定外键约束 symbol 外键约束名称 > > * FOREIGN KEY 代表外键 > > * (col_name,...) 指定当前表中哪些字段是外键引用 > > * REFERENCES tbl_name (key_part,...) 外键字段 引用表tbl_name 中的字段 key_part > > 2. 删除外键 > > ~~~sql > DROP FOREIGN KEY fk_symbol #删除外键 fk_symbol外键约束名称 > ~~~ > > ~~~sql #创建班级表 create table class( id int auto_increment key, name varchar(10) ); # 班级--对学生 一个班级对应多个学生 学生---对班级 多对一 在多的一端增加一个字段引用一的一端 create TABLE student( id int auto_increment key, name varchar(10) comment '学生名称', cid int comment '班级id', FOREIGN key (cid) REFERENCES class(id) ) #查看表的创建结构 show create table student; show index from student; #删除学生表的外键 alter table student DROP FOREIGN KEY student_ibfk_1; #增加外键 alter table student add CONSTRAINT fk001 FOREIGN key (cid) REFERENCES class(id); ~~~ * 唯一约束（假如只设置了唯一 没有设置not null） unique 1. 允许为空 ,且允许有多条记录为空 2. 一旦有不为空的 ，不允许 重复 ~~~sql drop table employer; create table employer( id int auto_increment key, account varchar(30) UNIQUE , name varchar(10) ) ~~~ ## DML 数据操纵语言，通常批insert update delete ### insert 插入 #### 常用方法 ~~~sql INSERT [INTO] tbl_name [(col_name [, col_name] ...)] { {VALUES | VALUE} (value [, value] ...) [, (value [, value] ...)] ... } ~~~ > * INSERT 向表中插入数据 > * [INTO] 可选 习惯性的加上 > * tbl_name 要插入的表名 > * [(col_name [, col_name] ...)] 可选 ，如果不指定 ，说明要插入表tbl_name 中所有列，可以通过(col_name [, col_name] ...) 指定你要插入哪几列 > * {VALUES | VALUE} 二选一 给前面指定的列赋值 > * (value [, value] ...) 值 要求值的数量与前面指定的列的数量 一致且保证顺序一致 ~~~sql # 插入客户资料 #不指定列名 id键自增可以用0代替 insert into customer value (0,'用友2','网一','小明','154555','1000',0,null,now()); #指定列名 （常用） insert into customer(company,source,user,mobile,qq) values('万达','推荐','老王','1233','2222'); # 批量插入 （推荐使用 效率最高） insert into customer(company,source,user,mobile,qq) values('c1','','c1','1233','2222'),('c2','','c2','1233','2222'),('c3','','c3','1233','2222'); ~~~ #### set赋值 （只适合插入单条数据） ~~~sql INSERT [INTO] tbl_name SET col_name=value [,col_name=value ]... ~~~ ~~~sql insert into customer set company='百度',user='小李',mobile='150000',qq='5555'; ~~~ #### 插入记录时 数据来源于其它表 ~~~sql INSERT [INTO] tbl_name [(col_name [, col_name] ...)] SELECT ... ~~~ ~~~sql #将student表中的学生名称 做为user表中的name 存储 #思路一 查询student中的name ,得到一个集合，遍历集合获取每一个名称 ，然后插入user表中的name #思路二 根据查询结果插入user表 insert into user(name) select name from student; ~~~ ### update 更新数据 ~~~sql UPDATE table_name SET col_name = value [,col_name = value,...] WHERE where_condition ~~~ > * UPDATE table_name 修改指定表中的记录 > > * SET 设置更新的值 > > * col_name = value 更新指定列col_name 的值为value , 可以同时更新多列值 > > * WHERE where_condition 指明要更新哪些数据 > > > 注意：更新时一定要加上条件，否则将会更新表中所有的数据 ~~~sql #将客户中创建时间更新为当前系统时间 update customer set createTime=now(); # 将id=4的客户来源更改为 电话营销 update customer set source='电话营销' where id=4; #将公司为 c2 来源 地推 手机号158000 update customer set source='地推',mobile='158000' where company='c2'; ~~~ ### 删除 delete ~~~sql DELETE FROM tbl_name [[AS] tbl_alias] [WHERE where_condition] ~~~ > * DELETE FROM tbl_name 从指定表删除 数据 > * [[AS] tbl_alias] 表的别名 ，为了where语句中的条件 通过别名引用字段 > * [WHERE where_condition] 删除条件 如果不加 会删除所有数据 **加上条件** 面试题： delete 和truncate 区别 ？（或者问 使用delete删除后主键值是否从1开始） > delete 删除所有数据后，新数据id继续自动增长 > > truncate 表名，快速截断数据，清空，新数据id从1开始自动增长 > > ### 核心区别对比表 > > | 特性 | DELETE | TRUNCATE | > | :----------- | :------------------------- | :-------------------- | > | **语言类型** | DML (数据操作语言) | DDL (数据定义语言) | > | **回滚能力** | ✅ 支持回滚 (ROLLBACK) | ❌ 不可回滚 (隐式提交) | > | **执行速度** | 慢 (逐行删除并记录日志) | 快 (直接释放数据页) | > | **自增ID** | 保持删除前的最大值继续自增 | 重置为 1 | > | **空间释放** | 不释放空间 (仅标记删除) | 立即释放空间 | > | **触发器** | ✅ 会触发 DELETE 触发器 | ❌ 不会触发触发器 | ~~~sql # 删除id=7的客户 物理删除 delete from customer c where c.id=7; #逻辑删除id=6 update customer set isdel=1 where id=6; #删除user表中所有数据 delete from user; insert into user set name='admin'; #截断表 TRUNCATE user; ~~~ ## 作业 [原型参考](https://www.axureshop.com/ys/2331450) 1. 使用ddl语句 创建 客户 联系人 （客户与联系人一对多的关系 ） 2. 使用insert语句 插入客户和联系人数据，二者通过外键关联 3. 理清 客户、公海的关系 ，利用sql语句 模拟： 转移客户、领取客户（这两项如何实现，思考一下表的设计） 4. 预习select查询语句，尝试实现下面的效果 ![image-20260105174029269](assets/image-20260105174029269.png) > 1. 查询所有客户 > 2. 查询我的客户 > 3. 查询下属客户（有难度 思考一下） 5. 扩展 区分线索 与 客户 （自己查找资料） ![image-20260105174303692](assets/image-20260105174303692.png) 实现以下功能的表设计： * 跟进记录 * 跟进计划 * 附件 ## DQL ### 语法 数据查询语言，select ~~~sql SELECT [ DISTINCT ] select_expr [, select_expr] ... [FROM table_references] [WHERE where_condition] [GROUP BY [ {col_name | expr | position}]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC]] [LIMIT {[offset,] row_count | row_count OFFSET offset}] ~~~ > * SELECT 查询语句的关键字 > > * [ DISTINCT ] 去重，慎用 效率低 > > * select_expr 查询表达式： **表字段**、**函数**、变量、表达式 。。。 最终查询的结果 > > * [FROM table_references] 数据源 从哪里查询 table_references ：**表名** 其它查询结果集。。。 > > * [WHERE where_condition] 条件过滤 不加查询条件 意味着全表扫描（避免全表扫描） > > > where 后面跟着表达式，表达式的结果：true|false 为true 符合条件返回 > > * [GROUP BY [ {col_name | expr | position}]] 分组 > > * [HAVING where_condition] 在分组的结果上过滤 > > * [ORDER BY {col_name | expr | position} > [ASC | DESC]] 排序 > > * [LIMIT {[offset,] row_count | row_count OFFSET offset}] 分页 逻辑运算： * and 代表多个条件 同时满足 相当于java中的&& * or 代表或 两边的条件满足任何一个 相当于java中的 || 示例： ~~~sql # 最基本的select语句 select UUID(),now(),CURRENT_DATE,VERSION(),3*6,2+3; #简单查询 * 代表所有字段 开发中避免这样使用 select * from customer; #开发中建议 直接指明具体的列 SELECT id,company,source from customer; #给表指定别名 引用字段 select c.id,c.company,c.source from customer as c; # 查询客户来源有哪些 #去重 SELECT DISTINCT c.source from customer c ; #注意 distince 后面如果跟多个字段 要求给定字段都不允许重复 select DISTINCT c.id,c.company,c.source from customer as c; #基本条件 true 查询每条记录永远为真 返回 select c.id,c.company,c.source from customer as c where true ; select c.id,c.company,c.source from customer as c where 1=1 ; #查询Id=1 select c.id,c.company,c.source from customer as c where c.id=1 ; #查询id>3的客户 select c.id,c.company,c.source from customer as c where c.id>=3; #不等 != <> #查询客户来源 不是网一 的客户 select c.id,c.company,c.source from customer as c where c.source !='网一'; select c.id,c.company,c.source from customer as c where c.source <> '网一'; #根据创建时间 查询 昨天到现在所有的客户数据 select c.id,c.company,c.source from customer as c where c.createTime > '2026-01-05'; # 查询客户来源为空的 客户 select c.id,c.company,c.source from customer as c where c.source=null; #为null的不能使=null #为空使用 is null select c.id,c.company,c.source from customer as c where c.source is null; #查询来源不为空的 客户 select c.id,c.company,c.source from customer as c where c.source is not null; #查询来源为空 （包含空字符串） select c.id,c.company,c.source from customer as c where c.source is null or c.source=''; #查询有来源的客户 select c.id,c.company,c.source from customer as c where c.source is not null and c.source !=''; ~~~ ### 客户 销售人员 * 客户 > id 客户名称 创建人 创建时间 拥有人 * 员工 ---销售 --- > id 名称 所在部门 * 部门 （凡是涉及上下级的 可以通过pid 来关联 ） > id 部门 pid 要求： 1. 销售人员只能查看自己的客户 2. 销售经理查看部门下所有销售人员的客户 3. 销售总监 查看整个销售团队的客户 #### in查询 ~~~sql # 创建部门 create table dept ( id int auto_increment key, deptName varchar(20), parentId int default 0 ) #创建员工 create table employer( id int auto_increment key, name varchar(10), deptId int , FOREIGN key(deptId) REFERENCES dept(id) ) #创建客户 create table customer( id int auto_increment key, name varchar(20), createUserId int , createDate datetime default CURRENT_TIMESTAMP, ownerId int , FOREIGN key(createUserId) REFERENCES employer(id), FOREIGN key(ownerId) REFERENCES employer(id) ) # 张三查询自己的客户 select * from customer c where c.ownerId=1; # 假如李四是销售一部的经理 他可以查看销售一部下所有员工的客户 #in (id,....) #1. 先查询销售一部的部门id select d.id from dept d where d.deptName='销售一部'; #2. 根据部门id查询 所有员工id select e.id from employer e where e.deptId=5; #3. 根据员工id查询所有客户 select * from customer c where c.ownerId=1 or c.ownerId=2; #查询客户 拥有人在(1,2)范围内的 返回 select * from customer c where c.ownerId in (1,2); # 合并 子查询 （sql语句里嵌套sql ） select e.id from employer e where e.deptId=(select d.id from dept d where d.deptName='销售一部'); #二次合并 select * from customer c where c.ownerId in (select e.id from employer e where e.deptId=(select d.id from dept d where d.deptName='销售一部')); select * from customer c where c.ownerId in (select e.id from employer e where e.deptId=(select d.id from dept d where d.deptName='销售二部')); #查询不是销售一部下的客户 select * from customer c where c.ownerId not in (select e.id from employer e where e.deptId=(select d.id from dept d where d.deptName='销售一部')); # 假如小红是销售部总监 能够看到所有员工的客户 # 按照上面的思路 1. 根据销售部查询所有子部门 2. 查询子部门所有员工id 全并结果 ~~~ #### 模糊查询 ~~~sql #查询姓张的员工 select * from employer e where e.`name` like '张%'; #查询名字中含有 小 的员工 select * from employer e where e.`name` like '%小%'; # 查询姓李的 名字是2个字的员工 select * from employer e where e.`name` like '李_'; ~~~ #### 编码机制 部门编码： 1. 10 2. 11 > 父部门编码+子部门编码 ： > > 1101 > > 1102 3. 12 员工： 编码： 部门编码+序号 如: 10-001 10-002 ~~~sql # 查询张三自己的客户 select * from customer c where c.ownerNo like '1101-001'; # 李四是销售一部经理 查询该部门下所有客户 select * from customer c where c.ownerNo like '1101-%'; #小红 查询销售部所有客户 select * from customer c where c.ownerNo like '11%'; ~~~ ### 面试题 in 和exists二者的区别 ？ exists查询：**主查询与子查询交替执行**。对主查询的每一行，去执行一次子查询判断是否存在 ![image-20260106152216471](assets/image-20260106152216471.png) ~~~sql #先执行子查询 select * from customer where ownerId in (select id from employer where deptId=5); #使用exists 实现相同效果 后面的条件是当前客户的拥有人id必须在员工表 表员工所属部门是6 select * from customer c where EXISTS ( select e.id from employer e where e.id=c.ownerid and e.deptId=6) # 1 #查询部门不是销售二部的客户 select * from customer c where not EXISTS ( select e.id from employer e where e.id=c.ownerid and e.deptId=6) ~~~ ### 正则表达式 * \d 代表的是数字 0-9 1\d{10} * \D 代表非数字 * \w 代表字符 包含字母、数字 、_ * \W 非字符 * \s 空白 * \S 代表非空白 * [0-9] 代表任意数字 * [a-z] 代表任意小写字母 * [a-zA-Z0-9] * [abcde] 任选一个 * \. 代表任意字符 量词： * \+ 代表最少出现一次 \d+ * ? 最多出现一次 * \* 代表出现任意次 * {m} 代表出现m次 * {m,} 最 少出现m次 * {m,n} 最少m次最多n次 > 密码：要求6-12位字符 > > \w{6,12} 边界词: * ^ 代表以开始 * $ 以结尾 手机号: ^1[356789]\d{9}$ ~~~sql #查询联系人名称以 小开头 select * from customer where user REGEXP '小.+'; ~~~ ### 排序 ~~~sql #按创建时间升序排序 select * from customer order by createTime asc ; #按创建时间降序排序 select * from customer order by createTime desc ; # 创建时间降序排序 更新时间升序 select * from customer order by createTime desc,updateTime desc ; ~~~ ### 分页 > 延伸思考： 面试题 大 数据如何分页 #### 实现一 ~~~sql limit [offset,] row_count ~~~ > * limit 是分页关键字 > * [offset,] 可选 offset 代表起始位置 从第几行开始查询，默认是0 > * row_count 查询行数 ~~~sql select * from customer ; #查询 前3行 select * from customer LIMIT 3; #按创建时间倒序排序 取前2条 select * from customer order by createTime desc limit 2; #条件 每页2条 #查询第一页 select * from customer limit 0,2; #查询第二页 select * from customer limit 2,2; #三页 page代表当前页 # offset=(page-1)*row_count ; select * from customer limit 4,2; ~~~ #### 实现二（了解） ~~~sql row_count OFFSET offset #条件 每页2条 #查询第一页 select * from customer limit 2 OFFSET 0; #查询第二页 select * from customer limit 2 OFFSET 2; #三页 page代表当前页 # offset=(page-1)*row_count ; select * from customer limit 2 OFFSET 4; ~~~ ## 高级查询 ### 一对多 在多的一端增加一个字段引用一的一端。 常见： * 部门 ----员工 在员工表中增加部门id * 班级 ----学生 * 商品分类----商品 * 线索----联系人 * 线索----跟进记录 * 订单----订单明细(购买的商品、价格、数量) ### 多对一 和一对多相反，在多的一端增加一个字段引用一的一端。 ### 多对多 创建第三方表维护多对多的关系 ，第三方表存两张表的主键。 * 学生 --- 课程 > 分析： 站在学生角度： 一个学生可以选择多门课程 * 供应商---商家 * 用户----角色 * 角色----菜单 ~~~sql #创建学生表 create table student( id int UNSIGNED auto_increment key, name varchar(10) comment '学生名称', birthday date comment '出生日期', gender tinyint DEFAULT 1 comment '1男 2女 3 保密', mobile char(11) comment '手机号' ); #创建课程表 create table course( id int UNSIGNED auto_increment key, name varchar(10) not null comment '课程名称' ); # 创建学生课程表 create table student_course( sid int UNSIGNED comment '学生id', cid int UNSIGNED comment '课程id', PRIMARY key(sid,cid), FOREIGN key (sid) REFERENCES student(id), FOREIGN key (cid) REFERENCES course(id) ) ~~~ ### 一对一 会员表----商家 公民---身份证表 常用于拆表： * 商品基本表 -----商品详情 ~~~sql #创建商品基本表 spu sku create table spu( id int auto_increment key, name varchar(100) comment '商品名称', price decimal(10,2) comment '价格', pic varchar(200) comment '图片路径' ) #商品详情表 create table spu_detail( id int key, detail text, FOREIGN key(id) REFERENCES spu(id) ) ~~~ ### 内连接 ~~~sql tableA a {[INNER | CROSS] JOIN } tableB b { ON search_conditio | USING (join_column_list)} ~~~ > * [INNER | CROSS] inner 是内连接 cross 交叉连接（笛卡尔积）(了解) > > * 两表关联条件 > > > 1. ON 指定两个表之间通过什么字段关连的 常用的 > > 2. USING (join_column_list) 两个表关连字段名称一样时 ![img](assets/1766990121134-483073c0-9e1a-4101-b677-e4f2f96a8399.png) ~~~sql #内连接 select d.id,d.deptName,e.`name` from dept d JOIN employer e on d.id=e.deptId; #内连接 查学生 选修的课程 select s.id,s.`name`,c.`name` courseName from student s INNER JOIN student_course sc on s.id=sc.sid INNER JOIN course c on sc.cid=c.id ~~~ ~~~sql #查询商品及详情 select s.id,s.`name`,d.detail from spu s INNER JOIN spu_detail d on s.id=d.id; #换写法 useing select s.id,s.`name`,d.detail from spu s INNER JOIN spu_detail d USING(id); ~~~ ### 外连接 * 左外连接 （用的多） * 右外连接 ~~~sql tableA a {LEFT|RIGHT} [OUTER] JOIN tableB b { ON search_conditio | USING (join_column_list)} ~~~ > 左外连接： tableA a left join tableB b on ... tableA a left outer join tableB b on ... ~~~sql # 查询所有部门 及部门下的员工 #左外连接 select d.id,d.deptName,e.`name` from dept d LEFT JOIN employer e on d.id=e.deptId; #右外连接 select d.id,d.deptName,e.`name` from employer e RIGHT JOIN dept d on e.deptId=d.id ~~~ > 当 MySQL 的**内连接（INNER JOIN）**和**外连接（OUTER JOIN）**都能实现你的业务要求时，我的建议是：**优先选择内连接（INNER JOIN）**1。 > > 在大多数开发场景下，这是一个更安全、更高效的选择。为了帮你做出更明智的决策，我为你总结了具体的判断依据和对比分析： > > ### 🤔 核心建议：为什么优先选“内连接”？ > > 如果业务逻辑上你只关心“两张表都有对应数据”的情况，那么内连接是首选，原因如下： > > 1. **性能更优** ⚡ > 内连接通常比外连接执行速度更快。因为内连接只返回匹配的记录，数据量较小，计算量也相对较少。而外连接（特别是左/右连接）需要扫描并保留一张表的所有数据，对于大表来说，这意味着更多的资源消耗和更长的传输时间1。 > 2. **数据更“干净”** 🧹 > 内连接不会产生 `NULL` 值。如果你的业务不需要处理空值逻辑，内连接返回的结果集更简洁，避免了后续代码中需要频繁判断 `NULL` 的麻烦18。 > 3. **语义更明确** 🎯 > 使用内连接时，查询意图非常清晰：我只要两张表“交集”部分的数据。这种明确性让代码更易于维护和理解 ### 自连接 场景： * 类别 * 部门 * 省市区 * 行业 ~~~sql # 查询一二三级类别 #能不能用内连接 SELECT c1.id, c1.`name`, c2.id twoId, c2.`name` twoName, c3.id threeId, c3.`name` threeName FROM category c1 INNER JOIN category c2 ON c1.id = c2.parnetId INNER JOIN category c3 ON c2.id = c3.parnetId where c1.parnetId=0; #外连接 SELECT c1.id, c1.`name`, c2.id twoId, c2.`name` twoName, c3.id threeId, c3.`name` threeName FROM category c1 LEFT JOIN category c2 ON c1.id = c2.parnetId LEFT JOIN category c3 ON c2.id = c3.parnetId where c1.parnetId=0; ~~~ ### 子查询 ~~~sql #查询 销售一部 下的员工 select d.id from dept d where d.deptName = '销售一部' #做为条件 的标量子查询 select * from employer e where e.deptId = (select d.id from dept d where d.deptName = '销售一部'); #做为结果的标量子查询 返回部门 不用关联查询 select e.id,e.`name`,(select d.deptName from dept d where d.id=e.deptId) deptName from employer e; #查询销售部下所有的员工 列子查询 返回一列多行 select d.id from dept d where d.deptName like '销售%'; select * from employer e where e.deptId in (select d.id from dept d where d.deptName like '销售%'); select e.id,e.`name`,(select CONCAT(d.id,d.deptName) from dept d where d.id=e.deptId) deptName from employer e; #在from 后面做为结果集 查询 select c.id,c.`name`,sc.sid from course c INNER JOIN student_course sc on c.id=sc.cid; #关联学生(用的少) select s.id,s.`name`,tmp.`name` courseName from student s INNER JOIN (select c.id,c.`name`,sc.sid from course c INNER JOIN student_course sc on c.id=sc.cid) tmp on s.id=tmp.sid ~~~ ### 联合查询 合并查询的结果集 客户公海： * 条件 没有拥有人的 客户 * 离职员工的客户 * 其它条件 关键字： * union 合并结果 去重 慢 * union all 合并结果 不去重 快 要求合并结果查询返回的列数相同 ~~~sql #查询公海客户 select c.id,c.`name` from customer c where c.ownerId is null UNION all select c.id,c.`name` from customer c LEFT JOIN employer e on c.ownerId=e.id where e.status=2 or c.ownerId is null ~~~ ## 聚合函数 面试题：你开发时常用的数据库函数有哪些？ > 缺点：很多人只知道聚合函数 ~~~sql # 查询客户的总记录数 select count(*) from customer; select count(1) from customer; select count(id) from customer; #学生成绩表 create table student_score(score decimal(4,1) comment '成绩') select sc.sid,sc.cid from student_course sc; #查询学生及成绩 select s.id,s.`name`,c.`name` courseName,ss.score from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id; #查询张三的总成绩 select sum(ss.score) '总成绩' from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id where s.`name`='张三'; # 与聚合函数同时出现的其它字段，必须group by select sum(ss.score) '总成绩',s.`name` from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id GROUP BY s.`name`; #查询所有学生的总成绩 总成绩在230分以上的 select sum(ss.score) total,s.`name` from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id GROUP BY s.`name` HAVING total>=230; # 计算java的平均成绩 select avg(ss.score) from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id where c.`name`='Java'; # 求各科的平均成绩 select c.`name`,avg(ss.score) from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id GROUP BY c.`name` ; #查询各科最高分 select c.`name`,max(ss.score) from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id GROUP BY c.`name` ; #各科最低分 select c.`name`,min(ss.score) from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id GROUP BY c.`name` ; # 查询 各科状元及分数 ~~~ ~~~sql #查询学生及成绩 select s.id,s.`name`,c.`name` courseName,ss.score from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id; select avg(ss.score) from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id where c.`name`='java'; # #查询学生及成绩 及对应课程的平均成绩 该学生的总成绩 select s.id,s.`name`,c.`name` courseName,ss.score, avg(ss.score) over(PARTITION by c.`name`) '平均成绩',sum(ss.score) over(PARTITION by s.`name`) '总成绩' from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id; # 查询 各科状元及分数 select s.`name`,c.`name` courseName,max(ss.score) over(PARTITION by c.`name`) '分数' from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id; ~~~ ### sqlmode 它决定了 MySQL 应该支持哪些 SQL 语法，以及如何执行数据校验。简单来说，它控制着 MySQL 是做一个“严厉的教官”（严格模式，数据不对直接报错）还是一个“宽容的老好人”（宽松模式，自动帮你修正错误数据 ![image-20260107150018585](assets/image-20260107150018585.png) ## 窗口函数 ![image-20260107152918405](assets/image-20260107152918405.png) ~~~sql #查询学生及成绩 select s.id,s.`name`,c.`name` courseName,ss.score from student s LEFT JOIN student_score ss on s.id=ss.sid LEFT JOIN course c on ss.cid=c.id; #先排名 SELECT RANK() over ( PARTITION BY c.`name` ORDER BY ss.score DESC ) rk, s.`name`, c.`name` courseName, ss.score FROM student s LEFT JOIN student_score ss ON s.id = ss.sid LEFT JOIN course c ON ss.cid = c.id ; #查第各科第一名 select tmp.name,tmp.courseName,tmp.score from (SELECT RANK() over ( PARTITION BY c.`name` ORDER BY ss.score DESC ) rk, s.`name`, c.`name` courseName, ss.score FROM student s LEFT JOIN student_score ss ON s.id = ss.sid LEFT JOIN course c ON ss.cid = c.id ) tmp where tmp.rk=1; #取各科前2名 select tmp.rk,tmp.name,tmp.courseName,tmp.score from (SELECT RANK() over ( PARTITION BY c.`name` ORDER BY ss.score DESC ) rk, s.`name`, c.`name` courseName, ss.score FROM student s LEFT JOIN student_score ss ON s.id = ss.sid LEFT JOIN course c ON ss.cid = c.id ) tmp where tmp.rk<=3; #先排名 SELECT ROW_NUMBER() over ( PARTITION BY c.`name` ORDER BY ss.score DESC ) rk, s.`name`, c.`name` courseName, ss.score FROM student s LEFT JOIN student_score ss ON s.id = ss.sid LEFT JOIN course c ON ss.cid = c.id ; ~~~ ## 视图 虚拟表 ，简化sql语句 ~~~sql #创建排名视图 create or REPLACE view v_score_sort as SELECT RANK() over ( PARTITION BY c.`name` ORDER BY ss.score DESC ) rk, s.`name`, c.`name` courseName, ss.score FROM student s LEFT JOIN student_score ss ON s.id = ss.sid LEFT JOIN course c ON ss.cid = c.id ; #查询各科第一名 select * from v_score_sort v where v.rk=1; #修改视图 增加学生id alter view v_score_sort as SELECT RANK() over ( PARTITION BY c.`name` ORDER BY ss.score DESC ) rk, s.id, s.`name`, c.`name` courseName, ss.score FROM student s LEFT JOIN student_score ss ON s.id = ss.sid LEFT JOIN course c ON ss.cid = c.id ; #查看创建视图 show create view v_score_sort; #查看视图 show tables like 'v%'; #删除视图 drop view if EXISTS v_score_sort; ~~~ ## 索引 索引是一种数据结构，用于快速查找和更新的。 > 两种结构： > > * b+tree索引 > * hash索引 ~~~sql CREATE [UNIQUE] INDEX index_name [index_type] ON tbl_name (key_part,...) ~~~ > * [UNIQUE] 索引类型 不写默认是普通索引，选择是唯一 索引 > * index_name 创建的索引名称 > * [index_type] 索引的类型：btree hash ，默认是btree > * ON tbl_name (key_part,...) 在指定表的哪些字段中创建索引 ~~~sql #分析sql EXPLAIN select * from customer where id=1; #全表扫描 EXPLAIN select * from customer; # 根据名称查询客户 EXPLAIN select * from customer where name ='百度'; EXPLAIN select * from customer where name like '百度'; #建议在客户表的name字段上创建普通索引 create index customer_name_index on customer(name); #创建唯一索引 create UNIQUE index customer_name_index on customer(name); #查看索引 show index from customer; #删除索引 drop index customer_name_index on customer; #没有索引查询 EXPLAIN select * from test where a='sd' and b='sd' and c='3' and d='23'; #创建联合索引 create index customer_union_index on test(a,b,c,d); #索引a,b, c,d == a ab abc abcd EXPLAIN select * from test where a='sd'; EXPLAIN select * from test where b='sd' and c='3' and d='23' and a='sd'; # EXPLAIN select * from test where a='sd' and c='3' and d='23'; ~~~ ~~~sql #面试常问的 索引什么情况下会失效？ EXPLAIN select * from customer c where c.`name` like '百度'; #左模糊 失效 EXPLAIN select * from customer c where c.`name` like '%百度'; #右模糊 EXPLAIN select * from customer c where c.`name` like '百度%'; #函数 EXPLAIN select * from customer c where LENGTH(c.`name`)>0; EXPLAIN select * from customer c where c.`name` <> '百度'; explain select * from spu s JOIN spu_detail d on s.id=d.id where s.id=1; #删除外键 alter table employer drop FOREIGN key employer_ibfk_1; EXPLAIN select e.`name`,d.deptName from employer e JOIN dept d on e.deptId=d.id where e.id=1; #没有索引查询 EXPLAIN select a,b,c,d from test where a='sd' and b='3' order by a; ~~~ ## 存储过程 ~~~sql CREATE PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]]) proc_parameter: [ IN | OUT | INOUT ] param_name type ~~~ > 参数类型： > > * in 默认的类型 输入参数 > * out 输出参数（类似于返回值） > * inout 既是输入参数也是输出 参数 ~~~sql #创建存储过程 create PROCEDURE if not EXISTS p_test() begin #过程体 select * from category; select * from customer; end #调用过程 call p_test(); #根据id查询部门信息 CREATE PROCEDURE p_query_dept(in id int) begin select d.deptName from dept d where d.id=id; end; #调用 call p_query_dept(1); #根据id 返回部门名称 CREATE PROCEDURE p_query_dept2(in id int,out _name varchar(30)) begin #查询部门名称 将结果存储到_name中 select d.deptName into _name from dept d where d.id=id; end; #调用 #通过set定义变量 set @name=''; call p_query_dept2(1,@name); select @name; #创建过程 根据参数模糊查询 输入输出参数 CREATE PROCEDURE p_query_dept3(inout _name varchar(30)) begin #查询部门名称 将结果存储到_name中 select d.deptName into _name from dept d where d.deptName like CONCAT('%',_name,'%'); end; #调用 #通过set定义变量 set @name='技术部'; call p_query_dept3(@name); select @name; SELECT s.id, s.`name`, CASE s.gender WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '保密' END AS '性别' FROM student s; #查看过程 SHOW PROCEDURE STATUS like 'p_q%'; #删除过程 drop PROCEDURE p_test; ~~~ ~~~sql #知道 #声明局部变量 （过程和函数内部） DECLARE var_name [, var_name] ... type [DEFAULT value] ~~~ ~~~sql IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF ~~~ ### case ### 🔄 1. 行转列 (Row to Column) **场景**：将同一分组下不同行的数据，根据特定条件（如科目、月份）变成同一行的不同列。 **核心逻辑**：`CASE WHEN` + 聚合函数（MAX/SUM） + `GROUP BY`12。 假设我们有一张学生成绩表 `tb_score`，数据如下： | userid | subject | score | | :----- | :------ | :---- | | 001 | 语文 | 90 | | 001 | 数学 | 92 | | 002 | 语文 | 88 | 我们需要将其转换为： | userid | 语文 | 数学 | | :----- | :--- | :--- | | 001 | 90 | 92 | | 002 | 88 | 90 | #### 💻 SQL 实现 ```sql SELECT userid, MAX(CASE WHEN subject = '语文' THEN score ELSE 0 END) AS '语文', MAX(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS '数学', MAX(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS '英语' FROM tb_score GROUP BY userid; ``` - 原理说明 ： 1. **`CASE WHEN`**：为每一行生成临时的“伪列”。如果科目是语文，就把分数填入“语文”列，否则填 0 或 NULL。 2. **`MAX` / `SUM`**：由于 `GROUP BY userid` 会把同一个用户的多行合并成一行，我们需要聚合函数来从多行中取出那个非空的值。因为每个用户每科只有一条记录，用 `MAX`、`SUM` 或 `MIN` 效果一样。 3. **`GROUP BY`**：指定按照哪个字段进行合并（这里是按用户 ID 分组）。 > **💡 另一种写法**：你也可以用 `IF` 函数代替 `CASE WHEN`，语法更简洁： > `SUM(IF(subject='语文', score, 0)) AS '语文'`23 ------ ### ⬇️ 2. 列转行 (Column to Row) **场景**：将一行中多个列的数据拆分成多行显示。 **核心逻辑**：`UNION ALL` 或 `CROSS JOIN`13。 假设我们有行转列后的结果表 `student_scores_pivot`： | userid | 语文 | 数学 | 英语 | | :----- | :--- | :--- | :--- | | 001 | 90 | 92 | 80 | 我们需要将其还原为： | userid | subject | score | | :----- | :------ | :---- | | 001 | 语文 | 90 | | 001 | 数学 | 92 | #### 💻 SQL 实现 (推荐 UNION ALL) ```sql SELECT userid, '语文' AS subject, 语文 AS score FROM student_scores_pivot WHERE 语文 IS NOT NULL UNION ALL SELECT userid, '数学' AS subject, 数学 AS score FROM student_scores_pivot WHERE 数学 IS NOT NULL UNION ALL SELECT userid, '英语' AS subject, 英语 AS score FROM student_scores_pivot WHERE 英语 IS NOT NULL ORDER BY userid, subject; ``` - 原理说明 ： - 我们手动编写多条 `SELECT` 语句，每条语句提取一个科目的数据。 - 使用 `UNION ALL` 将这些结果纵向堆叠起来。 - `WHERE` 条件用于过滤掉空值，避免产生无意义的 0 分记录。 #### 💻 SQL 实现 (MySQL 8.0+ 使用 CROSS JOIN) 如果你使用的是 MySQL 8.0 及以上版本，还可以使用 `VALUES` 行构造器配合 `CROSS JOIN` 来实现，代码更紧凑： ```sql SELECT s.userid, c.subject, CASE c.subject WHEN '语文' THEN s.语文 WHEN '数学' THEN s.数学 WHEN '英语' THEN s.英语 END AS score FROM student_scores_pivot s CROSS JOIN (VALUES ROW('语文'), ROW('数学'), ROW('英语')) AS c(subject) HAVING score IS NOT NULL; ``` ------ ### 📊 总结对比表 为了方便记忆，我为你总结了这两种操作的区别 | 特性 | 行转列 (Pivot) | 列转行 (Unpivot) | | :----------- | :------------------------ | :----------------------- | | **目的** | 将多行数据合并为一行多列 | 将一行多列数据拆分为多行 | | **核心语法** | `CASE WHEN` + `GROUP BY` | `UNION ALL` | | **常用聚合** | `MAX()`, `SUM()`, `MIN()` | (通常不需要聚合) | | **适用场景** | 交叉报表、统计汇总 | 数据逆透视、清洗数据 | **建议**： - 如果你的列是固定的（比如固定的几个科目），直接使用上面的 `CASE WHEN` 写死列名即可，非常高效。 - 如果你的列是动态的（比如每个月新增一个产品线），MySQL 需要通过**存储过程**动态拼接 SQL 语句来实现，逻辑会复杂一些 ### 游标(了解) 在 MySQL 中，游标是一种**处理数据的方法**24。虽然 SQL 语言通常是面向集合的（即一条语句处理多行数据），但有时候我们需要像高级编程语言（如 Python、Java）一样，**逐行**处理查询结果。 ~~~sql #声明游标 DECLARE cursor_name CURSOR FOR select_statement ~~~ > * cursor_name 游标名称 > * CURSOR 关键字 > * select_statement 查询语句 要逐行遍历的结果集 游标使用步骤： 1. 声明游标 2. 打开游标 3. 遍历读取游标 4. 关闭游标 ~~~sql #创建过程 修复数据 更新等级 create PROCEDURE p_udpate_level() begin #声明游标是否结束 默认是没有的 DECLARE done INT DEFAULT FALSE; #声明变量 DECLARE _sid,_cid int ; DECLARE _score DECIMAL(4,1); DECLARE rs varchar(10); #声明游标 DECLARE cur_score CURSOR for select sid,cid,score from student_score; #如果游标中找不到数据 结束 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; #2. 打开 游标 open cur_score; #循环游标 beginLabel: LOOP #开始循环 #读取游标数据 一行一行读的 FETCH cur_score into _sid,_cid,_score; #条件判断 如果游标中没有数据 退出循环 if done then LEAVE beginLabel; end if; # 读取到数据 if _score >= 90 then #select '优秀' into rs; set rs='优秀'; elseif _score >=80 then set rs='良好'; elseif _score >=70 then set rs='中等'; elseif _score >=60 then set rs='及格'; else set rs='不及格'; end if; #结束条件判断 #更新当前行的level update student_score set level=rs where sid=_sid and cid=_cid; END LOOP; #结束循环 #4. 关闭游标 close cur_score; end; ~~~ while(了解) ~~~sql #创建过程 修复数据 更新等级 create PROCEDURE p_udpate_level2() begin #声明游标是否结束 默认是没有的 DECLARE done INT DEFAULT FALSE; #声明变量 DECLARE _sid,_cid int ; DECLARE _score DECIMAL(4,1); DECLARE rs varchar(10); #声明游标 DECLARE cur_score CURSOR for select sid,cid,score from student_score; #如果游标中找不到数据 结束 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; #2. 打开 游标 open cur_score; #循环游标 while done=false do #开始循环 #读取游标数据 一行一行读的 FETCH cur_score into _sid,_cid,_score; # 读取到数据 if _score >= 90 then #select '优秀' into rs; set rs='优秀'; elseif _score >=80 then set rs='良好'; elseif _score >=70 then set rs='中等'; elseif _score >=60 then set rs='及格'; else set rs='不及格'; end if; #结束条件判断 #更新当前行的level update student_score set level=rs where sid=_sid and cid=_cid; END while; #结束循环 #4. 关闭游标 close cur_score; end; ~~~ ## 函数 ~~~sql CREATE FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]]) RETURNS type func_parameter: param_name type ~~~ > 函数是有返回值的 > > * RETURNS type 指定返回类型 ~~~sql #根据id返回部门名称 CREATE FUNCTION f_getDeptName ( id INT ) RETURNS VARCHAR ( 30 ) BEGIN DECLARE _name VARCHAR ( 30 ); SELECT d.deptName INTO _name FROM dept d WHERE d.id = id;-- 返回值 RETURN _name; END; #查看函数 show FUNCTION status; #删除函数 drop FUNCTION f_getDeptName; ~~~ ## 内置函数 ### group_concat ~~~sql GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val]) ~~~ > 将多行结果合并 为一列 > > * [DISTINCT] 允许去重 > * ORDER BY 排序 > * [SEPARATOR str_val]) 指定分隔符 ### 字符串拼接 ~~~sql # 将一行中多个字段值合并 concat select CONCAT(c.id,c.`name`,c.ownerId) from customer c #常见应用场景 模糊查询 select * from customer c where c.name like CONCAT('%','百','%'); #使用字符将给定的值 分隔开 select CONCAT_WS(':','公司名称是',c.`name`) from customer c; #将多行合并为一列 select c.`name` from customer c ; #默认值用,隔开 select GROUP_CONCAT(c.`name`) from customer c ; #去重 select GROUP_CONCAT(DISTINCT c.`name`) from customer c ; #将一级类别名称 拼接 select GROUP_CONCAT(c.`name` order by c.sort SEPARATOR '@') from category c where c.parnetId=0; #将类别id和名称 用，分隔 多个类别用@分隔 select GROUP_CONCAT(CONCAT_WS(',',c.id,c.`name`) SEPARATOR '@') from category c ; ~~~ ### 日期 date_add 单位 ![image-20260109110835315](assets/image-20260109110835315.png) 日期格式： ![image-20260109113049996](assets/image-20260109113049996.png) > 记住常用： > > * %Y yyyy 年 > * %y yy 年 > * %m 月 1-12 > * %d 天 1-31 > * %H 小时 0-23 > * %i 分钟 0-59 > * %s 秒 0-59 ~~~sql #提取日期 select date(now()),YEAR(now()),MONTH(now()),DAY(NOW()),WEEK(now(),1),WEEKDAY(now()); #日期增加 # 小明购买一个月会员 expr 增加值 unit 单位 select CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 MONTH); update member set expireDate=DATE_ADD(CURDATE(),INTERVAL 1 MONTH) where id=1; #张三购买1年会员 update member set expireDate=DATE_ADD(CURDATE(),INTERVAL 1 YEAR) where id=2; #统计最近一周的客户数据 select CURDATE(),DATE_ADD(CURDATE(),interval -1 WEEK),DATE_SUB(CURDATE(),interval 1 week); select * from customer c where c.createDate>=DATE_SUB(CURDATE(),interval 1 week); #统计最近半个月的客户信息 select CURDATE(),DATE_SUB(CURDATE(),INTERVAL 2 WEEK),DATE_SUB(CURDATE(),INTERVAL 14 DAY); #统计本周的客户 select DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) day); #出生日期 计算年龄 set @birth='2000-05-06'; select year(CURDATE())-SUBSTR(@birth from 1 for 4),year(@birth),year(CURDATE())-year(@birth); #查询小明会员还有多少天 select DATEDIFF(m.expireDate,CURDATE()) from member m where id=1; #查询未来7天会员到期的会员 select * from member m where DATEDIFF(m.expireDate,CURDATE()) BETWEEN 0 and 31; #指定时间单位 差 select TIMESTAMPDIFF(week,CURDATE(),m.expireDate) from member m; #日期格式 化 select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s'),DATE_FORMAT(now(),'%m'); ~~~ ~~~sql select c.`name`,IF(c.account is null,'a',c.account) account ,ifnull(c.account,'no') from customer c ; #查询学生中 各性别学生的数量 select count(*),s.gender from student s GROUP BY s.gender; SELECT count( CASE WHEN s.gender = 1 THEN 1 ELSE null END ) '男', count( CASE WHEN s.gender = 2 THEN 1 ELSE null END ) '女', count( CASE WHEN s.gender = 3 THEN 1 ELSE null END ) '保密' FROM student s; -- 密码加密 update customer set PASSWORD=md5('123456'); -- 用户登录 BaiDu 123456 select * from customer where account='BaiDu' and password=md5('123456s'); select version(),database(),user(); select uuid(); select length('8405f785-ed2c-11f0-aef6-7c10c91e841d') ~~~ ## 事务 > 面试题：什么是事务？ > > 1. 事务的介绍 > 2. 事务的特性ACID > 3. 事务的隔离级别 > 4. 脏读、幻读、不可重复读 > > 扩展：你在开发中如何使用事务？ ### 读未提交 ~~~sql show VARIABLES like '%iso%'; #查询当前会话隔离级别 select @@session.TRANSACTION_isolation; #查询全局隔离级别 select @@global.TRANSACTION_isolation; #将当前会话设置为最低级别 读未提交 set session TRANSACTION ISOLATION LEVEL read UNCOMMITTED; #更改全局隔离级别 set global TRANSACTION ISOLATION LEVEL read UNCOMMITTED; #将事务改成手动提交 set @@autocommit=0; #查询事务是否自动提交 show variables like 'autocommit'; #事务A #开启事务 begin | start TRANSACTION begin ; insert into member set account='test'; #回滚事务 ROLLBACK; ~~~ ~~~sql #查询当前会话隔离级别 select @@session.TRANSACTION_isolation; #查询全局隔离级别 select @@global.TRANSACTION_isolation; #将当前会话设置为最低级别 读未提交 set session TRANSACTION ISOLATION LEVEL read UNCOMMITTED; #更改全局隔离级别 set global TRANSACTION ISOLATION LEVEL read UNCOMMITTED; #将事务改成手动提交 set @@autocommit=0; #查询事务是否自动提交 show variables like 'autocommit'; #事务B #开启事务 begin | start TRANSACTION begin ; #查看数据 select * from member; ROLLBACK; ~~~ ### 读已提交 ### 事务现象 * 脏读 A事务读取B事务未提交的数据 * 不可重复读 在一个事务内，同一条记录多次读取数据不一致。 * 幻读 在一个事务内,同样的条件 ，多次读取 记录数不一样 ## 锁 * 表级锁 ~~~sql LOCK {TABLE | TABLES} tbl_name { READ [LOCAL] | WRITE} #释放锁 UNLOCK {TABLE | TABLES} ~~~ * 行锁 ~~~sql select * from table ... for update ;# 写锁 select * from table ... for share ;# 共享锁 读锁 ~~~ 锁： 面试问： * 悲观锁 认为并发事务一定存在冲突 ，要求加锁 * 乐观锁 不认为会冲突，不会显示的加锁，只有在提交数据时，才会比较 （版本号或时间戳）版本号 > id=1 version=1 > > 事务A： 读取 1 ----1 > > > 事务A更新1： update 表 version=读取的版本号+1 where 数据库的版本号1=读取时的版本号1 允许更新--------更新后数据库版本号为2 > > 事务B： 读取 1-----1 > > > 事务B更新1： update 表 version=读取的版本号+1 where 数据库的版本号2=读取时的版本号1 更新失败 ## 作业 * mysql如何实现主从复制 （读写分离） * crm原型 根据原理定义表、数据操作 * 查询练习 没做完的接着做 * [MySQL经典练习50题](https://www.cnblogs.com/wanghuizhao/p/16986090.html) ## 权限管理 掌握账号的创建和基本授权 ![img](assets/1767146301214-3b62d570-b7ab-4dbc-9b65-d56742f39d27.png) [MySQL支持的权限](https://dev.mysql.com/doc/refman/8.4/en/grant.html#grant-privileges) ~~~sql # 创建用户 使用默认加密方式 caching_sha2_password create user 'crm'@'%' identified by '1357'; #修改密码 alter user 'crm'@'%' identified by 'crm'; #更改用户名 rename user 'crm' to 'test'; #指定加密方式 #create user 'crm2'@'%' identified WITH mysql_native_password by '1357'; #删除用户 create user 'crm2'@'%' identified by '1357'; drop user crm2; #授权 all 代表所有权限 将crm数据库所有权限 grant all on crm.* to 'crm'@'%'; #收回权限 revoke all on crm.* from test; ~~~ ## 备份恢复 ~~~sql mysqldump -hhost -u用户名 -P端口号 -p ~~~ > * -hhost 数据库服务器地址 默认是localhost > * -u用户名 账号 > * -P端口号 默认是3306 > * -p密码 ~~~sql #备份指定数据库表结构和数据 不含过程和函数 mysqldump -uroot -p crm > d:/crm.sql #备份课程表和数据 mysqldump -uroot -p crm course > d:/course.sql #备份多张表 mysqldump -uroot -p crm course student >d:/cs.sql #只导出表结构 -d 不含数据 mysqldump -uroot -d -p crm > d:/c.sql #导出含有过程和函数 -R mysqldump -uroot -d -R -p crm>d:/crm.sql #备份多个数据库（了解） mysqldump -uroot -p --databases crm test > d:/ct.sql #备份所有数据库（了解） mysqldump -uroot -p --all-databases > d:/all.sql #创建数据库并进入 执行sql脚本 \. *.sql source *.sql #恢复 crm2是目标数据库（sql脚本不含有创建数据库的） mysql -uroot -p crm2 < d:/crm.sql #备份含有创建数据库脚本 -B 含有创建数据库和use切换 数据库 mysqldump -uroot -B -p crm>d:/cc.sql #恢复 mysql -uroot -p < d:/cc.sql ~~~ > 重要选项: > > * -d 导出时不含有表格数据 > * -R 导出时包含过程和函数（默认没有） > * -B 含有创建数据库和use切换 数据库 作业： Timer定时类 使用Java程序调用mysqldump实现数据库的备份。