数据库
数据库
- 思维导图链接
- 视频链接
- MySQL深度原理
- 顺便分享一下我在工作中写的小工具，可以用 JSON 来写 SQL：http://sql.yupi.icu，代码已开源到 github（liyupi/sql-generator） 要不是自己造了这个轮子，我估计需求肯定是没办法按时完成了，现在想想真的刺激。
一、数据库简介
数据库(DataBase,简称DB) 是能够存储的大量的，有结构的，共享的数据集合
- 作用：存储数据，存储在电脑磁盘文件中
- ✨数据库操作系统 和 语言处理程序(C、java) 同级别，都作用于磁盘文件(其实是虚拟机处理程序)
- Linux 是MySQL数据库服务器中最常使用的(宿主)操作系统——结合虚拟机结构理解
-
分类
-
层次数据库——树状图
-
网状数据库——网状图
-
关系数据库——二维表(表格与表格建立联系)
- MySQL、Oracle、SQL server
-
面向对象数据库——未来大趋势
-
面向检索的列式存储
- HaBase
-
面向高并发的缓存存储——Map中的键值对
- ✨Redis
-
-
-
术语
- 数据库系统 相当于Windows系统
- 数据库管理系统 相当于 cmd(控制台)
-
1.1 MySQL 数据库
- 版本
- 5.x——2020年 5.7.32
- 8.x——2021年 8.2.6
- 8.x 新特性
- 性能：速度较 5.7 快两倍
- 提供 NoSQL非关系存储
- 索引：隐藏索引，降序索引
- 8.x 新特性
1、MySQL 安装
下载、安装、配置、服务、卸载
1、下载
- 国内镜像地址：http://www.filehorse.com/download-mysql-64/download/
- MySQL 8.0.28版本——安装在D盘
2、安装
傻瓜式安装(直接下一步)
- 安装类型选择(一般选择默认)
- ✔其中自定义安装，可以自行选择要安装的product
- ✔有时需要安装依赖的环境——例如 Microsoft Visual C++ 2015-2019
-
- 安装的products
-
3、配置环境变量
- 端口
-
- 设置密码及新用户
-
4、🌻服务的启动与停止
MySQL 是以服务的形式运行在系统中
-
第一种方式：计算机管理窗口
- 此电脑——右键——管理
-
-
第二种方法：以管理员身份运行 cmd
-
-
✨通过 Navicat 连接是通过提供服务器接口
5、卸载
-
关闭服务
-
//上面 net stop MYSQL80
-
-
卸载 MySQL 相关所有软件
控制面板—程序和功能
-
删除目录
- Everything搜索
-
删除注册表
-
win + R
-
-
找到mysql进行删除
-
-
-
2、DBMS(管理工具)
用户通过 DBMS（DataBase Management System）对数据库进行操作
🌻命令行工具
🔔未启动服务或密码错误都会闪退
MySQL command line Client软件——MySQL默认安装
- 类似于DOS控制台(cmd)的作用(与机器对话)
-
基本流程
✔需要先启动服务器(上面介绍的两种方式)
root——密码
show databases;——查看所有数据库
use mydb_one;——进入一个数据库
desc books;——查看该数据库中表结构
创建、删除、修改
🌻可视化工具(客户端工具)
- SQLyog
- ✨Navicat
- 连接失败解决
- 'caching_sha2_password'——加密规则改动导致
-
- 连接失败解决
3、🔔逻辑结构
存储结构：二维表
数据
字段(列)——一维数组
数据表table——二维数组
数据库database——网(表与表关系)
1.2、✨SQL语言
SQL (Structured Query Lanaguage) 结构化查询语句
作用：数据库开发的编程语言，拥有自己的一套流程控制，语法定义。
- 特点
- 面向集合的语言
- 非过程语言
- 🌻适用于不同底层结构的不同类型的数据库
1、SQL分类
针对对数据库的不同操作，将 SQL 指令分为四类：
- ✔DDL (Data Definition Language) 数据定义语言
- 作用对象：数据库对象(数据库、数据表、触发器、视图、索引...)
- 具体操作：创建、删除、修改
- ✔DML (Data Manipulation Language) 数据操作语言
- 作用对象：数据表中的数据
- 具体操作：增、删、改
- ✔DQL (Data Query Language) 数据查询语言
- 作用对象：数据表中的数据
- 具体操作：查询
- ✔DCL (Data Control Language) 数据控制语言
- 作用对象：事务管理
2、SQL基本语法
在任何管理工具中均可以使用
- 🧨注意
- 只要有一条指令错误，所有指令都不会执行！
- SQL 指令不分大小写
- 注释：#、--
- 每条指令都已以 ";" 结尾
- 因为 SQL 叫结构化查询语句，所以可以在 Navicat 的查询中编写 SQL 指令
二、DDL库+表定义语言
2.1、⚽数据库操作
查询数据库 show
显示mysql中数据库列表
show databases;
显示指定名称的数据库的信息
show creat database <dbName>;
创建数据库 create
1、dbName 表示创建的数据库名称，可以自定义
- create database <dbName>;
2、当指定的数据库不存在时执行创建（创建数据库的另一种语句）
- create database if not exit <dbName>;
3、创建数据库时指定数据库的—字符集（中文编码格式）
- create database dbName character set utf8;
修改数据库 alter
修改数据库字符集(包含utf8 gbk...)
- alter database dbName character set utf8;
删除数据库 drop
- 删除数据库
- drop database dbname;
- 如果数据库存在则删除数据库
- drop database is exists dbname;
2.2、🥎数据表操作
-
列1 列2 列3 字段1.1 字段1.2 字段1.3 字段2.1 字段2.2 字段2.3 - 列的性质
- 列类型包括：Field Type Null(非空) Key(unique) Default(字段默认值) Extra
- 字段的性质
- 列的性质
创建数据表 create
列之间用 " " 隔开
字段之间用 "," 隔开
- 设计表：
| Field | Type | Null(非空) | Key(unique) | Default(字段默认值) | Extra |
|---|---|---|---|---|---|
| stu_num | char(8) | NO | PRI | Null | |
| stu_name | varchar(20) | NO | Null |
//-进入/切换数据库
use dbname; （作用：确定在哪个数据库中创建表）
//创建表格
create table students(
stu_num char(8) not null unique ,
stu_name varchar(20) not null
);
create table books(
stu_num int(4),
stu_name varchar(20) not null
);
查询数据表 show
-
- 查询当前表 - show tables; - 查询具体表的结构 - desc students;
删除数据表 drop
-
- 删除数据表 - drop table students; - 如果数据库存在则删除数据库 - drop table is exists studets;
修改数据表(列操作) alter..add/drop/change
-
- 修改表名 - alter table students rename to stu; - 添加列 - alter table 表名 add 列名 varchar(200); - 修改列名和类型 - alter table stu change 旧列名 新列名 类型; - 修改列类型（此类型包含数据类型和字段约束） - alter table 表名 modify 列名 char; - 删除列 - alter table 表名 drop 列名；
2.3、⚾列(纵)的数据类型
列类型包括：
- Type
- Default(字段默认值)
- 约束(Null(非空) 、Key(unique) 及组合)
1、✨Type类型
选择 数值类型、字 符串类型、日期类型中某一个类型
1.1、数值类型
1.2、字符串类型
💖1、char(n)或varcahr(n)的区别
- char是一种固定长度的类型，无论储存的数据有多少都会固定长度，如果插入的长度小于定义长度，则可以用空格进行填充。
- varchar是一种可变长度的类型，当插入的长度小于定义长度时，插入多长就存多长
- n代表最大长度，例如存放 n 个 "a" 或 "我"
💖2、ENUM 和SET 类型
一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。
-
ENUM 类型——单选项。
- 在处理相互排拆的数据时容易让人理解，比如人类的性别。
-
SET 类型——任意数量的值。
💖3、Blob 和 Text 类型
-
Blob——将图片换算成二进制保存到数据库
-
Text——保存文本(字符)数据
-
1.3、日期类型
- 虽然可以用字符串，但基于日期类型，便于查询
| 日期类型 | 占用空间（字节数） | 表示范围 | 用途 |
|---|---|---|---|
| date | 4 | 1000-01-01 ~ 9999-12-31 | 年-月-日 |
| time | 3 | -838:59:59.000000 ~ 838:59:59.000000 | 时-分-秒 |
| year | 1 | 1901-2155 | 年 |
| datetime | 8 | 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 | 年-月-日 时-分-秒 |
| timestamp | 4 | 1970-01-01 00:00:00.000000UTC ~ 2038-01-19 03:14:07.000000UTC | 年月日时分秒 |
2、Default 类型
默认数据类型
- 对应 DML操作中的 vaules 值
3、✨字段约束类型
💖定义在列，但作用于字段
为什么要添加约束？
- 保证数据的有效性
- 保证数据的完整性
- 保证数据的准确性
1、非空约束(NULL)
限制表中此列的值不能为空——not null
-
创建表
-
create table books( book_isbn char(4), book_name varchar(10) not null, book_author char(6) );
-
-
报错
2、唯一约束(Key)
此列的值不能重复——unique
-
创建表
-
create table books( book_isbn char(4) unique, book_name varchar(10) not null, book_author char(6) );
-
-
报错
3、主键约束(primary key)
非空+唯一
一张表只能有一个主键（主键可以设为一个或多个列组合的属性）
1、创建主键约束
-
//第一种方法 create table books( book_isbn char(4) primary key, book_name varchar(10) not null, book_author char(6) ); //第二种方法 create table books( book_isbn char(4), book_name varchar(10) not null, book_author char(6)， primary key(book_isbn) );
2、删除主键约束
- alter table books drop primary key;
3、创建表后添加主键约束
alter table books modify 表名 char(4) primary key;
4、主键自动增长
描述：如果数据表中有列可以作为主键（例如：学号、图书序号），则设这一列主键属性
如果没有 适合的，需要单独定义一列，添加一个int类型的自动增长(auto_increment)的主键
创建自动增长的主键
-
create table inc( type_id int primary key auto_increment ,//此列默认填充 book_name varchar(10) not null, book_author char(6) );
注意：自动增长从1开始递增，删除一行数据后不会重复生成（保证唯一性）
5、联合主键
额外创建一个表，将其他数据表中的主键列添加进来
-
定义联合主键
creat table grades( stu_num char(8), course_id int, score int, primary key(stu_num,course_id) );
6、外键✨
作用：额外添加一个cid列作为外键，关联其他"父"表的主键
1、基本操作
✨案例—
学生表 和班级表
- 目的：在学生表中添加外键与班级表的主键进行关联
🌻创建班级表
create table class(
class_id int primary key auto_increment,
class_name varchar(10) not null unique,
class_remark varchar(40)
);
🌻创建学生表
在学生表中添加 cid外键列
- 语法——声明外键 并 关联对应表的主键
constraint 外键名 foreign key(cid) references class(class_id)
【方式一】创建表同时添加外键
#定义cid列，并添加外键约束
#由于cid列和class_id关联，所以这两列的类型和长度需要一致
create table studnet(
stu_num int primary key,
stu_name char(10) not null,
stu_grnder char(2),
stu_age int,
cid int,
constraint 外键名 foreign key(cid) references class(class_id)
);
【方式二】先创建表，后添加外键
create table studnet(
stu_num int primary key,
stu_name char(10) not null unique,
stu_grnder char(2),
stu_age int,
cid int
);
alter table student add constraint 外键名 foreign key(cid) references class(class_id)
【删除外键】
alter table student drop foreign key 外键名;
✨案例二
修改学生表中学生的班级
- 目的：在外键列中增、删、改相应主键数据
- 查询操作在多表查询中
-
三部走：
-
去关联；——删
-
修改班级表某字段信息；
-
加关联——增、改
-
2、级联操作
针对案例二（简便操作）
🌻语法
ON UPDATE CASCADE ON DELETE CASCADE//只需在创建时，设置级联修改和级联删除
🌻1、删除原有外键
alter table student drop foreign key 外键名;
🌻2、声明新外键 并 关联对应表的主键 并设置 级联修改和级联删除
constraint 外键名 foreign key(cid) references class(class_id) ON UPDATE CASCADE ON DELETE CASCADE;
🌻3、修改班级表某字段信息
- 关联 Java2104 这个班的学生表中的 cid 也会同步修改
update class set class_id=5 where class_name="Java2104";
2.5、🏀表关联关系
✨MySQL 是一个关系型数据库，最牛逼的当属表与表的关联！
- 需要用到外键操作（案件一、二）
1、一对一
2、一对多/多对一
类似于树型结构
3、多对多
额外创建一张表+将两个外键添加进来
类似于图结构
🚀流程控制与函数
1、流程控制
1.1、定位语句 Where
🌻Where 定位语句
在删除、修改、查询中用来定位（满足条件）
下面演示在查询中的使用：
常用
-
- 删除：delete from 表名 where 条件; - 修改：update 表名 set 列名=value where 定位行; - 查询：select 列1,列2... from 表名 where 条件
✨条件运算符
在where 子句的条件中使用，包含——匹配、关系、逻辑运算符
常用：
- 区间查找——between...and...
- 定元素查询——like
-
匹配运算符
-
✨like定元素条件查询
-
🎈IN 在集合中查询
在where条件中，使用like关键字实现定元素查询
-
语法
-
select * from stu where stu_name like '%o%';
-
-
用法
-
-
-
关系运算符
3、逻辑运算符
- and/or/not
🌻 having 定位语句
优先级：where>having
🌻ON 定位语句
- where 与 on 的区别
- where——先执行程序，再从结构中选出满足条件的字段
- on——执行满足条件的程序(大大节省空间)
1.2、选择语句
一般在语句块(存储过程)中使用
1、IF
-
🌻语法
-
✔--单分支 if 条件 then --SQL语句 --如果满足条件，就执行该语句 end if; ✔--多分支 if 条件 then --SQL语句 elseif 条件 then --SQL语句 else --SQL语句 end if;- 示例
2、case
case 形参
when 1 then
--SQL语句
when 2 then
--SQL语句
else
--SQL语句
end else;
1.3、循环语句
1、while
while 条件 do
--SQL语句
end while;
2、repeat
repeat
--SQL语句 --一直重复，直到条件满足
until 条件
end repeat;
3、loop
类似 break 中止
loop_label:loop
--SQL语句
if 条件 then leave loop_label;
end if;
end loop;
2、函数
2.1、聚合(数值)函数
SQL中提供了一些对查询的列进行计算的函数
-
count()统计函数——where条件根据情况加或不加
-
select count(book_isbn) from books where book_gender='男';//返回book_isbn列所有满足条件的数相加的总和
-
-
max()
-
min()
-
sum()
-
avg()——求平均
2.2、字符串函数
-
拼接多列
-
select concat(列1,列2,...) from 表;
-
-
指定列转换大写/小写
-
select upper/lower(列1) from 表;
-
-
✨部分显示
-
select substring(stu_tel,8,4);//第八位开始，向后取四位
-
2.3、日期类型 now()
当添加日期类型时，
可以通过日期函数获取当前时间
- now() 和 sysdate()
可以省去手打固定格式
3、关键字
| 关键字 | 作用 | 示例 |
|---|---|---|
| set | 给变量赋值 | set @m = 0; |
三、DML字段操作语言
对数据表中的字段(数据)进行添加、删除、修改操作
3.1、添加数据 insert into
insert into 列表名（列1,列2,列3...）values(value1,value2...);
-
向数据表中添加数据
-
注意：
- 如果字段允许非空可以不写
- （列1,列2,列3...）可以与创建表中的列不保持一致，但 (value1,value2...) 要和（列1,列2,列3...）保持一致
-
//向指定列添加数据——推荐使用，增强SQL的稳定性 insert into books(book_isbn,book_name,book_age) values('03','平方的世界','');——可以为空 //向所有列添加数据 insert into books values(...)——与创建表中列保持一致
-
3.2、删除数据 delete from
delete from 表名 where 列条件;
-
删除序号为02的书单
-
delete from books where book_isbn='02';
-
-
删除年龄大于20的信息行
-
delete from books where stu_age>20;
-
3.3、修改数据 update..set..
update 表名 set 列名=value where 定位行;
-
书单号为001的改作者名为莫言
-
注意：
- 若没有where,则无法定位行，结果将改变这一列的所有值
-
update books set book_author='莫言' where book_isbn='001';
-
四、DQL
✨目的：从数据表中 提取满足条件的记录(字段)
- 单表查询
- 多表查询
所有的查询处理都不会影响原表数据！！！
4.1、查询基础 select..from
-
查询数据表中某一或多列的数据
-
select 显示的列(列1, 列2...) from 表名
-
-
查询数据表所有信息（列及其数据）——区分于desc 表名（只查看所有列）
-
select *from 表名
-
+-----------+------------+-------------+----------+
| book_isbn | book_name | book_author | stu_name |
+-----------+------------+-------------+----------+
| 001 | 平方的世界 | 莫言 | 11 |
| 002 | 道理 | 李四 | 12 |
+-----------+------------+-------------+----------+
✨游标(指针) cursor
游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，即游标用来逐行读取结果集。
作用：
游标cursor，主要用于循环处理结果集。存储Select的查询结果，并用来遍历。
如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。
回滚：在存储了游标之后，应用程序可以根据需要滚动混着浏览其中的数据
使用步骤：
1、声明游标
declare 游标名 cursor for 查询语句;
2、打开游标
open 游标名;
3、使用游标
利用循环，实现游标下移
fetch 游标名 into 记录;
4、关闭游标
close 游标名;
- 示例
- 注意：count(1) and count(字段)，两者的主要区别是
（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
- 注意：count(1) and count(字段)，两者的主要区别是
4.2、查询结果处理
所有的查询处理都不会影响原表数据！！！
✨包含：排序、分组、分页、多表、嵌套查询
1、基本使用
-
计算列(并显示出来)
-
select stu_name-1 from books;//注意有个“-1”
-
+------------+
| stu_name-1 |
+------------+
| 10 |
| 11 |
+------------+
-
对列进行改名（并显示）——as
-
select stu_name-1 as bri from books;
-
+------+
| bri |
+------+
| 10 |
| 11 |
+------+
-
消除重复行
-
select distinct stu_name from books;
-
4.3、排序查询 order by
查询到 满足条件的记录 按照 指定列的值 升序或降序
- asc 升序(默认)
- desc 降序
语法：
select * from 表 where 条件 order by 指定列 asc/desc;
实例：
4.5、💖分组查询 group by
将查询结果进行分组，字段值相同的为一组
⚙结合 聚合函数 一起使用
-
基本语法
-
select 列/聚合函数 from 表名 [where 条件] group by 列
-
常规——先对性别进行统计，然后分别统计每组人的个数
select stu_gender,count(stu_num) from stu group by stu_gender by order stu_age; //group by 前后的列(stu_gender)保持一致 -
-
综合步骤
-
定位条件——where
-
分组之后的定位条件——having
-
分组——group up
-
排序——order by
✨综上所述：where>group by>having>order by
-
-
应用
- 查询 ✔学生年龄列|统计每组人数列 + where定位 + 按年龄分组 + having定位 + 按年龄排序
- 查询 ✔学生年龄列|统计每组人数列 + where定位 + 按年龄分组 + having定位 + 按年龄排序
4.6、分页查询 limit
数据较多时，为增加可读性，进行分页展示
4.7、多表(连接)查询
从多张表中查询数据
🌻创建班级表+学生表
🌻添加班级+学生数据
- 班级表
- 学生表
按功能不同分类：
1、内连接 inner join..on
🌻基本语法
select 学生/班级列 from 学生表 inner join 班级表 ON 匹配条件;//匹配条件般为：students.cid=classes.class_id;
-
为什么要用 ON 定位条件？
减少无效数据+优越性
- 1、A表 inner join B表 执行结果
- 满足笛卡尔乘积 = A表字段总数*B表字段总数
- 2、where 与 on 的区别
- where——先执行程序，再从结构中选出满足条件的字段
- on——执行满足条件的程序(大大节省空间)
- 1、A表 inner join B表 执行结果
2、外连接 left/right join
1、左连接 left join
显示左表中所有字段 + 右表中满足匹配的数据；若无对应匹配，则显示Null(区别于内连接不显示)
🌻语法
select 学生/班级列 from 学生表 left join 班级表 ON 匹配条件//匹配条件般为：students.cid=classes.class_id;
🌻起别名
select 学生/班级列 from 学生表 s left join 班级表 c ON 匹配条件//匹配条件般为：ss.cid=c.class_id;
2、右连接 right join
对标 左连接
4.8、多表(嵌套)查询
先进行一次查询，把第一次查询的结果作为第二次查询的条件
又叫 ：子查询
- 根据第一次查询(括号内)的返回结果，分为三类：
- 返回单行单列、返回多行单列、返回多行多列
🌻返回单行单列 =
单行—— Java2104
单列——cid
select * from students where cid=(select class_id from classes where clss_name='Java2105');
🌻返回多行单列 IN
多行——Java%
单列——cid
IN——在集合中查询
select * from students where cid IN (select class_id from classes where clss_name LIKE 'JAVA%');
🌻返回多行多列
不常用
五、存储过程(函数) procedure
- 存储过程(Stored Procedure)是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象(提供接口)
- ✨用到了封装思想，存储过程是数据库的一个对象，与表一个层级
- 类比：增删改查表
作用：
- 创建
- 将能够完成特定功能的SQL指令进行封装（SQL指令集），
- 编译后存储到数据库服务器上，并为之取名，
- 调用
- 客户端可以通过这个名字直接调用这个SQL指令集，获取执行结果
🌻传统过程
-
SQL指令底层执行流程
-
执行过程存在的问题
- 当进行重复性执行相同 SQL 指令时，需要不断地接受和传递，效率低下
- 当执行多条指令，且第二条指令需要第一条指令的结果作为参数时
-
🌻通过存储过程对特定功能的SQL指令封装、取名、调用
-
存储过程底层执行流程
-
✨常用的存储引擎对比
-
存储引擎——集成开发环境
存储过程——在集成开发环境上编写具体程序
- SQL引擎——封装好的程序接口或集合(最底层的功能实现)
- InnoDB 存储引擎是 Mysql 的默认存储引擎
-
- 🎈存储过程优缺点
- 优点
- 安全性：本地上传，无需网络(保存在磁盘)
- 无需重复编译操作
- SQL指令间存在逻辑关系，支持流程控制语句（选择，循环），可以实现更复杂的业务
- 缺点
- 不同数据库需要重新编写SQL指令
- 互联网项目，数据库的高并发访问，会增加执行时间
- 优点
5.1、变量
1、用户变量
作用域：当前整个连接
-
用户变量使用set关键字直接定义，变量名以@开头
-
🌻语法
set @r=1;
-
-
用户变量存储在mysql数据库字典（daul）中，可通过select @变量名 from dual查询
-
select @r from dual;
-
2、局部变量 declare
declare ——声明局部变量关键字/default ——初始化数据
set关键字 ——给变量赋值
- 作用域：只能在语句块内部使用，语句块执行完后，就消失了
🌻语法
declare 名 类型 []
delimiter //
CREATE PROCEDURE test5(in a int,out r int)
BEGIN
declare x int default 0;
declare y int default 1;
set x = a*a;
set y = a/2;
set r =x+y;
end //
3、变量赋值 into
两种方式：
在 set 命令中，使用“=”
在 select 命令中，使用“into” 或“ :=”
delimiter //
create procedure test8(IN a int,IN b int,OUT c int)
begin
set @d=0;
SET c = a+b;
SELECT count(stu_num) into @d from books;
--查询
SELECT c as 别名,@d;--as,起别名
end
//
set @c;
call test8(5,8,@c);
5.2、参数
-
✨重要联系！
-
参数为函数关键变量
-
delimiter // CREATE PROCEDURE test5(关键变量) BEGIN 辅助变量 end // -
delimiter // CREATE PROCEDURE test5(in a int,out r int)--参数 BEGIN declare x int default 0;--局部变量 set y;--用户变量 end //
-
1、三类参数
- IN——输入参数
- 创建时形参，调用时实参
- OUT——输出参数，相当于Java的返回值
- INOUT——既可输入，也可输出
5.3、创建存储过程
和方法类似
🌻语法
- 注意：关键字 delimiter //...// 分隔符的使用
- MySQL 默认以“;”为分隔符，每次只能执行一句
- 若未声明delimiter，N编译器会将存储过程当作SQL指令执行，结果会报错
delimiter //
create procedure 过程名(IN/OUT args);
begin
SQL语句
end;--begin...end;相当于{...}
//
//示例
delimiter //
create procedure test(IN a int,IN b int,OUT c int)
begin
SET c = a+b;
--输出
select c [from dual];//dual为存储变量的数据库字典,[]可省略
end
//
5.3、调用存储过程 call
类比于创建方法+形参、实参的使用
🌻语法
- 仅选中 call 语句运行
set @m;
--调用
call test(5,6,@m);
- 示例
delimiter //
create procedure test8(IN a int,IN b int,OUT c int)
begin
set @d=0;
SET c = a+b;
SELECT count(stu_num) into @d from books;
--查询
SELECT c as 别名,@d;--as,起别名
end
//
set @c;
call test8(5,8,@c);
5.4、增删改查
在函数里面，可以点击查看所有创建的存储过程
1、查询数据过程
存储过程隶属于某个数据库，所有要声明在哪个数据库中调用此存储过程
🌻语法
--查询所有存储过程
show procedure status where db='sys';
--查看某个存储过程的创建细节
show CREATE procedure sys.test7;
2、修改数据过程
修改存储过程的特征，并不能修改具体内容
需要“点击”修改
🌻语法
alter procedure test7 [reads sql data];
🎈存储过程的特征参数
3、删除数据过程
drop PROCEDURE 存储过程名;
5.5、存储函数
5.6、设计借书系统
1、逻辑流程图
- 准备工作
- 创建存储过程
- 调用测试
2、代码实现
- 创建表
- 图书信息表、学生表、借书记录表
- 创建存储过程
-- 创建存储过程：实现借书业务
-- 参数1：a 输入参数 学号
-- 参数2：b 输入参数 图书编号
-- 参数3：m 输入参数 借书的数量
-- 参数4：state 输出参数 借书状态（1：借书成功，2：学号不存在，3：图书不存在，4：库存不足）
DELIMITER $$
CREATE PROCEDURE proc_borrow_book(IN a CHAR(8),IN b INT,IN m INT,OUT state INT)
BEGIN
DECLARE stu_count INT DEFAULT 0;
DECLARE book_count INT DEFAULT 0;
DECLARE stock INT DEFAULT 0;
-- 判断学号是否存在：根据参数 a 到学生信息表查询是否有 stu_num=a 的记录
SELECT COUNT(stu_num) INTO stu_count FROM students WHERE stu_num=a;
IF stu_count>0 THEN
-- 学号存在
-- 判断图书 id 是否存在：根据参数 b 查询图书记录总数
SELECT COUNT(book_id) INTO book_count FROM books WHERE book_id=b;
IF book_count>0 THEN
-- 图书存在
-- 判断图书的库存是否充足：查询当前图书库存，然后和 m 进行比较
SELECT book_stock INTO stock FROM books WHERE book_id=b;
IF stock>=m THEN
-- 执行借书操作
-- 操作1：在借书记录表中添加记录,使用系统时间：SYSDATE()
INSERT INTO records(snum,bid,borrwo_num,is_return,borrow_date)
VALUES(a,b,m,0,SYSDATE());
-- 操作2：修改图书库存
UPDATE books SET book_stock=stock-m WHERE book_id=b;
-- 借书成功修改状态
SET state=1;
ELSE
-- 库存不足
SET state=4;
END IF;
ELSE
-- 图书不存在
SET state=3;
END IF;
ELSE
-- 学号不存在
SET state=2;
END IF;
END $$
- 测试
## 调用存储过程借书
-- 测试学号不存在
SET @state=0;
CALL proc_borrow_book('1005',1,2,@state);
SELECT @state FROM DUAL;
-- 测试图书不存在
SET @state=0;
CALL proc_borrow_book('1001',3,2,@state);
SELECT @state FROM DUAL;
-- 测试图书库存不足
SET @state=0;
CALL proc_borrow_book('1001',2,50,@state);
SELECT @state FROM DUAL;
-- 测试借书成功
SET @state=0;
CALL proc_borrow_book('1001',1,2,@state);
SELECT @state FROM DUAL;
3、建模思维
-
以商业项目为例：最终目标是签署商业合同，这其中牵涉到策划、宣传、调研、谈判、法律等很多人和事。商业项目有其既定的程序，也就是按—定的顺序来完成这些活动，最终达到商业目的是在正式开展商业项目之前，这个程序应该是被计划一好了的，这称之为一个方案，在建模来说，这就是一个场景。
商场如战场，任何事情都可能发生，所以我们要做好多种可选方案，在建模来说，这就是多个场景。市场瞬息万变， 再好的商业方案也不能一条道走到黑，当市场发生变化时，方案也要随之做出调整，在建模来说，这就是分支过程。 另外，我们也不得不考虑到意外情况的发生，要做好应对措施，在建模来说，这就是异常过程。
-
绘制场景的两个基本要求：
场景隐含着两个基本要求：一是必须忠实于真实业务，二是一个场景只能描述业务的一种执行方式。也就是说，在描述业务用例场景时不能带有“设计“思想在里面，或者试图“抽象”和“优化“业务过程，它必须和客户认可的实际业务执行一致。同时，不要试图在一个场景里把业务的所有内容都包括进来，绘制出一幅充满了判断分支，像蜘蛛网一样的活动图。每一个场景只针对一种业务执行方式，应当清晰而明了。
六、触发器(功能函数) trigger
6.1、基本概念
触发器是指，事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行
- 触发器是一种特殊类型的存储过程，是否可以在函数中查看？
- 区别：
- 存储过程需要主动调用其名字执行
- 触发器是通过 DML 触发而自动触发被执行的
-
优点
-
✨触发器可以通过数据库中的关联表实现级联更改，即一张表数据的改变会影响其他表的数据
-
可以保证数据安全，并进行安全校验
-
-
缺点
- 过分依赖触发器，影响数据库的结构，增加数据库的维护成本
6.2、创建触发器
案例：向学生表中添加、删除、修改学生信息时，使用触发器自动进行日记记录
- 准备工作——创建学生表和日记表(关联表)
🌻语法
delimiter $$ --$$(自定义结束符号)
create trigger 触发器名
<before|after> <insert|delete|update> on 表名 --定义触发时机+定义DML类型
for each row --声明为行级触发器（只要操作一条就触发）
sql_statement --触发操作
end $$
示例
- 添加数据操作
- 当学生表中发生添加操作时，则向日记表中记录一条记录
delimiter $$
create trigger tri_test1
before insert on students
for each row
insert into stulogs(time,log_text)values(now(),concat('添加',new.stu_num,'学生信息'))--因为只有这一条语句，所以 --省略 began...end
$$
6.3、查看触发器
show triggers
6.4、测试触发器
- SQL语句操作
- 可视化操作
6.5、删除触发器
drop triggers 触发器名
6.6、new和old✨
new:获取insert操作中添加的数据，update修改后的数据
old：获取delete操作删除前的数据，update修改前的数据
-
new
-
insert操作中
insert into stulogs(time,log_text)values(now(),concat('添加'，new.stu_num,'学生信息')) -
update操作中
-
-
old
-
delete操作中——删除的记录
-
update操作中——修改前的记录
-
七、视图(虚拟表) view
由数据库中一张表或多张表根据特定条件查询出的数据构成的虚拟表
- 视图 和 DQL功能查询 结合使用效果更佳
- 与 DQL 中查询结果处理的区别
- 会修改原表数据
- 视图的作用——类比于虚拟机的作用
（1）简化了操作，把经常使用的数据定义为视图
我们在使用查询时，写的语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我们只需要select * from view就可以啦，这样很方便。
（2）安全性，用户只能查询和修改能看到的数据
因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图不可以随意的更改和删除，可以保证数据的安全性。
（3）逻辑上的独立性，屏蔽了真实表的结构带来的影响
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而使程序与数据库表被视图分割开来
7.1、创建视图
- 语法
create view <view_name>
as
--DQL语句
7.2、查询视图
select * from <view_name>;
-
案例
7.3、修改视图
7.4、删除视图
八、索引(查找) index
目的：额外插入一张索引表，提高查询数据的效率，避免全表扫描查询(传统穷举)——当数据庞大时，优势就显现出来啦！
查找时，使用到索引表的数据结构
- 顺序表：分块查找
- 树型：B+树
执行原理
- 当数据查询时，先在索引表中进行查找得到数据对应的地址，然后根据地址在数据表中快速获取数据
✨查找的类型
- 普通查找——根据算法不同
- 顺序查找(穷举)、折半查找、二叉排序树、B树
- 索引查找
- 分块查找、B+树
- 散列查找
- 优缺点
8.1、分类
根据创建索引表的列类型的不同：
-
主键索引——要求：所在列，字段的值不能重复且不为空
-
唯一索引——要求：所在列，字段的值不能重复
-
普通索引——无要求
-
组合索引
- 最左前缀：在查询语句中必须包含组合的所有列或最左列才会触发
- 为了进一步榨取MySQL的效率，就要考虑建立组合索引
-
全文索引——模糊查询
- like + % 就可以实现模糊匹配，但是对于大量的文本数据检索，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。
8.2、创建索引
- 注意：字段添加主键、唯一约束，会自动在字段中创建对应索引
- 索引创建后，系统自动调用对应索引
create index 索引名 on 表名(列名);
--1、唯一索引
create unique index index_test1 on tb_testindex(tid);
--2、普通索引
create index index_test1 on tb_testindex(name);
--3、组合索引
create index index_test1 on tb_testindex(tid,name);
- 注意：index_type 为 B+ Tree
8.3、查看索引
--查询所有索引
show indexes from tb_testindex;
--查看(主、唯一)键索引
show keys from tb_testindex;
8.4、删除索引
drop index index_test3 on tb_testindex;
九、事务管理(DCL) transaction
数据库事务：完成同一个业务的多个DML操作
- ✨类比于进程——进程是负责执行代码/命令的执行者
9.1、事务的 ACID 特性
- 原子性（Atomicity):一个事务中的多个DML操作，要么同时执行成功，要么同时执行失败
- 一致性(Consistency)：事务执行前后，数据库的数据是一致的，完整性不被破坏
- ✨隔离性(lsolation): 数据库允许多个事务同时执行（张三、李四同时借Java书），多个并行的事务之间不能相互影响
- 持久性(Durability):事务执行后，对数据的操作是永久的
9.2、保证原子性 Atomicity
底层实现可能是——进程控制(通过原语)
1、自动提交
自动提交(系统默认)：客户端执行 DML 操作，经过缓存，自动提交到数据文件
- 无法保证原子性
2、✨事务管理(DCL)—手动提交
三步核心操作
开启事务
start transaction回滚事务
rollback——撤销操作
- 用于处理结果异常情况，类比于Java异常处理
提交事务
commit
- 需要手动提交到数据文件
- 工作流程
- 代码实现
9.3、保障隔离性 Isolation
数据库允许多个事务同时执行，多个并行的事务之间不能相互影响
1、四种级别
1.1、读未提交 read uncommitted
一个事务读取到了另一个事务中未提交的数据
- 可能问题：脏读——T1 发生回滚
2、读已提交 read committed
T2只读取另T1已提交的数据
-
解决了脏读
-
可能问题：虚读——T1 在T2两次查询操作之间的时间段内，修改并提交，结果导致——两次查询的数据不一样
-
作用域：两次查询操作之间“开放”
3、可重复读 repeatable read(系统默认)
T2在执行第一次查询之后，在多次查询结束之前，其他事务不能修改对应数据，结果导致——两次查询的数据一样
- 解决了虚读
- 可能问题：幻读——T1操作的延后性，导致信息不一致
- 例如在 T2 事务的两次查询之间 添加一条 将所有商品价格+1 的操作
- 作用域：两次查询操作之间 ”封锁“
4、串行化 serializable
提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行
同时代价也是最高的，性能很低，一般很少使用
- 避免了脏读，需读，幻读
2、设置隔离级别
- 两种方式
- 在数据库中，设置默认隔离级别
- JDBC，设置事务间隔离性（如在应用程序Spring中）
- 设置隔离级别
set session transaction isolation level <read committed>;
- 查询隔离级别
select @@transaction_isolation;
十、数据库设计
数据库是为应用系统服务的，数据库存储什么数据也是由应用系统决定的
- 数据库设计的要求：
- 完成数据的存储，方便提取
10.1 数据库设计流程
要根据应用系统的需求，
- 分析
- 分析要存储的数据对象
- 提取提取实体的数据项
- 符合规范——三范式
- 选框架
- 绘制E-R图——实体关系图
- 数据库建模
- 貌似是个插件，能够显示关系
- 建库建表
测试
-
分析数据实体（即要存储的数据对象）
-
电商系统：商品、用户、订单
-
教务管理系统：学生、课表、成绩
-
-
提取实体的数据项
- 学生的id、学号、性别......
- 用户的id、登入名、登入密码.......
-
设计三范式规范
便于维护和数据一致性
- 第一范式：要求数据表中的字段不可再分
- 第二范式：不存在非关键字段对关键字段的部分依赖
- 第三范式：不存在非关键字段之间的传递依赖
-
绘制E-R图
(Entiy—Relationship)实体关系图，展示实体与实体之间的关联关系（一对一、一对多、多对一）
-
数据库建模
建模工具
- PowerDesigner
- PDman
- 三线图
-
建库建表
编写SQL指令创建数据库、数据表
-
添加测试数据，SQL测试
10.2 设计案例
图书管理系统（借书）
1、数据实体
- 学生
- 图书
- 类别
- 借书记录
- 管理员
2、提取实体的数据项
- 学生（）
- 图书（）
- 类别
- 借书记录
- 管理员
3、三范式
-
第一范式：要求数据表中的字段不可再分
-
第二范式：不存在非关键字段对关键字段的部分依赖
-
第三范式：不存在非关键字段之间的传递依赖
4、绘制E-R图
5、数据库建模
E-R图实际上是建模的一种
6、建库建表
7、测试
十一、JDBC
JDBC(Java Database connecivity) Java连接数据库的规范(标准),可以使用java语言连接数据库完成CRUD操作
SUN公司为了简化开发人员（对数据库统一）的操作，提供了一个（java操作数据库的）规范，俗称 JDBC
-
✨JDBC的组成部分——接口+驱动(需要自己导入)
-
![在这里插入图片描述]()
-
Java中定义了访问数据库的接口,可以为多种[关系型数据库]提供统一的访问方式
-
-
有数据库厂商提供的驱动实现类(Driver数据库驱动)
-
导入的两种方式
-
1、
-
2、在XML中配置
<!--mysql的驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency>
-
-
11.1、JDBC传统开发步骤
创建在 TestJdbc 类中，包含业务逻辑+数据库访问——耦合在一起，不利于维护——在MyBatis中进行优化
- 里面所用到的接口或类，是Java中定义的访问数据库的专用接口
1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
2.连接数据库 建立连接
Connection connection = DriverManager.getConnection(url, username, password);//路径、用户、密码
3.获取发送sql的对象
Statement statement = connection.createStatement();
4.编写执行SQL 语句
- 注意：
- DML语句增删改时,返回受影响的行数（int类型）
- 例如：增加一条数据，return 1
- DQL语句:查询时,返回结果数据（ResultSet结果集）——下一节会展开讲
- DML语句增删改时,返回受影响的行数（int类型）
//以编写和执行DML语句为例
int result = statement.executeUpdate("INSERT INTO bank(id,money) VALUES(4,2000)");//编写
5.处理结构
if(result !=0)
{
System.out.println("执行成功");
}else
{
System.out.println("执行失败");
}
6.释放资源
statement.close();
connection.close();
综合DML语句
public class TestJdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
//useUnicode=true&characterEncoding=utf-8 解决中文乱码
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库,代表数据库
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys","root","root");//路径、用户、密码
if(connection!=null)
{
System.out.println("数据库连接成功");
}
//3.向数据库发送SQL的对象Statement,PreparedStatement : CRUD
Statement statement = connection.createStatement();
//4、编写执行SQL 语句
//以编写执行DML语句为例
int result = statement.executeUpdate("INSERT INTO bank(id,money) VALUES(4,2000)");//编写
//5.处理结果
if(result !=0)
{
System.out.println("执行成功");
}else
{
System.out.println("执行失败");
}
//6.关闭连接，释放资源（一定要做） 先开后关
statement.close();
connection.close();
}
}
11.2、结果集 ResultSet
上面的第4步(编写执行SQL 语句)中，单独讨论执行DQL语句返回结果集的情况
- 区别
- 增删改时，调用 executeUpdate
- 查询时，调用 executeQuery
1、将查询到的数据存放到 结果集 resultSet虚拟表中
//上面的第4步，执行DQL时
ResultSet resultSet = statement.executeQuery(sql:"select * from users");
2、遍历resultSet虚拟表
上面的第5步,结果处理
-
数据行指针：初始位置在第一行；判断是否存在；存在则向下移动
-
获取当前字段行列信息
-
浙公网安备 33010602011771号