第三章 表相关

创建表

语法格式：

create table 表名(
  字段名1 数据类型,
  字段名2 数据类型,
  字段名3 数据类型,
  ......
);

例如：创建学生表

create table t_student(
  no int,
  name varchar,
  gender char(1) default '男'
);

插入数据

语法格式：

insert into 表名(字段名1, 字段名2, 字段名3,......) values (值1,值2,值3,......);

字段名和值要一一对应。类型要一一对应，数量要一一对应。
字段名也可以省略，如果字段名省略就表示把所有字段名都写上去了，并且顺序和建表时的顺序相同。

删除表

语法格式：

drop table 表名;

或者

drop table if exists 表名;

判断是否存在这个表，如果存在则删除。避免不存在时的报错。

MySQL数据类型

数据类型（data_type）是指系统中所允许的数据的类型。数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。
MySQL 的数据类型可以分为整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制类型等。

整数类型

tinyint：1个字节（微小整数）
smallint：2个字节（小整数）
mediumint：3个字节（中等大小的整数）
int（integer）：4个字节（普通大小整数）
bigint：8个字节（大整数）

浮点数类型

float：4个字节，单精度（最多5位小数）
double：8个字节，双精度（最多16位小数）

定点数类型

decimal：定点数类型。底层实际上采用字符串的形式存储数字。
语法：decimal(m, d)
例如：decimal(3, 2) 表示3个有效数字，2个小数。（有效数字最多65个，小数位最多30个）

日期和时间类型

year：1个字节，只存储年，格式YYYY
time：3个字节，只存储时间，格式HH:MM:SS / HHMMSS
date：3个字节，只存储年月日，格式：YYYY-MM-DD
datetime：8个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1000年~公元9999年）
timestamp：4个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1980年~公元2040年）或者格式为 YYYYMMDDHHMMSS（采用这种格式不需要使用单引号，当然你使用单引号也可以）

字符串类型

char

char(m)：m长度是0~255个字符。固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。m表示列的长度，范围是 0～255 个字符。例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当插入的字符长度大于4，则报错（除非超过4个长度之后都是空格字符，则空格字符会自动被删除用来保证插入的成功）。

varchar

varchar(m)：m长度是0~16383个字符长度可变的字符串。varchar 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。例如，varchar(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。varchar在值保存和检索时尾部的空格仍保留。

text

text类型：

• tinytext 表示长度为 255字符的 TEXT 列。
• text 表示长度为 65535字符的 TEXT 列。
• mediumtext 表示长度为 16777215字符的 TEXT 列。
• longtext 表示长度为 4294967295 或 4GB 字符的 TEXT 列。

enum

enum类型：

• 语法：<字段名> enum('值1','值2',...)
• 该字段插入值时，只能是指定的枚举值。

set

set类型：

• 语法：<字段名> set('值1','值2','值3',...) 注意：值不可重复。
• 该字段插入值时，只能是指定的值。

二进制类型

BLOB类型：二进制大对象，可以存储图片、声音、视频等文件。

• blob：小的，最大长度65535个字节
• mediumblob：中等的，最大长度16777215个字节
• longblob：大的，最大长度4GB的字节

增删改表结构DDL

创建一个学生表

create table t_student(
  no bigint,
  name varchar(255),
  age int comment '年龄'
);

查看建表语句

show create table 表名;

修改表名

alter table 表名 rename 新表名;

新增字段

alter table 表名 add 字段名 数据类型;

修改字段名

alter table 表名 change 旧字段名 新字段名 数据类型;

修改字段数据类型

alter table 表名 modify column 字段名 数据类型;

删除字段

alter table 表名 drop 字段名;

DML语句

当我们对表中的数据进行增删改的时候，称它为DML语句。（数据操纵语言），主要包括：insert、delete、update

insert 增

语法格式：

insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);

表名后面的小括号当中的字段名如果省略掉，表示自动将所有字段都列出来了，并且字段的顺序和建表时的顺序一致。
一般为了可读性强，建议把字段名写上。

insert into 表名 values(值1,值2,值3,...);

一次可以插入多条记录：

insert into t_stu(no,name,age) values(1,'jack',20),(2,'lucy',30);

delete 删

语法格式：

# 将所有记录全部删除
delete from 表名;

# 删除符合条件的记录
delete from 表名 where 条件;

以上的删除属于DML的方式删除，这种删除的数据是可以通过事务回滚的方式重新恢复的，但是删除的效率较低。（这种删除是支持事务的。）

另外还有一种删除表中数据的方式，但是这种方式不支持事务，不可以回滚，删了之后数据是永远也找不回来了。这种删除叫做：表被截断。

注意：这个语句删除效率非常高，巨大的表，瞬间干掉所有数据。但不可恢复。

truncate table 表名;

update 改

语法格式：

update 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3 where 条件;

如果没有更新条件的话，所有记录全部更新。

约束constraint

创建表时，可以给表的字段添加约束，可以保证数据的完整性、有效性。比如大家上网注册用户时常见的：用户名不能为空。对不起，用户名已存在。等提示信息。
约束通常包括：

• 非空约束：not null
• 检查约束：check
• 唯一性约束：unique
• 主键约束：primary key
• 外键约束：foreign key

非空约束

语法格式：

create table t_stu(
  no int,
  name varchar(255) not null,
  age int
);

name字段不能为空。插入数据时如果没有给name指定值，则报错。

检查约束

create table t_stu(
  no int,
  name varchar(255),
  age int,
  check(age > 18)
);

唯一性约束

语法格式：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255) unique
);

email字段设置为唯一性，唯一性的字段值是可以为NULL的。但不能重复。以上在字段后面添加的约束，叫做列级约束。
当然，添加约束还有另一种方式：表级约束：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(email)
);

使用表级约束可以为多个字段添加联合唯一。

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(name,email)
);

创建约束时也可以给约束起名字，将来可以通过约束的名字来删除约束：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  constraint t_stu_name_email_unique unique(name,email)
);

所有的约束都存储在一个系统表当中：table_constraints。这个系统表在这个数据库当中：information_schema

主键约束

1. 主键：primary key，简称PK
2. 主键约束的字段不能为NULL，并且不能重复。
3. 任何一张表都应该有主键，没有主键的表可以视为无效表。
4. 主键值是这行记录的身份证号，是唯一标识。在数据库表中即使两条数据一模一样，但由于主键值不同，我们也会认为是两条完全的不同的数据。
5. 主键分类：

1. 1. 根据字段数量分类：

1. 1. 1. 单一主键（1个字段作为主键）==>建议的
      2. 复合主键（2个或2个以上的字段作为主键）

1. 1. 根据业务分类：

1. 1. 1. 自然主键（主键和任何业务都无关，只是一个单纯的自然数据）===>建议的
      2. 业务主键（主键和业务挂钩，例如：银行卡账号作为主键）

1. 单一主键（建议使用这种方式）

create table t_student(
  id bigint primary key,
  sno varchar(255) unique,
  sname varchar(255) not null
)

1. 复合主键（很少用，了解）

create table t_user(
  no int,
  name varchar(255),
  age int,
  primary key(no,name)
);

1. 主键自增：既然主键值是一个自然的数字，mysql为主键值提供了一种自增机制，不需要我们程序员维护，mysql自动维护该字段

create table t_vip(
  no int primary key auto_increment,
  name varchar(255)
);

外键约束

外键也可以联合外键

1. 有这样一个需求：要求设计表，能够存储学生以及学校信息。

1. 1. 第一种方案：一张表

这种方式会导致数据冗余，浪费空间。

1. 1. 第二种方案：两张表：一张存储学生，一张存储学校

t_school 表

t_student 表

如果采用以上两张表存储数据，对于学生表来说，sno这个字段的值是不能随便填的，这个sno是学校编号，必须要求这个字段中的值来自学校表的sno。

为了达到要求，此时就必须要给t_student表的sno字段添加外键约束了。

1. 外键约束：foreign key，简称FK。
2. 添加了外键约束的字段中的数据必须来自其他字段，不能随便填。
3. 假设给a字段添加了外键约束，要求a字段中的数据必须来自b字段，b字段不一定是主键，但至少要有唯一性。
4. 外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键很少用。
5. a表如果引用b表中的数据，可以把b表叫做父表，把a表叫做子表。

1. 1. 创建表时，先创建父表，再创建子表。
   2. 插入数据时，先插入父表，在插入子表。
   3. 删除数据时，先删除子表，再删除父表。
   4. 删除表时，先删除子表，再删除父表。

1. 如何添加外键：

create table t_school( 
  sno int primary key, 
  sname varchar(255) 
); 
create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) 
);

1. 级联删除

创建子表时，外键可以添加：on delete cascade，这样在删除父表数据时，子表会级联删除。谨慎使用。

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete cascade 
);

###删除约束
alter table t_student drop foreign key t_school_sno_fk;
alter table t_student drop constrainit t_school_sno_fk;
###添加约束
alter table t_student add constraint t_student_sno_fk foreign key(sno) references t_school(sno) on delete cascade;

1. 级联更新

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on update cascade 
);

1. 级联置空

父表删除时，子表相应字段不删除，置空为null

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete set null 
);