Mysql学习(二)

目录

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;

 

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

打赏作者

浮游ZL

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

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

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

打赏作者

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

抵扣说明:

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

余额充值