11-04 C # Base Database--connection query, federated query, index

One, Sub-query supplement :

usage of Exists:

Select*FromhahawhereExistsSelect*FromBumen whereBumen.Code=haha.Bumen, andBumen.Name= sales  >< Span style= "font-family: ' Times New Roman '; font-size:12pt; Mso-spacerun: ' Yes '; >35-- equivalent to looping nested statements

General Usage: =

Select*FromhahawhereBumen=(SelectCodeFromBumenwhereName=‘Sales Department') and age>-- the query in parentheses is used as parameters

Second, connection query

A subquery can be used as a condition not only in a place

Original sentence: sentence one: Select name,sex,age,bumen from haha

Replace the sentence, replace the bumen in the haha with the code in the Bumen: Sentence two: Select Name,sex,age, (select name from Bumen where Bumen.code=haha.bumen) as department, ( Select CEO from Bumen where bumen.code=haha.bumen) from haha

The above is the format of the connection query.

Simplified connection Query Statement format: Select.haha.name,sex,age,bumen.name,ceo from Haha,bumen where Haha.bumen=bumen.code

It can also be used in the middle of select. Equivalent to a conversion function, the query must be a row of one column.

Tables and tables which table is the first to execute which table in front of the query, must be used when there is a connection between the table and the table.

1. Horizontal joint

Stitching Query--join...on ... (Display two tables together, up to three tables)

Select Haha.name,sex,age,bumen.name,ceo from haha

Join Bumen on haha.bumen=bumen.code--adds the statement after join to the table above.

The "caveats" query to the result must be in select: To be displayed.

You can add three modifiers before join:

1) Full Join...on

Plus full: All the data is listed, showing all the data in two tables, even if the data in one of the tables has no relation to the other table.

2) LEFT join: On

Plus left: Displays all the data from the table on the right, in the order of the table on the left, and the data from the table on the side is arranged by relationship, and the right side is not displayed.

3) Right Join: On

Add right: (through the relationship display) to display all the data of the table on the right, in the order of the table in the left, while the data on the table on the other side of the arrangement, left no relationship is not displayed.

2. Vertical Union Union:

One table holds the existing data, and the other table holds the deleted data.

Columns must be consistent.

1), haha >40 years of age or age in the table

Select * from haha where age>40

Union

Select * from haha where age<30

2), the Ceo,code query in the Name,bumen and bumen tables in the Haha table appears in the same table

Select Name,bumen from haha

Union

Select Ceo,code,from Bumen

"Precautions" 1. Has the function of automatic weight-down.

2. The data type must correspond to the same

"Case 1"--Create Student Information table: School number, name, class, gender, language teacher number, math teacher number, English teacher number

--Create a teacher's table: Teacher number, name, course, gender, date of birth

--Create a score table: Chinese score, math score, English score, student study number

--Insert virtual data, then query operation:

Constraints except primary key / foreign key constraints , There are other constraints

1, set the uniqueness of the constraint (as a candidate index) set up in advance to prevent errors in entry;

As long as a column with uniqueness is set, it can be used as a foreign key.

Method Two : The column name that you need to set the unique key directly adds unique.

Example: CID varchar (a) unique

2. View the query code

3. Check constraint

An expression can be concatenated with and (multiple expressions can be added), equivalent to the where condition

11-04 C # Base Database--connection query, federated query, index