1.别名的as关键字可以省略吗?
可以的 mysql> select deptno,dname deptname from dept;
2.别名里带空格怎么办?
select deptno,dname ‘dept name’ from dept; //加单引号
select deptno,dname “dept name” from dept; //加双引号
注意:在所有的数据库当中,字符串统一使用单引号括起来,
单引号是标准,双引号在oracle数据库中用不了。但是在mysql
中可以使用。
再次强调:数据库中的字符串都是采用单引号括起来。这是标准的。
双引号不标准。
3.字段可以使用数学表达式吗?
可以的 select ename,sal*12 from emp;
4.如何查询字段为null/不为null的?
使用is null 和 is not null,不能使用= null 或者!= null
5.and和or同时出现的话,有优先级问题吗?
select
*
from
emp
where
sal > 2500 and deptno = 10 or deptno = 20;
分析以上语句的问题?
and优先级比or高。
以上语句会先执行and,然后执行or。
以上这个语句表示什么含义?
找出工资大于2500并且部门编号为10的员工,或者20部门所有员工找出来。
6.模糊查询语法?
like
称为模糊查询,支持%或下划线匹配
%匹配任意多个字符
下划线:任意一个字符。
(%是一个特殊的符号,_ 也是一个特殊符号)
找出名字中有“”的?
select name from t_student where name like '%%'; //这样不行。
mysql> select name from t_student where name like '%\_%'; // \转义字符。
7.可以两个字段排序吗?或者说按照多个字段排序?
查询员工名字和薪资,要求按照薪资升序,如果薪资一样的话,
再按照名字升序排列。
select
ename,sal
from
emp
order by
sal asc, ename asc; // sal在前,起主导,只有sal相等的时候,才会考虑启用ename排序。
8.常见的单行函数
8.1 lower 转换小写
mysql> select lower(ename) as ename from emp;
8.2 upper 转换大写
mysql> select upper(name) as name from t_student;
8.3 substr 取子串
select substr(ename, 1, 1) as ename from emp;
注意:起始下标从1开始,没有0.
找出员工名字第一个字母是A的员工信息?
第一种方式:模糊查询
select ename from emp where ename like ‘A%’;
第二种方式:substr函数
select
ename
from
emp
where
substr(ename,1,1) = ‘A’;
首字母大写?
select name from t_student;
select upper(substr(name,1,1)) from t_student;
select substr(name,2,length(name) - 1) from t_student;
select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
8.4 concat 字符串的拼接
函数进行字符串的拼接
select concat(empno,ename) from emp;
8.5 length 取长度
select length(ename) enamelength from emp;
8.6 trim 去空格
select * from emp where ename = trim(’ KING’);
8.7 str_to_date 将字符串转换成日期
8.8 date_format 格式化日期
8.9 format 设置千分位
8.10 case…when…then…when…then…else…end
当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%,其它正常。
(注意:不修改数据库,只是将查询结果显示为工资上调)
select
ename,
job,
sal as oldsal,
(case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 else sal end) as newsal
from
emp;
8.11 round 四舍五入
select round(1236.567, 0) as result from emp; //保留整数位
select round(1236.567, 1) as result from emp; //保留1个小数
select round(1236.567, 2) as result from emp; //保留2个小数
select round(1236.567, -1) as result from emp; // 保留到十位
8.12 rand() 生成随机数
select round(rand()*100,0) from emp; // 100以内的随机数
8.13 ifnull 可以将 null 转换成一个具体值
ifnull 可以将 null 转换成一个具体值
ifnull是空处理函数。专门处理空的。
在所有数据库当中,只要有NULL参与的数学运算,最终结果就是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 |
±-------±--------+
计算每个员工的年薪?
年薪 = (月薪 + 月补助) * 12
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 |
+--------+----------+
注意:NULL只要参与运算,最终结果一定是NULL。为了避免这个现象,需要使用ifnull函数。
ifnull函数用法:ifnull(数据, 被当做哪个值)
如果“数据”为NULL的时候,把这个数据结构当做哪个值。
补助为NULL的时候,将补助当做0
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 |
+--------+----------+
9.分组函数(多行处理函数)
多行处理函数的特点:输入多行,最终输出一行。
5个:
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注意:
分组函数在使用的时候必须先进行分组,然后才能用。
如果你没有对数据进行分组,整张表默认为一组。
分组函数在使用的时候需要注意哪些?
第一点:分组函数自动忽略NULL,你不需要提前对NULL进行处理。
第二点:分组函数中count(*)和count(具体字段)有什么区别?
count(具体字段):表示统计该字段下所有不为NULL的元素的总数。
count(*):统计表当中的总行数。(只要有一行数据count则++)
因为每一行记录不可能都为NULL,一行数据中有一列不为NULL,则这行数据就是有效的。
第三点:分组函数不能够直接使用在where子句中。
为什么分组函数不能直接使用在where后面?
select ename,sal from emp where sal > min(sal);//报错。
因为分组函数在使用的时候必须先分组之后才能使用。
where执行的时候,还没有分组。所以where后面不能出现分组函数。
第四点:所有的分组函数可以组合起来一起用。
select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
10.分组查询
10.1什么是分组查询?
在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作。
这个时候我们需要使用分组查询,怎么进行分组查询呢?
10.2 重点结论:
在一条select语句当中,如果有group by语句的话,
select后面只能跟:参加分组的字段,以及分组函数。
其它的一律不能跟。
10.3找出“每个部门,不同工作岗位”的最高薪资?
±-------±----------±--------±-------+
| ename | job | sal | deptno |
±-------±----------±--------±-------+
| MILLER | CLERK | 1300.00 | 10 |
| KING | PRESIDENT | 5000.00 | 10 |
| CLARK | MANAGER | 2450.00 | 10 |
| FORD | ANALYST | 3000.00 | 20 |
| ADAMS | CLERK | 1100.00 | 20 |
| SCOTT | ANALYST | 3000.00 | 20 |
| JONES | MANAGER | 2975.00 | 20 |
| SMITH | CLERK | 800.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| MARTIN | SALESMAN | 1250.00 | 30 |
| ALLEN | SALESMAN | 1600.00 | 30 |
| TURNER | SALESMAN | 1500.00 | 30 |
| WARD | SALESMAN | 1250.00 | 30 |
| JAMES | CLERK | 950.00 | 30 |
+--------+-----------+---------+--------+
技巧:两个字段联合成1个字段看。(两个字段联合分组)
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 |
+--------+-----------+----------+
10.4 使用having可以对分完组之后的数据进一步过滤。
having不能单独使用,having不能代替where,having必须
和group by联合使用。
找出每个部门最高薪资,要求显示最高薪资大于3000的?
第一步:找出每个部门最高薪资
按照部门编号分组,求每一组最大值。
select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
第二步:要求显示最高薪资大于3000
select
deptno,max(sal)
from
emp
group by
deptno
having
max(sal) > 3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
思考一个问题:以上的sql语句执行效率是不是低?
比较低,实际上可以这样考虑:先将大于3000的都找出来,然后再分组。
select
deptno,max(sal)
from
emp
where
sal > 3000
group by
deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
优化策略:
where和having,优先选择where,where实在完成不了了,再选择
having。
大总结(单表的查询学完了)
select
…
from
…
where
…
group by
…
having
…
order by
…
以上关键字只能按照这个顺序来,不能颠倒。
执行顺序?
1. from
2. where
3. group by
4. having
5. select
6. order by
从某张表中查询数据,
先经过where条件筛选出有价值的数据。
对这些有价值的数据进行分组。
分组之后可以使用having继续筛选。
select查询出来。
最后排序输出!