Linux 数据库的安装(mariadb)及基础操作

什么是mariadb:

MariaDB是一个开源的关系型数据库管理系统，它是MySQL数据库的一个分支。MariaDB由MySQL的原开发者创建，目的是为了提供一个更加开放、稳定和高性能的数据库解决方案。

MariaDB拥有与MySQL相似的特性和语法，可以无缝替换MySQL而不需要修改现有的应用程序。它支持多种操作系统，包括Windows、Linux和Mac OS等。MariaDB也提供了一些新的功能和改进，例如更快的查询性能、更好的安全性、更好的扩展性等。

MariaDB的特点包括：

1. 开源：MariaDB是一个开源项目，任何人都可以访问和修改源代码。

2. 兼容性：MariaDB与MySQL兼容，可以无缝替换MySQL而不需要修改现有的应用程序。

3. 高性能：MariaDB通过使用更快的查询优化算法和索引技术，提供更好的查询性能。

4. 安全性：MariaDB提供了更多的安全功能，如加密、访问控制和审计等，以保护数据库的安全性。

5. 扩展性：MariaDB支持水平扩展和垂直扩展，可以根据需求灵活地扩展数据库的容量和性能。

总的来说，MariaDB是一个功能强大、高性能、可扩展和安全的关系型数据库管理系统，适用于各种规模和类型的应用程序。

在Linux里安装数据库

[root@yigeling ~]# yum install mariadb-server mariadb -y

查询是否安装成功

[root@yigeling ~]# rpm -qa |grep mariadbmariadb-libs-5.5.68-1.el7.x86_64mariadb-5.5.68-1.el7.x86_64mariadb-server-5.5.68-1.el7.x86_64

启动服务及停止服务命令

[root@yigeling ~]# systemctl start mariadb-serverFailed to start mariadb-server.service: Unit not found.[root@yigeling ~]# systemctl start mariadb查看是否启动[root@yigeling ~]# ps -ef |grep mariadbmysql4815 46491 10:09 ?00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sockroot 4851 44030 10:09 pts/000:00:00 grep --color=auto mariadb停止服务[root@yigeling ~]# systemctl stop mariadb[root@yigeling /]# ps -ef| grep mariadbroot 101626 1004450 21:34 pts/100:00:00 grep --color=auto mariadb

设置开机自启

[root@yigeling ~]# systemctl enable mariadbCreated symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

管理服务的配置文件

/usr/lib/systemd/system/mariadb.service.编辑这个目录下的配置文件xxx.service

连接mysql命令

[root@yigeling system]# mysqlWelcome to the MariaDB monitor.Commands end with ; or \g.Your MariaDB connection id is 1Server version: 5.5.68-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)]>

查看当前有哪些数据库:show databases;

# MariaDB [(none)]> show databases;# +--------------------+# | Database |# +--------------------+# | information_schema |# | mysql|# | performance_schema |# | test |# +--------------------+# 4 rows in set (0.00 sec)

使用数据库

MariaDB [(none)]> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase 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 * from user;

接\G即可竖着展示：

MariaDB [mysql]> select * from user \G;

删除某个字段

删除user表里面User字段为空的行MariaDB [mysql]> delete from user where User="";Query OK, 2 rows affected (0.00 sec)

创建新用户并且给它授权：

授权并创建用户，所有库所有表的所有权限给sc用户，可以从任何客户端登录，密码为123456MariaDB [mysql]> grant all privileges on *.* to 'sc'@'%'identified by '123456';Query OK, 0 rows affected (0.00 sec)对权限做了改动最好刷新一下权限riaDB [mysql]> flush privileges;Query OK, 0 rows affected (0.00 sec)

验证用户是否创建成功，能否成功登录

[root@yigeling system]# mysql -u sc -pEnter password:Welcome to the MariaDB monitor.Commands end with ; or \g.Your MariaDB connection id is 3Server version: 5.5.68-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)]>

建库

MariaDB [mysql]> create database sc;Query OK, 1 row affected (0.01 sec)MariaDB [mysql]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql|| performance_schema || sc || test |+--------------------+5 rows in set (0.01 sec)

建表

create table StuInfo (id int primary key auto_increment,name varchar(128),age int8,address varchar(256))default charset utf8;

查看表结构

MariaDB [sc]> desc StuInfo;+---------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra|+---------+--------------+------+-----+---------+----------------+| id| int(11)| NO | PRI | NULL| auto_increment || name| varchar(128) | YES| | NULL||| age | bigint(20) | YES| | NULL||| address | varchar(256) | YES| | NULL||+---------+--------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

查询表

MariaDB [sc]> select * from StuInfo;Empty set (0.00 sec)

插入数据

MariaDB [sc]> insert into StuInfo(name, age, address) values("sc", 8, "长沙");Query OK, 1 row affected (0.00 sec)MariaDB [sc]> select * from StuInfo;+----+------+------+---------+| id | name | age| address |+----+------+------+---------+|1 | sc |8 | 长沙|+----+------+------+---------+1 row in set (0.00 sec)

查询指定的数据

MariaDB [sc]> select name,age from StuInfo;+------+------+| name | age|+------+------+| sc |8 || sc1|9 || sc2| 10 || sc3| 15 |+------+------+4 rows in set (0.00 sec)MariaDB [sc]> select * from StuInfo where age > 10;+----+------+------+---------+| id | name | age| address |+----+------+------+---------+|4 | sc3| 15 | 武汉|+----+------+------+---------+1 row in set (0.00 sec)# MariaDB [sc]> select * from StuInfo where address = "长沙";# +----+------+------+---------+# | id | name | age| address |# +----+------+------+---------+# |1 | sc |8 | 长沙|# |2 | sc1|9 | 长沙|# +----+------+------+---------+# 2 rows in set (0.00 sec)

修改数据

# 修改数据 --把StuInfo 这个表中 名字为sc的年龄改为10# MariaDB [sc]> update StuInfo set age = 10 where name = "sc";# Query OK, 1 row affected (0.00 sec)# Rows matched: 1Changed: 1Warnings: 0