webday16數據庫完整性約束,mySQL編碼問題,備份與恢復,多表查詢_MySQL

約束

* 約束是添加在列上的，用來約束列的！

1. 主鍵約束（唯一標識）

****非空***

****唯一***

****被引用****

* 當表的某一列被指定為主鍵后，該列就不能為空，不能有重復值出現。

* 創建表時指定主鍵的兩種方式：

>

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列添加主鍵約束

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

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

2. 主鍵自增長

* 因為主鍵列的特性是：必須唯一、不能為空，所以我們通常會指定主鍵類為整型，然后設置其自動增長，這樣可以保證在插入數據時主鍵列的唯一和非空特性。

* 創建表時指定主鍵自增長

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

* 修改表時設置主鍵自增長：ALTER TABLE stu CHANGEsid sid INT AUTO_INCREMENT;

* 修改表時刪除主鍵自增長：ALTER TABLE stu CHANGEsid sid INT;

* 測試主鍵自增長：

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

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

3. 非空約束

* 因為某些列不能設置為NULL值，所以可以對列添加非空約束。

* 例如：

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,

age INT,

gender VARCHAR(10)

);

* 對sname列設置了非空約束

4. 唯一約束

* 車庫某些列不能設置重復的值，所以可以對列添加唯一約束。

* 例如：

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL UNIQUE,

age INT,

gender VARCHAR(10)

);

* 對sname列設置了唯一約束

5. 概念模型

對象模型：可以雙向關聯，而且引用的是對象，而不是一個主鍵！

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

對象模型：在java中是domain！！！例如：User、Student

is a

has a(關聯)

> 1對1

> 1對多

> 多對多

use a

關系模型：在數據庫中表！！！

當我們要完成一個軟件系統時，需要把系統中的實體抽取出來，形成概念模型。

例如部門、員工都是系統中的實體。概念模型中的實體最終會成為Java中的類、數據庫中表。

實體之間還存在著關系，關系有三種：

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

* 1對1：例如老公和老婆就是一對一的關系，一個老公只能有一個老婆，而一個老婆只能有一個老公。

* 多對多：老師與學生的關系就是多對多，一個老師可以有多個學生，一個學生可以有多個老師。

概念模型在Java中成為實體類（javaBean）

類就使用成員變量來完成關系，一般都是雙向關聯！

多對一雙向中關聯，即員工關聯部門，部門也關聯員工

 class Employee {//多方關聯一方
 ...
 private Department department;
 }
 class Department {//一方關聯多方
 ...
 private List employees;
 }
 
 class Husband {
 ...
 private Wife wife;
 }
 class Wife {
 ...
 private Husband
 }
 
 class Student {
 ...
 private List teachers
 }
 classTeacher {
 ...
 private List students;
 }

6. 外鍵約束

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

* 外鍵可以重復

* 外鍵可以為空

* 一張表中可以有多個外鍵！

概念模型在數據庫中成為表

數據庫表中的多對一關系，只需要在多方使用一個獨立的列來引用1方的主鍵即可

/*員工表*/

create talbe emp (

empno int primary key,/*員工編號*/

...

deptno int/*所屬部門的編號*/

);

/*部門表*/

create table dept (

deptno int primary key,/*部門編號*/

...

);

emp表中的deptno列的值表示當前員工所從屬的部門編號。也就是說emp.deptno必須在dept表中是真實存在！

但是我們必須要去對它進行約束，不然可能會出現員工所屬的部門編號是不存在的。這種約束就是外鍵約束。

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

語法：CONSTRAINT 約束名稱 FOREIGN KEY(外鍵列名) REFERENCES 關聯表(關聯表的主鍵)

創建表時指定外鍵約束

create talbe emp (

empno int primary key,

...

deptno int,

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

);

修改表時添加外鍵約束

ALERT TABLE emp

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

修改表時刪除外鍵約束

ALTER TABLE emp

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

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

7數據庫表與表之間的關系

一對一：

例如t_person表和t_card表，即人和身份證。這種情況需要找出主從關系，即誰是主表，誰是從表。人可以沒有身份證，但身份證必須要有人才行，所以人是主表，而身份證是從表。

設計從表可以有兩種方案：

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

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

一對多（多對一）：

最為常見的就是一對多！一對多和多對一，這是從哪個角度去看得出來的。t_user和t_section的關系，從t_user來看就是一對多，而從t_section的角度來看就是多對一！這種情況都是在多方創建外鍵！

多對多：

例如t_stu和t_teacher表，即一個學生可以有多個老師，而一個老師也可以有多個學生。這種情況通常需要創建中間表來處理多對多關系。例如再創建一張表t_stu_tea表，給出兩個外鍵，一個相對t_stu表的外鍵，另一個相對t_teacher表的外鍵。

數據庫一對一關系

在表中建立一對一關系比較特殊，需要讓其中一張表的主鍵，即是主鍵又是外鍵。

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即是主鍵，又是相對husband表的外鍵！

husband.hid是主鍵，不能重復！

wife.wid是主鍵，不能重復，又是外鍵，必須來自husband.hid。

所以如果在wife表中有一條記錄的wid為1，那么wife表中的其他記錄的wid就不能再是1了，因為它是主鍵。

同時在husband.hid中必須存在1這個值，因為wid是外鍵。這就完成了一對一關系。

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

數據庫多對多關系

在表中建立多對多關系需要使用中間表，即需要三張表，在中間表中使用兩個外鍵，分別引用其他兩個表的主鍵。

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)

);

這時在stu_tea這個中間表中的每條記錄都是來說明student和teacher表的關系

例如在stu_tea表中的記錄：sid為1001，tid為2001，這說明編號為1001的學生有一個編號為2001的老師

sid tid

101 201 /*編號為101的學生有一個編號為201的老師*/

101 202 /*編號為101的學生有一個編號為202的老師*/

101 203 /*編號為101的學生有一個編號為203的老師*/

102 201 /*編號為102的學生有一個編號為201的老師*/

102 204 /*編號為102的學生有一個編號為204的老師*/

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

編碼

1. 查看MySQL數據庫編碼

*SHOW VARIABLES LIKE 'char%';

2. 編碼解釋

*character_set_client：MySQL使用該編碼來解讀客戶端發送過來的數據，例如該編碼為UTF8，那么如果客戶端發送過來的數據不是UTF8，那么就會出現亂碼

*character_set_results：MySQL會把數據轉換成該編碼后，再發送給客戶端，例如該編碼為UTF8，那么如果客戶端不使用UTF8來解讀，那么就會出現亂碼

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

3. 控制臺亂碼問題

* 插入或修改時出現亂碼：

> 這時因為cmd下默認使用GBK，而character_set_client不是GBK的原因。我們只需讓這兩個編碼相同即可。

> 因為修改cmd的編碼不方便，所以我們去設置character_set_client為GBK即可。

* 查詢出的數據為亂碼：

> 這是因為character_set_results不是GBK，而cmd默認使用GBK的原因。我們只需讓這兩個編碼相同即可。

> 因為修改cmd的編碼不方便，所以我們去設置character_set_results為GBK即可。

* 設置變量的語句：

> set character_set_client=gbk;

> set character_set_results=gbk;

注意，設置變量只對當前連接有效，當退出窗口后，再次登錄mysql，還需要再次設置變量。

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

設置default-character-set=gbk即可。

4. 指定默認編碼

我們在安裝MySQL時已經指定了默認編碼為UTF8，所以我們在創建數據庫、創建表時，都無需再次指定編碼。

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

設置character-set-server=utf8即可。

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

character_set_connection | utf8

character_set_database | utf8

character_set_results | utf8 --> mysql發送給客戶端的數據都是utf8的。一是客戶端用utf8編碼，二是如果客戶端使用gbk來編碼，那么需要修改這個變量為gbk的。

character_set_server | utf8

character_set_system | utf8

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

character_set_client=utf8，無論客戶端發送的是什么編碼的數據，mysql都當成是utf8的數據！

> 若客戶端發送的是GBK

> 服務器會當成utf8對待

> 總結：必然亂碼！

處理問題的手段有兩種；

> 讓客戶端發送utf8的數據(行不通)

> 把character_set_client修改為gbk

setcharacter_set_client=gbk; --> 只在當前窗口內有效，也就是說，關閉窗口后，再打開，又回到utf8了。

character_set_results=utf8，把數據用什么編碼發送給客戶端！

> 若服務器發送給客戶端的是utf8的數據

> 客戶端會把它當成gbk，因為我們的小黑屏，只能顯示gbk

> 總結：必然亂碼！

處理問題的手段有兩種：

> 讓服務器發送gbk的數據：set character_set_results=gbk

> 讓小黑屏使用utf8來解讀（行不通）

my.ini

在總配置文件中進行配置，可以一勞永逸

[client]

port=3306

[mysql]

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

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

備份與恢復

數據庫 --> sql語句

sql語句 --> 數據庫

1. 數據庫導出SQL腳本(備份數據庫內容，并不是備份數據庫！)

> mysqldump –u用戶名–p密碼 數據庫名>生成的腳本文件路徑

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

> 注意，不要打分號，不要登錄mysql，直接在cmd下運行

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

2. 執行SQL腳本

第一種方式

> mysql -u用戶名 -p密碼數據庫<腳本文件路徑

> 例如：

*先刪除mydb1庫，再重新創建mydb1庫

*mysql -uroot -p123 mydb1

> 注意，不要打分號，不要登錄mysql，直接在cmd下運行

第二種方式

> 登錄mysql

> source SQL腳本路徑

> 例如：

*先刪除mydb1庫，再重新創建mydb1庫

*切換到mydb1庫

*source c:\mydb1.sql

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

數據庫 --> sql：備份

sql --> 數據庫：恢復

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

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

mysql -uroot -p123 mydb3恢復

source c:/a.sql --> 恢復

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

多表查詢

關鍵字執行順序

select *

from emp, dept, (select * from emp)

where

group by

having

order by

limit

分類

*合并結果集(了解)

*連接查詢

*子查詢

合并結果集

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

*UNION，去除重復行

*UNION ALL，不去除重復行

SELECT * FROM cd

UNION ALL

SELECT * FROM ab;

連接查詢

1. 分類

*內連接

*外連接

> 左外連接

> 右外連接

> 全外連接(MySQL不支持)

*自然連接（屬于一種簡化方式）

2. 內連接

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

*標準：SELECT * FROM 表1 別名1 INNER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

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

*內連接查詢出的所有記錄都滿足條件。

3. 外連接

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

> 左表記錄無論是否滿足條件都會查詢出來，而右表只有滿足條件才能出來。左表中不滿足條件的記錄，右表部分都為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

> 右表記錄無論是否滿足條件都會查詢出來，而左表只有滿足條件才能出來。右表不滿足條件的記錄，其左表部分都為NULL

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

*全鏈接：可以使用UNION來完成全鏈接

子查詢

　　：查詢中有查詢（查看select關鍵字的個數！）

1. 出現的位置：

*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}

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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com