AI 助理
备案控制台登录注册
开发者社区 数据库 文章 正文

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

2017-11-21 1006
版权
举报
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 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 版
关系型数据库
存储
索引
数据库
MySQL
SQL
关键词：
云数据库 RDS MySQL 版数据
云数据库 RDS MySQL 版教程
云数据库 RDS MySQL 版数据类型数据
云数据库 RDS MySQL 版数据类型
云数据库 RDS MySQL 版完整性
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
技术小阿哥
+关注
13665文章
目录
打赏
0
0
0
0
265
分享
相关文章
蓝易云
|
1月前
|
Java 关系型数据库 MySQL
SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
通过详细的步骤和示例代码，您可以在 SpringBoot 项目中成功集成 Flink CDC，并实时追踪 MySQL 数据库的变动。
蓝易云
233 43
东阳马生架构
|
1月前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分，包括表空间、段、区、页和行。表空间是最高逻辑层，包含多个段；段由若干个区组成，每个区包含64个连续的页，页用于存储多条行记录。文章还详细解析了Page结构，分为通用部分（文件头与文件尾）、数据记录部分和页目录部分。此外，文中探讨了行记录格式，包括四种行格式（Redundant、Compact、Dynamic和Compressed），重点介绍了Compact行记录格式及其溢出机制。最后，文章解释了不同行格式的特点及应用场景，帮助理解InnoDB存储引擎的工作原理。
东阳马生架构
57 6
MySQL底层概述—4.InnoDB数据文件
蓝易云
|
18天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询，需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化，可以显著提升查询性能，确保系统的高效稳定运行。
蓝易云
35 9
蓝易云
|
24天前
|
监控 关系型数据库 MySQL
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时，优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略，可以显著提高查询效率。本文介绍的技巧和方法，可以为开发人员在数据处理和查询优化中提供有效的指导，提升系统的性能和用户体验。掌握这些技巧后，您可以在处理海量数据时更加游刃有余。
蓝易云
80 9
拉丁解牛说技术
|
25天前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制，包括表空间、段、区、页和行的具体结构，以及如何设计和优化行数据存储以提高性能。
拉丁解牛说技术
35 1
1639437117631681
|
24天前
|
存储 SQL 关系型数据库
【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'; 【解决/规避方法】与应用确认存储的数据，正确定义数据
1639437117631681
21 0
蓝易云
|
3月前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍，希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小，并结合优化技术提升查询性能，以满足实际业务需求。
蓝易云
216 6
游客r2tgrg3ez7yzc
|
2月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示：“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后，连接时提示不允许连接问题解决
游客r2tgrg3ez7yzc
235 69
蓝易云
|
1月前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤，希望对您有所帮助。
蓝易云
176 42
游客kufrkwrbkmpsa
|
6天前
|
SQL 关系型数据库 MySQL
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案，最后总结了迁移体验，包括工具部署和操作特点，也指出功能有优化空间及暂不支持的部分，期待其不断优化。
游客kufrkwrbkmpsa
23 1

热门文章

最新文章

  • 1
    基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
    16
  • 2
    ELK实现nginx、mysql、http的日志可视化实验
    31
  • 3
    如何排查和解决PHP连接数据库MYSQL失败写锁的问题
    14
  • 4
    如何解决 MySQL 数据库服务器 CPU 飙升的情况
    15
  • 5
    MySQL 和 Oracle 的区别？
    18
  • 6
    MySQL 中的全文索引：强大的文本搜索利器
    11
  • 7
    RDS用多了，你还知道MySQL主从复制底层原理和实现方案吗？
    13
  • 8
    seatunnel配置mysql2hive
    35
  • 9
    MySQL和SQLSugar百万条数据查询分页优化
    18
  • 10
    100%兼容MySQL！手把手教你基于PolarDB搭建RAG系统
    19
  • 1
    无缝集成 MySQL，解锁秒级 OLAP 分析性能极限，完成任务可领取三合一数据线！
    59
  • 2
    Dataphin功能Tips系列（50）-离线集成输入端支持mysql数据源分库分表
    30
  • 3
    MySQL复制以及调优
    21
  • 4
    简单聊聊MySQL的三大日志（Redo Log、Binlog和Undo Log）各有什么区别
    25
  • 5
    如何在宝塔mysql修改掉3306端口
    21
  • 6
    MySQL8官方YUM仓库使用指南
    33
  • 7
    mysql的undo log、redo log、bin log、buffer pool
    18
  • 8
    MySQL生产环境迁移至YashanDB数据库深度体验
    23
  • 9
    使用崖山YMP 迁移 Oracle/MySQL 至YashanDB 23.2 验证测试
    13
  • 10
    mysql的undo log、redo log、bin log、buffer pool
    32

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    获取百炼API-KEY
    目录
    AI助理

    你好，我是AI助理

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