Database-T-SQL Statement-Advanced query

1. Connection query (extension of the column, the two tables are connected to display together) (the data volume is large, less use of such queries, slow, will form a Cartesian product)

The first form of

SELECT * FROM Info,nation #会笛卡尔积

SELECT * from info,nation where info.nation = Nation.code #加上筛选条件

Select Info.code,info.name,sex,nation.name,birthday from info,nation where info.nation = Nation.code #查询指定列 If two tables have the same column name , you need to add a table name such as Info.code, if not the same name, you can directly write the column names

Select Info.code as ' codename ', Info.name as ' name ', sex as ' gender ', nation.name as ' national ', Birthday as ' birthday ' from Info,na tion where info.nation = Nation.code #改表头 after the column name with AS ' ' Change the table header

The second form of:

SELECT * from Info join Nation #会形成笛卡尔积

SELECT * from Info join Nation on info.nation = Nation.code # Join in on after add filter condition

2. Federated queries (expansion of rows)

SELECT * from Info where Nation = ' n002 '

Union

SELECT * from Info where Code = ' p002 '

3. Subquery: In an SQL statement, there are at least two queries, one of the results of a query as another B's query condition A is called the inner query or subquery, B is called the outer query or the parent query

3.1 Unrelated subqueries

Unrelated subquery features: subqueries can be executed separately

Query people for "Han" personnel information

SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

SELECT * from Info where Nation in (select Code from Nation where name = ' Han ' or name = ' hui ') #结果不止一条时 with in connection. not is in the query results and in the opposite.

3.2 Related subqueries

Inquire about the same series of vehicles with lower fuel consumption than average fuel consumption

Select AVG (oil) from Car where Brand = "

SELECT * from Car where oil < average fuel consumption

SELECT * from Car a where A.oil < (select AVG (b.oil) from car b where B.brand = A.brand)

Database-T-SQL Statement-Advanced query