mysql12.15老师上课代码


stu1.sql
5.8 KB
#数据冗余 #多表查询 数据减少冗余 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 */;
stu.txt
5.8 KB
stu.sql
5.0 KB
符攀飞 符攀飞
9 个月前
42

扫描下方二维码手机阅读文章

0