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不同校对集,比较方式也是不一样的。在数据库创建的时候就可以指定校对集.(默认不区分大小写)。校对集可以给表单独设置

-- _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). 整型

整形	占用字节	范围
tinyint	1	-2^7~2^7-1 (-128-127)
smallint	2	-2^15~2^15-1(-32768 - 32767)
mediumint	3	-2^23-2^23-1(-8388608-8388607)
int	4	-2^31-2^31-1(-2147483648--2147483647)
bigint	8	-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). 浮点型

浮点型	占用字节	范围
float	4	-3.4E+38~3.4E+38
double	8	-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)
tinytext	2^8-1
text	2^16-1
mediumtext	2^24-1
longtext	2^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,"jack@aliyun.com","123456789123456789");
-- idCard重复
insert into demo16 values(null,13988888888,"jack@aliyun.cn","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,这样可以保证表中没有垃圾数据。

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

设置了外键会影响效率
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)必须基于第二范式,消除传递依赖(主键信息既可以查到说有关联信息)