Mysql(三)約束

標籤：提高   char   刪除索引   sts   sel   唯一性   mysql   額外   方式   

一、視圖

      視圖是虛擬資料表，本身不儲存資料，而是提供資料的邏輯 展示。

      1、建立視圖

create view stu_view asselect s1.id, s1.name, s2.room, s2.stay_timefrom student s1, stay s2where s1.id = s2.id;

建立視圖後，就可以像查詢資料表一樣查詢檢視。  視圖的欄位就是我們從資料表中查詢而來的欄位。

select id from stu_view   desc stu_view
      2、修改視圖

alter view stu_view asselect s1.id, s1.name, s2.room, s2.stay_timefrom student s1 left outer join stay s2on s1.id = s2.id;select * from stu_view

　　3、刪除視圖

drop view stu_view;

       4、 在修改視圖時，我們可以不使用alter，而是or replace。 在視圖不存在時，建立視圖，當視圖存在時，替換視圖。

create or replace view stu_view as
select s1.id, s1.name, s2.room, s2.stay_time
from student s1 left outer join stay s2
on s1.id = s2.id;

       5、視圖只是一個預儲存的查詢語句。（as後面的查詢語句）  當我們查詢檢視時，視圖的查詢語句就會展開。（就是從 視圖儲存的查詢語句（結果集）中查詢。

select * from stu_viewselect * from (select s1.id, s1.name, s2.room, s2.stay_timefrom student s1 left outer join stay s2on s1.id = s2.id) x;

　　視圖的特徵：

1 視圖作為預儲存的查詢語句，不能提高效能。
2 視圖可以簡化我們的輸入操作。
3 視圖可以提供相同資料表的不同邏輯展示。

 

二、

    視圖預設情況下，是以我們查詢的欄位命名。
#當視圖的查詢欄位出現同名時（命名衝突）時，我們可以:
#1 使用別名 當使用別名時，視圖欄位會以別名來命名。
#2 自訂視圖欄位的名稱

     1、別名

create or replace view stu_view asselect s1.id stu_id, s2.id stay_id, s1.name, s2.room, s2.stay_timefrom student s1 left outer join stay s2on s1.id = s2.id;desc  stu_view;

　2、自訂視圖欄位的名稱，指定的欄位數量與查詢的欄位數量必須一致。

create or replace view stu_view(field1, field2) asselect s1.id, s2.stay_timefrom student s1 left outer join stay s2on s1.id = s2.id;desc  stu_view;

 

三、索引

      索引是資料表中一個特殊的對象。

優點：索引可以加快記錄的查詢速度。

缺點：
#1 索引需要佔用額外的硬碟空間。
#2 當資料表中記錄發生變動時（增加，修改等），索引需要 進行重新變更（重新維護）。

索引使用的場合：

1 資料量很大（表中的記錄多）
2 查詢操作多
3 增加，刪除等操作少。

      1、建立索引

create index stu_index on student(id);

create index 索引名 on 表名(欄位1，欄位2， ……欄位n);     可以指定一列或者多列

      2、刪除索引

drop index 索引名 on 表名

 

drop index stu_index on student

索引這就類似於圖書的目錄一樣，如果我們想要查詢某些內容， 我們可以從第一頁開始，逐頁進行尋找，但這勢必會耗費大 量的尋找時間。但是，如果我們通過目錄來查詢，就可以快 速的定位到相關的頁碼上。 
 
四、資料庫約束  

約束
約束，就是一種限制，其可以保證資料的正確性與完整性。
約束可以分為如下幾類：
1唯一性限制式
2非空約束
3主鍵約束
4外鍵約束
5檢查約束

約束具有欄位級文法與表級文法。

1、唯一性限制式

唯一性限制式保證約束列（一列或多列）的值不能出現重複。唯一性限制式允許加入多個null值。因為MySQL中，null不等於任何值，包括其自身。因此，多個null值，彼此也是不等的。

欄位級文法：

如果不存在建立表create table if not exists t(id int primary key,    age int unique);

  如果表存在，刪除。
  drop table if exists t;

　

insert into t(id, age) values (1, 1);#錯誤，違法唯一性限制式。#insert into t(id, age) values (2, 1);insert into t(id, age) values (3, null);#允許插入多個null值。insert into t(id, age) values (4, null);select * from t;

表級文法：

唯一性限制式也可以作用於多個欄位。當作用於多個欄位時， 只要多個欄位的值不全相等，則認為是不重複的。

create table t(id int primary key,    age int,    name varchar(20),    unique key (age, name));

與表級文法unique key (age, name)不同，對於表級文法， age，name只要有一個欄位值不同即可，對於欄位級文法， age與name兩個欄位值都不允許重複。

在建表之後增加唯一性限制式：

對於建表之後加入的約束，一定要保證當前表中的資料  沒有破壞該新增的約束，否則，約束就無法加入成功。

alter table t add unique key(age, name);
alter table t modify age int unique;

刪除唯一性限制式
alter table t drop index age;

2、非空約束

非空約束表示欄位不允許為null值，該約束只有欄位級文法，沒有表級文法。

create table t(
id int primary key,
age int not null
);

在建表之後指定非空約束

create table t(    id int primary key,    age int);alter table t modify age int not null;　　

刪除（取消）非空約束。
alter table t modify age int null;

3、主鍵約束

主鍵欄位既不能為null，也不能重複。主鍵約束就是唯一性限制式+非空約束。

欄位級文法：

create table t (
id int primary key
);

表級文法：

create table t (id int,    name varchar(10),    primary key(id, name)    #我們可以給主鍵命名，但僅僅是文法上支援，功能上不支援。    #不管我們如何命名，MySQL主鍵名都是primary。    #primary key pk(id, name));

　　當我們使用多個欄位充當主鍵（聯合主鍵），作為主鍵的多個欄位只要不同時相同，就認為是不重複的，但是，每個欄位都不允許為null。

建表之後增加主鍵：

create table t(
id int
);
alter table t add primary key(id);
alter table t modify id int primary key;

刪除主鍵：

alter table t drop primary key;

4、外鍵約束：

如果B表中B1欄位參照A表中的A1欄位，則我們稱B表為從表，A表為主表。B1欄位的值或者為null，或者必須是A1欄位中存在的值。A1欄位必須是主鍵約束，或者是唯一性限制式。

圖書表
create table book(
id int primary key,
name varchar(30),
author varchar(30)
);

借書表
#欄位級文法，MySQL僅支援文法，不支援功能。
create table borrow(
id int primary key,
book_id int references book(id),
borrow_person varchar(30)
);

#表級文法，MySQL支援
drop table if exists borrow;
create table borrow(
id int primary key,
book_id int,
borrow_person varchar(30),
foreign key(book_id) references book(id)
#也可以自訂外鍵的名字。
#constraint fk foreign key(book_id) references book(id)
);

insert into book(id, name, author) values(1, ‘Java‘, ‘abc‘);insert into book(id, name, author) values(2, ‘C++‘, ‘def‘);insert into book(id, name, author) values(3, ‘C#‘, ‘張三‘);insert into book(id, name, author) values(4, ‘Hadoop‘, ‘小李‘);select * from book;insert into borrow(id, book_id, borrow_person)values (1001, 3, ‘學生A‘);#錯誤，違反外鍵約束。#insert into borrow(id, book_id, borrow_person)#values (1002, 5, ‘學生B‘);insert into borrow(id, book_id, borrow_person)values (1002, null, ‘學生B‘);update book set id=10 where id=3delete from book where id=3;

　　在建表之後增加外鍵：

create table borrow(
id int primary key,
book_id int,
borrow_person varchar(30)
);
alter table borrow add foreign key(book_id) references book(id);

指定外鍵名
alter table borrow add constraint fk   foreign key(book_id) references book(id);
刪除外鍵

alter table borrow drop foreign key fk;

當主表的某條記錄被從表所參照時，當主表記錄修改或刪除時，
從表的表現方式（行為）：
1restrict 當主表記錄修改或刪除時，拒絕執行。
2cascade 當主表記錄修改或刪除時，從表隨之也修改或刪除。
3set null 當主表記錄修改或刪除時，從表記錄設定為null值。
4no action 等價於restrict
預設的行為為：restrict

   5、檢查約束（MySQL僅支援文法，不支援功能）

create table t(    id int primary key,    age int,    check (age > 0));insert into t (id, age) values (1, -2);select * from t;

　　

約束的表級文法與欄位級文法。

1 相對於表級文法，欄位級文法更簡單些。
2 欄位級文法只能作用於單個欄位，而表級文法可以作用於多個 欄位。例如：聯合主鍵。
3 欄位級文法不能為約束命名，而表級文法可以為約束命名。

 

五、union

union 用於合并多個結果集。

要求多個結果集的欄位類型與欄位數量一致。

union distinct 會去掉結果集中的重複記錄。

union all 不會去掉結果集中的重複記錄。

預設為union distinct

優先考慮使用union all（效能會好一些）。

select * from studentselect * from stay;select id from student union select id from stay;select id from student union all select id from stay;

六、子查詢

子查詢即查詢中還有查詢（巢狀查詢）
根據子查詢出現的位置，可以將子查詢分為兩類：
1 出現在from之後，作為臨時的資料表。
2 出現在where（having）之後，作為過濾條件。

子查詢需要使用()括起。

子查詢根據查詢結果記錄條數，可以將子查詢分為：
1 單行子查詢返回一條記錄
2 多行子查詢返回多條（一條）記錄。

Mysql(三)約束