Mysql学习（二）

最新推荐文章于 2024-07-29 13:31:56 发布
最新推荐文章于 2024-07-29 13:31:56 发布
阅读量140 收藏 1
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gcd1997/article/details/84134563
版权

目录

1   having子句

2   子查询

3   条件子查询

4   from子查询、行内视图

5   约束

5.1     主键

5.2     自增主键

5.2.1 获得刚刚产生的自增值

5.3     外键

5.4     非空

5.5     唯一

5.6     检查

6   多表连接查询

7   外连接

8   事务

8.1     启动事务

8.2     提交事务

8.3     回滚事务

8.4     事务特性ACID

9   数据访问冲突的问题

9.1     脏数据

9.2     不可重复读

9.3     幻觉读

10     隔离级别

mysql -uroot -p

****



use hr;



-- 50部门，工资大于等于8000的员工，按工资降序排列

select id,fname,sal,dept_id

from emps

where dept_id=50 and sal>=8000

order by sal desc;



-- 电话号码tel包含'44'，并把44替换成88

select id,fname,tel,

replace(tel,'44','88')

from emps

where tel like '%44%';



-- mgr_id，主管id是100,120,122 的人数

select count(*)

from emps

where mgr_id in(100,120,122);



-- 按主管id分组，求每个主管的手下人数

select mgr_id, count(*)

from emps

where mgr_id is not null

group by mgr_id;

 

 

1   having子句

用来对多行函数结果进行过滤

  • having 和 where 作用相同，都是条件过滤
  • where 过滤普通条件，最早执行
  • having 过滤多行函数结果，分组，求完多行函数后，才执行
  • having 跟在 group by 后面
-- 只有一个手下的主管id

select mgr_id, count(*) c

from emps

where mgr_id is not null

group by mgr_id

having c=1;



-- 平均工资小于等于5000的岗位代码

select job_id, avg(sal) a

from emps

group by job_id

having a<=5000;

 

2   子查询

查询嵌套

  • 条件子查询
  • from子查询、行内视图
  • select子查询

 

3   条件子查询

一个查询的查询结果，作为另一个查询的过滤条件

 

select .. where a=(select ...)

 

  • 单值子查询

n  = > >= < <=

  • 多值子查询

n  in

n  > all 比最大值大

n  > any 比最小值大

  • 多列子查询

n  where (a,b) in (select ...)

n  where (a,b) = (select ...)

-- 工资小于平均工资

select avg(sal) from emps;



select id,fname,sal

from emps

where sal<6461.682243;



select id,fname,sal

from emps

where sal<(

select avg(sal) from emps

);



-- 每个部门，拿最高工资的员工

-- 每个部门的最高工资值

select dept_id,max(sal) m

from emps

where dept_id is not null

group by dept_id;



select id,fname,sal,dept_id

from emps

where (dept_id,sal) in(

select dept_id,max(sal) m

from emps

where dept_id is not null

group by dept_id

);



-- 只有一个人的部门，查询这些员工

-- 1. 按部门分组求人数，过滤只有一人的部门

-- 2. 用部门id过滤查询员工



select dept_id

from emps

where dept_id is not null

group by dept_id

having count(*)=1;



select id,fname,sal,dept_id

from emps

where dept_id in(10,40,70);



select id,fname,sal,dept_id

from emps

where dept_id in(

select dept_id

from emps

where dept_id is not null

group by dept_id

having count(*)=1

);

 

 

4   from子查询、行内视图

从查询的查询结果，再查询

 

  • select ... from (select ...) t
-- 平均工资最低的工作岗位代码

-- 1. 按job_id分组求平均工资

-- 2. 平均工资最小值

-- 3. 用平均工资最小值，过滤岗位代码



select job_id, avg(sal) a

from emps

group by job_id;



select min(a)

from (

select job_id, avg(sal) a

from emps

group by job_id

) t;





select job_id, avg(sal) a

from emps

group by job_id

having a=(

select min(a)

from (

select job_id, avg(sal) a

from emps

group by job_id

) t

);



-- 手下人数最多的主管id

100  5

120  7

122  7

150  3



-- 1. 按mgr_id分组求人数

-- 2. 求人数的最大值

-- 3. 用人数过滤查询主管id



select mgr_id,count(*) c

from emps

where mgr_id is not null

group by mgr_id

order by c;



select max(c) from (

select mgr_id,count(*) c

from emps

where mgr_id is not null

group by mgr_id

order by c

) t;





select mgr_id,count(*) c

from emps

where mgr_id is not null

group by mgr_id

having c=(

select max(c) from (

select mgr_id,count(*) c

from emps

where mgr_id is not null

group by mgr_id

order by c

) t

);

 

5   约束

限制字段中的取值

  • 主键
  • 外键
  • 非空
  • 唯一
  • 检查

 

5.1     主键

数据表中，一行数据的唯一标识

  • 不重复
  • 不能是null值
  • 自动生成索引
  • 一般使用“非业务数据”来作为主键
  • 自动增加
  • 随机生成
-- 如果没有db1库，新建

create database db1 charset utf8;



use db1;



create table xuesheng (

   id int primary key,

   stu_num int,

   name varchar(20)

);



-- 告诉服务器，客户端使用的什么字符编码

set names gbk;



insert into xuesheng(id,name)

values(1,'张三'),(2,'李四');



-- 违反主键约束

insert into xuesheng(id,name)

values(1,'王五');

 

5.2     自增主键

整数类型主键，可以设置自动生成自增的值

 

create table xuesheng (

   id int primary key auto_increment,

   ....

);

-- 修改表，把主键id修改成自增

alter table xuesheng

modify id int auto_increment;



insert into xuesheng(name)

values('李四'), ('王五'), ('赵六');



select * from xuesheng;

 

5.2.1 获得刚刚产生的自增值

  • last_insert_id()
  • 只获得当前会话产生的自增值
insert into xuesheng(name)

values('钱七');



select last_insert_id();

 

5.3     外键

  • 限制一个字段，只能取指定的主键字段中，存在的值
  • 自动生成索引
create table banji(

id int primary key auto_increment,

name varchar(20)

);



-- 修改学生表，添加外键字段 ban_id，

-- 引用 banji表的主键id

alter table xuesheng

add (

   ban_id int,

   foreign key(ban_id) references banji(id)

);





-- 1 A

-- 2 B

insert into banji(name)

values('A'), ('B');



select * from banji;



-- 让学生1,2,3,在1班

update xuesheng set ban_id=1

where id in(1,2,3);



-- 让学生4,5在2班

update xuesheng set ban_id=2

where id in(4,5);



select * from xuesheng;

 

5.4     非空

create table xuesheng(

   ...

   name varchar(20) not null,

   ...

);

5.5     唯一

  • 自动创建索引

 

create table xuesheng(

   ...

   email varchar(100) unique

   ...

);

5.6     检查

  • 设置检查条件，约束字段的取值
  • mysql不支持
  • mariadb 支持检查约束

 

create table xuesheng(

   ...

   age int,

   gender char(1),

   ...,

   check(age>=7 && age<=60),

   check(gender in('男', '女'))

);

 

 

6   多表连接查询

  • 两张表，按指定条件，连接成一张表
  • 从连接的结果表中查询
use hr;



-- 部门表

select * from depts;



-- 查询员工，显示员工的部门名

select e.id,e.fname,e.sal,

        d.dept_id,d.dept_name

from emps e, depts d

where e.dept_id=d.dept_id;



-- 地区表

select * from locations;



-- 查询部门，显示部门的城市

select d.dept_id, d.dept_name,

        l.city

from depts d, locations l

where d.loc_id=l.loc_id;



-- 查询部门，显示部门经理名

select d.dept_id, d.dept_name,

        e.fname

from depts d, emps e

where d.mgr_id=e.id;



-- 查询员工，显示员工主管名

-- 自连接，一张表，看做是两张表来连接

select e1.id, e1.fname, e1.sal,

        e2.fname mgr

from emps e1, emps e2

where e1.mgr_id=e2.id;

 

7   外连接

  • 内连接
  • 只查询满足连接条件的数据
  •  外连接
  • 不满足连接条件的数据也要查询
  • 左外连接
  • 查询左侧表条件外数据
  • 右外连接
  • 查询右侧表条件外数据
  • 全外连接
  • 双侧表条件外数据
  • MySql不支持

 

 

 

 

  • 外连接，非标准sql连接语句，每种数据库外连接语法都不同
  • sql server

where a.id(+) = b.xid

  • oracle

where a.id *= b.xid

  •  MySql

没有外连接的非标准语法

 

  • 标准的表连接语法
  • select ...

from a join b

     on(a.id=b.xid)

     join c

     on(...)

  • select ...

from a left join b

     on(a.id=b.xid)

  • select ...

from a right join b

     on(a.id=b.xid)

-- 所有部门，显示部门经理，没有经理显示null



select d.dept_id, d.dept_name,

        e.fname

from depts d

left join emps e

on(d.mgr_id=e.id);



-- 查询107个员工，显示部门名和城市

select e.id, e.fname, e.sal,

        d.dept_name,

        l.city

from emps e

      left join depts d

      on(e.dept_id=d.dept_id)

      left join locations l

      on(d.loc_id=l.loc_id);

 

 

8   事务

  • 事务由一组sql语句组成
  • 事务的sql语句整体成功，整体失败

 

A --> B

 

update 账户 set money=money-100

where id='A';

update 账户 set money=money+100

where id='B';

 

  • 数据操作最小单元是事务，而不是sql语句

 

8.1     启动事务

  • begin
  • start transaction
  • 事务启动后，执行的数据增删改操作，会暂时记录在一个日志文件中

8.2     提交事务

  • commit
  • 事务日志中记录的数据操作，在数据表中生效
  • 清空日志文件

8.3     回滚事务

  • rollback
  • 清空日志

 

 

use db1;

 

select * from banji;

 

会话一

会话二

begin;

begin;

insert into

banji(name)

values('C');

 

update banji set name='Z' where id=1;

 

select * from banji;

 

 

select * from banji;

commit;

 

 

select * from banji;

 

commit;

 

select * from banji;

会话一

会话二

begin;

begin;

update banji set

name='Y' where id=1;

 

 

update banji set

name='X' where id=1;

rollback;

 

 

update banji set

name='X' where id=1;

 

commit;

 

select * from banji;

 

 

 

8.4     事务特性ACID

  •  A - Atomic

原子性，数据操作的最小单元是事务，而不是sql语句

  •  C - Consistency

一致性，事务完成前后，数据要保持逻辑的一致性

A+B=100 转账前

A+B=100 转账后

  •  I - Isolation

隔离性，一个事务操作数据时，其他事务要等待

  •  D - Durancy

持久性，事务成功，数据被永久的保存

 

 

9   数据访问冲突的问题

三种问题

  • 脏数据
  • 不可重复读
  • 幻觉读

 

9.1     脏数据

一个事务未提交的数据，被另一个事务查询到

9.2     不可重复读

  • 针对数据修改 update 操作
  • 查询过的数据，再次查询时，与之前的查询结果不一致

9.3     幻觉读

  • 针对数据的插入和删除
  • 新插入的数据，查询不到
  • 删除的数据，能查询到

 

10     隔离级别

为了避免数据访问冲突的问题，避免数据的混乱，数据库会让事务进行隔离

为了兼顾数据安全，和性能，数据库提供了四种隔离级别

  • 级别越高，越安全，性能越差
  • 级别越低，越不安全，性能越高
  • mysql设置隔离级别：

set tx_isolation='read-uncommitted'

脏读、不可重复读、幻觉读

set tx_isolation='read-committed'

不可重复读、幻觉读

set tx_isolation='repeatable-read' 默认级别

幻觉读

set tx_isolation='serialiable'

序列化

 

 

会话一

会话二

rollback;

rollback;

set tx_isolation='read-uncommitted';

begin;

begin;

insert into

banji(name)

values('E');

 

update banji set name='V' where id=1;

 

 

select * from banji;

rollback;

 

 

select * from banji;

会话一

会话二

rollback;

rollback;

set tx_isolation='read-committed';

begin;

begin;

 

select * from banji;

update banji set name='T' where id=1;

 

commit;

 

 

select * from banji;

会话一

会话二

rollback;

rollback;

set tx_isolation='repeatable-read';

begin;

begin;

 

select * from banji;

insert into

banji(name)

values('F');

 

update banji set name='S' where id=1;

 

commit;

 

 

select * from banji;

 

update banji set

name=concat('*',name);

 

select * from banji;

会话一

会话二

rollback;

rollback;

begin;

begin;

 

select * from banji;

delete from xuesheng;

 

delete from banji

where id in(1,2);

 

commit;

 

 

select * from banji;

 

update banji set

name=concat('#',name);

 

select * from banji;

 

commit;

 

select * from banji;

 

 

 

 

 

 

 

浮游ZL
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL超详细学习教程，2023年硬核学习路线
热门推荐
zhangxia_的博客
01-19 3万+
为什么学习MySQL？这是一篇 MySQL 通关一篇过硬经验学习路线，包括数据库相关知识，SQL语句的使用，数据库约束，设计等。未来是一个数字化的时代，数据是我们最为宝贵的资源，不论是哪个专业出身，未来或多或少都会卷入数据时代的浪潮之中。而那些前提性的工作基本都是依靠`数据库`来完成。
mybatis plus 自定义sql查询遇到的坑＜where＞ ${ew.sqlSegment} ＜/where＞
beyond513222的博客
11-07 1万+
List<Chapter> selectChapterAndQuestionCount(@Param(Constants.WRAPPER) QueryWrapper<Chapter> queryWrapper); <where> ${ew.sqlSegment} </where> 主要是如果使用${ew.customSqlSegment}，上面那个<where></where>标签是不用加滴 ...
mysql 部门工资_哪些员工的工资大于所在部门的平均工资？用mysql查询语句
weixin_39793708的博客
01-19 2367
1这是表的数据：createtableemp_hiloo(empnoint(8),enamevarchar(20),jobvarchar(15),salaryint(8),bonusint(8),hiredatedate,mgrint(8),deptnoint(8));insertintoemp_hiloovalues(10...1 这是表的数据：create table emp_hiloo(em...
mysql查询5000到8000_MySQL基础查询
weixin_35834687的博客
01-19 478
查询语句select语句的语法格式如下:select 字段列表from 数据源[ where条件表达式 ][ group by 分组字段[ having条件表达式 ][ order by 排序字段 [ asc | desc ] ]查询表达式每一个表达式表示想要的一列，必须至少有一列，多个列之间以逗号分隔*表示所有列，tbl_name.*可以表示命名表的所有列查询表达式可以使用[as] alias_...
MySQL学习路线
蜡笔小新星的博客
07-29 1659
以上学习路线可根据个人实际情况灵活调整。在学习过程中，建议结合实践，尽量在自己的机器或云服务器上搭建MySQL环境进行实践。同时，参与社区讨论，向他人学习，会加速你的成长过程。祝你学习顺利！
第二十四章 MySQL学习笔记
你好, 我在学java的博客
01-23 1万+
存放文本时，也可以使用Text数据类型，可以将TEXT列视为VARCHAR列，注意Text不能有默认值，大小0-2^16字节；同一查询在同一事务中多次进行，由于其它提交事务所做的修改和删除，每次返回不同的结果集，则发生不可重复读；多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据是的准确性；同一查询在同一个事务中多次执行，由于其它提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读；同真是的表一样，视图包含列，其数据来自对应的真实表(基表)
MySQL学习教程（超全）
qq_51533426的博客
03-15 6021
mysql学习安装配置
MySQL学习笔记
m0_70415287的博客
06-06 3124
MySQL初级知识学习
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MySQL学习第二天 安装和配置mysql winx64
01-19
一、安装方式  MySQL安装文件分为两种，一种是MSI格式的，一种是ZIP格式的。下面来看看这两种方式：  MSI格式的可以直接点击安装...二、安装MySQL(Windows 64位操作系统) (1)双击运行已经下载好的MySQL安装文件mysql
mysql学习资料 45讲 深度学习
10-29
这个“mysql学习资料 45讲 深度学习”压缩包包含了多个PDF文件，涵盖了MySQL的关键知识点，包括性能优化、高可用性、主备一致性和安全性等方面。下面我们将深入探讨其中几个关键的主题。 1. **"order by"是怎么工作...
Mysql学习文档
04-17
### MySQL学习文档知识点详解 #### 一、MySQL简介与特性 **标题与描述解析：** - **标题**：“Mysql学习文档”表明该文档旨在帮助读者掌握MySQL的相关知识和技术。 - **描述**：“文档讲述的从入门到精通的课程...
文件转换为Base64形式（可来回转换，带加密功能）
最新发布
10-09
文件转换为Base64形式（可来回转换，带加密功能） 使用说明 === https://blog.csdn.net/sxzlc/article/details/139076076 ====
基于粒子群算法的配电网日前优化调度 采用IEEE33节点配电网搭建含风光，储能，柴油发电机和燃气轮机的经济调度模型 以运行成本
10-09
基于粒子群算法的配电网日前优化调度 采用IEEE33节点配电网搭建含风光，储能，柴油发电机和燃气轮机的经济调度模型。 以运行成本和环境成本最小为目标，考虑储能以及潮流等约束，采用粒子群算法对模型进行求解，得到电源的每小时出力情况。
ECU ccp协议上位机测试与标定源码，C#源码上位机ECU标定与测量程序 - ASAM MCD -1MC的 ccp 标定与测
10-09
ECU ccp协议上位机测试与标定源码，C#源码上位机ECU标定与测量程序。 - ASAM MCD -1MC的 ccp 标定与测量上位机软件，短上传和DAQ功能，支持一维二维等测量和标定，显示控件丰富，具有仪表盘，进度条，二维map等。 - ASAM MCD-2MC 的文件读取（a2l文件读取） - 在解析的同时提供了文件结构检查 - 读取和导出ECU 测量数据为excel文件 - 支持多个设备 ，包括 ETAs, Vector, Peak, 8Devices, Kvaser, usbcan兼容设备 等等CAN设备的支持，同时有一个简单的接口，用于实现其他设备支持，接口dll函数替即可。 .Net 目标框架为 4.0，需要引用Excel程序，默认版本为 office 2019，也可在源码中修改。
超市商品管理系统 SSM毕业设计 源码+数据库+论文（JAVA+SpringBoot+Vue.JS）.zip
10-09
超市商品管理系统 SSM毕业设计 源码+数据库+论文（JAVA+SpringBoot+Vue.JS） 启动教程：https://www.bilibili.com/video/BV1GK1iYyE2B
GTO-CNN-BiLSTM进行多变量多步时间序列预测的详细Python 项目实例（包含详细的完整的程序和数据）
10-09
本文提供了一个基于图论优化（GTO）、卷积神经网络（CNN）和双向长短期记忆网络（BiLSTM）相结合的时间序列预测项目实例。通过一系列具体操作，包括数据预处理、构建融合CNN与BiLSTM特性的模型，以及最后的训练、验证直至预测与可视化结果等流程，向读者展示了一个典型且易上手的实验范本。 适用人群：有一定深度学习与时间序列数据分析背景的知识工作者和技术人员。 使用场景及目标：适用于金融分析、天气预报等领域内的多种多变量时间序列的长期与短期趋势预报，特别是那些对模型解释性和精度都有较高要求的应用场合。 阅读收获：理解GTO-CNN-BiLSTM模型及其组件（卷积层、双向LSTM层等）的工作机制，掌握一套实用且高效的时序数据分析技巧，有助于提高相关领域的应用研究和项目实践经验。
mysql学习手册 第二版
01-07
《Mysql学习手册 第二版》是一本非常重要的数据库技术书籍，它详细介绍了Mysql数据库的相关知识和技术。这本书包含了Mysql数据库的基础知识，例如数据库的安装、配置和管理，以及高级内容，比如性能优化、备份恢复和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

浮游ZL

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值