内容大纲
1、创建表的完整语法
2、基本数据类型
整型
浮点型
字符类型
日期类型
枚举与集合
3、约束条件
primary key
unique key
not null
foreign key
一、创建表的完整语法
# 语法
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)
总结：
单列唯一 既 单个字段下的记录不能重复
联合唯一 既 多个字段下的记录不能同时重复
prmary key + auto_increment
prmary 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 tb1；
作用：将整张表重置,id重新从0开始记录
浙公网安备 33010602011771号