SQL Server advanced content table expressions and review

1.Table expression

(1) Use a table as a source or a query result set as a source to process the source and obtain a new data source for query.

(2)Put the table expression inFromClause

(3) to derive a table, use the result set obtained from the table query as a new data source. Pay attention to the following points:

1)The derived table must be enclosed in parentheses.

2) aliases must be obtained for the derived table.

3) all fields in the derived table must have aliases.(The field must be named).

4) only result sets that can be used as derived tables (cursors do not work ).

(4)Write a paragraphSQLStatement.

 1   Select   *   From   2   3   (  4   5       Select  S1.stuid,  6   7   S1.stuname, 8   9       Case  S1.stusex  10   11           When   '  F  '   Then   '  Female  '  12   13           Else   ' Male  '  14       15       End   As  Stusex,  16   17       Datediff (Yy, s1.stubirthdate, Getdate ()) As  Stuage,  18   19       Convert (Varchar ( 10 ), S1.stustudydate, 101 ) As  Stustudydate,  20   21   S1.stuemail,  22   23   S1.stuphone,  24   25   S2.testbase,  26  27   S2.testbeyond,  28   29   S2.testname,  30   31       Convert ( Varchar ( 10 ), S2.testdate, 101 ) As  Testdate,  32   33  S3.classname,  34   35   S3.classdescription  36   37           From  38   39 Student As  S1  40   41               Inner   Join  42   43 Score As  S2  44   45               On S1.stuid =  S2.stuid  46   47           Inner   Join  48   49 Course As  S3  50  51           On S1.classid =  S3.classid  52   53               Where  54   55 S1.stuisdel =  0  56   57 ) As  Tb1  58  59           Where  60   61 Tb1.stuage =  20  62   63           And  64   65 Tb1.stusex =  '  Female  '  

2.Some knowledge points of sqlhelper

(1) sqlconnection is used to connect to the database

(2) sqlcommand is used to executeSQLStatement

1)Parameterized query:Sqlparameter

2) Execution Method

-> Executenonquery (): Execute add, delete, modify, and return the number of rows affected at the beginning.

-> Executescalar ()Read the data in the first column of the First row andObject.

-> Executereader () returnsDatareader

(3) sqldatareaderUsed to read each piece of data

(4) sqldataadapter encapsulates all the above classes