目录

1、创建表的完整操作

2、mysql中的数据类型

3、约束条件

1、创建表的完整操作

创建表的完整语法：

    create table 表名(

        id int primary key auto_increment,

        字段1 类型[(宽度) 约束条件],

        字段1 类型[(宽度) 约束条件],

        字段1 类型[(宽度) 约束条件],

        ...

    );

    注意问题：

        1、字段名不能重复

        2、字段的类型必须要有，但是宽度与约束条件可选

        3、最后一个字段后不能加逗号

2、mysql中的数据类型

为什么要给数据分类?

1.描述数据更加准确

2.节省内存空间

1.数值类型

整形

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：存储年龄，等级，id，各种号码等

常用的整形类型介绍:

        tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：

            有符号：

                -128 ～ 127

            无符号：

                      0～ 255

            PS： MySQL中无布尔值，使用tinyint(1)构造。

========================================

        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：

            有符号：

                    -2147483648 ～ 2147483647

            无符号：

                                  0 ～ 4294967295

========================================

        bigint[(m)][unsigned][zerofill]

            大整数，数据类型用于保存一些范围的整数数值范围：

            有符号：

                    -9223372036854775808 ～ 9223372036854775807

            无符号：

                               0～  18446744073709551615

整形的使用:

默认的整形在创造出来时都是有符号的

create table t1(x tinyint);

create table t2(x tinyint unsigned);   在创建的时候可以指定整形的类型和约束条件

create table t5(x int(8) unsigned zerofill);  显示时，不够8位用0填充，如果超出8位则正常显示

整形类型的介绍:以int为例

int的存储宽度是4个Bytes，即32个bit，即2**32

无符号最大值为：4294967296-1

有符号最大值：2147483648-1

有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的

最后：整形类型，其实没有必要指定显示宽度，使用默认的就可以

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关,其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了,默认的显示宽度，都是在最大值的基础上加1

整形的存储范围详细说明:

有符号和无符号的区别,有符号的需要占用一个比特位来表示正负

浮点型

浮点型共有三种类型:float、double、decimal

create table t1(x float(255,30)); 

create table t1(x double(255,30)); 

create table t1(x decimal(65,30)); 

float与double能支持的十进制数的总位数都是255，其中小数都可以达到30位，但double类型的精度比float更高

decimal能支持的十进制数的总位数是65，其中小数都可以达到30位，但ddecimal类型的精度比float和double更高，

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

M 表示总长度  D 表示小数部分的长度

长度表示不是数据存储范围 而是字符长度

例如:10.12 总长为4  小数部分为2

定义：

        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号范围：

           -3.402823466E+38 to -1.175494351E-38,

           1.175494351E-38 to 3.402823466E+38

无符号范围：

           1.175494351E-38 to 3.402823466E+38

精确度： 

           **** 随着小数的增多，精度变得不准确 ****

适用于大部分场景

======================================

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

定义：

           双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号范围：

           -1.7976931348623157E+308 to -2.2250738585072014E-308

           2.2250738585072014E-308 to 1.7976931348623157E+308

无符号范围：

           2.2250738585072014E-308 to 1.7976931348623157E+308

精确度：

           ****随着小数的增多，精度比float要高，但也会变得不准确 ****

适用于计算要求精度较高的场景

======================================

decimal[(m[,d])] [unsigned] [zerofill]

定义：

          准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

精确度：

           **** 随着小数的增多，精度始终准确 ****

           对于精确数值计算时需要用此类型

           decaimal能够存储精确值的原因在于其内部按照字符串存储。

适用于科学计算和银行的资金管理系统中

浮点型验证

mysql> create table t1(x float(255,30)); #建表成功

Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(x double(255,30)); #建表成功

Query OK, 0 rows affected (0.02 sec)

mysql> create table t3(x decimal(65,30)); #建表成功

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

+---------------+

| Tables_in_db1 |

+---------------+

| t1            |

| t2            |

| t3            |

+---------------+

rows in set (0.00 sec)

mysql> insert into t1 values(1.1111111111111111111111111111111); #小数点后31个1

Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(1.1111111111111111111111111111111);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(1.1111111111111111111111111111111);

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t1; #随着小数的增多，精度开始不准确

+----------------------------------+

| x                                |

+----------------------------------+

| 1.111111164093017600000000000000 |

+----------------------------------+

row in set (0.00 sec)

mysql> select * from t2; #精度比float要准确点，但随着小数的增多，同样变得不准确

+----------------------------------+

| x                                |

+----------------------------------+

| 1.111111111111111200000000000000 |

+----------------------------------+

row in set (0.00 sec)

mysql> select * from t3; #精度始终准确,d为30，于是只留了30位小数

+----------------------------------+

| x                                |

+----------------------------------+

| 1.111111111111111111111111111111 |

+----------------------------------+

row in set (0.00 sec)

小结:

相同点

1、对于三者来说，都能存放30位小数，

不同点：

1、精度的排序从低到高：float,double,decimal

2、float与double类型能存放的整数位比decimal更多

3.字符串类型

char、varchar

创建字符串类型:(字符串在创建时varchar必须制定字符长度,char有默认长度)

create table t1(name char(6))

create table t1(name varchar(6))

注意：char和varchar括号内的参数指的都是存储的最大字符长度,

char类型：定长，简单粗暴，浪费空间，存取速度快

    字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）

    存储：

        存储char类型的值时，会往右填充空格来满足长度

        例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

    检索：

        在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）

varchar类型：变长，精准，节省空间，存取速度慢

    字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8

    存储：

        varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来

        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）

        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）

        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）

    检索：

        尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

字符串类型的mysql详解: https://dev.mysql.com/doc/refman/5.7/en/char.html

length：查看字节数

char_length:查看字符数

字符串类型在查询时的特殊之处:

1. char填充空格来满足固定长度，但是在查询时却会自动的删除尾部的空格（装作自己好像没有浪费过空间一样），但是可以通过修改sql_mode让其现出原形,在读出结果的时候自动去掉末尾的空格,因为末尾的空格在以下的场景中是无用的:

select * from t14 where name="lxx"; # name字段明确地等于一个值，该值后填充空格是没有用

select * from t14 where name like "lxx"; # name字段模糊匹配一个值，该值后填充空格是有用的

2. 虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比 较其值,忽略 CHAR 值存在的右填充,即使将 SQL _MODE 设置为 PAD_CHAR_TO_FULL_ LENGTH 也一样,,但这不适用于like,like匹配是一种匹配方式,在小节末尾  我们会介绍like和=的异同.

SQL语句中like和=的区别(点击前往)

SQL语句中的模糊匹配(点击前往)

4.集合和枚举类型

字段的值只能在给定范围中选择，如单选框，多选框

enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female

set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

示例:

mysql> create table consumer( 

    -> name varchar(50),

    -> sex enum('male','female'),

    -> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一

    -> hobby set('play','music','read','study') #在指定范围内，多选多

    -> );

mysql> insert into consumer values  

    -> ('egon','male','vip5','read,study'),

    -> ('alex','female','vip1','girl');

mysql> select * from consumer;

+------+--------+-------+------------+

| name | sex    | level | hobby      |

+------+--------+-------+------------+

| egon | male   | vip5  | read,study |

| alex | female | vip1  |            |

+------+--------+-------+------------+

验证

5.日期类型

DATE TIME DATETIME TIMESTAMP YEAR 

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

日期类型介绍:()中为范围

YEAR

            YYYY（1901/2155）

DATE

            YYYY-MM-DD（1000-01-01/9999-12-31）

TIME

            HH:MM:SS（'-838:59:59'/'838:59:59'）

DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

日期类型的使用

create table student(

    id int,

    name char(16),

    born_year year,

    birth date,

    class_time time,

    reg_time datetime

);

insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');

注意:

1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入

2. 插入年份时，尽量使用4位值

3. 插入两位年份时，<=69，以20开头，比如50,  结果2050     

                >=70，以19开头，比如71，结果1971

日期类型中datetime和timestamp的区别

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。

1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。

2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

datetime与timestamp的区别

3、约束条件

约束条件是一种对数据限制

约束的作用是?

    为了保证数据的正确性,完整性

    例如要存储密码   char(20)   只限制了类型和长度,   无法保证数据的正确性

额外的约束

语法:

    创建时指定约束

    create table 表名称(字段名 类型(长度) 约束名称1 约束名称n,....)

    后期修改的方式添加 约束

    alter table 表名称 modify 字段名 类型(长度) 约束名称1 约束名称n,....

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

mysql中约束条件主要分为以下几种:

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录

FOREIGN KEY (FK)    标识该字段为该表的外键

NOT NULL    标识该字段不能为空

UNIQUE KEY (UK)    标识该字段的值是唯一的

AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT    为该字段设置默认值

UNSIGNED 无符号

ZEROFILL 使用0填充

1. 是否允许为空，默认允许为NULL，可设置NOT NULL，字段不允许为空，必须赋值

2. 字段是否有默认值，初始的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male'

age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20

3. 是否是key

主键 primary key

外键 foreign key

索引 (index,unique...)

接下来对约束条件进行详细的介绍

NOT NULL和DEFAULT(默认值约束)

NOT NULL(非空约束)

限制该字段的值不能为空

是否可空，null表示空，非字符串

not null - 不可空

null - 可空

DEFAULT(默认值约束)

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

create table tb1(

id int not null defalut 2,

num int not null

)

验证:

==================not null====================

mysql> create table t1(id int); #id字段默认可以插入空

mysql> desc t1;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id    | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

mysql> insert into t1 values(); #可以插入空

mysql> create table t2(id int not null); #设置字段id不为空

mysql> desc t2;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id    | int(11) | NO   |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

mysql> insert into t2 values(); #不能插入空

ERROR 1364 (HY000): Field 'id' doesn't have a default value

==================default====================

#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

mysql> create table t3(id int default 1);

mysql> alter table t3 modify id int not null default 1;

UNIQUE(唯一性约束)

限制该字段的值是唯一的,不能出现重复

分为单列唯一和联合唯一(联合唯一不推荐使用)

单列唯一:

只限制这一个字段,保证该字段中的数据类型是具有唯一性的

create table t16(

    id int unique,

    name char(16)

);

设置单列唯一的第二种办法(了解)

create table department2(

id int,

name varchar(20),

comment varchar(100),

constraint uk_name unique(name)

);

联合唯一:

联合唯一表示两个或多个字段合起来是唯一的,意思是他们的组合形式是具有唯一性的,但不代表单列不会出现相同的值

create table server(

    id int unique,

    ip char(15),

    port int,

    unique(ip,port)

);

我们在这里可以思考一下, 如果not null unique一起使用回出现什么样的效果(单列情况下)?

create table t1(id int not null unique);     他们一起使用就会使得该字段的值不为空且唯一

PRIMARY KEY(很重要!!!!)

首先简单了解一下key:key是mysql中一种特殊的数据结构中,是用来加速查询的

mysql中存在一种专门的数据结构，叫key，又称为索引，通过该数据结构可以减少io次数，从而加速查询效率

我们目前为止已经学过的key类型:

    index key ： 只有加速查询的效果，没有约束的功能

    unique key：不仅有加速查询的效果，还附加了一种约束的功能

    primary key：不仅有加速查询的效果，还附加了一种约束的功能，并且innodb存储引擎会按照主键字段的值来组织表中所有的数据，所                            以一种inndob表中必须有、而且只能有一个主键，通常是该表的id字段

primary key:单单从约束角度去看，primary key就等同于not null unique,但是他们是有区别的:

UNIQUE NOT NULL 与 主键的区别

    UNIQUE NOT NULL 不能被其他表引用 (不能作为其它表的外键)

    UNIQUE NOT NULL 约束一个表中可以有多个   但是主键只能有一个

强调（******）

1、一张表中必须有，并且只能有一个主键

2、一张表中都应该有一个id字段，而且应该把id字段做成主键

create table t17(

    id int primary key,

    name char(16),

    age int,

    sex char(6)

)engine=innodb;

当我们建立一张表时,mysql会首先看创建的字段中有没有那个字段被指定为主键,如果有就直接使用,如果没有回从上到下依次寻找一个不为空且唯一的一个字段作为主键的替代品作为主键,如果没有不为空且唯一的主键,就会创建一个隐藏的字段作为主键,但是隐藏字段不可见,所以就是去了加速查询的效果,也就失去了存储引擎的很大的优势,所以,在创建表格的时候一定要指定一个字段作为主键,一般表格中都会有id字段,我们习惯将id字段作为主键

联合主键(不推荐使用)

create table t19(

    ip char(15),

    port int,

    primary key(ip,port)

);

AUTO_INCREMENT(自增长)

该约束条件,会有一个初始值,并且在向表中添加字段的时候会自动增长(初始步长为1),在使用的时候可以不指定也可以输入空,

通常auto_increment和primary一起使用

primary key auto_increment

create table t20(

    id int primary key auto_increment,

    name char(16)

)engine=innodb;

auto_increment注意点：

1、通常与primary key连用，而且通常是给id字段加

2、auto_incremnt只能给被定义成key（unique key，primary key）的字段加

---------------------