MySQL数据库（三）

矫情吗；* 2022-02-17 12:36 305阅读 0赞

### 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 后面写多个字段

1.  查询每个员工的姓名、工资、部门编号，按照部门编号降序排序如果部门编号一致则按照工资升序排序  
    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,sal*5 年终奖 from emp;
2.  查询商品表中每个商品的单价，库存和总金额(单价*库存)  
    select price,num,price*num 总金额 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;

1.  提取时间分量 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;
2.  日期格式化

*  格式： 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秒’);

1.  把非标准时间格式转成标准格式 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;

1.  最大值 max(字段名)

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

1.  最小值 min(字段名)

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

1.  求和 sum(字段名)

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

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

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

### 字符串相关函数 ###

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

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

1.  获取字符串的字符长度 char\_length(str)

*  查询员工姓名和对应的字符长度  
    select ename,char\_length(ename) from emp;

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

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

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

*  左边截取： 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);

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