第37次文章：數據庫基本語法

2019 年 10 月 8 日
筆記

本周我們結束了最為複雜的dql語法，完成最後一個進階9—聯合查詢，然後進入剩下的dml和ddl語法介紹。一起來看看吧~

進階9：聯合查詢

一、含義

union：合併、聯合，將多次查詢結果合併成一個結果

二、語法

查詢語句1

union【all】

查詢語句2

union【all】

…

三、意義

1、將一條比較複雜的查詢語句拆分成多條語句

2、適用於查詢多個表的時候，查詢的列基本是一致的

四、特點

1、要求多條查詢語句的查詢列數必須一致

2、要求多條查詢語句的查詢的各列類型、順序最好一致

3、union去重，union all包含重複項

以上就結束了查詢語言（dql）的全部介紹，下面我們對其進行一個基本總結，將所有學到的dql語法匯總起來，寫出一個模板，以後需要用到哪一條直接往裏面放相應的查詢語句即可。具體如下：

select 查詢列表

from 表1 別名連接類型 join 表2 別名 on 連接條件

where 篩選條件

group by 分組條件

having 分組後篩選條件

order by 排序條件

limit 起始索引，條目數

union 聯合查詢

DML語言

DML語言稱為數據管理語言，主要負責對表格中的數據進行增刪改操作。

一、插入

1、方式一

（1）語法：insert into 表名（字段名,…） values（值,….）；

（2）特點：

要求的類型的字段的類型要一致或兼容
字段的個數和順序不一定與原始表中的字段個數和順序一致，但必須保證值和字段一一對應
假如表中有可以為null的字段，注意可以通過以下兩種方式插入null值
字段名和值都省略
字段寫，值使用null
字段和值的個數必須一致
段名可以省略，默認所有列

2、方式二

語法：insert into 表名 set 字段=值,字段=值,…;

3、兩種方式的區別

（1）方式一支持一次性插入多行，語法如下：

insert into 表名 values(值1，值2，….),(值1，值2，….),(值1，值2，….),(值1，值2，….)

（2）方式一支持子查詢，語法如下：

insert into 表名查詢語句；

4、案例

我們根據兩種方式向一個beauty表格中插入數據，beauty表格中的各個屬性如下所示：

對於各個屬性參數的插入，具體語法如下：

#1、方式一支持插入多行，方式二不支持  INSERT INTO beauty  VALUES (23,'楊冪1','女','1985-10-1','19988886666',NULL,2)  ,(24,'楊冪2','女','1985-10-1','19988886666',NULL,2)  ,(25,'楊冪3','女','1985-10-1','19988886666',NULL,2)  #2、方式一支持子查詢，方式二不支持  INSERT INTO beauty(id,NAME,phone)  SELECT 26,'宋茜','5436546';  #3、方式二插入數據  INSERT INTO beauty  SET id=17,NAME='劉亦菲',phone='999'

tips：在上面的3條插入語句中，我們僅僅展示一下語法輸入。在表格屬性中我們看到不可以為空的屬性有：id、name、phone。所以這三個屬性是我們必須插入的值。

第一條語句中，我們沒有填寫需要添加的屬性，所以默認的所有屬性都需要添加進去，同時，我們使用到了多行插入。

第二條語句中，我們填寫了需要插入的屬性，所以我們需要對應着屬性來插入相關的值，與此同時，我們使用到了子查詢語句，在select語句後面對應的插入參數值。

第三條語句中，我們使用的是方式二的set方法來，此時在表名後面不用填寫需要插入的屬性，因為set後面的語法中，已經直接填寫了對應的屬性。

總體而言，方式二較為簡單，但是由於只能單行插入，所以使用的較少，一般都是採取方式一的語法進行操作。

二、修改

1、修改單表的記錄

update 表1 別名連接類型 join 表2 別名 on 連接條件

set 字段=值，字段=值【where 篩選條件】;

2、修改多表的記錄【補充】

（1）sql92語法：update 表1 別名,表2 別名set 列=值,….where 連接條件and 篩選條件

（2）sql99語法：update 表1 別名【join type】 join 表2 別名 on 連接條件set 列=值,….where 篩選條件

3、案例

#1.修改單表中的記錄  #案例：修改beauty表中姓楊的女神電話為11122233344  UPDATE beauty  SET phone='11122233344'  WHERE NAME LIKE '楊%';  #2.修改多表的記錄  #案例：修改張無忌的女朋友的手機號114  UPDATE beauty b JOIN boys bo ON b.`boyfriend_id`=bo.`id`  SET b.`phone`=114  WHERE bo.`boyName`='張無忌';

tips：在上面的代碼中，我們主要展示對表中數據的修改語法。查看這段代碼，我們可以將其類比為插入語法中的方式二，使用set關鍵字，將特定的列值修改為設定的參數值。對比單表修改和多表修改，我們可以發現，不同點就在於多表中有一個多表連接，連接語法與我們在dql語言中使用的類似。

三、刪除

1、方式一

使用delete

（1）刪除單表的記錄

delete from 表名【where 篩選條件】【limit 條目數】

（2）級聯刪除【補充】

delete 別名1,別名2 from 表1 別名連接類型 join 表2 別名 on 連接條件

【where 篩選條件】

2、方式二

使用truncate

語法：truncate table 表名

3、案例

#1.單表刪除  #案例：刪除手機號以9結尾的女神信息  DELETE FROM beauty WHERE phone LIKE '%9';  #2.級聯刪除  #案例：刪除黃曉明的信息以及他女朋友的信息  DELETE b,bo  FROM beauty b JOIN boys bo ON b.`boyfriend_id`=bo.`id`  WHERE bo.`boyName`='黃曉明';  #方式二：truncate語句  #無法添加篩選條件，直接將整張表清空  TRUNCATE TABLE boys;

tips：對於單表刪除，直接查看語句就會一目了然，我們不再贅述。主要提一下級聯刪除，級聯刪除還是屬於多表刪除，使用到了多表互連的語法。在對表中數據進行刪除時，需要注意一點，delete後面，需要加上需要刪除的表，因為語法中涉及到了多表，而我們可能只是刪除其中某個表格中的數據，其他表格僅僅是用於篩選，如果需要刪除所有表格中符合篩選條件的數據，那麼我們就需要將每個表名放在delete關鍵字後面。

對於truncate語句，其默認的是將整個表格的數據全部清空，所以該語法中不需要加入篩選條件。對於兩種刪除語法的區別，我們在下面將會加以介紹。

4、兩種方式的區別【面試題】

（1）truncate刪除後，如果再插入，標識列從1開始；delete刪除後，如果再插入，標識列從斷點開始

（2）delete可以添加篩選條件；truncate不可以添加篩選條件

（3）truncate效率較高，delete效率較低

（4）truncate沒有返回值，delete有返回值

（5）truncate是徹底刪除，不能回滾，delete可以回滾

DDL語言

date define language數據定義語言，主要負責對數據庫和表的結構進行修改，而不是對表中數據進行修改。

一、庫的管理

1、創建庫

create database 【if not exists】庫名【character set 字符集】；

2、修改庫

alter database 庫名 character set 字符集；

3、刪除庫

drop database 【if exists】庫名；

4、案例

#1、創建庫Books  CREATE DATABASE IF NOT EXISTS books;  #2、庫的修改  #更改庫的字符集  ALTER DATABASE books CHARACTER SET utf8;  #3、庫的刪除  DROP DATABASE IF EXISTS books;

tips：在一般的工程中，我們不會對庫有什麼具體的修改，因為對庫的修改容易導致整個數據的不穩定。數據庫的命名以及字符集等等屬性設置，一般在創建的初始時就需要被設置好。在以後的運維過程中基本也不會有什麼變動，可能變化的主要就是數據庫的字符集。所以在對庫的操作中，我們主要介紹了創建和刪除，以及對數據庫字符集的修改。對比後面對錶的管理，使用的關鍵字也是一樣的！

二、表的管理

1、創建表

create table 【if not exists】表名(

字段名數據類型【約束】,

……

字段名數據類型【約束】

)；

2、修改表

（1）添加新列

alter table 表名 add column 列名類型【first|after 字段名】；

（2）修改列的類型

alter table 表名 modify column 舊字段名新類型【新約束】；

（3）對字段重命名

alter table 表名舊字段名 change column 新字段名數據類型

（4）刪除列

alter table 表名 drop column 列名；

（5）修改表名

alter table 表名 rename 【to】新表名

3、刪除表

drop table 【if exists】表名；

4、複製表

（1）僅僅複製表的結構

create table 表名1 like 表名2

（2）複製表的結構+數據

create table 表名1

select * from 表名2 【where 篩選】

5、案例

#1、表的創建  #案例：創建表Book  CREATE TABLE IF NOT EXISTS book(    id INT,#圖書編號    bName VARCHAR(20),#圖書的名稱    price DOUBLE,#價格    authorId INT,#作者編號    publishDate DATETIME#出版日期  );  #案例：創建表author  CREATE TABLE IF NOT EXISTS author(    id INT,#作者id    au_name VARCHAR(20),#作者姓名    nation VARCHAR(20)#國籍  )    #2.表的修改  #（1）修改列名  ALTER TABLE book CHANGE COLUMN publishDate puDate DATETIME;  #（2）修改列的類型或約束  ALTER TABLE book MODIFY COLUMN pudate TIMESTAMP;  #（3）添加列  ALTER TABLE author ADD COLUMN annual DOUBLE;  #（4）刪除列  ALTER TABLE author DROP COLUMN annual;  #（5）修改表名  ALTER TABLE author RENAME book_author;    #3.表的刪除  DROP TABLE IF EXISTS book_author;    #4.表的複製  INSERT INTO author  VALUES(1,'村上春樹','日本'),(2,'莫言','中國'),(3,'金庸','中國'),(4,'古龍','中國');  #（1）僅僅複製表的結構  CREATE TABLE copy LIKE author;  #（2）複製表的結構+數據  CREATE TABLE copy2 SELECT * FROM author;  #（3）僅僅複製表的部分數據  CREATE TABLE copy3 SELECT * FROM author WHERE nation = '中國';  #（4）僅僅複製表的部分結構，不複製數據  CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;

tips：在上面對錶的增刪改中，全都是按照上述的基本語法進行操作，修改列的時候，一定要注意將列的類型重新更新一下。還有一點就是表的複製，通過上面的案例可以發現一點，表的複製，使用的是create關鍵字，在表的後面可以添加子查詢語句，有點類似於dml語句中的插入語法。

三、數據類型

1、數值型

（1）整型

tinyint、smallint、mediumint、int、integer、bigint

特點：

都可以設置無符號和有符號，默認有符號，通過unsigned設置無符號
如果超出了範圍，會報out of range異常，插入臨界值
長度可以不指定，默認會有一個長度

長度代表顯示的最大寬度，如果不夠則左邊用0填充，但需要搭配zerofill，並且默認變為無符號整型。

tips：當設置為無符號時，插入負數，將會在數據庫中用0進行填充。

（2）浮點型

定點數：decimal（M,D）

浮點數：

float（M，D）

double（M，D）

特點：

M代表整數部位+小數部位的個數，D代表小數部位
如果超出範圍，則報out of range 異常，並且插入臨界值
M和D都可以省略，但對於定點數，M默認為10，D默認為0，如果是float和double，則會根據插入的數值的精度來決定精度。
如果精度要求較高，則優先考慮使用定點數。如：貨幣運算等則考慮使用。

tips：double所佔位元組數為8，float所佔位元組數為4，無其他區別。

2、字符型

char、varchar、binary、varbinary、enum、set、text、blob

char：固定長度的字符，寫法為char（M），最大長度不能超過M，其中M可以省略，默認為1

varchar：可變長度的字符，寫法為varchar（M），最大長度不能超過M，其中M不可以省略

tips：對於char和varchar兩種類型，需要根據實際情況進行選擇。當某一列確定為性別或學號等等信息時，已經可以知道其長度，此時應該選擇char，可以佔用更小的內存空間。

3、日期型

year年

date日期

time時間

datetime 日期+時間 8位元組

timestamp 日期+時間 4位元組比較容易受時區，語法模式、版本的影響，更能反映當前時區的真實時間

tips：timestamp可以根據所在時區的不同，自動的將存儲的時間轉換為對應時區的時間，所以，timestamp類型所反映的時間更加精準。

四、常見的約束

1、含義

一種限制，用於限制表中的數據，為了保證表中的數據的準確和可靠

2、分類

（1）not null ：非空，用於保證該字段的值不能為空。比如姓名，學號等（2）default：默認，用於保證該字段有默認值。比如性別

（3）primary key：主鍵，用於保證該字段的值具有唯一性，並且非空。比如學號、員工編號等

（4）unique：唯一，用於保證該字段的值具有唯一性，可以為空。比如座位號（5）check：檢查約束【mysql中不支持】。比如年齡。性別

（6）foreign key：外鍵。用於限制兩個表的關係，用於保證該字段的值必須來自於主表的關聯列的值。在從表添加外加約束，用於引用主表中某列的值。比如學生表的專業編號

3、添加約束

（1）添加約束的時機

創建表時
修改表時

（2）約束的添加分類

列級約束：六大約束語法上都支持，但是外鍵約束沒有效果
表級約束：除了非空、默認，其他的都支持

4、案例

#一、創建表時添加約束  #1、添加列級約束  /*  語法  直接在字段名和類型後面追加 約束類型即可。  只支持：默認、非空、主鍵、唯一  */  CREATE TABLE stuinfo(    id INT PRIMARY KEY,#主鍵    stuName VARCHAR(20) NOT NULL,#非空    gender CHAR(1) CHECK(gender='男' OR gender='女'),#檢查    seat INT UNIQUE,#唯一    age INT DEFAULT 18,#默認約束    majorId INT REFERENCES major(majorid)#外鍵  );  #2.添加表級約束  /*  語法：在各個字段的最下面  【constraint 約束名】 約束類型（字段名）  */  DROP TABLE IF EXISTS stuinfo;  CREATE TABLE stuinfo(    id INT,    stuname VARCHAR(20),    gender CHAR(1),    seat INT,    age INT,    majorid INT,    CONSTRAINT pk PRIMARY KEY(id),#主鍵    CONSTRAINT uq UNIQUE(seat),#唯一鍵    CONSTRAINT ck CHECK(gender='男' OR gender='女'),#檢查    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(majorid)#外鍵  );    #二、修改約束  /*  1、添加列級約束  alter table 表名 modify column 字段名 字段類型 新約束;  2、添加表級約束  alter table 表名 add 【constraint 約束名】 約束類型（字段名） 【外鍵的引用】;  */  #1、添加非空約束  ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;  #2、添加默認約束  ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;  #3、添加主鍵  #(1)列級約束  ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;  #(2)表級約束  ALTER TABLE stuinfo ADD PRIMARY KEY(id);  #4、添加唯一  #(1)列級約束  ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;  #(2)表級約束  ALTER TABLE stuinfo ADD UNIQUE(seat);  #5、添加外鍵  ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(majorid);    #三、修改表時刪除約束  #1、刪除非空約束  ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);  #2、刪除默認約束  ALTER TABLE stuinfo MODIFY COLUMN age INT;  #3、刪除主鍵  ALTER TABLE stuinfo DROP PRIMARY KEY;  #4、刪除唯一  ALTER TABLE stuinfo DROP INDEX seat;  #5、刪除外鍵  ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

tips：在上面的案例中，我們將每種情況都列出了相應的案例。最主要的語法並沒有特別大的改變，主要是反覆修改相應的約束關鍵字。在列級修改和表級修改中，主要在於約束類型的限制。