DQL查询

简单查询

查询一个字段

​ 语法格式：select 字段名 from 表明;

​ 所有的sql语句以 ; 结尾

查询所有字段

​ 语法格式：select *from dept;

​ 缺点：效率低，可读性差；

select语句永远不会进行修改操作

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 |
+--------+----------+

条件查询

不是将表中所有的数据都查出来，是查询出来符合条件的

语法格式：select 字段1,字段2,字段3 from 表名 where 条件;

mysql> select empno,ename from emp where sal =800;
+-------+-------+
| empno | ename |
+-------+-------+
| 7369 | SMITH |

mysql> select empno, ename from emp where ename = ‘King’;
+-------+-------+
| empno | ename |
+-------+-------+
| 7839 | KING |
+-------+-------+

mysql> select empno, ename from emp where sal between 2450 and 3000;
+-------+-------+
| empno | ename |
+-------+-------+
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7902 | FORD |
+-------+-------+

查询null

mysql> select empno,ename,sal from emp where comm is null;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+

and

mysql> select empno,ename,sal from emp where job = ‘MANAGER’ and sal > 2500;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
+-------+-------+---------+

OR

mysql> select empno,ename,sal from emp where job = ‘MANAGER’ or job = ‘salesman’;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7844 | TURNER | 1500.00 |
+-------+--------+---------+

and和or同时出现，and优先级较高

mysql> select * from emp where sal > 2500 and( deptno = 10 or deptno = 20);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 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 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+

优先级一般用加（）解决

IN：后面跟的是具体的值，非区间

not in 便是不是这几个值的

mysql> select * from emp where job in(‘MANAGER’,‘analyst’) ;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 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 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+

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’;

找出以K开始的

mysql> select ename from emp where ename like ‘K%’;

找出第二个字母是A的

mysql> select ename from emp where ename like ‘_A%’;
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+

找出有下划线的

用’\‘转义

mysql> select ename from emp where ename like ‘%_%’;

排序

查询所有薪资并排序

​ select ename,sal from emp order by sal;

默认升序

如何降序？

​ 指定降序：select ename,sal from emp order by sal desc;

​ 指定升序：select ename,sal from emp order by sal asc;

按多个字段排序：按照薪资排序，薪资一样的情况下再按照名字排序

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 |
+--------+---------+

注意顺序

select……from……where……order by……