1. Sub-query Concepts
(1) is the query in the WHERE clause in the judgment based on the results of another query, so that constitutes an external query and an internal query, this internal query is from the query.
(2) Self-query classification
1 Independent Sub-query
-> independent Single value (scalar) subquery (=)
Copy Code code as follows:
Select
Testid,stuid,testbase,testbeyond,testpro
From Score
Where Stuid= (
Select Stuid from Student where stuname= ' Kencery '
)
-> Independent multivalued subquery (in)
Copy Code code as follows:
Select
Testid,stuid,testbase,testbeyond,testpro
From Score
where Stuid in (
Select Stuid from Student where stuname= ' Kencery '
)
2) Related subqueries
(3) Write the child query considerations
1 The subquery is lavish with a curly brace, it is necessary to alias the table, use "as name" can be.
2. Table Connection \
(1) A table link is a combination of multiple tables, but not a union of result sets, but a table link can combine different tables and share fields.
(2) Cross connection of table connection (cross join)
1) Create two tables
Copy Code code as follows:
Use Test
Go
CREATE TABLE TestNum1
(
NUM1 int
);
CREATE TABLE TestNum2
(
Num2 int
);
INSERT into TESTNUM1 values (1), (2), (3)
INSERT into TESTNUM2 values (4), (5)
2 Execute the cross connected SQL statement
SELECT * from TestNum1 Cross join TESTNUM2
3) Notes
Cross-Connect is to match all the data in the first table with all the data in the second table once, forming a new table.
4) Self-crossover implementation
Execute INSERT SQL statement:
Copy Code code as follows:
INSERT into TESTNUM1 values (4), (5), (6), (7), (8), (9), (0)
Execute the SQL statement from the intersection:
Copy Code code as follows:
Select t1.num1,t2.num2 from TESTNUM1 as T1 cross join TESTNUM2 as T2
5) Another way of writing:
SELECT * from Testnum1,testnum2 is not advocated, first there is a relatively new syntax, the flaw is a comma ambiguous, and this syntax with both internal and external connections can be used, if the join declaration, then the syntax error can be wrong, but use this syntax, The check that skipped this syntax may be interpreted by SQL Server as a cross connection because of a partial syntax error
(3) connection within the table connection
1 The inner link is to add a constraint on the basis of a cross connection
2 Syntax: SELECT * FROM table 1 inner JOIN table 2 on table 1. field = table 2. Field
Copy Code code as follows:
Selects1.stuid,
S1.stuname,
S1.stusex,
S2.testbase,
S2.testbeyond
From Student as S1
INNER JOIN Score as S2
On S1.stuid=s2.stuid
where s1.stuisdel=0;
(4) Outside the table connection
1 Execute the following SQL statement
Copy Code code as follows:
CREATE TABLE Tblmain
(
ID int,
Name nvarchar (20),
FID int
);
CREATE TABLE Tblother
(
ID int,
Name nvarchar (20)
)
INSERT into Tblmain values (1, ' John ', 1), (2, ' Dick ', 2)
INSERT into Tblother values (1, ' C + + '), (2, '. NET '), (3, ' Java ')
SELECT * FROM
Tblmain as T1
INNER JOIN
Tblother as T2
On
T1.fid=t2.id
2) on the basis of the connection, in doing one thing, that is, the Java in the Tblother is also displayed, this time to use the external connection, outside the connection has left outer connection and right outer connection.
3 What is the difference between the left and right connections?? The difference is that * * connection is to * * table as the main table, on the basis of the connection, will not have the data of the table information or to be displayed for users to view, then the main table is to display the table. The left and right outer joins are in the front of the table is the left table, in the following table is the right table, the left join with the join, a connection using the right-hand join.
4 The following SQL statement is executed above, and the Java in the Tblother table is displayed.
Copy Code code as follows:
SELECT * FROM
Tblmain as T1
Right join Tblother as T2
On T1.fid=t2.id
