8.2 多表关系---参考黑马程序员

用到的数据表：

-----部门表----
create table dept(
    id   INT  AUTO_INCREMENT   PRIMARY KEY,
    name  VARCHAR(50) NOT  NULL 
);

INSERT INTO  dept  VALUES
(1, '研发部'), 
(2, '市场部'),
(3, '财务部'), 
(4, '销售部'), 
(5, '总经办');

SELECT  * FROM  dept;

----员工表----
CREATE  TABLE  emp(
    id  INT   AUTO_INCREMENT   PRIMARY KEY,
    name  VARCHAR(50) NOT  NULL,
    age  INT ,
    job  VARCHAR(20) ,
    salary  INT ,
    entrydate   DATE ,
    managerid  INT ,
    dept_id   INT 
);

INSERT INTO emp  VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '市场分析员',6600, '2004-10-12', 1,2);

SELECT  * FROM  emp;

----学生表---

CREATE  TABLE   student(
id    BIGINT  PRIMARY  KEY  AUTO_INCREMENT,
name   VARCHAR(4)  NOT NULL
);

 INSERT INTO student VALUES
 (202201010244, '小明'),
 (202201050445, '小玉'),
 (202203090101, '小青'),
 (202203090102, '小兰'),
 (202203090134, '小红');

----课程表---
CREATE   TABLE  course(
id  INT  PRIMARY KEY  AUTO_INCREMENT,
name  VARCHAR(4) NOT NULL
);

 INSERT INTO course (id, name)VALUES 
(1, '数据库'),
(2, 'java'),
(3, '音乐鉴赏'),
(4, '老子导读'),
(5, '论语导读'),
(6, '中国地理');

----选课表--

CREATE  TABLE  student_course(
id  INT  PRIMARY KEY,
studentid   BIGINT,
coureseid   INT
);

INSERT INTO  student_course(id,studentid,coureseid)VALUES
(1,202203090134,'1'),
(2,202203090134,'2'),
(3,202203090134,'3'),
(4,202201010244,'1'),
(5,202201010244,'2'),
(6,202201010244,'4'),
(7,202201050445,'1'),
(8,202201050445,'2'),
(9,202201050445,'3'),
(10,202203090101,'1'),
(11,202203090101,'2'),
(12,202203090101,'5'),
(13,202203090102,'1'),
(14,202203090102,'2'),
(15,202203090102,'6');

#添加外键

ALTER  TABLE  student_course  ADD  CONSTRAINT  fy_stu_course1  FOREIGN KEY  (studentid)  REFERENCES  student(id);

ALTER  TABLE  student_course  ADD  CONSTRAINT  fy_stu_course2  FOREIGN KEY  (coureseid)  REFERENCES  course(id);
  

-----------学生信息表------
CREATE TABLE  xinxi_2202(
id     BIGINT(12)   PRIMARY KEY,    
name    VARCHAR(3)    DEFAULT  '风晴雪' , 
age      TINYINT(4)     DEFAULT 18,  
sex          ENUM('男','女')  NOT NULL,
hobby     SET('探寻新知','读书','跳舞','听音乐','磨砺自己变得更强','买花')  DEFAULT '听音乐' ,
add_time      DATE
);

 INSERT INTO xinxi_2202  VALUES
(1, '云清', 18, '男', '听音乐', '2022-09-13'),
(2, '白云', 19, '男', '跳舞', '2022-09-13'),
(3, '柳如是', 20, '女', '跳舞', '2022-09-13'),
(4, '王小明', 17, '男', '读书', '2022-09-13'),
(5, '夏天', 18, '女', '磨砺自己变得更强', '2022-09-13'),
(6, '柳云飞', 19, '男', '探寻新知', '2022-09-13');

-----------学生成绩表------
CREATE  TABLE   score_2202(
id                BIGINT(12)  PRIMARY KEY,    
name           VARCHAR(3) ,
Yuwen          FLOAT,
Gaoshu          FLOAT  ,
English          FLOAT,  
Dababase      FLOAT ,
Java              DECIMAL,
kaoshi_time      DATE
);

INSERT INTO score_2202 VALUES
(1,'云清',50.4,20.3,100,86.9,100,'2023-6-24'),
(2,'白云',64,78,96,63,100,'2023-6-28'),
(3,'柳如是',78,89,56,23,45,'2023-6-27'),
(4,'王小明',100,null,96,86,75,'2023-06-20'),
(5,'夏天',3,70,89,25,null,'2023-06-20'),
(6,'柳云飞',89,85,null,45,98,'2023-6-24');

---------添加外键----

ALTER  TABLE  score_2202  ADD  CONSTRAINT  fy_xinxi_score  FOREIGN KEY  (name)  REFERENCES  xinxi_2202(name)   ON UPDATE  CASCADE  ON DELETE CASCADE;