Create foreign key mysql> create table ' Study_record ' ( -> ' ID ' int (one) NOT NULL, -> ' Day ' int not NULL, -> ' status ' char (+) NOT NULL, -> ' stu_id ' int (one) not null, - > PRIMARY KEY (' id '), -> key ' FK _student_key ' (' stu_id '), -> constraint ' Fk_student_key ' FOREIGN KEY (' stu_id ') REFERENCES ' student ' (' id ') -> ); query ok, 0 rows affected (0.05 sec) mysql> alter table study_ record modify id int auto_increment; query ok, 0 rows affected (0.07 sec) Records: 0 duplicates: 0 warnings: 0mysql> insert into study_record (day,status,stu_id) values (1, ' Yes ', 4); query ok, 1 row affected (0.00 sec) mysql> insert into study_ record (day,status,stu_id) values (1, ' Yes ', 1); query ok, 1 row affected (0.00 sec) mysql> mysql> select * from study_record;+----+-----+--------+--------+| id | day | status | stu_id |+----+-----+--------+--------+| 1 | 1 | yes | 4 | | 2 | 1 | Yes | 1 |+----+-----+--------+--------+2 rows in set (0.00 sec) mysql> insert into study_record (day,status,stu_id) values (2, ' No ', 1); query ok, 1 row affected (0.01 sec) mysql> select * from study_record;+----+-----+--------+--------+| id | day | status | stu_id |+----+-----+--------+--------+| 1 | 1 | yes | 4 | | 2 | 1 | Yes | 1 | | 3 | 2 | No | 1 |+----+-----+--------+--------+3 rows in set (0.00 sec) mysql> select * from study_record;+----+-----+--------+--------+| id | day | status | stu_id |+----+-----+--------+--------+| 1 | 1 | yes | 4 | | 2 | 1 | Yes | 1 | | 3 | 2 | No | 1 |+----+-----+--------+--------+3 rows in set (0.00 sec) mysql> delete from study_record where id=3; query ok, 1 row affected (0.02 sec) mysql> select * from study_record;+----+-----+--------+--------+| id | day | status | stu_id |+----+-----+--------+--------+| 1 | 1 | yes | 4 | | 2 | 1 | Yes | 1 |+----+-----+--------+--------+2 rows in set (0.00 SEC) mysql> Multi-Table query: Mysql> create table a ( -> a int not null); query ok, 0 rows affected (0.04 sec) mysql> create table b ( b int not null); query ok, 0 rows affected (0.02 sec) mysql> insert into a (a) values (1); query ok, 1 row affected (0.01 sec) mysql> insert into a ( A) values (2); query ok, 1 row affected (0.00 sec) mysql> insert into a ( A) values (3); query ok, 1 row affected (0.00 sec) mysql> insert into a ( A) values (4); query ok, 1 row affected (0.00 sec) mysql> insert into b ( b) values (3); query ok, 1 row affected (0.02 SEC) mysql> insert into b (B) values (4); query ok, 1 row affected (0.00 sec) mysql> insert into b ( b) values (5); query ok, 1 row affected (0.00 sec) mysql> insert into b ( b) values (6); query ok, 1 row affected (0.00 sec) mysql> insert into b ( b) values (7); query ok, 1 row affected (0.00 sec) mysql> select * from a ; +---+| a |+---+| 1 | | 2 | | 3 | | 4 |+---+4 rows in set (0.00 sec) mysql> select * from b;+---+| b |+---+| 3 | | 4 | | 5 | | 6 | | 7 |+---+5 rows in set (0.00 sec) Inner join syntax is actually just showing the intersection of 2 tables MySQL > select * from a inner join b on a.a = b.b;+---+---+| a | b |+---+---+| 3 | 3 | | 4 | 4 |+---+---+2 rows in set (0.00 sec) the second syntax mysql> select a.*,b.* from a,b where a.a=b.b;+---+---+| a | b |+---+---+ | 3 | 3 | | 4 | 4 |+---+---+2 rows in set (0.00 sec) left join Grammar seeking difference level mysql> select * from a left join b on a.a = b.b ; +---+------+| a | b |+---+------+| 3 | 3 | | 4 | 4 | | 1 | null | | 2 | null |+---+------+4 rows in set (0.00 sec) mysql> select * from b left join a on a.a = b.b;+---+------+| b | a |+---+------+| 3 | 3 | | 4 | 4 | | 5 | null | | 6 | null | | 7 | null |+---+------+5 rows in set (0.00 sec) right Joinmysql> select * from a right join b on a.a = b.b ; +------+---+| a | b |+------+---+| 3 | 3 | | 4 | 4 | | null | 5 | | null | 6 | | null | 7 |+------+---+5 rows in set (0.01 sec) full join mysql does not directly support full join,but is always difficult to us mysql> select * from A left join B on A.a=B.b union select * from A right join b on a.a=b.b;+------+------+| a | b |+------ +------+| 3 | 3 | | 4 | 4 | | 1 | null | | 2 | null | | null | 5 | | null | 6 | | null | 7 |+------+------+7 rows in set (0.01 SEC) mysql>
MySQL Insert foreign key
