MySQL-核心查询（二）

第一节单表查询

1.1排序

1）单列排序

语法结构：

SELECET 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC/DESC]

• ASC表示升序排列（默认）
• DESC表示降序排序
• 不影响真实数据

 

代码示例：

select * from emp e where salary>6000 ORDER BY e.salary DESC;

select * from emp order by salary;

 

2）多列排序

• 同时对多个字段进行排序，如果第一个字段相同，就按照第二个字段进行排序，依次类推

select * from emp order by salary desc, eid asc;
-- 排序完之后按照，另外一个条件进行排序

 

1.2聚合函数

• 函数：方法，它封装了一些逻辑，比如给他一堆数据，特定函数可以返回最大值max(),avg()平均值
• 聚合，也称为聚合统计或聚合查询，就需要使用select关键字，有select就得有from xxx

语法结构：

select 聚合函数（字段名） from 表名；

 

5个聚合函数：

聚合函数	作用
count（字段）	统计指定列不为NULL的记录行为
sum（字段）	计算指定列的数值和
max（字段）	计算指定列的最大值
min（字段）	计算指定列的最小值
avg（字段）	计算指定列的平均值

 

代码示例：

-- 1 查询员工的总数
select COUNT(DISTINCT eid) from emp;
select count(*) from emp;
select count(1) from emp; -- count函数会忽略掉空值

-- 2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
select sum(salary) as '总薪水', max(salary) '最大薪水', min(salary) '最小薪水', avg(salary) '平均薪水' from emp;

-- 3 查询薪水大于4000员工的个数
select count(*) from emp where salary>4000;

-- 4 查询部门为'教学部'的所有员工的个数
select count(*) from emp where dept_name='教学部';

-- 5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) as '市场部平均薪资' from emp where dept_name='市场部';

 

1.3分组

group by 函数

• 分组往往和聚合函数一起使用，对数据进行分组，分完组之后在各个组内进行聚合统计分析
• 比如：求各个部门的员工数
• 分组查询指的是使用GROUP BY 语句，对查询的信息进行分组，相同数据作为一组

语法格式：

SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件]；

• HAVING 是对聚合之后的结果进行进一步的过滤

 

代码示例：

需求1：

SELECT sex,avg(salary) from emp GROUP BY sex;

• -- 注意：

1）GROUP BY 的字段必须出现在前面SELECT的位置；

2）前面SELECT的位置，除了GROUP BY 的字段、聚合函数，不能出现其他字段；但在MySQL中其实是可以的

• 原句&原理：

需求2：

-- 0.查询所有部门的信息
select dept_name as '部门名称' from emp group by dept_name;

-- 1.查询每个部门的平均薪资
select dept_name,avg(salary) from emp GROUP BY dept_name;

-- 2.查询每个部门的平均薪资, 部门名称不能为null
select dept_name,avg(salary) from emp GROUP BY dept_name HAVING dept_name is not null;
-- where是GROUP BY之前过滤的
select dept_name, avg(salary) from emp where dept_name is not null GROUP BY dept_name;

需求3：

-- 查询平均薪资大于6000的部门
select dept_name, avg(salary) from emp WHERE dept_name is not NULL GROUP BY dept_name HAVING avg(salary)>6000;

where和having的区别

过滤方式	特点
where	where进行分组前的过滤 where后面不能写 聚合函数
having	having 是分组后的过滤 having后面可以写 聚合函数

 

1.4 limit 关键字--分页

• limit是限制的意思，用于限制返回的查询结果的行数（可以通过limit指定查询多少行数据）和python当中的head(10)有些类似
• limit语法是MySQL的方言，用来完成分页

语法结构：

select 字段1，字段2··· from 表名 limit offet, length;

 

参数说明：

• limit offet, length；关键字可以接受一个 或者两个为0 或者正整数的参数
• offset 起始行数，从0开始记数，如果省略 则默认为0， 也就是要查询的数据从第一条开始；
• length 返回的行数，也就是终止显示的行数

 

代码示例：

select * from emp limit 0,3;

/*

通常用于分页
每一页显示多少条记录往往是固定，PageSize=3（每页显示3条记录）
当前页pageNum=2

根据以上参数从MySQL中获取到第二页应该显示的那3条记录
第一页 limit 0,3
第二页 limit 3,3
第三页 limit 6,3
第n页  limit ?,3
起始行的偏移量该怎么计算？
（当前页-1）*每页条数
（n-1）*3

在程序中计算出来，然后传入到MySQL语句中，
最终拼接出类似于这样的语句：
select * from emp limit x,y;
和外部工程师结合得是比较多的
*/

 

第二节 约束

• 约束是针对字段的

常见的约束

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

 

2.1 主键约束

特点	不可重复 唯一 非空
作用	用来表示数据库中的每一条记录（唯一标识锁数据表中的一条记录）

 

1）添加主键约束

语法结构：

字段名 字段类型 primary key

 

代码示例：

①建表添加主键方式一,直接在创建字段的时候定义

create table emp2(
    eid int primary key,
    ename varchar(20),
    sex char(1)
);

② 建表添加主键方式二，也可以在设置好字段之后加上主键

create table emp3(
    eid int,
    ename VARCHAR(20),
    sex char(1),
    primary key(eid)
);

③ 给已经存在的数据表添加主键约束

alter table emp add primary key(eid);

那些数据可以作为主键？

• 通常根据业务去设计主键，在实际生产过程中每张表都会设计一个主键
• 是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义没有关系，只要能够保证不重复就好，比如身份证号码etc
• 另外，如果没有和业务关联太大的可以设计为主键的列的话，我们在进行数据库设计的时候往往人为加一列作为主键列，习惯上起名为id，rid等：12345

 

2）删除主键

alter table emp drop primary key;
desc emp;

 

3）主键的自增

• 主键如果让我们自己添加有可能重复，我们通常希望在每次插入新记录的时候，数据库自动生成关键字段的值
• 关键字：

AUTO_INCREMENT 表示自动增长（字段类型必须是整数类型）

 

代码示例：

-- 创建主键自增的表
create table epm2(
    -- 关键字 AUTO_INCREMENT，主键类型必须是整数类型
    eid int primary key auto_increment,
    ename VARCHAR(20),
    sex char