数据库mysql高级

简介

  • 实体与实体之间有3种对应关系,这些关系也需要存储下来
  • 在开发中需要对存储的数据进行一些处理,用到内置的一些函数
  • 视图用于完成查询语句的封装
  • 事务可以保证复杂的增删改操作有效

关系

  • 创建成绩表scores,结构如下
    • id
    • 学生
    • 科目
    • 成绩
  • 思考:学生列应该存什么信息呢?
  • 答:学生列的数据不是在这里新建的,而应该从学生表引用过来,关系也是一条数据;根据范式要求应该存储学生的编号,而不是学生的姓名等其它信息
  • 同理,科目表也是关系列,引用科目表中的数据
  • 数据库mysql高级 

引用主键:把学生表的id引到成绩表。 一个学生对应多个成绩,学生和成绩是一对多,一个科目对应多个学生成绩,所以科目和成绩也是一对多(一对多就把关系字段存储在多的表中,多对对的话就新建一张表存关系)

  • 创建表的语句如下
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);
decimal(4,1)一共4位数,小数占一位

   查看表scores的结构

  stuid=1的这个学生在subid=1的这个科目考了100分

外键

  • 思考:怎么保证关系列数据的有效性呢?任何整数都可以吗?(比如只有三个班级,但有个学生信息在四班就错了)
  • 答:必须是学生表中id列存在的数据,可以通过外键约束进行数据的有效性验证
  • 为stuid添加外键约束(stu id是关系字段,添加外键是为了保证stuid的有效性)
alter table scores add constraint stu_sco foreign key(stuid) references students(id);
  • 此时插入或者修改数据时,如果stuid的值在students表中不存在则会报错
  • 在创建表时可以直接创建约束
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id) ); 注:stuid指的是对这个表的哪个字段做外键,reference后边跟的是引用哪个表的哪个字段(一般就是主键)

外键的级联操作

数据库mysql高级

 数据库mysql高级

 把第一张表student中id=1的学生删了,那么在第二张表scores中所有stuid=1的学生都无效了,此时该怎么办?级联操作提供了四种方法

  • 在删除students表的数据时,如果这个id值在scores中已经存在,则会抛异常
  • 推荐使用逻辑删除,还可以解决这个问题
  • 可以创建表时指定级联操作,也可以在创建表后再修改外键的级联操作
  • 语法
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
  • 级联操作的类型包括:
    • restrict(限制):默认值,抛异常(报错)
    • cascade(级联):如果主表的记录删掉,则从表中相关联的记录都将被删除(把stuid=1的行都物理删除)
    • set null:将外键设置为空(把stuid=1的行都设为空)
    • no action:什么都不做(你删你的,我啥都不做)

连接简介

先看个问题

  • 问:查询每个学生每个科目的分数
  • 数据库mysql高级这样不好看呀,中国人都是记名字,谁去记编号啊
  • 分析:学生姓名来源于students表,科目名称来源于subjects,分数来源于scores表,怎么将3个表放到一起查询,并将结果显示在同一个结果集中呢?
  • 答:当查询结果来源于多张表时,需要使用连接查询
  • 关键:找到表间的关系,当前的关系是
    • students表的id---scores表的stuid
    • subjects表的id---scores表的subid
  • 则上面问题的答案是:
select students.name,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;join后边写表的名字,on后边写关系(scores和student的关系)这样多好看呀!!
  • 结论:当需要对有关系的多张表进行查询时,需要使用连接join

连接查询

  • 连接查询分类如下:
    • 表A inner join 表B:表A与表B匹配的行会出现在结果中
    • 表A left join 表B:表A与表B匹配的行会出现在结果中,外加表A中独有的数据,未对应的数据使用null填充(图形界面中操作)
    • 数据库mysql高级
    • 表A right join 表B:表A与表B匹配的行会出现在结果中,外加表B中独有的数据,未对应的数据使用null填充
  • 在查询或条件中推荐使用“表名.列名”的语法
  • 如果多个表中列名不重复可以省略“表名.”部分
  • 如果表的名称太长,可以在表名后面使用‘ as 简写名‘或‘ 简写名‘,为表起个临时的简写名称

练习

  • 查询学生的姓名、平均分
select students.name,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
  • 查询男生的姓名、总分
select students.name,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.name;
  • 查询科目的名称、平均分
select subjects.title,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;  分组操作:把科目相同的扔到一堆然后做聚合(平均)
  • 查询未删除科目的名称、最高分、平均分
select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;

 数据库mysql高级

自关联:物理上是一张表,逻辑上是多(三)张表;连接成功就是一张大表(比如学生和成绩在一起的大表

本表当中的某个字段引用这个表当中的主键(这个表当中的外键引用这个表当中的主键),自关联查询必须起别名(如果都一样都是areas就没法区分谁是谁了)

    数据库mysql高级

   数据库mysql高级

    reference后边跟的是自己,表示自引用

创建表:

 数据库mysql高级

查看表:

  数据库mysql高级

  数据库mysql高级

把areas.sql文件放在桌面上,命令:退到桌面,再连接数据库,再showdatabases,show tables

数据库mysql高级

 数据库mysql高级

 数据库mysql高级

 数据库mysql高级

数据库mysql高级

 数据库mysql高级

把市过滤掉:

 数据库mysql高级

 数据库mysql高级

 数据库mysql高级

视图

  • 对于复杂的查询,在多次使用后,维护是一件非常麻烦的事情
  • 解决:定义视图
  • 视图本质就是对查询的一个封装
  • 定义视图
create view stuscore as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;
stuscore是创建的视图的名字,为了它不和表名冲突,一般这个名字以v开头,视图和表时放在一起的

    数据库mysql高级

  • 视图的用途就是查询
select * from stuscore; 这一句就代表刚刚那一大坨代码,如果想修改这个视图用alter
 

事务

  • 当一个业务逻辑需要多个sql完成时,如果其中某条sql语句出错,则希望整个操作都退回
  • 使用事务可以完成退回的功能,保证业务逻辑的正确性
  • 事务四大特性(简称ACID)
    • 原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行
    • 一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致
    • 隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的
    • 持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障
  • 要求:表的类型必须是innodb或bdb类型,才可以对此表使用事务
  • 查看表的创建语句
show create table students;
  • 修改表的类型
alter table ‘表名‘ engine=innodb;
  • 事务语句
开启begin;
提交commit;
回滚rollback;

    表里有个数据叫1郭靖,比如说要做一个updat操作,首先执行begin把1郭靖这条数据锁起来,想删除修改这个数据都不行,然后在临时的一个表中把数据改成1小郭,再执行commit提交确定刚刚的更改,rollback放弃刚刚的操作(begin后边的      所有操作都放弃)还是原来的数据1郭靖(commit和roolback二选一,要么提交要么放弃)

示例1

  • 步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
终端1:
select * from students;
------------------------
终端2:
begin;
insert into students(sname) values(‘张飞‘);
  • 步骤2
终端1:
select * from students;
  • 步骤3
终端2:
commit;
------------------------
终端1:
select * from students;

示例2

  • 步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
终端1:
select * from students;
------------------------
终端2:
begin;
insert into students(sname) values(‘张飞‘);
  • 步骤2
终端1:
select * from students;
  • 步骤3
终端2:
rollback;
------------------------
终端1:
select * from students;

 

相关推荐