• MySQL导入.sql文件方法以及导入失败的问题解决

    首先这是聂老师的sql文件

    1.  
    2. -- MySQL dump 10.13  Distrib 8.0.27, for Win64 (x86_64)
    3. --
    4. -- Host: localhost    Database: teaching
    5. -- ------------------------------------------------------
    6. -- Server version	8.0.27
    7.  
    8. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    9. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    10. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    11. /*!50503 SET NAMES utf8mb4 */;
    12. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    13. /*!40103 SET TIME_ZONE='+00:00' */;
    14. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    15. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    16. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    17. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    18.  
    19. --
    20. -- Table structure for table `advisor`
    21. --
    22.  
    23. DROP TABLE IF EXISTS `advisor`;
    24. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    25. /*!50503 SET character_set_client = utf8mb4 */;
    26. CREATE TABLE `advisor` (
    27.   `s_ID` varchar(5) NOT NULL,
    28.   `i_ID` varchar(5) DEFAULT NULL,
    29.   PRIMARY KEY (`s_ID`),
    30.   KEY `i_ID` (`i_ID`),
    31.   CONSTRAINT `advisor_ibfk_1` FOREIGN KEY (`i_ID`) REFERENCES `instructor` (`ID`) ON DELETE SET NULL,
    32.   CONSTRAINT `advisor_ibfk_2` FOREIGN KEY (`s_ID`) REFERENCES `student` (`ID`) ON DELETE CASCADE
    33. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    34. /*!40101 SET character_set_client = @saved_cs_client */;
    35.  
    36. --
    37. -- Dumping data for table `advisor`
    38. --
    39.  
    40. LOCK TABLES `advisor` WRITE;
    41. /*!40000 ALTER TABLE `advisor` DISABLE KEYS */;
    42. INSERT INTO `advisor` VALUES ('12345','10101'),('44553','22222'),('45678','22222'),('00128','45565'),('76543','45565'),('23121','76543'),('98988','76766'),('76653','98345'),('98765','98345');
    43. /*!40000 ALTER TABLE `advisor` ENABLE KEYS */;
    44. UNLOCK TABLES;
    45.  
    46. --
    47. -- Table structure for table `classroom`
    48. --
    49.  
    50. DROP TABLE IF EXISTS `classroom`;
    51. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    52. /*!50503 SET character_set_client = utf8mb4 */;
    53. CREATE TABLE `classroom` (
    54.   `building` varchar(15) NOT NULL,
    55.   `room_number` varchar(7) NOT NULL,
    56.   `capacity` decimal(4,0) DEFAULT NULL,
    57.   PRIMARY KEY (`building`,`room_number`)
    58. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    59. /*!40101 SET character_set_client = @saved_cs_client */;
    60.  
    61. --
    62. -- Dumping data for table `classroom`
    63. --
    64.  
    65. LOCK TABLES `classroom` WRITE;
    66. /*!40000 ALTER TABLE `classroom` DISABLE KEYS */;
    67. INSERT INTO `classroom` VALUES ('Packard','101',500),('Painter','514',10),('Taylor','3128',70),('Watson','100',30),('Watson','120',50);
    68. /*!40000 ALTER TABLE `classroom` ENABLE KEYS */;
    69. UNLOCK TABLES;
    70.  
    71. --
    72. -- Table structure for table `course`
    73. --
    74.  
    75. DROP TABLE IF EXISTS `course`;
    76. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    77. /*!50503 SET character_set_client = utf8mb4 */;
    78. CREATE TABLE `course` (
    79.   `course_id` varchar(8) NOT NULL,
    80.   `title` varchar(50) DEFAULT NULL,
    81.   `dept_name` varchar(20) DEFAULT NULL,
    82.   `credits` decimal(2,0) DEFAULT NULL,
    83.   PRIMARY KEY (`course_id`),
    84.   KEY `dept_name` (`dept_name`),
    85.   CONSTRAINT `course_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,
    86.   CONSTRAINT `course_chk_1` CHECK ((`credits` > 0))
    87. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    88. /*!40101 SET character_set_client = @saved_cs_client */;
    89.  
    90. --
    91. -- Dumping data for table `course`
    92. --
    93.  
    94. LOCK TABLES `course` WRITE;
    95. /*!40000 ALTER TABLE `course` DISABLE KEYS */;
    96. INSERT INTO `course` VALUES ('BIO-101','Intro. to Biology','Biology',4),('BIO-301','Genetics','Biology',4),('BIO-399','Computational Biology','Biology',3),('CS-101','Intro. to Computer Science','Comp. Sci.',4),('CS-190','Game Design','Comp. Sci.',4),('CS-315','Robotics','Comp. Sci.',3),('CS-319','Image Processing','Comp. Sci.',3),('CS-347','Database System Concepts','Comp. Sci.',3),('EE-181','Intro. to Digital Systems','Elec. Eng.',3),('FIN-201','Investment Banking','Finance',3),('HIS-351','World History','History',3),('MU-199','Music Video Production','Music',3),('PHY-101','Physical Principles','Physics',4);
    97. /*!40000 ALTER TABLE `course` ENABLE KEYS */;
    98. UNLOCK TABLES;
    99.  
    100. --
    101. -- Table structure for table `department`
    102. --
    103.  
    104. DROP TABLE IF EXISTS `department`;
    105. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    106. /*!50503 SET character_set_client = utf8mb4 */;
    107. CREATE TABLE `department` (
    108.   `dept_name` varchar(20) NOT NULL,
    109.   `building` varchar(15) DEFAULT NULL,
    110.   `budget` decimal(12,2) DEFAULT NULL,
    111.   PRIMARY KEY (`dept_name`),
    112.   CONSTRAINT `department_chk_1` CHECK ((`budget` > 0))
    113. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    114. /*!40101 SET character_set_client = @saved_cs_client */;
    115.  
    116. --
    117. -- Dumping data for table `department`
    118. --
    119.  
    120. LOCK TABLES `department` WRITE;
    121. /*!40000 ALTER TABLE `department` DISABLE KEYS */;
    122. INSERT INTO `department` VALUES ('Biology','Watson',90000.00),('Comp. Sci.','Taylor',100000.00),('Elec. Eng.','Taylor',85000.00),('Finance','Painter',120000.00),('History','Painter',50000.00),('Music','Packard',80000.00),('Physics','Watson',70000.00);
    123. /*!40000 ALTER TABLE `department` ENABLE KEYS */;
    124. UNLOCK TABLES;
    125.  
    126. --
    127. -- Table structure for table `instructor`
    128. --
    129.  
    130. DROP TABLE IF EXISTS `instructor`;
    131. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    132. /*!50503 SET character_set_client = utf8mb4 */;
    133. CREATE TABLE `instructor` (
    134.   `ID` varchar(5) NOT NULL,
    135.   `name` varchar(20) NOT NULL,
    136.   `dept_name` varchar(20) DEFAULT NULL,
    137.   `salary` decimal(8,2) DEFAULT NULL,
    138.   PRIMARY KEY (`ID`),
    139.   KEY `dept_name` (`dept_name`),
    140.   CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,
    141.   CONSTRAINT `instructor_chk_1` CHECK ((`salary` > 29000))
    142. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    143. /*!40101 SET character_set_client = @saved_cs_client */;
    144.  
    145. --
    146. -- Dumping data for table `instructor`
    147. --
    148.  
    149. LOCK TABLES `instructor` WRITE;
    150. /*!40000 ALTER TABLE `instructor` DISABLE KEYS */;
    151. INSERT INTO `instructor` VALUES ('10101','Srinivasan','Comp. Sci.',65000.00),('12121','Wu','Finance',90000.00),('15151','Mozart','Music',40000.00),('22222','Einstein','Physics',95000.00),('32343','El Said','History',60000.00),('33456','Gold','Physics',87000.00),('45565','Katz','Comp. Sci.',75000.00),('58583','Califieri','History',62000.00),('76543','Singh','Finance',80000.00),('76766','Crick','Biology',72000.00),('83821','Brandt','Comp. Sci.',92000.00),('98345','Kim','Elec. Eng.',80000.00);
    152. /*!40000 ALTER TABLE `instructor` ENABLE KEYS */;
    153. UNLOCK TABLES;
    154.  
    155. --
    156. -- Table structure for table `prereq`
    157. --
    158.  
    159. DROP TABLE IF EXISTS `prereq`;
    160. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    161. /*!50503 SET character_set_client = utf8mb4 */;
    162. CREATE TABLE `prereq` (
    163.   `course_id` varchar(8) NOT NULL,
    164.   `prereq_id` varchar(8) NOT NULL,
    165.   PRIMARY KEY (`course_id`,`prereq_id`),
    166.   KEY `prereq_id` (`prereq_id`),
    167.   CONSTRAINT `prereq_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE,
    168.   CONSTRAINT `prereq_ibfk_2` FOREIGN KEY (`prereq_id`) REFERENCES `course` (`course_id`)
    169. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    170. /*!40101 SET character_set_client = @saved_cs_client */;
    171.  
    172. --
    173. -- Dumping data for table `prereq`
    174. --
    175.  
    176. LOCK TABLES `prereq` WRITE;
    177. /*!40000 ALTER TABLE `prereq` DISABLE KEYS */;
    178. INSERT INTO `prereq` VALUES ('BIO-301','BIO-101'),('BIO-399','BIO-101'),('CS-190','CS-101'),('CS-315','CS-101'),('CS-319','CS-101'),('CS-347','CS-101'),('EE-181','PHY-101');
    179. /*!40000 ALTER TABLE `prereq` ENABLE KEYS */;
    180. UNLOCK TABLES;
    181.  
    182. --
    183. -- Table structure for table `section`
    184. --
    185.  
    186. DROP TABLE IF EXISTS `section`;
    187. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    188. /*!50503 SET character_set_client = utf8mb4 */;
    189. CREATE TABLE `section` (
    190.   `course_id` varchar(8) NOT NULL,
    191.   `sec_id` varchar(8) NOT NULL,
    192.   `semester` varchar(6) NOT NULL,
    193.   `year` decimal(4,0) NOT NULL,
    194.   `building` varchar(15) DEFAULT NULL,
    195.   `room_number` varchar(7) DEFAULT NULL,
    196.   `time_slot_id` varchar(4) DEFAULT NULL,
    197.   PRIMARY KEY (`course_id`,`sec_id`,`semester`,`year`),
    198.   KEY `building` (`building`,`room_number`),
    199.   CONSTRAINT `section_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE,
    200.   CONSTRAINT `section_ibfk_2` FOREIGN KEY (`building`, `room_number`) REFERENCES `classroom` (`building`, `room_number`) ON DELETE SET NULL,
    201.   CONSTRAINT `section_chk_1` CHECK ((`semester` in (_gbk'Fall',_gbk'Winter',_gbk'Spring',_gbk'Summer'))),
    202.   CONSTRAINT `section_chk_2` CHECK (((`year` > 1701) and (`year` < 2100)))
    203. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    204. /*!40101 SET character_set_client = @saved_cs_client */;
    205.  
    206. --
    207. -- Dumping data for table `section`
    208. --
    209.  
    210. LOCK TABLES `section` WRITE;
    211. /*!40000 ALTER TABLE `section` DISABLE KEYS */;
    212. INSERT INTO `section` VALUES ('BIO-101','1','Summer',2017,'Painter','514','B'),('BIO-301','1','Summer',2018,'Painter','514','A'),('CS-101','1','Fall',2017,'Packard','101','H'),('CS-101','1','Spring',2018,'Packard','101','F'),('CS-190','1','Spring',2017,'Taylor','3128','E'),('CS-190','2','Spring',2017,'Taylor','3128','A'),('CS-315','1','Spring',2018,'Watson','120','D'),('CS-319','1','Spring',2018,'Watson','100','B'),('CS-319','2','Spring',2018,'Taylor','3128','C'),('CS-347','1','Fall',2017,'Taylor','3128','A'),('EE-181','1','Spring',2017,'Taylor','3128','C'),('FIN-201','1','Spring',2018,'Packard','101','B'),('HIS-351','1','Spring',2018,'Painter','514','C'),('MU-199','1','Spring',2018,'Packard','101','D'),('PHY-101','1','Fall',2017,'Watson','100','A');
    213. /*!40000 ALTER TABLE `section` ENABLE KEYS */;
    214. UNLOCK TABLES;
    215.  
    216. --
    217. -- Table structure for table `student`
    218. --
    219.  
    220. DROP TABLE IF EXISTS `student`;
    221. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    222. /*!50503 SET character_set_client = utf8mb4 */;
    223. CREATE TABLE `student` (
    224.   `ID` varchar(5) NOT NULL,
    225.   `name` varchar(20) NOT NULL,
    226.   `dept_name` varchar(20) DEFAULT NULL,
    227.   `tot_cred` decimal(3,0) DEFAULT NULL,
    228.   PRIMARY KEY (`ID`),
    229.   KEY `dept_name` (`dept_name`),
    230.   CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,
    231.   CONSTRAINT `student_chk_1` CHECK ((`tot_cred` >= 0))
    232. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    233. /*!40101 SET character_set_client = @saved_cs_client */;
    234.  
    235. --
    236. -- Dumping data for table `student`
    237. --
    238.  
    239. LOCK TABLES `student` WRITE;
    240. /*!40000 ALTER TABLE `student` DISABLE KEYS */;
    241. INSERT INTO `student` VALUES ('00128','Zhang','Comp. Sci.',102),('12345','Shankar','Comp. Sci.',32),('19991','Brandt','History',80),('23121','Chavez','Finance',110),('44553','Peltier','Physics',56),('45678','Levy','Physics',46),('54321','Williams','Comp. Sci.',54),('55739','Sanchez','Music',38),('70557','Snow','Physics',0),('76543','Brown','Comp. Sci.',58),('76653','Aoi','Elec. Eng.',60),('98765','Bourikas','Elec. Eng.',98),('98988','Tanaka','Biology',120);
    242. /*!40000 ALTER TABLE `student` ENABLE KEYS */;
    243. UNLOCK TABLES;
    244.  
    245. --
    246. -- Table structure for table `takes`
    247. --
    248.  
    249. DROP TABLE IF EXISTS `takes`;
    250. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    251. /*!50503 SET character_set_client = utf8mb4 */;
    252. CREATE TABLE `takes` (
    253.   `ID` varchar(5) NOT NULL,
    254.   `course_id` varchar(8) NOT NULL,
    255.   `sec_id` varchar(8) NOT NULL,
    256.   `semester` varchar(6) NOT NULL,
    257.   `year` decimal(4,0) NOT NULL,
    258.   `grade` varchar(2) DEFAULT NULL,
    259.   PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),
    260.   KEY `course_id` (`course_id`,`sec_id`,`semester`,`year`),
    261.   CONSTRAINT `takes_ibfk_1` FOREIGN KEY (`course_id`, `sec_id`, `semester`, `year`) REFERENCES `section` (`course_id`, `sec_id`, `semester`, `year`) ON DELETE CASCADE,
    262.   CONSTRAINT `takes_ibfk_2` FOREIGN KEY (`ID`) REFERENCES `student` (`ID`) ON DELETE CASCADE
    263. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    264. /*!40101 SET character_set_client = @saved_cs_client */;
    265.  
    266. --
    267. -- Dumping data for table `takes`
    268. --
    269.  
    270. LOCK TABLES `takes` WRITE;
    271. /*!40000 ALTER TABLE `takes` DISABLE KEYS */;
    272. INSERT INTO `takes` VALUES ('00128','CS-101','1','Fall',2017,'A'),('00128','CS-347','1','Fall',2017,'A-'),('12345','CS-101','1','Fall',2017,'C'),('12345','CS-190','2','Spring',2017,'A'),('12345','CS-315','1','Spring',2018,'A'),('12345','CS-347','1','Fall',2017,'A'),('19991','HIS-351','1','Spring',2018,'B'),('23121','FIN-201','1','Spring',2018,'C+'),('44553','PHY-101','1','Fall',2017,'B-'),('45678','CS-101','1','Fall',2017,'F'),('45678','CS-101','1','Spring',2018,'B+'),('45678','CS-319','1','Spring',2018,'B'),('54321','CS-101','1','Fall',2017,'A-'),('54321','CS-190','2','Spring',2017,'B+'),('55739','MU-199','1','Spring',2018,'A-'),('76543','CS-101','1','Fall',2017,'A'),('76543','CS-319','2','Spring',2018,'A'),('76653','EE-181','1','Spring',2017,'C'),('98765','CS-101','1','Fall',2017,'C-'),('98765','CS-315','1','Spring',2018,'B'),('98988','BIO-101','1','Summer',2017,'A'),('98988','BIO-301','1','Summer',2018,NULL);
    273. /*!40000 ALTER TABLE `takes` ENABLE KEYS */;
    274. UNLOCK TABLES;
    275.  
    276. --
    277. -- Table structure for table `teaches`
    278. --
    279.  
    280. DROP TABLE IF EXISTS `teaches`;
    281. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    282. /*!50503 SET character_set_client = utf8mb4 */;
    283. CREATE TABLE `teaches` (
    284.   `ID` varchar(5) NOT NULL,
    285.   `course_id` varchar(8) NOT NULL,
    286.   `sec_id` varchar(8) NOT NULL,
    287.   `semester` varchar(6) NOT NULL,
    288.   `year` decimal(4,0) NOT NULL,
    289.   PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),
    290.   KEY `course_id` (`course_id`,`sec_id`,`semester`,`year`),
    291.   CONSTRAINT `teaches_ibfk_1` FOREIGN KEY (`course_id`, `sec_id`, `semester`, `year`) REFERENCES `section` (`course_id`, `sec_id`, `semester`, `year`) ON DELETE CASCADE,
    292.   CONSTRAINT `teaches_ibfk_2` FOREIGN KEY (`ID`) REFERENCES `instructor` (`ID`) ON DELETE CASCADE
    293. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    294. /*!40101 SET character_set_client = @saved_cs_client */;
    295.  
    296. --
    297. -- Dumping data for table `teaches`
    298. --
    299.  
    300. LOCK TABLES `teaches` WRITE;
    301. /*!40000 ALTER TABLE `teaches` DISABLE KEYS */;
    302. INSERT INTO `teaches` VALUES ('76766','BIO-101','1','Summer',2017),('76766','BIO-301','1','Summer',2018),('10101','CS-101','1','Fall',2017),('45565','CS-101','1','Spring',2018),('83821','CS-190','1','Spring',2017),('83821','CS-190','2','Spring',2017),('10101','CS-315','1','Spring',2018),('45565','CS-319','1','Spring',2018),('83821','CS-319','2','Spring',2018),('10101','CS-347','1','Fall',2017),('98345','EE-181','1','Spring',2017),('12121','FIN-201','1','Spring',2018),('32343','HIS-351','1','Spring',2018),('15151','MU-199','1','Spring',2018),('22222','PHY-101','1','Fall',2017);
    303. /*!40000 ALTER TABLE `teaches` ENABLE KEYS */;
    304. UNLOCK TABLES;
    305.  
    306. --
    307. -- Table structure for table `time_slot`
    308. --
    309.  
    310. DROP TABLE IF EXISTS `time_slot`;
    311. /*!40101 SET @saved_cs_client     = @@character_set_client */;
    312. /*!50503 SET character_set_client = utf8mb4 */;
    313. CREATE TABLE `time_slot` (
    314.   `time_slot_id` varchar(4) NOT NULL,
    315.   `day` varchar(1) NOT NULL,
    316.   `start_hr` decimal(2,0) NOT NULL,
    317.   `start_min` decimal(2,0) NOT NULL,
    318.   `end_hr` decimal(2,0) DEFAULT NULL,
    319.   `end_min` decimal(2,0) DEFAULT NULL,
    320.   PRIMARY KEY (`time_slot_id`,`day`,`start_hr`,`start_min`),
    321.   CONSTRAINT `time_slot_chk_1` CHECK (((`start_hr` >= 0) and (`start_hr` < 24))),
    322.   CONSTRAINT `time_slot_chk_2` CHECK (((`start_min` >= 0) and (`start_min` < 60))),
    323.   CONSTRAINT `time_slot_chk_3` CHECK (((`end_hr` >= 0) and (`end_hr` < 24))),
    324.   CONSTRAINT `time_slot_chk_4` CHECK (((`end_min` >= 0) and (`end_min` < 60)))
    325. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    326. /*!40101 SET character_set_client = @saved_cs_client */;
    327.  
    328. --
    329. -- Dumping data for table `time_slot`
    330. --
    331.  
    332. LOCK TABLES `time_slot` WRITE;
    333. /*!40000 ALTER TABLE `time_slot` DISABLE KEYS */;
    334. INSERT INTO `time_slot` VALUES ('A','F',8,0,8,50),('A','M',8,0,8,50),('A','W',8,0,8,50),('B','F',9,0,9,50),('B','M',9,0,9,50),('B','W',9,0,9,50),('C','F',11,0,11,50),('C','M',11,0,11,50),('C','W',11,0,11,50),('D','F',13,0,13,50),('D','M',13,0,13,50),('D','W',13,0,13,50),('E','R',10,30,11,45),('E','T',10,30,11,45),('F','R',14,30,15,45),('F','T',14,30,15,45),('G','F',16,0,16,50),('G','M',16,0,16,50),('G','W',16,0,16,50),('H','W',10,0,12,30);
    335. /*!40000 ALTER TABLE `time_slot` ENABLE KEYS */;
    336. UNLOCK TABLES;
    337. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    338.  
    339. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    340. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    341. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    342. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    343. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    344. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    345. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    346.  
    347. -- Dump completed on 2022-08-29 14:09:25

    然后我们现在有很多种导入方法

    导入sql文件的几种方法

    1.使用工具Navicat for MySQL导入

    该工具下载路径:Navicat

    1.1打开localhost_3306,选中右击“新建数据库”

    1.2指定数据库名和字符集（可根据sql文件的字符集类型自行选择）

    1.3选中数据库下的表运行SQL文件

    1.4选中路径导入

    文件路径可以和上图不一样,自己设定就好了

    2.使用官方工具MySQL Workbench导入

    2.1NO.1

    1. 新建一个数据库,名字任取,这里建议取和你sql文件相同的名称,然后点击指示图标(或者File栏里面的Open SQL Script...)

    1. 选中路径后导入SQL文件
    2. 添加指定库名的命令.并点击运行

    PS：不用点保存，导入后直接×掉即可，保存会修改sql文件本身；

    在左侧栏点击刷新查看结果

    2.2NO.2

    1. 点击导入(或者Server栏里面的Data Import)
    2. 选择导入文件的路径
    3. Start Import
    4. 刷新查看结果

    3.使用命令行导入

    1. 点击开始菜单输入cmd(或者按住win+R)回车,打开dos界面

    1. cd进入到MySQL安装目录的bin文件

    1. 输入"mysql -u root -p",然后输入数据库密码
    2. create database teaching; 新建一个数据库
    3. 选中数据库 use teaching;
    4. 选中导入的路径"source D:/teaching.sql;"
    5. 查看表show tables;

    导入sql文件时遇见的一些问题

    1.MySQL中创建外键的错误：1215 Cannot add the foreign key constraint

    引言： MySQL中经常会需要创建父子表之间的约束，这个约束是需要建立在主外键基础之上的，这里解决了一个在创建主外键约束过程中碰到的一个问题。

    1. 问题的提出

    创建两个表：

    product: 商品表

    sealer: 供货商表

    相应的ＳＱＬ如下：

    　
    1. 　　product:
    2. DROP TABLE IF EXISTS `product`;
    3. CREATE TABLE `product` (
    4.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    5.   `name` varchar(20) NOT NULL COMMENT 'product name',
    6.   `price` float(10,3) NOT NULL,
    7.   `description` varchar(20) DEFAULT NULL,
    8.   `count` int(11) NOT NULL DEFAULT '0',
    9.   `sid` int(11) NOT NULL,
    10.   PRIMARY KEY (`id`),
    11.   UNIQUE KEY `id_index` (`id`) USING HASH,
    12.   UNIQUE KEY `sid_index` (`sid`) USING HASH
    13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    14.    sealer:
    15. DROP TABLE IF EXISTS `sealer`;
    16. CREATE TABLE `sealer` (
    17.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    18.   `name` varchar(30) NOT NULL,
    19.   `city` varchar(255) DEFAULT NULL,
    20.   `created_time` datetime DEFAULT NULL,
    21.   `updated_time` datetime DEFAULT NULL,
    22.   `level` int(11) NOT NULL DEFAULT '0',
    23.   `description` varchar(40) DEFAULT NULL,
    24.   PRIMARY KEY (`id`),
    25.   UNIQUE KEY `id_index_1` (`id`) USING HASH
    26. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

    接下来我们需要关联product.sid 至 sealer.id，进行父子表的主外键关联。

    2. 碰到错误

    在创建外键之时，使用的SQL和碰到错误信息如下:

    alter table `product' add CONSTRAINT `sid_ref` FOREIGN KEY (`sid`) REFERENCES `sealer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

    碰到的错误信息如下：

    无法正确的插入外键约束。

    3. 问题分析

    主外键更多的是某表的主键与子表的某个列进行关联，要求是具备相同的数据类型和属性，问题会不会出现在这里？

    要求： 具备相同的数据类型和约束

    发现： unsigned，数字的字符长度不一致。

    4. 解决的办法

    修改product.sid中的数据类型，添加unsigned和字段的长度，将其设置为相同即可

    5. 总结

    之所以出现1215的问题，是由于主外键之间的数据类型不一致造成的，以后类似问题，皆可按此处理。

    2.聂老师sql文件版本问题

    大家在导入的时候会发现,出现了很多莫名其妙的报错,然后导致很多表没有成功导进去,我们在每一个表的sql语句上找共同点,会发现,都会有字符集的解释作为结尾,但是这里的字符集,大部分人的5.6版本是不支持的,所以要么卸载所有的现MySQL版本,要么就修改聂老师的teaching.sql文件,这两个方法都非常复杂.

    第一种还好,虽然卸载程序比较繁琐,但是只要跟着网上的教程一步一步来,最后把注册表删删干净就没多大问题.

    第二种是真的让我破防了.

    首先我在MySQL Workbench里面打开了teaching.sql文件,试着运行过去,好不容易发现并修改了字符集的错误,然后又莫名其妙地出现了无法创建外键连接的错误.

    我寻找了网上的教程,如上所说,导致外键创建失败的原因主要有以下两点:

    1. 外键字段不能为该表的主键
    2. 外键字段参考字段必须为参考表的主键.

    如果出现”cannot add foreign key constraint”的情况，建议先检查一下，主表是否有主键以及从表关联字段是否为从表主键，然后，核对从表字段与主表关联字段的数据类型是否相同，其中是否有不符合的数据(不好弄就先把数据备份，然后删完了试一下，如果可以，说明确实是存在不符合的数据问题)。

    为此我还去问了老师.他的回答也是如此.

    但是我都试过了,还是会报那个错误,然后我突发奇想,换一个平台,也许是控制环境的问题.于是我换成了IDEA的平台(不得不说,IDEA用着是真舒服,不愧是一天几刀的开发IDE)

    如图,我把字符集的问题修改了一下之后,直接就成功了,目前来看,原因应该是IDEA拥有自动版本适配的功能,然而MySQL workbench的官方图形化操作平台开发得相对比较呆板,所以5.7版本的MySQL不能适配8版本的sql语法等.(不清楚是不是这个原因,如有误请大佬评论区指正原因)

    我们将teaching所有的表中的数据可视化.OK,非常完整,成功!

  • 相关阅读:
    input输入事件
    MySQL中的Statistics等待
    【docker专栏3】docker基础概念-容器、镜像以及引擎组成部分
    【操作系统】安全管理/防火墙
    今天起，Windows可以一键召唤GPT-4了
    TiKV 源码分析之 PointGet
    指针笔试题解析（1）
    return语句
    java计算机毕业设计网上主题超市系统源代码+数据库+系统+lw文档
    java毕业设计的大学宿舍管理系统mybatis+源码+调试部署+系统+数据库+lw
  • 原文地址：https://blog.csdn.net/qq_63511424/article/details/127379345