MySQL數據庫基本SQL語句教程之高級操作

Posted on by

前言：

瞭解瞭一下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的對比

drop truncate delete
屬於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語句高級操作內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：