1.數(shù)據(jù)庫字符集

1.1字符集

字符集: 可見字符在保存和傳輸?shù)臅r候?qū)?yīng)的二進制代碼集合. 
字符集在兩個地方用到

1.保存數(shù)據(jù)
2.數(shù)據(jù)傳輸12

1.2在存續(xù)時使用字符集

1. MySQl可以在服務(wù)器上, 數(shù)據(jù)庫, 表, 字段上指定字符編碼.2. 在服務(wù)器上指定字符編碼是安裝服務(wù)器的時候指定的.3. 數(shù)據(jù)庫, 表, 字段上是創(chuàng)建時候指定的123

create table test(
-> name varchar(10) charset utf8
->)charset=gbk;123

1.3 gbk, gb2312, utf8的區(qū)別

這三種都是常用的字符編碼方式, gbk和gb2312(自帶)是簡體中文的編碼, utf8是國際通用編碼. 
Python, MySQL, jQuery等等都是開源的產(chǎn)品, 開源產(chǎn)品建議使用utf8編碼 
gbk和gb2312都是簡體中文, 但是內(nèi)部編碼方式是不一樣的. 
在python中

在gbk或gb2312下，一個中文占用2個字節(jié)
在utf8下，一個中文占用3個字節(jié)12

注意:在數(shù)據(jù)庫中如果字符編碼是utf8的，一個漢字字符長度就是1，gbk一個漢字的字節(jié)長度是2。

create table `emp`(
sex varchar(21844)
)engine=myisam charset=utf8;65535/3-1create table `emp2`(
sex varchar(32766)
)engine=myisam charset=gbk;65535/2-1123456789101112

注意;數(shù)據(jù)庫如果是UTF8,那么你在此庫中建立的’表’如果沒有設(shè)定字符集,默認使用數(shù)據(jù)庫的字符集 

1.4 在傳輸?shù)臅r候字符編碼

 
 
mysql 服務(wù)器接收的編碼

mysql> show variables like 'collation%'; 查看排序集mysql> show variables like 'character_%';
+--------------------------+-----------------------------------------------+| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+| character_set_client     | utf8  #服務(wù)器接受客戶端的字符碼                 |
| character_set_connection | utf8                                          |
| character_set_database   | gbk                                           |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8  #返回結(jié)果的字符編碼                       |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          || character_sets_dir       | C:\phpStudy\PHPTutorial\MySQL\share\charsets\ |
+--------------------------+-----------------------------------------------+8 rows in set (0.00 sec)123456789101112131415

現(xiàn)在客戶端發(fā)送的編碼和服務(wù)器接收的編碼不一致

解決:#windows默認的是GBKset character_set_client=gbk;字符編碼不一樣返回的就不一樣返回的是utf8   windows不認識會亂碼解決:set character_set_results=gbk;小技巧:set names gbk; ->set names 可以一次設(shè)置修改3個字符編碼;mysql> show variables like 'character_%';+--------------------------+-----------------------------------------------+| Variable_name            | Value                                         |+--------------------------+-----------------------------------------------+| character_set_client     | gbk                                           || character_set_connection | gbk                                           || character_set_database   | gbk                                           || character_set_filesystem | binary                                        || character_set_results    | gbk                                           || character_set_server     | utf8                                          || character_set_system     | utf8                                          || character_sets_dir       | C:\phpStudy\PHPTutorial\MySQL\share\charsets\ |+--------------------------+-----------------------------------------------+8 rows in set (0.00 sec)1

2.校對集

在某種字符集下, 字符和字符的關(guān)系成為校對集. 比如(ASCII)a和B的大小關(guān)系, 如果區(qū)分大小寫 a>B, 如果不區(qū)分大小寫a

#utf8_general_ci  使用_ci這種校對集不區(qū)分大小寫create table t1(
name char(1)
)charset=utf8 collate=utf8_general_ci;create table t2(
name char(1)
)charset=utf8 collate=utf8_bin;insert into t1 values ('a'),('B');insert into t2 values ('a'),('B');1234567891011

mysql> select * from t1 order by name;
+------+| name |
+------+| a    || B    |
+------+2 rows in set (0.00 sec)

#排序查詢以后沒有區(qū)分大小寫說明a和b不區(qū)分大小寫;12345678910

mysql> select * from t2 order by name;
+------+| name |
+------+| B    || a    |
+------+2 rows in set (0.00 sec)

#按照正序排列,對照ASCLL編碼,B<a,B在a前面那么這種二進制校對集區(qū)分大小寫;12345678910

牢記:
_bin：按二進制編碼比較
_ci：不區(qū)分大小寫比較123

| Charset  | Description                 | Default collation   | Maxlen |12

#顯示所有校對集mysql> show collation;12

3.MySQL的數(shù)據(jù)類型–值類型

(1)整型

(2)unsigned(無符號)

一個數(shù)是無符號數(shù), 那么這個數(shù)肯定是非負數(shù) 
數(shù)據(jù)庫mysql第一條記錄一定是1(起碼是),絕對不能是0

無符號數(shù)的范圍相當于是有符號數(shù)的兩倍.

mysql> create table test1(
    -> age tinyint unsigned    -> );Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (128);
Query OK, 1 row affected (0.00 sec)1234567

(3)顯示寬度

整形支持顯示寬度, 顯示寬度就是最小的顯示位數(shù), 比如int(11)表示最少用11 
位數(shù)字表示這個數(shù), 如果不夠用0來做前導. 默認情況下顯示位數(shù)不起作用, 必須集合zerofill才起作用

create table stu(
id int(5),
age int(5) zerofill
);

#插入測試數(shù)據(jù)
insert into stu values (1,23),(2,123456);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from stu;
+------+--------+| id   | age    |
+------+--------+|    1 |  00023 ||    2 | 123456 |    #超過顯示寬度5就顯示值本身
+------+--------+1234567891011121314151617

顯示寬度不決定整形的顯示大小, 只是在值的位置不夠的時候用前導0來填充, 如果超過顯示位數(shù), 就不加前導0, 顯示值本身

(4)浮點型

create table test2(
  num1 float,
  num2 double
  );insert into test2 values(23.123,2.123);#float理論上只保留小數(shù)點一位,根據(jù)實際版本
#double理論上只保留小數(shù)點兩位, 根據(jù)實際版本insert into test2 values(2.2E2,2.123);#也支持科學計數(shù)法insert into test2 values(999.999999999999999999999999999,2.123456);#丟失精度1234567891011121314

python中我們沒有雙精度這一類型, float代表浮點數(shù).

(5)定點數(shù)

decimal(M,D),M的最大值是65,D的最大值是30,默認是(10,0)

create table test3(    num1 float(10,2),    num2 decimal(10,2)    );insert into test3 values(123.57,1234567.00);mysql> select * from test3;
+-------------+-------------+| num1        | num2        |
+-------------+-------------+| 12345679.00 |      123.57 || 12345679.00 | 12346623.57 |
+-------------+-------------+2 rows in set (0.00 sec)


#定點數(shù)可以保留多個小數(shù)點位,它在有些情況下也會失去精度.浮點數(shù)的執(zhí)行效率要高于定點數(shù).
#浮點數(shù)和定點數(shù)都支持 無符號.1234567891011121314151617181920

4.MySQL的數(shù)據(jù)類型–字符類型

char(10)和varchar(10)

    相同點:都是不能超過10個字符
    不同點:char你輸入5個字符,它還是占用了10個字符
        varchar你輸入5個字符,他會返還多于的空間        char的最大長度是255
        varchar的理論長度是65535
        實際上達不到,我使用的是gbk(mysql中占用2個字符),那么65535/2.12345678

create table test(
  name varchar(32766)
  )charset=gbk;#utf8在創(chuàng)建的時候報錯了,最多支持21845個字符,因為utf8一個漢字要使用3個字符create table test(
  name varchar(65535)
  )charset=gbk

#utf8在創(chuàng)建的時候報錯了,最多支持32767個字符,因為gbk一個漢字要使用2個字符123456789101112

5.MySQL數(shù)據(jù)類型–枚舉(enum)

列出一些選項的, 單選.

create table test(
name varchar(32),
sex enum('男','女','保密')
);insert into test values('賈乃亮','男');#插入正常insert into test values('PGONE','人妖');#插入報錯123456789

枚舉型在數(shù)據(jù)庫內(nèi)部是通過整型來管理的,第一個值1,第二個值是2,依次向后推insert into test values('王寶強',1);insert into test values('劉運斌',3);枚舉型的優(yōu)點:
1.限制值
2.節(jié)省空間
3.運行效率高
思考:已知枚舉型占用2個字節(jié),請問最多可以設(shè)置多少個枚舉值?

2個字節(jié)16位,2^16-1,最多可保存65535/2個值12345678910111213

6.MySQL數(shù)據(jù)類型–集合(set)

列出數(shù)據(jù)類型, 保存多選

create table test2(
name varchar(32),
hobby set('吃','睡覺','看書','鑒黃')
);insert into test2 values('劉運斌','吃,睡覺,鑒黃');insert into test2 values('劉運斌-2','吃,睡覺,李小璐');1234567

集合和枚舉一樣,為每個元素分配一個固定的值,分配方式不一樣.
它的管理也是使用整型
集合是這么分配的:'吃','睡覺','看書','鑒黃'2^0   2^1    2^2   2^3  如果后面還有依次向后推
保存的時候,把多個值(轉(zhuǎn)換為整型了)加起來,這樣值會邊的很大(浪費資源)
一個集合他的元素通過一個位表示,有幾個集合元素就需要幾個位.

思考:已知集合占用8個字節(jié),那么集合能保存多少個選項?
只能保存64個選項.1234567891011

7.MySQL數(shù)據(jù)類型–日期時間型

(1)datetime

#格式 年-月-日 時:分:秒create table test4(
create_at datetime
);insert into test4 values('2018-01-12 15:00:53');insert into test4 values(now());#表示的是當前的時間insert into test4 values('10000-12-31 23:59:59'); #錯誤12345678

(2)date

僅表示時間的日期部分

(3)time

表示時間部分, 也表示時間間隔, 范圍是-838:59:59 ~ 838:59:59

create table test5(
create_at time);insert into test5 values('12:12:12');insert into test5 values('212:12:12');insert into test5 values('-800:12:12');insert into test5 values('-839:59:59');  #錯誤的,超出范圍

#time支持以天的方式表示時間間隔insert into test5 values('10 12:59:59');1234567891011

(4)timestamp

從1970年1月1日 00:00:00秒的格林威治時間開始計算,在python中是特殊類型,但在mysql中顯示為datatime格式

create table test6(
create_at timestamp);insert into test6 values(now());insert into test6 values('2018-01-12 15:43:30');insert into test6 values('2038-01-19 11:14:07');#這里就是一個節(jié)點

\insert into test6 values('2038-01-19 11:14:08');#超過了4個字節(jié)
timestamp在格式上和datetime是一樣的,它們的區(qū)別在于:
datetime 從1到9999,而timestamp從1970年~2038年.
timestamp占用4個字節(jié),到2038年超過4個字節(jié)的長度了.12345678910111213

(5)year

占用一個字節(jié), 只能是255個數(shù), 以1900年為基數(shù), 范圍1900+1 ~ 1900+255.

create table test7(
create_at year);insert into test7 values(1900);#錯誤的insert into test7 values(1901);insert into test7 values(2155);insert into test7 values(2156);#錯誤的,不在范圍內(nèi)12345678

8.布爾型(boolean)

mysql不支持布爾型, 所以用1和0代替

create table test8(
num boolean
);insert into test8 values(True);insert into test8 values(1);insert into test8 values(False);insert into test8 values(0);insert into test8 values('A');#錯誤

布爾型的使用是比較少的,可以用tinyint代替,或者用enum代替1234567891011

9.列屬性–是否為空(null|not null)

create table test9(
id int auto_increment primary key not null comment'主鍵',
username varchar(64) not null,
pwd char(64) null);insert into test9 values(null,'admin','');insert into test9 values(null,'','');insert into test9 values(null,null,'');insert into test9 values(null,'',null);#空字符串不等于null123456789101112

10.列屬性–(default)

create table test10(
id int auto_increment primary key not null comment'主鍵',
username varchar(64) not null,
pwd char(64) null default '123456');insert into test10(username) values('admin2');#推薦的SQL語句create table test11(
age int  default '123456');insert into test11 values(null);#輸入null加插入null,不寫是默認1234567891011121314

11.自動增長列

auto_increment
字段值默認從1開始,每次遞增1,特點:不會有重復的值,主鍵常用.

友情提示:在mysql中自動增長的列必須作為主鍵.
自動增長的烈在插入的時候可以輸入(null)

被刪除的id不能被再次使用:
如果中間的摸個ID值比較大,那么后面插入的id根據(jù)最大值依次+112345678

12.列屬性–主鍵

primary key
主鍵:主鍵是唯一的
特點:不能為空,也不能重復
一個表只有一個主鍵CREATE TABLE `test12` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `num` int(11) NOT NULL,  `age` int(11) NOT NULL,  PRIMARY KEY (`id`,`num`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#以上的主鍵有'3'個,其實一個主鍵,這個3個字段組成(一個聯(lián)合主鍵),在開發(fā)中幾乎不會被用上
#(教學使用)create table demo1(
id int auto_increment primary key);create table demo2(
id int auto_increment ,primary key(id)
);create table demo3(
id int(11) 
);alter table demo3 add primary key(id);選擇主鍵的原則:
1.最少性:必須選擇單個鍵作為主鍵
2.穩(wěn)定性:作為主鍵的這個字段最好不要被修改123456789101112131415161718192021222324252627282930313233

13.列屬性–唯一鍵(unique)

特點:不能重復, 不能為空 
一個表可以有多個唯一鍵

create table test13(
uid int auto_increment primary key,
mobile char(11) unique,
email char(32) unique,
pwd char(32)
);insert into test13 values(null,'13877776666','this_dog@qq.com','123456');insert into test13 values(null,'13877776665','this_do@qq.com','123456');#修改表屬性方法添加唯一鍵alter table test13 add unique `my`(mobile);#刪除唯一鍵還是使用刪除的方法alter table test13 drop index mobile;#有別名刪除別名,沒別名刪除字段名12345678910111213141516

14.列屬性–備注(comment)

備注用來給程序員相互交流使用的
有點:起碼你能看的動這個字段是用來干嗎的.

stauts tinyint comment'描述一個人的狀態(tài) 1表示死了,2表示活的,0表示半死不活' 1234

15.SQL注釋

python的注釋# 單行注釋'''

''' 多行注釋

mysql的注釋# 單行注釋--單行注釋

/*注釋的內(nèi)容*/ 多行注釋1234567891011

select * from test13 where uname=” ” or 1=1 –” ” and pwd=” “; 
練習

#手機號碼一般使用什么數(shù)據(jù)類型儲存?字符型 char(11)#性別使用什么數(shù)據(jù)類型?字符型 (枚舉型) 布爾型#年齡用什么?整型 tinyint unsigned#照片用什么?binary  字符(只保存路徑,不保存資源)
varchar()#工資用什么類型?decimal 定點型 
#學員的姓名允許為空嗎?不允許#家庭地址可以為空嗎?最好不可以#電子郵箱可以為空嗎?對于目前來說有一點分量,隨意,最好不用#考試成績可以為空?不允許 default 0 #在主鍵列輸入的數(shù)值,能為空嗎?肯定不能 null當作占位符#一個表可以有多個主鍵嗎?不可以#在一個學校的數(shù)據(jù)庫中,如果這個學校允許學員重名, 班級不允許有重名的,那么把班級和學生的姓名作為組合主鍵可以嗎?primary key(id,name)
可以,但是不合理#標識列允許使用字符串類型嗎?可以,但不允許#表中沒有合適的列(字段)作為主鍵怎么辦?自己添加一個自增長的字段作為主鍵

原文地址https://blog.csdn.net/qq_41637554/article/details/80859645