查询及匹配条件

最新推荐文章于 2022-02-25 21:17:49 发布

wanboo0205

最新推荐文章于 2022-02-25 21:17:49 发布

阅读量270

点赞数

分类专栏： DBA 文章标签： mysql DBA

本文链接：https://blog.csdn.net/weixin_41176080/article/details/107929464

版权

DBA 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

4案例4：查询及匹配条件

4.1问题

练习常见的SQL查询及条件设置

创建stu_info表，并插入数据
练习常见SQL查询及条件设置

4.2步骤

实现此案例需要按照如下步骤进行。

步骤一：根据任务要求建立员工档案表stu_info（如上个实验已创建，可将上个实验stu_info表中记录清除后继续使用）

1）在userdb库中创建stu_info表
以root用户登入MySQL服务器：

[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

打开test库：

mysql> USE userdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

创建stu_info表，包括name、gender、age三个字段：

mysql> CREATE TABLE stu_info(
    -> name varchar(12) NOT NULL,
    -> gender enum('boy','girl') DEFAULT 'boy',
    -> age int(3) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

确认表结构：

mysql> DESC stu_info;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| name   | varchar(12)        | NO   |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
| age    | int(3)             | NO   |     | NULL    |       |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

2）准备测试表格
向建立的stu_info表插入几条测试记录

mysql> INSERT INTO stu_info VALUES
    -> ('Jim','girl',24),
    -> ('Tom','boy',21),
    -> ('Lily','girl',20),
    -> ('Jerry','boy',27),
    -> ('Mike','boy',21)
    -> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

确认stu_info表的所有记录内容：

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

步骤二：练习常见的SQL查询及条件设置

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 |
| 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)

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)

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)

7）按指定的字段排序，ORDER BY
列出stu_info表的所有记录，按年龄排序：

mysql> SELECT * FROM stu_info GROUP 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 GROUP 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 GROUP 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)

wanboo0205

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
查询及匹配条件

4案例4：查询及匹配条件4.1问题练习常见的SQL查询及条件设置创建stu_info表，并插入数据练习常见SQL查询及条件设置4.2步骤实现此案例需要按照如下步骤进行。步骤一：根据任务要求建立员工档案表stu_info（如上个实验已创建，可将上个实验stu_info表中记录清除后继续使用）1）在userdb库中创建stu_info表以root用户登入MySQL服务器：[root@dbsvr1 ~]# mysql -u root -pEnter password:Welcome t
复制链接

扫一扫