MySQL学习笔记——SQL语句操作_3（拉勾教育数据分析实战训练营学习笔记）

SQL语句操作_MySQL核心查询1

1 单表查询

1.1 排序

• 通过 ORDER BY 子句,可以将查询出的结果进行排序
• 排序只是显示效果,不会影响真实数据
• 语法格式

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

• 示例

#1 单列排序
-- 默认升序（ASC）
select * from emp order by salary; 
select * from emp order by salary ASC;
-- 降序排序（DESC）
select * from emp order by salary DESC; 

#2 组合排序 同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推
select * from emp order by salary DESC,eid DESC; 

1.2 聚合函数

• 纵向查询，对某一列的值进行计算，然后返回一个单一的值
• 语法格式

SELECT 聚合函数(字段名) FROM 表名;

五个常用聚合函数：

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

• 示例

-- 查询员工总数
select count(*) from emp;
select count(1) from emp; -- count(1)与count(*)效果一样
select count(eid) from emp; 
select count(dept_name) from emp; -- count()会忽略掉空值

-- 查询员工总薪水、最高薪水、最低薪都会、平均薪水
select 
	sum(salary) '薪水总和',
	max(salary) '最高薪水',
	min(salary) '最低薪水',
	avg(salary) '平均薪水'
from emp;

1.3 分组

• 分组往往和聚合函数一起时候，对数据进行分组，分完组之后在各个组内进行聚合统计分析
• 分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
• 语法格式

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

• 注意：
  • 分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作
  • 除分组字段和聚合函数外，查询其他字段都是没有意义的
    分组过程：
    1. 按分组字段，将分组字段相同的数据分为一组
    2. 返回每组的第一条数据
• 示例

-- 通过性别字段进行分组，求每个组的平均薪资
select sex,avg(salary) from emp group by sex;
-- 查询每个部门的平均薪资
select dept_name,avg(salary) from emp group by dept_name;
-- 查询每个部门的平均薪资，部门名称不能为null
select dept_name,avg(salary) from emp where dept_name is not null group by dept_name;

• 条件过滤

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

• 示例

-- 查询平均薪资大于6000的部门
select 
	dept_name,avg(salary) 
from 
	emp 
where 
	dept_name is not null 
group by 
	dept_name 
having 
	avg(salary) > 6000;

1.4 LIMIT关键字

• 作用
  • 限制返回的查询结果的行数（可以通过limit指定查询多少行数据）
  • limit 语法是MySql的方言，用来完成分页
• 语法格式

SELECT 字段1,字段2... FROM 表名 LIMIT offset,length;

• 参数说明

参数	说明
offset	起始行数, 从0开始记数, 如果省略 则默认为 0
length	返回的行数

• 示例

-- 查询emp表中，从第0条开始，查询3条
select * from emp limit 0,3;
select * from emp limit 3;

-- 分页 每页显示3行记录
/*
	每页显示多少条记录往往是固定的，假设pageSize=3（每页显示3条记录），当前页 pageNum=2

	根据以上参数从MySQL中获取到第二页应该显示的哪3条记录？
	第一页 limit 0,3
	第二页 limit 3,3
	第三页 limit 6,3
	第n页  limit ?,3
	
	起始行的偏移量该怎么计算呢？
	（当前页-1）*每页条数
	(n-1)*3
	
	select * from 表名 limit (pageNum-1)*pageSize,pageSize;
*/
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
select * from emp limit 9,3;

2 SQL约束

• 作用：进一步限制表中数据，保证数据的正确性、有效性、完整性
• 注意：约束是针对字段的
• 常见约束：主键、唯一、非空、外键

2.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);

• 删除主键

-- 删除主键
alter table emp drop primary key;

• 主键自增

-- 创建表时指定主键，同时主键自增
create table emp2(
	-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型 
	eid INT PRIMARY KEY auto_increment,
	ename VARCHAR(20),
	sex CHAR(1)
); -- )auto_increment = 1000; 设置自增开始的值，默认为0

• 不同删除方式对自增的影响

清空表数据的方式	特点
DELETE	只是删除表中所有数据,对自增没有影响
TRUNCATE	将整个表删除掉,然后创建一个新的表自增的主键,重新从1开始

delete from emp2; -- 继续
truncate table emp2; -- 从头开始

2.2 非空约束

• 特点：某一列不允许为空
• 关键字：NOT NULL
• 语法格式

字段名 字段类型 NOT NULL

2.3 唯一约束

• 特点：某一列不允许重复
• 关键字：UNIQUE
• 语法格式

字段名 字段类型 UNIQUE

2.4 外键约束

• 关键字：FOREIGN KEY
• 具体见多表查询

2.5 默认值约束

• 特点：指定某列的默认值
• 关键字：DEFAULT
• 语法格式

字段名 字段类型 DEFAULT 默认值

• 示例

-- 默认值约束 default
create table emp3(
	eid INT PRIMARY KEY auto_increment,
	-- 为ename字段添加默认值
	ename VARCHAR(20) DEFAULT '黎明',
	sex CHAR(1)
); 
-- 使用默认值添加数据
insert into emp3(ename,sex)  values(DEFAULT,'男');

文章内容来源于拉勾教育数据分析实战训练营