Mariadb DML statement and user authorization, mariadbdml

Mariadb DML statement and user authorization, mariadbdml

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

INSERT [INTO] tbl_name [(col1,...)] {VALUES | VALUE} (val1 ,...),(...),...

 

Suppose there is a table above

Insert a row of data: insert students values (1, 'xijinping', 51, 'zhonglanhai', 'M'); the string must be enclosed by quotation marks, and the number cannot be enclosed by quotation marks.

Insert multiple rows of data simultaneously: insert students (id, name) values (2, 'hujingta'), (3, 'jiangzeming ');

 

SELECT:

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

View only the specified field: select id, name from students;

Take the field id as an alias: select id as stuid, name from students;

WHERE clause: used to specify the selection criteria

Col_name operator value, for example, age> 30

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

Example: select * from students where id = 1;

Select * from students where name = 'jiangzemin'; Note: The following strings must be enclosed in quotation marks. Whether the strings are case sensitive depends on the character type.

Binary or varbinary is case sensitive, while char or varchar is case insensitive.

 

Combination conditions: or, and, not

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

Select name, age from students where age between 30 and 50; keep up with the preceding statement

Operator: BETWEEN... AND... LIKE 'pattern' RLIKE 'pattern' (Regular Expression matches string PATTERN) IS NULL

IS NOT NULL

Select name from students where name like '% ji %'; note that the query method like rlike has very low performance.

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

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

Select name, age from students where age is null;

Select id, name from students order by name; sort by the first letter in the name field. The default value is ascending. Add desc to descending order.

Note that the DML statements are executed first by the select query.

　　

DELETE: the deletion is Row-specific.

(1) delete from tbl_name WHERE where_condition

(2) delete from tbl_name [order by...] [LIMIT row_count]

Delete from students; (delete all rows in the table)

Delete from students where age is null;

Tip 1. quickly create tables with the same structure, including indexes: MariaDB [test1]> create table a like students;

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

　　　　

[Root @ localhost ~] # For I in {1 .. 100}; do AGE = $ [$ RANDOM % 100]; mysql-e "insert test1.a (id, name, age) values

($ I, \ "stu $ I \", $ AGE); "; done adds some data to Table.

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 permission management:

User Account: 'username' @ 'host'

Do not check the Host Name: Add skip_name_resolve = ON to the [mysqld] section in the my. cnf configuration file. Generally, you must add this option to improve performance.

CREATE a USER account: create user 'username' @ 'host' [identified by 'Password'];

For example, create user 'testuser' @ '192. 192. %. % 'identified by 'testpass'; refresh the user authorization table flush privileges;

Delete USER Account: DROP user 'user' @ 'host' [, user @ host]...

Example: drop user 'testuser' @ '192. 192. %. % ';

View the user authorization table command: select * from mysql. user \ G;

　　　　

Authorization: GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user' @ 'host' [identified by 'Password'];

Example: grant select, insert on test1.students to 'testuser' @ '192. 192. %. % ';

 

View the authorization obtained by the specified user: show grants for 'user' @ 'host'; show grants for CURRENT_USER;

REVOKE permission: REVOKE priv_type,... ON db_name.tbl_name FROM 'user' @ 'host ';

Example: revoke insert on test1.students from 'testuser' @ '192. 192. %. % ';

 

Note: When the MariaDB service process is started, all the authorization tables of the mysql database will be read to the memory.

(1) permissions executed by GRANT or REVOKE commands will be stored in the table. MariaDB will generally re-read the authorization table automatically at this time, and the permission modification will take effect immediately.

(2) For permission modification implemented in other methods, you must manually run the flush privileges command to take effect.

 

After mariadb is installed, the password is empty. This is insecure;

 

You can use this command to set the root password: [root @ localhost ~] # Mysql_secure_installation