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

MariaDB 数据库管理

news 2025/8/11 7:52:04

表操作

[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.xdnf.cn/news/1274563.html

相关文章：

  • 活动策划（展会、年会），在线工具能快速出邀请函不？
  • Python 实例属性和类属性
  • 为wordpress顶部header.php文件中调用不同的标题和摘要
  • H3C（基于Comware操作系统）与eNSP平台（模拟华为VRP操作系统）的命令差异
  • Shell脚本-了解i++和++i
  • 堆（Java实现）
  • Spark学习（Pyspark）
  • 整数规划-分支定界
  • 【软件测试】BUG篇 — 详解
  • ATF(TF-A)安全通告 TFV-13（CVE-2024-7881）
  • 33.搜索旋转排序数组
  • ECharts 的理解和简单应用笔记
  • Gin vs Beego vs Echo：三大主流 Go Web 框架深度对比
  • 使用Blender可视化多传感器坐标系转换
  • sqli-labs-master/Less-51~Less-61
  • 文件 IO
  • MySQL 子查询
  • 大模型时代的机器人研究趋势：从多模态融合到高效迁移
  • Flutter 与 Android NDK 集成实战：实现高性能原生功能
  • wordpress文章摘要调用的3种方法
  • AI（1）-神经网络（正向传播与反向传播）
  • String AOP、事务、缓存
  • Java数据结构——LinkedList
  • Python与MySQL数据库交互实践：自动化数据插入系统
  • Radiology：经颅交流电刺激调节轻度阿尔茨海默病皮层与海马功能连接
  • 【Docker实战】将Django应用容器化的完整指南
  • YOLOv8算法改进--通过yaml文件添加注意力机制【附代码】
  • 从Redisson源码角度深入理解Redis分布式锁的正确实现
  • JavaScript垃圾回收机制
  • 106-基于Flask的重庆充电桩投建数据可视化分析系统