一、克隆/復(fù)制數(shù)據(jù)表

方法一：

create table test2 like test;    #復(fù)制格式，通過LIKE方法，復(fù)制yyy表結(jié)構(gòu)生成yyy2表
insert into test2 select * from test;      #備份內(nèi)容

方法二：

CREATE TABLE test3 (SELECT * from test);   #復(fù)制test 表數(shù)據(jù)到test3中

二、清空表，刪除表內(nèi)的所有數(shù)據(jù)

方法一

delete from 表名;

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

方法二

truncate table 表名;

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

刪除小結(jié)

drop table 表名

• 刪除速度快
• 表和內(nèi)容和結(jié)構(gòu)刪除
• 不可以帶 where
• 不可回滾(無法恢復(fù))
• 屬于 DDL

truncate table 表名

• 刪除速度快
• 表的內(nèi)容刪除
• 不可以帶where
• 不可回滾 (無法恢復(fù))
• 屆于 DDL

delete from 表名

• 刪除速度慢，需要逐行刪除
• 刪除后表結(jié)構(gòu)在，表內(nèi)容要看where執(zhí)行的情況
• 可以帶where
• 可以回滾 (可恢復(fù))
• 屬于DML

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

CREATE TEMPORARY TABLE 表名 (字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型[, ...][, PRIMARY KEY (主鍵名)]);

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

應(yīng)用場景：可做測試環(huán)境使用，比如刪除大量數(shù)據(jù)的時候，可以創(chuàng)建臨時表 做一個復(fù)雜刪除

四、MySQL中6種常見的約束

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

1、外鍵的定義

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

2、創(chuàng)建外鍵約束作用

創(chuàng)建外鍵約束作用(誤刪，修改)，保證數(shù)據(jù)的完整性和一致性。

主鍵表和外鍵表的理解

1. 以公共關(guān)鍵字作主鍵的表為主鍵表(父表、主表)
2. 以公共關(guān)鍵字作外鍵的表為外鍵表(從表、外表)

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

3、創(chuàng)建主表test4

create table test4 (hobid int(4),hobname varchar(50));

4、創(chuàng)建從表test5

create table test5 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));

5、為主表test4添加一個主鍵約束。主鍵名建議以"PK_”開頭。

alter table test4 add constraint PK_hobid primary key (hobid);

6、為從表test5表添加外鍵，并將test5表的hobid字段和test4表的hobid字段建立外鍵關(guān)聯(lián)。外鍵名建議以"FK_”開頭。

alter table test5 add constraint FK_hob foreign key (hobid) references test4 (hobid);

7、可以使用查詢表語句結(jié)構(gòu)命令查看外鍵關(guān)聯(lián)

show create table test5;

desc test4;
desc test5;

8、插入新的數(shù)據(jù)記錄時，要先主表再從表

insert into test4 values(1,'runing');
insert into test5 values(1,'zhangsan',18,1);

9、刪數(shù)數(shù)據(jù)記錄時，要先從表再主表

drop tables test5;
drop tables test4;

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

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

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

1、新建用戶

CREATE USER '用戶名'@'來源地址' [IDENTIFIED BY [PASSWORD] '密碼'];

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

2、查看用戶信息

創(chuàng)建后的用戶保存在mysql數(shù)據(jù)庫的user表里

USE mysql;
SELECT User,authentication_string,Host from user;

3、重命名指定

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

4、刪除用戶

DROP USER 'lisi'@'localhost' ;

5、修改當(dāng)前密碼

SET PASSWORD = PASSWORD('abc123');

6、修改其他用戶密碼

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123T);

7、忘記root密碼的解決辦法

修改/etc/my.cnf 配置文件，免密登陸mysql

vim /etc/my.cnf
[mysqld]
skip-grant-tables    #添加，使登錄mysql不使用授權(quán)表

systemctl restart mysqld
mysql    #直接登錄

然后使用SQL語句修改密碼
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';

FLUSH PRIVILEGES;
quit

mysql -u root -pabc123
PS:最后再把/etc/my.cnf 配置文件里的skip-grant-tables 刪除，并重啟mysql服務(wù)

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

1、授予權(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ù)庫和表的名稱，其中可以使用通配符"*"。
例如，使用“school.*"表示授權(quán)操作的對象為school數(shù)據(jù)庫中的所有表。

#'用戶名@來源地址'：用于指定用戶名稱和允許訪問的客戶機地址，即誰能連接、能從哪里連接。來源地址可以是域名、IP地址，還可以使用“%”通配符，表示某個區(qū)域或網(wǎng)段內(nèi)的所有地址，如“%.xyw.com"、“192. 168.80.%”等。

#IDENTIFIED BY：用于設(shè)置用戶連接數(shù)據(jù)庫時所使用的密碼字符串。
在新建用戶時，若省略“IDENTIFIED BY"部分，則用戶的密碼將為空。

2、查看權(quán)限

SHOW GRANTS FOR 用戶名@來源地址;

3、撤銷權(quán)限

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

USAGE權(quán)限只能用于數(shù)據(jù)庫登陸，不能執(zhí)行任何操作; USAGE權(quán)限不能被回收，即REVOKE不能刪除用戶。

4、補充

授權(quán)用戶權(quán)限 all privileges 代表了哪些權(quán)限呢？

等文章來源地址http://www.zghlxwxcb.cn/news/detail-569869.html

到了這里，關(guān)于MySQL數(shù)據(jù)表高級操作的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！