SQL view local Variables global variables condition statement transaction trigger

A. View

1. The view is a virtual table, and instead of the actual data, he stores the query statements, but we can do the same thing as the data table.

2. Why use a view? My understanding is: 1. When transmitting data remotely, you can avoid long query characters and reduce traffic. 2. He can simplify complex multi-table nested query statements. 3. Security to prevent unauthorized users from accessing sensitive data, because we can create views to show to users that we want to give them the data to view.

3. Creation of views

Create VIEW Vw_cityasselect CityName from the city   -This query statement can be arbitrarily written, if it is a number of nested query statements, then the use of the following view of the simplicity of the highlight. Should be able to use a short sentence of the query can be the original complex

4. Query for Views

SELECT * FROM Vw_city

5. Because views are stored as query statements, the general view is used only to view the data, and generally does not make any additional deletions to the view. If the table data in the database changes, the data in the view changes as well, because the view is the equivalent of a query statement.

Two. Local variables (using easy-to-write database methods, stored procedures, etc.)

1. Declaration of a local variable (one @)

DECLARE @n int   

2. Assigning values to local variables

Set @s= ' F4 ' set @[email protected]+1  --the assignment of a variable has two methods, one is through set, the other is a select if the variable does not have an initial value, the default is Null,null the result of participating in the calculation or null, This line of @n is equal to Nullselect @n=age from students-if the  variable is assigned by a SELECT, there may be a lot of age results found in the table, here only the last age result of the query is assigned, if set use this method will be an error. Set @n= (select age from Students where id= ' 1 ')--you can also assign a value to him

Three. Global variables

1. Global variables are system-customized, maintained, and we cannot modify the values of global variables. Start with @@ 开头

2. Global Variables List

Select App_name () as W--the current session of the application

SELECT @ @ERROR-Returns the error code (integer) of the last executed Transact-SQL statement (0 if the last statement is not faulted)

SELECT @ @IDENTITY--Returns the last inserted identity value

Select USER_NAME ()--Return user database user name

SELECT @ @CONNECTIONS-Returns the number of connections or attempts to connect since the last SQL startup.

Select GETDATE ()--Current time

SELECT @ @CPU_BUSY/100--Returns the working time of the CPU in milliseconds since the last time SQL was started

Use tempdb SELECT @ @DBTS as W-returns the value of the current timestamp data type for the current database. This timestamp value is guaranteed to be unique in the database.

SELECT @ @IDLE as W--returns the time that SQL has been idle since the last startup, in milliseconds

SELECT @ @IO_BUSY as W--Returns the time, in milliseconds, that SQL has been used to perform input and output operations since the last startup

SELECT @ @LANGID as W--Returns the local language identifier (ID) of the currently used language

SELECT @ @LANGUAGE as W--returns the currently used language name

SELECT @ @LOCK_TIMEOUT as W-the current lock time-out setting for the current session, in milliseconds.

SELECT @ @MAX_CONNECTIONS as W-Returns the maximum number of simultaneous user connections allowed on SQL. The number returned does not have to be the current configured value

EXEC sp_configure--Displays the global configuration settings for the current server

SELECT @ @MAX_PRECISION as W-Returns the precision level used for the decimal and numeric data types, which is the precision currently set in the server. The default maximum precision is 38.

SELECT @ @OPTIONS as W-returns information for the current SET option.

SELECT @ @PACK_RECEIVED as W-returns the number of input packets read from the network since SQL started.

SELECT @ @PACK_SENT as W-Returns the number of output packets that have been written to the network since the last startup.

SELECT @ @PACKET_ERRORS as W-returns the number of network packet errors that occurred on the SQL connection since SQL startup.

SELECT @ @SERVERNAME as W--Returns the name of the running SQL Server.

SELECT @ @SERVICENAME as W--returns the registry key name under which SQL is running

SELECT @ @TIMETICKS as W-returns the number of microseconds in a SQL Server moment

SELECT @ @TOTAL_ERRORS as W-returns the number of disk read/write errors encountered by the SQL Server since it was started .

SELECT @ @TOTAL_READ as W-returns the number of times the SQL Server has read the disk since it started.

SELECT @ @TOTAL_WRITE as W-returns the number of times the SQL Server has written to disk since startup.

SELECT @ @TRANCOUNT as W-Returns the number of active transactions for the current connection.

SELECT @ @VERSION as W-returns the date, version, and processor type of the SQL Server installation.

Four. Conditional statements

1. Conditional statements

if (conditional expression) BEGIN statement ... endelsebegin statement ... end

2. Looping statements

Whilebegin statement .... break/continueend

Five. Business (can't go to the end, back to the origin)

1. Transaction: If you want to execute multiple statements together, if there is a failure you can have them all undone.

BEGIN Tran  --open a thing delete from city  select * FROM City---There is no data here because the previous sentence deleted rollback TRAN  SELECT * from city
   --here in the query, surprised to find that the data resumed, because the previous sentence rollback is to let this transaction rollback effect, BEGIN tran  --open a thing delete from the city  select * from the city--- There is no data here, because the previous sentence deleted the Commit tran  SELECT * from the city  --here in the query, sorry, the data is really gone, even if the bottom of your rollback data will not come back, Because commit executes the data, it really erased the data.

2. Small Business case

---Bank table is the transfer form, the Memoney field is my balance, Hemoney is my friend's account balance, this transaction is done I transfer to a friend begin trandeclare @ersum int        -- Define this local variable in order to record whether the transaction will be wrong, if the error will eventually not be a value of 0, it is completed we determine whether the transaction is missing set @ersum =0 Update Bank set memoney=memoney-1000 where ID =me   ---Take off my card 1000set @[email protected][email protected] @ERROR           -if not wrong here is still 0, if wrong, this will not be 0update bank set hemoney=hemoney+100 where Id=he    ---to my friend card plus 1000set @[email protected][email protected] @ERROR     -- If the result here is 0 will indicate no error if (@ersum <>0)  --true wrong, false yes Beginrollback Tran  ---ROLLBACK TRANSACTION endelsebegincommit Tran    --Performing transactions
End

Six. Triggers

1. Triggers, when a table in the database has been modified (add and remove) is triggered by a single event, such a mechanism is the trigger.

2. Trigger type: After with for, the event is triggered after the statement has finished executing. Instead of: Originally you want to perform one operation, the result performs another operation, the operation that originally wants to perform is replaced.

3. Creation of triggers

Create trigger trigger Name on action table (actual table to be manipulated) For|after|instead of  -Select any of its types, where for is the same as after Update|delete|insert    -- What is the original Action Assql statement    to perform ... ---The SQL statement you want to execute

4. Small case of Trigger

-This example is to delete the data inside the TB1 and back up the deleted data to the Tb1bak table create trigger Tri_bak on TB1  --Declare a tri_bak if the data changes in the TB1 table trigger the trigger after Delete   ---Do the following when the delete is complete asinsert into Tb1bak select *   from deleted---here the deleted is the system automatically generated table that holds the data deleted for the last database table.

5. Small knowledge Point: The process of inserting the data into the database table is inserted into the Insert table of the system table, and the deleted data is inserted into the deleted table, with the new data inserted in the deleted table, and the Insert table is also added. The Insert and deleted tables I have here are automatically generated by the system, not the tables we manipulate.

Seven. If I write wrong, or you want to say something, welcome message.

SQL view local Variables global variables condition statement transaction trigger