mysql两个分组字段 mysql分组拼接

转载

mob64ca1417b0c6 2023-08-24 12:15:42

文章标签 mysql两个分组字段字段 IT 表名 文章分类 MySQL 数据库

mysql的快速入门

详细介绍关于mysql中的查询语法的使用
首先介绍使用到的几张表的字段和关系
locations记录部门的位置、departments部门的信息、employees记录员工的信息、jobs记录工种的信息
job_grades更具薪资计算薪资等级。

mysql两个分组字段 mysql分组拼接_表名

查询的基础语法

语法：select 查询的列表 from 表名;
特点：

1、查询列表的可以是：表中中的字段，长量值，表达式，函数；
2、查询的结果是一个虚拟的表格

1、选择使用的数据库

user 表名

USE myemployees;

2、查询某个字段

SELECT 字段名 FROM 表名;

SELECT  last_name  FROM employees;`

3、查询多个字段

SELECT first_name,last_name,phone_number,job_id,commission_pct,manager_id,hiredate FROM employees;

SELECT * FROM employees;

4、查询常量

SELECT 100;
SELECT 'tom';

5、查询表达

SELECT 100%87;

6、查询函数

SELECT VERSION();#查看mysql的版本号

7、起别名（关键词AS）

方式一：（省略AS）

SELECT last_name “别名”,first_name “别名” FROM 表名;

SELECT last_name  "姓",first_name "名" FROM employees;

方式二：（不省略AS））

SELECT last_name AS “别名”,first_name AS “别名” FROM employees;

SELECT last_name AS "姓",first_name AS "名" FROM employees;

8、去重（关键词DISTINCT）

查询employees表中所有的工资

SELECT salary  "薪水" FROM employees;
SELECT  DISTINCT salary  "薪水" FROM employees;

查询employees表中所有的部门编号

SELECT  DISTINCT department_id  "部门编号" FROM employees;

9、字符串的拼接（关键词concat）

java 中的+ 号的含义：

--- 运算符  简单的加法运算
--- 字符串的拼接

mysql中的 + 号的含义
—就是运算符
‘123’+10== 将‘123’强转成数字进行运算
‘tom’+10== 将‘tom’转化为0进行运算
null+XXX;== 结果就是null
从employees表中将姓和名进行拼接

SELECT CONCAT(last_name,first_name)   "姓名"   FROM employees;

10、count函数的详细使用(统计数量)

效率：

MYISAM存储引擎下，COUNT（*）的效率高
INNODB存储引擎下，COUNT（*）和COUNT（1）的效率差不多，比COUNT（字段）要高一些

条件运算符

语法：
select 查询列表 from 表名 where 查询的条件
查询的条件分类:
一、简单的条件查询：> < = (!=) <> >= <=
二、按逻辑表达式进行筛选 && || ！ and or not
三、模糊查询 like 、between and、in、is null

1、查询工资大于12000的员工的信息

SELECT * FROM employees WHERE salary>12000;

2、查询部门编号不等于90的员工名称和部门编号

SELECT 
	CONCAT(last_name,first_name)  "员工姓名",department_id "部门编号"
FROM 
	employees
WHERE department_id <>	90;

3、查询工资在10000到18000之间的员工名、工资以及奖金。

SELECT 
	last_name ,
	salary,
	commission_pct
FROM 
	employees
WHERE
	salary>10000 AND salary<180000;

4、查询部门编号不是在90到110之间，或者工资高于15000的员工信息

SELECT
	*
FROM
	employees
WHERE 
	#department_id<90 OR department_id>110 OR salary>15000;
	 NOT(department_id>=90 AND department_id<=110) OR salary>15000;

5、查询员工名字中包含s的员工信息(like)
% 多个字符 _单个字符支持转义

SELECT 
	*
FROM 
	employees
WHERE  last_name  LIKE '%s%';
#============================================
SELECT 
	last_name
FROM 
	employees
WHERE  last_name  LIKE '_$%' ESCAPE '$';	#转义或者escape

6、查询员工编号在100到120之间的员工信息

SELECT 
	*
FROM 
	employees
WHERE 
	`employee_id` BETWEEN 100 AND 120;

7、查询员工的工种编号是IT_PROG IT_PROG FI_ACCOUNT中的一个的员工名和工种编号（in）

SELECT 
	last_name,job_id	
FROM 
	employees
WHERE 
	job_id='IT_PROG' OR job_id='IT_PROG' OR job_id='FI_ACCOUNT' ;
#========================================================
SELECT 
	last_name,job_id	
FROM 
	employees
WHERE 
	job_id IN('IT_PROG','IT_PROG','FI_ACCOUNT');

8、查询没有奖金的员工名和奖金率（判断null值 —>is null / is not null）

# = <>不能判断null值
SELECT 
	last_name,
	commission_pct
FROM  
	employees
WHERE
	commission_pct IS NULL;

9、查询员工号为176的员工的姓名，部门号和年薪

SELECT 
	CONCAT(`first_name`,`last_name`) "姓名",
	department_id "部门号",
	salary*12*(1+IFNULL(commission_pct,0)) "年薪"
FROM 
	employees
WHERE 
	employee_id=176;

排序查询（order by）

语法：（desc降序 asc升序）
select 查询列表
from 表名
where 筛选条件
order by 排序列表 desc|asc;
练习：

1、查询工种编号为IT_PROG或者AD_VP员工信息，,要去按照薪资从高到低

SELECT 
    *
FROM 
    employees
WHERE 
    job_id IN('IT_PROG','AD_VP')
ORDER BY
    salary DESC;

2、查询部门编号>=90的员工信息，要求按照入职的时间进行排序

SELECT 
	* 
FROM
	employees 
WHERE 
	department_id >= 90 
ORDER BY 
	TO_DAYS(hiredate);

3、按年薪的高低显示员工的信息和年薪【按照表达式从高到低】

SELECT * , salary*12*(1+IFNULL(1+commission_pct,0))	"年薪"
FROM employees
ORDER BY salary*12*(1+IFNULL(1+commission_pct,0));

4、按年薪的高低显示员工的信息和年薪【按别名排序 order by支持别名排序】

SELECT * , salary*12*(1+IFNULL(1+commission_pct,0))	"年薪"
FROM employees
ORDER BY "年薪";

5、按姓名的长度显示员工的姓名和工资【按函数排序】

SELECT CONCAT(first_name,last_name) "姓名" ,salary
FROM employees
ORDER BY LENGTH(CONCAT(first_name,last_name));

6、查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】

SELECT *
FROM employees
ORDER BY salary DESC,employee_id  ASC;

SQL函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名好处：
1、隐藏了实现细节
2、提高代码的重用性调用：select 函数名（实参列表）【from表】；特点：
①叫什么（函数名）
②干什么（函数功能）
分类：
1、单行函数
如concat、length、ifnul1等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
列举一些常见的函数使用

1、length获取参数值的字节个数

# 查询编码（根据编码查看中文占据几个字节）
SHOW VARIABLES LIKE "%char%";

SELECT LENGTH('12465ahs') ;
SELECT LENGTH('张三丰') ;

2、concat拼接字符串

SELECT CONCAT(first_name,'_',last_name) '姓名'  FROM employees;

3、upper（大写转化）、lower（小写转化）

SELECT UPPER('asdhg');
SELECT LOWER('aASDH');

4、substr、substring（下标从1开始）

SELECT SUBSTR('张三丰武功天下第一',2);

案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)))  FROM employees;

5、instr返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('augsd JHAJ','JH');

6、trim（取出前后空格）

SELECT TRIM('   ashdh  asdh asjdhi  oi ');

7、lpad用指定的字符实现左填充指定长度

SELECT LPAD('nauksghdf ',20,'*');

8、rpad用指定的字符实现右填充指定长度

SELECT RPAD('nauksghdf ',20,'*');

9、replace替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏')

10、ifnull函数（ifnull(exp1,0)）

如果表达式1的结果为null值，就去第二个参数

数学函数

1、round 四舍五入

SELECT ROUND(1.56,1);

2、ceil (向上取整，取得值是大于等于该参数的)

SELECT CEIL(1.00);
SELECT CEIL(1.002);
SELECT CEIL(-1.002);

3、floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(1.00);
SELECT FLOOR(1.002);
SELECT FLOOR(-1.002);

4、mod取余（mod(a,b) 相等 a-a/b*b）

SELECT MOD(15,2);
SELECT MOD(-15,2);

日期函数

1、now返回当前系统日期+时间

SELECT NOW();

2、curdate返回当前系统日期，不包含时间

SELECT CURDATE();

3、curtime返回当前时间，不包含日期

SELECT CURTIME();

4、可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

5、str_to_date将字符通过指定的格式转换成日期

SELECT STR_TO_DATE(NOW(),'%Y-%c-%D');

案例：查询入职日期为1992-4-3的员工信息

SELECT * FROM  employees WHERE hiredate=STR_TO_DATE('4-3 1992' , '%c-%d %y');

6、date_format将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%Y年-%c月-%d日');

案例：查询有奖金的员工名和入职日期（xx月/xx日xx年）

SELECT last_name,DATE_FORMAT(hiredate,'%Y年-%c月-%d日') FROM employees;

其他函数

SELECT USER();		#获取用户名
SELECT VERSION();	#获取数据库版本号

流程控制函数

1、if函数：if else的效果（有点和三目运算符类似）

SELECT  *,IF(commission_pct IS NOT NULL,'有','无') FROM employees;

2、和java中的switch case类似

case 要判断的表达式或者字段
   when 常量1 then 要显示的值1或者语句1
   when 常量2 then 要显示的值2或者语句2
   ...
   else 要显示的值n或者语句n
   end

案例1：查询员工的工资，要求部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

SELECT salary '原始工资',department_id '部门id',
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END    '新工资'
FROM
	employees;

案例2：查询员工的工资的情况如果工资20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示c级别
否则，显示D级别

SELECT	last_name,
CASE 
WHEN salary>20000 THEN 'A级别'
WHEN salary>15000 THEN 'B级别'
WHEN salary>10000 THEN 'C级别'
ELSE 'D级别'
END   '工资级别'
FROM employees;

分组函数

功能：做统计使用的函数，又称为聚合函数或者统计函数或者组函数
分类：
sum 和；
count 数量；
avg 平均值；
min 最小值；
max 最大值；。。。
特点：
1、sum和avg一般处理数值型参数
count，min和max处理任何参数类型的值
2、以上函数都会回忽略null值的
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count（*）用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段（oracle）
sql执行的顺序：（仅仅总结的是自己用到的供参考）

SELECT 
		  department_id,
		  job_id,
		  AVG(salary) 
	FROM
		  employees 
	WHERE 	  department_id IS NOT NULL
	GROUP BY  department_id,
		  job_id 
	HAVING    AVG(salary) >10000
	ORDER BY  AVG(salary) DESC ;
	limit	...

关于分组的一点小总结：
1、分组查询中的筛选条件分为两类

数据源		      位置        		   关键字
分组前筛选            原始表		  group by子句的前面       where
分组后筛选            分组后的结果集    group by子句的后面      having

①分组函数做条件肯定是放在having子句中
②能用分组前筛选的，就优先考虑使用分组前筛选

2、group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求，表达式或函数（用得较少）

3、也可以添加排序（排序放在整个分组查询的最后）

4、关于where和having后面的条件的使用位置问题：
**where：**如果添加的条件在原始数据库表中可以找到的话
**having：**添加的条件在原始数据库表中【不】可以找到
即需要从原始表查询获取到的新表中再次进行判断的时候

0、引言：查询每个部门的平均工资

SELECT * FROM employees;
SELECT  department_id,AVG(salary) FROM employees GROUP BY department_id;

1、查询每个工种的最高工资（根据工种进行分组）

SELECT job_id,MAX(salary) FROM employees GROUP BY job_id;

2、查询每个位置上的部门个数（根据位置id进行分组）

SELECT COUNT(*),location_id FROM departments GROUP BY location_id;

添加筛选条件

3、查询邮箱中包含a字符的，每个部门的平均工资（根据部门id进行分组）

SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

4、查询有奖金的每个领导手下员工的最高工资（根据领导进行分组）

SELECT manager_id,last_name,MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

5、查询哪个部门的员工个数>2（根据部门id进行分组）

COUNT(*)的结果是分组之后才出现的结果（而且是聚合函数），所以查询条件放在group by 后面，做进一步的筛选

SELECT department_id , COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*)>2;

6、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

最高工资在原始表中不存在（是分组查询后的结果）所以加在HAVING后面

SELECT  job_id, MAX(salary) 
FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary) > 12000 ;

7、查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

SELECT   MIN(salary), manager_id 
FROM  employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING MIN(salary) > 5000 ;

按表达式或函数分组

8、按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

SELECT LENGTH(last_name) "姓名长度" ,COUNT(*) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;

9、查询每个部门每个工种的员工的平均工资（按照部门和工种进行分组）

SELECT department_id,job_id,AVG(salary)  FROM employees GROUP BY department_id,job_id;

10、查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示

（按照部门和工种进行排序）（按照平均工资排序）

SELECT 
  department_id,
  job_id,
  AVG(salary) 
FROM
  employees 
GROUP BY department_id,
  job_id 
ORDER BY AVG(salary) DESC ;

11、查询每个部门每个工种的员工的平均工资,部门编号不为null，工种编号，平均工资大于10000,并且按平均工资的高低显示

（考察了对条件的添加和位置）（where和having的使用）

SELECT 
  department_id,
  job_id,
  AVG(salary) 
FROM
  employees 
WHERE department_id IS NOT NULL
GROUP BY department_id,
  job_id 
HAVING AVG(salary) >10000
ORDER BY AVG(salary) DESC ;