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

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). 浮點型

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和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.日期時間型別

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,'[email protected]','123456789123456789');
-- idCard重復
insert into demo16 values(null,13988888888,'[email protected]','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)必須基于第二范式,消除傳遞依賴(主鍵資訊既可以查到說有關聯資訊)

標籤：MySQL

上一篇：MySQL資料庫升級

下一篇：mysql入門（五）