匹配条件

匹配条件

 

基本匹配条件

数值比较

·字段必须是数值类型

 

 

字符比较/匹配空/非空

·字段必须是字符类型

 

 

 

逻辑匹配

多个判断条件时使用

 

 

 

范围匹配/去重显示

匹配范围内的任意一个值即可

 

 

 

匹配条件练习

1）常用的表记录统计函数

查询stu_info表一共有多少条记录（本例中为5条）：

mysql> SELECT count(*) FROM stu_info;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

计算stu_info表中各学员的平均年龄、最大年龄、最小年龄：

mysql> SELECT avg(age),max(age),min(age) FROM stu_info;
+----------+----------+----------+
| avg(age) | max(age) | min(age) |
+----------+----------+----------+
|  22.6000 |       27 |       20 |
+----------+----------+----------+
1 row in set (0.00 sec)

计算stu_info表中男学员的个数：

mysql> SELECT count(gender) FROM stu_info WHERE gender='boy';
+---------------+
| count(gender) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

2）字段值的数值比较

列出stu_info表中年龄为21岁的学员记录：

mysql> SELECT * FROM stu_info WHERE age=21;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Tom  | boy    |  21 |
| Mike | boy    |  21 |
+------+--------+-----+
2 rows in set (0.00 sec)

列出stu_info表中年龄超过21岁的学员记录：

mysql> SELECT * FROM stu_info WHERE age>21;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
2 rows in set (0.00 sec)

列出stu_info表中年龄大于或等于21岁的学员记录：

mysql> SELECT * FROM stu_info WHERE age>=21;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
| Jerry | boy    |  27 |
| Mike  | boy    |  21 |
+-------+--------+-----+
4 rows in set (0.00 sec)

列出stu_info表中年龄在20岁和24岁之间的学员记录：

mysql> SELECT * FROM stu_info WHERE age BETWEEN 20 and 24;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
| Mike | boy    |  21 |
+------+--------+-----+
4 rows in set (0.00 sec)

3）多个条件的组合

列出stu_info表中年龄小于23岁的女学员记录：

mysql> SELECT * FROM stu_info WHERE age < 23 AND gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Lily | girl   |  20 |
+------+--------+-----+
1 row in set (0.00 sec)

列出stu_info表中年龄小于23岁的学员，或者女学员的记录：

mysql> SELECT * FROM stu_info WHERE age < 23 OR gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
| Mike | boy    |  21 |
+------+--------+-----+
4 rows in set (0.00 sec)

如果某个记录的姓名属于指定范围内的一个，则将其列出：

mysql> SELECT * FROM stu_info WHERE name IN
    -> ('Jim','Tom','Mickey','Minnie');
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
+------+--------+-----+
2 rows in set (0.00 sec)

4）使用SELECT做数学计算

计算1234与5678的和：

mysql> SELECT 1234+5678;
+-----------+
| 1234+5678 |
+-----------+
|      6912 |
+-----------+
1 row in set (0.00 sec)

计算1234与5678的乘积：

mysql> SELECT 1234*5678;
+-----------+
| 1234*5678 |
+-----------+
|   7006652 |
+-----------+
1 row in set (0.00 sec)

计算1.23456789除以3的结果：

mysql> SELECT 1.23456789/3;
+----------------+
| 1.23456789/3   |
+----------------+
| 0.411522630000 |
+----------------+
1 row in set (0.00 sec)

输出stu_info表各学员的姓名、15年后的年龄：

mysql> SELECT name,age+15 FROM stu_info;
+-------+--------+
| name  | age+15 |
+-------+--------+
| Jim   |     39 |
| Tom   |     36 |
| Lily  |     35 |
| Jerry |     42 |
| Mike  |     36 |
+-------+--------+
5 rows in set (0.00 sec)

 

高级匹配条件

模糊查询

 where　　字段名　　like　　 '通配符'
  -  表示1个字符
 % 表示0~n个字符·示例
列出name值“4个字符”的记录
mysql> select name from userdb.user
where name like "----";

5）使用模糊查询，LIKE

以下划线 _ 匹配单个字符，% 可匹配任意多个字符。

列出stu_info表中姓名以“J”开头的学员记录：

mysql> SELECT * FROM stu_info WHERE name LIKE 'J%';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
2 rows in set (0.00 sec)

列出stu_info表中姓名以“J”开头且只有3个字母的学员记录：

mysql> SELECT * FROM stu_info WHERE name LIKE 'J__';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
+------+--------+-----+
1 row in set (0.00 sec)

正则表达式

·用法
- where字段名regexp '正则表达式’

-正则元字符^  $  .  []   * l

·示例
―列出name值“以j开头或以y结尾”的记录
mysql> select name from db1.t2
where name regexp '^j | y$' ;

6）使用正则表达式，REGEXP

列出stu_info表中姓名以“J”开头且以“y”结尾的学员记录：

mysql> SELECT * FROM stu_info WHERE name REGEXP '^J.*y$';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
+-------+--------+-----+
1 row in set (0.00 sec)

效果等同于：

mysql> SELECT * FROM stu_info WHERE name Like 'J%y';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
+-------+--------+-----+
1 row in set (0.00 sec)

列出stu_info表中姓名以“J”开头或者以“y”结尾的学员记录：

mysql> SELECT * FROM stu_info WHERE name REGEXP '^J|y$';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Lily  | girl   |  20 |
| Jerry | boy    |  27 |
+-------+--------+-----+
3 rows in set (0.00 sec)

效果等同于：

mysql> SELECT * FROM stu_info WHERE name Like 'J%' OR name Like '%y';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Lily  | girl   |  20 |
| Jerry | boy    |  27 |
+-------+--------+-----+
3 rows in set (0.00 sec)

四则运算

运算操作
一字段必须是数值类型

 

 

 

操作查询结果

聚集函数

.MySQL内置数据统计函数

- avg(字段名)　　//统计字段平均值

- sum(字段名)　　//统计字段之和

- min(字段名)　　//统计字段最小值

- max(字段名)　　//统计字段最大值

- count(字段名)　　//统计字段值个数

mysql> select count("*) from userdb.user;

mysql> select count(name) from userdb.user
　　　　　　　　where shell !="/bin/bash” ;

查询结果排序查询结果分组

用法
-SQL查询     order by　　字段名　　[ asc | desc ];

- asc升序排序
- desc降序排序
通常是数值类型字段
mysql> select  name,uid    from   userdb.user
　　　　　　　　　　      order by uid desc;

用法
- SQL查询      group by     字段名;
通常是字符类型字段

mysql> select shell from userdb.user group by shell;
mysql> select shell from userdb.user
　　　where uid<=1000 group by shell;

查询结果过滤

 

. having用法
- SQL查询     having     条件表达式;
mysql> select name from userdb.user
　　　　　　　　having name="bob";
mysql> select name from userdb.user
　　　　　　　　where uid> =1000
　　　　　　　　                 having name="bob";

限制查询结果显示行数

·用法
- SQL查询    limit   数字;　　//显示查询结果前多少条记录

- SQL查询    limit   数字1,数字2;　　//显示指定范围内的查询记录

一数字1 起始行（0表示第1行)

―数字2总行数

mysql> select name,uid,gid from user limit 3;

mysql> select name.uid,gid from user limit 3,3;

7）按指定的字段排序，ORDER BY

列出stu_info表的所有记录，按年龄排序：

mysql> SELECT * FROM stu_info ORDER BY age;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Lily  | girl   |  20 |
| Tom   | boy    |  21 |
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
4 rows in set (0.00 sec)

因默认为升序（Ascend）排列，所以上述操作等效于：

mysql> SELECT * FROM stu_info ORDER BY age ASC;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Lily  | girl   |  20 |
| Tom   | boy    |  21 |
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
4 rows in set (0.00 sec)

若要按降序（Descend）排列，则将ASC改为DESC即可：

mysql> SELECT * FROM stu_info ORDER BY age DESC;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
| Lily  | girl   |  20 |
+-------+--------+-----+
4 rows in set (0.00 sec)

8）限制查询结果的输出条数，LIMIT

查询stu_info表的所有记录，只列出前3条：

mysql> SELECT * FROM stu_info LIMIT 3;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
+------+--------+-----+
3 rows in set (0.00 sec)

列出stu_info表中年龄最大的3条学员记录：

mysql> SELECT * FROM stu_info GROUP BY age DESC LIMIT 3;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
+-------+--------+-----+
3 rows in set (0.00 sec)

9）分组查询结果，GROUP BY

针对stu_info表，按性别分组，分别统计出男、女学员的人数：

mysql> SELECT gender,count(gender) FROM stu_info GROUP BY gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| boy    |             3 |
| girl   |             2 |
+--------+---------------+
2 rows in set (0.00 sec)

列出查询字段时，可以通过AS关键字来指定显示别名，比如上述操作可改为：

mysql> SELECT gender AS '性别',count(gender) AS '人数'
    -> FROM stu_info GROUP BY gender;
+--------+--------+
| 性别   | 人数   |
+--------+--------+
| boy    |      3 |
| girl   |      2 |
+--------+--------+
2 rows in set (0.00 sec)

查询综合练习题，按要求写出对应查询语句。

1、添加记录编号字段id 在所有字段上方，字段值可以自动增长。

2、显示uid 是四位数的用户的用户名和uid号。

3、显示名字是以字母r 开头 且是以字母d结尾的用户名和uid号。

4、查看gid 小于10的用户使用shell的种类。

5、查看shell不是/bin/bash用户中uid号最大用户名及uid号。

6、统计uid是3位数的用户的个数。

参考答案

1、添加记录编号字段id 在所有字段上方，字段值可以自动增长。

alter  table  userdb.userlist  add  id  int(2) primary key  auto_increment  first;

2、显示uid 是四位数的用户的用户名和uid号。

select  name,uid  from userdb.userlist where uid >=1000 and uid<=9999; 或 select  name,uid  from userdb.userlist where uid  between  1000  and  9999; 或  select  name,uid  from userdb.userlist where uid regexp ‘^....$’;

3、显示名字是以字母r 开头 且是以字母d结尾的用户名和uid号。

select name,uid  from userdb.userlist where  name regexp ‘^r.*d$’;或select user from mysql.user where user regexp '^r' and user regexp 'd$';

4、查看gid 小于10的用户使用shell的种类。

select shell from userdb.userlist where uid<10 group by shell;或Select distinct shell from userdb.userlist where uid<10； 

5、查看shell不是/bin/bash用户中uid号最大用户名及uid号。

select name,uid  from userdb.userlist where shell!=”/bin/bash” order by uid desc limit 1;

6、统计uid是3位数的用户的个数。

select count(name) from userdb.userlist where uid >=100 and uid<=999