[大数据]数据库约束&&MySQL进阶查询

目录

• 数据库约束
• MySQL进阶查询

数据库约束

1.约束类型

NOT NULL - 指示某列不能存储 NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

DEFAULT - 规定没有给列赋值时的默认值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列或多个列的结合有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

create table students(
	id int not null,
	name varchar(20),
	telephone varchar(20),
	score decimal(4,2));

create table students(
	id int not null,
	name varchar(20),
	telephone varchar(20) unique,
	score decimal(4,2));

create table students(
	id int not null,
	name varchar(20) default 'unknow',
	telephone varchar(20) unique,
	score decimal(4,2));

create table students(
	id int primary key,
	name varchar(20),
	telephone varchar(20),
	score decimal(4,2));

-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
create table students(
	id int primary key auto_increment,
	name varchar(20),
	telephone varchar(20),
	score decimal(4,2));

foreign key (字段名) references 主表(列)

create table student1(
	id int,
	name varchar(20),
	class_id int,
	foreign key(class_id) references class(classId));
-- 注意：字段里面如果有关键字，例如desc，则必须用反引号 ` 引起来: `desc`

create table class(
	classId int primary key auto_increment,
	name varchar(20));

2.表的设计

大概思路：

1.明确需求场景

2.提取出需求中的"实体"，实体可以认为是"关键性的名词",类似于面向对象中的对象.

3.?一般来说,每个实体都会分配一个表来进行表示.

4.除了实体之外,还需要理清楚,实体和实体之间的关系.

5.实体之间的关系主要分三种:

一对一?, 例如 : 学生与学号之间的关系,一个学生只有一个学号,一个学号也只能对应一个学生.

一对多 ,?例如 :?学生与班级之间的关系,一个学生只属于一个班级,而一个班级可以包含多个学生.

多对多 ,?例如?:?学生与课程之间的关系,一个学生可以选择多门课程,一个课程也可以包含多个学生.

• 一对一

情况一:
create table account(
    account_id int primary key,
    password varchar(50));

create table student(
    student_id int,
    name varchar(20),
    account_id int
    foreign key(account_id) references account(account_id));


情况二:
create table student(
    student_id int primary key,
    name varchar(20));


create table account(
    account_id int,
    password varchar(50),
    student_id int,
    foreign key(student_id) references student(student_id));

一对一的表关系,外键既可以放在学生表,也可以放在学号表,无论是哪一种,多表查询的时候,学生表里的一条记录只能对应学号表里的一条记录.?一对一的表关系也可以都放在一张表里面,但是这样做,不方便管理,所以不建议这样做.

• 一对多

create table class(
    class_id int primary key,
    className varchar(10));

create table student(
    student_id int,
    name varchar(20),
    account_id int,
    foreign key(account_id) references account(account_id));

一对多的表关系,外键通常设定在多的一方,例如上面的学生,多表查询的时候,班级表里的记录可以对应多条学生表里的记录。

• ?多对多

create table student(
    id int primary key,
    name varchar(20));

create table course(
    id int primary key,
    name varchar(20));

create table score(
    id int primary key,
    score decimal(3,1),
    student_id int,
    course_id int,,
    foreign key (student_id) references student(id),
    foreign key  (course_id) references course(id));

?多对多的表关系,需要创建中间表来映射两张表的关系。

3.?新增

插入查询结果

-- 表结构一样的情况下
insert into student1 select * from student2;

1.聚合查询

1.1 聚合函数

• COUNT

-- 统计班上有多少个学生
select cont(*) from student;

-- 统计班上数学有成绩的同学个数,math为null的数据不会被统计
select count(math) from exam_score;

??count的以上两种用法针对null数据的区别：

?

• ?SUM

-- 统计班上数学成绩的总分
select sum(math) from exam_score;

-- 统计班上数学不及格成绩的总分
select sum(math) from exam_score where math < 60;

• AVG

-- 统计班上数学成绩的平均分
select avg(math) from exam_score;

-- 统计班上所有成绩的平均总分
select avg(math+english+chinese) from exam_score; -- 先算各科的平均分，然后相加

• MAX

-- 返回英语最高分
select max(math) from exam_score;

• MIN

-- 返回 > 70 分以上的数学最低分
select min(math) from exam_score where math > 70;

注意:聚合函数都是针对行与行之间的运算,前面的表达式查询是针对列与列之间的运算,这里要区分开来.

1.2 分组查询（group by）

select 中使用 ?group by? 子句可以对指定列进行分组查询。需要满足：使用 group by ? 进行分组查询时，select? 指定的字段必须是 “ 分组依据字段 ” ，其他字段若想出现在 select ? 中则必须包含在聚合函数中。

select column1, sum(column2), .. from table group by column1,column3;

【其他示例】：?

create table students(
	id int,
	name varchar(20),
	gender varchar(20),
	score decimal(4,2));


insert into students values(1,'张三','男',95);
insert into students values(2,'李四','女',75);
insert into students values(3,'王五','男',85);
insert into students values(4,'赵六','女',65);


-- 统计男生和女生各自的最高分，最低分，平均分
select gender,max(score),min(score)avg(score) from students group by gender;

?

?

1.3 having语句

?group by 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用?where?语句，而需要用having。

-- 统计男女各自的最高分、最低分、以及平均分，并且只显示平均分大于80的数据
select gender,max(score),min(score),avg(score) from students 
    group by gender having avg(score) > 80;

总结：

• 指定筛选条件的聚合查询

1.在聚合之前，进行筛选，针对筛选后的结果，再聚合 -> 使用 where 子句。
select gender,avg(score) from students where name != '赵六' group by gender;

2.在聚合之后，进行筛选，使用 having 子句
select gender,avg(score) from students group by gender having avg(score) > 80;

3.聚合前后都筛选
select gender,avg(score) from students where name != '张三' 
   group by gender having avg(score) > 80;

• 聚合查询的执行过程

1.先按照 where 条件进行筛选记录。
2.把筛选出来的结果按照 group by 来分组。
3.分组之后按照 having 再来进行筛选。
4.最后按照指定列中调用的聚合函数来显示计算结果。

2.联合查询

在讲联合查询之前，我们先了解一个东西---笛卡尔积。

笛卡尔积(排列组合)

2.1 内连接

【示例一】:

?

-- 许仙在student表,成绩在score表,所以第一步
1.select * from student,score；

-- 第二步
2.select * from student,score where id = student_id; -> 连接条件
-- 如果列名相同怎么办？
select * from student，score where student.id = score.student_id;

-- 第三步
3.select * from student,score where student.id = score.student_id 
    and student.name = '许仙';

-- 第四步
4.select name,course_id,score from student,score where student.id = score.student_id 
    and student.name = '许仙';

【示例二】:

查询所有同学的总成绩

-- 同学在student表,成绩在score表
select * from student,score;

-- 每个人的成绩都是行和行之间的关系,,所以计算总成绩需要聚合查询
select * from student,score where student.id = score.student_id group by id;

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

-- 起别名
select name,sum(score)  as total from student,score where 
    student.id = score.student_id group by id;

我们之前在学表达式查询的时候,说过可以给表达式起别名,这里在多表查询的时候,也是可以给表起别名,看个人喜好吧,我不太喜欢起别名,但是后面讲自连接的时候,是必须要给表起别名的.

多表查询还可以使用 join ..?on

1.select * from student inner join score on student.id = score.student_id; --两张表
2.select student.name,course.name,score.score from student join course  -- 三张表
    join score on student.id = score.student_id and course.id = score.course_id;
-- inner 可以省略
-- 当使用join计算笛卡尔积的时候，后面的条件使用on来表示，而不是where，这个相当于固定搭配

?总结多表查询的一般步骤:

1.根据需求理清楚想要的数据都在哪些表中。
2.[核心操作] 先针对多个表进行笛卡尔积。
3.根据连接条件，筛选出合法数据，过滤掉非法数据。
4.进一步增加条件，根据需求做更精细的筛选。
5.去掉不必要的列保留最关键的信息。

2.2?外连接

外连接分别左外连接和右外连接,,,

1.左外连接 :?(以 join 左侧的表为主，保证左侧的表每个记录都能体现在结果中，如果左侧表在? 右侧表中不存在，则填充null).

2.右外连接 :?(以 join 右侧的表为主，保证右侧的表每个记录都能体现在结果中，如果右侧表在左侧表中不存在，则填充null).

3.场景 :?学校考试后统计数据时，我们希望包含那些缺考的同学，就用外连接；我们不希望包含那些缺考的同学，就使用内连接

4.区别 :?

• 内连接：我们取得是两张表的交集
• 左外连接：我们取得是左表
• 右外连接：我们取得是右表

2.3?自连接

自己和自己笛卡尔积，，

【示例】 ：查询哪个同学的课程 id3 的成绩比课程 id1 的成绩高，显示出同学的学号（行和行之间的比较）

-- 自连接，必须给表起别名
1.select * from score as s1,score as s2;

-- 连接条件筛选合法数据
2.select * from score as s1,score as s2  where s1.student_id = s2.student_id;

-- 因为我们的条件查询中的比较运算符中的大于小于，是针对列和列之间的，
-- 而这里的自连接恰好将行和行转化成了列和列。
3.select * from score1 as s1,score as s2 where s1.student_id = s2.student_id 
    and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

-- 去除不必要的列
4.select s1.student_id from score as s1,score as s2 where 
    s1.student_id = s2.student_id and s1.course_id = 3 
    and s2.course_id = 1 and s1.score > s2.score;

2.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询，，

【示例】：查询与“不想毕业” 同学的同班同学

-- 正常步骤
1.select classes_id from exam_score where name = '不想毕业';  --> classes_id = 1
2.select name from exam_score where classes_id = '1';

-- 子查询
select name from exam_score where classes_id 
    = (select classes_id from exam_score where name = '不想毕业');

2.5 合并查询（union）

【示例】：查询id小于3，或者名字为“英文”的课程：

-- 条件查询
select * from course where id < 3 or name = '英文';

-- 合并查询
select * from course where id < 3 union select * from course where name = '英文';

• ?union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

• union all

该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。

?本期博客就到这里了，谢谢观看！~ ！