数据库从入门到出门之三

2024-01-13 07:48
文章标签 入门 数据库 之三 出门

本文主要是介绍数据库从入门到出门之三，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

下面要说高级查询了。

 

目录

一：高级查询-多表查询

一：关联查询（连接查询）

1.内连接

2.外连接

3.自然连接

4.自连接

二：子查询（嵌套查询）

1.分类

三.事务

那什么是事务？

四.存储程序

1.存储过程

2.存储函数 

3.触发器

 

一：高级查询-多表查询

一：关联查询（连接查询）

1.内连接

select ename,dname from emp,dept where emp.deptno = dept.deptno;
select * from emp inner join dept on emp.deptno = dept.deptno;
select * from emp inner join dept using(deptno); 通用列字段名称必须一致;去除重复字段

关联表中都出现的字段值最终才能出现在结果集中。

内连接与连接顺序无关，没有什么主从表之分。  这点说的主从表是后边左右连接就有区别了。

2.外连接

有主从表之分，与连接顺序有关

依次遍历主表中记录，与从表中记录进行匹配；如果匹配到则连接展示，否则以null 填充。

左右连接  left/right  join 。。。on

select * from emp left join dept on emp.deptno=dept.deptno;

3.自然连接

自然连接一定是等值连接，但等值连接不一定是自然连接。

select * from emp natural join dept;

4.自连接

就是 和自己查询出来的结果作为另外一张表进行关联。

二：子查询（嵌套查询）

嵌套查询，将一个查询结果当做另一个查询的条件或者结果集。

子查询最接近思考方式，最自然的查询。

1.分类

单行子查询：子查询的返回结果只有一条记录。

select * from emp where sal > (select sal from emp where ename = 'scott');

多行子查询：子查询返回结果有多条记录。

select * from emp where sal in ( select distinct sal from emp where deptno = 20) and deptno <>20;

any  all

=any:相当于in    >any:大于最小值    <any:小于最大值

>all:大于最大值   <all小于最小值

来几个Demo：

#查询20号部门除了工资最高员工的员工信息
select * from emp where sal<any(select sal from emp where deptno=20);
#查询大于20号部门的平均工资的20号部门的员工信息
select * from emp where sal>(
select AVG(sal) from emp where deptno=20) and deptno<>20;

联合查询（or 会导致索引失效） or 的替代

#联合查询 union 可以去重  union all 不去重  索引 使用 or 失效 所以要用 union 
#联合的结果集必须一致 否则没办法使用 union的
select * from emp where deptno=20
union
select * from emp where sal>2000;

三.事务

存储引擎

数据库底层软件组织，DBMS通过存储引擎实现对数据库的操作，mysql的核心就是存储引擎。

mysql 可以设置多种存储引擎，不同的存储引擎在索引、存储以及索引策略上是不同的。

mysql 5.5 之前，采用myisam 存储引擎，支持全文搜索，不支持事务。

5.5 之后，默认采用innodb 存储引擎，支持事务以及行级锁。

行级锁是什么呢，其实就类似pyhton的交互式编程。

这是区别：

 

那什么是事务？

事务保证数据一致性，一组DML操作要么同时成功，要么同时失败。

最好的例子就是：转账，不能转过去没扣钱吧，或是扣钱了却没转账吧。

  事务的ACID特性：

1.原子性：放在同一事务中的一组操作不可分割。

2.一致性：事务执行前后整体的状态保持不变。

3.隔离性：并发事务之间互相不能干扰。

4.持久性：事务执行之后将永久化到数据库。

事务语法：

#查看mysql 的事务自动提交

show variables like 'autocommit'；    你会看到是 on 

当然了，你可以修改

#修改自动提交

set autocommit = 0;

#显式开启事务(begin)   就是定义事务的开始 
start transaction;  #在同一个事务
update account set money = money + 100 where name = 'zs';
update account set money = money - 100 where name = 'ls';#手动提交或回滚事务	 事务的结束，不提交或者回滚不会永久化到数据库
#commit;	
rollback;

 

像这种 在内存中的数据在并发时很容易出现问题，那么并发事务会出现什么问题呢？

脏读:

一个事务执行范围内读到了另一事务未提交的数据。

不可重复读:

一个事务在只读范围内,被另一事务修改并提交事务，导致多次读取事务不一致的问题。

幻读(虚读):

一个事务只读范围内,被另一事务删除或者添加数据，导致读取数据不一致问题。

 

解决以上问题呢，有一个事务隔离级别：

#事务 隔离级别 ：
#1.读未提交 不能处理任何一个问题 
#2.读已提交 解决脏读问题 
#3.可重复读 解决脏读和不可重复读的问题   
#4.串行化  解决所有问题 
#查看事务隔离级别
select @@tx_isolation
#修改  
set session

这些个隔离级别的采用还要牵扯到效率问题，最终mysql默认采取的是 可重复读。

四.存储程序

#存储程序 
  #运行于服务器端程序 
    #优点 1.简化开发 2.执行效率高  
    #缺点 1.程序保存在服务器端 占用服务器端资源  2.数据迁移 3.调试编写程序不方便 
#分类
#1.存储过程 
#2.存储函数 
#3.触发器 

1.存储过程

关于存储过程来个Demo吧

#存储过程#服务器端运行的可重复调用的sql代码块，包含名称，输入输出参数，以及一组sql 
#创建存储过程 #查询所有的用户名称和部门名称delimiter //;#修改结束标志
create PROCEDURE query_emp()
begin#sqlselect dname,ename from emp,dept where emp.deptno=dept.deptno;
end;
#运行
call query_emp();#参数的传入 #根据部门编号查询员工 delimiter //;create procedure find_emp_no1(in dno int)beginselect * from  emp where deptno=dno;end;call find_emp_no(10);
#参数的模式
#in 传入模式 默认都是传入模式 
#out 传出模式
#inout 传入传出模式 #根据员工编号查询员工姓名，传出来姓名 
delimiter //;
create procedure finNameByNo(eno int,out v_name VARCHAR(20))
beginselect ename into v_name from emp where empno=eno;
end;
call finNameByNo(7369,@v_name);
select @v_name;
#根据员工姓名查询员工职位
delimiter //;
create procedure findJob(inout name_job varchar(20))
beginselect job from emp where ename=name_job;end;set @name_job='smith';call findJob(@name_job);select @name_job;#if (成绩分级)delimiter //;create procedure score_level(score int)begin#变量的声明declare v_level varchar(20);if score>=80 then #变量赋值set v_level ='A';elseif score>=60 thenset v_level='b';elseset v_level='c';end if;select v_level;
end;#循环 ：while...do..end while/loop...end loop/repeat..until...end repeat #从 1 加到 100delimiter //;create procedure calc()begindeclare i int;declare sum int;set i=1;set sum=0;while i<=100 doset sum=sum+i;set i=i+1;end while;select sum;end;delimiter //;create procedure calc()begindeclare i int;declare sum int;set i=1;set sum=0;lip:loop set sum=sum+i;set i=i+1;if i>100 then leave lip;end if;end loop;select sum;end;call calc();delimiter //;create procedure calc1()begindeclare i int;declare sum int;set i=1;set sum=0;repeatset sum=sum+i;set i=i+1;until i>100 #不能有分号 end repeat;select sum;end;call calc1;delimiter //;create procedure wtf()begindeclare i int;declare sum int;set i=1;set sum=0;while i<100  doset sum=i+sum;set i=i+1;end while;select sum;end;delimiter //;create procedure wtf1()begindeclare i int;declare sum int;set i=1;set sum=0;lop:loop set sum=sum+i;set i=i+1;
if i>100 then 
leave lop;
end if;
end loop;
select sum;
end;call wtf1()delimiter //;create procedure wtf2()begindeclare i int;declare sum int;set i=1;set sum=0;repeat

#存储函数（函数）

2.存储函数
 

#存储在服务器端，有返回值，函数作为sql 一部分 ，注意必须有返回值的，我刚开始在网上找了一大圈，

以为能写像void 的函数呢。不多说 上demo

#存储函数（函数）#存储在服务器端，有返回值，函数作为sql 一部分 #根据用户编号查询姓名delimiter //;create function findNameByNo(eno int)returns varchar(20) #返回值类型 deterministic #确定的begindeclare v_name varchar(20);select ename into v_name from emp where empno=eno;return v_name;end;select findNameByNo(7788);

 #函数和存储过程区别
 #关键字不同 procedure function 
 #存储过程三种参数模式 实现数据输入输出 
 #函数有返回值 返回数据
 #存储过程可以作为独立个体执行，函数只能作为sql 的一部分运行 
 
 show VARIABLES like '%char%';
 #修改编码为 utf-8  在 my.ini  修改 
    

3.触发器

#触发器
   #本质也是存储程序，存储在服务器端
     #由事件（增删改）调用，不能传参
     create table user_bak select * from userinfo where 0;
     
     
     #创建触发器
     delimiter //;
     create trigger tri_user
     after delete
     on userinfo for each row 
     begin
        # old:删除的记录   new 
      insert into user_bak values(old.id,old.name,old.password,old.birthday); 
        end;
     delete from userinfo where id=1;

 

 

 

 

 

这篇关于数据库从入门到出门之三的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/600719。

相关文章

SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
SQL Server数据库死锁处理超详细攻略

SQL Server数据库死锁处理超详细攻略

《SQLServer数据库死锁处理超详细攻略》SQLServer作为主流数据库管理系统,在高并发场景下可能面临死锁问题,影响系统性能和稳定性,这篇文章主要给大家介绍了关于SQLServer数据库死... 目录一、引言二、查询 Sqlserver 中造成死锁的 SPID三、用内置函数查询执行信息1. sp_w
阅读更多...
Python中OpenCV与Matplotlib的图像操作入门指南

Python中OpenCV与Matplotlib的图像操作入门指南

《Python中OpenCV与Matplotlib的图像操作入门指南》：本文主要介绍Python中OpenCV与Matplotlib的图像操作指南,本文通过实例代码给大家介绍的非常详细,对大家的学... 目录一、环境准备二、图像的基本操作1. 图像读取、显示与保存 使用OpenCV操作2. 像素级操作3.
阅读更多...
Druid连接池实现自定义数据库密码加解密功能

Druid连接池实现自定义数据库密码加解密功能

《Druid连接池实现自定义数据库密码加解密功能》在现代应用开发中,数据安全是至关重要的,本文将介绍如何在​​Druid​​连接池中实现自定义的数据库密码加解密功能,有需要的小伙伴可以参考一下... 目录1. 环境准备2. 密码加密算法的选择3. 自定义 ​​DruidDataSource​​ 的密码解密3
阅读更多...
Maven项目中集成数据库文档生成工具的操作步骤

Maven项目中集成数据库文档生成工具的操作步骤

《Maven项目中集成数据库文档生成工具的操作步骤》在Maven项目中,可以通过集成数据库文档生成工具来自动生成数据库文档,本文为大家整理了使用screw-maven-plugin（推荐）的完... 目录1. 添加插件配置到 pom.XML2. 配置数据库信息3. 执行生成命令4. 高级配置选项5. 注意事
阅读更多...
在Java中基于Geotools对PostGIS数据库的空间查询实践教程

在Java中基于Geotools对PostGIS数据库的空间查询实践教程

《在Java中基于Geotools对PostGIS数据库的空间查询实践教程》本文将深入探讨这一实践,从连接配置到复杂空间查询操作,包括点查询、区域范围查询以及空间关系判断等,全方位展示如何在Java环... 目录前言一、相关技术背景介绍1、评价对象AOI2、数据处理流程二、对AOI空间范围查询实践1、空间查
阅读更多...
Python+PyQt5实现MySQL数据库备份神器

Python+PyQt5实现MySQL数据库备份神器

《Python+PyQt5实现MySQL数据库备份神器》在数据库管理工作中,定期备份是确保数据安全的重要措施,本文将介绍如何使用Python+PyQt5开发一个高颜值,多功能的MySQL数据库备份工具... 目录概述功能特性核心功能矩阵特色功能界面展示主界面设计动态效果演示使用教程环境准备操作流程代码深度解
阅读更多...
MySQL数据库实现批量表分区完整示例

MySQL数据库实现批量表分区完整示例

《MySQL数据库实现批量表分区完整示例》通俗地讲表分区是将一大表,根据条件分割成若干个小表,：本文主要介绍MySQL数据库实现批量表分区的相关资料,文中通过代码介绍的非常详细,需要的朋友可以参考... 目录一、表分区条件二、常规表和分区表的区别三、表分区的创建四、将既有表转换分区表脚本五、批量转换表为分区
阅读更多...
MySQL Workbench工具导出导入数据库方式

MySQL Workbench工具导出导入数据库方式

《MySQLWorkbench工具导出导入数据库方式》：本文主要介绍MySQLWorkbench工具导出导入数据库方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝... 目录mysql Workbench工具导出导入数据库第一步 www.chinasem.cn数据库导出第二步
阅读更多...
Mysql数据库中数据的操作CRUD详解

Mysql数据库中数据的操作CRUD详解

《Mysql数据库中数据的操作CRUD详解》：本文主要介绍Mysql数据库中数据的操作（CRUD）,详细描述对Mysql数据库中数据的操作（CRUD）,包括插入、修改、删除数据,还有查询数据,包括... 目录一、插入数据（insert）1.插入数据的语法2.注意事项二、修改数据（update）1.语法2.有
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2