开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL_Ch_2 -> 正文阅读

[大数据]MySQL_Ch_2

MySQL_Ch_2

1.特殊比较字符

特殊的比较运算符
– 1) between...and...
– 格式: 列名between下限and上限

– 包含区间是含两个端点等价大于等于并且小于等于

– 查询emp表中,20号部门月薪在1000-2000之间的员工信息
```
select * from emp where deptno = 20 and sal >= 1000 and sal <= 2000;
```
– 不要按照数学公式方式写区间0 <= sal <= 2000;
```
select * from emp where deptno = 20 and sal between 1000 and 2000;
```
– 下限和上限可以使用数值、文字(字符串)、日期

– 通常使用最多的是数值(表示数值范围)、日期(表示日期范围)

– 文字(使用很少,例如:英文名字可以按照字母顺序表示)

– 查询emp表中,在1982年入职的员工信息
```
select * from emp where hiredate between '1982-1-1' and '1982-12-31'
```
– 英文字母顺序
```
select * from emp where ename between 'A' and 'B';-- 后者端点不包含
```
– 上限和下限可以反着写,但是你的语句虽然不报错,但是逻辑上是不行的
```
select * from emp where deptno = 20 and sal between 2000 and 1000;
```
in

– 格式:列名 in(值1,值2,...)

– 等价于多个or的关系组合

– 查询所有30号部门,其上司的员工编号是7902、7698、7788的员工信息
```
select * from emp where (mgr = 7902 or mgr = 7698 or mgr = 7788) and deptno = 30;
```
```
select * from emp where mgr in(7902,7698,7788)and deptno = 30;
```
– 针对单个值,也可以使用in,它的执行效率要低,实际中有时需要写in

– =等号是无法与null进行匹配
like(重要)

– 格式: 列名like包含统配字符的字符串

– 模糊查询

统配字符 (% _)

– % :0-n个任意字符

– _ :1个任意的字符

– 查询所有名字首字母是B的员工信息
```
select * from emp where ename like 'B%';
```
– 查询倒数第二个字符是T的员工信息
```
select * from emp where ename like '%T_';
```
关键字:escape 转义 将通配字符转义为普通的字符含义

ESCAPE '/' 表示'/'为换码字符，在查询时使用可以方便查询到原本就含有'_'、'%'的值。

– 如果要查询到名字在四个字符以上、倒数第三个字符为d、倒数第二个字符为_的教师的全部信息：
```
select * from prof where Pname like '%d/__' escape '/';
```
– 查询名字为HaHa%Ha的员工信息
```
select * from emp where ename like '%/%%' escape '/';
```
– escape后面需要指定一个符号

– like一般针对文字的模糊查询使用较多

is null

– 用于筛选空值的

– 查询没有奖金的员工信息

错误写法:select * from emp where comm = 0 or comm = null;-- 错误写法,null不能写成=等号

select * from emp where comm = 0 or comm is null;

– 查询公司的Boss是谁(查询其编号为空值的)

select * from emp where mgr is null;

not

– 逻辑非关系

– 可以与上述4个特殊比较运算符一起使用

– not between ... and...

– not in(...)

– not like ...

– is not null (英文语法设置)
ALL

(min, max)

小于ALL ==> 小于min

大于ALL ==> 大于max
ANY

(min, max)

小于ANY ==> 小于max

大于ANY ==> 大于ANY

等于ANY ==> 大于等于min && 小于等于max 类似于in

2.排序

排序

– order by 排序

– group by 分组

– 升序asc(默认) 和降序desc

– 数值、日期、文字(字母日期)

– 查询所有10号部门的员工信息,按照月薪升序排序
```
select * from emp where deptno = 10 order by sal asc;-- 实际写法
```
– 按照多列排序

– 查询员工姓名,年收入,特定是20号部门,按照年收入的升序排序,按照入职日期的降序排序

– 第二次排序是要建立在第一次排序的基础上
```
select ename,(sal + ifnull(comm,0))*12 as 年收入
from emp 
where deptno = 20 
order by 年收入 asc,hiredate desc; -- 使用别名可以进行排序
```
– 查询年收入高于15000的所有员工信息
```
# 错误语法,这里别名不能使用
select ename,(sal + ifnull(comm,0))*12 as 年收入
from emp where 年收入 > 15000;
-- Unknown column '年收入' in 'where clause'
```
– SQL语句执行顺序

– 书写顺序: select ... from... where ... order by

– 执行顺序: from ... where ... select ...order by

– 因此别名在这里where不能使用进行判断
```
select ename,(sal + ifnull(comm,0))*12 as 年收入
from emp where (sal + ifnull(comm,0))*12 > 15000;
```

3.练习

练习

查询员工信息,按照部门编号升序排列,按照月薪降序排列
```
select * from emp order by empno asc ,sal desc;
```

查询在1982期间入职的,其上司的员工编号是7782,7788的员工信息

select * from emp where (hiredate between '1982-1-1'and '1982-12-31') and mgr in(7782,7788);

查询名字中包含至少一个o,至少一个T的员工信息

select * from emp where ename like '%o%T%'or ename like'%T%o%';

查询员工姓名,月薪,奖金数,按照年收入升序排列

select ename as 姓名,sal as 月薪,comm as 奖金数 from emp order by (sal + ifnull(comm,0))*12;

查询在10号部门工作,月薪在2000-3000之间的员工信息

select * from emp where deptno = 10 and sal between 2000 and 3000;

4.MySQL的函数

– 函数:官方软件中已经给我们封装了一些固定的功能

– 我们只需要了解对应的函数名称,知道如何调用即可

– 分为两大类

– 1) 单行函数

– 2) 多行函数(分组函数、聚合函数)

1.单行函数

– 只对一行数据进行变换,每一行都会返回一个结果

字符函数
1. upper()小写转大写,针对英文字母
2. lower()大写转小写,针对英文字母 – 中文不会转换
  
  查询一个结果,将一个字符串从小写转为大写;使用upper()函数
```
select upper('hGhE') from dual;
```
  – dual在MySQL中属于关键字;在oracle中属于虚表;
```
# 在oracle中可以使用:	select * from dual;
```
3. 字符截取函数
  1. substring(str,n,len)获取一个子字符串
    
    str:被截取的字符串
    
    n:开始截取的位置,mysql中索引位是以1开始
    
    len: 截取字符个数
```
select substring('0123456',3,3) from dual;
```
```
# 查询emp表中,员工姓名前三个字母
select substring(ename,1,3) from emp;
```
4. 字符查找函数
  
  – instr(str1,str2)
  
  – 在str1中寻找(不是遍历,及只找第一次出现的位置)str2字符串,返回一个位置,str2在str1中的位置
  
  – 如果没有查到,返回0
```
select instr('I Like GiRL','K') from dual; #5 空格也是一个字符;不区分大小写
```
5. 字符串拼接函数
  
  –concat(str1,str2)
  
  – 多个字符串拼接
```
select concat('haha','hehe','xixi') from dual;
```
6. 字符串替换函数
  
  –replace(str1,str2,str3)
  
  – 在str1字符串中,使用str3来替换全部的str2
```
select replace('ABCDEF','CD','HAHA') from dual;
```
7. 字符串的长度函数
  
  – length(str)
  
  – 返回的是一个字符串的长度
```
select length('abcde'),length('哈哈') from dual; #5 6 mysql中一个汉字占3个Unicode编码长度
```
8. 字符串补齐函数
  
  – lpad(str1,n,str2)左补齐函数
  
  – 将字符串str1使用str2在其左侧补充n个长度
  
  – rpad(str1,n,str2)右补齐函数
  
  – 将字符串str1使用str2在其右侧补充n个长度
```
select lpad('abc',6,'*'),lpad('abc',2,'*')from dual;	#	***abc	ab
```
9. 去除函数trim()
  
  – 去除前后字符函数,函数能去掉字符串前后的空格,或者去掉字符串首尾特定的字符.
```
select trim('   hell  oworld') from dual;
select trim('h' from 'helloworldhh') from dual; # elloworld
select length('   hell  oworld'),length(trim('   hell  oworld'))from dual;
```
数值函数
1. round(n1,n2) 四舍五入
  
  – 将n1值四舍五入,精确到小数点后n2位
  
  – n2表示负数时,将小数点看成0,小数点左边就是1,2…;小数点左边依次为-1,-2,-3…
```
select round(3.1415,3),round(3.14,0),round(16.14,-1),round(16.14,-2)from dual;	# 3.142	3	20	0
```
2. trunca(n1,n2)舍去函数
  
  – 将n1数舍去,精确到小数点后n2位
```
select truncate(3.1415,3),truncate(3.14,0),truncate(6.14,-1)from dual;
```
3. mod(n1,n2)取余函数
  
  – 求n1除以n2后得到的余数
```
select mod(5,3),mod(5,-3),mod(-5,3),mod(-5,-3) from dual;	# 2 2 -2 -2
```
  – 判断依据:余数是正还是负,取决于被除数

日期函数

now()

– 获取数据库所在服务器的当前系统时间

select now() from dual;	#MySQL特有的
select sysdate() from dual;	-- system date 系统时间	通用写法
select curdate()from dual;  #2020-11-11
# cur_开头的函数都与时间有关

时间差值函数

– timediff(date1,date2):返回的是两个日期的差值,是一个数值

– datediff(date1,date2):返回两个日期相差的天数

– 假设emp表中所有的员工至今都未离职,计算他们的司龄

select datediff(now(),hiredate)/365 from emp;	#只能算大概
select timediff(now(),hiredate) from emp;	#格式对应才能准确,小时分钟秒要有都有
select timediff(curdate(),hiredate) from emp;#大概
select year(curdate())-year(hiredate) from emp;	-- MySQL中准确的

追加/追减函数

– 追加 date_add(date,interval expr type)

– 追减 date_sub(date,interval expr type)

– 设置公司的试用期是6个月,计算emp表中所有的员工的转正日期
```
select date_add(hiredate,interval 6 month) from emp;
```
– 查询工龄大于或者等于35年的员工信息
```
select * from emp where (year(curdate())-year(hiredate)) >= 35;
select * from emp where date_sub(curdate(),interval 35 year) >= hiredate;
```
1. last_day(date)查询所在月份的最后一天的日期
```
select ename,hiredate,last_day(hiredate) from emp;
```
1. round()、trunc()、extract()函数
  
  – round
  
  – 在MySQL中,round()函数是将日期变为字符串
```
select round(now()) from dual;
```
  – 在oracle中,round()函数用法:月份中15号含15号之前的舍去,16含16之后的进位
  
  – 查询1981年入职的员工姓名,入职日期
```
select ename,hiredate,round(hiredate,'month') from emp where substr(round(hiredate,'month'),3,2) = 81;
```
  – trunc 舍去
  
  – extract 抽取日期
```
select ename,hiredate,extract(month from hiredate) from emp;
```
2. 日期的格式化函数
  
  – 日期的格式化:date_format(date,format)
  
  – 时间的格式化:time_format(time,format)
  
  – format 指定的日期格式或者是指定时间格式
```
select date_format(now(),'%Y/%m/%d %H:%i:%s') from dual;
select date_format(now(),'%Y年%m月%d日 %H:%i:%s') from dual;
select now() from dual;		#2020-11-10 16:43:03
```

转换函数

– MySQL中转换函数:日期格式转换、大小写转换

– oracle中转换函数:to_date、to_char、to_number
通用函数

– 控制语句实际中一般不使用，描述程序的执行流程

– 基本上大多数的控制在Java语言中进行描述
```
select case when 1 > 2 then 'true' else 'false' end; #false
```
– if(expr1,expr2,expr3)

– 如果expr1是true,则if语句返回的值是expr2,否则返回的是expr3
```
select if(1 > 2, 2, 3); # mysql规则不是很规范
select if(1 > 2, 2, 3) from dual;
```

多行函数(分组函数)

有多条记录生成一组统计出的数据结果
数据库标准叫法:行(记录) 列(属性)

统计型的函数
1. 求和 sum()函数
```
select sum(sal) from emp;
```
2. 求平均值(使用最多) avg()函数
```
select avg(sal) from emp;
```
3. 最大值max()函数
```
select max(sal) from emp;
```
4. 最小值min()函数
```
select min(sal) from emp;
```
5. 统计个数(计数)(使用最多) count()函数
  
  查询emp中,有多少个员工
```
select count(ename) from emp;
```
  查询emp中,有多少个部门
```
select count(distinct deptno)from emp;
```
6. 分组 group by 依据…分组
  
  查询各个部门的工资平均值
```
select deptno,avg(sal) from emp group by deptno;
```
```
select empno,deptno,avg(sal) from emp group by deptno;#错误写法
```
  – 注意:
  
  – 只有使用了分组函数(sum() avg() max() min() count()等）,才能使用group by
  
  – 只有在group by 中出现的列,才能写在select中
  
  – 在MySQL中如果select 中的列,没有出现group by中,可以执行,但是逻辑是不对的.
  - group by 多次分组
    
    – 第二次分组是建立在第一次分组的基础上
    
    分组先后是有顺序区别顺序的,结果相同,列的输出顺序不同
    
    – 查询每个部门不同岗位的平均工资
```
select deptno,job,avg(sal) from emp group by deptno, job; # 每个部门不同岗位
select deptno,job,avg(sal) from emp group by job, deptno; #顺序前后有区别,这个与上面那个结果是相同的
```
    – 查询10号部门和20部门的平均工资
```
select deptno avg(sal) from emp where deptno in(10,20) group by deptno;
```
7. having 专门用于分组函数的筛选,代替了where的查询
  
  – 只有在where 中出现分组函数时,才用having替换.
  
  – group by ... having ...
  
  – 查询平均工资高于2500的部门id和平均工资
```
select deptno,avg(sal)
from emp where avg(sal) > 2500 -- 数据库是无法区分到底以什么分组
group by detpno  #错误写法
```
```
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2500;
```

SQL语句书写顺序和执行顺序

  -- 只有在`where` 中出现分组函数时,才用`having`替换.
  
  -- 书写顺序: `select ... from ... where ... group by ... having ... order by...`
  
  -- 执行顺序: `from ... where ... group by ...having ... select ... order by ...`

练习（以employees表）

查询Nancy Greenberg 的员工信息

select * from employees where first_name = 'Nancy' and last_name = 'Greenberg';

查询职位是AD_VP,并且在1990年前入职的员工信息

select * from employees where job_id = 'AD_VP' and hire_date < '1990-1-1';

select * from employees where job_id = 'AD_VP' and substr(round(hire_date,'month'),1,4) < 1990;

查询月薪在10000-20000之间的且部门编号是90的员工信息

select * from employees where (salary between 10000 and 20000) and department_id = 90;

查询司龄高于20年的员工信息,按照月薪升序排列

select * from employees where (year(curdate())-year(hire_date)) > 20 order by salary asc;
##
select * from employees where date_sub(curdate(),interval 20 year) > hire_date order by salary asc;
# oracle 特有函数:months_between()函数可以计算两个量之间的相差月数

查询年收入高于25万的员工信息

select * from employees where ((1 + ifnull(commission_pct,0)) * salary*12) > 250000; 

select * from employees where ((salary + ifnull(commission_pct,0) * salary)*12) > 250000;

查询职位以SA开头的所有员工信息

select * from employees where job_id like('SA%');

select * from employees where substr(job_id,1,2) = 'SA';

查询上司是145、146、147、149的所有员工信息

select * from employees where manager_id in(145,146,147,149);

查询公司的老板信息

select * from employees where manager_id is null;

查询不属于任何部门的员工信息
```
select * from employees where department_id is null;
```
- 分组练习题

查询每个岗位的平均工资,最高工资和最低工资

select job_id,avg(salary), max(salary),min(salary) from employees group by job_id;

查询月薪高于8000的员工,按照岗位查询平均工资


select avg(salary) from employees where salary > 8000 group by job_id;

查询各部门,各个经理负责的员工的平均工资

select  distinct department_id, manager_id ,avg(salary) from employees group by department_id,manager_id;
##
select avg(salary) from employees group by department_id,manager_id;

查询平均工资高于8000的岗位id和平均工资

select job_id, avg(salary) from employees group by job_id having avg(salary) > 8000;

查询司龄高于20年的员工的平均工资

select avg(salary) from employees where (year(curdate()) - year(hire_date)) > 20;

查询1995年之前入职员工的最高工资和最低工资

select max(salary), min(salary) from employees where hire_date < '1995-1-1';

查询各个部门的部门id和部门人数

select department_id, count(*) from employees where department_id is not null group by department_id;

查询各个部门部门id和月薪的总和

select department_id, sum(salary) from employees where department_id is not null group by department_id;

查询在1992-1993年间入职员工平均工资

select avg(salary) from employees where hire_date < '1993-1-1' and hire_date >= '1992-1-1'; 
##
select avg(salary) from employees where hire_date between '1992-1-1' and  '1993-12-31';

查询各部门平均工资在8000元以上的部门id及平均工资。

select department_id,avg(salary) from employees group by department_id having avg(salary) > 8000;

查询工作编号中不含有“SA_”字符串及平均工资在8000元以上的工作编号及平均工资，并按平均工资降序排序。

select job_id,avg(salary) 
from employees 
where job_id not like 'SA/_%' escape '/' 
group by job_id
having avg(salary) > 8000
order by avg(salary) desc;
####
select job_id,avg(salary)
from employees
where instr(job_id,'SA_') = 0 ---可变的
group by job_id
having avg(salary) > 8000
order by avg(salary) desc;

查询工作不为AD_PRES，工资的和大于等于25000的工作编号和每种工作工资的和。

select job_id,sum(salary)
from employees
where job_id not like 'AD/_PRES' escape '/'
group by job_id
having sum(salary) > 25000;
####
select job_id,sum(salary)
from employees
where job_id <> 'AD_PRES'
group by job_id
having sum(salary) > 25000;

显示经理号码，这个经理所管理员工的最低工资，不包括经理号为空的，不包括最低工资小于3000的，按最低工资由高到低排序。
```
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >=3000
order by min(salary) desc;
```

显示公司员工总工资及平均工资。

select sum(salary),avg(salary) from employees;

查询未分配部门的雇员信息。

select * from employees where department_id is null;

查找职位是FI_ACCOUNT或者工资超过16000的职位是AD_VP的员工信息。

select * from employees where job_id = 'FI_ACCOUNT' or (job_id = 'AD_VP'and salary >16000);

相同职位且经理相同的员工平均工资。

select distinct job_id,manager_id ,avg(salary)
from employees
group by job_id, manager_id;

大数据最新文章

实现Kafka至少消费一次

亚马逊云科技：还在苦于ETL？Zero ETL的时代

初探MapReduce

【SpringBoot框架篇】32.基于注解+redis实现

Elasticsearch：如何减少 Elasticsearch 集

Go redis操作

Redis面试题

专题五 Redis高并发场景

基于GBase8s和Calcite的多数据源查询

Redis——底层数据结构原理

加:2021-11-24 08:01:04 更:2021-11-24 08:01:38

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/24 9:00:20-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码

[大数据]MySQL_Ch_2

MySQL_Ch_2

1.特殊比较字符

统配字符 (`%` `_`)

关键字:`escape 转义` 将通配字符转义为普通的字符含义

2.排序

3.练习

4.MySQL的函数

1.单行函数

多行函数(分组函数)

分组先后是有顺序区别顺序的,结果相同,列的输出顺序不同

SQL语句书写顺序和执行顺序

练习（以employees表）

[大数据]MySQL_Ch_2

MySQL_Ch_2

1.特殊比较字符

统配字符 (% _)

关键字:escape 转义 将通配字符转义为普通的字符含义

2.排序

3.练习

4.MySQL的函数

1.单行函数

多行函数(分组函数)

分组先后是有顺序区别顺序的,结果相同,列的输出顺序不同

SQL语句书写顺序和执行顺序

练习（以employees表）

统配字符 (`%` `_`)

关键字:`escape 转义` 将通配字符转义为普通的字符含义