資料庫的結構

所謂資料庫的具體結構層級如下:
- DBMS資料庫管理系統 > database資料庫 > table表 > column欄位
創建database、table、column都需要自己命名

命名規則

庫名、表名不得超過30字符；變量限制為29字符
只能包含大小寫英數字與_下劃線
不可包含空格
同一DBM中庫不可重名，同一庫中表不可重名，同一表中字段不可重名
不可與關鍵字、保留字等衝突
命名與資料類型須保持一致性
- 常用的幾類類型:

資料類型	描述
INT	從-2^31到2^31-1的整型數據。存儲大小為 4個位元組
CHAR(size)	定長字元資料。若未指定，預設為1個字元，最大長度255
VARCHAR(size)	可變長字元資料，根據字串實際長度保存，必須指定長度
FLOAT(M,D)	單精確度，佔用4個位元組，M=整數位元+小數位，D=小數位。 D<=M<=255,0<=D<=30，預設M+D<=6
DOUBLE(M,D)	雙精度，佔用8個位元組，D<=M<=255,0<=D<=30，預設M+D<=15
DECIMAL(M,D)	高精度小數，佔用M+2個位元組，D<=M<=65，0<=D<=30，最大取值範圍與DOUBLE相同。
DATE	日期型資料，格式’YYYY-MM-DD'
BLOB	二進位形式的長文本資料，最大可達4G
TEXT	長文本資料，最大可達4G

database庫的指令

必須具備相應權限

創建資料庫

create database if not exists test_database_01 character set 'utf8mb4';

用if not exists避免報錯，指定character set避免5.7版本的用了奇奇怪怪的編碼

刪改資料庫

alter database char set 'big5';

drop database if exists test_database_01;

管理資料庫

查看當前所有的資料庫

SHOW DATABASES; #有一個S，代表多個資料庫

查看當前正在使用的資料庫

SELECT DATABASE();  #使用的一個 mysql 中的全域函數

查看指定庫下所有的表

SHOW TABLES FROM 資料庫名;

查看資料庫的創建資訊

SHOW CREATE DATABASE 資料庫名;

使用/切換資料庫

USE 資料庫名;

注意要操作表格和資料之前必須先說明是對哪個資料庫進行操作，否則就要對所有物件加上"資料庫名."

table表的指令

基於某個database，用[]中括號表示為可選填項目

指令的格式基本都是 欲執行的操作 table 表名

創建表

完全新創

CREATE TABLE IF NOT EXISTS 表名(
	欄位1, 資料類型 [約束條件] [預設值],
	欄位2, 資料類型 [約束條件] [預設值],
	欄位3, 資料類型 [約束條件] [預設值],
	……
	[表約束條件]
);

範例

create table student(
    id int auto_increment,
    name varchar(20),
    birthday date,
    primary key (id)
);

desc student;

基於現有的表

基於某張表的篩選結果，創建同時導入資料

CREATE TABLE emp1 AS SELECT * FROM employees;

範例

-- 從employees表篩出部門50的資料，指定成為一張新表
create table dept50
as
select employee_id,last_name,salary*12 "year_salary",hire_date
from employees
where department_id=50;

查看表中欄位屬性

desc tablename;
-- 更詳細的包含編碼資料等
show full columns from tablename;

重命名表

方式一：使用RENAME

RENAME TABLE emp
TO myemp;

方式二：

ALTER table dept
RENAME [TO] detail_dept;  -- [TO]可以省略

必須是物件的擁有者

刪除表

在MySQL中，當一張資料表沒有與其他任何資料表形成關聯關係時，可以將當前資料表直接刪除
資料和結構都被刪除
所有正在運行的相關事務被提交
所有相關索引被刪除
語法格式

DROP TABLE [IF EXISTS] 資料表1 [, 資料表2, …, 資料表n];

IF EXISTS的含義為：如果當前資料庫中存在相應的資料表，則刪除資料表；如果當前資料庫中不存在相應的資料表，則忽略刪除語句，不再執行刪除資料表的操作。

舉例：

DROP TABLE dept80;

DROP TABLE 語句不能回滾

清空表

TRUNCATE TABLE語句：
- 刪除表中所有的資料
- 釋放表的存儲空間
舉例：

TRUNCATE TABLE detail_dept;

TRUNCATE語句不能回滾，而使用 DELETE 語句刪除資料，可以回滾
對比：

SET autocommit = FALSE;
-- 先把自動提交(存檔)關閉，因為ROLLBACK只能回復一步
-- 如果DELETE FROM=ture，相當於DELETE FROM完馬上自動存檔，GG

DELETE FROM emp2;
#TRUNCATE TABLE emp2;

SELECT * FROM emp2;

ROLLBACK;

SELECT * FROM emp2;

【參考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日誌資源少，但 TRUNCATE 無事務且不觸發 TRIGGER，有可能造成事故，故不建議在開發代碼中使用此語句。

說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同

column欄位(列)的指令

column=欄位、直的列、字段

基於某張表，格式都是alter table 表名操作欄位名

插入新欄位

alter table 表名 add 欄位名 資料類型 [預設值] [指定位置];

範例

alter table dept50
add job_id varchar(15) after last_name;

修改欄位屬性

alter table 表名 modify 欄位名 資料類型 [預設值] [指定位置];

範例

alter table dept50 modify job_id varchar(20) default 'cleaner' after employee_id;

對預設值的修改不涉及既往
也可以透過此方式修改約束

欄位改名

使用 CHANGE old_column new_column dataType子句重命名列。語法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新資料類型;

舉例：

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15);

注意修改表名是用rename table ...
而修改欄位名是alter table ... change ...且還要指定欄位的資料型態

刪除一個列

刪除表中某個欄位的語法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】欄位名

舉例：

ALTER TABLE  dept80
DROP COLUMN  job_id;

延伸知識

阿裡巴巴《Java開發手冊》之MySQL欄位命名

【強制】表名、欄位名必須使用小寫字母或數位，禁止出現數位開頭，禁止兩個底線中間只出現數字。資料庫欄位名的修改代價很大，因為無法進行預發佈，所以欄位名稱需要慎重考慮。
- 正例：aliyun_admin，rdc_config，level3_name
- 反例：AliyunAdmin，rdcConfig，level_3_name
【強制】禁用保留字，如 desc、range、match、delayed 等，請參考 MySQL 官方保留字。
【強制】表必備三欄位：id, gmt_create, gmt_modified。
- 說明：其中 id 必為主鍵，類型為BIGINT UNSIGNED、單表時自增、步長為 1。gmt_create, gmt_modified 的類型均為 DATETIME 類型，前者現在時表示主動式創建，後者過去分詞表示被動式更新
【推薦】表的命名最好是遵循 “業務名稱_表的作用”。
- 正例：alipay_task 、 force_project、 trade_config
【推薦】庫名與應用名稱儘量一致。
【參考】合適的字元存儲長度，不但節約資料庫表空間、節約索引存儲，更重要的是提升檢索速度。
- 正例：無符號值可以避免誤存負數，且擴大了表示範圍。

如何理解清空表、刪除表等操作需謹慎？！

表刪除操作將把表的定義和表中的資料一起刪除，並且MySQL在執行刪除操作時，不會有任何的確認資訊提示，因此執行刪除操時應當慎重。在刪除表前，最好對表中的資料進行備份，這樣當操作失誤時可以對資料進行恢復，以免造成無法挽回的後果。

同樣的，在使用 ALTER TABLE 進行表的基本修改操作時，在執行操作過程之前，也應該確保對資料進行完整的備份，因為資料庫的改變是無法撤銷的，如果添加了一個不需要的欄位，可以將其刪除；相同的，如果刪除了一個需要的列，該列下面的所有資料都將會丟失。

MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支援事務完整性，即DDL操作要麼成功要麼回滾。DDL操作回滾日誌寫入到data dictionary資料字典表mysql.innodb_ddl_log（該表是隱藏的表，通過show tables無法看到）中，用於回滾操作。通過設置參數，可將DDL操作日誌列印輸出到MySQL錯誤日誌中。

課後練習

總共有3部分，練習熟悉相關語句的使用

#1. 創建資料庫test01_office,指明字元集為utf8。並在此資料庫下執行下述操作
create database if not exists test01_office char set 'utf8mb4';
use test01_office;

#2.	創建表dept01
/*
欄位      類型
id	 INT(7)
NAME	 VARCHAR(25)
*/
create table if not exists dept01(
    id int(7),
    name varchar(25)
);

#3.將表departments中的資料插入新表dept02中
create table if not exists dept02
as
select *
from atguigudb.departments; -- 用庫.表名就不用切換當前使用的庫

#4.	創建表emp01
/*
欄位            類型
id		INT(7)
first_name	VARCHAR (25)
last_name	VARCHAR(25)
dept_id		INT(7)
*/
select database();
create table if not exists emp01(
    id int(7),
    first_name varchar(25),
    last_name varchar(25),
    dept_id int(7)
);

#5.將列last_name的長度增加到50
alter table emp01 modify last_name varchar(50);
desc emp01;

#6.根據表employees創建emp02
create table if not exists emp02
as
select *
from atguigudb.employees;

show tables from test01_office;
desc emp02;
select *
from emp02;

#7.刪除表emp01
drop table if exists emp01;

#8.將表emp02重命名為emp01
alter table emp02 rename emp01;

#9.在表dept02和emp01中添加新列test_column，並檢查所作的操作
alter table dept02 add test_column varchar(5);
desc dept02;

#10.直接刪除表emp01中的列 department_id
alter table emp01 drop department_id;

#練習2：
# 1、創建資料庫 test02_market
create database if not exists test02_market char set 'utf8mb4';
use test02_market;

# 2、創建資料表 customers
create table if not exists customers(
    c_num int,
    c_name varchar(50),
    c_contact varchar(50),
    c_city varchar(50),
    c_birthday date
);

# 3、將 c_contact 欄位移動到 c_birth 欄位後面
alter table customers modify c_contact varchar(50) after c_birthday;
desc customers;

# 4、將 c_name 欄位資料類型改為 varchar(70)
alter table customers modify c_name varchar(70);

# 5、將c_contact欄位改名為c_phone
alter table customers change c_contact c_phone varchar(50);

# 6、增加c_gender欄位到c_name後面，資料類型為char(1)
alter table customers add c_gender char(1) after c_name;

# 7、將表名改為customers_info
rename table customers to customers_info;
desc customers_info;

# 8、刪除欄位c_city
alter table customers_info drop c_city;

#練習3：
# 1、創建資料庫test03_company
show create database if not exists test03_company;
use test03_company;
alter database char set 'utf8mb4';

# 2、創建表offices
create table if not exists offices(
officeCode INT,
city VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
);

# 3、創建表employees
CREATE TABLE IF NOT EXISTS employees(
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
);


# 4、將表employees的mobile欄位修改到code欄位後面
desc employees;
alter table employees modify mobile varchar(25) after code;

# 5、將表employees的birth欄位改名為birthday
alter table employees change birth birthday date;

# 6、修改sex欄位，資料類型為char(1)
alter table employees modify sex char(1);

# 7、刪除欄位note
alter table employees drop note;

# 8、增加欄位名favoriate_activity，資料類型為varchar(100)
alter table employees add favoriate_activity varchar(100);

# 9、將表employees的名稱修改為 employees_info
rename table employees to employees_info;

上次修改於 2021-12-21