MySQL武林秘籍，SQL学废必过考试(1)

2401_83817843

于 2024-04-19 14:46:27 发布

阅读量667

点赞数 22

分类专栏：程序员文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/2401_83817843/article/details/137964727

版权

这篇博客详细介绍了如何在MySQL中进行数据库创建、表的结构设计与修改，包括设置字符集、创建主键与外键约束、数据插入与更新、查询操作等，旨在帮助读者掌握MySQL的基本操作和数据完整性约束。

摘要由CSDN通过智能技术生成

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE tb_course (

courseNo char(6) NOT NULL,

courseName varchar(20) NOT NULL,

credit decimal(3,1) NOT NULL,

courseHour tinyint(2) NOT NULL,

term tinyint(1) DEFAULT NULL,

priorCourse char(6) DEFAULT NULL,

PRIMARY KEY (courseNo),

UNIQUE KEY courseName (courseName),

UNIQUE KEY uqidx_courseName (courseName(3)),

KEY fk_course (priorCourse),

CONSTRAINT fk_course FOREIGN KEY (priorCourse) REFERENCES tb_course (courseNo)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

/*!40101 SET character_set_client = @saved_cs_client */;

–

– Dumping data for table tb_course

–

LOCK TABLES tb_course WRITE;

/*!40000 ALTER TABLE tb_course DISABLE KEYS */;

INSERT INTO tb_course VALUES (‘11003’,‘管理学’,2.0,32,2,NULL),(‘11005’,‘会计学’,3.0,48,3,NULL),(‘21001’,‘计算机基础’,3.0,48,1,NULL),(‘21002’,‘OFFICE高级应用’,3.0,48,2,‘21001’),(‘21004’,‘程序设计’,4.0,64,2,‘21001’),(‘21005’,‘数据库’,4.0,64,5,‘21004’),(‘21006’,‘操作系统’,4.0,64,5,‘21001’),(‘31001’,‘管理信息系统’,3.0,48,3,‘21004’),(‘31002’,‘信息系统-分析与设计’,2.0,32,4,‘31001’),(‘31005’,‘项目管理’,3.0,48,5,‘31001’);

/*!40000 ALTER TABLE tb_course ENABLE KEYS */;

UNLOCK TABLES;

–

– Table structure for table tb_score

–

DROP TABLE IF EXISTS tb_score;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE tb_score (

studentNo char(10) NOT NULL,

courseNo char(6) NOT NULL,

score decimal(4,1) NOT NULL,

PRIMARY KEY (studentNo,courseNo),

KEY idx_stuNo_courNo (courseNo,studentNo),

KEY idx_courseNo (courseNo),

CONSTRAINT fk_score_courNo FOREIGN KEY (courseNo) REFERENCES tb_course (courseNo),

CONSTRAINT fk_score_stuNo FOREIGN KEY (studentNo) REFERENCES tb_student (studentNo)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

/*!40101 SET character_set_client = @saved_cs_client */;

–

– Dumping data for table tb_score

–

LOCK TABLES tb_score WRITE;

/*!40000 ALTER TABLE tb_score DISABLE KEYS */;

INSERT INTO tb_score VALUES (‘2013110101’,‘11003’,90.0),(‘2013110101’,‘21001’,86.0),(‘2013110103’,‘11003’,89.0),(‘2013110103’,‘21001’,88.0),(‘2013110201’,‘11003’,78.0),(‘2013110201’,‘21001’,92.0),(‘2013110202’,‘11003’,82.0),(‘2013110202’,‘21001’,85.0),(‘2013310101’,‘21004’,83.0),(‘2013310101’,‘31002’,68.0),(‘2013310103’,‘21004’,80.0),(‘2013310103’,‘31002’,76.0),(‘2014210101’,‘11003’,80.0),(‘2014210101’,‘11005’,75.0),(‘2014210101’,‘21001’,60.0),(‘2014210101’,‘21002’,93.0),(‘2014210101’,‘21004’,89.0),(‘2014210101’,‘21005’,55.0),(‘2014210101’,‘21006’,80.0),(‘2014210101’,‘31001’,68.0),(‘2014210101’,‘31002’,77.0),(‘2014210101’,‘31005’,85.0),(‘2014210102’,‘21002’,95.0),(‘2014210102’,‘21004’,88.0),(‘2014310101’,‘21001’,79.0),(‘2014310101’,‘21004’,80.0),(‘2014310102’,‘21001’,91.0),(‘2014310102’,‘21004’,87.0);

/*!40000 ALTER TABLE tb_score ENABLE KEYS */;

UNLOCK TABLES;

–

– Table structure for table tb_student

–

DROP TABLE IF EXISTS tb_student;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE tb_student (

studentNo char(10) NOT NULL,

studentName varchar(10) NOT NULL,

sex char(2) DEFAULT NULL,

birthday date DEFAULT NULL,

native varchar(20) DEFAULT NULL,

nation varchar(20) DEFAULT ‘汉’,

classNo char(6) DEFAULT NULL,

PRIMARY KEY (studentNo),

KEY fk_student (classNo),

CONSTRAINT fk_student FOREIGN KEY (classNo) REFERENCES tb_class (classNo)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

/*!40101 SET character_set_client = @saved_cs_client */;

–

– Dumping data for table tb_student

–

LOCK TABLES tb_student WRITE;

/*!40000 ALTER TABLE tb_student DISABLE KEYS */;

INSERT INTO tb_student VALUES (‘2013110101’,‘张晓勇’,‘男’,‘1997-12-11’,‘山西’,‘汉’,‘AC1301’),(‘2013110103’,‘王一敏’,‘女’,‘1996-03-25’,‘河北’,‘汉’,‘AC1301’),(‘2013110201’,‘江山’,‘女’,‘1996-09-17’,‘内蒙’,‘锡伯’,‘AC1302’),(‘2013110202’,‘李明’,‘男’,‘1996-01-14’,‘广西’,‘壮’,‘AC1302’),(‘2013310101’,‘黄菊’,‘女’,‘1995-09-30’,‘北京’,‘汉’,‘IS1301’),(‘2013310102’,‘林海’,‘男’,‘1996-01-18’,‘北京’,‘满’,‘IS1301’),(‘2013310103’,‘吴昊’,‘男’,‘1995-11-18’,‘河北’,‘汉’,‘IS1301’),(‘2014210101’,‘黄涛’,‘男’,‘1997-04-03’,‘湖南’,‘侗’,‘CS1401’),(‘2014210102’,‘郭志坚’,‘男’,‘1997-02-21’,‘上海’,‘汉’,‘CS1401’),(‘2014210103’,‘王玲’,‘女’,‘1998-02-21’,‘安徽’,‘汉’,‘CS1401’),(‘2014310101’,‘王林’,‘男’,‘1996-10-09’,‘河南’,‘汉’,‘IS1401’),(‘2014310102’,‘李怡然’,‘女’,‘1996-12-31’,‘辽宁’,‘汉’,‘IS1401’),(‘2015310103’,‘李彤’,‘男’,NULL,NULL,‘傣’,‘IS1401’);

/*!40000 ALTER TABLE tb_student 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 CHA