Shulou(Shulou.com)05/31 Report--

SQL Server in the sub-query and table link concept and what is used, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. The concept of subquery

(1) the judgment in the where clause of the query is based on the result of another query, so it constitutes an external query and an internal query, which is the self-query.

(2) Classification of self-query 1) independent subquery-> independent single-valued (scalar) subquery (=)

The code is as follows:

Select testID,stuID,testBase,testBeyond,testPro from Score where stuID= (select stuID from Student where stuName='Kencery')

-> Independent multi-valued subquery (in)

The code is as follows:

Select testID,stuID,testBase,testBeyond,testPro from Score where stuID in (select stuID from Student where stuName='Kencery')

2) related subquery

(3) points for attention in writing subqueries

1) the subquery is generous with parentheses, and if necessary, you need to alias the table and use the "as name".

two。 Table connection\

(1) Table linking is to combine multiple tables into one table, but not to merge result sets like union, but table linking can merge different tables and share fields.

(2) Cross join of table joins (cross join)

1) create two tables

The code is 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) Annotation cross-join is to match all the data in the first table with all the data in the second table one by one to form a new table.

4) the self-crossover implementation executes the insert SQL statement:

The code is as follows: insert into testNum1 values

Execute self-crossover SQL statements:

The code is as follows: select t1.num1 cross join testNum2 as T2. Num2 from testNum1 as T1 cross join testNum2 as T2

5) another way of writing: select * from testNum1,testNum2 is not recommended. First of all, there is a relatively new syntax. The defect is that the comma is not clear, and this syntax can be used with both internal and external connections. If you use join declaration, you can report an error in syntax, but using this syntax may skip this syntax check because of some grammatical errors, which may be interpreted by SQL Server as cross-joins.

(3) join within table joins

1) Internal links add a constraint on the basis of cross-connections

2) Syntax: select * from Table 1 inner join Table 2 on Table 1. Field = Table 2.

The field copy code is 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) join outside the table join

1) execute the following SQL statement

The code is 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 inner join tblOther as' Zhang San', 1), (2) insert into tblOther values (1 from tblMain as'Li Si'), (2) select * from tblMain as T1 inner join tblOther as T2 on t1.fid=t2.id

2) on the basis of the inner connection, one thing to do is to show the Java in the tblOther. At this time, we should use the outer connection, which has the left outer connection and the right outer connection. 3) what is the difference between a left connection and a right connection? The difference is that the * * connection takes the * * table as the main table, and on the basis of the internal connection, the information of the table without data should be displayed for the user to view, so the main table is the table to be displayed. The left outer connection and the right outer connection are respectively in the front of this table is the left table, the back table is the right table, the left connection uses left join, and the connection uses right join. 4) if the following SQL statement is re-executed above, the Java in the tblOther table will be displayed.

The code is as follows:

Select * from tblMain as T1 right join tblOther as T2 on t1.fid=t2.id

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.