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

数据库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;

1730001-20190831131416358-83975034.png

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要多。

1730001-20190831131445778-1322266327.png

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)
;

转载于:https://www.cnblogs.com/liubing8/p/11438672.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值