“This is the 24th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

mariadb&mysql

The difference between Mariadb and mysql is that Mariadb is not just an alternative to mysql, which is already closed by Oracle. Mariadb is open source, and mariadb includes some new features that make it better than mysql. MariaDB is compatible with MySQL in most respects, and for developers, it will hardly feel any different. Maria DB is a branch of MySQL using Aria storage engine, and MySQL is the most popular relational database management system, in WEB applications MySQL is one of the best RDBMS applications. MariaDB is also the fastest growing MySQL branch release, with new releases faster than Oracle’s official MySQL release.

The environment

Because I use kali2020 system, I cannot use mysql directly, but use mariadb, its substitute product. This operation is basically consistent with mysql. Currently, SQL is closed source by Orcle while Mariadb is open source and may even be better than mysql in some respects. And, to the delight of Kali users, Mariadb is installed by default. You only need to turn on the server.

Service is open

systemctl start mariadb.service
Copy the code

Close the service

systemctl stop maraidb.service
Copy the code

Restart the service

systemctl restart mariadb.service
Copy the code

User login We started with the default root user, which is also the superuser with the highest privileges. We need to log in to it.

mysql -uroot -p
Copy the code

Then enter your Kali login password, but of course you can enter it directly

mysql
Copy the code

By default, you can log in to user root without entering a password.

User creation

The format of the basic directive created is as follows:

create users 'usrname'@'Specify user login address' identified by'password'; # query userselect * from mysql.user;
Copy the code

Such as:

create users 'Hello'@'localhost' identified by'abc123'; Here,'localhost'Is the machine IPThe '%'Is any IP addressCopy the code

You can do this if you want to create a user that you can connect to remotely

create users 'Hello'@The '%' identified by'abc123';
Copy the code

Change a user’s password If you forget your password, you can log in to the database using another account that has sufficient permissions to change that account.

set password for 'Hello'@The '%' = password('abcd123');
Copy the code

The user to delete

drop usr 'Hello'@The '%';
Copy the code

User permissions

When we create a user we can pass

show grants for 'Hello'@'localhost';
Copy the code

View for example; Permission to modifyFirst of all, what are the permissions;SELECT INSERT CREATE DELECT DROP UPDATE CRANT OPITONThe same permissions given to other users (the right to be someone else’s father) now we give it the right except CREATEOPTION

grant all privileges on *.* to Hello@localhost;
Copy the code
revoke GRANT OPTION on *.* from Hello@localhost;
Copy the code
flush privileges; Make the configuration workCopy the code

Don’t panic here, this Hello user can’t be someone else’s baba.

The database

Database Viewing

show databases;
Copy the code

The rough structure of the databaseEach table is similar to an Excel table, with columns Switching to a database

use Name;
Copy the code

Creating a database

create database Name charset=utf8;
Copy the code

Create a table

create table Name(id int primary key auto_increment,name varchar(128),age int)charset=utf8;
Copy the code

I’m going to change create to drop and it’s a little bit more complicated when you’re creating a table, because what’s inside the parentheses is actually that field, that little column title that you had when you were doing Excel. The format is name type condition such as age int unique

Table field modification

(Table A is used as an example.) View the table creation

show create table A;
Copy the code

Field addition

alter table Name addName type condition;alter table A add ctl int unique;
Copy the code

You can also specify the location

alter table A add ctl int uniquefrist; The first onealter table A add ctl int uniqueafter name; After nameCopy the code

Changing the field name

This can change the name and properties:alter table A change ctl height int;

Modify the attributes of a field

alter table A modify height varchar(128);

Copy the code

Field to delete

alter table A drop ctl;
Copy the code

The movement of the table

alter tableTable name rename database (another). Table name;Copy the code
alter tableA renaem Another database. B;Copy the code

Move table A to another database named A(or B)

SQL > alter database character set show char set SQL > alter database character set Modification:

alter database Name default character set utf8 collateThe character set.Copy the code

Such as:

alter database Hello default character set utf8 collate utf8_general_ci;
Copy the code

This is similar to modifying a database

alter table A default character set utf8 collate utf8_general_ci;
Copy the code

Simple add, delete, change and check

Add:

insert into A(name,age,ctl) values('jack'.18.192);
Copy the code

Since ID is an incremented primary key, you don’t have to add it, or you can add it. Example :(select * from table)Check: This is used with where and having statements for example

select name,age from A;
select age from A where name='make';
Copy the code

Change: for example, change KKKKK’s name to Jack

update A set name='jack' where name='kkkkk';
Copy the code

Delete: for example, delete Jack

delete from A where name='jack';
Copy the code

If you do not add where, the contents of the table will be deleted.In practice, lookups are the most frequently used, and the next article focuses on lookups.