Mariadb learning summary (4): data insertion, deletion and modification

INSERT insert data

INSERT statement format:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
 [ ON DUPLICATE KEY UPDATE
   col=expr
     [, col=expr] ... ]

The table structure is as follows:

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)

Insert a single piece of data

MariaDB [mydb]> INSERT INTO user VALUES(1,'test','test',NOW(),NOW(),'127.0.0.1');

Because in many cases some values ​​are default, so we can specify which columns to insert data for, while other columns use default values, as follows:

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test2','test2');

Similarly, inserting specific column data can also be written like this:

MariaDB [mydb]> INSERT INTO user SET username='test3',password='test3';

This will only insert the username and password, and use the default values ​​for other values.

MariaDB [mydb]> SELECT * FROM user;
+----+----------+----------+---------------------+---------------------+-----------+
| id | username | password | regtime             | logtime             | logip     |
+----+----------+----------+---------------------+---------------------+-----------+
|  1 | test     | test     | 2018-02-24 15:43:41 | 2018-02-24 15:43:41 | 127.0.0.1 |
|  2 | test2    | test2    | 2018-02-24 15:45:16 | 0000-00-00 00:00:00 | NULL      |
|  3 | test3    | test3    | 2018-02-24 15:46:56 | 0000-00-00 00:00:00 | NULL      |
+----+----------+----------+---------------------+---------------------+-----------+
3 rows in set (0.00 sec)

Insert multiple rows of data

Many times we have the need to use one INSERT statement to insert multiple records into the data table. We can write it like this:

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test4'),('test5',
    -> 'test5');

Inexplicable priorities?

When the storage engine used (MyISAM, MEMORY, MERGE) uses table-level locks, you can use the two keywords LOW_PRIORITY | HIGH_PRIORITY:
When using the LOW_PRIORITY keyword, data is written when no client reads the table again.
When HIGH_PRIORITY is used, the INSERT statement has the same priority as the SELECT statement. (default policy)

So, when a SELECT statement is executed before the INSERT statement is executed, the INSERT blocks and waits for the SELECT to be read, but at this time, if a SELECT enters the schedule again, the SELECT is blocked (the read lock can be read directly), but at this time because The INSERT statement has the same priority as the SELECT statement, so SELECT cannot be executed until the INSERT is completed, so INSERT can add LOW_PRIORITY to optimize the reading speed.

Read it slowly here, I don’t know much about locks.

Modify data

UPDATE statement syntax is as follows:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
  [PARTITION (partition_list)]
  SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]

Update all

When the WHERE clause is not used to constrain the selection conditions, all data is updated. For example, the login time of all records in the user table is modified to be now:

MariaDB [mydb]> UPDATE user SET logtime=NOW();
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

Update the login IP of the first three registered people to 127.0.0.1

MariaDB [mydb]> UPDATE user SET logip='127.0.0.1' ORDER BY regtime LIMIT 3;

The ORDER BY statement can be used in SELECT UPDATE DELETE, etc., to indicate which field arrangement of the table should be followed when outputting, deleting, and updating the table.
For example, in the above, ORDER BY regtime is to sort and update in order according to the registration time, and only the first three rows are updated with the LIMIT statement.

Use DESC to specify the flashback arrangement, for example: ORDER BY regtime DESC

LIMIT statement: used to limit the number of query results.
Usage:

LIMIT[位置偏移量,]行数

The first line starts at 0, so the following:

SELECT * FROM user LIMIT 2,2;  //从第3行开始，取两行，即取第3、4条记录。

Use WHERE statement to select specific rows for update

MariaDB [mydb]> UPDATE user SET logip='192.168.1.2' WHERE username='test2';

Because there are many things in the WHERE clause, I won’t post too much content here.

LOW_PRIORITY: This is the same as LOW_PRIORITY of INSERT.

REPLACE statement

MariaDB [mydb]> REPLACE INTO user VALUES(1,'test111','test111',NOW(),NOW(),'192.168.1.1');

The above statement is MariaDB's extended SQL, which is equivalent to deleting duplicate (primary key or unique index) records and adding new records.

It seems a bit tasteless? ....

Delete data

DELETE语语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 
    FROM tbl_name [PARTITION (partition_list)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
    [RETURNING select_expr 
      [, select_expr ...]]

删除所有数据

不使用WHERE语句来约束条件时，则删除所有数据，如下：

MariaDB [mydb]> DELETE FROM user;

使用WHERE语句约束选中行

MariaDB [mydb]> DELETE FROM user WHERE username='test1';

那么？这篇文章就简单写到这吧~~

The above is the detailed content of Mariadb learning summary (4): data insertion, deletion and modification. For more information, please follow other related articles on the PHP Chinese website!