MySQL：二、Table約束，多表聯查，數據庫備份、恢復

一、數據的完整性、約束

實體完整性、域完整性

• 作用：保證使用者輸入的數據儲存到數據庫中是正確的
• 實質：建立表的時候給表中的欄位新增約束

1.1 實體完整性

• 實體：表中的一行或者一條記錄代表一個實體
• 實體完整性的作用：標識每一行數據不重複
• 約束型別：
  主鍵約束【primary key】
  唯一約束【unique】
  自動增長列【auto_increment】

1.1.1 主鍵約束【primary key】

• 特點：數據唯一，且不能爲null
• 主關鍵字可以是表中的一個欄位或者多個欄位，它的值用來唯一標識表中的某一條記錄
• 場景：在多個表的關聯關係中

方式一：
create table stu2(
id int primary key auto_increment,
name varchar(50),
);
方式二：同時建立多個主鍵
create table stu2(
id int,
name varchar(50),
primary key(id，name)
);
方式三：建立完表後單獨新增主鍵
create table stu2(
id int,
name varchar(50)
);
alter table stu3 add constraint stu3_id primary key(id);

1.1.2 唯一約束

• 作用：在非主鍵列中不能輸入重複的值

create table stu4(
id int primary key,
name varchar(50) unique
);

• primary key和unique之間的區別：
  a.二者都強調的是唯一性
  b.在同一個表中，一般只出現一個primary key，可以出現多個unique
  c.primary key不允許爲null，但是unique是允許的

1.1.3 自動增長列

• 給主鍵新增自動增長性：auto_increment，列只能是整數型別。主鍵列的值會自動疊加。
• 場景：一般新增給主鍵

1.2 域完整性

• 作用：限制單元格數據的正確性，域代表當前單元格。
• 約束型別：
  a.非空約束【not null】
  b.預設值約束【default】

1.2.1 非空約束【not null】

• 注意：name被約束爲not null，插入數據的時候，name堅決不能爲null，如果爲null，數據庫立馬報錯。

create table stu2(
id int,
name varchar(50) not null
);

1.2.2 預設值約束

create table stu7(
id int primary key auto_increment,
name varchar(50) unique not null,
address varchar(50) default "beijing"
);

1.3 外來鍵約束

• 新增外來鍵約束：foreign key，將兩個或多個表聯繫起來。
• 注意：新增外來鍵必須先有主鍵，主鍵和外來鍵的型別必須保持一致。

方式一：stu_sco_id是給約束起的名字，可以自定義
create table score1( score int, courseid int,stuid varchar(10), constraint stu_sco_id foreign key(stuid) references student(stuid) );

方式二：先建立表，再單獨新增外來鍵
alter table score2 add constraint stu_sco_id2 foreign key(stuid) references student(stuid);

二、多表查詢

• 表與表之間的關係：一對一、一對多（新增外來鍵）、多對多（合併新表）。

2.1 合併結果集

• 作用：將兩個select語句的查詢結果合併到一起.
• 方式：
  union：去除重複記錄【並集】、union all：獲取所有的結果

注意：被合併的兩個結果，列數、列型別必須相同

union：
select * from A union select * from B;

union all：
select * from A union all select * from B;

2.2 連線查詢

• 笛卡爾積：求出多個表的乘積，如果直接採用連線查詢，不新增過濾條件就會產生笛卡爾積。
• 解決辦法：在實際應用中，需要去除重複記錄，則需要通過條件進行過濾。

2.2.1 內連線：inner join on

• 內連線的特點：查詢結果必須滿足條件，inner可省略。

on可以用where替代：（給別名）
select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid;

2.2.2 外連線：outer join on

• 特點：以其中一個表作爲參照連線另外一個表。
• 分類：
  左外連線：left join on、右外連線：right join on

左外連線：
select s.stuid,s.stuname,c.score,c.courseid  from student s left join score c on s.stuid=c.stuid;
右外連線：
select s.stuid,s.stuname,c.score,c.courseid  from student s right join score c on s.stuid=c.stuid;

2.4 子查詢

• 在一個select語句中包含另外一個完整的select語句【select語句的巢狀】
• 注意：
  a.子查詢出現的位置：from後where子句的後面，作爲條件的一部分被查詢；
  b.當子查詢出現在where後面作爲條件時，可以使用關鍵字：any、all。
  c.子查詢結果集的形式：單行單列、單行多列、多行多列、多行單列。

1.查詢和scott在同一個部門的員工:
select * from emp where deptno=(select deptno from emp where enname='scott');
2.查詢工作型別和工資與martin完全相同的員工資訊:
select * from emp where (job,sal) in(select job,sal from emp where enname='martin');

子查詢涉及返回值爲多個時，返回的是一個元組。where後面的欄位也要元組形式。

三、數據庫的備份和恢復

3.1 備份：mysqldump

• 生成SQL指令碼，導出數據
• 命令：mysqldump -u root -p 數據庫名>生成sql指令碼的路徑/備份名.sql

注意：可以不需要登錄數據庫，備份路徑要加上備份生成的檔名

mysqldump -u root -p mydb1 > /home/rock/Desktop/mydb1.sql

3.2 恢復：source

• 執行sql指令碼，恢復數據
• 前提：必須先建立空數據庫

注意：需要先登錄數據庫，然後進入指定的數據庫，執行sql指令碼。

create database test;
use test;
source /home/rock/Desktop/mydb1.sql;