MariaDB（MySql）数据库从入门到放弃

前期准备

环境

基于 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)