MySQL数据库（三）

is null 和 is not null

1. 查询没有奖金comm的员工信息
   select * from emp where comm is null;
2. 查询没有领导的员工姓名ename
   select ename from emp where mgr is null;
3. 查询有领导的员工姓名ename和领导编号mgr
   select ename,mgr from emp where mgr is not null;

别名

select ename as ‘姓名’ from emp;
select ename ‘姓名’ from emp;
select ename 姓名 from emp;

1. 查询员工的姓名和工资要求使用别名
   select ename 姓名,sal 工资 from emp;

去重 distinct

• 去掉重复的数据

1. 查询所有员工从事的工作job有哪些
   select distinct job from emp;
2. 查询所有员工所属部门编号deptno有哪些
   select distinct deptno from emp;

and和or

• and 相当于java中的&&
• or 相当于java中的||

1. 查询20号部门工资大于2000的员工姓名和工资
   select ename,sal from emp where deptno=20 and sal>2000;
2. 查询10号部门有上级领导的员工姓名、工资和领导编号
   select ename,sal,mgr from emp where deptno=10 and mgr is not null;
3. 查询20号部门或者工资高于2000的员工姓名、工资和部门编号
   select ename,sal,deptno from emp where deptno=20 or sal>2000;

比较运算符 > < >= <= = != 和 <>

1. 查询职位是manager的所有员工姓名和职位
   select ename,job from emp where job=‘manager’;
2. 查询员工工资小于等于1600的所有员工编号、姓名和工资
   select empno,ename,sal from emp where sal<=1600;
3. 查询不是10号部门的所有员工编号、姓名以及部门的编号（两种写法）
   select empno,ename,deptno from emp where deptno!=10;
   select empno,ename,deptno from emp where deptno<>10;
4. 查询商品表t_item单价price等于23的商品信息
   select * from t_item where price=23 \G;
5. 查询单价不等于8443的商品标题title和单价
   select title,price from t_item where price!=8443;
6. 查询不是10号部门并且工资小于3000的员工编号、姓名、工资和所属部门编号
   select empno,ename,sal,deptno from emp where deptno!=10 and sal<3000;
7. 查询部门编号是30或者上级领导为7698的员工姓名、职位、上级领导编号以及所属部门编号
   select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;

模糊查询 like

• % ：代表0或多个未知字符
• _ ：代表1个未知字符
  举例：
  以a开头： a%
  以b结尾： %b
  第三个字符是m: _m%
  包含x： %x%
  第二个字符是x倒数第三个字符是y: x%y

1. 查询名字中包含a的员工姓名
   select ename from emp where ename like ‘%a%’;

2. 查询第二个字符是l的员工姓名和工资
   select ename,sal from emp where ename like ‘_l%’;

3. 查询t_emp表中，标题title中包含记事本的商品标题
   select title from t_item where title like ‘%记事本%’;

4. 查询职位job中包含an并且工资高于1500的员工姓名、工资、职位
   select ename,sal,job from emp where job like ‘%an%’ and sal>1500;

5. 查询有赠品的dell商品详情(卖点sell_point中包含“赠”字,标题中包含dell)
   select * from t_item where sell_point like ‘%赠%’ and title like ‘%dell%’;

6. 查询单价低于100元的笔记本(标题中包含笔记本)
   select * from t_item where price<100 and title like ‘%笔记本%’;

7. 查询有图片(image字段不为null)的得力商品
   select * from t_item where image is not null and title like ‘%得力%’;

8. 查询员工姓名中不包含a的员工编号、姓名、工资
   select empno,ename,sal from emp where ename not like ‘%a%’;

查询在两者之间 between x and y 包含xy

1. 查询工资在2000到3000之间的员工姓名和工资
   select ename,sal from emp where sal>=2000 and sal=<3000;

   select ename,sal from emp where sal between 2000 and 3000;

2. 查询单价在50-100之间的商品标题和商品单价
   select title,price from t_item where price between 50 and 100;

3. 查询工资在1000到2000之外的员工姓名和员工工资
   select ename,sal from emp where sal not between 1000 and 2000;

in

1. 查询工资为800，1300，1500，5000的员工信息
   select * from emp where sal=800 or sal=1300 or sal=1500 or sal=5000;
   select * from emp where sal in(800,1300,1500,5000);
2. 查询商品价格为56，58，89的商品标题和单价
   select title,price from t_item where price in(56,58,89);
3. 查询工资不为3000、5000、1500的员工姓名和工资
   select ename,sal from emp where sal not in(3000,5000,1500);

排序

• 格式：order by 字段名 asc/desc;

1. 查询员工姓名和工资，按照工资升序排序
   select ename,sal from emp order by sal;
2. 查询有奖金的员工姓名和奖金通过奖金降序排序
   select ename,comm from emp where comm>0 order by comm desc;
3. 查询单价低于100的商品标题和单价，按照单价升序排序
   select title,price from t_item where price<100 order by price;

• 多字段排序 在order by 后面写多个字段

4. 查询每个员工的姓名、工资、部门编号，按照部门编号降序排序如果部门编号一致则按照工资升序排序
   select ename,sal,deptno from emp order by deptno desc,sal;

分页查询

• 格式： limit 跳过的条数,请求的条数 limit (页数-1)*条数,条数
• 请求第三页的10条数据 limit 20,10
• 请求第5页的8条数据 limit (5-1)*8,8

1. 员工表工资降序排序， 请求前5条数据
   select * from emp order by sal desc limit 0,5;
2. 查询所有商品按照单价升序排序 显示第二页，每页7条数据
   select * from t_item order by price limit 7,7;
3. 查询员工表中奖金最高的员工信息
   select * from emp order by comm desc limit 0,1;

数值计算 + - * / % 7%2 和 mod(7,2)

1. 查询员工姓名，工资和年终奖（年终奖=工资5）
   select ename,sal,sal5 年终奖 from emp;
2. 查询商品表中每个商品的单价，库存和总金额(单价库存)
   select price,num,pricenum 总金额 from t_item;
3. 查询每个员工涨薪5块钱之后的工资
   select sal,sal+5 涨薪后 from emp;

日期相关函数

• SQL的helloworld
  select “helloworld”;

1. 获取当前的系统时间（年月日时分秒）
   select now();
2. 获取当前的年月日 current当前
   select curdate();
3. 获取当前的时分秒
   select curtime();
4. 从年月日时分秒中提取年月日 和 提取时分秒
   select date(now());
   select time(now());

• 举例：select time(birthday) from user;
  查询商品表中每个商品上传年月日
  select date(created_time) from t_item;
  查询商品表中每个商品上传时分秒
  select time(created_time) from t_item;

5. 提取时间分量 extract
   select extract(year from now());
   select extract(month from now());
   select extract(day from now());
   select extract(hour from now());
   select extract(minute from now());
   select extract(second from now());
   • 查询每个员工入职的年份
     select extract(year from hiredate) from emp;
6. 日期格式化

• 格式： date_format(时间,格式)
  %Y 四位年 2019
  %y 2位年 19
  %m 两位月 03
  %c 1位月 3
  %d 日
  %H 24小时
  %h 12小时
  %i 分钟
  %s 秒
  • 把now()转换成转成 2019年03月21号 15点34分00秒
    select date_format(now(),’%Y年%m月%d号 %H点%i分%s秒’);

7. 把非标准时间格式转成标准格式 str_to_date(非标准字符串时间,格式)
   • 把 21.03.2019 15:41:30 转成标准时间格式
     select str_to_date(“21.03.2019 15:41:30”,"%d.%m.%Y %H:%i:%s");

ifnull()函数

• age = ifnull(x,y) 如果x值为null则age=y 不为null age=x
  1. 把员工表奖金为null的修改为0
     update emp set comm=ifnull(comm,0);

聚合函数

• 对多条数据进行统计查询 平均值、最大值、最小值、求和、统计数量

1. 平均值 avg(字段名)

• 查询10号部门的平均工资
  select avg(sal) from emp where deptno=10;
• 查询部门表的平均奖金
  select avg(comm) from emp;

2. 最大值 max(字段名)

• 查询20号部门的最高工资
  select max(sal) from emp where deptno=20;
• 查询最贵商品的价格
  select max(price) from t_item;

3. 最小值 min(字段名)

• 查询最低的工资是多少
  select min(sal) from emp;

4. 求和 sum(字段名)

• 查询10号部门的工资总和
  select sum(sal) from emp where deptno=10;
• 查询商品表里100块钱以内的一样买一个总共多少钱
  select sum(price) from t_item where price<100;

5. 统计数量 count(字段名)

• 查询20号部门的人数
  select count(*) from emp where deptno=20;

字符串相关函数

1. 字符串拼接 concat(s1,s2)

• 查询emp表中员工姓名和工资 工资后面有单位：元
  select ename,concat(sal,‘元’) from emp;

2. 获取字符串的字符长度 char_length(str)

• 查询员工姓名和对应的字符长度
  select ename,char_length(ename) from emp;

3. 获取字符串在另外一个字符串中出现的位置

• 格式 instr(str,substr)
  select instr(‘abcdefg’,‘d’);
• 格式 locate(substr,str)
  select locate(‘d’,‘abcdefg’);

4. 转大写 转小写
   select upper(‘nba’),lower(‘AbC’);
5. 截取字符串

• 左边截取： left(str,count);
  select left(“abcdefg”,2);
• 右边截取： right(str,count);
  select right(“abcdefg”,2);
• 自由截取： substring(str,start,length?);
  select substring(“abcdefg”,2);
  select substring(“abcdefg”,2,3);

6. 去空白： trim(str)
   select trim(" a b ");
7. 重复 repeat(str,count)
   select repeat(‘ab’,2);
8. 替换 replace(str,old,new)
   select replace(‘abc abc’,‘c’,‘m’);
9. 反转 reverse(str)
   select reverse(“abc”);