Mariadb10.6版本基本使用

发表于 更新于 分类于 ， ， 阅读次数： Waline： 阅读次数： 本文字数： 22k 阅读时长 ≈ 46 分钟
Debian11下Mariadb10.6版本一些基础用法记录

写在前面的：

一、安装及初始化

Debian11下数据源安装10.6版本

1
2
3
4
5
$ sudo apt-get install software-properties-common dirmngr apt-transport-https
$ sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
$ sudo add-apt-repository 'deb [arch=amd64,i386,arm64,ppc64el] https://mirrors.inet.vn/mariadb/repo/10.6/debian bullseye main'
$ sudo apt update
$ sudo apt install mariadb-server mariadb-common

初始化：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
$ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):   //设置数据库root密码
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n  //切换到unix套接字身份验证
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y  //移除匿名账户
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y  //禁止root账户远程登陆
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y  //删除test数据库和对此数据库访问权限
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y  // 刷新权限
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

切换到root账户登陆mariadb

1
2
3
4
5
6
7
8
9
10
11
12
13
$ su
密码：
root@yourUserName-pc:# mariadb -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.6.5-MariaDB-1:10.6.5+maria~bullseye mariadb.org binary distribution

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
2
3
$ mariadb -uroot -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

解决办法如下：
跳过安全规则

1
2
3
4
5
6
7
8
9
10
$ sudo mysqld_safe --skip-grant-tables
$ mysql -u root -p
Enter password:   //无密码，直接回车
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.5-MariaDB-1:10.6.5+maria~bullseye mariadb.org binary distribution

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

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

此时root权限如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [(none)]> show grants for 'root'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
# 刷新权限
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.147 sec)
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'you password'; // 重新设置密码
Query OK, 0 rows affected (0.022 sec)

MariaDB [(none)]> \q
Bye

重新登陆：
1
2
3
4
5
6
7
8
9
10
11
$ mariadb -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.6.5-MariaDB-1:10.6.5+maria~bullseye mariadb.org binary distribution

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）查看权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [(none)]> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*0F57*******' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for 'yourUserName'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for yourUserName@localhost                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `yourUserName`@`localhost` IDENTIFIED BY PASSWORD '*0F571********' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

（2）授予权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]>  GRANT ALL PRIVILEGES ON *.* TO `yourUserName`@`localhost` IDENTIFIED BY 'password';
/* 其中`*.*`表示所有数据库中所有表； 'password' 表示密码；*/
MariaDB [(none)]> GRANT ALL PRIVILEGES ON `novel_plus`.* TO `yourUserName`@`localhost`;
Query OK, 0 rows affected (0.011 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.147 sec) 
MariaDB [(none)]> show grants for 'yourUserName'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for yourUserName@localhost                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `yourUserName`@`localhost` IDENTIFIED BY PASSWORD '*0F571A639A45A10D51B1F0F65FF29E5D3*******' |
| GRANT ALL PRIVILEGES ON `novel_plus`.* TO `yourUserName`@`localhost`                                                         |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

（3）取消权限

1
2
3
4
5
6
7
8
9
10
MariaDB [(none)]> REVOKE ALL PRIVILEGES ON `novel_plus`.* FROM `yourUserName`@`localhost`; 
Query OK, 0 rows affected (0.026 sec)

MariaDB [(none)]> show grants for 'yourUserName'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for yourUserName@localhost                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `yourUserName`@`localhost` IDENTIFIED BY PASSWORD '*0F571A639A45A10D51B1F0F65FF29E5D3736C4E0' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

注意：在mysql8.0以上版本中，需先授予root用户system_user帐户权限才能进行后续操作：

1
2
3
4
5
6
7
8
mysql> GRANT system_user ON *.* to 'root';
Query OK, 0 rows affected (0.07 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'yourUserName'@'%';
Query OK, 0 rows affected (0.62 sec)
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'yourUserName'@'%';
Query OK, 0 rows affected (0.16 sec)]
mysql> SET PASSWORD FOR 'yourUserName'@'%'='abc';
Query OK, 0 rows affected (0.20 sec)

最后刷新权限：
1
FLUSH PRIVILEGES;

1、创建新库及新表

1
2
3
4
5
6
7
8
9
10
11
12
13
$ mariadb -uyourUserName -p
Enter password: 
## 创建新数据库`study_test`
MariaDB [(none)]> CREATE DATABASE study_test;
Query OK, 1 row affected (0.021 sec)
## 创建新数据表`usr_table`
MariaDB [study_test]> CREATE TABLE usr_table(
    -> usr_id INT NOT NULL,
    -> usr_name VARCHAR(20) NOT NULL,
    -> submit_time DATE,
    -> PRIMARY KEY(usr_id)
    -> );
Query OK, 0 rows affected (0.107 sec)

若根据已有表创建新表参见第6部分

2、修改表中字段属性

1
2
3
MariaDB [study_test]> ALTER TABLE test_table MODIFY submit_time DATETIME;
Query OK, 5 rows affected (0.287 sec)              
Records: 5  Duplicates: 0  Warnings: 0

3、增加/删除字段

（1）增加

1
2
3
MariaDB [study_test]> ALTER TABLE usr_table ADD COLUMN IF NOT EXISTS (is_vip INT);
Query OK, 0 rows affected (0.154 sec)
Records: 0  Duplicates: 0  Warnings: 0

（2）删除

先增加待删除的字段is_vip1：

1
2
3
MariaDB [study_test]> ALTER TABLE usr_table ADD COLUMN IF NOT EXISTS (is_vip1 INT);
Query OK, 0 rows affected (0.044 sec)
Records: 0  Duplicates: 0  Warnings: 0

1
2
3
4
5
6
7
8
9
10
11
MariaDB [study_test]> SELECT * FROM test_table;
+---------+-----------+---------------------+--------+--------+---------+
| test_id | test_name | submit_time         | is_vip | usr_id | is_vip1 |
+---------+-----------+---------------------+--------+--------+---------+
|       1 | one       | 2021-11-14 10:39:28 |      0 |      1 |    NULL |
|       2 | two       | 2021-11-14 10:39:31 |      0 |      2 |    NULL |
|       3 | three     | 2021-11-14 10:43:13 |      1 |      3 |    NULL |
|       4 | four      | 2021-11-14 10:43:13 |      0 |      4 |    NULL |
|       5 | five      | 2021-11-14 10:43:13 |      1 |      5 |    NULL |
+---------+-----------+---------------------+--------+--------+---------+
5 rows in set (0.001 sec)

删除is_vip1字段:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [study_test]> ALTER TABLE test_table DROP COLUMN is_vip1;
Query OK, 0 rows affected (0.036 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [study_test]> SELECT * FROM test_table;
+---------+-----------+---------------------+--------+--------+
| test_id | test_name | submit_time         | is_vip | usr_id |
+---------+-----------+---------------------+--------+--------+
|       1 | one       | 2021-11-14 10:39:28 |      0 |      1 |
|       2 | two       | 2021-11-14 10:39:31 |      0 |      2 |
|       3 | three     | 2021-11-14 10:43:13 |      1 |      3 |
|       4 | four      | 2021-11-14 10:43:13 |      0 |      4 |
|       5 | five      | 2021-11-14 10:43:13 |      1 |      5 |
+---------+-----------+---------------------+--------+--------+
5 rows in set (0.001 sec)

4、插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [study_test]> INSERT INTO usr_table (usr_id,usr_name,submit_time) VALUES (1,'ONE',NOW()),(2,'TWO',NOW()),(3,'THREE',NOW()),(4,'FOUR',NOW()),(5,'FIVE',NOW()),(6,'SIX',NOW());
Query OK, 6 rows affected (0.023 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [study_test]> select * from usr_table;
+--------+----------+---------------------+--------+
| usr_id | usr_name | submit_time         | is_vip |
+--------+----------+---------------------+--------+
|      1 | ONE      | 2021-11-17 11:06:43 |   NULL |
|      2 | TWO      | 2021-11-17 11:06:43 |   NULL |
|      3 | THREE    | 2021-11-17 11:06:43 |   NULL |
|      4 | FOUR     | 2021-11-17 11:06:43 |   NULL |
|      5 | FIVE     | 2021-11-17 11:06:43 |   NULL |
|      6 | SIX      | 2021-11-17 11:06:43 |   NULL |
+--------+----------+---------------------+--------+
6 rows in set (0.000 sec)

5、更新表中数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 更新某区间 （BETWEEN） 
MariaDB [study_test]> UPDATE usr_table SET is_vip = 1 WHERE usr_id BETWEEN 3 AND 5;
Query OK, 3 rows affected (0.041 sec)
Rows matched: 3  Changed: 3  Warnings: 0
# 更新某给定范围 （IN）
MariaDB [study_test]> UPDATE usr_table SET submit_time = NOW() WHERE usr_id IN (3,5);
Query OK, 2 rows affected (0.015 sec)
Rows matched: 2  Changed: 2  Warnings: 0
MariaDB [study_test]> select * from usr_table;
+--------+----------+---------------------+--------+
| usr_id | usr_name | submit_time         | is_vip |
+--------+----------+---------------------+--------+
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |
|      4 | FOUR     | 2021-11-17 11:06:43 |      1 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |
+--------+----------+---------------------+--------+
6 rows in set (0.000 sec)

更复杂查询更新参见第8部分

6、从老表中选择字段创建新表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [study_test]> CREATE TABLE usr_score_table SELECT usr_id,submit_time FROM usr_table;
Query OK, 6 rows affected (0.116 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [study_test]> SELECT * FROM usr_score_table;
+--------+---------------------+
| usr_id | submit_time         |
+--------+---------------------+
|      1 | 2021-11-17 11:06:43 |
|      2 | 2021-11-17 11:06:43 |
|      3 | 2021-11-17 11:17:46 |
|      4 | 2021-11-17 11:06:43 |
|      5 | 2021-11-17 11:17:46 |
|      6 | 2021-11-17 11:06:43 |
+--------+---------------------+
6 rows in set (0.001 sec)

7、查询数据及分组

（1）查询数据出现次数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
# 给表usr_score_table新增俩字段y_score和s_score
MariaDB [study_test]> ALTER TABLE usr_score_table ADD COLUMN IF NOT EXISTS (y_score INT NOT NULL,s_score INT NOT NULL);
Query OK, 0 rows affected (0.129 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 更新部分数据并插入更多数据
MariaDB [study_test]> UPDATE usr_score_table SET y_score = 68,s_score=66 WHERE usr_id IN(1,3,5);
Query OK, 3 rows affected (0.039 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [study_test]> UPDATE usr_score_table SET y_score = 86,s_score=88 WHERE usr_id IN(2,4,6);
Query OK, 3 rows affected (0.023 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [study_test]> INSERT INTO usr_score_table (usr_id,submit_time,y_score,s_score) VALUES (1,NOW(),72,71),(2,NOW(),68,73),(3,NOW(),77,80),(1,NOW(),76,78),(5,NOW(),80,82),(6,NOW(),88,82);
Query OK, 6 rows affected (0.015 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [study_test]> SELECT * FROM usr_score_table;
+--------+---------------------+---------+---------+
| usr_id | submit_time         | y_score | s_score |
+--------+---------------------+---------+---------+
|      1 | 2021-11-17 11:06:43 |      68 |      66 |
|      2 | 2021-11-17 11:06:43 |      86 |      88 |
|      3 | 2021-11-17 11:17:46 |      68 |      66 |
|      4 | 2021-11-17 11:06:43 |      86 |      88 |
|      5 | 2021-11-17 11:17:46 |      68 |      66 |
|      6 | 2021-11-17 11:06:43 |      86 |      88 |
|      1 | 2021-11-17 12:03:22 |      72 |      71 |
|      2 | 2021-11-17 12:03:22 |      68 |      73 |
|      3 | 2021-11-17 12:03:22 |      77 |      80 |
|      1 | 2021-11-17 12:03:22 |      76 |      78 |
|      5 | 2021-11-17 12:03:22 |      80 |      82 |
|      6 | 2021-11-17 12:03:22 |      88 |      82 |
+--------+---------------------+---------+---------+
12 rows in set (0.000 sec)

MariaDB [study_test]> SELECT * FROM usr_table WHERE usr_id IN (SELECT usr_id FROM usr_score_table GROUP BY usr_id
 HAVING COUNT(usr_id) >= 2);
+--------+----------+---------------------+--------+
| usr_id | usr_name | submit_time         | is_vip |
+--------+----------+---------------------+--------+
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |
+--------+----------+---------------------+--------+
5 rows in set (0.033 sec)

（2）查询数据并分组(GROUP BY)

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [study_test]> SELECT usr_id, count(*) AS counts from usr_score_table GROUP BY usr_id;
+--------+--------+
| usr_id | counts |
+--------+--------+
|      1 |      3 |
|      2 |      2 |
|      3 |      2 |
|      4 |      1 |
|      5 |      2 |
|      6 |      2 |
+--------+--------+
6 rows in set (0.018 sec)

（3）两表联合查询(JOIN)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
# INNER JOIN 返回两个表中联结字段相等的行
MariaDB [study_test]> SELECT * FROM usr_table INNER JOIN usr_score_table ON usr_table.usr_id=usr_score_table.usr_i
d;
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
| usr_id | usr_name | submit_time         | is_vip | usr_id | submit_time         | y_score | s_score |
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 11:06:43 |      68 |      66 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |      2 | 2021-11-17 11:06:43 |      86 |      88 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |      3 | 2021-11-17 11:17:46 |      68 |      66 |
|      4 | FOUR     | 2021-11-17 11:06:43 |      1 |      4 | 2021-11-17 11:06:43 |      86 |      88 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |      5 | 2021-11-17 11:17:46 |      68 |      66 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |      6 | 2021-11-17 11:06:43 |      86 |      88 |
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 12:03:22 |      72 |      71 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |      2 | 2021-11-17 12:03:22 |      68 |      73 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |      3 | 2021-11-17 12:03:22 |      77 |      80 |
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 12:03:22 |      76 |      78 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |      5 | 2021-11-17 12:03:22 |      80 |      82 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |      6 | 2021-11-17 12:03:22 |      88 |      82 |
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
12 rows in set (0.019 sec)

#left join 左表中的所有记录和右表中联结字段相等的记录，以左表为准
# 给usr_table新增一行数据
MariaDB [study_test]> INSERT INTO usr_table (usr_id,usr_name,submit_time,is_vip) VALUES (7,'SEVEN',NOW(),1);
Query OK, 1 row affected (0.016 sec)

MariaDB [study_test]> SELECT * FROM usr_table LEFT JOIN usr_score_table ON usr_table.usr_id=usr_score_table.usr_id
;
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
| usr_id | usr_name | submit_time         | is_vip | usr_id | submit_time         | y_score | s_score |
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 11:06:43 |      68 |      66 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |      2 | 2021-11-17 11:06:43 |      86 |      88 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |      3 | 2021-11-17 11:17:46 |      68 |      66 |
|      4 | FOUR     | 2021-11-17 11:06:43 |      1 |      4 | 2021-11-17 11:06:43 |      86 |      88 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |      5 | 2021-11-17 11:17:46 |      68 |      66 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |      6 | 2021-11-17 11:06:43 |      86 |      88 |
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 12:03:22 |      72 |      71 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |      2 | 2021-11-17 12:03:22 |      68 |      73 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |      3 | 2021-11-17 12:03:22 |      77 |      80 |
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 12:03:22 |      76 |      78 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |      5 | 2021-11-17 12:03:22 |      80 |      82 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |      6 | 2021-11-17 12:03:22 |      88 |      82 |
|      7 | SEVEN    | 2021-11-17 12:37:48 |      1 |   NULL | NULL                |    NULL |    NULL |
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
13 rows in set (0.012 sec)

# RIGHT JOIN 右表中的所有记录和左表中联结字段相等的记录，以右表为准
MariaDB [study_test]> SELECT * FROM usr_table RIGHT JOIN usr_score_table ON usr_table.usr_id=usr_score_table.usr_i
d;
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
| usr_id | usr_name | submit_time         | is_vip | usr_id | submit_time         | y_score | s_score |
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 11:06:43 |      68 |      66 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |      2 | 2021-11-17 11:06:43 |      86 |      88 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |      3 | 2021-11-17 11:17:46 |      68 |      66 |
|      4 | FOUR     | 2021-11-17 11:06:43 |      1 |      4 | 2021-11-17 11:06:43 |      86 |      88 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |      5 | 2021-11-17 11:17:46 |      68 |      66 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |      6 | 2021-11-17 11:06:43 |      86 |      88 |
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 12:03:22 |      72 |      71 |
|      2 | TWO      | 2021-11-17 11:06:43 |      0 |      2 | 2021-11-17 12:03:22 |      68 |      73 |
|      3 | THREE    | 2021-11-17 11:17:46 |      1 |      3 | 2021-11-17 12:03:22 |      77 |      80 |
|      1 | ONE      | 2021-11-17 11:06:43 |      0 |      1 | 2021-11-17 12:03:22 |      76 |      78 |
|      5 | FIVE     | 2021-11-17 11:17:46 |      1 |      5 | 2021-11-17 12:03:22 |      80 |      82 |
|      6 | SIX      | 2021-11-17 11:06:43 |      0 |      6 | 2021-11-17 12:03:22 |      88 |      82 |
+--------+----------+---------------------+--------+--------+---------------------+---------+---------+
12 rows in set (0.001 sec)

# NATURAL JOIN 从右表添加左表没有的字段并根据左表内容输出
MariaDB [study_test]> SELECT * FROM usr_table NATURAL JOIN usr_score_table;
+--------+---------------------+----------+--------+---------+---------+
| usr_id | submit_time         | usr_name | is_vip | y_score | s_score |
+--------+---------------------+----------+--------+---------+---------+
|      1 | 2021-11-17 11:06:43 | ONE      |      0 |      68 |      66 |
|      2 | 2021-11-17 11:06:43 | TWO      |      0 |      86 |      88 |
|      3 | 2021-11-17 11:17:46 | THREE    |      1 |      68 |      66 |
|      4 | 2021-11-17 11:06:43 | FOUR     |      1 |      86 |      88 |
|      5 | 2021-11-17 11:17:46 | FIVE     |      1 |      68 |      66 |
|      6 | 2021-11-17 11:06:43 | SIX      |      0 |      86 |      88 |
+--------+---------------------+----------+--------+---------+---------+
6 rows in set (0.012 sec)

8、根据查询结果更新某字段值

（1）在某条件下单个更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [study_test]> UPDATE usr_table SET is_vip = 1 WHERE usr_id=(SELECT usr_id FROM usr_table WHERE usr_id IN (SELECT usr_id FROM usr_score_table GROUP BY usr_id  HAVING COUNT(usr_id) >= 3));
Query OK, 1 rows affected (0.014 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [study_test]> SELECT * FROM usr_table NATURAL JOIN usr_score_table;
+--------+---------------------+----------+--------+---------+---------+
| usr_id | submit_time         | usr_name | is_vip | y_score | s_score |
+--------+---------------------+----------+--------+---------+---------+
|      1 | 2021-11-17 11:06:43 | ONE      |      1 |      68 |      66 |
|      2 | 2021-11-17 11:06:43 | TWO      |      0 |      86 |      88 |
|      3 | 2021-11-17 11:17:46 | THREE    |      1 |      68 |      66 |
|      4 | 2021-11-17 11:06:43 | FOUR     |      1 |      86 |      88 |
|      5 | 2021-11-17 11:17:46 | FIVE     |      1 |      68 |      66 |
|      6 | 2021-11-17 11:06:43 | SIX      |      0 |      86 |      88 |
+--------+---------------------+----------+--------+---------+---------+
6 rows in set (0.001 sec)

（2）更新范围内的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MariaDB [study_test]> UPDATE usr_table SET is_vip = 1 WHERE usr_id IN (SELECT usr_id FROM usr_table WHERE usr_id I
N (SELECT usr_id FROM usr_score_table GROUP BY usr_id  HAVING COUNT(usr_id) >= 2));
Query OK, 2 rows affected (0.014 sec)
Rows matched: 5  Changed: 2  Warnings: 0

MariaDB [study_test]> SELECT * FROM usr_table NATURAL JOIN usr_score_table;
+--------+---------------------+----------+--------+---------+---------+
| usr_id | submit_time         | usr_name | is_vip | y_score | s_score |
+--------+---------------------+----------+--------+---------+---------+
|      1 | 2021-11-17 11:06:43 | ONE      |      1 |      68 |      66 |
|      2 | 2021-11-17 11:06:43 | TWO      |      1 |      86 |      88 |
|      3 | 2021-11-17 11:17:46 | THREE    |      1 |      68 |      66 |
|      4 | 2021-11-17 11:06:43 | FOUR     |      1 |      86 |      88 |
|      5 | 2021-11-17 11:17:46 | FIVE     |      1 |      68 |      66 |
|      6 | 2021-11-17 11:06:43 | SIX      |      1 |      86 |      88 |
+--------+---------------------+----------+--------+---------+---------+
6 rows in set (0.001 sec)

9、Debian11下mariadb升级10.5 ==》10.6

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ mariadb --help
mariadb  Ver 15.1 Distrib 10.5.12-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Usage: mariadb [OPTIONS] [database]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

root@yourUserName-pc:/etc# sudo apt install software-properties-common dirmngr apt-transport-https
root@yourUserName-pc:/etc# sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
root@yourUserName-pc:/etc# sudo add-apt-repository 'deb [arch=amd64,i386,arm64,ppc64el] https://mirror.serverion.com/mariadb/repo/10.6/debian bullseye main'

$ sudo apt dist-upgrade

$ mariadb --help
mariadb  Ver 15.1 Distrib 10.6.5-MariaDB, for debian-linux-gnu (x86_64) using readline EditLine wrapper
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Usage: mariadb [OPTIONS] [database]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

如果升级后出现登陆不了问题，参考本文写在前面的部分

10、卸载命令

1
2
3
4
5
6
7
sudo apt remove mysql-server
sudo apt autoremove mysql-server
sudo apt remove mysql-common
sudo rm /var/lib/mysql/ -R
sudo rm /etc/mysql/ -R
sudo apt autoremove mysql* --purge
sudo apt remove apparmor

关于备份请参考
Debian10备份与恢复mariaDB数据库

关于前期基础介绍请参考
Debian10下MariaDB/MySQL数据库增、删、改、查、备份及恢复

参考资料：
Download MariaDB Server
10.6.5tar包

相关文章