#数据冗余
#多表查询 数据减少冗余
CREATE VIEW v_1(a1,a2,b1,b2,b3,b4,c1,c2,c3,c4,c5,c6,c7,c8,
c9,c10,d1,d2,d3,d4,d5 )
AS SELECT
a.*,b.*,c.*,d.*
FROM grade a,result b,student c,SUBJECT d
WHERE a.gradeid=d.gradeid
AND b.studentno=c.studentno
and d.subjectno=b.subjectno;
select * from v_1;
#查询课程为《高等数学-2》
#且分数不小于80分的学生的学号和姓名
select b1,c3 from v_1 where
d2='高等数学-2' and b4>=80;
create view view_2 as
select * from student where sex='女';
select * from view_2;
insert into view_2(studentno,phone,address,email)
values('55','15454545','85','88787');
图片来源：王优秀。
/*
SQLyog Professional v12.08 (32 bit)
MySQL - 5.5.50 : Database - tt
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`tt` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `tt`;
/*Table structure for table `t_course` */
DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
`course_id` char(8) NOT NULL,
`course_name` varchar(50) NOT NULL,
`course_type` varchar(10) DEFAULT NULL,
`course_credit` int(11) DEFAULT NULL,
`teacher_id` char(10) DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_course` */
insert into `t_course`(`course_id`,`course_name`,`course_type`,`course_credit`,`teacher_id`) values ('16610001','C语言','必修课',2,'1995081001'),('16610002','数据库技术','必修课',3,'1996081002'),('16610003','数据结构与算法','必修课',3,'2002081007'),('16610004','JAVA程序设计','必修课',3,'1995081001'),('16610005','公共体育','选修课',2,'2005081011'),('16610006','软件工程','必修课',3,'2003081009'),('16610007','大学英语','选修课',2,'2003081008'),('16610008','应用文写作','选修课',1,'2001081006'),('16610009','移动互联应用开发','必修课',3,'2003081009');
/*Table structure for table `t_major` */
DROP TABLE IF EXISTS `t_major`;
CREATE TABLE `t_major` (
`major_id` char(5) NOT NULL,
`major_name` varchar(50) NOT NULL,
PRIMARY KEY (`major_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_major` */
insert into `t_major`(`major_id`,`major_name`) values ('31601','计算机应用技术'),('31607','云计算技术'),('31611','智能控制技术'),('31613','软件技术'),('31616','移动通信技术'),('31621','网络技术');
/*Table structure for table `t_score` */
DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score` (
`score_id` char(4) NOT NULL,
`stu_id` char(10) DEFAULT NULL,
`course_id` char(8) DEFAULT NULL,
`grade` int(11) DEFAULT NULL,
PRIMARY KEY (`score_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_score` */
insert into `t_score`(`score_id`,`stu_id`,`course_id`,`grade`) values ('1001','1631607101','16610001',82),('1002','1631607101','16610002',65),('1003','1631607101','16610003',78),('1004','1631607102','16610004',90),('1005','1631607102','16610005',89),('1006','1631611104','16610003',66),('1007','1631611104','16610004',97),('1008','1631611104','16610005',87),('1009','1631611104','16610006',80),('1010','1731613106','16610009',65),('1011','1731613107','16610009',87),('1012','1631601101','16610002',76),('1013','1631601102','16610003',75),('1014','1631601103','16610004',80),('1015','1631601104','16610005',70),('1016','1631601105','16610006',69),('1017','1631601105','16610007',88);
/*Table structure for table `t_student` */
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`stu_id` char(10) NOT NULL,
`stu_name` varchar(50) NOT NULL,
`stu_sex` char(2) DEFAULT NULL,
`stu_birthday` date DEFAULT NULL,
`stu_telephone` varchar(20) DEFAULT NULL,
`major_id` char(5) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_student` */
insert into `t_student`(`stu_id`,`stu_name`,`stu_sex`,`stu_birthday`,`stu_telephone`,`major_id`) values ('1631601101','宫雪花','女','1998-08-18','17709365789','31601'),('1631601102','王伟','男','1999-12-10','13256779833','31601'),('1631601103','张峰','男','1998-05-18','17797363527','31601'),('1631601104','李明','男','1998-05-30','13976455283','31601'),('1631601105','王志鹏','男','1999-06-20','19963544644','31601'),('1631607101','谭学飞','男','1999-12-12','13912341234','31607'),('1631607102','余波','男','1998-06-10','13823457765','31607'),('1631607103','王洪','男','1998-04-24','17708183344','31607'),('1631611101','吴江','男','1999-02-10','19923450912','31611'),('1631611102','王明霞','女','1999-03-13','15823458901','31611'),('1631611103','胡秋香','女','1999-04-20','17723409871','31611'),('1631611104','汪诗寒','女','1999-09-28','18909234786','31611'),('1631621101','江云道','男','1999-07-31','19973635534','31621'),('1631621102','胡晓华','女','1998-03-10','13864552648','31621'),('1631621103','陈家润','男','1998-04-12','13758575383','31621'),('1731613106','江诗敏','女','1999-06-11','13709278939','31613'),('1731613107','韩霜','女','1998-05-06','15709276589','31613');
/*Table structure for table `t_teacher` */
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
`teacher_id` int(11) NOT NULL DEFAULT '0',
`teacher_name` varchar(50) NOT NULL,
`teacher_title` varchar(20) DEFAULT NULL,
`teacher_sex` char(2) DEFAULT NULL,
PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_teacher` */
insert into `t_teacher`(`teacher_id`,`teacher_name`,`teacher_title`,`teacher_sex`) values (1995081001,'李强','教授','男'),(1996081002,'王东志','教授','男'),(1999081003,'秦明露','副教授','女'),(1999081004,'万豪','副教授','男'),(2000081005,'程志鹏','讲师','男'),(2001081006,'王海霞','讲师','女'),(2002081007,'喻唯','副教授','女'),(2003081008,'张敏','副教授','女'),(2003081009,'李冉冉','讲师','女'),(2004081010,'海飞','讲师','男'),(2005081011,'汪诗诗','讲师','女');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
符攀飞