编程知识 cdmana.com

MySQL must know and be able to organize - data insertion, update and deletion [10]

Preface

Simply organize data, insert updates and delete .

Text

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 .

When inserting, for the sake of safety , Then you should write the column name every time you insert .

No matter what kind of INSERT grammar , Must give VALUES The correct number of .

If you don't provide a column name , You must provide a value for each table column . If you provide a list , You must give a value for each listed column .

If not , An error message will be generated , The corresponding row was inserted unsuccessfully .

Omit column If the definition of the table allows , Can be in INSERT Omit some columns in the operation . The omitted column must satisfy one of the following conditions .

  1. This column is defined to allow NULL value ( No value or null value ).

  2. Give default values in the table definition . This means that if you don't give a value , The default value... Will be used

Improve overall performance Databases are often accessed by multiple customers , It is important to manage what requests are processed and in what order MySQL The task of .

INSERT The operation can be time-consuming ( Especially when there are many indexes to update ), And it may reduce the waiting time SELECT Statement performance .

If data retrieval is the most important ( This is usually the case ), Then you can use the INSERT and INTO Add keywords between LOW_PRIORITY, instructions MySQL Reduce INSERT Priority of statement .

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 lines , Just add more values that will do .

INSERT It is generally used to insert a row with a specified column value into the table . however ,INSERT There is another form , You can use it to make a SELECT The result of the statement is inserted into the table .

That's what's called INSERT SELECT, seeing the name of a thing one thinks of its function , It is made up of a INSERT Statement and a line SELECT The sentences make up .

If you want to merge the customer list from another table to your customers surface . You don't need to read one line at a time , Then use it INSERT Insert , It can be done as follows :

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

That's almost it .

INSERT SELECT Column name in For the sake of simplicity , This example is in the INSERT and SELECT The same column name is used in the statement .

however , Column name matching is not required . in fact ,MySQL Don't even care SELECT Returned column name .

It uses the position of the column , therefore SELECT The first column in ( Regardless of its listing ) Will be used to fill

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 that use different column names .

The update operation is described below .

To update ( modify ) Table data , You can use UPDATE sentence . There are two ways to use UPDATE:

1. Update specific rows in the table ;

2. Update 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 keyword If you use UPDATE Statement update multiple lines , And an error occurs when updating one or more of these rows , Then the whole UPDATE Operation cancelled ( All rows updated before the error are restored to their original values ).

For even errors , Also continue to update , You can use IGNORE keyword , As shown below : UPDATE IGNORE customers…

Let me explain , If you are sure to update only one line , So the suggestion is to update with update 1 This model , Indicates that only one row is updated .

To delete from a table ( Get rid of ) data , Use DELETE sentence . You can use it in two ways DELETE:

  1. Delete specific rows from the table ;

  2. Delete all rows from the table .

Delete the contents of the table instead of the table DELETE Statement to delete a row from a table , Even
Delete all rows in the table . however ,DELETE Don't delete the table itself .

Faster delete If you want to delete all rows from the table , Do not use DELETE.

You can use TRUNCATE TABLE sentence , It does the same thing , But faster (TRUNCATE The actual is to delete the original table and recreate a table , Instead of deleting the data in the table row by row )

junction

Next section , Tables and views .

版权声明
本文为[Ao Maomao]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/10/20211002145410449v.html

Scroll to Top