MYSQL 表的全面總結(jié)
更新時(shí)間：2021年11月09日 15:16:27 作者：隨風(fēng)去遠(yuǎn)方
這篇文章主要介紹了MYSQL表，文章主要圍繞MySQL表的相關(guān)資料如創(chuàng)建表、刪除表、修改表、等展開(kāi)內(nèi)容,需要的朋友可以參考一下，希望對(duì)你有所幫助
1、創(chuàng)建表
1.1、創(chuàng)建表基本語(yǔ)法
CREATE TABLE tablename (column_name_1 column_type_1 constraints， column_name_2 column_type_2 constraints ， ……）
column_name 是列的名字
column_type 是列的數(shù)據(jù)類型
contraints 是這個(gè)列的約束條件
1.1.1、創(chuàng)建一張簡(jiǎn)單的表
mysql> create table orders (ordername varchar(10),createtime date,ordermoney decimal(10,2),ordernumber int(2)); Query OK, 0 rows affected (0.23 sec)
1.1.2、查看創(chuàng)建表定義
結(jié)構(gòu)化定義：
mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(10) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
表詳細(xì)定義：
查看詳細(xì)的表定義：
mysql> show create table orders \G;
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`ordername` varchar(10) DEFAULT NULL,
`createtime` date DEFAULT NULL,
`ordermoney` decimal(10,2) DEFAULT NULL,
`ordernumber` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
由此可以看到表的 ENGINE（存儲(chǔ)引擎）是InnoDB
CHARSET（字符集）是Latin1
“\G”選項(xiàng)的含義是使得記錄能夠按照字段豎著排列，對(duì)于內(nèi)容比較長(zhǎng)的記錄更易于顯示。
2、刪除表
命令：
DROP TABLE tablename
刪除orders：
mysql> drop table orders
-> ;
Query OK, 0 rows affected (0.14 sec)
3、修改表
3.1、修改表類型命令
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
例：修改表 orders 的 name 字段定義，將 varchar(10)改為 varchar(20) ：
mysql> alter table orders modify ordername varchar(20); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
3.2、字段改名命令
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
例：表 orders 上將ordernumber修改為ordernumbers
mysql> alter table orders change column ordernumber ordernumbers int(4); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumbers | int(4) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
特別說(shuō)明：change 和 modify 都可以修改表的定義，不同的是 change 后面需要寫兩次列名，不方便。但是 change 的優(yōu)點(diǎn)是可以修改列名稱，modify 則不能。
3.3、增加表字段命令
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
例：表 orders 上新增加字段 username，類型為 varchar(3) ：
mysql> alter table orders add column username varchar(30); Query OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | | username | varchar(30) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
3.4、刪除表列字段命令
ALTER TABLE tablename DROP [COLUMN] col_name
例：表 orders 上刪除字段 username：
mysql> alter table orders drop column username; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
3.5、表改名命令
ALTER TABLE tablename RENAME [TO] new_tablename
例：表 orders 名字改為goodsorders
mysql> alter table orders rename goodsorders; Query OK, 0 rows affected (0.16 sec) mysql> desc orders; ERROR 1146 (42S02): Table 'ordermanage.orders' doesn't exist mysql> desc goodsorders; +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumbers | int(4) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
4、DML 語(yǔ)句
插入（insert）、查詢（select）、更新（update）、刪除（delete）
4.1、插入記錄 命令
INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);
例：表 goodsorders 中插入一條記錄，ordername 為zhang，createtime為2021-05-12，ordermoney為100.00,ordernumbers為：1
mysql> insert into goodsorders (ordername,createtime,ordermoney,ordernumbers) values('zhang','2021-05-12',100.00,1);
Query OK, 1 row affected (0.03 sec)
也可以省略(field1,field2,……fieldn)這一部分
mysql> insert into goodsorders values('zhang1','2021-05-12',1001.00,11);
Query OK, 1 row affected (0.05 sec)
4.2、查看插入數(shù)據(jù)命令
4.2.1、查詢?nèi)?/h4>
SELECT * FROM tablename [WHERE CONDITION]
SELECT * FROM tablename [WHERE CONDITION]
例：查看goodsorders中所有插入數(shù)據(jù)
mysql> select * from goodsorders; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | zhang | 2021-05-12 | 100.00 | 1 | | zhang1 | 2021-05-12 | 1001.00 | 11 | +-----------+------------+------------+--------------+ 2 rows in set (0.00 sec)
其中“*”表示要將所有的記錄都選出來(lái)
4.2.2、查詢不重復(fù)記錄命令關(guān)鍵字
distinct
例：查詢非goodsorders中非重復(fù)創(chuàng)建時(shí)間（createtime）的數(shù)據(jù)
mysql> select * from goodsorders; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | zhang | 2021-03-11 | 50.00 | 1 | | li | 2020-05-12 | 70.00 | 15 | | li | 2020-03-12 | 70.00 | 15 | | li | 2020-03-11 | 70.00 | 15 | | li | 2021-03-11 | 70.00 | 15 | +-----------+------------+------------+--------------+ 5 rows in set (0.00 sec) mysql> select distinct createtime from goodsorders; +------------+ | createtime | +------------+ | 2021-03-11 | | 2020-05-12 | | 2020-03-12 | | 2020-03-11 | +------------+ 4 rows in set (0.00 sec)
由此可以看到，將重復(fù)的一條時(shí)間數(shù)據(jù)2021-03-11去掉了
4.2.3、多條件查詢關(guān)鍵字
where 后面的條件是一個(gè)字段的‘='比較，還可以使用>、<、>=、<=、!=等比較運(yùn)算符；
多個(gè)條件之間還可以使用 or、and 等邏輯運(yùn)算符進(jìn)行多條件聯(lián)合查詢，
例：查詢非goodsorders中 ordername='li'并且createtime為2020-03-11
mysql> select * from goodsorders where ordername='li'and createtime ='2020-03-11'; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | li | 2020-03-11 | 70.00 | 15 | +-----------+------------+------------+--------------+ 1 row in set (0.00 sec)
4.2.4、排序查詢命名
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] ， field2 [DESC|ASC]，……fieldn [DESC|ASC]]
例：把 goodsorders表中的記錄按照創(chuàng)建時(shí)間高低進(jìn)行排序顯示
mysql> select * from goodsorders order by createtime; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | li | 2020-03-11 | 70.00 | 15 | | li | 2020-03-12 | 70.00 | 15 | | li | 2020-05-12 | 70.00 | 15 | | zhang | 2021-03-11 | 50.00 | 1 | | li | 2021-03-11 | 70.00 | 15 | +-----------+------------+------------+--------------+ 5 rows in set (0.01 sec)
4.2.5、顯示一部分，而不是全部，指令
SELECT ……[LIMIT offset_start,row_count]
offset_start 表示記錄的起始偏移量
row_count 表示顯示的行數(shù)
例如1：顯示 goodsorders表中按照 createtiem 排序后的前 3 條記錄：
mysql> select * from goodsorders order by createtime limit 3; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | li | 2020-03-11 | 70.00 | 15 | | li | 2020-03-12 | 70.00 | 15 | | li | 2020-05-12 | 70.00 | 15 | +-----------+------------+------------+--------------+ 3 rows in set (0.00 sec)
例如2：如果要顯示 goodsorders表中按照 createtiem 排序后 從第二條記錄開(kāi)始，顯示3條數(shù)據(jù)：
mysql> select * from goodsorders order by createtime limit 2,3; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | li | 2020-05-12 | 70.00 | 15 | | zhang | 2021-03-11 | 50.00 | 1 | | li | 2021-03-11 | 70.00 | 15 | +-----------+------------+------------+--------------+ 3 rows in set (0.00 sec)
4.2.6、統(tǒng)計(jì)數(shù)據(jù)，聚合指令
SELECT [field1,field2,……fieldn] fun_name FROM tablename [WHERE where_contition] [GROUP BY field1,field2,……fieldn [WITH ROLLUP]] [HAVING where_contition]
參數(shù)說(shuō)明：
- 1、
fun_name表示要做的聚合操作，也就是聚合函數(shù)，常用的有sum（求和）、count(*)（記錄數(shù)）、max（最大值）、min（最小值） - 2、
GROUP BY關(guān)鍵字表示要進(jìn)行分類聚合的字段，比如要按照部門分類統(tǒng)計(jì)員工數(shù)量，部門就應(yīng)該寫在groupby 后面。 - 3、
WITH ROLLUP是可選語(yǔ)法，表明是否對(duì)分類聚合后的結(jié)果進(jìn)行再匯總。 - 4、
HAVING關(guān)鍵字表示對(duì)分類后的結(jié)果再進(jìn)行條件的過(guò)濾。
注意：having 和 where 的區(qū)別在于 having 是對(duì)聚合后的結(jié)果進(jìn)行條件的過(guò)濾，而 where 是在聚合前就對(duì)記錄進(jìn)行過(guò)濾，如果邏輯允許，我們盡可能用 where 先過(guò)濾記錄，這樣因?yàn)榻Y(jié)果集減小，將對(duì)聚合的效率大大提高，最后再根據(jù)邏輯看是否用 having 進(jìn)行再過(guò)濾。
例1：查詢統(tǒng)計(jì)goodsorders表中，記錄總數(shù)
mysql> select count(1) from goodsorders; +----------+ | count(1) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)
例2：在此基礎(chǔ)上，按照創(chuàng)建日期（createtime）進(jìn)行分組統(tǒng)計(jì)
mysql> select createtime,count(1) from goodsorders group by createtime; +------------+----------+ | createtime | count(1) | +------------+----------+ | 2020-03-11 | 1 | | 2020-03-12 | 1 | | 2020-05-12 | 1 | | 2021-03-11 | 2 | +------------+----------+ 4 rows in set (0.00 sec)
例3：在此基礎(chǔ)上，既要按照創(chuàng)建日期（cretetime）進(jìn)行分組統(tǒng)計(jì)，又要計(jì)算總數(shù)
mysql> select createtime,count(1) from goodsorders group by createtime with rollup; +------------+----------+ | createtime | count(1) | +------------+----------+ | 2020-03-11 | 1 | | 2020-03-12 | 1 | | 2020-05-12 | 1 | | 2021-03-11 | 2 | | NULL | 5 | +------------+----------+ 5 rows in set (0.02 sec)
最有一行，null所展示的數(shù)字，就是總數(shù)
例4：按照創(chuàng)建日期（createtime）進(jìn)行分組統(tǒng)計(jì)，并且數(shù)量大于1
mysql> select createtime,count(1) from goodsorders group by createtime having count(1)>1; +------------+----------+ | createtime | count(1) | +------------+----------+ | 2021-03-11 | 2 | +------------+----------+ 1 row in set (0.00 sec)
例5：查詢goodsorders表中，訂單金額（ordermoney）的總額、最低額、最高額
mysql> select * from goodsorders; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | zhang | 2021-03-11 | 50.00 | 1 | | li | 2020-05-12 | 70.00 | 15 | | li | 2020-03-12 | 70.00 | 15 | | li | 2020-03-11 | 70.00 | 15 | | li | 2021-03-11 | 70.00 | 15 | +-----------+------------+------------+--------------+ 5 rows in set (0.00 sec) mysql> select sum(ordermoney),max(ordermoney),min(ordermoney) from goodsorders; +-----------------+-----------------+-----------------+ | sum(ordermoney) | max(ordermoney) | min(ordermoney) | +-----------------+-----------------+-----------------+ | 330.00 | 70.00 | 50.00 | +-----------------+-----------------+-----------------+ 1 row in set (0.02 sec)
4.2.7、表連接
- 1、左連接：包含所有的左邊表中的記錄甚至是右邊表中沒(méi)有和它匹配的記錄；關(guān)鍵指令：
left join - 2、右連接：包含所有的右邊表中的記錄甚至是左邊表中沒(méi)有和它匹配的記錄；關(guān)聯(lián)指令：
right join
例1：現(xiàn)在我們又創(chuàng)建一張用戶表（member）,使用goodorders進(jìn)行左連接，查詢關(guān)聯(lián)的用戶表信息
mysql> select * from member; +------+------------+ | id | membername | +------+------------+ | 15 | zhang | | 1 | li | | 13 | liss | +------+------------+ 3 rows in set (0.00 sec) mysql> select * from goodsorders; +-----------+------------+------------+--------------+----------+ | ordername | createtime | ordermoney | ordernumbers | memberid | +-----------+------------+------------+--------------+----------+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | | li | 2020-05-12 | 70.00 | 15 | 1 | | li | 2020-03-12 | 70.00 | 15 | 1 | | li | 2020-03-11 | 70.00 | 15 | 3 | | li | 2021-03-11 | 70.00 | 15 | 1 | +-----------+------------+------------+--------------+----------+ 5 rows in set (0.00 sec) mysql> select * from goodsorders left join member on goodsorders.memberid = member.id; +-----------+------------+------------+--------------+----------+------+------------+ | ordername | createtime | ordermoney | ordernumbers | memberid | id | membername | +-----------+------------+------------+--------------+----------+------+------------+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | 15 | zhang | | li | 2020-05-12 | 70.00 | 15 | 1 | 1 | li | | li | 2020-03-12 | 70.00 | 15 | 1 | 1 | li | | li | 2021-03-11 | 70.00 | 15 | 1 | 1 | li | | li | 2020-03-11 | 70.00 | 15 | 3 | NULL | NULL | +-----------+------------+------------+--------------+----------+------+------------+ 5 rows in set (0.00 sec)
例2：member和goodsorders中數(shù)據(jù)不變，我們?cè)賮?lái)看一下右連接的查詢，以及結(jié)果：
mysql> select * from goodsorders right join member on goodsorders.memberid = member.id; +-----------+------------+------------+--------------+----------+------+------------+ | ordername | createtime | ordermoney | ordernumbers | memberid | id | membername | +-----------+------------+------------+--------------+----------+------+------------+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | 15 | zhang | | li | 2020-05-12 | 70.00 | 15 | 1 | 1 | li | | li | 2020-03-12 | 70.00 | 15 | 1 | 1 | li | | li | 2021-03-11 | 70.00 | 15 | 1 | 1 | li | | NULL | NULL | NULL | NULL | NULL | 13 | liss | +-----------+------------+------------+--------------+----------+------+------------+ 5 rows in set (0.00 sec)
這里發(fā)生了翻轉(zhuǎn)，變?yōu)樽髠?cè)goodsorders 表中的一條數(shù)據(jù)為空了
4.2.8、子查詢，相關(guān)關(guān)鍵字
主要包括 in、not in、=、!=、exists、not exists 等
例：從goodsorders表中查詢所有用戶在memeber表中的記錄
mysql> select * from member; +------+------------+ | id | membername | +------+------------+ | 15 | zhang | | 1 | li | | 13 | liss | +------+------------+ 3 rows in set (0.00 sec) mysql> select * from goodsorders; +-----------+------------+------------+--------------+----------+ | ordername | createtime | ordermoney | ordernumbers | memberid | +-----------+------------+------------+--------------+----------+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | | li | 2020-05-12 | 70.00 | 15 | 1 | | li | 2020-03-12 | 70.00 | 15 | 1 | | li | 2020-03-11 | 70.00 | 15 | 3 | | li | 2021-03-11 | 70.00 | 15 | 1 | +-----------+------------+------------+--------------+----------+ 5 rows in set (0.00 sec) mysql> select * from goodsorders where memberid in(select id from member); +-----------+------------+------------+--------------+----------+ | ordername | createtime | ordermoney | ordernumbers | memberid | +-----------+------------+------------+--------------+----------+ | zhang | 2021-03-11 | 50.00 | 1 | 15 | | li | 2020-05-12 | 70.00 | 15 | 1 | | li | 2020-03-12 | 70.00 | 15 | 1 | | li | 2021-03-11 | 70.00 | 15 | 1 | +-----------+------------+------------+--------------+----------+ 4 rows in set (0.05 sec)
4.2.9、記錄聯(lián)合，指令
SELECT * FROM t1 UNION|UNION ALL SELECT * FROM t2 …… UNION|UNION ALL SELECT * FROM tn;
UNION 和 UNION ALL 的主要區(qū)別：
UNION ALL 是把結(jié)果集直接合并在一起，
UNION 是將UNION ALL 后的結(jié)果進(jìn)行一次 DISTINCT，去除重復(fù)記錄后的結(jié)果。
例1：將member表和goodsorders表中的用戶編號(hào)id（memberid）的集合顯示出來(lái)
mysql> select memberid from goodsorders union all select id from member; +----------+ | memberid | +----------+ | 15 | | 1 | | 1 | | 3 | | 1 | | 15 | | 1 | | 13 | +----------+ 8 rows in set (0.00 sec)
例2：如果希望將上面的結(jié)果去掉重復(fù)記錄后顯示
mysql> select memberid from goodsorders union select id from member; +----------+ | memberid | +----------+ | 15 | | 1 | | 3 | | 13 | +----------+ 4 rows in set (0.00 sec)
4.3、更新記錄命令
UPDATE tablename SET field1=value1，field2.=value2，……fieldn=valuen [WHERE CONDITION]
例：將表 goodsorders 中ordername為zhang的訂單金額（ordermoney）改為50
mysql> update goodsorders set ordermoney=50.00 where ordername='zhang'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from goodsorders; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | zhang | 2021-05-12 | 50.00 | 1 | | zhang1 | 2021-05-12 | 1001.00 | 11 | +-----------+------------+------------+--------------+ 2 rows in set (0.00 sec)
更新時(shí)，如遇到錯(cuò)誤代碼1175：
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences
解決方法：
1、先進(jìn)行狀體查詢：
show variables like 'SQL_SAFE_UPDATES';
2、執(zhí)行下面的sql，關(guān)閉safe-updates模式：
SET SQL_SAFE_UPDATES = 0;
或者
SET SQL_SAFE_UPDATES = false;
4.4、刪除記錄命名
DELETE FROM tablename [WHERE CONDITION]
例：將表 goodsorders 中ordername為zhang1的記錄全部刪除
mysql> delete from goodsorders where ordername = 'zhang1'; Query OK, 1 row affected (0.06 sec) mysql> select * from goodsorders; +-----------+------------+------------+--------------+ | ordername | createtime | ordermoney | ordernumbers | +-----------+------------+------------+--------------+ | zhang | 2021-05-12 | 50.00 | 1 | +-----------+------------+------------+--------------+ 1 row in set (0.02 sec)
4.5、初始化表
例：將表中的所有數(shù)據(jù)清空
mysql> select * from varc; +------+------+ | v | c | +------+------+ | abc | abc | +------+------+ 1 row in set (0.03 sec) mysql> truncate table varc; Query OK, 0 rows affected (0.25 sec) mysql> select * from varc; Empty set (0.00 sec)
5、DCL 語(yǔ)句
DCL語(yǔ)句主要是為了管理數(shù)據(jù)庫(kù)系統(tǒng)中的操作對(duì)象權(quán)限
5.1創(chuàng)建數(shù)據(jù)庫(kù)用戶
例：創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)用戶 user1,初始密碼為123，具有對(duì) ordermanage 數(shù)據(jù)庫(kù)中所有表的 SELECT/INSERT 權(quán)限：
mysql> grant select,insert on ordermanage.* to 'user1'@'localhost' identified by '123'; Query OK, 0 rows affected, 1 warning (0.06 sec) mysql> exit Bye C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uuser1 -p123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 82 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ordermanage | +--------------------+ 2 rows in set (0.00 sec)
在此基礎(chǔ)上，將此用戶（user1）的insert權(quán)限進(jìn)行收回
mysql> revoke insert on ordermanage.* from 'user1'@'localhost';
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uuser1 -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 84
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use ordermanage;
Database changed
mysql> insert into member values('11','ss');
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'member'
mysql>
由此可以看出插入權(quán)限不足，插入失敗
到此這篇關(guān)于MYSQL表的介紹的文章就介紹到這了,更多相關(guān)MYSQL表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql中提高Order by語(yǔ)句查詢效率的兩個(gè)思路分析
在MySQL數(shù)據(jù)庫(kù)中，Order by語(yǔ)句的使用頻率是比較高的。但是眾所周知，在使用這個(gè)語(yǔ)句時(shí)，往往會(huì)降低數(shù)據(jù)查詢的性能。2011-03-03
MySQL長(zhǎng)字符截?cái)嗟膶?shí)現(xiàn)示例
本文主要介紹了MySQL長(zhǎng)字符截?cái)嗟膶?shí)現(xiàn)示例，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
在我們使用mysql的時(shí)候,對(duì)字段的選用以及具體使用什么類型會(huì)很有疑問(wèn),下面這篇文章主要給大家介紹了關(guān)于MySQL?text能存多少個(gè)字符的相關(guān)資料,需要的朋友可以參考下2023-01-01
這篇文章主要介紹了MySQL排序與分頁(yè)講解，使用 ORDER BY 對(duì)查詢到的數(shù)據(jù)進(jìn)行排序操作，按照dept_id的降序排列，salary的升序排列相關(guān)展開(kāi)文章，需要的小伙伴可以參考一下2022-01-01
MySql逗號(hào)分割的字段數(shù)據(jù)分解為多行代碼示例
逗號(hào)分割的字符串可以作為分組數(shù)據(jù)的標(biāo)識(shí)符,用于對(duì)數(shù)據(jù)進(jìn)行分組和聚合操作,下面這篇文章主要給大家介紹了關(guān)于MySql逗號(hào)分割的字段數(shù)據(jù)分解為多行的相關(guān)資料,需要的朋友可以參考下2023-12-12
CentOS7環(huán)境下安裝MySQL5.5數(shù)據(jù)庫(kù)
大家好，本篇文章主要講的是CentOS7環(huán)境下安裝MySQL5.5數(shù)據(jù)庫(kù)，感興趣的同學(xué)趕快來(lái)看一看吧，對(duì)你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
MySQL使用MD5加密算法進(jìn)行數(shù)據(jù)加密功能
在現(xiàn)代的數(shù)據(jù)庫(kù)應(yīng)用中,數(shù)據(jù)的安全性和隱私性變得尤為重要,MySQL作為最流行的關(guān)系型數(shù)據(jù)庫(kù)之一,提供了多種加密功能,允許用戶對(duì)數(shù)據(jù)進(jìn)行加密和解密操作,在這篇文章中,我們將深入探討MySQL的加密功能,并重點(diǎn)介紹如何使用MD5加密算法進(jìn)行加密,需要的朋友可以參考下2024-12-12
MySQL INNER JOIN 的底層實(shí)現(xiàn)原理分析
這篇文章主要介紹了MySQL INNER JOIN 的底層實(shí)現(xiàn)原理,INNER JOIN的工作分為篩選和連接兩個(gè)步驟，連接時(shí)可以使用多種算法，通過(guò)本文，我們深入了解了MySQL中INNER JOIN的底層實(shí)現(xiàn)原理，需要的朋友可以參考下2023-06-06
使用logrotete定時(shí)切割mysql的慢日志操作
本文介紹了如何在Linux系統(tǒng)中使用logrotate工具對(duì)MySQL慢查詢?nèi)罩具M(jìn)行自動(dòng)輪轉(zhuǎn)和管理配置,包括創(chuàng)建配置文件、設(shè)置輪轉(zhuǎn)頻率、文件命名、權(quán)限、執(zhí)行腳本以及測(cè)試配置的方法,感興趣的朋友一起看看吧2025-03-03
MySQL如何基于Explain關(guān)鍵字優(yōu)化索引功能
這篇文章主要介紹了MySQL如何基于Explain關(guān)鍵字優(yōu)化索引功能,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-10-10