数据库内容补充

最新推荐文章于 2024-10-02 00:27:17 发布

weixin_45979978

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量105

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/weixin_45979978/article/details/128519660

版权

这篇博客详细介绍了MySQL数据库的管理，包括行列转换、视图的创建与使用、事务处理的ACID特性、触发器和存储过程的定义及删除，以及MySQL编程中的条件语句和变量操作。还探讨了MySQL的核心内容，如SQL语句、数据库设计、JDBC、事务和存储引擎，特别是事务的隔离级别及其对并发问题的影响。

摘要由CSDN通过智能技术生成

行列转换

如有现有表score

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z9eSiw2T-1672632708405)(C:\Users\lenovo\AppData\Roaming\Typora\typora-user-images\1672194296007.png)]

转换为

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9oMQWevL-1672632708407)(C:\Users\lenovo\AppData\Roaming\Typora\typora-user-images\1672194268651.png)]

select stu_id,stu_name,
sum(if(c_name = '高等数学',cj,null)) as '高等数学',
sum(if(c_name = '思修',cj,null)) as '思修',
sum(if(c_name = '大学英语',cj,null)) as '大学英语',
sum(if(c_name = '大学体育',cj,null)) as '大学体育'
from score
group by stu_id

视图View

视图可以当做数据库中的一个临时表，保存一些较为复杂的查询后的结果。

之后可以直接通过该视图查询数据，不需要再次编写复杂的sql语句。

视图同时可以隐藏一些查询细节，定制查询数据

创建视图

create view 视图名 as 查询的sql语句;

使用视图

select * from 视图名;

修改视图中的数据，会直接修改原始表中的数据。

删除视图

drop view 视图名;

-- 创建视图
create view myview as select c.c_id,c_name,avg(cj) as 'avg_cj' from score s2,course c where s2.c_id = c.c_id group by c_name

-- 视图可以当做表使用
select t_name,c_name from teacher t1,teach t2,(
select * from myview where avg_cj=(select max(avg_cj) from myview)) t where t1.t_id = t2.t_id and t2.c_id = t.c_id

-- 删除视图
drop view myview;

事务transation

事务是由一组sql语句组成的执行单元，这些sql之间一般都相互依赖。

这个执行单元要么全部执行，要么全部不执行。

转账

1、select *from 表 where id=1 and money>=1000

2、updete 表 set money = money-1000 where id=1

3、updete 表 set money = money+1000 where id=2

以上所有sql组成了一个转账的事务

事物的特性ACID

原子性Atomicity

事务是最小的执行单元

一致性Consistency

事务执行前后必须让所有数据保持一致状态。(总体数据守恒)

隔离性Isolation

事务并发时互相隔离，互补影响

持久性Durability

事物一旦提交，对数据的改变是永久的

事物的使用

提交：commit

回滚：rollback

mysql中事务是默认自动提交的。

查看事务自动提交开启状态：select @@autocommit 1表示开启状态；0表示关闭自动提交

设置事务不自动提交：set @@autocommit=0

如果关闭了事务自动提交，在执行某个事物途中，如果出错可以使用rollback进行回滚，让数据回到事务执行之前的状态。

如果不出错通过commit命令提交事务，一旦提交事务，无法进行回滚。

手动提交/回滚事务

1、关闭事务自动提交：set @@autocommit=0

2、开启事务：start transaction;

3、事务要执行的sql;

4、没有提交之前，如果要回滚，使用rollback;

5、如果要提交，使用commit；一旦提交成功，无法rollback

事务并发可能出现的问题

系统在同一时刻同时执行多个事务时，称为事物的并发。

事务并发会有可能出现以下问题。

问题	描述
脏读	事务A读取到事务B还没有提交的数据
不可重复读	事务A中如果要读两次数据，在这期间，事务B对事物进行了修改并提交，导致事务A读取两次的情况不一致
幻读	事务A读取1-10之间的数据，假如只有id为2和5的数据，在读取期间，事务B添加了一条id为3的数据，导致事务A多读到了事务B中的数据

事务的隔离级别

为了防止事务并发时出现以上问题，数据库中设计了集中事务与事务之间的隔离级别。

隔离级别	能否出现脏读	能否出现不可重复读	能否出现幻读
Read Uncommitted未提交读RU	会	会	会
Read Committed已提交读RC(Oracle默认)	不会	会	会
Repeatable Read可重复读RR(Mysql默认)	不会	不会	会
Serializable可序列化	不会	不会	不会

查看事务隔离级别

select @@transaction_isolation

设置事务隔离级别

set [session|global] transaction isolation level [read uncommitted|read commited|repeatable read|serializable]

触发器trigger

如果要在更新某张表之前/之后，自动执行另一组sql时可以使用触发器实现。

如表A是客户表，表B是操作日志表，对表A进行更新操作时，将操作记录

创建触发器


create trigger 触发器名 
触发时机 触发操作 on 表 for each row 
begin 
	满足触发时机时自动执行的sql;
end

--创建操作日志表
create table log(
	log_id int not null primary key auto_increment,
	log_opt varchar(20) not null,
	log_time datetime not null
)

-- 在向customer表中添加一条记录后，自动在log表中添加一条记录
create trigger mytrigger
after insert on customer for each row 
begin 
	insert into log values(null,'插入的一条数据',now())
end

使用触发器

一旦创建成功触发器，无需可以调用，在执行相应的操作时，会自动执行触发器

-- 只对customer表做插入操作，会自动向log表中添加记录
insert into customer values(null,'测试插入','123123',0,null)

删除触发器

drop trigger 触发器名;

存储过程procedure

类似于java中的方法，定义一组用于完成特定功能的sql语句。

定义存储过程后，通过条用存储过程名，就可以执行定义时的内容。

存储过程可以有参数。

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();

-- 调用输入型参数存储过程
call 存储过程名('实参');

-- 调用输出型参数存储过程
call 存储过程名(@变量);

-- 调用输入输出型参数存储过程
set @变量
call 存储过程名(@变量);

定义存储过程

create procrdure 存储过程名([参数类型 参数名 参数数据类型]) -- 参数类型分为输入型/输出型/输入输出型
begin
	sql语句;
end

无参数

create procedure 存储过程名
begin
	sql语句;
end

-- 创建存储过程，查询每本书的书名、作者、类型
CREATE PROCEDURE myproce1() 
BEGIN
	select book_name,book_author,type_name
	from book_info bi,book_type bt
	where bi.type_id = bt.type_id;
END
-- 调用存储过程
call myproce1;

输入型参数

create procedure 存储过程名(in 形参名 数据类型)
begin
	sql语句
end

-- 根据图书类型查询该类型下的所有图书
CREATE PROCEDURE myproce2(IN lx VARCHAR(20))
BEGIN
	select *
	from book_info bi,book_type bt
	where bi.type_id = bt.type_id and type_name = lx;
END

-- 调用
call myproce2('小说')

输出型参数

类似于java中的方法有返回值

create procedure 存储过程名(out 形参名 数据类型)
begin
	sql语句
	-- 通常需要将查询出的结果通过into赋值给形参
end

-- 根据作者查询图书数量
CREATE PROCEDURE myproce3(in zz varchar(20),out count int)
BEGIN
-- 将查询的结果into给参数count中
	select count(book_id) into count
	from book_info where book_author = zz;
END

-- 调用存储过程，@x表示将存储过程的输出型参数保存到变量x中
call myproce3('金庸',@count)
-- 查询参数中保存的数据
select @count

输入输出型参数

create procedure 存储过程名(inout 形参名 数据类型)
begin
	sql语句
end

-- 查询书名中带有指定文字的图书名、作者和类型
CREATE PROCEDURE myproce4(INOUT keyword VARCHAR(20))
BEGIN
	select *
	from book_info bi,book_type bt 
	where 
	bi.type_id=bt.type_id and 
	book_name like concat('%',keyword,'%');
END
-- 调用存储过程
set @keyword='龙';
call myproce4(@keyword);
select @keyword;

删除存储过程

drop procedure 存储过程名;

Mysql编程

在定义存储过程中，可以定义变量，使用流程控制语句

定义变量

create procedure 存储过程名();
begin
	-- declare 变量名 数据类型
	declare num int;
	declare name varchar(20);
end

给变量赋值

create procedure 存储过程名();
begin
	declare num int;
	declare name varchar(20);
	-- 给num赋值
	-- select 字段/值 into 变量 [from 表];
	select 123 into num;
	select book_name into name from ook_info where book_id=1;
end

读取变量值

create procedure 存储过程名();
begin
	declare num int;
	declare name varchar(20);
	select 123 into num;
	select book_name into name from ook_info where book_id=1;
	
	-- select 变量
	select num;
	select name;
end

-- 创建存储过程，查询所有图书总数，保存到变量中
create procedure myproce5()
begin
	-- 定义变量
	declare sum_num int;
	-- 给变量赋值
	select sum(book_num) into sum_num from book_info;
	-- 打印变量值
	select sum_num;
end

call myproce5()

条件语句

单分支if语句

if 条件
then
	满足条件时执行的sql;
end if;

-- 根据作者查询图书库存，如果不足1000，输出‘库存不足1000’
create procedure myproce6(in zz varchar(20))
begin
	-- 定义变量保存根据作者查询到的图书数量
	declare num int;
	select sum(book_num) into num from book_info where book_author=zz;
	if num<300
	then
		select '库存不足300';
	end if;
end

call myproce6('金庸')

双分支语句

if 条件
then
	满足条件时执行的sql;
else
	不满足条件时执行的sql;
end if;

-- 根据图书类型查询图书数量，如果不足3，输出不足3本图书，如果足够输出详情
create procedure myproce7(in lx varchar(20))
begin
	declare count int;
	select count(book_id) into count from book_info bi,book_type bt where bi.type_id = bt.type_id and type_name = lx;
	if count<3
	then 
		select '不足3本图书';
	else
		select * from book_info bi,book_type bt where bi.type_id = bt.type_id and type_name = lx;
	end if;
END

call myproce7('小说')

case语句

CASE 变量
	WHEN 值 THEN
		满足该值时执行的sql;
	WHEN 值 THEN
		满足该值时执行的sql;
	ELSE
		都不满足值时执行的sql;
END CASE;

-- case语句
create procedure myproce8(in num int)
begin
	case num
	    when 1 then
				select '1';
			when 5 then 
				select '5';
			else
				select '都不是';
	end case;
end
call myproce8(6);

while

while 变量 do
	满足条件时执行的sql;
end while;

-- 添加10个客户
create procedure myproce9()
begin
	-- 定义循环变量
	declare num int;
	-- 初始化循环变量
	select 1 into num;
	-- while循环
	while num<10 do
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		-- 更新循环变量
		set num=num+1;
	end while;
end

call myproce9();

repeat循环

REPEAT
	循环体；
UNTIL 条件 END REPEAT;

-- repeat 循环
create procedure myproce10()
begin
	declare num int;
	select 50 into num;
	repeat
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		set num = num+1;
	until num=60 end repeat;
end
-- 调用
call myproce10()

loop循环

循环名: LOOP
	循环体

	IF 条件 THEN
		LEAVE 循环名; 
	END IF; 
END LOOP;

-- loop 循环
create procedure myproce11()
begin
	declare num int;
	select 10 into num;
	test:loop
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		set num = num-1;
	  if num=0 then leave test;
	  end if;
	end loop;
end
-- 调用
call myproce11()

MySQL核心内容

SQL语句

数据库和数据表的创建、修改、删除
数据完整性(约束)
增删改查CURD
函数
多表查询、嵌套查询

数据库设计

实体关系模型(ER)
ER图
范式

JDBC

连接MySQL所需的jar文件
- 普通的java工程需要手动导入.jar文件
- maven项目需要使用依赖自动导入.jar文件

MySQL驱动名

//mysql5.5之前版本
Class.froName("com.mysql.jdbc.Driver")
//mysql5之后版本
Class.froName("com.mysql.cj.jdbc.Driver")

连接数据库的字符串

String url="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai";
String username="root";
String password="root";

事务

事物的概念和特性
事物的隔离级别
事务并发时出现的问题
- 脏读
- 不可重复读
- 幻读
事物的隔离级别
- read uncommitted 可能出现脏读、不可重复读和幻读问题
- read committed 解决了脏读问题，可能出现不可重复读和幻读问题
- repeatable read MySQL默认 解决了脏读和不可重复读，可能出现幻读问题
- serializable 解决了脏读、不可重复读和幻读问题，效率最低

存储引擎

MySQL5.5版本之前，默认使用MyIsam存储引擎，不支持事务
ar文件

MySQL驱动名

//mysql5.5之前版本
Class.froName("com.mysql.jdbc.Driver")
//mysql5之后版本
Class.froName("com.mysql.cj.jdbc.Driver")

连接数据库的字符串

String url="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai";
String username="root";
String password="root";

事务

事物的概念和特性
事物的隔离级别
事务并发时出现的问题
- 脏读
- 不可重复读
- 幻读
事物的隔离级别
- read uncommitted 可能出现脏读、不可重复读和幻读问题
- read committed 解决了脏读问题，可能出现不可重复读和幻读问题
- repeatable read MySQL默认 解决了脏读和不可重复读，可能出现幻读问题
- serializable 解决了脏读、不可重复读和幻读问题，效率最低