Mysql from entry to enchantment-7. Insert, update and delete
- 1. Insert data
- 1.1 Insert a complete line
- 1.2 Insert some rows
- 1.3 Insert the retrieved data
- 1.4 Copy from one table to another
- 2. Update the data
- 2.1 Update single row and single column
- 2.2 Update single row and multiple columns
- 2.3 Update all rows
- 2.4 Delete the value in the column
- 3. Delete data
- 3.1 Delete a single line
- 3.2 Delete all rows
❤ Series content❤
Mysql from entry to enchantment-1. Data preparation (
starting article) Mysql from entry to enchantment-2. Manipulating databases and tables
Mysql from entry to enchantment-3. Query, sort, and WHERE filtering
Mysql from entry to enchantment- 4. Wildcards, calculated fields, and functions
Mysql from entry to enchantment-5. Aggregation, grouping, and sub-query
Mysql from entry to enchantment-6. Table join and combination query
Mysql from entry to enchantment-7. Insert, update and Delete
Mysql from entry to enchantment-8. Views, stored procedures, transaction processing
Mysql from entry to enchantment-9. Cursor, advanced SQL features (to be updated...)
Mysql from entry to enchantment-10. Shorthand for knowledge points (end) )(pending upgrade…)
Main content of this article
There are several ways to insert data, update data and delete data. If you don’t understand the structure of the table in the text, you can check the series homepage. Not much to say, let's get to the point!
1. Insert data
1.1 Insert a complete line
INSERTinsert it requires a complete row specify the table name and the new value is inserted into the row.
mysql> INSERT INTO Customers -> VALUES('1000000006', -> 'Toy Land', -> '123 Any Street', -> 'New York', -> 'NY', -> '11111', -> 'USA', -> NULL, -> NULL); Query OK, 1 row affected (0.54 sec)
- You must provide a value for each column, use a null value
- The columns must be filled in the order in which they appear in the table definition
1.2 Insert some rows
INSERTThe recommended insertion method is to explicitly give the column names of the table. In this way, you can also omit columns, that is, only provide values for some columns and no values for other columns.
The omitted columns must meet one of the following conditions:
The column is defined to allow
NULLthe value (null value or no value).
The default value is given in the table definition (if no value is given, the default value will be used).
If the table is not allowed
NULLvalues or default value, but at this time the values in the table are omitted,
DBMSwill generate an error message, the corresponding row is not successfully inserted.
Also in now
Customersinsert a row into the table.
mysql> INSERT INTO Customers(cust_id, -> cust_name, -> cust_address, -> cust_city, -> cust_state, -> cust_zip, -> cust_country) -> VALUES('1000000006', -> 'Toy Lnad', -> '123 Any Street', -> 'New York', -> 'NY', -> '11111', -> 'USA'); Query OK, 1 row affected (0.24 sec)
No matter which INSERT syntax is used, the number of VALUES must be correct. 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 provide a value for each column listed. Otherwise, an error message will be generated and the corresponding row cannot be successfully inserted.
1.3 Insert the retrieved data
INSERTThere is also another form, it can use
SELECTstatement results into a table.
First create a new
-- 创建表 CREATE TABLE New_Customers ( cust_id char(10) NOT NULL , cust_name char(50) NOT NULL , cust_address char(50) NULL , cust_city char(50) NULL , cust_state char(5) NULL , cust_zip char(10) NULL , cust_country char(50) NULL , cust_contact char(50) NULL , cust_email char(255) NULL ); -- 插入数据 INSERT INTO new_customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES ('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', '[email protected]'), ('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
New_Customerstable columns incorporated into the customer
mysql> INSERT INTO Customers(cust_id, -> cust_contact, -> cust_email, -> cust_name, -> cust_address, -> cust_city, -> cust_state, -> cust_zip, -> cust_country) -> SELECT cust_id AS id, -> cust_contact, -> cust_email, -> cust_name, -> cust_address, -> cust_city, -> cust_state, -> cust_zip, -> cust_country -> FROM New_Customers -> WHERE New_Customers.cust_id NOT IN (SELECT cust_id FROM Customers); Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0
When inserted, you should not have been used in
PRIMARY KEY) value, using a subquery embodiment above filter out duplicate
cust_id. Not concerned about when you insert
SELECTa column name returned, instead of using the position of the column, so you can use an alias.
1.4 Copy from one table to another
There is a data insertion does not use
INSERTstatement. To copy the contents of a table to a brand new table (a table created during operation).
mysql> CREATE TABLE CustomersCopy AS -> SELECT * FROM Customers; Query OK, 6 rows affected (0.59 sec) Records: 6 Duplicates: 0 Warnings: 0
SELECToptions and clauses can be used, include
You can use joins to insert data from multiple tables.
No matter how many tables are retrieved from the data, the data can only be inserted into one table.
Main purpose: it is experimenting with new
SQLgood tool to copy the table before the statement. First replication, data replication can be tested on the
SQLcode without affecting the actual data.
2. Update the data
UPDATEstatement, update the data in the table (modified).
Used in two
- Update a specific row in the table
- Update all rows in the table
When used to clear is updating a particular row or update all rows .
UPDATEStatement can use sub-queries that can
SELECTstatement to retrieve the data update column data.
2.1 Update single row and single column
1000000005's e-mail address is updated
mysql> UPDATE Customers -> SET cust_email = '[email protected]' -> WHERE cust_id = '1000000005';
2.2 Update single row and multiple columns
When updating a plurality of columns, the use of only one
SETcommand, each "column = value" separated by commas (comma after the last one without) between pairs.
mysql> UPDATE Customers -> SET cust_contact = 'Sam Roberts', -> cust_email = '[email protected]' -> WHERE cust_id = '1000000005';
2.3 Update all rows
Do not use
WHEREthe proviso that updates all rows in the table.
mysql> UPDATE Customers -> SET cust_contact = 'Sam Roberts', -> cust_email = '[email protected]';
2.4 Delete the value in the column
If the table definition allowed
NULLvalue, the value of a column to be deleted, it may be set to
NULL. (Pay attention to the difference between deleting column values (retaining column structure) and deleting columns (completely deleting))
mysql> UPDATE Customers -> SET cust_email = NULL -> WHERE cust_id = '1000000005';
3. Delete data
DELETEstatement, delete the data in the table.
Used in two
- Delete a specific row in the table
- Delete all rows in the table
To clear when used to delete a particular row or delete all the rows .
3.1 Delete a single line
mysql> DELETE FROM Customers -> WHERE cust_id = '1000000006';
3.2 Delete all rows
Customersall rows. The table itself is not deleted.
mysql> DELETE FROM Customers;
If you want to delete all the rows from a table is recommended to use
TRUNCATE TABLEthe statement, it does the same, but faster (because of variable data not recorded).
But be warned:
TRUNCATEpart of the data definition language (
DDL), and the
TRUNCATEcommand can not be rolled back after the execution, using
TRUNCATEbest to do a backup of the data in the table before the current command.
mysql> TRUNCATE TABLE Customers;
That's all there is to this article, if it feels good. ❤ Click a like before leaving! ! ! ❤Follow
-up will continue to share the "Mysql From Getting Started to Enchanting" series of articles, if you are interested, you can click to pay attention to not get lost~.