Mysql学习笔记-创建实验表

参考书籍：Mysql核心技术与最佳实践 /孔祥盛编著. -北京：人民邮电出版社,2014.5 ISBN 978-7-115-33769-6

use choose;
create table teacher(
teacher_no char(10) primary key,
teacher_name char(10) not null, #教师姓名不允许为空
teacher_contact char(20) not null
)engine=InnoDB default charset=utf8;

create table classes(
class_no int auto_increment primary key,
class_name char(20) not null unique, #不允许为空，且不重复
department_name char(20) not null
)engine=InnoDB default charset=utf8;

create table course(
course_no int auto_increment primary key,
course_name char(10) not null,
up_limit int default 60, #上限默认值60
description text not null,
status char(6) default '未审核',
teacher_no char(10) not null unique, #唯一性约束实现课程与课程1:1关系
constraint course_teacher_fk foreign key (teacher_no) references teacher(teacher_no) #外键约束关系，键 (teacher_no) 参考 teacher表中(teacher_no)
)engine=InnoDB default charset=utf8;

create table student(
student_no char(11) primary key,
student_name char(10) not null,
student_contact char(20) not null,
class_no int,
constraint student_class_fk foreign key (class_no) references classes(class_no)
)engine=InnoDB default charset=utf8;

create table choose(
choose_no int auto_increment primary key,
student_no char(11) not null,
course_no int not null,
score tinyint unsigned,
choose_time datetime not null, #由now()函数自动生成
constraint choose_student_fk foreign key(student_no) references student(student_no),
constraint choose_course_fk foreign key(course_no) references course(course_no)
)engine=InnoDB default charset=utf8;

SQL文件：

-- MySQL dump 10.13  Distrib 5.5.42, for Linux (i686)
--
-- Host: localhost    Database: choose
-- ------------------------------------------------------
-- Server version	5.5.42-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 `choose`
--

DROP TABLE IF EXISTS `choose`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `choose` (
  `choose_no` int(11) NOT NULL AUTO_INCREMENT,
  `student_no` char(11) NOT NULL,
  `course_no` int(11) NOT NULL,
  `score` tinyint(3) unsigned DEFAULT NULL,
  `choose_time` datetime NOT NULL,
  PRIMARY KEY (`choose_no`),
  KEY `choose_student_fk` (`student_no`),
  KEY `choose_course_fk` (`course_no`),
  CONSTRAINT `choose_course_fk` FOREIGN KEY (`course_no`) REFERENCES `course` (`course_no`),
  CONSTRAINT `choose_student_fk` FOREIGN KEY (`student_no`) REFERENCES `student` (`student_no`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `choose`
--

LOCK TABLES `choose` WRITE;
/*!40000 ALTER TABLE `choose` DISABLE KEYS */;
INSERT INTO `choose` VALUES (1,'2012001',2,40,'2016-01-24 19:18:13'),(2,'2012001',1,50,'2016-01-24 19:18:38'),(3,'2012002',3,60,'2016-01-24 19:18:49'),(4,'2012002',2,70,'2016-01-24 19:19:04'),(5,'2012003',1,80,'2016-01-24 19:19:23'),(6,'2012004',2,90,'2016-01-24 19:19:34'),(7,'2012005',2,NULL,'2016-01-24 19:19:52'),(8,'2012005',1,NULL,'2016-01-24 19:19:58');
/*!40000 ALTER TABLE `choose` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `classes`
--

DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
  `class_no` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` char(20) NOT NULL,
  `department_name` char(20) NOT NULL,
  PRIMARY KEY (`class_no`),
  UNIQUE KEY `class_name` (`class_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `classes`
--

LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (1,'2012 自动化1班','信息学院'),(2,'2012 自动化2班','信息学院'),(3,'2012 自动化3班','信息学院');
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
  `course_no` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` char(10) NOT NULL,
  `up_limit` int(11) DEFAULT '60',
  `description` text NOT NULL,
  `status` char(6) DEFAULT '未审核',
  `teacher_no` char(10) NOT NULL,
  PRIMARY KEY (`course_no`),
  UNIQUE KEY `teacher_no` (`teacher_no`),
  CONSTRAINT `course_teacher_fk` FOREIGN KEY (`teacher_no`) REFERENCES `teacher` (`teacher_no`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!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,'JAVA',60,'暂无','已审核','001'),(2,'Mysql',60,'暂无','已审核','002'),(3,'C',60,'暂无','已审核','003');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `new_student`
--

DROP TABLE IF EXISTS `new_student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `new_student` (
  `student_no` char(11) NOT NULL,
  `student_name` char(10) NOT NULL,
  `student_contact` char(20) NOT NULL,
  `class_no` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_no`),
  KEY `student_class_fk` (`class_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `new_student`
--

LOCK TABLES `new_student` WRITE;
/*!40000 ALTER TABLE `new_student` DISABLE KEYS */;
INSERT INTO `new_student` VALUES ('2012001','张三','15000000000',1),('2012002','李四','16000000000',1),('2012003','王五','17000000000',3),('2012004','马六','18000000000',2),('2012005','田七','19000000000',2);
/*!40000 ALTER TABLE `new_student` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `student_no` char(11) NOT NULL,
  `student_name` char(10) NOT NULL,
  `student_contact` char(20) NOT NULL,
  `class_no` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_no`),
  KEY `student_class_fk` (`class_no`),
  CONSTRAINT `student_class_fk` FOREIGN KEY (`class_no`) REFERENCES `classes` (`class_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!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 ('2012001','张三','15000000000',1),('2012002','李四','16000000000',1),('2012003','王五','17000000000',3),('2012004','马六','18000000000',2),('2012005','田七','19000000000',2),('2012006','腊八','20000000000',NULL);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teacher` (
  `teacher_no` char(10) NOT NULL,
  `teacher_name` char(10) NOT NULL,
  `teacher_contact` char(20) NOT NULL,
  PRIMARY KEY (`teacher_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!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 ('001','张老师','1100000000'),('002','李老师','1200000000'),('003','王老师','1300000000');
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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 2016-01-25 16:56:15

下载：choose