MySQL 基础笔记

MySQL 基础笔记

1. 数据库概述

1.1 数据存储概述

  • 存储数据的方法
    • 大脑来记住数据
    • 写在
    • 写在计算机的内存
    • 写成磁盘文件
    • ……

1.2 数据存储要求

  • 存储大量数据,方便检索和访问
  • 保持数据信息的一致、完整
  • 共享和安全
  • 通过组合分析,产生新的有用信息

1.3 数据存储和应用程序的关系

在这里插入图片描述

  • 应用程序
    • 作用:响应操作并显示结果、向数据库请求数据
    • 要求:美观、操作简单方便
  • 数据库
    • 作用:存储数据、检索数据、生成新的数据
    • 要求:统一、安全、性能

1.4 主流数据库产品

  • Oracle
    • Oracle公司的产品
    • 产品免费、服务收费
  • SQL Server
    • 针对不同用户群体的多个版本
    • 易用性好
  • MySQL
    • 开放源代码
    • 网站应用广泛
  • MongoDB
    • 非关系型数据库
    • 成长空间大

1.5 数据库的基本概念

  • 客观存在的、可以被描述的事物都是“实体”

在这里插入图片描述

1.6 关系型数据库的基本概念

  • 格式相同的实体
  • 数据库就是“数据”的“仓库”
  • 数据库由表、关系以及操作对象组成
  • 数据存放在表中
    在这里插入图片描述

2. 数据库的设计流程

2.1 软件开发生命周期中的数据库设计

  • 需求分析阶段:分析客户的业务和数据处理需求
  • 概要设计阶段:设计数据库的E-R模型图,确认需求信息的正确和完整
  • 详细设计阶段:应用三大范式审核数据库结构
  • 代码编写阶段:物理实现数据库,编码实现应用
  • 软件测试阶段:……
  • 安装部署:……

在这里插入图片描述

2.2 数据库设计的重要性

2.2.1 良好的数据库设计
  • 节省数据的存储空间
  • 能够保证数据的完整性
  • 方便进行数据库应用系统的开发
2.2.2 糟糕的数据库设计
  • 数据冗余、存储空间浪费
  • 内存空间浪费
  • 数据更新和插入的异常

2.3 数据库设计的步骤

  • 收集信息
    • 与该系统有关人员进行交流、座谈,充分了解用户需求,理解数据库需要完成的任务
  • 标识实体 (Entity)
    • 标识数据库要管理的关键对象或实体,实体一般是名词
  • 标识每个实体的属性(Attribute)
  • 标识实体之间的关系(Relationship)

2.4 数据库设计的ER图绘制

在这里插入图片描述

  • 映射基数
    在这里插入图片描述
    在这里插入图片描述

2.5 数据库设计的数据库模型图

  • 将各实体转换为对应的表,将各属性转换为各表对应的列
  • 标识每个表的主键列
  • 在表之间建立主外键,体现实体之间的映射关系

在这里插入图片描述

2.6 多对多数据库设计

在这里插入图片描述

3. 数据库的详细设计

3.1 数据库设计的规范化

  • 不合规范的表设计
    • 信息重复
    • 更新异常
    • 插入异常
      • 无法正确表示信息
    • 删除异常
      • 丢失有效信息

3.2 数据库设计的第一范式

  • 第一范式的目标是确保每列的原子性
    • 如果每列都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式(1NF)

3.3 数据库设计的第二范式

  • 第二范式要求每个表只描述一件事情

3.4 数据库设计的第三范式

  • 如果一个关系满足2NF,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式(3NF) (非主键字段之间也存在依赖关系

3.5 规范化设计的ER图

在这里插入图片描述

3.6 规范化设计的数据库模型图

在这里插入图片描述

3.7 规范化设计

  • 一张表描述了多件事情:
    在这里插入图片描述

  • 应用第二范式规范化:
    在这里插入图片描述

  • 应用第三范式规范化:
    在这里插入图片描述

3.8 规范化设计和性能

  • 为满足某种商业目标,数据库性能比规范化数据库更重要
    • 通过在给定的表中添加额外的字段以大量减少需要从中搜索信息所需的时间
    • 通过在给定的表中插入计算列(如成绩总分),以方便查询
  • 在数据规范化同时,要综合考虑数据库的性能

4. 数据的完整性

4.1 数据的完整性要求

  • 实体完整性
  • 引用完整性
  • 域完整性

4.2 数据的实体完整性

  • 标识列和主键

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

4.3 数据的引用完整性

在这里插入图片描述
在这里插入图片描述

4.4 数据的域完整性

  • 域完整性和域约束

在这里插入图片描述

4.5 数据完整性小结

在这里插入图片描述

5. 数据库开发环境的搭建

5.1 MySQL数据库的常见命令

  • 登录mysql服务器mysql -u root -p
  • 查看当前服务器上的所有数据库信息show databases;
  • 创建数据库:create database 库名;
  • 打开数据库:use 库名;
  • 查看数据库中所有数据表: show tables;
  • 创建数据表: CREATE TABLE table_name (column_name column_type);
  • 导入数据文件: mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql)
  • 查看数据: select * from 表;
  • 备份: mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql
  • 还原: mysql -hhostname -uusername -ppassword databasename < backupfile.sql
  • 退出: quit

6. SQL 概述

6.1 SQL 概念

  • 结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言

6.2 SQL 类型

  • DDL:数据定义语言
  • DQL:数据查询语言
  • DML:数据操作语言
  • 。。。

7. 基本查询语句

在这里插入图片描述

7.1 基本查询语法

  • 获取所有表中的数据
    • select 字段名 [*] from 表名
    • select empId,eName,....,deptId from emp;
  • 举例
## 查询每个员工的编号、姓名
select empId,eName from emp;

## 查询员工的职位
select job from emp;

7.2 去除重复数据

  • 重复数据的处理方式
  • 语法:
    • select distinct 列名 from 表名
    • distinct 去重
  • 举例
## 去重查询
select distinct job from emp;

7.3 数据的运算

  • 查询语句支持数据的运算
  • 举例
## 查询员工姓名、职位和年收入。年收入等于年薪加上每月100元伙食补贴。
select eName 姓名, job 职位, (salary+100)*12 年收入 from emp;

7.4 数据的组合

  • 数据的组合查询方式
    • concat函数用于把多个数据组合在一起输出显示
  • 语法
    • concat(参数1,参数2,参数3......)
    • select concat(empId,eName) from emp;
  • 举例
## 完成以下显示的查询结果, 年收入按年薪加上每月100元伙食补贴和每年2000元的车费补贴。
select concat('编号:',empId,'姓名:',eName) 基本信息, (salary+100)*12+2000 年收入 from emp;

7.5 条件查询

  • 语法:
    • Select 列名 from 表名 where 条件集合
    • 条件集合包含 and,or,not> ,< ,=,<>,between and 等组合符号
    • 为空判断采用 is null 和 is not null , not …. Is null
    • 范围查询采用 in,not in
    • 模糊查询采用 like,匹配单个字符’_’,匹配任意多个字符’%’
  • 举例
## 查询月工资大于 1500 元的员工信息
select * from emp where salary > 1500;

## 查询职位是销售员的员工信息
select * from emp where job = '销售员';

## 查询月工资在 15003000 之间的员工信息
select * from emp where salary > 1500 and salary < 3000;
select * from emp where salary between 1500 and 3000   (between...and...包含 15003000)

## 查询月工资在 15003000 之间,并且职位是经理的员工信息
select * from emp where salary > 1500 and salary < 3000 and job = '经理';

## 查询职位是经理或者分析师,并且月薪大于 2800 的员工信息,注意优先级问题
select * from emp where (job = '经理' or '分析师') and salary > 2800;
## in 范围查询,相当于 or 运算
select * from emp where job in ('经理','分析师') and salary > 2800

## 查询入职时间在 1981 年的员工信息
select * from emp where hireDate > '1980-12-31' and hireDate < '1982-1-1';

## 查询所有姓名中包含’云’的员工信息
select * from emp where eName like '%云%'

## 查询没有奖金的员工信息,为空比较不能用等于
select * from emp where comm is null

## 查询奖金不为空的员工信息
select * from emp where comm is not null
select * from emp where not comm is null 

7.6 排序查询

  • 语法
    • Order by 列名集合 asc(desc)
  • 举例
## 查询员工信息,以工资从高到低排列;工资相同时,以入职日期从早到晚排列显示
select * from emp order by salary,hireDate

## 查询员工信息,以工资从高到低排列;工资相同时,以入职日期从早到晚排列显示
select * from emp order by salary desc,hireDate desc   (倒序)

8. 分组统计查询语句

8.1 统计函数

  • 统计函数的类型
    • count:统计个数
    • sum:获取指定列的数据总和
    • avg:获取指定列的数据平均值
    • max:获取指定列的数据最大值
    • min:获取指定列的数据最小值
  • 举例
## 查询显示公司的全部员工数,每月的工资总额以及平均工资
select count(empId) 总人数, sum(empId) 月工资总金额, avg(salary) 平均工资, max(salary) 最高薪资, min(salary) 最低薪资 from emp;

## 显示员工中最早入职的日期和最晚入职的日期
select min(hireDate) 最早入职日期, max(hireDate) 最晚入职日期 from emp;

## 查询当前拿佣金的人员总数
select count(empId) from emp where comm is not null;
select count(comm) from emp   (count函数会过滤掉null值的记录)

## 查询公司职位的总数
select count(distinct job) from emp;   (去重+统计)

8.2 分组函数

  • 使用场合
    • 当数据字段中存在重复的数据,分组才有意义
    • 分组函数经常和统计函数一起使用
  • 语法
    • Select 字段集合
    • from 表集合
    • where 条件集合(where不能使用统计函数进行条件筛选)
      • Group by 字段集合
      • having 条件集合(having是在分组统计基础上进行筛选)
      • Order by 字段集合
  • 举例
## 按部门编号分组,显示员工总数和平均工资
select count(empId) 部门员工总数, avg(salary) 部门平均工资 from emp group by deptId
select * from emp group by depId;    只适用于MySQL,其他数据库中会显示语法错误

## 按职位分组,求出每个职位的最高工资和最低工资
select job 职位, max(salary) 最高工资, min(salary) 最低工资 from emp GROUP BY job;

## 按职位分组,求出每个职位的员工人数,和工资总和,同时工资总和超过5000
select job, count(empId), sum(salary) from emp group by job having sum(salary) > 5000
  • 注意事项
    • 统计函数可以单独使用,或者组合使用,但是不要和普通列一起使用
    • 当存在分组函数时,统计函数和普通列可以一起使用
    • 分组函数存在时,select子句中只能出现分组的字段和统计函数
    • 分组函数允许多列
## 不合适语句
## 1. 普通列和统计函数一起使用
select eName, count(empId) from emp;  (x)

## 2. 有分组的情况下,普通列中出现非分组字段
select eName, job, max(salary), min(salary) from emp group by job  (x)

9. 多表查询语句

9.1 常见类型

  • 表连接
  • 子查询

9.2 表连接(SQL92标准)

  • From子句中放置多张数据表实现多表查询
  • 语法:
    • Select * From 表1,表2,….表n

9.3 表连接的基本原理

  • 多表查询的笛卡儿积
    • Select * from emp,dept

在这里插入图片描述

  • 去除笛卡儿积的办法 —— 关联查询

    • Select * from emp,dept where emp.deptno = dept.deptno
    • 注意添加表名前缀
    • 可以给数据表取别名
    • Select * from emp e,dept d where e.deptId = d.deptId
      在这里插入图片描述
  • 举例

## 查询显示每个员工的编号、姓名、工资、工资级别
select empId, eName, salary, gradeId from emp e, salgrade s where e.salary between s.losal and s.hisal

9.4 表连接(SQL99标准)

  • 语法

    • Select * From 表1 join 表2 on 表1.字段 = 表2.字段
  • 表连接的类型

    • 内连接
    • 左外连接
    • 右外连接
  • 表外连接的使用场合

    • 数据关联时,匹配不上造成的数据缺失
  • 举例

## 内连接
select empId, eName, d.deptId, dName, location from emp e join dept d on e.deptId = d.deptId

## 外连接(左外连接 -- 当数据不匹配的时候,确保 join 左边的表显示所有数据,右边的表的数据不匹配用null代替)
select empId, eName, d.deptId, dName, location from emp e left join dept d on e.deptId = d.deptId

## 外连接(右外连接 -- 当数据不匹配的时候,确保 join 右边的表显示所有数据,左边的表的数据不匹配用null代替)
select empId, eName, d.deptId, dName, location from emp e right join dept d on e.deptId = d.deptId

9.5 自表连接

  • 特殊的多表查询——自身关联查询
  • 举例
## 显示员工的姓名、编号、职位和领导的编号和姓名
## 内连接
select e2.empId, e2.eName, e2.job, e2.leader, e1.eName from emp e1,emp e2 where e1.emp e2 where e1.empId = e2.leader

## 外连接
select e2.empId, e2.eName, e2.job, e2.leader, e1.eName from emp e1 right join emp e2 on e1.empId = e2.leader

## 查找工资比张三高的所有员工信息
select * from emp e1 join emp e2 on e1.salary > e2.salary where e2.eName = '张金山';

9.6 子查询

  • 对子查询的理解
    • 子查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询
  • 子查询语法
    • Select 字段集合, [ ( 子查询语句 ) ]
    • From 表名, [ ( 子查询语句 ) 表别名 ]
    • Where 条件 [ ( 子查询语句 ) ]
  • 子查询通常出现在fromwhere子句中

9.7 子查询的类型

  • 非相关子查询
    • 子查询只执行一次,把查询结果提交给外部查询效率较高
  • 相关子查询
    • 子查询的执行依赖于外部查询数据外部执行一次子查询就执行一次
  • 举例
## 查询薪资超出相同部门员工平均薪资的员工信息
## 相关子查询
select * from emp e1 where salary > (
	select avg(salary) from emp e2 where e1.deptId = e2.deptId
)

## 表连接
select * from emp join (select deptId,avg(salary) deptAvg from emp group by deptId) temp on emp.deptId = temp.deptId where salary > deptAvg

9.8 子查询的主要应用

  • Where 子查询
    • 返回单行单列,与比较运算符联合使用
  • From 子查询
    • 返回多行多列,类似于数据表
  • 举例
## 查询在C区工作的所有员工信息
select e.* from emp e join dept d on e.deptId = d.deptId where location = 'C区';    -- 表连接

select * from emp where deptId = (
	select deptId from dept where location = 'C区'    -- 子查询
)

## 查询与张金山相同职位的所有员工信息
select * from emp where job = (
	select job from emp where eName = '张金山'
)

## 查询工资高于全体员工平均工资的员工信息
select * from emp where salary > (
	select avg(salary) from emp
)

9.9 子查询的优化

  • 子查询方式优化原理
    • 减少查询的次数
  • 举例
## 查询每个部门编号、名称、所在位置,人数和平均工资
select d.deptId,dName,location,count(empId),avg(salary) from emp e right join dept d on e.deptId = d.deptId group by e.deptId

## 优化
select * from dept left join (
	select deptId, count(empId), avg(salary) from emp group By depId
) as temp on dept.deptId = temp.deptId

9.10 Case 分类子查询

  • 应用于分类查询
  • 语法
CASE
	WHEN 条件1  THEN   结果1 
	WHEN 条件2  THEN   结果2
   。。。。。。。   
   ELSE  其他结果  
   END
  • 举例
## 分类查询
select empId 工号, eName 姓名, salary 月薪,
	case
		when salary < 1000 then '屌丝'
		when salary between 1000 and 1500 then '草根'
		when salary between 1501 and 3000 then '白领'
		else '土豪'
	end 评价 from emp

9.11 查询语句练习

## 查询员工奖金是 300,500或者为空的员工信息
select * from emp where comm = 300 or comm = 500 or comm is null
select * from emp where comm in (300,500) or comm is null

## 查询显示所有领取奖金的员工人数和不领取奖金的员工人数
select 10 年龄,'男' 性别
select count(empId) 领取奖金的人数 from emp where comm is not null;
select count(empId) 不领取奖金的人数 from emp where comm is null;
select (select count(empId) from emp where comm is not null) 领取奖金的人数,(select count(empId) from emp where comm is null) 不领取奖金的人数

## 查询出工资中百位上是5的员工姓名
select * from emp where salary like '%5__'    -- 利用模糊查询
select * from emp where salary / 100 MOD(10) = 5     -- 利用系统函数

## 获取所有职位的平均工资的最高值
select max(avgSal) from (select job,avg(salary) avgSal from emp group by job) temp
select * from (select job,avg(salary) avgSal from emp group by job order by avg(salary) desc temp limit 1

## 查询出每个部门的名称,部门人数1以及部门平均工资
select deptId,count(empId),avg(salary) from emp group by deptId
select dName,temp.* from dept d left join (select deptId,count(empId),avg(salary) from emp group by deptId) temp on d.deptId = temp.deptId

## 在上题基础上,再显示部门编号和所在位置,并且部门平均工资高于2000
select * from dept d join (select deptId,count(empId),avg(salary) from emp group by deptId having avg(salary) > 2000) temp on d.deptId = temp.deptId

## 显示非销售人员的职位名称,以及从事同一职位工作的员工工资总和,且该总和必须大于8000元,输出结果按总和升序排列
select job,sum(salary) from emp where job not in ('销售员','销售') group by job having sum(salary) > 4000 order by sum(salary)

## 按工资级别分组,显示级别名称和每级的员工总数和该级别员工工资总和
select gradeId,count(empId),sum(salary) from emp e join salgrade s on e.salary between s.loSal and s.hisal group by gradeId

## 列举出工资高于30部门所有员工工资的员工姓名、部门,所在地和部门平均工资
select * from (
	select temp.*,dept.location,dept.dName from dept,(
		select * from emp where salary >(
			select max(salary) from emp where emp.deptId = 30
		)
	) temp where dept.deptId = temp.deptId
) a,(
	select emp.deptId,avg(salary) from emp group by emp.deptId
) b where a.deptId = b.deptId

## 列举出目前尚无员工的部门编号、名称和位置
select * from dept where deptId not in (
	select distinct deptId from emp
)

## 计划给任职超过35年或者入职在8月份的员工加薪,10部门加10%20部门加15%30部门加20%40部门加30%,显示出员工姓名,部门名称,和加薪前后的工资
select 
eName,
deptId,
salary 加薪前,
case
	when deptId = 10 then salary * 1.1
	when deptId = 20 then salary * 1.15
	when deptId = 30 then salary * 1.2
	else salary * 1.3
end 加薪后
from emp where year(now()) - year(hireDate) > 35 or month(hireDate) = 8

10. 数据操纵语句(DML)

10.1 数据操作语句

  • SQL的数据操纵主要是指对表中的记录进行插入、更新和删除的操作
    • 数据插入:Insert
    • 数据删除:Delete
    • 数据更新:Update

10.2 数据的增加

  • 插入完整记录
    • insert into 表名 values(value1,value2…)
  • 插入部分数据
    • insert into 表名(字段名1,字段名2…) values(value1,value2…)
  • 多行插入:插入一个集合
    • insert into 表名 子查询语句
## 完整插入语句
insert into dept values(50,'法律部','10楼')

## 插入部分语句
insert into dept(deptId,location) values(60,'18楼')
// insert into dept(deptId,dName) values(70,'发展部')  -- 注意不可为空的约束

## 关于自动增长列的数据插入
create table TestTB(
	uId int auto_increment primary key,
	uName varchar(10) not null,
	uPwd varchar(10) not null
)

## mysql 自动增长列的添加可以接受手动输入
insert into TestTB values(200,'zs','666')
insert into TestTB values(uName,uPwd) values('Mike','999')

## 多行插入
create table temp(
	id int not null,
	pName varchar(20) not null,
	pLocation varchar(20) not null
)
insert into temp
select * from dept where deptId in (
	select distinct deptId from emp
)

10.3 数据的删除

  • 使用delete命令来删除表中的记录
    • delete from 表名 [where 条件]
  • from 指定要删除的表名
  • where 指定要删除的记录应满足的条件
  • 如果没有 where 子句,则删除全部记录
## 删除 temp 的数据
delete from temp where id = 1101

10.4 数据的修改

  • 使用update语句来修改表中数据
update 表名 
set 字段名1 = 新的字段值 [,字段名2=新的字段值…] 
[WHERE 条件]
  • set 指定要更新的字段及其更新后的值一次可以更新多个字段
  • where 子句指定要更新的记录应满足的条件,如果没有where,则更新全部记录
update stuInfo set stuSex = '女' where stuNo = 's25301'

11. 视图

11.1 视图的概念

  • 视图是一种虚拟表
    • 它表示一张表的部分数据或多张表的综合数据其结构和数据是建立在对表的查询基础上
  • 视图中并不存放数据,而是存放在视图所引用的原始表
  • 同一张原始表,根据不同用户的不同需求,可以创建不同的视图
  • 支持增删改(单表)视图可以嵌套

11.2 视图的实际应用

在这里插入图片描述

11.3 视图的创建和使用

  • 创建视图语法
create view 视图名
as
	查询语句
  • 通过视图执行查询命令,实现对查询结果的显示
select 字段集合 from 视图名 
  • 举例
## 创建视图,显示每个工资级别的员工数量,最高薪资,最低薪资,平均薪资
create view v_1
as
	select gradeId,count(empId),max(salary),min(salary),avg(salary)
	from emp e join salgrade s on e.salary between s.losal and s.hisal group by gradeId

## 使用视图
select * from v_1

========================================================================================

## 课堂练习1
create view v_stuScore1
as
select (select count(1) from stuInfo) 应到人数,
	   (select count(1) from stuMark) 实考人数,
	   (select count(1) from stuInfo) - (select count(1) from stuMark) 缺考人数
select * from v_stuScore1

========================================================================================

## 课堂练习2
create view v_stuScore2
select
stuName 姓名, 
s1.stuNo 学号,
case
	when writtenExam is null then '缺考'
	else writtenExam
end 笔试成绩,
case
	when labExam is null then '缺考'
	else labExam
end 机试成绩,
case
	when writtenExam >= 60 and labExam >= 60 then '是'
	else '否'
end 是否通过
from stuInfo s1 left join stuMark s2
on s1.stuNo = s2.stuNo
select * from v_stuScore2

========================================================================================

## 课堂练习3
create view v_stuScore3
as
select (select count(1) from stuInfo) 总人数, 
	   (select count(1) from v_stuScore2 where 是否通过='是') 通过人数,
	   concat (round(100 * (select count(1) from v_stuScore2 where 是否通过='是') / (select count(1) from stuInfo)),'%') 通过率
select * from v_stuScore3

12. 存储过程

12.1 存储过程的概念

  • 存储过程是能实现特定功能的sql语句块

在这里插入图片描述

12.2 存储过程的优势

  • 执行速度更快
  • 允许模块化程序设计
  • 提高系统安全性
  • 减少网络流通量

12.3 存储过程的创建

  • 语法
create procedure 过程名 [(参数表)]
begin
      执行的SQL语句块;
end;

12.4 存储过程的调用

  • 调用存储过程的语法
call  存储过程名()
  • 举例
## 获取员工表中的记录总数,并显示输出
create procedure p_1()
begin
	select count(1) 总人数 from emp;
end;

## 使用存储过程
call p_1()

12.5 存储过程的参数

  • 参数类型
    • 实参: 在调用时传入的具体参数值
    • 形参: 在定义语句中定义的参数
  • 参数模式: 用于控制形参的行为
    • IN 模式(默认方式)
    • OUT 模式

12.6 复杂的存储过程(带参存储过程的调用)

  • 输入参数
    • call 存储过程名(输入参数)
  • 输出参数
    • call 存储过程名(@变量)
    • select @变量

12.7 存储过程的参数案例

## 输入一个员工姓名,查询工资大于该员工的所有员工的信息
create procedure p_2(pName varchar(10))
begin
	select * from emp where salary > (
		select salary from emp where eName = pName
	);
end;
call p_2('张金山')

==========================================================================

## 实现一个注册用户的功能,要求输入用户名和密码,然后返回该用户自动增长所生成的ID
create procedure p_3(p_uName varchar(10), p_uPwd varchar(10), out p_uId int)
begin
	// 1. 添加新用户
	insert into testtb(uName, uPwd) values(p_uName, p_uPwd);
	// 2. 返回新用户的id
	select max(uId) into p_uId from testtb;
end;
call p_3('张三','996',@newId);
select @newId

==========================================================================

## 同存储过程接受一个关键字,查询名称中包含该关键字的部门的员工总数,部门名称和平均薪资
create procedure p_4(keyword varchar(10))
begin
select dName, count(1),avg(salary) from emp e join (
	select * from dept where dName like concat('%',keyword,'%')
) t on e.deptId = t.deptId;
end;
call p_4('技术')

12.8 复杂的存储过程(变量的使用)

  • 过程在实现业务功能时,需要定义变量来保存临时的数据
  • 变量定义语法
declare 变量名 数据类型 [default 数值]
declare stuName varchar(20) default ‘前端开发工程师’
  • 变量赋值语法
set 变量名 = 数值 /*简单赋值*/
select 字段 into 变量名 from/*查询赋值*/

12.9 复杂的存储过程(逻辑结构的语法)

  • 逻辑结构
## 顺序结构
// 每句语句以`分号结尾`

## 条件分支结构
if 条件1 then
	执行代码块1;
elseif 条件2
	执行代码块2;
else
	执行代码块3;
end if;

## 循环结构
while  条件表达式  do
	执行代码块;
end while;
  • 存储过程中的语法
create procedure p_5(num int)
begin
	declare i int default 0;
	declare sum int default 0;
	where i < num do
		set sum = sum + i;
		set i = i + 1;
	end where;
	select sum '累加结果';
end;
call p_5(10)
  • 字段的值可以使用分类查询
select salary from emp
  • 分类加分
update emp set salary = case end

12.10 存储过程实战案例——分页查询

## 编写存储过程,输入每页多少条数据和第几页,实现对数据表记录的分页查询
12.10.1 技术点分析1
  • 分页查询语法
  • 存储过程传参
    • pageIndex:页码
    • pageSize:每页记录的数量
  • 存储过程执行语句的拼接
    • 使用concat函数拼接
    • 分页参数的设置
select * from  表名 limit  start , pagesize

// 数据分页
select * from emp limit 5    // 显示记录的数量
select * from emp limit 0,5  // 第一个参数是开始的索引位置,第二个参数显示记录的数量
12.10.2 技术点分析2
  • 存储过程执行语句的执行
prepare strsql from @strSqls;
execute strsql;
deallocate prepare strsql;

13. 事务

13.1 事务的概念

  • 事务(Transaction)是作为单个逻辑工作单元执行的一系列操作
  • 主要作用是保证一组操作的完整性,在商业级应用中普遍使用

13.2 事务的实际应用场景

  • 银行转帐案例
    • 假定资金从人民币帐户A转到美元帐户B,需要两个步骤:
      • 帐户A的资金减少,并进行汇率转换
      • 然后帐户B的资金相应增加

13.3 事务的特性

  • 事务必须具备以下四个特性,简称ACID 特性
    • 原子性(Atomicity)
      • 事务是一个完整的操作
      • 事务的各步操作是不可分的(原子的)
      • 要么都执行,要么都不执行
    • 一致性(Consistency)
      • 当事务完成时,数据必须处于一致状态
    • 隔离性(Isolation)
      • 对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务
    • 永久性(Durability)
      • 事务完成后,它对数据库的修改被永久保持事务日志能够保持事务的永久性

13.4 事务的应用语法

  • MySQL 使用下列语句来管理事务
DECLARE t_error  int;
DECLARE  CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
START TRANSACTION
。。。。。。
IF t_error = 1  THEN
	ROLLBACK;
ELSE
	COMMIT;
END  IF

14. 总结

  • 学习 MySQL 的笔记总结
©️2020 CSDN 皮肤主题: 像素格子 设计师:CSDN官方博客 返回首页