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

第3章 数据类型和数据完整性

准备数据库

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

练习1：比较各种数值型

create table tmp1

(

id INT,

name VARCHAR(25),

deptId decimal(5,1),

salary FLOAT

);

执行desc tmp1;

显示

插入两行记录查看结果

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

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

查看结果

select * from tmp1

练习2： 日期和时间类型

1. Year数据类型练习

create TABLE tmp2(y YEAR);

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

select * from tmp2;

year的取值范围1901-2155

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

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

delete from tmp2;

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

select * from tmp2;

根据输入的值自动使用取值范围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;

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

delete from tmp3

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

select * from tmp3;

插入系统时间

delete from tmp3

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

select * from tmp3;

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;

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

delete from tmp4;

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

select * from tmp4;

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

插入系统时间

insert into tmp5 values (now());

select * from tmp5

5. TimeStamp

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

北京时间比格林尼治时间（世界时）早8小时，即：北京时间=世界时+8小时。

create table tmp6

(

ts timestamp

)

select now();

INSERT into tmp6 values (NOW());

select * from tmp6;

更改mySQL时区

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

select * from tmp6;

可以看到时间戳类型会根据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

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

3. Enum类型

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

create table tmp9

(

sname char(20),

sex ENUM('男','女')

);

drop table tmp9

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

select * from tmp9

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

select * from tmp9

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

select sname,sex,sex+0 from tmp9

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

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

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

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

数据完整性

数据完整性类型

实体完整性

每一条记录必须唯一，每个实体必须拥有一个主键或者其他的唯一标识列，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创建主键

同样给TSubject表subJectID列创建主键

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

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

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

设置主键后向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条记录第四条没有插入成功

练习5：域完整性（默认值）

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

插入一条记录

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

select StudentID,Sname,sex,class from TStudent

可以看到Class默认为网络班

练:6：参照完整性（外键参照）

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

5. 设置外键参照

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

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`

插入成绩 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

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

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

可以看到插入失败