欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MYSQL 表的全面總結(jié)

 更新時(shí)間：2021年11月09日 15:16:27   作者：隨風(fēng)去遠(yuǎn)方  
這篇文章主要介紹了MYSQL表，文章主要圍繞MySQL表的相關(guān)資料如創(chuàng)建表、刪除表、修改表、等展開內(nèi)容,需要的朋友可以參考一下，希望對(duì)你有所幫助

1、創(chuàng)建表

1.1、創(chuàng)建表基本語法

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)建一張簡單的表

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)容比較長的記錄更易于顯示。

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)

特別說明：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 語句

插入（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]

例：查看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)

其中“*”表示要將所有的記錄都選出來

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 排序后 從第二條記錄開始，顯示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ù)說明：

  • 1、fun_name 表示要做的聚合操作，也就是聚合函數(shù)，常用的有 sum（求和）、count(*)（記錄數(shù)）、max（最大值）、min（最小值）
  • 2、GROUP BY 關(guān)鍵字表示要進(jìn)行分類聚合的字段，比如要按照部門分類統(tǒng)計(jì)員工數(shù)量，部門就應(yīng)該寫在 group by 后面。
  • 3、WITH ROLLUP 是可選語法，表明是否對(duì)分類聚合后的結(jié)果進(jìn)行再匯總。
  • 4、HAVING 關(guān)鍵字表示對(duì)分類后的結(jié)果再進(jìn)行條件的過濾。

注意：having 和 where 的區(qū)別在于 having 是對(duì)聚合后的結(jié)果進(jìn)行條件的過濾，而 where 是在聚合前就對(duì)記錄進(jìn)行過濾，如果邏輯允許，我們盡可能用 where 先過濾記錄，這樣因?yàn)榻Y(jié)果集減小，將對(duì)聚合的效率大大提高，最后再根據(jù)邏輯看是否用 having 進(jìn)行再過濾。

例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、左連接：包含所有的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄；關(guān)鍵指令：left join
  • 2、右連接：包含所有的右邊表中的記錄甚至是左邊表中沒有和它匹配的記錄；關(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ù)不變，我們再來看一下右連接的查詢，以及結(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）的集合顯示出來

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 語句

DCL語句主要是為了管理數(shù)據(jù)庫系統(tǒng)中的操作對(duì)象權(quán)限

5.1創(chuàng)建數(shù)據(jù)庫用戶

例：創(chuàng)建一個(gè)數(shù)據(jù)庫用戶 user1,初始密碼為123，具有對(duì) ordermanage 數(shù)據(jù)庫中所有表的 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控制用戶輸錯(cuò)密碼嘗試次數(shù)

    MySQL控制用戶輸錯(cuò)密碼嘗試次數(shù)

    這篇文章主要介紹了MySQL如何控制用戶輸錯(cuò)密碼嘗試次數(shù),文中給大家提到了死鎖監(jiān)控方法及處理方案，需要的朋友可以參考下
    2019-11-11
  • MySQL優(yōu)化之如何了解SQL的執(zhí)行頻率

    MySQL優(yōu)化之如何了解SQL的執(zhí)行頻率

    MySQL 客戶端連接成功后，通過 show [session|global]status 命令 可以提供服務(wù)器狀態(tài)信息，也可以在操作系統(tǒng)上使用 mysqladmin extended-status 命令獲得這些消息
    2014-05-05
  • MySQL如何比較兩個(gè)表數(shù)據(jù)的差異

    MySQL如何比較兩個(gè)表數(shù)據(jù)的差異

    這篇文章主要介紹了MySQL比較兩個(gè)表數(shù)據(jù)的差異,這些方式可以根據(jù)具體需求和數(shù)據(jù)結(jié)構(gòu)選擇合適的方法來比較兩個(gè)表的數(shù)據(jù)差異,本文給大家介紹的非常詳細(xì),需要的朋友可以參考下
    2023-10-10
  • mysql中取字符串中的數(shù)字的語句

    mysql中取字符串中的數(shù)字的語句

    在很多時(shí)間我們需要把字符串的數(shù)字給取出來，通常大家會(huì)用php,asp等這類來操作，本文章介紹了在sql中取字符中的數(shù)字辦法，有需要的朋友可以參考一下
    2012-04-04
  • Mysql之組合索引方法詳解

    Mysql之組合索引方法詳解

    這篇文章主要介紹了Mysql之組合索引方法詳解，文中通過示例代碼和查詢結(jié)果展示介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-08-08
  • 阿里云centos7中安裝MySQL8.0.13的方法步驟

    阿里云centos7中安裝MySQL8.0.13的方法步驟

    這篇文章主要介紹了阿里云centos7中安裝MySQL8.0.13的方法步驟，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-07-07
  • MySQL核心參數(shù)優(yōu)化文件my.ini實(shí)現(xiàn)

    MySQL核心參數(shù)優(yōu)化文件my.ini實(shí)現(xiàn)

    本文主要介紹了MySQL核心參數(shù)優(yōu)化文件my.ini實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-01-01
  • MYSQL 修改root密碼命令小結(jié)

    MYSQL 修改root密碼命令小結(jié)

    MYSQL 修改root密碼命令小結(jié)，需要的朋友可以參考下。
    2011-10-10
  • mysql 動(dòng)態(tài)生成測試數(shù)據(jù)

    mysql 動(dòng)態(tài)生成測試數(shù)據(jù)

    mysql 動(dòng)態(tài)生成測試數(shù)據(jù)的語句，方便測試數(shù)據(jù)。
    2009-08-08
  • MySQL數(shù)據(jù)庫之字符集?character

    MySQL數(shù)據(jù)庫之字符集?character

    這篇文章主要介紹了MySQL數(shù)據(jù)庫之字符集?character，文章基于MySQL的的相關(guān)資料展開詳細(xì)介紹，具有一定的參考價(jià)值需要的小伙伴可以參考一下
    2022-05-05

最新評(píng)論