第三部分——熟练使用mysql - 第八章——使用各种条件提取 - 《mysql基础教程》

8.1
- 8.1.3 使用别名
8.2计算列值或处理字符串之后显示列
8.3设置条件进行显示
8.4指定多个条件进行选择
8.5排序
- 8.5.1按升序排序并显示
- 8.5.3指定记录的显示范围
8.6分组显示
8.7设置条件分组显示

8.1

8.1.3 使用别名

可以在查询某一列的数据时给列加上名称<br />  **语法：**

select 列名 as 别名 from 表名

示列：
查询tb表时给empid列加上员工号，sales加上销售额

mysql> select empid as '员工号', sales '销售额' from tb;
+--------+--------+
| 员工号 | 销售额 |
+--------+--------+
| A101   |    300 |
| A102   |    205 |
| A103   |     17 |
| A104   |    181 |
+--------+--------+
4 rows in set (0.00 sec)

mysql>

8.2计算列值或处理字符串之后显示列

8.2.1使用列值计算并显示

    我们可以使用列中的数据自由的进行四则运算。<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/12862216/1622452321074-87c7d03b-23ac-48f9-909c-2f807e85c48e.png#clientId=ubf4fcee4-8467-4&from=paste&height=278&id=uc1d512ee&margin=%5Bobject%20Object%5D&name=image.png&originHeight=370&originWidth=802&originalType=binary&size=274723&status=done&style=none&taskId=u4f80a38b-faa0-40d7-9b73-b15e3acd71f&width=602)<br />** 示列**：tb表sales列*1000显示，并添加别名

mysql> select sales*1000 '销售额' from tb;
+--------+
| 销售额 |
+--------+
| 300000 |
| 205000 |
|  17000 |
| 181000 |
+--------+
4 rows in set (0.00 sec)

mysql>

8.2.2使用函数进行计算

   使用函数计算，在sql中（）存放的大多是列名<br />    **示列一**：显示表tb的列sales的平均值<br />

mysql> select avg(sales) from tb;
+------------+
| avg(sales) |
+------------+
|   175.7500 |
+------------+
1 row in set (0.00 sec)

mysql>

示列二：显示表tb的列sales的总和

mysql> select sum(sales) from tb;
+------------+
| sum(sales) |
+------------+
|        703 |
+------------+
1 row in set (0.00 sec)

mysql>

示列三：统计tb表中的sales列中记录的个数

mysql> select count(sales) from tb;
+--------------+
| count(sales) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

mysql>

8.2.3用于显示各种信息的函数

①：PI（）
②：显示mysql服务器版本：select version();
③：显示当前使用的数据库:show databse();
④：显示当前用户：show uers();
⑤：显示由参数指定的字符的字符编码show charset(‘字符’)；

8.2.4：连接字符串

mysql中还有很多用于处理字符串的函数，可以使用concat函数。concat中指定的字符串列需要”,”;
示列：连接tb1表中的列empid、name 、及先生

mysql> select concat(empid,name,' 先生')from tb1;
+----------------------------+
| concat(empid,name,' 先生') |
+----------------------------+
| A101佐藤 先生              |
| A102高桥 先生              |
| A103中川 先生              |
| A104渡边 先生              |
| A105西泽 先生              |
| N110未输入名字 先生        |
+----------------------------+
6 rows in set (0.00 sec)

mysql>

  concat也可以连接三个字符以上的的字符串

8.2.5字符串中常用的函数

8.2.6日期和时间函数

now()返回当前日期和时间

8.3设置条件进行显示

8.3.1limit:

可以使用limit来限定要显示的记录数<br />**   语法：**

select 列名 from 表名 limit 显示的记录数

示列：只显示tb表3条记录数

mysql> select * from tb limit 3;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   300 |     6 |
| A102  |   205 |     6 |
| A103  |    17 |     5 |
+-------+-------+-------+
3 rows in set (0.00 sec)

mysql>

8.3.2使用where来提前记录

1.where条件查询
可以使用where后跟条件语句作为查找或更新删除的前提
2.like模糊查询
1. like配合where的使用可以进行模糊条件的查询,同常配合%和
%:代表一组字符
;任意一个字符
列如：
%0；代表任意字符，只要最后面的是0；
0:一个字符，后面是0；
A%：代表任意字符只要最前的是A
A:代表前面是A的一个字符
示列：查询tb1中name列中只要有川子的记录

mysql> select * from tb1 where name like '%川%';
+-------+------+------+
| empid | name | age  |
+-------+------+------+
| A103  | 中川 |   20 |
+-------+------+------+
1 row in set (0.00 sec)

2.提前不包含指定字符串的记录
提取不包含指定字符串的记录时需要使用NOT like命令。
示列：提前tb1name列中的不以佐开头的

mysql> select * from tb1 where name not like '佐%';
+-------+------------+------+
| empid | name       | age  |
+-------+------------+------+
| A102  | 高桥       |   28 |
| A103  | 中川       |   20 |
| A104  | 渡边       |   23 |
| A105  | 西泽       |   35 |
| N110  | 未输入名字 |   25 |
+-------+------------+------+
5 rows in set (0.00 sec)

mysql>0

8.3.3使用null作为条件

 1.提取列值为null的记录是需要在where后使用is null;

mysql> select * from tbe1;
+-------+-------+------+------+
| empid | birth | name | age  |
+-------+-------+------+------+
| A101  | NULL  | 佐藤 |   40 |
| A102  | NULL  | 高桥 |   28 |
| A103  | NULL  | 中川 |   20 |
| A104  | NULL  | 渡边 |   23 |
| A105  | NULL  | 西泽 |   35 |
+-------+-------+------+------+
5 rows in set (0.01 sec)
mysql> select * from tbe1 where birth is null;
+-------+-------+------+------+
| empid | birth | name | age  |
+-------+-------+------+------+
| A101  | NULL  | 佐藤 |   40 |
| A102  | NULL  | 高桥 |   28 |
| A103  | NULL  | 中川 |   20 |
| A104  | NULL  | 渡边 |   23 |
| A105  | NULL  | 西泽 |   35 |
+-------+-------+------+------+
5 rows in set (0.00 sec)

mysql>

注意：当提前值为null的记录时既是使用where 列名=null也无法提取；

mysql> select * from tbe1 where birth=null;
Empty set (0.00 sec)

2.提前列值不是null的记录需要where后使用is not null;

mysql> select * from tbe1 where birth is not null;
Empty set (0.00 sec)

8.3.4不重复提前

 示列：

mysql> select empid from tb;
+-------+
| empid |
+-------+
| A103  |
| A103  |
| A104  |
| A101  |
| A103  |
| A101  |
| A102  |
| A104  |
| A103  |
| A107  |
+-------+
10 rows in set (0.00 sec)

mysql>

  像上面这样有很多记录重复出现，但是给在要提前的字段前面加上distinct就能取出重复记录

mysql> select distinct empid from tb;
+-------+
| empid |
+-------+
| A103  |
| A104  |
| A101  |
| A102  |
| A107  |
+-------+
5 rows in set (0.00 sec)

8.4指定多个条件进行选择

8.4.1使用and

    使用and 我们可以设置xx和xx的条件<br />    示列：and配合like模糊查询  显示tb表中empid最后为1且month为4；

/*tb 表的全部内容*/
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A103  |   101 |     4 |
| A104  |   181 |     5 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
+-------+-------+-------+

mysql> select * from tb where empid like'%1' and month=4;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   184 |     4 |
+-------+-------+-------+
1 row in set (0.00 sec)

mysql>

8.4.2使用or;

使用or我们可以设置xx或xx的条件
示列：显示tb表中sales大于200小于50的记录

mysql> select * from tb where sales<50 or sales>200;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A103  |    12 |     6 |
+-------+-------+-------+
4 rows in set (0.00 sec)

mysql>

8.4.3使用多个and和or

我们可以设置任意次数的and和or，也可以同时使用and和or
示列：查询tb表中empid=A101;且month=4；后者sales>=200；

mysql> select * from tb where empid like '%1' and month=4 or sales>=200;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   184 |     4 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
+-------+-------+-------+
3 rows in set (0.00 sec)

mysql>

注意：

1.当and和or混合使用是会优先处理and 2.我们可以用（）的形式来让我们想先处理的内容括起来

8.4.4使用case;

![image.png](https://cdn.nlark.com/yuque/0/2021/png/12862216/1622508968674-4d8f5e14-094c-4ed2-944a-0e8a999bf98d.png#clientId=uc9b78727-1bfe-4&from=paste&height=329&id=ud4eea510&margin=%5Bobject%20Object%5D&name=image.png&originHeight=657&originWidth=1570&originalType=binary&size=810025&status=done&style=none&taskId=u873e3a8b-210c-40d0-aade-8435b3ff645&width=785)<br />**  示列一**：tb表中销售额大于等于100时为高，大于等于50小于100时为中等，否则为 低；

mysql> select case
    -> when sales>=100 then '高'
    -> when sales>=50  then '中等'
    -> else '低'
    -> end from tb;
+---------------------------------------------------------------------------+
| case
when sales>=100 then '高'
when sales>=50  then '中等'
else '低'
end |
+---------------------------------------------------------------------------+
| 高                                                                        |
| 高                                                                        |
| 高                                                                        |
| 高                                                                        |
| 低                                                                        |
| 高                                                                        |
| 高                                                                        |
| 中等                                                                      |
| 低                                                                        |
| 中等                                                                      |
+---------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql>

示列二：在示列一的基础上给case end 部分加上别名“评价”,同时，让列sales和empid也显示

mysql> select empid ,sales ,case
    -> when sales>=100 then '高'
    -> when sales>=50 then '中等'
    -> else '低'
    -> end as 评价
    -> from tb;
+-------+-------+------+
| empid | sales | 评价 |
+-------+-------+------+
| A103  |   101 | 高   |
| A103  |   101 | 高   |
| A104  |   181 | 高   |
| A101  |   184 | 高   |
| A103  |    17 | 低   |
| A101  |   300 | 高   |
| A102  |   205 | 高   |
| A104  |    93 | 中等 |
| A103  |    12 | 低   |
| A107  |    87 | 中等 |
+-------+-------+------+
10 rows in set (0.00 sec)

mysql>

8.5排序

8.5.1按升序排序并显示

 直接通过selectc查询的记录顺序是不规则的，我们可以使用order by按指定的列排序并显示<br />   语法：按升序排序<br />

select 列名 from 表名 order by 作为排序键的列

示列：tb表按sales升序显示

mysql> select * from tb order by sales;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |    12 |     6 |
| A103  |    17 |     5 |
| A107  |    87 |     6 |
| A104  |    93 |     5 |
| A103  |   101 |     4 |
| A103  |   101 |     4 |
| A104  |   181 |     5 |
| A101  |   184 |     4 |
| A102  |   205 |     6 |
| A101  |   300 |     5 |
+-------+-------+-------+
10 rows in set (0.00 sec)

mysql>

 注意，在什么都不指定的条件下 order by 会按升序排序，如果想要明确的指定升序，可以在后面加上asc<br />


mysql> select * from tb order by sales asc;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |    12 |     6 |
| A103  |    17 |     5 |
| A107  |    87 |     6 |
| A104  |    93 |     5 |
| A103  |   101 |     4 |
| A103  |   101 |     4 |
| A104  |   181 |     5 |
| A101  |   184 |     4 |
| A102  |   205 |     6 |
| A101  |   300 |     5 |
+-------+-------+-------+
10 rows in set (0.00 sec)

mysql>

8.5.2按降序排序
按降序排序的时候，只需要在后面加上desc。但是要注意。desc和asc 要加在order by 列名的后面
示列：tb表按销售额从高到底排序

mysql> select * from tb order by sales desc;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A101  |   184 |     4 |
| A104  |   181 |     5 |
| A103  |   101 |     4 |
| A103  |   101 |     4 |
| A104  |    93 |     5 |
| A107  |    87 |     6 |
| A103  |    17 |     5 |
| A103  |    12 |     6 |
+-------+-------+-------+
10 rows in set (0.00 sec)

mysql>

  也可以配合limit来只显示销售额前五名<br />

mysql> select * from tb order by sales desc  limit 5 ;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A101  |   184 |     4 |
| A104  |   181 |     5 |
| A103  |   101 |     4 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql>

8.5.3指定记录的显示范围

示列：tb表销售额降序后只显示第4和第五

mysql> select * from tb order by sales desc limit 2 offset 3;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A104  |   181 |     5 |
| A103  |   101 |     4 |
+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from tb order by sales desc limit 2 offset 3;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A104  |   181 |     5 |
| A103  |   101 |     4 |
+-------+-------+-------+
2 rows in set (0.00 sec)

8.6分组显示

8.6.1分组显示

  对于一列中有多条相似的记录，我们可以用group by将这些相同的记录归纳为一个分组，<br />语法:

select 列名 from 表名 group by 用于分组的列名

示列一：将tb表按empid 进行分组

mysql> select * from tb group by empid;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   184 |     4 |
| A102  |   205 |     6 |
| A103  |   101 |     4 |
| A104  |   181 |     5 |
| A107  |    87 |     6 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql>

示列详解：

group by使得没一个empid 都执行select 通过分组，我们知道tb表empid列有五中数据，但是分组之后empid 对应的记录只是随机挑选出来的。所以一定要记住分组之后的处理对象是同一组中的所有记录

8.6.2计算各组的记录数

通过count函数，可以让我们看到每组记录的个数，同时为了更让人们明白我们的分组也可以配合别名使用，也可将分组的列名打印出来，更直观。<br />示列：tb表显示分组后empid一组有多少记录数

mysql> select empid,count(*) as 记录数 from tb group by empid;
+-------+--------+
| empid | 记录数 |
+-------+--------+
| A101  |      2 |
| A102  |      1 |
| A103  |      4 |
| A104  |      2 |
| A107  |      1 |
+-------+--------+
5 rows in set (0.00 sec)

mysql>

8.6.3计算分组后的综合平均值

示列一；计算分组之后每组的销售额

mysql> select empid,sum(sales) as 销售额总和 from tb group by empid;
+-------+------------+
| empid | 销售额总和 |
+-------+------------+
| A101  |        484 |
| A102  |        205 |
| A103  |        231 |
| A104  |        274 |
| A107  |         87 |
+-------+------------+
5 rows in set (0.00 sec)

示列二：计算每组销售额的平均值

mysql> select empid,avg(sales) as 平均值 from tb group by empid;
+-------+----------+
| empid | 平均值   |
+-------+----------+
| A101  | 242.0000 |
| A102  | 205.0000 |
| A103  |  57.7500 |
| A104  | 137.0000 |
| A107  |  87.0000 |
+-------+----------+
5 rows in set (0.00 sec)

mysql>

8.7设置条件分组显示

8.7.1分组后提前记录（Having）

    having用于分组之后的过滤查询。<br />**语法：**

select 统计列 from 表名 group by 分组列 having 条件

示列：显示对分组后的t安装销售额总和大于>200

 FUNCTION empid.sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

8.7.2提取记录后分组

   ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12862216/1622513202699-fcf00d21-f0c0-4de9-b578-14e05e138e4f.png#clientId=uc9b78727-1bfe-4&from=paste&height=123&id=u2ce6b6fe&margin=%5Bobject%20Object%5D&name=image.png&originHeight=245&originWidth=1390&originalType=binary&size=315732&status=done&style=none&taskId=u9cdd1720-87be-40ce-bc55-6926da764c0&width=695)<br />**示列：**提前tb表中销售额大于等于50万的记录并安装empid进行分组，分组之后计算各组的平均值

mysql> select empid,avg(sales) from tb where sales>=50 group by empid;
+-------+------------+
| empid | avg(sales) |
+-------+------------+
| A101  |   242.0000 |
| A102  |   205.0000 |
| A103  |   101.0000 |
| A104  |   137.0000 |
| A107  |    87.0000 |
+-------+------------+
5 rows in set (0.00 sec)

mysql>

8.7.3分组后排序

 对于分组后的结果重写排序，需要同时使用group by 和 order by 但要现象group by 后写 order by<br />**示列**：对tb表格员工的平局销售额（分组后计算销售额的平均值）进行降序

mysql> select empid,avg(sales) as 平局额 from tb group by empid order by avg(sales) desc;
+-------+----------+
| empid | 平局额   |
+-------+----------+
| A101  | 242.0000 |
| A102  | 205.0000 |
| A104  | 137.0000 |
| A107  |  87.0000 |
| A103  |  57.7500 |
+-------+----------+
5 rows in set (0.00 sec)

mysql>

8.7.4分组方法结合使用：

在分组的情况下设置条件有以下两种情况：
a:分组后提前记录：先使用group by 进行分组，然后使用having提前记录
b:提前记录后分组：先用where提前记录，然后使用having进行分组
同时，我们还可以使用order by 对分组后进行排序，同时，这些分组方法可以结合起来处理。
示列：提前tb表中销售额大于50的平均值并按降序排序

mysql> select empid, avg(sales) as 平均值  from tb where sales>=50 group by empid order by avg(sales) desc;
+-------+----------+
| empid | 平均值   |
+-------+----------+
| A101  | 242.0000 |
| A102  | 205.0000 |
| A104  | 137.0000 |
| A103  | 101.0000 |
| A107  |  87.0000 |
+-------+----------+
5 rows in set (0.00 sec)

mysql>