SQL Server Table Management _ details of the operation of the data additions and deletions (case code)

Source: Internet
Author: User

SQL Server Table Management _ Detailed information on the operation of data deletion and modification (case code)-DML1. SQL INSERT INTO statement (insert in table)

The INSERT INTO statement is used to insert a new record into the table.

SQL INSERT into Syntax

The INSERT into statement can be written in two ways.
The first form does not specify the name of the column for which you want to insert data, only the value you want to insert:

INSERT  into table_name (column1, Column2, Column3, ...) VALUES (Value1, value2, Value3, ...);

If you want to add values for all the columns in the table, you do not need to specify the column names in the SQL query. However, make sure that the values are in the same order as the columns in the table. The INSERT into syntax is as follows:

INSERT  into table_name VALUES (Value1, value2, Value3, ...);
Demo Database

The following is the data in the "Customers" table:

City
CustomerID CustomerName ContactName Address PostalCode Country
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Karl Jablonski 305-14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek Ul. Filtrowa 68 Walla 01-012 Poland

INSERT into Instance code

Suppose we want to insert a new row in the "Customers" table.

We can use the following SQL statement:

Example
 insert  into   Customers (CustomerName, ContactName, Address, City, PostalCode, country)  values  ( cardinal   ' ,  '  tom B. Erichsen  ,  "  ' ,  " stavanger  , "  4006  ,  '  norway   ' ); 

Now, the data selected from the "Customers" table looks like this:

City
CustomerID CustomerName ContactName Address PostalCode Country
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Karl Jablonski 305-14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek Ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway

Insert data only in the specified column

We can also insert data in only the specified columns.

The following SQL statement inserts a new row, but only inserts data in the CustomerName, city, and Countryn columns (the CustomerID field is automatically updated):

Instance
INSERT  into Customers (CustomerName, City, country) VALUES ('Cardinal'Stavanger'Norway  ');

Now, the data selected from the "Customers" table looks like this:

City
CustomerID CustomerName ContactName Address PostalCode Country
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Karl Jablonski 305-14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek Ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Null Null Stavanger Null Norway

2. SQL DELETE statement (delete records from table)

The DELETE statement is used to delete existing records in the table.

SQL DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax
DELETE  from table_name WHERE condition;

The following is the data in the "Customers" table:

City
CustomerID CustomerName ContactName Address PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. De la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy Hanover Sq. London WA1 1DP UK
5 Berglunds Snabbköp Christina Berglund Berguvsvägen 8 Across S-958 22 Sweden
SQL DELETE Instance Code

Suppose we want to remove the customer "Alfreds Futterkiste" from the "Customers" table.

We use the following SQL statement:

Example
DELETE  from Customers WHERE CustomerName='Alfreds futterkiste';

Now, the "Customers" table looks like this:

CustomerID CustomerName Contactnam E Address city PostalCode country
2 ana Trujillo emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno taquería Ant Onio Moreno mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy Hanover Sq. London WA1 1DP UK
5 berglunds snabbköp Christina berglund berguvsvägen 8 luleå S-958 Sweden
Delete all data

You can delete all rows in a table without having to delete the table. This means that the structure, properties, and indexes of the table will remain the same:

DELETE  from table_name;

Or

DELETE *  from table_name;

Note: in the absence of a backup, delete the record with extra care! Because you deleted it can't be repeated!

3. SQL UPDATE statement (records in the Update table) modified

The UPDATE statement is used for updating existing records in the table.

SQL UPDATE Statement

The UPDATE statement is used for updating records that already exist in the table.

SQL UPDATE Syntax
UPDATE table_name SET = = value2, ... WHERE condition;
Demo Database

The following is the data in the "Customers" table:

City
CustomerID CustomerName ContactName Address PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. De la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy Hanover Sq. London WA1 1DP UK
5 Berglunds Snabbköp Christina Berglund Berguvsvägen 8 Across S-958 22 Sweden

SQL UPDATE Instance

The following SQL statement updated "CustomerName" and "City" for the first customer (CustomerID = 1):

Instance
UPDATE Customers SET = ' Alfred Schmidt ', city='Frankfurt'WHERE=1;

Now, the data selected from the "Customers" table looks like this:

City
CustomerID CustomerName ContactName Address PostalCode Country
1

Alfreds Futterkiste Alfred Schmidt Obere Str. 57 Frankfurt 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. De la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Thomas Hardy Hanover Sq. London WA1 1DP UK
5 Berglunds Snabbköp Christina Berglund Berguvsvägen 8 Across S-958 22 Sweden
update multiple records

The WHERE clause determines the number of records that will be updated.

The following SQL statement updates the contact name for all records in the country/region "Mexico" to "Juan":

UPDATE Customers SET ContactName='Juan'WHERE Country='Mexico  ';

The selections in the "Customers" table now look like this:

City
CustomerID CustomerName ContactName Address PostalCode Country
1

Alfreds Futterkiste Alfred Schmidt Obere Str. 57 Frankfurt 12209 Germany
2 Ana Trujillo Emparedados y helados Juan Avda. De la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Juan Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Thomas Hardy Hanover Sq. London WA1 1DP UK
5 Berglunds Snabbköp Christina Berglund Berguvsvägen 8 Across S-958 22 Sweden

Update Warning!

Be careful when updating records. If you omit the WHERE clause, all records will be updated!

UPDATE Customers SET ContactName='Juan';

The "Customers" table will resemble the following:

City
CustomerID CustomerName ContactName Address PostalCode Country
1

Alfreds Futterkiste Juan Obere Str. 57 Frankfurt 12209 Germany
2 Ana Trujillo Emparedados y helados Juan Avda. De la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Juan Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Juan Hanover Sq. London WA1 1DP UK
5 Berglunds Snabbköp Juan Berguvsvägen 8 Across S-958 22 Sweden

About the view is more complex, after the details of it!!!

SQL Server Table Management _ details of the operation of the data additions and deletions (case code)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.