前期准备
环境
基于 MariaDB 8 由 CentOS 8 驱动。
安装数据库
yum install mariadb mariadb-server
启动数据库
systemctl start mariadb.service
加入开机启动项
systemctl enable mariadb
密码管理
以下每项操作后需要使用重启服务命令以生效改动。
systemctl restart mariadb.service
设置数据库密码(空白密码)
//该命令是在系统的命令行中运行,且在此之前 MariaDB 是空密码的场景下。
mysqladmin -uroot password "newpasswd01"
修改数据库密码(已知密码)
\\该命令是在系统的命令行中运行,且在此之前已知 MariaDB 旧密码的场景下。
\\updatepwd 是新密码,回车后提示 Enter password: 时输入旧密码。
mysqladmin -uroot -p password "updatepwd"
Enter password:
同时也可以以明文模式输入旧密码到命令行,但不建议这样操作,oldpwd 是旧密码,updatepwd 是新密码。。
mysqladmin -uroot -poldpwd password "updatepwd"
重置数据库密码(忘记密码)
\\在忘记当前密码场景下修改数据库密码。
停止 MariaDB 服务
systemctl stop mariadb.service
以安全模式跳过密码登录 MariaDB
mysqld_safe --skip-grant-tables &
并输入 mysql,提示成功登录到 MariaDB
mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-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)]>
切换到 mysql 库
MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
查看当前 root 用户的密文密码
MariaDB [mysql]> select host,user,password from user;
+-----------------+------+-------------------------------------------+
| host | user | password |
+-----------------+------+-------------------------------------------+
| % | root | *XXXF2D624C07AAEBB979DA5C980D0250C37D8F63 |
| vm-xx-xx-centos | root | *XXXF2D624C07AAEBB979DA5C980D0250C37D8F63 |
| 127.0.0.1 | root | *XXXF2D624C07AAEBB979DA5C980D0250C37D8F63 |
| ::1 | root | *XXXF2D624C07AAEBB979DA5C980D0250C37D8F63 |
+-----------------+------+-------------------------------------------+
4 rows in set (0.000 sec)
重置 root 用户的密码
MariaDB [mysql]> UPDATE user SET password=password("newpassword") WHERE user='root';
Query OK, 0 rows affected (0.000 sec)
Rows matched: 4 Changed: 0 Warnings: 0
刷新权限表
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
退出数据库
MariaDB [mysql]> exit
Bye
启动 MariaDB 服务后生效
systemctl start mariadb.service
使用重置后的密码登录,成功!
mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-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)]>
基本操作
登录数据库
//以下是在本地主机以及没有设置密码的情景下的快捷登录方式,标准登录方法请跳转:login-mariadb。
mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-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)]>
显示所有数据库 show
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
创建一个数据库 create
//创建的数据库名称不能与现有的重复。
MariaDB [(none)]> create database hexingxing_db
-> Ctrl-C -- exit!
Aborted
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hexingxing_db |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
打开一个数据库 use
//执行后界面提示 Database changed \n MariaDB [hexingxing_db]>,说明已经切换到 hexingxing_db 库。
MariaDB [(none)]> use hexingxing_db
Database changed
MariaDB [hexingxing_db]>
删除一个数据库 drop
//删除后,所有数据库中不再显示 hexingxing_db。
MariaDB [(none)]> drop database hexingxing_db;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
数据表操作
创建数据表需要先创建数据库,并使用 “use [数据库]” 以用于在该数据库中创建数据表。
创建库 create
MariaDB [(none)]> create database hexingxing_db;
Query OK, 1 row affected (0.000 sec)
打开库 use
MariaDB [(none)]> use hexingxing_db
Database changed
MariaDB [hexingxing_db]>
创建表 create
//配置表项目参数。
MariaDB [hexingxing_db]> create table tmp_tb1
-> (
-> id int(11),
-> name varchar(25),
-> deptid int(11),
-> salary float
-> );
Query OK, 0 rows affected (0.018 sec)
显示表 show
//在 hexingxing_db 数据库中的表有 tmp_tb1。
MariaDB [hexingxing_db]> show tables;
+-------------------------+
| Tables_in_hexingxing_db |
+-------------------------+
| tmp_tb1 |
+-------------------------+
1 row in set (0.000 sec)
插入表 insert
//输入数据到数据表。
MariaDB [hexingxing_db]> insert into tmp_tb1 (name,salary)
-> values('hexingxing',25000),('liuhua',22000),('xujiaojiao',12000),('liyicai',11000);
查询表 select
查询表内的所有内容:select *
MariaDB [hexingxing_db]> select * from tmp_tb1;
+------+------------+--------+--------+
| id | name | deptid | salary |
+------+------------+--------+--------+
| NULL | hexingxing | NULL | 25000 |
| NULL | liuhua | NULL | 22000 |
| NULL | xujiaojiao | NULL | 12000 |
| NULL | liyicai | NULL | 11000 |
+------+------------+--------+--------+
4 rows in set (0.000 sec)
查询表内的指定内容:select name,salary
MariaDB [hexingxing_db]> select name,salary from tmp_tb1;
+------------+--------+
| name | salary |
+------------+--------+
| hexingxing | 25000 |
| liuhua | 22000 |
| xujiaojiao | 12000 |
| liyicai | 11000 |
+------------+--------+
4 rows in set (0.000 sec)
表结构 describe
//简写 desc。
MariaDB [hexingxing_db]> describe tmp_tb1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
表详细结构
//说法:show create table [表名]\G
//语句结尾将 ; 换成 \G 可以以更易读的形式呈现内容。
MariaDB [hexingxing_db]> show create table tmp_tb1\G
*************************** 1. row ***************************
Table: tmp_tb1
Create Table: CREATE TABLE `tmp_tb1` (
`id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
改表名 rename
//语法:alter table [旧表名] rename [新表名];
MariaDB [hexingxing_db]> alter table tmp_tb1 rename table01;
Query OK, 0 rows affected (0.014 sec)
MariaDB [hexingxing_db]> show tables;
+-------------------------+
| Tables_in_hexingxing_db |
+-------------------------+
| table01 |
+-------------------------+
1 row in set (0.000 sec)
改字段的数据类型 modify
//语法:alter table [表名] modify [字段名] [数据类型];
MariaDB [hexingxing_db]> desc table01;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
MariaDB [hexingxing_db]> alter table table01 modify name varchar(35);
Query OK, 0 rows affected (0.004 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hexingxing_db]> desc table01;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(35) | YES | | NULL | |
| deptid | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
改字段名 change
//语法:alter table [表名] change [旧字段名] [新字段名] [新数据类型];
//[新数据类型] 可以保持旧值,但不能留空。
MariaDB [hexingxing_db]> alter table table01 change deptid departmentId int(15);
Query OK, 0 rows affected (0.004 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hexingxing_db]> desc table01;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(35) | YES | | NULL | |
| departmentId | int(15) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
1 条评论
CentOS 8 搭建当前最新版本 LNMP 环境(Linux+Nginx+MariaDB+PHP) – 何星星 · 2022年3月16日 13:57
[…] 关于 MariaDB 的相关详细实战请跳转至:https://hexingxing.cn/mariadb-mysql-database-combat […]