1 项目四 建库、建表与数据表管理项目四建库、建表与数据表管理MySQL数据库任务驱动式教程（第3版）（微课版）2 项目四 建库、建表与数据表管理任务7 创建数据库和表【任务背景】S学校要建立一个教学管理系统根据需求分析，要求创建学生信息、课程、成绩、院系单位、教师和讲授等数据表来存储数据接下来，要创建数据库，设计数据表的结构，并初始化相关表数据任务要求】本任务将学习创建和管理数据库、创建和管理表、表数据操作，以及对JSON数据类型的使用的基本方法和技巧在任务实施过程中，要特别注意表的规范化，要注意数据类型的正确选择，还要注意数据库和数据表字符集的统一问题3 项目四 建库、建表与数据表管理创建与管理数据库使用CREATE DATABASE或CREATE SCHEMA命令可以创建数据库其语法结构如下创建库n CREATE DATABASE | SCHEMA IF NOT EXISTS DB_NAME DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name【任务7.1】创建数据库JXGLmysqlCREATE DATABASE JXGL;【任务7.2】创建数据库CPXS，并指定字符集为GB 2312。
mysqlCREATE DATABASE CPXS DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;4 项目四 建库、建表与数据表管理创建与管理数据库n 用SHOW DATABASES命令查看库，运行结果如图7.1所示n 创建数据库并不表示选定并使用它，要选定或使用所创建的库，必须执行明确的操作为了使JXGL成为当前的数据库，使用如下命令n mysqlUse JXGL;查看库图7.1查看库运行结果5 项目四 建库、建表与数据表管理创建与管理数据库n 数据库创建后，如果需要修改数据库的参数，可以使用ALTER DATABASE命令n 语法格式如下n ALTER DATABASE | SCHEMA db_name DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name修改库【任务7.3】将JXGL数据库的字符集修改为GB 2312，校对原则修改为gb2312_chinese_cimysqlALTER DATABASE JXGL DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci6 项目四 建库、建表与数据表管理创建与管理数据库n 已经创建的数据库，如果需要删除，可使用DROP DATABASE命令。
n 语法格式如下n DROP DATABASE IF EXISTS db_name删除库【任务7.4】删除JXGL库mysql DROP DATABASE JXGL; 特别要注意的是，删除了数据库，数据库里的所有表也同时被删除最好先对数据库做好备份，再执行删除操作7 项目四 建库、建表与数据表管理创建与管理表表决定了数据库的结构，是用来存放数据的一个库需要什么表，各数据库表中有什么样的列，是要合理设计的创建表的语法格式如下CREATE TEMPORARY TABLE IF NOT EXISTS table_name ( column_definition , . | index_definition ) table_option select_statement;创建表8 项目四 建库、建表与数据表管理创建与管理表与本书配套的教学示例数据库为学生管理系统（JXGL），在这个库中要设计6张表：students（学生信息表）、course（课程表）、score（成绩表）、departments（院系单位表）、teachers（教师表）和teach（讲授表）各表的结构见表7.17.6字段名数据类型长度是否空值是否主键/外键默认值备注s_no定长字符型CHAR6否主键学号s_name定长字符型CHAR6否姓名sexENUM(男,女)2是男性别birthday日期型DATE否出生日期d_no定长字符型Char6否外键系别address变长字符型VARCHAR20否家庭地址phone变长字符型VARCHAR20否联系photo二进制BLOB是照片表7.1students9 项目四 建库、建表与数据表管理创建与管理表表7.2course字段名数据类型长度是否空值是否主键/外键默认值备注c_no定长字符型CHAR4否主键课程号c_name定长字符型CHAR10否课程名hours小整数型TINYINT3否学时credit小整数型TINYINT3否学分type变长字符型VARCHAR5否必修类型表7.3score字段名数据类型长度是否空值是否主键/外键默认值备注s_no定长字符型CHAR8否主键、外键学号c_no定长字符型CHAR4否主键、外键课程号report浮点数FLOAT3，1否0成绩10 项目四 建库、建表与数据表管理创建与管理表表7.4departments表7.5teachers字段名数据类型长度是否空值是否主键/外键默认值备注d_no定长字符型CHAR8否主键系别d_name定长字符型CHAR4否院系名称字段名数据类型长度是否空值是否主键/外键默认值备注t_no定长字符型CHAR8否主键教师编号t_name定长字符型CHAR4否教师姓名d_no定长字符型CHAR4否外键系别表7.6teach字段名数据类型长度是否空值是否主键/外键默认值备注t_no定长字符型CHAR8否主键、外键教师编号c_no定长字符型CHAR4否主键、外键课程编号11 项目四 建库、建表与数据表管理创建与管理表【任务7.5】创建表students。
mysql CREATE TABLE IF NOT EXISTS students (s_no CHAR(6)NOT NULL COMMENT学号, s_name CHAR(6)NOT NULL COMMENT姓名, sex ENUM(男, 女) DEFAULT 男 COMMENT 性别, birthday DATE NOT NULL COMMENT出生日期, d_no VARCHAR(6)NOT NULL COMMENT系别, address VARCHAR (20) NOT NULL COMMENT家庭地址, phone VARCHAR (20) NOT NULL COMMENT联系, photo BLOB COMMENT照片, PRIMARY KEY (s_no) ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.6】创建表coursemysql CREATE TABLE IF NOT EXISTS course(c_no CHAR(4)NOT NULL, c_name CHAR(10)NOT NULL, hours INT(3)NOT NULL, credit INT(3)NOT NULL, type EMUN(必修课, 选修) DEFAULT必修, PRIMARY KEY (c_no) ENGINE=InnoDB DEFAULT CHARSET=gb2312;12 项目四 建库、建表与数据表管理创建与管理表【任务7.7】创建表score。
mysql CREATE TABLE IF NOT EXISTS score (s_no CHAR(8)NOT NULL, c_no CHAR(4)NOT NULL, report FLOAT(3, 1) DEFAULT 0, PRIMARY KEY (s_no, c_no) ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.8】创建表departmentsmysql CREATE TABLE IF NOT EXISTS departments (d_no CHAR(8)NOT NULL COMMENT 系别, d_name CHAR(4)NOT NULL COMMENT 院系名称, PRIMARY KEY (d_no) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.9】创建表teachersmysql CREATE TABLE IF NOT EXISTS teachers ( t_no CHAR(8)NOT NULL COMMENT 教师编号, t_name CHAR(4)NOT NULL COMMENT教师姓名, d_no CHAR(4)NOT NULL COMMENT 系别, PRIMARY KEY (t_no) ENGINE=InnoDB DEFAULT CHARSET=gb2312;13 项目四 建库、建表与数据表管理创建与管理表【任务7.10】创建表teach。
mysql CREATE TABLE IF NOT EXISTS teach( t_no VARCHAR(8)NOT NULL, c_no VARCHAR(4)NOT NULL, KEY t_no (t_no), KEY c_no (c_no) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.11】在CPXS库中，创建进货单表，进货ID是自动增量，将进货单价字段的精度设置为8，小数位设置为2位，进货时间默认为当前时间mysqlUSE CPXSmysqlCREATE TABLE进货单( 进货ID TINYINT NOT NULL AUTO_INCREMENT, 商品ID VARCHAR(10)NOT NULL, 进货单价FLOAT(8, 2) NOT NULL, 数量 INT NOT NULL, 进货时间DATE TIME NOT NULL DEFAULT NOW(), 进货员工ID CHAR(6)NOT NULL, PRIMARY KEY (进货ID) );14 项目四 建库、建表与数据表管理创建与管理表进货单表结构见表7.7字段名数据类型长度AUTO_INCREMENT是否空值是否主键进货IDTINYINT5是否是商品IDVARCHAR10否进货单价FLOAT8,2否数量INT否进货时间TIMESTAMP否进货员工IDCHAR6否15 项目四 建库、建表与数据表管理创建与管理表创建数据表后，可用SHOW TABLES查询已创建的表的情况。
mysql SHOW TABLES;运行结果如图7.2所示查看表图7.2运行结果16 项目四 建库、建表与数据表管理创建与管理表ALTER TABLE用于更改原有表的结构，例如，增加或删减列、重新命名列或表，以及修改默认字符集语法格式如下修改表ALTER IGNORE TABLE tbl_namealter_specification , alter_specification .alter_specification:ADD COLUMN column_definition FIRST | AFTER col_name /添加字段| ALTER COLUMN col_name SET DEFAULT literal | DROP DEFAULT /修改字段默认值| CHANGE COLUMN old_col_name column_definition /重命名字段FIRST|AFTER col_name| MODIFY COLUMN column_definition FIRST | AFTER col_name /修改字段数据类型| DROP COLUMN col_name/删除列| RENAME TO new_tbl_name/对表重命名| ORDER BY col_name/按字段排序| CONVERT TO CHARACTER SET charset_name COLL。