时间：2021-07-01 10:21:17 帮助过：7人阅读
mariadb数据库
一 mariadb建立
1 yum install mariadb-server.x86_64 -y ###安装软件###
2 systemctl start mariadb ###开启服务###
3 netstat -antlpe | grep mysql ###查看数据库在网络中的端口###
4 vim /etc/my.cnf ###编辑配置文件###
内容：
skip-networking=1 #####绕过网络的功能，关闭数据库在网络中开启的端口，此时只允许通过套接字文件进行本地连接,阻断所有来自网络的tcp/ip连接。###
5 systemctl restart mariadb.service ###重启服务###
6 mysql_secure_installation ####对数据库初始化,如果不初始化，则使用mysql直接能进入数据库####
初始化的内容:
Set root password? [Y/n] ###是否设置密码###
Remove anonymous users? [Y/n] ###是否允许匿名用户登入###Disallow root login remotely? [Y/n] ###是否允许远程root登入####Remove test database and access to it? [Y/n] ###是否删除测试数据库####
Reload privilege tables now? [Y/n] y ###是否重新加载表####
7 mysql -uroot -p ###-u表示指定登入用户，-p表示此用户密码，不要在-p后直接输入密码，会被截取，不安全####
过程如下：
[root@server Desktop]# yum install mariadb-server.x86_64 -y
[root@server Desktop]# vim /etc/my.cnf ###编辑配置文件###
内容：
[mysqld]
skip-networking=1 #####绕过网络的功能，关闭数据库在网络中开启的端口###
[root@server Desktop]# systemctl restart mariadb.service ###重启服务###
[root@server Desktop]# netstat -antlpe | grep mysql ###再次查看端口###
[root@server Desktop]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> -Ctrl-C -- exit!
Aborted
[root@server Desktop]# mysql_secure_installation ####对数据库初始化####
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
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):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y ###设置密码###
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y ###是否允许匿名用户登入###
... Success!
Normally, root should only be allowed to connect from ‘localhost‘. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y ###是否允许远程root登入####
... Success!
By default, MariaDB comes with a database named ‘test‘ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y ###是否删除测试数据库####
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y ###是否重新加载####
... Success!
Cleaning up...
All done! If you‘ve completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@server Desktop]# mysql ###现在没有指定用户就登不上###
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
[root@server Desktop]# mysql -uroot -p ###-u表示指定登入用户，-p表示此用户密码，不要在-p后直接输入密码，会被截取，不
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
二 数据库的基本sql语句操作
1）数据库的登入
mysql -uroot -p ###-u表示指定登入用户，-p表示此用户密码####
2)查询
show databases; ####显示有哪些数据库####
use mysql; ###进入mysql库#####
show tables; ###显示当前库中表的名称####
select * from user; ##查询user表中的所有内容（*可以用此表中的任何字段代替）####
desc user; ###查询user表的结构（显示所有字段名称）
过程如下：
[root@server ~]# mysql -uroot -p ####登入数据库####
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> show databases; ####显示有哪些数据库####
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> use mysql; ###进入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 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
MariaDB [mysql]> select User,Host from user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)
MariaDB [mysql]> desc user; ###查询user表的结构（显示所有字段名称）
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | 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 | |
| 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 | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
3)数据库和表的建立
1 create database westos; ###创建westos库####
2 create table linux( ####创建linux表，并且linux表中含有两个字段，username，password######
-> username varchar(15) not null， ####username字段，varchar类型的字符长度最多为255，在此处限定15，not null指不能为空###
-> password varchar(15) not null ); ###password字段，varchar类型
3 insert into linux valus (‘user1‘,‘123‘); ###在linux表格中插入数据，username字段的数据为user1，password字段为123
insert into linux values (‘user4‘,password(‘123‘)); ###在linux表格中插入数据user4，且插入password字段是用password加密过的####
4 delete from linux where password=‘0‘; ###删除linux表中password为0的数据####
过程如下：
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [mysql]> create database westos; ###创建westos库####
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use westos; ###进入westos库###
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)
MariaDB [westos]> create table linux( ####创建linux表，并且linux表中含有两个字段，username，password######
-> username varchar(15),not null, ####username字段，varchar类型的字符长度最多为255，在此处限定15
-> password varchar(15),not null );
Query OK, 0 rows affected (0.35 sec)
MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [westos]> insert into linux values (‘user3‘,‘123‘);
Query OK, 1 row affected (0.09 sec)
MariaDB [westos]> insert into linux values (‘user2‘,‘123‘);
Query OK, 1 row affected (0.09 sec)
MariaDB [westos]> insert into linux values (‘user1‘,‘123‘);
Query OK, 1 row affected (0.08 sec)
MariaDB [westos]> insert into linux values (‘user4‘,password(‘123‘));
Query OK, 1 row affected, 1 warning (0.08 sec)
MariaDB [westos]> select * from linux
-> ;
+----------+-----------------+
| username | password |
+----------+-----------------+
| user3 | 123 |
| user2 | 123 |
| user1 | 123 |
| user4 | *23AE809DDACAF9 |
+----------+-----------------+
4 rows in set (0.00 sec)
MariaDB [westos]> select * from linux;
+----------+-----------------+
| username | password |
+----------+-----------------+
| user1 | 123 |
| user2 | 123 |
| user3 | *23AE809DDACAF9 |
| user4 | 123 |
| user5 | 0 |
| user6 | 0 |
+----------+-----------------+
6 rows in set (0.01 sec)
MariaDB [westos]> delete from linux where password=‘0‘; ###删除linux表中password为0的数据####
MariaDB [westos]> select * from linux;
+----------+-----------------+
| username | password |
+----------+-----------------+
| user1 | 123 |
| user2 | 123 |
| user3 | *23AE809DDACAF9 |
| user4 | 123 |
+----------+-----------------+
4 rows in set (0.00 sec)
4）更新数据库信息
1 update linux set password=password(‘123‘) where password=‘123‘; ###更新linux表中密码为123的数据，将其全部变成加密###
2 alter table linux add class varchar(20) not null; ##alter指改变表的结构，添加class字段，字符最大长度为20且不能为空####
3 alter table linux add date varchar(20) not null; ###添加date字段到linux表的最后一列##
4 alter table linux drop date; ###删除date字段###
5 alter table linux add date varchar(20) not null after password;###添加date字段在password字段后
6 updata linux set password=password(‘456‘) where ( username=‘user1‘or username=‘user2‘ ); ###更新linux表将username字段的user1和user2的password字段为password加密的456###
过程如下：
MariaDB [westos]> select * from linux;
+----------+-----------------+
| username | password |
+----------+-----------------+
| user1 | 123 |
| user2 | 123 |
| user3 | *23AE809DDACAF9 |
| user4 | 123 |
+----------+-----------------+
4 rows in set (0.00 sec)
MariaDB [westos]> update linux set password=password(‘123‘) where password=‘123‘; ###更新linux表中密码为123的数据，将其全部变成加密###