MySQL(7)

注释：主键用蓝色标识，外键用绿色标识

如何创建和记录多个有相互联系的列表呢？

create database sql_tutorial;
use sql_tutorial;
#定义表格（属性）
create table `employee`(
  `emp_id` int primary key,
  `name` varchar(10),
  `birth_date` date,
  `sex` varchar(1),
  `salary` int,
  `branch_id` int,
  `sup_id` int
  );
#当列表中字段名被定义为foreign key时，要求foreign key关联的表格和表格的主键已经被定义
create table `branch`(
   `branch_id` int primary key,
   `branch_name` varchar(10),
   `manager_id` int,
   foreign key(`manager_id`) references `employee`(`emp_id`) on delete set null
   );
#若foreign key关联的表格和表格的主键没有被定义，那么用alter语句定义属性
alter table `employee` 
add foreign key(`sup_id`) 
references `employee`(`emp_id`)
on delete set null;
alter table `employee`
add foreign key(`branch_id`)
references `branch`(`branch_id`)
on delete set null;
create table `client`(
   `client_id` int primary key,
   `client_name` varchar(10),
   `phone` varchar(20)
   );
#列表中有不止一个primary key时，primary key的定义格式如下：
create table `works_with`(
   `emp_id` int ,
   `client_id` int,
   `total_sales` int,
   primary key(`emp_id`,`client_id`),
   foreign key(`emp_id`) references `employee`(`emp_id`) on delete cascade,
   foreign key(`client_id`) references `client`(`client_id`) on delete cascade
   );
#记录属性为外键的列的值时，要求该列关联的表格已经被定义，主键的值已经被记录，
#否则用null填充
insert into `branch` values(1,'研发',null);
insert into `branch` values(2,'行政',null);
insert into `branch` values(3,'资讯',null);
insert into `employee` values(206,'小黄','1998-10-08','F',50000,1,null);
insert into `employee` values(207,'小绿','1999-04-08','M',70000,2,206);
insert into `employee` values(208,'小黑','1974-10-08','F',60000,3,206);
insert into `employee` values(209,'小白','1935-11-08','F',30000,3,207);
insert into `employee` values(210,'小蓝','1999-09-04','M',84000,1,207);
#或者用修改update语句修改表格的外键的值
update `branch`
set `manager_id`=208
where `branch_id`=3;
update `branch`
set `manager_id`=207
where `branch_id`=2;
update `branch`
set `manager_id`=206
where `branch_id`=1;
insert into `client` values(400,'阿狗','234567');
insert into `client` values(401,'阿猫','345623');
insert into `client` values(402,'阿猪','680972');
insert into `client` values(403,'旺来','123765');
insert into `client` values(404,'路西','986543');
insert into `works_with` values(206,400,70000);
insert into `works_with` values(207,401,80000);
insert into `works_with` values(208,402,50000);
insert into `works_with` values(209,403,70000);
insert into `works_with` values(210,404,60000);

语法内容的总结：

1：foreign/primary  key的定义

2：foreign/primary  key的语法