用到的数据表:
-----部门表----
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;