Mysql from entry to enchantment-7. Insert, update and delete

Mysql from entry to enchantment-7. Insert, update and delete

❤ 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

Use 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)

note:

  • You must provide a value for each column, use a null value NULL
  • 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 New_Customerstable.

-- 创建表
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);

Now the New_Customerstable columns incorporated into the customer Customerstable.

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 Customersthe spent cust_id( 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

Any SELECToptions and clauses can be used, include WHEREand GROUP BY.

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

Use UPDATEstatement, update the data in the table (modified).

Used in two UPDATEways:

  • 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

The customer 1000000005's e-mail address is updated[email protected]

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

Use DELETEstatement, delete the data in the table.

Used in two DELETEways:

  • 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

Delete Customersthe table cust_idfor the 1000000006rows.

mysql> DELETE FROM Customers
    -> WHERE cust_id = '1000000006';


3.2 Delete all rows

Delete 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

Insert picture description here


-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~.