当前位置： 首页 > news >正文

MariaDB 数据库管理

news 2025/8/11 10:48:27

表操作

[root@server ~ 09:57:55]# yum install -y mariadb-server​[root@client ~ 09:59:22]# yum install -y mariadb​[root@server ~ 09:59:00]# systemctl enable mariadb --now#注册设置。。。[root@server ~ 10:00:40]# mysql_secure_installation​[root@server ~ 10:02:15]# mysql -u root -p​​#授予所有权限给lyk，此用户可以从任意端登录MariaDB [(none)]> grant all privileges on *.* to lyk@'%' identified by '123';Query OK, 0 rows affected (0.00 sec)​​[root@client ~ 09:59:22]# mysql -u lyk -p123 -h serverERROR 2005 (HY000): Unknown MySQL server host 'server' (2)[root@client ~ 10:07:16]# mysql -u lyk -p123 -h 10.1.8.10Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 12Server version: 5.5.68-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)]> quitBye​#client端编辑添加[root@client ~ 10:21:15]# vim /etc/my.cnf.d/client.cnf[client]user=lykpassword=123host=10.1.8.10port=3306​[root@client ~ 10:23:52]# mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 16Server version: 5.5.68-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)]> ​

MariaDB 中 SQL

[root@server ~ 10:34:19]# mysql -uroot -p123MariaDB [(none)]> CREATE DATABASE inventory;Query OK, 1 row affected (0.00 sec)[root@server ~ 10:36:27]# rz -Erz waiting to receive.[root@server ~ 10:36:33]# ls inventory.dump inventory.dump[root@server ~ 10:36:41]# mysql -uroot -p123 inventory < inventory.dump ​[root@server ~ 10:38:16]# mysql -uroot -p123MariaDB [(none)]> show databases;​MariaDB [(none)]> USE inventory;​​​

查询表

#查询表列表MariaDB [inventory]> SHOW TABLES;+---------------------+| Tables_in_inventory |+---------------------+| category            || manufacturer        || product             |+---------------------+3 rows in set (0.00 sec)​​​#查询表结构MariaDB [inventory]> DESCRIBE product;+-----------------+--------------+------+-----+---------+----------------+| Field           | Type         | Null | Key | Default | Extra          |+-----------------+--------------+------+-----+---------+----------------+| id              | int(11)      | NO   | PRI | NULL    | auto_increment || name            | varchar(100) | NO   |     | NULL    |                || price           | double       | NO   |     | NULL    |                || stock           | int(11)      | NO   |     | NULL    |                || id_category     | int(11)      | NO   |     | NULL    |                || id_manufacturer | int(11)      | NO   |     | NULL    |                |+-----------------+--------------+------+-----+---------+----------------+6 rows in set (0.00 sec)​#**查询表中所有记录所有字段**MariaDB [inventory]> select * from product;+----+-------------------+---------+-------+-------------+-----------------+| id | name              | price   | stock | id_category | id_manufacturer |+----+-------------------+---------+-------+-------------+-----------------+|  1 | ThinkServer TS140 |  539.88 |    20 |           2 |               4 ||  2 | ThinkServer RD630 | 2379.14 |    20 |           2 |               4 ||  3 | RT-AC68U          |  219.99 |    10 |           1 |               3 ||  4 | X110 64GB         |   73.84 |   100 |           3 |               1 |+----+-------------------+---------+-------+-------------+-----------------+4 rows in set (0.00 sec)​MariaDB [inventory]> SELECT * FROM category;+----+------------+| id | name       |+----+------------+|  1 | Networking ||  2 | Servers    ||  3 | Ssd        |+----+------------+3 rows in set (0.00 sec)​​MariaDB [inventory]> SELECT * FROM manufacturer;+----+----------+----------------+-------------------+| id | name     | seller         | phone_number      |+----+----------+----------------+-------------------+|  1 | SanDisk  | John Miller    | +1 (941) 329-8855 ||  2 | Kingston | Mike Taylor    | +1 (341) 375-9999 ||  3 | Asus     | Wilson Jackson | +1 (432) 367-8899 ||  4 | Lenovo   | Allen Scott    | +1 (876) 213-4439 |+----+----------+----------------+-------------------+4 rows in set (0.00 sec)​​#**查询表中所有记录特定字段**MariaDB [inventory]> SELECT name,price,stock FROM product;+-------------------+---------+-------+| name              | price   | stock |+-------------------+---------+-------+| ThinkServer TS140 |  539.88 |    20 || ThinkServer RD630 | 2379.14 |    20 || RT-AC68U          |  219.99 |    10 || X110 64GB         |   73.84 |   100 |+-------------------+---------+-------+4 rows in set (0.00 sec)​​

WHERE子句

# 条件操作符包括：=、<>、>、<、>=、<=MariaDB [inventory]> SELECT * FROM product WHERE price > 100;​# BETWEEN，匹配2个数字之间（包括数字本身）的记录。MariaDB [inventory]> SELECT * FROM product WHERE id BETWEEN 1 AND 3;​# IN，匹配列表中记录MariaDB [inventory]> SELECT * FROM product WHERE id IN (1,3);MariaDB [inventory]>  SELECT * FROM category WHERE name IN ('Servers','Ssd');​# LIKE，用于匹配字符串。%表示一个或多个字符，_表示一个字符，[charlist]表示字符列中的任何单一字符，[^charlist]或者[!charlist]不在字符列中的任何单一字符MariaDB [inventory]> SELECT * FROM product WHERE name like '%Server%';​# 逻辑与ANDMariaDB [inventory]> SELECT * FROM product WHERE name like '%RD%' AND price>1000;​# 逻辑或orMariaDB [inventory]> SELECT * FROM product WHERE name like '%RD%' OR price>500;​# ORDER BY 关键字用于对结果集进行排序MariaDB [inventory]> SELECT * FROM product ORDER BY price;MariaDB [inventory]> SELECT * FROM product ORDER BY price desc;

多表查询

#产品类型是Servers的产品名称和价格MariaDB [inventory]> SELECT product.name,product.price-> FROM product,category-> WHERE product.id_category = category.id -> AND category.name='Servers';​#查询厂商是Lenovo的产品名称和价格MariaDB [inventory]> SELECT product.name,product.price-> FROM product,manufacturer -> WHERE product.id_manufacturer = manufacturer.id-> AND manufacturer.name='Lenovo';​

函数

#查询产品价格平均值MariaDB [inventory]> SELECT avg(price) FROM product;​#查询产品价格最大值MariaDB [inventory]> SELECT max(price) FROM product;​#查询产品价格最小值MariaDB [inventory]> SELECT min(price) FROM product;​#查询产品存量MariaDB [inventory]> SELECT sum(stock) FROM product;​#查询产品价格最小值的那个产品信息MariaDB [inventory]> SELECT min(price) FROM product;MariaDB [inventory]> SELECT * FROM product WHERE price=73.84;MariaDB [inventory]> SELECT * FROM product WHERE price=(SELECT min(price) FROM product);​#查询Lenovo厂商提供了几种产品MariaDB [inventory]> SELECT count(product.name)-> FROM product,manufacturer -> WHERE product.id_manufacturer = manufacturer.id-> AND manufacturer.name='Lenovo';​#GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组MariaDB [inventory]> SELECT id_category,sum(stock) FROM product GROUP BY id_category;​

创建表

MariaDB [inventory]> CREATE TABLE staff(-> id INT(11) NOT NULL,-> name VARCHAR(100) NOT NULL,-> age INT(11)  DEFAULT 10,-> id_department INT(11) -> );Query OK, 0 rows affected (0.01 sec)​

插入记录

MariaDB [inventory]> INSERT INTO staff (id,name,age,id_department)-> VALUES (1,'aaa',11,1111);Query OK, 1 row affected (0.00 sec)​MariaDB [inventory]> INSERT INTO staff (id,name,age,id_department) VALUES (1,'bbb',22,2222);Query OK, 1 row affected (0.00 sec)​MariaDB [inventory]> INSERT INTO staff (id,name,age,id_department) VALUES (1,'ccc',33,3333);Query OK, 1 row affected (0.00 sec)

更新记录

MariaDB [inventory]> UPDATE staff SET age=11 WHERE id=1;Query OK, 2 rows affected (0.00 sec)Rows matched: 3  Changed: 2  Warnings: 0

如果使用不带WHERE子句的UPDATE，则表中的所有记录都会更新

删除记录

MariaDB [inventory]> DELETE FROM staff WHERE id=1 ;Query OK, 3 rows affected (0.00 sec)

删除表

MariaDB [inventory]> DROP TABLE staff ;

管理 MariaDB 用户

创建用户账户

MariaDB [(none)]> CREATE USER lyk@'%' IDENTIFIED BY '123';Query OK, 0 rows affected (0.00 sec)​

控制用户权限

MariaDB [(none)]> SELECT USER();+---------------+| USER()        |+---------------+| lyk@localhost |+---------------+1 row in set (0.00 sec)​MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)​MariaDB [(none)]>  USE mysql;ERROR 1044 (42000): Access denied for user 'lyk'@'%' to database 'mysql'MariaDB [(none)]> CREATE DATABASE inventory;ERROR 1044 (42000): Access denied for user 'lyk'@'%' to database 'inventory'​

查询用户权限(root权限)

[root@server ~ 13:54:14]# mysql -uroot -pMariaDB [(none)]> SHOW GRANTS FOR root@localhost;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost                                                                                                              |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |+----------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)​

授予用户权限(root下执行)

MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE, INSERT-> ON inventory.category-> TO lyk@localhost;Query OK, 0 rows affected (0.00 sec)​MariaDB [(none)]> exitBye​

验证权限

[root@server ~ 14:01:33]# mysql -u lyk -pEnter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 30Server version: 5.5.68-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)]> USE inventory;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A​Database changed​MariaDB [inventory]> SELECT * FROM category;+----+------------+| id | name       |+----+------------+|  1 | Networking ||  2 | Servers    ||  3 | Ssd        |+----+------------+3 rows in set (0.01 sec)

回收用户权限

REVOKE SELECT, UPDATE, DELETE, INSERT ON inventory.category FROM 'lyk'@'%';

删除用户

MariaDB [(none)]>  DROP USER lyk@localhost;​

更改用户密码

# root用户修改普通用户账户密码MariaDB [(none)]> USE mysql;MariaDB [(mysql)]> UPDATE user SET password=PASSWORD('mypass') WHERE user='lyk' and host=’localhost’;​# 或者MariaDB [(none)]> SET PASSWORD FOR 'lyk'@'localhost' = PASSWORD('mypass');​# 普通用户修改自己账户密码MariaDB [(none)]> SET PASSWORD = PASSWORD('mypass');MariaDB [(none)]> FLUSH PRIVILEGES;

忘记 root 用户密码

[root@server ~]# mysql -u rootMariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('新密码') where USER='root';MariaDB [(none)]> exit

执行备份

执行物理备份
#备份[root@server ~ 14:46:04]# systemctl stop mariadb[root@server ~ 14:46:14]# ls /var/lib/mysql*aria_log.00000001  ibdata1      ib_logfile1  mysqlaria_log_control   ib_logfile0  inventory    performance_schema[root@server ~ 14:47:53]# cp -r /var/lib/mysql{,.back}[root@server ~ 14:48:49]# ls /var/lib/mysql*/var/lib/mysql:aria_log.00000001  ibdata1      ib_logfile1  mysqlaria_log_control   ib_logfile0  inventory    performance_schema​/var/lib/mysql.back:aria_log.00000001  ibdata1      ib_logfile1  mysqlaria_log_control   ib_logfile0  inventory    performance_schema​[root@server ~ 14:48:52]# systemctl start mariadb[root@server ~ 14:49:48]# mysql -uroot -p123​MariaDB [(none)]> drop user root@localhost;Query OK, 0 rows affected (0.00 sec)​MariaDB [(none)]> drop user root@127.0.0.1;Query OK, 0 rows affected (0.00 sec)​MariaDB [(none)]> quit​​

恢复

[root@server ~ 14:51:30]# systemctl stop mariadb#发现不能进入[root@server ~ 14:52:22]# mysql -uroot -p123ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)​#查看权限，权限不对执行以下命令[root@server ~ 14:53:04]# ll /var/lib/mysql/mysqlchmod 660 /var/lib/mysql/mysql/user.*chown mysql:mysql /var/lib/mysql/mysql/user.*改为：-rw-rw---- 1 mysql mysql  10630 8月   8 14:52 user.frm-rw-rw---- 1 mysql mysql    532 8月   8 14:52 user.MYD-rw-rw---- 1 mysql mysql   2048 8月   8 14:52 user.MYI​#重启查看服务[root@server ~ 14:53:09]# systemctl start mariadb[root@server ~ 14:54:54]# mysql -uroot -p123Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.68-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)]> ​​
http://www.lryc.cn/news/616176.html

相关文章：

  • 【牛客刷题】小红的项链（字节跳动面试题）
  • Graham 算法求二维凸包
  • OpenEnler等Linux系统中安装git工具的方法
  • WGS84 与 ITRF 坐标系的差异及转换算法详解
  • Linux | i.MX6ULL移植 ssh 服务到开发板(第十五章)
  • 苍穹外卖-Day1 | 环境搭建、nginx、git、令牌、登录加密、接口文档、Swagger
  • 攻击实验（ARP欺骗、MAC洪范、TCP SYN Flood攻击、DHCP欺骗、DHCP饿死）
  • 【接口自动化】初识pytest，一文讲解pytest的安装，识别规则以及配置文件的使用
  • YOLOv11 模型轻量化挑战：突破边缘计算与实时应用的枷锁
  • Ollama+Deepseek+Docker+RAGFlow打造自己的私人AI知识库
  • C语言深度剖析
  • Docker 详细介绍及使用方法
  • 【东枫科技】 FR2 Massive MIMO 原型验证与开发平台，8*8通道
  • DBSACN算法的一些应用
  • 力扣-20.有效的括号
  • Design Compiler：布图规划探索（ICC II）
  • 【FPGA】初识FPGA
  • Jotai：React轻量级状态管理新选择
  • 密码学的数学基础2-Paillier为什么产生密钥对比RSA慢
  • 重学React（四）：状态管理二
  • 机器学习第八课之K-means聚类算法
  • 编程基础之多维数组——计算鞍点
  • 【Docker实战】将Django应用容器化的完整指南
  • 【代码随想录day 15】 力扣 257. 二叉树的所有路径
  • [FOC电机控制] 电压频谱图
  • 如何解决pip安装报错ModuleNotFoundError: No module named ‘ray’问题
  • Redis一站式指南一：从MySQL事务到Redis持久化及事务实现
  • 【每天一个知识点】深度领域对抗神经网络
  • MACBOOK M1安装达梦8数据库
  • nginx-主配置文件