Shulou(Shulou.com)06/01 Report--

Xiaosheng blog: http://xsboke.blog.51cto.com

-Thank you for your reference. If you have any questions, you are welcome to communicate.

1. Composition of T-SQL language

T-SQL consists of three main languages:

1. DML: data manipulation language (data manipulation language)

Query, insert, delete, and modify data

SELECT 、 INSERT 、 UPDATE 、 DELETE

2. DDL: data definition language (data definition language)

Create databases, database objects, and define their columns

CREATE (create new database or table), ALTER (add columns in data table), DROP (delete database or table)

DCL: data Control language (data control language)

Control access permissions, access permissions, etc., of database components

GRANT (Grant permission), REVOKE (Recycling right), DENY (deny)

II. SELECT grammatical structure

1. The syntax of the select statement.

SELECT select_list

[INTO new_table_name]

FROM table_name

[WHERE search_conditions]

[GROUP BY group_by_expression] [HAVING search_conditions]

[ORDER BY order_expression [ASC | DESC]]

Parameter explanation:

SELECT clause: specify the content of the query

INTO clause: store the query results in a new table

FROM clause: specify query source

WHERE clause: query condition

GROUP BY clause: specifies the grouping conditions for query results

HAVING clause: specifies the condition to be executed after grouping, which is used together with the GROUP BY clause and has the same function as where, but the condition after grouping cannot use where but can only use having.

ORDER BY clause: specifies how the query results are sorted. The default is ascending asc and descending desc.

two。 Expression, which is generally used when setting conditions.

1) conditional expression

① constant: a letter, number, or symbol that represents a single specified data value

② column name: the name of the column in the table

③ unary operator: an operator with only one Operand: "+" for positive and "-" for negative

④ binary operators: operators that combine two operands to perform an operation: arithmetic operator, bit operator, logical operator, comparison operator

2) logical expression

Connect the conditions with a logical operator, and the result is a logical value: TRUE (true) or FALSE (false)

The keyword "in" can be used to limit the scope

For example, the types of credit cards that can be used to limit payment

Payment method = 'credit card' and credit card in ('Peony Card', 'Jinsui Card', 'Dragon Card', 'Sunshine Card')

3. The use of as (as is to set an alias for the column name of an output, and then display the column name with an alias when output)

The advantage is that the query results can be displayed more humanly, such as outputting English column names into Chinese column names.

Example: the average score of all students in the query table student

1) without as

Select AVG (grades)

From student

2) when adding as

Select AVG (grade) as GPA

From student

4. Usage of Like (for conditions that do not exactly match)

Example: query the student surnamed Zhang in the table student

5. Usage of In (limited)

Ex.: find out the students with scores of 89, 90 and 91 in the student table.

6. Usage of Top (limit the number of rows returned by the query)

Example: query the students in the first five rows of the student table

7. Usage of the function count (statistical line)

Example: query the total number of people for each position in the table

Third, multi-table query

1. The connection type of the table

1) Internal connection (INNER JOIN)

Only rows that exactly match between two data sets are returned

2) external connection (left and right outer connection is about the same, just remember one)

① left outside connection (LEFT JOIN/leftouter join)

The result set includes all rows of the left table

② right external connection (RIGHT JOIN/rightouter join)

The result set includes all rows of the right table

③ full external connection (FULL JOIN/fullouter join)

Returns all rows in the left and right tables

3) Cross-connect (cross join, generally not used)

Returns all rows that are connected one by one from the left table to the right table

two。 Inner join: only exactly matching rows in two tables are returned

Example: use inner joins to query student names and occupations in tables An and B (the names that exist in the two tables are not exactly the same)

1) method 1: the result is shown as follows

Select A.name,a.school,b.job

From A,B

Where a.name=b.name

2) method 2: the result is shown below

Select A.name,a.school,b.job

From An innerjoin B

On A.name=b.name

3. External connection

1) left outer connection

Example: use the left outer connection to query the student's name, school and occupation in tables An and B.

2) right external connection

Example: use the left outer connection to query the student's name, school and occupation in tables An and B.

3) complete external connection: no more examples, complete external connection will only display exactly matching contents in the two tables

4. Transaction (if there is an error in the executed statement, the statement will not be executed, and before the data recovery statement is executed)

1) introduction to transactions

① concept

A transaction is an indivisible logical unit of work, including a set of commands that are either executed or not executed.

② attribute

Atomicity: a transaction is a complete operation. The elements of a transaction are inseparable.

Consistency: when the transaction completes, the data must be in a consistent state.

Isolation: transactions are independent and do not depend on or affect other transactions in any way.

Persistence: once the transaction is committed, the effect of the transaction is permanently retained in the database.

2) Syntax for executing transactions

① start transaction: BEGIN TRANSACTION

② commit transaction: COMMITTRANSACTION

③ rollback (undo) transaction: ROLLBACKTRANSACTION

3) example:

The balance in Zhang San card is only 1000, and Li Si borrows 1000 from Zhang San, but the bank limits the balance to at least 1, so you need to use transactions, otherwise it will result in Zhang San's balance unchanged, while Li Si's balance will increase by 1000.

Select * from bank

Updatebank set balance = balance-1000

Where account = 'Zhang San'

Updatebank set balance = balance + 1000

Where account ='Li Si'

Print 'View the balance before the transfer transaction'

Select * from bank

/ *-- start the transaction (specify that the transaction starts here, and all subsequent T-SQL statements are a whole-- * /

Begintransaction

/ *-- define variables to accumulate errors during transaction execution-- * /

Declare@errorsum int-declares variables that hold the sum of the error numbers of all T-SQL statements

Set@errorsum=0

/ *-transfer: Zhang San's account is 1000 yuan less, Li Si's account is 1000 yuan more-- * /

Updatebank set balance = balance-1000

Where account = 'Zhang San'

Set@errorsum=@errorsum+@@ERROR-whether there are cumulative errors

Updatebank set balance = balance + 1000

Where account ='Li Si'

Set@errorsum=@errorsum+@@ERROR-whether there are cumulative errors

Print 'View balance during transfer transaction'

Select * from bank

/ *-determine whether the transaction is committed or undone based on whether there are errors-* /

If@errorsum0-if there is an error

Begin

Print 'transaction failed, rollback transaction'

Rollbacktransaction

End

Else

Begin

Print 'transaction succeeded, transaction committed, written to disk, saved permanently'

Committransaction

End

Go

Print 'View balance after transfer transaction'

Select * from bank

Go

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.