第二章—简单的查询语句(DQL)
1.查询select
-
语法格式:select 字段名1,字段名2,字段名3…… form 表名;
注:任何一条sql语句以";"结尾。
sql语句不区分大小写。
mysql> select empno,ename from emp;
+-------+--------+
| empno | ename |
+-------+--------+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
- select语句中,字段可以参与数学运算。
mysql> select sal from emp;
+---------+
| sal |
+---------+
| 800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
+---------+
mysql> select sal*12 from emp;
+----------+
| sal*12 |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
+----------+
-
给查询结果的列重命名:select 字段的数学运算 as 重命名 from 表名;‘
注:标准sql语句中字符串以单引号形式括起来。
mysql> select sal*12 as yearsal from emp;
+----------+
| yearsal |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
+----------+
mysql> select sal*12 as '年薪' from emp;
+----------+
| 年薪 |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
+----------+
-
查询所有字段:select * from 表名;
注:实际开发中,不建议使用,效率太低。
2.条件查询where
-
语法格式:select 字段,字段…… from 表名 where 条件;
执行顺序:先from,然后where,最后select。
条件查询运算符:
运算符 说明 = 等于 <>或!= 不等于 < 小于 <= 小于等于 > 大于 >= 大于等于 and 并且 between…and… 在两个值之间,即>=and<= is null 为空 or 或者 in 包含,相当于多个or not not可以去非,主要用在is或in中 like 模糊查询,支持%或下划线匹配
#查询字段sql为5000的ename。
mysql> select ename from emp where sal = 5000;
+-------+
| ename |
+-------+
| KING |
+-------+
#查询ename='SMITH'的sal。
#一个字段的数据类型是varchar表示一个字符串
mysql> select sal from emp where ename = 'SMITH';
+--------+
| sal |
+--------+
| 800.00 |
+--------+
#找出sal大于3000的ename
mysql> select ename,sal from emp where sal>3000;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
#找出sal在1000到3000之间的ename
mysql> select ename,sal from emp where sal>=1000 and sal<=3000;
#另一种写法,between是闭区间。
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 |
+--------+---------+
#between可以使用在字符串中
select ename from emp where ename between'A' and 'C';
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+
- 空(NULL)不是一个值,不能用等号衡量,需要使用is null或者is not null。
#找出comm字段为空
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 |
+--------+---------+------+
#找出comm字段不为空
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 |
+--------+---------+---------+
#找出comm为空或者位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 |
+--------+---------+------+
-
and和or的使用
注:当运算符的优先级不确定时,加小括号。
#找出job为MANAGER和SALESMAN的ename
mysql> select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
#and优先级高于or。
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 |
+--------+---------+--------+
- in:在某个值中。
#找出job为MANAGER和SALESMAN的ename
mysql> select ename,job from emp where job in ('SALESMAN','MANAGER');
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
#in是查询具体的数据,不是区间。
mysql> select ename,job,sal from emp where sal in(1100,3000);
+-------+---------+---------+
| ename | job | sal |
+-------+---------+---------+
| SCOTT | ANALYST | 3000.00 |
| ADAMS | CLERK | 1100.00 |
| FORD | ANALYST | 3000.00 |
+-------+---------+---------+
- not in:不在这几个值当中。
mysql> select ename,job,sal from emp where sal not in(1100,3000);
+--------+-----------+---------+
| ename | job | sal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| JONES | MANAGER | 2975.00 |
| MARTIN | SALESMAN | 1250.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| KING | PRESIDENT | 5000.00 |
| TURNER | SALESMAN | 1500.00 |
| JAMES | CLERK | 950.00 |
| MILLER | CLERK | 1300.00 |
+--------+-----------+---------+
3.模糊查询like
- like查询:%代表任意多个字符,_代表任意1个字符。
#找出ename当中含有O的
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
#找出ename当中第二个字母是A的
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
#找出ename当中第三个字母是A的
mysql> select ename from emp where ename like '__A%';
+-------+
| ename |
+-------+
| BLAKE |
| CLARK |
| ADAMS |
+-------+
#找出ename当中有下划线的,在_前面添加转义字符\
mysql> select ename from emp where ename like '%\_%';
Empty set (0.00 sec)
#找出ename当中最后一个字母是T
mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
4.排序order
-
order:默认为升序排序。
asc:指定升序
desc:表示降序
#mysql> select ename,sal from emp order by sal asc;
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 |
+--------+---------+
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 |
+--------+---------+
#按照sal降序排列,当sal相同时按照ename升序排序。
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 |
+--------+---------+
#查询job是SALESMAN的ename,按照sal降序排列。
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 |
+--------+----------+---------+
5.分组函数
-
所有分组函数是对“某一组“数据进行操作。
count计数、sum求和、avg平均值、max最大值、min最小值
-
分组函数一共5个,也称为多行处理函数。
注:分组函数自动忽略NULL。
#求sal这一组数据的和
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
#求sal的平均值
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
#求sal的最高值select max(sal) from emp;
#求sal的最低值select min(sal) from emp;
#求sal的总人数值select count(*) from emp;
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
#分组函数自动忽略NULL。
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
#可以组合起来用
mysql> select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal) | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
+----------+----------+-------------+----------+----------+
-
所有数据库中规定,只要有NULL参与的结果一定是NULL。
ifnull()空处理函数:属于单行处理函数
语法格式:ifnull(可能出现NULL的字段,把它处理为)
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 |
+--------+----------+
-
count(*):不是统计某个字段中数据的个数,而是统计总记录条数。(与某个字段无关)
count(字段):表示统计这个字段中不为NULL的数据总数量。
-
group by:按照某个字段或某些字段进行分组。如果语句没有group by整张表会自成一个组。
注:一般来说,group by与分组函数联合使用。且分组函数在group by语句执行结束后才会执行。group by在where执行之后才执行。
所以where后面不能使用分组函数。
-
当一个语句中,有group by时,select只能跟参加分组的字段以及分组函数。有其他字段会出现错误。
#找出每个job中最高的sal
mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job | max(sal) |
+-----------+----------+
| CLERK | 1300.00 |
| SALESMAN | 1600.00 |
| MANAGER | 2975.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
#找出每个job中的平均sal
mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
#找出不同部门之间不同工作岗位的最高薪资
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 20 | CLERK | 1100.00 |
| 30 | SALESMAN | 1600.00 |
| 20 | MANAGER | 2975.00 |
| 30 | MANAGER | 2850.00 |
| 10 | MANAGER | 2450.00 |
| 20 | ANALYST | 3000.00 |
| 10 | PRESIDENT | 5000.00 |
| 30 | CLERK | 950.00 |
| 10 | CLERK | 1300.00 |
+--------+-----------+----------+
- having:having是对分组之后的数据进行再次过滤。
#找出每个部门的最高薪资,要求显示薪资大于2900的数据
select max(sal),deptno from emp group by deptno having max(sal) > 2900;#这种方式效率低
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 3000.00 | 20 |
| 5000.00 | 10 |
+----------+--------+
#这种效率比较高,能先使用where过滤,先使用where
mysql> select max(sal),deptno from emp where sal>2900 group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 3000.00 | 20 |
| 5000.00 | 10 |
+----------+--------+
#当无法使用where进行过滤,再使用having
#例如找出每个deptno中平均薪资大于2000的数据。
mysql> select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
+-------------+--------+
| avg(sal) | deptno |
+-------------+--------+
| 2175.000000 | 20 |
| 2916.666667 | 10 |
+-------------+--------+
6.小结
-
一个完整的DQL语句格式(顺序不能颠倒):
select … from … where … group by … having … order by…
学习视频:动力节点MySQL基础入门到精通