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 6023
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的相关知识和技术。 - **描述**：“文档讲述的从入门到精通的课程...
多台三相逆变器并联（本模型为三台并联，市面上多为两台并联）matlab simulink仿真 功能：实现并联系统中各逆变器输出
最新发布
10-09
多台三相逆变器并联（本模型为三台并联，市面上多为两台并联）matlab simulink仿真。 功能：实现并联系统中各逆变器输出功率均分。 （有能力的话还可以研究下垂特性、功率指令以及静态功工作点三者之间的联系） 控制策略：VSG控制策略（同步机控制） 逆变器主电路：三相逆变器，LCL滤波电路，VSG控制模块。 VSG控制模块：定、转子方程，dq变，电压电流双闭环，预同步，pwm发生器。
Ringtones波点音乐-《Lightning Moment》.mp3
10-09
Ringtones波点音乐-《Lightning Moment》.mp3
onnx2onnx-0.3.1.tar.gz
10-09
onnx2onnx-0.3.1.tar
网页商城系统 SSM毕业设计 源码+数据库+论文（JAVA+SpringBoot+Vue.JS）.zip
10-09
网页商城系统 SSM毕业设计 源码+数据库+论文（JAVA+SpringBoot+Vue.JS） 启动教程：https://www.bilibili.com/video/BV1GK1iYyE2B
实现卡尔曼滤波器,扩展卡尔曼滤波器,双卡尔曼滤波器和平方根卡尔曼滤波器Matlab代码.rar
10-09
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。
mysql学习手册 第二版
01-07
《Mysql学习手册 第二版》是一本非常重要的数据库技术书籍，它详细介绍了Mysql数据库的相关知识和技术。这本书包含了Mysql数据库的基础知识，例如数据库的安装、配置和管理，以及高级内容，比如性能优化、备份恢复和安全性等方面。此外，本书还介绍了Mysql数据库的新特性和最佳实践，帮助读者更好地理解和掌握Mysql数据库的相关技术。 《Mysql学习手册 第二版》的内容丰富全面，将Mysql数据库的常见问题和解决方法都详细地进行了说明和分析。读者可以通过阅读本书，系统地了解Mysql数据库的原理、技术架构和使用方法，掌握Mysql数据库的管理与优化技巧，同时还可以学习到一些高级的数据库应用开发技术和实际应用案例。总的来说，本书适合数据库管理员、开发人员和对Mysql数据库有兴趣的技术爱好者阅读，对于提高他们的技术水平和解决实际工作中的问题将会有很大的帮助。 综上所述，《Mysql学习手册 第二版》是一本非常有价值的数据库技术书籍，它涵盖了丰富的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、付费专栏及课程。

余额充值