表记录的查询（一）

表记录的查询（一）

表记录的查询-基础用法

select [distinct] *|field1,field2,... from db_name;

distinct用来剔除重复行， *表示查找所有列，也可以指定某一列、某几列

• 查询表中所有员工信息select * from em;

+----+--------+--------+------------+-------+-------+--------+
| id | name   | gender | birthday   | DP    | bouns | salary |
+----+--------+--------+------------+-------+-------+--------+
|  1 | Tim    |      1 | 1988-02-02 | NULL  |   400 |  15000 |
|  2 | Linzy  |      1 | 1990-12-11 | 2345  |   200 |  10000 |
|  3 | Jessic |      1 | 1991-12-20 | 2345  |   300 |  13000 |
|  4 | Miz    |      1 | 1998-04-20 | sales |   400 |  14000 |
|  5 | Sasa   |      1 | 1996-04-20 | sales |  3000 |   5000 |
|  6 | Tim    |      1 | NULL       | IT    |   400 |  15000 |
+----+--------+--------+------------+-------+-------+--------+

• 查询表中所有员工的姓名和对应的所属部门select name,dp from em;

+--------+-------+
| name   | dp    |
+--------+-------+
| Tim    | NULL  |
| Linzy  | 2345  |
| Jessic | 2345  |
| Miz    | sales |
| Sasa   | sales |
| Tim    | IT    |
+--------+-------+

• 过滤上一条查询结果中的重复元素 select distinct name from em;

+--------+
| name   |
+--------+
| Tim    |
| Linzy  |
| Jessic |
| Miz    |
| Sasa   |
+--------+

表记录的查询-表达式、别名的使用

• 所有记录的bouns增加200 select name,bouns+200 from em;

+--------+-----------+
| name   | bouns+200 |
+--------+-----------+
| Tim    |       600 |
| Linzy  |       400 |
| Jessic |       500 |
| Miz    |       600 |
| Sasa   |      3200 |
| Tim    |       600 |
| John   |       550 |
+--------+-----------+

• 统计员工Miz薪资总和 select name,bouns+salary from em where name="Miz";

+------+--------------+
| name | bouns+salary |
+------+--------------+
| Miz  |        14400 |
+------+--------------+

• 使用别名表示员工薪资总和 select name, bouns+salary as income from em where name="Sasa";

+------+--------+
| name | income |
+------+--------+
| Sasa |   8000 |
+------+--------+

使用where子句，进行过滤查询

• 逻辑运算：">" “<” “=” “!=” “>=” “<=”
• 值在80到100之间：“between 80 and 100”
• 值是10或20或30: in(10,20,30)
• like ‘Je%’ 或者 like ‘Je_’ **%代表匹配任意多个字符，_**代表匹配一个字符

• 查询薪资总和超过12000的员工 select name, bouns+salary as income from em where bouns+salary>12000;

+--------+--------+
| name   | income |
+--------+--------+
| Tim    |  15400 |
| Jessic |  13300 |
| Miz    |  14400 |
| Tim    |  15400 |
+--------+--------+

• 查询一个未被分配部门的员工 select name from em where dp is null and birthday is null;

+----+------+--------+----------+------+-------+--------+
| id | name | gender | birthday | DP   | bouns | salary |
+----+------+--------+----------+------+-------+--------+
|  8 | John |      1 | NULL     | NULL |   350 |   8000 |
+----+------+--------+----------+------+-------+--------+

Order by 指定排序的列

select *|field1,field2,field3... from tab_name order by field [Asc|Desc]

• 按照bouns的升序排列表记录：

select * from em order by bouns;

+----+--------+--------+------------+-------+-------+--------+
| id | name   | gender | birthday   | DP    | bouns | salary |
+----+--------+--------+------------+-------+-------+--------+
|  2 | Linzy  |      1 | 1990-12-11 | 2345  |   200 |  10000 |
|  3 | Jessic |      1 | 1991-12-20 | 2345  |   300 |  13000 |
|  8 | John   |      1 | NULL       | NULL  |   350 |   8000 |
|  1 | Tim    |      1 | 1988-02-02 | NULL  |   400 |  15000 |
|  4 | Miz    |      1 | 1998-04-20 | sales |   400 |  14000 |
|  6 | Tim    |      1 | NULL       | IT    |   400 |  15000 |
|  5 | Sasa   |      1 | 1996-04-20 | sales |  3000 |   5000 |
+----+--------+--------+------------+-------+-------+--------+

• 对员工按照收入总和降序排列：

select name,(ifnull(bouns,0)+ifnull(salary,0)) as income from em order by income desc;

+--------+--------+
| name   | income |
+--------+--------+
| Tim    |  15400 |
| Tim    |  15400 |
| Miz    |  14400 |
| Jessic |  13300 |
| Linzy  |  10200 |
| John   |   8350 |
| Sasa   |   8000 |
+--------+--------+