查询
1. 简单查询
1.1 查询一个字段
select 字段名 from 表名;
注意:
select和from都是关键字。
字段名和表名都是标识符。强调:
对于SQL语句来说,是通用的,
所有的SQL语句以“;”结尾。
另外SQL语句不区分大小写,都行。
例:查询部门名字
mysql> select dname from dept;
+------------+
| dname |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
mysql> SELECT DNAME FROM DEPT;
+------------+
| DNAME |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
1.2 查询两个字段,或者多个字段
使用逗号隔开“,”
例:查询部门编号和部门名
mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
1.3 查询所有字段
-
第一种方式:可以把每个字段都写上
select a,b,c,d,e,f… from tablename; -
第二种方式:可以使用 *
select * from dept;
例:查询dept表的所有字段
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
这种方式的缺点:
1、效率低
2、可读性差。
1.4 给查询的列起别名
使用as关键字起别名。
例:
mysql> select deptno,dname as deptname from dept;
+--------+------------+
| deptno | deptname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
注意:只是将显示的查询结果列名显示为deptname,原表列名还是叫:dname
记住:select语句是永远都不会进行修改操作的。(因为只负责查询)
-
as关键字可以省略
mysql> select deptno,dname deptname from dept;
-
假设起别名的时候,别名里面有空格
select deptno,dname 'dept name' from dept; //加单引号 select deptno,dname "dept name" from dept; //加双引号 +--------+------------+ | deptno | dept name | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+
注意:在所有的数据库当中,字符串统一使用单引号括起来,双引号不标准。
1.5 计算
字段可以使用数学表达式
例:计算员工年薪 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 |
+--------+----------+
2. 条件查询
2.1 什么是条件查询
查询出来符合条件的的数据
语法格式:
select
字段1,字段2,字段3 ...
from
表名
where
条件;
2.2 查询条件的类型
2.2.1 = 等于
例:查询薪资等于800的员工姓名和编号
mysql> select * from emp where sal=800;
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
2.2.2 <> 或 != 不等于
例:查询薪资不等于800的员工姓名和编号
select * from emp where sal != 800;
select * from emp where sal <> 800; // 小于号和大于号组成的不等号
2.2.3 < 小于
例:查询薪资小于2000的员工姓名和编号
mysql> select empno,ename,sal from emp where sal < 2000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
2.2.4 <= 小于等于
例:查询薪资小于等于3000的员工姓名和编号
select empno,ename,sal from emp where sal <= 3000;
2.2.5 > 大于
例:查询薪资大于3000的员工姓名和编号
select empno,ename,sal from emp where sal > 3000;
2.2.6 >= 大于等于
例:查询薪资大于等于3000的员工姓名和编号
select empno,ename,sal from emp where sal >= 3000;
2.2.7 between … and …. 两个值之间, 等同于 >= and <=
例:查询薪资在2450和3000之间的员工信息?包括2450和3000
-
第一种方式:>= 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 | +-------+-------+---------+
-
第二种方式:between … and …
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 | +-------+-------+---------+
注意:
使用between and的时候,必须遵循左小右大。
between and是闭区间,包括两端的值。
2.2.8 is null 为 null(is not null 不为空)
例:查询哪些员工的津贴/补助为null
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 |
+-------+--------+---------+------+
错误写法:
mysql> select empno,ename,sal,comm from emp where comm = null;
Empty set (0.00 sec)
注意:
在数据库当中null不能使用等号进行衡量。需要使用is null因为数据库中的null代表什么也没有,它不是一个值,所以不能使用等号衡量。
例:查询哪些员工的津贴/补助不为null
select empno,ename,sal,comm from emp where comm is not null;
+-------+--------+---------+---------+
| empno | ename | sal | comm |
+-------+--------+---------+---------+
| 7499 | ALLEN | 1600.00 | 300.00 |
| 7521 | WARD | 1250.00 | 500.00 |
| 7654 | MARTIN | 1250.00 | 1400.00 |
| 7844 | TURNER | 1500.00 | 0.00 |
+-------+--------+---------+---------+
2.2.9 and 并且
例:查询工作岗位是MANAGER并且工资大于2500的员工信息
mysql> select
-> empno,ename,job,sal
-> from
-> emp
-> where
-> job='MANAGER' and sal>2500;
+-------+-------+---------+---------+
| empno | ename | job | sal |
+-------+-------+---------+---------+
| 7566 | JONES | MANAGER | 2975.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2.2.10 or 或者
例:查询工作岗位是MANAGER和SALESMAN的员工
mysql> select
-> empno,ename,job
-> from
-> emp
-> where
-> job='MANANGER' or job='SALESMAN';
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7654 | MARTIN | SALESMAN |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
and和or同时出现的优先级问题
例:查询工资大于2500,并且部门编号为10或20部门的员工
mysql> select * from emp where sal>2500 and deptno=10 or deptno=20;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
分析以上语句:
and优先级比or高。
以上语句会先执行and,然后执行or。
以上语句的含义:找出工资大于2500并且部门编号为10的员工,或者20部门所有员工找出来。
and和or同时出现,and优先级较高。如果想让or先执行,需要加“小括号”
select
*
from
emp
where
sal > 2500 and (deptno = 10 or deptno = 20);
2.2.11 in 包含
相当于多个 or (not in 不在这个范围中)
例:查询工作岗位是MANAGER和SALESMAN的员工
select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
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 |
+-------+--------+----------+
注意:in不是一个区间。in后面跟的是具体的值。
not in 表示不在这几个值当中的数据。
select ename,sal from emp where sal not in(800, 5000, 3000);
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| MILLER | 1300.00 |
+--------+---------+
not 可以取非,主要用在 is 或 in 中
is null
is not null
in
not in
2.2.12 like
称为模糊查询,支持%或下划线匹配
%匹配任意多个字符
下划线:任意一个字符。
(%是一个特殊的符号,_ 也是一个特殊符号)
例:找出名字中含有O的
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
例:找出名字以T结尾的
mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
例:找出名字以K开始的
mysql> select ename from emp where ename like 'K%';
+-------+
| ename |
+-------+
| KING |
+-------+
例:找出第二个字每是A的
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
例:找出第三个字母是R的
mysql> select ename from emp where ename like '__R%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| TURNER |
| FORD |
+--------+
例:找出名字中有“_”的
mysql> select name from t_student where name like '%\_%'; // \转义字符。
+----------+
| name |
+----------+
| jack_son |
+----------+
3. 排序
3.1 排序
order by
默认是升序
例:查询所有员工薪资,排序
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 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
3.2 降序
指定降序:desc
mysql> 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 |
| 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 |
+--------+---------+
指定升序:asc
mysql> select
-> ename,sal
-> from
-> emp
-> order by
-> sal 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 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
3.3 两个字段或多个字段排序
例:查询员工名字和薪资,要求按照薪资升序,如果薪资一样的话,再按照名字升序排列。
mysql> select
-> ename,sal
-> from
-> emp
-> order by
-> sal asc,ename asc; // sal在前,起主导,只有sal相等的时候,才会考虑启用ename排序。
+--------+---------+
| 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 |
+--------+---------+
3.4 (了解)根据字段的位置也可以排序
例:按照查询结果的第2列sal排序。
select ename,sal from emp order by 2; // 2表示第二列。第二列是sal
4. 综合案例
例:找出工资在1250到3000之间的员工信息,要求按照薪资降序排列
mysql> select
-> ename,sal
-> from
-> emp
-> where
-> sal between 1250 and 3000
-> order by
-> sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| 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 |
+--------+---------+
关键字顺序不能变:
select
...
from
...
where
...
order by
...
以上语句的执行顺序必须掌握:
第一步:from
第二步:where
第三步:select
第四步:order by(排序总是在最后执行!)