单表查询
1、列起别名:
给查询的列起别名:
select loc as lc from dept;
结果为:
mysql> select loc as lc from dept;
+----------+
| lc |
+----------+
| NEW YORK |
| DALLAS |
| CHICAGO |
| BOSTON |
+----------+
注:1、as关键字可以省略,用空格替代:
select loc lc from dept;
结果为:
mysql> select dname dn from dept;
+------------+
| dn |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
2、别名里有空格,用“ ”包裹别名
select dname 'd a'from dept; //双引号也可,但是单引号为统一标准
结果为:
mysql> select loc 'l c' from dept;
+----------+
| l c |
+----------+
| NEW YORK |
| DALLAS |
| CHICAGO |
| BOSTON |
+----------+
2、列参与运算:
原数据:
select ename,sal from emp;
结果为:
mysql> select ename,sal from emp;
+--------+---------+
| 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 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
把sal * 12:
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 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 |
+--------+----------+
注:如果别名是中文,用单引号括起来
3、条件查询;
- = 等于:
mysql> select empno,ename from emp where sal = 800;
+-------+-------+
| empno | ename |
+-------+-------+
| 7369 | SMITH |
+-------+-------+
-
<>或 != 不等于
mysql> select empno,ename from emp where sal != 800; +-------+--------+ | empno | ename | +-------+--------+ | 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 | +-------+--------+
另一种写法:
mysql> select empno,ename from emp where sal <> 800; //小于号和大于号组成的不等号 +-------+--------+ | empno | ename | +-------+--------+ | 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 | +-------+--------+
-
between … and …,两个值之间,等同于 >= and <=
mysql> select empno,ename,sal from emp where sal >=2450 and sal <= 3000; +-------+-------+---------+ | empno | ename | sal | +-------+-------+---------+ | 7566 | JONES | 2975.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+
另一种写法:注意左小右大
mysql> select empno,ename,sal from emp where sal between 2450 and 3000; +-------+-------+---------+ | empno | ename | sal | +-------+-------+---------+ | 7566 | JONES | 2975.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+
-
查询是否为空:
mysql> select empno,ename,sal,comm from emp where comm is null; +-------+--------+---------+------+ | empno | ename | sal | comm | +-------+--------+---------+------+ | 7369 | SMITH | 800.00 | NULL | | 7566 | JONES | 2975.00 | NULL | | 7698 | BLAKE | 2850.00 | NULL | | 7782 | CLARK | 2450.00 | NULL | | 7788 | SCOTT | 3000.00 | NULL | | 7839 | KING | 5000.00 | NULL | | 7876 | ADAMS | 1100.00 | NULL | | 7900 | JAMES | 950.00 | NULL | | 7902 | FORD | 3000.00 | NULL | | 7934 | MILLER | 1300.00 | NULL | +-------+--------+---------+------+
**注:在数据库中null不能使用等号进行衡量,需要使用is null **
-
优先级:
and的优先级大于or,如果想让or先执行,要加小括号。
-
in 包含
in相当于多个or,(not in 表明不在这个范围中)
mysql> select empno,ename,job from emp where job in ('MANAGER','SALESMAN'); +-------+--------+----------+ | empno | ename | job | +-------+--------+----------+ | 7499 | ALLEN | SALESMAN | | 7521 | WARD | SALESMAN | | 7566 | JONES | MANAGER | | 7654 | MARTIN | SALESMAN | | 7698 | BLAKE | MANAGER | | 7782 | CLARK | MANAGER | | 7844 | TURNER | SALESMAN | +-------+--------+----------+
相当于
mysql> select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; +-------+--------+----------+ | empno | ename | job | +-------+--------+----------+ | 7499 | ALLEN | SALESMAN | | 7521 | WARD | SALESMAN | | 7566 | JONES | MANAGER | | 7654 | MARTIN | SALESMAN | | 7698 | BLAKE | MANAGER | | 7782 | CLARK | MANAGER | | 7844 | TURNER | SALESMAN | +-------+--------+----------+
注:in 后面是具体的值,不是区间
-
like:模糊查询
支持 % 或下划线匹配
%:匹配任意多个字符
_:任意一个字符
mysql> select ename from emp where ename like '%o%'; //查询名字里含有o的 +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+
4、排序:
-
指定降序:
mysql> select ename,sal from emp order by sal desc; //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 | | MARTIN | 1250.00 | | WARD | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+
-
指定升序
mysql> select ename,sal from emp order by sal asc;//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 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+
-
两个或多个字段排序:
查询员工名字和薪资,要求按照薪资升序,如果薪资一样,再按照名字升序排列
mysql> 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 | +--------+---------+
5、数据处理函数:
-
单行处理函数有:
lower 转换小写
mysql> select ename from emp; +--------+ | ename | +--------+ | SMITH | | ALLEN | | WARD | | JONES | | MARTIN | | BLAKE | | CLARK | | SCOTT | | KING | | TURNER | | ADAMS | | JAMES | | FORD | | MILLER | +--------+
转换后:
mysql> select lower(ename) from emp; +--------------+ | lower(ename) | +--------------+ | smith | | allen | | ward | | jones | | martin | | blake | | clark | | scott | | king | | turner | | adams | | james | | ford | | miller | +--------------+
注:单行处理函数,14 => 14
-
substr : 取子串,下标从1 开始
mysql> select substr(ename,1,2) as ename from emp; +-------+ | ename | +-------+ | SM | | AL | | WA | | JO | | MA | | BL | | CL | | SC | | KI | | TU | | AD | | JA | | FO | | MI | +-------+
-
length 取长度
mysql> select length(ename) enamelength from emp; +-------------+ | enamelength | +-------------+ | 5 | | 5 | | 4 | | 5 | | 6 | | 5 | | 5 | | 5 | | 4 | | 6 | | 5 | | 5 | | 4 | | 6 | +-------------+
-
round(),四舍五入
mysql> select round(123.456,1) as result from emp; +--------+ | result | +--------+ | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | | 123.5 | +--------+
-
rand()生成随机数
-
ifnull 可以将 null 转换成一个具体的值
mysql> select ename,sal + comm as salcomm from emp; +--------+---------+ | ename | salcomm | +--------+---------+ | SMITH | NULL | | ALLEN | 1900.00 | | WARD | 1750.00 | | JONES | NULL | | MARTIN | 2650.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 1500.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+---------+
只要有nulll参与运算,结果就是null,为了避免,用ifnull
mysql> select ename,sal + ifnull(comm,0) as salcomm from emp; +--------+---------+ | ename | salcomm | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1900.00 | | WARD | 1750.00 | | JONES | 2975.00 | | MARTIN | 2650.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+
-
选择语句:
mysql> 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 | +--------+-----------+---------+---------+
6、分组处理函数:
注:1、分组函数在使用的时候必须先进行分组,如果没分组,则整张表为一个组;
2、分组函数会自动忽略null;
3、分组函数中count和count(*)的区别:
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
count(具体字段):表示统计该字段下所有不为null的元素的总数;
count(*):统计表当中的总行数。(只要有一行数据count ++ )
4、分组函数不能直接使用在where子句中
select ename,sal from emp where sal > min(sal);//报错
因为分组函数在使用的时候必须先分组之后才能使用。
where执行的时候,还没分组,所以where后面不能出现分组函数。
-
执行顺序
from>where>group by>select>order by
eg:找出每个工作岗位的工资和
实现思路:按照工作岗位分组,然后对工资求和。
重点结论:在一条select语句当中,如果有group by 语句的话,select后面只能跟:参加分组的字段,以及分组函数,其他的一律不能跟。
mysql> select -> job,sum(sal) -> from -> emp -> group by -> job; +-----------+----------+ | job | sum(sal) | +-----------+----------+ | ANALYST | 6000.00 | | CLERK | 4150.00 | | MANAGER | 8275.00 | | PRESIDENT | 5000.00 | | SALESMAN | 5600.00 | +-----------+----------+ //执行顺序是,先从emp表中查询数据;根据job字段进行分组,然后对每一组的数据进行sum(sal)
-
按照多字段分组:
eg:找出每个部门,不同岗位的最高薪资
//原表格信息: mysql> select ename,job,sal,deptno from emp; +--------+-----------+---------+--------+ | ename | job | sal | deptno | +--------+-----------+---------+--------+ | SMITH | CLERK | 800.00 | 20 | | ALLEN | SALESMAN | 1600.00 | 30 | | WARD | SALESMAN | 1250.00 | 30 | | JONES | MANAGER | 2975.00 | 20 | | MARTIN | SALESMAN | 1250.00 | 30 | | BLAKE | MANAGER | 2850.00 | 30 | | CLARK | MANAGER | 2450.00 | 10 | | SCOTT | ANALYST | 3000.00 | 20 | | KING | PRESIDENT | 5000.00 | 10 | | TURNER | SALESMAN | 1500.00 | 30 | | ADAMS | CLERK | 1100.00 | 20 | | JAMES | CLERK | 950.00 | 30 | | FORD | ANALYST | 3000.00 | 20 | | MILLER | CLERK | 1300.00 | 10 | +--------+-----------+---------+--------+
技巧:两个字段联合成1个字段看(两个字段联合分组)
mysql> select job,deptno,max(sal) from emp group by job,deptno; +-----------+--------+----------+ | job | deptno | max(sal) | +-----------+--------+----------+ | ANALYST | 20 | 3000.00 | | CLERK | 10 | 1300.00 | | CLERK | 20 | 1100.00 | | CLERK | 30 | 950.00 | | MANAGER | 10 | 2450.00 | | MANAGER | 20 | 2975.00 | | MANAGER | 30 | 2850.00 | | PRESIDENT | 10 | 5000.00 | | SALESMAN | 30 | 1600.00 | +-----------+--------+----------+
-
having语句
eg:找出每个部门最高薪资,要求显示最高薪资大于3000;
mysql> select deptno,max(sal) from emp group by deptno where max(sal) >= 3000;
正确:
mysql> select deptno,max(sal) from emp group by deptno having max(sal) > 3000; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+
但是效率低,可以这样考虑:
先将大于3000的都找出来,然后再分组
mysql> select deptno,max(sal) from emp where sal > 3000 group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+
能使用where过滤的先用where过滤
-
大总结:
select
from
···
wheregroup by
having
order by
执行顺序: - from - where - group by - having - select - order by eg:找出每个岗位的平均薪资,要求显示平均薪资大于1500,除MANAGER岗位之外,要求按照平均薪资降序排 ```mysql mysql> select job,avg(sal) as avgsal from emp where job != 'MANAGER' group by job having avg(sal) > 1500 order by avgsal desc; +-----------+-------------+ | job | avgsal | +-----------+-------------+ | PRESIDENT | 5000.000000 | | ANALYST | 3000.000000 | +-----------+-------------+