DQL 简单查询
1.简单的查询语句(DQL)
提示:1.任何一条sql语句以“;”结尾。
2.sql语句不区分大小写。
1.1查询一个字段
select 字段名1,字段名2,字段名3,…from 表名;
select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows in set (0.10 sec)
查询员工的年薪(字段可以参与数学运算)
mysql> select empno,ename,sal*12 from emp;
+-------+--------+----------+
| empno | ename | sal*12 |
+-------+--------+----------+
| 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 |
+-------+--------+----------+
14 rows in set (0.00 sec)
给查询结果的列重命名(字符串用单引号括起来,不然)
mysql> select empno,ename,sal*12 as 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 |
+-------+--------+----------+
14 rows in set (0.00 sec)
mysql> select empno,ename,sal*12 as '年薪' 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 |
+-------+--------+----------+
14 rows in set (0.00 sec)
select empno,ename,sal*12 as ‘年薪’ from emp; //as关键字可以省略
查询所有字段(不建议java中使用,效率比较低)
select * from emp;
1.2、条件查询
select
字段,字段
from
表名
where
条件;
查询工资等于5000的员工姓名:select empno,ename from emp where sal = 5000;
查询smith的工资:select empno,ename,sal from emp where ename = ‘smith’;
mysql> select empno,ename from emp where sal = 5000;
+-------+-------+
| empno | ename |
+-------+-------+
| 7839 | KING |
+-------+-------+
1 row in set (0.00 sec)
mysql> select empno,ename,sal from emp where ename = 'smith';
+-------+-------+--------+
| empno | ename | sal |
+-------+-------+--------+
| 7369 | SMITH | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)
mysql> desc emp;//查看表结构
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
找出工资不等于3000的:select empno,ename,sal from emp where sal <> 3000;***(也可以使用 !=)***
mysql> select empno,ename,sal from emp where sal <> 3000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
找出工资在1100-3000的员工,包括1100与3000:
select empno,ename,sal from emp where sal >=1100 and sal <= 3000; (and可以换成&&)
mysql> select empno,ename,sal from emp where sal >=1100 && sal <= 3000;
+-------+--------+---------+
| 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 |
| 7788 | SCOTT | 3000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
11 rows in set, 1 warning (0.00 sec)
between关键字
上面那个范围中也可以写成:
select empno,ename,sal from emp where sal between 1100 and 3000;
between… and…是一个闭区间【1100 ~ 3000】,必须是左小右大!
between还可以用在字符串方面,如:
select ename from emp where ename between ‘A’ and ‘D’;
mysql> select ename from emp where ename between 'A' and 'D';
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| CLARK |
| ADAMS |
+-------+
4 rows in set (0.00 sec)
用在字符串方面是左闭右开 【A - D)
1.3、条件查询 is null 与 is not null
找出那些人没有津贴?
在数据当中,Null不是一个值,它代表什么都没有,为空
空不是一个值,不能用等号衡量。
必须使用 is null 或者 is not null
select ename,sal,comm from emp where comm is null or comm = 0;
mysql> select ename,sal,comm from emp where comm is null or comm = 0;
+--------+---------+------+
| ename | sal | comm |
+--------+---------+------+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+
11 rows in set (0.00 sec)
and = && 并且
or = || 或者
找出工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
mysql> select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
and,or并用(and的优先级更高,会优先配对)
找出薪资大于3000的并且部门标号是20或30的员工。
select ename,sal,deptno from emp where (deptno = 20 or deptno =30) and sal > 1000;
mysql> select ename,sal,deptno from emp where (deptno = 20 or deptno =30) and sal > 1000;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
+--------+---------+--------+
9 rows in set (0.00 sec)
in 等同于or:找出工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
select ename,job from emp where job in (‘SALESMAN’, ‘MANAGER’);
in后面的值是具体的值,不是区间
not in:不在这几个值当中
select ename,job from emp where job not in (‘SALESMAN’, ‘MANAGER’);
mysql> select ename,job from emp where job not in ('SALESMAN', 'MANAGER');
+--------+-----------+
| ename | job |
+--------+-----------+
| SMITH | CLERK |
| SCOTT | ANALYST |
| KING | PRESIDENT |
| ADAMS | CLERK |
| JAMES | CLERK |
| FORD | ANALYST |
| MILLER | CLERK |
+--------+-----------+
7 rows in set (0.00 sec)
1.4、like 模糊查询
模糊查询中有两个特殊符号,一个是%,一个是_
%代表任意多个字符,_代表一个字符。
/*找出名字当中含O的?*/
mysql> select ename , sal from emp where ename like '%O%';
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
+-------+---------+
3 rows in set (0.00 sec)
/*找出名字当中第二个字母是A的?*/
mysql> select ename , sal from emp where ename like '_A%';
+--------+---------+
| ename | sal |
+--------+---------+
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| JAMES | 950.00 |
+--------+---------+
3 rows in set (0.00 sec)
\ 斜杠具有转义作用,\ _ 等于_.
查找名字中有下划线的?
select ename , sal from emp where ename like ‘%\ _%’;