SQL——视图的更新及作用及总结案例

create table stue
(
id     int       auto_increment primary key    comment '  客户编号',
name   varchar(20)             comment '客户名称',
mima   varchar(100)           comment'客户密码 ' ,
phonr  varchar(20)            comment '客户电话',
xb       char(2)                  comment '选则男，女',
y_x      varchar(20)            comment   '邮箱 ' ,
d_z         varchar(20)               comment'地址'

)comment'记录客户基本信息';

insert into stue (id, name, mima, phonr, xb, y_x, d_z) values (1,'小草','123451','183953...','男','1273@qq','安徽'),
                                                                       (2,'小理','123452','18783...','男','1253@qq','安徽'),
                                                                       (3,'小马','123453','18353...','女','1238@qq','安徽'),
                                                                       (4,'小了','123454','18334...','男','1223@qq','安徽'),
                                                                       (5,'小个','123455','18327...','女','1283@qq','安徽'),
                                                                       (6,'小逼','123456','18369...','女','12309@qq','安徽'),
                                                                       (7,'小了就','123454','18334...','男','12523@qq','安徽'),
                                                                       (8,'小个好','123455','18327...','女','12873@qq','安徽'),
                                                                       (9,'小逼个','123456','18369...','女','12396@qq','安徽'),
                                                                       (10,'小了是','123454','18334...','男','12423@qq','安徽'),
                                                                       (11,'小个女','123455','18327...','女','12283@qq','安徽');


create  table  student1
(
id int primary key  auto_increment,
name varchar(16),
no  varchar(10)
)comment '学生';
insert into student1(id, name, no) values (1,'时科',2142151207),(2,'海龟',2142151208),(3,'工会',2142151209);





create table  coure
 (
  id int primary key auto_increment,
  name varchar(18)
)comment '课程';
insert into coure(id, name) values (4,'clode'),(1,'javaEE'),(3,'MySQL'),(2,'Spring');



create table  student1_coure
(
  id int primary key  auto_increment,
  student_id int,
  coure_id   int
)comment '学生与课程的中间表';
insert into student1_coure(id, student_id, coure_id) values (1,1,1),(2,1,2),(3,1,3),(4,2,2),(5,2,3),(6,3,4);





select * from stue ;


-- 案例
-- 1 为了保证数据库表的安全，开发人员在操作stue表时，只能看到的用户的基本字段，屏蔽手机号和邮箱俩个字段
create  view  stue_view as select  id,name,mima,xb,d_z from stue;
select  * from stue_view;

-- 2 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都能使用到，为了简化操作，定义一个视图
select * from student1 s,student1_coure sc ,coure c where  s.id=sc.student_id and  c.id=sc.coure_id;
select s.id, s.name ,s.no,c.name from student1 s,student1_coure sc ,coure c where  s.id=sc.student_id and  c.id=sc.coure_id;
create  view  tb_student1_coue as select s.id, s.name s_name ,s.no,c.name c_name from student1 s,student1_coure sc ,coure c where  s.id=sc.student_id and  c.id=sc.coure_id;

2. 联查
   
   

联查视图