数据库学习 3 (MySQL)

数据库约束

约束类型

• not null
• unique
• default
• primary key
• auto_increment
• foreign key
• check

综合演示

创建班级表和学生表

create table class (id int primary key auto_increment,name varchar(20));
create table student(id int primary key auto_increment,name varchar(20), classId int, foreign key(classId) references class(id));

class中id为主键;
student中id为主键,classId为外键;
sql语句中 foreign需要指定student 中哪个属性是外键,references 指定以哪个表的属性作为参考.

外键的设置需要指定三方面信息

• 指定当前表中的哪列进行关联
• 指定和哪张表关联
• 指定和目标表中的哪列关联

后续往student中插入数据时,mysql就会自动检查classId的值是否在class表的id列中出现过,如果没有出现过,就会插入失败.

使用外键会对插入操作的效率产生一定影响
外键约束也会影响表的删除
也会影响表的更新
如果真的把表删除了,那么student的classId列进行的任何操作也没有意义了

表的设计

一对一的关系

形如上方两个表,如果一个同学只属于一个班级,并且一个班只有一个人,就属于一对一的关系

一对多的关系

student表中的classId可能存在很多条记录,在这些记录中,很多classId可能是相同的,这些classId相同的记录就表示存在于相同的班级中.

多对多

例如有多个学生,又有很多课程,任意一门课程都能被多个学生选择,一个学生能选择多个课程.
为了描述同学相应的课程成绩,需要增加一个成绩表.

drop table student;
drop table class;

create table score (studentId int  , classId int  ,score int);
create table student(id int primary key auto_increment ,name varchar(20));
create table course(courseId int primary key auto_increment, name varchar(20));

insert into student values(null,'光子郎'),(null,'妙蛙种子'),(null,'暴龙兽'),(null,'天使兽');
insert into course values(null,'地理'),(null,'历史'),(null,'美术'),(null,'音乐'),(null,'体育');

产生的这些表由于是多对多的关系,score表中会看到courseId存在很多重复的数据,表示很多同学都修了这门课,很多student也存在重复,表示一个学生修了多门课程.

如果想找光子郎的历史成绩的要如何操作?

1. 先找到 光子郎 的studentId
2. 再找到 历史 的courseId
3. 结合这两个id 在score表中查询

子查询
先创建第四个表

create table student2(id int,name varchar(20));```

将student中的信息插入到student2中

insert into student2 select * from student;

子查询得到的列的数目,序号,类型,都得和被插入的表的属性的数量类型顺序一致

聚类查询
一般需要搭配MySQL中的内置函数
count 计算结果的行数

select count(*) from student;

输出4行

group by
把得到的查询结果集按照一定的规则分组(可能分成多个组)

先创建一个表,这个表里role 表示职业,salary表示工资.

insert into emp values(null,'大熊','学生',23),(null,'光子郎','战士',30),(null,'太一','战士',25),(null,'蒙毅','战士',35),(null,'玉漱','学生',21),(null,'浪客秦昊','歌手',500),(null,'列侬','歌手',600);

再看一下都有哪些职业

当以职业分组后

select role from emp group by role;

group操作之后 角色相同的记录会被分到一个组内

select role,avg(salary) from emp group by role;

有了 group by 之后就把role相同记录放到同一组中,avg就是针对每个组分别来求平均值.

查看每个职业的最高最低工资

select role,avg(salary),max(salary),min(salary) from emp group by role;

group by也可以结合一些条件对数据进行进一步筛选,不是使用where,而是having

查出所有平均工资大于等于30的职业和工资
having 是针对group by 之后的结果进行筛选
where 是针对原始数据进行筛选再进行group by
所以两者表达的含义是不一样的

select role,avg(salary) from emp group by role having avg(salary) >= 30;

多表查询

实现联合查询的基本机制:笛卡尔积
笛卡尔积实例:


然后 构造了一系列数据…
得到它的笛卡尔积
查询的时候要写 [表名].[列名]
sql select student.id , student.name ,score.student_id,score.score from student, score ;

很长很乱,没有添加约束条件,得到的就是笛卡尔积结果
在这个笛卡尔积中,大量的数据是没有意义的,只有studen.id = score.student_id,的记录才有意义.

添加条件:studen.id = score.student_id

select student.id , student.name ,score.student_id,score.score from student, score where student.id = score.student_id;

1.下面查找许仙的相关成绩

select  student.name ,score.score from student,score where student.id = score.student_id and student.name = '许仙'; 

此问题的解决思路:

1. 先拿两张表得到笛卡尔积
2. 按照student.id = score.student_id对笛卡尔积进行筛选,保留有意义的数据
3. 再对名字进行筛选

多表查询的另一种写法 join on

select student.id,student.name,score.student_id,score.score from student join score  on student.id = score.student_id and student.name = '许仙';

与上面的结果相同
2.查找所有同学的总成绩,以及同学的基本信息
筛选条件:
1.按照学生id来筛选掉无用数据
2.按照学生id进行分类,求总成绩

select student.id, student.name ,sum(score) from student,score where student.id = score.student_id group by student.id;  

3.查找所有同学每一门科目的成绩
解决步骤:

1. 先得到学生表和成绩表和课程表的笛卡尔积
2. 根据student.id = score.student_id 去掉无效数据
3. 根据course.id = score.course_id

select student.id ,student.name ,course.name,score.score from student, score ,course where student.id = score.student_id and course.id = score.course_id ;

此时,可以发现,没有同学名为"老外学中文"的相关成绩,这是因为笛卡尔积按照id筛选之后剩下的数据一定是在两张表中都出现过的,这样的过程就是内连接,相当于取两张表都有的数据

外连接 : 数据在student中存在,在score中不存在,或者student中不存在,score中存在,这样的记录也能查出来
左连接 : 数据在student中存在,在score中不存在这样的数据能查到,student中不存在,score中存在,这样的记录不能查出来
右连接 : 数据在student中存在,在score中不存在这样的数据不能查到,student中不存在,score中存在,这样的记录能查出来