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

最新推荐文章于 2024-05-19 03:18:57 发布
最新推荐文章于 2024-05-19 03:18:57 发布
阅读量195 收藏
点赞数
文章标签： 架构 mysql apache 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Devin0501/article/details/131920687
版权

第六周

一、将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：支持行级锁定，可以避免表级锁定，提高并发性能。
Devin-小学徒
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL存储引擎MyISAM与InnoDB区别总结整理
09-09
今天小编就为大家分享一篇关于MySQL存储引擎MyISAM与InnoDB区别总结整理，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起跟随小编来看看吧
MySQL存储引擎MyISAM与InnoDB的9点区别
12-15
虽然MySQL里的存储引擎不只是MyISAM与InnoDB这两个，但常用的就是它俩了。可能有站长并未注意过MySQL的存储引擎，其实存储引擎也是数据库设计里的一大重要点，那么博客系统应该使用哪种存储引擎呢？下面我们分别来看...
WordPress建站教程 从零开始服务器搭建网站超详细
fulk6667g78o8的专栏
04-27 9126
说起第一次使用Wordpress建站，相信大部分人的印象都是虚拟主机、空间搭网站，因为便宜、省心，自己搭个小站放上去也算足够，但是随着各种技术的迅速发展，原来的很多虚拟主机已经不能再满足Wordpress配置要求，再加上各种云服务、VPS的降价，服务器性价比已经很高，无论是wordpress企业建站还是个人博客，很多人开始使用服务器搭建网站，今天WP主题站就为大家分享VPS服务器建站教程 本篇教程将详细讲解WordPress建站流程，从服务器配置、域名解析、宝塔面板、wordpress建...
mysql优化 wordpress_WordPress优化Mysql InnoDB转MYISAM
weixin_35935514的博客
02-08 238
昨天成功把本小站网站全部转移到西部数据，本以为皆大欢喜，但是今日刷新了一下空间使用率，我靠，Mysql数据库260多M，这是啥概念，要崩溃的节奏WordPress就这几个表，虽然我文章数达到了700多篇，也不至于这么多。以为是虚拟主机的问题，给客服提交了工单，根据工作人员的回复，是wp_options一个文件过大，高达233M，把Mysql数据库引擎【engine】修改成MYISAM即可。，而my...
WordPress入坑日记1——优化篇
HUIB.TOP
03-13 322
最近我在折腾WordPress这个博客框架，因为我之前都在用Z-Blog弄博客的，对WordPress不太熟悉，所以最近换了WordPress时就遇到了很多坑。发这个帖主要是为了记录和总结一下我所遇到的坑。
Centos 7下安装mysql或者MariaDB详细教程
第十六年春
11-09 2979
一、目前Centos下默认支持的数据库是MariaDB,MariaDB是mysql的增强版本，由于mysql被Oracle收购之后，mysql之父担心之后mysql会变成闭源的软件，就又开发了这个版本，支持mysql的所有功能，还增加了一些mysql没有的功能，只是和mysql相比，有些操作稍微不同。个人使用可以直接用MariaDB，省的去折腾mysql环境。 Centos上安装MariaDB非常...
Wordpress启动mysql_wordpress建站之数据库mysql（或mariadb）配置
weixin_39631689的博客
01-19 959
今天卡布小屋要给大家分享的是，在用wordpress建站时数据库的配置问题无论用wordpress还是typecho搭建网站都需要配置数据库，我们最常用到的就是mysql或者mariadb，两者同宗同源，差别不大我们不必对mysql十分精通，记住几个经常会用到的操作语句就足够用了，卡布小屋经常在用到的时候想不起来，索性整理出来方便自己也方便各位朋友(以centos或红帽系统为例)操作步骤：1、安装...
MySQL简介以及MySQL中InnoDB 与 MyISAM 引擎的区别
瘦子de啤酒肚的博客
01-18 288
MySQL MySQL简介 数据库的产生 为何有数据库？ 文件保存数据有以下缺陷： 1.文件安全性问题 2.不利于查询和管理 3.不利于存储海量数据 4.程序中使用不方便 什么是数据库？ 综合以上文件存储的缺陷，专家们设计出更利于管理数据的东西----数据库，可以有效的管理数据，数据库水平是衡量一个程序员水平的重要指标。 数据库的存储介质： 1.磁盘 2.内存 主流数据库 SQL Serve...
mysql dml锁ddl锁_MySQL之DDL、DML、读锁，写锁、显示锁、事务、隔离级别详解
weixin_32327999的博客
02-03 913
MySQL之DDL、DML、读锁，写锁、显示锁、事务、隔离级别详解mysql> help insert;DDL:DATABASETABLEVIEWDML:SELECTINSERT/REPLACEUPDATEDELETEINSERT INTO:第一种：INSERT INTO tb_name [(col1, col2,...)] {VALUES|VALUE} (val1, val2,...)[,...
宝塔mysql使用默认引擎 myisam_MySql的多存储引擎架构, 默认的引擎InnoDB与 MYISAM的区别(滴滴 阿里)...
weixin_39946657的博客
01-19 452
1、存储引擎是什么?MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySql默认配置了许多不同的存储引擎，可以预先设置或者在MySq...
MySQL MyISAM 与InnoDB 的区别
12-14
区别： 1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； 2. InnoDB支持外键，而MyISAM不支持...
MySQL存储引擎中的MyISAM和InnoDB区别详解
12-15
在使用MySQL的过程中对MyISAM和InnoDB这两个概念存在了些疑问，到底两者引擎有何分别一直是存在我心中的疑问。为了解开这个谜题，搜寻了网络，找到了如下信息： MyISAM是MySQL的默认数据库引擎（5.5版之前），由...
2、架构-服务间的通信
qq_29434541的博客
05-14 793
远程服务将计算机程序的工作范围从单机扩展至网络，从本地延 伸至远程，是构建分布式系统的首要基础。而远程服务又不仅仅是为 分布式系统服务的，在网络时代，浏览器、移动设备、桌面应用和服 务端的程序，普遍都有与其他设备交互的需求，所以今天已经很难找 到没有开发和使用过远程服务的程序员了，但是没有正确理解远程服 务的程序员却不少。
【MySQL精通之路】管理和实用程序
最新发布
Anakki的博客
05-19 179
8 mysql_migrate_keyring——keyring密钥迁移实用程序。2 innocchecksum——离线InnoDB文件校验和实用程序。7 mysql_config_editor——mysql配置工具。9 mysqlbinlog——用于处理二进制日志文件的实用程序。6 myisampack——生成压缩的只读MyISAM表。4 myisamchk——MyISAM表格维护实用程序。5 myisamlog——显示MyISAM日志文件内容。3 myisam_ftdump——显示全文索引信息。
MySQL--增、删、改、查，
weixin_65476290的博客
05-15 1000
数据库的概述、发展、现状、历史、分类MySQL关系型数据库、架构（C/S）window系统安装MySQL数据库Linux系统【选学】数据库对象——数据库（database）show、create、drop命令数据库对象——表（table）数据类型数据库表的约束表结构的调整（alter）数据库授权和权限管理（grant）数据库的编码情况【编码一致，建议统一采用utf8mb4】
mysql高版本导入低版本Unknown collation: utf8mb4_0900_ai_ci
susu1083018911的博客
05-15 180
mysql高版本导入低版本Unknown collation: utf8mb4_0900_ai_ci
js遇到需要正则匹配来修改img标签+清除行内样式
阿乐的博客
05-14 331
遇到特殊的情况就是自带样式，那么方法一就不能用了，因为只会算是从原有字符串上面多加字符串。这个正则表达式就是匹配所有的。//踩坑完毕，可以直接使用。第二行代码按自己需要改改。
一条sql语句在mysql中是怎么执行的
m0_59925573的博客
05-17 659
连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。
【Golang】 MySQL 在 Go 语言驱动程序中常用参数
tr6666的博客
05-15 322
在 Go 语言中，我们使用数据库驱动程序来连接和操作数据库。对于 MySQL，我们通常使用 go-sql-driver/mysql 驱动程序。在使用这个驱动程序时，我们需要提供一个数据源名称（DSN），它是一个包含数据库连接信息的字符串。在这个字符串中，我们可以设置很多参数来控制驱动程序的行为。本文将介绍一些常用的参数。在 MySQL 的 Go 语言驱动程序中，我们可以通过设置 DSN 中的参数来控制驱动程序的行为。这些参数可以影响到连接的建立，数据的读写，以及数据的解析等各个方面。
mysql myisam和innodb区别
03-16
MySQL中的MyISAM和InnoDB是两种不同的存储引擎。MyISAM是MySQL的默认存储引擎，而InnoDB则是MySQL的另一种存储引擎。 MyISAM存储引擎是一种非事务性存储引擎，它支持全文索引和压缩表等功能，但不支持事务和外键约束。MyISAM适用于读取频繁、写入不频繁的应用场景，如博客、新闻等网站。 InnoDB存储引擎是一种事务性存储引擎，它支持事务和外键约束等功能，但不支持全文索引和压缩表等功能。InnoDB适用于读写频繁的应用场景，如电子商务、金融等网站。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值