mysql之表操作

一、存储引擎

1、什么是存储引擎

存储引擎就是表的类型 

 

2、查看MySQL支持的存储引擎

show engines;

| InnoDB      | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES

 

MEMORY   临时存放数据用的,只有表结构，没有表数据

 

BLACKHOLE 放一条数据就没了，可以放垃圾数据,也是只有表结构

 

我们常用的是innodb

 

3、指定表类型/存储引擎

create table t1(id int)engine=innodb;

create table t2(id int)engine=memory;  

create table t3(id int)engine=blackhole;

create table t4(id int)engine=myisam;

二、表的数据类型

一、数值类型

1、整数型

create table t1(x tinyint); # 超出范围报错

create table t2(x tinyint unsigned); # 无符号

create table t3(id int(1) unsigned); # 整型类型后面的宽度不是他的存储宽度，整形类型的存储宽度都已经固定死了，而是显示宽度，除了int其他的数据类型都是指定的存储跨度

create table t5(id int(5) unsigned zerofill); # 不够5个就用0填充，超过了就正常显示，所以说认为的增加宽度没有意义

# int(11)，tinyint(1)，bigint(20)，后面的数字，不代表占用空间容量。而代表最小显示位数。这个东西基本没有意义，除非你对字段指定zerofill。

create table t6(id int unsigned); # 默认宽度是10(4个字节无符号所能显示的最多数字)
create table t7(id int); # 默认宽度是11，因为去掉了符号

2、浮点型

FLOAT(M,D) [ZEROFILL]

定义： 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

DOUBLE(M,D) [ZEROFILL]

定义：双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

decimal(m[,d]) [zerofill]

定义：准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。decimal底层原理是用字符串实现的，所以准确。

精准度排名，decimal > double > float

 

显示1.111……(小数点后30个1)

float：1.111111164093017600000000000000

double：1.111111111111111200000000000000

decimal：1.111111111111111111111111111111

总结：float 和 double数值范围大，精度低，decimal精度高，数值范围小。通常float就已经够用了，造原子弹的话得用decimal。

 

 

2、日期类型

插入日期的两种方式

方式一：

create table student(
id int,
name char(6),
born_year year,
birth_date date,
class_time time,
reg_time datetime);
insert students values
(1,'egon',now(),now(),now(),now() )

方式二：

create table student(
id int,
name char(6),
born_year year,
birth_date date,
class_time time,
reg_time datetime);
insert students values
(2,'alex',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11");

datetime与timestamp的区别

MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。

1.DATETIME的日期范围是1001—9999年，TIMESTAMP的时间范围是1970—2038年。

2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

 

3、字符串类型

char： # 定长   不够的用空格补
varchar： # 变长 传几个就显示几个
create table t13(name char(5));
create table t14(name varchar(5));
insert t13 values('李杰 ');  # "李杰   "
insert t14 values('李杰 ');  # "李杰 "
select char_length(name) from t13;   # 2  mysql取的时候会把空格默认去掉,但是只去末尾的空格。

# mysql在存char类型的时候，会补全空格存进来，但是取的时候会把末尾的空格去掉，是末尾的空格
select char_length(name) from t13;   # 2
select char_length(name) from t14;   # 3

# 先出原形前的like语句
select name from t13 where name like '李杰';  # 查得到
select name from t13 where name like '李杰   ';  # 查不到

# ------------- 分割线  -------------------

SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';  # 让char现出原形

# 现出原形后的长度
select char_length(name) from t13;   # 5
select char_length(name) from t14;   # 3

# like也不去空格
select name from t13 where name like '李杰';  # 查不到
select name from t13 where name like '李杰   ';  # 精准的补全3个空格才能查到

# 下面的查找都能找到
select name from t13 where name='李杰';
select name from t13 where name='李杰         ';

# 下面的查找就找不到了，因为只去末尾的空格，不去前面和中间的
select name from t13 where name='    李杰';
select name from t13 where name='    李      杰';

原理

name char(5)

egon |alex |wxx |

name varchar(5)

1bytes+egon|1bytes+alex|1bytes+wxx bytes是存长度的

 

节省空间的不同

如果传的值大部分不足指定的存储长度，用varchar类型更省空间；如果正好是指定的存储长度，用char更省空间，因为varchar会视情况，用专门的bytes去储存数据长度,最少1bytes,最多2bytes。

 

速度的不同

char存取速度更快，因为是固定长度的存，不用考虑别的；varchar的存取速度慢，因为都要先存取头，在存取数据。

 

注意

在数据库中不会要存太长的数据。当有人请求数据的时候，数据库这台机器应该给请求者一个地址，这个地址指向数据的具体内容，而这个具体内容在其他服务器上，这样能大大减少数据库的压力。

 

总结

大部分场景还是要用char类型，因为现在存储空间已经不是一种限制了，我们要追求存取性能。如果是一些和查询无关的数据，就是存完以后很少去查询的，可以用varchar。定长的往前方，变长的往后放，两者不要混着用。

 

4、枚举类型和集合类型

enum：枚举类型，单选，从多个当中选择一个。

set：集合，多选，从多个当中选择多个

create table consumer(
id int,
name char(16),
gender enum('male','female','other'),
level enum('vip1','vip2','vip3'),
hobbies set('play','music','read','run')
);
Insert consumer values(1,'egon','male','vip2','music,read');
Insert consumer values(2,'alex','xxx','vip2','music,read'); # 不在范围之内，传进来就为空了。

三、约束条件

1、not null 与 default

not null：不在范围之内，传进来就为空了。

default：不在范围之内，传进来就为空了。

create table t15 (
    id int(11) unsigned zerofill
);

create table t16(
    id int,
    name char(6),
    sex enum('male','female') not null default 'male'
);

insert t16(id,name) values(1,'egon');

2、unique key

标识该字段的值是唯一的

1、单列唯一

create table department(
    id int,
    name char(10)
);

insert department values(1,'IT'),(2,'IT');

 

方式一：

create table department(
    id int unique,
    name char(10) unique
);

insert department values(1,'IT'),(2,'IT');

方式二：

create table department(
    id int ,
    name char(10) ,
    unique(id),
    unique(name),
);

insert department values(1,'IT'),(2,'IT');

2、联合唯一

create table services(
    id int,
    ip char(15),
    port int,
    unique(id),
    unique(ip,port)
);

insert services values
(1,'192.168.11.10',80),
(2,'192.168.11.10',81),
(3,'192.168.11.13',80);

insert services values
(4,'192.168.11.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.11.10  -80' for key 'ip'

3、primary key(主键)

标识该字段为该表的主键，可以唯一的标识记录

约束：not null unique，不为空且唯一。

存储引擎默认用的innodb。对于innodb存储引擎来说，一张表必须有一个主键。

1、单列主键

通常把id字段设置成主键

create table t17(
    id int primary key,  
    name char(16)
);

insert t17 values
(1,'egon'),
(2,'alex');

insert t17 values
(2,'wxx');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

insert t17(name) values
('wxx');
# 先给一个默认的id号0。如果再次插入的话就报错，告诉你id0已经有了。

create table t18(
    id int not null unique,
    name char(16)
);
# 没有设定主键就会去找不为空且唯一的字段去当主键。如果找不到的话就，就会找一个隐藏的字段当主键，不过这样没有意义。这是innodb的独特特性。

# 这个表id是主键

2、复合主键

create table t19(
    ip char(15),
    port int,
    primary key(ip,port)
);
 insert t19 values
 ('1.1.1.1',80),
 ('1.1.1.1',80);
 ERROR 1062 (23000): Duplicate entry '1.1.1.1        -80' for key 'PRIMARY'
 
 insert t19 values
 ('1.1.1.1',80),
 ('1.1.1.1',81);
 
 # 插入成功

4、auto_increment（自动增长）

create table t20(
    id int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 必须得被定义成key

create table t20(
    id int primary key auto_increment,
    name char(16)
);

insert t20(name) values
('egon'),
('alex'),
('wxx');

insert t20(id,name) values
(7,'yuanhao');

insert t20(name) values
('egon1'),
('egon2'),
('egon3');
# 以上一条记录(7)为基准开始增长

# 下面是需要了解的
show variables like 'auto_inc%';  # %代表任意长度的字符，这样可以显示auto_inc开头的所有关键字(或变量)

# 步长：
auto_increment_increment  # 默认1

# 起始位置：
auto_increment_offset  # 默认1

# 设置步长
set session auto_increment_increment=5;  # 只在本次链接有效，链接退出后失效。
set global auto_increment_increment=5;  # 所有的会话都生效，但是所有的会话得在重新登录一遍。

# 设置起始偏移量
set session auto_increment_offset=3; 
set global auto_increment_offset=3;   # 第一步跑到哪个位置，起始偏移量要小于步长


# 实操
set global auto_increment_offset=3; 
set global auto_increment_increment=5; 

create table t21(
    id int primary key auto_increment,
    name char(16)
);

insert t21(name) values
('egon'),
('alex'),
('wxx'),
('yxx');

# 还原回来，因为很少用到
set global auto_increment_offset=1;
set global auto_increment_increment=1;

# 清空表(需要掌握的)
delete from t20;

select * from t20;
Empty set (0.00 sec)

show create table t20;
AUTO_INCREMENT=16  delete from t20;  # 并没有把自增长的值回归到1

insert t20(name) values
('xxx');
select * from t20;
# 插入后还是直接16开始

# 所以清空表不要用delete去清空，用truncate。delete是用来和where连用，删除固定的条目，比如：delete from t20 where id=x 或 id>x;

truncate t20;

insert t20(name) values
('xxx');
# 从1开始了

5、foreign key(外键)

foreign key是用来建立表之间的关系

# 1、建立表关系

# 先建被关联的表
 create table dep(
     id int,
     name char(16),
     comment char(50)
 );

# 再建关联的表
create table emp(
    id int primary key,
    name char(10),
    gender enum('male','female'),
    dep_id int,
    foreign key(dep_id) references dep(id)
);
ERROR 1215 (HY000): Cannot add foreign key constraint
# 这是因为被关联表的字段没有这个主键，这就导致被关联表的字段不是唯一，说白了没有主键就是让所有员工信息表的信息都指向了部门表的第一个字段。

# 先建被关联的表，并且保证被关联的字段唯一
 create table dep(
     id int primary key,
     name char(16),
     comment char(50)
 );
# id int unique也可以


2、插入数据

先往被关联表插入记录
insert dep values
(1,'IT','技术能力有限部门'),
(2,'销售','销售能力不足部门'),
(3,'财务','花钱特别多部门');


再往关联表插入记录
insert emp values
(1,'egon','male',1),
(2,'alex','male',1),
(3,'wupeiqi','female',2),
(4,'yuanhao','male',3),
(5,'jinxin','male',2);


# IT部门不行，把IT部门裁掉

# 方法1：先删员工，在删部门
delete from emp where dep_id=1;
delete from dep where id=1;  

# 方法2：先删部门，在删员工(报错)
delete from dep where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db4`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

# 方法3：在创建关联表的时候加上删除和更新同步
create table emp(
    id int primary key,
    name char(10),
    sex enum('male','female'),
    dep_id int,
    foreign key(dep_id) references dep(id)
    on delete cascade
    on update cascade    
);

delete from dep where id=1;
部门和对应的员工全被删除了

update dep set id=202 where id=2;
部分和关联的员工表全被更新了

总结

有了foreign key相当于把两张表耦合到一起了，虽然更便利，但你必须得先向非关联表查记录，然后在查关联表。在写项目的时候，不要硬给他们强耦合到一起，而是从逻辑意义上去实现两张表的关系，自己用代码实现。这样好管理，扩展或改任何一张表，都不会影响到另外一张表。在用Django开发的时候，考虑到长期的扩展性，尽量不用建立这种硬性限制（foreign key)，要用逻辑代码