项目三 操作学生管理数据库中的表

项目三 操作学生管理数据库中的表

1，使用MySQL中的常用数据类型

数据类型决定了数据的取值范围、存储方式与占用的空间大小以及能够对其进行的一组合法操作。

1.1，使用数值类型

1，MySQL 的数值类型大致可以分为两个类别：整数类型和浮点类型或小数类型。

3，int类型特点：

• MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值
  时，可以把这个值加长到指定的长度。
• 如果需要存储的字段值超出许可范围，MySQL 会根据允许范围最接近它的一端截短后再进行存储。
• UNSIGNED 修饰符规定字段只保存正值
• ZEROFILL 修饰符规定 0（不是空格）可以用来填补输出的值。

4，float，double，decimal类型

• 例：如语句 FLOAT 7,3 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。

1.2，使用字符串类型

1，MySQL 提供了 10 种基本字符串类型

2，char和varchar类型

#CHAR 类型的使用格式为 ：
char 或者 char（长度）

#CHAR 类型可以使用 BINARY 修饰符，使用格式为：
CHAR （M） BINARY

CHAR 和 VARCHAR 不同之处在于 MySQL 数据库处理圆括号中的指示器长度的方式。CHAR 把这个

大小视为值的大小，若长度不足时用空格补足。

CHAR 类型在保存数据时不论数据的实际长度为多少个字符，实际分配的空间都为 CHAR 类型指示器长度规定的字节数；VARCHAR 类型会根据数据的实际长度动态改变存储值的长度；

1.3，使用日期和时间类型

1，DATE、TIME 和 YEAR 类型：MySQL 用 DATE 和 YEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。

• 如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开， 而 TIME 类型的值应该使用冒号作为分隔符分开。

• 需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

• 使用 current_date 或者 now () 函数的值作为输入，将系统的当前日期保存到数据库中。

• 使用 current_time 或者 now ()函数的值作为输入，将系统的当前时间保存到数据库中。

2、DATETIME 和 TIMESTAMP 类型

• 使用 now( )或者 current_timestamp( )函数的值作为输入，将系统的当前日期和时间保存到数据库中
• 如果没有为 DATETIME 类型的字段赋值，或者为其赋予 null 值，存储到数据库中的值均为 null。
• 如果没有为 TIMESTAMP 类型的字段赋值，或者为其赋予 null 值，存储到数据库中的值均为系统的 当前日期和时间。

1.4，使用复合类型

一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类 型允许从一个集合中取得任意多个值

1，enum类型：是枚举类型。表示从一个集合中选取一个值，有点类似于单选项，比如人的性别。

#格式为：
enum ‘值 1’, ‘值 2’, ……, ‘值 n’ 称为枚举列表

需要注意的是，枚举列表中的每个值都有一个顺序编号，MySQL中存入的就是这个顺序编号，而不是列表中的值。错误值用索引 0 或者一个空字符串表示.

2，set类型：SET 类型可以从预定义的集合中取得任意数量的值。

2，在学生管理数据库中创建数据表

2.1，区分MySQL数据库的不同引擎

1，从数据的逻辑存储结构上讲，表是数据库中真正存储数据的单元，是用户在数据库管理系统中操作数据的主要对象。而从数据的物理存储结构上讲，表中的数据又是存放到它所对应的若干个物理文件中，这些文件是真实存在于磁盘上的，它们的位置是由 MySQL 系统自动安排的。

2，在 MySQL 系统中包括的存储引擎有：innodb 存储引擎、myisam 存储引擎、ndb 存储引擎、memory 存储引擎、archive 存储引擎、maria 存储引擎。从 MySQL 5.5 版以后，InnoDB 就是默认的存储引擎。

3，一个数据表所对应的物理文件就是和服务器或该表设置的存储引擎有关。

4，表的存储引擎若是innodb，那么该表对应的物理文件包括两个都以表名命名的，扩展名分别是“.frm”和“.ibd”的物理文件。
其中“.frm”文件是表结构定义文件，“.ibd”文件是存储数据、索引和插入缓冲等信息的文件。

#查看当前服务器的默认存储引擎
show engines;

#查看某个表的存储引擎
show create table 表名;

2.2，根据业务需求在数据库中创建表

1，使用create table语句创建表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] 
[table_options] [select_statement]

temporary：临时表 
tbl_name:表名 
表名必须符合标识符规则。一般情况下在表名中仅使用字母、数字及下划线。
#[create_definition]：关键字段 
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE KEY] | [PRIMARY KEY] [COMMENT 'string']
	col_name:段名 
	type：字段数据类型 
	[not null|null]：字段是否允许为空 
	[default default_value]:默认值 
	auto_increment:自动增长列[只有整型列才能设置此属性] 		//常用
	[unique key] | [primary key]:字段是否为唯一键或主键 		//常用
	[comment 'string']：描述性文字 	//常用

#table_options：新建表的一些属性设置
	engine | type = engine_name:为表指定引擎
    auto_increment = value：为表设置初始的 AUTO_INCREMENT 值 
    [default] character set charset_name [collate collation_name]:指定默认的字符集合与排列规则 
    default character set：字符集 	//常用
    default collate：排列规则	//常用
    comment = 'string'：表的注释，最长 60 个字符。 
#select_statement：该选项表示新建的表是基于已有的查询结果集创建的

#创建student表（学号stuid，姓名stuname，性别stusex，生日stubirthday，籍贯native，民族national），联系电话telephone，班级编号classid）
create table if not exists Student
(StuId char(8),
StuName varchar(10),
StuSex char(2),
StuBirthday date,
Native varchar(20) comment '籍贯',
National char(6) comment '民族',
Telephone char(11),
ClassID char(10)
);

#创建course表（课程编号id，课程名称name，课程性质nature，学分credit，备注remark text）
create table Course
(CourseID char(8),
CourseName varchar(25),
CourseNature char(10) comment '课程性质',
Credit tinyint(1) comment '学分',
CourseRemark text comment '备注' )
engine=innodb
default character set utf8
default collate utf8_general_ci;

#utf8是字符集；utf8_general_ci是排列顺序。

2，使用navicat工具创建表

3，完成对数据表的各项操作

3.1，查看表结构

#show tables；查看有哪些表，但是查不到临时表

#show create table表名； 查看表的定义语句

#describe|desc 表名；查看表结构和定义

#select * from 表名；查看表内容

3.2，录入新的学生信息

#使用insert into语句插入数据

#可以将查询结果集中的数据插入到新表中（新旧表的数据是相同的）
格式：create table 表名 [(字段的定义列表)] [as] select 语句
#该语句在创建新表时可以复制结构、空值、数据，但索引和约束不会复制,当然复制的记录多少和 select 语句的查询结果集有关.

#建表时，只想复制表结构不想复制数据
格式：create table 新表名 like 旧表名；

3.3，在数据表中使用默认值

#向表中添加新记录时，只有当这个字段没有指定值或指定了 default 关键字的时候，默认值才起作用，否则字段的值就是用户所指定的值。
#用 default定义的默认值，只能是常量.
CREATE  [TEMPORARY]  TABLE  [IF NOT EXISTS]  表名
(字段n	数据类型	[not null|null][ddefault 默认值]);		#默认属性是可以改的

#创建teacher表（编号id，姓名name，性别sex，生日birthday，电话telephone，所在系部编号）
create table Teacher
(TeacherID char(10),
TeacherName varchar(8),
TeacherSex char(2)default'女',
TeacherBirthday date,
Telephone char(11),
DepartmentID char(8)
 );

3.4，删除无用的数据表

#删库语句
drop table [if exists] 表名1，表名2...

4，根据业务需求调整表结构

#alter table 语句的总体格式如下:
alter table 表名
add  column  字段名 字段的定义  [first | after 字段名]  --添加新字段
 |alter [column] 字段名 {set default 默认值|drop default} --设置默认值或删除默认值
 |change [column] 旧的字段名 新的字段名 字段的定义 --修改字段的名称或字段的定义 （即使新旧字段相同也必须将数据类型充新写）
 |modify [column] 字段名 字段的新定义 --修改字段的定义 
 |drop [column] 字段名 --删除字段
 |rename to 新表名 --为表重命名（rename table 旧表名 to 新表名）

5，为数据表添加约束

• 数据库中的数据并不是杂乱无章的、随意的保存在表中的，数据会因业务逻辑的原因具有一些约束和限制，也就是说数据要保证它的合理性、正确性和完整性。类似于这样对字段取值的限制，我们称之为约束

• 表中的约束可以在创建表的同时为字段定义约束，也可以在创建表之后为字段增加约束

(可以在 create table语句中定义约束也可以在 alter table 语句中增加约束。)

#约束的统一格式：
[constraint 约束名]  约束关键字及定义语句

5.1，使用两种方式创建约束

1，create table语句中定义约束

• 约束是对表中字段取值的限制，因此约束是定义在表结构中的

• 将只限制一个字段的约束称为字段级约束，一般直接定义在被约束的字段后面；

• 同时限制多个字段取值的约束，称为表级约束，需要在所有约束字段定义完成后才能定义表级约

  束，因此表级约束一般定义在表结构的最后。

#使用 create table 语句定义表结构的同时定义约束
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(字段 n 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [约束定义语句] [COMMENT 'string'],
[[constraint 约束名] 约束定义语句] )
[table_options] [select_statement]

• 约束定义语句可以写在某个字段的后面，作为字段的属性定义，但不能为约束命名，也可以定义在

表结构的最后，作为单独的一部分定义，此时可以使用 constraint 关键字为约束命名。

• 对表中字段的定义内容与顺序大致为：字段名、数据类型、宽度、非空约束的定义、默认值的定

义、 自动增长列、其他约束的定义、字段的描述性文字。

2，alter table 语句增加约束

ALTER TABLE 表名 ADD  CONSTRAINT 约束名  约束定义语句

• 注：如果现有数据已经违反了要建立的约束，则增加约束失败，必须修改数据后再增加约束

5.2，创建部门表和成绩表的主键约束

主键约束是限制表中的字段取值不能重复并且不能为空。表中的类似于编号这样的字段被定义为主键约束。

[constraint 约束名] primary key [(字段名)] 
#主键约束的关键字是“primary key；
#若约束定义在字段后面，字段名必须省略，不能为约束命名
#若字段定义完成后再定义约束，字段名不能省略，能为约束名

#创建系部表（直接将约束定义在字段后面）
mysql> create table Department (DepartmentID char(8),primary key(DepartmentID),
DepartmentName varchar(20) );

#系部表（字段定义完成后，再单独定义约束）
mysql> drop table department;
create table Department
(DepartmentID char(8),
primary key(DepartmentID),
DepartmentName varchar(20) 
);

#系部表，为约束命名
mysql> drop table department;
create table Department
(DepartmentID char(8),
DepartmentName varchar(20),
constraint pk_id primary key(DepartmentID)
);

#先建表，再使用修改语句为表添加约束
mysql> drop table department;
create table Department
(DepartmentID char(8),
DepartmentName varchar(20)
);
alter table Department add constraint pk_id primary key(DepartmentID);

#插入数据验证
insert into Department values('1311001','信息工程系');

组合主键中的每个字段允许有重复值，但是组合在一起不能重复，而且组合中的每个字段都不能取空值

[constraint 约束名] primary key [(字段名 1, 字段名 2,…, 字段名 n)]

#创建成绩表
create table Score
(StuID char(8),
CourseID char(8),
ExamScore int,
PartScore int,
primary key(StuID,CourseID)
);

#插入数据验证
insert into Score values('150101','10101',98,91);

组合主键只能定义为表级约束，定义在表的最后或被约束字段定义完成之后。

5.3，创建学生表和专业信息表的唯一约束

唯一键约束，又称替代键约束，它限制了被约束字段取值的唯一性，即字段值不能重复，但是允许为空值 null，一个表中可以有多个唯一键约束

#格式：
[constraint 约束名] unique [(字段名)]
说明：当唯一键约束直接建立在字段后面的时候，“unique”关键字后面的字段名必须省略。


#创建专业信息表
create table Major (MajorID char(8)primary key, 
MajorName varchar(20)unique, 
DepartmentID char(8));

#插入信息验证有效性
insert into Major values('67001','移动通信开发','1311007');

5.4，创建学生表和教室信息表的非空约束

创建学生表和教室信息表的非空约束

• 不允许为空约束是指限制一个字段的取值不允许有空值，即不能取 null 值

• 格式：在被约束的字段定义的数据类型的后面增加 not null 关键字即可，非空约束只能定义在当前字段的后面

#创建教室信息表（教室编号为主键约束，教室类型为非空约束）
create table Classroom 
(ClassroomID varchar(10)primary key, 
ClassroomType varchar(20)not null,
PersonNumber int, Place varchar(20));

#插入数据验证有效性
 insert into Classroom values('cm001','多媒体教室','100','a104');
 
#为表增加非空约束时，必须使用 alter table 语句的 modify 子句

5.5，创建教师表和点名表的检查约束

• 检查约束是指对一个字段或多个字段取值范围的约束。新插入的数据必须满足规定的范围。

#定义检查约束的格式如下：
[constraint 约束名]  check（检查约束的表达式）

检查约束的表达式：就是对被约束字段的取值范围进行限制的式子，涉及到的字段可以是一个也可以是多个。
#点名表
mysql> create table RollCall (StuID char(8), CourseID char(8), AttendNumber int check(AttendNumber>=0), AbsenceNumber int check(AbsenceNumber>=0), LateNumber int check(LateNumber>=0), primary key(StuID,CourseID) );
#验证一下(答案是不行滴，这个就违反了检查约束)
insert into RollCall values('150101','10101',-54,0,0);

#修改教师表添加检查约束
mysql> alter table Teacher add check(TeacherSex in('男','女'));
#插入数据测试
mysql> insert into Teacher values('221102158','李小含','男','1980-04-09','15081267071','');
mysql> insert into Teacher values('221103160','王晓萌','女','1980-10-03','15630244478','1311001');

• 注意：MYSQL中所有的存储引擎（如，INNODB 和MyISAM）均对CHECK子句进行分析，但是忽略CHECH子句。也就是说MYSQL中只对CHECK约束进行语法检查，但并不生效。

• 在某种情况下，可以使用 enum 数据类型（枚举类型）代替检查约束。

#例如：修改教师表，将教师性别字段的数据类型修改为枚举类型。
（1）先清除数据。
mysql> delete from Teacher;
（2）修改性别字段的数据类型。
mysql> alter table Teacher modify TeacherSex enum('男','女');
（3）插入数据问题
mysql> insert into Teacher values('221100135','刘一然','女','1977-02-12','13483217762','');
mysql> insert into Teacher values('221101146','刘亚军','男','1977-05-06','13784426958','');
(4)我们插入一天错误数据，发现是插入不了的，说明枚举类型代替检查约束。
mysql> insert into Teacher values('221102158','李小含','无','1980-04-09','15081267071','');

5.6，创建学生表，班级表和排课信息表的外键约束

• 一个表中的某字段的取值应该参考另一个表中的某字段的取值，我们称之为外键约束。受约束的表通常称为子表，约束子表的表称为父表，外键约束就是定义在子表中。

• 父表对子表的限制：

  • 子表中的外键字段的数据类型要和父表中参考字段的数据类型一样。
  • 父表中的参考字段必须被定义为主键约束或唯一键约束，才能约束子表中的外键字段。
  • 子表中的外键字段的值可以为空值，但是如果有值必须在父表参照列的取值范围内。
  • 父表中的主键值或唯一键值一旦被子表参照，那么这些值就不能随意的修改或删除

#外键约束格式：
[constraint 约束名] [foreign key (字段名)] references 父表(主键字段|唯一键字段):
[on delete restrict | cascade| set null | no action]
[on update restrict | cascade| set null | no action]

constraint 约束名:外键约束名，可省略
foreign key (字段名):关键字
references 父表(主键字段|唯一键字段):外键字段的取值要参考父表

on delete 选项或 on update 选项：表示当要删除或更新父表中被参照列上在外键中出现的值时，子表
 中的相关数据如何处理:
- restrict；拒绝对父表的更删
- cascade：会自动更删表中匹配的行
- set null：将子表中与之对应的外键字段设置为null
- no action：若有相关外键值在被参考的表里，删更会被拒绝
- set default：指定子表的外键列为默认值
#MySQL 中 InnoDB 类型的表支持对外键限制条件进行检查
#创建‘排课表’验证外键约束(可能没有主键约束/唯一键约束，所以下面有添加)
mysql> create table TeachingCourse
(ClassID char(10),
CourseID char(8),
ClassroomID varchar(10),
TeacherID char(10),
WeekDay char(3) not null,
Part tinyint not null,
primary key(ClassID,CourseID,ClassroomID,TeacherID,WeekDay,Part),
foreign key(ClassID) references Class(ClassID),
foreign key(CourseID) references Course(CourseID),
foreign key(ClassroomID) references Classroom(ClassroomID),
foreign key(TeacherID) references Teacher(TeacherID)
);

mysql> alter table Class add primary key(ClassID);
mysql> alter table Course add primary key(CourseID);
mysql> alter table Classroom add primary key(ClassroomID)
mysql> alter table Teacher add primary key(TeacherID);

#班级表
mysql> create table Class( ClassID char(10), ClassName varchar(20), Num int(11), TeacharID char(10), MajorID char(8));

案例1，#为学生表的 ClassID 增加外键约束，使其参考 Class 表的 ClassID，并且定义级联删除和级联更新属性。
#先删除表中数据.
mysql> delete from Student;
#创建外键约束
mysql> alter table Student add foreign key(ClassID) references Class(ClassID) on delete restrict on update restrict;
                   
案例2，#为班级表中的专业编号增加外键约束其参考专业表的专业编号字段，并且定义级联修改属性，当专业表的编号字段被修改时，班级表中的专业编号自动被更新。
#创建外键约束
mysql> alter table Class add constraint fk_majorID foreign key(MajorID) references Major(MajorID) on update cascade;

（1）#向父表Major插入
mysql> insert into Major values('67001','移动通信开发','131107');
（2）#向Class插入
mysql> insert into Class values('mb1501','移动15级1班','50','221198101','67001');
（3）#修改父表中的值67001改成77001
mysql> update Major set MajorID='77001' where MajorID='67001';

5.7，删除冗余约束

1，#删除主键约束
格式：alter table 表名 drop primary key;

2，#删除唯一约束|索引
格式：
（1）alter table 表名 drop index 约束名|索引名
（2）drop index 约束名|索引名 on 表明

3，#删除外键约束（所以外键约束可以独立于索引而被删除）
格式：alter table 表名 drop foreign key 外键约束名

6，识别并定义表的自增长列

• 使用auto_increment关键字定义自动增长列

• 注意事项：

  • 该字段必须是整数类型，且必须是一个主键或唯一键
  • 初始值定义：auto_increment=初始值，否则默认从1开始

#创建自动增长列
mysql> create database product_management;
mysql> use product_management;
mysql> create table product(pid int auto_increment unique,pname varchar(20));

#插入数据观察pid的变化
mysql> insert into product(pname) values('计算机');
mysql> insert into product values(null,'打印机');
mysql> insert into product values(default,'扫描仪');
mysql> select * from product;
+-----+-----------+
| pid | pname     |
+-----+-----------+
|   1 | 计算机    |
|   2 | 打印机    |
|   3 | 扫描仪    |
+-----+-----------+
#在插入数据时，若没有为自动增长列赋值或者是赋予 null 或者是赋予default 关键字，系统将为其赋值从 1 开始逐个递增

#设置初始值法
mysql> insert into product values(10,'投影机');

#为表的自动增长列设置初始值
mysql> create table product1 (pid int auto_increment unique,pname varchar(20)) auto_increment=1000;
#插入数据观察
mysql> insert into product1(pname) values('计算机');
mysql> insert into product1(pname) values('打印机');
mysql> select * from product1;
+------+-----------+
| pid  | pname     |
+------+-----------+
| 1000 | 计算机    |
| 1001 | 打印机    |
+------+-----------+

#在定义字符型字段时可以在类型的后面加上 binary 关键字，这样字段值就可以区分大小写
mysql> create table t3(name varchar(10)binary);
mysql> insert into t3 values('aa');
mysql> select * from t3 where name='AA';	#不行的
Empty set (0.00 sec)	
mysql> select * from t3 where name='a';		#这也是不行的
Empty set (0.00 sec)

mysql> select * from t3 where name='aa';	#只能精准查询
+------+
| name |
+------+
| aa   |
+------+