首頁 > 軟體

MySQL資料庫基本SQL語句教學之高階操作

2022-06-26 14:02:21

前言:

瞭解了一下MySQL資料庫的基本語句,這章瞭解一下它的高階操作,包括使用者增刪除與給予相對應的許可權

一.克隆表

1.1克隆方法一(將表與內容分開克隆)

#​create table 新表名 like 複製的表名;  ​
​複製格式,能將複製表的格式到新表,但是裡面的內容無法複製​
 
​insert into 新表名 select * from 複製的表名; ​
​複製原表內容到新表

1.2克隆方法二(將表與內容一起復制)

create table 新表名 (select * from 複製的表名)
​資料結構和資料能一起復制

 二.清空表,刪除表內的所有資料  

2.1方法一

delete from naixu1;
#DELETE清空表後,返回的結果內有刪除的記錄條目;
delete
工作時是一行一行的刪除記錄資料的;如果表中有自增長欄位,使用DELETE FROM
刪除所有記錄後,在此新增的記錄會從原來最大的記錄id後面繼續自增寫入資料

2.2方法二

truncate table naixu1;
#TRUNCATE清空表後,沒有返回被刪除的條目:TRUNCATE
工作時是將表結構按原樣重新建立
因此在速度上TRUNCATE會比DELETE清空錶快
使用TRUNCATE TABLE 清空表內資料後,id會從1開始重新記錄

2.3小小結之drop,truncate,eleted的對比

droptruncatedelete
屬於DDL屬於DDL屬於DML
不可回滾不可回滾可回滾
不可帶where不可帶where可帶where
表內容和結構刪除表內容刪除表結構在,表內容要看where執行的情況
刪除速度快刪除速度快刪除速度慢,需要逐行刪除

總結:

不再需要一張表的時候用drop想刪除部分資料行的時候用delete,並且帶上where子句保留表而刪除所有資料的時候用truncate刪除速度:drop>truncate> delete安全性 delete 最好

三.建立臨時表

##新增臨時表niaxu3
create temporary table naixu3 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
 
## 檢視當前庫中所有表
show tables; 
##在臨時表中新增資料
insert into test03 values(1,'hehe',12345,'看美女'); 
##檢視當前表中所有資料
select * from naixu3;
##退出資料庫
quit      
 
##重新登入後進行檢視  
mysql -u root -p
##檢視之前建立的臨時表中所有資料,發現已經被自動銷燬
select * from naixu3; 

四.使用者管理

4.1新建使用者

CREATE USER '使用者名稱'@'來源地址' [IDENTIFIED BY [PASSWORD] '密碼'];
 
#‘使用者名稱':指定將建立的使用者名稱
#‘來源地址':指定新建立的使用者可在哪些主機上登入,可使用IP地址、網段、主機名的形式,本地使用者可用localhost,允許任意主機登入可用萬用字元%
#‘密碼':若使用明文密碼,直接輸入'密碼',插入到資料庫時由Mysql自動加密;
#######若使用加密密碼,需要先使用SELECT PASSWORD(‘密碼'); 獲取密文,再在語句中新增 PASSWORD ‘密文';
#若省略「IDENTIFIED BY」部分,則使用者的密碼將為空(不建議使用)

4.2使用明文密碼建立使用者

create user 'nannan'@'localhost' identified by '123455';

4.3使用密文建立資料庫

五.檢視使用者資訊 

建立後的使用者儲存在 mysql 資料庫的 user 表裡
 
use mysql;   #使用mysql庫 
select User from user;

六.重新命名使用者

rename user 'nannan'@'localhost' to 'lnhs'@'localhost';
#將使用者nannan改名為lnhs

 七.刪除使用者

drop user 'chenchen'@'localhost';
#刪除使用者chenchen

 八.密碼管理

8.1修改當前使用者密碼

set password = password('123456');

8.2修改其他使用者的密碼

set password for 'naixu'@'localhost' = password('123456');

8.3忘記root密碼

修改組態檔,新增設定,免密登入MySQL
vim /etc/my.cnf
skip-grant-tables #新增,使登入mysql不適用授權表

8.3.1給root設定密碼

update mysql.user set authentication_string = password('123456') where user='root';
flush privileges;  #重新整理 
 
登入資料庫之後再次修改my.conf組態檔,註釋掉之前新增的設定命令,並再次重啟服務
使用新密碼登入

九.資料庫授權  

9.1關於授權

  GRANT語句:專門用來設定資料庫使用者的存取許可權。當指定的使用者名稱不存在時,GRANT語句將    會建立新的使用者;當指定的使用者名稱存在時,GRANT 語句用於修改使用者資訊。

9.2授權

GRANT 許可權列表 ON 資料庫名/表名 TO '使用者名稱'@'來源地址' [IDENTIFIED BY '密碼'];
許可權列表用於列出授權使用的各種資料庫操作,以逗號進行分隔,如“select,insert,update”。使用“all”表示所有許可權,可授權執行任何操作。
資料庫名.表名用於指定授權操作的資料庫和表的名稱,其中可以使用萬用字元
使用者名稱@來源地址用於指定使用者名稱稱和允許存取的客戶機地址,即誰能連線、能從哪裡連線。來源地址可以是域名、IP地址,還可以使用“%”萬用字元,表示某個區域或網段內的所有地址,如“%.accp.com”、“192.168.80.%”等。
IDENTIFIED BY用於設定使用者連線資料庫時所使用的密碼字串。在新建使用者時,若省略“IDENTIFIED BY”部分,則使用者的密碼將為空。

9.2.1授權列表

許可權功能
select查詢資料
insert插入資料
update更新資料
delete刪除資料
create建立庫、表
drop刪除庫、表
index建立索引
alter更改表屬性
event事件
trigger on建立觸發器

9.3資料庫授權

show grants for nannan@localhost;
#檢視使用者許可權

指定使用者可以檢視哪個資料庫或表,別的無法存取

grant select on hehe.* to nannan@localhost;
#使用者nannan只有hehe庫下所有表的查詢許可權

切換使用者進行驗證

9.4遠端登陸授權(使用navicat遠端登陸)

grant all on *.* to 'nannan'@'%' identified by '123456';

9.5復原許可權

revoke select on hehe.* from nannan@localhost;

 再次切換存取,就已經沒有許可權了

十.總結

本章和拐友們講解MySQL的高階語句,包括瞭如何克隆表,如何進行使用者的增刪改以及使用者的許可權設定,總的來說就是隻要記住3點增刪改就行

到此這篇關於MySQL資料庫基本SQL語句教學之高階操作的文章就介紹到這了,更多相關MySQL SQL語句高階操作內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!


IT145.com E-mail:sddin#qq.com