一、创建和管理表
MySQL中的数据类型
类型 | 类型举例 |
---|---|
整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT |
浮点类型 | FLOAT、DOUBLE |
定点数类型 | DECIMAL |
位类型 | BIT |
日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP |
文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
枚举类型 | ENUM |
集合类型 | SET |
二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
JSON类型 | JSON对象、JSON数组 |
空间数据类型 | 单值:GEOMETRY、POINT、LINESTRING、POLYGON; 集合:MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |
其中,常用的几类类型介绍如下:
数据类型 | 描述 |
---|---|
INT | 从-231到231-1的整型数据。存储大小为 4个字节 |
CHAR(size) | 定长字符数据。若未指定,默认为1个字符,最大长度255 |
VARCHAR(size) | 可变长字符数据,根据字符串实际长度保存,必须指定长度 |
FLOAT(M,D) | 单精度,占用4个字节,M=整数位+小数位,D=小数位。 D<=M<=255,0<=D<=30,默认M+D<=6 |
DOUBLE(M,D) | 双精度,占用8个字节,D<=M<=255,0<=D<=30,默认M+D<=15 |
DECIMAL(M,D) | 高精度小数,占用M+2个字节,D<=M<=65,0<=D<=30,最大取值范围与DOUBLE相同。 |
DATE | 日期型数据,格式'YYYY-MM-DD' |
BLOB | 二进制形式的长文本数据,最大可达4G |
TEXT | 长文本数据,最大可达4G |
创建数据库
方式2:判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符集;
使用数据库
查看当前所有的数据库
SHOW DATABASES; #有一个S,代表多个数据库
查看当前正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数
查看指定库下所有的表
SHOW TABLES FROM 数据库名;
查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者:
SHOW CREATE DATABASE 数据库名\G
使用/切换数据库
USE 数据库名;
注意:要操作表格和数据之前必须先说明是对哪个数据库进行操作,否则就要对所有对象加上“数据库名.”。
修改数据库
更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如:gbk、utf8等
删除数据库
DROP DATABASE IF EXISTS 数据库名;
创建表
-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);
或
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
修改表
追加一个列
ALTER TABLE dept80
ADD job_id varchar(15);
修改一个列
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;
重命名一个列
ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);
删除一个列
ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略
删除表
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
清空表
TRUNCATE TABLE detail_dept;
TRUNCATE语句不能回滚,而使用 DELETE 语句删除数据,可以回滚
阿里开发规范:
【参考】TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 TRIGGER,有可能造成事故,故不建议在开发代码中使用此语句。
二、数据处理之增删改
插入数据
INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');
INSERT还可以将SELECT语句查询的结果插入到表中,此时不需要把每一条记录的值一个一个输入,只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
更新数据
UPDATE copy_emp
SET department_id = 110;
删除数据
DELETE FROM departments
WHERE department_name = 'Finance';
MySQL8新特性:计算列
某一列的值是通过别的列计算得来的。
CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);
三、MySQL数据类型
MySQL关键字 | 含义 |
---|---|
NULL | 数据列可包含NULL值 |
NOT NULL | 数据列不允许包含NULL值 |
DEFAULT | 默认值 |
PRIMARY KEY | 主键 |
AUTO_INCREMENT | 自动递增,适用于整数类型 |
UNSIGNED | 无符号 |
CHARACTER SET name | 指定一个字符集 |
UNSIGNED
UNSIGNED
: 无符号类型(非负),所有的整数类型都有一个可选的属性UNSIGNED(无符号属性),无符号整数类型的最小取值为0。
ZEROFILL
ZEROFILL
: 0填充,(如果某列是ZEROFILL,那么MySQL会自动为当前列添加UNSIGNED属性),如果指定了ZEROFILL只是表示不够M位时,用0在左边填充,如果超过M位,只要不超过数据存储范围即可。
适用场景
TINYINT
:一般用于枚举数据,比如系统设定取值范围很小且固定的场景。
SMALLINT
:可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。
MEDIUMINT
:用于较大整数的计算,比如车站每日的客流量等。
INT、INTEGER
:取值范围足够大,一般情况下不用考虑超限问题,用得最多。比如商品编号。
BIGINT
:只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
浮点类型
从MySQL 8.0.17开始,FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用,将来可能被移除。另外,关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了,将来也可能被移除。因为浮点数是不准确的,所以我们要避免使用“=”来判断两个数是否相等。
定点数类型
数据类型 | 字节数 | 含义 |
---|---|---|
DECIMAL(M,D),DEC,NUMERIC | M+2字节 | 有效范围由M和D决定 |
“由于 DECIMAL 数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)用到整数类型外,其他的数值都用的是 DECIMAL,原因就是这个项目所处的零售行业,要求精准,一分钱也不能差。 ” ——来自某项目经理
日期与时间类型
类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|---|
YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07UTC |
YEAR类型
从MySQL5.5.27开始,2位格式的YEAR已经不推荐使用。
TIMESTAMP和DATETIME的区别:
TIMESTAMP存储空间比较小,表示的日期时间范围也比较小
底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。
两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同,显示不同的结果。而DATETIME则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。
文本字符串类型
CHAR与VARCHAR类型
字符串(文本)类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|---|
CHAR(M) | 固定长度 | M | 0 <= M <= 255 | M个字节 |
VARCHAR(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度 + 1) 个字节 |
类型 | 特点 | 空间上 | 时间上 | 适用场景 |
---|---|---|---|---|
CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大,速度要求高 |
VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非CHAR的情况 |
TEXT类型
文本字符串类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|---|
TINYTEXT | 小文本、可变长度 | L | 0 <= L <= 255 | L + 2 个字节 |
TEXT | 文本、可变长度 | L | 0 <= L <= 65535 | L + 2 个字节 |
MEDIUMTEXT | 中等文本、可变长度 | L | 0 <= L <= 16777215 | L + 3 个字节 |
LONGTEXT | 大文本、可变长度 | L | 0 <= L<= 4294967295(相当于4GB) | L + 4 个字节 |
由于实际存储的长度不确定,MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况,只能采用 CHAR(M),或者 VARCHAR(M)。
开发中经验:
TEXT文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用CHAR,VARCHAR来代替。还有TEXT类型不用加默认值,加了也没用。而且text和blob类型的数据删除后容易导致“空洞”,使得文件碎片比较多,所以频繁使用的表不建议包含TEXT类型字段,建议单独分出去,单独用一个表。
ENUM类型
ENUM类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时,ENUM类型只允许从成员中选取单个值,不能一次选取多个值。
SET类型
SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为64
。设置字段值时,可以取取值范围内的 0 个或多个值。
二进制字符串类型
MySQL中的二进制字符串类型主要存储一些二进制数据,比如可以存储图片、音频和视频等二进制数据。
MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。
BINARY与VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR,只是它们存储的是二进制字符串
BLOB类型
BLOB是一个二进制大对象
,可以容纳可变数量的数据。
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型,它们可容纳值的最大长度不同。可以存储一个二进制的大对象,比如图片
、音频
和视频
等。
需要注意的是,在实际工作中,往往不会在MySQL数据库中使用BLOB类型存储大对象数据,通常会将图片、音频和视频文件存储到服务器的磁盘上
,并将图片、音频和视频的访问路径存储到MySQL中。
JSON 类型
JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。
四、约束
根据约束的作用范围分类:
列级约束:只能作用在一个列上,跟在列的定义后面
表级约束:可以作用在多个列上,不与列一起,而是单独定义
位置 支持的约束类型 是否可以起约束名
列级约束: 列的后面 语法都支持,但外键没有效果 不可以
表级约束: 所有列的下面 默认和非空不支持,其他支持 可以(主键没有效果)
根据约束起的作用,约束可分为:
NOT NULL 非空约束,规定某个字段不能为空
UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
CHECK 检查约束
DEFAULT 默认值约束
非空约束
限定某个字段/某列的值不允许为空
CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);
删除非空约束
ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;
唯一性约束
用来限制某个字段/某列的值不能重复。
CREATE TABLE t_course(
cid INT UNIQUE,
cname VARCHAR(100) UNIQUE,
description VARCHAR(200)
);
删除唯一约束
-
添加唯一性约束的列上也会自动创建唯一索引。
-
删除唯一约束只能通过删除唯一索引的方式删除。
-
删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样。
-
如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
ALTER TABLE USER
DROP INDEX uk_name_pwd;
注意:可以通过
show index from 表名称;
查看表的索引
PRIMARY KEY 约束
用来唯一标识表中的一行记录。
-
主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值。
-
一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建。
-
主键约束对应着表中的一列或者多列(复合主键)
-
如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
-
MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用。
-
当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高)。如果删除主键约束了,主键约束对应的索引就自动删除了。
-
需要注意的一点是,不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。
CREATE TABLE temp(
id int primary key,
name varchar(20)
);
表级约束
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);
自增列:AUTO_INCREMENT
某个字段的值自增
1)一个表最多只能有一个自增长列
(2)当需要产生唯一标识符或顺序值时,可设置自增长
(3)自增长列约束的列必须是键列(主键列,唯一键列)
(4)自增约束的列的数据类型必须是整数类型
(5)如果自增列指定了 0 和 null,会在当前最大值的基础上自增;如果自增列手动指定了具体值,直接赋值为具体值。
create table employee(
eid int primary key auto_increment,
ename varchar(20)
);
MySQL 8.0将自增主键的计数器持久化到重做日志
中。每次计数器发生改变,都会将其写入重做日志中。如果数据库重启,InnoDB会根据重做日志中的信息来初始化计数器的内存值。
FOREIGN KEY 约束
限定某个表的某个字段的引用完整性。
比如:员工表的员工所在部门的选择,必须在部门表能找到对应的部分。
主表(父表):被引用的表,被参考的表
从表(子表):引用别人的表,参考别人的表
例如:员工表的员工所在部门这个字段的值要参考部门表:部门表是主表,员工表是从表。
例如:学生表、课程表、选课表:选课表的学生和课程要分别参考学生表和课程表,学生表和课程表是主表,选课表是从表。
(1)从表的外键列,必须引用/参考主表的主键或唯一约束的列
为什么?因为被依赖/被参考的值必须是唯一的
(2)在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名(例如 student_ibfk_1;),也可以指定外键约束名。
(3)创建(CREATE)表时就指定外键约束的话,先创建主表,再创建从表
(4)删表时,先删从表(或先删除外键约束),再删除主表
(5)当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据
(6)在“从表”中指定外键约束,并且一个表可以建立多个外键约束
(7)从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致。如果类型不一样,创建子表时,就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。
例如:都是表示部门编号,都是int类型。
(8)当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。(根据外键查询效率很高)
(9)删除外键约束后,必须手动
删除对应的索引
create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致,意义都是表示部门的编号
);
说明:
(1)主表dept必须先创建成功,然后才能创建emp表,指定外键成功。
(2)删除表时,先删除从表emp,再删除主表dept
如果需要修改表的设计(比如添加新的字段,增加新的关联关系),但没有预先定义外键约束,那么,就要用修改表的方式来补充定义。
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
添加了外键约束后,主表的修改和删除数据受约束
添加了外键约束后,从表的添加和修改数据受约束
在从表上建立外键,要求主表必须存在
删除主表时,要求从表从表先删除,或将从表中外键引用该主表的关系先删除
-
Cascade方式
:在父表上update/delete记录时,同步update/delete掉子表的匹配记录 -
Set null方式
:在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能为not null -
No action方式
:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 -
Restrict方式
:同no action, 都是立即检查外键约束 -
Set default方式
(在可视化工具SQLyog中可能显示空白):父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别
如果没有指定等级,就相当于Restrict方式。
对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT
的方式。
CHECK 约束
检查某个字段的值是否符号xx要求,一般指的是值的范围
MySQL5.7 可以使用check约束,但check约束对数据验证没有任何作用。添加数据时,没有任何错误或警告
但是MySQL 8.0中可以使用check约束了。
create table employee(
eid int primary key,
ename varchar(5),
gender char check ('男' or '女')
);
DEFAULT约束
给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值。
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);