SQL Basics 2

Sub-query
Stand-alone sub-query
correlated subqueries in (SQL) exists (SQL)

Connection Query
Cross joins and,
Inner link INNER JOIN
Outer link LEFT OUTER join
Right outer join

Cartesian product on for screening

Views View
Query Statement Encapsulation
Create View name creation
As
Statement.....
ALTER VIEW name modification
As
Statement.....
Benefits of the View 5 tables 2 views can be divided into database user view permissions to ensure security
Views do not store data if an index is created on the view then the indexed view will have data
The query for a view is unordered, and the sort is not a result set.

TSQL Programming
--tsql programming
--Declaring variables
DECLARE @n int=1001
--Assigned value
Set @n=100 the first type
Select @[email protected]+1 the second type
Print @n

Set @n = (select count (*) from tblstudent) no problem.
Select @n=count (*) from tblstudent no problem

Set @n = (select Tsage from Tblstudent) when a subquery returns more than one worth of error
Select @count =tsage from Tblstudent This assigns the last value to the variable

Transaction
BEGIN Tran
declare @num int = 100
SELECT * FROM Tblstudent
If @num = 100
Rollback
Else
Commit

Stored Procedures
Excellent:
Faster execution-all compiled
Allows modular programming-similar method multiplexing
Safety--Prevent injection
Reduce network traffic-the name of the value transfer stored procedure
Disadvantages:
Put too much business logic in the database

Stored procedures for the database system beginning with the XP SP
Execute stored procedure exec sp_database Execute stored procedure

CREATE PROCEDURE Usp_aaa
@param int=0 Parameters
As
Begin
SELECT * FROM Tblarea
End
EXEC usp_aaa

Trigger
A trigger is a special type of stored procedure that cannot pass a parameter event trigger call
Inserted table and deleted table
Inserted table when insert or update is used
Deleted table is used when delete or update

After trigger ex post trigger
Instead OF trigger substitution triggers

Create Trigger Tri_delete on Tblclass
After delete
As
Begin
--To back up the record you just deleted to another table
INSERT INTO Tblclassdelete
Select Tclassname,tclassdesc from deleted
End

Delete Tblclass where tclassid=2

SELECT * FROM Tblclass
SELECT * FROM Tblclassdelete

SQL Foundation 2