MARIADB DML statements and user authorization

DML (Data manipulation Language): INSERT, DELETE, UPDATE, SELECT

INSERT [into] tbl_name [(col1,...)] {values| VALUE} (Val1, ...), (...),...

If there's this watch above

Insert a row of data:Insert students values (1, ' xijinping ', Wuyi, ' Zhonglanhai ', ' m '); Strings must be quoted, numbers cannot be quoted

Inserting multiple rows of data simultaneously:Insert students (ID,NAME) VALUES (2, ' Hujingtao '), (3, ' jiangzeming ');

SELECT:

View all fields of a table:select * from students;

View only the specified fields:select Id,name from students;

Take the field ID to the individual name: Select ID as stuid,name from students;

WHERE clause: Used to indicate selection criteria

Col_name operator value, for example: Age > 30

Operator (1): <, >=, <=, = =,! =

For example: SELECT * from students where id=1;

SELECT * from students where name= ' jiangzemin '; Note: The following string is quoted, whether case-sensitive, depending on the character type, if it is

Binary or varbinary are case-sensitive, char or varchar do not need to be case sensitive

Combination condition: Or,and,not

Select Name,age from students where age>=30 and age<=50;

　　Select Name,age from students where age between and; With the above statement is a meaning

Operator: Between ...  　　And ... Like ' pattern ' rlike ' pattern ' (regular expression for string pattern matching) is NULL

is not NULL

　　　select name from students where name is like '%ji% '; Note that the query method like Like,rlike is extremely low performance

%: Any character of any length _: any single character

　　　　Select name from students where name Rlike ' min$ ';

Select Name,age from students where-is null;

　　　　Select Id,name from students order by name; Sort by the first letter in the Name field, default ascending, and desc to descending

Note that the EXECUTE DML statement first executes the select query

　　

Delete: The deletion is relative to the row

(1) DELETE from Tbl_name WHERE where_condition

(2) DELETE from Tbl_name [ORDER by ...] [LIMIT Row_count]

　　　　 Delete from students; (Delete all rows of the table)

　　　　　Delete from students where-is null;

Tips: 1. Quickly create tables of the same structure, including indexes: MariaDB [test1]> CREATE Table a like students;

2. Quickly create a table of the same structure, but do not create an index:MariaDB [test1]> CREATE TABLE B select * from students limit 0;

　　　　

[[email protected] ~]# for i in {1..100}; Do age=$[$RANDOM%100]; MYSQL-E "Insert test1.a (id,name,age) values

($i, \ "Stu$i\", $AGE); "; Done for a This table adds some data

　　　　Delete from a order BY age DESC limit 20;

UPDATE:

　　　　Update a set age=age-5 ORDER by id desc limit 10;

　　　Update a set age=age-5 where name not like ' stu__ ';

User account and Rights management:

User account: ' username ' @ ' host '

Disable Check host name: Add skip_name_resolve = On in the [Mysqld] segment in the MY.CNF configuration file to improve performance

Create user account: ' username ' @ ' host ' [identified by ' Password '];

For example: Create user ' testuser ' @ ' 192.168.%.% ' identified by ' testpass '; Remember to refresh the user authorization form flush privileges;

Delete user account: Drop users ' user ' @ ' host ' [, [email protected]] ...

For example:drop user ' testuser ' @ ' 192.168.%.% ';

View user Authorization Form commands: SELECT * from Mysql.user\g;

　　　　

Authorization: Grant Priv_type,... On [object_type] db_name.tbl_name to ' user ' @ ' host ' [identified by ' Password '];

For example:Grant Select,insert on test1.students to ' testuser ' @ ' 192.168.%.% ';

View the authorization granted by the specified user: Show GRANTS for ' user ' @ ' host ', show GRANTS for Current_User;

Reclaim permissions: REVOKE Priv_type, ... On db_name.tbl_name from ' user ' @ ' host ';

For example: Revoke insert on test1.students from ' testuser ' @ ' 192.168.%.% ';

Note: When the MARIADB service process starts, all authorization tables from the MySQL library are read into memory

(1) Permission actions such as GRANT or revoke commands are saved in the table, mariadb the authorization form is normally reread at this time, and permission changes take effect immediately

(2) Other ways to implement the permission changes, to be effective, you must manually run the flush privileges command

MARIADB installed after the password is empty, it is not safe to see;

You can use this command to complete the root password setting: [[email protected] ~]# mysql_secure_installation

　　　

　　　　

　　　　

　　　　

　　　　

　　　　　　

MARIADB DML statements and user authorization