2. 數(shù)據(jù)庫設(shè)計-DDL

下面我們就正式的進入到SQL語句的學(xué)習(xí)，在學(xué)習(xí)之前先給大家介紹一下我們要開發(fā)一個項目，整個開發(fā)流程是什么樣的，以及在流程當(dāng)中哪些環(huán)節(jié)會涉及到數(shù)據(jù)庫。

2.1 項目開發(fā)流程

需求文檔：

• 在我們開發(fā)一個項目或者項目當(dāng)中的某個模塊之前，會先會拿到產(chǎn)品經(jīng)理給我們提供的頁面原型及需求文檔。

設(shè)計：

• 拿到產(chǎn)品原型和需求文檔之后，我們首先要做的不是編碼，而是要先進行項目的設(shè)計，其中就包括概要設(shè)計、詳細設(shè)計、接口設(shè)計、數(shù)據(jù)庫設(shè)計等等。
• 數(shù)據(jù)庫設(shè)計根據(jù)產(chǎn)品原型以及需求文檔，要分析各個模塊涉及到的表結(jié)構(gòu)以及表結(jié)構(gòu)之間的關(guān)系，以及表結(jié)構(gòu)的詳細信息。最終我們需要將數(shù)據(jù)庫以及數(shù)據(jù)庫當(dāng)中的表結(jié)構(gòu)設(shè)計創(chuàng)建出來。

開發(fā)/測試：

• 參照頁面原型和需求進行編碼，實現(xiàn)業(yè)務(wù)功能。在這個過程當(dāng)中，我們就需要來操作設(shè)計出來的數(shù)據(jù)庫表結(jié)構(gòu)，來完成業(yè)務(wù)的增刪改查操作等。

部署上線：

• 在項目的功能開發(fā)測試完成之后，項目就可以上線運行了，后期如果項目遇到性能瓶頸，還需要對項目進行優(yōu)化。優(yōu)化很重要的一個部分就是數(shù)據(jù)庫的優(yōu)化，包括數(shù)據(jù)庫當(dāng)中索引的建立、SQL 的優(yōu)化、分庫分表等操作。

在上述的流程當(dāng)中，針對于數(shù)據(jù)庫來說，主要包括三個階段：

1. 數(shù)據(jù)庫設(shè)計階段
   • 參照頁面原型以及需求文檔設(shè)計數(shù)據(jù)庫表結(jié)構(gòu)
2. 數(shù)據(jù)庫操作階段
   • 根據(jù)業(yè)務(wù)功能的實現(xiàn)，編寫SQL語句對數(shù)據(jù)表中的數(shù)據(jù)進行增刪改查操作
3. 數(shù)據(jù)庫優(yōu)化階段
   • 通過數(shù)據(jù)庫的優(yōu)化來提高數(shù)據(jù)庫的訪問性能。優(yōu)化手段：索引、SQL優(yōu)化、分庫分表等

接下來我們就先來學(xué)習(xí)第一部分數(shù)據(jù)庫的設(shè)計，而數(shù)據(jù)庫的設(shè)計就是來定義數(shù)據(jù)庫，定義表結(jié)構(gòu)以及表中的字段。

2.2 數(shù)據(jù)庫操作

我們在進行數(shù)據(jù)庫設(shè)計，需要使用到剛才所介紹SQL分類中的DDL語句。

DDL英文全稱是Data Definition Language(數(shù)據(jù)定義語言)，用來定義數(shù)據(jù)庫對象(數(shù)據(jù)庫、表)。

DDL中數(shù)據(jù)庫的常見操作：查詢、創(chuàng)建、使用、刪除。

2.2.1 查詢數(shù)據(jù)庫

查詢所有數(shù)據(jù)庫：

show databases;

命令行中執(zhí)行效果如下：

查詢當(dāng)前數(shù)據(jù)庫：

select database();

命令行中執(zhí)行效果如果：

我們要操作某一個數(shù)據(jù)庫，必須要切換到對應(yīng)的數(shù)據(jù)庫中。

通過指令：select database() ，就可以查詢到當(dāng)前所處的數(shù)據(jù)庫

2.2.2 創(chuàng)建數(shù)據(jù)庫

語法：

create database [ if not exists ] 數(shù)據(jù)庫名;

案例： 創(chuàng)建一個itcast數(shù)據(jù)庫。

create database itcast;

命令行執(zhí)行效果如下：

注意：在同一個數(shù)據(jù)庫服務(wù)器中，不能創(chuàng)建兩個名稱相同的數(shù)據(jù)庫，否則將會報錯。

• 可以使用if not exists來避免這個問題

-- 數(shù)據(jù)庫不存在,則創(chuàng)建該數(shù)據(jù)庫；如果存在則不創(chuàng)建
create database if not extists itcast; 

命令行執(zhí)行效果如下：

2.2.3 使用數(shù)據(jù)庫

語法：

use 數(shù)據(jù)庫名 ;

我們要操作某一個數(shù)據(jù)庫下的表時，就需要通過該指令，切換到對應(yīng)的數(shù)據(jù)庫下，否則不能操作。

案例：切換到itcast數(shù)據(jù)

use itcast;

命令執(zhí)行效果如下：

2.2.4 刪除數(shù)據(jù)庫

語法：

drop database [ if exists ] 數(shù)據(jù)庫名 ;

如果刪除一個不存在的數(shù)據(jù)庫，將會報錯。

可以加上參數(shù) if exists ，如果數(shù)據(jù)庫存在，再執(zhí)行刪除，否則不執(zhí)行刪除。

案例：刪除itcast數(shù)據(jù)庫

drop database if exists itcast; -- itcast數(shù)據(jù)庫存在時刪除

命令執(zhí)行效果如下：

說明：上述語法中的database，也可以替換成 schema

• 如：create schema db01;
• 如：show schemas;

2.3 圖形化工具

2.3.1 介紹

前面我們講解了DDL中關(guān)于數(shù)據(jù)庫操作的SQL語句，在我們編寫這些SQL時，都是在命令行當(dāng)中完成的。大家在練習(xí)的時候應(yīng)該也感受到了，在命令行當(dāng)中來敲這些SQL語句很不方便，主要的原因有以下 3 點：

1. 沒有任何代碼提示。（全靠記憶，容易敲錯字母造成執(zhí)行報錯）
2. 操作繁瑣，影響開發(fā)效率。（所有的功能操作都是通過SQL語句來完成的）
3. 編寫過的SQL代碼無法保存。

在項目開發(fā)當(dāng)中，通常為了提高開發(fā)效率，都會借助于現(xiàn)成的圖形化管理工具來操作數(shù)據(jù)庫。

目前MySQL主流的圖形化界面工具有以下幾種：

DataGrip是JetBrains旗下的一款數(shù)據(jù)庫管理工具，是管理和開發(fā)MySQL、Oracle、PostgreSQL的理想解決方案。

官網(wǎng)： https://www.jetbrains.com/zh-cn/datagrip/

2.3.2 安裝

安裝： 參考資料中提供的《DataGrip安裝手冊》

說明：DataGrip這款工具可以不用安裝，因為Jetbrains公司已經(jīng)將DataGrip這款工具的功能已經(jīng)集成到了 IDEA當(dāng)中，所以我們就可以使用IDEA來作為一款圖形化界面工具來操作Mysql數(shù)據(jù)庫。

2.3.3 使用

2.2.3.1 連接數(shù)據(jù)庫

1、打開IDEA自帶的Database

2、配置MySQL

3、輸入相關(guān)信息

4、下載MySQL連接驅(qū)動

5、測試數(shù)據(jù)庫連接

6、保存配置

默認情況下，連接上了MySQL數(shù)據(jù)庫之后， 數(shù)據(jù)庫并沒有全部展示出來。 需要選擇要展示哪些數(shù)據(jù)庫。具體操作如下：

2.2.3.2 操作數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫：

有了圖形化界面工具后，就可以方便的使用圖形化工具：創(chuàng)建數(shù)據(jù)庫，創(chuàng)建表、修改表等DDL操作。

其實工具底層也是通過DDL語句操作的數(shù)據(jù)庫，只不過這些SQL語句是圖形化界面工具幫我們自動完成的。

查看所有數(shù)據(jù)庫：

2.3 表操作

學(xué)習(xí)完了DDL語句當(dāng)中關(guān)于數(shù)據(jù)庫的操作之后，接下來我們繼續(xù)學(xué)習(xí)DDL語句當(dāng)中關(guān)于表結(jié)構(gòu)的操作。

關(guān)于表結(jié)構(gòu)的操作也是包含四個部分：創(chuàng)建表、查詢表、修改表、刪除表。

2.3.1 創(chuàng)建

2.3.1.1 語法

create table  表名(
	字段1  字段1類型 [約束]  [comment  字段1注釋 ],
	字段2  字段2類型 [約束]  [comment  字段2注釋 ],
	......
	字段n  字段n類型 [約束]  [comment  字段n注釋 ] 
) [ comment  表注釋 ] ;

注意： [ ] 中的內(nèi)容為可選參數(shù)； 最后一個字段后面沒有逗號

案例：創(chuàng)建tb_user表

• 對應(yīng)的結(jié)構(gòu)如下：

• 建表語句：

create table tb_user (
    id int comment 'ID,唯一標識',   # id是一行數(shù)據(jù)的唯一標識（不能重復(fù)）
    username varchar(20) comment '用戶名',
    name varchar(10) comment '姓名',
    age int comment '年齡',
    gender char(1) comment '性別'
) comment '用戶表';

數(shù)據(jù)表創(chuàng)建完成，接下來我們還需要測試一下是否可以往這張表結(jié)構(gòu)當(dāng)中來存儲數(shù)據(jù)。

雙擊打開tb_user表結(jié)構(gòu)，大家會發(fā)現(xiàn)里面沒有數(shù)據(jù)：

添加數(shù)據(jù)：

此時我們再插入一條數(shù)據(jù)：

我們之前提到過：id字段是一行數(shù)據(jù)的唯一標識，不能有重復(fù)值。但是現(xiàn)在數(shù)據(jù)表中有兩個相同的id值，這是為什么呢？

• 其實我們現(xiàn)在創(chuàng)建表結(jié)構(gòu)的時候， id這個字段我們只加了一個備注信息說明它是一個唯一標識，但是在數(shù)據(jù)庫層面呢，并沒有去限制字段存儲的數(shù)據(jù)。所以id這個字段沒有起到唯一標識的作用。

想要限制字段所存儲的數(shù)據(jù)，就需要用到數(shù)據(jù)庫中的約束。

2.3.1.2 約束

概念：所謂約束就是作用在表中字段上的規(guī)則，用于限制存儲在表中的數(shù)據(jù)。

作用：就是來保證數(shù)據(jù)庫當(dāng)中數(shù)據(jù)的正確性、有效性和完整性。（后面的學(xué)習(xí)會驗證這些）

在MySQL數(shù)據(jù)庫當(dāng)中，提供了以下5種約束：

注意：約束是作用于表中字段上的，可以在創(chuàng)建表/修改表的時候添加約束。

案例：創(chuàng)建tb_user表

• 對應(yīng)的結(jié)構(gòu)如下：

在上述的表結(jié)構(gòu)中:

• id 是一行數(shù)據(jù)的唯一標識

• username 用戶名字段是非空且唯一的

• name 姓名字段是不允許存儲空值的

• gender 性別字段是有默認值，默認為男

• 建表語句：

create table tb_user (
    id int primary key comment 'ID,唯一標識', 
    username varchar(20) not null unique comment '用戶名',
    name varchar(10) not null comment '姓名',
    age int comment '年齡',
    gender char(1) default '男' comment '性別'
) comment '用戶表';

數(shù)據(jù)表創(chuàng)建完成，接下來測試一下表中字段上的約束是否生效

大家有沒有發(fā)現(xiàn)一個問題：id字段下存儲的值，如果由我們自己來維護會比較麻煩(必須保證值的唯一性)。MySQL數(shù)據(jù)庫為了解決這個問題，給我們提供了一個關(guān)鍵字：auto_increment（自動增長）

主鍵自增：auto_increment

• 每次插入新的行記錄時，數(shù)據(jù)庫自動生成id字段(主鍵)下的值
• 具有auto_increment的數(shù)據(jù)列是一個正數(shù)序列開始增長(從1開始自增)

create table tb_user (
    id int primary key auto_increment comment 'ID,唯一標識', #主鍵自動增長
    username varchar(20) not null unique comment '用戶名',
    name varchar(10) not null comment '姓名',
    age int comment '年齡',
    gender char(1) default '男' comment '性別'
) comment '用戶表';

測試：主鍵自增

2.3.1.3 數(shù)據(jù)類型

在上面建表語句中，我們在指定字段的數(shù)據(jù)類型時，用到了int 、varchar、char，那么在MySQL中除了以上的數(shù)據(jù)類型，還有哪些常見的數(shù)據(jù)類型呢？ 接下來,我們就來詳細介紹一下MySQL的數(shù)據(jù)類型。

MySQL中的數(shù)據(jù)類型有很多，主要分為三類：數(shù)值類型、字符串類型、日期時間類型。

數(shù)值類型

示例: 
    年齡字段 ---不會出現(xiàn)負數(shù), 而且人的年齡不會太大
	age tinyint unsigned
	
	分數(shù) ---總分100分, 最多出現(xiàn)一位小數(shù)
	score double(4,1)

字符串類型

char 與 varchar 都可以描述字符串，char是定長字符串，指定長度多長，就占用多少個字符，和字段值的長度無關(guān) 。而varchar是變長字符串，指定的長度為最大占用長度 。相對來說，char的性能會更高些。

示例： 
    用戶名 username ---長度不定, 最長不會超過50
	username varchar(50)
	
	手機號 phone ---固定長度為11
	phone char(11)

日期時間類型

示例: 
	生日字段  birthday ---生日只需要年月日  
	birthday date
	
	創(chuàng)建時間 createtime --- 需要精確到時分秒
	createtime  datetime

2.3.1.4 案例

需求：根據(jù)產(chǎn)品原型/需求創(chuàng)建表((設(shè)計合理的數(shù)據(jù)類型、長度、約束)

參考資料中提供的《黑馬-tlias智能學(xué)習(xí)輔助系統(tǒng)》頁面原型，設(shè)計員工管理模塊的表結(jié)構(gòu)

暫不考慮所屬部門字段

產(chǎn)品原型及需求如下：

步驟：

1. 閱讀產(chǎn)品原型及需求文檔，看看里面涉及到哪些字段。

2. 查看需求文檔說明，確認各個字段的類型以及字段存儲數(shù)據(jù)的長度限制。

3. 在頁面原型中描述的基礎(chǔ)字段的基礎(chǔ)上，再增加額外的基礎(chǔ)字段。

使用SQL創(chuàng)建表：

create table emp (
  id int unsigned primary key auto_increment comment 'ID',
  username varchar(20) not null unique comment '用戶名',
  password varchar(32) default '123456' comment '密碼',
  name varchar(10) not null comment '姓名',
  gender tinyint unsigned not null comment '性別, 說明: 1 男, 2 女',
  image varchar(300) comment '圖像',
  job tinyint unsigned comment '職位, 說明: 1 班主任,2 講師, 3 學(xué)工主管, 4 教研主管',
  entrydate date comment '入職時間',
  create_time datetime not null comment '創(chuàng)建時間',
  update_time datetime not null comment '修改時間'
) comment '員工表';

除了使用SQL語句創(chuàng)建表外，我們還可以借助于圖形化界面來創(chuàng)建表結(jié)構(gòu)，這種創(chuàng)建方式會更加直觀、更加方便。

操作步驟如下：

1. 在指定操作的數(shù)據(jù)庫上，右鍵New ==> Table

2. 編輯表結(jié)構(gòu)的相關(guān)信息

2.3.1.5 設(shè)計表流程

通過上面的案例，我們明白了，設(shè)計一張表，基本的流程如下：

1. 閱讀頁面原型及需求文檔

2. 基于頁面原則和需求文檔，確定原型字段(類型、長度限制、約束)

3. 再增加表設(shè)計所需要的業(yè)務(wù)基礎(chǔ)字段(id主鍵、插入時間、修改時間)

說明：

• create_time：記錄的是當(dāng)前這條數(shù)據(jù)插入的時間。

• update_time：記錄當(dāng)前這條數(shù)據(jù)最后更新的時間。

2.3.2 查詢

關(guān)于表結(jié)構(gòu)的查詢操作，工作中一般都是直接基于圖形化界面操作。

查詢當(dāng)前數(shù)據(jù)庫所有表

show tables;

查看指定表結(jié)構(gòu)

desc 表名 ;#可以查看指定表的字段、字段的類型、是否可以為NULL、是否存在默認值等信息

查詢指定表的建表語句

show create table 表名 ;

2.3.3 修改

關(guān)于表結(jié)構(gòu)的修改操作，工作中一般都是直接基于圖形化界面操作。

添加字段

alter table 表名 add  字段名  類型(長度)  [comment 注釋]  [約束];

案例： 為tb_emp表添加字段qq，字段類型為 varchar(11)

alter table tb_emp add  qq  varchar(11) comment 'QQ號碼';

圖形化操作：添加字段

修改數(shù)據(jù)類型

alter table 表名 modify  字段名  新數(shù)據(jù)類型(長度);

alter table 表名 change  舊字段名  新字段名  類型(長度)  [comment 注釋]  [約束];

案例：修改qq字段的字段類型，將其長度由11修改為13

alter table tb_emp modify qq varchar(13) comment 'QQ號碼';

案例：修改qq字段名為 qq_num，字段類型varchar(13)

alter table tb_emp change qq qq_num varchar(13) comment 'QQ號碼';

圖形化操作：修改數(shù)據(jù)類型和字段名

刪除字段

alter table 表名 drop 字段名;

案例：刪除tb_emp表中的qq_num字段

alter table tb_emp drop qq_num;

圖形化操作：刪除字段

修改表名

rename table 表名 to  新表名;

案例：將當(dāng)前的tb_emp表的表名修改為emp

rename table tb_emp to emp;

圖形化操作：修改表名

2.3.4 刪除

關(guān)于表結(jié)構(gòu)的刪除操作，工作中一般都是直接基于圖形化界面操作。

刪除表語法：

drop  table [ if exists ]  表名;

if exists ：只有表名存在時才會刪除該表，表名不存在，則不執(zhí)行刪除操作(如果不加該參數(shù)項，刪除一張不存在的表，執(zhí)行將會報錯)。

案例：如果tb_emp表存在，則刪除tb_emp表

drop table if exists tb_emp;  -- 在刪除表時，表中的全部數(shù)據(jù)也會被刪除。

圖形化操作：刪除表

文章來源地址http://www.zghlxwxcb.cn/news/detail-822932.html

到了這里，關(guān)于【JaveWeb教程】（18） MySQL數(shù)據(jù)庫開發(fā)之 MySQL數(shù)據(jù)庫設(shè)計-DDL 如何查詢、創(chuàng)建、使用、刪除數(shù)據(jù)庫數(shù)據(jù)表 詳細代碼示例講解的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！