1、简单的查询语句(DQL)
语法格式:
select 字段名1,字段名2,字段名3,… from 表名;
mysql> select ename,empno from emp;
+--------+-------+
| ename | empno |
+--------+-------+
| SMITH | 7369 |
| ALLEN | 7499 |
| WARD | 7521 |
| JONES | 7566 |
| MARTIN | 7654 |
| BLAKE | 7698 |
| CLARK | 7782 |
| SCOTT | 7788 |
| KING | 7839 |
| TURNER | 7844 |
| ADAMS | 7876 |
| JAMES | 7900 |
| FORD | 7902 |
| MILLER | 7934 |
+--------+-------+
mysql> SELECT ENAME,EMPNO FROM EMP;
+--------+-------+
| ENAME | EMPNO |
+--------+-------+
| SMITH | 7369 |
| ALLEN | 7499 |
| WARD | 7521 |
| JONES | 7566 |
| MARTIN | 7654 |
| BLAKE | 7698 |
| CLARK | 7782 |
| SCOTT | 7788 |
| KING | 7839 |
| TURNER | 7844 |
| ADAMS | 7876 |
| JAMES | 7900 |
| FORD | 7902 |
| MILLER | 7934 |
+--------+-------+
提示:
1、任何一条sql语句都以";"结尾。
2、sql语句不区分大小写,大小写混合也可以。
查询员工年薪?(字段可以参与数学运算)
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 as 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 |
+--------+----------+
别名中有中文?
mysql> select ename,sal * 12 as 年薪 from emp; (错误)
mysql> select ename,sal * 12 as '年薪' from emp; (正确做法)
+--------+----------+
| ename | 年薪 |
+--------+----------+
| 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 |
+--------+----------+
注意:标准sql语句中要求字符使用单引号括起来。虽然mysql支持双引号,尽量别用。
as关键字可以省略?
mysql> select empno,ename,sal*12 yearsal from emp;
+-------+--------+----------+
| empno | ename | yearsal |
+-------+--------+----------+
| 7369 | SMITH | 9600.00 |
| 7499 | ALLEN | 19200.00 |
| 7521 | WARD | 15000.00 |
| 7566 | JONES | 35700.00 |
| 7654 | MARTIN | 15000.00 |
| 7698 | BLAKE | 34200.00 |
| 7782 | CLARK | 29400.00 |
| 7788 | SCOTT | 36000.00 |
| 7839 | KING | 60000.00 |
| 7844 | TURNER | 18000.00 |
| 7876 | ADAMS | 13200.00 |
| 7900 | JAMES | 11400.00 |
| 7902 | FORD | 36000.00 |
| 7934 | MILLER | 15600.00 |
+-------+--------+----------+
查询全部字段?
select * from emp; (实际开发中不建议使用,*效率低,它需要先转换为字段)
2、条件查询
语法格式:
select:
字段,字段...
from
表名
where
条件;
执行顺序:先from,然后where,最后select
查询工作等于5000的员工姓名?
mysql> select ename from emp where sal=5000;
+-------+
| ename |
+-------+
| KING |
+-------+
查询SMiTH的工资?
mysql> select sal from emp where ename='SMITH'; // 字符串使用单引号括起来
+--------+
| sal |
+--------+
| 800.00 |
+--------+
找出工资不低于3000的员工?
mysql> select ename,sal from emp where sal>=3000;
+-------+---------+
| ename | sal |
+-------+---------+
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
运算符:
<>或者!= 不等于
between...and... 两个值之间(闭区间,必须左小右大!!!),等同于>=and<=
is null 为null(is not null 不为空)
and 并且
or 或者
in 包含,相当于多个or(not in 不在这个范围中)
not 非,主要是用在in 和 null 中
like 模糊查询
找出工资不等于3000的?
select ename,sal from emp where sal <> 3000;
select ename,sal from emp where sal != 3000;
找出工资在1100和3000之间的员工,包括1100和3000?
select ename,sal from emp where sal >= 1100 and sal <= 3000;
select eanme,sal from emp where sal between 1100 and 3000;
between…and…在使用的时候必须左小右大。
between…and…除了可以使用在数字方面之外,还可以使用在字符串方面。
mysql> select ename from emp where ename between 'A' and 'C'; //(注意:首字母左闭右开!!!)
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+
找出哪些人津贴为空?
在数据库当中NULL不是一个值,代表什么也没有,为空。
空不是一个值,不能用等号来衡量。
必须使用 is null 或者 is not null;
select ename,sal,comm from emp where comm is null;
找出哪些人没有津贴?
select ename,sal,comm from emp where comm is null or comm = 0;
and和or联合起来用:找出薪资大于1000的并且部门编号是20或者30的部门员工
select ename,sal,deptno from emp where sal > 1000 and deptno =20 or deptno = 30; // 错误
select ename,sal,deptno from emp where sal > 1000 and (deptno =20 or deptno = 30); // 正确的
注意:当运算符的优先级不确定的时候,加小括号。
找出工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job='SALESMAN' or job='MANAGER';
select ename,job from emp where job in('SALESMAN','MANAGER');
select ename,job from emp where sal in (1000,5000); //注意:这里是工资为1000或者5000,不是1000~5000
模糊查询like?
(在模糊查询中,必须掌握两个特殊符号,一个是%,一个是_)
%代表任意多个字符,_代表任意1个字符。(有点像正则)
找出名字中含有O的?
select ename from emp where ename like '%o%';
mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
找出名字中第二个字母是A的?
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
找出名字中含有下划线的?
这里需要用到转义字符\
select ename from emp where ename like '%\_%';
3、排序(升序、降序)
按照工资升序,找出员工名和薪资?
select
ename, sal
from
emp
order by
sal;
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 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
+--------+---------+
注意:默认升序。怎么指定升序还是降序呢?asc表示升序,desc表示降序。
select ename,sal from emp order by sal;
select ename,sal from emp order by sal asc; // 指定升序
select ename,sal from emp order by sal desc; // 指定降序
按照工资的降序排列,当工资一样的时候,再按照名字的升序排列?
select ename,sal from emp order by sal desc, ename asc;
注意:越靠前的字段越能起到主导作用。只有当前面的字段无法排序的时候,才会启用后面的字段。
select ename,sal from emp order by 1; // 1表示按照第一列排序,这里指ename
select ename,sal from emp order by 2; // 2表示按照第二列排序,这里指sal
找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列
select
ename,job,sal
from
emp
where
job='SALESMAN'
order by
sal desc;
这里先执行from,然后执行where,接着执行select,最后执行order by。
4、分组函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
记住:所有的分组函数都是对“某一组”数据进行操作的。
找出工资总和:
select sum(sal) from emp;
找出最高工资:
select max(sal) from emp;
找出平均工资:
select avg(sal) from emp;
找出总人数:
select count(ename) from emp;
或者:select count(*) from emp;
分组函数一共只有这5个,分组函数还有另一个名字:多行处理函数。
多行处理函数特点:输入多行,最终输出的结果是1行。
注意:分组函数自动忽略NULL。
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
mysql> select sum(comm) from emp;
//有NULL的四则运算结果肯定为NULL,而sum不为NULL
//说明分组函数自动忽略NULL,不需要额外添加条件语句
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+
找出工资高于平均工资的员工?
select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function
错误信息:无效地使用了分组函数
原因:SQL语句中有一个语法规则,分组函数不能直接使用在where子句中。why??
怎么解释???
因为group by是在where执行之后才会执行的。当一条sql语句没有group by的话,整张表的数据自成一组,这里可以理解为隐藏着group by.
执行顺序:
select
... 5
from
... 1
where
... 2 第一次过滤
group by
... 3 分组
having
... 4 再次过滤
order by
... 6
那么如何找出工资高于平均工资的员工?
第一步:找出平均工资
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
第二步:高于平均工资的员工
select ename,sal from where sal > 2073.214286;
能不能用一句话解决?子查询,SQL语句嵌套
select ename,sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
count(*)和count(具体的某个字段)有什么区别?
count(*):不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)
count(comm):表示统计comm字段中不为NULL的数据总数量。
分组函数也能组合起来用:
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
5、单行处理函数
什么是单行处理函数?
输入一行,输出一行。
计算每个员工的年薪?
select ename,(sal+comm)*12 as yearsal from emp;
mysql> select ename,(sal+comm)*12 as yearsal from emp; // 但是任何数据与NULL作四则远算,结果都为NULL
使用ifnull()函数:
select ename,(sal + ifnull(comm,0))*12 as yearsal from emp;
ifull() 空处理函数?
ifnull(可能为NULL的数据,被当做什么处理)
select ename,ifnull(comm,0) as comm from emp;
6、group by 和 having
group by:按照某个字段或者某些字段进行分组。
having: 对分组之后的数据进行再次过滤。不能单独使用,必须和group by组合使用。
案例:找出每个岗位的最高薪资。
select max(sal),job from emp group by job;
+----------+-----------+
| max(sal) | job |
+----------+-----------+
| 3000.00 | ANALYST |
| 1300.00 | CLERK |
| 2975.00 | MANAGER |
| 5000.00 | PRESIDENT |
| 1600.00 | SALESMAN |
+----------+-----------+
注意:分组函数一般都会和group by 联合使用,这样是为什么它被称为分组函数的原因。
并且任何一个分组函数都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话,整张表的数据自成一组。
注意下面这条语句:
select ename,max(sal),job from emp group by job;
这条语句在oracle中会语法报错,但在mysql中可以执行,并且这条语句的执行结果毫无意义。
记住一个规则:有group by的语句,select后面只能跟分组函数和参与分组的字段。
多个字段能不能联合起来一块分组?
案例:找出每个部门不同岗位的最高薪资
select
deptno,job,max(sal)
from
emp
group by
deptno,job; // 可以看成分组依据为deptno+job
找出每个部门的最高薪资,要求显示薪资大于2900的数据。
第一步:找出每个部门最高薪资
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
第二步:找出薪资大于2900的:
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>2900;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
+--------+----------+
// 这种方式效率低,因为它依据找出所有部门max(sal),最后又舍弃部分。
// 不如在找最大值之前就把不用的去掉。
实际上这里用where更加高效,建议能够使用where过滤的尽量使用where
select deptno,max(sal) from emp where sal>2900 group by deptno;
找出每个部门平均薪资,要求显示薪资大于2000的数据。
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
7、总结:一个完整的DQL语句怎么写?
select
... 5
from
... 1
where
... 2
group by
... 3
having
... 4
order by
... 6