SQL基础入门:第3课:创建表及常见的数据类型

本节要点

再次从EXCEL表格说起

创建表

常见的数据类型

再次从EXCEL表格说起

因为创建表相关的知识点还是比较多的,所以关于创建表,我打算分两节课来讲。本节课主要讲解创建表的基本语法以及表中字段的常见数据类型。

在讲解创建表的语法之前,我们先再次从上节课的EXCEL表格说起。不知大家还记不记得上节课讲到的深圳市第一中学的EXCEL,里面有三个SHEET页,分别是学校、老师、学生。

在EXCEL文件与数据库的对比中,EXCEL就相当于一个数据库,而一个SHEET页,就相当于一个数据库表,SHEET页里的表头就对应于数据库表的列。

在上节课中,我们使用MySQL-Front客户端工具创建了一个mysqlstudy的数据库,并且在数据库里创建了一张老师表(teacher)。这是创建数据库和表的一种方式。

还有另外一种方式来创建表,就是我们本节课要讲解的SQL语句。

创建表

使用SQL语句创建表,需要使用数据库的关键字CREATE TABLE。关键字也叫保留字,是不能随意使用的,不能作为对象名,比如不能作为表名、字段名等。

创建一个SHEET页时,我们需要指定SHEET页的名称、SHEET页里表格的表头,以及每一列需要存储的数字格式。

创建一张表也是类似的,我们需要指定表名、字段名、字段的数据类型。比如下面这个语句创建了一张老师表(表名为teacher),它包含三个字段,分别是teacher_id、teacher_name、gender,字段类型分别为varchar(20)、varchar(50)、varchar(10)。

CREATETABLEteacher

(

teacher_idVARCHAR(20),

teacher_nameVARCHAR(50),

genderVARCHAR(10)

);

这里面有一些需要注意的地方,大家一定要记住。

表名和字段名不能使用中文(标点符号也不能使用中文的);(一般为字母开头,字母、数字、下划线组成的字符串)

CREATE TABLE关键字后跟上表名,字段名在括号中列出且以逗号分隔;

语句最后以分号结尾;

不区分大小写,但最好关键字都大写;按一定的规则缩进;

每个字段都需要设置数据类型;

对于第1条,常见的表名命名方式有:t_teacher、T_Teacher、tech。千万不要使用一些稀奇古怪的字符作为表名。

上面有讲到,每个字段都需要设置数据类型。那什么是数据类型呢?他有什么作用呢?

常见的数据类型

数据类型,其实就是数据在数据库里保存的格式,它决定了对应的字段可以存储什么格式的数据,以及如何存储这些数据。

常见的数据类型有以下几类:

字符串类

数值类

日期和时间类

二进制类

字符串类

字符串类,是最常见的数据类型。基本可以说所有我们看到的符号,都是字符串。所以,如果说没有特殊要求,所有字段都设置为字符串,也是没问题的。

举一些例子,比如常见的学生的姓名、身份证、家庭地址、电话号码,使用的电脑型号,以及购买的书名等。这些都是字符串。

而字符串,一般来说,又分为两种。一种是定长字符串,另一种是不定长字符串。

定长字符串:长度固定的字符串类型,其长度是在创建表时指定的。定义时使用关键字CHAR。

比如邮政编码‘510623’,是6位的字符。如果向一个6位的定长字符串类型的字段,只插入2位字符,则数据库默认会在2个字符末尾补4个空格,以补足6位。

不定长字符串:长度不固定的字符串类型,创建表时指定一个最大长度,或不指定长度。定义时使用关键字VARCHAR。

比如姓名,有2位、3位、4位甚至更长的。比如:‘冯巩’、‘周杰伦’、‘仓央嘉措’、‘库尔班-热合曼’。

关于定长字符串和不定长字符串的区别,看下下面两张图,可能会更直观一些。

定长字符串就像左边的胶全板一样,每一张的大小尺寸都一样(不一样的话,会使用木屑等补足),搬运起来也比较方便。而不定长字符串,就像右边的树杆原木,长度粗细比较随意,堆叠在一起来,就会产生很多缝隙,也不太好搬运。

所以,定长字符串具有处理性能高、产生较少的磁盘存储碎片的优点,但占用的存储可能会多些。而不定长字符串,占用的存储较少,但处理性能低,会产生较多的存储碎片。

在创建表时,具体使用哪种类型,还要看具体场景而定。不过随着数据库发展,存储碎片的产生,对数据库的性能影响也越来越小,所以使用VARCHAR也影响不大。

数值类

数值类数据类型也是一种非常常见的数据类型。在生活中也非常常见,基本可以说,所有能够度量的数值,都可以使用数值类数据类型来储存。比如说,像年龄、身高、厚度、速度、利率、人数等,都可以理解为数值。

而数值数据类型又分为两种。

整数数值:不带小数点的数值。比如说,中国有4个直辖市;2016年深圳市常住人口有11988400人;或者说某人今年的年龄是80岁等待。常见的保存整数数值的字段类型有SMALLINT、INT、BIGINT、DECIMAL(m)。

小数数值:带小数点的数值。比如说,珠穆朗玛峰的高度为8844.43米;银行一年期定期存款利率为2.75%等。常见的保存小数数值的字段类型有FLOAT、DOUBLE、DECIMAL(m,d)。

日期和时间类

第三种是日期和时间类型,比如学校要开学了,规定的报名日期9月1日;我们国家的国庆节10月1日;公司的下班时间17点30分;沿着操场跑一圈,使用秒表记录的时间4分13秒;爬山时,从开始上山到最后下山中间经过的时间间隔5小时25分钟等,

而日期和时间类的数据类型,分为三种。

日期类型:只包含年、月、日的日期;比如,某人的出生日期为1990年1月3日;定义时使用关键字DATE。

时间类型:包含时、分、秒、毫秒的时间。比如,上班时间为08:30:00。定义时使用关键字TIME。

日期+时间类型:包含年、月、日、时、分、秒、毫秒的时间戳类型。比如,某人从银行ATM机上取出1000元现金的时间是2017年9月2日12时35分11秒734毫秒。定义时使用关键字DATETIME、TIMESTAMP。

二进制数据类型

最后简单说一下二进制数据类型。二进制我们都知道,是0和1组成的一串数字。像我们常见的图片、视频、音频、文档等,底层都是使用二进制保存的。所以打开这类文件时,才需要图片浏览软件、播放器、OFFICE软件等。

有时候,我们也想把这类数据存入数据库,这时就需要使用到二进制数据类型。在MySQL数据库,一般使用BLOB和TEXT类型来定义二进制数据类型的字段。

但因为数据库处理这类数据效率并不高,而且数据量一大,会极大的影响数据库的处理效率,所以一般都不建议在数据库中使用这类数据类型。

设置字段默认值

上面我们讲了,在创建一张表时,需要为表中的字段定义数据类型。这样在向表中插入数据时,就需要插入与对应位置字段的字段类型一致的数据,否则是会报错的。

但当我们插入数据的时候,并不想或并不需要对某个字段赋值时,如果字段有设置默认值,则会以默认值来填充该字段的值而不会导致错误;比如,在网上下订单时,对于下订单的日期字段,可以使用数据库的系统变量“当前日期”来默认赋值,这样就不需要在向订单表插入订单数据时,单独再对下订单的日期字段进行赋值。

另外,在向表中插入数据的时候,如果某个字段并没有指定赋值为多少,而此时该字段也没有赋默认值,那么就会向该字段插入NULL值。而NULL值的使用效率并不高,并且使用起来也比较麻烦。

所以,为避免字段的值被插入NULL值,一般在表创建时,都会为字段指定默认值。

为字段指定默认值时,使用关键字DEFAULT。如下所示:

CREATETABLEteacher

(

teacher_idVARCHAR(20)DEFAULT'',

teacher_nameVARCHAR(50)DEFAULT'',

genderVARCHAR(10)DEFAULT''

);

设置默认值时,有以下几点需要注意:

表中所有的字段都可以设置默认值;

默认值的数据类型必须与字段保持一致;(不一致时,数据库虽然有时也会默认做隐式类型转换,但并不推荐这样做。一方面是这样做并不规范,另一方面是这样做可能会导致未知的错误)

除主键字段外,建议其他所有字段都设置默认值;

那一般根据什么来决定字段应该赋什么默认值呢?

对于字符型字段来说,一般赋值为空字符串(也就是’’),或者具有某种业务含义的值。比如,对于性别字段,有三个取值,F-女,M-男,N-未知,当向这个性别字段插入数据时,可能并不知道性别是男还是女,那这时,就可以定义默认值为N-未知,代表我们并不知道这条数据记录的值到底是多少。

对于数值型字段来说,一般赋值为0,或者具有某种业务含义的值,或者该字段所表示的业务含义的最大值或极大值。

对于日期和时间型呢,常见的赋值有系统当前日期CURRENT_DATE、系统当前时间CURRENT_TIME、系统当前时间戳CURRENT_TIMESTAMP及NOW()等。

总的来说,我们都可以根据字段的业务含义来为字段赋默认值。

下面是创建老师表、班级表、学生表的示例语句:

CREATETABLEteacher

(

teacher_idvarchar(50)notnull,

teacher_namevarchar(100)notnulldefault'',

gendervarchar(10)notnulldefault'F',

);

CREATETABLEclass

(

class_idvarchar(50)notnull,

class_namevarchar(100)notnulldefault''

);

CREATETABLEstudent

(

student_idvarchar(50)notnull,

student_namevarchar(100)notnulldefault'',

gendervarchar(10)notnulldefault'',

ageintegernotnulldefault,

class_idvarchar(50)notnulldefault''

);

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181203G1KJL000?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券