一、基本概念

1、從系統(tǒng)架構(gòu)的層面來(lái)看，數(shù)據(jù)庫(kù)從大到小依次是數(shù)據(jù)庫(kù)服務(wù)器(上面安裝了DBMS和數(shù)據(jù)庫(kù))、數(shù)據(jù)庫(kù)(也稱(chēng)database或者schema)、數(shù)據(jù)表、數(shù)據(jù)表的行與列

二、創(chuàng)建和管理數(shù)據(jù)庫(kù)

1、創(chuàng)建數(shù)據(jù)庫(kù)

• 直接創(chuàng)建：CREATE DATABASE 數(shù)據(jù)庫(kù)名;(使用默認(rèn)的字符集)
• 創(chuàng)建時(shí)指明字符集：CREATE DATABASE 數(shù)據(jù)庫(kù)名 CHARACTER SET 'gbk';
• 創(chuàng)建時(shí)判斷數(shù)據(jù)庫(kù)是否已存在，如果不存在則創(chuàng)建：CREATE DATABASE IF NOT EXISTS 數(shù)據(jù)庫(kù)名 CHARACTER SET 'utf8';，推薦使用此種方式

2、管理數(shù)據(jù)庫(kù)

下面是常見(jiàn)的數(shù)據(jù)庫(kù)管理SQL

# 查看當(dāng)前連接中都有哪些數(shù)據(jù)庫(kù)
SHOW DATABASES;
# 指定使用哪個(gè)數(shù)據(jù)庫(kù)或者切換數(shù)據(jù)庫(kù)
USE 數(shù)據(jù)庫(kù)名;
# 查看當(dāng)前數(shù)據(jù)庫(kù)中保存的數(shù)據(jù)表
SHOW TABLES;
# 查看當(dāng)時(shí)使用的數(shù)據(jù)庫(kù)
SELECT DATABASE() FROM DUAL;
# 查看指定數(shù)據(jù)庫(kù)下存在哪些表
SHOW TABLES FROM 數(shù)據(jù)庫(kù)名;

3、修改數(shù)據(jù)庫(kù)

一般情況下不建議做此操作，多發(fā)生在數(shù)據(jù)庫(kù)剛創(chuàng)建出來(lái)時(shí)

數(shù)據(jù)庫(kù)名一般不可以修改，某些可視化工具可以修改數(shù)據(jù)庫(kù)名，實(shí)際上它是新創(chuàng)建了一個(gè)數(shù)據(jù)庫(kù)，然后把舊數(shù)據(jù)庫(kù)的數(shù)據(jù)復(fù)制過(guò)去，再刪除舊庫(kù)

# 修改數(shù)據(jù)庫(kù)字符集
ALTER DATABASE 數(shù)據(jù)庫(kù)名 CHARACTER SET 字符集;

4、刪除數(shù)據(jù)庫(kù)

• 直接刪除：DROP DATABASE 數(shù)據(jù)庫(kù)名;
• 刪除前先判斷，如果存在則刪除：DROP DATABASE IF EXISTS 數(shù)據(jù)庫(kù)名;，推薦使用此方式

三、創(chuàng)建和管理表

1、創(chuàng)建表

1）常見(jiàn)數(shù)據(jù)類(lèi)型

整數(shù)類(lèi)型：TINYINT、SMALLINT、MEDIUMINT、INT(或者INTEGER)、BIGINT

浮點(diǎn)類(lèi)型：FLOAT、DOUBLE

定點(diǎn)數(shù)類(lèi)型：DECIMAL

位類(lèi)型：BIT

日期時(shí)間類(lèi)型：YEAR、TIME、DATE、DATETIME、TIMESTAMP

文本字符串類(lèi)型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

枚舉類(lèi)型：ENUM

集合類(lèi)型：SET

二進(jìn)制字符串類(lèi)型：BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

JSON類(lèi)型：JSON對(duì)象、JSON數(shù)組

空間數(shù)據(jù)類(lèi)型

• 單值：GEOMETRY、POINT、LINESTRING、POLYGON
• 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

2）創(chuàng)建表

方式一：從零開(kāi)始創(chuàng)建

# 使用VARCHAR定義字段必須指明其長(zhǎng)度，如果創(chuàng)建表時(shí)沒(méi)有指明字符集，那么使用所在數(shù)據(jù)庫(kù)默認(rèn)的字符集
CREATE TABLE IF NOT EXISTS 表名(
id INT [約束條件] [默認(rèn)值],
emp_name VARCHAR(15) [約束條件] [默認(rèn)值],
birth DATE [約束條件] [默認(rèn)值]
) ENGINE=InnoDB DEFAULT CHARSET = utf8mb3;
# 查看表結(jié)構(gòu)
DESC 表名;
# 查看建表語(yǔ)句
SHOW CREATE TABLE 表名;

方式二：基于現(xiàn)有的表創(chuàng)建新表

新表的數(shù)據(jù)、結(jié)構(gòu)都與SELECT語(yǔ)句的結(jié)果集相同

如果SELECT語(yǔ)句使用了別名，那么新表中的字段就使用別名

CREATE TABLE 新表名
AS
SELECT id,emp_name,birth
FROM t_decade_employee;

建表完成之后，不管是新表還是舊表發(fā)生變化，他們都不會(huì)互相影響

2、修改表

1）追加一個(gè)字段

語(yǔ)法格式為

ALTER TABLE 表名 ADD 字段名 字段類(lèi)型 [FIRST|AFTER 字段名];

我們追加如下四個(gè)字段，看看FIRST、AFTER關(guān)鍵字的效果

ALTER TABLE t_decade_user ADD hobby VARCHAR(10);
ALTER TABLE t_decade_user ADD company_name VARCHAR(10) FIRST;
ALTER TABLE t_decade_user ADD salary VARCHAR(10) AFTER sex;
ALTER TABLE t_decade_user ADD address VARCHAR(10) AFTER name;

執(zhí)行后，結(jié)果如下

2）修改字段的長(zhǎng)度、數(shù)據(jù)類(lèi)型、默認(rèn)值

語(yǔ)法格式為

ALTER TABLE 表名 MODIFY 字段類(lèi)型 [DEFAULT 默認(rèn)值];

例如修改上面新加的兩個(gè)字段的長(zhǎng)度和默認(rèn)值，數(shù)據(jù)類(lèi)型一般確定后就不會(huì)改變了

ALTER TABLE t_decade_user MODIFY salary VARCHAR(15);
ALTER TABLE t_decade_user MODIFY address VARCHAR(25) DEFAULT 'CHINA';
DESC t_decade_user;

3）重命名一個(gè)字段

語(yǔ)法格式為

ALTER TABLE 表名
CHANGE 舊字段名 新字段名 字段類(lèi)型;

我們這里測(cè)試將薪酬salary的名字和字段類(lèi)型進(jìn)行調(diào)整

ALTER TABLE t_decade_user CHANGE salary month_salary DOUBLE(10,2);
DESC t_decade_user;

結(jié)果如下

4）刪除一個(gè)字段

語(yǔ)法格式為

ALTER TABLE 表名DROP COLUMN 要?jiǎng)h除的字段;

現(xiàn)在我們將剛剛添加的那些字段全部刪除

ALTER TABLE t_decade_user DROP COLUMN company_name;
ALTER TABLE t_decade_user DROP COLUMN month_salary;
ALTER TABLE t_decade_user DROP COLUMN hobby;
ALTER TABLE t_decade_user DROP COLUMN address;
DESC t_decade_user;

結(jié)果如下

3、重命名表

方式一：RENAME

RENAME TABLE 舊的表名 TO 新的表名;

方式二：ALTER，這里的TO可以省略

ALTER TABLE 舊的表名 RENAME [TO] 新的表名;

4、刪除表

刪除表結(jié)構(gòu)的同時(shí)，也會(huì)刪除表中數(shù)據(jù)，釋放表的存儲(chǔ)空間

DROP TABLE [IF EXISTS] 表名1,表名2...

注意：刪除表之后，無(wú)法回滾，除非有備份等操作

5、清空表

清空表只會(huì)清空數(shù)據(jù)，表的結(jié)構(gòu)會(huì)保存下來(lái)

TRUNCATE TABLE 表名

刪除表中所有數(shù)據(jù)

釋放表的存儲(chǔ)空間

對(duì)比TRUNCATE TABLE和DELETE FROM對(duì)比

• 相同點(diǎn)：都可以實(shí)現(xiàn)對(duì)表中所有數(shù)據(jù)的刪除，同時(shí)保留表結(jié)構(gòu)
• 不同點(diǎn)：TRUNCATE 語(yǔ)句刪除數(shù)據(jù)無(wú)法回滾，而使用DELETE語(yǔ)句刪除數(shù)據(jù)可以回滾

DDL和DML的區(qū)別

• DDL操作一旦執(zhí)行，就不可回滾，因?yàn)樗皇?code>SET autocommit = FALSE的影響
• DML的操作一旦執(zhí)行，默認(rèn)情況下不可回滾，但是，如果在執(zhí)行DML之前，執(zhí)行了SET autocommit = FALSE，執(zhí)行的DML操作就可以回滾

我們拿某個(gè)表執(zhí)行一次DELETE FROM 和 TRUNCATE TABLE進(jìn)行比較

COMMIT;
SELECT * FROM t_decade_user;
SET autocommit = false;
# 如果執(zhí)行TRUNCATE TABLE t_decade_user;那么數(shù)據(jù)就無(wú)法回滾到最近一次commit
DELETE FROM t_decade_user;
SELECT * FROM t_decade_user;
ROLLBACK;
SELECT * FROM t_decade_user;

發(fā)現(xiàn)只有DELETE FROM可以回滾

四、DCL中的COMMIT和ROLLBACK

1、commit

commit指提交數(shù)據(jù)，一旦執(zhí)行commit，則數(shù)據(jù)就被永久的保存在數(shù)據(jù)庫(kù)中，意味著數(shù)據(jù)不可以回滾

2、rollback

rollback指回滾數(shù)據(jù)，一旦執(zhí)行rollback，就可以實(shí)現(xiàn)數(shù)據(jù)的回滾，回滾到最近一次commit之后的狀態(tài)

五、MySQL8.0中DDL的原子化

MySQL8.0在DDL方面新增了一個(gè)原子化的新特性：DDL操作要么成功要么回滾

假設(shè)數(shù)據(jù)庫(kù)中只存在一個(gè)表t_decade_user

我們同時(shí)在MySQL5.7和MySQL8.0中執(zhí)行DROP語(yǔ)句

DROP TABLE t_decade_user,t_decade_employee;

它們都會(huì)報(bào)錯(cuò)，但是MySQL5.7會(huì)成功刪除掉t_decade_user

而MySQL8.0不會(huì)影響到t_decade_user

到此這篇關(guān)于DDL數(shù)據(jù)庫(kù)與表的創(chuàng)建和管理深入講解使用教程的文章就介紹到這了,更多相關(guān)DDL數(shù)據(jù)庫(kù)與表的創(chuàng)建和管理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論