mysql 三： 表的详细操作

一、创建表的完整语法
#语法：
create table 库名.表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
);
约束条件：是在数据类型之外对字段附加的额外的限制

#注意：
1、最后一个字段之后不能加逗号
2. 在同一张表中，字段名是不能相同
3. 宽度和约束条件可选，字段名和类型是必须的


二，数据类型

1，整型：默认是有符号的。

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

tinyint:

小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127
            无符号：
                0 ～ 255

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了

默认的显示宽度，都是在最大值的基础上加1

 

 

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

无符号最大值为：4294967296-1

有符号最大值：2147483648-1

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

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

 

#1、整型：默认是有符号的
create table t3(x tinyint);    （有符号的 （-128-127），超过127存的还是127）

ps：修改sql_mode为严格模式，必须重启客户端才能生效
    set global sql_mode="strict_trans_tables";
    select @@sql_mode;

create table t4(x tinyint unsigned);     （无符号的（0-255））

# 强调：整型类型后面的宽度限制的根本不是存储宽度，限制的是显示宽度
create table t5(id int(1));
create table t6(id int(5));


2.浮点型：

float(255,30)
double(255,30)
decimal(65,30)

以上三种形式的浮点型

#FLOAT[(M,D)]

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

create table t8(x float(255,30));
create table t9(x double(255,30));
create table t10(x decimal(65,30));

insert into t8 values(1.111111111111111111111111111111);
insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);

建议：平时用 float就够了，虽然精确度没有下面两个高。已经足够我们平时用的

三，日期类型：

DATE TIME DATETIME TIMESTAMP YEAR 

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

year 1999
date 1999-11-11
time 08:30:00
datetime/timestamp 1999-11-11 08:30:00

create table student(
    id int primary key auto_increment,
    name char(16),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime
);
insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',now(),now(),now(),now());   （now()代表的是当前的时间。） 方法一：

insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,20001111,now(),now());                            方法二：

insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,'2000-11-11',083000,now());                        方法三：
 
insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,'2000-11-11',"08:30:00",20171111111111);          方法四：

insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,'2000-11-11',"08:30:00","2017-11-11 11:11:11");     方法五：

create table t11(x timestamp);   （timestamp不传值默认就是当前时间）

insert into t1 values();

create table t12(x datetime not null default now());

insert into t1 values();

 

建议：平时我们用timestamp就可以了


4.字符类型：

#注意：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：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
    存储：
        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）
    
    检索：
        尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

 

测试前了解两个函数
length：查看字节数
char_length:查看字符数


3.总结

#InnoDB存储引擎：建议使用VARCHAR类型
单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。

 

# 注意：宽度指限制的是字符个数
char：定长
    char(5)

varchar：变长
    varchar(5)

相同点：宽度指的都是最大存储的字符个数，超过了都无法正常存储
不同点：
    char(5):
        'm'--->'m    '5个字符

    varchar(5)
        'm'--->'m'1个字符

set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

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

注意：mysql在查询时针对where 字段="值    "会忽略掉右面的空格，即where 字段="值"
如果时like模糊匹配就不会忽略右面的空格了

char(5)
egon |axx  |lxx  |fm   |

varchar(5)
1bytes+egon|1bytes+axx|1bytes+lxx|1bytes+fm|

# 宽度相关练习
mysql> create table t13(x char(5));
Query OK, 0 rows affected (0.20 sec)

mysql> create table t14(x varchar(5));
Query OK, 0 rows affected (0.27 sec)

mysql>
mysql>
mysql> insert into t13 values('xxxxxx');
ERROR 1406 (22001): Data too long for column 'x' at row 1
mysql> insert into t14 values('xxxxxx');
ERROR 1406 (22001): Data too long for column 'x' at row 1




5，枚举与集合类型：

枚举enum('a','b','c'):多选一
集合set('a','b','c'):多选多

create table emp(
    name varchar(15),
    sex enum('male','female','unkown'),
    hobbies set('read','music','yinshi','play')
);

insert into emp values
('zhangming','xxx','xxxx');


mysql> insert into emp values('zhangming','female','read,play');  （必须写字段里面的内容）
Query OK, 1 row affected (0.03 sec)

mysql> select * from emp;
+-----------+--------+-----------+
| name      | sex    | hobbies   |
+-----------+--------+-----------+
| zhangming | female | read,play |
+-----------+--------+-----------+
1 row in set (0.00 sec)