目录

数据库02 /MySQL基础数据类型以及多表之间建立联系

1.MySQL常用数据类型预览

1. 数字：    整型：tinyinit  int  bigint    小数：#        float ：在位数比较短的情况下不精准#        double ：在位数比较长的情况下不精准#        decimal：（如果用小数，则用推荐使用decimal）#            精准、内部原理是以字符串形式去存2. 字符串：    char（10）：简单粗暴，浪费空间，存取速度快#        root存成root000000    varchar：精准，节省空间，存取速度慢#    sql优化：创建表时，定长的类型往前放，变长的往后放#                   比如性别           比如地址或描述信息#    >255个字符，超了就把文件路径存放到数据库中。#            比如图片，视频等找一个文件服务器，数据库中只存路径或url。3. 时间类型：#    最常用：datetime4. 枚举类型与集合类型

2.数值类型

2.1 整数类型

整数类型：tinyint、smallint、mediumint、int、bigint插入超出范围的数字会储存为能达到的最大数字，（默认为宽松模式）作用：存储年龄，等级，id，各种号码等1.tinyint[(m)] [unsigned] [zerofill]   小整数，数据类型用于保存一些范围的整数数值范围：  #有符号范围：-128 ～ 127  #无符号范围：0 ～ 2552.int[(m)][unsigned][zerofill]  整数，数据类型用于保存一些范围的整数数值范围：  #有符号：-2147483648 ～ 2147483647  #无符号：0 ～ 42949672953.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/女femaleAn 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 cascadeon 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 cascadeon update cascade,constraint fk_book foreign key(book_id) references book(id)on delete cascadeon 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);