Primary key, foreign key and subquery

1 differences between a primary key and a foreign key

1.1 Definitions
Primary Key-uniquely identifies a record, cannot be duplicated, is not allowed to be empty

Foreign key--the foreign key of the table is the primary key of another table, the foreign key can have duplicate, can be empty

1.2 Effects

Primary key--to ensure data integrity

Foreign key--used to establish contact with other tables.

1.3 Numbers:

Primary key--the primary key can only have one

Foreign key--a table can have multiple foreign keys

2 PRIMARY KEY statements
code int PRIMARY KEY IDENTITY (.),
--primary Key identifies primary key
--identity (first) from the beginning, each additional row plus one;
Inserting data is no longer written.

3 FOREIGN Key
3.1 Foreign key value rule: null value or reference primary key value.

(1) When a non-null value is inserted, it cannot be inserted if the value is not in the primary key table.

(2) When updating, you cannot change the value that is not in the primary key table.

(3) When you delete a primary key table record, you can either cascade Delete or reject it when you select a foreign key record while the foreign key is being constructed.

(4) When updating a primary key record, there is also a selection of cascading updates and rejected executions.
3.2 Settings
Select the table, right-click: Design, select the field that you want to set the foreign key, choose Relationships, select Add
, select the right ellipsis, and select the appropriate relationship at the table and column specifications

3 Sub-query
In the SQL language, when one query statement is nested within the query condition of another query, it is called a subquery
3.1 (1) subqueries are enclosed in parentheses;
(2) Place the subquery on the right side of the comparison operator;
(3) Do not use the ORDER BY clause in a subquery.
There can be only one ORDER BY clause in a SELECT statement, and it can only be the last clause of the main SELECT statement.
But if you have top N, you can have an order by.
3.2 Paged Query: Select Top 5 * from EMP where empno in
(select TOP 5 empno from emp ORDER BY empno DESC) Order by Empno)

3.3 Single-line subquery: A single-line subquery refers to a subquery that returns only one row of data.
(1) Select Code,name,sex,age, (select Bname from Bumen where BUMEN.BCODE=RENYUAN.BC),
(select Bceo from Bumen where BUMEN.BCODE=RENYUAN.BC) from Renyuan
(2) Select code from Renyuan where age= (select Max (age) from Renyuan where sex= ' man ')
3.4 Multiline subquery: A multiline subquery is the result of a subquery that returns multiple rows of data. When
A multiline comparison symbol (In,all,any) must be used to compare the subquery results in the conditional statement of the main query statement.
Select Top 5 *from Renyuan where code not in (select Top 5 code from Renyuan)

Primary key, foreign key and subquery