day44数据库查询命令

-- 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');