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;
修改删除索引(等我考完试再来。。。)
未完待续~
🍀取乎其上,得乎其中;取乎其中,得乎其下;取乎其下,则无所得矣🍀
🍀每天都要有收获 (*/ω\*)🍀
🍀后悔是没有用的,从现在开始,立刻马上去做(≧∀≦)ゞ🍀