Operate Database _ MySQL using SQL statements

-One day, when you find that the days are very difficult, it may be that this harvest will be extremely huge. -To those lazy years this chapter task: increase, modify, and delete data in student database goals: 1: insert data into a table using a T-SQL 2: update data in a table using a T-SQL 3: make-one day, when you find that the days are particularly difficult, it may be that this harvest will be extremely huge. -To those slack years

Tasks in this chapter:

Add, modify, and delete data in the student database

Objectives:

1: Use a T-SQL to insert data into a table

2: update table data with T-SQL

3: Use T-SQL to delete table data

First, let's briefly introduce SQL and its role.

During database 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 the database, 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 and execute corresponding operations to provide data for the program. Currently, the instruction sets in the table are timely SQL languages.

The SQL language is a language for real databases. it can create databases and data tables and add, delete, modify, and query database data. it can be used for common views and stored procedures, users can be granted permissions.

Operators in SQL

An operator is a symbol used for comparison and mathematical operations between columns or variables. In SQL, common operators include arithmetic operators, value assignment operators, comparison operators, and logical operators.

1: arithmetic operator

Arithmetic operators include: + (plus),-(minus), * (multiplication),/(division), and % (modulo. Arithmetic operators are used to perform mathematical operations on two numbers or expressions. these two expressions can be expressions of any two numeric data types.

Operator description

+ Add operation to calculate the sum of two numbers or expressions

-Subtraction: calculates the subtraction between two numbers or expressions.

* Multiplication operation: calculates the product of the multiplication of two numbers or expressions.

/Division operation, calculate the quotient of two numbers or expressions, for example, the value of 5/5 is 1, and the value of 5.7/3 is 1.900000

% Modulo operation, calculate the remainder of the division of two numbers or expressions. for example, the value of 5% 3 is 2.

2: value assignment operator

SQL has a value assignment operator, that is, "=" (equal sign), which is used to assign a value to a number, variable, or expression to another variable.

Operator description

= A number, variable, or expression is assigned to another variable. for example, Name = 'Zhang san'

3: comparison operators

Comparison operators are used to determine the size relationship between two expressions. except for expressions of the text, ntext, or Image data type, comparison operators can be used for almost all other expressions.

Operator description

= Equal to, for example, age = 23

> Greater than, for example, price> 100

<Less

<> Not equal

> = Greater than or equal

<= Less than or equal

! = Not equal to (non-SQL-92 standard)

4: logical operators

A logical operator is used to determine a condition to obtain the TRUE or FALSE of the condition. a Boolean data type with a value of TRUE or FALSE is returned.

Operator description

AMD returns TRUE if and only if both Boolean expressions are TRUE.

OR if and only if both Boolean expressions are FALSE, FALSE is returned.

NOT returns the inverse value of a Boolean expression, preferably at a finite level.

----------------------------------------------------------------------

Insert data with T-SQL

1. INSERT data using INSERT (using INSERT statements to INSERT data in one row is the most common method)

Syntax:

INSERT [INTO] table name [(column name list)] VALUES (value-type table );

Where

1. [INTO] is optional and can be omitted.

2. the table name is required.

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

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

5. semicolon (;) is the T-SQL statement terminator, good is not necessary.

Insert multiple rows at a time

1. add data to the table using the insert select statement

For example, if you create a new table AddressList to store the communication information of the current class, you can extract the relevant data from the student table and insert it into the created AddressList table. The statement is as follows:

Insert into Addresslist (name, address, email)

SELECT SName, SAdd warm-up match, SEmail

FROM Students

Note:

The number, sequence, and data type of the queried data must be consistent with the inserted data.

The AddressList table must be created in advance and has three columns: 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 to INSERT data INTO a new table. The difference is that the new table was created when the query statement was executed, cannot exist in advance.

Example:

SELECT Students. Name, Students. SAddress, Students. SEMmail

INTO AddressList

FROM Students

Create the AddressList of the new table, use SName, SAddress, and SEmail in the Students table as the new column of the AddressList table, and insert all the queried data into the new table.

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 types. Therefore, when inserting data multiple times into a table, you can use SELECT... UNION to simplify operations

Example:

INSERT Student (SName, SGrade, SSex)

SELECT 'Zhang San', 7,1 UNION

SELECT 'Li Si', UNION

SELECT 'Wang 5', 2, 0 UNION

SELECT 'Zhu Liu', 3, 0 UNION

SELECT 'Wang Erma sub', 7,1 UNION

The above INSERT... the effect of the SELECT statement is the same, except that multiple rows of data are post-written. then, the UNION statement is used to merge multiple rows of data records. Finally, these rows of data records are inserted together.

----------------------------------------------------------------------

Update data with T-SQL

Syntax:

UPDATE table name SET column name = UPDATE value [where update condition]

Where:

1. SET can be followed by multiple "column names = update values". you can modify the value of the Dongge data column by commas.

2. the WHERE clause is optional and is used to restrict the conditions for updating data. If no limit is set, the data row of the entire table is updated.

Use T-SQL to delete data

Syntax:

DELETE [FROM] table name [WHERE <删除条件> ]

Example:

Delete data named "Michael" in the student information table

Delete from Students

WHERE SName = 'zhangsan'

Use ttruncate table to delete data

The ttruncate table is used to DELETE rows in a TABLE, which is similar to a DELETE statement without a WHERE clause.

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

Trncate table Students

However, the truncate table is faster than the DELETE operation, and fewer system resources and transaction log resources are used. after the data is deleted, the ID column of the TABLE starts to be numbered again.

In practice, it is not recommended to use the truncate table statement because the data deleted using it cannot be restored.

Tip: before the deletion, ask yourself if you really want to delete it three times, ask the person who ordered the deletion to delete it three times, and confirm that there are no omissions before you perform the operation.