数据库02 /MySQL基础数据类型以及多表之间建立联系
1.MySQL常用数据类型预览
1. 数字：
整型：tinyinit int bigint
小数：
# float ：在位数比较短的情况下不精准
# double ：在位数比较长的情况下不精准
# decimal：（如果用小数，则用推荐使用decimal）
# 精准、内部原理是以字符串形式去存
2. 字符串：
char（10）：简单粗暴，浪费空间，存取速度快
# root存成root000000
varchar：精准，节省空间，存取速度慢
# sql优化：创建表时，定长的类型往前放，变长的往后放
# 比如性别 比如地址或描述信息
# >255个字符，超了就把文件路径存放到数据库中。
# 比如图片，视频等找一个文件服务器，数据库中只存路径或url。
3. 时间类型：
# 最常用：datetime
4. 枚举类型与集合类型
2.数值类型
2.1 整数类型
整数类型：tinyint、smallint、mediumint、int、bigint
插入超出范围的数字会储存为能达到的最大数字，（默认为宽松模式）
作用：存储年龄，等级，id，各种号码等
1.tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
#有符号范围：-128 ～ 127
#无符号范围：0 ～ 255
2.int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
#有符号：-2147483648 ～ 2147483647
#无符号：0 ～ 4294967295
3.bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
#有符号：-9223372036854775808 ～ 9223372036854775807
#无符号：0 ～ 18446744073709551615
unsigned---约束无符号
整型类型测试
#mysql> create table t1(money tinyint); #注意,创建表时最后一个字段后面不能有逗号,错误写法mysql> create table t1(money tinyint,);
#mysql> insert into t1 values(11);
#Query OK, 1 row affected (0.28 sec)
#mysql> select * from t1;
#mysql> insert into t1 values(-10);
#Query OK, 1 row affected (0.11 sec)
#mysql> select * from t1;
#mysql> insert into t1 values(-200);
#Query OK, 1 row affected, 1 warning (0.10 sec)
#mysql> select * from t1;
设置了数据类型,会有一个约束效果,只能表示自己范围内的数
创建无符号数值类型的写法:
mysql> create table t2(id tinyint unsigned);
2.2浮点型
浮点型分类：float、double、decimal
作用：存储薪资、身高、温度、体重、体质参数等
1.FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
#定义：单精度浮点数（非准确小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值为255，d最大值为30，例如：float(255,30)/小数点不占位
#有符号： -3.402823466E+38 to -1.175494351E-38,
# 1.175494351E-38 to 3.402823466E+38
#无符号：1.175494351E-38 to 3.402823466E+38
#精确度：**** 随着小数的增多，精度变得不准确 ****
2.DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
#定义： 双精度浮点数（非准确小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值也为255，d最大值也为30
#有符号：-1.7976931348623157E+308 to -2.2250738585072014E-308
# 2.2250738585072014E-308 to 1.7976931348623157E+308
#无符号：2.2250738585072014E-308 to 1.7976931348623157E+308
#精确度：****随着小数的增多，精度比float要高，但也会变得不准确 ****
3.decimal[(m[,d])] [unsigned] [zerofill]
#定义：准确的小数值，m是整数部分+小数部分的总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。比float和double的整数个数少，但是小数位数都是30位
#精确度： **** 随着小数的增多，精度始终准确 ****
# 对于精确数值计算时需要用此类型
# decimal能够存储精确值的原因在于其内部按照字符串存储。
精度从高到低：decimal、double、float
decimal精度高，但是整数位数少
float和double精度低，但是整数位数多
#float已经满足绝大多数的场景了，但是什么导弹、航线等要求精度非常高，所以还是需要按照业务场景自行选择，如果又要精度高又要整数位数多，那么你可以直接用字符串来存。
浮点型测试
#mysql> create table t3(id float(60,30));
#Query OK, 0 rows affected (1.70 sec)
#mysql> create table t4(id double(60,30));
#Query OK, 0 rows affected (0.88 sec)
#mysql> create table t5(id decimal(60,30));
#Query OK, 0 rows affected (0.96 sec)
#mysql> insert into t3 values(1.1111111111111111111111);
#Query OK, 1 row affected (0.13 sec)
#mysql> insert into t4 values(1.1111111111111111111111);
#Query OK, 1 row affected (0.22 sec)
#mysql> insert into t5 values(1.1111111111111111111111);
#Query OK, 1 row affected (0.09 sec)
#mysql> select * from t3;
#mysql> select * from t4;
#mysql> select * from t5;
3.日期类型
类型：DATE，TIME，DATETIME ，IMESTAMP，YEAR
作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等
#1.YEAR：YYYY（范围：1901/2155）2018
#2.DATE：YYYY-MM-DD（范围：1000-01-01/9999-12-31）例：2018-01-01
#3.TIME：HH:MM:SS（范围：'-838:59:59'/'838:59:59'）例：12:09:32
#4.DATETIME：YYYY-MM-DD HH:MM:SS（范围：1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
#5.TIMESTAMP：YYYYMMDD HHMMSS（范围：1970-01-01 00:00:00/2037 年某时）
#（由于日期马上到了，现在基本不怎么使用了）
日期类型测试：
#mysql> create table t6(d1 year ,d2 date,d3 datetime);
#Query OK, 0 rows affected (1.75 sec)
#mysql> insert into t6 values(now(),now(),now());
#Query OK, 1 row affected, 1 warning (0.12 sec)
#mysql> select * from t6;
4.字符串类型
类型：char，varchar
作用：名字，信息等等
注意：char和varchar括号内的参数指的都是字符的长度（5.6版本以前指的是字节，5.6版本以前指的是字符）
1.char类型：定长，简单粗暴，浪费空间，存取速度快
#字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
#存储：存储char类型的值时，会往右填充空格来满足长度
#例如：指定长度为10，存>10个字符则报错(严格模式下)，存<10个字符则用空格填充直到凑够10个字符存储
#检索： 在检索或者说查询时，查出的结果会自动删除尾部的空格，如果你想看到它补全空格之后的内容，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';）
2.varchar类型：变长，精准，节省空间，存取速度慢
#字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8）
#存储：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）
#检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
varchar在存数据的时候，会在每个数据前面加上一个头，这个头是1-2个bytes的数据，这个数据指的是后面跟着的这个数据的长度，1bytes能表示2**8=256，两个bytes表示2**16=65536，能表示0-65535的数字，所以varchar在存储的时候是这样的：1bytes+sb+1bytes+ssb1+1bytes+ssbb2，所以存的时候会比较麻烦，导致效率比char慢，取的时候也慢，先拿长度，再取数据。
优点：节省了一些硬盘空间，一个acsii码的字符用一个bytes长度就能表示，但是也并不一定比char省，看一下官网给出的一个表格对比数据，当你存的数据正好是你规定的字段长度的时候，varchar反而占用的空间比char要多。
5.MySQL的sql_mode模式说明以及设置
注意：一定要用严格模式
#sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题.
严格模式设置和修改(以解决上述问题为例)：
方式一：先执行select @@sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';，例如：set session sql_mode='STRICT_TRANS_TABLES';改为严格模式 #session可以不用写
此方法只在当前会话中生效，关闭当前会话就不生效了。
方式二：先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set global sql_mode = '修改后的值'。
此方法在当前服务中生效，重新MySQL服务后失效
方法三：在mysql的安装目录下，或my.cnf文件(windows系统是my.ini文件)，新增 sql_mode = STRICT_TRANS_TABLES
添加my.cnf如下：
[mysqld] sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
5.1 MySQL的sql_mode的四种模式，宽松模式、严格模式
mysql支持的sql_mode模式：ANSI、TRADITIONAL、STRICT_ALL_TABLES和STRICT_TRANS_TABLES。
ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
#TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。
#STRICT_ALL_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。
6.枚举和集合类型
enum中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。enum只允许从值集合中选取单个值，而不能一次取多个值。如性别 sex 男male/女female
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
set和enum非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。如爱好1,爱好2,爱好3...
A SET column can have a maximum of 64 distinct members.
测试：
#mysql> create table consumer(
# -> name varchar(50),
# -> sex enum('male','female'),
# -> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一
# -> hobby set('play','music','read','study') #在指定范围内，多选多
# -> );
#
# mysql> insert into consumer values
# -> ('xiaogui','male','vip5','read,study'),
# -> ('taibai','female','vip1','girl');（在严格模式下会报错）
#
# mysql> select * from consumer;
# +------+--------+-------+------------+
# | name | sex | level | hobby |
# +------+--------+-------+------------+
# | xiaogui | male | vip5 | read,study |
# | taibai | female | vip1 | |
# +------+--------+-------+------------+
错误示范:
create table t5(sex char(16) enum('male','female') default 'male');
正确示范：
create table t5(sex enum('male','female') default 'male');
注意：枚举前面的字段不需要加属性
7.完整性约束
7.1 not null 不为空
create table t1(name char(10) not null);
==================not null====================
#mysql> create table t1(id int); #id字段默认可以插入空
#mysql> desc t1;
#+-------+---------+------+-----+---------+-------+
#| Field | Type | Null | Key | Default | Extra |
#+-------+---------+------+-----+---------+-------+
#| id | int(11) | YES | | NULL | |
#+-------+---------+------+-----+---------+-------+
#mysql> insert into t1 values(); #可以插入空
#mysql> create table t2(id int not null); #设置字段id不为空
#mysql> desc t2;
#+-------+---------+------+-----+---------+-------+
#| Field | Type | Null | Key | Default | Extra |
#+-------+---------+------+-----+---------+-------+
#| id | int(11) | NO | | NULL | |
#+-------+---------+------+-----+---------+-------+
#mysql> insert into t2 values(); #不能插入空
#ERROR 1364 (HY000): Field 'id' doesn't have a default value
7.2 default 默认值
create table t1(name char(10) not null default 'xx');
==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
#mysql> create table t3(id int default 1);
#mysql> alter table t3 modify id int not null default 1;
7.3 unique #唯一,字段数据不能重复
create table t1(name char(10) unique);
============设置唯一约束 UNIQUE===============
#create table department1(
#id int,
#name varchar(20) unique,
#comment varchar(100)
#);
============设置联合唯一约束 UNIQUE===============
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);
7.4 primary key #主键 约束效果:不为空且唯一 not null + unique
create table t1(id int primary key);
constraint pk_name primary key(id); #创建主键并为其命名pk_name
7.5 联合主键
和联合唯一是类似的， 看key，两个都写的是pri，两个联合起来作为主键，他们两个作为一个主键，不能再有其他的主键了，也就是在创建表的时候，只能出现一次primary key方法。
# mysql> create table t10(
# ->id int,
# ->port int,
# ->primary key(id,port)
# -> );
# Query OK, 0 rows affected (0.45 sec)
#
# mysql> desc t10;
# +-------+---------+------+-----+---------+-------+
# | Field | Type | Null | Key | Default | Extra |
# +-------+---------+------+-----+---------+-------+
# | id | int(11) | NO | PRI | 0 | |
# | port | int(11) | NO | PRI | 0 | |
# +-------+---------+------+-----+---------+-------+
# 2 rows in set (0.10 sec)
7.5 auto_increment 自增
#create table t1(id int primary key auto_increment);
前面必须是个key
在创建完表后，修改自增字段的起始值
mysql> alter table student auto_increment=3;
#mysql> create table student(
# -> id int primary key auto_increment,
# -> name varchar(20),
# -> sex enum('male','female') default 'male'
# -> );
#
注意：default后面不用加括号
#mysql> alter table student auto_increment=3;
#
#mysql> show create table student;
#.......
#ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
#
#mysql> insert into student(name) values('egon');
#Query OK, 1 row affected (0.01 sec)
#
#mysql> select * from student;
#+----+------+------+
#| id | name | sex |
#+----+------+------+
#| 3 | egon | male |
#+----+------+------+
#row in set (0.00 sec)
#
#mysql> show create table student;
#.......
#ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
#create table student(
#id int primary key auto_increment,
#name varchar(20),
#sex enum('male','female') default 'male'
#)auto_increment=3;
设置步长
#sqlserver：自增步长
基于表级别
# create table t1（
# id int。。。
# ）engine=innodb,auto_increment=2 步长=2 default charset=utf8
mysql自增的步长：
# show session variables like 'auto_inc%';
基于会话级别
# set session auto_increment_increment=2 #修改会话级别的步长
基于全局级别的
# set global auto_increment_increment=2 #修改全局级别的步长（所有会话都生效）
7.6 foreign key #外键 建立表关系使用的约束条件
7.6.1 多对一（一对多）
foreign key（外键其实就是标明表和表之间的关系）
#一对多或称为多对一
三张表：出版社，作者信息，书
一对多（或多对一）：一个出版社可以出版多本书
关联方式：foreign key
=====================多对一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;
总结：
1、先要建立被关联的表才能建立关联表
2、在插入数据记录的时候，要先想被关联表中插入数据，才能往关联表里面插入数据
3、更新或者删除数据的时候，都需要考虑关联表和被关联表的关系
解决方案：
a.删除表的时候，先删除关联表，再删除被关联表
b.重建表的时候，在加外键关联的时候加上这两句：on delete cascade 和 on update cascade
7.6.2 一对一
#一对一
两张表：学生表和客户表
一对一：一个学生是一个客户
关联方式：foreign key+unique
=====================一对一=====================
create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> );
create table student(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique, #该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
-> on delete cascade
-> on update cascade
-> );
#增加客户
mysql> insert into customer(name,qq,phone) values
-> ('韩蕾','31811231',13811341220),
-> ('杨澜','123123123',15213146809),
-> ('翁惠天','283818181',1867141331),
-> ('杨宗河','283818181',1851143312),
-> ('袁承明','888818181',1861243314),
-> ('袁清','112312312',18811431230)
mysql> #增加学生
mysql> insert into student(class_name,customer_id) values
-> ('脱产1班',3),
-> ('周末1期',4),
-> ('周末1期',5)
-> ;
7.6.3 多对多
三张表：出版社，作者信息，书
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表
=====================多对多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
3738
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
