2.MySQL 数据表的关联关系2023-05-19

3.注解和反射2023-10-01 4.Java日期、时间专题合集2023-10-04

数据表的关联关系

数据与数据之间的关联关系

一对一
一对多
多对一
多对多

外键约束#

-- 【方式一】在创建表的时候，定义cid字段，并添加外键约束
constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)
-- constraint(关键字) FK_STUDENTS_CLASSES(约束名称) foreign key(cid)(外检约束+具体字段) references classes(class_id)(关联的表的具体字段)

-- 【方式二】先创建表，再添加外键约束
alter table sutdent add constraint FK_STUDENT_CLASS foreign key(cid) references class(class_id);

外键约束-级联#

在添加外键时，设置级联修改和级联删除

-- 删除原有的外键
alter table student drop foreign key FK_STUDENT_CLASS;

-- 重新添加外键，并设置级联修改和级联删除
alter table student 
add constraint FK_STUDENT_CLASS foreign key(cid) references
class(class_id) on update cascade on delete cascade;

连接查询

-- 创建班级信息表和学生信息表

create table class(
	class_id int primary key auto_increment,
	class_name varchar(20) not null unique,
	class_remark varchar(200)
);
create table student(
	stu_num char(4) primary key,
	stu_name varchar(10) not null,
	stu_gender char(2) not null,
	stu_age int not null,
	cid int,
	constraint FK_STUDENT_CLASS foreign key(cid) references class(class_id) on update cascade on delete cascade
);

-- 插入数据

insert into class (class_name, class_remark) values ('Java', '.............');
insert into class (class_name, class_remark) values ('Python', '.............');
insert into class (class_name, class_remark) values ('JavaScript', '.............');
insert into class (class_name, class_remark) values ('C++', '.............');

insert into student (stu_name,stu_gender,stu_age) values ('张三','男', 18);
insert into student (stu_name,stu_gender,stu_age) values ('李四','男', 18);
insert into student (stu_name,stu_gender,stu_age) values ('王五','男', 18);
insert into student (stu_name,stu_gender,stu_age) values ('周六','女', 18);

join 分为三个操作

inner join
left join
right join

内连接#

select ... from tableName1 inner join tableName2 on 匹配条件 [where 条件];

笛卡尔积

笛卡尔积（A集合&B集合）：使用A中的每个记录依次关联B中的每个记录，笛卡尔积的总数=A总数*B总数

内连接条件

使用on设置两张表连接查询的匹配条件

-- 使用where设置过滤条件，先生成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select * from student inner join class where student.cid = class.class_id;

-- 使用on设置连接查询条件，先判断连接条件是否成立，如果成立两张表的数据组合生成一条结果记录
select * from student inner join class on student.cid = class.class_id;

左连接#

左连接：显示左表中的所有数据，如果在有右表中存在与左表记录满足匹配条件的数据，则进行匹配；如果右表不存在匹配数据，则显示为Null

-- 语法
select * from leftTable left join rightTable on 匹配条件 [where 条件];

-- 左连接：显示左表中的所有数据
select * from student left join class on student.cid = class.class_id;

右连接#

同左连接

数据表别名#

-- 只修改列名
alter table tableName rename column oldName to newName;

-- 别名
select student.name,class.name
from student
inner join class
on student.cid = class.class_id;

select s.name,c.name
from student s
inner join class c
on s.cid = c.class_id;

嵌套查询/子查询#

子查询——先进行一次查询，第一次查询的结果作为第二次查询的条件/源（第二次查询是基于第一次的查询结果来进行的）

传统方式

-- 1.查询java班的班级编号
select class_id from class where class_name='java2023';
-- 2.查询此班级编号下的学生信息
select * from student where cid=1;

-- 关键字 union 可作连接

子查询 - 单行单列

-- 如果子查询返回的是一个值（单列单行），条件可以直接使用关系运算符（= != ...）
select * from student
where cid = (
    select class_id
    from class
    where class_name='java2023'
);

子查询返回多个值 - 多行单列

-- 如果子查询返回的结果是多个值（单行多列），条件使用 in / not in
select * from student where cid in (select class_id from class where class_name like 'java%');

子查询返回多个值 - 多行多列

-- 多条件查询：
select * from student where cid=1 and stu_gender='男';

-- 子查询:先查询cid=1班级中的所有学生信息，将这些信息作为一个整体虚拟表（多行多列），再基于这个虚拟表查询性别为男的学生信息（‘虚拟表’需要别名）
select * from (select * from student where cid=1) s where s.stu_gender='男';

存储过程

创建存储过程#

-- 语法[参数部分，与java类似，可以定义参数，也可以不定义参数]:
create procedure <proc_name>([in/out args])
begin
	-- SQL
end;

存储过程中变量的使用#

存储过程中的变量分为两种：局部变量和用户变量

定义局部变量

局部变量：定义在存储过程中的变量，只能在存储过程内部使用

局部变量定义语法

-- 局部变量要定义在存储过程中，而且必须定义在存储过程开始
declare <attr_name> <type> [default value];

局部变量定义示例：

create procedure proc_test(in a int, out r int)
begin
	declare x int default 0;
	declare y int default 1;
	set x = a*a;
	set y = a/a;
	set r = x+y;
end;

定义用户变量

用户变量：相当于全局变量，定义的用户变量可以通过select @attrName from dual进行查询

-- 用户变量会存储在mysql数据库的数据字典中（dual）
-- 用户变量定义使用set关键字直接定义，变量名要以@开头

set @n=1;
call proc_test(6,@n);
select @n from dual;

set @num = 0;
create procedure pro_test2(out n int)
begin
	select count(stu_name) into n from student;
end;

call pro_test2(@num);
select @num from dual;

存储过程的参数#

mysql存储过程的参数共三种：in / not / innot

IN 输入参数

输入参数：在调用存储过程中传递数据给存储过程的参数（在调用的过程必须为具有实际值的变量或者字面值）

-- 创建存储过程：添加学生信息
create procedure pro_test4(in snum char(8),in sname varchar(20),in gender char(2),in age int,in cid int,in remark varchar(255))
begin
	insert into student(stu_num, stu_name, stu_gender, stu_age, cid, remark)
	values(snum, sname, gender, age, cid, remark);
end;

call proc_test4('2013','青凤','男','aaa');

OUT 输出参数

输出参数：将存储过程中产生的数据返回给过程调用者，相当于java方法的返回值，但不同的是一个存储过程可以有多个输出参数

-- 创建存储过程，根据学生学号查询学生姓名
create procedure pro_test5(in snum char(8),out sname varchar(20))
begin
	select stu_name into sname from students where stu_num=snum;
end;

set @name='';
call proc_test5('2013',@name);
select @name from dual;

INOUT 输入输出参数

注意：此方式不建议使用，一般输入就用 in 输出就用 out，此参数代码可读性低，容易出错

create procedure pro_test6(inout str varchar(20))
begin
	select stu_name into str from students where stu_num=str;
end;

set @name='2013';
call proc_test5(@name);
select @name from dual;

存储过程中的流程控制#

分支语句

if-then-else

-- 单分支：如果条件成立，则执行SQL
if conditions then
	--SQL
end if;

-- 如果参数a的值为1，则添加一条班级信息
create procedure proc_test8(in a int)
begin
	if a=1 then
		insert into class(class_name, remark) values ('java','test');
	end if;
end;



-- 双分支：如果条件成立执行SQL1，否则执行SQL2
if conditions then
	-- SQL1
else
	-- SQL2
end if;

-- 如果参数a的值为1，则添加一条班级信息，否则添加一条学生信息
create procedure proc_test8(in a int)
begin
	if a=1 then
		insert into class(class_name, remark) values ('java','test');
	else
		insert into class(class_name, remark) values ("Python",'test2')
	end if;
end;

case

create procedure proc_test9(in a int)
begin
	case a
		when 1 then
			-- SQL1	如果a的值为1，执行SQL1
		when 2 then
			-- SQL2
		else
			-- SQL
	end case;
end;	



create procedure proc_test3(in a int)
begin	
	case a
		when 1 then
			insert into student (stu_name,stu_gender) values ('ttt','男');
		when 2 then
			insert into student (stu_name,stu_gender,stu_age,cid) values ('2222','男',20,1);
		when 3 then
			insert into student (stu_name,stu_gender,stu_age,cid) values ('3333','男',20,1);
		when 4 then
			insert into student (stu_name,stu_gender,stu_age,cid) values ('4444','男',20,1);
		else
			insert into student (stu_name,stu_gender,stu_age,cid) values ('666','男',20,1);
	end case;
end;

call proc_test3(5);

循环语句

while

-- while

create procedure proc_test4(in a int)
begin
	declare i int;
	set i = 0;
		while i<a do
			insert into student (stu_name, stu_gender, stu_age) values (concat('小明',i),'男',i);
			set i = i + 1;
		end while;
end;

call proc_test4(100);

repeat
loop

存储过程管理#

查询存储过程

-- 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='student';

-- 查询存储过程的创建细节
show create procedure student.proc_test1;

修改存储过程

alter procedure <proc_name> 特征1 [特征2 ...]

存储过程的特征参数

contains sql
no sql
reads sql data
modifies sql data
....
alter proedure proc_test1 reads sql data;

删除存储过程

-- 删除存储过程
-- drop		删除数据库中的对象 数据库 数据表 列 存储过程 视图 触发器 索引
-- delete	删除数据表中的数据
drop procedure proc_test1;

游标#

略

触发器

介绍#

触发器，可以监测，如果对数据表中的数据执行DML操作是可自动触发这个SQL片段的执行，无需手动调用

在mysql中只有执行insert,delete,update操作才能触发触发器的执行

创建触发器#

-- 语法
create trigger tri_name
<before|after>				-- 定义触发时机
<insert|delete|update>		-- 定义DML类型
on <table_name>
for each row				-- 声明为行级触发器（只要操作一条记录就触发触发器执行一次）
sql_statement				-- 触发器操作


-- 创建触发器：当学生信息表发生添加操作时，则向日志信息表中记录一条日志
create trigger tri_test1
after insert on stdent
for each row
insert into stulogs(time,log_text) values(now(),concat('添加',new.stu_num,'学生信息'))

查看触发器#

show triggers;

NEW和OLD#

触发器用于监听对数据表中数据的insert, delete, update操作，在触发器中通常处理一些DML的关联操作；其中可以使用NEW和OLD关键字在触发器中获取这个触发器的DML操作的数据

NEW：在触发器中用于获取insert操作添加的数据、update操作修改后的记录
OLD：在触发器中用于获取delete操作删除前的数据、update操作修改前的数据

NEW#

insert操作中：NEW表示添加的新纪录

create tigger tri_test1
after insert on student
for each row
insert info stulogs(time, text) values(now(), concat('添加',NEW.stu_num,'学生信息'))

update操作中：NEW表示修改后的数据

-- 创建触发器：在监听update操作的触发器中，可以使用NEW获取修改后的数据
create trigger tri_test3
after update on student
for each row
insert into stulogs(time, text) values (now(), concat('修改学生信息',new.stu_num,new.stu_name));

OLD#

delete

create trigger tri_test3
after delete on student
for each row
insert into stulogs(time, text) values (now(), concat('删除',new.stu_num,'信息');

update

create trigger tri_test3
after update on student
for each row
insert into stulogs(time, text) values (now(), concat('将学生信息',new.stu_name,'更改为：',old.stu_name);

视图

概念#

视图，就是有数据库中一张表或者多张表根据特定的条件查询得出数据构造成得虚拟表

作用#

安全性
简单性

创建视图#

语法

create view <view_name>
as
select_statement

实例

实例1

-- 创建视图实例1：将学生表中性别为男的学生生成一个视图
create view view_test1
as
select * from students where stu_gender='男';

-- 查询视图
select * from view_test1;

实例2

视图数据的特性#

视图是虚拟表，查询视图的数据是来源数据表的，当对视图进行操作时，对原数据表中的数据是否有影响呢？

查询操作： 如果在数据表中添加了新的数据，而且这个数据满足创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据，当删除原表中满足查询条件的数据时，也会从视图中删除

新增数据：如果在视图中添加数据，数据也会被添加到原数据表

删除数据：如果从视图删除数据，数据也将从原表中删除

修改数据：如果通过修改数据，则也将修改原数据表中的数据

查询视图结构#

-- 查询视图结构
desc view_test;

修改视图#

-- 方式一
or replace(替换)

create or replace view view_test
as
select * from student where stu_gender='女';


-- 方式二
alter(直接进行修改)
alter view view_test
as
select * from student where stu_gender='女';

删除视图#

删除数据表时会同时删除数据表中的数据，删除视图时不会影响原数据表中的数据

-- 删除视图
drop view view_test;

索引

略略略

posted @ 2023-05-19 21:33 sroot 阅读(162) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MySQL 基础

· Python 基础

· MySQL 笔记二

· python开发基础篇:十:数据库

· MySQL操作数据表

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？

公告

七里香 - 周杰伦

00:00 / 00:00

An audio error has occurred, player will skip forward in 2 seconds.

1 七里香周杰伦
2 夜曲周杰伦
3 花海周杰伦
4 不能说的秘密周杰伦
5 借口周杰伦
6 Panroama X-Ray dog
7 Epic Music John Dreamer
8 This’sWhatYouCameFor Chave
9 Mine SLANDER
10 Shorts Imagine Dragons
11 Where is your love J.lisk
12 告白の夜 Ayasa
13 年少有为李荣浩
14 因为你，所以我五月天
15 楼顶上的小斑鸠队长
16 哪里都是你队长
17 11 队长
18 一万次悲伤(Live) 逃跑计划
19 起风了买辣椒也用券
20 下女的圣经 Bo Peep

昵称： sroot
园龄： 1年9个月
粉丝： 2
关注： 7

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类 (11)

随笔档案 (13)

阅读排行榜

评论排行榜

1. Javascript 上(1)

加载中...

科技教徒

MySQL 数据表的关联关系

数据表的关联关系

外键约束#

外键约束-级联#

连接查询

内连接#

左连接#

右连接#

数据表别名#

嵌套查询/子查询#

存储过程

创建存储过程#

存储过程中变量的使用#

存储过程的参数#

存储过程中的流程控制#

存储过程管理#

游标#

触发器

介绍#

创建触发器#

查看触发器#

NEW和OLD#

NEW#

OLD#

视图

概念#

作用#

创建视图#

视图数据的特性#

查询视图结构#

修改视图#

删除视图#

索引

公告

搜索

常用链接

最新随笔

我的标签

合集 (5)

随笔分类 (11)

随笔档案 (13)

阅读排行榜

评论排行榜