mysql易错总结-count,exists,in，view易错点

create database test1;
use test1;

create table if not exists student(
sno varchar(3) primary key comment'学生编号',
sname varchar(4) not null comment'姓名',
ssex varchar(2) not null comment'性别',
sbirthday date comment'生日',
class varchar(5) not null comment'班级代号'
);

#student
insert into student(sno,sname,ssex,sbirthday,class) values
(108,'曾华','男','1977-09-01',95033),
(105,'匡明','男','1975-10-02',95031),
(107,'王丽','女','1976-01-23',95033),
(101,'李军','男','1976-02-20',95033),
(109,'王芳','女','1975-02-10',95031),
(103,'陆军','男','1974-06-03',95031);

Create view view_student
As
Select * from student;

Select * from student;
Select * from view_student;
delete from  student where sno='103';
#drop view view_student;
delete from  student where sno='103';
#delete from student;
truncate student;
Select * from student;
Select * from view_student;
#验证修改视图数据是否会修改原图？
#如果有外键关联是不能执行成功的   -用use myschool1;验证
#use myschool1;Delete from view_student where studentno=101;
Delete from view_student where sno=101;
Select * from student;
Select * from view_student;
#已验证，会-（就是对数据的增删改查会），删除视图是不会的，
drop view view_student;

#不会改变原表数据结构
SELECT
concat('alter  table student add COLUMN id1 varchar(32) NULL')
FROM
    test1.student t; #ok
ALTER TABLE test1.student ADD COLUMN id2 varchar(32) NULL;  #ok

SELECT
concat('alter  view view_student add COLUMN id3 varchar(32) NULL')
FROM
    test1.view_student t; #没有改变视图结构，有没有改变原本表结构
#视图里的列要不要加主键啊？为何用Navcat一打开就提示要设呀？
#view是没法改结构的，它本身只是个引用，不存储实际数据，你可以把原表上的主键列加到view中去，先drop view。
/*
总而言之就是，对视图进行数据增删改查也会同步到原表，
但是原表也会同步到视图数据，且原表能改变结构，而视图不能

count,exists,in，view易错点
count(3)，只要括号里面有字符就可以，结果都一样，关键是group by
exists(),只要true就执行
in,区别=就是筛选一条，in是多条

*/
 

 

 

 

 不可以先删除原表字段，会使视图出错

 

 可以先创建视图，对原表添加字段