mysql查询+函数语句基本运用

1.0聚合函数

本次全文全是用oracle 9经典样表，下载链接。

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，前提数据是数字
AVG([DISTINCT] expr)	返回查询到的数据的平均值
MAX([DISTINCT] expr)	返回查询到的数据的最大值
MIN([DISTINCT] expr)	返回查询到的数据的最小值

举例

在数据库里使用mysql文件
\. 文件的绝对路径(点与文件的绝对路径之间存有空格)

统计员工有多少个

select count(*) from emp;

统计职位有多少个

select count(distinct job) from emp;

找出最高薪资的人

select max(sal) from emp;

找出最低薪资的人

select min(sal) from emp;

ground by子句的使用

• 在select中使用ground by之句可以对指定的列进行分组查询

select column1,column2,column3…… from table group by column;

案例

• 显示每个部门的平均薪水，和最高工资

select deptno,avg(sal),max(sal) from emp group by deptno;

• 显示每个部门里每种岗位的平均工资和最低工资

select avg(sal),min(sal),job,deptno from emp group by deptno,job; 

• 显示平均工资低于2000的部门和它的平均工资

统计各个部门的平均工资

select avg(sal) from emp group by deptno;

having 与group by配合使用，对group by结果进行过滤

select avg(sal) 平均工资,deptno from emp group by deptno having 平均工资<2000; 

having经常和group by搭配使用，作用是对分组进行赛选，作用有点类似于where

日期函数

函数名称	描述
current_date	当前日期
current_time	当前时间
date(datetime)	返回datetime参数的日期部分
date_add(date,interval d_value_type)	在date中添加时间或日期 interval 后的数值单位可以是yeat minute second day
date_sub(date,interval d_value_type)	在date中减去日期或者时间， interval后的数值单位可以是year minute second day
now()	当前时间日期

日期函数的运用

• 获取年月日

select current_date();

• 获取时分秒

select current_time();

• 获取当前的时间戳

select current_timestamp();

• 在当前的时间的基础上增加两天

select date_add(current_timestamp,interval 2 day);

• 计算两个日期直接相差多少天

select datediff(now(),date_add(now(),interval 2 day));

案例

• 创建一张表，记录生日

create table tmp(
id int primary key,
birthday date
);
insert tmp values(1,current_date());
insert tmp value(2,current_timestamp());

• 创建一个留言表

create table msg(
id int primary key auto_increment,
content varchar(30) not null,
sendtime datetime
);

插入数据

insert into msg(content,sendtime) values('hello',now());
insert into msg(content,sendtime) values('hello',now());
select * from msg;

字符串函数

charset(str)	返回字符串字符集
concat(string2[,……])	连接字符串
instr(string,substring)	返回substring在string中出现的位置，没有则返回0
ucase(string2)	转换成大写
lcase(string2)	转换成小写
left(string2,length)	从string2中的左边起读取length个字符
repalce(str,search_str,replace_str)	在str中用repace_str替换成search_str
strcmp(str1,str2)	逐字符比较两字符串的大小
length(str)	str的长度
substring(str,position[,……])	从str的position开始，取length个字符
ltrim(string) strim(string) trim(string)	去除前空格或后空格

案例

• 获取emp表的ename列的字符集

select charset(ename) from emp;

• 要求显示emp表中的信息，显示格式："XXX的职位是XXX,薪水是XXX。

select concat(ename,'的职位是',job,'薪水是',sal) 信息 from emp;

切记使用concat连用时，里面的符号都要为英文符号,进制出现中文符号

数学函数

函数名称	描述
abs(number)	绝对值函数
bin(decimal_number)	十进制转换成二进制
hex(decimal_number)	转换成十六进制
conv(number,from_base,to_base)	进制转换
ceiling	向上取整
floor	向下取整
hex(decimalNumber)	转换成十六进制
rand()	返回随机浮点数，范围(0.0,1.0)
mod(number,denominator)	取模，求余
format(number,decimal_places)	格式化，保留小数位数

• 绝对值

select abs(-100.2);

*向上取整

select ceiling(23.04);

• 向下取整

select floor(23.7);

• 保留两位小数位(小数四舍五入)

select format(12.345,2);

其他一些函数

• md5函数

md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串

select md5('abcd');

• ifnull函数

ifnull(val1,val2)，如果val1为空，则返回val2，否则返回val1

select ifnull('abc','123');
select ifnull(null,'123');

多表查询

• 多表查询的基本语法

select 字段1,字段2… from 表1,表2… [where……]……
多表查询，不加过滤，得到的结果称为笛卡尔积

• 案例 ：此时我们那salgrade表进行多表查询

select * from salgrade a,salgrade b;---使用同一个表的多表查询需要起别名

• 显示雇员名，雇员工资以及所在部门的名字

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

• 显示部门号为10的部门名，员工名和工资

select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;

• 显示各个员工的姓名，工资，及工资级别

select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal;

自连接

自连接是指在同一张表连接查询

• 显示员工 FORD 的上级领导的编号和姓名

select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');

• 使用多表查询(自查询)

select leader.empno,leader.ename from emp leader,emp worker where leader.empno=worker.mgr and worker.ename='FORD';

自查寻

单行自查询

• 返回一行记录的子查询

select * from emp where deptno=(select deptno from emp where ename='smith');

多行自查询

• in关键字的运用

select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10) and deptno<>10;

• all关键字：显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号

select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);

• any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);

多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句

• 案例：查询和SMITH的部门和岗位完全相同的所有雇员，不包含SMITH本人

select ename from emp where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith';

在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

• 案例
• 显示每个高于自己部门平均工资的员工的姓名，部门，工资，平均工资

select ename,emp.deptno,sal from emp,(select avg(sal) asl,deptno from emp group by deptno) tmp where emp.sal>tmp.asl and emp.deptno=tmp.deptno;

• 找出每个部门工资最高的人的姓名，工资，部门，最高工资
  原理？：打印姓名，工资，部门，最高工资
  表？：员工表 ，分组部门
  条件 部门编号，薪资相等

select ename,sal,job,max from emp,(select max(sal) max,deptno from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal=tmp.max;

合并查询

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

• 案例：将工资大于2500或职位是MANAGER的人找出来

select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='manager';

使用or语句的结果？？

select ename,sal,job from emp where job='manager'or sal>2500;

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

• 案例：将工资大于2500或职位为

select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='manage';

表的内外连接

表的连接分为内连接和外连接

内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。
语法：select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件
前面我们所学的都是内连接

外连接

外连接分为左外连接和右外连接

左外连接

语法：select 字段名 from 表名1 left join 表2 on 连接条件

• 当左表和右边表没有匹配时，也会显示左边表的数据

右外连接

语法：select 字段名 from 表名1 right join 表2 on 连接条件

• 当左表和右边表没有匹配时，也会显示右边表的数据