查询
-
查询所有的数据
select * from emp; -
查询指定的数据
select column/函数
select column_name1, column_name2, … from table_name;
select mgr from emp;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7698 |
| 7839 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7566 |
+------+
- 去除重复记录的查询
默认查找会把重复的也给查出来.
select distinct column_name1, column_name2 from table_name;
distinct: 去除查询结果中重复的记录时使用的关键字, distinct 作用于单个字段。
select distinct mgr from emp;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
+------+
- 使用算术运算符的查询
可以使用+ - * / %等运算符
已知月薪,求年薪
select sal*12 from emp;
列后面的名称代表的是取的别名
+----------+
| sal*12 |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 36000.00 |
| 60000.00 |
| 36000.00 |
+----------+
- 使用字段别名的查询
select column_name1 [as] othername1 from table_name;
[as] 中括号括起来的代表是可选的
select sal*12 from emp;
+----------+
| sal*12 |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 36000.00 |
| 60000.00 |
| 36000.00 |
+----------+
注意:如果字段名中包含空格和一些特殊的字符的话,需要用单引号括起来。
select sal*12 'yearsal&' from emp;
- 设置数据显示格式的查询
为了方便的浏览表中的信息,可以设置数据显示的格式。可以使用到concat()函数。
注意:在Mysql中有两种字符串的连接方式:+ 和 concat()函数
select concat('姓名:',ename,',部门:',depto) info from emp;
+-------------------------------+
| info |
+-------------------------------+
| 姓名:Smith,部门:20 |
| 姓名:Allen,部门:20 |
| 姓名:Ward,部门:30 |
| 姓名:Jones,部门:20 |
| 姓名:Maritn,部门:30 |
| 姓名:Blake,部门:30 |
| 姓名:Scott,部门:10 |
| 姓名:King,部门:10 |
| 姓名:Ford,部门:20 |
+-------------------------------+
这样子就可以直观方便的查看数据了。
- 对查询数据排序
7.1 按照指定的单字段排序
select column_name1, column_name2 from table_name order by order_name [asc|desc];
asc代表升序, desc代表是降序, 默认是升序。
升序
select ename, sal from emp order by sal;
+--------+---------+
| ename | sal |
+--------+---------+
| Smith | 800.00 |
| Ward | 1250.00 |
| Maritn | 1250.00 |
| Allen | 1600.00 |
| Blake | 2850.00 |
| Jones | 2975.00 |
| Scott | 3000.00 |
| Ford | 3000.00 |
| King | 5000.00 |
+--------+---------+
降序
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 |
| Allen | 1600.00 |
| Ward | 1250.00 |
| Maritn | 1250.00 |
| Smith | 800.00 |
+--------+---------+
注意:如果指定的排序字段的值为Null, 则会将该值作为最小值来处理
7.2 按照指定的多字段排序
select column_name1, column_name2 from table_name order by order_name1 [asc|desc], order_name2 [asc|desc];
select ename, hiredate, sal from emp order by sal,hiredate;
+--------+------------+---------+
| ename | hiredate | sal |
+--------+------------+---------+
| Smith | 1980-12-17 | 800.00 |
| Maritn | 1981-06-22 | 1250.00 |
| Ward | 1981-02-22 | 1250.00 |
| Allen | 1981-02-20 | 1600.00 |
| Blake | 1981-09-22 | 2850.00 |
| Jones | 1981-03-22 | 2975.00 |
| Ford | 1981-03-22 | 3000.00 |
| Scott | 1981-01-22 | 3000.00 |
| King | 1981-04-22 | 5000.00 |
+--------+------------+---------+
在这里sal是升序,而hiredate是降序排列的.
- 条件查询
之前的都是查询表中的所有的字段,但是在我们实际的开发过程中,经常是根据某个条件来进行查询,达到一个过滤的效果。
select column_name1, column_name2 from table_name where where_condition;
这里时使用了where字句,用来指定查询条件.
条件查询的实现形式多种多样,可以在where子句里面使用比较运算符、between and 、 in 、 is null、 like 、 and 、 or等来指定查询条件。
8.1 使用比较运算符的查询
通常使用 >, < , >=, <=, = , <>不等于, !=不等于
1. 查询emp表中编号deptno字段中值为30的所有员工的信息。
select * from emp where deptno=30;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
2. 使用<=
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
| 7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL | 10 |
| 7839 | King | president | NULL | 1981-04-22 | 5000.00 | NULL | 10 |
| 7902 | Ford | analyst | 7566 | 1981-03-22 | 3000.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
注意:在使用比较运算符的时候,= 比较字符串的条件(不区分大小写).
select * from emp where ename='smith';
select * from emp where ename='SMITH';
这两个是一样的。
8.2 使用 [not] between … and … 的范围查询
实现判断某个字段的值是否在指定范围内的条件查询.
select column_name1,column_name2 from table_name where column_name [not] between value and value2;
1. select * from emp where sal between 1500 and 3000;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
| 7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL | 10 |
| 7902 | Ford | analyst | 7566 | 1981-03-22 | 3000.00 | NULL | 20 |
+-------+-------+----------+------+------------+---------+--------+--------+
注意:是包括1500和3000值的。
2. select * from emp where sal not between 1500 and 3000;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
| 7839 | King | president | NULL | 1981-04-22 | 5000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
8.3 使用 [not] in 的指定集合查询
不仅可以使用between and 来表示某个字段的值是否在指定的范围中,还可以使用 in 关键字来判断某个字段的值是否在某个指定的集合内。
select column_name1,column_name2 from table_name where column_name [not] in (value1, value2, …);
1. select * from emp where ename in ('King, 'SMITH');
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7839 | King | president | NULL | 1981-04-22 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
会查找到在in里面的数据。
2. select * from emp where ename not in ('King, 'SMITH');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
| 7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL | 10 |
| 7902 | Ford | analyst | 7566 | 1981-03-22 | 3000.00 | NULL | 20 |
+-------+--------+----------+------+------------+---------+---------+--------+
8.4 使用is [not] null 的空值查询
可以在where子句中使用 ‘is null’ 来判断某个字段的值是否为空(空值为null, 并不是0或者空字符串)。
select column_name1, column_name2,… from table_name where column_name is [not] null;
1. select * from emp where comm is null;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
| 7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL | 10 |
| 7839 | King | president | NULL | 1981-04-22 | 5000.00 | NULL | 10 |
| 7902 | Ford | analyst | 7566 | 1981-03-22 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
2. select * from emp where comm is not null;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
8.5 使用 [not] like 的模糊查询
在where中可以使用预算符=来指定查找,但是在我们实际开发中,我们需要根据以S来查找所有的S开头的字母,这就会用到模糊查询,查询这一类的数据。
slect column_name1, column_name2 from table_name where column_name [not] like value;
- 使用%通配符的模糊查询(是查询的多个字符,相当于*的作用,匹配的是[0,])
1. select * from emp where ename like 'S%';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL | 10 |
+-------+-------+---------+------+------------+---------+------+--------+
查询到所有的以S开头的字母
2. select * from emp where ename like '%S';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
创建阿寻到所有以S结尾的字符
3. select * from emp where ename like '%S%';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
| 7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL | 10 |
+-------+-------+---------+------+------------+---------+------+--------+
查询到所有的字母中包含S的,不管是开头,还是结尾,还是中间都会进行匹配。
- 使用"_"通配符模糊查询 (匹配的是一个, [0,1]);
1. select * from emp where ename like '_L%';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
2. select * from emp where ename like '%L_';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | Alle | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7698 | Bla | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
3. select * from emp where ename like '_L_';
匹配的是 三个字符
使用not like ‘%S%’;
select * from emp where ename not like '%S%';
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
| 7839 | King | president | NULL | 1981-04-22 | 5000.00 | NULL | 10 |
| 7902 | Ford | analyst | 7566 | 1981-03-22 | 3000.00 | NULL | 20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
注意:要是在里面要使用字符串% 和 _ 的话, 就是使用到转译字符, ‘_’, ‘%’ 来进行转译。
8.6 使用 and 的多条件查询
前面都是讲述了只有一个查询条件,但有时候仅仅指定一个查询条件并不能满足用户的需求,需要使用and,进行多条件的查询。
select column_name1, column_name2 from table_name where where_condition and where_conditon2 and where_condition3;
select * from emp where deptno=20 and job='clerk';
+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
8.7 使用 or 的多条件查询
在MySql中还有一个关键字可以将多个查询条件连接起来,这个关键字就是 ‘or’。 将多个查询条件使用 or 连接起来,表示只要满足其中一个任意的条件,记录就能够查询出来。
select column_name1, column_name2, from table_name where where_condition 1 or where_condition 2 [or where_condition 3]
select * from emp where deptno=20 or job='clerk';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
| 7902 | Ford | analyst | 7566 | 1981-03-22 | 3000.00 | NULL | 20 |
+-------+-------+----------+------+------------+---------+--------+--------+
注意:
1. and 和 or 都是;逻辑运算符。
2. and 于 && 等价。
3. or 与 || 等价。
- 限制查询
在实际的开发过程中,表中的查询量是非常大的,查询到的数据过多的话,如果把所有的数据都显示出来显然是不符合实际的,此时我们就可以通过MySql提供的限制查询结果中显示记录的数目。
限制查询使用的关键字是limit, 在该关键字后可以指定查询结果中的显示记录的初始位置和记录显示的行数。
select column_name1, column_name2 from table_name where where_condition limit [start_index], row_count;
其中, start_index 为可选项, 表示显示记录的初始位置, row_count表示记录显示的行数。
9.1 不指定初始位置的限制查询
select * from emp where sal < 3000 limit 3;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 20 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
start_index 参数的值默认为0, 查询出来前三条数据, 原本有很多条数据,因为limit限制了条数,就查询出来了三条。
9.2 指定初始化位置的限定查询
select * from emp where sal < 3000 limit 3, 3;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL | 20 |
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
| 7698 | Blake | manager | 7839 | 1981-09-22 | 2850.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
limit 3 代表 start_index 从第四条记录开始查询, 查询出几条是由参数row_count来决定的。
注意:
1. 如果limit限制如果有11条记录,而有13条限制, 就只会显示出来11条数据。
2. limit 关键字通常用于分页显示,第一页不需要指定显示记录的初始位置,从第二条开始则需要指定。
3. limit 关键字通常与 order by 子句一起使用,先对查询结果进行排序,然后分页显示。
- 函数查询
MySql中提供了大量的函数来简化用户对数据库的操作,如字符串的处理,日期的运算,数值的运算。使用函数可以提高查询的能力,同时也给数据的转换和处理提供了方便。
函数只是对查询结果中的数据进行处理,不会改变数据库中数据表的值,提供了对单行函数和多行函数两大类。
10.1 使用单行函数的查询
单行函数是对每一条数据进行计算,经过函数的计算得到结果返回给用户。
- 字符串函数。
(1). concat(str1, str2, strn); //拼接成一个新字符串
(2). insert(str,index , n newstr) //将字符串str从第index位置开始n个字符替换成字符串newstr
(3). length // 获取字符串str的长度
(4). lower(str) //转为小写
(5). upper(str) //转为大写
(6). left(str, n); // 获取str最左边的n个字符
(7). replace(str, oldstr, newstr); //用字符串newstr替换字符串str中的所有的子字符串oldstr
(8). substring(str, index, n); //获取从字符串str的index位置开始的n个字符
1. concat
select concat('姓名:',ename,',部门:',deptno) info from emp where deptno = 10;
+------------------------+
| info |
+------------------------+
| 姓名:Scott,部门:10 |
| 姓名:King,部门:10 |
+------------------------+
2. length()函数的使用
如果根据字符的长度来作为查询条件的话,就会用到length()函数。
select * from emp where length(ename)=6;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3. 使用lower() 和 upper() 函数的查询
select ename, lower(ename), upper(ename) from emp where deptno = 10;
+-------+--------------+--------------+
| ename | lower(ename) | upper(ename) |
+-------+--------------+--------------+
| Scott | scott | SCOTT |
| King | king | KING |
+-------+--------------+--------------+
4. replace()函数的使用
select ename, job, replace(job, 'clerk', 'staff') newjob from emp where job='clerk';
+-------+-------+--------+
| ename | job | newjob |
+-------+-------+--------+
| Smith | clerk | staff |
+-------+-------+--------+
可以看出, 成功得奖字符串clerk替换成了staff
5. substring() 函数的使用
select ename, substring(ename, 1, 3) subname, job from emp where job = 'clerk';
+-------+---------+-------+
| ename | subname | job |
+-------+---------+-------+
| Smith | Smi | clerk |
+-------+---------+-------+
注意: 第一个字符的下表为1, 而不是0, 所以1-3截取的是Smi
- 数值函数
数值函数是用来处理数值运算的函数。
(1). abs(num) //返回的是num的绝对值
(2). ceil //向上取整
(3). floor(num); //向下取整
(4). mod(num1, num2); //返回num1/num2 的余数(取模)
(5). pi(); //返回圆周率的值
(6). pow(num. n) / power(num, n); //返回num的n次方
(7). rand(num); 返回0-1之间的随机数
(8). round(num, n); //四舍五入的值, 该值保留小数点后n位
(9). truncate(num, n); //返回num被舍去至小数点后n位的值。
- 日期与时间函数
- 间隔类型函数
- 流程函数
(1). if(condition, t, f); //如果条件condition为真, 则返回t,否则返回f
(2). ifnull(value1, value2); //如果value1 不为null, 则返回value1, 否则返回value2
(3). nullif(value1, value2); //如果value1等于value2 ,返回null, 否则返回value1
(4). case value when [value] then result1[when value2 then result2 [else result] end //如果value等于value1, 则返回result, 否则返回 result
1. if() 函数
select ename, deptno, sal, if(sal >= 3000, 'high','low') sal_level from emp where deptno= 20;
+-------+--------+---------+-----------+
| ename | deptno | sal | sal_level |
+-------+--------+---------+-----------+
| Smith | 20 | 800.00 | low |
| Allen | 20 | 1600.00 | low |
| Jones | 20 | 2975.00 | low |
| Ford | 20 | 3000.00 | high |
+-------+--------+---------+-----------+
2. ifnull() 函数
2.1 select ename, deptno,comm, job, sal*12 + comm year_income from emp where deptno=30;
+--------+--------+---------+----------+-------------+
| ename | deptno | comm | job | year_income |
+--------+--------+---------+----------+-------------+
| Ward | 30 | 500.00 | salesman | 15500.00 |
| Maritn | 30 | 1400.00 | salesman | 16400.00 |
| Blake | 30 | NULL | manager | NULL |
+--------+--------+---------+----------+-------------+
注意: Blake 的年薪返回的不是一个数字, 而是null, 这是因为 null + 数字 返回 null 所导致的, null不等于空字符或者0
2.2 解决办法使用 ifnull()函数
select ename, deptno,comm, job, sal*12 + ifnull(comm, 0) year_income from emp where deptno=30;
+--------+--------+---------+----------+-------------+
| ename | deptno | comm | job | year_income |
+--------+--------+---------+----------+-------------+
| Ward | 30 | 500.00 | salesman | 15500.00 |
| Maritn | 30 | 1400.00 | salesman | 16400.00 |
| Blake | 30 | NULL | manager | 34200.00 |
+--------+--------+---------+----------+-------------+
我们会发现,如果使用了ifnull, 可以完美解决null的问题。
3. nullif() 函数
select nullif(1,1), nullif(1,2) from emp;
+-------------+-------------+
| nullif(1,1) | nullif(1,2) |
+-------------+-------------+
| NULL | 1 |
可以看出, 如果两个参数相等时,返回的是null, 两个数字不同时,返回的是第一个参数的值。
4. case..when..then..() 函数
前面使用if函数来实现薪水高度的分类,使用case..when..then..()也可以实现。
select ename, deptno, sal, case sal>=3000 when true then 'high' else 'low' end sal_level from emp where deptno= 20;
+-------+--------+---------+-----------+
| ename | deptno | sal | sal_level |
+-------+--------+---------+-----------+
| Smith | 20 | 800.00 | low |
| Allen | 20 | 1600.00 | low |
| Jones | 20 | 2975.00 | low |
| Ford | 20 | 3000.00 | high |
+-------+--------+---------+-----------+
- 其他函数
10.2 使用多行函数的查询
多行函数是针对一组数据进行运算, 针对这一组数据(多行巨鹿)只返回一个结果,也称为分组函数,聚集函数
在实际的开发中,多行函数多为统计函数。统计人数,统计平均数等,就需要用到统计函数。
(1). count() //统计表中记录的数目
(2). sum() //计算指定字段值的总和
(3). avg() //计算指定字段值的平均值
(4). max() //统计字段值中的最大值
(5). min() //统计字段值中的最小值
1. count() 函数
1.1 count(*) 返回表中记录的总数
1.2 count(exp) 返回表达式exp值非空的记录数目
1.3 count(distinct(exp)); 返回exp值不重复、非空的记录数目.
select count(*), count(comm),count(distinct(mgr)), count(distinct(ifnull(mgr, 0))) from emp;
+----------+-------------+----------------------+---------------------------------+
| count(*) | count(comm) | count(distinct(mgr)) | count(distinct(ifnull(mgr, 0))) |
+----------+-------------+----------------------+---------------------------------+
| 9 | 3 | 4 | 5 |
+----------+-------------+----------------------+---------------------------------+
2. sum(), avg() 函数
sum(exp); 返回总和
sum(distinct(exp)) 返回不重复表达式的总和
avf() 和 sum () 函数用法一样
select sum(sal),avg(sal), sum(distinct sal), avg(distinct sal) from emp;
+----------+-------------+-------------------+-------------------+
| sum(sal) | avg(sal) | sum(distinct sal) | avg(distinct sal) |
+----------+-------------+-------------------+-------------------+
| 21725.00 | 2413.888889 | 17475.00 | 2496.428571 |
+----------+-------------+-------------------+-------------------+
3. max() 和 min() 函数
select max(sal), min(sal) from emp;
+----------+----------+
| max(sal) | min(sal) |
+----------+----------+
| 5000.00 | 800.00 |
+----------+----------+
注意:在计算最小值时,null不会计算,会被忽略.
- 分组查询
在实际的开发过程中,我们通常将表中的所有数据记录并按照不同的类别进行分组,然后对分组后的数据进行计算。
我们使用 group by 子句可以指定哪一个或哪些字段进行分组, 指定字段值相同的记录为一组。
使用group by 子句分组后的查询操作比较复杂, 分为三个阶段。
1. 使用group by 的简单分组查询.
2. 使用group by 于 统计函数的分组查询。
3. 使用 group by 于 having的分组查询
单字段分组
select sum(sal) from emp group by deptno;
+----------+
| sum(sal) |
+----------+
| 8000.00 |
| 8375.00 |
| 5350.00 |
+----------+
分组要和聚集函数一起用,才可以.
多字段分组
select sum(sal) from emp group by deptno,job;
+----------+
| sum(sal) |
+----------+
| 3000.00 |
| 5000.00 |
| 3000.00 |
| 800.00 |
| 2975.00 |
| 1600.00 |
| 2850.00 |
| 2500.00 |
+----------+
与统计函数分组
select deptno, count(*), avg(sal), max(sal) from emp group by deptno;
+--------+----------+-------------+----------+
| deptno | count(*) | avg(sal) | max(sal) |
+--------+----------+-------------+----------+
| 10 | 2 | 4000.000000 | 5000.00 |
| 20 | 4 | 2093.750000 | 3000.00 |
| 30 | 3 | 1783.333333 | 2850.00 |
+--------+----------+-------------+----------+
group by 与 where 连用
select deptno, count(*), avg(sal), max(sal) from emp where deptno<>10 group by deptno;
+--------+----------+-------------+----------+
| deptno | count(*) | avg(sal) | max(sal) |
+--------+----------+-------------+----------+
| 20 | 4 | 2093.750000 | 3000.00 |
| 30 | 3 | 1783.333333 | 2850.00 |
+--------+----------+-------------+----------+
使用group by 与 group concat() 的分组查询
select deptno, count(*), group_concat(ename) enames from emp group by deptno;
+--------+----------+------------------------+
| deptno | count(*) | enames |
+--------+----------+------------------------+
| 10 | 2 | Scott,King |
| 20 | 4 | Smith,Allen,Jones,Ford |
| 30 | 3 | Ward,Maritn,Blake |
+--------+----------+------------------------+
使用group by 与 having 的分组查询
注意: where语句不能使用统计函数,就是不能使用where子句来实现对分组的条件限制, 这是因为一个执行的顺序所导致的。MySql提供了一种专门对分组进行限制的,having子句.
select deptno,count(*), avg(sal) from emp group by deptno having avg(sal)>= 2000;
+--------+----------+-------------+
| deptno | count(*) | avg(sal) |
+--------+----------+-------------+
| 10 | 2 | 4000.000000 |
| 20 | 4 | 2093.750000 |
+--------+----------+-------------+
注意:
1. MySql中各子句的执行过程由先到后的执行依次为: from -> where -> group by -> having -> select -> order by;
2. where 过滤行, having 过滤分组,having支持所以的where操作。
3. having 子句出现group by 子句之后, 而 where子句要出现在 group by 子句之前。