数据库 | mysql表操作

目录

存储引擎

表和数据的基础操作

mysql中的数据类型

表的完整性约束

表与表之间的关系

详解

存储引擎

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

一张表中的信息：

常用存储引擎

1.Innodb存储引擎

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

2.Myisam存储引擎

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

3.Memory存储引擎

存储引擎相关的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(长度)：

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

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