RHCE 第十三天 mariadb

RHCE 第十三天 mariadb

文章目录

• 数据库
• 基本用法

数据库

1. 数据库： 高效的存储和处理数据的介质（磁盘和内存）
   数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。
   简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。
2. 数据库分类: SQL标准化查询语言也是关系型数据库管理系统的标准化语言
   基于存储介质的不同分为：关系型数据库（SQL）和非关系型数据库(NOSQL:Not Only SQL)

基本用法

初始化数据库： mysql_secure_installation

用户登录： mysql -uroot -p200001

输入用户密码登录，并直接进入某个数据库： mysql -u root -p mysql

不进入交互式界面，展示某个数据库中的表： mysql -u root -p mysql -e 'show tables;'

创建数据库： create database person;

进入数据库： use person;

查看当前所有的数据库： show databases;

在数据库中创建表：

create table person.student (
-> number int,
-> name varchar(20),
-> age int,
-> sex varchar(10),
-> birth date
-> );				

查看当前数据库中的所有表： show tables;

查看表怎么定义的： desc student;

修改数据库中的表名： alter table person.student rename person.students;

根据表的定义，指定字段插入： insert into students (name,age,sex) values ("zhangsan",18,"man");

根据表的定义，顺序插入信息： insert into students values (1,"wangkai",22,"man",19960909);

展示表中所有信息： select * from students;

依据表的某个字段信息，删除某一行： delete from students where name="zhangsan";

更改某一行的信息： update students set birth=19990202 where number=1;

展示特定的某几列表中的信息： select name,age from students;

创建数据库用户： create user maomao identified by "200001";

显示当前用户： select user();

给与权限： grant select,insert on students to maomao;

回收权限： revoke insert on students from maomao;

备份数据库，先退出mariadb： mysqldump -u root -p person > /person_backup_$(date +%F_%T).dump

删除表： drop table students;

恢复备份： source /person_backup_2021-09-03_11:43:31.dump;

[root@localhost ~]# mount /dev/sr0 /mnt/
[root@localhost ~]# yum install mariadb-server -y
[root@localhost ~]# systemctl restart mariadb.service [root@localhost ~]# mysql_secure_installation 					#这里要初始化 一直y就行 需要输密码就输密码[root@localhost ~]# mysql -uroot -p200001						#第一种登录方式 用root身份登录
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB ServerCopyright (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)]> exit											#退出
Bye# 第二种直接进入mysql数据库[root@localhost ~]# mysql -u root -p mysql		
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -AWelcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 12
Server version: 10.3.17-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.MariaDB [mysql]># 第三种展示mysql里面的表[root@localhost ~]# mysql -u root -p mysql -e 'show tables;'
Enter password: 
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+# 创建数据库 person
[root@localhost ~]# mysql -uroot -p200001
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 14
Server version: 10.3.17-MariaDB MariaDB ServerCopyright (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)]> create database person;			#创建数据库的语法
Query OK, 1 row affected (0.001 sec)MariaDB [(none)]> use person;						
Database changed									#执行成功会有反馈结果MariaDB [person]> use mysql							#切换到其他数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [mysql]> create table person.student (		#在mysql数据库给person数据库创建student表-> number int,-> name varchar(20),-> age int,-> sex varchar(10),-> birth date-> );											#末尾记得加);
Query OK, 0 rows affected (0.021 sec)MariaDB [mysql]> show databases;					#查看数据库
+--------------------+
| Database           |
+--------------------+
| Discuz             |
| boke               |
| information_schema |
| mysql              |
| performance_schema |
| person             |
+--------------------+
6 rows in set (0.001 sec)#进入person数据库
MariaDB [mysql]> use person;						
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed#展示person数据库中的所有表
MariaDB [person]> show tables;						
+------------------+
| Tables_in_person |
+------------------+
| student          |
+------------------+
1 row in set (0.001 sec)#展示student表
MariaDB [person]> desc student;						
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| sex    | varchar(10) | YES  |     | NULL    |       |
| birth  | date        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.002 sec)#切换数据库
MariaDB [person]> use mysql;						
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed#更改person数据库中的student表名 为students
MariaDB [mysql]> alter table person.student rename person.students;		
Query OK, 0 rows affected (0.019 sec)#插入数据，先进入目标数据库
MariaDB [mysql]> use person;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed#第一种插入方式，指定字段插入
MariaDB [person]> insert into students (name,age,sex) values ("zhangsan",18,"man");
Query OK, 1 row affected (0.002 sec)#第二种插入，顺着字段插入，注意字符串和date要有双引号
MariaDB [person]> insert into students values (1,"wangkai",22,"man","1996-09-09");
Query OK, 1 row affected (0.001 sec)MariaDB [person]> select * from students;
+--------+----------+------+------+------------+
| number | name     | age  | sex  | birth      |
+--------+----------+------+------+------------+
|   NULL | zhangsan |   18 | man  | NULL       |
|      1 | wangkai  |   22 | man  | 1996-09-09 |
+--------+----------+------+------+------------+
2 rows in set (0.000 sec)MariaDB [person]> delete from students			#删除表中的数据-> where number=1;							#指定删除number为1的一行数据
Query OK, 1 row affected (0.002 sec)	#显示students中的所有信息
MariaDB [person]> select * from students;		
+--------+----------+------+------+-------+
| number | name     | age  | sex  | birth |
+--------+----------+------+------+-------+
|   NULL | zhangsan |   18 | man  | NULL  |
+--------+----------+------+------+-------+
1 row in set (0.001 sec)#插入一段信息：
MariaDB [person]> insert into students values (1,"wangkai",22,"man","1996-02-02");
Query OK, 1 row affected (0.001 sec)MariaDB [person]> insert into students values (2,"lili",21,"woman","1997-03-03");
Query OK, 1 row affected (0.001 sec)MariaDB [person]> insert into students values (3,"kaili",21,"woman","1997-04-04");
Query OK, 1 row affected (0.003 sec)MariaDB [person]> insert into students values (4,"wangkai",20,"woman","1998-05-05"); 
Query OK, 1 row affected (0.002 sec)MariaDB [person]> insert into students values (5,"mabo",20,"man","1998-02-02");
Query OK, 1 row affected (0.002 sec)MariaDB [person]> select * from students;
+--------+----------+------+-------+------------+
| number | name     | age  | sex   | birth      |
+--------+----------+------+-------+------------+
|   NULL | zhangsan |   18 | man   | NULL       |
|      1 | wangkai  |   22 | man   | 1996-02-02 |
|      2 | lili     |   21 | woman | 1997-03-03 |
|      3 | kaili    |   21 | woman | 1997-04-04 |
|      4 | wangkai  |   20 | woman | 1998-05-05 |
|      5 | mabo     |   20 | man   | 1998-02-02 |
+--------+----------+------+-------+------------+
6 rows in set (0.002 sec)#删除zhangsan这一行
MariaDB [person]> delete from students where name="zhangsan";
Query OK, 1 row affected (0.001 sec)#展示所有信息
MariaDB [person]> select * from students;
+--------+---------+------+-------+------------+
| number | name    | age  | sex   | birth      |
+--------+---------+------+-------+------------+
|      1 | wangkai |   22 | man   | 1996-02-02 |
|      2 | lili    |   21 | woman | 1997-03-03 |
|      3 | kaili   |   21 | woman | 1997-04-04 |
|      4 | wangkai |   20 | woman | 1998-05-05 |
|      5 | mabo    |   20 | man   | 1998-02-02 |
+--------+---------+------+-------+------------+
5 rows in set (0.001 sec)#更改某行的某个数据：
MariaDB [person]> update students set birth=19990202 where number=1;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0MariaDB [person]> select * from students;
+--------+---------+------+-------+------------+
| number | name    | age  | sex   | birth      |
+--------+---------+------+-------+------------+
|      1 | wangkai |   22 | man   | 1999-02-02 |
|      2 | lili    |   21 | woman | 1997-03-03 |
|      3 | kaili   |   21 | woman | 1997-04-04 |
|      4 | wangkai |   20 | woman | 1998-05-05 |
|      5 | mabo    |   20 | man   | 1998-02-02 |
+--------+---------+------+-------+------------+
5 rows in set (0.001 sec)#展示特定某列的方法：
MariaDB [person]> select name,age from students;
+---------+------+
| name    | age  |
+---------+------+
| wangkai |   22 |
| lili    |   21 |
| kaili   |   21 |
| wangkai |   20 |
| mabo    |   20 |
+---------+------+
5 rows in set (0.000 sec)#添加一个数据库用户：
MariaDB [person]> create user maomao identified by "200001";
Query OK, 0 rows affected (0.002 sec)#查看当前用户：
MariaDB [person]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)#给予权限： 先进入指定的数据库
MariaDB [(none)]> use person;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed#给予了查看和插入的权限给maomao
MariaDB [person]> grant select,insert on students to maomao;
Query OK, 0 rows affected (0.000 sec)#回收权限：先进入指定的数据库
MariaDB [(none)]> use person;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [person]> revoke insert on students from maomao;
Query OK, 0 rows affected (0.001 sec)#备份：先退出数据库
MariaDB [person]> exit
Bye[root@localhost ~]# mysqldump -u root -p person > /person_backup_$(date +%F_%T).dump 
Enter password: [root@localhost ~]# ll 
-rw-r--r--.   1 root root      2136 Sep  3 11:43 person_backup_2021-09-03_11:43:31.dump#删除表并恢复备份：
[root@localhost ~]# mysql -uroot -p200001#进入person数据库
MariaDB [(none)]> use person;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed#删除students表
MariaDB [person]> drop table students;
Query OK, 0 rows affected (0.018 sec)#成功删除：
MariaDB [person]> show tables;
Empty set (0.000 sec)#恢复备份：
MariaDB [person]> source /person_backup_2021-09-03_11:43:31.dump;
Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.019 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 5 rows affected (0.001 sec)
Records: 5  Duplicates: 0  Warnings: 0Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.002 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)#展示所有表，能发现已经恢复了
MariaDB [person]> show tables;
+------------------+
| Tables_in_person |
+------------------+
| students         |
+------------------+
1 row in set (0.001 sec)#数据也成功恢复
MariaDB [person]> select * from students;
+--------+---------+------+-------+------------+
| number | name    | age  | sex   | birth      |
+--------+---------+------+-------+------------+
|      1 | wangkai |   22 | man   | 1999-02-02 |
|      2 | lili    |   21 | woman | 1997-03-03 |
|      3 | kaili   |   21 | woman | 1997-04-04 |
|      4 | wangkai |   20 | woman | 1998-05-05 |
|      5 | mabo    |   20 | man   | 1998-02-02 |
+--------+---------+------+-------+------------+
5 rows in set (0.000 sec)