MYSQL4天速成--第二天

MYSQL4天速成–第二天

常见函数

select 函数名（实参列表） from 表；

特点：
1.叫什么（函数名）
2.干什么（函数功能）
分类：
1.单行函数（concat、length、ifnull 等）
2.分组函数
功能：做统计用，又称统计函数、聚合函数、组函数

字符函数

• length

select length （‘John’）;   字节
show variables like '%char%';

• concat 拼接字符串

select  concat(lastname ,'-',firstname)姓名 from  employees;

• upper lower 大小写

select upper('jobn')；
select lower('joHn');

将姓变成大写，名小写，然后拼接

select concat(upper(lastname),lower(firstname)) 姓名 from employees;

• substr substring
  注意：索引从1开始，两者都是截取字符串
  如果只有一个参数，两者的作用都一样：都是是截取字符串从当前下标以后直到字符串最后的字符串片段。
  如果有2个参数：
  substr ：第二个参数是截取字符串的长度（从起始点截取某个长度的字符串）；
  substring: 第二个参数是截取字符串最终的下标 （截取2个位置之间的字符串,‘含头不含尾’）。

select substr ('李莫愁爱上陆展元'，7) ；    陆展元
select  substr('李莫愁爱上了陆展元'，1,3)；   李莫愁
select  substring('李莫愁爱上了陆展元'，1,3)；   莫愁

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

select concat (upper(substr(lastname,1)),lower(substr(lastname,2))) output from employees;

• instr 返回字符串第一次出现的索引，如果找不到返回0

select instr ('你是谁啊'，'你是') as output;

• trim 去掉空格（空格）

select trim(' 张翠山 ') as output；
select trim('a' from 'aaaaaa张aaaa翠山aaaaaa') as output;  张aaaa翠山

• lpad 用指定的字符实现左填充指定长度，超过截断

select lapd('殷素素',10,'*') as output；  *******殷素素

• rpad 右填充

select rpad ('殷素素'，12，'ab') as output；

• replace 替换

select replace ('周芷若爱上了张无忌'，'周芷若'，'赵敏') as output;    赵敏爱上了张无忌

数学函数

• round 四舍五入

select round(1.45);
select round(1.457,2);保留几位

• ceil 向上取整 返回大于等于该参数的最小整数

select  ceil（1.52）; 2 (1.002) 2

• floor 向下取整 返回小于等于该参数的最大参数

select floor (-9.99);

• truncate 截断

select truncate(1.65,1);  1.6

• mod 取余

select mod(0.3); 1   第一个数是正结果就为正 第一个结果为负 结果就为负
mod  取余  mod(a,b):a-a/b*b
mod(-10,-3):-10-(-10)/(-3)*(-3) =-1

日期函数

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

select now();

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

select curdate();

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

select curtime();

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

select year(now()) 年；
select year('1998-1-1')年；
select month (now())月；

将日期格式的字符转换成指定格式的日期 str_to_date

str_to_date('9-13-1999','%m-%d-%Y')

date_format:将日期转换成字符

date_format('2018/6/16','%Y年%m月%d日')

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

select * from employees  where data= ‘1992-4-3’；
select  * from  employees where data = str_to_date('4-3 1992','%c-%d %Y');

查询有奖金的员工名和入职日期

select lastname,datatime(date,'%m月/%d日 %y年') 入职日期 from  employees where expression_pet is  not null;

其他函数

select veraion();
select database();
select user();

流程控制函数

• if函数：if else的效果

select lastname,commion_pet,if(commion_pet is null,'没奖金'，'有奖金') 备注 from employees；

• case函数的使用

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

查询员工的工资 要求：部门号30显示工资1.1倍 部门号40显示工资1.2倍…其他的为原工资

select salary,department_id,
case department_id
when 30 then  salary*1.1
when 40 then  salary*1.2
else salary
end as 新工资
from employees；

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和 avg平均值 max min count
参数类型：1.sum avg 一般用于处理数值型 max min count 一般处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct 搭配实现去重
4.count 函数详细介绍

select  count（*） from employeees;
select count(1) from employees;
效率myisam引擎下，count（*）效率高 innodb引擎下 count(*)和count(1)差不多

5.和分组函数一同查询的字段有限制（group by）
查询公司员工工资的最大值、最小值、平均值、总和

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

查询员工表中的最大入职时间和最小入职时间的相差天数

select  datediff(max(hiredate),min(hiredate)) diffence from employees;
datediff 日期函数 返回两个日期之间的时间

查询部门编号为90的员工个数

select count（*） from employees where department_id =90;

可以使用group by 子句将表中的数据分成若干份，where 一定放在from 后面

select 分组函数，列（要求出现group by的后面）
from 表
where 筛选条件 
group by 分组的列表
order by 子句

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段
特点：1.分组查询中的筛选条件分为两类
分组前的筛选：数据源是原始表，位置放在group by子句的前面 关键字用where
分组后的筛选：数据源是分组后的结果集 位置放在group by 子句的后面 关键字是having

分组函数做条件肯定是放在having子句中
能用分组前筛选的就优先考虑使用分组
2.group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或者函数（用的较少）
3.也可以添加排序（放在最后）

查询每个工种的最高工资

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

查询每个位置上的部门个数

select count(*),location_id from department group by location_id;

查询邮箱中包含a字符的，每个部门的平均工资

select avg(salary),department_id from employees where email like '%a%' group by department_id;

查询有奖金的每个领导手下员工的最高工资

select max(salary),manager_id
from employees
where commsion is not null
group by manager_id; 

查询哪个部门员工个数大于2
分步骤讨论：1.查询每个部门的员工个数
2.根据第一个结果进行筛选，查询哪个部门的员工个数大于2

select count(*),department_id
from employees
group by department_id
having count(*)>2;

查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

select job_id,max(salary)
from employees
where commsion is not null 
group by job_id
having  max(salary)>12000;

查询领导编号大于102的每个领导手下的最低工资>500的领导编号是哪个以及最低工资

select manager_id,min(salary)
from employees
where manager_id>102
group by manager_id
having min(salary)>500;

按表达式或函数分组
按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些

select count(*),length(lastname)
from empoyees
group by length(lastname)
having count(*)>5;

按多个字段分组
查询每个部分每个工种的平均工资

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

添加排序
查询每个部门每个工种的员工平均工资，并且按平均工资的高低显示

select avg(salary),department_id,job_id
from employees
group by job_id,department_id
order by avg(salary) desc;

查询各job_id的员工工资的最大值、最小值、平均值、总和、并按job_id升序

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

查询员工最高工资和最低工资的差距

select max（salary）-min(salary) deiffence from employees;

查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select min(salary),manager_id
from employees
where manager_id is not null
group by manager_id
having min(salary)>=6000;

查询所有部门的编号，员工数量和工资的平均值，并按平均工资降序

select department_id,count(*),avg(salary)
from employees
group by job_id
order by avg(salary) desc;

选择具有各个job_id的员工人数

select count(*) ,job_id
from employees
group by job_id;

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
分类：按功能分类：内连接（等值连接、非等值连接、自连接）、外连接（左外连接、右外连接、全外连接）、交叉连接

内连接

等值连接
查询女神名和对应的男神名

select name,boyname
from boys,beauty
where beauty.boyfriend_id = boy.id; 

查询员工名和对应的部门名

select lastname,department_id
from employees,departments
where employees.department_id = departments.department_id;

查询员工名、工种号、工种名(起别名)
起别名的好处：
1.提高简洁度
2.区分多个重名的字段
注意：如果为表起了别名，则查询的字段就把不能使用原来的表名去限定

select lastname,e.job_id,job_title
from employees as e,jobs j
where e.job_id = j.job_id;

可以加筛选 用 and
查询有奖金的员工名、部门名

select job_id,department_id
from employees e,department d
where e.department_id = d.department_id
and e.commision_pet is not null;

可以加分组
查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select department_name,d.manager_id,min(salary)
from departments d ,employees e
where d.department_id = e.department_id
and commsion_pet is  not null 
group by department_name;

可以加排序
查询每个工种的工种名和员工个数，并且按员工个数降序

select job_title,count(*)
from employees e ,job j
where e.job_id = j.job_id
group by job_title
order by count(*)desc;

可以三表连接
查询员工名、部门名和所在的城市

select lastname,department_name,city
from employees e ,departments d , location l
where e.department_id = d.department_id
and d.location_id = l.location_id;

非等值连接

查询员工的工资和工资级别

select salary,grade_level
from employees e,job_grades g
where salary between g.lower_sal and g.high_sal;

自连接
查询员工名和上级的名称

select m.employees_id,n.employees
from employees m,employees n;

sq99语法

select 查询列表
from 表1 别名 连接类型
from 表2 别名 on  连接条件
where 筛选条件
group by 分组
having 筛选条件
order by 排序列表


连接类型：内连接 inner、外连接（左外 left outer 、右外  right outer)全外 full）、交叉连接 cross

内连接

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；
分类：等值、非等值、自连接
特点：可以添加排序、分组、筛选
inner可以忽略
筛选条件放在where 后面，连接条件放在on 后面

外连接

应用场景：用于查询一个表中有，另一个表没有的记录
特点：1.外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null值
外连接查询结果=内连接查询的结果+主表中有而从表中没有的记录
2.左外连接，left左边的是主表
3.右外连接，right右边的是主表
4.左外和右外交换两个表的顺序，可以实现相同的效果