2021-05-22

image.png
image.png
image.png
image.png
image.png
image.png image.png
image.png
image.png image.png image.png image.png image.png image.png
image.png
-- 使用school数据库实例
-- 先删除表
DROP TABLE IF EXISTS Course;
-- 再创建表
CREATE TABLE Course (
c_id VARCHAR(20) COLLATE utf8mb4_croatian_ci NOT NULL COMMENT '课程号',
c_name VARCHAR(20) COLLATE utf8mb4_croatian_ci NOT NULL DEFAULT '' COMMENT '课程名',
t_id VARCHAR(20) COLLATE utf8mb4_croatian_ci NOT NULL COMMENT '教师号',
PRIMARY KEY (c_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;
/*Table structure for table Score /
DROP TABLE IF EXISTS Score;
CREATE TABLE Score (
s_id INT(4) NOT NULL COMMENT '学号',
c_id VARCHAR(20) COLLATE utf8mb4_croatian_ci NOT NULL COMMENT '课程号',
s_score INT(3) NOT NULL COMMENT '成绩',
PRIMARY KEY (s_id,c_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;
/Table structure for table Student /
DROP TABLE IF EXISTS Student;
CREATE TABLE Student (
s_id INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
s_name VARCHAR(20) COLLATE utf8mb4_croatian_ci NOT NULL COMMENT '姓名',
s_birth VARCHAR(20) COLLATE utf8mb4_croatian_ci DEFAULT NULL COMMENT '生日：yyy-MM-dd',
s_sex VARCHAR(10) COLLATE utf8mb4_croatian_ci DEFAULT NULL COMMENT '性别：男、女',
PRIMARY KEY (s_id)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;
/Table structure for table Teacher */
DROP TABLE IF EXISTS Teacher;
CREATE TABLE Teacher (
t_id VARCHAR(20) COLLATE utf8mb4_croatian_ci NOT NULL COMMENT '教师号',
t_name VARCHAR(20) COLLATE utf8mb4_croatian_ci NOT NULL DEFAULT '' COMMENT '教师名',
PRIMARY KEY (t_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;
DROP VIEW IF EXISTS v_student ;
CREATE VIEW v_student
AS
SELECT
a.s_id AS s_id,
a.s_name AS s_name,
a.s_birth AS s_birth,
a.s_sex AS s_sex,
c.c_name AS c_name,
d.t_name AS t_name,
b.s_score AS s_score
FROM (((Student a
JOIN Score b)
JOIN Course c)
JOIN Teacher d)
WHERE ((a.s_id = b.s_id)
AND (b.c_id = c.c_id)
AND (c.t_id = d.t_id));

DROP VIEW IF EXISTS v_student ;
CREATE VIEW v_student
AS
SELECT
a.s_id AS s_id,
a.s_name AS s_name,
a.s_birth AS s_birth,
a.s_sex AS s_sex,
c.c_name AS c_name,
d.t_name AS t_name,
b.s_score AS s_score
FROM (((Student a
JOIN Score b)
JOIN Course c)
JOIN Teacher d)
WHERE ((a.s_id = b.s_id)
AND (b.c_id = c.c_id)
AND (c.t_id = d.t_id));

   /*Data for the table `Course` */

INSERT INTO Course (c_id, c_name, t_id)
VALUES
('01', '语文', '02'),
('02', '数学', '01'),
('03', '英语', '03') ;
/*Data for the table Score /
INSERT INTO Score (s_id, c_id, s_score)
VALUES
(1, '01', 80),
(1, '02', 90),
(1, '03', 99),
(2, '01', 70),
(2, '02', 60),
(2, '03', 80),
(3, '01', 80),
(3, '02', 80),
(3, '03', 80),
(4, '01', 50),
(4, '02', 30),
(4, '03', 20),
(5, '01', 76),
(5, '02', 87),
(6, '01', 31),
(6, '03', 34),
(7, '02', 89),
(7, '03', 98) ;
/Data for the table Student /
INSERT INTO Student (
s_id,
s_name,
s_birth,
s_sex
)
VALUES
(1, '赵雷', '1990-01-01', '男'),
(2, '钱电', '1990-12-21', '男'),
(3, '孙风', '1990-05-20', '男'),
(4, '李云', '1990-08-06', '男'),
(5, '周梅', '1991-12-01', '女'),
(6, '吴兰', '1992-03-01', '女'),
(7, '郑竹', '1989-07-01', '女'),
(8, '王菊', '1990-01-20', '女') ;
/Data for the table Teacher */
INSERT INTO Teacher (t_id, t_name)
VALUES
('01', '张三'),
('02', '李四'),
('03', '王五') ;

image.png
image.png
image.png
image.png
image.png
image.png