webday16數(shù)據(jù)庫(kù)完整性約束,mySQL編碼問(wèn)題,備份與恢復(fù),多表查詢_MySQL

約束

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

1. 主鍵約束（唯一標(biāo)識(shí)）

****非空***

****唯一***

****被引用****

* 當(dāng)表的某一列被指定為主鍵后，該列就不能為空，不能有重復(fù)值出現(xiàn)。

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

>

CREATE TABLE stu(

sid CHAR(6) PRIMARY KEY,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

指定sid列為主鍵列，即為sid列添加主鍵約束

>

CREATE TABLE stu(

sid CHAR(6),

sname VARCHAR(20),

age INT,

gender VARCHAR(10),

PRIMARYKEY(sid)

);

指定sid列為主鍵列，即為sid列添加主鍵約束

* 修改表時(shí)指定主鍵：ALTER TABLE stu ADD PRIMARYKEY(sid);

* 刪除主鍵：ALTER TABLE stu DROP PRIMARYKEY;

2. 主鍵自增長(zhǎng)

* 因?yàn)橹麈I列的特性是：必須唯一、不能為空，所以我們通常會(huì)指定主鍵類為整型，然后設(shè)置其自動(dòng)增長(zhǎng)，這樣可以保證在插入數(shù)據(jù)時(shí)主鍵列的唯一和非空特性。

* 創(chuàng)建表時(shí)指定主鍵自增長(zhǎng)

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

* 修改表時(shí)設(shè)置主鍵自增長(zhǎng)：ALTER TABLE stu CHANGEsid sid INT AUTO_INCREMENT;

* 修改表時(shí)刪除主鍵自增長(zhǎng)：ALTER TABLE stu CHANGEsid sid INT;

* 測(cè)試主鍵自增長(zhǎng)：

> INSERT INTO stu VALUES(NULL, 'zhangSan',23,'male');

> INSERT INTO stu(sname,age,gender) VALUES('zhangSan',23,'male');

3. 非空約束

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

* 例如：

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,

age INT,

gender VARCHAR(10)

);

* 對(duì)sname列設(shè)置了非空約束

4. 唯一約束

* 車庫(kù)某些列不能設(shè)置重復(fù)的值，所以可以對(duì)列添加唯一約束。

* 例如：

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL UNIQUE,

age INT,

gender VARCHAR(10)

);

* 對(duì)sname列設(shè)置了唯一約束

5. 概念模型

對(duì)象模型：可以雙向關(guān)聯(lián)，而且引用的是對(duì)象，而不是一個(gè)主鍵！

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

對(duì)象模型：在java中是domain?。?！例如：User、Student

is a

has a(關(guān)聯(lián))

> 1對(duì)1

> 1對(duì)多

> 多對(duì)多

use a

關(guān)系模型：在數(shù)據(jù)庫(kù)中表！??！

當(dāng)我們要完成一個(gè)軟件系統(tǒng)時(shí)，需要把系統(tǒng)中的實(shí)體抽取出來(lái)，形成概念模型。

例如部門、員工都是系統(tǒng)中的實(shí)體。概念模型中的實(shí)體最終會(huì)成為Java中的類、數(shù)據(jù)庫(kù)中表。

實(shí)體之間還存在著關(guān)系，關(guān)系有三種：

* 1對(duì)多：例如每個(gè)員工都從屬一個(gè)部門，而一個(gè)部門可以有多個(gè)員工，其中員工是多方，而部門是一方。

* 1對(duì)1：例如老公和老婆就是一對(duì)一的關(guān)系，一個(gè)老公只能有一個(gè)老婆，而一個(gè)老婆只能有一個(gè)老公。

* 多對(duì)多：老師與學(xué)生的關(guān)系就是多對(duì)多，一個(gè)老師可以有多個(gè)學(xué)生，一個(gè)學(xué)生可以有多個(gè)老師。

概念模型在Java中成為實(shí)體類（javaBean）

類就使用成員變量來(lái)完成關(guān)系，一般都是雙向關(guān)聯(lián)！

多對(duì)一雙向中關(guān)聯(lián)，即員工關(guān)聯(lián)部門，部門也關(guān)聯(lián)員工

 class Employee {//多方關(guān)聯(lián)一方
 ...
 private Department department;
 }
 class Department {//一方關(guān)聯(lián)多方
 ...
 private List employees;
 }
 
 class Husband {
 ...
 private Wife wife;
 }
 class Wife {
 ...
 private Husband
 }
 
 class Student {
 ...
 private List teachers
 }
 classTeacher {
 ...
 private List students;
 }

6. 外鍵約束

* 外鍵必須是另一表的主鍵的值(外鍵要引用主鍵！)

* 外鍵可以重復(fù)

* 外鍵可以為空

* 一張表中可以有多個(gè)外鍵！

概念模型在數(shù)據(jù)庫(kù)中成為表

數(shù)據(jù)庫(kù)表中的多對(duì)一關(guān)系，只需要在多方使用一個(gè)獨(dú)立的列來(lái)引用1方的主鍵即可

/*員工表*/

create talbe emp (

empno int primary key,/*員工編號(hào)*/

...

deptno int/*所屬部門的編號(hào)*/

);

/*部門表*/

create table dept (

deptno int primary key,/*部門編號(hào)*/

...

);

emp表中的deptno列的值表示當(dāng)前員工所從屬的部門編號(hào)。也就是說(shuō)emp.deptno必須在dept表中是真實(shí)存在！

但是我們必須要去對(duì)它進(jìn)行約束，不然可能會(huì)出現(xiàn)員工所屬的部門編號(hào)是不存在的。這種約束就是外鍵約束。

我們需要給emp.deptno添加外鍵約束，約束它的值必須在dept.deptno中存在。外鍵必須是另一個(gè)表的主鍵！

語(yǔ)法：CONSTRAINT 約束名稱 FOREIGN KEY(外鍵列名) REFERENCES 關(guān)聯(lián)表(關(guān)聯(lián)表的主鍵)

創(chuàng)建表時(shí)指定外鍵約束

create talbe emp (

empno int primary key,

...

deptno int,

CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)

);

修改表時(shí)添加外鍵約束

ALERT TABLE emp

ADDCONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

修改表時(shí)刪除外鍵約束

ALTER TABLE emp

DROP FOREIGN KEY fk_emp_deptno;/*約束名稱*/

--------------------------

7數(shù)據(jù)庫(kù)表與表之間的關(guān)系

一對(duì)一：

例如t_person表和t_card表，即人和身份證。這種情況需要找出主從關(guān)系，即誰(shuí)是主表，誰(shuí)是從表。人可以沒(méi)有身份證，但身份證必須要有人才行，所以人是主表，而身份證是從表。

設(shè)計(jì)從表可以有兩種方案：

在t_card表中添加外鍵列（相對(duì)t_user表），并且給外鍵添加唯一約束；

給t_card表的主鍵添加外鍵約束（相對(duì)t_user表），即t_card表的主鍵也是外鍵。

一對(duì)多（多對(duì)一）：

最為常見(jiàn)的就是一對(duì)多！一對(duì)多和多對(duì)一，這是從哪個(gè)角度去看得出來(lái)的。t_user和t_section的關(guān)系，從t_user來(lái)看就是一對(duì)多，而從t_section的角度來(lái)看就是多對(duì)一！這種情況都是在多方創(chuàng)建外鍵！

多對(duì)多：

例如t_stu和t_teacher表，即一個(gè)學(xué)生可以有多個(gè)老師，而一個(gè)老師也可以有多個(gè)學(xué)生。這種情況通常需要?jiǎng)?chuàng)建中間表來(lái)處理多對(duì)多關(guān)系。例如再創(chuàng)建一張表t_stu_tea表，給出兩個(gè)外鍵，一個(gè)相對(duì)t_stu表的外鍵，另一個(gè)相對(duì)t_teacher表的外鍵。

數(shù)據(jù)庫(kù)一對(duì)一關(guān)系

在表中建立一對(duì)一關(guān)系比較特殊，需要讓其中一張表的主鍵，即是主鍵又是外鍵。

create table husband(

hid int PRIMARY KEY,

...

);

create table wife(

wid int PRIMARY KEY,

...

ADD CONSTRAINT fk_wife_wid FOREIGN KEY(wid) REFERENCES husband(hid)

);

其中wife表的wid即是主鍵，又是相對(duì)husband表的外鍵！

husband.hid是主鍵，不能重復(fù)！

wife.wid是主鍵，不能重復(fù)，又是外鍵，必須來(lái)自husband.hid。

所以如果在wife表中有一條記錄的wid為1，那么wife表中的其他記錄的wid就不能再是1了，因?yàn)樗侵麈I。

同時(shí)在husband.hid中必須存在1這個(gè)值，因?yàn)閣id是外鍵。這就完成了一對(duì)一關(guān)系。

*****從表的主鍵即是外鍵！

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

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

create table student(

sid int PRIMARY KEY,

...

);

create table teacher(

tid int PRIMARY KEY,

...

);

create table stu_tea(

sid int,

tid int,

ADD CONSTRAINT fk_stu_tea_sid FOREIGN KEY(sid) REFERENCES student(sid),

ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY(tid) REFERENCES teacher(tid)

);

這時(shí)在stu_tea這個(gè)中間表中的每條記錄都是來(lái)說(shuō)明student和teacher表的關(guān)系

例如在stu_tea表中的記錄：sid為1001，tid為2001，這說(shuō)明編號(hào)為1001的學(xué)生有一個(gè)編號(hào)為2001的老師

sid tid

101 201 /*編號(hào)為101的學(xué)生有一個(gè)編號(hào)為201的老師*/

101 202 /*編號(hào)為101的學(xué)生有一個(gè)編號(hào)為202的老師*/

101 203 /*編號(hào)為101的學(xué)生有一個(gè)編號(hào)為203的老師*/

102 201 /*編號(hào)為102的學(xué)生有一個(gè)編號(hào)為201的老師*/

102 204 /*編號(hào)為102的學(xué)生有一個(gè)編號(hào)為204的老師*/

-----------------------

編碼

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

*SHOW VARIABLES LIKE 'char%';

2. 編碼解釋

*character_set_client：MySQL使用該編碼來(lái)解讀客戶端發(fā)送過(guò)來(lái)的數(shù)據(jù)，例如該編碼為UTF8，那么如果客戶端發(fā)送過(guò)來(lái)的數(shù)據(jù)不是UTF8，那么就會(huì)出現(xiàn)亂碼

*character_set_results：MySQL會(huì)把數(shù)據(jù)轉(zhuǎn)換成該編碼后，再發(fā)送給客戶端，例如該編碼為UTF8，那么如果客戶端不使用UTF8來(lái)解讀，那么就會(huì)出現(xiàn)亂碼

其它編碼只要支持中文即可，也就是說(shuō)不能使用latin1

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

* 插入或修改時(shí)出現(xiàn)亂碼：

> 這時(shí)因?yàn)閏md下默認(rèn)使用GBK，而character_set_client不是GBK的原因。我們只需讓這兩個(gè)編碼相同即可。

> 因?yàn)樾薷腸md的編碼不方便，所以我們?nèi)ピO(shè)置character_set_client為GBK即可。

* 查詢出的數(shù)據(jù)為亂碼：

> 這是因?yàn)閏haracter_set_results不是GBK，而cmd默認(rèn)使用GBK的原因。我們只需讓這兩個(gè)編碼相同即可。

> 因?yàn)樾薷腸md的編碼不方便，所以我們?nèi)ピO(shè)置character_set_results為GBK即可。

* 設(shè)置變量的語(yǔ)句：

> set character_set_client=gbk;

> set character_set_results=gbk;

注意，設(shè)置變量只對(duì)當(dāng)前連接有效，當(dāng)退出窗口后，再次登錄mysql，還需要再次設(shè)置變量。

為了一勞永逸，可以在my.ini中設(shè)置：

設(shè)置default-character-set=gbk即可。

4. 指定默認(rèn)編碼

我們?cè)诎惭bMySQL時(shí)已經(jīng)指定了默認(rèn)編碼為UTF8，所以我們?cè)趧?chuàng)建數(shù)據(jù)庫(kù)、創(chuàng)建表時(shí)，都無(wú)需再次指定編碼。

為了一勞永逸，可以在my.ini中設(shè)置：

設(shè)置character-set-server=utf8即可。

character_set_client | utf8 --> mysql把我們客戶端傳遞的數(shù)據(jù)都當(dāng)成是utf8！一是給它傳遞utf8，二是如果我們傳遞的是gbk，那么需要修改這個(gè)變量為gbk

character_set_connection | utf8

character_set_database | utf8

character_set_results | utf8 --> mysql發(fā)送給客戶端的數(shù)據(jù)都是utf8的。一是客戶端用utf8編碼，二是如果客戶端使用gbk來(lái)編碼，那么需要修改這個(gè)變量為gbk的。

character_set_server | utf8

character_set_system | utf8

----------------------------

character_set_client=utf8，無(wú)論客戶端發(fā)送的是什么編碼的數(shù)據(jù)，mysql都當(dāng)成是utf8的數(shù)據(jù)！

> 若客戶端發(fā)送的是GBK

> 服務(wù)器會(huì)當(dāng)成utf8對(duì)待

> 總結(jié)：必然亂碼！

處理問(wèn)題的手段有兩種；

> 讓客戶端發(fā)送utf8的數(shù)據(jù)(行不通)

> 把character_set_client修改為gbk

setcharacter_set_client=gbk; --> 只在當(dāng)前窗口內(nèi)有效，也就是說(shuō)，關(guān)閉窗口后，再打開(kāi)，又回到utf8了。

character_set_results=utf8，把數(shù)據(jù)用什么編碼發(fā)送給客戶端！

> 若服務(wù)器發(fā)送給客戶端的是utf8的數(shù)據(jù)

> 客戶端會(huì)把它當(dāng)成gbk，因?yàn)槲覀兊男『谄粒荒茱@示gbk

> 總結(jié)：必然亂碼！

處理問(wèn)題的手段有兩種：

> 讓服務(wù)器發(fā)送gbk的數(shù)據(jù)：set character_set_results=gbk

> 讓小黑屏使用utf8來(lái)解讀（行不通）

my.ini

在總配置文件中進(jìn)行配置，可以一勞永逸

[client]

port=3306

[mysql]

default-character-set=gbk /*它可以一勞永逸！它可以修改三個(gè)變量：client、results、connection*/

-------------------------

備份與恢復(fù)

數(shù)據(jù)庫(kù) --> sql語(yǔ)句

sql語(yǔ)句 --> 數(shù)據(jù)庫(kù)

1. 數(shù)據(jù)庫(kù)導(dǎo)出SQL腳本(備份數(shù)據(jù)庫(kù)內(nèi)容，并不是備份數(shù)據(jù)庫(kù)！)

> mysqldump –u用戶名–p密碼 數(shù)據(jù)庫(kù)名>生成的腳本文件路徑

> 例如：mysqldump -uroot-p123 mydb1>C:\mydb1.sql (與mysql.exe和mysqld.exe一樣, 都在bin目錄下)

> 注意，不要打分號(hào)，不要登錄mysql，直接在cmd下運(yùn)行

> 注意，生成的腳本文件中不包含createdatabase語(yǔ)句

2. 執(zhí)行SQL腳本

第一種方式

> mysql -u用戶名 -p密碼數(shù)據(jù)庫(kù)<腳本文件路徑

> 例如：

*先刪除mydb1庫(kù)，再重新創(chuàng)建mydb1庫(kù)

*mysql -uroot -p123 mydb1

> 注意，不要打分號(hào)，不要登錄mysql，直接在cmd下運(yùn)行

第二種方式

> 登錄mysql

> source SQL腳本路徑

> 例如：

*先刪除mydb1庫(kù)，再重新創(chuàng)建mydb1庫(kù)

*切換到mydb1庫(kù)

*source c:\mydb1.sql

---------------------------------

數(shù)據(jù)庫(kù) --> sql：備份

sql --> 數(shù)據(jù)庫(kù)：恢復(fù)

------------------

mysqldump -uroot -p123 mydb3>c:/a.sql--> 備份

mysql -uroot -p123 mydb3恢復(fù)

source c:/a.sql --> 恢復(fù)

---------------------------------

多表查詢

關(guān)鍵字執(zhí)行順序

select *

from emp, dept, (select * from emp)

where

group by

having

order by

limit

分類

*合并結(jié)果集(了解)

*連接查詢

*子查詢

合并結(jié)果集

* 要求被合并的表中，列的類型和列數(shù)相同

*UNION，去除重復(fù)行

*UNION ALL，不去除重復(fù)行

SELECT * FROM cd

UNION ALL

SELECT * FROM ab;

連接查詢

1. 分類

*內(nèi)連接

*外連接

> 左外連接

> 右外連接

> 全外連接(MySQL不支持)

*自然連接（屬于一種簡(jiǎn)化方式）

2. 內(nèi)連接

*方言：SELECT * FROM 表1 別名1, 表2 別名2 WHERE 別名1.xx=別名2.xx

*標(biāo)準(zhǔn)：SELECT * FROM 表1 別名1 INNER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

*自然：SELECT * FROM 表1 別名1 NATURAL JOIN 表2 別名2

*內(nèi)連接查詢出的所有記錄都滿足條件。

3. 外連接

*左外：SELECT * FROM 表1 別名1 LEFT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

> 左表記錄無(wú)論是否滿足條件都會(huì)查詢出來(lái)，而右表只有滿足條件才能出來(lái)。左表中不滿足條件的記錄，右表部分都為NULL

*左外自然：SELECT * FROM 表1 別名1 NATURAL LEFT OUTERJOIN 表2 別名2 ON 別名1.xx=別名2.xx

*右外：SELECT * FROM 表1 別名1 RIGHT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

> 右表記錄無(wú)論是否滿足條件都會(huì)查詢出來(lái)，而左表只有滿足條件才能出來(lái)。右表不滿足條件的記錄，其左表部分都為NULL

*右外自然：SELECT * FROM 表1 別名1 NATURAL RIGHT OUTERJOIN 表2 別名2 ON 別名1.xx=別名2.xx

*全鏈接：可以使用UNION來(lái)完成全鏈接

子查詢

　?。翰樵冎杏胁樵儯ú榭磗elect關(guān)鍵字的個(gè)數(shù)?。?/p>

1. 出現(xiàn)的位置：

*where后作為條件存在

*from后作為表存在(多行多列)

2. 條件

*(***)單行單列：SELECT * FROM 表1 別名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 別名2 WHERE 條件)

*(**)多行單列：SELECT * FROM 表1 別名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 別名2 WHERE 條件)

*(*)單行多列：SELECT * FROM 表1 別名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 別名2 WHERE 條件)

*(***)多行多列：SELECT * FROM 表1 別名1 , (SELECT ....) 別名2 WHERE 條件

====================================================

笛卡爾積

{a, b, c} {1,2}

{a1, a2, b1, b2, c1, c2}

====================================================

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com