SQL ———— Advanced Query

Advanced Query

--Connection Query select * FROM table 1, table 2 ———— form Cartesian product

SELECT * FROM table 1, table 2 where table 1. primary KEY = Table 2. Foreign key ———— The primary foreign key position can be interchanged

--join on Inner Connection

Format: SELECT * FROM table 1 join foreign key on table 1. Primary KEY = Table 2. Foreign key

--to find out which students have a number of subjects

Select Student.sname,course.cname,score.degree from student join score on Score.sno=student.sno join course on COURSE.CNO = Score.cno

--Right connection

The table on the right must be full, if there is no information corresponding to the table on the left, the empty value

Format: SELECT * FROM table 1 Right Join table 2 on table 1. Primary KEY = Table 2. Foreign key

--Left connection

The left table must show all, and if there is no information corresponding to the table on the right, the fill-in value

SELECT * FROM table 1 left JOIN table 2 on table 1. Primary KEY = Table 2. Foreign key

--Full connection

Both the left and right tables are displayed completely

Format: SELECT * FROM table 1 full join table 2 on table 1. Primary KEY = Table 2. Foreign key

--Joint query

Union display for two or more identical tables with the same structure isolated

Format: Select column 1, column 2 from table 1 Union Select column 1, column 2 from table 2

--------Sub-query------------------

--the result of the subquery as a condition of the parent query select * FROM Info

--Unrelated subqueries

Sub-query execution is independent, and the parent query is not related (nothing to the parent query)

SELECT * FROM info where year (Birthday) = (select years (Birthday) from info where code= ' p005 ') ———— Query and study number ' p005 ' information about the same person who was born

--related sub-query

For example: SELECT * from teacher ———— the computer department and Electronic Engineering department of different titles of teachers information

SELECT * from teacher t1 where depart= ' computer system '

And NOT EXISTS (SELECT * from teacher T2 where depart= ' electronic Engineering Department ' and t1.prof = t2.prof)

Union

SELECT * from teacher T1 where depart= ' electronic Engineering Department '

And NOT EXISTS (SELECT * from teacher T2 where depart= ' computer system ' and t1.prof = t2.prof)

--Inquire about other students ' information in addition to the highest score for each course.

SELECT * FROM Score

SELECT * from score S1 where degree not in (select MAX (degree) from score S2 GROUP by CNO have s1.cno = s2.cno)

Paging SELECT * FROM Car

Select Top 5 * from Car-———— Top 5 data, first page

Select Top 5 * from car where Code not in (select Top 5 Code from car) ———— second page of data

Select Top 5 * from car where Code not in (select top of code from car) ———— third page of data

Select Top 5 * from car where Code not in (select Top (5*2) Code from car) ———— third page of data

Select Ceiling (COUNT (*)/5) from Car ———— Total pages

SELECT * from Car where condition limit skips a few data, takes a few data ———— the page of MySQL

SQL ———— Advanced Query