无码人妻精品一区二区,玩弄少妇人妻中文字幕

4.2.1、查詢?nèi)?/li>
4.2.2、查詢不重復(fù)記錄命令關(guān)鍵字
4.2.3、多條件查詢關(guān)鍵字
4.2.4、排序查詢命名
4.2.5、顯示一部分，而不是全部，指令
4.2.6、統(tǒng)計(jì)數(shù)據(jù)，聚合指令
4.2.7、表連接
4.2.8、子查詢，相關(guān)關(guān)鍵字
4.2.9、記錄聯(lián)合，指令

4.3、更新記錄命令

4.4、刪除記錄命名

4.5、初始化表

5、DCL 語句

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

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)的含義是使得記錄能夠按照字段豎著排列，對于內(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 是可選語法，表明是否對分類聚合后的結(jié)果進(jìn)行再匯總。
4、HAVING 關(guān)鍵字表示對分類后的結(jié)果再進(jìn)行條件的過濾。

注意：having 和 where 的區(qū)別在于 having 是對聚合后的結(jié)果進(jìn)行條件的過濾，而 where 是在聚合前就對記錄進(jìn)行過濾，如果邏輯允許，我們盡可能用 where 先過濾記錄，這樣因?yàn)榻Y(jié)果集減小，將對聚合的效率大大提高，最后再根據(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)中的操作對象權(quán)限

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

例：創(chuàng)建一個(gè)數(shù)據(jù)庫用戶 user1,初始密碼為123，具有對 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)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。

相關(guān)文章

mysql學(xué)習(xí)筆記之表的基本操作

centos編譯安裝mysql 5.6及安裝多個(gè)mysql實(shí)例詳解

mysql 5.7.11 winx64.zip安裝配置方法圖文教程

mysql 5.7.17 winx64.zip安裝配置方法圖文教程

CentOS安裝mysql5.7 及簡單配置教程詳解

MySQL 5.7 zip版本(zip版)安裝配置步驟詳解

MySQL5.6.31 winx64.zip 安裝配置教程詳解

MySQL注入繞開過濾的技巧總結(jié)

一次Mysql死鎖排查過程的全紀(jì)錄

Windows10 64位安裝MySQL5.6.35的圖文教程

服務(wù)器配置數(shù)據(jù)庫配置服務(wù)器運(yùn)維服務(wù)器安全
互聯(lián)網(wǎng)資訊服務(wù)器公司 IDC數(shù)據(jù)中心
免費(fèi)服務(wù)器香港服務(wù)器租用美國服務(wù)器租用

五月综合激情婷婷六月,日韩欧美国产一区不卡,他扒开我内裤强吻我下面视频 ,无套内射无矿码免费看黄,天天躁,日日躁,狠狠躁

公司動(dòng)態(tài)

產(chǎn)品資訊

行業(yè)資訊

MYSQL 表的全面總結(jié)

目錄

1、創(chuàng)建表

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

1.1.1、創(chuàng)建一張簡單的表

1.1.2、查看創(chuàng)建表定義

2、刪除表

3、修改表

3.1、修改表類型命令

3.2、字段改名命令

3.3、增加表字段命令

3.4、刪除表列字段命令

3.5、表改名命令

4、DML 語句

4.1、插入記錄命令

4.2、查看插入數(shù)據(jù)命令

4.2.2、查詢不重復(fù)記錄命令關(guān)鍵字

4.2.3、多條件查詢關(guān)鍵字

4.2.4、排序查詢命名

4.2.5、顯示一部分，而不是全部，指令

4.2.6、統(tǒng)計(jì)數(shù)據(jù)，聚合指令

4.2.7、表連接

4.2.8、子查詢，相關(guān)關(guān)鍵字

4.2.9、記錄聯(lián)合，指令

4.3、更新記錄命令

4.4、刪除記錄命名

4.5、初始化表

5、DCL 語句

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

深入淺析SQL中的group by 和 having 用法

SQL Server ltrim(rtrim()) 去不掉空格的原因分析

實(shí)時(shí)開通

免備案

全天候客戶服務(wù)

專屬顧問服務(wù)

五月综合激情婷婷六月,日韩欧美国产一区不卡,他扒开我内裤强吻我下面视频 ,无套内射无矿码免费看黄,天天躁,日日躁,狠狠躁

公司動(dòng)態(tài)

產(chǎn)品資訊

行業(yè)資訊

MYSQL 表的全面總結(jié)

目錄

1、創(chuàng)建表

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

1.1.1、創(chuàng)建一張簡單的表

1.1.2、查看創(chuàng)建表定義

2、刪除表

3、修改表

3.1、修改表類型命令

3.2、字段改名命令

3.3、增加表字段命令

3.4、刪除表列字段命令

3.5、表改名命令

4、DML 語句

4.1、插入記錄 命令

4.2、查看插入數(shù)據(jù)命令

4.2.2、查詢不重復(fù)記錄命令關(guān)鍵字

4.2.3、多條件查詢關(guān)鍵字

4.2.4、排序查詢命名

4.2.5、顯示一部分，而不是全部，指令

4.2.6、統(tǒng)計(jì)數(shù)據(jù)，聚合指令

4.2.7、表連接

4.2.8、子查詢，相關(guān)關(guān)鍵字

4.2.9、記錄聯(lián)合，指令

4.3、更新記錄命令

4.4、刪除記錄命名

4.5、初始化表

5、DCL 語句

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

深入淺析SQL中的group by 和 having 用法

SQL Server ltrim(rtrim()) 去不掉空格的原因分析

實(shí)時(shí)開通

免備案

全天候客戶服務(wù)

專屬顧問服務(wù)

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

1.1.1、創(chuàng)建一張簡單的表

1.1.2、查看創(chuàng)建表定義

2、刪除表

3、修改表

3.2、字段改名命令

3.3、增加表字段命令

3.4、刪除表列字段命令

4、DML 語句

4.1、插入記錄命令

4.2.2、查詢不重復(fù)記錄命令關(guān)鍵字

4.2.3、多條件查詢關(guān)鍵字

4.2.4、排序查詢命名

4.2.5、顯示一部分，而不是全部，指令

4.2.6、統(tǒng)計(jì)數(shù)據(jù)，聚合指令

4.2.7、表連接

4.2.8、子查詢，相關(guān)關(guān)鍵字

4.2.9、記錄聯(lián)合，指令

4.4、刪除記錄命名

5、DCL 語句