排序
#默认是升序
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 |
+--------+---------+
14 rows in set (0.39 sec)
#指定是升序 升序是asc
select ename,sal from sal 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 |
+--------+---------+
14 rows in set (0.00 sec)
#多个字段进行排序的时候,在前面的那个是起的主导的作用,用逗号隔开即可
select ename,sal from emp order by sal asc,ename asc;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.32 sec)
#找出薪资在1500-3000直接按着降序进行排列
select ename,sal from emp where sal between 1500 and 3000 order by sal desc;
#这里的执行顺序是
#第一步 from ...
#第二步 where...
#第三步 select...
#第四步 order by...
+--------+---------+
| ename | sal |
+--------+---------+
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
+--------+---------+
7 rows in set (0.10 sec)
数据处理函数
#单行处理函数,是一行一行进行输入输出;多行处理函数是多行输入一行输出
#lower 大写转小写函数
#upper 小写转大写函数
select lower(ename) as ename from emp;
+--------+
| ename |
+--------+
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
+--------+
#concat做字符串的拼接
#substr是做字符串的阶段substr(name,Index_0,Index_1); 这里面的都是从1开始的不是从0开始
#提取首字母并且将首字母变成大写
#1.首先提取首字母并且变成大写
+--------------------------+
| upper(substr(ename,1,1)) |
+--------------------------+
| S |
| A |
| W |
| J |
| M |
| B |
| C |
| S |
| K |
| T |
| A |
| J |
| F |
| M |
+--------------------------+
14 rows in set (0.10 sec)
#2.提取后面的字母 substr(ename,2,length(name))
select lower(substr(ename,2,length(ename))) from emp;
+--------------------------------------+
| lower(substr(ename,2,length(ename))) |
+--------------------------------------+
| mith |
| llen |
| ard |
| ones |
| artin |
| lake |
| lark |
| cott |
| ing |
| urner |
| dams |
| ames |
| ord |
| iller |
+--------------------------------------+
14 rows in set (0.00 sec)
#3.将两个进行拼接
select concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)))) from emp;
+-----------------------------------------------------------------------+
| concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)))) |
+-----------------------------------------------------------------------+
| Smith |
| Allen |
| Ward |
| Jones |
| Martin |
| Blake |
| Clark |
| Scott |
| King |
| Turner |
| Adams |
| James |
| Ford |
| Miller |
+-----------------------------------------------------------------------+
14 rows in set (0.00 sec)
#length是去长度
select length(ename),empno from emp where sal>3000;
+---------------+-------+
| length(ename) | empno |
+---------------+-------+
| 4 | 7839 |
+---------------+-------+
1 row in set (0.34 sec)
#trim可以去除空格
select * from emp where ename=' king';
Empty set (0.00 sec)
select * from emp where ename=trim(' king');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.17 sec)
#select 后面可以加变量名也可以加字段值
select abc from emp;
ERROR 1054 (42S22): Unknown column 'abc' in 'field list';
select 'abc' from emp;
+-----+
| abc |
+-----+
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
+-----+
14 rows in set (0.00 sec)
#可以进行四舍五入
select round(1234.5678,0) from emp;
+--------------------+
| round(1234.5678,0) |
+--------------------+
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
+--------------------+
14 rows in set (0.09 sec)
select round(1234.5678,1) from emp;
+--------------------+
| round(1234.5678,1) |
+--------------------+
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
+--------------------+
14 rows in set (0.00 sec)
select round(1234.5678,2) from emp;
+--------------------+
| round(1234.5678,2) |
+--------------------+
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
| 1234.57 |
+--------------------+
14 rows in set (0.00 sec)
select round(1234.5678,-1) from emp;
+---------------------+
| round(1234.5678,-1) |
+---------------------+
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
+---------------------+
14 rows in set (0.11 sec)
#rand()生成随机数
select rand() from emp;
+---------------------+
| rand() |
+---------------------+
| 0.8531329406920196 |
| 0.1976366100810809 |
| 0.4287842590629908 |
| 0.5510114958053561 |
| 0.46870473257145356 |
| 0.6904892061748441 |
| 0.04633186389350506 |
| 0.1601917316766379 |
| 0.6619630974363192 |
| 0.8292403228853273 |
| 0.1603123528513241 |
| 0.3138408263342835 |
| 0.08826710385109028 |
| 0.49981307098624583 |
+---------------------+
14 rows in set (0.00 sec)
select round(rand()*100,0) from emp;
+---------------------+
| round(rand()*100,0) |
+---------------------+
| 43 |
| 39 |
| 67 |
| 19 |
| 91 |
| 1 |
| 31 |
| 54 |
| 75 |
| 14 |
| 46 |
| 86 |
| 92 |
| 4 |
+---------------------+
14 rows in set (0.00 sec)
#在数据库操作当中null加上任何的数值都是null
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
select SAL+COMM from emp;
+----------+
| SAL+COMM |
+----------+
| NULL |
| 1900.00 |
| 1750.00 |
| NULL |
| 2650.00 |
| NULL |
| NULL |
| NULL |
| NULL |
| 1500.00 |
| NULL |
| NULL |
| NULL |
| NULL |
+----------+
14 rows in set (0.00 sec)
#mysql ifnull()函数用法ifnull(被处理的数值,被处理成的数值)
select sal+ifnull(comm,0) from emp;
+--------------------+
| sal+ifnull(comm,0) |
+--------------------+
| 800.00 |
| 1900.00 |
| 1750.00 |
| 2975.00 |
| 2650.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
+--------------------+
14 rows in set (0.00 sec)
#case...when...then...when...then...else...end 这里的end很容易忘掉
select ename,job,sal as oldsal
(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end)
as newsal from emp;
+--------+-----------+---------+---------+
| ename | job | oldsal | newsal |
+--------+-----------+---------+---------+
| SMITH | CLERK | 800.00 | 800.00 |
| ALLEN | SALESMAN | 1600.00 | 2400.00 |
| WARD | SALESMAN | 1250.00 | 1875.00 |
| JONES | MANAGER | 2975.00 | 3272.50 |
| MARTIN | SALESMAN | 1250.00 | 1875.00 |
| BLAKE | MANAGER | 2850.00 | 3135.00 |
| CLARK | MANAGER | 2450.00 | 2695.00 |
| SCOTT | ANALYST | 3000.00 | 3000.00 |
| KING | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN | 1500.00 | 2250.00 |
| ADAMS | CLERK | 1100.00 | 1100.00 |
| JAMES | CLERK | 950.00 | 950.00 |
| FORD | ANALYST | 3000.00 | 3000.00 |
| MILLER | CLERK | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec)
分组函数
#多行函数处理,输入多行最终输出一行
#count计数
#sum求和
#avg平均值
#max最大值
#min最小值
#计算最高工资
select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
1 row in set (0.13 sec)
#计算最低工资
select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
1 row in set (0.00 sec)
#计算工资的和
select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.10 sec)
#计算平均工资
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
#计算员工数量
select count(sal) from emp;
+------------+
| count(sal) |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)
#分组函数会自动忽略null,不需要对null进行处理
select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+
1 row in set (0.00 sec)
select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
#count * 表示的是统计的总行数
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.12 sec)
#分组函数不能用在where的后面???????
分组查询*****
#分组查询select...from...where...group by...order by...
#1.from
#2.where
#3.group by
#4.select
#5.order by
#分组函数不可以用在where后面 分组函数要在分组之后(group by)之后才可以进行运用
#所以不可以放在where后面 因为这时候 还没有分组
select job,sum(sal) from emp group by job;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| CLERK | 4150.00 |
| SALESMAN | 5600.00 |
| MANAGER | 8275.00 |
| ANALYST | 6000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
5 rows in set (0.14 sec)
select ename,job,sum(sal) from emp group by job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
#在mysql语句当中,如果含有group by select后面只能跟分组的字段以及分组函数,其他的不可以有
#Oracle要比mysql语法严格还会报错
select deptno,sum(sal) from emp group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 20 | 10875.00 |
| 30 | 9400.00 |
| 10 | 8750.00 |
+--------+----------+
3 rows in set (0.01 sec)
#找出每个部门的最高薪资
select deptno,max(sal) from emp group by deptno order by desc deptno;
select deptno,max(sal) from emp group by deptno order by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
3 rows in set (0.14 sec)
#找出每个部门不同岗位的最高薪资
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 |
+--------+-----------+----------+
9 rows in set (0.01 sec)
#group by 分完组之后可以用 having来进行进一步的过滤
#having 必须和group by联合使用
# select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
3 rows in set (0.01 sec)
#select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
1 row in set (0.38 sec)
#这样的分组效率很低,可以在where后面先去限制工资
select deptno,max(sal) from emp where sal>3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
1 row in set (0.00 sec)
#优先选择where where完成不了再选用having
#计算每个部门的平均薪资,找出平均薪资大于两千五的
select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.11 sec)
#where 后面一定不可以使用分组函数
报错1140
mysql> select deptno,sum(sal) from emp order by deptno;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'bjpowernode.emp.DEPTNO'; this is incompatible with sql_mode=only_full_group_by
总结
单表查询的顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
select ... from ... where ... group by ... having ... order by ...
#找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除MANAGER岗位之外,要求按照平均薪资降序排。
select job,avg(sal) from emp where job!='MANAGER' group by job having avg(sal)>1500 order by avg(sal) desc;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
+-----------+-------------+
2 rows in set (0.00 sec)
#不要忘记职位什么的加上单引号