Mysql如何添加外键，如何实现连表查询

创建表student和表score，表student设置主键，表score设置表student中属性相同的为外键：

创建student表

create table student
(
    id int primary key ,  #主键约束
    name varchar(20)  ,  #唯一约束
    age int NOT NULL, #非空约束
    sex varchar(2) ,
    address varchar(20) default '广西'  #默认约束
) ;

插入数据：

insert into student values(
-> '13408400106',
-> '龙永震',
-> '24',
-> '男',
-> '广西');

创建score表

create table score
(
    id int primary key ,
    sid int ,             #上个表的主键
    china int ,
    history int,
    english int,
    constraint FK_sid foreign key(sid) references student(id)  #通过外键创建链接
) ;

插入数据：
insert into score values( '120491','13400106' , '100',  '100', '100');

联合查询，将两个表内容显示：

 

#select student.id,student.name,student.age,student.sex,student.address,score.id,score.china,score.history,score.english from student inner join score;

+----------+-----------+-----+------+---------+--------+-------+---------+---------+
|   id     |  name | age | sex | address | id    | china | history | english |
+----------+-----------+-----+------+---------+--------+-------+---------+---------+
| 13400106 | 龙永震 | 24  | 男  | 广西    | 120491 | 100   | 100     | 100     |
+----------+-----------+-----+------+---------+--------+-------+---------+---------+

select * from score inner join student ;

+--------+----------+-------+---------+---------+----------+-----------+-----+------+---------+
| id     | sid      | china | history | english | id       | name      | age | sex  | address |
+--------+----------+-------+---------+---------+----------+-----------+-----+------+---------+
| 120491 | 13400106 |   100 |     100 |     100 | 13400106 | 龙永震    |  24 | 男   | 广西    |
+--------+----------+-------+---------+---------+----------+-----------+-----+------+---------+

 

转载于:https://www.cnblogs.com/longyongzhen/p/6549731.html