cgb2105-day02

一，Mysql的基础函数

–1，常见函数

lower–全转小写
upper–全转大写
length–求长度
concat–拼接串
substr–截取字符串
replace–替换字符串
ifnull–对null元素的替换
round–对小数四舍五入
ceil–对小数向上取整
floor–对小数向下取整
now–获取当前的年月日时分秒
year–获取日期里的年
month–获取日期里的月
day–获取日期里的日
hour–获取日期里的时
minute–获取日期里的分
second–获取日期里的秒
转义字符 ’

–2，测试

#基础函数
#lower全转小写
select dname from dept
select dname,lower(dname) from dept
#upper全转大写
select dname,lower(dname),upper(dname) from dept
#length求长度,一个字母或数字占1一个字节,一个汉字占3个字节
select dname,length(dname),loc,length(loc) from dept

#substr截取字符串
SELECT dname,SUBSTR(dname,2) from dept #从第二个字符开始都截取完
SELECT dname,SUBSTR(dname,2,5) from dept #从第二个字符开始,截取出来5个
#concat拼接字符串
select dname,concat(dname,'hello') from dept #在原来值的后面拼接hello
#replace替换
select dname,replace(dname,'a','666') from dept#把a都换成666
#ifnull如果是null就替换
select comm,ifnull(comm,0) from emp
#round四舍五入 & ceil向上取整 & floor向下取整
select comm,ROUND(comm),ceil(comm),floor(comm) from emp

#now & year年 & month月 & day日 & hour时 & minute分 & second秒
select now() #2021-06-29 11:18:21
select now(),year('1990-1-1') 
select now(),year(now()),month(now()),day(now())
select now(),hour(now()),minute(now()),second(now())

#转义字符，SQL中包含着一些特殊字符，需要转义\
select 'xi\'an'
select "xi'an"

二，条件查询

–1，概述

distinct   where   like   null    between...and   limit     order by 

–2，distinct where

 #条件查询 DISTINCT where like null 
#查询部门地址 
select loc from dept #包含重复结果
select DISTINCT loc from dept #去掉重复结果

#where用来过滤数据
#查询部门编号=1的记录
select * from dept  #全表查--低效
select * from dept where deptno=1  #只查1条--高效
#SQL的执行顺序 from  where  select 

#查询在二区的部门
select * from dept where loc="二区"

#查询在二区的research的部门
select * from dept where loc='二区' and dname='research'

#查询在二区的research的部门编号
select deptno from dept where loc='二区' and dname='research'

#查询在一区的编号是1的部门名称
select dname from dept where loc='一区' and deptno=1 #and高效

#查询在一区的部门或者编号是3的部门
select * from dept where loc='一区' or deptno=3 #or低效
select * from dept where dname = 'o'  #明确的条件,查询名称=o的部门

–3，like

#like模糊的条件 %占位符匹配0~n个字符
select * from dept where dname like '%o%' #模糊的条件,查询名称里包含o的部门 
select * from dept where dname like 'a%' #以a开头的部门--高效
select * from dept where dname like '%ch' #以ch结尾的部门
#查询在一区的部门或者名称包含ting的部门
select * from dept where loc='一区' or dname like '%ting%'

–4，null

#查询comm是空的 员工的信息
select * from emp where comm is null
#查询comm不为空的 员工的信息
select * from emp where comm is not null
#查询每个员工的月薪
select *,sal+comm from emp 
select *,sal+ifnull(comm,0) from emp  #null不参与运算，需要特殊处理
select *,sal+ifnull(comm,0),sal*12+ifnull(comm,0)*12 from emp 

–5，between and /limit/order by

#查询工资(5000,10000)的员工信息
select * from emp where sal>5000 and sal<10000
select * from emp where sal between 5000 and 10000 #都包含[5000,10000]

#分页 limit,限制数据的条数
select * from emp limit 2 #取前两条
select * from emp limit 0,2 #从0(第1条记录)开始,取两条
select * from emp limit 2,2 #从2(第3条记录)开始,取两条
select * from emp limit 1,3 #从1(第2条记录)开始,取三条
#查询工资>5000的前两条记录
select * from emp where sal>5000 limit 2

#order by排序 升序(默认的)、降序
select * from emp order by sal asc #升序
select * from emp order by sal desc #desc降序

#练习1：查询工资>5000的两个最高薪的员工信息
select * from emp where sal>5000 order by sal desc limit 2
#练习2：查询名字里包含o的老员工
select * from emp where ename like '%o%' order by hiredate limit 1
#练习3：查询名字里包含o的员工的入职年份
select *,year(hiredate),year(now())-year(hiredate) 
from emp where ename like '%o%' 

–6，综合练习

#练习4：2015年以前入职的老员工
select * from emp where year(hiredate)<2015
select * from emp where hiredate < '2015-1-1'
#练习5：查询每个员工入职了几年
select *,year(NOW())-year(hiredate) from emp 
#练习6：查询2015~2019年入职的员工
select * from emp where year(hiredate) >=2015 and year(hiredate) < 2019
select * from emp where year(hiredate)  between 2015 and 2019
#练习7：一年13薪，算年薪 
select *,sal*13+ifnull(comm,0)*13 from emp 
select *,sal*13+ifnull(comm,0)*13 as 年薪 from emp  #给列/字段设置别名
select *,sal*13+ifnull(comm,0)*13 年薪 from emp  #省略as

三，聚合函数

–1,测试

#查询最高薪的员工信息
select * from emp order by sal desc limit 1
#max最大值  min最小值  sum求和  avg平均数  
select max(sal),min(sal),sum(sal),avg(sal) from emp
#count统计个数/行数/总记录数
select count(*) from emp #低效
select count(1) from emp #高效
select count(empno) from emp 
select count(comm) from emp #低效,按照字段名统计个数,如果字段值是null将不做统计