-- MySQL dump 10.13  Distrib 8.0.25, for Win64 (x86_64)
--
-- Host: localhost    Database: work
-- ------------------------------------------------------
-- Server version	8.0.25

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;

--
-- Table structure for table `course`
--

DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(45) NOT NULL,
  `credit` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `course`
--

LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'通信原理',3.00),(2,'计算机网络',2.00),(3,'移动通信',3.50),(4,'大学物理',3.00),(5,'微波技术',4.00),(6,'多媒体通信',3.50),(7,'复变函数',2.50),(8,'高等数学1',4.00),(9,'电路',3.00),(10,'数字电路',3.50),(11,'高等数学2',4.00),(12,'数字信号',3.00),(13,'高频电子',3.50),(14,'大学英语',2.00),(15,'单片机原理',3.50),(16,'概率论',2.50),(17,'信息论与编码',3.00),(18,'数字通信',3.00),(19,'大学体育',2.00),(20,'中国近代史',2.00),(21,'马克思原理',2.00),(22,'软件无线电',3.00),(23,'电磁场与电磁波',3.50),(24,'线性代数',3.00),(25,'专业英语',2.00),(26,'信号与系统',3.50),(27,'计算机基础',1.50),(28,'c程序设计',2.00),(29,'操作系统导论',2.50),(30,'现代交换技术',2.50),(31,'新世界交互英语',2.00);
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `option_course`
--

DROP TABLE IF EXISTS `option_course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `option_course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `student_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `option_fk_course_idx` (`course_id`),
  KEY `option_fk_student_idx` (`student_id`),
  CONSTRAINT `option_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
  CONSTRAINT `option_fk_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `option_course`
--

LOCK TABLES `option_course` WRITE;
/*!40000 ALTER TABLE `option_course` DISABLE KEYS */;
INSERT INTO `option_course` VALUES (1,20,1),(2,2,1),(3,4,1),(4,5,1),(5,6,1),(6,10,1),(7,15,1),(8,18,1),(9,20,2),(10,4,2),(11,5,2),(12,7,2),(13,19,2),(14,30,2),(15,18,2),(16,17,2),(17,15,2),(18,14,2),(19,1,2),(20,6,2),(21,10,2),(22,1,3),(23,3,3),(24,5,3),(25,7,3),(26,9,3),(27,10,3),(28,15,3),(29,17,3),(30,19,3),(31,20,3),(32,2,3),(33,20,4),(34,2,4),(35,4,4),(36,6,4),(37,7,4),(38,9,4),(39,13,4),(40,15,4),(41,14,4),(42,17,4),(43,28,4),(44,25,4),(45,1,5),(46,8,5),(47,9,5),(48,14,5),(49,16,5),(50,19,5),(51,15,5),(52,20,5),(53,27,5),(54,26,5),(55,24,5),(56,28,5),(68,2,7),(69,3,7),(70,8,7),(71,11,7),(72,25,7),(73,14,7),(74,9,7),(75,13,7),(76,20,7),(77,1,8),(78,4,8),(79,10,8),(80,15,8),(81,13,8),(82,20,8),(83,23,8),(84,25,8),(85,27,8),(86,30,8),(87,2,9),(88,9,9),(89,13,9),(90,15,9),(91,4,9),(92,14,9),(93,17,9),(94,30,9),(95,31,9),(96,24,9),(97,20,9),(98,1,10),(99,3,10),(100,7,10),(101,11,10),(102,14,10),(103,19,10),(104,20,10),(105,21,10),(106,25,10),(109,22,10),(112,2,10),(113,4,10),(114,4,10);
/*!40000 ALTER TABLE `option_course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `password`
--

DROP TABLE IF EXISTS `password`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `password` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cipher_code` varchar(45) NOT NULL,
  `student_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `password_fk_student_idx` (`student_id`),
  CONSTRAINT `password_fk_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `password`
--

LOCK TABLES `password` WRITE;
/*!40000 ALTER TABLE `password` DISABLE KEYS */;
INSERT INTO `password` VALUES (1,'20182815',1),(2,'bob',2),(3,'king',4),(4,'20182877',10),(5,'zoe',9),(6,'20182820',3),(7,'20182873',5),(8,'mag',8),(10,'tom',7);
/*!40000 ALTER TABLE `password` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `room`
--

DROP TABLE IF EXISTS `room`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `room` (
  `id` int NOT NULL AUTO_INCREMENT,
  `localtion` varchar(45) NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `room_fk_course_idx` (`course_id`),
  CONSTRAINT `room_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `room`
--

LOCK TABLES `room` WRITE;
/*!40000 ALTER TABLE `room` DISABLE KEYS */;
INSERT INTO `room` VALUES (1,'树人楼-北-301',2),(2,'树人楼-北-305',1),(3,'树人楼-北-201',3),(4,'树人楼-南-208',4),(5,'树木楼-205',6),(6,'树人楼-北-208',5),(7,'树人楼-北-401',7),(8,'树人楼-北-405',8),(9,'树人楼-北-501',9),(10,'树人楼-北-502',10),(11,'树人楼-北-101',11),(12,'树人楼-北-201',12),(13,'树人楼-南-204',13),(14,'树人楼-南-210',14),(15,'经世楼-301',15),(16,'树木楼-301',16),(17,'树木楼-302',17),(18,'树木楼-310',18),(19,'树木楼-202',19),(20,'树木楼-101',20),(21,'经世楼-101',21),(22,'经世楼-201',22),(23,'经世楼-401',23),(24,'树人楼-南-205',24),(25,'树人楼-南-208',25),(26,'树人楼-南-502',26),(27,'树人楼-南-504',27),(28,'树人楼-北-206',28),(29,'诚意楼-102',29),(30,'诚意楼-205',30),(31,'诚意楼-301',31);
/*!40000 ALTER TABLE `room` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `ID_num` char(8) NOT NULL,
  `sex` char(1) NOT NULL DEFAULT 'm',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ID_num_UNIQUE` (`ID_num`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'alice','20182815','w'),(2,'bob','20182817','m'),(3,'rose','20182820','w'),(4,'king','20182858','m'),(5,'jack','20182873','m'),(7,'tom','20172421','m'),(8,'mag','20172524','w'),(9,'zoe','20202421','w'),(10,'sally','20182877','w');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_course`
--

DROP TABLE IF EXISTS `t_course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t_course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int DEFAULT NULL,
  `teacher_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `course_id_UNIQUE` (`course_id`),
  KEY `t_course_fk_teacher_idx` (`teacher_id`),
  CONSTRAINT `t_course_fk_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
  CONSTRAINT `t_course_fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_course`
--

LOCK TABLES `t_course` WRITE;
/*!40000 ALTER TABLE `t_course` DISABLE KEYS */;
INSERT INTO `t_course` VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,6,5),(6,9,6),(7,17,7),(8,18,7),(9,5,8),(10,13,8),(11,26,9),(12,22,10);
/*!40000 ALTER TABLE `t_course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teacher`
--

DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `ID_num` char(8) DEFAULT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ID_num_UNIQUE` (`ID_num`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teacher`
--

LOCK TABLES `teacher` WRITE;
/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;
INSERT INTO `teacher` VALUES (1,'张凌涛','20000001','zhang'),(2,'刘拥民','20000004','liuyongmin'),(3,'郑志安','20000005','zza'),(4,'盛忠志','20000002','sheng'),(5,'秦姣华','20000003','qingjiaohua'),(6,'李琳','20000007','lilin'),(7,'朱江章','20000009','zhujiangzhang'),(8,'徐卓农','20000010','xuzhuonong'),(9,'何明芳','20000011','hemingfang'),(10,'黄洪旭','20000014','huanghongxu');
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

ALTER TABLE option_course ADD UNIQUE INDEX `te` (`course_id`, `student_id`);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-06-23 16:18:06