-- is null 和 is not null
#1. 查询没有上级领导的员工编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
#2. 查询emp表中没有奖金（comm）的员工姓名，工资，奖金
select ename,sal,comm from emp where comm is null;
#3. 查询emp表中有奖金的员工信息
select * from emp where comm is not null;
-- 别名
# 把查询到的员工姓名ename 改成 名字
select ename as '名字' from emp;
select ename '名字' from emp;
select ename 名字 from emp;
-- 去重 distinct
# 1. 查询emp表中出现的所有的职位job
select distinct job from emp;
-- 比较运算符： > < >= <= = !=和<>
#1. 查询工资小于1600的员工的姓名和工资
select ename,sal from emp where sal<1600;
#2. 查询部门编号是20的所有员工的姓名，职位(job)和部门编号deptno
select ename,job,deptno from emp where deptno = 20;
#3. 查询职位是manager的所有员工姓名和职位
select ename,job from emp where job= 'manager';
#4. 查询部门不是10号部门的所有员工姓名和部门编号（两种写法）
select ename,deptno from emp where deptno !=10;
select ename,deptno from emp where deptno <> 10;
#5 查询t_item表中单价price 等于23的商品信息
select * from t_item where price = 23;
#6. 查询t_item表中单价price不等于8443的商品标题title和单价
select title,price from t_item where price!=8443;
-- 逻辑运算符
-- 逻辑与： and 和java中的&&效果一样
-- 逻辑或： or 和Java中的||效果一样
-- 逻辑非 not 和java中的！效果一样
-- 逻辑异或: xor 和Java中^的效果一样
#1. 查询不是10号部门并且工资小于3000的员工信息
select * from emp where deptno!=10 and sal<3000;
select * from emp where deptno!=10 && sal<3000;
#2. 查询部门编号为20或者上级领导为7698的员工姓名，职位，上级领导（mgr）和部门编号
select ename,job,mgr,deptno from emp where deptno=20 or mgr=7698;
#3. 查询工资高于1500，或者empno为7639的员工，两者只能满足其一
select * from emp where sal>1500 or empno=7369;
-- in
-- 当查询某个字段的值为多个的时候使用in 关键字
# 1. 查询emp表中工资为5000,1500,3000的员工信息
select * from emp where sal=5000 or sal=1500 or sal=3000;
#使用in
select * from emp where sal in(5000,1500,3000);
#2. 查询emp表中工资不等于5000,1500,3000的员工信息
select * from emp where sal not in(5000,1500,3000);
-- between x and y 包括x和y
#1. 查询emp表中工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;
#2. 查询emp表中工资在1000到3000之外的员工信息
select * from emp where sal not between 1000 and 3000;
-- 模糊查询 like
/*
_:代表单个未知字符
%: 代表0个或者多个未知字符
案例：
1. 查询以a开头 like 'a%'
2. 以m结尾的 like '%m'
3. 第二个字符是a like '_a%'
4. 第三个字符是x, 倒数第二个字符是y like '_ _ x%y_'
5. 倒数第三个字符是 x like '%x _ _'
6. 查询包含a like '%a%'
*/
#1. 查询t_item中 标题中包含记事本的商品信息
select * from t_item where title like '%记事本%';
#2. 查询单价低于30的记事本（title中包含记事本）
select * from t_item where price<30 and title like '%记事本%';
#3.查询单价在50-200之间的得力商品
select * from t_item where price between 50 and 200 and title like '%得力%';
#4. 查询有图片的得力商品（图片字段不能为null）
select * from t_item where image is not null and title like '%得力%';
#5. 查询分类（category_id）为238 917的商品信息
select * from t_item where category_id in(238,917);
#6. 查询有赠品的商品信息（sell_point 卖点字段中包含赠字包含送字的）
select * from t_item where sell_point like '%赠%' or '%送%';
#7.查询标题中不包含得力的商品标题
select title from t_item where title not like '%得力%';
#8 查询价格在50到200以外的商品信息
select * from t_item where price not between 50 and 200;
-- 排序 order by
/*
格式： order by 字段名 desc(降序) / asc（升序 默认就是升序）
*/
# 1. 查询所有员工的姓名和工资，按照工资升序排序
select ename, sal from emp order by sal asc;
#2. 查询所有员工的姓名，工资和部门编号，按照部门编号降序排序
select ename ,sal, deptno from emp order by deptno desc;
-- 多字段排序： order by 字段名1 asc/desc, 字段名2 asc/desc
#1. 查询所有的员工姓名，工资和部门编号，按照部门编号降序排序
# 如果部门编号一致则按照工资升序排序
select ename,sal,deptno from emp order by deptno desc, sal asc;
-- 分页查询 limit
/*
格式： limit 跳过的条数，请求的条数
limit （页数-1）*条数，条数
举例：
1. 请求第一页20条数据 limit 0,20
2. 请求第三页10条数据 limit 20,10
3. 请求第五页8条数据 limit 32, 8
4. 请求第四页7条数据 limit 21,7
*/
#1. 查询所有商品按照单价升序排序，显示第二页每页7条数据
select id, title,price from t_item order by price asc limit 7,7;
#2.查询员工表中所有员工的姓名和工资，按照工资降序排序，每页显示三条数据，显示第三页
select ename,sal from emp order by sal desc limit 6,3;
#3.查询所有员工中工资前三名的姓名和工资
select ename,sal from emp order by sal desc limit 0,3;
#4. 查询工资最高的员工姓名和工资
select ename,sal from emp order by sal desc limit 0,1;
-- 算术运算符 + - * / % 7%2 等效于 mod(7,2)
#1. 查询员工姓名，工资，及年终奖（年终奖 = 工资*5）
select ename,sal,sal*5 as 年终奖 from emp;
#2. 查询t_item表中商品的单价，库存以及总金额（单价*库存）
select price,num, price*num 总金额 from t_item;
#3.修改每个员工的工资，每个人涨薪10元
update emp set sal=sal+10;
-- 改回去
update emp set sal=sal-10;
-- 日期相关的函数
select 'helloworld'
#1.获取系统当前时间 日期+时间 now();
select now();
#2. 获取当前日期 curdate()
select curdate();
#3.获取当前时间：curtime()
select curtime();
#4. 从当前系统时间中获取年月日 和时分秒
select date(now());
select time(now());
#查询商品上传日期
select date(created_time) from t_item;
#5. 从年月日时分秒中提取时间分量 extract(year from now())
#当前年份
select extract(year from now());
#当前月份（month）
select extract(month from now());
#当前日（day）
select extract(day from now());
#当前时(hour)
select extract(hour from now());
#当前分(minute)
select extract(minute from now());
#当前秒(second)
select extract(second from now());
#查询每个员工入职的年份
select ename,extract(year from hiredate) 入职年份 from emp;
#查询入职年限满43年的员工姓名
select ename from emp where (extract(year from now()) - extract(year from hiredate)) >=43;
#6.日期格式化
/*
格式：
%Y 四位的年 2023
%y 两位的年 23
%m 两位的月 12 02 03
%d 日
%c 一位的月 12 2 3
%H 24小时制中的时间 15:08
%h 12小时制中的时间 3:08
%i 分
%s 秒
date_format(时间,格式)
*/
#例如：把时间默认格式转换成年月日时分秒
select now(); #当前系统时间，显示格式2023-09-20 15:11:23
#格式：2023年09月20日 15时11分23秒
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');
#1. 把t_item表中的商品创建日期改成 年月日 ：：：
select title, date_format(created_time,'%Y年%m月%d日 %H:%i:%s') 生产日期 from t_item;
#把非标准格式的时间转换成默认的时间格式：str_to_date('非标准格式的时间','格式')
#例如：把20.09.2023 16:00:00
select str_to_date('20.09.2023 16:00:00','%d.%m.%Y %H:%i:%s');
-- ifnull(x,y)函数
# age = ifnull(x,y) 如果x的值为null,则age=y,如果x值不为null,则age=x
#1. 把员工表中奖金为null的全部改成0，其他的不便
update emp set comm=ifnull(comm,0);
-- 聚合函数:聚合函数用于对多行数据进行统计，也叫多行函数，如统计平均值，最大值，最小值，求和，统计数量等
#平均值:avg(字段)
#1. 查询所有员工的平均工资
select avg(sal) from emp;
#2. 查询10号部门的平均工资
select avg(sal) from emp where deptno=10;
#3. 查询戴尔商品的平均单价
select avg(price) from t_item where title like '%戴尔%';
#最大值：max(字段)
#1. 查询所有员工中的最高工资
select max(sal) from emp;
select sal from emp order by sal desc limit 0,1;
#2. 查询30号部门中的最高奖金
select max(comm) from emp where deptno=30;
select comm from emp where deptno=30 order by comm desc limit 0,1 ;
#求和 sum(字段名)
#1. 查询30号部门每个月需要发多少钱工资
select sum(sal) from emp where deptno=30;
#2. 查询戴尔商品的库存总量
select sum(num) from t_item where title like '%戴尔%';
#统计数量：count(字段名)、
#1. 统计员工表中员工的数量
select count(*) from emp;
#2. 统计员工表中30号部门工资大于2000块钱的人数
select count(*) from emp where deptno=30 and sal>2000;
/*
回顾:聚合函数
平均值： avg()
最大值： max()
最小值： min()
求和：sum()
统计数量：count()
*/
-- 字符串相关的函数
#字符串拼接 concat('aaa','bbb') -> aaabbb
select concat('aaa','bbb');
#1. 查询emp表中员工姓名和工资，工资后面要显示单位元
select ename,concat(sal,'元') from emp;
#2. 查询emp表中员工姓名和工资，工资格式 ￥56元
select ename, concat('￥',sal,'元') from emp;
#获取字符串长度 char_length('abc') 3
select char_length('abcdef');
#1. 查询员工姓名和姓名的长度
select ename,char_length(ename) from emp;
# 获取字符串在另一个字符串中出现的位置
#格式一： instr(str, substr)
select instr('abcdefg','cd');
#格式二：locate(substr,str)
select locate('cd','abcdeft');
#插入字符串 insert(str,start,length,newStr)
select insert('abcdefg',3,2,'爱你');
# 大小写转换 upper("str") lower(str)
select upper('abc');
select lower('ABC');
#两端去除空格 trim()
select trim(' a n ');
#截取字符串
-- left(srt,num)
select left('abcdefg',2);
-- right(str,num)
select right('abcdeft',2);
-- substring(str,start,length)
select substring('abcdefg',2,2);
#重复 repeat
select repeat('ab',2);
#替换 replace
select replace('abcdefg','c','123');
#反转 reverse()
select reverse('abc');