目录
一.关系模型的简要概述
二.数据库的约束
2.1约束类型
2.2NULL约束
2.3UNIQUE:唯一约束
2.4 默认约束
2.5 PRIMARY KEY:主键约束
2.6 FOREIGN KEY:外键约束
2.7 CHECK约束
三.高效率查询
3.1高效率查询的分类
3.2聚合查询
3.2.1聚合函数
3.2.2 GROUP BY子句
3.2.3HAVING
3.3.联合查询
3.3.1联合查询的解析
3.3.2内连接
3.3.3外连接
3.3.4自连接
3.3.5子查询
3.3.6合并查询
四.总结
个人主页:tq02的博客_CSDN博客-C语言,Java,Java数据结构领域博主
本文由 tq02 原创,首发于 CSDN
本章讲解内容:MySQL的数据库的约束、数据的高效率查询欢迎各位→点赞 +收藏⭐ +评论+关注✨
注:在学习本文前,需要学会数据库的基本操作以及数据表中数据的增删查找
数据库的基本操作 学习链接:http://t.csdn.cn/LS3Dp
数据表的数据增删查找 学习链接:http://t.csdn.cn/78QIz
一.关系模型的简要概述
我们先来普及最基础的知识点—–关系模型的基本术语:
关系:一个关系对应一张表
属性:表中一列即为一个属性
元组:表中一行即为一个元组
域:某个属性的取值范围
候选码:关系中某个属性组的值能唯一标识一个元组,则该属性组为候选码。
主码:也称’主键’,当一个关系中有多个候选码,则可以选择任意一个为主码。
外码:也称’外键’,如果一个数据表中的一个属性是另一个数据表的主码,则为外码
候选码的详细讲解
例如上图,学号的值不会重复,每一个值可以标识一个元组。而姓名不可以,其中含有重复的值,也就是说候选码的条件是 属性中没有重复的值。但从定义中知道 这个是属性组,也就是说,一个表的候选码不唯一,而成绩的值也不存在重复,因此成绩也是候选码。
主码的详细讲解
就是从候选码中选择一个,作为主码。当然,选择主码应该要选择更合适的,例如学生成绩的表,如果再添加一组数据,那么成绩可能重复,所以为了安全起见,我们选择学号为主码
外码的讲解
从图中,我们可以看出,学生成绩表的候选码:学号,成绩。图书借阅表的候选码:图书编号
而当我们将学号作为学生成绩表的主码时,图书借阅表中的‘学号’属性便是学生成绩表的外码
二.数据库的约束
2.1约束类型
数据库的约束,其实约束的是数据表结构,控制每一列的存储数值。
约束类型:
- NOT NULL :限制列取值不为空。
- DEFAULT: 给定列的默认值。
- UNIQUE:限制列的取值不重复。
- CHECK:限制列的取值范围。
- PRIMARY KEY:指定本列为主码。
- FREIGN KEY :定义本列为引用其他表的外码。
2.2NULL约束
创建表时,可以指定某列不为空。
例如:创建一个学生表,并且指定学号不为空
CREATE TABLE student (id INT NOT NULL,sn INT,name VARCHAR(20),qq_mail VARCHAR(20));
2.3UNIQUE:唯一约束
创建表时,指定某一列的值不能重复。
指定 sn 列不可重复。
CREATE TABLE student (id INT NOT NULL,sn INT UNIQUE,name VARCHAR(20),qq_mail VARCHAR(20));
2.4 默认约束
创建表时,当输入的某字段值为空,可赋 默认值。
例如:指定插入数据时,name列为空,默认值tq02
CREATE TABLE student (id INT NOT NULL,sn INT UNIQUE,name VARCHAR(20) DEFAULT 'tq02',qq_mail VARCHAR(20));
2.5 PRIMARY KEY:主键约束
创建表时,指定某列为主键。
注:对于整数类型的主键,常配搭自增长auto_increment来使用。插入数据对应字段不给值时,使用最大值+1.
例如:指定 id 列为主键
CREATE TABLE student (id INT NOT NULL PRIMARY KEY,sn INT UNIQUE,name VARCHAR(20) DEFAULT 'unkown',qq_mail VARCHAR(20));
2.6 FOREIGN KEY:外键约束
创建表时,用于关联其他表的主码和唯一键
语法格式:foreign key (字段名) references 主表(列)
例子:先创建一个班级表,id为主码。
//创建班级表,有使用MySQL关键字作为字段时,需要使用``来标识CREATE TABLE classes (id INT PRIMARY KEY auto_increment,name VARCHAR(20),`desc` VARCHAR(100));
再创建一个学生表student,一个学生对应一个班级,一个班级对应多个学生,使用id为主码,class_id为外键,关联班级表id
-- 重新设置学生表结构DROP TABLE IF EXISTS student;CREATE TABLE student (id INT PRIMARY KEY auto_increment,sn INT UNIQUE,name VARCHAR(20) DEFAULT 'unkown',qq_mail VARCHAR(20),classes_id int,FOREIGN KEY (classes_id) REFERENCES classes(id));
2.7 CHECK约束
约束某个列的取值范围
约束性别,只能为男和女。
create table test_user (id int,name varchar(20),sex varchar(1),check (sex ='男' or sex='女'));
三.高效率查询
3.1高效率查询的分类
高效率查询分为2种,聚合查询和联合查询
聚合查询:对数据表中的一个字段进行部分或者全部进行统计查询。
联合查询:合并多个相似的选择查询的结果,等同于将一个表追加到另一个表,从而实现将2个表的查询组合在一起。
3.2聚合查询
3.2.1聚合函数
聚合函数,用于统计总数、计算平局值等操作。
常见的聚合函数:
函数 | 意义 |
COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的 总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的 平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的 最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的 最小值,不是数字没有意义 |
count使用:
//统计班级收集的 qq_mail 有多少个,qq_mail 为 NULL 的数据不会计入结果SELECT COUNT(qq_mail) FROM student;
sum使用:
-- 不及格 < 60 的总分,没有结果,返回 NULLSELECT SUM(math) FROM exam_result WHERE math < 60;
avg使用:
-- 统计平均总分SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
max使用:
-- 返回英语最高分SELECT MAX(english) FROM exam_result;
min使用:
-- 返回 > 70 分以上的数学最低分SELECT MIN(math) FROM exam_result WHERE math > 70;
3.2.2 GROUP BY子句
使用group by子句,可以指定列进行分组查询。
使用规定:select指定的字段必须是’分组依据字段‘,其他字段若是想出现,则必须包含在聚合函数中。
语法格式:select 字段名1,聚合函数名(字段名2)…..for table group by 字段名1,字段名2;
例子:
- 准备一个测试表,有id(主键),name(姓名),role(职位),salary(薪水)
create table emp(id int not null primary key auto_increment,name varchar(20) not null,role varchar(20) not null,salary numeric(11,2));insert into emp(name, role, salary) values('奥特曼','服务员', 1000.20),('灰太狼','游戏陪玩', 2000.99),('孙悟空','洗碗工', 999.11),('橙留香','收破烂', 333.5),('熊大','伐木工', 700.33),('火云飞','董事长', 12000.66);
- 查询每个职工的最高工资,最低工资,平局工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
看到这里的时候,我们会发现,原来聚合函数需要和group by组合一起使用才会有高效率啊。
3.2.3HAVING
GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用HAVING
例如:显示平均工资低于1500的员工和他们的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by rolehaving avg(salary)<1500;
3.3.联合查询
3.3.1联合查询的解析
联合联合,顾名思义,需要多表进行查询,而多表查询:对多张表的数据取笛卡尔积。
联合查询分为了5种:1.内连接 2.外连接 3.自连接 4.子连接5.合并查询
为了更好的讲述联合查询,我们可以先建立几个数据表,并往其中添加数据。
1.建立班级表,往其中加入数据
create table classes(classes_id int not null , descrtionvarchar(20) not nullprimary key);insert into classes(classes_id, descrtion) values(1, '学习了Java语言'),(2,'学习了中国传统文学'),(2,'学习了语文');
2.建立学生表,往其中添加数据
//创建学生表CREATE TABLE student (sid varchar(20) not nullprimary key ,name VARCHAR(20) comment '姓名',age INT comment '年龄',sex varchar(1) comment '性别',classes_id int comment '班级');insert into student(sid, name, age, sex, classes_id) values('09982','黑旋风李逵',18,'男',1),('00835','菩提老祖',19,'女',1),('00391','白素贞',22,'女',2),('00031','许仙',25,'男',1),('09527','孙悟空',300,'男',2);
3.建立课程表,往其中添加数据
create table course(name varchar(100) comment '课程名',course_id int comment '课程号');insert into course(name,course_id) values('Java',1),('中国传统文化',2),('语文',4),('高阶数学',5);
4.建立分数表,并往其中添加数据
create table score(grade int comment"分数",sid varchar(20) comment"学号", course_idint comment "课程号");insert into score(grade,sid,course_id) values(-- 黑旋风李逵(70, '09982', 1),-- 菩提老祖(60, '00835', 1),--白素贞(99, '00391', 2),(88, '00391',4),-- 许仙(67, '00031', 1),-- 孙悟空(81, '09527', 2),(37,'09527',4));
3.3.2内连接
内连接,也被称为自然连接,只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据,舍弃了不匹配的数据。
语法格式:
- select *from 表1 join 表2 [ on过滤条件 ] [ where查询条件 ] ;
- select *from 表1,表2 [ where查询条件 ] ;
- select *from 表1 inner join 表2 [ on过滤条件 ] [ where查询条件 ] ;
注,经常使用1来进行多表连接
查询白素贞的总分时,使用:
select student.name,sum(score.grade) as sum from student join score on student.sid=score.sid where student.name=’白素贞’;
当查询白素贞的个人所有信息时,我们使用:
select * from student join score on student.sid=score.sid where student.name=’白素贞’;
3.3.3外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。
1.左外连接,表1完全显示:
语法格式 :select 字段名 from 表名1 left join 表名2 on 连接条件[where条件查询];
注:表1 查询结果是所有数据,表2查询结果是与表1重合部分的数据。
2.右外连接,表2完全显示:
语法格式:select 字段 from 表名1 right join 表名2 on 连接条件[where条件查询];
注:表2查询的是所有数据,表 1查询结果是与表2重合的部分
联表查询中on和where的区别:
- on在内连接中可省略,外连接不可省略;
- on在内连接中执行效果和外连接执行效果不同;
- left join…on查询不能过滤掉左表中的数据,而内连接on查询可以过滤掉全局数据。
- 外连接中on和where不同,on筛选笛卡尔积过滤条件,where筛选具体业务
3.3.4自连接
自连接,顾名思义,在同一张表连接自身。
语法格式:select *from 表名 as t1,表名 as t2 where t1.id=t2.id [, …];
例如,查询分数表中,某个人一门成绩大于另一门的成绩
使用:select * from score as t1,score as t2 where t1.sid=t2.sid and t1.grade<t2.grade;
3.3.5子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
分两种,一种单行子查询,另一种多行子查询
- 单行子查询
返回一行记录的子查询
例如:查询与许仙同班的同学
- 多行子查询
返回多行记录的子查询
例如:查询Java和中国传统文学的分数
3.3.6合并查询
在实际操作中,我们需要合并多了select的执行结果,因此我们需要使用集合操作符union,union all。
- union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
例如:查询学生表中大于22岁的男性信息
- union all
该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。
结:这个很鸡肋,几乎没有用处,了解就好。
四.总结
我们需要先弄懂关系模型的各个专业术语概念,才能理解到约束条件的作用,接着接触到查询当中的聚合和联合查询。
聚合查询,使用的是函数方法,较为简单,更容易理解,但是联合查询更为麻烦,但是理解了外连接之后,其他连接方式就更加容易明白。
最后,如果本文有帮助到你们,我很开心,希望可以得到大家的赞同,谢谢大家,祝大家在数据库的世界里大显身手,未来前程似锦!!!