MySQL（二）表结构的管理

MySQL表结构的管理

 

一、MySQL数据库类型

 

 

 

1.MySQL整数类型

 

 

 

　　　　　　　　　　　　　　　　　　5种整数类型的取值范围

 

 

2.MySQl小数类型

 

 

 

decimal（length，precision）用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定了该小数的最大位数，precision用于设置精度（小数点后数字的位数）。

例如：decimal（5,2）表示小数的取值范围是-999.99~999.99，而decimal（18,9）表示-99999~99999的整数。

decimal（length，precision）占用的存储空间由length以及precision共同决定。

例如：decimal（18,9）会在小数点两边各存储9个数字，共占用9个字节的存储空间，其中4个字节存储小数点之前的数字，1个字节存储小数点，另外4个字节存储小数点之后的数字。

 

3.MySQL字符串类型

 

 

注：每个汉字占用3个字节的存储空间

 

4.MySQL日期类型

 

 

　　　　　　　　　datetime和timestamp的书写格式

 

 

将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。

 

 datetime与timestamp的区别

步骤1：使用create table语句在choose数据库中创建today表，执行结果如图。

use choose;

create table today(

t1 datetime,

t2 timestamp

);

 

 

 

步骤2:下面的两条insert语句负责向today表中插入两条记录，执行结果如图。

insert into today values(now(),now());

insert into today values(null,null);

 

 

 

步骤3：在下面MySQL代码中，首先查看当前MySQL服务实例的时区；然后使用select语句查询today表的所有记录；接着使用“set time_zone='+12:00';”命令“临时地”将时区设置为新西兰时区，即东12区（+12:00）；再次查看当前MySQL服务实例的时区；最后使用select语句再次查询today表的所有记录，执行结果如图。

show variables like 'time_zone';

select *from today;

set time_zone='+12:00';

show variables like 'time_zone';

select *from today;

 

 

 

从执行结果可以看出，在datetime字段中插入NULL值后，该字段的值就是NULL的值；在timestamp字段中插入NULL值后，该字段的值就是当前MySQL服务器当前日期。时区修改前后，t1没变，t2增加了4h。

也就是说，datetime字段的值不受时区的影响，而timestamp的值受到时区的影响。

当向now()函数或者curtime()函数传递一个整数值，并把它作为函数的参数时，可以得到MySQL服务器当前更精确的时间。

 

 5.MySQL复合类型

 

enum类型的字段只允许从一个集合中取得一个值，set类型的字段允许从一个集合中取得多个值。

例：

use choose;

create table person(

sex enum('男','女'),

interest set('听音乐','看电影','购物','旅游','游泳','游戏')

);

insert into person values('男','看电影,游泳,听音乐');

select *from person;

 

 

 

6.MySQL二进制类型

 

 二、创建表

1、设置约束

1.1主键（primary key）约束

（1）如果一个表的主键是单个字段，直接在该字段的数据类型或者其他约束条件后加上“primary key”关键字，即可将该字段设置为主键约束。

字段名 数据类型[其他约束条件]primary key

例：

student_no char(11) primary key

（2）如果一个表的主键是多个字段的组合，定义所有字段后，使用下面的语法规则将（字段名1，字段名2）设置为复合主键。

primary key(字段名1.字段名2）

例：

在choose数据库中创建nowadays表，并将（t1,t2）的字段组合设置为Nowadays表的主键。

use choose;

create table nowadays(

t1 datetime,

t2 timestamp,

primary key(t1,t2)

);

 

查看表中所有约束条件，数据库与表之间使用“.“隔开。

select constraint_name,constraint_type

from information_schema.table_constraints

where table_schema='choose' and table_name='nowadays';

 

 

查看nowadays表的索引信息

show index from nowadays\G

 

1.2.设置非空（not NULL）约束

字段名 数据类型 not null

例如，将学生student表的student_name字段设置为非空约束。

student_name char (10) not null

1.3.设置默认值（default）约束

字段名 数据类[其他约束条件] default 默认值

例如，将课程course表的up_limit字段设置默认值约束，且默认值为整数60.

up_limit int default 60

例如，将课程course表的status字段设置默认值约束，且默认值为‘未审核’

status char  (6) default '未审核'

1.4.设置唯一性（unique）约束

字段名 数据类型 unique

例如，将班级classes 表的班级名class_name字段设置为非空约束以及唯一性约束。

class_name char(20) not null unique

或者

class_name char(20)  unique not null

1.5.设置外键（foreign key）约束

外键约束主要用于定义表与表之间的某种关系

constraint 约束名 foreign key （表A字段名或字段名列表）references 表B（字段名或字段名列表）[on delete 级联选项][on update级联选项]

级联选项有四种：分别是cascade、set null、no action、restrict

例如、将学生student表的class_no字段设置为外键，该字段的值参照（reference）班级classes表的class_no字段的取值，可以在学生student表的create table语句中使用下面的SQL代码片段（其中student_class_fk为外键约束名，fk后缀为foreign key 的缩写）

constraint student_class_fk foreign key(class_no) references classes(class_no)

2.设置自增型字段

字段名 数据类型 auto_increment

例如：将班级classes表的class_no字段设置为主键，并设置为自增型字段，可以使用下面的SQL片段。

class_no int auto_increment primary key

(自增型字段的数据类型必须为整数）

建议将自增型字段设置为主键，否则创建数据表将会失败。

3.创建“选课系统”数据库表

use choose;

//表teacher

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 =gbk;

//表classes

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=gbk;

/*

可能会报错ERROR 1300 (HY000): Invalid utf8 character string: '\xA1\xA1\xA1\xA1'

输入set names gbk;

之后就可以继续打下去

*/

//表course

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,　　#课程的描述信息为文本字符串text，且不能为空

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)

)engine=InnoDB default charset=gbk;

//表student

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=gbk;

//表choose

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=gbk;

注：建表的时候注释最好去掉，可能会报错。

4.复制一个表结构

法一：

在create table语句末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。

create table 新表名

like 源表

例如：将today表的表结构拷贝到新表today1中，可以使用下面的create table语句，执行结果如图。

use choose;

create table today1 like today;

show create table today1;

select *from today1;

法二：

在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。

create table 新表名 select*from 源表

例如：将today表的表结构拷贝到新表today2中，可以使用下面的create table语句，执行结果如图。

use choose;

create table today2 select*from today;

show create table today2;

select *from today2;

 

修改删除索引（等我考完试再来。。。）

 

未完待续~