mySQL 教程 第3章 数据类型和数据完整性

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

准备数据库

创建练习数据库,以下实验都是在这个数据库中完成。

clip_image001

clip_image002

clip_image003

clip_image004

clip_image005

clip_image006

练习1:比较各种数值型

clip_image007

create table tmp1

(

id INT,

name VARCHAR(25),

deptId decimal(5,1),

salary FLOAT

);

执行desc tmp1;

显示

clip_image008

插入两行记录查看结果

insert into tmp1 VALUES (199,'韩立刚',3223.32,544.5);

insert into tmp1 VALUES (19999,'韩旭',32223.23,5445.23323);

查看结果

select * from tmp1

clip_image009

练习2: 日期和时间类型

1. Year数据类型练习

create TABLE tmp2(y YEAR);

insert INTO tmp2 values(2010),('2012'),('2166');

select * from tmp2;

year的取值范围1901-2155

clip_image010

可以看到超过取值范围值为0

Year可以使用2位数构造年份

delete from tmp2;

insert INTO tmp2 values('0'),('12'),('66'),('00');

select * from tmp2;

clip_image011

根据输入的值自动使用取值范围1901-2155构造年份

2. Time 数据类型表示方式

create table tmp3(t TIME);

insert into tmp3 VALUES ('10:05:23'),('23:43'),('2 10:10'),('2 02'),('10');

select * from tmp3;

clip_image012

下面看time数值另外一种输入方法

delete from tmp3

insert into tmp3 VALUES ('101112'),(111212),('0'),('107010');

select * from tmp3;

clip_image013

插入系统时间

delete from tmp3

insert into tmp3 VALUES (CURRENT_TIME),(NOW());

select * from tmp3;

clip_image014

3. Data类型表示方式

取值范围1000-01-01---9999-12-3

表示方法YYYY-MM-DD或 YYYYMMDD

create table tmp4(d DATE);

insert into tmp4 VALUES (CURRENT_DATE),('2010-02-12'),('19920302'),('20121219');

select * from tmp4;

clip_image015

向表中插入YY-MM-DD和YYMMDD格式日期

delete from tmp4;

insert into tmp4 VALUES (111111),(221212),('661209'),('120923'),('99-11-21');

select * from tmp4;

clip_image016

4. Datetime数据类型练习

该数据类型包括日期和时间,需要8个字节存储,格式’YYYY-MM-DD HH:MM:SS

create table tmp5 (dt DATETIME);

insert into tmp5 values ('1999-08-08 08:08:08'),('19750605080808'),('20100201121212');

select * from tmp5

clip_image017

插入系统时间

insert into tmp5 values (now());

select * from tmp5

clip_image018

5. TimeStamp

Datatime在存储时间时,按着实际输入的格式存储,即输入什么就存储什么,与时区无关,timestamp值的存储以UTC(世界标准时间)格式保存,存储时对当前时区进行转换,检索时在转换回当前时区。即查询时,根据当前时区不同,显示的时间值不同。

北京时间比格林尼治时间(世界时)早8小时,即:北京时间=世界时+8小时。

create table tmp6

(

ts timestamp

)

select now();

INSERT into tmp6 values (NOW());

select * from tmp6;

clip_image019

更改mySQL时区

set time_zone='+10:00' 默认时间是+8:00

select * from tmp6;

clip_image020

可以看到时间戳类型会根据mySQl的时区调整输出结果。

练习3:字符串类型

1. 比较Char和varchar

Char(M) 存储占用M字节 1<=M<=255

Varchar(M) 存储L个字符,占用L+1字节,L<=M 并且 1<=M<=255

比如Vch varchar(5) vch=’abcde’ 占用了6个字节

Vch=’ab’ 占用3个字节

create table tmp7

(

ch CHAR(4),

vch VARCHAR(4)

);

insert into tmp7 VALUES ('ab ','ab ');

insert into tmp7 VALUES ('abcde','abcdefg');

select CONCAT('(',ch,')'),CONCAT('(',vch,')') from tmp7

clip_image021

MySQL中concat函数

使用方法:

CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串

2. Text数据类型不删除尾部空格

Text 占用的空间L+2 L<2^16 即L<65536个字节

比如 t text; t=’abcd’ 存储占用6个字节

Tinytext占用的空间L+1 L<2^8

Text占用的空间L+2 L<2^16

MediumText占用的空间L+3 L<2^24

LongText占用的空间L+4 L<2^32

create table tmp8

(

t TEXT

);

insert into tmp8 VALUES ('How are you! Fine,thank you! ');

select CONCAT('(',t,')') from tmp8

clip_image022

3. Enum类型

定义性别为枚举数据类型,只允许输入男女,只允许选取一个值。

create table tmp9

(

sname char(20),

sex ENUM('男','女')

);

drop table tmp9

insert into tmp9 values('韩立刚','男'),('韩立辉','男'),('韩旭','女')

select * from tmp9

clip_image023

insert into tmp9 values('张飞','南')

select * from tmp9

clip_image024

枚举类型数值 可以查看索引,从1开始,空值为索引值为0,使用以下方法可以看到枚举类型的索引值

select sname,sex,sex+0 from tmp9

clip_image025

向表中插入数据时,对于枚举类型的数据库可以使用索引值插入。

insert into tmp9 values('刘备',1),('貂蝉','2')

select * from tmp9 where sname in ('刘备','貂蝉')

clip_image026

4. Set数据类型

是字符串对象,可以零个或多个值,Set列最多可以有64个成员。指定多值时,使用‘,’隔开。

创建表存储用户的兴趣爱好。

create table tmp10

(

sname char(20),

hobby SET('音乐','旅游','游泳','读书','美术')

);

insert into tmp10 values ('韩立刚','音乐,美术')

insert into tmp10 values ('韩旭','音乐,美术,音乐')

insert into tmp10 values ('韩愈','音乐,跳舞,美术')

select * from tmp10

clip_image027

数据完整性

数据完整性类型

clip_image028

实体完整性

每一条记录必须唯一,每个实体必须拥有一个主键或者其他的唯一标识列,SQL实现实体完整性主要通过唯一索引、UNIQUE约束、Primary Key约束或Identity属性,强制表的标识符列或主键来实现。

域完整性

考虑该列应该输入哪些值,域完整性限制列输入的值的范围,包括正确的数据类型、格式和有效的数据范围。

比如性别 只允许输入男女,成绩 只允许输入 1-100

通过 数据类型 Check约束 规则 Foreign Key约束 Default定义 Not Null定义

在mySQL中是set或enum类型来实现

引用完整性

又称为参照完整性,在插入和删除数据时,引用完整性用于维持参照表和被参照表之间的数据一致性。

在mySQL中,应用完整性通过主键(Primary Key)和外键(Foreign Key)约束来实现。

数据完整性实例

创建数据库schoolDB2和三张表

--创建学生表

CREATE TABLE `TStudent` (

`StudentID` varchar(15) NOT NULL,

`Sname` varchar(10) DEFAULT NULL,

`sex` char(1) DEFAULT NULL,

`cardID` varchar(20) DEFAULT NULL,

`Birthday` datetime DEFAULT NULL,

`Email` varchar(40) DEFAULT NULL,

`Class` varchar(20) DEFAULT NULL,

`enterTime` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--创建课程表

create table TSubject

(

subJectID nvarchar(10),

subJectName nvarchar(30),

BookName nvarchar(30),

Publisher nvarchar(20)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

--创建分数表

create table TScore

(

StudentID nvarchar(15),

subJectID nvarchar(10),

mark decimal

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

练习4:实体完整性实例(主键)

为TStudent创建主键

clip_image029

clip_image030

clip_image031

clip_image032

同样给TSubject表subJectID列创建主键

clip_image033

使用命令给TScore表添加复合主键。图形界面只支持单列创建主键。

alter table TScore add primary key (StudentID,subJectID);

刷新后可以看到创建的复合主键

clip_image034

设置主键后向Tstudent表插入3条记录

insert into TStudent (StudentID,Sname,sex)values ('00003','韩立刚','男');

insert into TStudent (StudentID,Sname,sex)values ('00002','韩立军','男');

insert into TStudent (StudentID,Sname,sex)values ('00001','韩旭','女');

insert into TStudent (StudentID,Sname,sex)values ('00003','韩庆利','男');

select StudentID,Sname,sex from TStudent

你只能看到3条记录第四条没有插入成功

clip_image035

练习5:域完整性(默认值)

将Tstudent表的class列的默认值设置为’网络班’。

clip_image036

插入一条记录

insert into TStudent (StudentID,Sname,sex)values ('00004','韩庆利','男');

select StudentID,Sname,sex,class from TStudent

可以看到Class默认为网络班

clip_image037

练:6:参照完整性(外键参照)

设置外键参照,需要将表的存储引擎设置为InnoDB,事务型数据库的首选引擎,支持ACID事务,支持行级锁定。在上面创建表时已经指定了存储引擎,在这里确认一下,如果不是InnoDB,要更改为InnoDB。

clip_image038

clip_image039

clip_image040

5. 设置外键参照

clip_image041

clip_image042

clip_image043

clip_image044

查看表之间引用关系,将三个表拖拽过去,可以看到之间的关系,你也可以在这里建立外键。

clip_image045

6. 验证参照完整性

清除TStudent表所有记录

delete from TStudent

插入4条记录

insert into TStudent (StudentID,Sname,sex)values ('00004','韩庆利','男');

insert into TStudent (StudentID,Sname,sex)values ('00003','韩立刚','男');

insert into TStudent (StudentID,Sname,sex)values ('00002','韩立军','男');

insert into TStudent (StudentID,Sname,sex)values ('00001','韩旭','女');

插入两门课程

insert into TSubject values ('0001','计算机网络','计算机网络','清华出版社')

insert into TSubject values ('0002','数据结构','数据结构','人邮出版社')

select * from `TSubject`

clip_image046

插入成绩 4个学生两门课

insert into TScore values ('00001','0001',89);

insert into TScore values ('00002','0001',79);

insert into TScore values ('00003','0001',87);

insert into TScore values ('00004','0001',58);

insert into TScore values ('00001','0002',69);

insert into TScore values ('00002','0002',85);

insert into TScore values ('00003','0002',88);

insert into TScore values ('00004','0002',98);

查看插入的成绩

select * from TScore

clip_image047

插入一个不存在的课程编号为0003的试一下,看看是否能够插入成功。

insert into TScore values ('00004','0003',98);

可以看到插入失败

clip_image048




本文转自 onesthan 51CTO博客,原文链接:http://blog.51cto.com/91xueit/1137965,如需转载请自行联系原作者

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
265
分享
相关文章
SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
通过详细的步骤和示例代码,您可以在 SpringBoot 项目中成功集成 Flink CDC,并实时追踪 MySQL 数据库的变动。
233 43
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
80 9
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制,包括表空间、段、区、页和行的具体结构,以及如何设计和优化行数据存储以提高性能。
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
216 6
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
176 42
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等