数据库学习Day3

实体与实体之间有3种对应关系，这些关系也需要存储下来。
视图用于完成查询语句的封装事务可以保证复杂的增删改操作有效当数据巨大时，为了提高查询速度可以通过索引实现
创建成绩表scores，
结构如下：
Id
学生
科目
成绩

Create table scores(Id int primary key auto_increment,Stuid int,Subid int,Score decimal(5,2));
外键
思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？答：必须时关系表中id列存在的数据，可以通过外键约束进行数据的有效性验证为stuid添加外键约束。



报错，
因为没有id为10的学生和id为10的科目。
外键的级联操作在删除students表的数据时，如果这个id值在scores种已经存在，则会抛异常可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
级联操作的类型包括：
restrict(限制)：默认值，抛异常
cascade(级联)：如果主表的记录删掉，则从表中相关联的记录都将被删除
set null：将外键设置为空
no action：什么都不做
查询每个学生每个科目的分数
分析：学生姓名来源于students表，科目名称来源于subjects，分数来源于scores表，怎么将3个表放到一起查询，并将结果显示再同一结果集中呢？
答：当查询结果来源于多张表时，需要使用连接查询关键：找到表间的关系，当前的关系是Students表的id–scores表的stuidSubjects表的id–scores表的subid

结论当需要对有关系的多张表进行查询时，需要使用连接join。
连接查询：
连接查询分类：
表A inner join表B：表A与表B匹配的行会出现再结果中。
表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充。
表Aright join表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充。在查询或条件中推荐使用“表名.列名”语法。



查询学生姓名、平均分

查询男生的姓名、总分

查询具体学生的成绩总和

查询男生的姓名、总分Students.gender=1Students.nameSum(scores.score)建立连接：students.id=scores.stuid使用sum->分组，姓名：每个人总分

查询科目的名称、平均分Subjects.titleAvg(scores.score)建立连接：subjects.id=scores.subidAvg->group by sujects.title

查询未删除科目的名称、最高分、平均分Where sujects.isDelete=0Sujects.titleMax(scores.score)avg(scores.score)建立连接：subjects.id=scores.subidMax,Avg->group by sujects.title

自关联
设计省信息的表结构
provinces
Id
Title
设计市信息的表结构
Id
Title
Cid
设计区县的表结构
Id
Titlt
Xid

存储的都是地区信息，而且眉中信息的数据量有限，没必要增加一个信标，或者将来还要存储区、乡镇信息，都增加新表的开销太大答案：定义表的areas，结构如下IdTitlePid因为省没有所属的省份，所以可以填写为null城市所属的省份pid，填写省对应的编号id这就是自关联，表中的某一例，关联了这个表中的另外一列，但是他们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息创建areas表的语句如下：create table areas(aid int primary ley,atitle varchar(20),pid int,foreign key(pid) references areas(id));


从sql文件导入数据Source areas.sql查询一共有多少个省

Select count() from areas where pid =null;查询省的名称为“山西省”的所有城市Select city. from areas as cityInner join areas as province on city.pid=province.aidWhere province.atitle=’山西省’；

视图
对于复杂的查询，在多次使用后，维护时是一件非常麻烦的事情。解决：定义视图试图本质就是对查询的一个封装定义视图create view stuscore as Select students. *,scores.score from scoresInner join students on scores.stuid=students.id;

alter view v_stu_sub_sco as select stu.*,sco.score,sub.title from scores as sco inner join students as stu on sco.stuid=stu.id inner join subjects as sub on sco.subid=sub.id where stu.isDelete=0 and sub.isDelete=0;