数据库 | mysql表操作

潘潘潘平 2021-09-06

359

存储引擎

表和数据的基础操作

mysql中的数据类型

表的完整性约束

表与表之间的关系

详解

存储引擎

存储引擎就是存储数据的方式，也称作表类型。

一张表中的信息：

数据；
表的结构；
索引：查询的时候使用的一个目录结构。

常用存储引擎

1.Innodb存储引擎

mysql5.6之后默认的存储引擎是Innodb存储引擎。

数据和索引存储在一起：有2个文件（数据索引、表结构）；
数据持久化：数据都是存储在硬盘上的；
支持事务：为了保证数据的完整性，将多个操作变成原子性操作，可以保证数据安全；
支持行级锁：修改的行少的时候使用，可以用于修改数据频繁的操作；
支持表级锁：批量修改多行的时候使用，可以用于对大量数据的同时修改；
支持外键：约束两张表中的关联字段不能随意的添加\删除，可以降低数据增删改的出错率。

2.Myisam存储引擎

mysql5.5之前默认的存储引擎是Myisam存储引擎。

数据和索引不存储在一起：有3个文件（数据、索引、表结构）；
数据持久化：数据都是存储在硬盘上的；
只支持表级锁。

3.Memory存储引擎

数据存储在内存中：有1个文件（表结构）；
数据断电消失：数据存储在内存中。

存储引擎相关的sql语句

1.查看当前数据库默认的存储引擎
show variables like "default_storage_engine";




2.查看当前数据库支持的存储引擎
show engines;




3.在建表时指定存储引擎
create table 表名(字段名1 数据类型(长度),字段名2 数据类型(长度),...) engine = 存储引擎; 
create table user(id bigint(12),name varchar(200)) engine = MyISAM; 




4.修改一个已经存在的表的存储引擎
alter table 表名 engine = 存储引擎;
alter table user engine = InnoDB;

（左右滑动查看完整内容）

在配置文件中指定存储引擎

# my.ini文件


[mysqld]
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

（左右滑动查看完整内容）

表和数据的基础操作

创建表

#语法
create table 表名(
字段名1 数据类型[(长度) 约束条件],
字段名2 数据类型[(选项) 约束条件],
...
);




#注意：
1. 在同一张表中，字段名不能重复
2. 长度和约束条件可以不写
3. 字段名和数据类型是必须写的




create table user(id int,name char(18));

（左右滑动查看完整内容）

往表中插入数据

insert into 表名 values (值1,值2,值3);
# 这张表有多少字段，就需要按照字段的顺序写入多少个值
insert into user values (1,'pamela');




insert into 表名 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
# 一次性写入多条数据
insert into user values (1,'pamela'),(2,'bill');




insert into 表名 (字段1,字段3) values (值1,值3);
# 指定字段名写入，可以任意的选择表中需要写入的字段
insert into user (name) values ('pamela');

（左右滑动查看完整内容）

查看表结构

desc 表名;
describe 表名;
# 能够查看字段\数据类型\长度，看不到表编码、存储引擎，具体的约束信息只能看到一部分




show create table 表名;
# 能查看字段\数据类型\长度\编码\存储引擎\约束
show create table 表名\G;
# \G可以使记录竖向排列，以便更好的显示内容较长的记录

（左右滑动查看完整内容）

修改表结构

1.修改表名
alter table 表名 rename 新表名;




2.增加字段
alter table 表名 
  add 字段名 数据类型[(长度)] [约束条件],
  add 字段名 数据类型[(长度)] [约束条件];
      
                                                  
3.删除字段
alter table 表名 drop 字段名;




4.修改字段
alter table 表名 modify 字段名 数据类型[(长度)] [约束条件];
alter table 表名 change 旧字段名 新字段名 旧数据类型[(长度)] [约束条件];
alter table 表名 change 旧字段名 新字段名 新数据类型[(长度)] [约束条件];




5.修改字段排列顺序/在增加的时候指定字段位置
alter table 表名 add 字段名 数据类型[(长度)] [约束条件] first;
alter table 表名 add 字段名 数据类型[(长度)] [约束条件] after 字段名;
alter table 表名 change 字段名 旧字段名 新字段名 新数据类型[(长度)] [约束条件] first;
alter table 表名 modify 字段名 数据类型[(长度)] [约束条件] after 字段名;

（左右滑动查看完整内容）

mysql中的数据类型

数值类型

int：整数，不约束长度，最多表示10位数。

float(m,n)：浮点数，m表示一共多少位，n表示小数部分多少位。

create table t1(
  id int,               # 默认是有符号的
  age tinyint unsigned  # 如果需要定义无符号的，使用unsigned
);




create table t2(
  f1 float(5,2),  # 保留2位小数，并四舍五入
  f2 float,
  f3 double(5,2),
  f4 double
)


insert into t2 (f2,f4) values (5.1783682169875975,5.1783682169875975179);




create table t3(
  f1 float,  # 保留2位小数，并四舍五入
  d1 double,
  d2 decimal(30,20),
  d3 decimal
);


insert into t3 values(5.1783682169875975179,5.1783682169875975179,
                      5.1783682169875975179,5.1783682169875975179);

（左右滑动查看完整内容）

日期时间类型

datetime：年月日时分秒，20210712141900。

year：年份，2021。

date：年月日，20210712。

time：时分秒，141900。

timestamp：时间戳，年月日时分秒，20210712141900。

create table t4(
  dt datetime,
  y year,
  d date,
  t time,
  ts timestamp
);




create table t5(
  id int,
  dt datetime NOT NULL                       # 不能为空
              DEFAULT CURRENT_TIMESTAMP      # 默认是当前时间
              ON UPDATE CURRENT_TIMESTAMP    # 在更新的时候使用当前时间更新字段
);

（左右滑动查看完整内容）

字符串类型

char(长度)：

最多能表示255个字符；
定长存储，浪费空间，节省时间；
char可以不写长度；
适用字段：身份证号、手机号码、银行卡号等。

varchar(长度)：

最多能表示65535个字符；
变长存储，节省空间，存取速度慢；
varchar必须写长度；
适用字段：评论、朋友圈、微博。

create table t6(
  c1 char(1),
  v1 varchar(1),
  c2 char(8),
  v2 varchar(8)
);  




create table t7(
  c1 char,
  v1 varchar(1),
  c2 char(8),
  v2 varchar(8)
);

（左右滑动查看完整内容）

enum和set类型

enum：单选行为，在创建表时通过枚举方式显示，只允许从值集合中选取单个值，而不能一次取多个值。

set：多选行为，在创建表时通过枚举方式显示，允许从值集合中任意选择一个或多个元素进行组合，对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

create table t8(
  id int,
  name char(18),
  gender enum('male','female')  # 单选
);




create table t9(
  id int,
  name char(18),
  hobby set('看书','看电影','跑步','画画','爬山')  # 多选
);


insert into t9 values (1,'pamela','看电影,画画,看书,跑步');
insert into t9 values (2,'bill','看书,看书,看书,跑步,打游戏');  # 会自动去重

（左右滑动查看完整内容）

表的完整性约束

表的完整性约束就是对某一个字段进行约束，约束条件与数据类型的长度一样，都是可选参数。

无符号的 unsigned

如果需要定义无符号的数值类型，可使用unsigned。

create table t10(
  id int unsigned
);

（左右滑动查看完整内容）

不能为空 not null

非空约束，指定某列不能为空，null表示空，非字符串。

create table t11(
  id int unsigned not null,
  name char(18) not null
);

（左右滑动查看完整内容）

默认值 default

创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。

create table t12(
  id int unsigned not null,
  name char(18) not null,
  male enum('male','female') not null default 'male'
);

（左右滑动查看完整内容）

唯一约束 unique

不能重复：unique，指定某列的值不能重复，但是null可以写入多个。

create table t13(
  id1 int unique,
  id2 int
);

（左右滑动查看完整内容）

联合唯一：指定某几列的组合不能重复，unique(字段1,字段2)。

create table t14(
  id int,
  server_name char(12),
  ip char(15),
  port char(5),
  unique(ip,port)  # 联合唯一
);

（左右滑动查看完整内容）

主键 primary key

主键就是非空 + 唯一约束。

一张表只能定义一个主键，如果不指定主键，默认是第一个非空+唯一的字段。

create table t15(
  id int not null unique,  # 主键
  username char(18) not null unique
);




create table t16(
  username char(18) not null unique,  # 主键
  id int not null unique
);




create table t17(
  username char(18) not null unique,
  id int primary key  # 主键
);

（左右滑动查看完整内容）

联合主键：primary key(字段1,字段2)。

create table t18(
  id int,
  server_name char(12),
  ip char(15) default '',
  port char(5) default '',
  primary key(ip,port)
);  




create table t19(
  id int primary key,
  server_name char(12),
  ip char(15) not null,
  port char(5) not null,
  unique(ip,port)
);

（左右滑动查看完整内容）

自增 auto_increment

约束字段为自动增长，自增只能对数字int有效，自带非空约束。

自增字段必须至少是unique的约束。

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

（左右滑动查看完整内容）

外键 foreign key

foreign key(自己的字段) references 外表(外表字段)

外键字段必须至少是unique的约束。

create table class(
  cid int primary key auto_increment,
  cname char(12) not null,
  startd date
);




create table student(
  id int primary key auto_increment,
  name char(12) not null,
  gender enum('male','female') default 'male',
  class_id int,
  foreign key(class_id) references class(cid)
);




create table student2(
  id int primary key auto_increment,
  name char(12) not null,
  gender enum('male','female') default 'male',
  class_id int,
  foreign key(class_id) references class(cid)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除，尽量不用
);

（左右滑动查看完整内容）

表与表之间的关系

多对一（一对多）

多对一（一对多）：一个班级可以对应多个学生，但一个学生只能对应一个班级。

关联方式：foreign key，学生表有一个外键关联班级表。

create table class(
  cid int primary key auto_increment,
  cname char(12) not null,
  startd date
);




create table student(
  id int primary key auto_increment,
  name char(12) not null,
  gender enum('male','female') default 'male',
  class_id int,
  foreign key(class_id) references class(cid)
);

（左右滑动查看完整内容）

多对多

多对多：一个班级可以对应多个学生，一个学生也可以对应多个班级（双向的一对多，即多对多）。

关联方式：foreign key + 一张新的表（两个外键），新表有两个外键，分别关联学生表和班级表。

create table class(
  cid int primary key auto_increment,
  cname char(12) not null,
  startd date
);




create table student(
  sid int primary key auto_increment,
  sname char(12) not null,
  gender enum('male','female') default 'male'
);




# 这张表就存放班级表与学生表的关系，即查询二者的关系查这个表就可以了
create table class_student(
  id int primary key auto_increment,
  class_id int not null,
  foreign key(class_id) references class(cid),
  student_id int not null,
  foreign key(student_id) references student(sid),
  primary key(class_id,student_id)
);

（左右滑动查看完整内容）

一对一

一对一：一个学生唯一对应一个客户。

关联方式：foreign key + unique，学生表有一个外键（唯一的）关联客户表。

create table customer(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender enum('male','female') default 'male',
  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
);

（左右滑动查看完整内容）

mysql

文章转载自潘潘潘平，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数据库 | mysql表操作

评论