目录
4.查询student表中最大和最小的sbirthday日期值
5.以班号和年龄从大到小的顺序查询student表中的全部记录
3.5.1 .read uncommitted; 读未提交的
1.查询练习
1.查询至少2名男生的班号
group by class 根据class分组
select * from student
--àselect class from student where ssex=’男’ group by class having count(*)>1;
2.查询student表中每个学生的姓名和年龄
年龄=当前月份-出生年份
select year(now());
select year(sbirthday) from student;
select sname,year(now)- year(sbirthday) as ‘年龄’ from student;
3.查询student表中不姓“王”的同学记录
select * from student where sname not like ‘王%’;
4.查询student表中最大和最小的sbirthday日期值
select sbirthday from student order by sbirthday;
max min
select max(sbirthday) as ‘最大’,min((sbirthday) as ‘最小’ from student;
5.以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc,sbirthday;
6.查询“男”教师及其所上的课程
select * from teacher where tsex =’男’;
select * from course where tno in(select * from teacher where tsex =’男’);
7.查询最高分同学的sno,cno和degree列
select max(degree) from score;
select * from score where degree=( select max(degree) from score);
8.查询和“李军”同性别的所有同学的sname
select ssex from student where sname=’李军’
select sname from student where ssex=( select ssex from student where sname=’李军’);
9. 查询和“李军”同性别并同班的同学sname
select sname from student
where ssex=( select ssex from student where sname=’李军’)
and class=(select class from student where sname=’李军’);
10.查询所有选修“计算机导论”课程的“男”同学成绩表
select * from student where ssex=’男’;
select * from course where cname=’计算机导论’;
select * from score
where cno=(select cno from course where cname=’计算机导论’)
and sno in (select sno from student where ssex=’男’);
11.假设使用如下命令建立一个grade表
create table grade(
low int(3),
upp int(3),
grade char(1)
);
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
12.现查询所有同学的sno,cno和degree列
select sno,cno,grade from score,grade where degree between low and upp;
2.sql的四种连接查询
内连接
inner join 和join
外连接
- 左连接 left join 或者 left outer join
- 右链接 right join 或 right outer join
- 完全外连接 full join 或者 full outer join
--创建两个表
create database testJoin;
--person 表
id,
name,
cardid
create table person(
id int,
name varchar(20),
cardId int
);
--card 表
id,
name
create table card(
id int,
name varchar(20)
);
insert into card values(1,’饭卡’);
insert into card values(2,’建行卡’);
insert into card values(3,’农行卡’);
insert into card values(4,’工商卡’);
insert into card values(5,’邮政卡’);
insert into person values(1,’张三’,1);
insert into person values(2,’李四’,3);
insert into person values(1,’王五’,6);
--并没有创建外键
2.1 inner join 查询 (内连接)
person表和card表建立联系,使person中的cardId和card中的id相等
select * from person inner join card on person.cardId=card.id;
--内敛查询,其实就是两张表中的数据,通过某个字段相对,查询出相关记录数据
2.2 left join(左外连接)
select * from person left join card on person.cardId=card.id;
左外连接,会把左表里面的所有数据取出来,而右边表中的数据,如果有相等的,就显示出来,如果没有,就会补null
2.3 right join(右外连接)
select * from person right join card on person.cardId=card.id;
右外连接,会把右表里面的所有数据取出来,而左边表中的数据,如果有相等的,就显示出来,如果没有,就会补null
2.4 full join(全外连接)
select * from person full join card on person.cardId=card.id;(这个不成立)
mysql不支持全外连接
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;(这个成立)
3.事务
mysql中,事务就是一个最小的不可分割的工作单元,事务能够保证一个业务的完整性
-
3.1介绍
- 比如银行转账:
- -->-100
update user set money=money-100 where name=’a’;
- -->+100
update user set money=money+100 where name=’a’;
实际的程序中,如果只有一条语句执行成功了,而另外一条没有执行成功?出现数据前后不一致?
update user set money=money-100 where name=’a’;
update user set money=money+100 where name=’a’;
多条sql语句,可能会有同时成功的要求,要么就同时失败。
3.2 如何控制事务
1.mysql默认是开启事务的(自动提交)。
select @@autocommit
2.默认事务开启的作用是什么?
当我们去执行一个sql语句的时候,效果会立即体现出来,且不能回滚
create database bank;
create table user(
id int primary key,
name varchar(20),
money int
);
insert into user values(1,’a’,1000);
3.事务回滚:撤销sql语句执行效果
rollback;
4.设置mysql自动提交为false
set autocommit=0;
select @@autocommit;
上面的操作,关闭了mysql的自动提交
5.事务关键:
- 自动提交? @@autocommit=1
- 手动提交? commit;
- 事务回滚? rollback.
- 自动提交后不可以撤回,手动提交后能撤回
- 事务给我们提供了一个返回的机会,用rollback
3.3 手动开启事务
- begin 或者 start transaction 都可以帮我们手动开启一个事务
- 之后再提交代码,就可以rollback撤回返回了
- commit提交后,就不能再撤回了,已经生效了
3.4 事务的四大特征
- 原子性:事物的最小的单位,不可以在分割
- 一致性:事务要求,同一事务中的sql语句,必须保证同时成功或者同时失败
- 隔离性:事务1和事务2之间是有隔离性的
- 持久性:事务一旦结束(commit,rollback),就不可以返回
事务开启:
- 修改默认提交 set autocommit=0;
- begin
- start transaction
事务手动提交:commit;
事务手动回滚:rollback;
3.5 事务的隔离性
3.5.1 .read uncommitted; 读未提交的
如果有事务a,和事务b,
a事务对数据进行操作,在操作的过程中,事务没有被提交,但是b可以看见a的操作结果
bank数据库 user表
insert int user values(3,’小明’,1000)
insert int user values(4,’淘宝店’,1000)
-------如何查看数据库的隔离级别?
mysql 8.0:
系统级别的:
select @@global.transaction_isolation;
会话级别的:
select @@transaction_isolation;
mysql 5.x:
select @@global.tx_isolation;
select @@tx_isolation;
------如何修改隔离级别?
set global transaction isolation level read uncommitted;
------转账:小明在淘宝店买鞋子:800块钱
小明--》成都 ATM
淘宝店--》广州 ATM
update user set money=money-800 where name=’小明’;
update user set money=money+800 where name=’淘宝店’;
给淘宝店打电话,说你去查一下,是不是到账了
淘宝店查账:
select * from user;
发货:
晚上请女友吃好吃的,花了1800
结账时发现钱不够
两个不同的地方,都在进行操作,如果事务a开启后,它的数据可以被其他事务读取到
这样就会出现(脏读)
脏读:一个事务读到了另一个事务没有提交的数据,就叫做脏读
实际开发不允许出现脏读
3.5.2 read committed; 读已提交的
- 修改隔离级别:set global transaction isolation level read uncommitted;
查看隔离级别:select @@global.transaction_isolation;
- bank 数据库 user 表
---小张:银行的会计
start transaction;
select * from user;
小张出去上厕所了并抽烟的同时
---小王:
start transaction;
insert into user values(5,’c’,100);
commit;
---小张上完厕所抽完烟
select avg(money) from user;
money的平均值变少了
虽然我只能读到另外一个事务提交的数据,但还是会出现问题,就是
读取到同一个表的数据,发现前后不一致
不可重复现象
3.5.3 repeatable; 可以重复读
- 修改隔离级别:set global transaction isolation level read uncommitted;
查看隔离级别:select @@global.transaction_isolation;
- --王全蛋--成都
start transaction;
--王尼玛--北京
start transaction;
--王全蛋--成都
insert into user values(6,’d’,100);
--王尼玛--北京
insert into user values(6,’d’,100);
事务a和事务b同时操作一张表,事务a提交的数据也不能被事务b读到,这种现象叫做幻读
3.5.4 serializable; 串行化
- 修改隔离级别:set global transaction isolation level read uncommitted;
查看隔离级别:select @@global.transaction_isolation;
- --王全蛋--成都
start transaction;
--王尼玛--北京
start transaction;
--王全蛋--成都
insert into user values(7,’赵铁柱’,1000);
commit;
--王尼玛--北京
select * from user;
--王全蛋--成都
start transaction;
insert into user values(8,’王小花’,1000);
语句被卡住了
当user表被另一个事务操作的时候,其他事务里面的写操作,是不可以进行的
进入排队状态(串行化),直到王尼玛那边的事务结束后,张全蛋这个写入操作才会执行
在没有等待超时的情况下
--王尼玛--北京
commit;
--王全蛋--成都
insert into user values(8,’王小花’,1000);执行成功
串行化问题是,性能特差!
read uncommitted; >read committed;>repeatable;>serializable;
隔离级别越高,性能越差
mysql默认隔离级别是 repeatable;