中山建设网站公司,做培训体系的网站,网站建设要架服务器,烟台网站seo服务文章目录 一、管理数据库1.1、连接数据库1.2、创建库1.3、选择数据库1.4、修改数据库名称1.5、查看数据库信息1.6、删除库 二、定义数据表字段2.1、数据表字段的数据类型2.2、数据表字段属性2.3、约束讲解2.3.1、约束的定义1#xff09;为什么需要约束2#xff09;什么是约束… 文章目录 一、管理数据库1.1、连接数据库1.2、创建库1.3、选择数据库1.4、修改数据库名称1.5、查看数据库信息1.6、删除库 二、定义数据表字段2.1、数据表字段的数据类型2.2、数据表字段属性2.3、约束讲解2.3.1、约束的定义1为什么需要约束2什么是约束 2.3.2、NOT NULL (非空约束)1添加非空约束2删除非空约束 2.3.3、UNIQUE唯一性约束1添加约束2删除约束 2.3.4、PRIMARY KEY主键约束1添加约束2删除约束 2.3.5、AUTO_INCREMENT自增列约束1设置自增约束2删除自增约束 2.3.6、FOREIGN KEY外键约束1外键约束的特点2 约束等级3添加外键约束4删除外键约束 2.3.7、DEFAULT默认值约束 2.4、数值类型2.4.1、整数类型2.4.2、浮点数类型2.4.3、定点数类型 2.5、时间与日期类型2.5.1、YEAR2.5.2、DATE类型2.5.3、TIME类型2.5.4、DATETIME类型2.5.5、TIMESTAMP 2.6、文本字符串类型2.6.1、char与varchar类型1、CHAR类型2、VARCHAR类型3、varchar与char的选择 2.6.2、TEXT类型2.6.3、ENUM类型2.6.4、SET类型 2.7、二进制字符串类型2.7.1、BINARY与VARBINARY类型2.7.2、blob 2.8、JSON类型 三、操作数据表字段3.1、添加列3.2、修改列3.3、删除列 四、操作数据表4.1、创建表4.2、删除表4.3、修改表名4.4、表设置备注 一、管理数据库
1.1、连接数据库
在命令行输入mysql -u root -p
mysql -u root -p 1.2、创建库
drop database if exists 旧库名;mysql create database database_name;# 判断数据库中是否存在同名数据库
mysql create database if not exists database_name;# 指定编码格式
mysql create database if not exists database_name default character set utf8;# 指定校验集
mysql create database if not exists database_name default character set utf8mb4 collate utf8_general_ci;1.3、选择数据库
use database_name1.4、修改数据库名称
rename database db_name to new_db_nameMySQL 5.1.23版本之后就将此SQL语句去掉了因为此SQL语句可能会造成数据丢失 1.5、查看数据库信息
# 查看数据库支持的字符集
show charset;# 查看系统默认的字符集
show variables like character_set_database;# 查看系统支持的字符集校验规则
show collation# 查看系统默认校验规则
show variables like collation_database;# 查看MySQL中存在的数据库
show databases;# 查看MySQL命令行所在的数据库
select database();# 查看数据库的创建信息
show create database database_name \G 或者 show create database database_name1.6、删除库
drop databases [if exists] 库名;二、定义数据表字段
2.1、数据表字段的数据类型 2.2、数据表字段属性 关于属性 CHARACTER SET name
1.创建数据库时指明字符集
create database if not exists dbtest12 character set utf8;show create database dbtest12;2.创建表的时候指明表的字符集
create table temp(
id int
)character set utf8;show create table temp;3.创建表指明表中的字段时可以指定字段的字符集
create table temp1(
id int,
name varchar(15) character set gbk
);show create table temp1;2.3、约束讲解
2.3.1、约束的定义
1为什么需要约束
数据完整性Data Integrity是指数据的精确性Accuracy和可靠性(Reliability。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑 实体完整性Entity Integrity 例如同一个表中不能存在两条完全相同无法区分的记录。 域完整性Domain Integrity 例如年龄范围0-120性别范围“男/女”。 引用完整性Referential Integrity 例如员工所在部门在部门表中要能找到这个部门。 用户自定义完整性User-defined Integrity 例如用户名唯一、密码不能为空等本部门经理的工资不得高于本部门职工的平均工资的5倍。
2什么是约束
在讲解数据表管理之前必须先讲解约束。约束用于限制表中数据的规则强制执行表中数据的完整性和准确性。
根据约束数据列的限制约束可以分为单列约束和多列约束。
根据约束的作用范围约束可以分为①列级约束只能作用在一个列上和②表级约束可以作用在多个列上单独定义
根据约束的作用可以分为 NOT NULL非空约束规定某个字段不能为空 UNIQUE唯一约束规定某个字段在整个表中是唯一的 PRIMARY KEY主键约束主键(非空且唯一)约束 FOREIGN KEY(外键约束) CHECK(检查约束) DEFAULT(默认值约束)
2.3.2、NOT NULL (非空约束)
限定某个字段/某列的值不允许为空 空字符串’不等于NULL0也不等于NULL 。一个表可以有很多列都分别限定了非空 1添加非空约束
建表时
CREATE TABLE 表名称( 字段名 数据类型, 字段名 数据类型 NOT NULL, 字段名 数据类型 NOT NULL
);建表后
alter table 表名称 modify 字段名 数据类型 not null;2删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;
#去掉not null相当于修改某个非注解字段该字段允 许为空 或
alter table 表名称 modify 字段名 数据类型;
#去掉not null相当于修改某个非注解字段该字段允许为空2.3.3、UNIQUE唯一性约束
用来限制某个字段/某列的值不能重复。
同一个表可以有多个唯一约束。唯一约束可以是某一个列的值唯一也可以多个列组合的值唯一。唯一性约束允许列值为null。在创建唯一约束的时候如果不给唯一约束命名就默认和列名相同。MySQL会给唯一约束的列上默认创建一个唯一索引。
1添加约束
建表时
create table 表名称( 字段名 数据类型, 字段名 数据类型 unique, 字段名 数据类型 unique key, 字段名 数据类型
);
create table 表名称( 字段名 数据类型, 字段名 数据类型, 字段名 数据类型, [constraint 约束名] unique key(字段1,字段2...)
);建表后
#字段列表中如果是一个字段表示该列的值唯一。
# 如果是两个或更多个字段那么复合唯一即多个字段的组合是唯一的#方式1
alter table 表名称 add unique key(字段1,字段2...); #方式2
alter table 表名称 modify 字段名 字段类型 unique;2删除约束
删除唯一约束只能通过删除唯一索引的方式删除唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称如果是单列就默认和列名相同如果是组合列那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
alter table 表名 drop index 唯一约束名;2.3.4、PRIMARY KEY主键约束
主键约束用来唯一标识表中的一行记录。主键约束相当于唯一约束非空约束的组合主键约束列不允许重复也不允许出现空值。
一个表最多只能有一个主键约束建立主键约束可以在列级别创建也可以在表级别上创建主键约束对应着表中的一列或者多列复合主键MySQL的主键名总是PRIMARY就算自己命名了主键约束名也没用当创建主键约束时系统默认会在所在的列或列组合上建立对应的主键索引能够根据主键查询的就根据主键查询效率更高。如果删除主键约束了主键约束对应的索引就自动删除了
1添加约束
建表时
create table 表名称( 字段名 数据类型 primary key, #列级模式 字段名 数据类型, 字段名 数据类型
);create table 表名称( 字段名 数据类型, 字段名 数据类型, 字段名 数据类型, [constraint 约束名] primary key(字段名) #表级模式
);
// 添加符合主键约束只能在表级模式下添加建表后
alter table 表名称 add primary key(字段列表);
# 字段列表可以是一个字段也可以是多个字段
# 如果是多个字段的话是复合主键2删除约束
删除主键约束不需要指定主键名因为一个表只有一个主键删除主键约束后非空还存在。
alter table 表名称 drop primary key;2.3.5、AUTO_INCREMENT自增列约束
自增约束的关键字为auto_increment要求某个字段的值自增。
一个表最多只能有一个自增长列当需要产生唯一标识符或顺序值时可设置自增长自增长列约束的列必须是键列主键列唯一键列自增约束的列的数据类型必须是整数类型如果自增列指定了 0 和 null会在当前最大值的基础上自增如果自增列手动指定了具体值直接赋值为具体值。
1设置自增约束
建表时
create table 表名称(字段名 数据类型 primary key auto_increment,字段名 数据类型 unique key not null,字段名 数据类型 unique key,字段名 数据类型 not null default 默认值,
);
create table 表名称(字段名 数据类型 default 默认值 ,字段名 数据类型 unique key auto_increment,字段名 数据类型 not null default 默认值,,primary key(字段名)
)建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;2删除自增约束
alter table 表名称 modify 字段名 数据类型;
#去掉auto_increment相当于删除此处还有一个自增值的持久化问题 MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变都会将其写入重做日志中。如果数据库重启InnoDB会根据重做日志中的信息来初始化计数器的内存值。 2.3.6、FOREIGN KEY外键约束
外键约束foreign key限定某个表的某个字段的引用完整性。比如员工表的员工所在部门的选择必须在部门表能找到对应的部分。 主表和从表父表和子表 主表父表被引用的表、被参考的表 学生和班级表中的班级表。从表子表引用别人的表、参考别人的表学生和班级表中的学生表学生表中的班级字段引用班级表的主键id。 1外键约束的特点
创建外键约束时 在创建表时就指定外键约束先创建主表再创建从表并且是在从表中指定外键约束一个表中可以创建多个外键约束。从表的外键列必须引用/参考主表的主键或唯一约束的列。因为需要保证被依赖/被参考的值必须是唯一的 从表的外键列和主表被参照的列之间两者名字可以不同但是数据类型必须一致。当创建外键约束时系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。根据外键查询效率很高创建外键约束的同时可以设置外键约束等级。删除外键约束后需要手动删除索引 操作主表、从表中的记录时 假如没有设置外键约束等级下列操作会失败①更新主表中的记录时涉及主表的被参考字段、②删除主表中的记录。要想操作成功需要先删除从表中依赖该记录的数据然后才可以在主表中删除该记录。添加外键约束后主表的修改和删除、从表的添加和修改操作都受约束。
2 约束等级 3添加外键约束
建表时
create table 主表名称(字段1 数据类型 primary key,字段2 数据类型
);
create table 从表名称(字段1 数据类型 primary key,字段2 数据类型,
[constraint 外键约束名称] foreign key(从表的某个字段) references 主表名(被参考字段)
);建表后
alter table 从表名 add [constraint 约束名] foreign key(从表的字段) references 主表名(被引用字段) [on update xx][on delete xx];4删除外键约束
(1)第一步先查看约束名和删除外键约束
select * from information_schema.table_constraints where table_name 表名称;#查看某个表的约束名alter table 从表名 drop foreign key 外键约束名;2第二步查看索引名和删除索引。注意只能手动删除
show index from 表名称; #查看某个表的索引名alter table 从表名 drop index 索引名;2.3.7、DEFAULT默认值约束
2.4、数值类型
2.4.1、整数类型
整数有以下类型tinyint、smallint、mediumint、int、bigint存储单位都是字节。 经常会看到tinyint(1)其实和tinyint是一样的。 同一种整数类型有符号与无符号所表示的数值范围也不同。其中有符号整数的最小值是一个负数无符号整数的最小值是0。 如果使用的数据类型超出了整数类型的范围则MySQL会抛出相应的错误。因此在实际使用的时候应该首先确认好数据的取值范围然后根据确认的结果选择合适的整数类型。 2.4.2、浮点数类型
浮点数类型主要有两种单精度浮点数FLOAT和双精度浮点数DOUBLE。 对于浮点数来说有符号与无符号所表示的数值范围也是不同的 浮点数类型中的FLOAT和DOUBLE类型在不指定数据精度时默认会按照实际的计算机硬件和操作系统决定的数据精度进行显示。如果用户指定的精度超出了浮点数类型的数据精度则MySQL会自动进行四舍五入操作。 对于浮点数来说可以使用(M,D)的方式进行表示精度(M,D)表示当前数值包含整数位和小数位一共会显示M位数字其中小数点后会显示D位数字M又被称为精度D又被称为标度。 点数不写精度和标度时会按照计算机硬件和操作系统决定的数据精度进行显示。如果用户指定的精度超出了浮点数类型的数据精度则MySQL会自动进行四舍五入操作数据能够插入MySQL中并能够正常显示。 2.4.3、定点数类型
MySQL中的定点数类型只有decimal一种类型。decimal类型也可以使用(M,D)进行表示。定点数在MySQL内部是以字符串的形式进行存储的它的精度比浮点数更加精确适合存储表示金额等需要高精度的数据。
使用定点数类型表示数据时当数据的精度超出了定点数类型的精度范围时则MySQL同样会进行四舍五入处理。当decimal类型不指定精度和标度时其默认为decimal(10,0)。
2.5、时间与日期类型 2.5.1、YEAR 从MySQL5.5.27开始2位格式的YEAR已经不推荐使用。YEAR默认格式就是”YYYY”没必要写成YEAR(4) 从MySQL8.0.19开始不推荐使用指定显示宽度的YEAR(4)数据类型。 以4位字符串或数字格式表示YEAR类型其格式为YYYY最小值为1901最大值为2155。 以2位字符串格式表示YEAR类型最小值为00最大值为99。 当取值为01到69时表示2001到2069当取值为70到99时表示1970到1999当取值整数的0或00添加的话那么是0000年当取值是日期/字符串的0添加的话是2000年。
2.5.2、DATE类型
在向 DATE 类型的字段插入数据时同样需要满足一定的格式条件。
以YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期其最小取值为1000-01-01最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。以YY-MM-DD 格式或者YYMMDD 格式表示的字符串日期此格式中年份为两位数值或字符串满足YEAR类型的格式条件为当年份取值为00到69时会被转化为2000到2069当年份取值为70到99时会被转化为1970到1999。使用 CURRENT_DATE()或者 NOW() 函数会插入当前系统的日期。
2.5.3、TIME类型
使用HH:MM:SS 格式来表示 TIME 类型其中 HH 表示小时 MM 表示分钟 SS 表示秒。
向TIME类型的字段插入数据时可以使用的格式
①可以使用带有冒号的字符串比如’ D HH:MM:SS’ 、 ’ HH:MM:SS ’ 、 ’ HH:MM ’ 、 ’ D HH:MM ’ 、 ’ D HH ’ 或 ’ SS ’ 格式都能被正确地插入TIME 类型的字段中。其中 D 表示天其最小值为 0 最大值为 34 。如果使用带有 D 格式的字符串插入TIME 类型的字段时 D 会被转化为小时计算格式为 D*24HH 。当使用带有冒号并且不带 D 的字符串表示时间时表示当天的时间比如12:10 表示 12:10:00 而不是 00:12:10 。②可以使用不带有冒号的字符串或者数字格式为 ’ HHMMSS ’ 或者 HHMMSS 。如果插入一个不合法的字符串或者数字 MySQL在存储数据时会将其自动转化为00:00:00 进行存储。比如 1210 MySQL 会将最右边的两位解析成秒表示00:12:10而不是 12:10:00 。③使用 CURRENT_TIME() 或者 NOW() 会插入当前系统的时间。
2.5.4、DATETIME类型
在格式上为DATE 类型和 TIME 类型的组合可以表示为 YYYY-MM-DD HH:MM:SS - YYYY 表示年份 - MM 表示月份- DD 表示日期 - HH 表示小时- MM 表示分钟 - SS 表示秒。以YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时最小值为1000-01-01 00:00:00最大值为9999-12-03 23:59:59。以YYYYMMDD HHMMSS格式的数字插入DATETIME类型的字段时会被转化为YYYY-MM-DD HH:MM:SS格式。以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS格式的字符串插DATETIME类型的字段时两位数的年份规则符合YEAR类型的规则00到69表示2000到206970到99表示1970到1999。使用函数current_timestamp() 和now()可以向datetime类型的字段插入系统的当前日期和时间 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期插入DATETIME类型的字段时会自动转换为YYYY-MM-DD 00:00:00 以YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS格式的字符串插入DATE类型的字段时会自动转换为YYYY-MM-DD进行存储。 在查询、删除、修改语句的where字段中也会遇到上述情况。 2.5.5、TIMESTAMP
显示格式与 datetime 类型相同都是 YYYY-MM-DD HH:MM:SS 需要 4 个字节的存储空间。timestamp 存储的时间范围比 datetime 要小很多只能存储 1970-01-01 00:00:01 UTC到 2038-01-19 03:14:07 UTC之间的时间。其中UTC表示世界统一时间也叫 作世界标准时间。 存储数据的时候需要对当前时间所在的时区进行转换查询数据的时候再将时间转换回当前的时区。因此使用 TIMESTAMP 存储的同一个时间值在不同的时区查询时会显示不同的时间。 timestamp和datetime的区别 timestamp存储空间比较小表示的日期时间范围也比较小。底层存储方式不同timestamp底层存储的是毫秒值距离1970-1-1 0:0:0 0毫秒的毫秒值。两个日期比较大小或日期计算时timestamp更方便、更快。timestamp和时区有关。timestamp会根据用户的时区不同显示不同的结果。而datetime则只能反映出插入时当地的时区其他时区的人查看数据必然会有误差的。 2.6、文本字符串类型
MySQL中文本字符串总体上分为 char 、 varchar 、 tinytext 、 text 、 mediumtext 、longtext 、enum 、 set 等类型。
2.6.1、char与varchar类型
CHAR和VARCHAR类型都可以存储比较短的字符串。
1、CHAR类型
char(M) 类型一般需要预先定义字符串长度。如果不指定(M)则表示长度默认是1个字符。如果保存时数据的实际长度比char类型声明的长度小则会在右侧填充空格以达到指定的长度。当MySQL检索char类型的数据时char类型的字段会去除尾部的空格。定义char类型字段时声明的字段长度即为char类型字段所占的存储空间的字节数。
2、VARCHAR类型 varchar(M) 定义时必须指定长度M否则报错。 MySQL4.0版本以下varchar(20)指的是20字节如果存放UTF8汉字时只能存6个每个汉字3字节 MySQL5.0版本以上varchar(20)指的是20字符。 检索varchar类型的字段数据时会保留数据尾部的空格。varchar类型的字段所占用的存储空间为字符串实际长度加1个字节。
3、varchar与char的选择 存储很短的信息。比如门牌号码101201……这样很短的信息应该用char因为varchar还要占个 byte用于存储信息长度本来打算节约存储的结果得不偿失。固定长度的。比如使用uuid作为主键那么使用char更合适。十分频繁改变的column。因为varchar每次存储都要有额外的计算得到长度等工作如果一个 非常频繁改变的那就要有很多的精力用于计算而这些对于char来说是不需要的。具体存储引擎中的情况 MyISAM 数据存储引擎和数据列MyISAM数据表最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化从而使 数据检索更快用空间换时间。MEMORY 存储引擎和数据列MEMORY数据表目前都使用固定长度的数据行存储因此无论使用 CHAR或VARCHAR列都没有关系两者都是作为CHAR类型处理的。InnoDB 存储引擎建议使用VARCHAR类型。因为对于InnoDB数据表内部的行存储格式并没有区分固定长度和可变长度列所有数据行都使用指向数据列值的头指针而且主要影响性能的因素是数据行使用的存储总量由于char平均占用的空间多于varchar所以除了简短并且固定长度的其他考虑varchar。这样节省空间对磁盘I/0和数据存储总量比较好。
2.6.2、TEXT类型
text用来保存文本类型的字符串 由于实际存储的长度不确定MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况你只能采用 CHAR(M)或者 VARCHAR(M)。 TEXT文本类型可以存比较大的文本段搜索速度稍慢因此如果不是特别大的内容建议使用CHAR VARCHAR来代替。还有TEXT类型不用加默认值加了也没用。而且text和blob类型的数据删除后容易导致 “空洞”使得文件碎片比较多所以频繁使用的表不建议包含TEXT类型字段建议单独分出去单独用 一个表。
2.6.3、ENUM类型
enum类型也叫作枚举类型enum类型的取值范围需要在定义字段时进行指定。设置字段值时ENUM 类型只允许从成员中选取单个值不能一次选取多个值。 其所需要的存储空间由定义ENUM类型时指定的成员个数决定。
当ENUM类型包含1255个成员时需要1个字节的存储空间当ENUM类型包含25665535个成员时需要2个字节的存储空间。ENUM类型的成员个数的上限为65535个。
create table test_enum(season enum(春,夏,秋,冬,unknow)
);insert into test_enumvalues(春),(秋);# 忽略大小写
insert into test_enumvalues(UNKNOW);# 允许按照角标的方式获取指定索引位置的枚举值
insert into test_enumvalues(1),(3);# Data truncated for column season at row 1
insert into test_enumvalues(ab);# 当ENUM类型的字段没有声明为NOT NULL时插入NULL也是有效的
insert into test_enumvalues(NULL);select * from test_enum;数据库保存枚举数据的时候使用enum类型还是tinyint类型呢 使用enum 优点enum 类型直接在数据库层面对枚举值进行了约束只能插入预定义的枚举成员。这可以确保数据的完整性防止非法值的插入。缺点一旦定义了 enum 类型若需添加或删除枚举成员通常需要修改表结构这可能涉及复杂的数据库迁移或停机维护。 使用tinyint tinyint 类型或其他整数类型可以轻松地通过增加或减少数值范围来适应枚举成员的变化无需修改表结构。几乎所有数据库系统都支持整数类型使用 tinyint 保存枚举可以保持代码的数据库无关性更容易移植到不同的数据库系统。工作中还是tinyint更加频繁。 2.6.4、SET类型
SET表示一个字符对象可以包含0个或多个成员但成员个数的上限为64设置字段值时可以取取值范围内的0个或多个值。SET类型在存储数据时成员个数越多其占用的存储空间越大。注意SET类型在选取成员时可以一次 选择多个成员这一点与ENUM类型不同。 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);
#Data truncated for column s at row 1select * from test_set;2.7、二进制字符串类型
MySQL中的二进制字符串类型主要存储一些二进制数据比如可以存储图片、音频和视频等二进制数据。
2.7.1、BINARY与VARBINARY类型
binary和varbinary类似于char和varchar只是它们存储的是二进制字符串。binary (M)为固定长度的二进制字符串M表示最多能存储的字节数取值范围是0~255个字符。如果未指定(M)表示只能存储1个字节 。例如BINARY (8)表示最多能存储8个字节如果字段值不足(M)个字 节将在右边填充’\0’以补齐指定长度。varbinary (M)为可变长度的二进制字符串M表示最多能存储的字节数总字节数不能超过行的字节长度限制65535另外还要考虑额外字节开销VARBINARY类型的数据除了存储数据本身外还需要1或2个 字节来存储数据的字节数。varbinary类型必须指定(M) 否则报错。
2.7.2、blob blob是一个二进制大对象 可以容纳可变数量的数据。 MySQL中的blob类型包括tinyblob、blob、mediumblob和longblob 4种类型它们可容纳值的最大长度不同。可以存储一个二进制的大对象比如图片 、 音频和视频 等。 需要注意的是在实际工作中往往不会在MySQL数据库中使用BLOB类型存储大对象数据通常会将图片、音频和视频文件存储到服务器的磁盘上 并将图片、音频和视频的访问路径存储到MySQL中。 text和blob的使用注意事项 BLOB和TEXT值也会引起自己的一些问题特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的空洞以后填入这些空洞的记录可能长度不同。为了提高性能建议定期使用 optimize table 功能对这类表进行碎片整理。 如果需要对大文本字段进行模糊查询MySQL 提供了 前缀索引。但是仍然要在不必要的时候避免检索大型的blob或text值。例如SELECT * 查询就不是很好的想法除非你能够确定作为约束条件的 WHERE子句只会找到所需要的数据行。否则你可能毫无目的地在网络上传输大量的值。 把BLOB或TEXT列分离到单独的表中。在某些环境中如果把这些数据列移动到第二张数据表中可以让你把原数据表中的数据列转换为固定长度的数据行格式那么它就是有意义的。这会 减少主表中的碎片使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。
2.8、JSON类型
Mysql5.7.8版本及其以后提供了一个原生的Json字段类型Json类型的值将不再以字符串的形式存储而是采用一种允许快速读取文本元素document elements的内部二进制internal binary格式。在Json列插入或者更新的时候将会自动验证Json文本未通过验证的文本将产生一个错误信息。
MySQL 提供了一些内置的 JSON 操作函数用于处理和查询存储在 JSON 类型列中的数据。以下是一些常用的 MySQL JSON 操作函数 JSON_EXTRACT(json_doc, path)从 JSON 文档中提取指定路径的值。例如JSON_EXTRACT({name: John, age: 30}, $.name) 将返回 “John”。 JSON_UNQUOTE(json_val)移除 JSON 字符串值的引号。例如JSON_UNQUOTE(John) 将返回 “John”。 JSON_SEARCH(json_doc, one_or_all, search_str [, escape_char [, path] ...])在 JSON 文档中搜索指定字符串并返回匹配的路径。one_or_all 参数可选用于指定搜索所有匹配项还是仅返回第一个匹配项。例如JSON_SEARCH({name: John, age: 30}, all, John) 将返回 [$.name]。 JSON_ARRAYAGG(expr)将表达式的结果作为 JSON 数组聚合。例如SELECT JSON_ARRAYAGG(name) FROM users 将返回包含所有用户名的 JSON 数组。 JSON_OBJECT(key1, value1 [, key2, value2, ...])创建一个 JSON 对象其中包含指定的键值对。例如JSON_OBJECT(name, John, age, 30) 将返回 {name: John, age: 30}。 JSON_ARRAY(elements)创建一个 JSON 数组其中包含指定的元素。例如JSON_ARRAY(John, 30) 将返回 [John, 30]。
这些函数只是 MySQL 提供的一部分 JSON 操作函数还有其他更多函数可用于处理 JSON 数据。你可以根据实际需求选择适合的函数来操作和查询 JSON 类型列中的数据。
需要注意的是这些 JSON 操作函数在 MySQL 5.7 及更高版本中可用要确保你的 MySQL 版本支持这些函数的使用。
CREATE TABLE test_json(
js json
);INSERT INTO test_json (js)
VALUES ({name:songhk, age:18, address:{province:beijing,
city:beijing}});SELECT * FROM test_json;当需要检索JSON类型的字段中数据的某个具体值时可以使用“-”和“-”符号
SELECT
js - $.name AS NAME,
js - $.age AS age ,
js - $.address.province AS province,
js - $.address.city AS city
FROM test_json;-- 创建测试表
create table tab_json (id bigint(20) not null auto_increment comment 主键id ,data json default null comment json字符串,primary key(id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENTjson测试表;-- 查询tab_json表
select * from tab_json;
-- 删除tab_json表
drop table tab_json;-- ----------------------------------------------------------------
-- 新增数据
insert into tab_json(id, data) values (1, {Tel: 132223232444, name: david, address: Beijing});
insert into tab_json(id, data) values (2, {Tel: 13390989765, name: Mike, address: Guangzhou});
insert into tab_json(id, data) values (3, {success: true,code: 0,message: ,data: {name: jerry,age: 18,sex: 男}});
insert into tab_json(id, data) values (4, {success: true,code: 1,message: ,data: {name: tome,age: 30,sex: 女}});-- ----------------------------------------------------------------
-- json_extract
select json_extract({name:Zhaim,tel:13240133388},$.tel);
select json_extract({name:Zhaim,tel:13240133388},$.name);-- ----------------------------------------------------------------
-- 对tab_json表使用json_extract函数
select json_extract(data,$.name) from tab_json;#如果查询没有的key,那么是可以查询,不过返回的是NULL.
select json_extract(data,$.name),json_extract(data,$.Tel) from tab_json;
select json_extract(data,$.name),json_extract(data,$.tel) from tab_json;
select json_extract(data,$.name),json_extract(data,$.address) from tab_json;-- ----------------------------------------------------------------
-- 条件查询
select json_extract(data,$.name),json_extract(data,$.Tel) from tab_json where json_extract(data,$.name) Mike; -- ----------------------------------------------------------------
-- 嵌套json查询
select * from tab_json where json_extract(data,$.success) true;
select json_extract(data,$.data) from tab_json where json_extract(data,$.success) true;
-- 查询data对应json中key为name的值
select json_extract( json_extract(data,$.data),$.name) from tab_json where json_extract(data,$.code) 1;
select json_extract( json_extract(data,$.data),$.name),json_extract( json_extract(data,$.data),$.age) from tab_json where json_extract(data,$.code) 0; -- ----------------------------------------------------------------
-- 性能验证 , 通过验证全部都是全表扫描使用场景数据量不大json字符串较大则可以采用数据量较大不建议使用。
explain select * from tab_json where json_extract(data,$.success) true;
explain select json_extract(data,$.data) from tab_json where json_extract(data,$.success) true;
-- 查询data对应json中key为name的值
explain select json_extract( json_extract(data,$.data),$.name) from tab_json where json_extract(data,$.code) 1;
explain select json_extract( json_extract(data,$.data),$.name),json_extract( json_extract(data,$.data),$.age) from tab_json where json_extract(data,$.code) 0; -- ------------------------------------------------------------------ 查询json对接集合对象
INSERT INTO tab_json(id, data) VALUES (5, {employee:[{name: zhangsan, code: 20220407001, age: 18},{name: zhangsan2, code: 20220407002, age: 28}]});INSERT INTO tab_json(id, data) VALUES (6, {employee:[{name: lisi, code: 20220407003, age: 16},{name: lisi2, code: 20220407004, age: 26}]});-- 查询data对应json值的employee集合对象中name为zhangsan的用户
select * from tab_json tj where JSON_CONTAINS(json_extract(tj.data,$.employee),JSON_OBJECT(name, zhangsan));
-- zhangsan3不存在返回空
select * from tab_json tj where JSON_CONTAINS(json_extract(tj.data,$.employee),JSON_OBJECT(name, zhangsan3));
三、操作数据表字段
3.1、添加列
alter table 表名 add column 列名 类型 [列约束];mysql drop table IF EXISTS test14;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql
mysql create table test14(- a int not null AUTO_INCREMENT PRIMARY KEY comment 字段a- );
Query OK, 0 rows affected (0.02 sec)
mysql alter table test14 add column b int not null default 0 comment 字段b;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql alter table test14 add column c int not null default 0 comment 字段c;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql insert into test14(b) values (10);
Query OK, 1 row affected (0.00 sec)
mysql select * from test14; c
----------
| a | b | c |
----------
| 1 | 10 | 0 |
----------
1 row in set (0.00 sec)3.2、修改列
alter table 表名 modify column 列名 新类型 [约束];
或者
alter table 表名 change column 列名 新列名 新类型 [约束];2种方式区别modify不能修改列名change可以修改列名
3.3、删除列
alter table 表名 drop column 列名;四、操作数据表
4.1、创建表
create table 表名(字段名1 类型[(宽度)] [约束条件] [comment 字段说明],字段名2 类型[(宽度)] [约束条件] [comment 字段说明],字段名3 类型[(宽度)] [约束条件] [comment 字段说明]
)[表的一些设置];在同一张表中字段名不能相同宽度和约束条件为可选参数字段名和类型是必须的最后一个字段后不能加逗号类型是用来限制字段 必须以何种数据类型来存储记录类型后写的约束条件是在类型之外的 额外添加的约束 4.2、删除表
drop table [if exists] 表名;4.3、修改表名
alter table 表名 rename [to] 新表名;4.4、表设置备注
alter table 表名 comment 备注信息;
