select *from emp;
SELECt *FROM dept;

 一、基础函数

 1、lower() 转化为全小写

select 'ABC' ,lower('ABC');

 2、upper() 转化为全大写

select 'def' ,lower('def');

 3、length() 获取长度 获取到的是字节数

select dname,length(dname) from dept; 

 4、substr() 截取字串 下标从1开始，截取时含头含尾。若超出范围则默认截取到最后

select dname,substr(dname,1,2) from dept;

 5、concat() 拼接字符串

select concat(dname,'(',loc,')') from dept;

 6、replace() 替换字符串

select dname,replace(dname,'人','人类') from dept;
select replace(replace(replace(replace(deptno,1,'一'),2,'二'),3,'三'),4,'四') from dept;

 7、round() 四舍五入 | ceil()向上取整 | floor()向下取整

select round(3.54),ceil(3.54),floor(3.54);  -- 4 4 3

 8、uuid() 通用唯一标识码

select uuid();  2ae7560e-30c3-11ec-9cc5-68f72814128b

 9、 转义字符

select 'ab'cde';   -- ab'cde

 练习：将'abc'的首字母大写

select concat(upper(substr('abc',1,1)),substr('abc',2,3));

 二、日期函数

 1、now() 获取当前系统时间

 2、curdate() 获取系统date，即年月日

 3、curtime() 获取系统time，即时分秒

 4、sysdate() 获取系统date，即年月日

select now(),curdate(),curtime(),sysdate();

 orcale中获取系统当前时间，dual为内置伪表
 dual是虚拟的，不存在的，是为了满足orcale中使用select必须加from的语法要求而存在的

select sysdate from dual;

 5、year() | month() | day() | hour() | minute() | second() 年月日时分秒

select hiredate,year(hiredate),month(hiredate),day(hiredate) from emp;
select hour(now()),minute(now()),second(now());

 6、date_format() 日期转字符串

select date_format(now(),'%Y年%m月%d日');
select date_format(now(),'%Y-%m-%d');

 7、last_day() 获取每个月的最大日期，即最后一天

select last_day(now());
select ename,hiredate,last_day(hiredate) from emp;

 *、获取每个月的第一天和最后一天

select date_format(now(),'%Y-%m-01'),last_day(now());

 8、str_to_date()字符串转日期

select str_to_date('2020-11-18','%Y-%m-%d');

 三、自定义函数

 1、无函数体：

function 方法名(参数名 参数类型) returns 返回值类型 return 返回值;

create function f1() returns int return 100;
create function f2(id int) returns int return id;
select f1(),f2(1001);

 2、有函数体：

delimiter $$   delimiter定义结束符
create function f3(name varchar(20)) returns varchar(20) 
begin  函数体开始
  declare str varchar(20);  declare 变量声明
  select job from emp where ename=name into str;
  return str;
end $$  函数体结束
delimiter ;

select f3('孙悟空');

3、删除函数

直接删：drop function f1;
有则删：drop function if exists f2;

四、条件查询

1、is null和is not null

查询数据时，如果查询的值为null，需要使用is null，而不是=null；不为null同理。

select * from emp where mgr is null;

select ename,mgr from emp where mgr is not null;

2、distinct去重

select distinct job from emp;

select distinct deptno from emp;

3、比较运算符> < = >= <= !=即<>

select ename,sal from emp where sal<=3000;

select ename,job from emp where deptno=1;

select ename,sal,job from emp where job!='程序员';

select ename,sal,job from emp where job<>'程序员';

select ename,comm from emp where comm>0 ;  --  或者>0

4、and | or 查询数据时多个条件同时满足使用and，只需要满足一个用or

select * from emp where sal<3000 and deptno=1;

select * from emp where sal=800 or sal=3000;

5、in关键字

当查询某个字段的值为多个值时使用

select * from emp where sal in (800,1500,3000);

select * from emp where job in ('销售','人事','程序员');

6、between x and y 两者之间，包括首尾

select * from emp where sal>=1000 and sal<=2000;

select * from emp where sal between 1000 and 1500;

7、ifnull(列名，值)

用于mysql中判断指定的列是否包含null值，如果有null值，用第二个值替换null值。

在orcale中：

nvl(列名,值)：如果指定的列值为null，使用第二个值替换null

nvl2(列名,值1,值2)：如果指定的列值为null返回值1，不为null返回值2

select ename,ifnull(sal,0)+ifnull(comm,0) from emp;

select nvl(mgr,'没有领导'),nvl2(comm,0,comm) from emp;

8、like 模糊查询

_即下划线代表一个未知字符，%代表0或多个未知字符

例：①以x开头：x% ②以x结尾：%x ③包含x：%x%  ④x开头y结尾：x%y

⑤第二个字符是x：_x%  ⑥第二个是x，倒数第三个是y：_x%y__

select ename from emp where ename like '孙%';

select * from emp where job like '_售%';

select ename from emp where ename like '%精';

select * from emp where ename like '%僧%' and sal>2000;

9、order by 排序

格式：order by 字段名/数字 asc生序(默认)/desc降序

后接字段：order by表示按照指定字段进行排序；

后接数字：order by n表示按select后的第n个字段进行排序

select ename,sal from emp order by sal asc;

select ename,sal from emp order by 2 asc;

select * from emp where deptno=3 order by sal desc;

select ename,sal,deptno from emp order by deptno asc, sal desc;

10、limit 分页查询

格式：limit 跳过的条数，显示的条数

计算公式：跳过的条数=(请求页数-1)*每页条数；当跳过条数为0时，可以省略不写

查询第1页的10条数据，即limit 0,10(limit 10)

查询第3页的8条数据，即limit 16,8

查询第2页的10条数据，即limit 10,10

查询第8页的5条数据，即limit 35,5

select * from emp order by sal limit 0,5;

select * from emp order by sal desc limit 3,3;

select * from emp where sal>1000 order by sal limit 0,3;

 11、as关键字：别名 用于展示表头

 注意：where关键字不能起别名；不改变原内容

select ename from emp;

select ename as '姓名' from emp;

select ename '姓名' from emp;

select ename 姓名 from emp;

 12、数值计算 + - * / %

 对查询的某个字段的数据可以直接进行计算。

select ename,sal,sal*5 '年终奖' from emp;

update emp set sal=sal+50 where deptno=1;

select ename,sal from emp where deptno=1;

 13、聚合函数

 SQL基本函数，可以对一组值执行计算，并返回单个值。

 使用聚合函数可以对查询的多条数据进行统计查寻

 统计方式：1.平均值 2.最大值 3.最小值 4.求和 5.计数

 (1)avg(字段名) 平均值

select avg(sal) from emp;

select avg(sal) from emp where deptno=2;

 (2)max(字段名) 最大值

select max(sal) from emp where deptno=1;

select max(comm) from emp;

 (3)min(字段名) 最小值

select min(sal) from emp where deptno=1;

select min(hiredate) from emp;

 (4)sum(字段名) 求和

select sum(sal) from emp where deptno=2;

 (5)count(*) 计数

select count(*) from emp where deptno=3;

select count(*) from emp where job='程序员';

select avg(sal)平均工资,max(sal)最高工资,min(sal)最低工资,

sum(sal)工资和,count(*)人数 from emp where deptno=1;

count(*) 、count(数字)、 count(字段名)异同：

 相同点：都是用来统计数据条数的

 不同点：count(*)和count(数字)统计出来的结果是一样的，都不会忽略null值，

         但是count(*)的效率没有count(数字)高。

         count(字段)在统计时，如果字段值为null，不会计入总数。

 练习：

1. 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第2页的两条数据

2. 查询和销售相关工作的工资总和

3. 查询1号部门工资高于2000的员工人数

4. 查询3号部门的人数和工资总和并起别名

5. 查询1号部门中名字包含僧的员工的人数和平均工资 起别名

select ename,sal from emp where sal>2000 order by sal limit 2,2;

select sum(sal) from emp where job like '%销售%';

select count(*) from emp where deptno=1 and sal>2000;

select count(*)人数,sum(sal)工资总和 from emp where deptno=3;

select count(*)人数,avg(sal)平均工资 from emp where deptno=1 and ename like '%僧%';