Mysql 建表语句

连接数据库

mysql -h地址 -P端口 -uroot -p密码

创建数据库

create database namedb; -- 创建数据库

create database namedb charset utf8; -- 创建数据库，并指定字符集

show databases; -- 查看所有数据库

use dbname; -- 使用数据库

drop database namedb; -- 删除数据库

show create database namedb; -- 查看数据字符集

创建表的过程就是声明列的过程

1. 下面创建 t1 表

2. 声明 sn、name 两个列

create database student; -- 创建数据库student

use student; -- 选择使用student库

create table t1( -- 创建t1表，声明 sn、name 两个列
  sn int,
  name varchar(10)
);

show create table t1; -- 查看t1数据表的编码

show tables; -- 显示库下所有数据表

desc t1\G -- 查看表结构
desc t1;

建表语句

CREATE TABLE IF NOT EXISTS tal_name(

列名1 类型 [列属性] [默认值],

列名2 类型 [列属性] [默认值],

....

列n 类型 [列属性] [默认值]

) engine=储存引擎 default charset=字符集;

字段类型分类

1.数值型（存数字用的）：

整型

浮点型

定点型

2.字符串型：

char

varchar

text

blob（blob系列把字符串当二进制）

3.日期时间类型：

2012-12-13

14:26:23

列（字段）选什么样的类型，给什么样的属性？

下面学习列的类型与其属性的相关知识

一、整型

整型是存储数值的，

上面建的 t1 表，sn 列用的 Int 类型，往里面存一个数字1，它将在磁盘上占据 4 个字节，占四个字节什么意思呢？

desc t1; -- 打印表结构

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

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

| sn | int(11) | YES | | NULL | | sn设置的是int类型

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

插入一行数据

insert into t1 values(1, 'ZhangSan');

select * from t1;

就是下面这个数值 1 ，在磁盘上占了四个字节，怎么来理解？

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

| sn | name |

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

| 1 | ZhangSan|

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

我们知道一个字节有八个位 00000001 每个位上有0或1，

四个字节存 1 是下面这样存的

00000000 00000000 00000000 00000001

一共4个字节它就在最低位上存了一个1，这是相对浪费的，事实上这四个字节最大能存到多少呢？

要是可着劲的存 32 个位全是 1，可以存到 0 到 2^32-1（0 到 2的三十二次方减1）的范围内

11111111 11111111 11111111 11111111

如果全是0就是0，

如果全是1，是2的三十二次方减1，大概是四十多亿，

没必要记具体的数字，只需要直观的这么来理解，一个列的占字节越多，它能存的范围越大，它也越浪费

Ps：

KB（即K）、MB（即M）、GB 等都是基于字节换算的存储单位，三者的换算为

1KB = 1024B

1MB = 1024KB

1GB = 1024MB

1、整形的类型

选哪一类的int类型，根据实际情况，

比如存每班的学号、年龄，Tinyint 类型就够用了，不考虑正负号年龄能存到 255

类型	字节	最小值（带符号的/无符号的）	最大值（带符号的/无符号的）
Bigint	占8个字节		天文数字
		0	天文数字
Int	占4个字节	-2147483648	2147483647
		0	4294967295
Mediumint	占3个字节	-8388608	8388607
		0	16777215
Smalint	占2个字节	-32768	32767
		0	65535
Tinyint	占1个字节	-128	127
		0	255（存学号、年龄）

2、整型列的可选参数

unsigned 无符号的意思

zerofill 适合用于学号、编码固定宽度的数字。zero是零的意思、fill填充的意思，用0填充至固定宽度，比如序号 0001、0002、0003、0004...

M 代表宽度

tinyint 类型的存储范围，有可能是0到255，也有可能是-128到127，怎么有两种可能呢？

创建num列，设置1个字节的tinyint类型

create table t2(
  num tinyint  -- num列是占一个字节的tinyint类型 
);

可能有两种范围，如果范围是0到255，插入255提示警告 warning 信息，Query OK, 1 row affected, 1 warning (0.00 sec)

insert into t2 values(255);

select * from t2;

插入255数据库中显示的是127

+------+

| num |

+------+

| 127 |

+------+

再插入128试一试，还是提示警告信息 Query OK, 1 row affected, 1 warning (0.00 sec)

insert into t2 values(128);

select * from t2;

插入128的结果还是127

+------+

| num |

+------+

| 127 |

+------+

这次插入127，返回的提示信息是正常的 Query OK, 1 row affected (0.00 sec)

insert into t2 values(127);

select * from t2;

插入的是127，数据表中显示是127

+------+

| num |

+------+

| 127 |

+------+

再插入负数-128，返回的信息正常 Query OK, 1 row affected (0.00 sec)

insert into t2 values(-128);

select * from t2;

插入-128成功

+------+

| num |

+------+

| 127 |

| -128 |

+------+

测试结果说明，

默认的 tinyint 类型是带正负号的，所以默认存储范围是-128到127之间，

如果存的是一个人的年龄，年龄不能为负只需要存正数，如何做到这个列只存正数呢？

很简单，列有一些可修饰的属性可以选择，接下来看 unsigned 属性。

unsigned 属性是无符号的意思，用 unsigned 属性修饰的列，列的值从0开始不为负数（0 - 255之间）

修改一下t2表，给它增加一个unum列，依然是tinyint类型，只不过加了unsigned属性修饰

Alter table t2 add unum tinyint unsigned;  -- 增加一个列

然后往unum列插入数字255

insert into t2 values (5, 255);

select * from t2;

存储的就是255

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

| num | unum|

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

| 127 | NULL |

| -128 | NULL |

| 5 | 255 |

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

如果unum列插入负数 -1，报warning信息，Query OK, 1 row affected, 1 warning (0.00 sec)

insert into t2 values (6, -1);

select * from t2;

插入-1不成功，存储的是0

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

| num |unum |

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

| 127 | NULL |

| -128 | NULL |

| 5 | 255 |

| 6 | 0 |

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

unsigned属性的意义就是无符号，列的值从0开始不可能为负数，

有些列不可能为负数，比如身高、年龄等，这些列用unsigned属性修饰

zerofill 和 M的意思

zero 的字面的意思是0，fill 的意思是填充

zerofill 属性适合用于序号、编码，固定宽度的数字，可以用0填充至固定宽度

比如序号是:

1填充成-> 0001

2填充成-> 0002

......

123填充成-> 0132

首先是0填充，然后要注意填充至多宽

zerofill 填充至多宽呢？

填充至多宽也是我们自己设置，参数 M 代表的就是宽度的意思

再增加一个学号sn列，还是tinyint类型，zerofill属性是用0填充，填充多宽在 tinyint(5) 小括号内的5是填充0的宽度（5或6都可以随便）

Alter table t2 add sn tinyint(5) zerofill; -- 增加一个学号列

往序号sn列分别插入 9, 23, 123

insert into t2 values (7, 7, 9);
insert into t2 values (8, 8, 23);
insert into t2 values (9, 9, 123);

select * from t2;

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

| num |unum | sn |

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

| 127 | NULL | NULL |

| -128 | NULL | NULL |

| 5 | 255 | NULL |

| 6 | 0 | NULL |

| 9 | 9 | 00123 |

| 8 | 8 | 00023 |

| 7 | 7 | 00009 |

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

想一个问题

生活中遇到这种用0填充的数字会是负数吗？生活中的编号不会是负数的，查看一下t2这张表

desc t2;

新增的sn列有一个 unsigned 属性，建表的时候没有设置 unsigned 属性，注意设置 zerofill 属性就已经决定了，该列默认为 unsigned 类型

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

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

| num | tinyint(4) | YES | | NULL | |

| sn | tinyint(5) unsigned zerofill | YES | | NULL | |

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

单独一个 M 没有任何意义，它必须和 zerofill 属性配合起来才有意义，

总有一个误区，比如就想存 0 - 9 之间的数字，增加一个 sx 列还是 tinyint 类型，M 既然代表宽度，tinyint(1) 写一个1就代表0到9了

Alter table t2 add sx tinyint(1);

然后插入数字 11, 111

insert into t2 (sx)values (11),(111);

select * from t2;

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

| num | unum| sn | sx |

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

| 5 | 255 | NULL | NULL |

| 6 | 0 | NULL | NULL |

| 7 | 7 | 00009 | NULL |

| 8 | 8 | 00023 | NULL |

| 9 | 9 | 00123 | NULL |

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

M的翻译是"宽度"确实让人迷惑，M 属性的宽度限制的不是范围，是指用 0 填充时候填的宽度，如果不用 0 填充，M 参数完全可以不写，因为它没有任何意义，M 是配合 zerofill 填充宽度时候用的，单用写一个 M 是没有意义的。

二、浮点型与定点型

浮点型有 float 和 double，定点类型是 decimal，这三个类型说白了是放小数用的

1. float 单精度浮点数

2. double 双精度浮点数

3. decimal 定点类型

1、float

float 称之为小浮点数（单精度），它能存的范围小一点，说小也比 int 大多了

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

float 类型也有 M 参数、也有 unsigned 属性和 zerofill 属性，重点关注一下 FLOAT(M, D)，M、D两个参数是干什么的？

上面 tinyint 类型的时候，M 参数必须和 zerofill 属性一起使用 M 才能起作用，

float 类型就不一样了，float 类型的参数 M 和 D，和 unsingned、zerofill 这两个属性无关，设置了立即就能起作用

什么环境适合用浮点数呢？

比如工资（薪水salary）

创建 salary 列用 float 类型，float(5, 2) 后面可选两个参数，如果不选值就在指定的范围内变化，这里可选参数是 5、2

create table t3( 
  salary float(5, 2) -- float类型可选参数是5、2
);

然后插入9999，提示警告信息，报warning警告，Query OK, 1 row affected, 1 warning (0.00 sec)

insert into t3 values (9999);

select * from t3;

显示的是999.99

+--------+

| salary |

+--------+

| 999.99 |

+--------+

试一试存999.99，插入成功

insert into t3 values (999.99); -- Query OK, 1 row affected (0.00 sec)

select * from t3;

数据表中显示999.99

+--------+

| salar |

+--------+

| 999.99 |

+--------+

float(M, D) 参数 M 和 D 的意思？

M 是精度，代表包含小数点的总位数，

D 是标度，代表小数点后面的位数

float(5, 2) 意味着总位数是 5 位，小数点后面是 2 位，小数点前面顶多能是 3 位，所以存"999.99"是没问题的，如果要想存1000（一千）那是不可以的，提示警告信息

insert into t3 values (1000); -- Query OK, 1 row affected, 1 warning (0.00 sec)

select * from t3;

存1000显示999.99

+--------+

| salar |

+--------+

| 999.99 |

+--------+

float(5, 2) 最小能存到多少少呢？最小能存到负的 -999.99

insert into t3 values (-999.99); -- 插入负的-999.99

select * from t3;

显示-999.99插入成功

+---------+

| salar |

+---------+

| 999.99 |

| -999.99 |

+---------+

存到 -1000 （负一千）是不可以的

insert into t3 values (-1000); -- Query OK, 1 row affected, 1 warning (0.00 sec)

select * from t3;

插入-1000（负一千）显示 -999.99

+---------+

| salar |

+---------+

| 999.99 |

| -999.99 |

+---------+

这是 float 浮点数参数 M 和 D 的意义，M 和 D 是实实在在影响浮点数的存储范围的

2、double

double 也同理，参数 M 和 D 在 double 里意义和 float 完全一样，

float 和 double 的区别在于范围上，double 的存储范围特别的大，可能连宇宙中的原子都能装下了

3、decimal

float，double 它两和 decimal 的区别

decimal 叫定点类型，什么是定点类型？

创建两个列

第一列 f 是 float(9, 2) 类型，

第二列 d 是 decimal(9, 2) 类型

create table t4( 
  f float(9, 2),
  d decimal(9, 2)
);

两个列都插入 1234567.23

insert into t4 values (1234567.23, 1234567.23);

select * from t4;

f 列显示的是1234567.25

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

| f | d |

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

|1234567.25| 1234567.23|

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

关于浮点的知识是比较难的部分，如果专门研究浮点数，美国有一个协会专门制定了浮点数的标准，

此处只需要知道浮点数是有精度损失的（在PHP中也说过），而 decimal 定点类型是把整数部分（1234567）和小数部分（23）分别存储的，所以定点数更精确

总结:

1. float 和 double 它俩的区别，是范围的区别

2. float、double 它俩和 decimal 比，浮点数有精度损失，decimal 是定点类型它更精确

3. float(M, D) 参数代表的意义，M 代表总位数，D 代表小数点后面的位数

三、字符类型

char(M) 定长类型，M 代表宽度，0<=M<=255，例：char(10) 输出10个字符

varchar(M) 变长类型，M 代表宽度，0<=M<=65535 ( 以 ascii 字符为例 65535，uft8 字符是 22000 左右 )

text 文本类型

blob

ENUM 举型

set 集合类型

字符类型我们常用有 char型、varchar型、text型、有时候也用枚举型，最长用也是面试最长考的是 char 型和 varchar 型

1、char 和 varchar

面试经常会被问，char 类型和 varchar 类型有什么区别？

char 类型叫做定长，

varchar 类型叫做变长，什么意思呢？

char(M) 类型

char(10) 如果 M 的宽度是 10，最多只能存 10 个字符，如果只插入 1 个字符，它在磁盘肯定还是占 10 个字符的宽度

mysql 寻找数据的时候会特别方便，只需要固定往后移 10 字符就到下一个了。

varchar(M) 类型

varchar(10) 比如 M 给 10 长度，最多也是能放 10 个字符，但是如果只插入1个字符，占用多少磁盘空间呢？

varchar 类型，有的行存 2 个字符长度，有的行存 3 个字符长度……，每一行存的字符长度都不一样，所以找到这一行的开头，找不到这一行的结尾。

找到一行的开始处，如何知道它的结尾处？

varchar 类型的列在每一行的开头处，会多于占一到二个字节，里面标注着每一行的长度，比如占用的5个字节，实际占用的长度要额外的再加1到2个字节，

查询这行的时候，找到行的开头处，先分析第一个字节，比如第一个字节是 3，就知道这行的宽度是 3 个字符长度，于是往后去找三个字符就结束了。

char 类型和 varchar 类型的区别：

char(10) 是定长，M 是10个宽度，无论存够不存够10个的宽度，都在磁盘上都占10个的宽度。

varchar(10) 用多少占多少，但是为了区分这次到底用了多少，前面额外增加一个标注说明性的字节。

类型	宽度	可存字符	实存字符(i<=M)	实占空间	利用率
Char	M	M	i	M	i/M <= 100%
Varchar	M	M	i	i字符+(1-2)字节	i/(i+1-2) < 100%

选用 char 还是 varchar？

如果存储的字符不是特别多，20 个字符以内都用 char，速度会有一个不错的提升。

char(5) 类型要是更小就是只存一个字符，利用率也是20%，一般存两到三个字符利用率在 60-80%，速度很不错因为"定长"寻址快。

varchar(5) 类型就算用了四个字符，它额外还要再加一个字节做说明字符，所以存的短就没必要用 varchar，除非存 100 个字符、200 字符用 varchar 类型。

一般开发中一般而言，M比较短 20 个以内、10 个以内直接用 char 型，浪费也浪费不了多少

char(10) 没存够 10 个字符也占 10 个字符的空间，他是怎么占的呢？

char如果不够M字符，内部用"空格"补齐，当取出来的时候再把右侧空格删掉

create table t5(
  n1 char(10), -- var类型
  n2 varchar(10) -- varchar类型
);

insert into t5 values(' hello ', ' hello '); -- 插入' hello '两边都有空格

select * from t5;

插入 " hello " hello两边都有空格，两个字段没看出什么不同

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

| n1 | n2 |

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

| hello | hello |

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

用 concat 函数查看

select concat('!', n1, '!'),  concat('!', n2, '!') from t5;

n1是定长的 char 类型，前面的空格还在，后面的空格不见了，

而 n2 列是 varchar 类型前后两边的空格还在，它就没有受到干扰，这是为什么呢？

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

| concat('!', n1, '!') | concat('!', n2, '!') |

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

| ! hello! | ! hello ! |

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

这就是 char 类型不足M个时，后面加空格补齐，取出的时候再把右侧空格去掉，这就意味着如果内容本身右侧有空格，将会被清除掉了

而 varchar 类型不会被删除，因为他有专门的字节来说明到哪里结束、有多少字符的长度，所以就不会丢失空格。

这是 char 和 varchar 不同之处，速度上 char 型定长更快一些。

注意一点

char(M) 和 varchar(M)，这个参数 M 限制的是字符不是字节

n1 列创建时候声明的 char(10)，比如数据表选用的字符集是utf8，那限制的就是utf8字符，n1列能存10个utf8字符，比如十个中文

-- start 先解决输入、输出中文乱码问题
set character_set_client=gbk;
set character_set_connection=utf8;
set character_set_results=gbk;
alter table t5 change n1 n1 varchar(10) character set utf8 collate utf8_general_ci; -- 修改n1列的字符集为utf8
alter table t5 change n2 n2 varchar(10) character set utf8 collate utf8_general_ci; -- 修改n2列的字符集为utf8
alter table t5 default character set utf8 collate utf8_general_ci; -- 修改数据表默认字符集
-- end

insert into t5 (n1) values ('生当做人杰死亦为鬼雄'); -- 插入十个中文字符

select * from t5;

十个中文字符都插入进去了

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

| n1 | n2 |

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

| hello | hello |

| 生当做人杰死亦为鬼雄 | NULL |

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

char 类型与 varchar 类型，选择的原则是空间利用率，比如四字成语用 char(4) 型，个人简介、微博 140 字用 varchar(140)

char 和 varchar 最大能存多少？

varchar 类型以前比较小，现在可以大到六万多范围是0 - 65535

char 类型稍微小一点范围是 0 - 255

2、text 文本类型

TEXT类型：65,535个字符

BLOB类型：与text相对应的blob类型是存大段的二进制

MEDIUMTEXT类型：16,777,215个字符

MEDIUMBLOB

text 类型能存的字符就更多了，text 有文本的意思，一大段文本

与 Text 相对应的还有一个 Blob，有一个 MEDIUMTEXT 就有一个 MEDIUMBLOB

先看 text 类型，还是存字符串，存大段文本可以选text

create table t6(
  tx text
);

insert into t6 value ('If You want Me');

select * from t6;

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

| tx |

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

| If You want Me |

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

3、blob类型

Text 与之相应的叫 blob，意思是存二进制的，

text 是存大段的文本，blob 是存大段的二进制，其实如果不是存图像 blob 几乎用不上，blob 用来存储图像、音频等二进制

blob 类型有什么好处呢？

如果用 text 字符类型存储，有utf8字符集、有gbk字符集、有ascii字符集，比如一张图片的二进制中有 0xFF 这样的字节，在 ascii 字符集里没有0xFF，超出范围认为是非法的，入库的时候信息被过滤掉一小部分，导致信息丢失

而二进制用 blob 类型，二进制就是0和1不用考虑字符集，所以blob类型使用上和text一样，区别是存储的时候blob不考虑字符集，存什么都行他不会过滤丢失。

4、ENUM 枚举类型

枚举类型是定义好的值，就在某几个枚举范围内，比如性别特别适合用枚举类型，限制只可能是男或女

create table t7(
  gender enum('男', '女')
)default charset=utf8;

插入时候只能用男或女

insert into t7 values('男'); -- 插入"男"没有问题

insert into t7 values('女'); -- 插入"女"没有问题

select * from t7;

+--------+

| gender |

+--------+

| 男 |

| 女 |

+--------+

插入的内容，不在枚举的范围的内提示警告

insert into t7 values('雄'); -- Query OK, 1 row affected, 1 warning (0.00 sec)

insert into t7 values('雌'); -- Query OK, 1 row affected, 1 warning (0.00 sec)

select * from t7;

插入时候只能选男或女，插入其它的内容显示为空

+--------+

| gender |

+--------+

| 男 |

| 女 |

| |

+--------+

5、SET 集合类型

与枚举相应的还有 set 集合类型 SET('value1', 'value2', ...)

集合与枚举型比较像，区别在于插入的时候枚举只能选其中之一，set 有集合的意思，一次可以选其中的一个或几个插入进去。

增加一个 flag 列，类型是 set 集合

Alter table t7 add `flag` set('c','h','p','f','s','j','a','b') DEFAULT NULL;

一次可以插入一个或插入多个

insert into t7 (flag) values ('a'); -- 插入一个 a
insert into t7 (flag) values ('a,c'); -- 插入两个 a c
insert into t7 (flag) values ('a,c,f'); -- 插入语三个 a c f

select * from t7;

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

| gender | flag |

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

| 男 | NULL |

| 女 | NULL |

| | NULL |

| NULL | a |

| NULL | c,a |

| NULL | c,f,a |

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

选择 flag 含有 c 的行

select * from t7 where FIND_IN_SET('c',flag);

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

| gender | flag |

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

| NULL | c,a |

| NULL | c,f,a |

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

总结:

字符类型记住 char 和 varchar 的区别，

text 特点是大文本，text 和 blob的区别，blob 是不考虑字符集，存二进制时候适合用 blob，

enum 枚举类型

6、转一篇 dedecms 里面 set 字段的博文

dede 中的文章的 flag 字段，存“a,b,c,d,e,f”

SELECT * from dede where FIND_IN_SET('b',flag);

对于包含超过一个 SET 成员的值，当你插入值时，无所谓以什么顺序列出成员。

也无所谓给出的值被列举了多少次。

当以后检索该值时，在值中的每个成员将出现一次，根据他们在表创建时所指定的顺序列出成员。

例如，如果一个列被定义为 SET("a","b","c","d")，那么，"a,d"、"d,a" 和 "d,a,a,d,d" 在被检索时均将被视为 "a,d"。

如果将一个不支持的值赋于一个 SET 列，该值将被忽略。

SET 以数字顺序排序。NULL 值排在非 NULL SET 值之前。

通常，可以使用 LIKE 操作符或 FIND_IN_SET() 函数执行在一个 SET 列上的 SELECT：

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

但是，下列示例也可以工作：

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

mysql> SELECT * FROM tbl_name WHERE set_col & 1;

第一个语句寻找一个精确的匹配。第二个语句寻找包含第一个集合成员的值。

如果希望得到一个 SET 列的所有可能值，可以使用 SHOW COLUMNS FROM table_name LIKE set_column_name 并分析第二列的 SET 定义。

转自: http://2611845.blog.163.com

四、日期时间类型

1. Year

存年份用的（占1字节）比如95/1995

范围1901 - 2155

在 insert 插入时，可以简写年的后2位，但是不推荐这样

[00-69] + 2000

[70-99] + 1900

既: 填2位，表示 1970 - 2069

2. Date

日期1988-12-31

范围1000/01/01 --> 9999/12/31

3. Time

时间13:56:23

范围838:59:59 --> 838:59:59

4. datetime

日期时间的结合1988-12-31 13:56:59

范围：1000/01/01 00:00:00 --> 9999:12:31 23:59:59

5. timestam 时间戳

从1970-01-01 00:00:00到当前的秒数，一般存注册时间、商品发布时间等，

实际开发并不是用datetime储存，而是用时间戳，因为datetime虽然直观但计算不便

这几种都做一个讨论

第一列 ya year类型

第二列 dt date类型

第三列 tm time类型

第四列 dttm datetime类型

create table t8(
  ya year, -- year类型
  dt date, -- date类型
  tm time, -- Time类型
  dttm datetime -- datetime类型字段
);

1、Year类型

首先测试年份，可以输入四个字1901

insert into t8 (ya) values(1901); -- 输入四个字的1901年

select * from t8;

显示1901没有问题

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

| ya | dt | tm | dttm |

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

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

上面是输入四个字的年份，这次插入两个字符的年份95，上个世纪喜欢写两个字

insert into t8 (ya) values(95); -- 插入两个字"95"年

select * from t8;

95年代表上个世纪1995年

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

| ya | dt | tm | dttm |

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

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

插入两个字 56

insert into t8 (ya) values(56); -- 插入56到了20世纪写两位不行了

select * from t8;

得到本世纪2056年

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

| ya | dt | tm | dttm |

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

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

年份year类型的时候就写4个字的就行了，因为写两个字的的时候是这样算的，

[70-99] + 1900 也就是说从1970开始记数到1999，

insert into t8 (ya) values(70);

insert into t8 (ya) values(99);

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

| ya | dt | tm | dttm |

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

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

[00-69] + 2000是从2000到2069

insert into t8 (ya) values(00);

insert into t8 (ya) values(69);

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

| ya | dt | tm | dttm |

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

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

2、date类型

date类型日期“年-月-日”

truncate table t8; -- 清空数据库

insert into t8 (dt) values ('1990-12-23');

select * from t8;

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

| ya | dt | tm | dttm |

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

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

3、time类型

time时间类型是按“时: 分: 秒”来存时间

truncate table t8;

insert into t8 (tm) values ('09:28:21');

select * from t8;

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

| ya | dt | tm | dttm |

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

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

4、datetime类型

datetime时期时间类型，按照“年-月-日时:分:秒”拼接起来

truncate table t8;

insert into t8 (dttm) values ('2020-10-28 09:33:18');

select * from t8;

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

| ya | dt | tm | dttm |

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

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

5、timestam类型

timestam时间戳类型，它会自动的取当前时间戳来存在

create table t9 (
    id int,
    ts timestamp -- 时间戳类型
);

只插入id列，不插入ts列它自动更新当前的时间戳

insert into t9 (id) values (1); -- 插入第一条

insert into t9 (id) values (2); -- 隔一会插入第二条

insert into t9 (id) values (2); -- 隔一会插入第三条

select * from t9;

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

| id | ts |

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

| 1 | 2020-11-06 13:51:09 |

| 2 | 2020-11-06 13:51:58 |

| 2 | 2020-11-06 13:55:12 |

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

不建议用"timestamp时间戳类型"它效率并不高，如果需要精确时间，用"int unsigned"无符号正整数，来存具体精确时间戳

建表就是声明列的过程，只需把一个一个的列类型选好了，列类型完毕了下面做一个建表的案例实战

五、列的默认值

建表语句有一个 NOT NULL default xxx 这是列的默认值。

首先要知道，

1). null查询不方便，它非常特殊要 is null 或者 is not null 来查询，

2). NULL的索引效率不高，会影响查询速度

所以在使用中都避免列的值为NULL，怎么避免列的值为NULL，这个列确实没给插入值，总要有一个值

如何避免列为null？声明列为 not null default 默认值

create table t10(
    id int not null default 0,        -- id列int类型，不能为null默认值是0
    name char(10) not null default '' -- name列字符串类型，不能为null默认值是空字符串
)default charset=utf8;

两个字段都设置了默认值

insert into t10 values(1, 'zhangSan'); -- 插入，id值为1，name值为zhangsan

insert into t10 (id) values (2);       -- id值为2，name字段没有插入值

select * from t10;

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

| id | name |

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

| 1 | zhangSan|

| 2 | | 没有插入，这里并没有显示null

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

没有插入值的列也可以查出来

select * from t10 where name = ''; -- 查询name等于空的数据

查询出来了，如果是null这样是查不出来的

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

| id | name|

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

| 2 | |

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

六、主键与自增

什么是主键？

主键"primary key"是能够区分每一行数据的列

以用户名为例，为了标记会员，往往都给他们一个独一无二的编号，这个编号称之为主键，既然用编号来区分，这个编号的特点肯定是不会重复的。主键是一定不会重复的，但反过来不重复的不一定是主键，比如注册会员的email邮箱也不允许重复。

主键用 primary key 来声明

create table t11(
    id int primary key, -- "primary key"声明id是主键
    name char(2)
);

也可以放到最后声明主键，这两种方式都可以

create table t12(
    id int,
    name char(2),
    primary key(id) -- 声明主键有两种方法，也可以放到最后在这声明主键
);

两种建立的表是完全一样的

desc t11;

desc t12;

t11表，id列是主键

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

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

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

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

t12表，id列是主键

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

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

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

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

主键决定了id列不能重复，往id列第一次插入一个数字"1"成功

insert into t12 values (1, 'aa');

往id列再插入一次数字"1"，提示报错 Duplicate entry '3' for key 'PRIMARY'

insert into t12 values (1, 'aa'); -- ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

02/

主键 primary key 往往和 auto_increment 一起出现一起出现，但并不意味着它俩必须要绑定在一块，因为一般情况下主键都是一个数字1、2、3……所以才让它递增，但有的时候就用email邮箱做主键也可以，这样就不用递增了。

主键递增也很简单

先做一个手脚，t13表中没有声明任何主键和索引，只有声明了id列是自增属性auto_increment，这张表能创建成功吗？t13表不能创建成功，因为auto_increment属性必须在主键上才能发挥作用

create table t13(
    id int auto_increment, -- 没有声明主键，只有自增auto_increment
    name char(2)
);

-- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

下面同样不设置任何主键，设置id列自增属性auto_increment，但是给id列设置普通索引，试一下t13表可以创建成功吗？

竟然可以创建成功

create table t13(
    id int auto_increment,
    name char(2),
    key id(id) -- id列设置索引
);

-- Query OK, 0 rows affected (0.01 sec)

auto_increment属性包含两点注意，一张表只能有一列为auto_increment自增，并且这个自增字列必须加索引(index)或主键(key)

只不过我们在实际使用中，往往把主键primary key和自增auto_increment配合起来

create table t14(
    id int primary key auto_increment, -- id是主键、且int型、且自增
    name char(2)
);

-- 创建成功Query OK, 0 rows affected (0.01 sec)

自增也是mysql独特的功能，在oracle里没有自增因为自增浪费效率，oracle有专门的东西生成序列号

03/

ecshop里面的一张表，大部分能可以看明白了

create table goods (
  goods_id mediumint(8) unsigned primary key auto_increment,
  goods_name varchar(120) not null default '',
  cat_id smallint(5) unsigned not null default '0',
  brand_id smallint(5) unsigned not null default '0',
  goods_sn char(15) not null default '',
  goods_number smallint(5) unsigned not null default '0',
  shop_price decimal(10,2) unsigned not null default '0.00',
  market_price decimal(10,2) unsigned not null default '0.00',
  click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8;

goods_id mediumint(8) unsigned primary key auto_increment,

mediumint(8)三个字节的长度能存一千多万，unsigned说明商品的id只能是正数，primary key auto_increment说明goods_id列是主键且自增

goods_name varchar(120) not null default '',

varchar型最长120个字符，不为null默认值是空字符串

cat_id smallint(5) unsigned not null default '0',

smallint(5)能六万多，unsigned正数，not null default '0'不为null默认值是0,

engine=myisam default charset=utf8;

engine=myisam表引擎，charset=utf8字符集

七、综合建表案例

01/

某高端白领私密社交网站

	列名称	列类型	默认值	是否主键
主键	id	Int unsigned		PRI
用户名	username	Varchar(20)	''
性别	gender	Char(1)男/女/tinyint(0表示女/1表示男)/enum枚举(男/女)
体重(KG)	weight	Tinyint unsigned
生日	birth	Date
工资	salary	Decimal(8,2)
上次登录	lastlogin	Datetime
个人简介	intro	Varchar(1500)

主键id列：

一般是Int类型，主键不能是负数unsigned，是PRI

用户名username列：

最长用的人名四个字，再长一点少数民族的民族，varchar(20)默认值为空字符串

性别gender列：

可以用var(1)存男或女，但字符串不如数字效率高，不用考虑字符集问题，用tinyint也可以比如用1表示男、0表示女，也可以用枚举enum(男,女)，

论速度这三者相比较还是tinyint快一些

体重(KG)weight列：

一般精确到千克，不用小数点了，选Tinyint类型（一个字节也不浪费）可以存0-255，unsigned重量也不可能为负数

生日birth列：

可以用Date来存储，格式是 1988-12-31

工资salary列：

不用太大，用定型Decimal(8,2)，小数点前六位最大能存到9万9千多，也够奋斗几年了

上次登录lastlogin列：

Datetime类型年月日时分秒，格式是 1988-12-31 13:56:59

个人简介intro列：

varchar(1500)类型，一千五百字的个人介绍

02/

这张表有不够好的地方，可以再优化一下：

除了intro列、username列长度是变化的之外，其它字段都是固定的长度，如何来优化呢？

如果一张表所有的列都能是定长，它的速度将会得到显著的提升，因为mysql在询址的时候，每一列的字节都能计算出来，应该停留在磁盘的那一个位置，就能立即定位出来，不用再去查询直接算就能算出来，所以一个表全是定长对这个表的查询速度提升是很好的。

用户名unername列优化：

随着磁盘空间的增大，username列由varchar(20)类型变成定长char(10)类型，虽然造成点浪费但是速度快

上次登录时间lastlogin列优化：

没必要用Datetime类型，因为一周内活跃的人用Datetime不好统计，可用用时间戳改成"int"类型不为负数unsigned

	列名称	列类型	默认值	是否主键
主键	id	Int unsigned		PRI
用户名	username	char(10)	''
性别	gender	tinyint	0
体重(KG)	weight	Tinyint unsigned	0
生日	birth	Date	0000-00-00
工资	salary	Decimal(8,2)	000000.00
上次登录	lastlogin	int unsigned	0

个人简介intro列优化：

intro列拿掉了

intro个人简介变成char(1500)一千五百字浪费太高，有的人就写几个字的介绍，另一方面个人介绍改的频率也不高

这种情况，把intro列单独拿出来另放一张表里，这也是开发中提高效率的技巧，定长与变长分离，常用列与不常用列分离

	列名称	列类型	默认值
主键	id	Int unsigned
用户名	username	char(10)	''
个人简介	intro	Varchar(1500)

建表sql语句

create table regist(
    `id` mediumint(8) unsigned primary key auto_increment NOT NULL,
    `username` char(10) not null default '',
    `gender` tinyint unsigned not null default 0,
    `weight` Tinyint unsigned not null default 0,
    `birth` date not null default '0000-00-00',
    `salary` decimal(8,2) not null default '000000.00',
    `lastlogin` int unsigned not null default 0
)default charset=utf8;

create table intro(
    `id` mediumint(8) unsigned PRIMARY KEY NOT NULL DEFAULT '0',
    `username`  char(10) not null default '',
    `intro`  varchar(1500) not null default ''
)default charset=utf8;


show create table regist\G -- No query specified 出现此错误 show create table regist\G;的时候，多加了一个“;”分号，去掉分号就不会出现此错误

show create table intro\G

建表的时候字段名如果非必要一律小写，因为大小写有时候敏感有时候不敏感

八、列的增删改

比如表建好了，发现少建了一个列能不能加上，然后多建了一个列能不能删掉，或者原本是char(5)不够用能不能换成char(10)，这些都是可以的。

注意新增一个列与新增一行数据的区别，新增一个列说明表的结构要改变了（原本3个列变2个列了）属于DDL操作数据库定义，插入列和表结构没关系仅仅是多一行数据

改表名，表名regist改成reg

rename table regist to reg;

reg表追加一个身高hieght列，alter有修改的意思

-- 语法: alter table 表名 add 字段名 字段类型 字段属性...;

alter table reg add height tinyint unsigned not null default 0;

desc reg;

默认新增的height列在表的最后

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

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

| id | mediumint(8) unsigned| NO| PRI | NULL | auto_increment|

| gender | tinyint(3) unsigned | NO | | 0 | |

| weight | tinyint(3) unsigned | NO | | 0 | |

| birth | date | NO | |0000-00-00| |

| salary | decimal(8,2) | NO | | 0.00 | |

| lastlogin | int(10) unsigned | NO | | 0 | |

| height | tinyint(3) unsigned | NO | | 0 | |

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

heigth列放到表的最后不太好，希望新增height列和weight列挨着

先删除把height列删除，顺便看一下删除列，关键字drop有放弃和删除的意思

-- 语法: alter 表名 drop column 字段名;

alter table reg drop column height;

新增的height列删除了

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

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

| id | mediumint(8) unsigned| NO| PRI | NULL | auto_increment|

| gender | tinyint(3) unsigned | NO | | 0 | |

| weight | tinyint(3) unsigned | NO | | 0 | |

| birth | date | NO | |0000-00-00| |

| salary | decimal(8,2) | NO | | 0.00 | |

| lastlogin | int(10) unsigned | NO | | 0 | |

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

height字段删了之后再重新加上，在weight字段后面追加一个height字段重量和身高挨着

-- 语法: alter table 表名 add 新增列名 字段类型 字段属性... after 列名（将会在指定列后）; 

alter table reg add height tinyint unsigned not null default 0 after weight;

desc reg;

height列在在指定字段weight列后面

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

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

| id | mediumint(8) unsigned| NO| PRI | NULL | auto_increment|

| gender | tinyint(3) unsigned | NO | | 0 | |

| weight | tinyint(3) unsigned | NO | | 0 | |

| height | tinyint(3) unsigned | NO | | 0 | |

| birth | date | NO | |0000-00-00| |

| salary | decimal(8,2) | NO | | 0.00 | |

| lastlogin | int(10) unsigned | NO | | 0 | |

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

修改列，比如把列名height改成shengao，tinyint类型改成smallint

alter table 表名 change 旧列名新列名 smallint(类型) unsigned(属性) not null(默认值不为null) default 0(默认值为0);

-- 语法: alter table 表名 change 修改的列名(height) 顺势可以修改列的新名(shengao) smallint(改类型) unsigned(改属性) not null default 0(改不为空默认值);

alter table reg change height shengao smallint unsigned not null default 0;

desc reg;

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

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

| id | mediumint(8) unsigned| NO| PRI | NULL | auto_increment|

| gender | tinyint(3) unsigned | NO | | 0 | |

| weight | tinyint(3) unsigned | NO | | 0 | |

| shengao | smallint(5) unsigned | NO | | 0 | |

| birth | date | NO | |0000-00-00| |

| salary | decimal(8,2) | NO | | 0.00 | |

| lastlogin | int(10) unsigned | NO | | 0 | |

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

还可以用modify修改列，用modify与change修改有什么不同？modify只能修改列类型、属性，不能修改列名

-- 语法: alter table 字段名 modify 字段名 类型 属性...

alter table reg modify shengao tinyint unsigned not null default 0;

desc reg; -- 查看表的状况

shengao列类型又改回tinyint

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

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

| id | mediumint(8) unsigned| NO| PRI | NULL | auto_increment|

| gender | tinyint(3) unsigned | NO | | 0 | |

| weight | tinyint(3) unsigned | NO | | 0 | |

| shengao | tinyint(5) unsigned | NO | | 0 | |

| birth | date | NO | |0000-00-00| |

| salary | decimal(8,2) | NO | | 0.00 | |

| lastlogin | int(10) unsigned | NO | | 0 | |

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

九、常用表/视图管理语句

01/

当选择一个库之后，看看这个库下有那些表和视图

show tables;

查看表结构，有那几列，列类型是什么

desc 表名; -- 查看表结构

desc 视图名; -- 查看视图（视图看起来就是一张表，外观上看不出视图和表的区别）

删除一个表\视图

drop table 表名; -- 删除一个表

drop view 视图名; -- 删除一个视图

查看创建表\视图过程的sql语句

show create table 表名; -- 查看创建表过程的sql语句

show create table 表名\G -- 查看创建表过程的sql语句

show create table 视图名; -- 查看创建视图过程的sql语句

02/

查看一个数据库下所有表的详细信息

show table status; -- 查看当前数据库下，所有表和视图的详细信息

show table status\G  -- 看不太清楚可以竖着显示，所有的表都罗列显示

Name: goods 表的名字

Engine: MyISAM 表的引擎

Version: 10

Row_format: Dynamic 行是固定的还是变长的？Dynamic代表每一行长度是不固定的

Rows: 31 目前有31行

Avg_row_length: 54 平均每行有多长

Data_length: 1696

Max_data_length: 281474976710655

Index_length: 2048

Data_free: 0

Auto_increment: 33

Create_time: 2020-11-06 16:35:07 什么时间创建的

Update_time: 2020-11-07 12:26:12

Check_time: NULL

Collation: utf8_general_ci utf8是字符集，general代表是广泛的，ci是可选的校对集

Checksum: NULL

Create_options:

Comment: 普通表没有值，如果值是视图值是VIEW，比如 "Comment: VIEW"

.......................................................................表太多省略线

查看某张表详细信息

show table status where name = 'goods'; -- 只查看goods表的详细信息

show table status where name = 'goods'\G

*************************** 1. row ***************************

Name: goods 商品表名goods

Engine: MyISAM 引擎是MySAM

Version: 10

Row_format: Dynamic 每一行的长度是不固定的是变化的

Rows: 0 表目前有几行数据，这里没有显示，可能是版本的问题

Avg_row_length: 0 平均每行多长这里也没显示

Data_length: 0

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: 1

Create_time: 2020-11-07 21:12:42

Update_time: 2020-11-07 21:12:42

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

03/

改表名

-- 语法： rename table 表名 to 新表名;

rename table oldName to newName; -- 改表名

如何快速清空一张表的数据，delete是删除数据，这个属于是DML操作，增删改查只不过把所有数据都删掉了

delete from 表名; -- 删除表的所有的数据，属于DML操作

truncate也能迅速的清空某一张表的所有数据

truncate table 表名; -- 清空表的数据

truncate和delete两着效果是不一样的

create table t15(
    id int primary key auto_increment -- id列int型，主键自增长
);

insert into t15 values (null); -- 执行1次
insert into t15 values (null); -- 执行2次 
insert into t15 values (null); -- 执行3次 

select * from t15;

插入的是null它会自增长

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

+----+

删除t15表id是3的数据

delete from t15 where id = 3; -- 删除t15表其中一条数据

select * from t15;

+----+

| id |

+----+

| 1 |

| 2 |

+----+

删除Id是3的一条数据后，在插入一条数据，id列显示的是4因为它是自增的

insert into t15 values (null); 

select * from t15;

第3条删除掉了，字段是自增的，再插入是第4条

+----+

| id |

+----+

| 1 |

| 2 |

| 4 |

+----+

如果把t15表删掉，删掉后再重新建这张表，再次插入数据id从1开始

drop table t15; -- 删除t15表

create table t15( -- 重新创建t15表
    id int primary key auto_increment
);

insert into t15 values (null); 
insert into t15 values (null); 
insert into t15 values (null); 

select * from t15;

id从"1"开始自增

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

+----+

truncate清空掉t15表，再插入数据id也是从1开始

truncate table t15; -- truncate清空t15表

insert into t15 values (null); -- 再插入数据 

select * from t15;

id从1开始

+----+

| id |

+----+

| 1 |

+----+

通过上面的例子看truncate和delete的区别：

delete是DML操作删掉表的所有数据，再继续增加行的时候主键还在继续增长

而truncate内部经历两步快速操作，一步是drop这张表后，然后再迅速的建立起这张表，所以主键自增长的信息等都被初始化了，当然truncate要快一些

十、存储引擎

01/

最直观的来看"xampp\mysql\data\student"目录下以goods表为例有三个文件组成

goods.frm 声明表的结构，建表时候每一列是什么类型、属性都在这里面存着

goods.MYD 表的数据

goods.MYI 表的索引文件

goods商品表和t14表比较一下

t14表有 t14.frm文件，它没有MYD、MYI文件，t14表的数据存在"xampp\mysql\data"目录下的 ibdata1 文件里面

往t14表里插入两条数据

insert into t14 (id, name) values (null, 'zhangsan');

insert into t14 (id, name) values (null, 'li');

"xampp\mysql\data"目录下"ibdata1"文件的修改时间，是刚刚插入数据时候的最新时间，

"ibdata1"文件的时间变了、它的大小也在变

02/

同样是数据表，goods表和t14表不一样

goods商品表，它的数据放到同一个目录下，goods.frm文件放结构，goods.MYD放数据，goods.MYI放索引文件

t14表，它的表结构放到一个目录下，表的数据和索引放到一个专门"ibdata1"大文件里面，好多个表共享这个"ibdata1"文件

这个就是因为表的引擎不同，数据存到的位置不一样，goods表和t14表使用的什么引擎？

查看goods表使用的引擎

show table status where name = 'goods'\G

Name: goods

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: 1

Create_time: 2020-11-07 21:12:42

Update_time: 2020-11-07 21:12:42

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

查看t14表使用的引擎

show table status where name = 't14'\G

Name: t14

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 2

Avg_row_length: 8192

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 3

Create_time: 2020-11-06 16:26:09

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

goods表引擎是MyISAM，t14表引擎是InnoDB，引擎不同组织数据的方式不同，myslq5.5以后官方把InnoDB设置成默认引擎，所以声明创建表的时候不指定引擎默认就是InnoDB

03/

既然有不同的表引擎，说明它们肯定有各自不同的特点

最常见的有三种引擎MyISAM、InnoDB、Memory其它还有BDB、Archive

Memory引擎创建的表、数据统统都存在内存里面不存放磁盘上，它速度是非常快的，但是服务器一关机，数据就没有了，所以不持久保存的临时的用Memory

重点，面试时候最容易被问到MyISAM和InnoDB的区别

Myisam更快一些，但是如果数据崩溃了丢了好几行、甚至表损坏了，找回难度比较大，InnoDB就算数据崩溃了也没关系，它有丰富的日志，每一行都有记录，凭着日志能恢复，更安全一些

两者的差别：

批量插入速度: Myisam高一些，InnoDB低一些

事务安全: Myisam不支持，InnoDB支持事务安全

全文索引: Myisam支持全文索引，InnoDB不支持

锁机制: Myisam直接锁整张表，InnoDB能精确到行来锁

04/

创建表的时候指定"存储引擎"

create table tp_name(
  id int unsigned primary key auto_increment,
  name varchar(20) not null default ''
) engine=myisam default charset=utf8;  -- engine=myisam指定存储引擎为myisam

show create table tp_name\G

Myisam有一个好处，导出数据时候比较简单，可以直接把xampp\mysql\data的目录文件拷贝就可以，这个目录就是数据库，InnoDB就不行，因为它是多张表，甚至多个库多个表混在一个文件里写的，不能直接拷贝。

sql语句修改存储引擎

alter table tp_name engine = innoDB;

show create table tp_name\G

十一、索引

索引是干什么的？

数据库帮我们存的数据，以goods商品表为例数据存存在"\xampp\mysql\data\student"目录下的"goods.MYD"文件里，当然它是二进制文件。

当我们取数据的时时候，希望它取的特别的快，当数据有十万条的时候"goods.MYD文件"将会越来越大，查select第一万条数据的时候，应该迅速的来到磁盘上这第一万行数据的位置，问题是怎么知道这一万行数据在"goods.MYD文件"的第多少字节，就是说沿着"goods.MYD文件"要走多少个字节才能到第一万行的位置呢，怎么能快速的查到呢？

这时候就要靠索引文件了，"goods.MVI"文件就是索引文件，索引文件里面又是一堆数据，只不过这个数据是通过某种数据结构，比如树形结构高效索引起来的

当数据越来越多的时候，索引的优势就越来越明显，比如当数据四十亿的时候索引顶多查32次就查到了

也就是说，索引是一种高效组织的数据结构，能够加快我们的查询

索引是数据的目录，能快速定义行数据的位置。

思考一下：

在十万行数据上再增加一行数据，增加到十万零一行。现有索引了文件，在修改数据的同时也要"更新索引文件"，所以索引是有代价的，并非加的越多越好，索引提高了查询的速度，但是也降低了增、删、改的速度。

在实际使用中都有可能发生一张表的索引文件比这个表的数据文件还要大

一般在查询比较频繁的列上加索引，而且重复度低的列上加索引效果更好，比如性别字段就男和女加索引意义不大，如果给身份证号加索引，身份证号不重复，这样就能迅速定位到一个人。

1、索引的语法以及索引的类型

key：普通索引，纯粹就帮我们提高查询的速度
unique key：唯一索引，它起到两个作用，1.加快查询速度，2.还能约束数据，这个数据不可能重复了，所以才叫unique唯一的意思
primary key：上面见过也叫主键索引不能重复.
主键必唯一，但是唯一索引不一定是主键，一张表上只能有一个主键，但是可以用一个或多个唯一索引
上述3种索引,都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章
fulltext：全文索引，在中文环境下几乎是无效的，在中文环境下用分词+索引，一般用第三方解决方案，如sphinx
优化 - 索引长度
多列索引
冗余索引

2、先看前两种索引，普通索引key、唯一索引unique key

语法：key 列名(索引名) 一般列名和索引名一样就可以了，可以把所有的列声明完毕之后在加索引

create table t16(
    name char(10),
    email char(20),

                            -- 可以把所有的列声明完毕之后在加索引
    key name(name),         -- name列加普通索引，索引名称也叫name，一般列名和索引名一样
    unique key email(email) -- email列加不允许重复的唯一索引
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

desc t16;

有一个key字段，MUL代表普通索引、UNI代表unique唯一索引

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

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

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

首次往t16表插入数据，name是"李四"、email是"770747402@qq.com"，成功插入

insert into t16 values ('李四', '770747402@qq.com'); -- Query OK, 1 row affected (0.00 sec)

再次插入数据，name重复是李四、email还重复770747402@qq.com，这时候就出错了

insert into t16 values ('李四', '770747402@qq.com'); -- ERROR 1062 (23000): Duplicate entry '770747402@qq.com' for key 'email'

在维护索引的时候，会检查email：'770747402@qq.com'已经存在了，就不允许插入这列的值了，这是unique key的特点。

不允许重复的列，比如用户名不能重复、email不能重复，在这两个列就能加上unique key索引，既能提高查询速度，也能起到约束的作用。

2、主键索引 primary key

"primary key"上面已经用过了，primary key就不用起名字了，因为一张表只可能有一个主键索引

给id列加主键索引

create table t17(
    id int unsigned primary key auto_increment, -- 主键索引一般是这样配合 unsigned primary key auto_increment
    name char(10),
    email char(20),
    key name(name),
    unique key email(email)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

也可以在列声明完成之后，在下面加主键索引

create table t17(
    id int unsigned auto_increment,
    name char(10),
    email char(20),
    primary key(id), -- 给id列声明主键索引primary key
    key name(name),
    unique key email(email)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

desc t17;

主键索引的key叫PRI

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

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

| id | int(10) unsigned | NO | PRI | NULL | auto_increment|

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

3、全文索引

全文索引在中文环境下几乎是无效的，在英文环境下有一个"分词的概念"，在英文环境下是把一长串字符的单词，一个一个分别建立索引，然后查中间的任意一个单词，都能查得到这行

但是对中文就不行了，中文的字都是连着的，区分不出来一个一个的单词，所以在中文环境下全文索引无效

一般在中文环境下想用"全文索引"，用分词+全文索引才有效，一般都用第三方解决方案，比如"sphinx"

掌握了索引最简单的用法，有的时候把索引更优化一点，可以设置索引的长度

4、优化 - 索引长度

创建索引时候，可以只索引列的前一部分的内容，比如只索引前10个字符

比如E-mail邮箱admin@ruyic.com，前半部分admin都是用户名区别很大，但是后面几个字符都是"@+域名"（比如：@ruyic.com）区分度就没有那么高了

所以为了省点索引的空间，就把E-mail的前十个字符截取下来创建索引，语法 key email(email(10)) 只取前十个字符创建索引

create table t18(
    name char(10),
    email char(20),
    key name(name),
    unique key email(email(10)) -- 只取email字段前10字符建索引
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

索引只限十个长度并不是说email被限制10个长度

insert into t18 values ('王五', 'abcdefghijkfl13@qq.com'); -- 插入超过十个字符的

select * from t18;

建索引的时候取前10个字符，不影响插入的email超过10个字符

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

| name| email |

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

| 王五 | abcdefghijkfl13@qq.com |

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

5、多列索引

有时候一列索引还查询不出来效果可以键多列索引，多列索引的意思是把两列或多列的值看成一个整体然后建索引

下面创建t19表，firstname列是姓的意思，lastname列是名的意思，分开存姓和名是为了以后扩展方便，这样单独查姓李的或姓张的都非常方便，但更多的时候是需要按整个的人名来查，比如: 郭德纲

希望把"郭"和"德刚"这两个看成一个整体"郭德纲"来查询

create table t19(
    firstname char(2),
    lastname char(10),
    key first_last(firstname, lastname) -- 多列索引
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入"郭"、"德纲"

insert into t19 values ('郭', '德纲');

desc t19; -- 查看表

MUL型的索引，里面包含了两个列的索引

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

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

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

用下面查看表结构的方法，可以看的更清晰

show index from t19;

show index from t19\G -- 竖着的形式查看

firstname列、lastname列都处于索引之下

*************************** 1. row ***************************

Table: t19

Non_unique: 1

Key_name: first_last 索引名

Seq_in_index: 1

Column_name: firstname 列名

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: t19

Non_unique: 1

Key_name: first_last

Seq_in_index: 2

Column_name: lastname

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

注意：多列索引同时查姓和名 where firstname = '郭' and lastname = '德纲'，索引当然可以发挥作用，系统把姓和名合成一个整体后，然后按照两列一个综合索引去查

select * from t19 where firstname = '郭' and lastname = '德纲';

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

| firstname | lastname |

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

| 郭 | 德纲 |

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

如果单独查询一个姓 where firstname = '郭'，索引也可以发挥作用

select * from t19 where firstname = '郭';

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

| firstname | lastname |

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

| 郭 | 德纲 |

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

如果只查找名字 where lastname = '德纲'，这样索引是不能发挥作用的

select * from t19 where lastname = '德纲';

可以用explain看一看姓和名 where firstname = '郭' and lastname = '德纲'

explain select * from t19 where firstname = '郭' and lastname = '德纲'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t19

type: system

possible_keys: first_last possible_keys 意思是有可能使用到的索引是"first_last"

key: NULL key是最终用到的索引，值应该是first_last这里没有显示，可能是版本问题，也可能是在索引上就解决了，没有去到行里

key_len: NULL

ref: NULL

rows: 1

Extra:

用explain只查看姓 where firstname = '郭'

explain select * from t19 where firstname = '郭'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t19

type: system

possible_keys: first_last 有可能使到的索引是 first_last

key: NULL 值应该是first_last还是没有显示

key_len: NULL

ref: NULL

rows: 1

Extra:

第三次用explain查看名 where lastname = '德纲'

explain select * from t19 where lastname = '德纲'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t19

type: system

possible_keys: NULL 有可能使到的索引为NULL，查询中绝对没有使用上索引

key: NULL

key_len: NULL

ref: NULL

rows: 1

Extra:

where lastname = '德纲' and firstname = '郭' 先查询"德纲"、再查询"郭"可以使用上索引吗？

explain select * from t19 where lastname = '德纲' and firstname = '郭'\G

id: 1

select_type: SIMPLE

table: t19

type: system

possible_keys: first_last 使用上的索引 first_last

key: NULL

key_len: NULL

ref: NULL

rows: 1

Extra:

为什么这次使用上索引了？

mysql有一个语言分析的过程， lastname = '德纲' and firstname = '郭' 等价于 firstname = '郭' and lastname = '德纲'，mysql会把语句做分析的。

上面这样 where lastname = '德纲' 为什么使不上索引，或者说mysql要满足一个什么原则能使用上索引？

记住一个“左前缀规则”，一个数据“abcdefg ”从左往右，查的时候后面3个记不清楚了，但记得前3个是 abc 是确定的，索引是有顺序的，可以定位大致的范围abc开头的都在哪一片

但是如果前面的3个字母abc记不住了，后面记住了是的没法定位了，前面有可能是abc也有可能是zzz，根据右边的几个是无法定位的，这叫“左前缀规则”，从左往右查，左的前几个记住了右边的记不住了，没关系前面的几个记住了索引还能部分发挥作用

所以查"郭德纲"，姓+名都查当然可以用上索引，单独查"郭"也可以使用上索引，而如果直接查"德纲"就使不上索引了。

6、冗余索引

就是在某个列上，可以存在多个索引

比如，在姓和名上都建立了一个索引 key first_last(firstname, lastname)，但是只查名字索引发挥不了作用，因此额外建立一个专门针对名字（lastname）发挥作用的索引 key lastname(lastname)

这样查姓可以发挥索引的作用，查姓名可以发挥索引的作用，查名也可以发挥索引的作用

观察对名（lastname）这个列，被两个索引所覆盖，这种情况叫冗余索引，在一个列上可能存在多个索引，实际开发中"冗余索引"也是经常用到的。

create table t20(
    firstname char(2),
    lastname char(10),
    key first_last(firstname, lastname), -- 先给姓、名加一个共同索引
    key lastname(lastname)        -- 在加一个"名"索引
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

show index from t20\G

*************************** 1. row ***************************

Table: t20

Non_unique: 1

Key_name: first_last 姓名索引在姓（firstname）这个列上

Seq_in_index: 1

Column_name: firstname

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: t20

Non_unique: 1

Key_name: first_last 姓名索引在名（lastname）这个列上

Seq_in_index: 2

Column_name: lastname

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: t20

Non_unique: 1

Key_name: lastname 名这个索引在名（lastname）这个列上

Seq_in_index: 1

Column_name: lastname

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

这种在同一个列上被多个索引所覆盖叫“冗余索引”，冗余索引在开发中是有的时候必要的

接下来看一看具体的索引语法，如何索引的查看索引、删除索引、添加索引

7、索引操作语法

查看：

查看一张表的索引

语法: show index from 表名;

show index from t20;

show index from t20\G

或者这样也能看到索引

语法： show create table 表名;

show create table t20;

删除：

删除索引，可以用alter来删除，语法: alter table 表名 drop index 索引名; 一般喜欢用这个方法来删

alter table t20 drop index lastname;

也可以用drop，语法： drop index index_name ON tbl_name;

drop index first_last on t20; -- 语法: drop index 索引名 on 表名;

添加：

添加普通索引或多列索引（复合索引）

语法: alter table 表名 add index 索引名(列名1, 列名2);

alter table t20 add index first_last(firstname, lastname);

添加unique唯一索引

语法: alter table 表名 add unique 索引名(列名);

alter table t20 add unique lastname(lastname);

主键索引：

添加主键索引也一样，唯一的不同是不用给索引起名字了

语法： alter table 表名 add primary key(列名);

alter table t20 add primary key(firstname); -- 给姓"firstname"添加一个主键索引

删除主键索引，也不用写列名称，因为主键只有一个

语法： alter table 表名 drop primary key;

alter table t20 drop primary key; -- 注意，主键索引不用写列名，删除成功

复习秘籍

秘籍：http://ruyic.com/blog/uploads/other/20201201/160680079843209.html