Basic operation of MariaDB

1. Create a database

MariaDB [(none)]> CRRATE DATABASE test1; #Create a database named test1 MariaDB [(none)]> CRRATE DATABASE IF NOT EXISTS test2; #Create a database named test2 MariaDB [(none)]> SHOW ASES; #Show existing databases

2. Delete database

MariaDB [(none)]> DROP DATABASE test1; #delete database named test1 MariaDB [(none)]> DROP DATABASE IF EXISTS test1;

3. Create a table

Format: CREAT TABLE tb_name(col1, col2, col3,...);

MariaDB [(none)]> CREATE TABLE test2.teacher(Name CHAR(20) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL); #create table in test2 teacherMariaDB [(none)]> USE test2;MariaDB [(test2)]> CREATE TABLE student(Name CHAR(20) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL); #Another way to create a table studentMariaDB [(test2)]> SHOW TABLES FROM test2; #View tables in database test2

4. View table structure

MariaDB [(test2)]> DESCRIPTION student;MariaDB [(test2)]> DESC teacher; #DESCRIPTION can be abbreviated as DESC

5. Delete table

MariaDB [(test2)]> DROP TABLE teacher;MariaDB [(test2)]> DROP TABLE IF EXISTS teacher;

6. Amendment table

MariaDB [(test2)]> ALTER TABLE student ADD course VARCHAR(80); #add a field courseMariaDB [(test2)]> DESC student;MariaDB [(test2)]> ALTER TABLE student CHANGE Course VARCHAR(80) AFTER Name; #Change course to Course and put it after Name MariaDB [(test2)]> DESC student;

7. Insert data information

MariaDB [(test2)]> INSERT INTO student (Name，Gender) VALUE ('Tom','M'),('Jerry','F');MariaDB [(test2)]> SELECT * FROM student;MariaDB [(test2)]> INSERT INTO student VALUE ('Jack','Math',16,'M');

8. Modify and update data information

MariaDB [(test2)]> UPDATE student SET Course='Physics' WHERE Name='Tom'; #Insert PhysicsMariaDB [(test2)]> SELECT Name,Course From student WHERE Course ='Physics 'into the Course field of the row named Tom; #Select Name and CourseMariaDB [(test2)]> Delete From student Where Coures='Physics'; #Delete all rows where lessons are physical

9. Create and delete users

CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

DROP USER 'username'@'host';

MariaDB [(none)]> CREATE USER 'Lucy'@'localhost' INDENTIFIED BY '123456';MariaDB [(none)]> SHOW GRANTS FOR 'Lucy'@'localhost'; #View user's authorizations MariaDB [(none)]> CREATE USER 'Lily'@'192.168.1.50'MariaDB [(none)]> CREATE USER 'Bob'@'%' #where % is a wildcard, indicating any number of characters, underscore_indicates any single character

10. Authorization

GRANT pri1,pri2,pri3,... ON dbname.tbname TO 'username'@'host' [IDENTIFIED BY 'password'];

REVOKE pri1,pri2,pri3,... ON dbname.tbname FROM 'username'@'host';#Remove permissions

MariaDB [(none)]> GRANT ALL PRIVILEGES ON test2.* TO 'Lucy'@'%'; #Grant user 'Lucy'@'%' full permissions on all tables in test2 library MariaDB [(none)]> SHOW GRANTS FOR 'Lucy'@'%';