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

This article mainly introduces how to achieve data insertion and update and deletion in mysql, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Insert INSERT INTO customers (customers.cust_address,customers.cust_city,customers.cust_state,customers.cust_zip,customers.cust_country,customers.cust_contact,customers.cust_email) VALUES ('zhangsan','good','111','ca','dasdsa','usa',NULL)

So the insertion is successful.

For security reasons when inserting, you should write the column name each time you insert it. No matter which INSERT syntax you use, you must give the correct number of VALUES. If you do not provide a column name, you must provide a value for each table column. If you provide a column name, you must give a value for each listed column. If not, an error message is generated and the corresponding row insertion is unsuccessful.

Omit columns if the definition of the table allows, you can omit some columns in the INSERT operation.

The omitted column must meet one of the following conditions

This column is defined as allowing null values (no values or null values).

Give the default value in the table definition. This means that if no value is given, the default value will be used

Improving overall performance databases are often accessed by multiple customers, and it is MySQL's task to manage what requests are processed and in what order.

INSERT operations can be time-consuming (especially when there are many indexes that need to be updated), and it can degrade the performance of SELECT statements waiting to be processed.

If data retrieval is the most important (usually), you can instruct MySQL to lower the priority of the INSERT statement by adding the keyword LOW_PRIORITY between INSERT and INTO.

INSERT INTO customers (customers.cust_address,customers.cust_city,customers.cust_state,customers.cust_zip,customers.cust_country,customers.cust_contact,customers.cust_email) VALUES ('zhangsan','good','111','ca','dasdsa','usa',NULL), (' zhangsan','good','111','ca','dasdsa','usa',NULL), ('zhangsan','good','111','ca','dasdsa','usa',NULL) ('zhangsan','good','111','ca','dasdsa','usa',NULL)

If you want to insert multiple rows, you only need to add multiple values.

INSERT is typically used to insert a row with a specified column value into a table. However, there is another form of INSERT that can be used to insert the results of a SELECT statement into a table.

This is called INSERT SELECT, which, as its name implies, consists of an INSERT statement and a SELECT statement.

If you want to merge the customer list from another table into your customers table. Instead of reading one line at a time and then inserting it with INSERT, you can do the following:

Insert into customers (xx,xx,xx) select xx,xx,xxfrom newcustomers

That's pretty much it.

The column names in INSERT SELECT are for simplicity, and this example uses the same column names in both INSERT and SELECT statements.

However, column names are not necessarily required to match. In fact, MySQL doesn't even care about the column names returned by SELECT.

It uses the position of the column, so the first column in the SELECT, regardless of its column name, will be used to populate the

The first column specified in the table column, the second column will be used to populate the second column specified in the table column, and so on.

This is useful for importing data from tables with different column names.

2. Update

To update (modify) the data in the table, you can use the update statement.

You can use UPDATE in two ways:

Update specific rows in the table

Update all rows in the table.

UPDATE customersSET cust_email = 'email@qq.com'where cust_id = 10005

This is the update statement above.

If you update multiple columns:

UPDATE customersSET cust_email = 'email@qq.com',cust_name =' game'where cust_id = 10005

IGNORE keyword if you update multiple rows with a UPDATE statement, and an error occurs while updating one or more of these rows, the entire UPDATE operation is canceled (all rows updated before the error occurs are restored to their original values).

To continue to update even if an error occurs, use the IGNORE keyword, as follows: UPDATE IGNORE customers...

To put it this way, if you are sure to update only one row, it is recommended that you update in update 1 mode, indicating that only one row will be updated.

To delete (remove) data from a table, use the delete statement. You can use DELETE in two ways:

3. Delete

Delete a specific row from the table

Delete all rows from the table.

Delete the contents of the table instead of the table DELETE statement deletes rows from the table, even

Delete all rows in the table. However, DELETE does not delete the table itself.

Faster deletion if you want to delete all rows from the table, do not use DELETE.

You can use the TRUNCATE TABLE statement, which does the same thing, but faster (TRUNCATE actually deletes the original table and recreates a table, rather than deleting the data in the table row by row)

Thank you for reading this article carefully. I hope the article "how to insert, update and delete data in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.