Mysql查询操作进阶

---

一、聚合函数

mysql数据库中内置的一些数据统计函数；

1. count(*)：统计结果条数

统计英语不及格的人数：

select count(*) from stu_tb where en<60;

2. sum(fields_name)：对指定字段数据求和

统计语文成绩之和：

select sum(ch) from stu_tb;

3. avg(fields_name)：对指定字段数据求平均值

统计语文平均成绩：

select avg(ch) from stu_tb;

4. max(fields_name)：对指定字段求最大值

求语文成绩最高的：

select max(ch) from stu_tb;

5. min(fields_name)：对指定字段求最小值

求语文成绩最低的：

select min(ch) from stu_tb;

二、分组查询—group by … having

以指定字段为依据，对表中数据进行分组统计。

操作：group by … having

示例：员工表中，包含有：员工姓名。员工岗位，员工薪资。

查询1：公司中都有哪些岗位，每个岗位的最大薪资是多少？最小薪资是多少？平均薪资是多少？

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

查询2：查找公司中平均薪资大于10000的岗位？

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

注意事项：

1. 分组查询，以表中指定字段对表中数据进行分组统计查询，
   但是查询结果中只能有分组依据字段以及聚合函数。
2. 分组查询，过滤条件不能使用where子句，而是使用having。

三、键值约束：表的约束

键值约束，起始就是对表中指定字段的描述以及约束。

1. 主键：primary key

约束表中指定字段，值不能为NULL，且数据不能重复。

create table pri2_table(id int primary key, name varchar(32),age int);

主键一张表中只能有一个，但是存在组合主键–以多个字段当做整体作为主键。

create table pri2_table(id int, name varchar(32), age int, 
primary key key_name(id, name));

2. 唯一键：unique key

约束表中的指定字段，值不能重复。

create table uni_table(id int primary key, 
name varchar(32) unique key, age int unique, sex int, unique key uk(sex));

NULL值并不触发唯一约束。

3. 非空约束：NOT NULL

约束表中指定字段，值不能为NULL。

create table nn_table(id int primary key, name varchar(32) not null);

当表中没有指定主键，但是指定了非空且唯一字段，则这个约束会升级为主键约束。

4. 外键约束：foreign key (…) references ….

限制当前指定字段的数据，必须在另一张表中指定字段数据中存在。

create table class_table(id int primary key, info varchar(32));

create table stu_table(id int primary key, name varchar(32),
class_id int, foreign key(class_id) references class_table(id));

使用外键可以让表中的数据更加严谨，
例如，学生信息中的班级ID，必须在班级表中存在这个班级。

5. 默认值：default

当没有进行主动插入某个字段数据的时候， 则自动以默认值进行新增。

create table def_table(id int primary key, name varchar(32),
sex varchar(1) default "男");

6. 自增属性：auto_increment

只能针对整数的主键字段进行设置， 当不插入数据的时候默认从1开始自增。

create table auto_table(id int primary key auto_increment, name varchar(32));

注：当前面属性缺失后，并不会补全，而是以当前数字继续自增。

四、数据库表的设计

1. ER图-实体关系图

以方形作为实体，以圆形作为属性，以菱形作为关系，描述表与表之间的关系。

图形化表示实体之间的关系，通过不同的实体间的关系，决定如何设计以及关联数据库表。

1.1 一对一：表中必须具有唯一标识作为主键。

1.2 一对多：在多方表中添加少的一方的主键字段。

1.3 多对多：建立一个中间关系实体来关联两个实体。

2. 三大范式

范式：数据库表的设计规范

数据库是存储数据的仓库，但是数据并不是仅仅存储就够了，还有查询，
然而一旦数据库表的设计不够合理，则查询或者插入修改效率大大降低。

2.1 第一范式：表中所有字段必须保证原子特性。

例如：下表中的家庭信息和学校信息不具备原子特性；

当字段不具备原子特性时，则查询效率会大大降低，因为数据的查询需要通过模糊匹配才能实现。

2.2 第二范式：表中所有字段都必须与主键完全相关，而不能部分相关（主要针对组合主键的使用）

例如：下表中的订单信息表设计存在问题：

表中字段并没有和组合主键完全相关，导致表中出现大量的数据冗余。
修改后：

2.3 第三范式：表中字段必须与主键直接相关，不能间接相关。

例如：下表中的班主任信息数据产生了冗余：

修改：

五、多表联查-联合查询

将多张表的数据合并在一起进行查询。

1. 表中数据如何合并：取笛卡尔积

当对两张表中的数据进行取笛卡尔积，会构造一张新表，然后在新表中进行数据查询：

比如：1表中有100w数据，2表中有100w数据，笛卡尔积合并后会有：1w亿的数据量。
这样会导致数据查询效率变得非常低，因此我们通常在进行多表连接的时候进行一些合并规则设置：
例如：student.classes_id=classes.id；

设置这个合并规则后，只有符合规则的数据才会被合并到一起构建成新表。
在这种模式下，合并就有三种不同的方式：
具体sql操作：join … on …

2. 内连接—inner join … on …

对两张表进行连接，但是连接的时候只取出符合合并规则的数据交集。

内连接语句：inner join … on …

select * from classes inner join stu on classes.id=stu.classes_id;

3. 外连接

以两张表中某张表为基表，然后在另一张表中找到符合合并规则的数据，将其合并过来。

3.1 左连接—left join … on …

以左表为基表，在右表中查找符合规则的数据进行合并，没有符合规则的数据则连接数置NULL

左连接sql语句：left join … on …

select * from classes left join stu on classes.id=stu.classes_id;

3.2 右连接—right join … on …

以右表为基表，在左表中查找符合规则的数据进行合并，没有符合规则的数据则连接数置NULL；

右连接sql语句：right join … on …

select * from classes right join stu on classes.id=stu.classes_id;

4. 特殊连接—自连接：同一张表自己连接自己

例子：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息；
思想：将成绩表进行自连接，使用第一张成绩表中的计算机原理成绩，
与第二张成绩表中的Java成绩进行比较。
举例表：

sql语句：

结果：

5. 子查询

也是多表联查的一种，但是跟连接又有所不同，
它是将一条查询语句的结果，当做一张表，再次进行过滤查询。
简单理解：一条查询语句的条件，是另一条查询语句的结果。

5.1 单行子查询：返回一行记录的子查询

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

select * from student where classes_id=(
select classes_id from student where name='不想毕业');

5.2 多行子查询：返回多行记录的子查询

示例：
查询“语文”或“英文”课程的成绩信息：

5.2.1 [not] in 关键字

因为子语句结果有多条，因此不能直接使用等号进行判断，
而要使用包含in，表示只要在子查询结果中就符合条件。

– 使用in

查询“语文”或“英文”课程的成绩信息：

select * from score where course_id in (
select id from course where name='语文' or name='英文'); 

– 使用 not in
查询不是“语文”和“英文”课程的成绩信息：

select * from score where course_id not in (
select id from course where name!='语文' and name!='英文'); 

5.2.2 [not] exists 关键字

exists主要判断子语句是否有结果，或者说是一个真或假的判断判断；

查询“语文”或“英文”课程的成绩信息：
（1）先从score表中获取一条成绩信息；
（2）从course表中查询课程信息，课程必须是语文或英文，
前提是这个课程id必须与一个成绩的课程id相同；
（3）判断获取到的成绩的课程id，是否与课程信息的id相同；
（4）判断为真，则表示整条成绩信息可以获取，否则这条成绩信息被丢弃。

语句：

select * from score where exists (
select score.id from course course where (
name='语文' or name='英文') and course.id = score.course_id);

简单理解：
在score表中查询成绩信息，每取出一条，就会通过子查询判断是否是语文或者英文的课程成绩，如果是则取出，不是则丢弃。

5.2.3 in与exists对比

• in：先将子语句执行完毕，得到的结果放到内存中，然后进行主语句执行，然后逐个判断主语句结果是否在子集中，符合条件则取出来。
  适用于自语句查询结果不太多的情况。
• exists：先将主语句进行执行，取出一条结果，然后拿结果作为子语句的条件进行查询，有结果则为真，则数据符合条件取出，否则丢弃。
  适用于子语句查询结果较多的情况。

6. 合并查询—union & union all

将两条查询语句的结果合并到一起。

操作语句：union & union all

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

例子：查询语文和英语的成绩：
英语成绩：

select * from score where course_id =4;

语文成绩：

select * from score where course_id =6;

合并查询：

select * from score where course_id =4;
union
select * from score where course_id =6;