mysql入门（二）

MySQL数据库中相关概念介绍

1.字符集

字符集:可见字符在保存和传输的时候,二进制的代码集合。

字符集:1. 保存数据；2. 传输数据。

1). MySQL可以在服务器上,数据库,表,字段上指定字符集

2). 服务器上在系统安装的时候就要指定

3). 数据库,表和字段在创建的指定

create table test( name varchar(64) charset gb2312)charset=gbk;

字符编码：字符编码一般在数据库创建的时候就已经决定了。在创建表的时候,字符集默认继承。常用的字符集gbk,utf8两种。gbk叫做中文字符合集,中文字符占用2个字节(一般)；utf8 国际编码,一般是占用3个字节；utf8mb4 是utf8的衍生版,为了解决日益增加的新的字体而创建的,一般是占用4个字节。

对比两种编码,在mysql带来的影响：

create table `test1`( name varchar(21844))charset=utf8;​-- 65535/3 -1 = 21844 ===> varchar最大长度是21844​create table `test2`( name varchar(32766))charset=gbk;​-- 65535 / 2 -1 = 32766

查看MYSQL服务器当前支持的格式：

show variables like 'character_%';

2.校对集

在某种字符集下,字符和字符的关系成为了校对集.比如(ASCll)a和B的大小关系,如果区分大小a>B,如果不区分大小了a<B。不同校对集,比较方式也是不一样的。在数据库创建的时候就可以指定校对集.(默认不区分大小写)。校对集可以给表单独设置

-- _ci 不比较大小的校对集create table t1( name char(1))collate=utf8mb4_general_ci;-- _bin 区分大小写的校对集,默认不使用区分大小写的校对集(增加了开发的难度)create table t2( name char(1))collate=utf8mb4_bin;insert into t1 values('a'),('B');insert into t2 values('a'),('B');

分组查询 order by 升序：

select * from t1 order by name;select * from t2 order by name;

显示所有的校对集：

show character set;show collation;

3.数据类型--值的类型

1). 整型

整形占用字节范围

tinyint1-2^7~2^7-1 (-128-127)

smallint 2-2^15~2^15-1(-32768 - 32767)

mediumint3-2^23-2^23-1(-8388608-8388607)

int4-2^31-2^31-1(-2147483648--2147483647)

bigint8-2^63-2^63-1(太大)

2). 无符号

关键字:unsigned。一个数是无符号的数,那么这个数一定是非负数。age tinyint unsigned:这时的tinyint取值范围是0-255。

create table demo1( age tinyint unsigned);​insert into demo1 set age=-1; -- x​insert into demo1 set age=256; -- x

3). 显示宽度

关键字:zerofill。填充零,整形支持显示宽度,显示的是数值的最小位数。首先用0作为前导，在限定的范围内填充0,超过了限定范围则显示最大长度。

​create table demo2( id int(11) zerofill);​insert into demo2 set id=11;insert into demo2 set id=111111;

4). 浮点型

浮点型占用字节范围

float4-3.4E+38~3.4E+38

double8-1.8E+308~1.8E+308

create table demo3( num1 float, num2 double);​insert into demo3 set num1=3.4E38,num2=1.23E100;​create table demo4( num1 float(10,2), -- 总长度10位,小数点2位 num2 double(11,6));​insert into demo4 set num1=3.446,num2=1.11111111111;

5). 定点型

decimal(M,D),默认(10,0),M是总位数,D小数取舍

​create table demo5( num1 float(10,2), -- 总长度10位,小数点2位 num2 decimal(11,6));insert into demo5 set num1=3.446,num2=1.22222;

4.字符型

数据类型描述

char定长(255)

varchar可变长度(65535)

tinytext2^8-1

text2^16-1

mediumtext2^24-1

longtext2^32-1

char和varchar的对比：

相同点:都是字符类型；

不同点:char是定长,比如我输入一个字符占了6个字节,保存以后占用255个；varchar是可变,比如我输入一个字符占了6个字节,保存以后占用6个。

5.枚举型(enum)

索引有两种类型,一种是枚举型,一种是关联型。枚举类型在WEB中,单选框经常使用。

create table demo6( name char(64), -- 只能选择括号中定义的单一值,索型从1开始 sex enum('男','女','保密'));​insert into demo6 set name='ton1',sex='人妖'; -- x insert into demo6 set name='ton2',sex=1;

枚举型的优点:可以控制值的范围,节省空间,运行效率高。

6.集合类型(set)

先列出很多的值,然后可以挑选多个值。集合类型在WEB中,多选框以往经常使用。

create table demo7( name char(64), sex enum('男','女','保密'), hobby set('吃','喝','抽'));​insert into demo7 values('小李',1,'吃,喝,抽');insert into demo7 values('小王',1,2);

注意：集合看上去和枚举非常相似,但是索引的递增方式不一样:

set('吃','喝','抽')​'''吃 2^0 = 1 索引为 1喝 2^1 = 2 索引为 2抽 2^2​ = 4 索引为 4'''

集合的值可能没有索引占用的空间多。那么多选问题怎么解决?这是一个工程问题​我们可以把爱好(多选问题)单独设置成一张表​。

1 吃

2 喝

3 抽

7.日期时间类型

数据类型描述

date日期

time时间

datetime日期时间

year时间

timestamp时间戳

1). datetime

-- 格式:年-月-日 时:分:秒create table demo8( create_at datetime);​insert into demo8 set create_at = '2019-02-18 00:00:01';​-- now()函数,返回的是当前的时间insert into demo8 set create_at =now();​-- 时间的极限insert into demo8 set create_at = '9999-12-31 23:59:59';

2). date

仅仅表示日期。

3). time

表示时间部分,范围-838:59:59~838:59:59

create table demo9( `time` time);​insert into demo9 set `time`=now();​insert into demo9 set `time`='837:59:59';​insert into demo9 set `time`='10 12:59:59';

4). timestamp

从1970年01月01日 00:00:00的英国格林尼治时间开始计算,单位是秒值。在mysql中时间戳和时间格式的显示方式一致,存储方式不一样

create table demo10( `at` timestamp );​insert into demo10 set `at`=now();insert into demo10 set `at`='2038-01-19 11:14:07';​-- 超过了极限的时间,超过4个字节insert into demo10 set `at`='2038-01-19 11:14:08';

5). year

占用1个,范围1900+1~1900+255

create table demo11( `year` year);​insert into demo11 set `year`=1901;insert into demo11 set `year`=2155;​-- 超过范围了insert into demo11 set `year`=2156;

8.布尔型(boolean)

mysql中也是0和1​。

create table demo12( num boolean);​insert into demo12 set num=True;insert into demo12 set num=False;insert into demo12 set num=1;insert into demo12 set num=0;insert into demo12 set num=100;

9.列的属性--是否为空(null | not null)

create table demo13( name char(64), sex char(4) not null);​insert into demo13 set sex=1;insert into demo13 set name='jack';

10.默认值

create table demo14( name char(64) default 'admin', sex char(4) not null);​insert into demo14 set sex=1;

在开户的时候可以使用,可以给每一个用户添加一个状态status(默认值为1|0)

11.自动增长

auto_increment：默认从1开始,每次递增1,特点:不会出现重复值,主键使用​。最好不要加上not null,not null 必须是用户输入的​。id被删除以后,id没有复用性,记录增长方式。

12.主键

primary key：主键:有且只有一个。特点:不能为空,不能重复。

create table demo15(id int(11) auto_increment, age tinyint(2), -- 这种方式叫做联合主键,但是是一个整体,联合主键不用 PRIMARY KEY(`id`,`age`));

主键:

1.最少性:必须选择一个字段作为主键,如果没有合适的字段作为主键,则自己添加一个字段；

2.稳定性:主键字段不能任意修改。

13.唯一键(unique key)

例如：手机号是唯一

create table demo16(id int(11) auto_increment primary key, mobile char(11) unique, email char(32) unique, idCard char(18) unique);​insert into demo16 values(null,13988889999,'[e-mail]','123456789123456789');-- idCard重复insert into demo16 values(null,13988888888,'[e-mail]','123456789123456789');

14.comment(备注)

备注是程序只之间互相理解代码结构的；好的程序中代码和注释73开​；status comment '状态栏,0是禁用,1是启用'。

15.SQL注释

mysql中两种注释​

单行注释​  --

多行注释​ ​ /*   */

16.数据的完整性

实体:一个实体就是一条记录

域:就是一个字段

引用的完整性:两张表之间的关联的完整(不可避免的)

自定义完整性:自己定义的规则,可以保证各种规范不出现偏差.

(1).保证实体完整性

1.自动增长的列(auto_increment)

2.主键的约束(primary key)

3.唯一键(unique)

(2)保证域的完整性

1.数据类型的约束

2.默认值的约束

3.非空的约束

(3)保证引用的完整性

建立外键->只能先删除从表,才能删除主表

(4)自定义的完整性

1.存储过程 (是mysql当中的特殊自定义函数)

2.触发器 (是一个特殊的存储过程,不用调用)

17. 外键(foreign key)

外键:从表的关联字段。外键的约束是为了保证引用的完整性,外键和主表的关联字段名字可以不一样,数据类型要一致.

特点:

1.主表中不存在的记录,从表中也不允许插入

2.主表中存在的记录,从表中可以先行删除

3.先删除从表,在删除主表

--学校数据库,学生的信息是主表,成绩是从表 create table userinfo( id int auto_increment primary key comment'学号', stu_name char(64)); insert into userinfo values(null,'小明'),(null,'小马'),(null,'小李'),(null,'小刘'); -- 对于从表来说,主键不一定是必须的,但是按照理论应当添加一个create table score( sid int auto_increment primary key comment'主键', stuno int comment'学号', ch float, math float, -- 相当于把关键字写成函数用：从表score中的stuno字段关联主表userinfo中的id字段 foreign key(stuno) references userinfo(`id`));insert into score values(null,1,100,90),(null,3,130,130);

外键的操作：

1.严格约束（constraint）；

2.置空操作(set null),主表删除,从表置空；

3.联级操作(cascade),主表更新,从表联动(更新)；

4.删除的时候也可以使用cascade,这样可以保证表中没有垃圾数据。

外键在数据量比较小的时候使用,我们要设置外键,必须要对表的关系非常的清楚。在从表中的数据和主表中的数据不对等的时候,我们还需要手动添加或删除。

1.设置了外键会影响效率

2.一般来说,每个从表都是单独的数据

外键升级版本 -> 触发器

foreign key(stuno) references userinfo(`id`) on delete set null on update cascade -- 在删除数据的时候设置为空（删完之后，这条信息就是垃圾了)，

在更新的时候级联-- 完整的外键(在建表的时候)foreign key(stuno) references userinfo(`id`) on delete cascade on update cascade -- 在删除数据的时候级联，在更新的时候级联

删除外键,应当删除外键的名字

alter table score drop foreign key `score_ibfk_1`;

添加外键（建表的时候没有设置外键，建完表之后添加外键的操作）,可以自定外键的名称(stuno_id)

alter table `score` add CONSTRAINT `stuno_id` FOREIGN KEY (`stuno`) REFERENCES `userinfo` (`id`)ON DELETE CASCADE ON UPDATE CASCADE;

18. 实体之间的关系

一个实体就是一条记录。

1.一对一的关系

2.一对多的关系

3.多对一的关系

4.多对多的关系

1). 一对一的关系

一个主表中的数据对应一个从表中的数据。

2). 一对多(多对一)的关系

订单系统:一个人买了很多不同类目的商品。

3). 多对多的关系

多对多关系中,不太区分谁是主要的表。

19. 数据库的设计

(1). 标识的实体

(2). 实体的属性

(3). 标识实体之间的关系:根据要求设计

(4). 生成E-R图

20. 数据的规范化

建立表的一些规范,统称为三范式。

(1)第一范式

第一范式:(1NF)确定每一列的数据原子化(不可在拆分)

(2)第二范式

第二范式:(2NF)基于第一范式,一张表描述一件事物(从表的非主键字段依赖主键字段)

(3)第三范式

第三范式:(3NF)必须基于第二范式,消除传递依赖(主键信息既可以查到说有关联信息)