数据库
数据库是按照数据结构来组织、存储和管理数据的，是建立在计算机存储设备上的仓库
-
数据库包安装
//下载mariadb数据库包
[root@localhost ~]# yum install mariadb-server -y -y
//启动数据库服务
[root@localhost ~]# systemctl restart mariadb
//初始化数据库
[root@localhost ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
#此时需要进行配置确认
//比较碍事，直接关闭防火墙
[root@localhost ~]# systemctl stop firewalld
#!!!数据库的结尾需要加上";" !!!#-
增删改查等基本操作
//此时登录数据库，使用刚才设置的密码
u 连接mysql服务器的用户名
h mysql服务器的ip地址/主机名
p 连接mysql服务器的密码
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 24
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)]>
//显示当前存在多少数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
//退出数据库系统
MariaDB [(none)]> quit
MariaDB [(none)]> exit
//切换到其他数据库下
MariaDB [mysql]> 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
//显示查看数据库的表
MariaDB [mysql]> show tables ;
+---------------------------+
| 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 |
+---------------------------+
31 rows in set (0.000 sec)
//查看数据表的表结构
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| Delete_history_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+
47 rows in set (0.001 sec)
//查询host用户在mysql表中
MariaDB [mysql]> select host from user ;
+-----------------------+
| host |
+-----------------------+
| 127.0.0.1 |
| ::1 |
| localhost |
| localhost.localdomain |
+-----------------------+
4 rows in set (0.001 sec)
//创建一个学生表（序号，姓名，性别，生日）
MariaDB [XXX]> create table student(number int(5),name varchar(10),sex varchar(5),birthday date);
Query OK, 0 rows affected (0.042 sec)
//插入几条数据
MariaDB [XXX]> insert into student (number,name,sex,birthday) values (1,"lxb","boy",20000916);
Query OK, 1 row affected (0.001 sec)
MariaDB [XXX]> insert into student (number,name,sex,birthday) values (2,"lsp","boy",20000917);
Query OK, 1 row affected (0.001 sec)
MariaDB [XXX]> insert into student (number,name,sex,birthday) values (3,"lxb","girl",20000917);
Query OK, 1 row affected (0.001 sec)
//搜索数据
MariaDB [XXX]> select * from student ;
+--------+------+------+------------+
| number | name | sex | birthday |
+--------+------+------+------------+
| 1 | lxb | boy | 2000-09-16 |
| 2 | lsp | boy | 2000-09-17 |
| 3 | lxb | girl | 2000-09-17 |
+--------+------+------+------------+
3 rows in set (0.000 sec)
//搜索sex为girl的
MariaDB [XXX]> select * from student where sex="girl";
+--------+------+------+------------+
| number | name | sex | birthday |
+--------+------+------+------------+
| 3 | lxb | girl | 2000-09-17 |
+--------+------+------+------------+
1 row in set (0.000 sec)
//搜索name是lxb的
MariaDB [XXX]> select * from student where name="lxb";
+--------+------+------+------------+
| number | name | sex | birthday |
+--------+------+------+------------+
| 1 | lxb | boy | 2000-09-16 |
| 3 | lxb | girl | 2000-09-17 |
+--------+------+------+------------+
2 rows in set (0.000 sec)
//搜索出name是lxb，sex是girl的
MariaDB [XXX]> select * from student where name="lxb" and sex="girl";
+--------+------+------+------------+
| number | name | sex | birthday |
+--------+------+------+------------+
| 3 | lxb | girl | 2000-09-17 |
+--------+------+------+------------+
1 row in set (0.000 sec)
//修改number为1的学生的name为lb
MariaDB [XXX]> update student set name="lb" where number="1";
Query OK, 1 row affected (1.623 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//查看数据库，已经修改
MariaDB [XXX]> select * from student
-> ;
+--------+------+------+------------+
| number | name | sex | birthday |
+--------+------+------+------------+
| 1 | lb | boy | 2000-09-16 |
| 2 | lsp | boy | 2000-09-17 |
| 3 | lxb | girl | 2000-09-17 |
+--------+------+------+------------+
3 rows in set (0.000 sec)
//删除表中name为lb的数据
MariaDB [XXX]> delete from student where name="lb" ;
Query OK, 1 row affected (0.001 sec)
MariaDB [XXX]> select * from student ;
+--------+------+------+------------+
| number | name | sex | birthday |
+--------+------+------+------------+
| 2 | lsp | boy | 2000-09-17 |
| 3 | lxb | girl | 2000-09-17 |
+--------+------+------+------------+
2 rows in set (0.000 sec)-
用户的管理和访问权限的控制
//创建数据库登陆用户
MariaDB [mysql]> create user lsp@localhost identified by '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [mysql]> create user lxb@localhost identified by "123456";
Query OK, 0 rows affected (0.000 sec)
//查看
MariaDB [mysql]> select host,user,password from user;
+-----------------------+------+-------------------------------------------+
| host | user | password |
+-----------------------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost.localdomain | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | lsp | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | lxb | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------------------+------+-------------------------------------------+
6 rows in set (0.000 sec)
//查看当前使用用户
MariaDB [(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)
//查看当前用户的数据库
MariaDB [(none)]> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.000 sec)
//重新登陆
[root@localhost ~]# mysql -ulxb -p 123456
//查看可以访问的数据库
MariaDB [(none)]> show databases;
//给lxb一张表的权限
MariaDB [(none)]> grant select,update,insert,delete on mysql.person to lxb@localhost;
//退出，重新登陆lxb
[root@localhost ~]# mysql -ulxb -p123456
//查询的权限
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
//增，删，改，查的权限
MariaDB [mysql]> select * from person;
MariaDB [mysql]> insert person (number,name,birthday) value (3,"xiaoming",20180303);
MariaDB [mysql]> update person set name="haha" where number=1
MariaDB [mysql]> delete from person where number=1;
//使用root用户登录，移除lxb删除的权限
MariaDB [(none)]> revoke delete on mysql.person from lxb@localhost;
//权限被拒绝
MariaDB [mysql]> delete from person where number=3 ;-
备份和还原
//备份数据库的所有表
[root@localhost ~]# mysqldump -u root -p123456 > /mysql_202212.dump
//使用root用户登录数据库
[root@localhost ~]# mysqldump -u root -p123456
//查询person表
MariaDB [mysql]> select * from person;
... ...
//删除person表
MariaDB [mysql]> drop table person;
//此时退出系统，进行还原操作
[root@localhost ~]# mysql -u root -p123456 < /mysql_202212.dum
#或source读入表信息
[root@localhost ~]# source /mysql_202212.dum
//重新登陆数据库
[root@localhost ~]# mysql -u root -p123456
//查看person表
MariaDB [mysql]> select * from person;
... ...
265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
