MySQL 学习笔记——排序查询、常见函数、分组查询、连接查询sql92

DQL语言的学习

排序查询

语法:
SELECT
  要查询的东西
FROM
  表
WHERE
  条件
ORDER BY 排序的字段\表达式\函数\别名 【ASC\DESC】
ORDER BY 子句在查询语句的最后面,除了LIMIT子句

#案例:查询部门编号>=90的员工信息,并按员工编号降序

SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;

常见函数

概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名

好处:1、隐藏了实现细节 2、提高代码的重用性
调用方式:select 函数名(实参列表) 【from 表】;

分类:

  1. 单行函数
    如 concat、length、ifnull等
  2. 分组函数
    功能:做统计使用,又称为统计函数、聚合函数、组函数

单行函数

  1. 字符函数:
      CONCAT 拼接
      SUBSTR 截取子串
      UPPER 转换成大写
      LOWER 转换成小写
      TRIM 去前后指定的空格和字符
      LTRIM 去左边空格
      RTRIM 去右边空格
      REPLACE 替换
      LPAD 左填充
      RPAD 右填充
      INSTR 返回子串第一次出现的索引
      LENGTH 获取字节个数
#案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来

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

#instr 返回子串第一次出现的索引,如果找不到返回0

SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
  1. 数学函数
      ROUND 四舍五入
      RAND 随机数
      FLOOR 向下取整
      CEIL 向上取整
      MOD 取余
      TRUNCATE 截断

  2. 日期函数
      NOW 当前系统日期+时间
      DATEDIFF 返回两个日期相差的天数
      CURDATE 当前系统日期
      CURTIME 当前系统时间
      STR_TO_DATE 将字符转换成日期
      DATE_FORMATE 将日期转换成字符

#now 返回当前系统日期 + 时间
SELECT NOW();

#查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCESELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS difference
FROM employees;

#curdate 返回当前系统的日期,不包含时间
SELECT CURDATE();

#curtime 返回当前时间,不包含日期
SELECT CURTIME();

#可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW());

#str_to_date 将字符通过指定的格式转换成日期。
SELECT STR_TO_DATE('2020-10-25','%Y-%c-%d') out_put;

#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') out_put;

#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
  1. 流程控制函数
      IF 处理双分支
      CASE语句 处理多分支
      情况1:处理等值判断
      情况2:处理条件判断
#1.if函数: if else 的效果

SELECT IF(10<5,'大','小');
#2.case 函数的使用一: switch case的效果

/*
switch(变量或表达式){
	case 常量1:语句1;break;
	...
	default : 语句 n;break;
}
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;(语句加分号;,值不能加分号)
when 常量2 then 要显示的值1或语句2;
...
else 要显示的值n或语句n;
end
*/

/*案例:查询员工的工资,要求
部门号 = 30,显示的工资为1.1倍
部门号 = 40,显示的工资为1.2倍
部门号 = 50,显示的工资为1.3倍
其他部门,显示为原工资
*/

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

# 3.case 函数的使用二:类似于多重if
/*
java中:
if(条件){
	语句1;
}else if(条件2){
	语句2;
}
...
else{
	语句n;
}

mysql中:

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n语句n
end

*/

/*案例:查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
*/

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

  1. 其他函数
      VERSION 版本
      DATEBASE 当前库
      USER 当前连接用户
      PASSWORD 返回字符的加密形式
      MD5 返回字符的MD5加密形式

分组函数

SUM 求和
MAX 最大值
MIN 最小值
AVG 平均值
COUNT 计数

特点:

  1. 以上五个分组函数都忽略NULL值,除了COUNT(*)
  2. SUM和AVG一般用于处理数值型
      MAX、MIN、COUNT可以处理任何数据类型
  3. 都可以搭配DISTINCT使用,用于统计去重后的结果
  4. COUNT的参数可以支持:
       字段 \ * \ 常量值,一般放1

效率:
MYISAM存储引擎下 ,COUNT(*)的效率高
INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些

所以一般使用count(*)用作统计行数

分组查询

语法:
  SELECT 查询的字段,分组函数
  FROM 表
  GROUP BY 分组的字段

特点:

  1. 可以按单个字段分组
  2. 和分组函数一同查询的字段最好是分组后的字段
  3. 分组筛选
针对的表位置关键字
分组前筛选原始表GROUP BY的前WHERE
分组后筛选GROUP BY后的结果集GROUP BY的后HAVING
  1. 可以按多个字段分组,字段之间用逗号隔开
  2. 可以支持排序
  3. HAVING后可以支持别名
#案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
SELECT 
  AVG (salary),
  department_id,
  job_id
FROM 
  employees
WHERE department_id IS NOT NULL
GROUP BY department_id,
  job_id
HAVING AVG(salary) > 10000;
ORDER BY AVG(salary) DESC;

连接查询 SQL92语法

含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询。

笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件

分类:

  1. 按年代分类:
    sql92标准:仅仅支持内连接
    sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
  2. 按功能分类:
    内连接:
    1. 等值连接
    2. 非等值连接
    3. 自连接
    外连接:
    1. 左外连接
    2. 右外连接
    3. 全外连接
    交叉连接

一、传统模式下的连接 :等值连接——非等值连接

  1. 等值连接的结果 = 多个表的交集
  2. n表连接,至少需要n-1个连接条件
  3. 多个表不分主次,没有顺序要求
  4. 一般为表起别名,提高阅读性和性能
    注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
  5. 可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
#一、sql92标准
#1、等值连接
#案例:查询每个工种的工种名和员工的个数,并且按员工个数降序

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#实现三表连接
#案例:查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;
#2、非等值连接
#案例1:查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
#3、自连接
#案例:查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值