学生信息管理
学生信息管理
students表的表结构
| 字段名称 | 数据类型 | 长度 | 小数位数 | 是否允许NULL | 约束 | 说明 |
|---|---|---|---|---|---|---|
| sno | char | 7 | 否 | 主码 | 学号 | |
| sname | varchar | 8 | 否 | 姓名 | ||
| ssex | char | 2 | 是 | 性别 | ||
| bday | date | 是 | 出生日期 | |||
| bplace | char | 10 | 是 | 生源地 | ||
| sdept | char | 16 | 是 | 系别 | ||
| speciality | varchar | 20 | 是 | 专业 | ||
| class | char | 10 | 是 | 班级 | ||
| IDNum | char | 18 | 是 | 身份证号 | ||
| Phone | char | 11 | 是 | 手机号 |
course表(课程名称表)的表结构
| 字段名称 | 数据类型 | 长度 | 是否允许NULL | 约束 | 说明 |
|---|---|---|---|---|---|
| cno | char | 7 | 否 | 主码 | 课号 |
| cname | varchar | 20 | 否 | 课程名 | |
| ccredit | int | 2 | 否 | 学分 | |
| cpno | Char | 7 | 是 | 外码，参照本表的课号 | 先修课号（上本课程前必须先学习的课） |
sc表(选课成绩表)的表结构
| 字段名称 | 数据类型 | 长度 | 小数位数 | 是否允许NULL值 | 约束 | 说明 |
|---|---|---|---|---|---|---|
| sno | char | 7 | 否 | 组合主码、外码 | 学号 | |
| cno | char | 7 | 否 | 组合主码、外码 | 课号 | |
| score | decimal | 4 | 1 | 是 | 成绩 | |
| point | decimal | 2 | 1 | 是 |
teachers表(教师表)的表结构
| 字段名称 | 数据类型 | 长度 | 小数位数 | 是否允许NULL值 | 约束 | 说明 |
|---|---|---|---|---|---|---|
| tno | char | 3 | 否 | 主码 | 教师编号 | |
| tname | varchar | 8 | 是 | 教师姓名 | ||
| tsex | char | 2 | 是 | 性别 | ||
| tbirthday | date | 是 | 出生日期 | |||
| tdept | char | 16 | 是 | 系别 | ||
| ps | char | 10 | 是 | 职称 |
teaching表(授课表)的表结构
| 字段名称 | 数据类型 | 长度 | 小数位数 | 是否允许NULL值 | 约束 | 说明 |
|---|---|---|---|---|---|---|
| sid | Int | 否 | 主码，自增 | 序号 | ||
| cno | char | 7 | 否 | 外码 | 课号 | |
| tno | char | 3 | 是 | 外码 | 教师编号 | |
| class | char | 10 | 是 | 班级 | ||
| cterm | int | 1 | 是 | 学期 | ||
| period | int | 3 | 是 | 学时 |
1、用Navicat管理工具提供的图形界面创建表。
在JXGL数据库中创建表students表和sc表。表结构要求见上面，仅建表，先不添加约束。
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sno` int(11) NOT NULL,
`cno` int(11) DEFAULT NULL,
`score` varchar(255) DEFAULT NULL,
`point` varchar(255) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`sno` char(7) NOT NULL,
`sname` varchar(8) DEFAULT NULL,
`ssex` char(2) DEFAULT NULL,
`bday` date DEFAULT NULL,
`bplace` char(10) DEFAULT NULL,
`sdept` char(16) DEFAULT NULL,
`speciality` varchar(20) DEFAULT NULL,
`class` char(10) DEFAULT NULL,
`IDNum` char(18) DEFAULT NULL,
`Phone` char(11) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、用Navicat图形界面操作修改表结构
(1) 向students表中增加“入学日期”列(enrollment)，其数据类型为日期型
alter table students
add enrollment DATE;
(2) 将students表中的sdept字段长度改为20
alter table students
MODIFY sdept VARCHAR(20);
(3) 将students表中的sdept字段改为不允许为空
ALTER TABLE `students`
MODIFY COLUMN `sdept` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `bplace`;
(4) 将students表中的speciality字段改名为spec
ALTER TABLE `students`
CHANGE COLUMN `speciality` `spec` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `sdept`;
(5) 将students表中的spec字段删除
alter table students
DROP COLUMN spec;
(6) 删除students表中的enrollment字段
alter table students
DROP COLUMN enrollment;
3、利用create table 命令完成创建course表，表结构见上面，仅建表，先不添加约束。
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cno` char(7) NOT NULL,
`cname` varchar(20) DEFAULT NULL,
`ccredit` int(2) DEFAULT NULL,
`cpno` char(7) DEFAULT NULL,
PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4、用alter table命令完成第2题中的相应操作，并记录sql语句。
ALTER TABLE `course`
DROP COLUMN `cpno`,
MODIFY COLUMN `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `cno`,
CHANGE COLUMN `ccredit` `ccredit-mod` int(10) NULL DEFAULT NULL AFTER `cname`;
5、用Navicat图形界面操作给表格添加约束。
(1) 给students表和course表添加主键。
在指定的栏位后标注主键，操作预览SQL参考如下
ALTER TABLE `students`
ADD PRIMARY KEY (`sno`);
ALTER TABLE `course`
ADD PRIMARY KEY (`cno`);
(2) 给students表的sname字段设置非空约束。
在students指定的sname栏位后勾选非空，操作预览SQL参考如下
ALTER TABLE `students`
MODIFY COLUMN `sname` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `sno`;
(3) 给sc表添加外键，关联到students表和course表。
(4) 给students表的IDNum字段设置唯一性约束。
(5) 给students表中的ssex字段设置默认约束“男”。
6、用SQL命令进行完整性约束定义
(1) 创建以下表，并设置相应的约束。
/*创建teaching表*/
CREATE TABLE `teaching` (
sid int not null AUTO_INCREMENT primary key , /*自增，主键*/
`cno` CHAR(7) NOT NULL,
`tno` CHAR(3), foreign key (tno) references teachers(tno) on update cascade, /*tno上的外键*/
`cterm` INT(1) NULL,
`class` char(10) NULL,
foreign key (cno) references course(cno) /*cno上的外键*/
) ;
CREATE TABLE `sc` ( /*创建sc表*/
`cno` CHAR(7) NOT NULL,
`sno` CHAR(7) NOT NULL,
`score` decimal(4,1) NULL,
`point` decimal(2,1) NULL,
Primary key (cno,sno) /*主键*/
);
(2) 为sc表添加外键约束
ALTER TABLE `sc`
ADD CONSTRAINT `fk_cno_sc_course` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`);
ALTER TABLE `sc`
ADD CONSTRAINT `fk_sno_sc_students` FOREIGN KEY (`sno`) REFERENCES `students` (`sno`);
(3)为teaching表添加cterm列的检查约束，取值范围1～10
ALTER TABLE `teaching`
ADD CONSTRAINT `ck_tch_cterm` check ( cterm>=1 AND cterm<=10)
(4)删除course表中cpno列上的外键
alter table course drop foreign key fk_course
(5)course表中可以在哪一列上添加唯一约束，并用sql语句执行完成
ALTER TABLE course add UNIQUE key (ccredit);
数据操作
1、录入数据
向上面设计的students表、course表、sc表中录入一些数据，每个表录入3条数据，截图说明。
student表录入数据
course表录入数据
sc表录入数据
2、体验完整性作用
(1) 学号能否不录入？能否录入重复的？为什么？
学号不能不录入，在student表中sno作为主键存在、在sc表中作为外键且设定了不能为空约束，因此必须要录入；在student表中sno不可重复，在sc中一个学生可以对应多个课程因此可重复
(2) 观察students表中ssex字段的数据，说明默认值什么时候起作用？
在新增数据的时候没有设定ssex字段初始值，则默认值生效自动填充
(3) 观察所设计的数据是否有出现违反完整性约束的错误提示，需要注意哪些数据的录入先后顺序？请详细说明。
例如在录入sc表数据的时候，一些字段做了非空约束控制，因此在录入数据的时候会出现下述提示，需要相应去填充信息，这种情况同样适用于其他一些基本表的情况
此处选择sc表作为说明，主要是其创建了两个分别关联students、course的外键约束，因此在构建sc表数据的时候需考虑完整性约束，其数据是依赖于主表数据的存在，且当主表数据变动亦会根据联动的约束设置做出相应的操作。
例如下述如果录入无关的cno或者sno则会出现下述提示，此时则需要确认students、course表中是否存在正确的数据，确保数据无误后再进行数据添加操作
(4) students表的IDNum字段能否不录入数据？能否录入重复的数据？为什么？
从下述表结构说明来看，students并没有设定not null或者唯一约束，因此IDNum并没有受到限制，可以不录入数据也可以录入重复的数据
分析
1、关于NOT NULL
(1) 在定义基本表语句时，NOT NULL参数的作用是什么？
数据库字段属性，使用NOT NULL后该字段不接受NULL值。被设置为NOT NULL的字段必须赋值，可以根据实际应用场景调整为空字符串，但不可为NULL
(2) 如果students表中有几行数据的sdept字段值为空，能否执行下面的语句？为什么？
ALTER TABLE ` students `
modify column ` sdept ` char(16) not null;
(3) 主码列修改成允许NULL”能否操作？MySql是怎么处理的？
该操作不被允许，主键作为一个唯一标识的存在必须不能为空
通过在nacivat工具测试，会自动重新勾选not null限制
2、关于外码
(1) 根据下面设计的表结构，Employee表的外键能否设置成功？思考外码设置需要注意哪些问题？
Department表的结构
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 |
|---|---|---|---|---|
| dno | 部门号 | INT(4) | ||
| dname | 部门名 | VARCHAR(20) |
Employee表的结构
| 字段名 | 字段描述 | 数据类型 | 主键 | 外键 |
|---|---|---|---|---|
| eno | 员工号 | INT(10) | ||
| dno | 所在部门号 | Char(10) | √ | |
| name | 姓名 | VARCHAR(20) |
不能设置成功，Department表并没有指定主键是否为dno，且dno字段在两个表中类型并不一致；在构建主外键关联，因考虑如下内容
a.检查从表的外键字段的类型以及大小是否和主表完全一致
b.试图引用的其中一个外键没有建立起索引，关联的字段没有设置primary key属性，则需为它创建一个索引
c.且参考书籍内容有相应说明：一个或两个表是MyISAM引擎的表，若想要使用外键约束，必须是InnoDB引擎
(2) 如果主表无数据，从表的数据能输入吗？
从表的数据依赖于主表数据，因此如果主表无数据，从表输入时会提示相应错误
(3) 先创建从表，再创建主表是否可以？
表结构的构建不限定先后顺序，但是在创建主外键关联的时候必须保证主表的存在才能构建主表和从表的主外键关联
3、关于主码和唯一约束
(1) 唯一约束列是否允许NULL值？
唯一索引中允许有null。唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的，允许为空，但只能出现一个空值
(2) 一张表可以设置几个主码，可以设置几个唯一约束？
一个表只能由一个主键，但是这个主键可以包含多个字段（组合主键概念）
可以设置多个唯一约束