0099 MySQL02

1.简单查询

查询一个字段

select 字段名 from 表名;

查询多个字段，使用“，”隔开

select 字段名,字段名 from 表名;

查询所有字段

1.把每个字段都写上

select 字段名,字段名,字段名.. from 表名;

2.使用*(效率低，可读性差)

select * from 表名;

2.给查询的列起别名

使用as关键字

select 字段名 as 别名 from 表名;

注：只是将查询结果列名显示为别名，原表列名不变

as可省略

select 字段名 别名 from 表名;

若别名里有空格，使用加上单引号或双引号

在所有数据库中，字符串统一使用单引号（双引号在Oracle数据库用不了）

3.列参与数学运算

如计算员工年薪

select ename,sal*12 from emp;

select ename,sal*12 as yearsal from emp;//起别名

select ename,sal*12 as 年薪 from emp;//错误

select ename,sal*12 as '年薪' from emp;//别名为中文，加上单引号

4.条件查询

格式：

        select 字段1，字段2...from 表名 where 条件;

条件：

 =等于

<>或!=不等于

<小于 <=小于等于

>大于 >=大于等于

between .. and ...在两个值之间，等同于 >= and <=

is null 为空(is not null不为空)

and 并且

or 或者

in 包含，相当于多个or（not in 不包含）

like 模糊查询，支持%或下划线匹配

%匹配任意多个字符

_下划线，一个下划线只匹配一个字符

---

1.查询史密斯的编号和薪资？

select empno,sal from emp where ename = 'smith';

2.查询薪资不等于3000的员工姓名和编号？

select empno,ename from emp where sal <> 3000;

select empno,ename from emp where sal != 3000;

3.查询薪资在3000-5000的员工姓名，编号和薪资？

select ename,empno,sal from emp where sal >= 3000 and <= 5000;//使用>=and<=

select ename,empno,sal from emp where sal between 3000 and 5000;//使用between and

//使用between and时，必须遵循左小右大

4.查询员工的津贴为null？

select empno,ename,sal,comm from emp where comm = null;

//错误，不能使用等号=，因为null不是值

select empno,ename,sal,comm from emp where comm is null;

5.查询工资大于3000，且部门编号为10或20的员工信息？

select * from emp where sal >3000 and deptno = 10 or deptno = 20;

//错误，and优先级比or高，含义表示为工资大于3000且部门编号为10的员工，或者部门编号为20

select * from emp where sal >3000 and (deptno = 10 or deptno = 20);//加上括号

6.查询工作岗位是manager或salesman的员工

select empno,ename,job from emp where job = 'manager' or job = 'salesman';

select empno,ename,job from emp where job in ('manager','salesman');

//in后面跟具体值，不是区间

7.找出名字含有o的？

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

找出名字以t结尾的？

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

找出第二个字母是a的？

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

找出名字有‘_’下划线的？

select ename from emp where ename like '%_%';//错误

select ename from emp where ename like '%\_%';//使用转义字符\

5.排序

1.查询所有员工的薪资并排序？

select ename,sal from emp order by sal;//默认是升序

select ename,sal from emp order by sal desc;//指定降序

select ename,sal from emp order by sal asc;//指定升序

2.多个字段排序

查询员工名字和薪资，要求按照薪资升序，如果薪资一样，按照名字升序

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

//sal在前为主导，只有sal相等时才会启用ename排序

3.根据字段位置排序

select ename,sal from emp order by 2;//2表示第2列，为sal（了解即可，不建议这样写）

4.找出工资在3000到5000之间的员工信息，并按照薪资降序排列

select ename,sal from emp where sal between 3000 and 5000 order by sal desc;

//执行顺序不能改变，from-where-select-order by

6.数据处理函数（单行处理函数）

特点：一个输入对应一个输出

对应于多行处理函数（多个输入对应一个输出）

常见的单行处理函数：

lower 转换小写

upper 转换大写

substr 取子串（格式：substr(被截取的字符串，起始下标，截取长度)）

length 取长度

trim 去空格

str_to_date 将字符串转成日期

date_format 格式化日期

format 设置千分位

round 四舍五入

rand() 生成随机数

ifnull 将null转成具体值

case..when..then..when..then..else..end

---

1.将名字转换为小写,大写

select lower(ename) from emp;

select upper(ename) from emp;

2.截取名字第一个长度

select substr(ename,1,1) from emp;

//注：起始下标从1开始，不是0

3.找出员工名字第一个字母是a的信息？

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

select ename from emp where substr(ename,1,1) = 'a';

4.让名字首字母大写(concat字符串拼接)

select upper(substr(name,1,1)) from ename;//首字母大写

select substr(name,2,length(name)-1) from ename;//截取除首字母之外的字符

select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) from ename;

5.round四舍五入

select round(1236.567,0) from emp;//1237

//select后可跟字段名，也可跟字面量（数据），0表示四舍五入保留0位小数

select round(1236.567,-1) from emp;//1240

//-1表示保留到十位

6.100以内的随机数

select round(rand()*100,0) from emp;

7.在所有数据库当中，只要有null参与的数学运算，最终结果都是null

计算员工年薪？

年薪=（月薪+月补助）*12

select ename,(sal + comm) * 12 from emp;//若补助为null，结果为null

select ename,(sal + ifnull(comm,0)) * 12 from emp;//使用ifnull,null当作0

8.当员工岗位为manager时，工资上调10%，为salesman时，上调50%

(不修改数据库，只是将查询结果显示为工资上调)

select ename,job,(case job when 'manager' then sal * 1.1 when 'salesman' then sal*1.5 else sal end) from emp;

7.分组函数（多行处理函数）

输入多行，最终输出一行

count 计数

sum 求和

avg 平均值

max 最大值

min 最小值

注：在使用时必须先进行分组，然后才能用，若没分组，整张表默认为一组

1.分组函数自动忽略null

2.分组函数中，count（*）和count（具体字段）的区别？

count（*）：统计表当中的总行数（只要有一行数据 count就加一）

count（具体字段）：统计该字段下所有不为null的总数

3.分组函数不能直接使用在where子句中

select ename,sal from emp where sal > min(sal);

//错误,因为where执行时还没分组，所以where后不能出现分组函数

//因为select在group by之后，所以可以使用

4.所有分组函数可以组合一起用

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

---

1.找出最高工资？

select max(sal) from emp;

2.计算工资和？

select sum(sal) from emp;

3.计算员工数量？

select count(ename) from emp;

8.分组查询

格式

select...from...group by...

将之前所学的全部组合在一起的执行顺序：

select...from...where...group by...order by...

执行顺序：from-->where-->group by--> select-->order by

---

1.找出每个岗位的工资和？

select job,sum(sal) from emp group by job;//按照岗位分组，然后对工资求和

select ename,job,sum(sal) from emp group by job;//加入ename，报错，无意义

即：select语句中，如果有group by语句，后面只跟参加分组的字段以及分组函数

2.找出每个部门的最高薪资？

select deptno,max(sal) from emp group by deptno;

3.找出每个部门，不同工作岗位的最高薪资？

select deptno,job,max(sal) from emp group by deptno,job;

4.找出每个部门最高薪资，且显示的最高薪资大于3000？

select deptno,max(sal) from emp group by deptno having max(sal) > 3000;

//使用having可以对分组后的数据进一步过滤，having不能单独使用，必须和group by联合使用

//以上执行效率低，可将大于3000的先找出，然后再分组

select deptno,max(sal) from emp where sal > 3000 group by deptno;

//where和having优先选择where

5.找出每个部门平均薪资，且显示平均薪资大于2500的

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;

//求平均，必须使用having

6.找出每个岗位的平均薪资，且显示平均薪资大于1500的，除manager岗位外，其他按照平均薪资降序排序

select job,avg(sal) from emp where job <> 'manager' group by job having avg(sal) > 1500 order by avg(sal) desc;