SQL primary foreign keys and subqueries

Primary key

A database primary key is a combination of one column or column in a table whose value uniquely identifies each row in the table. Such a column or columns is called the table's primary key, which enforces the entity integrity of the table. When you create or change a table, you can create a primary key by defining the PRIMARY key constraint. A table can have only one PRIMARY key constraint, and a column in the PRIMARY key constraint cannot accept null values. Because the PRIMARY KEY constraint ensures unique data, it is often used to define identity columns.

the role of the primary key

The main functions of the primary key are as follows:

(1) Ensure the integrity of the entity;

(2) Speed up the operation of the database;

(3) When a new record is added to a table, the database automatically checks the primary key value of the new record and does not allow the value to be duplicated with the primary key value of other records;

(4) The database automatically displays the records in the table in the order of the primary key values. If no primary key is defined, records in the table are displayed in the order in which they are entered.

The primary key features: Uniqueness, non-nullability.

Example of setting a primary KEY statement:

Code int primary Key, primary key cannot be empty, cannot be duplicated, ensures uniqueness

Example of setting a self-growing primary KEY statement:

Code int primary key identity (1), 1 increase each time, add values without adding this column

To set a foreign key:

Right-click on the table where you want to set the foreign key, select design, and right-click the column name where you want to set the foreign key, such as:

Select Relationship Click, the dialog box appears, click Add, click the ellipsis following the table and column specification, such as:

In the interface that appears, do the following:

Click OK, then click OK, the operation is successful.

Sub-query, also called nested query.

make a query statement as a result set for other SQL statements are used, as with normal tables, as query statements that are treated as result sets are called sub-queries.

There are two types of sub-queries:

One is to return only a single-valued subquery, at which point it can be used where a single value can be used, when a subquery can be considered a function that has a return value;

Another is a subquery that returns a column of values, at which point the subquery can be thought of as a data table that is temporarily present in memory.

Create a new department table, an employee table, and a department column number for each person in the employee table.

Sub-query Example:

Practice:

Create two tables:

1. Elective ID account name Teacher name Teacher age

2. The name of the school number of the selected course ID

I'm going to pick a lesson from teacher a.

I'm going to pick the youngest teacher of my class.

Which class does one student choose and how old is the teacher?

There are a few candidates, teacher A is called what

add an Age column to the student's table by code

I'm going to teach my younger teacher

the youngest of the students in teacher a

All student information for mathematics selection

Student information for all students who choose a teacher older than 20

Create TableStudent (Scodeint Primary Key Identity(1001,1), snamevarchar(Ten), Xuankeint)Create TableTeacher (Tcodeint Primary Key Identity(1,1), Kemuvarchar( -), Tnamevarchar(Ten), ageint)Insert  intoTeacherValues('Mathematics','Zhang San', to)Insert  intoTeacherValues('language','John Doe', $)Insert  intoTeacherValues('English','Harry', -)Insert  intoTeacherValues('Physical','Zhao Liu', -)Insert  intoTeacherValues('Chemical','Feng Qi', -)Insert  intoStudentValues('AA',1)Insert  intoStudentValues('BB',2)Insert  intoStudentValues('CC',3)Insert  intoStudentValues('DD',4)Insert  intoStudentValues('EE',5)Insert  intoStudentValues('FF',5)Select *  fromStudentSelect *  fromTeacherSelectKemu fromTeacherwhereTname='Zhang San'Select Top 1Kemu fromTeacherOrder  by AgeSelectKemu,tname,age fromTeacherwhereTcode=(SelectXuanke fromStudentwhereScode=1002)SelectSname fromStudentwhereXuanke=(SelectTcode fromTeacherwhereTname='Zhang San')Alter TableStudentAddSageintSelect Top 1Sname fromStudentwhereXuanke=(SelectTcode fromTeacherwhereTname='Zhang San')Order  bySageSelect *  fromTeacherwhereAge<(SelectSage fromStudentwhereScode=1003)Select *  fromStudentwhereXuankeinch(SelectTcode fromTeacherwhereKemu='Mathematics')Select *  fromStudentwhereXuankeinch(SelectTcode fromTeacherwhereAge> -)

SQL primary foreign keys and subqueries