文章目录
第八章 使用各种条件进行提取
1.设计列的显示内容并执行select
1.准备表 tb
我自己是使用在记事本上编辑信息导入到数据库的，建议大家使用插入语句
mysql> insert into tb values('A103',102,4);
用记事本插入的语句方法是
mysql> load data infile 'C:/ch3/tb.csv' into table tb fields terminated by ',';
让我们来看一下是不是已经将数据输入到库中
mysql> select * from tb;
+-------+-------+-------+
| empid | sales | mouth |
+-------+-------+-------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
+-------+-------+-------+
10 rows in set (0.02 sec)
显然，我们已经数据导入到tb表中了
2.改变列的显示顺序
- 利用select 语句可以按照自己的意愿去改变表的
显示顺序，但是不会改变表的实际顺序.
他的语句是
mysql> select sales,empid from tb;
//以下就是显示结果，我们发现empid和sales就交换了位置
+-------+-------+
| sales | empid |
+-------+-------+
| 101 | A103 |
| 54 | A102 |
| 181 | A104 |
| 184 | A101 |
| 17 | A103 |
| 300 | A101 |
| 205 | A102 |
| 93 | A104 |
| 12 | A103 |
| 87 | A107 |
| 102 | A103 |
+-------+-------+
11 rows in set (0.03 sec)
这样我们就可以看到他们就交换了位置，你可以试试看看原表有没有发现变化。
-
使用别名
使用别名就是给一个表或者表中的列再起一个名字，注意不是改变原来列的名字，只是在你显示的时候会出现的一个名字，你也可以理解为给某个人起小名。
1.指定别名的语法
select 列名 as 别名 from 表名；
2.实例
mysql> select empid as 员工号,sales as 销售额 from tb;
+--------+--------+
| 员工号 | 销售额 |
+--------+--------+
| A103 | 101 |
| A102 | 54 |
| A104 | 181 |
| A101 | 184 |
| A103 | 17 |
| A101 | 300 |
| A102 | 205 |
| A104 | 93 |
| A103 | 12 |
| A107 | 87 |
| A103 | 102 |
+--------+--------+
11 rows in set (0.02 sec)
这样我们注意到emid 和sales已经变成了 员工号 和 销售额
2.计算列值或处理字符串之后显示列
1.使用列值进行计算并显示
- 算术运算符
| 运算符 | 使用示例 | 含义 |
|---|---|---|
| + | a+b | a加b |
| - | a-b | a减b |
| * | a*b | a乘以b |
| / | a/b | a除以b |
| DIV | a DIV b | a 除以 b 取整 |
| %，MOD | a %b | a除以b取余 |
-
实例
下边的操作是给tb表的销售额乘以1000的操作。
mysql> select sales*1000 as 销售额 from tb;
+--------+
| 销售额 |
+--------+
| 101000 |
| 54000 |
| 181000 |
| 184000 |
| 17000 |
| 300000 |
| 205000 |
| 93000 |
| 12000 |
| 87000 |
| 102000 |
+--------+
11 rows in set (0.05 sec)
-
使用函数进行计算
1.显示总和的函数sum的使用
mysql> select SUM(sales) from tb ;
+------------+
| SUM(sales) |
+------------+
| 1234 |
+------------+
1 row in set (0.00 sec)
2.显示个数
mysql> select count(sales) from tb;
+--------------+
| count(sales) |
+--------------+
| 10 |
+--------------+
1 row in set (0.02 sec)
-
用于显示各种信息的函数
1.显示PI()函数的值
mysql> select PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.03 sec)
2.显示MySQL服务器的版本
mysql> select Version();
+-----------+
| Version() |
+-----------+
| 5.7.24 |
+-----------+
1 row in set (0.02 sec)
3.显示当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
4.显示当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)
5.显示由参数指定的字符的字符编码
mysql> select charset('这个字符');
+---------------------+
| charset('这个字符') |
+---------------------+
| gbk |
+---------------------+
1 row in set (0.02 sec)
-
连接字符串
下边我们来在一个显示的数据后边，在显示的时候后边添加某些字符，但是实际不会添加到数据库中。
mysql> select concat(empid,name,'先生') from tb1;
+---------------------------+
| concat(empid,name,'先生') |
+---------------------------+
| A101佐藤先生 |
| A102高桥先生 |
| A103中川先生 |
| A104渡边先生 |
| A105西泽先生 |
+---------------------------+
5 rows in set (0.05 sec)
我们看到实际的数据库没有改变
mysql> select* from tb1;
+-------+------+------+
| empid | name | age |
+-------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高桥 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A105 | 西泽 | 35 |
+-------+------+------+
5 rows in set (0.00 sec)
2.字符串操作中常用的函数
-
1.从右取出：Right函数
下边的命令用于显示empid最右边的2个字符
mysql> select right(empid,2) from tb1;
+----------------+
| right(empid,2) |
+----------------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
+----------------+
5 rows in set (0.02 sec)
-
2.从左取出：left 函数
下面的命令用于显示列empid 最右边的2个字符
mysql> select left(empid,2) from tb1;
+---------------+
| left(empid,2) |
+---------------+
| A1 |
| A1 |
| A1 |
| A1 |
| A1 |
+---------------+
5 rows in set (0.00 sec)
-
3.从第X个字符开始截取a个字符：substring函数
下面的命令用于从列empid 的第2个字符开始连续显示3个字符
mysql> select substring(empid,2,3) from tb1;
+----------------------+
| substring(empid,2,3) |
+----------------------+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+----------------------+
5 rows in set (0.02 sec)
注意：是从第二个开始算起，也就是说，跳过第一个，从第二个开始显示。
-
4.重复显示：repeat函数
下面的命令用于重复显示字符‘&’，其重复次数为列age的值
mysql> select repeat('&',age) from tb1;
+------------------------------------------+
| repeat('&',age) |
+------------------------------------------+
| &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
| &&&&&&&&&&&&&&&&&&&&&&&&&&&& |
| &&&&&&&&&&&&&&&&&&&& |
| &&&&&&&&&&&&&&&&&&&&&&& |
| &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+------------------------------------------+
5 rows in set (0.00 sec)
-
5.翻转显示：reverse 函数
下面把name中的字符串倒着显示出来
mysql> select reverse(name) from tb1;
+---------------+
| reverse(name) |
+---------------+
| 藤佐 |
| 桥高 |
| 川中 |
| 边渡 |
| 泽西 |
+---------------+
5 rows in set (0.02 sec)
日期和时间函数
下边我们首先来创建一个时间的表
mysql> create table t_now (a int auto_increment primary key ,b datetime);
Query OK, 0 rows affected (0.20 sec)
下边我们使用NOW()函数来向t_now 表中插入现在的时间
mysql> insert into t_now (b) values (NOW());
Query OK, 1 row affected (0.12 sec)
请把上述插入操作进行4次。
现在我们来查看一下
mysql> select * from t_now;
+---+---------------------+
| a | b |
+---+---------------------+
| 1 | 2021-04-08 10:04:51 |
| 2 | 2021-04-08 10:04:55 |
| 3 | 2021-04-08 10:04:57 |
| 4 | 2021-04-08 10:04:59 |
+---+---------------------+
3.设置条件进行显示
1.确定记录数并记录
1.限制显示的记录数
select 列名 from 表名 limit 显示的记录数
2.实例
mysql> select * from tb limit 3;
+-------+-------+-------+
| empid | sales | mouth |
+-------+-------+-------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
+-------+-------+-------+
3 rows in set (0.00 sec)
很显然我们只看到了tb中的三条数据
2.使用WHERE提取记录
1.仅显示符合条件的数据的where语句
select 列名 from 表名 where 条件；
2.实例
mysql> select * from tb where sales >=100;
+-------+-------+-------+
| empid | sales | mouth |
+-------+-------+-------+
| A103 | 101 | 4 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
+-------+-------+-------+
5 rows in set (0.02 sec)
我们看到上边显示的数据，是salece>=100的一下信息。
3.比较运算符
1.常用的比较运算符
| 比较运算符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于 |
| O IN X | O 在 X 列表中 |
| O NOT IN X | O 不在 X的列表中 |
| O between X and XX | O 在 X 到 XX 之间 |
| O not between X and XX | O 不在 X 到 XX 之间 |
2.一些实例
（1）显示列sales的值小于50的值
mysql> select * from tb where sales<50;
+-------+-------+-------+
| empid | sales | mouth |
+-------+-------+-------+
| A103 | 17 | 5 |
| A103 | 12 | 6 |
+-------+-------+-------+
2 rows in set (0.00 sec)
(2)列mouth的值不等于4
mysql> select * from tb where month <>4;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A102 | 54 | 5 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
+-------+-------+-------+
7 rows in set (0.00 sec)
(3)列sales的值在50到100之间的数据
mysql> select * from tb where sales between 50 and 100;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A102 | 54 | 5 |
| A104 | 93 | 5 |
| A107 | 87 | 6 |
+-------+-------+-------+
3 rows in set (0.00 sec)
(4)列sales 的值不在50到200之间
mysql> select * from tb where sales not between 50 and 200;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A103 | 12 | 6 |
+-------+-------+-------+
4 rows in set (0.00 sec)
(5)month 的值等于5或者6
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A102 | 54 | 5 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
+-------+-------+-------+
7 rows in set (0.00 sec)
4.使用字符串作为条件
先来看一个实例吧
mysql> select * from tb where empid = 'A101';
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 184 | 4 |
| A101 | 300 | 5 |
+-------+-------+-------+
2 rows in set (0.00 sec)
上边就是用字符串作为条件查询的一个实例
1.like：模糊查询
就是说我们只是模糊的记得一个数据的某些信息时的查询，比如说，你只记得某个人的人名的某个字或者姓氏的时候，用的一种模糊查询。
- 字符串的通配符及其使用示例
| 通配符 | 含义 |
|---|---|
| % | 任意字符串 |
| _ | 任意一个字符 |
-
示例
(1)显示它把最后一位为1，前边为任意字符的所有项。
mysql> select * from tb where empid like '%1';
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 184 | 4 |
| A101 | 300 | 5 |
+-------+-------+-------+
(2)显示中间为“川”的人的姓名
mysql> select * from tb1 where name like '%川%';
+-------+------+------+
| empid | name | age |
+-------+------+------+
| A103 | 中川 | 20 |
+-------+------+------+
1 row in set (0.00 sec)
- 提取不包含指定字符串的记录
用 not like 命令提取不含某个字符串的记录
mysql> select * from tb1 where name not like '佐%';
+-------+------+------+
| empid | name | age |
+-------+------+------+
| A102 | 高桥 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A105 | 西泽 | 35 |
+-------+------+------+
4 rows in set (0.02 sec)
我们发现tb1中的“佐藤”的数据是没有被显示的
5.使用NULL作为条件
1.当列值为NULL时
提取列值为NULL的记录时需要只用IS NULL语句
下边的命令是用于提取tb1H中列age为NULL的记录
mysql> select * from tb1H where age is NULL;
Empty set (0.03 sec)
它显示，在tb1H表中是存在age为NULL的值
2.当列值非NULL时
** 提取列值不是NULL的记录时需要使用IS NOT NULL**
下面的命令用于提取表tbH1中列age不为NULL的记录
mysql> select * from tb1H where age is not null;
+-------+------+------+
| empid | name | age |
+-------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高桥 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A105 | 西泽 | 35 |
+-------+------+------+
5 rows in set (0.00 sec)
这样我们就将age不为空的数据给提取出来了。
4.指定多个条件进行选择
1.使用AND和OR
1.使用AND可以将多个条件给连接起来，他表示的是条件的并集
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> select * from tb where empid like '%1';
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 184 | 4 |
| A101 | 300 | 5 |
+-------+-------+-------+
2 rows in set (0.00 sec)
显然他会有两个数据，month是4和5的数据，对于上边第一个命令month限制为4，所以就显示了一条数据。
2.使用OR将多个条件连起来，他表示的是条件的或集
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)
显然它将sales<50 和sales >200的值给显示出来了，这和 not between and 有点相似
mysql> select * from tb where sales not between 50 and 200;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A103 | 12 | 6 |
+-------+-------+-------+
4 rows in set (0.00 sec)
显然他们显示的数据是一样的
2.使用多个AND和OR
1.当 AND和OR混合使用时，会优先处理AND,如需要先处理OR，应该讲将OR写在AND之前并用（）括号括起来。
(1)empid 是‘A101’且”month =4“ ，或者”sales大于等于200“的实例
mysql> select * from tb where empid = 'A101' 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)
注意OR 这个条件
(2)“sales”大于等于2000，或者empid 为’A101’且“month”是4的实例
mysql> select * from tb where sales>=200 or empid = 'A101' and month=4;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 184 | 4 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
+-------+-------+-------+
3 rows in set (0.00 sec)
mysql>
select * from tb where (sales>=200 or empid = 'A101') and month=4;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 184 | 4 |
+-------+-------+-------+
1 row in set (0.00 sec)
为什么，同样的语句，加完（）之后就结果不一样了呢 ，现在， 我们来分析一下上边的条件，
我们先看看（)里的条件显示出来会是什么呢
mysql> select * from tb where (sales>=200 or empid = 'A101');
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 184 | 4 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
+-------+-------+-------+
3 rows in set (0.00 sec)
这就是（）里条件的显示结果，我们看到他出现了三条结果，现在我们看看（）后边AND的条件，他说month=4，所以，他在上边的三个数据中选择了month=4的数据显示了出来，是不是很神奇。
使用CASE WHEN(case when)语句
1.根据条件改变并显示值得语法
case
when 条件1 then 显示值1;
when 条件2 then 显示值2;
when 条件3 then 显示值3;
else 不满足所有条件的值
end
2.当销售额(sales)大于等于100时为“高”，大于等于50小于100时为“中等”，否则为“低”
mysql> select empid,sales,
-> case
-> when sales >=100 then '高'
-> when sales >=50 then '中等'
-> else '低'
-> end as 评价
-> from tb;
+-------+-------+------+
| empid | sales | 评价 |
+-------+-------+------+
| A103 | 101 | 高 |
| A102 | 54 | 中等 |
| A104 | 181 | 高 |
| A101 | 184 | 高 |
| A103 | 17 | 低 |
| A101 | 300 | 高 |
| A102 | 205 | 高 |
| A104 | 93 | 中等 |
| A103 | 12 | 低 |
| A107 | 87 | 中等 |
+-------+-------+------+
10 rows in set (0.00 sec)
发现这样就显示出来了。
5.排序
1.按升序、降序排列并显示
1.按升序显示的语法
select 列名 from 表名 order by 作为键的列；
2.实例
下边我们以sales从小到大（即升序）的顺序来排列
mysql> select * from tb order by sales;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103 | 12 | 6 |
| A103 | 17 | 5 |
| A102 | 54 | 5 |
| A107 | 87 | 6 |
| A104 | 93 | 5 |
| A103 | 101 | 4 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A102 | 205 | 6 |
| A101 | 300 | 5 |
+-------+-------+-------+
10 rows in set (0.00 sec)
3.按照降序排列的时候，需要加上DESC
mysql> select * from tb order by sales desc limit 5;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A101 | 184 | 4 |
| A104 | 181 | 4 |
| A103 | 101 | 4 |
+-------+-------+-------+
5 rows in set (0.00 sec)
2.指定记录的显示范围
1.指定范围并显示的语法
select 列名 from 表名 limit 显示的记录数 offset 开始显示的记录数
2.实例
mysql> select * from tb order by sales desc limit 2 offset 3;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A104 | 181 | 4 |
| A103 | 101 | 4 |
+-------+-------+-------+
2 rows in set (0.00 sec).
我们可以看到它跳过了3个数据在第4个数据开始显示，并且显示了两个。
6.分组显示
1.分组显示
注意这里可能会出现错误，你需要先设置一下，执行下面代码
SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
你会发现他会出现这个语句，我们现在只需要把他重新设置一下就行
执行下边代码，你可以多试一试
mysql> set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
设置完我们就可以开始了
1.分组显示的语法
select 列名 from 表名 group by 用于分组的列名；
2.实例
mysql> select * from tb group by empid;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101 | 184 | 4 |
| A102 | 54 | 5 |
| A103 | 101 | 4 |
| A104 | 181 | 4 |
| A107 | 87 | 6 |
+-------+-------+-------+
5 rows in set (0.00 sec)
很显然我们empid做了分组，这样是不容易看见的，我们执行下面操作，直观的感受一下
mysql> select empid,count(*) as 个数
-> from tb
-> group by empid;
+-------+------+
| empid | 个数 |
+-------+------+
| A101 | 2 |
| A102 | 2 |
| A103 | 3 |
| A104 | 2 |
| A107 | 1 |
+-------+------+
5 rows in set (0.02 sec)
3.显示各组的总和以及平均值
显示总和
mysql> select
-> empid,sum(sales) as 总和
-> from tb
-> group by empid;
+-------+------+
| empid | 总和 |
+-------+------+
| A101 | 484 |
| A102 | 259 |
| A103 | 130 |
| 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 | 129.5000 |
| A103 | 43.3333 |
| A104 | 137.0000 |
| A107 | 87.0000 |
+-------+----------+
5 rows in set (0.02 sec)
2.分组显示的条件
1.分组并设置记录提取条件
select 统计列 from 表名 group by 分组列 having 条件；
2.实例
mysql> select
-> empid ,sum(sales)
-> from tb
-> group by empid
-> having sum(sales)>=200;
+-------+------------+
| empid | sum(sales) |
+-------+------------+
| A101 | 484 |
| A102 | 259 |
| A104 | 274 |
+-------+------------+
3 rows in set (0.02 sec)
3.提取记录后分组
mysql> select empid,AVG(sales)
-> from tb
-> where sales>=50
-> group by empid;
+-------+------------+
| empid | AVG(sales) |
+-------+------------+
| A101 | 242.0000 |
| A102 | 129.5000 |
| A103 | 101.0000 |
| A104 | 137.0000 |
| A107 | 87.0000 |
+-------+------------+
5 rows in set (0.00 sec)
注意：HAVING只能用于GROUP语句，不能用于别的语句。
4.分组后排序
mysql> select
-> empid ,AVG(sales)
-> from tb
-> group by empid
-> order by AVG(sales)
-> desc;
+-------+------------+
| empid | AVG(sales) |
+-------+------------+
| A101 | 242.0000 |
| A104 | 137.0000 |
| A102 | 129.5000 |
| A107 | 87.0000 |
| A103 | 43.3333 |
+-------+------------+
5 rows in set (0.00 sec)
我们可以看到他在分组之后按平均值大小降序排列了。