~~接上回,我们简单的说了,库的基本操作,表的增删改查等操作,我们来学习,表操作的进阶
1.数据库的约束
1.1什么是数据库的约束:
数据库约束是对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。约束通常与一个表相关联,所有的关系数据库都支持对数据表使用约束,通过约束可以更好地保证数据表里数据的完整性。是表上强制执行的校验规则,除此之外,当表中数据存在相互依赖性时,可以保护相关数据不被删除。约束通常无法修改。
了解了什么是数据库的约束后,我们来看一看Mysql的约束都有哪些
1.2约束类型
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句(不支持)
其中的主键约束(primary key) 常常搭配auto_increment(自增)使用,例如快速入门Mysql(一)中建的表User我们就可以修改为
create table user(
userId int primary key auto_increment,
userName varchar(5),
birthday timestamp,
location varchar(50),
introduction text
);
primary key 约束确保字段非空且唯一;
这样修改后 我们在插入数据时就不需要管UserId字段了,mysql会自动的增长
插入数据
insert into user values(null,"小明","1999-02-03","西安市","小明是一名大学生");
运行后我们会发现,userId字段的值为1,继续插入数据,该字段会+1;
2.表的设计
表与表之间的关系:
- 1对1:就像人与身份证之间;
- 1对多:班级与学生之间;
- 多对多:学习选课,一个学生可以选择多门课程,一门课程也能被多个学生选择;
没有关系;
三种关系的之间表的设计
一对一:直接使用两张进行存储
一对多:这种情况,就需要创建外键,在关系为1的一方创建外键
以学生和班级为例:
create table class(
classId int primary key,
className varchar(10)
);
create table student(
stuId int primary key auto_increment,
stuName varchar(10),
classId int ,
foreign key (classId) references class(classId)
);
多对多:对于表之间为多对多的关系,就需要创建一张额外的表,这张表中存放的是两张关系表的主键,为这张额外表的外键
就以选课为例:
可以创建一张中间表成绩表
create table course (
id int primary key auto_increment,
name varchar(20)
);
中间表
create table score (
id int primary key auto_increment,
score decimal(3, 1),
stuId int,
courseId int,
foreign key (stuId) references student(stuId),
foreign key (courseId) references course(id)
);
3.查询
3.1聚合查询
3.1.1 聚合函数
函数 | 说明 |
---|---|
count([DISTINCT] expr) | 返回查询到的数据的数量 |
SUM([DISTINCT] expr) | 返回查询到数据的总和 不是数字没意义 |
AVG([DISTICT] expr) | 返回查到的数据的平均值 不是数字没意义 |
MAX([DISTICT] expr) | 返回查到的数据的最大值 不是数字没意义 |
MIN([DISTICT] expr) | 返回查到的数据的最小值 不是数字没意义 |
测试:
往student表中插入数据(因为student表中存在外键classId 所以先要往class表中插入数据)
insert into class values(1,"软件工程01班");
insert into class values(2,"软件工程02班");
insert into student values(null,"xiaoming",1);
insert into student values(null,"xiaohong",1);
insert into student values(null,"xiaoqiang",1);
insert into student values(null,"xiaozhang",2);
insert into student values(null,"xiaowang",2);
insert into student values(null,"xiaoyu",2);
insert into student values(null,"xiaoli",2);
- count() 统计有多少学生
其他几种和count 的使用方法一样
3.1.2GROUP BY(分组查询)
我们的表中有如软件工程01班的学生与软软件工程02班的学生
现在有如下的需求:
查询软件工程01班和软件工程02班各有多少人(需要用到group by)
select classId ,count(*) 人数 from student group by classId;
该sql语句适用于我们当前的情况,但如果没有班级,查询结果就会出现如下结果:
classId中会出现NULL,而不想让出现NULL该怎么做呢?
这就要提到我们后面要说的多表查询
因为classId在两张表中都存在,我们就要加以限定,在前面加上表名(这里我们给表起了个别名 student s class c)
select s.classId ,count(*) 人数
from student s
inner join class c
on s.classId=c.classId
group by s.classId;
查询结果:
也可以查询到班级名称(只需在要查询的字段中加上className即可)
3.1.3 Having
Having 与 where的意思相同,都是用来加条件的
只不过Having用于分组后,即在使用在GROUP BY 语句后
例如:只查询一班的人数
select s.classId ,className,count(*) 人数
from student s
inner join class c
on s.classId=c.classId
group by s.classId
having classId=1;
3.2联合查询
为什么会有联合查询呢?
向我们上面的几个查询中,通过对一张表进行操作的时候,发现查询结果不不是我们所期望的(查询一班二班的人数),所以在实际的业务中的数据往往来自多张表,所以就需要多表联合查询
说到联合查询,就不得不说笛卡尔乘积
在Mysql中多表联合查询的结果就是一个笛卡尔集
了解了多表查询的结果是笛卡尔乘积后,我们就可以学习多表的查询了
多表查询分为:
- 外连接
- 左外连接
- 右外连接
- 内连接
3.2.1内连接(无合适案例不做演示)
select 查询列表
from 表1 别名 【连接类型】
【inner】 join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
3.2.2
- 1.左外连接
select 查询列表
from 表1 别名
【连接类型】left join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
以表1为主表,表二为从表,如果表二中没有对应表1中的结果时候,为NULL
- 2.右外连接
select 查询列表
from 表1 别名
【连接类型】right join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】