mysql 匹配包含指定的两个字符串 mysql匹配查询

转载

mob6454cc749e02 2024-08-07 19:33:02

文章标签 mysql 匹配包含指定的两个字符串 mysql MySQL 存储引擎 文章分类 MySQL 数据库

NSD DBA1 DAY04

查询及匹配条件
MySQL存储引擎的配置

1 查询及匹配条件

1.1 问题

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

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

1.2 步骤

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

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

1）在userdb库中创建stu_info表

以root用户登入MySQL服务器：

1. [root@dbsvr1 ~]# mysql -u root -p
2. Enter password:
3. Welcome to the MySQL monitor. Commands end with ; or \g.
4. Your MySQL connection id is 5
5. Server version: 5.6.15 MySQL Community Server (GPL)
6. 
7. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
8. 
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
12. 
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14. 
15. mysql>

打开test库：

1. mysql> USE userdb;
2. Reading table information for completion of table and column names
3. You can turn off this feature to get a quicker startup with -A
4. 
5. Database changed

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

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

确认表结构：

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

2）准备测试表格

向建立的stu_info表插入几条测试记录

1. mysql> INSERT INTO stu_info VALUES
2. -> ('Jim','girl',24),
3. -> ('Tom','boy',21),
4. -> ('Lily','girl',20),
5. -> ('Jerry','boy',27),
6. -> ('Mike','boy',21)
7. -> ;
8. Query OK, 5 rows affected (0.06 sec)
9. Records: 5 Duplicates: 0 Warnings: 0

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

1. mysql> SELECT * FROM stu_info;
2. +-------+--------+-----+
3. | name | gender | age |
4. +-------+--------+-----+
5. | Jim | girl | 24 |
6. | Tom | boy | 21 |
7. | Lily | girl | 20 |
8. | Jerry | boy | 27 |
9. | Mike | boy | 21 |
10. +-------+--------+-----+
11. 5 rows in set (0.00 sec)

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

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

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

1. mysql> SELECT count(*) FROM stu_info;
2. +----------+
3. | count(*) |
4. +----------+
5. | 5 |
6. +----------+
7. 1 row in set (0.00 sec)

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

1. mysql> SELECT avg(age),max(age),min(age) FROM stu_info;
2. +----------+----------+----------+
3. | avg(age) | max(age) | min(age) |
4. +----------+----------+----------+
5. | 22.6000 | 27 | 20 |
6. +----------+----------+----------+
7. 1 row in set (0.00 sec)

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

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

2）字段值的数值比较

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

1. mysql> SELECT * FROM stu_info WHERE age=21;
2. +------+--------+-----+
3. | name | gender | age |
4. +------+--------+-----+
5. | Tom | boy | 21 |
6. | Mike | boy | 21 |
7. +------+--------+-----+
8. 2 rows in set (0.00 sec)

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

1. mysql> SELECT * FROM stu_info WHERE age>21;
2. +-------+--------+-----+
3. | name | gender | age |
4. +-------+--------+-----+
5. | Jim | girl | 24 |
6. | Jerry | boy | 27 |
7. +-------+--------+-----+
8. 2 rows in set (0.00 sec)

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

1. mysql> SELECT * FROM stu_info WHERE age>=21;
2. +-------+--------+-----+
3. | name | gender | age |
4. +-------+--------+-----+
5. | Jim | girl | 24 |
6. | Tom | boy | 21 |
7. | Jerry | boy | 27 |
8. | Mike | boy | 21 |
9. +-------+--------+-----+
10. 4 rows in set (0.00 sec)

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

1. mysql> SELECT * FROM stu_info WHERE age BETWEEN 20 and 24;
2. +------+--------+-----+
3. | name | gender | age |
4. +------+--------+-----+
5. | Jim | girl | 24 |
6. | Tom | boy | 21 |
7. | Lily | girl | 20 |
8. | Mike | boy | 21 |
9. +------+--------+-----+
10. 4 rows in set (0.00 sec)

3）多个条件的组合

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

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

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

1. mysql> SELECT * FROM stu_info WHERE age < 23 OR gender='girl';
2. +------+--------+-----+
3. | name | gender | age |
4. +------+--------+-----+
5. | Jim | girl | 24 |
6. | Tom | boy | 21 |
7. | Lily | girl | 20 |
8. | Mike | boy | 21 |
9. +------+--------+-----+
10. 4 rows in set (0.00 sec)

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

1. mysql> SELECT * FROM stu_info WHERE name IN
2. -> ('Jim','Tom','Mickey','Minnie');
3. +------+--------+-----+
4. | name | gender | age |
5. +------+--------+-----+
6. | Jim | girl | 24 |
7. | Tom | boy | 21 |
8. +------+--------+-----+
9. 2 rows in set (0.00 sec)

4）使用SELECT做数学计算

计算1234与5678的和：

1. mysql> SELECT 1234+5678;
2. +-----------+
3. | 1234+5678 |
4. +-----------+
5. | 6912 |
6. +-----------+
7. 1 row in set (0.00 sec)

计算1234与5678的乘积：

1. mysql> SELECT 1234*5678;
2. +-----------+
3. | 1234*5678 |
4. +-----------+
5. | 7006652 |
6. +-----------+
7. 1 row in set (0.00 sec)

计算1.23456789除以3的结果：

1. mysql> SELECT 1.23456789/3;
2. +----------------+
3. | 1.23456789/3 |
4. +----------------+
5. | 0.411522630000 |
6. +----------------+
7. 1 row in set (0.00 sec)

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

1. mysql> SELECT name,age+15 FROM stu_info;
2. +-------+--------+
3. | name | age+15 |
4. +-------+--------+
5. | Jim | 39 |
6. | Tom | 36 |
7. | Lily | 35 |
8. | Jerry | 42 |
9. | Mike | 36 |
10. +-------+--------+
11. 5 rows in set (0.00 sec)

5）使用模糊查询，LIKE引领

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

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

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

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

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

6）使用正则表达式，REGEXP引领

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

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

效果等同于：

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

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

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

效果等同于：

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

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

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

1. mysql> SELECT * FROM stu_info GROUP BY age;
2. +-------+--------+-----+
3. | name | gender | age |
4. +-------+--------+-----+
5. | Lily | girl | 20 |
6. | Tom | boy | 21 |
7. | Jim | girl | 24 |
8. | Jerry | boy | 27 |
9. +-------+--------+-----+
10. 4 rows in set (0.00 sec)

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

1. mysql> SELECT * FROM stu_info GROUP BY age ASC;
2. +-------+--------+-----+
3. | name | gender | age |
4. +-------+--------+-----+
5. | Lily | girl | 20 |
6. | Tom | boy | 21 |
7. | Jim | girl | 24 |
8. | Jerry | boy | 27 |
9. +-------+--------+-----+
10. 4 rows in set (0.00 sec)

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

1. mysql> SELECT * FROM stu_info GROUP BY age DESC;
2. +-------+--------+-----+
3. | name | gender | age |
4. +-------+--------+-----+
5. | Jerry | boy | 27 |
6. | Jim | girl | 24 |
7. | Tom | boy | 21 |
8. | Lily | girl | 20 |
9. +-------+--------+-----+
10. 4 rows in set (0.00 sec)

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

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

1. mysql> SELECT * FROM stu_info LIMIT 3;
2. +------+--------+-----+
3. | name | gender | age |
4. +------+--------+-----+
5. | Jim | girl | 24 |
6. | Tom | boy | 21 |
7. | Lily | girl | 20 |
8. +------+--------+-----+
9. 3 rows in set (0.00 sec)

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

1. mysql> SELECT * FROM stu_info GROUP BY age DESC LIMIT 3;
2. +-------+--------+-----+
3. | name | gender | age |
4. +-------+--------+-----+
5. | Jerry | boy | 27 |
6. | Jim | girl | 24 |
7. | Tom | boy | 21 |
8. +-------+--------+-----+
9. 3 rows in set (0.00 sec)

9）分组查询结果，GROUP BY

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

1. mysql> SELECT gender,count(gender) FROM stu_info GROUP BY gender;
2. +--------+---------------+
3. | gender | count(gender) |
4. +--------+---------------+
5. | boy | 3 |
6. | girl | 2 |
7. +--------+---------------+
8. 2 rows in set (0.00 sec)

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

1. mysql> SELECT gender AS '性别',count(gender) AS '人数'
2. -> FROM stu_info GROUP BY gender;
3. +--------+--------+
4. | 性别 | 人数 |
5. +--------+--------+
6. | boy | 3 |
7. | girl | 2 |
8. +--------+--------+
9. 2 rows in set (0.00 sec)

2 MySQL存储引擎的配置

2.1 问题

本案例要求MySQL数据存储引擎的使用，完成以下任务操作：

查看服务支持的存储引擎
查看默认存储类型
更改表的存储引擎
设置数据库服务默认使用的存储引擎

2.2 步骤

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

步骤一：查看存储引擎信息

登入MySQL服务器，查看当前支持哪些存储引擎。

使用mysql命令连接，以root用户登入：

1. [root@dbsvr1 ~]# mysql -u root –p
2. Enter password: 
3. Welcome to the MySQL monitor. Commands end with ; or \g.
4. Your MySQL connection id is 9
5. Server version: 5.7.17 MySQL Community Server (GPL)
6. 
7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
8. 
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
12. 
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14. 
15. mysql>

执行SHOW ENGINES\G指令可列表查看，MySQL 5.6可用的存储引擎有9种（除最后的FEDERATED以外，其他8种都支持），其中默认采用的存储引擎为InnoDB：

1. mysql> SHOW ENGINES\G
2. *************************** 1. row ***************************
3. Engine: InnoDB
4. Support: DEFAULT //此存储引擎为默认
5. Comment: Supports transactions, row-level locking, and foreign keys
6. Transactions: YES
7. XA: YES
8. Savepoints: YES
9. *************************** 2. row ***************************
10. Engine: MRG_MYISAM
11. Support: YES
12. Comment: Collection of identical MyISAM tables
13. Transactions: NO
14. XA: NO
15. Savepoints: NO
16. *************************** 3. row ***************************
17. Engine: MEMORY
18. Support: YES
19. Comment: Hash based, stored in memory, useful for temporary tables
20. Transactions: NO
21. XA: NO
22. Savepoints: NO
23. *************************** 4. row ***************************
24. Engine: BLACKHOLE
25. Support: YES
26. Comment: /dev/null storage engine (anything you write to it disappears)
27. Transactions: NO
28. XA: NO
29. Savepoints: NO
30. *************************** 5. row ***************************
31. Engine: MyISAM
32. Support: YES
33. Comment: MyISAM storage engine
34. Transactions: NO
35. XA: NO
36. Savepoints: NO
37. *************************** 6. row ***************************
38. Engine: CSV
39. Support: YES
40. Comment: CSV storage engine
41. Transactions: NO
42. XA: NO
43. Savepoints: NO
44. *************************** 7. row ***************************
45. Engine: ARCHIVE
46. Support: YES
47. Comment: Archive storage engine
48. Transactions: NO
49. XA: NO
50. Savepoints: NO
51. *************************** 8. row ***************************
52. Engine: PERFORMANCE_SCHEMA
53. Support: YES
54. Comment: Performance Schema
55. Transactions: NO
56. XA: NO
57. Savepoints: NO
58. *************************** 9. row ***************************
59. Engine: FEDERATED
60. Support: NO //此引擎不被支持
61. Comment: Federated MySQL storage engine
62. Transactions: NULL
63. XA: NULL
64. Savepoints: NULL
65. 9 rows in set (0.01 sec)

步骤二：查看默认存储类型

查看系统变量default_storage_engine 的值，确认默认采用的存储引擎是InnoDB：

1. mysql> SHOW VARIABLES LIKE 'default_storage_engine';
2. +------------------------+--------+
3. | Variable_name | Value |
4. +------------------------+--------+
5. | default_storage_engine | InnoDB |
6. +------------------------+--------+
7. 1 row in set (0.00 sec)

步骤三：修改默认存储引擎

在 mysql> 环境中，可以直接通过SET指令更改默认的存储引擎（只在本次连接会话过程中有效，退出重进即失效）。比如临时修改为MyISAM，可执行下列操作：

1. mysql> SET default_storage_engine=MyISAM;             //改用MyISAM引擎
2. Query OK, 0 rows affected (0.00 sec)
3. 
4. mysql> SHOW VARIABLES LIKE 'default_storage_engine';         //确认结果
5. +------------------------+--------+
6. | Variable_name | Value |
7. +------------------------+--------+
8. | default_storage_engine | MyISAM |
9. +------------------------+--------+
10. 1 row in set (0.00 sec)

若希望直接修改MySQL服务程序所采用的默认存储引擎，应将相关设置写入配置文件/etc/my.cnf，并重启服务后生效。比如：

1. [root@dbsvr1 ~]# vim /etc/my.cnf
2. [mysqld]
3. .. ..
4. default_storage_engine=MEMORY                             //改用MEMORY引擎
5. 
6. [root@dbsvr1 ~]# systemctl restart mysqld.service         //重启服务

重新登入 mysql> 确认修改结果：

1. [root@dbsvr1 ~]# mysql -u root -p
2. Enter password: 
3. Welcome to the MySQL monitor. Commands end with ; or \g.
4. Your MySQL connection id is 3
5. Server version: 5.7.17 MySQL Community Server (GPL)
6. 
7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
8. 
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
12. 
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14. 
15. 
16. mysql> SHOW VARIABLES LIKE 'default_storage_engine';
17. +------------------------+--------+
18. | Variable_name | Value |
19. +------------------------+--------+
20. | default_storage_engine | MEMORY |                 //默认引擎已修改
21. +------------------------+--------+
22. 1 row in set (0.00 sec)
23. 
24. mysql> exit
25. Bye

步骤四：设置数据库服务默认使用的存储引擎

为了避免后续实验障碍，测试完后记得恢复原状——移除默认引擎设置，或者将其修改为InnoDB即可：

1. [root@dbsvr1 ~]# vim /etc/my.cnf
2. [mysqld]
3. .. ..
4. default_storage_engine=InnoDB
5. [root@dbsvr1 ~]# systemctl restart mysqld.service

确认恢复结果（选项 -e 可调用指定的SQL操作后返回Shell命令行）：

1. [root@dbsvr1 ~]# mysql -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"
2. Enter password:
3. +------------------------+--------+
4. | Variable_name | Value |
5. +------------------------+--------+
6. | default_storage_engine | InnoDB |
7. +------------------------+--------+

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。