SQL Getting Started Classic (fifth edition) Ryan Stephens study notes (VI, seven, eight, nine, 10 chapters)

Sixth chapter: Managing Database Transactions

A transaction is a DML completed by the fifth data manipulation language, which is an operation or modification to a database lock.

• All transactions have a start and end
• Transactions can be saved and revoked
• If a transaction fails halfway through, any part of the transaction is not logged to the database

Control services: Commit, rollback, savepoint;

6.1 Commit

Required before operation: Set autocommit = 0; Otherwise, all operations are automatically committed.

Commit: Used to save changes made by the firm to the database, which saves all transactions after the last commit or ROLLBACK command to the database.

6.2 Rollback

Used to revoke a command that has not yet been saved to the database, it can only be used to revoke a transaction after the last commit or ROLLBACK command

6.3 SavePoint

The savepoint, a logical point in the transaction process, can roll back the transaction to this point.

SavePoint Point_name;

Update Insert Delete

Rollback to Point_name; Revert to a point

Release SavePoint Point_name; Delete a point

Eighth: Using operators to classify data

1. Comparison operators

Equal/not equals = <>

Greater than/less than > <

Greater than or equal to/less than or equal to >= <=

2. Logical operators

is Null:where name=null;

Between:where cost between and 20; Contains 10 and 20

In:where Cost in (' 5 ', ' 30 ', ' 39 ');

Like:%: stands for 0, one, multiple characters

_: Represents a number or character

Match 200 start value where salary like ' 200% '

Matches a value that contains 200 where salary like '%200% '

Match the second three characters is 0 where salary like ' _00% ';

Exists:select cost from the TBL where exists (select cost from TBL where cost>100);

If cost>100 exists, all cost is displayed.

3. Connection operator

and

Or

4. Negation operator

Not between

Not-like

Not in

NOT EXISTS

NOT NULL

5. Arithmetic operators

Nineth Chapter: Summarize the data obtained by the query

1. Summary function:

Count: Statistics A field that does not contain a null record, which returns a numeric value

Select COUNT (*) from Stu; Statistics Stu all rows in a table

Select count (Salary) from Stu; Counts the number of rows in Stu for which all salary are non-empty

SQL Getting Started Classic (fifth edition) Ryan Stephens study notes (VI, seven, eight, nine, 10 chapters)