数据库从入门到出门之三

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。

相关文章

Python调用Orator ORM进行数据库操作

Python调用Orator ORM进行数据库操作

《Python调用OratorORM进行数据库操作》OratorORM是一个功能丰富且灵活的PythonORM库,旨在简化数据库操作,它支持多种数据库并提供了简洁且直观的API,下面我们就... 目录Orator ORM 主要特点安装使用示例总结Orator ORM 是一个功能丰富且灵活的 python O
阅读更多...
使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置（可选）启动服务应用场景
阅读更多...
使用Navicat工具比对两个数据库所有表结构的差异案例详解

使用Navicat工具比对两个数据库所有表结构的差异案例详解

《使用Navicat工具比对两个数据库所有表结构的差异案例详解》：本文主要介绍如何使用Navicat工具对比两个数据库test_old和test_new,并生成相应的DDLSQL语句,以便将te... 目录概要案例一、如图两个数据库test_old和test_new进行比较：二、开始比较总结概要公司存在多
阅读更多...
MySQL数据库函数之JSON_EXTRACT示例代码

MySQL数据库函数之JSON_EXTRACT示例代码

《MySQL数据库函数之JSON_EXTRACT示例代码》：本文主要介绍MySQL数据库函数之JSON_EXTRACT的相关资料,JSON_EXTRACT()函数用于从JSON文档中提取值,支持对... 目录前言基本语法路径表达式示例示例 1: 提取简单值示例 2: 提取嵌套值示例 3: 提取数组中的值注意
阅读更多...
查询SQL Server数据库服务器IP地址的多种有效方法

查询SQL Server数据库服务器IP地址的多种有效方法

《查询SQLServer数据库服务器IP地址的多种有效方法》作为数据库管理员或开发人员,了解如何查询SQLServer数据库服务器的IP地址是一项重要技能,本文将介绍几种简单而有效的方法,帮助你轻松... 目录使用T-SQL查询方法1：使用系统函数方法2：使用系统视图使用SQL Server Configu
阅读更多...
SQL Server数据库迁移到MySQL的完整指南

SQL Server数据库迁移到MySQL的完整指南

《SQLServer数据库迁移到MySQL的完整指南》在企业应用开发中,数据库迁移是一个常见的需求,随着业务的发展,企业可能会从SQLServer转向MySQL,原因可能是成本、性能、跨平台兼容性等... 目录一、迁移前的准备工作1.1 确定迁移范围1.2 评估兼容性1.3 备份数据二、迁移工具的选择2.1
阅读更多...
Python中连接不同数据库的方法总结

Python中连接不同数据库的方法总结

《Python中连接不同数据库的方法总结》在数据驱动的现代应用开发中,Python凭借其丰富的库和强大的生态系统,成为连接各种数据库的理想编程语言,下面我们就来看看如何使用Python实现连接常用的几... 目录一、连接mysql数据库二、连接PostgreSQL数据库三、连接SQLite数据库四、连接Mo
阅读更多...
Oracle数据库如何切换登录用户(system和sys)

Oracle数据库如何切换登录用户(system和sys)

《Oracle数据库如何切换登录用户(system和sys)》文章介绍了如何使用SQL*Plus工具登录Oracle数据库的system用户,包括打开登录入口、输入用户名和口令、以及切换到sys用户的... 目录打开登录入口登录system用户总结打开登录入口win+R打开运行对话框，输php入：sqlp
阅读更多...
数据库使用之union、union all、各种join的用法区别解析

数据库使用之union、union all、各种join的用法区别解析

《数据库使用之union、unionall、各种join的用法区别解析》：本文主要介绍SQL中的Union和UnionAll的区别,包括去重与否以及使用时的注意事项,还详细解释了Join关键字,... 目录一、Union 和Union All1、区别：2、注意点：3、具体举例二、Join关键字的区别&php
阅读更多...
查询Oracle数据库表是否被锁的实现方式

查询Oracle数据库表是否被锁的实现方式

《查询Oracle数据库表是否被锁的实现方式》本文介绍了查询Oracle数据库表是否被锁的方法,包括查询锁表的会话、人员信息,根据object_id查询表名,以及根据会话ID查询和停止本地进程,同时,... 目录查询oracle数据库表是否被锁1、查询锁表的会话、人员等信息2、根据 object_id查询被
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2