mysql-210711-02
简单查询语句(DQL)
语法格式:
select 字段1,字段2,字段3,...... from 表名;
不区分大小写
以 ; 号结尾
mysql> select emp.ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
+--------+
mysql> select ename,empno from emp;
+--------+-------+
| ename | empno |
+--------+-------+
| SMITH | 7369 |
| ALLEN | 7499 |
| WARD | 7521 |
| JONES | 7566 |
+--------+-------+
字段可以参与数学运算
// 查询年薪(字段可以参与数学运算)
mysql> select ename , sal * 12 from emp;
+--------+----------+
| ename | sal*12 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
+--------+----------+
给查询结果的列重新命名
// 给查询结果的列重新命名
mysql> select ename,sal*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
+--------+----------+
// 别名可以出现中文,必须 ' ' 号
// MySQL中可以使用 ' ' 也可以使用 " "
// Oracle 中只能为 ' '
mysql> select ename,sal*12 as '年薪' from emp;
+--------+----------+
| ename | 年薪 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
+--------+----------+
// 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 |
+-------+--------+----------+
条件查询
语法格式
select 字段1,字段2.....
from 表名
where 条件;
案例1(等于)
// 查询工资为5000的员工信息
mysql> select ename , empno from emp where sal=5000;
+-------+-------+
| ename | empno |
+-------+-------+
| KING | 7839 |
+-------+-------+
// 查询SMITH的工资
mysql> select sal from emp where ename='smith';
+--------+
| sal |
+--------+
| 800.00 |
+--------+
案例2(大于)
// 查询工资大于2000的员工信息
mysql> select empno,ename,sal from emp where sal > 2000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
案例3(不等于)
// 查询工资不等于3000的员工信息
// <> 就是 不等于
// != 也是 不等于
mysql> 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 |
+-------+--------+---------+
案例4(between…and…)
// 查询工资在1100~3000之间的员工信息
// 两种方法
// between...and...
// 是闭区间[1100,3000],小数字在左,大数字在右
mysql> select empno,ename,sal from emp where sal>=1100 and sal<=3000;
mysql> select empno,ename,sal from emp where sal between 1100 and 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 |
+-------+--------+---------+
// between...and... 还可以用在字符方面
// 规则是左闭右开
// [a,e)
mysql> select empno,ename,sal from emp where ename between 'a' and 'e';
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7499 | ALLEN | 1600.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7876 | ADAMS | 1100.00 |
+-------+-------+---------+
4 rows in set (0.00 sec)
案例5(is null 和 is not null)
// 查询没有津贴的员工信息,即津贴为NULL
// 在数据库中,NULL不是一个值,代表什么也没有,说明字段为空
// 空不是一个值,不能用等号衡量
// 必须使用is null 或者 is not null
mysql> select empno,ename,comm,sal from emp where comm is null or comm = 0;
+-------+--------+------+---------+
| empno | ename | comm | sal |
+-------+--------+------+---------+
| 7369 | SMITH | NULL | 800.00 |
| 7566 | JONES | NULL | 2975.00 |
| 7698 | BLAKE | NULL | 2850.00 |
| 7782 | CLARK | NULL | 2450.00 |
+-------+--------+------+---------+
案例6(or)
// 查询岗位是 salesman 和 menager 的员工
mysql> select empno,ename,job,sal from emp where job='salesman' or job='manager';
+-------+--------+----------+---------+
| empno | ename | job | sal |
+-------+--------+----------+---------+
| 7499 | ALLEN | SALESMAN | 1600.00 |
| 7521 | WARD | SALESMAN | 1250.00 |
| 7566 | JONES | MANAGER | 2975.00 |
| 7654 | MARTIN | SALESMAN | 1250.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
| 7782 | CLARK | MANAGER | 2450.00 |
| 7844 | TURNER | SALESMAN | 1500.00 |
+-------+--------+----------+---------+
// 查询 薪资大于1000 并且 部门编号为20或30 的员工
// 运算符优先级不确定的情况下,加括号(),括号的优先级一定高
mysql> select empno,ename,deptno,sal from emp where sal>1000 and (deptno=20 or deptno=30);
+-------+--------+--------+---------+
| empno | ename | deptno | sal |
+-------+--------+--------+---------+
| 7499 | ALLEN | 30 | 1600.00 |
| 7521 | WARD | 30 | 1250.00 |
| 7566 | JONES | 20 | 2975.00 |
| 7654 | MARTIN | 30 | 1250.00 |
| 7698 | BLAKE | 30 | 2850.00 |
| 7788 | SCOTT | 20 | 3000.00 |
| 7844 | TURNER | 30 | 1500.00 |
| 7876 | ADAMS | 20 | 1100.00 |
| 7902 | FORD | 20 | 3000.00 |
+-------+--------+--------+---------+
案例7(in)
// in等用于or
// 查询岗位是 salesman 和 menager 的员工
mysql> select empno,ename,job,sal from emp where job='salesman' or job='manager';
mysql> select empno,ename,job,sal from emp where job in ('salesman','manager');
+-------+--------+----------+---------+
| empno | ename | job | sal |
+-------+--------+----------+---------+
| 7499 | ALLEN | SALESMAN | 1600.00 |
| 7521 | WARD | SALESMAN | 1250.00 |
| 7566 | JONES | MANAGER | 2975.00 |
| 7654 | MARTIN | SALESMAN | 1250.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
| 7782 | CLARK | MANAGER | 2450.00 |
| 7844 | TURNER | SALESMAN | 1500.00 |
案例8(like)
// 查询名字中含有 o 的员工
// like 模糊查询
// % 任意多个字符
// _ 任意一个字符
mysql> select empno,ename,job from emp where ename like '%o%';
+-------+-------+---------+
| empno | ename | job |
+-------+-------+---------+
| 7566 | JONES | MANAGER |
| 7788 | SCOTT | ANALYST |
| 7902 | FORD | ANALYST |
+-------+-------+---------+
// 查询第二个字母为 a 的员工
mysql> select empno,ename,job from emp where ename like '_a%';
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7521 | WARD | SALESMAN |
| 7654 | MARTIN | SALESMAN |
| 7900 | JAMES | CLERK |
+-------+--------+----------+
// 查询名字中含有 _ 的员工
// 需要转义
mysql> select empno,ename,job from emp where ename like '%\_%';
运算符
运算符 | 说明 |
---|
= | 等于 |
<> 或 != | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between…and… | 两个值之间,等同于>= and <= |
is null | 为空(is not null 不为空) |
and | 并且 |
or | 或者 |
in | 包含,相当于多个or (not in 为不在这个范围内) |
not | not可以取非,主要用在 is 或 in 中 |
like | like称为模糊查询,支持 % 或 下划线 匹配 |
% 匹配任意多个字符
_ 匹配任意一个字符