Getting Started with Linux Engineer learning------MARIADB

1. mariadb Database

Database: Databases

What is a database

DB, Database: A collection of data, a mainstream database used to store relational tabular data

1.1 Virtual Machine Server: Installing the MARIADB Database

[Email protected] ~]# yum-y install Mariadb-server

MARIADB database, Port:3306

1.2 Starting the database service

[Email protected] ~]# systemctl restart MARIADB

[Email protected] ~]# Systemctl enable MARIADB

[[Email protected] ~] #netstate-ANPTU | grep 3306 #查看是否启动

MARIADB Database Basic Operations

[email protected] ~]# MySQL #默认没有密码

MariaDB [(None)]> show databases; #查看所有库, notice the end of the semicolon

MariaDB [(None)]> CREATE Database nsd1710; #创建库

MariaDB [(None)]> show databases;

MariaDB [(None)]> drop database nsd1710; #删除库

MariaDB [(None)]> show databases;

MariaDB [(none)]> quit

1.3 Setting the password for the MARIADB database administrator

Database administrator name: root mariadb database mysql----->user

System administrator Name: ROOT/ETC/PASSWD

mysqladmin [-u user name] [-p[old password]] password ' new password '

[Email protected] ~]# mysqladmin-u root password ' 123 ' #设置数据库密码

[Email protected] ~]# mysql-u root-p #交互式设置

Enter Password: enter password

[Email protected] ~]# mysql-u root-p123 #非交互输入密码进入

1.4 No monitoring, only for native

[Email protected] ~]# VIM/ETC/MY.CNF

[Mysqld]

skip-networking //Skip network snooping, the database can only be accessed by localhost

.. ..

[Email protected] ~]# systemctl restart MARIADB

– Using/Selecting the database: Use database name;

– List What tables are in the library: SHOW TABLES;

[Email protected] ~]# mysql-u root-p123

MariaDB [mysql]> show databases;

MariaDB [mysql]> use MySQL;

MariaDB [mysql]> Show tables;

MariaDB [mysql]> CREATE DATABASE nsd1710;

MariaDB [mysql]> show databases;

1.5 importing data into the database

operation on virtual machine SERVER0: Download the database files backed up in advance

wget Http://classroom/pub/materials/users.sql

[Email protected] ~]# mysql-u root-p123 nsd1710 < Users.sql

[Email protected] ~]# mysql-u root-p123

MariaDB [(None)]> use nsd1710;

MariaDB [nsd1710]> Show tables;

1.6 Querying the table records in the database

Select table field from library. Table name

SELECT * from Nsd1710.base;

MariaDB [nsd1710]> SELECT * from location;

MariaDB [nsd1710]> SELECT * from base;

Add insert

Delete Delete

Change update

Check Select

1.7 Conditional queries

1. In the base table, query the name of the user whose password is 123?

[Email protected] ~]# mysql-u root-p123

MariaDB [(None)]> use nsd1710;

MariaDB [nsd1710]> Show tables;

> select * from base where password= ' 123 ';

> select Name,password from base where password= ' 123 ';

> select name from base where password= ' 123 ';

> select Id,name from Base;

and use the appropriate SQL query to answer the following questions:

1) password is the name of the person who solicitous?

> select name from base where password= ' solicitous ';

2) How many people's names are Barbara while residing in Sunnyvale?

> select * from base,location where base.name= ' Barbara ' and location.city= ' Sunnyvale ' and base.id=location.id ;

> select COUNT (*) from base,location where base.name= ' Barbara ' and location.city= ' Sunnyvale ' and Base.id=locat Ion.id;

> Insert Base VALUES (6, ' Barbara ', ' 321 '); #插入表记录

> Insert Location VALUES (6, ' Sunnyvale '); #插入表记录

> select * from base;

> select * from location;  

> select * from base,location where base.name= ' Barbara ' and location.city= ' Sunnyvale ' and base.id=location.id;

1.8 Database Authorization

– In addition to the root user, this nsd1710 database can only be queried by the user Lisi, the password for this user is 123

MARIADB Database MySQL----->user

–grant permissions list on database name. Table name to User name @ Client address identified by ' password ';

[Email protected] ~]# mysql-u root-p123

Grant Select on nsd1710.* to [e-mail protected] identified by ' 123 ';

When Lisi logs in from localhost, enter the password 123. You will get permission to query all tables in the nsd1710 library

Verification: Test Lisi Login

[Email protected] ~]# mysql-u lisi-p123

1.9DELETE Delete Table records

? MariaDB [(none)]> Interactive Instruction

–delete from [Database.] Table name WHERE condition statement;

1. Disable null password root user access to MARIADB database

[Email protected] ~]# mysql-u root-p123

> Use MySQL;

> select User,host,password from User;

> select User,host,password from user where password= ';

> Delete from user where password= ';

> select User,host,password from User;

Refresh the record of the user table:

MariaDB [(None)]> flush privileges;

Getting Started with Linux Engineer learning------MARIADB