目录
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; |