How to use primary keys and foreign keys in MySQL statements

1. Primary key:

1.1) Primary key field definition:

In a database table, if there is a set of fields that can uniquely determine a records, you can design them as the primary key fields of the table.

Example: If you want to create a person's information table (fields: name, age, place of origin, work unit...), then the ID number is the only way to identify you. , so the ID number is the primary key.

1.2) Create:

Primary key field name primary key

1.3) Primary key selection principle;

In principle, use one field as the primary key , and it is recommended to use fields with no business meaning as primary keys, such as id, etc.

1.4) How the primary key value is generated:

The type and length of the primary key field are determined by how the primary key value is generated:

1.4.1)

Auto-increment: With the help of the database's own primary key generation mechanism
The numerical length is determined by the amount of data

Creation: primary key field name primary key not null auto_increment

Features:

Advantages: High development efficiency, because it can be automatically numbered and stored in order for easy retrieval. It takes up little space and can save developers time.

Disadvantages: Low operating efficiency and inflexibility. Because of its automatic growth, it will be troublesome to manually insert or import data with a specified ID. If the amount of data is too large, the database will automatically increase the primary key. The time will become longer and the operation of querying the database will become slower. If there is an operation to merge tables, there is a probability that the primary key will be duplicated.

1.4.2)

assighed: The programmer manually generates the primary key value, the only non-null, algorithm.
hi/low: The length of the numeric type is determined by the amount of data
UUID : The string length is 32 bits

public class UUIDUtil {//java中自带UUID的算法
    public static  String getUUID(){
        return UUID.randomUUID().toString().replaceAll("-", "");
    }
}

Features:

Advantages: The primary key value generation is very flexible and can ensure uniqueness under any circumstances, and data migration is convenient.                                                                                                                                                                                                         

Disadvantages: takes up a lot of space, is difficult to remember, and will reduce performance.

1.4.4)

Joint primary key: determined by the types and lengths of multiple fields

2. Foreign key:

2.1) Foreign key definition :

Usually used to establish a relationship between two tables. The main purpose of foreign keys is to maintain data integrity between two independent instances of an entity.

2.2) Foreign key (constraint) creation (not recommended, generally no foreign key constraints, only foreign key conventions):

alert table primary key table name add constraint FK_ID (foreign key Name) foreign key (foreign key field name) references External table name (primary key field name)

2.3) The occurrence of foreign keys:

Supplementary:
When creating the table, There may be business relationships (association relationships) between tables, in which case foreign keys will be generated.

Relationship types that exist in the association relationship:

1 to 1: One record in one table (A) can only correspond to one record in another table (B), and the other One record in one table (B) can only correspond to one record in one table (A).

          eg: Software Engineering 2777 class and squad leader, citizenship and ID card. (Product table and description table of the product)

1 to many: One record in one table (A) can correspond to multiple records in another table (B), and the other table ( One record in B) can only correspond to one record in one table (A).

        eg: The product classification table and the product represent a one-to-many relationship. There can be multiple products under one product category, students and classes

Many-to-many: (There are two tables, One piece of data in table A corresponds to multiple items in table B, and at the same time, one item of data in table B corresponds to multiple items in table A)

            eg: customer table and department table.

Related query (using foreign keys to query data in multiple tables at the same time)

Type of related query:

Inner join: query all data that meets the conditions, and require the results to be in There are corresponding records in both tables

Left outer join: Query all qualified data in the left table, even if there are no corresponding records in the right table

Right outer join: Query all qualified data in the right table, even if there is no corresponding record in the left table

2.3.1) In one-to-many:

Example: A(1) --------- B (n)
Father Watch
TBL_STUDENT TBL_CLASS
ID name Class_id ID name
1001 ZS 111 CLASS1
1002 LS 111 222 Class2
1003 ww 222
1004 zl

 //查询所有姓张的学生的id,name和所在班级name
                                             select s.id,s.name,c.name as className
                                             from tbl_student s
                                              join tbl_class c on s.class_id=c.id//假如外键不可以为空
                                              where s.name like 'z%'

There are corresponding records in the table, using inner connections. If you want to retrieve all matching left table records, you can use left outer join query

2.3.2)在一对一中：

tbl_person tbl_card
id name id name
1001 zs 1001 card1

特点：
a)共享主键：(不推荐)
添加数据：先添加先产生的表，再后产生的表记录
删除数据：先删除后产生的表记录，再删除先产生的表记录
查询数据：无需进行连接查询：

  //查询zhangsan的驾照信息  1001
                   select *
                   from tbl_card
                   where id='1001'

2.3.3)多对多：

tbl_student tbl_course
id name id name
1001 zs 111 java
1002 ls 222 mysql
tbl_student_course_relation
student_id course_id
1001 111
1001 222
1002 111
1002 222
特点：

添加数据时，先添加父表记录(tbl_student,tbl_course),再添加子表(tbl_student_course_relation)记录;
删除数据时，先删除子表记录(tbl_student_course_relation),再删除父表记录(tbl_student,tbl_course)
查询数据时，可能会进行关联查询：

  //查询所有姓张的学生的id,name,和所选课程的name
              select s.id,s.name,c.name as courseName
              from tbl_student s
                          join tbl_student_course_relation scr on s.id=scr.student_id
                          join tbl_course c on scr.course_id=c.id
              where s.name like 'z%'

三.主键和外键的区别总结:

The above is the detailed content of How to use primary keys and foreign keys in MySQL statements. For more information, please follow other related articles on the PHP Chinese website!