LAMP架构搭建wordpress站点、MyISAM与InnoDB的区别、MySQL查询、DDL、DML用法

Devin-小学徒 Devin-小学徒 博客等级 墨衍会员 · AI 创作全网分发 墨衍智能分发 本文由作者通过墨衍一键同步至各平台 1分发平台 400+累计阅读 412平均单平台 已同步平台 CSDN 微信公众号 微博 知乎 掘金 百家号 博客园 抖音 小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网 我也要 码龄8年
关注
原创 最新推荐文章于 2026-10-26 18:30:00 发布 412 阅读 AI 写同款· GEO 优化 ›
基于rocky9使用LAMP架构搭建wordpress并实现网络共享存储 架构图：实现逻辑：该架构是一个基于Linux的分布式WordPress网站部署，结合了NFS和MySQL数据库，支持负载均衡和内容共享。该分布式网络应用架构，其中包含四台服务器，分别承担不同的角色以支持WordPress网站的运行和管理。该架构支持冗余和资源共享，优化了资源利用率和可用性。 阅读详情

第六周

一、将server和client端的mysql配置默认字符集为utf8mb4;

1、查看当前默认字符集；
MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8                         |
| character_set_connection | utf8                         |
| character_set_database   | latin1                       |
| character_set_filesystem | binary                       |
| character_set_results    | utf8                         |
| character_set_server     | latin1                       |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)

MariaDB [(none)]>
2、修改配置文件/etc/my.cnf
[root@rocky8 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything


[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4
[root@rocky8 ~]#
3、完成修改配置文件后重启服务
MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8mb4                      |
| character_set_connection | utf8mb4                      |
| character_set_database   | latin1                       |
| character_set_filesystem | binary                       |
| character_set_results    | utf8mb4                      |
| character_set_server     | latin1                       |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)

MariaDB [(none)]>

二、 总结mysql常见的数据类型。

数据类型主要包括：数值型、字符型和时间、日期型
1、数值型包含整数型和小数型
  • 型号 (tinyint、smallint、mediumint、int、int、bigint) int 用于存放整数，如年龄和数值等；
  • （float、decimal）用于存放小数，如货币的金额或者科学计数法等；
2、字符型：用于存储文本信息
  • char （定长)
  • varchar （变长）：变长的字符串。比较适用于较短的文本；
  • text 文本：适用于较长的文本，比如文章的内容或者备注之类的；
3、时间、日期类型：用于存储日期、时间的信息
  • date：可用于存储日期、如生日； 2023-08-25
  • time：用于存储时间 ；14:50:05
  • datetime ：2023-08-05 14:28:06

三、 创建MySQL主机表

创建一个主机表host,放在testdb中，详细要求如下：
  • 1) 主键自增id 无符号, tinyint.
  • 2) hostname可变字符长度256，可为空。
  • 3）ip 可变字符长度256，可为空。
  • 4）账号，可变字符长度256，可为空。
  • 5）密码，可变字符长度256，可为空。
  • 6）创建时间，时间类型，非空。
  • 7）更新时间，时间类型，默认当前时间。
  • 8）区域，只能在华南，华北，华东，三个区域之一。
  • 9）端口，无符号整数，可为空。
  • 10）外网地址，可变字符长度256，可为空。
  • 11）内网地址，可变字符长度256，可为空。
1、创建数据库testdb，并在testsdb库中创建host表
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| zabbix             |
+--------------------+
7 rows in set (0.000 sec)

MariaDB [(none)]> use testdb
Database changed
MariaDB [testdb]> create table host(
    -> id tinyint unsigned primary key auto_increment,
    -> hostname varchar(256),
    -> ip varchar(256),
    -> zhanghao varchar(256),
    -> password varchar(256),
    -> careate_time timestamp not null,
    -> update_time timestamp default current_timestamp,
    -> region enum('华南','华北','华东'),
    -> port int unsigned,
    -> public_address varchar(256),
    -> private_address varchar(256),
    -> check (region in ('华南','华北','华东'))
    -> );
Query OK, 0 rows affected (0.040 sec)

MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| host             |
+------------------+
1 row in set (0.000 sec)

MariaDB [testdb]> select * from host;
Empty set (0.001 sec)
2、给testdb.host表中添加多条数据
MariaDB [testdb]> INSERT INTO host(hostname,ip,zhanghao,password,careate_time,update_time,region,port,public_address,private_address) values
    ->      ('centos1','192.168.1.1','root1','123456','2023-08-05 10:00:00','2023-08-05 10:30:00','华南',3306,'publicA.com','provateA.com'),
    ->      ('centos2','192.168.1.2','root2','123456','2023-08-05 11:00:00','2023-08-05 11:30:00','华北',3307,'publicB.com','provateB.com'),
    ->      ('centos3','192.168.1.3','root3','123456','2023-08-05 12:00:00','2023-08-05 12:30:00','华东',3306,'publicC.com','provateC.com'),
    ->      ('centos4','192.168.1.4','root4','123456','2023-08-05 13:00:00','2023-08-05 13:30:00','华南',3309,'publicD.com','provateD.com');
Query OK, 4 rows affected (0.003 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [testdb]> desc host;
+-----------------+----------------------------------+------+-----+---------------------+-------------------------------+
| Field           | Type                             | Null | Key | Default             | Extra                         |
+-----------------+----------------------------------+------+-----+---------------------+-------------------------------+
| id              | tinyint(3) unsigned              | NO   | PRI | NULL                | auto_increment                |
| hostname        | varchar(256)                     | YES  |     | NULL                |                               |
| ip              | varchar(256)                     | YES  |     | NULL                |                               |
| zhanghao        | varchar(256)                     | YES  |     | NULL                |                               |
| password        | varchar(256)                     | YES  |     | NULL                |                               |
| careate_time    | timestamp                        | NO   |     | current_timestamp() | on update current_timestamp() |
| update_time     | timestamp                        | NO   |     | current_timestamp() |                               |
| region          | enum('华南','华北','华东')       | YES  |     | NULL                |                               |
| port            | int(10) unsigned                 | YES  |     | NULL                |                               |
| public_address  | varchar(256)                     | YES  |     | NULL                |                               |
| private_address | varchar(256)                     | YES  |     | NULL                |                               |
+-----------------+----------------------------------+------+-----+---------------------+-------------------------------+
11 rows in set (0.031 sec)

MariaDB [testdb]> select * from host;
+----+----------+-------------+----------+----------+---------------------+---------------------+--------+------+----------------+-----------------+
| id | hostname | ip          | zhanghao | password | careate_time        | update_time         | region | port | public_address | private_address |
+----+----------+-------------+----------+----------+---------------------+---------------------+--------+------+----------------+-----------------+
|  1 | centos1  | 192.168.1.1 | root1    | 123456   | 2023-08-05 10:00:00 | 2023-08-05 10:30:00 | 华南   | 3306 | publicA.com    | provateA.com    |
|  2 | centos2  | 192.168.1.2 | root2    | 123456   | 2023-08-05 11:00:00 | 2023-08-05 11:30:00 | 华北   | 3307 | publicB.com    | provateB.com    |
|  3 | centos3  | 192.168.1.3 | root3    | 123456   | 2023-08-05 12:00:00 | 2023-08-05 12:30:00 | 华东   | 3306 | publicC.com    | provateC.com    |
|  4 | centos4  | 192.168.1.4 | root4    | 123456   | 2023-08-05 13:00:00 | 2023-08-05 13:30:00 | 华南   | 3309 | publicD.com    | provateD.com    |
+----+----------+-------------+----------+----------+---------------------+---------------------+--------+------+----------------+-----------------+
4 rows in set (0.001 sec)

四、根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。

DDL:用于创建、修改和删除数据库及其对象;
1、创建数据库、创建表；
create database zabbix   创建数据库；
MariaDB [zabbix]> create table student(                 //在数据库中创建student表；
    -> id int primary key auto_increment,
    -> name char(4) not null,
    -> age tinyint unsigned,
    -> gender varchar(10)
    -> );
Query OK, 0 rows affected (0.008 sec)


MariaDB [zabbix]> show tables;
+------------------+
| Tables_in_zabbix |
+------------------+
| student          |
+------------------+
1 row in set (0.001 sec)
1.1、通过查询现存的表进行创建新表，新表会被直接插入查询到的数据，表结构与数据可以一并进行复制；
MariaDB [testdb]> select hostname,ip,region,port from host;    //查询host表中的相关数据；
+----------+-------------+--------+------+
| hostname | ip          | region | port |
+----------+-------------+--------+------+
| centos1  | 192.168.1.1 | 华南   | 3306 |
| centos2  | 192.168.1.2 | 华北   | 3307 |
| centos3  | 192.168.1.3 | 华东   | 3306 |
| centos4  | 192.168.1.4 | 华南   | 3309 |
+----------+-------------+--------+------+
4 rows in set (0.000 sec)
 // 创建表host2,并将查询到host表的结果插入到host2表中
MariaDB [testdb]> create table host2 select hostname,ip,port,public_address from host;
Query OK, 4 rows affected (0.039 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [testdb]> desc host2
    -> ;
+----------------+------------------+------+-----+---------+-------+
| Field          | Type             | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| hostname       | varchar(256)     | YES  |     | NULL    |       |
| ip             | varchar(256)     | YES  |     | NULL    |       |
| port           | int(10) unsigned | YES  |     | NULL    |       |
| public_address | varchar(256)     | YES  |     | NULL    |       |
+----------------+------------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip          | port | public_address |
+----------+-------------+------+----------------+
| centos1  | 192.168.1.1 | 3306 | publicA.com    |
| centos2  | 192.168.1.2 | 3307 | publicB.com    |
| centos3  | 192.168.1.3 | 3306 | publicC.com    |
| centos4  | 192.168.1.4 | 3309 | publicD.com    |
+----------+-------------+------+----------------+
4 rows in set (0.000 sec)

MariaDB [testdb]>
2、DML（数据操作语言）用于对数据进行增、删、改、查：

2.1. 插入数据（INSERT）：

MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip          | port | public_address |
+----------+-------------+------+----------------+
| centos1  | 192.168.1.1 | 3306 | publicA.com    |
| centos2  | 192.168.1.2 | 3307 | publicB.com    |
| centos3  | 192.168.1.3 | 3306 | publicC.com    |
| centos4  | 192.168.1.4 | 3309 | publicD.com    |
+----------+-------------+------+----------------+
4 rows in set (0.000 sec)
MariaDB [testdb]> INSERT INTO host2 (hostname,ip,port,public_address) values('centos5','192.168.2.1',2306,'publicF.com');
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT * FROM host2;
+----------+-------------+------+----------------+
| hostname | ip          | port | public_address |
+----------+-------------+------+----------------+
| centos1  | 192.168.1.1 | 3306 | publicA.com    |
| centos2  | 192.168.1.2 | 3307 | publicB.com    |
| centos3  | 192.168.1.3 | 3306 | publicC.com    |
| centos4  | 192.168.1.4 | 3309 | publicD.com    |
| centos5  | 192.168.2.1 | 2306 | publicF.com    |
+----------+-------------+------+----------------+
5 rows in set (0.000 sec)

MariaDB [testdb]>

2.2. 更新数据(UPDATE)：

MariaDB [testdb]> SELECT * FROM host2;
+----------+-------------+------+----------------+
| hostname | ip          | port | public_address |
+----------+-------------+------+----------------+
| centos1  | 192.168.1.1 | 3306 | publicA.com    |
| centos2  | 192.168.1.2 | 3307 | publicB.com    |
| centos3  | 192.168.1.3 | 3306 | publicC.com    |
| centos4  | 192.168.1.4 | 3309 | publicD.com    |
| centos5  | 192.168.2.1 | 2306 | publicF.com    |
+----------+-------------+------+----------------+
5 rows in set (0.000 sec)

MariaDB [testdb]> update  host2 set port = 3698 where hostname = 'centos5';   将用户centons5的端口号修改为3698；
Query OK, 1 row affected (0.029 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM host2;
+----------+-------------+------+----------------+
| hostname | ip          | port | public_address |
+----------+-------------+------+----------------+
| centos1  | 192.168.1.1 | 3306 | publicA.com    |
| centos2  | 192.168.1.2 | 3307 | publicB.com    |
| centos3  | 192.168.1.3 | 3306 | publicC.com    |
| centos4  | 192.168.1.4 | 3309 | publicD.com    |
| centos5  | 192.168.2.1 | 3698 | publicF.com    |
+----------+-------------+------+----------------+
5 rows in set (0.000 sec)

MariaDB [testdb]>

2.3. 删除数据：

MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip          | port | public_address |
+----------+-------------+------+----------------+
| centos1  | 192.168.1.1 |    1 | 3306           |
| centos2  | 192.168.1.2 |    2 | 3307           |
| centos3  | 192.168.1.3 |    3 | 3306           |
| centos4  | 192.168.1.4 |    1 | 3309           |
+----------+-------------+------+----------------+
4 rows in set (0.000 sec)

MariaDB [testdb]> DELETE FROM host2  where port = 2;   //删除端口号为2的数据；
Query OK, 1 row affected (0.003 sec)

MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip          | port | public_address |
+----------+-------------+------+----------------+
| centos1  | 192.168.1.1 |    1 | 3306           |
| centos3  | 192.168.1.3 |    3 | 3306           |
| centos4  | 192.168.1.4 |    1 | 3309           |
+----------+-------------+------+----------------+
3 rows in set (0.000 sec)

MariaDB [testdb]>

2.4. 查询数据：

MariaDB [hellodb]> select * from students where age < 20;   找出表中年龄小于20周岁的学生信息；
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
|    14 | Lu Wushuang  |  17 | F      |       3 |      NULL |
|    15 | Duan Yu      |  19 | M      |       4 |      NULL |
|    19 | Xue Baochai  |  18 | F      |       6 |      NULL |
|    20 | Diao Chan    |  19 | F      |       7 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
8 rows in set (0.000 sec)

MariaDB [hellodb]> select name,age,gender from students where age > 25;  从表中找出年龄大于25岁的学生信息并只显示姓名，年龄和性别；
+--------------+-----+--------+
| name         | age | gender |
+--------------+-----+--------+
| Xie Yanke    |  53 | M      |
| Ding Dian    |  32 | M      |
| Yu Yutong    |  26 | M      |
| Shi Qing     |  46 | M      |
| Tian Boguang |  33 | M      |
| Xu Xian      |  27 | M      |
| Sun Dasheng  | 100 | M      |
+--------------+-----+--------+
7 rows in set (0.000 sec)

MariaDB [hellodb]>
- DDL用于管理数据库结构，包括创建、修改和删除数据库及其对象。
- DML用于操作数据，包括插入、更新、删除和查询数据。

五、导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例。

5.1、 DQL（数据查询语言）
  • DQL用于从数据库中检索数据。
  • 主要命令是SELECT语句。
5.2、GROUP BY、HAVING子句：
  • GROUP BY :用于将结果集按照指定列的值进行分组。
  • 通常与聚合函数（如SUM、AVG）一起使用，以便对每个组进行计算。
  • HAVING子句用于在GROUP BY之后对分组进行过滤。
MariaDB [hellodb]> SELECT gender,avg(age) from students group by gender;  //通过对表中的年龄平均值按照性别分组显示；
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F      |  19.0000 |
| M      |  33.0000 |
+--------+----------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> SELECT gender,avg(age) from students group by gender having gender = 'F'; 使用having对GROUP BY分组之后的数据进行过滤
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F      |  19.0000 |
+--------+----------+
1 row in set (0.000 sec)

MariaDB [hellodb]>
5.3、ORDER BY
  • 用于对检索出来的数据进行排序，默认正序排列；
  • 可以按照一个或多个列进行升序（ASC）或降序（DESC）排序。
MariaDB [hellodb]> select * from students order by age;     对学生表中的数据按照年龄正序排列；
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)

MariaDB [hellodb]> 
MariaDB [hellodb]> select * from students order by age desc;   加上desc即可对年龄倒序排列；
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)

MariaDB [hellodb]>
5.4、WHERE
  • 用于过滤检索的数据，基于指定的条件。
  • 可以使用比较运算符（=、<、>等）和逻辑运算符（AND、OR）来构建条件。
MariaDB [hellodb]> SELECT * FROM students where classid in (1,2,3) and age <= 20;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
|    14 | Lu Wushuang  |  17 | F      |       3 |      NULL |
|    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.001 sec)

MariaDB [hellodb]> select * from students where age <=30 and age >=20;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
12 rows in set (0.000 sec)
5.5、LIMIT
  • LIMIT子句用于限制检索结果的数量。
  • 指定返回的行数，有助于分页和优化查询性能。

示例：

MariaDB [hellodb]> SELECT * FROM STUDENTS LIMIT 3;
ERROR 1146 (42S02): Table 'hellodb.STUDENTS' doesn't exist
MariaDB [hellodb]> SELECT * FROM students LIMIT 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)

MariaDB [hellodb]> SELECT * FROM students LIMIT 1,2,3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '3' at line 1
MariaDB [hellodb]> SELECT * FROM students LIMIT 1,3;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
|     2 | Shi Potian |  22 | M      |       1 |         7 |
|     3 | Xie Yanke  |  53 | M      |       2 |        16 |
|     4 | Ding Dian  |  32 | M      |       4 |         4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)

MariaDB [hellodb]> SELECT * FROM students LIMIT 2,3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     3 | Xie Yanke |  53 | M      |       2 |        16 |
|     4 | Ding Dian |  32 | M      |       4 |         4 |
|     5 | Yu Yutong |  26 | M      |       3 |         1 |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)

六、 基于hellodb 库, 总结子查询，关联查询 ，交叉连接，内连接，左连接，右连接，完全连接，自连接。

6.1、子查询
  • 可在SQL语句中嵌入一个子查询语句，SQL语句调用另一个SQL语句，可对同一张表或多张表；
  • 用于表达式中的子查询，子查询仅能返回单个值

也可将查询到的结果用于更新指定表中的某个数据值；


MariaDB [hellodb]> select max(age) from students; 
+----------+
| max(age) |s
+----------+                            //将查询结果嵌入到SQL语句中
|      100 |
+----------+
1 row in set (0.001 sec)
MariaDB [hellodb]> update teachers set age = (select max(age) from students)where tid = 2;
Query OK, 1 row affected (0.011 sec)
Rows matched: 1  Changed: 1  Warnings: 0       ///通过查询学生表中年纪最大的值来更新老师表中的数据结果 ；
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng | 100 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.003 sec)

查询老师表中年龄等于学生表中年龄最大值的记录
MariaDB [hellodb]> select * from teachers where age = (select max(age) from students);
+-----+--------------+-----+--------+
| TID | Name         | Age | Gender |
+-----+--------------+-----+--------+
|   4 | Lin Chaoying | 100 | F      |
+-----+--------------+-----+--------+
1 row in set (0.001 sec)

MariaDB [hellodb]>
6.2、联合查询（union） 纵向合并
  • 实现条件：多个表中的字段数量需要相同，字段名称和数据类型可以不同，但一般书记类型是相同的；
  • 当对同一张表进行合并时，union会对重复的数据进行过滤；
MariaDB [hellodb]> select stuid,name,age,gender from students union select * from teachers;  
+-------+---------------+-----+--------+
| stuid | name          | age | gender |     将学生表中的数据与老师表中的数据进行合并；
+-------+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |
|     2 | Shi Potian    |  22 | M      |
|     3 | Xie Yanke     |  53 | M      |
|     4 | Ding Dian     |  32 | M      |
|     5 | Yu Yutong     |  26 | M      |
|     6 | Shi Qing      |  46 | M      |
|     7 | Xi Ren        |  19 | F      |
|     8 | Lin Daiyu     |  17 | F      |
|     9 | Ren Yingying  |  20 | F      |
|    10 | Yue Lingshan  |  19 | F      |
|    11 | Yuan Chengzhi |  23 | M      |
|    12 | Wen Qingqing  |  19 | F      |
|    13 | Tian Boguang  |  33 | M      |
|    14 | Lu Wushuang   |  17 | F      |
|    15 | Duan Yu       |  19 | M      |
|    16 | Xu Zhu        |  21 | M      |
|    17 | Lin Chong     |  25 | M      |
|    18 | Hua Rong      |  23 | M      |
|    19 | Xue Baochai   |  18 | F      |
|    20 | Diao Chan     |  19 | F      |
|    21 | Huang Yueying |  22 | F      |
|    22 | Xiao Qiao     |  20 | F      |
|    23 | Ma Chao       |  23 | M      |
|    24 | Xu Xian       |  27 | M      |
|    25 | Sun Dasheng   | 100 | M      |
|     1 | Song Jiang    |  45 | M      |
|     2 | Zhang Sanfeng |  94 | M      |
|     3 | Miejue Shitai |  77 | F      |
|     4 | Lin Chaoying  | 100 | F      |
+-------+---------------+-----+--------+
29 rows in set (0.001 sec)

MariaDB [hellodb]> select stuid id,name 姓名,age 年龄,gender 性别 from students union select * from teachers;
+----+---------------+--------+--------+
| id | 姓名          | 年龄   | 性别   |
+----+---------------+--------+--------+
|  1 | Shi Zhongyu   |     22 | M      |
|  2 | Shi Potian    |     22 | M      |
|  3 | Xie Yanke     |     53 | M      |
|  4 | Ding Dian     |     32 | M      |
|  5 | Yu Yutong     |     26 | M      |
|  6 | Shi Qing      |     46 | M      |
|  7 | Xi Ren        |     19 | F      |
|  8 | Lin Daiyu     |     17 | F      |
|  9 | Ren Yingying  |     20 | F      |
| 10 | Yue Lingshan  |     19 | F      |
| 11 | Yuan Chengzhi |     23 | M      |
| 12 | Wen Qingqing  |     19 | F      |
| 13 | Tian Boguang  |     33 | M      |
| 14 | Lu Wushuang   |     17 | F      |
| 15 | Duan Yu       |     19 | M      |
| 16 | Xu Zhu        |     21 | M      |
| 17 | Lin Chong     |     25 | M      |
| 18 | Hua Rong      |     23 | M      |
| 19 | Xue Baochai   |     18 | F      |
| 20 | Diao Chan     |     19 | F      |
| 21 | Huang Yueying |     22 | F      |
| 22 | Xiao Qiao     |     20 | F      |
| 23 | Ma Chao       |     23 | M      |
| 24 | Xu Xian       |     27 | M      |
| 25 | Sun Dasheng   |    100 | M      |
|  1 | Song Jiang    |     45 | M      |
|  2 | Zhang Sanfeng |     94 | M      |
|  3 | Miejue Shitai |     77 | F      |
|  4 | Lin Chaoying  |    100 | F      |
+----+---------------+--------+--------+
29 rows in set (0.002 sec)

MariaDB [hellodb]> 

MariaDB [hellodb]> select * from teachers union select * from teachers   当对同一张表进行合并时 union会对重复值进行过滤；
    -> ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  | 100 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.001 sec)

MariaDB [hellodb]> select * from teachers union all  select * from teachers
    -> ;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  | 100 | F      |
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  | 100 | F      |
+-----+---------------+-----+--------+
8 rows in set (0.000 sec)
6.3、交叉连接 cross join
  • 交叉连接：会将多个表中的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加
  • 该操作执行结果会非常大，不建议使用；

示例：将老师表与学生表进行交叉连接

MariaDB [hellodb]> SELECT * FROM teachers cross join students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name          | Age | Gender | StuID | Name          | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
|   1 | Song Jiang    |  45 | M      |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|   2 | Zhang Sanfeng |  94 | M      |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|   3 | Miejue Shitai |  77 | F      |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|   4 | Lin Chaoying  | 100 | F      |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|   1 | Song Jiang    |  45 | M      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   2 | Zhang Sanfeng |  94 | M      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   3 | Miejue Shitai |  77 | F      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   4 | Lin Chaoying  | 100 | F      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   1 | Song Jiang    |  45 | M      |     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|   2 | Zhang Sanfeng |  94 | M      |     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|   3 | Miejue Shitai |  77 | F      |     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|   4 | Lin Chaoying  | 100 | F      |     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|   1 | Song Jiang    |  45 | M      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   2 | Zhang Sanfeng |  94 | M      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   3 | Miejue Shitai |  77 | F      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   4 | Lin Chaoying  | 100 | F      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   1 | Song Jiang    |  45 | M      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   2 | Zhang Sanfeng |  94 | M      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   3 | Miejue Shitai |  77 | F      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   4 | Lin Chaoying  | 100 | F      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   1 | Song Jiang    |  45 | M      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   1 | Song Jiang    |  45 | M      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   1 | Song Jiang    |  45 | M      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   1 | Song Jiang    |  45 | M      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   1 | Song Jiang    |  45 | M      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   1 | Song Jiang    |  45 | M      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   1 | Song Jiang    |  45 | M      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   1 | Song Jiang    |  45 | M      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   1 | Song Jiang    |  45 | M      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   1 | Song Jiang    |  45 | M      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   1 | Song Jiang    |  45 | M      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   1 | Song Jiang    |  45 | M      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   1 | Song Jiang    |  45 | M      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   1 | Song Jiang    |  45 | M      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   1 | Song Jiang    |  45 | M      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   3 | Miejue Shitai |  77 | F      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   1 | Song Jiang    |  45 | M      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|   3 | Miejue Shitai |  77 | F      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|   4 | Lin Chaoying  | 100 | F      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
100 rows in set (0.000 sec)
6.4、内连接
MariaDB [hellodb]> select s.stuid,s.name,s.teacherid,t.tid,t.name from students s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----------+-----+---------------+
| stuid | name        | teacherid | tid | name          |
+-------+-------------+-----------+-----+---------------+
|     5 | Yu Yutong   |         1 |   1 | Song Jiang    |
|     1 | Shi Zhongyu |         3 |   3 | Miejue Shitai |
|     4 | Ding Dian   |         4 |   4 | Lin Chaoying  |
+-------+-------------+-----------+-----+---------------+
3 rows in set (0.029 sec)
MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  | 100 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.000 sec)
6.5、左连接、右连接
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid = t.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |  100 | F      |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid = t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  | 100 | F      |
|     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.000 sec)

MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid = t.tid where s.teacherid is null;
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
|  NULL | NULL | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
1 row in set (0.001 sec)

MariaDB [hellodb]>
6.6、右连接
6.7、完全连接
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid = t.tid
    -> union
    -> select * from students s right outer join teachers t on s.teacherid = t.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age  | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |   22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |   22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |   53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |   32 | M      |       4 |         4 |    4 | Lin Chaoying  |  100 | F      |
|     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     6 | Shi Qing      |   46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |   17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |   20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |   23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |   19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |   33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |   17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |   19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |   21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |   25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |   23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |   18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |   19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |   22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |   20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |   23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |   27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   |  100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.001 sec)

MariaDB [hellodb]> 
MariaDB [hellodb]> select * from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.CourseID=co.CourseID;
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score | CourseID | Course         |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  1 |     1 |        2 |    77 |        2 | Kuihua Baodian |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  2 |     1 |        6 |    93 |        6 | Weituo Zhang   |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |  3 |     2 |        2 |    47 |        2 | Kuihua Baodian |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |  4 |     2 |        5 |    97 |        5 | Daiyu Zanghua  |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |  5 |     3 |        2 |    88 |        2 | Kuihua Baodian |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |  6 |     3 |        6 |    75 |        6 | Weituo Zhang   |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |  7 |     4 |        5 |    71 |        5 | Daiyu Zanghua  |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |  8 |     4 |        2 |    89 |        2 | Kuihua Baodian |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |  9 |     5 |        1 |    39 |        1 | Hamo Gong      |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 | 10 |     5 |        7 |    63 |        7 | Dagou Bangfa   |
|     6 | Shi Qing    |  46 | M      |       5 |      NULL | 11 |     6 |        1 |    96 |        1 | Hamo Gong      |
|     7 | Xi Ren      |  19 | F      |       3 |      NULL | 12 |     7 |        1 |    86 |        1 | Hamo Gong      |
|     7 | Xi Ren      |  19 | F      |       3 |      NULL | 13 |     7 |        7 |    83 |        7 | Dagou Bangfa   |
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 14 |     8 |        4 |    57 |        4 | Taiji Quan     |
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 15 |     8 |        3 |    93 |        3 | Jinshe Jianfa  |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
15 rows in set (0.000 sec)

MariaDB [hellodb]> select st.name,sc.score,co.course from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.CourseID=co.CourseID;
+-------------+-------+----------------+
| name        | score | course         |
+-------------+-------+----------------+
| Shi Zhongyu |    77 | Kuihua Baodian |
| Shi Zhongyu |    93 | Weituo Zhang   |
| Shi Potian  |    47 | Kuihua Baodian |
| Shi Potian  |    97 | Daiyu Zanghua  |
| Xie Yanke   |    88 | Kuihua Baodian |
| Xie Yanke   |    75 | Weituo Zhang   |
| Ding Dian   |    71 | Daiyu Zanghua  |
| Ding Dian   |    89 | Kuihua Baodian |
| Yu Yutong   |    39 | Hamo Gong      |
| Yu Yutong   |    63 | Dagou Bangfa   |
| Shi Qing    |    96 | Hamo Gong      |
| Xi Ren      |    86 | Hamo Gong      |
| Xi Ren      |    83 | Dagou Bangfa   |
| Lin Daiyu   |    57 | Taiji Quan     |
| Lin Daiyu   |    93 | Jinshe Jianfa  |
+-------------+-------+----------------+
15 rows in set (0.000 sec)

MariaDB [hellodb]>
6.8、自连接
MariaDB [hellodb]> create table emp (id int,name varvhar(10),leaderid int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(10),leaderid int)' at line 1
MariaDB [hellodb]> create table emp (id int,name varchar(10),leaderid int);
Query OK, 0 rows affected (0.008 sec)

MariaDB [hellodb]> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| leaderid | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.030 sec)

MariaDB [hellodb]> insert emp values(1,'mage',null),(2,'zhangsan',1),(3,'wangwu',2),(4,'liusan',3);
Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> select * from emp;
+------+----------+----------+
| id   | name     | leaderid |
+------+----------+----------+
|    1 | mage     |     NULL |
|    2 | zhangsan |        1 |
|    3 | wangwu   |        2 |
|    4 | liusan   |        3 |
+------+----------+----------+
4 rows in set (0.001 sec)

MariaDB [hellodb]> select * from emp e inner join emp l on e.leader=l.id;
ERROR 1054 (42S22): Unknown column 'e.leader' in 'on clause'
MariaDB [hellodb]> select * from emp e inner join emp l on e.leaderid=l.id;
+------+----------+----------+------+----------+----------+
| id   | name     | leaderid | id   | name     | leaderid |
+------+----------+----------+------+----------+----------+
|    2 | zhangsan |        1 |    1 | mage     |     NULL |
|    3 | wangwu   |        2 |    2 | zhangsan |        1 |
|    4 | liusan   |        3 |    3 | wangwu   |        2 |
+------+----------+----------+------+----------+----------+
3 rows in set (0.000 sec)

MariaDB [hellodb]> select e.name emp_name,l.name from emp e inner join emp l on e.leaderid=l.id;
+----------+----------+
| emp_name | name     |
+----------+----------+
| zhangsan | mage     |
| wangwu   | zhangsan |
| liusan   | wangwu   |
+----------+----------+
3 rows in set (0.000 sec)

MariaDB [hellodb]> select e.name emp_name,l.name from emp e lift join emp l on e.leaderid=l.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'lift join emp l on e.leaderid=l.id' at line 1
MariaDB [hellodb]> select e.name emp_name,l.name from emp e left join emp l on e.leaderid=l.id;
+----------+----------+
| emp_name | name     |
+----------+----------+
| zhangsan | mage     |
| wangwu   | zhangsan |
| liusan   | wangwu   |
| mage     | NULL     |
+----------+----------+
4 rows in set (0.001 sec)

MariaDB [hellodb]> select e.name emp_name,l.name,IFNULL(l.name,'无上级') from emp e left join emp l on e.leaderid=l.id;
+----------+----------+----------------------------+
| emp_name | name     | IFNULL(l.name,'无上级')    |
+----------+----------+----------------------------+
| zhangsan | mage     | mage                       |
| wangwu   | zhangsan | zhangsan                   |
| liusan   | wangwu   | wangwu                     |
| mage     | NULL     | 无上级                     |
+----------+----------+----------------------------+
4 rows in set (0.000 sec)

MariaDB [hellodb]> select e.name emp_name,l.name,IFNULL(l.name,'无上级') leader_name from emp e left join emp l on e.leaderid=l.id;
+----------+----------+-------------+
| emp_name | name     | leader_name |
+----------+----------+-------------+
| zhangsan | mage     | mage        |
| wangwu   | zhangsan | zhangsan    |
| liusan   | wangwu   | wangwu      |
| mage     | NULL     | 无上级      |
+----------+----------+-------------+
4 rows in set (0.000 sec)

MariaDB [hellodb]>

七、 总结select语句处理顺序。

  • FROM：首先，MySQL会从指定的表中检索数据。这可能涉及到多个表，以及它们之间的连接。

  • WHERE：然后，MySQL会根据WHERE子句中的条件筛选出满足条件的行。

  • GROUP BY：如果查询包含GROUP BY子句，MySQL会将结果行按照指定的列进行分组。

  • HAVING：如果查询中有HAVING子句，MySQL会根据HAVING条件筛选出满足条件的分组。

  • SELECT：接下来，MySQL会选择要返回的列，并进行计算、聚合等操作，生成最终的结果集。

  • ORDER BY：如果查询包含ORDER BY子句，MySQL会对结果集按照指定的列进行排序。

  • LIMIT：最后，MySQL会根据LIMIT子句限制返回的结果行数。

八、 总结mysql事件管理,用户管理，权限管理。

1、EVENT事件管理：可以允许在特定的某一个时间段自动执行SQL语句，定时性的操作不会依赖外部程序，会直接使用数据库本身的功能实现每秒钟执行一个任务，缺点是定时触发，不能对SQL直接进行调用
  • MySQL事件调度器event_schedule负责调用事件，默认是关闭的状态；
MariaDB [(none)]> select @@event_scheduler;        
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
|  9 | root        | localhost | NULL | Query   |    0 | Init                     | show processlist |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.000 sec)

MariaDB [(none)]> set global event_scheduler=1;   //开启调度器
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>
2、用户管理：涉及创建、修改和删除用户，以及分配合适的权限。用户是连接到数据库的身份，权限控制他们可以执行哪些操作。例如，创建一个新用户并授权他们只能查询指定表：
  • 创建用户：CRWATE USER
MariaDB [(none)]> create user devin@'10.0.0.%' identified by '123456';  // 创建Devin用户可以在10网段的主机对数据库进行连接；
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select user,host from mysql.user;
+-------+-----------+
| user  | host      |
+-------+-----------+
| devin | 10.0.0.%  |
| xu    | 10.0.0.%  |
| root  | 127.0.0.1 |
| root  | ::1       |
| root  | localhost |
| root  | rocky8.5  |
+-------+-----------+
6 rows in set (0.000 sec)

MariaDB [(none)]> 
[root@localhost ~]# mysql -udevin -p123456 -h'10.0.0.110' 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.35-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>
  1. 权限管理：权限管理决定了用户可以在数据库中执行的操作。它可以精细到表、列甚至是特定SQL语句级别。比如，授予Devin用户对zabbix数据库的所有的权限：
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| zabbix             |
+--------------------+
7 rows in set (0.000 sec)

MariaDB [(none)]> grant all on zabbix.* to devin@'10.0.0.%';  赋权
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| zabbix             |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]>

九、 基于apache, php, mysql搭建wordpress站点。

下载wordpress 软件包：点击下载wordpress6.2.2

1、安装相关软件
 yum install -y httpd php php-mysqlnd php-json
2、将wordpress安装包解压，http服务设置开机自启，并对/var/www/html/ 文件夹赋权；
[root@rocky8 wordpress] systemctl enable --now httpd
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
[root@rocky8 wordpress] mv * /var/www/html/
[root@rocky8 wordpress] chown -R apache. /var/www/html/
drwxr-xr-x. 5 apache apache 4096 Aug  7 20:59 /var/www/html/
[root@rocky8 wordpress] ll -d /var/www/html/
drwxr-xr-x. 5 apache apache 4096 Aug  7 20:59 /var/www/html/
3、创建wordpress数据库及用户 并设置用户登录密码；
MariaDB [(none)]> create database wordpress;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> create user wordpress@'10.0.0.115' identified by '123456';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all on wordpress.* to wordpress@'10.0.0.115';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>
4、配置域名尝试进行访问网站；
[root@rocky8 ~] cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.115  blog.xuweidong.com

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

十、总结myisam和Innodb存储引擎的区别

常见主要区别

  1. 事务支持：

    • MyISAM：不支持事务，这意味着不能使用BEGIN、COMMIT和ROLLBACK等语句来管理事务。
    • InnoDB：支持事务，可以实现ACID（原子性、一致性、隔离性和持久性）特性，适用于要求数据完整性和并发控制的应用。

  2. 并发控制：

    • MyISAM：不支持行级锁，只能使用表级锁定，这可能导致在高并发环境下的性能问题。
    • InnoDB：支持行级锁，可以更好地处理并发操作，提供更好的性能。

  3. 外键约束：

    • MyISAM：不支持外键约束，无法定义外键关系。
    • InnoDB：支持外键约束，可以定义和维护表之间的关系，保持数据的完整性。

  4. 崩溃恢复：

    • MyISAM：在崩溃时，可能会出现数据损坏，需要手动修复表。
    • InnoDB：具有更好的崩溃恢复机制，能够保证数据的完整性，并且支持自动恢复。

  5. 索引：

    • MyISAM：使用B-tree索引，支持全文索引。
    • InnoDB：同样使用B-tree索引，但也支持辅助索引，可以更好地支持外键约束。

  6. 表锁定：

    • MyISAM：在执行写操作（INSERT、UPDATE、DELETE）时，会锁定整个表，可能影响并发性能。
    • InnoDB：支持行级锁定，可以避免表级锁定，提高并发性能。
标签
#架构 #mysql #apache #数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
一定要了解的 WordPress 数据库中默认 12 个表 可以说熟悉 WordPress 数据库中这 12 个默认数据表对您用好 WordPress 、优化 WordPress 都有非常大的帮助，无论您是老站长还是新手站长明月都建议您收藏本文认真的了解和学习一番，不求精通只求熟悉即可，绝对会受益匪浅的！ 阅读详情

相关推荐

Spring Security 框架深度解析：过滤器链、认证授权架构与三大登录方式实战【源码原理、表单登录、JWT、OAuth2、方法级安全、踩坑清单】

本文深度解析Spring Security 6核心机制，以一次登录请求旅程为主线，厘清Filter链、认证授权体系及五大核心组件。结合Spring Boot 3.x，实战演示表单登录、JWT无状态认证、OAuth2三种主流登录方式的完整配置，涵盖自定义认证、方法级安全、测试与常见坑点，助开发者穿透抽象层，掌握安全框架底层逻辑。

王大师企业官方博客 2000

WordPress建站教程 从零开始服务器搭建网站超详细

说起第一次使用Wordpress建站，相信大部分人的印象都是虚拟主机、空间搭网站，因为便宜、省心，自己搭个小站放上去也算足够，但是随着各种技术的迅速发展，原来的很多虚拟主机已经不能再满足Wordpress配置要求，再加上各种云服务、VPS的降价，服务器性价比已经很高，无论是wordpress企业建站还是个人博客，很多人开始使用服务器搭建网站，今天WP主题站就为大家分享VPS服务器建站教程 本篇教程将详细讲解WordPress建站流程，从服务器配置、域名解析、宝塔面板、wordpress建...

fulk6667g78o8的专栏 1万+

H3C设备VRF配置实战：5步搞定多业务网络隔离（附排错技巧）

本文详细介绍了在H3C设备上配置VRF（虚拟路由转发）实现多业务网络隔离的实战方法。通过五步配置流程，从创建VPN-instance、绑定接口到配置独立路由表，帮助网络工程师在单台设备上构建逻辑隔离的企业级网络，并分享了高级路由配置与常见排错技巧。

gold8的专栏 927

mysql优化 wordpress_WordPress优化Mysql InnoDB转MYISAM

昨天成功把本小站网站全部转移到西部数据，本以为皆大欢喜，但是今日刷新了一下空间使用率，我靠，Mysql数据库260多M，这是啥概念，要崩溃的节奏WordPress就这几个表，虽然我文章数达到了700多篇，也不至于这么多。以为是虚拟主机的问题，给客服提交了工单，根据工作人员的回复，是wp_options一个文件过大，高达233M，把Mysql数据库引擎【engine】修改成MYISAM即可。，而my...

weixin_35935514的博客 329

别再死磕Excel了！用Vensim PLE免费版，30分钟搞定你的第一个系统动力学模型

本文介绍如何使用Vensim PLE免费版在30分钟内构建第一个系统动力学模型，无需编程或数学基础。通过个人储蓄增长案例，详细讲解因果回路图绘制、存量流量图创建及方程编写，帮助读者掌握动态系统建模与仿真技巧，适用于理财、项目管理等多种场景。

weixin_29045001的博客 320

IEC61850实战：从ICD文件解析到模型构建

本文深入解析IEC61850标准中的ICD文件结构，从Header、IED到DataTypeTemplates部分，详细介绍了如何构建电力自动化设备的数据模型。通过实战案例展示ICD文件的创建过程，并分享常见问题的调试技巧，帮助工程师快速掌握IEC61850建模方法，提升电力系统自动化项目的实施效率。

weixin_26841743的博客 485

WordPress 、Typecho 站点的 MySQL/MariaDB 数据库优化

其实 MySQL 数据库的优化无非就是缓存数据库查询记录到 Linux 物理内存里，减少 MySQL 实时查询的频率，对于我们很多个人博客站点来说，512MB 物理内存基本就可以缓存 99%以上的 MySQL 实时查询记录了，更多的时候往往都是用不完，除非你用的插件、主题里有频繁的数据库读写操作才会造成 MySQL 负载飙升的现象，所以慎用来历不明和不负责任的插件和主题也是优化数据库性能要考虑的。

草根博客站长明月登楼的CSDN博客 1786

用逐飞STC32G库做个智能小车：编码器测速、PWM调电机、ADC读电池电压（完整项目）

本文详细介绍了如何使用逐飞科技的STC32G12K128开源库构建智能小车项目，涵盖编码器测速、PWM电机控制和电池电压监测等核心功能。通过硬件选型、开发环境搭建、代码实现及调试技巧的全流程讲解，帮助开发者快速掌握基于STC32G的嵌入式系统开发，实现高性能的智能小车控制。

weixin_42526417的博客 190

真实经验长期实战-帮忙搞定wordpress的性能问题- 性能第一

根据 Akamai 的数据，页面加载延迟 100 毫秒可能会使转化率下降 7%，而 2 秒的延迟则可能导致跳出率惊人地增加 103%。无论小红书调研还是linux.do调研，发现wordpress性能问题都是最大的讨论，所以解决它。

liangdabiao的博客 2535

如何学习总线技术

如何学习总线技术 以形象生动的比喻来描绘了总线技术的基本思想，指出了总线的基本分类和总线传输的基本原理，以及在学习过程中应当掌握的最基本的知识，对初学者有起到抛砖引入的作用。 0引言 　　如果一座只能容一个人来往的独木桥，两端的人都想要过桥，为了不拥挤、阻塞，那我们就得采取有效的办法。比如规定某段时间哪端的人过桥，另一端的人就等着该他过桥的时间段的到来，同时也还可以规定人多时要按先来后到或年

ce123的技术博客 8520

【WRF教程第3.5期】预处理系统 WPS 详解：以4.5版本为例

预处理系统 WPS 详解：以4.5版本为例

WW、forever的博客 1420

WordPress入坑日记1——优化篇

最近我在折腾WordPress这个博客框架，因为我之前都在用Z-Blog弄博客的，对WordPress不太熟悉，所以最近换了WordPress时就遇到了很多坑。发这个帖主要是为了记录和总结一下我所遇到的坑。

HUIB.TOP 742

如何在MacBook上安装部署原生openclaw

本文介绍了在MacBook上安装OpenClaw的详细步骤：首先通过curl或国内镜像源安装nvm和Node.js（版本≥22），然后使用nvm安装Node.js 24并验证安装。接着通过脚本快速安装OpenClaw，完成安装后选择Qwen作为AI模型并进行OAuth认证。配置过程中可跳过联网搜索和Agent技能设置，最后选择Web界面作为呈现方式。安装完成后，系统会启动openclaw-gateway进程并打开Web界面，用户可通过简单的"hello"指令开始与AI对话。目前基础功能已

木简熙的博客 806

wordpress 占用内存 CPU过高的解决方案

wordpress 占用内存 CPU过高的解决方案

Robin罗兵 9495

ximalaya 新xm-sign逆向思路

本文主要介绍目标网站新xm-sign值的逆向思路。仅代表个人理解，如有其他建议可在评论区沟通。声明仅仅记录一下自己的学习方法，不作为其他参考、更不作为商业用途。如有侵犯请联系本人删除。

weixin_66580433的博客 2007

OV9734 DATASHEET，完整规格书

OV9734完整规格书，包括电气参数，尺寸规格，寄存器配置表等

Centos 7下安装mysql或者MariaDB详细教程

一、目前Centos下默认支持的数据库是MariaDB,MariaDB是mysql的增强版本，由于mysql被Oracle收购之后，mysql之父担心之后mysql会变成闭源的软件，就又开发了这个版本，支持mysql的所有功能，还增加了一些mysql没有的功能，只是和mysql相比，有些操作稍微不同。个人使用可以直接用MariaDB，省的去折腾mysql环境。 Centos上安装MariaDB非常...

第十六年春 3260

SAP B1中文培訓教材_SDK

公司内部培训资料，对开发工具 ，DIAPI， UIAPI，Add-ON有详细解说

数据库对比：选择MariaDB还是MySQL？

作者 | EverSQL译者 | 无明这篇文章的目的主要是比较 MySQL 和 MariaDB 之间的主要相似点和不同点。我们将从性能、安全性和主要功能方面对这两个数据库展开对比，并列出在选择数据库时需要考虑的重要事项。谁在使用 MySQL 和 MariaDB？MySQL 和 MariaDB 都发布了各自的用户名单。使用 MySQL 的有 Facebook、Github、YouTube、Twitt...

weixin_33947521的博客 2446

深度学习工具包 DeepLearningToolbox

深度学习工具包Deprecation notice.-----This toolbox is outdated and no longer maintained.There are much better tools available for deep learning than this toolbox, e.g. [Theano](http://deeplearning.net/software/theano/), [torch](http://torch.ch/) or [tensorflow](http://www.tensorflow.org/)I would suggest you use one of the tools mentioned above rather than use this toolbox.Best, Rasmus.DeepLearnToolbox================A Matlab toolbox for Deep Learning.Deep Learning is a new subfield of machine learning that focuses on learning deep hierarchical models of data.It is inspired by the human brain's apparent deep (layered, hierarchical) architecture.A good overview of the theory of Deep Learning theory is[Learning Deep Architectures for AI](http://www.iro.umontreal.ca/~bengioy/papers/ftml_book.pdf)For a more informal introduction, see the following videos by Geoffrey Hinton and Andrew Ng.* [The Next Generation of Neural Networks](http://www.youtube.com/watch?v=AyzOUbkUf3M) (Hinton, 2007)* [Recent Developments in Deep Learning](http://www.youtube.com/watch?v=VdIURAu1-aU) (Hinton, 2010)* [Unsupervised Feature Learning and Deep Learning](http://www.youtube.com/watch?v=ZmNOAtZIgIk) (Ng, 2011)If you use this toolbox in your research please cite [Prediction as a candidate for learning deep hierarchical models of data](http://www2.imm.dtu.dk/pubdb/views/publication_details.php?id=6284)```@MASTERSTHESIS\{IMM2012-06284, author = "R. B. Palm", title = "Prediction as a candidate for learning deep hierarchical models of data", year = "2012",}```Contact: rasmusbergpalm at gmail dot comDirectories included in the toolbox-----------------------------------`NN/` - A library for Feedforward Backpropagation Neural Networks`CNN/` - A library for Convolutional Neural Networks`DBN/` - A library for Deep Belief Networks`SAE/` - A library for Stacked Auto-Encoders`CAE/` - A library for Convolutional Auto-Encoders`util/` - Utility functions used by the libraries`data/` - Data used by the examples`tests/` - unit tests to verify toolbox is workingFor references on each library check REFS.mdSetup-----1. Download.2. addpath(genpath('DeepLearnToolbox'));Example: Deep Belief Network---------------------```matlabfunction test_example_DBNload mnist_uint8;train_x = double(train_x) / 255;test_x = double(test_x) / 255;train_y = double(train_y);test_y = double(test_y);%% ex1 train a 100 hidden unit RBM and visualize its weightsrand('state',0)dbn.sizes = [100];opts.numepochs = 1;opts.batchsize = 100;opts.momentum = 0;opts.alpha = 1;dbn = dbnsetup(dbn, train_x, opts);dbn = dbntrain(dbn, train_x, opts);figure; visualize(dbn.rbm{1}.W'); % Visualize the RBM weights%% ex2 train a 100-100 hidden unit DBN and use its weights to initialize a NNrand('state',0)%train dbndbn.sizes = [100 100];opts.numepochs = 1;opts.batchsize = 100;opts.momentum = 0;opts.alpha = 1;dbn = dbnsetup(dbn, train_x, opts);dbn = dbntrain(dbn, train_x, opts);%unfold dbn to nnnn = dbnunfoldtonn(dbn, 10);nn.activation_function = 'sigm';%train nnopts.numepochs = 1;opts.batchsize = 100;nn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.10, 'Too big error');```Example: Stacked Auto-Encoders---------------------```matlabfunction test_example_SAEload mnist_uint8;train_x = double(train_x)/255;test_x = double(test_x)/255;train_y = double(train_y);test_y = double(test_y);%% ex1 train a 100 hidden unit SDAE and use it to initialize a FFNN% Setup and train a stacked denoising autoencoder (SDAE)rand('state',0)sae = saesetup([784 100]);sae.ae{1}.activation_function = 'sigm';sae.ae{1}.learningRate = 1;sae.ae{1}.inputZeroMaskedFraction = 0.5;opts.numepochs = 1;opts.batchsize = 100;sae = saetrain(sae, train_x, opts);visualize(sae.ae{1}.W{1}(:,2:end)')% Use the SDAE to initialize a FFNNnn = nnsetup([784 100 10]);nn.activation_function = 'sigm';nn.learningRate = 1;nn.W{1} = sae.ae{1}.W{1};% Train the FFNNopts.numepochs = 1;opts.batchsize = 100;nn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.16, 'Too big error');```Example: Convolutional Neural Nets---------------------```matlabfunction test_example_CNNload mnist_uint8;train_x = double(reshape(train_x',28,28,60000))/255;test_x = double(reshape(test_x',28,28,10000))/255;train_y = double(train_y');test_y = double(test_y');%% ex1 Train a 6c-2s-12c-2s Convolutional neural network %will run 1 epoch in about 200 second and get around 11% error. %With 100 epochs you'll get around 1.2% errorrand('state',0)cnn.layers = { struct('type', 'i') %input layer struct('type', 'c', 'outputmaps', 6, 'kernelsize', 5) %convolution layer struct('type', 's', 'scale', 2) %sub sampling layer struct('type', 'c', 'outputmaps', 12, 'kernelsize', 5) %convolution layer struct('type', 's', 'scale', 2) %subsampling layer};cnn = cnnsetup(cnn, train_x, train_y);opts.alpha = 1;opts.batchsize = 50;opts.numepochs = 1;cnn = cnntrain(cnn, train_x, train_y, opts);[er, bad] = cnntest(cnn, test_x, test_y);%plot mean squared errorfigure; plot(cnn.rL);assert(er<0.12, 'Too big error');```Example: Neural Networks---------------------```matlabfunction test_example_NNload mnist_uint8;train_x = double(train_x) / 255;test_x = double(test_x) / 255;train_y = double(train_y);test_y = double(test_y);% normalize[train_x, mu, sigma] = zscore(train_x);test_x = normalize(test_x, mu, sigma);%% ex1 vanilla neural netrand('state',0)nn = nnsetup([784 100 10]);opts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samples[nn, L] = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.08, 'Too big error');%% ex2 neural net with L2 weight decayrand('state',0)nn = nnsetup([784 100 10]);nn.weightPenaltyL2 = 1e-4; % L2 weight decayopts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samplesnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex3 neural net with dropoutrand('state',0)nn = nnsetup([784 100 10]);nn.dropoutFraction = 0.5; % Dropout fraction opts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samplesnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex4 neural net with sigmoid activation functionrand('state',0)nn = nnsetup([784 100 10]);nn.activation_function = 'sigm'; % Sigmoid activation functionnn.learningRate = 1; % Sigm require a lower learning rateopts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samplesnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex5 plotting functionalityrand('state',0)nn = nnsetup([784 20 10]);opts.numepochs = 5; % Number of full sweeps through datann.output = 'softmax'; % use softmax outputopts.batchsize = 1000; % Take a mean gradient step over this many samplesopts.plot = 1; % enable plottingnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex6 neural net with sigmoid activation and plotting of validation and training error% split training data into training and validation datavx = train_x(1:10000,:);tx = train_x(10001:end,:);vy = train_y(1:10000,:);ty = train_y(10001:end,:);rand('state',0)nn = nnsetup([784 20 10]); nn.output = 'softmax'; % use softmax outputopts.numepochs = 5; % Number of full sweeps through dataopts.batchsize = 1000; % Take a mean gradient step over this many samplesopts.plot = 1; % enable plottingnn = nntrain(nn, tx, ty, opts, vx, vy); % nntrain takes validation set as last two arguments (optionally)[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');```[![Bitdeli Badge](https://d2weczhvl823v0.cloudfront.net/rasmusbergpalm/deeplearntoolbox/trend.png)](https://bitdeli.com/free "Bitdeli Badge")

Outlook Express 6 (OE6) for Win7/Win8/Win10

英文版 Outlook Express6.0， 兼容Win7/Win10/WinXP，解压就可用。

Wordpress启动mysql_wordpress建站之数据库mysql（或mariadb）配置

今天卡布小屋要给大家分享的是，在用wordpress建站时数据库的配置问题无论用wordpress还是typecho搭建网站都需要配置数据库，我们最常用到的就是mysql或者mariadb，两者同宗同源，差别不大我们不必对mysql十分精通，记住几个经常会用到的操作语句就足够用了，卡布小屋经常在用到的时候想不起来，索性整理出来方便自己也方便各位朋友(以centos或红帽系统为例)操作步骤：1、安装...

weixin_39631689的博客 1216

浅谈MySQL和mariadb区别

        MariaDB是MySQL源代码的一个分支，在意识到Oracle会对MySQL许可做什么后分离了出来（MySQL先后被Sun、Oracle收购）。除了作为一个Mysql的“向下替代品”，MariaDB包括的一些新特性使它优于MySQL。         这两个数据库究竟有什么本质的区别，我看mariadb文件夹BIN中还是mysql*.exe，除了MySQL会被ORACLE闭...

wjxbj的博客 1260

wordpress mysql 挂了_搭建wordpress的mysql老是挂掉，怎么关闭innodb-问答-阿里云开发者社区-阿里云...

搭建wordpress的mysql老是挂掉，怎么关闭innodb美地源 2017-09-07 17:32:59 5806wordpress的mysql老是挂掉，只要在后台操作一下马上就挂！！！配置：centos7+nginx+php+sql 5.51G内存+40G硬盘。网站总显示 数据库链接不上，重启下就好...

weixin_42197129的博客 292

Wordpress 频繁导致 Mysql 挂掉解决办法

最近两周在我使用访问Wordpress搭建的博客之后，经常出现服务器错误！你没访问量挂掉没问题啊！但是我的另一个网站访问量还可以啊～～～ 起因 访问个人Wordpress博客或者在博客后台进行数据写操作导致服务器Mysql挂掉 错误提示 can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock...

壹言——越过山丘 才发现无人等候 3314

WordPress清理和优化数据库，推荐几个优秀插件

精通mysql的朋友会发现wordpress的数据库表设计的很奇怪，很吃主机资源，使用过程中，生成了大量的数据库垃圾。使用下边的方法，把数据库没用的东西全部剔除了，数据库变的娇小起来，备份更快捷了。 第一步：清理数据库插件 由于一些博主使用了定时发布功能，修订、文稿功能没有被禁止，垃圾文件不断生成，可以通过以下几个插件清理，建议使用wp-sweep和clean 1、WP-Sweep 目前最强大的清理数据库插件，可以清理各种草稿、修订历史版本、清理各种孤立的重复的Meta、清理垃圾评论，还可以优化数据库。

janever99的专栏 1610

极限优化WordPress！打造完美站点

极限优化WordPress！打造完美站点

youcijibi的博客 6858

中小学人工智能实验室选型：教育大模型本地化部署架构

中小学AI实验室在2025-2026年面临一个具体的技术矛盾：大模型推理需求与校园本地化部署条件之间的错配。教育场景中，学生终端通常不具备独立运行7B以上参数模型的算力，而云端API调用受限于数据隐私、网络延迟与按次计费成本。教育部等九部门《关于加快推进教育数字化的意见》明确提出“探索生成式人工智能在教学中的应用”，但对本地化部署架构未给出具体参数指引。单节点峰值算力（TOPS）、模型量化后的显存占用（GB）、并发推理响应时延（ms）。某北京中职学校在2025年秋季。

2601_95481182的博客 139

Agent蜂群 核心 L2 记忆架构：ReMeLight 嵌入式长期文件记忆架构 ，人机共治打通多 Agent 共享认知

Agent蜂群 核心 L2 记忆架构：ReMeLight 嵌入式长期文件记忆架构 ，人机共治打通多 Agent 共享认知

架构师尼恩 83

SOA 参考架构白皮书精读：企业信息化架构设计的服务化第一课

重读SOA有助于理解微服务架构的演进逻辑。SOA参考架构（如OASIS标准）系统性地定义了服务边界、契约、治理与ESB等核心概念，为现代微服务提供了理论基础。尽管ESB存在性能瓶颈与耦合问题，但其在服务编排、协议转换、治理机制等方面的实践，仍对当前架构设计具有重要借鉴意义。深入理解SOA，可避免重复踩坑，更理性地权衡微服务中的去中心化、事件驱动与分布式事务等关键决策。

软件工程师文艺 308
上一篇： 增加系统硬盘创建分区、shell
下一篇： Kubernetes 集群部署（基于Docker）新手入门保姆级操作手册
点赞 0
评论 0
书签 书签 0
Devin-小学徒
Devin-小学徒
博客等级 码龄8年 78粉丝 16原创
+ 关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值