Fork me on GitHub

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;

 

修改删除索引(等我考完试再来。。。)

 

未完待续~

posted @ 2018-09-25 19:28  🍀🍀🍀  阅读(700)  评论(0编辑  收藏  举报
🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀 🍀