【目录】
一 存储引擎介绍
二 表介绍
三 创建表
四 查看表结构
五 数据类型
1、介绍
2、数值类型
3、日期类型
4、字符串类型
5、枚举类型与集合类型
六 表完整性约束
1、介绍
2、not null与default
3、 unique
4、primary key
5、auto_increment
6、foreign key
七 修改表ALTER TABLE
八 复制表
九 删除表
一 、存储引擎介绍
1、什么是存储引擎
日常生活中文件格式有很多中,并且针对不同的文件格式会有对应不同存储方式和处理机制(txt,pdf,word,mp4...)
针对不同的数据应该有对应的不同的处理机制来存储
2、MySQL主要存储引擎
是MySQL5.5版本及之后默认的存储引擎
存储数据更加的安全
myisam
是MySQL5.5版本之前默认的存储引擎
速度要比Innodb更快 但是我们更加注重的是数据的安全
memory
内存引擎(数据全部存放在内存中) 断电数据丢失
blackhole
无论存什么,都立刻消失(黑洞)
3、SQL语句查看存储引擎
# 查看所有的存储引擎
show engines;
# 不同的存储引擎在存储表的时候 异同点
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;# 存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
二 、表介绍
表相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段。
三 、创建表
# 语法
create table 表名(
字段名1 类型(宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件
)# 注意
1 在同一张表中字段名不能重复2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的
约束条件写的话 也支持写多个
字段名1 类型(宽度) 约束条件1 约束条件2...,
create table t5(id); 报错3 最后一行不能有逗号
create table t6(
id int,
name char,
); 报错
"""补充"""
# 宽度
一般情况下指的是对存储数据的限制
create table t7(name char); 默认宽度是1
insert into t7 values('jason');
insert into t7 values(null); 关键字NULL
针对不同的版本会出现不同的效果
5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符,那么我会自动帮你截取
5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超,一旦超出范围立刻报错 Data too long for ....
"""严格模式到底开不开呢?"""
MySQL5.7之后的版本默认都是开启严格模式的
使用数据库的准则:
能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力# 约束条件 null not null不能插入null
create table t8(id int,name char not null);"""
宽度和约束条件到底是什么关系
宽度是用来限制数据的存储
约束条件是在宽度的基础之上增加的额外的约束
"""
四 、查看表结构
describe t1; #查看表结构,可简写为desc 表名
show create table t1G; #查看表详细结构,可加G
五 、数据类型
(一)数值类型
1、整型
-
TINYINT SMALLINT MEDUIMINT INT BIGINT
-
作用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
""" 以TINYINT 是否有符号 默认情况下是带符号的 超出会如何 超出限制只存最大可接受值 """ create table t9(id tinyint); insert into t9 values(-129),(256); # 约束条件之unsigned 无符号 create table t10(id tinyint unsigned); create table t11(id int); # int默认也是带符号的 # 整型默认情况下都是带有符号的 # 针对整型 括号内的宽度到底是干嘛的 create table t12(id int(8)); insert into t12 values(123456789); """ 特例:只有整型括号里面的数字不是表示限制位数 id int(8) 如果数字没有超出8位 那么默认用空格填充至8位 如果数字超出了8位 那么有几位就存几位(但是还是要遵守最大范围) """ create table t13(id int(8) unsigned zerofill); # 用0填充至8位 # 总结: 针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了
# 如何查看严格模式 show variables like "%mode"; 模糊匹配/查询 关键字 like %:匹配任意多个字符 _:匹配任意单个字符 # 修改严格模式 set session 只在当前窗口有效 set global 全局有效 set global sql_mode = 'STRICT_TRANS_TABLES'; 修改完之后 重新进入服务端即可
2、浮点型
FLOAT、DOUBLE、DECIMAL
作用
# 存储限制 float(255,30) # 总共255位 小数部分占30位 double(255,30) # 总共255位 小数部分占30位 decimal(65,30) # 总共65位 小数部分占30位 # 精确度验证 create table t15(id float(255,30)); create table t16(id double(255,30)); create table t17(id decimal(65,30)); """你们在前期不要给我用反向键 所有的命令全部手敲!!!增加熟练度""" insert into t15 values(1.111111111111111111111111111111); insert into t16 values(1.111111111111111111111111111111); insert into t17 values(1.111111111111111111111111111111); float < double < decimal # 要结合实际应用场景 三者都能使用
(三)其他类型
3、日期类型
date:年月日 2020-5-4
datetime:年月日时分秒 2020-5-4 11:11:11
time:时分秒11:11:11
create table student( id int, name varchar(16), born_year year, birth date, study_time time, reg_time datetime ); insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');
4、字符串类型
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
""" char 定长 char(4) 数据超过四个字符直接报错 不够四个字符空格补全 varchar 变长 varchar(4) 数据超过四个字符直接报错 不够有几个存几个 """ create table t18(name char(4)); create table t19(name varchar(4)); insert into t18 values('a'); insert into t19 values('a'); # 介绍一个小方法 char_length统计字段长度 select char_length(name) from t18; select char_length(name) from t19; """ 首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的 但是在显示的时候MySQL会自动将多余的空格剔除 """ # 再次修改sql_mode 让MySQL不要做自动剔除操作 set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
""" char 缺点:浪费空间 优点:存取都很简单 直接按照固定的字符存取数据即可 jason egon alex wusir tank 存按照五个字符存 取也直接按照五个字符取 varchar 优点:节省空间 缺点:存取较为麻烦 1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank 存的时候需要制作报头 取的时候也需要先读取报头 之后才能读取真实数据 以前基本上都是用的char 其实现在用varchar的也挺多 """ 补充: 进来公司之后你完全不需要考虑字段类型和字段名 因为产品经理给你发的邮件上已经全部指明了
5、枚举类型与集合类型
分类:
"""
枚举(enum) :多选一
集合(set): 多选多
"""
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table user( id int, name char(16), gender enum('male','female','others') ); insert into user values(1,'jason','male'); 正常 insert into user values(2,'egon','xxxxooo'); 报错 # 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储 create table teacher( id int, name char(16), gender enum('male','female','others'), hobby set('read','DBJ','hecha') ); insert into teacher values(1,'jason','male','read'); 正常 insert into teacher values(2,'egon','female','DBJ,hecha'); 正常 insert into teacher values(3,'tank','others','生蚝'); 报错 # 集合可以只写一个 但是不能写没有列举的
六 、表完整性约束
1、介绍
约束条件与数据类型的宽度一样,都是可选参数
作用:用于保证数据的完整性和一致性 主要分为:
PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
说明:
1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,
此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 #必须为正值(无符号) 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)
2、not null与default
是否可空,null表示空,非字符串 not null - 不可空 null - 可空
默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
create table tb1( nid int not null defalut 2, num int not null )
3、 unique
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
============设置唯一约束 UNIQUE=============== 方法一: create table department1( id int, name varchar(20) unique, comment varchar(100) ); 方法二: create table department2( id int, name varchar(20), comment varchar(100), constraint uk_name unique(name) ); mysql> insert into department1 values(1,'IT','技术'); Query OK, 1 row affected (0.00 sec) mysql> insert into department1 values(1,'IT','技术'); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table t1(id int not null unique); Query OK, 0 rows affected (0.02 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table service( id int primary key auto_increment, name varchar(20), host varchar(15) not null, port int not null, unique(host,port) #联合唯一 ); mysql> insert into service values -> (1,'nginx','192.168.0.10',80), -> (2,'haproxy','192.168.0.20',80), -> (3,'mysql','192.168.0.30',3306) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
4、primary key
从约束角度看primary key字段的值不为空且唯一,那我们直接使用not null+unique不就可以了吗,要它干什么?
主键primary key是innodb存储引擎组织数据的依据,innodb称之为索引组织表,一张表中必须有且只有一个主键。
一个表中可以:
单列做主键 多列做主键(复合主键)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
============单列做主键=============== #方法一: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)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(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'); ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
5、auto_increment
约束字段为自动增长,被约束的字段必须同时被key约束
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#不指定id,则自动增长 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ mysql> insert into student(name) values -> ('egon'), -> ('alex') -> ; mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | | 2 | alex | male | +----+------+------+ #也可以指定id mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+--------+ | id | name | sex | +----+------+--------+ | 1 | egon | male | | 2 | alex | male | | 4 | asb | female | | 7 | wsb | female | +----+------+--------+ #对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长 mysql> delete from student; Query OK, 4 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 8 | ysb | male | +----+------+------+ #应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.01 sec) mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | +----+------+------+ 1 row in set (0.00 sec)
6、foreign key
外键就是用来帮助我们建立表与表之间关系的
foreign key
表的关系
表与表之间最多只有四种关系
一对多关系
在MySQL的关系中没有多对一一说
一对多 多对一 都叫一对多!!!
多对多关系
一对一关系
没有关系
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
""" 判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议 换位思考 分别站在两张表的角度考虑 员工表与部门表为例 先站在员工表 思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据) 不能!!! (不能直接得出结论 一定要两张表都考虑完全) 再站在部门表 思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据) 能!!! 得出结论 员工表与部门表示单向的一对多 所以表关系就是一对多 """ foreign key 1 一对多表关系 外键字段建在多的一方 2 在创建表的时候 一定要先建被关联表 3 在录入数据的时候 也必须先录入被关联表 # SQL语句建立表关系 create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3); # 修改dep表里面的id字段 update dep set id=200 where id=2; 不行 # 删除dep表里面的数据 delete from dep; 不行 # 1 先删除教学部对应的员工数据 之后再删除部门 操作太过繁琐 # 2 真正做到数据之间有关系 更新就同步更新 删除就同步删除 """ 级联更新 >>> 同步更新 级联删除 >>> 同步删除 """ create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
""" 图书表和作者表 """ create table book( id int primary key auto_increment, title varchar(32), price int, author_id int, foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); create table author( id int primary key auto_increment, name varchar(32), age int, book_id int, foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); """ 按照上述的方式创建 一个都别想成功!!! 其实我们只是想记录书籍和作者的关系 针对多对多字段表关系 不能在两张原有的表中创建外键 需要你单独再开设一张 专门用来存储两张表数据之间的关系 """ create table book( id int primary key auto_increment, title varchar(32), price int ); create table author( id int primary key auto_increment, name varchar(32), age int ); create table book2author( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade, # 同步删除 foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade # 同步删除 );
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
""" id name age addr phone hobby email........ 如果一个表的字段特别多 每次查询又不是所有的字段都能用得到 将表一分为二 用户表 用户表 id name age 用户详情表 id addr phone hobby email........ 站在用户表 一个用户能否对应多个用户详情 不能!!! 站在详情表 一个详情能否属于多个用户 不能!!! 结论:单向的一对多都不成立 那么这个时候两者之间的表关系 就是一对一 或者没有关系(好判断) 客户表和学生表 在你们报名之前你们是客户端 报名之后是学生(期间有一些客户不会报名) """ 一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中 create table authordetail( id int primary key auto_increment, phone int, addr varchar(64) ); create table author( id int primary key auto_increment, name varchar(32), age int, authordetail_id int unique, foreign key(authordetail_id) references authordetail(id) on update cascade # 同步更新 on delete cascade # 同步删除 )
总结:
""" 表关系的建立需要用到foreign key 一对多 外键字段建在多的一方 多对多 自己开设第三张存储 一对一 建在任意一方都可以 但是推荐你建在查询频率较高的表中 判断表之间关系的方式 换位思考!!! 员工与部门 图书与作者 作者与作者详情 """
七 、修改表 ALTER TABLE
# MySQL对大小写是不敏感的 """ 1 修改表名 alter table 表名 rename 新表名; 2 增加字段 alter table 表名 add 字段名 字段类型(宽度) 约束条件; alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名; 3 删除字段 alter table 表名 drop 字段名; 4 修改字段 alter table 表名 modify 字段名 字段类型(宽度) 约束条件; alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件; """
八 、复制表
""" 我们sql语句查询的结果其实也是一张虚拟表 """ create table 表名 select * from 旧表; 不能复制主键 外键 ... create table new_dep2 select * from dep where id>3;
九 、删除表
DROP TABLE 表名;
参考资料: