MySQL資料庫管理

一、MySQL資料庫管理

1、庫和表

資料庫–>資料表–>行（記錄）：用來描述一個對象的資訊

_______________ 列（字段）：用來描述對象的一個屬性

2、常用的資料類型

int ：整型

float ：單精度浮點 4位元組32位

double ：雙精度浮點 8位元組64位

char ：固定長度的字元類型

varchar ：可變長度的字元類型

text ：文本

image ：圖檔

decimal(5,2) ：5個有效長度數字，小數點後面有2位

二、檢視資料庫結構

1、檢視目前伺服器中的資料庫

SHOW DATABASES; #大小寫不區分，分号“;”表示結束

2、檢視資料庫中包含的表

USE 資料庫名;

SHOW TABLES;

3、檢視表的結構（字段）

DESCRIBE [資料庫名.]表名;

可縮寫成：DESC 表名;

三、SQL語句

SQL語句用于維護管理資料庫，包括資料查詢、資料更新、通路控制、對象管理等功能

1、SQL語句分類：

語句	代表的意思
DDL	資料定義語言，用于建立資料庫對象，如庫、表、索引等
DML	資料操縱語言，用于對表中的資料進行管理
DQL	資料查詢語言，用于從資料表中查找符合條件的資料記錄
DCL	資料控制語言，用于設定或者更改資料庫使用者或角色權限

四、建立及删除資料庫和表

1、建立新的資料庫

CREATE DATABASE 資料庫名;

2、建立新的表

CREATE TABLE 表名 (字段1 資料類型,字段2 資料類型[,...][,PRIMARY KEY (主鍵名)]);

主鍵一般選擇能代表唯一性的字段不允許取空值（NULL），一個表隻能有一個主鍵。

例：

CREATE DATABASE bbc;
USE bbc;
CREATE TABLE ab (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));

DESC ab;

3、删除指定的資料表

DROP TABLE [資料庫名.]表名; #如不用USE進入庫中，則需加上資料庫名

4、删除指定的資料庫

DROP DATABASE 資料庫名;

五、管理表中的資料記錄

1、向資料表中插入新的資料記錄

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);

INSERT INTO ab(id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456'));
#PASSWORD('123456')：查詢資料記錄時，密碼字串以加密形式顯示；若不使用PASSWORD()，查詢時以明文顯示。

INSERT INTO ab VALUES(2,'lisi',90.5,654321);

SELECT * FROM ab; #查詢表的資料記錄

2、查詢資料記錄

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 條件表達式];

SELECT * FROM ab;
SELECT id,name,score FROM ab WHERE id=2;

select name from ab\G #以清單方式豎向顯示
select * from ab limit 2; #隻顯示頭2行
select * from ab limit 2,3; #顯示第2行後的前3行

2.1 查詢全部資料

2.2 查詢指定資料

2.3 以清單方式豎向顯示

2.4 顯示指定行内容

3、修改、更新資料表中的資料記錄

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 條件表達式];

UPDATE ab SET passwd=PASSWORD('') WHERE name='zhangsan';
UPDATE ab SET name='wangxiaoer',passwd='' WHERE id=3;

4、在資料表中删除指定的資料記錄

DELETE FROM 表名 [WHERE 條件表達式];

DELETE FROM ab WHERE id=4;

六、修改表名和表結構

1、修改表名

ALTER TABLE 舊表名 RENAME 新表名;

ALTER TABLE ab RENAME abc;
ALTER TABLE abc RENAME ab;

2、擴充表結構（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default '位址不詳';

#default '位址不詳'：表示此字段設定預設值位址不詳；可與 NOT NULL 配合使用

3、修改字段（列）名，添加唯一鍵

ALTER TABLE 表名 CHANGE 舊列名新列名資料類型 [unique key];

ALTER TABLE abc CHANGE name user_name varchar(10) unique key;
#CHANGE可修改字段名、資料類型、限制等所有項。

4、删除字段

ALTER TABLE 表名 drop 字段名;

ALTER TABLE abc drop address;

5、使用 if 判斷建立表并測試自增和填充

案例

use bbc;
create table if not exists info (
id int(4) zerofill primary key auto_increment,  #指定主鍵的第二種方式
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

分析：

#if not exists：表示檢測要建立的表是否已存在，如果不存在就繼續建立

#int(4) zerofill：表示若數值不滿4位數，則前面用“0”填充，例0001

#auto_increment：表示此字段為自增長字段，即每條記錄自動遞增1，預設從1開始遞增；自增長字段資料不可以重複；自增長字段必須是主鍵；如添加的記錄資料沒有指定此字段的值且添加失敗也會自動遞增一次

#unique key：表示此字段唯一鍵限制，此字段資料不可以重複；一張表中隻能有一個主鍵, 但是一張表中可以有多個唯一鍵

#not null：表示此字段不允許為NULL

測試：建立資料測試自動補齊和自增長

七、資料表進階操作

1．克隆表，将資料表的資料記錄生成到新的表中

方法一：先複制表再導入資料

create table test01 like bbc; #通過 LIKE 方法，複制 bbc 表結構生成 test01 表
insert into test01 select * from bbc;

方法二：複制表的同時導入資料

CREATE TABLE test02 (SELECT * from bbc);

show create table test02\G  #擷取資料表的表結構、索引等資訊
SELECT * from test02;

2．清空表，删除表内的所有資料

方法一：delete删除

delete from test01;

#DELETE清空表後，傳回的結果内有删除的記錄條目；DELETE工作時是一行一行的删除記錄資料的；如果表中有自增長字段，使用DELETE FROM 删除所有記錄後，再次新添加的記錄會從原來最大的記錄 ID 後面繼續自增寫入記錄。

方法二:truncate删除

truncate table test01;

#TRUNCATE 清空表後，沒有傳回被删除的條目；TRUNCATE 工作時是将表結構按原樣重建立立，是以在速度上 TRUNCATE 會比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内資料後，ID 會從 1 開始重新記錄。

3、建立臨時表

臨時表建立成功之後，使用SHOW TABLES指令是看不到建立的臨時表的，臨時表會在連接配接退出後被銷毀。如果在退出連接配接之前，也可以可執行增删改查等操作，比如使用 DROP TABLE 語句手動直接删除臨時表。

CREATE TEMPORARY TABLE 表名 (字段1 資料類型,字段2 資料類型[,...][,PRIMARY KEY (主鍵名)]);

create temporary table test03 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

insert into test03 values(1,'zhangsan',123456,'running');

select * from test03;
show tables;

quit
select * from test03;

4、建立外鍵限制，保證資料的完整性和一緻性

主鍵表和外鍵表的了解:

(1)以公共關鍵字作主鍵的表為主鍵表(父表、主表)

(2)以公共關鍵字作外鍵的表為外鍵表(從表、外表)

注意：與外鍵關聯的子表的字段必須設定為主鍵。要求主表外鍵字段和子表的字段具備相同的資料類型、字元長度和限制。

建立外鍵限制的操作步驟：

4.1 建立主鍵表和從表

4.2 為主鍵表添加一個主鍵限制。主鍵名建議以“PK_”開頭。

alter table live add constraint PK_id primary key(id);

4.3 為從表添加外鍵，外鍵名建議以“FK_”開頭，并與主鍵表建立關聯

為 det表添加外鍵，并将 det表的id 字段和 live表的 id 字段建立外鍵關聯。外鍵名建議以“FK_”開頭。

alter table det add constraint FK_id foreign key (id) references live (id);

4.4 插入新的資料記錄時，要先主表再從表

4.5 删數資料記錄時，要先從表再主表，也就是說删除主鍵表時必須先删除其他與之關聯的表

4.6 檢視和解除外鍵限制以及删除外鍵

show create table det;
alter table det drop foreign key FK_hob;
alter table det drop key FK_hob;
desc det;

4.7 外鍵的定義:

如果同一個屬性字段x在表一中是主鍵，而在表二中不是主鍵，則字段x稱為表二的外鍵

4.8 MySQL中6種常見的限制

主鍵限制（primary key）

外鍵限制（foreign key）

非空限制（not null）