MySQL學習筆記（3,4）——資料庫和資料表的基本操作

文章目錄

• 3. 資料庫的基本操作
• • 3.1 資料庫的建立
  • 3.2 資料庫的删除
• 4. 資料表的基本操作
• • 4.1 安裝Navicat并連接配接MySQL
  • 4.2 建立資料表
  • • 4.2.1 建立表的文法形式
    • 4.1.2 使用主鍵限制
    • 4.1.3 使用外鍵限制
    • 4.1.4 使用非空限制
    • 4.1.5 使用唯一性限制
    • 4.1.6 使用預設限制
    • 4.1.7 設定表的屬性自動加一
  • 4.2 檢視資料表的結構
  • • 4.2.1 檢視表的基本結構語句
    • 4.2.2 檢視表的詳細結構語句
  • 4.3 修改資料表
  • • 4.3.1 修改表名
    • 4.3.2 修改字段的資料類型
    • 4.3.3 修改字段名
    • 4.3.4 添加字段
    • 4.3.5 删除字段
    • 4.3.6 修改字段的排列位置
    • 4.3.7 更改表的存儲結構
    • 4.3.8 删除表的外鍵限制
  • 4.4 删除資料表
  • • 4.4.1 删除沒有被關聯的表
    • 4.4.2 删除被其他表關聯的主表
  • 4.5 綜合案例——資料表的基本操作

3. 資料庫的基本操作

3.1 資料庫的建立

1. SHOW DATABASES

   : 檢視目前所有存在的資料庫

   

資料庫清單中包含了6個資料庫，MySQL是必需的，它描述使用者通路權限，使用者經常利用test資料庫做測試的工作

1. CREATE DATABASE database_name

   “database_name”為要建立的資料庫的名稱，該名稱不能與已經存在的資料庫重名。

建立資料庫是在系統磁盤上劃分一塊區域用于資料的存儲和管理，如果管理者在設定權限的時候為使用者建立了資料庫，則可以直接使用，否則需要自己建立資料庫

3.2 資料庫的删除

删除資料庫是将已經存在的資料庫從磁盤空間上清除，清除之後，資料庫中的所有資料也将一同被删除。

1. DROP DATABASE database_name;

   删除資料庫是将已經存在的資料庫從磁盤空間上清除，清除之後，資料庫中的所有資料也将一同被删除。

1. SHOW CREATE DATABASES test_db;

   執行結果給出一條錯誤資訊“ERROR 1049(42000)：Unknown database ‘test_db’”，即資料庫test_db已不存在，說明删除成功。

   

4. 資料表的基本操作

4.1 安裝Navicat并連接配接MySQL

為了友善運作sql代碼，我們可以下載下傳Navicat（強推哦~！）

Navicat MySQL是一個強大的MySQL資料庫伺服器管理和開發工具。它可以與任何3.21或以上版本的MySQL一起工作，支援觸發器、存儲過程、函數、事件、視圖、管理使用者等

1. Navicat官網下載下傳位址,然後傻瓜式安裝即可
2. 連接配接MySQL

   

   

4.2 建立資料表

4.2.1 建立表的文法形式

建立資料表，指的是在已經建立好的資料庫中建立新表。建立資料表的過程是規定資料列的屬性的過程，同時也是實施資料完整性（包括實體完整性、引用完整性和域完整性等）限制的過程。

在Navicat中輸入如下指令：

create database db; #建立資料庫
use db; # 選擇建立表的資料庫
create table tb
(
	id int(11),
	name varchar (25),
	deptId int(11),
	salary float
);
show tables;
           

點選運作後，出現如下界面

4.1.2 使用主鍵限制

主鍵，又稱主碼，是表中一列或多列的組合。

主鍵限制（Primary Key Constraint）要求主鍵列的資料唯一，并且不允許為空。

主鍵能夠唯一地辨別表中的一條記錄，可以結合外鍵來定義不同資料表之間的關系，并且可以加快資料庫查詢的速度

主鍵分為兩種類型：單字段主鍵和多字段聯合主鍵。

• 單字段主鍵

  主鍵由一個字段組成，SQL語句格式分為以下兩種情況

  1）在定義列的同時指定主鍵，文法規則如下：

  字段名 資料類型 PRIMARY KEY [預設值]

create table tb1
(
	id int(11) primary key,
	name varchar (25),
	deptId int(11),
	salary float
);
           

2）在定義完所有列之後指定主鍵,文法規則如下：

[CONSTRAINT <限制名>] PRIMARY KEY [字段名]

create table tb3
(
	id int(11) ,
	name varchar (25),
	deptId int(11),
	salary float,
	primary key(id)
);
           

• 多字段聯合主鍵

  主鍵由多個字段聯合組成，文法規則如下

  PRIMARY KEY [字段1，字段2，...字段n]

建立了一個名稱為tb_emp4的資料表，name字段和deptId字段組合在一起成為tb_emp4的多字段聯合主鍵。

create table tb4
(
	name varchar (25),
	deptId int(11),
	salary float,
	primary key(name,depId)
);
           

4.1.3 使用外鍵限制

外鍵用來在兩個表的資料之間建立連接配接，可以是一列或者多列。一個表可以有一個或多個外鍵。外鍵對應的是參照完整性，一個表的外鍵可以為空值，若不為空值，則每一個外鍵值必須等于另一個表中主鍵的某個值。

外鍵：首先它是表中的一個字段，雖可以不是本表的主鍵，但要對應另外一個表的主鍵。外鍵的主要作用是保證資料引用的完整性，定義外鍵後，不允許删除在另一個表中具有關聯關系的行

主表（父表）：對于兩個具有關聯關系的表而言，相關聯字段中主鍵所在的那個表即是主表。從表（子表）：對于兩個具有關聯關系的表而言，相關聯字段中外鍵所在的那個表即是從表。

建立外鍵的文法規則：

[CONSTRAINT<外鍵名>] FOREIGN KEY 字段名1 [，字段名2，...]

REFERENCES <主表名> 主鍵列1 [, 主鍵列2，...]

“外鍵名”為定義的外鍵限制的名稱，一個表中不能有相同名稱的外鍵；“字段名”表示子表需要添加外鍵限制的字段列；“主表名”即被子表外鍵所依賴的表的名稱；“主鍵列”表示主表中定義的主鍵列，或者列組合

use db;
create table tb6(
	id int (11) primary key,
	name varchar(22) not null,
	location varchar(50)
);
create table tb7
(
	id int(11) primary key,
	name varchar(25),
	deptId int(11),
	salary float,
	constraint fk_emp_dept1 FOREIGN key(deptId) references tb6(id)
);
           

4.1.4 使用非空限制

非空限制（Not Null Constraint）指字段的值不能為空。對于使用了非空限制的字段，如果使用者在添加資料時沒有指定值，資料庫系統會報錯。非空限制的文法規則如下;

字段名 資料類型 not null

4.1.5 使用唯一性限制

唯一性限制（Unique Constraint）要求該列唯一，允許為空，但隻能出現一個空值。唯一限制可以確定一列或者幾列不出現重複值

唯一性限制的文法規則如下：

（1）在定義完列之後直接指定唯一限制：

字段名 資料類型 UNIQUE

（2）在定義完所有列之後指定唯一限制:

[CONSTRAINT<限制名>] UNIQUE(<字段名>)

UNIQUE和PRIMARY KEY的差別：一個表中可以有多個字段聲明為UNIQUE，但隻能有一個PRIMARY KEY聲明；聲明為PRIMAY KEY的列不允許有空值，但是聲明為UNIQUE的字段允許空值（NULL）的存在

4.1.6 使用預設限制

預設限制（Default Constraint）指定某列的預設值。

預設限制的文法規則：

字段名 資料類型 DEFAULT 預設值

以上語句執行成功之後，表tb_emp7上的字段deptId擁有了一個預設的值1111，新插入的記錄如果沒有指定部門編号，則預設都為1111。

4.1.7 設定表的屬性自動加一

在資料庫應用中，經常希望在每次插入新記錄時，系統自動生成字段的主鍵值。可以通過為表主鍵添加AUTO_INCREMENT關鍵字來實作。

預設的，在MySQL中AUTO_INCREMENT的初始值是1，每新增一條記錄，字段值自動加1。一個表隻能有一個字段使用AUTO_INCREMENT限制，且該字段必須為主鍵的一部分AUTO_INCREMENT限制的字段可以是任何整數類型（TINYINT、SMALLIN、INT、BIGINT等）。

設定表的屬性值自動增加的文法規則：

字段名 資料類型 AUTO_INCREMENT

CREATE TABLE tb8
(
 id INT(11) PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(25) NOT NULL,
 deptId INT(11),
 salary FLOAT
);

INSERT INTO tb8(name,salary);
VALUES('Lucy',1000),('Lura',1200),('Kevin',1500);

SELECT*FROM tb8;
           

4.2 檢視資料表的結構

4.2.1 檢視表的基本結構語句

DESCRIBE/DESC語句可以檢視表的字段資訊，其中包括字段名、字段資料類型、是否為主鍵、是否有預設值等。文法規則如下：

DESCRIBE 表名;

或者簡寫為

DESC 表名;

各個字段的含義分别解釋如下:

NULL：表示該列是否可以存儲NULL值。

Key：表示該列是否已編制索引。PRI表示該列是表主鍵的一部分；UNI表示該列是UNIQUE索引的一部分；MUL表示在列中某個給定值允許出現多次。

Default：表示該列是否有預設值，有的話指定值是多少。

Extra：表示可以擷取的與給定列有關的附加資訊，例如AUTO_INCREMENT等

4.2.2 檢視表的詳細結構語句

SHOW CREATE TABLE語句可以用來顯示建立表時的CREATE TABLE語句，文法格式如下：

SHOW CREATE TABLE <表名\G>

使用SHOW CREATE TABLE語句，不僅可以檢視表建立時候的詳細語句，還可以檢視存儲引擎和字元編碼

4.3 修改資料表

4.3.1 修改表名

MySQL是通過ALTER TABLE語句來實作表名的修改的，具體的文法規則如下：

ALTER TABLE<舊表名> RENAME [TO] <新表名>;

其中，TO為可選參數，使用與否均不影響結果。

ALTER TABLE tb_dept1 RENAME TB9;
SHOW TABLES;
           

修改表名并不修改表的結構，是以修改名稱後的表和修改名稱前的表的結構必然是相同的。

4.3.2 修改字段的資料類型

修改字段的資料類型，就是把字段的資料類型轉換成另一種資料類型。在MySQL中修改字段資料類型的文法規則如下：

ALTER TABLE <表名> MODIFY <字段名> <資料類型>

其中，“表名”指要修改資料類型的字段所在表的名稱，“字段名”指需要修改的字段，“資料類型”指修改後字段的新資料類型。

ALTER TABLE tb1 MODIFY name VARCHAR(30);
DESC tb1;
           

4.3.3 修改字段名

MySQL中修改表字段名的文法規則如下：

ALTER TABLE <表名> CHANGE <舊字段名> <新字段名> <新資料類型>

其中，“舊字段名”指修改前的字段名；“新字段名”指修改後的字段名；“新資料類型”指修改後的資料類型，如果不需要修改字段的資料類型，将新資料類型設定成與原來一樣即可，但資料類型不能為空。

ALTER TABLE tb1 CHANGE salary loc VARCHAR(50);
DESC tb1;
           

CHANGE也可以隻修改資料類型，實作和MODIFY同樣的效果，方法是将SQL語句中的“新字段名”和“舊字段名”設定為相同的名稱，隻改變“資料類型”。由于不同類型的資料在機器中存儲的方式及長度并不相同，修改資料類型可能會影響到資料表中已有的資料記錄，是以當資料庫表中已經有資料時，不要輕易修改資料類型

4.3.4 添加字段

一個完整字段包括字段名、資料類型、完整性限制,添加字段的文法格式如下：

ALTER TABLE <表名> ADD <新字段名><資料類型> [限制條件] [FIRST |AFTER 已存在的字段名]

新字段名為需要添加的字段的名稱；“FIRST”為可選參數，其作用是将新添加的字段設定為表的第一個字段；“AFTER”為可選參數，其作用是将新添加的字段添加到指定的“已存在字段名”的後面。

“FIRST”或“AFTER已存在字段名”用于指定新增字段在表中的位置，如果SQL語句中沒有這兩個參數，則預設将新添加的字段設定為資料表的最後列

• 添加無完整性限制條件的字段

  在資料表tb1中添加一個沒有完整性限制的INT類型的字段managerId（部門經理編号）

ALTER TABLE tb1 ADD managerId INT(10);
DESC tb1;
           

• 添加有完整性限制條件的字段

  資料表tb1中添加一個不能為空的VARCHAR(12)類型的字段column1

ALTER TABLE tb1 ADD column1 VARCHAR(12) NOT NULL ;
DESC tb1;
           

• 在表的第一列添加一個字段

  在資料表tb_dept1中添加一個INT(11)類型的字段column2

ALTER TABLE tb1 ADD column2 INT(11) FIRST;
DESC tb1;
           

• 在表的指定列之後添加一個字段

  在資料表tb_dept1中name列後添加一個INT(11)類型的字段column3

ALTER TABLE tb1 ADD column3 INT(11) AFTER name;
DESC tb1;
           

4.3.5 删除字段

删除字段是将資料表中的某個字段從表中移除，文法格式如下：

ALTER TABLE <表名> DROP<字段名>

删除資料表tb1表中的column2字段

ALTER TABLE tb1 DROP column2;
DESC tb1;
           

4.3.6 修改字段的排列位置

對于一個資料表來說，在建立的時候，字段在表中的排列順序就已經确定了，但表的結構并不是完全不可以改變的，可以通過ALTER TABLE來改變表中字段的相對位置。文法格式如下：

ALTER TABLE <表名> MODIFY <字段1><資料類型> FIRST|AFTER <字段2>；

其中，“字段1”指要修改位置的字段；“資料類型”指“字段1”的資料類型；“FIRST”為可選參數，指将“字段1”修改為表的第一個字段；“AFTER字段2”指将“字段1”插入到“字段2”後面。

• 修改字段為表的第一個字段

  将資料表tb_deptl中的column1字段修改為表的第一個字段

ALTER TABLE tb1 MODIFY column1 VARCHAR(12) FIRST;
DESC tb1;
           

• 修改字段到表的指定列後
• 将資料表tb_dept1中的column1字段插入到name字段後面

ALTER TABLE tb1 MODIFY column1 VARCHAR(12) AFTER name;
DESC tb1;
           

4.3.7 更改表的存儲結構

存儲引擎是MySQL中的資料存儲在檔案或者記憶體中時采用的不同技術實作。可以根據自己的需要，選擇不同的引擎，甚至可以為每一張表選擇不同的存儲引擎。MySQL中主要的存儲引擎有MyISAM、InnoDB、MEMORY（HEAP）、BDB、FEDERATED等。

可以使用

SHOW ENGINES;

語句檢視系統支援的存儲引擎。

更改表的存儲引擎的文法格式如下：

ALTER TABLE <表名> ENGINE=<更改後的存儲引擎名>；

将資料表tb_deptment3的存儲引擎修改為MyISAM

SHOW CREATE TABLE tb3 \G;
ALTER TABLE tb3 ENGINE= MyISAM;
           

4.3.8 删除表的外鍵限制

對于資料庫中定義的外鍵，如果不再需要，可以将其删除。外鍵一旦删除，就會解除主表和從表間的關聯關系，MySQL中删除外鍵的文法格式如下：

ALTER TABLE <表名> DROP FOREING KEY <外鍵限制名>

“外鍵限制名”指在定義表時CONSTRAINT關鍵字後面的參數

create table tb7
(
	id int(11) primary key,
	name varchar(25),
	deptId int(11),
	salary float,
	constraint fk_emp_dept1 FOREIGN key(deptId) references tb6(id)
);
ALTER TABLE tb7 DROP FOREIGN KEY fk_emp_dept1;
SHOW CREATE TABLE tb7 \G;
           

4.4 删除資料表

4.4.1 删除沒有被關聯的表

在MySQL中，使用DROP TABLE可以一次删除一個或多個沒有被其他表關聯的資料表。文法格式如下：

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

其中，“表n”指要删除的表的名稱，後面可以同時删除多個表，隻需将要删除的表名依次寫在後面，互相之間用逗号隔開即可。如果要删除的資料表不存在，則MySQL會提示一條錯誤資訊，“ERROR 1051 (42S02): Unknown table ‘表名’”。參數“IF EXISTS”用于在删除前判斷删除的表是否存在，加上該參數後，再删除表的時候，如果表不存在，SQL語句可以順利執行，但是會發出警告（warning）。

DROP TABLE IF EXISTS tb2;
SHOW TABLES;
           

4.4.2 删除被其他表關聯的主表

在資料表之間存在外鍵關聯的情況下，如果直接删除父表，結果會顯示失敗，原因是直接删除将破壞表的參照完整性。如果必須要删除，可以先删除與它關聯的子表，再删除父表，隻是這樣就同時删除了兩個表中的資料。有的情況下可能要保留子表，這時若要單獨删除父表，隻需将關聯的表的外鍵限制條件取消，然後就可以删除父表了。

解除關聯子表tb_emp的外鍵限制

create table tb_dept2
(
	id int(11) primary key,
	name varchar(22),
	location varchar(50)
)

create table tb_emp
(
	id int(11) primary key,
	name varchar(25),
	depId int(11),
	salary float,
	constraint fk_emp_dept foreign key (depId) references tb_dept2(id)
)
           

直接删除父表tb_dept2, 會報錯

解除關聯子表tb_emp的外鍵限制, 然後再删除

ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;
DROP TABLE tb_dept2;
           

4.5 綜合案例——資料表的基本操作

1. 登入資料庫

   【法1】打開MySQL 8.0 Command Line Client，隻輸入使用者密碼也可以登入。登入成功後顯示如下資訊：

   

   【法2】打開MySQL 8.0 Command Line Client，隻輸入使用者密碼也可以登入。登入成功後顯示如下資訊：

   

2. 建立資料庫company

結果顯示建立成功，在company資料庫中建立表，必須先選擇該資料庫

為了輸入指令友善，以下示範如何使用Navicat for mysql

首先連結資料庫

然後建立新的Query

點選New Query , 然後就可以在編輯器中輸入指令了。

1. 建立表offices

create　table offices
(
	officeCode int(10) not null unique,
	city varchar(50) not null,
	address varchar(50) not null,
	country varchar(50) not null,
	postalCode varchar(15) not null,
	primary key(officeCode)
)
show tables;
           

1. 建立表employees

create table employees
(
	employeeNumber int(11) not null primary key auto_increment,
	lastName varchar(50) not null,
	firstName varchar(50) not null,
	mobile varchar(25) not null,
	officeCode int(10) not null,
	jobTitle varchar(50) not null,
	birth DATETIME,
	note varchar(255),
	sex varchar(5),
	constraint office_fk foreign key(officeCode) references offices(officeCode)
);
           

desc offices;
desc employees;
           

1. 将表employees的mobile字段修改到officeCode字段後面

alter table employees modify mobile varchar(25) after officeCode;
desc employees;
           

可以看到，mobile字段已經插入到officeCode字段的後面。

1. 将employees 的birth 字段改名為employ_birth

alter table employee change birth employee_birth datetime;
desc employees;
           

1. 修改sex 字段，資料類型為char(1),非空限制

alter table employees modify sex char(1) not null;
desc employees;
           

1. 删除字段note

alter table employees drop note;
desc employees;
           

DESC語句傳回了8個列字段，note字段已經不在表結構中，删除字段成功

1. 增加字段名 favorite_activity, 資料類型為varchar(100).

alter table employees add favorite_activity varchar(100);
desc employees;
           

可以看到，資料表employees中增加了一個新的列favoriate_activity，資料類型為VARCHAR(100)，允許空值，添加新字段成功

1. 删除表offices

在建立表employees時，設定了表的外鍵，該表關聯了其父表offices的officeCode主鍵。如前面所述，删除關聯表時，要先删除子表employees的外鍵限制，才能删除父表。是以，必須先删除employees表的外鍵限制。

（1）删除employees表的外鍵限制，輸入如下語句

（2）删除表offices，輸入如下語句：

結果顯示執行删除操作成功，使用“SHOWTABLES;”語句檢視資料庫中的表，結果如下：

可以看到，資料庫中已經沒有名稱為offices的表了，删除表成功

1. 修改表employees存儲引擎為MyISAM。修改表存儲引擎，需要用到ALTER TABLE語句，輸入語句如下：

結果顯示執行修改存儲引擎操作成功，使用SHOW CREATE TABLE語句檢視表結構，結果如下：

可以看到，倒數第2行中的ENGINE後面的參數已經修改為MyISAM，修改成功。

1. 将表employees名稱修改為employees_info

   修改資料表名，需要用到ALTER TABLE語句，輸入語句如下

alter table employees rename employees_info;
show tables;