7. Basic Mariadb (mysql) operations, 7. mariadbmysql

7. Basic Mariadb (mysql) operations, 7. mariadbmysql

1. Installation and initialization

1) Installation

Yum install-y mariadb \*

 

 

2) initialization

Systemctl restart mariadb

Systemctl enable mariadb

Mysql_secure_installation

View database version: select 'version ';

In addition to setting the password, always press "y ".

 

 

2. Database Operations

1) database check: show databases;

2) database creation: create database name;

3) delete database: drop database name;

4) database entry: use Database Name;

 

 

 

3. Create a table

(1) Understanding Common Data Types

Date and Time Data Types

 

MySQLData Type	Description
Date	3Byte, date, format:Septem
Time	3Byte, time, format:08:42:30
Datetime	8Byte, date and time, format:08:42:30
Timestamp	4Bytes, the time when the record is automatically stored
Year	1Byte, year

Integer

MySQLData Type	Meaning (Signed)
Tinyint	1Byte, range (-128 ~ 127)
Smallint	2Byte, range (-32768 ~ 32767)
Mediumint	3Byte, range (-8388608 ~ 8388607)
Int	4Byte, range (-2147483648 ~ 2147483647)
Bigint	8Byte, range (+-9.22*10Of18Power to power)

Bigint

8Byte, range (+-9.22*10Of18Power to power)

Floating Point Type

MySQLData Type	Description
Float (m, d)	4Byte, single-precision floating point type,MTotal number,DDecimal places
Double (m, d)	8Byte, double-precision floating point type,MTotal number,DDecimal places
Decimal (m, d)	DecimalIs the floating point number stored as a string

String Data Type

MySQLData Type	Description
Char (n)	Fixed Length, Max255Characters
Varchar (n)	Variable Length, maximum65535Characters
Tinytext	Variable Length, maximum255Characters
Text	Variable Length, maximum65535Characters
Mediumtext	Variable Length, maximum2Of24Power-1Characters
Longtext	Variable Length, maximum2Of32Power-1Characters

 

(2) create table Name

(

Column name 1 data type,

Column name 2 data type,

Column name 3 Data Type

);

Instance: create table list

(

Id int,

Name varchar (50 ),

Passwd varchar (100)

);

 

 

 

4. view the table data structure: desc table name;

 

5. query the table details in the database; show tables ;!!! (Before checking, you need to enter the corresponding database use name ;)

 

6. delete a table: drop table name;

 

 

7. modify a table

(1) rename a table:

Alter table old table name rename new table name;

(2) Add a column to the table:

Alter table name add column name data type to be added;

(3) Delete a column in the table:

Alert table Name drop column name deleted;

(4) modify the Data Type of a column:

Alter table name modify column name data type;

(5) rename a column:

Alter table name change column old column name new column name data type;

 

 

8. insert statements into the table

(1) Insert a record to all columns in the table:

Insert into table name values (value 1, value 2, value 3 );

Example: insert into name values (99, 'hangsan', 'hangsan-passwd ');

(2) Insert a record into a specified column:

Insert into Table Name (column 1, column 3) values (value 1, value 3 );

Example: insert into name (username, password) values (lisi, lisi-passwd );

 

 

9. query data

(1) query data records from tables:

Query the data records of all columns in the Table: select * from table name;

Example: select * from name;

(2) query the data records of the specified columns in the Table: select column name 1, column name 2, column name 3 from table name;

Example: select username, password from name;

 

10. query data by conditions

Select column name from table name where specifies the column operator value. (if the conditions are met, a row of the specified table is listed! "*" Indicates all !)

Example: select * from name where id = 3;

Select username, password from name where id = 3;

Select username, password from name where id> 3;

Select username, password from name where id <3;

Select username, password from name where id <> 3; not equal

Select username, password from name where id> = 3;

Select username, password from name where id <= 3;

11. delete a record:

(1) delete all records in the Table: delete * from Table Name:

Example: delete * from name;

(2) delete the specified record in the Table: delete from table name where column name operator value:

Example: delete from name where id = 3 ;! Note that single quotes are required for strings!

 

12. Update a record

(1) update a record from the table: update table name set column name = new value where column = value;

(2) lost mysql administrator password retrieval:

1. Shut down the database: systemctl stop mariadb

2. mysqld_safe -- skip-grant-tables -- user = mysql &

3. go to the mysql database and change the user password in the database: update mysql. user set password = password ("new password") where user = 'root' and host = 'localhost'; note! After updating, use flush privileges to refresh the database and release \ q.

4. mysqladmin-u root-p shutdown !! Enter the Updated Password here and restart the database: systemctl restart mariadb

Formula: update name set variable name = 'value' where judgment condition and judgment condition;

 

13. Remove duplicate items from returned results:

Select distinct column name from table name;

Example: select distinct username from name;

 

14. Use logical combinations in the where condition:

Select * from table name where column name 1 = 'value 1' and column name 2 = 'value 2'; the two conditions before and after 'and' must be true

Select * from table name where column name 1 = 'value 1' or column name 2 = 'value 2'; a condition before or must be met = true

Example: select * from name where username = zhangsan and id = 1;

Select * from name where username = zhangsan or id = 0;

 

15. Sort the query results:

(1) sort from small to large: select * from table name order by column name;

Example: select * from name order by id;

(2) sort from large to small: select * form table name order by column name; desc; note that desc indicates "Inverted" here!

Example: select * from name order by id desc;

 

16. mariadb user management

(1) create a mariadb Database User:

Create user Username identified by 'Password ';

Example: create user Luigi identified by 'redhat-passwd ';

! Use flush privileges; Refresh!

(2) Delete A user: drop user Username;

Example: drop user Luigi;

! Use flush privileges; Refresh!

(3) rename the user: rename the original user name to the new user name;

Example: rename user Luigi to Natasha;

! Use flush privileges; Refresh!

(4) change the user password:

① Change the current user PASSWORD: set PASSWORD = PASSWORD ('new password ');

For example, set PASSWORD = PASSWORD ('redhat-passwd ');

! Use flush privileges; Refresh!

② Change the PASSWORD of the specified user: set PASSWORD for username = PASSWORD ('new password ');

Example: set PASSWORD for zhangsan = PASSWORD ('zhangsan-passwd ');

! Use flush privileges; Refresh!

 

17. permission management

(1) Permission classification: 1. Check whether the user can connect to the host.

2. Are you authorized to operate the database.

(2) authorization level: 1. Global Level

2. Database level

3. Surface Level

4. Column level

5. subroutine level

(3) use the grant command to grant user permissions and use revoke to revoke User Permissions

① Grant a user permission grant permissions on the level to 'username '@ 'hostname 'identified by 'Password ';

For example, grant Luigi all the management permissions on all databases: grant all on *. * to 'lugi' @ '%' identified by 'redhat ';

! Use flush privileges; Refresh!

Grant Luigi local query permission on the centos Database: grant select on centos. * to 'lugigi' @ 'localhost' identified by 'redhat ';

! Use flush privileges; Refresh!

② Revoke the permission of a user: revoke all privileges from user name;

Example: revoke all privileges from Luigi;

! Use flush privileges; Refresh!

(4) host connection authentication: grant all privileges on *. * to 'user' @ 'hostname 'identified by 'Password ';

For example, grant all privileges on *. * to 'lugigi' @ '* .example.com 'identified by 'redhat ';

! Use flush privileges; Refresh!

 

18. Simple backup and recovery:

(1) Back up a specified database: mysqldump-u root-p Database Name>/backup path/backup file

Example: mysqldump-u root-p rhce>/root/rhce. SQL

(2) restore a database: mysql-u root-p database name </backup path/Database Name

Example: mysql-u root-p rhce </root/rhce. SQL