參考與：http://codingxiaxw.cn/

1.解決中文亂碼問(wèn)題

1.1查看MySQL數(shù)據(jù)庫(kù)編碼

在命令行中輸入:SHOW VARIABLES LIKE 'CHAR%'; 回車(chē)可看到如下內(nèi)容:

Paste_Image.png

1.2編碼解釋

• character_set_client: mysql使用該編碼來(lái)解讀客戶(hù)端發(fā)送過(guò)來(lái)的數(shù)據(jù)，例如該編碼為utf，那么如果客戶(hù)端發(fā)送過(guò)來(lái)的數(shù)據(jù)不是utf，就會(huì)出現(xiàn)亂碼。
• character_set_results：mysql會(huì)把數(shù)據(jù)轉(zhuǎn)換成該編碼后，再發(fā)送給客戶(hù)端，例如該編碼為utf，那么如果客戶(hù)端不使用utf來(lái)解讀，那么就會(huì)出現(xiàn)亂碼，其它編碼只要支持中文即可。

除了這兩個(gè)編碼格式外我們還需要考慮一個(gè)工具(用于編寫(xiě)sql語(yǔ)句的工具)的編碼格式，即控制臺(tái)(用命令行寫(xiě)sql語(yǔ)句)或者用于寫(xiě)sql語(yǔ)句的可視化工具，為什么要用”或”呢，因?yàn)槎呔褪且惑w，修改一個(gè)即一起修改了兩個(gè)的編碼格式。下面1.3節(jié)我在分析亂碼原因后會(huì)告訴你如何保持client、results以及控制臺(tái)與可視化工具這四者編碼格式的一致，以解決中文亂碼問(wèn)題。

1.3控制臺(tái)亂碼問(wèn)題

windows系統(tǒng)中:

• 插入或修改時(shí)出現(xiàn)亂碼：因?yàn)榭刂婆_(tái)默認(rèn)使用gbk，而character_set_client不是gbk的原因。
• 查詢(xún)出的數(shù)據(jù)為亂碼，因?yàn)閏haracter_set_results不是gbk而控制臺(tái)默認(rèn)使用gbk。
• 解決方法:通過(guò)在命令行中輸入代碼set character_set_client=gbk;和set character_set_results=gbk;來(lái)設(shè)置編碼。

通過(guò)將client和results的編碼格式改成gbk后使得它們二者和控制臺(tái)的編碼格式一致，這樣便可以實(shí)現(xiàn)中文亂碼問(wèn)題解決了編碼的問(wèn)題。

注意：通過(guò)上述方式我們將client、results、控制臺(tái)這三者編碼方式設(shè)置一致，設(shè)置編碼只對(duì)當(dāng)前連接有效，當(dāng)退出mysql后再次登陸mysql時(shí)又回到utf了。解決方法:找到my.ini配置文件，在配置文件中設(shè)置set default_character=gbk即可。

或是保持client和results的編碼格式繼續(xù)為utf-8，然后將控制臺(tái)默認(rèn)的的gbk編碼方式改為utf-8格式，這種方法我沒(méi)試過(guò)，畢竟我用的是mac，估計(jì)也是在控制臺(tái)的my.ini配置文件中進(jìn)行設(shè)置。

而對(duì)于我的mac系統(tǒng):由于mac的控制臺(tái)默認(rèn)編碼不是gbk(我也不知道m(xù)ac系統(tǒng)默認(rèn)是什么編碼格式)，我為什么知道它的默認(rèn)編碼不是gbk呢，因?yàn)槲倚薷牧薱lient和results的編碼格式為gbk后，在控制臺(tái)中輸入中文仍顯示亂碼，所以我才知道它的默認(rèn)編碼格式不是gbk。針對(duì)mac用戶(hù)我也提供如下兩種方法:

• 1.在命令行中輸入set character_set_client=gbk;和set character_set_results=gbk;來(lái)設(shè)置client和results的編碼格式為gbk，然后再修改控制臺(tái)的編碼格式為gbk。那么如何修改控制臺(tái)的編碼格式呢?mac的控制臺(tái)編碼格式是與編寫(xiě)sql語(yǔ)句的可視化工具連在一起的，修改可視化工具的編碼格式便可以修改控制臺(tái)的編碼格式。而我用的可視化工具為MySQL Workbench，所以我在MySQL Workbench中創(chuàng)建數(shù)據(jù)庫(kù)時(shí)指定創(chuàng)建數(shù)據(jù)庫(kù)的編碼格式為gbk并在創(chuàng)建表時(shí)也設(shè)置表的編碼格式為gbk,這樣便保持了它們編碼格式的一致，成功解決中文亂碼問(wèn)題。

• 2.保持client和results的編碼格式繼續(xù)為utf-8,然后修改可視化工具創(chuàng)建數(shù)據(jù)庫(kù)時(shí)的編碼格式為utf-8并在創(chuàng)建表時(shí)也指定表的編碼格式為utf-8,通過(guò)這種方式也保持了它們?nèi)呔幋a格式的一致，成功解決編碼亂碼的問(wèn)題。

2.備份數(shù)據(jù)庫(kù)與恢復(fù)數(shù)據(jù)庫(kù)

2.1備份數(shù)據(jù)庫(kù)

備份就是將數(shù)據(jù)庫(kù)導(dǎo)出為sql腳本。在命令行中輸入:mysqldump -u用戶(hù)名 -p密碼 數(shù)據(jù)庫(kù)名>導(dǎo)出文件路徑

注意:1.末尾不要打分號(hào)。2.執(zhí)行此語(yǔ)句前應(yīng)該先退出mysql客戶(hù)端。3.導(dǎo)出的內(nèi)容不包括創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句只包含數(shù)據(jù)庫(kù)里面的內(nèi)容。

2.2恢復(fù)數(shù)據(jù)庫(kù)

就是將導(dǎo)出的sql腳本插入到數(shù)據(jù)庫(kù)中。有如下兩種實(shí)現(xiàn)方式:

• 第一種方式:1.登錄mysql:mysql -u用戶(hù)名 -p密碼。2.創(chuàng)建數(shù)據(jù)庫(kù):create database 數(shù)據(jù)庫(kù)名。3.輸入命令:mysql -uroot -p密碼 數(shù)據(jù)庫(kù)名<備份的數(shù)據(jù)路徑并回車(chē)。
• 第二種方式:1.刪除數(shù)據(jù)庫(kù):drop database 數(shù)據(jù)庫(kù)名。2.重新創(chuàng)建數(shù)據(jù)庫(kù):create database 數(shù)據(jù)庫(kù)名。3.切換到數(shù)據(jù)庫(kù):use 數(shù)據(jù)庫(kù)名。4.輸入命令:source sql腳本路徑并回車(chē)。

3.約束

約束是添加在列上用來(lái)約束列的。

3.1主鍵約束(primary key)

特點(diǎn):1.非空。2.唯一。3.可被引用。當(dāng)表的某一列被指定為主鍵后，該列的值就不能為空，也不能有重復(fù)值出現(xiàn)。

• 創(chuàng)建表時(shí)指定主鍵的兩種方式:

1.create table emp(
  empno int primary key,
  ename varchar(50)
);
2.create table emp(
  empno int,
  ename varchar(50),
  primary key(empno)
 );

• 修改表時(shí)指定主鍵的方式:

alter table emp
and primary key;

• 修改表時(shí)刪除主鍵的方式:

alter table emp
drop primary key;

3.2主鍵自增長(zhǎng)(auto_increment)

create table student(
id int primary key auto_increment,
name varchar(50)
);

注意:auto_increment必須添加在int類(lèi)型后，指定主鍵自增長(zhǎng)后，插入數(shù)據(jù)時(shí)便可以給該主鍵設(shè)置null值。

限制:主鍵自增長(zhǎng)在群集環(huán)境下不好使，所以大部分情況下我們使用UUID來(lái)作主鍵。

3.3非空約束(not null)

因?yàn)槟承┝胁荒茉O(shè)置為null值，所以可以對(duì)列添加非空約束。

create table student(
id int primary key auto_increment,
name varchar(50) not null
);

3.4唯一約束(unique)

create table student(
id int primary key auto_increment,
name varchar(50) not null unique
);

3.5概念模型

• 在java中表現(xiàn)為對(duì)象模型：在java中是domain。例如：User、Student。

• 在數(shù)據(jù)庫(kù)中表現(xiàn)為關(guān)系模型：在數(shù)據(jù)庫(kù)中表現(xiàn)為 表。
  當(dāng)我們要完成一個(gè)軟件系統(tǒng)時(shí)，需要把系統(tǒng)中的實(shí)體抽取出來(lái)，形成概念模型。例如部門(mén)、員工都是系統(tǒng)中的實(shí)體。概念模型中的實(shí)體最終會(huì)成為java中的類(lèi)、數(shù)據(jù)庫(kù)中的表。實(shí)體之間還存在著關(guān)系，關(guān)系有三種:

• 1對(duì)多:例如員工和部門(mén)的關(guān)系

• 1對(duì)1:例如老公和老婆的關(guān)系

• 多對(duì)1:例如老師與學(xué)生的關(guān)系
  對(duì)象模型：可以雙向關(guān)聯(lián)，而且引用的是對(duì)象，而不是一個(gè)主鍵。

關(guān)系模型：只能多方引用一方，而且引用的是主鍵，而不是一整行記錄。

3.6外鍵約束

• 1.外鍵必須是另一表(或自己表)的主鍵的值(即外鍵要引用主鍵的值)。
• 2.外鍵可以為空。
• 3.外鍵可重復(fù)。
• 4.一張表可以有多個(gè)外鍵。

create table dept(
deptno int primary key auto_increment,
name varchar(50)
);
create table emp(
empno int primary key auto_increment,
name varchar(50), 
dno int,
constraint fk_emp_dept  foreign key(dno)  references dept (deptno)
);

最后一行就是給emp表添加外鍵約束，添加外鍵約束后，在emp表中對(duì)dno列進(jìn)行賦值時(shí)就應(yīng)該考慮外鍵約束的三個(gè)條件了。(上圖創(chuàng)建的兩張表演示的也是數(shù)據(jù)庫(kù)中1對(duì)多的關(guān)系。)

3.7數(shù)據(jù)庫(kù)中1對(duì)1的關(guān)系

create table husband (
hid int primary key auto_increment,
hname varchar(50)
);

insert into husband values(null,’劉備’);
insert into husband values(null,’張飛’);
insert into husband values(null,’關(guān)羽’);

create table wife(
wid int primary key auto_increment,
wname varchar(50),
constraint fk_wife_husband foreign key (wid)  references husband(hid)
);

特點(diǎn):外鍵引用自身表的主鍵。

3.8數(shù)據(jù)庫(kù)中多對(duì)多的關(guān)系

在表中建立多對(duì)多關(guān)系需要使用中間表(關(guān)聯(lián)表)，即需要三張表，在中間表中使用兩個(gè)外鍵，分別引用其它兩個(gè)表的主鍵。

create table student(
sid int primary key auto_increment,
sname varchar(50)
);

create table teacher (
tid int primary key auto_increment,
name varchar(50)
);

create table stu_tea(
sid int,
tid int,
constraint fk_student foreign key(sid) references student(sid),
constraint fk_teacher foreign key(tid) references student(tid)
);

insert into student values(null,’劉德華’);
insert into student values(null,’梁朝偉);
insert into student values(null,’黃日華’);
insert into student values(null,’苗僑偉’);
insert into student values(null,’湯鎮(zhèn)業(yè)’);

insert into teacher values(null,’崔老師’);
insert into teacher values(null,’劉老師’);
insert into teacher values(null,’石老師’);

insert into stu_tea values(1,1);
insert into stu_tea values(2,1);
insert into stu_tea values(3,1);
insert into stu_tea values(4,1);
insert into stu_tea values(5,1);
insert into stu_tea values(1,2);
insert into stu_tea values(2,2);
insert into stu_tea values(3,2);
insert into stu_tea values(3,3);
insert into stu_tea values(4,3);
insert into stu_tea values(5,3);

select * from stu_tea;

4.多表查詢(xún)

4.1分類(lèi)

• 合并結(jié)果集(了解)
• 連接查詢(xún)
• 子查詢(xún)

4.2合并結(jié)果集

要求兩個(gè)結(jié)果集(注意這里強(qiáng)調(diào)的是結(jié)果集，而不是兩張表)的列數(shù)、列類(lèi)型完全相同。關(guān)鍵字union:去除重復(fù)行;關(guān)鍵字union all:不去除重復(fù)行。

create table ab(
a int, 
b,varchar(50)
);

insert into ab values(1,’1’);
insert into ab values(2,’2’);
insert into ab values(3,’3’);

create table cd(
c int,
d varchar(50)
);

insert into cd values (3,’3’);
insert into cd values (5,’5’);
insert into cd values (5,’5’);

合并操作為:

select * from ab
union (all)
select * from cd;

4.3連接查詢(xún)

特點(diǎn):外連接有一主一次。

• 左外連接左表為主，那么左表中所有的記錄無(wú)論滿(mǎn)足不滿(mǎn)足條件，都打印出來(lái)。不滿(mǎn)足條件的值用null填補(bǔ)。語(yǔ)法為:select * from emp left outer join dept on emp.deptno=dept.deptno;

• 右外連接右表為主，那么右表中所有的記錄無(wú)論滿(mǎn)足不滿(mǎn)足條件，都打印出來(lái)。不滿(mǎn)足條件的值用null填補(bǔ)。語(yǔ)法為::select * from amp right outer join dept on emp.deptno=dept.deptno;

• 全外連接：左右表都為主，左表和右表中的記錄都要打印出來(lái)，不滿(mǎn)足條件的值用null填補(bǔ)。使用union將左外連接和右外連接的結(jié)果集合并起來(lái)就是全外連接。

4.4子查詢(xún)

子查詢(xún)通俗來(lái)講，就是查詢(xún)中有查詢(xún)。

見(jiàn)例子:

/*查詢(xún)本公司工資最高的員工的詳細(xì)信息*/

select * 
from amp
where sal=max(sal);

此種寫(xiě)法錯(cuò)誤，因?yàn)閣here條件中不能有聚合函數(shù)。所以想到要用子查詢(xún)。

思路:首先查出最高工資:select max(sal) from amp;然后查詢(xún)?cè)摴べY的員工:select * from amp where sal=剛剛的查詢(xún)結(jié)果。所以合并起來(lái)為：select * from amp where sal=(select max(sal) from amp);

4.4.1子查詢(xún)能出現(xiàn)的位置

• where后作為條件(上述例子)
• from后作為二次查詢(xún)(下面例子)select e.empno,e.ename from (select * from amp where deptno=30) as e where 條件;

4.4.2子查詢(xún)的結(jié)果集

• 單行單列:select * from 表1 where 列1 [=、>、<、>=、<=、!=] （select 列 from 表2 where 條件);

• 多行單列::select * from 表1 where 列1 [=、>、<、>=、<=、!=] ［any、all、in］（select 列 from 表2 where 條件;

• 單行多列:select * from 表1 where （列1，列2） in (select 列1，列2 from 表2 where 條件);

• 多行多列:該結(jié)果集用在from后作為二次查詢(xún)。select * from 表1,(select …) 別名 where 條件;