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

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));

二、克隆/復制一個表

1、方法一

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

2、方法二

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

三、清空表，洗掉表內資料

1、方法一

delete from 表名;

#delete清空表后，回傳的結果內有洗掉的記錄條目；delete作業時是一行一行的洗掉記錄資料的；如果表中有自增長欄位，使用delete from 洗掉所有記錄后，再次新添加的記錄會從原來最大的記錄 ID 后面繼續自增寫入記錄

2、方法二

truncate table 表名;

#truncate 清空表后，沒有回傳被洗掉的條目；truncate 作業時是將表結構按原樣重新建立，因此在速度上 truncate 會比 delete 清空表快；使用 truncate table 清空表內資料后，ID 會從 1 開始重新記錄，

3、方法三

drop table [資料庫名.]表名;

#drop 屬于直接洗掉表，比較暴力

4、三個洗掉方法的對比

• 論洗掉速度而言：
  • drop 為直接洗掉表
  • truncate 為直接摧毀重構
  • delete 為逐條洗掉
  • 因此 drop > truncate > delete
• 就安全性而言：
  • drop 和 truncate 都是不保留有記錄的，不便于資料的恢復
  • delete 因為有洗掉操作的每一個步驟，因此當誤刪的時候可以根據日志進行恢復操作
  • 因此 delete 的安全性最好

四、創建臨時表

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

create temporary table 表名 (欄位1 型別，欄位2 型別 [,primary key (主鍵名)])；

五、mysql常見約束

六、外鍵約束

1、外鍵的定義

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

2、作用

• 創建外鍵約束的作用：保證資料的完整性和一致性

3、主表和從表

• 1、以公共關鍵字作為主鍵的表為主鍵表（父表、主表）

• 2、以公共關鍵字作為外鍵的表為外鍵表（從表、外表）

• 注意：與外鍵關聯的主表的欄位必須設定為主鍵，要求從表不能是臨時表，主從表的欄位具有相同的資料型別、字符長度和約束

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

①、首先創建兩張表，并且其中一張表的欄位和另一張表的主鍵相同

②、向表內添加資料內容

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

④、查看結構情況顯示外鍵關聯

⑤、插入資料（先主表，再從表）

⑥、洗掉資料記錄（先從表，后主表）

⑦、如果要洗掉外鍵約束欄位（先洗掉外鍵約束，再洗掉外鍵名）

七、資料庫用戶管理

1、新建用戶

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

• 用戶名：指定將創建的用戶名
• 來源地址：指定新創建的用戶可在哪些主機上登錄，可使用IP地址、網段、主機名的形式，本地用戶可用localhost，允許任意主機登錄
• 可用通配符%
• 可使用通配符 %
• 密碼’：若使用明文密碼，直接輸入’密碼’，插入到資料庫時由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、修改當前登錄用戶密碼

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服務

②、免密登錄mysql，使用 update 修改 root 密碼，并重繪資料庫

③、退出資料庫，注釋掉組態檔中的免密登錄項，并重啟mysql服務

八、資料庫用戶授權

1、授予權限

grant 提權
grant 權限串列 on 資料庫名.表名 to '用戶名'@'來源地址' [identified by '密碼'];

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

2、查看權限

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

3、撤銷權限

revoke 權限串列 on 資料庫名.表名 from 用戶名@來源地址;

總結

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

1、需要了解的5個項：

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

2、關于克隆一個表的操作

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

3、清空表操作

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

4、臨時表

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

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

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

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

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

7、用戶管理

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