Chapter 3 SQLServer Data Management

This chapter introduces the use of SQL statements. It describes how to use SQL statements to insert, modify, and delete data. 3.1SQL introduction during data management, if you need to manually manage and store the data in SQLServerManagementStudio every time you create a database or table or read data from the database

This chapter introduces the use of SQL statements. It describes how to use SQL statements to insert, modify, and delete data. 3.1 SQL introduction during data Management, if you need to manually create a database, table, or read data from the database in SQL Server Management Studio each time, it is not only inconvenient to manage and store

This chapter introduces the use of SQL statements. It describes how to use SQL statements to insert, modify, and delete data.

3.1 SQL Introduction

During data Management, if you need to manually create a database, table, or read data from the database in SQL Server Management Studio, it is not convenient to manage data, in addition, the data stored in the database cannot be provided to the program at all. Therefore, the database also needs a set of instruction sets that can identify instructions, execute corresponding operations, and provide data for programs.

Currently, the standard instruction set is SQL.

3.1.1 SQL and T-SQL

The SQL language was proposed by Boyce and Chamberlin in. The Relational Database Management System SystemR developed by IBM in-has implemented this language. After years of development, the SQL language has become the standard language for relational databases.

Unlike programming languages such as Java and C #, SQL is only an instruction that can be recognized by the database. However, in a program, SQL statements can be organized and sent to the database, the database then performs corresponding operations. For example, to obtain records in the SQL Server database table in the C # program, you can write SQL query statements in the C # program and then send them to the database, the database queries the website space based on the queried SQL statement, and then returns the query result to the C # program.

T-SQL (Transact-SQL) is an enhanced version of standard SQL, in addition to standard SQL commands, but also made a lot of expansion of SQL commands, provide similar to the basic functions of Java language, such as variable description, process control, and function.

3.1.2 T-SQL Composition

The T-SQL language consists of the following parts.

DML is used to query, Insert, Delete, and modify data in a database, such as SELECT, Insert, Update, and Delete.

DCL (Data Control Language): used to control the access permission and access permissions of database components, such as Grant and Revoke.

DDL (Data Definition Language): used to Create databases, database objects, and define columns. Most of the commands starting with "Create", such as "Create Table", "Create View", and "Drop Table.

In addition, the T-SQL also includes variable descriptions, embedded functions and other commands.

3.2 conditional expressions and logical operators in T-SQL

Like Java, expressions are a combination of symbols and operators and can be evaluated to be worth a single data value. A simple expression can be a constant, variable, column, or scalar function. You can use an operator to concatenate two or more simple expressions into a complex expression.

3.2.1 conditional expressions

Expressions in SQL Server can contain one or more of the following parameters.

Constant: a symbol that represents a single specified data value. A constant consists of one or more letters and numbers (letters ~ Z, ~ Z, number 0 ~ 9) or symbol (! , @, #, Etc. Letters and DateTime must be enclosed in quotation marks, while binary strings and numeric constants are not required.

Column name: name of the column in the Table. Only the column name can be used in the expression.

{Unary operator}: Only one operand operator. "+" indicates a positive number, "-" indicates a negative number, and "~" indicates a negative number. The complement operator.

{Binary operator}: operator that combines two operands for operation. Binary operators can be Arithmetic Operators, value assignment operators (=), bitwise operators, comparison operators, logical operators, String concatenation (or join) Operators (+), or unary operators. Comparison operators and their meanings.

Operator meaning

= Equal

> Greater

<小于< p>

> = Greater than or equal

<= Less than or equal

<> Not equal

! Non

These operators can be used to form conditional expressions. For example, you can write the following code:

PRICE> 100

Name Like 'Lee %'

GRADE <> '3'

Wildcard operators in graphs can also be used in some T-SQL statements.

Wildcard interpretation example

'_' One character A Like 'C _'

% String of any length B Like 'co _ %'

[] A character C Like '9w0 [1-2] 'in the specified range in parentheses

[^] No longer any character in the range specified in parentheses d like '9w0 [^ 1-2]'

Wildcards are often used together with Like keywords. For example, you can use Like and wildcard to complete some special constraints on the table. For example, you must enter an 11-digit mobile phone number in the phone number column in the table. You can write the following constraints:

TelCode Like '13 [5-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]'

Like statements are often used in queries.

3.2.2 logical expression

The logical operators supported by the T-SQL are And, OR, And Not.

And Or operator join conditions, Not negative conditions. And returns True only when both conditions are True. OR connects two conditions, but returns True if any of them is True.

When multiple logical operators are used in a statement, evaluate the Not value first, then the And value, And then the Or value.

For example, if the payment method in the purchase order form is a credit card and the constraints must be Peony Card, jinsui card, longcard, or sunshine card, you can write the following constraints:

Not (payment method = 'credit card') OR (credit card IN ('peony ca', 'jinsui ca', 'longca', 'Sunshine ca '))

The IN keyword is used to limit the range. Logical expressions are often used in queries.

3.3 insert data with T-SQL

You have learned how to create a table, modify the table structure, and add constraints. Now you need to know how to add data to the table.

It is easy to insert data in SQL Server Management. You only need to select a table and select the "Open Table" option in the shortcut menu to directly input data rows to the table.

You can use a T-SQL to add new data to a table, or to add data from an existing table to a newly created table.

3.3.1 Insert data rows Using Insert

Using the Insert statement to Insert data one row at a time is the most common method. The syntax format is as follows:

Insert [Into] <表名> [Column name] Values <值列表>

Where:

[Into] is optional and can be omitted.

The table name is required, and the column name of the table is optional. If it is omitted, It is inserted in sequence.

Multiple column names and multiple value lists are separated by commas.

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

Insert into Students (Name, Address, Grade, Email, Sex) Values ('zhang san', 'shanghai', 6, '2017 @ 163.com ', 0)

SQL statements are generally executed in the query window.

Check whether the SQL statement is correctly executed. Open the table and check whether the data item is correctly added.

Note the following when inserting data.

It is impossible to insert only half or several columns of data into one row at a time. If the non-null constraint of the field is violated, the insert statement will fail.

The number of data values must be the same as the number of columns. The data type, precision, and decimal places of each data value must also match the corresponding columns.

The Insert statement cannot specify a value for the ID column because its numbers automatically increase.

When inserting data for a column of the character type, it is best to enclose it in single quotes, because it is especially prone to errors when the character contains numbers.

Although no column name can be specified, it is best to specify the inserted column and the corresponding value to be "aware ".

If you specify that a column is not allowed to be blank when designing a table, you must insert data into this column, a Hong Kong VM. Otherwise, an error message will be reported.

The inserted data items must comply with the check constraints. For example, we have set the Email field to contain a character @. If the inserted statement is changed:

Insert into Students (Name, Address, Grade, Email, Sex) Values ('zhang san', 'shanghai', 6, '123', 0)

After the statement is executed, a prompt is displayed that the statement is in conflict with the table constraints. insertion fails.

Another problem is how to insert data for columns with default values if a column name is specified? For example, the address information in the above student information table has a default value. In this case, you can use the Default keyword instead of the inserted value. The insert statement is as follows:

Insert into Students (Name, Address, Grade, Email, Sex) Values ('zhang san', Default, 6, '123', 0)

3.3.2 insert multiple rows of data at a time

There are three ways to insert multiple rows at a time.

1. Use the Insert Selete statement to add data in the existing table to the new table.

For example, if you create a new table to store the address book information of the current class, you can extract the relevant data from the student table. The SQL statement is as follows:

Insert into TongXunLu (Name, Address, Email) Select Name, Address, Email From Students

The Select statement is used for query. The preceding SQL statement is used to insert existing names, addresses, and e-mail information in the student information table to the new TongXunLu table, avoiding entering a large number of repeated data items.

Pay attention to the following two points.

(1) The data quantity, sequence, and data type obtained by the query must be consistent with the inserted items.

(2) The TongXunLu table must be created in advance and has three fields: name, address, and email.

2. Use the Select Into statement to add data from an existing table to a new table.

Similar to the Insert into statement above, the Select Into statement selects some data from a table and inserts it into the new table. The difference between the Hong Kong Space and, this new table is created when the query statement is executed and cannot exist in advance.

The following T-SQL statement, for example:

Select Students. Name, Students. Address, Student. Email Into TongXunLu From Students

Create a new table TongXunLu, use Name, Address, and Email in the Students table as the new columns of the TongXunLu table, and insert all the queried data into the new table.

When inserting data into a new table, a new problem occurs: how to insert an ID column?

Because the data in the ID column cannot be specified, we can create a new ID column. The syntax is as follows.

Select IDENTITY (data type, identification seed, identification growth) AS column name Into new table From original table

The preceding statement can be modified:

Select Students. Name, Students. Address, Students. Email, IDENTITY (int, 1, 1) AS StudentID Into TongXunLu From Students

3. Merge data using the Union keyword for insertion

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

Of course, different data or query results also require consistent data count, order, and data type. Therefore, when you insert data to a table multiple times, you can use Select... Union to simplify the operation.

For example, the following T-SQL statement.

Insert Students (Name, Grade, Sex) Select 'zhang san', 7, 1 Union Select 'Li si', 3, 2 Union Select 'Wang wu', 5, 5 Union Select 'Liu 6', 6, 6

In fact, this effect is the same as that of Insert · Select, except that multiple rows of data are handwritten and then combined into multiple rows by Union, finally, insert these multiple rows of data together.

3.4 Update data with T-SQL

Data updates are a frequent occurrence, and data updates can be performed using T-SQL.

The syntax for updating a row in a table using a T-SQL is as follows:

UPDATE <表名> Set <列名 = 更新值> [WHERE <更新条件> ]

Where:

Set can be followed by update values of multiple data columns.

The Where clause is optional and used to restrict conditions. If no limit is set, all data rows in the entire table will be updated.

Note that using the Update statement may Update a row of data, Update multiple rows of data, or Update any data.

For example, in the student information table, change the gender of all students to 0 (female ).

Update Students Set Sex = 0

If the student's address is left blank, the default value is "Beijing female vocational and technical school". If this class is changed to a school, the student's address must be updated as required.

Update Students Set Address = 'Beijing female vocational and technical college 'where Address = 'Beijing female vocational and technical college'