mysql data insert, update and delete details

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)

This way the insertion is successful.

For safety reasons, you should write the column name each time you insert. Regardless of 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 column names are provided, a value must be given for each listed column. If this is not done, an error message will be generated and the row will not be inserted successfully.

Omitting Columns If the table definition permits, you can omit certain columns in an INSERT operation.

The omitted columns must satisfy one of the following conditions

• The column is defined to allow NULL values ​​(no value or empty 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 clients, and it is MySQL 's job to manage which requests are processed and in what order.

INSERT operations can be time consuming (especially if there are many indexes that need to be updated), and they can degrade the performance of pending SELECT statements.

If data retrieval is of primary importance (which it usually is), you can instruct MySQL to lower the priority of the INSERT statement by adding the keywords LOW_PRIORITY between INSE 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, just add multiple values .

INSERT is generally 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 , and as the name suggests, it consists of an INSERT statement and a SELECT statement.

Suppose you want to merge a list of customers from another table into your customers table. Instead of reading a row each time and inserting it with INSERT , you can do it as follows:

insert into customers(xx,xx,xx)
select xx,xx,xx
from newcustomers

That's about it.

Column Names in INSERT SELECT For simplicity, this example uses the same column names in both INSERT and SELECT statements.

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

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

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

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

2. Update

To update (modify) data in a table, use the UPDATE statement.

There are two ways to use UPDATE :

• Update a specific row in a table;
• Updates all rows in the table.

UPDATE customers
SET cust_email = '[email protected]'
where cust_id = 10005;

This is the update statement above.

If you update multiple columns:

UPDATE customers
SET cust_email = '[email protected]',
cust_name = 'game'
where cust_id = 10005;

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

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

To explain this, if you are sure to update only one row, then it is recommended to use the update 1 mode to indicate that only one row is to be updated.

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

3. Delete

• Delete specific rows from a table;
• Delete all rows from a table.

Delete the contents of a table instead of the table. The DELETE statement removes rows from a table, or even deletes all rows in a table. However, DELETE does not delete the table itself.

Faster Deletions If you want to delete all rows from a table, don't use DELETE .

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

This is the end of this article about the details of MySQL data insertion, update and deletion. For more relevant MySQL data insertion, update and deletion content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!