Shulou(Shulou.com)06/01 Report--

This article mainly explains the "Mariadb data insertion, deletion and modification methods", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "Mariadb data insertion, deletion and modification methods"!

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-0000: 00:00 | | logip | varchar (20) | YES | | NULL | | +-| -+-+ 6 rows in set (0.01sec) insert single data MariaDB [mydb] > INSERT INTO user VALUES (1) 'test','test',NOW (), NOW (),' 127.0.0.1')

Because many times some values are default, 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 as follows:

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

In this way, only the user name and password are inserted, and the other values use the default.

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-0000: 00:00 | NULL | | 3 | test3 | test3 | 2018-02-24 15:46:56 | 0000-00-0000: 00:00 | NULL | +- -+-+ 3 rows in set (0.00 sec) insert multiple rows of data

Many times we have the need to insert multiple records into a data table with a single INSERT statement, so we can write:

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

The two keywords LOW_PRIORITY | HIGH_PRIORITY can be used when the storage engine (MyISAM, MEMORY, MERGE) uses table-level locks: when the LOW_PRIORITY keyword is used, the data is written only when no client is reading the table. When using HIGH_PRIORITY, INSERT statements have the same priority as SELECT statements. (default policy)

Therefore, when the INSERT statement is executed before the SELECT statement is executed, the INSERT blocks and waits for the SELECT to finish reading, but at this time, if the SELECT enters the schedule again, the SELECT is blocked (the read lock is supposed to be read directly). But at this time, because the INSERT statement has the same priority as the SELECT statement, the SELECT can not be executed until the end of the INSERT, so INSERT can add LOW_PRIORITY to optimize the read speed.

Take your time here. I don't know much about locks.

Modify data

The syntax of the UPDATE statement 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 you do not use the WHERE clause to constrain the selection condition, update all data, for example, modify the login time of all records in the user table to now:

MariaDB [mydb] > UPDATE user SET logtime=NOW (); Query OK, 5 rows affected (0.01sec) Rows matched: 5 Changed: 5 Warnings: 0 the login IP of the first three people to register for update is 127.0.0.1MariaDB [mydb] > UPDATE user SET logip='127.0.0.1' ORDER BY regtime LIMIT 3

The ORDER BY statement can be used for SELECT UPDATE DELETE, etc., to indicate which field arrangement of the table to follow when the table is output, deleted, or updated. For example, ORDER BY regtime arranges updates in positive order according to the registration time, and updates only the first three lines with the LIMIT statement.

Then 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 [position offset,] number of rows

The first line starts at 0, so the following:

SELECT * FROM user LIMIT 2 FROM user LIMIT 2; / / starting from line 3, take two lines, that is, take the 3rd and 4th records. Use the where statement to select a specific line to update MariaDB [mydb] > UPDATE user SET logip='192.168.1.2' WHERE username='test2'

Because there are a lot of things in the WHERE clause, there won't be too much content here.

LOW_PRIORITY: this is the same as INSERT's LOW_PRIORITY.

The REPLACE statement MariaDB [mydb] > REPLACE INTO user VALUES (1meme Test111 NOW (), '192.168.1.1')

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

Do you seem to have a little chicken rib? .

Delete data

DELETE grammar:

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

When you do not use the where statement to constrain the condition, all data is deleted, as follows:

MariaDB [mydb] > DELETE FROM user; use where statement to constrain the selected row MariaDB [mydb] > thank you for reading, the above is the "Mariadb data insertion, deletion and modification methods" of the content, after the study of this article, I believe that we have a deeper understanding of the Mariadb data insertion, deletion and modification methods of this problem, the specific use of the need for you to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.