# --视图：单表+多表
# create view my_v1 as
# select * from my_student;
# create view my_v2 as
# select * from my_student;
# create view my_v3 as
# select * from my_student as s
# left join my_class as c on s.c_id
# =c.id;--id修复
# --多表视图
# create view my_v3 as
# select s.*,c.c_name,c.room
# from my_student as s
# left join my_class as c
# on s.c_id=c.id;
# --查看视图创建语句
# show create view my_v3\G
# --视图使用
# select * from my_v1;
# select * from my_v2;
# select * from my_v3;
# --修改视图
# alter view my_v1 as
# select id,name,age,sex,height,
# c_id
# from my_student;
# --创建视图
# create view my_v4 as select *
# from my_student;
# --删除视图
# drop view my_v4;
# --多表视图插入数据
# insert into my_v3
# values(null,'bc2019006','张三丰','男',150,180,1,'Python1907','B407');
# 将学生表的学号字段设置成不允许为空
# alter table my_student modify
# number char(10) not null unique;
# --单表视图插入数据：视图不包括所有不允许为空的字段
# insert into my_v1
# values(null,'张三丰',150,'男',180,1);--失败(学号不允许为空)
# --单表视图插入数据
# insert into my_v2
# values(2,'Python1811','B410');
# --多表视图删除数据
# delete from my_v3 where id=1;
# --单表视图删除数据
# delete from my_v2 where id=4;
# --多表视图更新数据
# update my_v3 set c_id=3 where id=5;
# --视图:age字段限制更新
# create view my_v4 as
# select * from my_student where
# age>30 with check option;
# 表示视图的数据来源都是年龄大于30岁，是由where age>30决定的
# --with check
# option决定通过视图更新的时候，不能将已经的到的数据age>30的改成<30的
# --将视图可以查到数据改成年龄小于30的
# update my_v4 set age=29 where id=3;
# --可以修改数据:可以改，但是视图查不到
# update my_v4 set age=32 where id=2;
# --获取所有班级中最高的一个学生
# create view my_v5 as
# select * from my_v5 group by c_id;
# select * from my_student group by c_id order by height desc;
# --指定算法为临时表算法
# create algorithm=temptable view
# my_v6 as select * from my_student order by height desc;
# select * from my_v6 group by c_id;
# --查看MySQL的版本
# select @@version;
# --创建myisam表
# create table my_myisam(
# id int
# )charset utf8 engine=myisam;
# --向my_myisam表插入几条记录
# insert into my_myisam values(1),(2),(3);
# --单表数据备份
# select * into outfile
# 'D:/1907/web/student.txt'
# --还原数据
# load data infile
# 'D:/1907/web/student.txt'
# into table my_class
# --字段处理
# fields
# enclosed by '""'
# 数据使用双引号包裹
# terminated by'|'
# 使用数显分隔字段数据
# --行处理
# lines
# starting by 'START'
# --SQL备份
# mysqldump -uroot -p123456
# mydatabase my_student > D:/1907/web/student.sql
# --整库备份
# mysqldump -uroot -p123456
# mydatabase my_student > D:/1907/web/student.sql
# --还原数据：mysql客户端还原
# mysql -uroot -p123456 mydatabase < D:/1907/web/student/sql
# --SQL指令还原SQL备份
# source D:/1907/web/student.sql;
