查询语句
简单的查询语句（DQL）
语法格式：select 字段名1,字段名2,字段名3,… from 表名;
mysql> select ENAME from emp; +--------+ | ENAME | +--------+ | SMITH | | ALLEN | | WARD | | JONES | | MARTIN | | BLAKE | | CLARK | | SCOTT | | KING | | TURNER | | ADAMS | | JAMES | | FORD | | MILLER | +--------+ -
sql语句以;结尾
-
sql语句不区分大小写
mysql> select ename,job from emp; +--------+-----------+ | ename | job | +--------+-----------+ | SMITH | CLERK | | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | SCOTT | ANALYST | | KING | PRESIDENT | | TURNER | SALESMAN | | ADAMS | CLERK | | JAMES | CLERK | | FORD | ANALYST | | MILLER | CLERK | +--------+-----------+ -
查询员工的年薪（字段可以参与数学运算）
mysql> select ename,sal * 12 from emp; +--------+----------+ | ename | sal * 12 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+给查询结果的列重命名
mysql> select ename,sal * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 |别名中有中文
mysql> select ename,sal * 12 as 年薪 from emp; +--------+----------+ | ename | 年薪 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+as关键字可以省略
mysql> select ename,sal *12 '年薪' from emp; +--------+----------+ | ename | 年薪 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ -
查询所有字段
select * from emp;//实际开发中不建议使用，效率较低
条件查询
语法格式 select字段,字段… from 表名 where 条件;
执行顺序：先from 然后where 最后select
-
查询工资等于5000的员工姓名
mysql> select ename from emp where sal = 5000; +-------+ | ename | +-------+ | KING | +-------+ -
查询SMITH的工资
mysql> select sal from emp where ename = smith; ERROR 1054 (42S22): Unknown column 'smith' in 'where clause' mysql> select sal from emp where ename = SMITH; ERROR 1054 (42S22): Unknown column 'SMITH' in 'where clause' mysql> select sal from emp where ename = 'SMITH'; +--------+ | sal | +--------+ | 800.00 | +--------+ -
找出工资高于3000的员工
mysql> select ename, sal from emp where sal > 3000; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | +-------+---------+ -
找出工资小于等于3000的员工
mysql> select ename, sal from emp where sal <= 3000; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ -
找出工资不等于3000的员工
mysql> select ename,sal from emp where sal <> 3000; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | MILLER | 1300.00 | -
找出工资位于1000到3000之间的员工
mysql> select ename, sal from emp where sal>= 1000 and sal <= 3000; +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+//闭区间 mysql> select ename, sal from emp where sal between 1000 and 3000; +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ -
找出免费精选名字大全位首字母在A和C中的员工
//左闭右开 mysql> select ename from emp where ename between 'A' and 'C'; +-------+ | ename | +-------+ | ALLEN | | BLAKE | | ADAMS | +-------+ -
找出津贴为NULL的员工
mysql> select ename, sal ,comm from emp where comm is null; +--------+---------+------+ | ename | sal | comm | +--------+---------+------+ | SMITH | 800.00 | NULL | | JONES | 2975.00 | NULL | | BLAKE | 2850.00 | NULL | | CLARK | 2450.00 | NULL | | SCOTT | 3000.00 | NULL | | KING | 5000.00 | NULL | | ADAMS | 1100.00 | NULL | | JAMES | 950.00 | NULL | | FORD | 3000.00 | NULL | | MILLER | 1300.00 | NULL | +--------+---------+------+ -
找出津贴不为NULL的员工
mysql> select ename, sal ,comm from emp where comm is not null; +--------+---------+---------+ | ename | sal | comm | +--------+---------+---------+ | ALLEN | 1600.00 | 300.00 | | WARD | 1250.00 | 500.00 | | MARTIN | 1250.00 | 1400.00 | | TURNER | 1500.00 | 0.00 | +--------+---------+---------+ -
找出没有津贴的员工（为NULL或者为0）
mysql> select ename, sal, comm from emp where comm is null or comm = 0; +--------+---------+------+ | ename | sal | comm | +--------+---------+------+ | SMITH | 800.00 | NULL | | JONES | 2975.00 | NULL | | BLAKE | 2850.00 | NULL | | CLARK | 2450.00 | NULL | | SCOTT | 3000.00 | NULL | | KING | 5000.00 | NULL | | TURNER | 1500.00 | 0.00 | | ADAMS | 1100.00 | NULL | | JAMES | 950.00 | NULL | | FORD | 3000.00 | NULL | | MILLER | 1300.00 | NULL | +--------+---------+------+ -
找出工资高于1000并且部门编号为20或者30的员工
//错误的 存在优先级 mysql> select ename, sal, deptno from emp where sal >= 1000 and deptno = 20 or deptno = 30; +--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | JAMES | 950.00 | 30 | | FORD | 3000.00 | 20 | +--------+---------+--------+mysql> select ename, sal, deptno from emp where sal >= 1000 and (deptno = 20 or deptno = 30); +--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | +--------+---------+--------+ -
找出工作岗位是MANAGER和SALESMAN的员工
mysql> select ename, job from emp where job = 'MANAGER' or job = 'SALESMAN'; +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+mysql> select ename, job from emp where job in ('MANAGER', 'SALESMAN'); +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+
模糊查询 like
-
找出免费精选名字大全中含有o的员工
//%表示任意个字符 mysql> select ename from emp where ename like '%o%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+ -
找出免费精选名字大全中第二个是’A’的员工
//_表示一个字符 mysql> select ename from emp where ename like '_A%'; +--------+ | ename | +--------+ | WARD | | MARTIN | | JAMES | +--------+ -
找出免费精选名字大全中第三个是’A’的员工
mysql> select ename from emp where ename like '__A%'; +-------+ | ename | +-------+ | BLAKE | | CLARK | | ADAMS | +-------+ -
找出免费精选名字大全中最后一个是’T’的员工
mysql> select ename from emp where ename like '%T'; +-------+ | ename | +-------+ | SCOTT | +-------+
排序（升序、降序）
-
按照工资排序（默认是升序） 找出员工名和薪资
mysql> select ename, sal from emp order by sal; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ -
asc表示升序
mysql> select ename, sal from emp order by sal asc; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ -
desc表示降序
mysql> select ename, sal from emp order by sal desc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+ -
按工资降序排序 工资相同按免费精选名字大全升序排序
//要靠前的字段越能起到主导作用 只有前面的字段无法完成排序时，即相同 才会启用后面的字段 mysql> select ename, sal from emp order by sal desc, ename asc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+ -
//按照第二列排序 mysql> select ename, sal from emp order by 2; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ -
找出工作岗位是’SALESMAN’的员工，并且按照工资的降序排序
mysql> select ename, job, sal from emp where job = 'SALESMAN' order by sal desc; +--------+----------+---------+ | ename | job | sal | +--------+----------+---------+ | ALLEN | SALESMAN | 1600.00 | | TURNER | SALESMAN | 1500.00 | | WARD | SALESMAN | 1250.00 | | MARTIN | SALESMAN | 1250.00 | +--------+----------+---------+ -
执行顺序
select xxx 3 from tableName 1 where 条件 2 order by ... 4
分组函数
多行处理函数 输入多行 输出一行
所有的分组函数都是对某一组数据进行操作的
count 计数
-
找出总人数
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+
sum 求和
-
找出员工的工资总和
mysql> select sum(sal) from emp; +----------+ | sum(sal) | +----------+ | 29025.00 | +----------+
avg 平均值
-
找出平均工资
mysql> select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+
max 最大值
-
找出最高工资
mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+
min 最小值
-
找出最低工资
mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ -
所有分组函数会自动忽略NULL
mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ -
计算每个员工的年薪
//有NULL参与运算 结果一定是NULL mysql> select ename, (sal + comm) * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | NULL | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | NULL | | MARTIN | 31800.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 18000.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+----------+ -
ifnull(可能为null的数据， 处理为什么值)
//ifnull()属于单行处理函数 输入几行 输出几行 mysql> select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | 35700.00 | | MARTIN | 31800.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ -
查找高于平均薪资的员工
//当一条sql语句没有group by时 整张表自成一张表 如果一条sql语句没有group by，相当于是缺省的 //先执行group by才能执行分组函数 group by语句是在where语句之后执行 分组函数又是在where语句里的 mysql> select ename, sal from emp where sal > avg(sal); ERROR 1111 (HY000): Invalid use of group functionmysql> select ename, sal from emp where sal > (select avg(sal) from emp); +-------+---------+ | ename | sal | +-------+---------+ | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+任何一个分组函数语句都是在group by之后执行的
where语句后之不能用分组函数
group by 和 having
-
group by：按照某个字段或者某些字段进行分组
-
having ：是对分组之后的数据再进行过滤（having必须和group by一起使用 不能单独使用）
-
找出每个工作岗位的最高工资
mysql> select job, max(sal) from emp group by job asc; +-----------+----------+ | job | max(sal) | +-----------+----------+ | ANALYST | 3000.00 | | CLERK | 1300.00 | | MANAGER | 2975.00 | | PRESIDENT | 5000.00 | | SALESMAN | 1600.00 | +-----------+----------+ //这条语句没有意义 最高薪资与员工不对应 select ename, job, max(sal) from emp group by job asc; +-------+-----------+----------+ | ename | job | max(sal) | +-------+-----------+----------+ | SCOTT | ANALYST | 3000.00 | | SMITH | CLERK | 1300.00 | | JONES | MANAGER | 2975.00 | | KING | PRESIDENT | 5000.00 | | ALLEN | SALESMAN | 1600.00 | +-------+-----------+----------+
-
当一条sql语句有group by时 select后面只能跟分组函数和参与分组函数的字段（按照什么分组）。
-
找出每个部门不同工作岗位的最高薪资（多个字段联合分组）
select deptno, job, max(sal) from emp group by deptno, job; +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 10 | CLERK | 1300.00 | | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 20 | ANALYST | 3000.00 | | 20 | CLERK | 1100.00 | | 20 | MANAGER | 2975.00 | | 30 | CLERK | 950.00 | | 30 | MANAGER | 2850.00 | | 30 | SALESMAN | 1600.00 | +--------+-----------+----------+ -
找出每个部门的最高薪资，并显示2900以上的薪资
能用where用where 不能才用having
//此种方式效率低 select deptno, max(sal) from emp group by deptno having max(sal) > 2900; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | +--------+----------+select deptno, max(sal) from emp where sal > 2900 group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | +--------+----------+ -
找出每个部门的平均薪资并且显示大于2000的
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | +--------+-------------+
关于查询结果的去重
//distinct只能出现在所有字段的最前面 select distinct job from emp; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+ //distinct放在所有字段的最前面表示联合去重 select distinct deptno, job from emp; +--------+-----------+ | deptno | job | +--------+-----------+ | 20 | CLERK | | 30 | SALESMAN | | 20 | MANAGER | | 30 | MANAGER | | 10 | MANAGER | | 20 | ANALYST | | 10 | PRESIDENT | | 30 | CLERK | | 10 | CLERK | +--------+-----------+ -
统计岗位数量
mysql> select count(distinct job) from emp; +---------------------+ | count(distinct job) | +---------------------+ | 5 | +---------------------+
总结
//执行顺序 select ... 5 from ... 1 where ... 2 group by ... 3 having ... 4 order by ... 6 需要了解更多数据库技术:mysql查询、排序、分组，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/818523.html