s1/c# Language and database technology fundamentals/08-manipulating data with SQL statements

The SQL language consists mainly of the following parts:

DML (data manipulation Language, which is also known as Data Manipulation language): Used to insert, modify, and delete data in a database, such as INSERT, UPDATE, and delete.

DDL (data definition Language): Used to build databases, database objects, and define their columns, most of which are commands that start with create, such as Create table,create view and drop TABLE.

DQL (data query Language): Used to query data in a database, such as SELECT.

DCL (Data Control Language): Used to control access permissions for database components, storage privileges, etc., such as Grant, REVOKE, and so on.

In addition, T-SQL includes variable descriptions, internal functions, and other commands.

Inserting data using T-SQL

1. Insert data Using Insert

Syntax: INSERT [into] table name [(column list)] VALUES (list of values);

which

1, [into] is optional, can also be omitted.

2, the table name is required.

3. The column name of the table is optional, and if omitted, all columns are inserted in turn.

4, multiple column names and multiple value lists are separated by commas.

5, semicolon (;) is a T-SQL statement Terminator, and semicolons are not required.

For example, the following statement inserts a row of data into the student table.

INSERT into Students (sname,saddress,sgrade,semail,ssex)

VALUES (' Zhang Qing ', ' Shanghai Songjiang ', 6, ' [email protected] ', 0)

Note: In SQL Server, the default T-SQL is case insensitive, and all T-SQL keywords in this book are capitalized to highlight performance.

When inserting data, you need to be aware of the following considerations.

• Each time you insert a whole row of data, it is not possible to insert only half a row or columns of data, but allow some column to be empty or use default values.
• The number of data values must be the same as the number of columns, and the data type, precision, and scale of each data value must also match the corresponding column.
• The INSERT statement cannot specify a value for an identity column because its number is automatically increased.
• For a column of character type, date type, when inserting data, enclose it in single quotation marks (').
• Although you can not specify a column name, it is a good idea to explicitly specify the inserted column and the corresponding value so that the columns and values can be clearly mapped.
• If you specify that a column is not allowed to be empty when you design the table, the column must insert data, otherwise an error message will be reported.
• The data item that is inserted requires compliance with the requirements of the check constraint, for example, we set the Semail column in front to include a character @ if the INSERT statement is modified to

INSERT into Students (sname,saddress,sgrade,semail,ssex)

VALUES (' Zhang Qing ', ' Shanghai Songjiang ', 6, ' ZQC ', 0)

After executing the statement, you will be prompted to conflict with the constraint on the table and insert failed.

• If you specify a column, how do I insert data for a column that has a default value? For example, the address information in the above Student information table has a default value of 10 sentences. You can use the default keyword instead of the inserted value at this time to insert the following statement.

INSERT into Students (sname,saddress,sgrade,semail,ssex)

VALUES (' Zhang Qing ', default,6, ' [email protected] ', 0)

2. Inserting multiple rows of data at once

There are three ways to insert multiple rows of data at a time, as explained below.

1/Add data to the table by using the Insert SELECT statement

For example, to create a new table AddressList to store the class's address book information, you can extract the relevant data from the student table into the built-in AddressList table, the T-SQL statement is as follows.

INSERT into AddressList (name, address, email)

SELECT Sname,saddress,semail

From Students

The SELECT statement is used for querying, and the SQL statement above is used to read and save the name, address, and e-mail information in the Student information table to the new AddressList table.

The following two points need to be noted.

• The number of data, order, data type, etc. that are queried must be consistent with the inserted item.
• The AddressList table must be pre-created and has three columns of name, address, and e-mail.

2/Add data from an existing table to a new table by using the SELECT INTO statement

Like the insert into above, the SELECT INTO statement also selects some data from a table to be inserted into the new table, and the difference is that the new table is created when the query statement is executed and cannot be pre-existing.

For example, the following T-SQL statement:

SELECT Students.sname,students.saddress,students.semail

Into AddressList

From Students

The new table AddressList will be created, students the sname, saddress, and semail in the table as the new columns of the AddressList table, and insert all the queried data into the new table.

When inserting data into a new table, it involves a new question: How do i insert an identity column?

Because the data for the identity column is not allowed to be specified, we can create a new identity column with the following syntax.

Grammar:

SELECT identity (data type, identity seed, identity growth) as column name

into new table

From original table

The above statement does not copy the relationship and check constraints, or it can be modified to

SELECT students.sname,students.saddress,students.semail,identity (int,1,1) as

StudentID

Into AddressList

From Students

The usage of the previous sentence is the same as the SELECT INTO new table.

3/INSERT with Union keyword merge data

The Union statement is used to synthesize two different data or query results into a new result set.

Of course, different data or query results also require the same number of data, order, data type, so when inserting data into a table multiple times, you can use SELECT ... Union to simplify the operation.

For example, the following T-SQL statement:

INSERT Students (Sname,sgrade,ssex)

SELECT ' Zhang can ', 7,1 UNION

SELECT ' Li Yang ', 4,0 UNION

SELECT ' Yang Xiao ', 2,0 UNION

SELECT ' Soup Beauty ', 3,0 UNION

SELECT ' Su Sandong ', 7,1 UNION

SELECT ' Wang Li ', 3,1 UNION

SELECT ' Zhang Wei ', 7,1 UNION

SELECT ' Chen Gang ', 4,1 UNION

SELECT ' Wang Juan Juan ', 7, 0;

The actual effect of this is actually with the above insert ... The effect of select is the same, except that multiple rows of data are handwritten, and then the Union is combined to form multiple rows of data records, which are then inserted together with the multiline data record. This method cannot be implemented (the default value), the defaults, the ' input, the date is also used ' character mode.

Updating data with T-SQL

Use T-SQL to update the syntax format for a row in a table as follows.

Grammar:

Update table name set column name = update value [WHERE update condition]

which

• The set can be followed by a number of column name = Update values, modifying the values of multiple data columns, not limited to one, separated by commas.
• The WHERE clause is optional and is used to limit the conditions for updating data. Without restrictions, all data rows for the entire table are updated.

It is important to note that using the UPDATE statement may update a row of data or may update multiple rows of data, but it may not update any data.

For example, in the Student information table, the gender of all students should be changed to 0 (female).

UPDATE Students SET ssex=0

For students with the address "embroidery class of Beijing women's Vocational Technical School", if the class is changed to home Economics class, it needs to be updated according to the conditions.

UPDATE Students

SET saddress= ' Beijing Women's Vocational Technical school embroidery class '

WHERE saddress= ' Beijing Women's Vocational Technical school embroidery class '

As mentioned earlier, in a T-SQL expression, you can use column names and values. If the students in the examination, there is a problem of the wrong answer, resulting in scoring errors, after the results of the performance table to update the results, all the results below or equal to 95 points on the original basis plus 5 points, updated SQL statement as follows.

UPDATE Score

SET score = score + 5

WHERE Score <= 95

Tip: When updating data, there are generally conditional restrictions, do not forget to write a WHERE condition statement, otherwise it will update all rows in the table data, which may lead to the loss of valid data.

Delete Data Using delete

Using T-SQL to delete data from a table, the syntax is as follows.

Grammar:

delete [from] table name [WHERE < delete condition;]

The SQL statement that deletes the data named "Zhang Qing" in the Student information table is as follows.

DELETE from Students

WHERE SName = ' Zhang Qing cut '

In another case, if the primary key value of the row to be deleted is referenced by another table, for example, the StudentID in the score table refers to the SCODE column in the Student information table, then the referenced row is deleted:

DELETE from Students

WHERE SCode = 22

SQL Server will report error messages that conflict with constraints

Tip: The DELETE statement deletes the entire record and does not delete only a single column, so the column name cannot appear after the delete, for example, the following statement:

DELETE saddress from Students

An error message is reported.

Delete Data using TRUNCATE TABLE

TRUNCATE table is used to delete all rows in a table, functionally it is similar to a DELETE statement without a WHERE clause.

For example, to delete all the record rows in the Student information table, you can use the following statement.

TRUNCATE TABLE Students

However, TRUNCATE TABLE performs faster than delete, uses fewer system resources and transaction log resources, and the identity column of the table restarts numbering after the data is deleted.

TRUNCATE table deletes all rows in the table, but the table's structure, columns, constraints, indexes, and so on, are not altered. TRUNCATE table cannot be used with tables that have a FOREIGN key constraint reference, in which case a DELETE statement is required.

In practice, it is not recommended to use the TRUNCATE TABLE statement because the data deleted by him cannot be restored.

s1/c# Language and database technology fundamentals/08-manipulating data with SQL statements