Mysql学习笔记-分页－表的创建－数据类型

分页

分页原理：
所谓分页显示,就是将数据库中的结果集,一段一段显示出来需要的条件。

• MySQL中使用 LIMIT 实现分页
• 格式:LIMIT [位置偏移量,] 行数

--前10条记录:
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录:
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录:
SELECT * FROM 表名 LIMIT 20,10;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT4,3;”返回的结果相同

同理：分页显式公式:(当前页数-1)*每页条数,每页条数

SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;

注意:LIMIT 子句必须放在整个SELECT语句的最后!
使用 LIMIT 的好处：
约束返回结果的数量可以 减少数据表的网络传输量 ,也可以提升查询效率 。如果我们知道返回结果只有1 条,就可以使用 LIMIT 1 ,告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表,只需要检索到一条符合条件的记录即可返回．

表的创建和管理

数据存储过程
一个完整的数据存储过程总共有 4 步,分别：是创建数据库、确认字段、创建数据表、插入数据。

因为从系统架构的层次上看,MySQL 数据库系统从大到小依次是：数据库服务器 、 数据库 、 数据表 、数据表的 行与列 。

标识符命名规则：

• 数据库名、表名不得超过30个字符,变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格

常用的几类数据类型:
创建数据库：
方式1:创建数据库
CREATE DATABASE 数据库名;
方式2:创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
方式3:判断数据库是否已经存在,不存在则创建数据库( 推荐 )
CREATE DATABASE IF NOT EXISTS 数据库名;
如果MySQL中已经存在相关的数据库,则忽略创建语句,不再创建数据库。

注意:DATABASE 不能改名。一些可视化工具可以改名,它是建新库,把所有表复制到新库,再删旧库完成的。

使用数据库：
查看当前正在使用的数据库：SELECT DATABASE();#使用的一个 mysql 中的全局函数

查看数据库的创建信息：SHOW CREATE DATABASE 数据库名;或SHOW CREATE DATABASE 数据库名\G

修改数据库：

• 更改数据库字符集:
  ALTER DATABASE 数据库名 CHARACTER SET 字符集;#比如:gbk、utf8等
• 删除指定的数据库:
  方法一：DROP DATABASE 数据库名;
  方法二：DROP DATABASE IF EXISTS 数据库名;( 推荐 )

创建表：
方式一

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
......
[表约束条件]
);

方式二：使用 AS subquery 选项,将创建表和插入数据结合起来

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

查看数据表结构：
DESCRIBE/DESC 语句查看数据表结构,也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句,还可以查看存储引擎和字符编码。

修改表
使用 ALTER TABLE 语句可以实现:

• 向已有的表中添加列
• 修改现有表中的列
• 删除现有表中的列
• 重命名现有表中的列

追加一个列:

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

例如：

ALTER TABLE dept80
ADD job_id varchar(15);

修改一个列：
可以修改列的数据类型,长度、默认值和位置
修改字段数据类型、长度、默认值、位置的语法格式如下:

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

例如：

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

重命名一个列：
使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下:

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

例如：

ALTER TABLE　dept80
CHANGE department_name dept_name varchar(15);

删除一个列：
删除表中某个字段的语法格式如下:

ALTER TABLE 表名 DROP 【COLUMN】字段名

例如：

ALTER TABLE　dept80
DROP department_name ;

重命名表：
必须是对象的拥有者
方式一:使用RENAME

RENAME TABLE emp
TO myemp;

方式二:

ALTER table dept
RENAME [TO] detail_dept;-- [TO]可以省略

删除表：
DROP TABLE 语句不能回滚

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, ..., 数据表n];

清空表：
TRUNCATE TABLE语句:
删除表中所有的数据，释放表的存储空间

TRUNCATE TABLE <tableName>;

RUNCATE语句不能回滚,而使用 DELETE 语句删除数据,可以回滚

DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

阿里开发规范:
【参考】TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 TRIGGER,有可能造成事故,故不建议在开发代码中使用此语句。说明:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

拓展1:MySQL8新特性—DDL的原子化
在MySQL 8.0版本中,InnoDB表的DDL支持事务完整性,即 DDL操作要么成功要么回滚 。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log(该表是隐藏的表,通过show tables无法看到)中,用于回滚操作。通过设置参数,可将DDL操作日志打印输出到MySQL错误日志中。

数据类型

日期时间型：
DATE:占3个字节，以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期,使用 CURRENT_DATE() 或者NOW()函数,会插入当前系统的日期;

TIME:占3个字节，可以使用带有冒号的字符串,比如’ D HH:MM:SS’ 、'HH:MM:SS ‘、’ HH:MM ‘、’ D HH:MM ‘、’ D HH ‘或’ SS '格式,都能被正确地插入TIME类型的字段中。其中D表示天,其最小值为0,最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。使用 CURRENT_TIME() 或者 NOW() ,会插入当前系统的时间。

DATETIME:占8个字节,YYYY-MM-DD HH:MM:SS 格式;使用函数 CURRENT_TIMESTAMP() 和 NOW() ,可以向DATETIME类型的字段插入系统的当前日期和时间。

TIMESTAMP：带有时区信息的日期时间信息，显示格式与DATETIME类型相同,都是 YYYY-MM-DDHH:MM:SS ,需要4个字节的存储空间,因此存储的时间范围比DATETIME要小很多．CURRENT_TIMESTAMP() 和 NOW()
TIMESTAMP与DATETIME的区别：
底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。
两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。

注意：用得最多的日期时间类型,就是 DATETIME,一般存注册时间、商品发布时间等,不建议使用DATETIME存储,而是使用时间戳UNIX_TIMESTAMP(),因为DATETIME虽然直观,但不便于计算。

文本字符串：
CHAR类型:
CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在 右侧填充 空格以达到指定的长
度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。
定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数

VARCHAR类型:
VARCHAR(M) 定义时, 必须指定 长度M,否则报错。
MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节) ;MySQL5.0版本上,varchar(20):指的是20字符。
检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

哪些情况使用 CHAR 或 VARCHAR 更好:
情况1:存储很短的信息。比如门牌号码101,201…这样很短的信息应该用char,因为varchar还要占个
byte用于存储信息长度,本来打算节约存储的,结果得不偿失。
情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据
长度的特性就消失了,而且还要占个长度信息。
情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个
非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
情况4:具体存储引擎中的情况:

• MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化,从而使数据检索更快 ,用空间换时间。
• MEMORY 存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。
• InnoDB 存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。

ENUM类型
ENUM类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时,ENUM
类型只允许从成员中选取单个值,不能一次选取多个值。
占用的存储空间：1或2个字节
当ENUM类型包含1~255个成员时,需要1个字节的存储空间;
当ENUM类型包含256~65535个成员时,需要2个字节的存储空间。
ENUM类型的成员个数的上限为65535个。

创建表如下:

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);

SET类型
SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为 64 。设置字段值时,可以取取值范围内的 0 个或多个值。当SET类型包含的成员个数不同时,其所占用的存储空间也是不同的,具体如下:

举例:
创建表:

CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);

向表中插入数据:

INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重复的SET类型成员时,MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时,MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;

二进制类型：
MySQL中的二进制字符串类型主要存储一些二进制数据,比如可以存储图片、音频和视频等二进制数据。

BINARY与VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR,只是它们存储的是二进制字符串

BLOB类型
BLOB是一个 二进制大对象 ,可以容纳可变数量的数据。可以存储一个二进制的大对象,比如 图片 、 音频 和 视频 等。
需要注意的是,在实际工作中,往往不会在MySQL数据库中使用BLOB类型存储大对象数据,通常会将图片、音频和视频文件存储到 服务器的磁盘上 ,并将图片、音频和视频的访问路径存储到MySQL中。

TEXT和BLOB的使用注意事项:
在使用text和blob字段类型时要注意以下几点,以便更好的发挥数据库的性能。
1 BLOB和TEXT值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的" 空洞 ",以后填入这些**“空洞”**的记录可能长度不同。为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行 碎片整理 。
2 如果需要对大文本字段进行模糊查询,MySQL 提供了 前缀索引 。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如,SELECT * 查询就不是很好的想法,除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则,你可能毫无目的地在网络上传输大量的值。
3 把BLOB或TEXT列 分离到单独的表 中。在某些环境中,如果把这些数据列移动到第二张数据表中,可以让你把原数据表中的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会 减少主表中的碎片 ,使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

JSON类型
JSON(JavaScript Object Notation)是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。

CREATE TABLE test_json(
js json
);

向表中插入JSON数据。

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

当需要检索JSON类型的字段中数据的某个具体值时,可以使用“->”和“->>”符号。

空间类型
MySQL中使用 Geometry(几何) 来表示所有地理特征。Geometry指一个点或点的集合,代表世界上任何具有位置的事物

小结及选择建议:
在定义数据类型时,如果确定是 整数 ,就用 INT ; 如果是 小数 ,一定用定点数类型DECIMAL(M,D) ; 如果是日期与时间,就用 DATETIME 。这样做的好处是,首先确保你的系统不会因为数据类型定义出错。不过,凡事都是有两面的,可靠性好,并不意味着高效。比如,TEXT 虽然使用方便,但是效率不如 CHAR(M) 和VARCHAR(M)。
阿里巴巴《Java开发手册》之MySQL数据库:
任何字段如果为非负数,必须是 UNSIGNED
【 强制 】小数类型为 DECIMAL,禁止使用 FLOAT 和 DOUBLE。
说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的时候,得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围,建议将数据拆成整数和小数并分开存储。
【 强制 】如果存储的字符串长度几乎相等,使用 CHAR 定长字符串类型。
【 强制 】VARCHAR 是可变长字符串,不预先分配存储空间,长度不要超过 5000。如果存储长度大于此值,定义字段类型为 TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。