mysql中创建表的语法、基本数据类型,约束条件
目录
一、创建表的完整语法
# 语法
[中括号内为可选参数]
create table 表名(
字段名1 类型[(宽度)] [约束条件], # 这里的类型既:限制 字段1 必须以 XX类型 来存储记录
字段名2 类型[(宽度)] [约束条件],
字段名3 类型[(宽度)] [约束条件]
)
代码示例:
create table file(
id int unsigned, # 未设置宽度,设置了约束条件
sex char(6), # 设置了宽度,未设置约束条件
age int(3) unsigned, # 宽度 和 约束条件都设置了
hobby char # 宽度 和 约束条件 都未设置
)
# 注意!
1、在同一张表中,字段名不能相同
2、宽度 和 约束条件为可选参数,字段名 和 字段名下记录的类型 是必须的
3、最后一个字段后不能加逗号
# 补充
1)宽度指的是对存储数据的限制(除整型外)
mysql> create table userinfo (name char);
Query OK, 0 rows affected (0.04 sec)
mysql> desc userinfo; # 从表中可以看出 char类型默认存储长度为 1
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert userinfo values ('egon'); # 超过 1 位存储长度则报错
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert userinfo values ('e'); # 可以存储1位长度的记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from userinfo; # 查看记录
+------+
| name |
+------+
| e |
+------+
1 row in set (0.00 sec)
# MySQL 3.7及以上版本都为严格模式,故超过可存储数据长度则会报错
2)约束条件
create table t1(id int,name char not null); # not null 既 对字段name的约束(不能为null)
insert into t1 values(1,'j'); # 正常存储
insert into t1 values(2,null); # 报错
# 总结:
类型是用来限制 字段 必须以何种数据类型来存储记录
类型其实也是对字段的约束(约束字段下的记录必须为XX类型)
类型后写的 约束条件 是在类型之外的 额外添加的约束
-
设置严格模式
我们刚刚在上面设置了char,tinyint,存储数据时超过它们的最大存储长度,发现数据也能正常存储进去,只是mysql帮我们自动截取了最大长度。但在实际情况下,我们应该尽量减少数据库的操作,缓解数据库的压力,让它仅仅只管理数据即可,这样的情况下就需要设置安全模式
show variables like "%mode%"; # 查看数据库配置中变量名包含mode的配置参数
# 修改安全模式
set session # 只在当前操作界面有效
set global # 全局有效
set global sql_mode ='STRICT_TRANS_TABLES'
# 修改完之后退出当前客户端重新登陆即可
二、基本数据类型
-
整型
分类: TINYINT SMALLINT MEDIUMINT INT BIGINT tinyint smallint mediumint int bigint 作用: 存储各种年龄,等级,id,号码等数据 类型存储范围:参考图片https://images2017.cnblogs.com/blog/1036857/201708/1036857-20170801181433755-146301178.png
验证整型字段有无符号及范围
测试环境皆为严格模式
mysql> create table t1 (x tinyint); Query OK, 0 rows affected (0.05 sec) mysql> insert into t1 values (128); # 不在范围内,报错 ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t1 values (127); # 在范围内,成功 Query OK, 1 row affected (0.03 sec) mysql> insert into t1 values (-129); # 不在范围内,报错 ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t1 values (-128); # 在范围内,成功 Query OK, 1 row affected (0.00 sec) mysql> select * from t1; # 查看表内记录 +------+ | x | +------+ | 127 | | -128 | +------+ 2 rows in set (0.00 sec) # tinyint的范围:(-128,127)
mysql> create table t2 (x tinyint unsigned); # 约束条件unsigned限制字段不可存储带符号的记录,既原来的(-128,0)区间的数就没了,这样范围总数就变成了(0,255) Query OK, 0 rows affected (0.04 sec) mysql> insert into t2 values (-1); # 不在范围内,报错 ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t2 values (0); # 在范围内,成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values (256); # 不在范围内,报错 ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t2 values (255); # 在范围内,成功 Query OK, 1 row affected (0.00 sec) mysql> select * from t2; # 查看表内记录 +------+ | x | +------+ | 255 | | 0 | +------+ 2 rows in set (0.00 sec) # tinyint unsigned 的范围:(0,255) 包括0的
mysql> create table t3(x int unsigned); # Query OK, 0 rows affected (0.05 sec) mysql> insert into t3 values(4294967296); # 不在范围内,报错 ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t3 values(4294967295); # 在范围内,成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t3 values(-1); # 不在范围内,报错 ERROR 1264 (22003): Out of range value for column 'x' at row 1 mysql> insert into t3 values(0); # 在范围内,成功 Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +------------+ | x | +------------+ | 4294967295 | | 0 | +------------+ 2 rows in set (0.00 sec) # int类型有符号范围为(-1247483648,1247483647) | 无符号范围为(0,4294967295) # 结论:整型默认都是有符号的
疑问:类型后面的宽度能否改变字段存储的大小限制
mysql> create table t4(x int(8)); # 宽度为8 Query OK, 0 rows affected (0.04 sec) mysql> insert into t4 values(1000000000); # 10位数 可以存储 Query OK, 1 row affected (0.00 sec) # 显示时,不够8位用0填充,如果超出8位则正常显示 mysql> create table t5(x int(8) unsigned zerofill); # 约束条件zerofill 既 用 0 填充 Query OK, 0 rows affected (0.04 sec) mysql> insert into t5 values(10000); # 5位数 Query OK, 1 row affected (0.00 sec) mysql> select * from t4,t5; +------------+----------+ | x | x | +------------+----------+ | 1000000000 | 00010000 | # 超过8位正常显示,不超过则用 0 填充 +------------+----------+ 1 row in set (0.00 sec) 强调:对于整型来说,数据类型后的宽度并不是存储限制,而是显示限制,所以在创建表时,如果字段采用的是整型类型,完全无需指定显示宽度, 默认的显示宽度,足够显示完整当初存放的数据
-
浮点型
分类: FLOAT DOUBLE decimal float double decimal 作用:记录身高,体重,薪资等数据 字段限制特点:(5,3)前一位表示所有的位数,后一位表示小数个数 # 存储限制 float(255,30) double(255,30) decimal(65,30) # 精确度验证 mysql> create table t6(x float(255,30)); Query OK, 0 rows affected (0.04 sec) mysql> create table t7(x double(255,30)); Query OK, 0 rows affected (0.05 sec) mysql> create table t8(x decimal(65,30)); Query OK, 0 rows affected (0.06 sec) mysql> mysql> insert into t6 values(1.111111111111111111111111111111); Query OK, 1 row affected (0.00 sec) mysql> insert into t7 values(1.111111111111111111111111111111); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values(1.111111111111111111111111111111); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from t6; +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | # float 精度最低 +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t7; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | # double 精度第二 +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t8; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111111111111111 | # decimal 精度最高 +----------------------------------+ 1 row in set (0.00 sec) # 由低至高 精度排名:float < double < decimal
-
字符类型
分类: char(定长) varchar(变长) 作用:姓名,地址,描述类信息
测试代码:测试char | varchar 区别
mysql> create table t9(name char(4)); # 超出四个字符报错,不够四个 就用字符空格补全 Query OK, 0 rows affected (0.04 sec) mysql> create table t10(name varchar(4)); # 超出四个字符报错,不够四个有几个就存几个 Query OK, 0 rows affected (0.04 sec) mysql> insert into t9 values('hello'); # 验证存储限制 ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> insert into t10 values('hello'); # 验证存储限制 ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> insert into t9 values('a'); # 'a ' # 补了3个空格 Query OK, 1 row affected (0.00 sec) # 'a' mysql> insert into t10 values('a'); Query OK, 1 row affected (0.00 sec) mysql> select * from t9,t10; # 无法查看真正的结果 +------+------+ | name | name | +------+------+ | a | a | +------+------+ 1 row in set (0.00 sec) mysql> select char_length(name) from t9; # 仍然无法查看到真正的结果 +-------------------+ | char_length(name) | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) mysql> select char_length(name) from t10; # 仍然无法查看到真正的结果 +-------------------+ | char_length(name) | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) # -----------------以下不是测试代码----------------- """首先应该肯定的是在硬盘上存的绝对是真正的数据,但显示的时候mysql会自动将末尾的空格取掉""" # 如果不想让mysql帮你做自动去除末尾空格的操作,需要再添加一个模式 set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; # 退出客户端重新登陆 select char_length(x) from t12; # 4 select char_length(y) from t13; # 1 # 针对char类型,mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格
char与varchar的使用区别
name char(5) # 缺点:浪费空间 # 优点:存取速度都快 egon alex lxx jxx txx ==> egon_alex_lxx__jxx__txx__ # 以空格补全成5个字符,然后读取时一次性读5个,并去除多去的空格 name varchar(5) # 缺点:存取速度慢 # 优点:节省空间 1bytes+egon 1bytes+alex 1bytes+lxx 1bytes+jxx 1bytes+txx # 存储时真实数据前会带有一个标识真实数据长度的表头,读取数据时,先读取报头,再读取根据报头得到的长度去读取真实数据
-
时间类型
分类: date:2019-05-01 time:11:11:11 Datetime:2019-01-02 11:11:11 Year:2019
测试代码
mysql> create table student( -> id int, -> name char(16), -> born_year year, -> birth date, -> study_time time, -> reg_time datetime -> ); Query OK, 0 rows affected (0.05 sec) mysql> insert into student values(1,'egon','2019','2019-05-09','11:11:00','2019-11-11 11:11:11'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+------+-----------+------------+------------+---------------------+ | id | name | born_year | birth | study_time | reg_time | +------+------+-----------+------------+------------+---------------------+ | 1 | egon | 2019 | 2019-05-09 | 11:11:00 | 2019-11-11 11:11:11 | +------+------+-----------+------------+------------+---------------------+ 1 row in set (0.00 sec)
-
枚举与集合类型
分类: 枚举 enum # 多选一 集合 set # 多选多
测试代码
# 枚举 mysql> create table user( -> id int, -> name char(16), -> gender enum('male','female','others') # 枚举,一次只能在枚举范围中选一个 -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into user values(1,'jason','xxx'); # 不可以选枚举范围外的 ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> insert into user values(2,'egon','female,others'); # 不可以选多个 ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> insert into user values(2,'egon','female'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +------+------+--------+ | id | name | gender | +------+------+--------+ | 2 | egon | female | +------+------+--------+ 1 row in set (0.00 sec) # 集合 mysql> create table teacher( -> id int, -> name char(16), -> gender enum('male','female','others'), -> hobby set('read','sleep','sanna','dbj') # 集合,一次可以从集合范围中选多个 -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into teacher values(1,'egon','male','read,sleep,dbj'); # 可以选多个 Query OK, 1 row affected (0.00 sec) mysql> insert into teacher values(1,'egon','male','read'); # 也可以只选一个 Query OK, 1 row affected (0.00 sec) mysql> select * from teacher; +------+------+--------+----------------+ | id | name | gender | hobby | +------+------+--------+----------------+ | 1 | egon | male | read,sleep,dbj | | 1 | egon | male | read | +------+------+--------+----------------+ 2 rows in set (0.00 sec)
三、约束条件
NOT NULL 标识该字段不能为空
DEFAULT 为该字段设置默认值
PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
UNSIGNED 无符号
ZEROFILL 使用0填充
-
not null+default(字段不能为空+为字段设置默认值)
mysql> create table user( -> id int, -> name char(16) # 没有其他约束条件 -> ); Query OK, 0 rows affected (0.05 sec) mysql> insert into user values(1,null); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +------+------+ | id | name | +------+------+ | 1 | NULL | # 可以写入NULL记录 +------+------+ 1 row in set (0.00 sec) mysql> delete from user where id=1; # 严格模式下修改字段需要字段下的记录都合法,所以要删除原违法记录 Query OK, 1 row affected (0.00 sec) mysql> alter table user modify name char(16) not null; # 修改字段,加上不可为空的约束 Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into user (name,id) values (null,2); # 指定传入字段,如是先传name,再传id,name加了约束条件,不能为NULL,故报错 ERROR 1048 (23000): Column 'name' cannot be null mysql> insert into user (name,id) values ('egon',2); # 合法值传入,成功 Query OK, 1 row affected (0.00 sec) mysql> select * from user; +------+------+ | id | name | +------+------+ | 2 | egon | +------+------+ 1 row in set (0.00 sec) # not null 与 defult 与 枚举/集合 联用 mysql> create table student( -> id int, -> name char(16) not null, # 约束了name not null -> gender enum('male','female','others') default 'male' # 指定了枚举范围,以及默认值 -> ); Query OK, 0 rows affected (0.11 sec) mysql> insert into student(id,name) values(1,'jason'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +------+-------+--------+ | id | name | gender | +------+-------+--------+ | 1 | jason | male | +------+-------+--------+ 1 row in set (0.00 sec)
-
unique
# 单列唯一 mysql> create table user1( -> id int unique, # 约束 该字段不可以有重复记录 -> name char(16) -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into user1 values(1,'jason'),(1,'egon'); ERROR 1062 (23000): Duplicate entry '1' for key 'id' mysql> insert into user1 values(1,'jason'),(2,'jason'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from user1; +------+-------+ | id | name | +------+-------+ | 1 | jason | | 2 | jason | +------+-------+ 2 rows in set (0.00 sec) # 联合唯一 mysql> create table server( -> id int, -> ip char(16), -> port int, -> unique(ip,port) -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into server values(1,'127.0.0.1',8080); Query OK, 1 row affected (0.00 sec) mysql> insert into server values(2,'127.0.0.1',8080); # 报错,因为ip与port同时与上已存在的记录相同了 ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'ip' mysql> insert into server values(1,'127.0.0.0',8080); # ip不同,port相同是合法的 Query OK, 1 row affected (0.00 sec) mysql> insert into server values(1,'127.0.0.1',8081); # ip相同,port不同是合法的 Query OK, 1 row affected (0.00 sec) 总结: 单列唯一 既 单个字段下的记录不能重复 联合唯一 既 多个字段下的记录不能同时重复
primary key + auto_increment
primary key: 标识该字段为该表的主键,可以唯一的标识记录 auto_increment: 标识该字段的值自动增长(整数类型,而且为主键)
# 单从约束角度来说primary key就等价于not null unique # ============单列做主键=============== # 方法一:not null+unique create table department1( id int not null unique, # 主键 name varchar(20) not null unique, comment varchar(100) ); mysql> desc department1; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | UNI | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec) # 方法二:在某一个字段后用primary key create table department2( id int primary key, # 主键 name varchar(20), comment varchar(100) ); mysql> desc department2; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.00 sec) # 方法三:在所有字段后单独定义primary key create table department3( id int, name varchar(20), comment varchar(100), constraint pk_name primary key(id); # 创建主键并为其命名pk_name mysql> desc department3; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec)
# ==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(ip,port) # ip和port作为主键==>ip和port,不可以为空,不可重复 ); mysql> desc service; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | NULL | | | port | char(5) | NO | PRI | NULL | | | service_name | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into service values -> ('172.16.45.10','3306','mysqld'), -> ('172.16.45.11','3306','mariadb') -> ; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into service values ('172.16.45.10','3306','nginx'); # ip和port与第一条记录重复了,故报错 ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
补充
delete from tb1; 强调:上面的这条命令确实可以将表里的所有记录都删掉,但不会将id重置为0, 所以收该条命令根本不是用来清空表的,delete是用来删除表中某一些符合条件的记录 delete from tb1 where id > 10; 如果要清空表,使用truncate table 表名; 作用:将整张表重置,id重新从0开始记录