create table 表名(

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

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

字段名3 类型[(宽度) 约束条件]

);

#类型：使用限制字段必须以什么样的数据类型传值

#约束条件：约束条件是在类型之外添加一种额外的限制

#tinyint:小整数值(1字节)  范围(有符号):(-128,127)  范围(无符号):(0,255)

create table t1(id tinyint);

insert into t1 values(-10),(128),(-129);    # id -10 127 -128

​

create table t2(id tinyint unsigned);       # 无符号的

insert into t2 values(-1),(256);            # id 0 255

​

#int:大整数值(4字节)  范围(有符号):(-2147483648,2147483647)    范围(无符号):(0,4294967295)

create table t3(x int unsigned);

insert into t3 values(4294967296);          # x 4294967295

​

create table t4(x int(12) unsigned);        #显示宽度12     #显示宽度，并非存储宽度

insert into t4 values(4294967296123);       # x 4294967295

create table t5(x int(8) unsigned zerofill);

insert into t5 values(4294967296123);       # x  4294967295

insert into t5 values(1);                  # x  00000001

# 查看sql_mode

mysql> show variables like "%sql_mode%";

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

| Variable_name              | Value               |

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

| sql_mode                  NO_ENGINE_SUBSTITUTION |

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

​

#修改sql_mode为严格模式：在该模式下，如果插入的数据超过限制，则会立即报错

mysql> set global sql_mode="strict_trans_tables";

create table t9(x float(255,30));   #255最大总数位   30最大小数位

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

create table t11(x decimal(65,30)); #65最大总数位    30最大小数位

​

insert into t9 values(1.111111111111111111111111111111);

insert into t10 values(1.111111111111111111111111111111);

insert into t11 values(1.111111111111111111111111111111);

​

mysql> select * from t9;

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

| x                                |

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

| 1.111111164093017600000000000000 |

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

​

mysql> select * from t10;

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

| x                                |

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

| 1.111111111111111200000000000000 |

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

​

mysql> select * from t11;

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

| x                                |

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

| 1.111111111111111111111111111111 |

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

create table t12(x char(4));            # 超出4个字符则报错，不够4个字符则用空格补全成4个字符

create table t13(y varchar(4));         # 超出4个字符则报错，不够4个字符那么字符有几个就存几个

​

insert into t12 values('hello');        #报错

insert into t13 values('hello');        #报错

​

insert into t12 values('a'); #'a    '   看不到空格，需要设置sql_mode

insert into t13 values('a'); #'a'

​

set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";# char_length(x)显示字段x对应的长度

select char_length(x) from t12; #4

select char_length(y) from t13; #1

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

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

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-01-27','08:30:00','2013-11-11 11:11:11');

mysql> select * from student;

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

| id   | name | born_year | birth      | class_time | reg_time            |

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

|    1 | egon |      2000 | 2000-01-27 | 08:30:00   | 2013-11-11 11:11:11 |

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

create table teacher(

    id int,

    name char(16),

    sex enum('male','female','others'),

    hobbies set('play','read','music','piao')

);

insert into teacher values(1,'egon','male','play,piao');

select * from teacher;

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

| id   | name | sex  | hobbies   |

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

|    1 | egon | male | play,piao |

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