注释:主键用蓝色标识,外键用绿色标识
如何创建和记录多个有相互联系的列表呢?
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的语法