MySQL-----数据类型及约束

数据类型

数据类型
#1. 数字：
    整型：tinyinit  int  bigint
    小数：
        float ：在位数比较短的情况下不精准
        double ：在位数比较长的情况下不精准
            0.000001230123123123
            存成：0.000001230000

        decimal：（如果用小数，则用推荐使用decimal）
            精准
            内部原理是以字符串形式去存

#2. 字符串：
    char（10）：简单粗暴，浪费空间，存取速度快
        root存成root000000
    varchar：精准，节省空间，存取速度慢

    sql优化：创建表时，定长的类型往前放，变长的往后放
                    比如性别           比如地址或描述信息

    >255个字符，超了就把文件路径存放到数据库中。
            比如图片，视频等找一个文件服务器，数据库中只存路径或url。



#3. 时间类型：
    最常用：datetime


#4. 枚举类型与集合类型


1.数字（默认有符号）
  int：个数 年龄  id qq号
 tinyint 小整型：支持范围
有符号：
  -128--127
无符号
    0---255
  bigint


float：
   价钱  身高  体重 余额
   float
   double
   decimal

   #测试
     create table t4(salary float(5,2));  #一共五位，小数占两位，所以整数最多只能是3位
     insert into t4 values (3.73555);
     insert into t4 values (-3.73555);
     insert into t4 values (-1111.73555);
     insert into t4 values (-111.73555);


2.字符(宽度指的是字符个数，与存储有关) char：姓名，性别，职业，地址，职称，介绍

  char  :简单粗暴，不够则凑够固定长度存放起来，浪费空间，存取速度快
  varchar：精准，计算出待存放的数据长度，节省空间，存取速度慢

    char与varchar测试()

    create table t6(name char(4));
    insert into t6 values('alexsb');
    insert into t6 values('欧德博爱');
    insert into t6 values('艾利克斯a');


     create table t7(x char(5),y varchar(5));
     #insert into t7 values('abcdef','abc');
     #insert into t7 values('abc','abc');
     #insert into t7 values('abc','abcdef');


      insert into t7 values('abc','abc'); #char_length ：查看字符的长度
      insert into t7 values('你好啊','好你妹'); #char_length ：查看字符的长度
      #了解
       insert into t7 values('你好啊','好你妹'); #length：查看字节的长度


     #注意两点：
         insert into t7 values('abc ','abc '); #length：查看字节的长度
         select * from t7 where y='abc    '; #去掉末尾的空格然后去比较

        set sql_model='pad_char_to_full_lengt'; #查看字节长度的时候用这句把不要脸的char打回原形


3.日期
create table student(
id int,
name char(5),
bron_data data,
bron_year year,
reg_time datatime,
class_time time
);

insert into student values(1,'alex',now(),now(),now(),now());
insert into student values(1,'alex','2017-09-06','2017','2017-09-06 10:39:00','08:30:00');

#了解
insert into student values(1,'alex','2017-09-06',2017,'2017-09-06 10:39:00','08:30:00');
insert into student values(1,'alex','2017/09/06',2017,'2017-09-06 10:39:00','08:30:00');
insert into student values(1,'alex','20170906',2017,'20170906103900','083000');


4 枚举与集合
create table student1(
id int primary key auto_increment,
name char(5),
sex enum('male','female'),
hobbies set('music','read','study','coding')
);

insert into student1(name,sex,hobbies) values('egon','None','asdfasdfasdf');
insert into student1(name,sex,hobbies) values('egon','male','music,read');








#foreign key  #一个表和外面的表有关系
#！！！先建被关联的表,并且被关联的字段必须唯一
create table dep(
id int primary key auto_increment,
name varchar(50),
comment varchar(100)
);

create table emp_info(
id int primary key auto_increment,
name varchar(20),
dep_id int,
constraint fk_depid_id foreign key(dep_id) references dep(id)
on delete cascade  #在删除和修改上是关联的
on update cascade
);

#先给被关联的表初始化记录
insert into dep values
(1,'欧德博爱技术有限事业部','说的好...'),
(2,'艾利克斯人力资源部','招不到人'),
(3,'销售部','卖不出东西');


insert into emp_info values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)


查询：
 和之前一样
 但是删的时候就不一样了

约束

1 not null 与default

create table student2(
id int primary key auto_increment,
name char(5),
sex enum('male','female') not null default 'female'
);

insert into student2(name) values('alex');


create table student3(
id int primary key auto_increment,
name char(5),
age int not null default 30
);
insert into student3(name) values('alex');


2 unique
#单列唯一
create table teacher(
id int not null unique,
name char(10)
);
insert into teacher values(1,'egon');
insert into teacher values(1,'alex');  id号是唯一的，如果有重复的就会出错
#多列唯一
#255.255.255.255
create table services(
id int primary key auto_increment,
name char(10),
host char(15),  #IP地址
port int,  #端口号
constraint host_port unique(host,port)  #约束后面的
);

insert into services values('ftp','192.168.20.17',8080);
insert into services values('httpd','192.168.20.17',8081);

primary key
primary key字段的值不为空且唯一
一个表中可以：
单列做主键
多列做主键（复合主键）
但一个表内只能有一个主键primary ke

============单列做主键===============
#方法一：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)
);
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'



#auto_increment_offset:偏移量
create table dep(
id int primary key auto_increment,
name char(10)
);
insert into dep(name) values('IT'),('HR'),('SALE'),('Boss');

create table dep1(
id int primary key auto_increment,
name char(10)
)auto_increment=10;
insert into dep1(name) values('IT'),('HR'),('SALE'),('Boss');


#auto_increment_increment:步长
create table dep2(
id int primary key auto_increment,
name char(10)
);
set session auto_increment_increment=2; #会话级，只对当前会话有效
set global auto_increment_increment=2; #全局，对所有的会话都有效
insert into dep1(name) values('IT'),('HR'),('SALE'),('Boss');


#auto_increment_offset:偏移量+auto_increment_increment:步长
注意：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略
set session auto_increment_offset=2;
set session auto_increment_increment=3;
show variables like '%auto_in%';  %全部的意思

create table dep3(
id int primary key auto_increment,
name char(10)
);
insert into dep3(name) values('IT'),('HR'),('SALE'),('Boss');


#auto_increment_offset:偏移量+auto_increment_increment:步长
注意：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略
set session auto_increment_offset=2;
set session auto_increment_increment=3;
show variables like '%auto_in%';

create table dep3(
id int primary key auto_increment,
name char(10)
);
insert into dep3(name) values('IT'),('HR'),('SALE'),('Boss');



#foreign key
#！！！先建被关联的表,并且被关联的字段必须唯一
create table dep(
id int primary key auto_increment,
name varchar(50),
comment varchar(100)
);

create table emp_info(
id int primary key auto_increment,
name varchar(20),
dep_id int,
constraint fk_depid_id foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);

#先给被关联的表初始化记录
insert into dep values
(1,'欧德博爱技术有限事业部','说的好...'),
(2,'艾利克斯人力资源部','招不到人'),
(3,'销售部','卖不出东西');


insert into emp_info values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)
;