Mysql數(shù)據(jù)庫（三）——mysql數(shù)據(jù)庫高級操作

• 一、指定主鍵的另一種方式
• 二、克隆/復(fù)制一個表
• • 1、方法一
  • 2、方法二
• 三、清空表，刪除表內(nèi)數(shù)據(jù)
• • 1、方法一
  • 2、方法二
  • 3、方法三
  • 4、三個刪除方法的對比
• 四、創(chuàng)建臨時表
• 五、mysql常見約束
• 六、外鍵約束
• • 1、外鍵的定義
  • 2、作用
  • 3、主表和從表
  • 4、主表和從表的操作原則和示范
  • • ①、首先創(chuàng)建兩張表，并且其中一張表的字段和另一張表的主鍵相同
    • ②、向表內(nèi)添加數(shù)據(jù)內(nèi)容
    • ③、為主表添加主鍵約束，并為從表添加外鍵
    • ④、查看結(jié)構(gòu)情況顯示外鍵關(guān)聯(lián)
    • ⑤、插入數(shù)據(jù)（先主表，再從表）
    • ⑥、刪除數(shù)據(jù)記錄（先從表，后主表）
    • ⑦、如果要刪除外鍵約束字段（先刪除外鍵約束，再刪除外鍵名）
• 七、數(shù)據(jù)庫用戶管理
• • 1、新建用戶
  • 2、查看用戶信息
  • 3、重命名用戶
  • 4、刪除用戶
  • 5、修改當(dāng)前登錄用戶密碼
  • 6、修改其他用戶密碼
  • 7、mysql的root登錄密碼忘記了處理辦法
  • • ①、修改配置文件，改成免密登錄，并重啟mysql服務(wù)
    • ②、免密登錄mysql，使用 update 修改 root 密碼，并刷新數(shù)據(jù)庫
    • ③、退出數(shù)據(jù)庫，注釋掉配置文件中的免密登錄項，并重啟mysql服務(wù)
• 八、數(shù)據(jù)庫用戶授權(quán)
• • 1、授予權(quán)限
  • 2、查看權(quán)限
  • 3、撤銷權(quán)限
• 總結(jié)
• • 1、需要了解的5個項：
  • 2、關(guān)于克隆一個表的操作
  • 3、清空表操作
  • 4、臨時表
  • 5、mysql的6個常見約束方式
  • 6、關(guān)于外鍵的定義、作用和主表從表的一些操作注意點
  • 7、用戶管理

一、指定主鍵的另一種方式

use lucien;create table if not exists ljm ( id int(4) zerofill primary key auto_increment, student_name varchar(20) not null, cardid varchar(18) not null unique key, hobby varchar(50));

if not exists表示檢測要創(chuàng)建的表是否存在，如果不存在就創(chuàng)建表

int(4) zerofill	表示若數(shù)值不滿4位數(shù)，則前面用“0”填充
auto_increment	表示此字段為自增長字段，即每條記錄自動遞增1，默認(rèn)從1開始遞增；自增長字段數(shù)據(jù)不可以重復(fù)；自增長字段必須是主鍵；如添加的記錄數(shù)據(jù)沒有指定此字段的值且添加失敗也會自動遞增一次
unique key	表示此字段唯一鍵約束，此字段數(shù)據(jù)不可以重復(fù)；一張表中只能有一個主鍵, 但是一張表中可以有多個唯一鍵
not null	表示此字段不允許為NULL

二、克隆/復(fù)制一個表

1、方法一

create table 新表名 like 復(fù)制的表名; #復(fù)制格式，能夠復(fù)制表的格式到新表，但是沒有內(nèi)容 insert into 新表名 select * from 復(fù)制的表名; #復(fù)制原表內(nèi)容到新表

2、方法二

create table 新表名 (select * from 復(fù)制的表名)

三、清空表，刪除表內(nèi)數(shù)據(jù)

1、方法一

delete from 表名;#delete清空表后，返回的結(jié)果內(nèi)有刪除的記錄條目；delete工作時是一行一行的刪除記錄數(shù)據(jù)的；如果表中有自增長字段，使用delete from 刪除所有記錄后，再次新添加的記錄會從原來最大的記錄 ID 后面繼續(xù)自增寫入記錄

2、方法二

truncate table 表名;#truncate 清空表后，沒有返回被刪除的條目；truncate 工作時是將表結(jié)構(gòu)按原樣重新建立，因此在速度上 truncate 會比 delete 清空表快；使用 truncate table 清空表內(nèi)數(shù)據(jù)后，ID 會從 1 開始重新記錄。

3、方法三

drop table [數(shù)據(jù)庫名.]表名;#drop 屬于直接刪除表，比較暴力

4、三個刪除方法的對比

• 論刪除速度而言：
  • drop 為直接刪除表
  • truncate 為直接摧毀重構(gòu)
  • delete 為逐條刪除
  • 因此 drop > truncate > delete
• 就安全性而言：
  • drop 和 truncate 都是不保留有記錄的，不便于數(shù)據(jù)的恢復(fù)
  • delete 因為有刪除操作的每一個步驟，因此當(dāng)誤刪的時候可以根據(jù)日志進行恢復(fù)操作
  • 因此 delete 的安全性最好

四、創(chuàng)建臨時表

• 臨時表創(chuàng)建成功之后，使用 show tables 命令是看不到創(chuàng)建的臨時表的，臨時表會在連接退出后被銷毀。
• 如果在退出連接之前，也可以可執(zhí)行增刪改查等操作，比如使用 drop table 語句手動直接刪除臨時表。
• 同時，臨時表也無法創(chuàng)建外鍵。

create temporary table 表名 (字段1 類型，字段2 類型 [,primary key (主鍵名)])；

五、mysql常見約束

主鍵約束primary key

外鍵約束	foreign key
非空約束	not null
唯一約束	unique [key
默認(rèn)值約束	default
自增約束	auto_increment

六、外鍵約束

1、外鍵的定義

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

2、作用

• 創(chuàng)建外鍵約束的作用：保證數(shù)據(jù)的完整性和一致性

3、主表和從表

• 1、以公共關(guān)鍵字作為主鍵的表為主鍵表（父表、主表）

• 2、以公共關(guān)鍵字作為外鍵的表為外鍵表（從表、外表）

• 注意：與外鍵關(guān)聯(lián)的主表的字段必須設(shè)置為主鍵，要求從表不能是臨時表，主從表的字段具有相同的數(shù)據(jù)類型、字符長度和約束

4、主表和從表的操作原則和示范

①、首先創(chuàng)建兩張表，并且其中一張表的字段和另一張表的主鍵相同

②、向表內(nèi)添加數(shù)據(jù)內(nèi)容

③、為主表添加主鍵約束，并為從表添加外鍵

④、查看結(jié)構(gòu)情況顯示外鍵關(guān)聯(lián)

⑤、插入數(shù)據(jù)（先主表，再從表）

⑥、刪除數(shù)據(jù)記錄（先從表，后主表）

⑦、如果要刪除外鍵約束字段（先刪除外鍵約束，再刪除外鍵名）

七、數(shù)據(jù)庫用戶管理

1、新建用戶

create user '用戶名'@'來源地址' [identified by [passwd] '密碼'];

• 用戶名：指定將創(chuàng)建的用戶名
• 來源地址：指定新創(chuàng)建的用戶可在哪些主機上登錄，可使用IP地址、網(wǎng)段、主機名的形式，本地用戶可用localhost，允許任意主機登錄
• 可用通配符%
• 可使用通配符 %
• 密碼’：若使用明文密碼，直接輸入’密碼’，插入到數(shù)據(jù)庫時由Mysql自動加密;
  • 若使用加密密碼，需要先使用SELECT PASSWORD(‘密碼’); 獲取密文，再在語句中添加 PASSWORD ‘密文’;
  • 若省略“IDENTIFIED BY”部分，則用戶的密碼將為空（不建議使用）

2、查看用戶信息

use mysql;select User,authentication_string,Host from user;

3、重命名用戶

rename user 'lisi'@'localhost' to 'wangwu'@'localhost';select User,authentication_string,Host from user;

4、刪除用戶

drop user 'wangwu'@'localhost';select User,authentication_string,Host from user;

5、修改當(dāng)前登錄用戶密碼

set password = password('123456'); quit mysql -u root -p

6、修改其他用戶密碼

set password for 'zhangsan'@'localhost' = password('111111'); use mysql; select User,authentication_string,Host from user;

7、mysql的root登錄密碼忘記了處理辦法

①、修改配置文件，改成免密登錄，并重啟mysql服務(wù)

②、免密登錄mysql，使用 update 修改 root 密碼，并刷新數(shù)據(jù)庫

③、退出數(shù)據(jù)庫，注釋掉配置文件中的免密登錄項，并重啟mysql服務(wù)

八、數(shù)據(jù)庫用戶授權(quán)

1、授予權(quán)限

grant 提權(quán) grant 權(quán)限列表 on 數(shù)據(jù)庫名.表名 to '用戶名'@'來源地址' [identified by '密碼'];

• 權(quán)限列表：用于列出授權(quán)使用的各種數(shù)據(jù)庫操作，以逗號進行分隔，如 “select,insert,update” 。使用 “all” 表示所有權(quán)限，可授權(quán)執(zhí)行任何操作。
• 數(shù)據(jù)庫名.表名：用于指定授權(quán)操作的數(shù)據(jù)庫和表的名稱，其中可以使用通配符 * 。lucien.* 表示授權(quán)操作的對象為lucien數(shù)據(jù)庫中的所有表。
• ‘用戶名@來源地址’：用于指定用戶名稱和允許訪問的客戶機地址，即誰能連接、能從哪里連接。來源地址可以是域名、IP 地址，還可以使用 “%” 通配符，表示某個區(qū)域或網(wǎng)段內(nèi)的所有地址，如 “%.lucien.com” 、“192.168.184.%” 等。
• identified by：用于設(shè)置用戶連接數(shù)據(jù)庫時所使用的密碼字符串。在新建用戶時，若省略 “identified by” 部分， 則用戶的密碼將為空。

2、查看權(quán)限

show grants for 用戶名@來源地址;

3、撤銷權(quán)限

revoke 權(quán)限列表 on 數(shù)據(jù)庫名.表名 from 用戶名@來源地址;

總結(jié)

• 這一章節(jié)，我們需要了解一些簡單的 DBA 的高級操作

1、需要了解的5個項：

• if not exists：表示檢測要創(chuàng)建的表是否存在，如果不存在就創(chuàng)建表
• int(4) zerofill：表示若數(shù)值不滿4位數(shù)，則前面用“0”填充
• auto_increment：表示此字段為自增長字段，即每條記錄自動遞增1，默認(rèn)從1開始遞增；自增長字段數(shù)據(jù)不可以重復(fù)；自增長字段必須是主鍵；如添加的記錄數(shù)據(jù)沒有指定此字段的值且添加失敗也會自動遞增一次
• unique key：表示此字段唯一鍵約束，此字段數(shù)據(jù)不可以重復(fù)；一張表中只能有一個主鍵, 但是一張表中可以有多個唯一鍵
• not null：表示此字段不允許為NULL

2、關(guān)于克隆一個表的操作

• 有兩種方法可進行克隆/復(fù)制一個表
• 方法一：先復(fù)制格式再復(fù)制內(nèi)容
  • create table 新表名 like 復(fù)制的表名;
  • insert into 新表名 select * from 復(fù)制的表名;
• 方法二：直接全部復(fù)制（包括格式和內(nèi)容）
  • create table 新表名 (select * from 復(fù)制的表名)

3、清空表操作

• 有三種方式：delete、truncate 和 drop
• 三種操作的對比：
  • 刪除速度：drop > truncate > delete
  • 安全性：delete 最好

4、臨時表

• 臨時表創(chuàng)建成功之后，使用 show tables 命令是看不到創(chuàng)建的臨時表的，臨時表會在連接退出后被銷毀。
• 如果在退出連接之前，也可以可執(zhí)行增刪改查等操作，比如使用 drop table 語句手動直接刪除臨時表。
• 同時，臨時表也無法創(chuàng)建外鍵。
• 創(chuàng)建方式：
  • create temporary table 表名 (字段1 類型，字段2 類型 [,primary key (主鍵名)])；

5、mysql的6個常見約束方式

• 主鍵約束：primary key
• 外鍵約束：foreign key
• 非空約束：not null
• 唯一約束：unique [key
• 默認(rèn)值約束：default
• 自增約束：auto_increment

6、關(guān)于外鍵的定義、作用和主表從表的一些操作注意點

• 定義：我把外鍵簡單理解為外部表格的主鍵。
• 作用：保證數(shù)據(jù)的完整性和一致性。能更好的避免誤刪的情況出現(xiàn)。
• 添加主鍵約束：
  • alter table fengchu add constraint PK_cardid primary key(cardid);
• 添加外鍵：
  • alter table wolong add constraint FK_card foreign key(cardid) references fengchu(cardid);
• 主表和從表：
  • 主表：公共關(guān)鍵字作為主鍵的表
  • 從表：公共關(guān)鍵字作為外鍵的表
• 操作注意點：
  • 插入數(shù)據(jù)時，先主表，后從表
  • 刪除數(shù)據(jù)時，先從表，后主表
• 刪除約束字段：先刪除外鍵約束，再刪除外鍵名
  • alter table wolong drop foreign key FK_card;
  • alter table wolong drop key FK_card;

7、用戶管理

• ‘用戶名’@'來源地址’是一個整體，不管是創(chuàng)建、刪除、重命名、改密碼等操作
• 所有的 mysql 用戶信息都存放在 mysql 庫的 user 表中，可使用 select User,authentication_string,Host from user;查看
• 關(guān)于root龍湖忘記登錄數(shù)據(jù)庫的密碼，修改完配置文件后，免密登錄mysql之后，并沒有提權(quán)，所以我們不能直接使用set 來修改密碼，需要使用 update mysql.user set authentication_string = password(‘010230’) where user=‘root’;
• 在給用戶授權(quán)的時候，可以使用通配符代表所有。
• 不建議省略 “identified by” 部分，因為這樣就代表不需要密碼登錄，會很不安全。
• 才進行撤銷權(quán)限操作的時候，最后剩下的USAGE代表登錄權(quán)限，為不可回收權(quán)限。

總結(jié)

以上是生活随笔為你收集整理的Mysql数据库（三）——mysql数据库高级操作的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。