1、創(chuàng)建表
1.1、創(chuàng)建表基本語法
|
1
2
|
create table tablename (column_name_1 column_type_1 constraints，column_name_2 column_type_2 constraints ， ……） |
column_name 是列的名字
column_type 是列的數(shù)據(jù)類型
contraints 是這個列的約束條件
1.1.1、創(chuàng)建一張簡單的表
|
1
2
|
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)化定義：
|
1
2
3
4
5
6
7
8
9
10
|
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ì)的表定義：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> show create table orders \g;*************************** 1. row *************************** table: orderscreate 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=latin11 row in set (0.00 sec)error:no query specified |
由此可以看到表的 engine（存儲引擎）是innodb
charset（字符集）是latin1
“\g”選項的含義是使得記錄能夠按照字段豎著排列，對于內(nèi)容比較長的記錄更易于顯示。
2、刪除表
命令：
|
1
|
drop table tablename |
刪除orders：
|
1
2
3
|
mysql> drop table orders -> ;query ok, 0 rows affected (0.14 sec) |
3、修改表
3.1、修改表類型命令
|
1
|
alter table tablename modify [column] column_definition [first | after col_name] |
例：修改表 orders 的 name 字段定義，將 varchar(10)改為 varchar(20) ：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table orders modify ordername varchar(20);query ok, 0 rows affected (0.11 sec)records: 0 duplicates: 0 warnings: 0mysql> 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、字段改名命令
|
1
2
|
alter table tablename change [column] old_col_name column_definition[first|after col_name] |
例：表 orders 上將ordernumber修改為ordernumbers
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table orders change column ordernumber ordernumbers int(4);query ok, 0 rows affected (0.06 sec)records: 0 duplicates: 0 warnings: 0mysql> 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) |
特別說明：change 和 modify 都可以修改表的定義，不同的是 change 后面需要寫兩次列名，不方便。但是 change 的優(yōu)點是可以修改列名稱，modify 則不能。
3.3、增加表字段命令
|
1
|
alter table tablename add [column] column_definition [first | after col_name] |
例：表 orders 上新增加字段 username，類型為 varchar(3) ：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> alter table orders add column username varchar(30);query ok, 0 rows affected (0.39 sec)records: 0 duplicates: 0 warnings: 0mysql> 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、刪除表列字段命令
|
1
|
alter table tablename drop [column] col_name |
例：表 orders 上刪除字段 username：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table orders drop column username;query ok, 0 rows affected (0.53 sec)records: 0 duplicates: 0 warnings: 0mysql> 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、表改名命令
|
1
|
alter table tablename rename [to] new_tablename |
例：表 orders 名字改為goodsorders
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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 existmysql> 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 語句
插入（insert）、查詢（select）、更新（update）、刪除（delete）
4.1、插入記錄 命令
|
1
|
insert into tablename (field1,field2,……fieldn) values(value1,value2,……valuesn); |
例：表 goodsorders 中插入一條記錄，ordername 為zhang，createtime為2021-05-12，ordermoney為100.00,ordernumbers為：1
|
1
2
|
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)這一部分
|
1
2
|
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>
1
select * from tablename [where condition]
|
1
|
select * from tablename [where condition] |
例：查看goodsorders中所有插入數(shù)據(jù)
|
1
2
3
4
5
6
7
8
|
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) |
其中“*”表示要將所有的記錄都選出來
4.2.2、查詢不重復(fù)記錄命令關(guān)鍵字
|
1
|
distinct |
例：查詢非goodsorders中非重復(fù)創(chuàng)建時間（createtime）的數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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ù)據(jù)2021-03-11去掉了
4.2.3、多條件查詢關(guān)鍵字
where 后面的條件是一個字段的‘='比較，還可以使用>、<、>=、<=、!=等比較運(yùn)算符；
多個條件之間還可以使用 or、and 等邏輯運(yùn)算符進(jìn)行多條件聯(lián)合查詢，
例：查詢非goodsorders中 ordername='li'并且createtime為2020-03-11
|
1
2
3
4
5
6
7
|
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、排序查詢命名
|
1
2
|
select * from tablename [where condition] [order by field1 [desc|asc] ， field2 [desc|asc]，……fieldn [desc|asc]] |
例：把 goodsorders表中的記錄按照創(chuàng)建時間高低進(jìn)行排序顯示
|
1
2
3
4
5
6
7
8
9
10
11
|
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、顯示一部分，而不是全部，指令
|
1
|
select ……[limit offset_start,row_count] |
offset_start 表示記錄的起始偏移量
row_count 表示顯示的行數(shù)
例如1：顯示 goodsorders表中按照 createtiem 排序后的前 3 條記錄：
|
1
2
3
4
5
6
7
8
9
|
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 排序后 從第二條記錄開始，顯示3條數(shù)據(jù)：
|
1
2
3
4
5
6
7
8
9
|
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)計數(shù)據(jù)，聚合指令
|
1
2
3
4
5
6
|
select [field1,field2,……fieldn] fun_name from tablename[where where_contition][group by field1,field2,……fieldn[with rollup]][having where_contition] |
參數(shù)說明：
-
1、
fun_name表示要做的聚合操作，也就是聚合函數(shù)，常用的有sum（求和）、count(*)（記錄數(shù)）、max（最大值）、min（最小值） -
2、
group by關(guān)鍵字表示要進(jìn)行分類聚合的字段，比如要按照部門分類統(tǒng)計員工數(shù)量，部門就應(yīng)該寫在groupby 后面。 -
3、
with rollup是可選語法，表明是否對分類聚合后的結(jié)果進(jìn)行再匯總。 -
4、
having關(guān)鍵字表示對分類后的結(jié)果再進(jìn)行條件的過濾。
注意：having 和 where 的區(qū)別在于 having 是對聚合后的結(jié)果進(jìn)行條件的過濾，而 where 是在聚合前就對記錄進(jìn)行過濾，如果邏輯允許，我們盡可能用 where 先過濾記錄，這樣因為結(jié)果集減小，將對聚合的效率大大提高，最后再根據(jù)邏輯看是否用 having 進(jìn)行再過濾。
例1：查詢統(tǒng)計goodsorders表中，記錄總數(shù)
|
1
2
3
4
5
6
7
|
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)計
|
1
2
3
4
5
6
7
8
9
10
|
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)計，又要計算總數(shù)
|
1
2
3
4
5
6
7
8
9
10
11
|
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)計，并且數(shù)量大于1
|
1
2
3
4
5
6
7
|
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）的總額、最低額、最高額
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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、左連接：包含所有的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄；關(guān)鍵指令：
left join -
2、右連接：包含所有的右邊表中的記錄甚至是左邊表中沒有和它匹配的記錄；關(guān)聯(lián)指令：
right join
例1：現(xiàn)在我們又創(chuàng)建一張用戶表（member）,使用goodorders進(jìn)行左連接，查詢關(guān)聯(lián)的用戶表信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
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ù)不變，我們再來看一下右連接的查詢，以及結(jié)果：
|
1
2
3
4
5
6
7
8
9
10
11
|
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表中的記錄
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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)合，指令
|
1
2
3
4
5
6
|
select * from t1union|union allselect * from t2……union|union allselect * from tn; |
union 和 union all 的主要區(qū)別：
union all 是把結(jié)果集直接合并在一起，
union 是將union all 后的結(jié)果進(jìn)行一次 distinct，去除重復(fù)記錄后的結(jié)果。
例1：將member表和goodsorders表中的用戶編號id（memberid）的集合顯示出來
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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ù)記錄后顯示
|
1
2
3
4
5
6
7
8
9
10
|
mysql> select memberid from goodsorders union select id from member;+----------+| memberid |+----------+| 15 || 1 || 3 || 13 |+----------+4 rows in set (0.00 sec) |
4.3、更新記錄命令
|
1
|
update tablename set field1=value1，field2.=value2，……fieldn=valuen [where condition] |
例：將表 goodsorders 中ordername為zhang的訂單金額（ordermoney）改為50
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> update goodsorders set ordermoney=50.00 where ordername='zhang';query ok, 1 row affected (0.09 sec)rows matched: 1 changed: 1 warnings: 0mysql> 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) |
更新時，如遇到錯誤代碼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)行狀體查詢：
|
1
|
show variables like 'sql_safe_updates'; |
2、執(zhí)行下面的sql，關(guān)閉safe-updates模式：
|
1
|
set sql_safe_updates = 0; |
或者
|
1
2
|
set sql_safe_updates = false; |
4.4、刪除記錄命名
|
1
|
delete from tablename [where condition] |
例：將表 goodsorders 中ordername為zhang1的記錄全部刪除
|
1
2
3
4
5
6
7
8
9
10
11
|
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ù)清空
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 語句
dcl語句主要是為了管理數(shù)據(jù)庫系統(tǒng)中的操作對象權(quán)限
5.1創(chuàng)建數(shù)據(jù)庫用戶
例：創(chuàng)建一個數(shù)據(jù)庫用戶 user1,初始密碼為123，具有對 ordermanage 數(shù)據(jù)庫中所有表的 select/insert 權(quán)限：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
mysql> grant select,insert on ordermanage.* to 'user1'@'localhost' identified by '123';query ok, 0 rows affected, 1 warning (0.06 sec)mysql> exitbyec:\program files\mysql\mysql server 5.7\bin>mysql -uuser1 -p123mysql: [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 82server 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 itsaffiliates. other names may be trademarks of their respectiveowners.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)行收回
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> revoke insert on ordermanage.* from 'user1'@'localhost';query ok, 0 rows affected (0.02 sec)mysql> exitbyec:\program files\mysql\mysql server 5.7\bin>mysql -uuser1 -p123mysql: [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 84server 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 itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> use ordermanage;database changedmysql> 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)容請搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！
原文鏈接：https://www.cnblogs.com/19930521zhang/p/14756371.html
