创建表
语法格式:
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
主键约束
- 主键:primary key,简称PK
- 主键约束的字段不能为NULL,并且不能重复。
- 任何一张表都应该有主键,没有主键的表可以视为无效表。
- 主键值是这行记录的身份证号,是唯一标识。在数据库表中即使两条数据一模一样,但由于主键值不同,我们也会认为是两条完全的不同的数据。
- 主键分类:
-
- 根据字段数量分类:
-
-
- 单一主键(1个字段作为主键)==>建议的
- 复合主键(2个或2个以上的字段作为主键)
-
-
- 根据业务分类:
-
-
- 自然主键(主键和任何业务都无关,只是一个单纯的自然数据)===>建议的
- 业务主键(主键和业务挂钩,例如:银行卡账号作为主键)
-
- 单一主键(建议使用这种方式)
create table t_student(
id bigint primary key,
sno varchar(255) unique,
sname varchar(255) not null
)
- 复合主键(很少用,了解)
create table t_user(
no int,
name varchar(255),
age int,
primary key(no,name)
);
- 主键自增:既然主键值是一个自然的数字,mysql为主键值提供了一种自增机制,不需要我们程序员维护,mysql自动维护该字段
create table t_vip(
no int primary key auto_increment,
name varchar(255)
);
外键约束
外键也可以联合外键
- 有这样一个需求:要求设计表,能够存储学生以及学校信息。
-
- 第一种方案:一张表
这种方式会导致数据冗余,浪费空间。
-
- 第二种方案:两张表:一张存储学生,一张存储学校
t_school 表
t_student 表
如果采用以上两张表存储数据,对于学生表来说,sno这个字段的值是不能随便填的,这个sno是学校编号,必须要求这个字段中的值来自学校表的sno。
为了达到要求,此时就必须要给t_student表的sno字段添加外键约束了。
- 外键约束:foreign key,简称FK。
- 添加了外键约束的字段中的数据必须来自其他字段,不能随便填。
- 假设给a字段添加了外键约束,要求a字段中的数据必须来自b字段,b字段不一定是主键,但至少要有唯一性。
- 外键约束可以给单个字段添加,叫做单一外键。也可以给多个字段联合添加,叫做复合外键。复合外键很少用。
- a表如果引用b表中的数据,可以把b表叫做父表,把a表叫做子表。
-
- 创建表时,先创建父表,再创建子表。
- 插入数据时,先插入父表,在插入子表。
- 删除数据时,先删除子表,再删除父表。
- 删除表时,先删除子表,再删除父表。
- 如何添加外键:
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)
);
- 级联删除
创建子表时,外键可以添加: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;
- 级联更新
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
);
- 级联置空
父表删除时,子表相应字段不删除,置空为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
);