MySQL笔记 —— 从其他表加载数据，视图，事务

从其他表加载数据

方式一

格式为：create table 新表名 as select * from 旧表名;

create table studentCopy as select * from student;

产生了一个一模一样的新表，当然也可以选择部分字段，将*换成具体的字段名即可

方式二

-- 创建一张空表
create table studentTest(
id int not null,
name varchar(255) default null,
primary key(id)
)engine=innodb;

-- 插入数据
insert into studentTest select id,name from student;

视图

使用select语句会产生一张虚拟表，这张虚拟表里面放着本次查询的结果。我们也可以对虚拟表进行查询，比如这篇博客的最后，求topn问题时就对虚拟表进行了再次查询，即子查询
但是会发现一个问题，那就是直接对实体表查询时只需要from 后面跟一个表名即可
而使用子查询时，from后面会跟着一串很长的查询语句，这串查询语句的结果是一张虚拟表
如果我们需要多次使用这张虚拟表，就会导致代码变得非常长，而且显得很乱，因此，我们可以将虚拟表变成视图，每次from的后面只需要一个视图名，就可以对虚拟表进行查询。

视图的定义：视图是由查询结果形成的一张虚拟的表，视图是对若干张基本表的引用，是一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）。

视图的创建

1. 针对一张表创建视图，并且字段完全一致（这样创建其实没有意思，因为可以直接用原表）

-- 创建语句
create view stuView as select * from stu1;
-- 查询语句
select * from stuView;

结果与原表的查询结果一样

2. 针对一张表创建视图，加入分组聚合

create view stuView1 as select id,name,avg(age) from stu1 group by id;

3. 针对多张表，不使用分组聚合

create view stuView2 as select student.*,score.* from student left join score on student.id=score.studentId;

4. 针对多张表，使用分组聚合

create view stuView3 as select student.id,name,sex,sum(score) as sc from student left join score on student.id=score.studentId group by id;

使用视图

按男女分组，分别求取每组的前三名

select * from stuView3 as s1 where 3>(select count(*) from stuView3 as s2 where s1.sex=s2.sex and s1.sc<s2.sc) order by sex;

查看视图信息

方式一，查看视图的结构

desc stuView3;

方式二，查看视图的创建语句

show create view stuView3;

修改基本表的数据，视图也会随之修改

一对一的情况

create view stuView as select * from stu1;
select * from stuView;
select * from stu1;

视图和原表的查询结果都是上图的结果

update stu1 set age=22 where name='zhang';
select * from stuView;
select * from stu1;

视图和原表的数据都发生了改变

一对多的情况

-- 创建语句
create view stuView2 as select student.*,score.* from student left join score on student.id=score.studentId;
-- 查询语句
select student.*,score.* from student left join score on student.id=score.studentId;
select * from stuView2;

两个查询语句的结果都一样

update student set name='zhang' where id='1001';

select student.*,score.* from student left join score on student.id=score.studentId;
select * from stuView2;

两个查询语句的结果依旧一致

删除视图

格式为：drop view 视图名;

create view studentView as select * from student;

-- 删除视图
drop view studentView;

事务

事务是一段sql语句的批量处理，把多条sql语句当作一个整体，按照原子性来执行。要么所有语句都执行，要么所有语句都不执行。如果执行到一半发现出错，无法继续执行，就会利用回滚机制，让之前已经执行的内容回滚到未执行时的情况。
开始事务 —— begin;
回滚事务 —— rollback;
提交事务 —— commit;
结束事务 —— end;

以在银行存钱取钱为例，先创建两张表，一张是person表，显示个人拥有的现金，初始为3000。一张是bank表，显示个人存在银行上的钱，初始为0。

create table person(
money int not NULL
)engine=innodb;

create table bank(
money int not NULL
)engine=innodb;

insert into person values(3000);
insert into bank values(0);

select * from person;
select * from bank;

首先展示一下运行出错，回滚的情况

begin;
update person set money=1000; -- 减少现金的金额2000,剩余现金为1000
update bank set money=3000;  -- 银行的账号应该加2000，这里却加了3000，出错
select * from person; -- 值为1000
select * from bank; -- 值为3000
ROLLBACK; --  清空 归0

select * from person; -- 值为3000，成功恢复到了原状
select * from bank; -- 值为0

然后再展示一下没有出错，最后提交的情况

begin;
update person set money=1000; -- 减少现金的金额2000,剩余现金为1000
update bank set money=2000; -- 银行的账号应该加2000
select * from person; -- 结果为1000，正确
select * from bank; -- 结果为2000，正确
COMMIT;-- 把缓冲区的写入到元数据

select * from person; -- 结果为1000，实体数据被修改
select * from bank; -- 结果为2000，实体数据被修改