Mysql基礎2-數據定義語言DDL

 主要：

1. 資料庫操作語句
2. 數據表操作語句
3. 視圖定義語句
4. 資料庫表設計原則

　　DDL: Data Definition Language 數據定義語言

資料庫操作語句

　　創建庫

　　 創建資料庫： create database dbname [charset 字元編碼] [collate 排序規則]； 如：

create database db charset utf8 collate utf8_general_ci;

　　查詢庫

　　1） 查看所有可用的字元編碼：  show charset;

　　2） 查看所有可用的排序規則:     show collation

　　3）顯示所有資料庫： show databases;

　　4） 顯示一個資料庫的創建語句： show create database 資料庫名；   如 show create database test;

　　刪除庫

　　 刪除資料庫：  drop database [if exists] 資料庫名

 drop database if exists db;

　　 修改庫

　　修改資料庫（只能修改資料庫的選項）： alter database 資料庫名 charset 新的編碼 collate 新的排序規則

　　　　資料庫修改只能修改： 修改編碼，修改排序規則

　　選擇庫

　　 選擇資料庫： use dbname;

表操作語句

 創建表

　　基本形式：

-- 形式1： 
create table  [if not exists] 表名 （欄位列表[, 索引或約束列表]）[表選項]

-- 形式2:
create table [if not exists] 表名  (欄位1， 欄位2， ..... [, 索引1， 索引2,...., 約束1， 約束2, ......])

　　欄位設定形式： 

　　　　　　欄位名  類型 [欄位屬性1  欄位屬性2  ...]

　　欄位屬性

　　　　auto_increment:  僅用於整數類型。設定的欄位的值自動獲得增長值

　　　　not null: 用於設定該欄位不能為空null，    沒有設置，則預設可以為空

　　　　primary key： 設定欄位為主鍵。 

　　　　unique key:  設置欄位 唯一，既不重覆

　　　　default 預設值：  設置欄位的預設值。 在insert時，沒賦值則使用該預設值。

　　　　comment 說明欄位　

create table tab_user(
   id int unsigned auto_increment primary key comment '主鍵', 
   username varchar(30) not null unique key comment '用戶名',
   password varchar(32) not null comment '密碼',
   age tinyint unsigned default 18 comment '年齡',
  email varchar(50) comment '電子郵箱'
);
-- 查看表結構
desc tab_user;

insert into tab_user (id, username, password, age, email) values(1,'user1','123',20,'[email protected]'),(null,'user2',md5('123'),null,'[email protected]');

insert into tab_user (username,password,email) values('user3',md5('123'),'[email protected]');

select * from tab_user;

　　索引

 　　概念：

　　　　　　是系統內部自動維護的隱藏的“數據表”。其中的數據是自動排好序的。

　　　　　　一個表可以設定或添加多個索引，對應則會有多個排序。

　　　　　　在資料庫內部，資料庫管理系統會創建並維護一個與當前表關聯的“索引表”。該表數據按一定的方式進行了排序。

　　作用：

　　　　　　加快查詢速度。 對沒有索引的數據表查詢數據，需要進行全表掃描。

　　　　　　過多索引會消耗資源，加速了數據讀取，同時加重了資料庫的增刪改執行的負擔

　　　　　　故： 索引一盤創建在 搜索， 排序， 分組等涉及經常查詢的數據列上。

　　創建索引

　　形式：  索引類型（要建立索引的欄位名）

　　創建索引： 有3種方式創建

　　　　方式1：  使用關鍵字 key 或 index 隨表一起創建

　　　　方式2：  表已經被創建了， 使用create index 命令創建.

　　　　　　　　　　如 create index ue on tab_user(username, email); #創建名稱為ue的索引為tab_user表的兩個列

　　　　方式3：  表已經被創建了， 使用alter table 命令創建

　　查看索引

　　查看索引： 生成索引清單

　　　　show index from 表名；  如 show index from tab_user;

　　刪除索引

　　 刪除指定的索引:　drop index 索引名稱 on 表名；  如 drop index ue on tab_user;

　　索引類別：

　　　　1）普通索引： index      

　　　　　　設定形式：   key [索引名]   （欄位名1[, 欄位名2,....]）

　　　　　　　　　　key與index是同義詞

　　　　　　作用： 加快查詢速度。 但會占用磁碟空間，減慢在索引數據列上的插入，修改和刪除操作。

　　　　2） 唯一索引

　　　　　　作用： 設定某欄位唯一避免數據重覆， 且建立索引，加快查詢速度

　　　　　　設定形式： unique [key]  [索引名]  （欄位名1[, 欄位名2, .....]）

　　　　　　使用情形： 當確定某個數據列將只包含不重覆的值時。 如果數據表中已經存在該欄位，則會拒絕插入。  如 ： 註冊郵箱

　　　　3） 主鍵索引

　　　　　　作用： 設定某欄位為主鍵， 且建立索引，具有唯一性。

　　　　　　主鍵與unique區別：

　　　　　　　　唯一性可以為空null, 而主鍵不能為空；  

　　　　　　　　每個數據表有且只能有1個主鍵索引， 但可以有多個唯一索引

　　　　　　　　主鍵索引是唯一索引的特例

　　　　　　設定形式： primary key  [索引名]  （欄位名1[, 欄位名2, .....]）

　　　　　　聯合主鍵： 多個欄位確定主鍵

　　　　4） 全文索引

　　　　　　設定某欄位可以進行全文查找

　　　　　　設定形式：  fulltext [key]  [索引名]  （欄位名1[, 欄位名2, .....]）

　　　　5） 外鍵索引

　　　　　　形式： foreign key (欄位名)  references 其他表 （對應其他表中的欄位名);

　　　　　　外鍵： 指設定的某個表tabel的某個欄位column1, 它的數據值必須是在另一個表table2中的某個欄位f2中存在。

　　　　　　　　　 如果給一個設定了外鍵的欄位插入一個值，而該值並沒有在該外鍵所指定的外部表的對應欄位中出現，則該值就會插入失敗

-- 索引創建語法
create table tab_index(
    id int auto_increment,
    user_name varchar(20),
    email varchar(50),
    age int,
    key (email),   # 普通索引
    primary key(id),  # 主鍵索引
    unique key(user_name)  # 唯一索引
);

show index from tab_index;

-- 常規索引
create table carts (
    cid int(10) not null auto_increment,
    uid int(10) not null,
    bid int(10) not null,
    num int(10) not null,
    primary key (cid),
    key ind(uid,bid)     -- 如果未給出索引名ind ,系統會根據第一個索引列的名稱自動選一個
)                        -- 建議使用“表名_列表”為索引命名
engine=innodb,
charset=utf8,
auto_increment=1,
comment '常規索引的創建';

show index from carts ;

-- 外鍵索引
create table departs(
    id int auto_increment primary key,
    name  varchar(20) unique key comment '部門名稱',
    pid  int  comment '上級部門',
    created_at  date comment '創建時間'
)engine=innodb,
charset=utf8,
auto_increment=1,
comment '部門表'
;

create table depart_user(
    id int auto_increment primary key,
    name varchar(10),
    age tinyint,
    depart_id  int comment '部門id',  --  插入該列數據時，必須是departs表中id中已經存在的值才能成功插入
    foreign key (depart_id ) references departs(id)
)engine=innodb,
charset=utf8,
auto_increment=1,
comment '用戶表'
;

-- 測試SQL
insert into departs (id, name, pid, created_at) values (null, 'market', 0, now());

-- depart_user 中插入的depart_id值在departs存在時，可以插入
insert into depart_user (id, name, age, depart_id) values(null, 'Tom',20,1);

-- depart_user 中插入的depart_id值在departs不存在時，不能插入
insert into depart_user (id, name, age, depart_id) values(null, 'Tom',20,10);

　　約束

　　約束是要求數據需要滿足指定條件的一種規定

　　1） 主鍵約束

　　　　形式: primary key(欄位名)

　　　   作用： 設定的欄位的值用於唯一確定一行數據。

　　2） 唯一約束

　　　　形式： unique key(欄位名)

　　　　作用： 使設定欄位的值具有 “唯一性”

　　3）外鍵約束

　　　　形式： foreign key(欄位名) references 其他表(對應其他表中的欄位名)

　　　　作用： 該設定欄位的值，必須在其設定的對應表中的對應欄位中已經有該值。

　　4） 非空約束

　　　　形式：  not null

　　　　作用： 設定的欄位不能為空。 該約束只能寫在欄位屬性上

　　5） 預設約束

　　　　形式： default XX值

　　　　作用： 當插入數據時，沒有給該設定的欄位賦值，則該欄位使用default 的預設值。 只能寫在欄位屬性上

　　6） 檢查約束

　　　　形式： check(某種判斷語句)    如： create table tab (age tinyint, check(age > 0 and age < 100));

　　　　作用： 設定的欄位必須滿足check條件

　　　　mysql支持性不好， 有的版本插入不滿足要求的數據時，會報錯。如版本5.7

　　　　　　本次測試在5.5.53中，超出數據可以插入，並沒有作用。

-- 檢查約束測試
create table tab_check(
   age tinyint,
   check (age>=0 and age<100)  -- 檢查約束  
);

-- 測試數據
insert into tab_check(age) values (10);

-- 插入非法數據 既不在0~100之間
insert into tab_check(age) values (110);

select * from tab_check;

　　表選項列表

　　各個表選項之間使用逗號或空格隔開

-- 表選項
create table 表名(
    ....
    ....      
)
charset = 字元編碼,   
engine = 存儲引擎（也叫表類型）,
auto_increment = 表自增長欄位的初始值，預設是1,
comment = '表說明文字....'
;

　　1） charset=字元編碼； 設定的字元編碼是為了跟資料庫設定的不一樣。如果相同則可以不用設定

　　2） engine=存儲引擎（也叫表類型）。是指一個表中的數據以何種方式存放在文件或記憶體中

　　　　   常見存儲引擎：  InnoDB, MyIsam,BDB,archive,Memory

　　　　存儲引擎： 是將數據存儲到硬碟的機制。不同的存儲機制主要從2大層面考慮

　　　　　　1-快速度， 2-多功能

　　3） auto_increment=表自增長欄位的初始值，預設是1

　　4） comment='該表的一些說明文字'

    不同的存儲引擎（表類型）提供不同的性能特性和可用功能： 實際使用根據數據的具體使用情形（需求）來選擇合適的存儲引擎

-- 表選項
create table tab_option(
    id int unsigned  auto_increment primary key,
    name varchar(10),
    age tinyint
)
charset = gbk,    #當前資料庫的字元編碼是utf8
engine = MyIsam,
auto_increment = 1000,
comment = '用戶表';

-- 查看表創建語句
show create table tab_option;

　　修改表

　　　對於欄位： 添加，刪除，修改

　　　對於索引： 添加，刪除

　　　　1）表重命名： 

　　　　　　　　alter table 表名 rename to 新表名

　　　　2）添加欄位：

　　　　　　　　alter table 表名 add [column] 新欄位名 欄位類型 [欄位屬性列表]

　　　　3）修改欄位並改名： 

　　　　　　　　alter table 表名 change [column] 舊欄位名 新欄位名  新欄位類型 [新欄位屬性列表]

　　　　4）修改欄位僅改屬性：

　　　　　　　　alter table 表名 modify  [column] 欄位名 新欄位類型 [新欄位屬性列表]

　　　　5) 刪除欄位：

　　　　　　　　alter table 表名 drop [colomn] 欄位名

　　　　6）添加欄位預設值

　　　　　　　　alter table 表名 alter [column] 欄位名 set default 預設值

　　　　7）刪除欄位預設值：

　　　　　　　　alter table 表名 alter [column] 欄位名 drop default 預設值

　　　　8）修改表名

　　　　　　alter table 表名 rename [to]  新表名

　　　　9） 修改表選項

　　　　　　alter table 表名 選項1=選項值1， 選項2=選項值2,....

　　刪除表

　　drop table [if exists] 表名

　　顯示表信息

　　1） 顯示所有表：　show tables;

　　2)   顯示表結構： 

　　　　　desc 表名；

　　　　   description 表名；

　　3） 顯示表創建語句

　　　　show create table 表名

　　複製表

　　1） 複製表結構： 從已存在的表複製表結構

　　　　create table [if not exists] 新表名 like 舊表名；

　　2） 表的完整複製包含數據

　　　　方式1： create  table 表1  as  select * from B.表2       #將B庫中的表2 複製到 當前庫命名為表1， 包括數據

　　　　方式2：

　　　　　　create table [if not exists] 新表名 like 舊表名；

　　　　　　insert into 新表名 select * from 舊表名

　　索引操作

　　1）創建索引

　　　　create [unique | fulltext] index 索引名 on 表名 （欄位1，欄位2,....）

　　　　　　實際在系統內部會映射為一條"alter table"的添加索引語句

　　2) 查看表索引

　　　　show index from 表名

　　3）刪除索引

　　　　drop index 索引名 on 表名     如： drop index ind on tab_option;     【實際會映射一條alter table 的刪除索引語句】

　　　　alter table 表名 drop index 索引名

　　　　alter table 表名 drop key 索引名

　　4） 刪除主鍵

　　　　alter table 表名 drop primary key

　　5) 刪除外鍵

　　　　alter table 表名 drop foreign key 鍵名

　　6）添加普通索引

　　　　alter table 表名 add key [索引名] (欄位名1[, 欄位名2,....])

　　7) 添加唯一索引

　　　　alter table 表名 add unique key [索引名] (欄位名1[,欄位名2,...])

　　8) 添加主鍵索引

　　　　alter table 表名 add primary key [索引名] (欄位名1[,欄位名2,...])

　　9) 添加外鍵索引

　　　　alter table 表名 add foreign key (欄位名1[,欄位名2,....])  refrences  表名2 (欄位名1[, 欄位名2,....])

視圖定義語句

　　視圖： 就是1個虛擬表，內容由一條查詢語句定義。 既是一條select語句的查詢結果，預先放在資料庫中。可以當做一張表來使用

　　使用視圖： 當做一個表使用

　　創建視圖

　　create view 視圖名 [(列1， 列2， ....)] as select 語句；

　　修改視圖

　　alter view 視圖名 [(列1，列2，列3，....)] as select 語句；

　　刪除視圖

　　drop view [if exists] 視圖名

資料庫表設計原則

　　資料庫設計原則--也稱為資料庫設計三範式（3NF）

　　1) 第一範式(1NF)： 原子性，數據不可再分

　　　　    一個表中的數據（欄位值）不可再分

　　2) 第二範式(2NF)： 唯一性， 消除部分依賴

　　　　    使每一行數據具有唯一性，並消除數據之間的“部分依賴”，使一個表中的非主鍵欄位，完全依賴於主鍵欄位

　　　　    容易出現違背2NF情形： 存在聯合主鍵的表

　　　　　保證唯一性： 給表設計主鍵即可實現

　　　　  什麼是依賴？ 表中欄位B可以由另一欄位A來決定 ， 則表示 B依賴於欄位A， 或A決定欄位B

　　　　　　　　含義：如果根據欄位A的某個值，一定可以找出一個確定的欄位B的值，就是A決定B
                            　　如： 主鍵決定其他欄位，其他欄位依賴主鍵

　　　　什麼是部分依賴？ 如果某個欄位只依賴 部分主鍵欄位 。  

　　　　　　發生前提： 主鍵欄位有多個

　　　　什麼是完全依賴？ 既某個欄位，是依賴於 主鍵的所有 欄位。
                    如果一個表的主鍵只有一個欄位， 則此時必是完全依賴

　　3) 第三範式(3NF)： 獨立性， 消除傳遞依賴

　　　　一個表中任何一個非主鍵，完全依賴於主鍵，而不能依賴於另外的非主鍵

　　　　出現違背情形：如果一個表中的一個非主鍵欄位（B）依賴於另一個非主鍵欄位（A）

　　　　　　　　　　　因為A作為非主鍵欄位，自然是依賴於主鍵欄位的（範式2所決定的），則此時就會出現傳遞依賴
                通常： 一個表存儲一種數據 即可滿足第3範式