溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
SQL語句之DWL、DCL語句
===============================================================================
概述：
本章將主要介紹SQL語句中的DWL和DCL簡單語句的使用方法，具體內容如下：
DWL語句的INSERT（增），DELETE（刪），UPDATE（改），SELECT（查）
SELECT單表查詢語句詳解；
·SELECT語句的執行流程：
·字段可以使用別名
·WHERE子句：
·GROUP BY,分組
·聚合函數
·HAVING,對分組聚合后的結果進行條件過濾
DCL語句的簡單使用；
·GRANT（授權）, REVOKE（回收權限）
===============================================================================
---INSERT，SELECT，DELETE，UPDATE
1.INSERT 增加插入數據
★語法：
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
演示：
MariaDB [mydb]> desc tbl1;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| ClassID | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('F','M') | YES | | M | |
+---------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
MariaDB [mydb]> insert into tbl1 (name,ClassID) value ('tom',1); # 新增一個叫tom在1
班的同學
Query OK, 1 row affected (0.02 sec)
MariaDB [mydb]> select * from tbl1; # 查看如下：
+----+------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+------+------+---------+--------+
| 1 | tom | NULL | 1 | M |
+----+------+------+---------+--------+
1 row in set (0.00 sec)
MariaDB [mydb]> insert into tbl1 (name,ClassID) value ('tao',1),('xiu',2); # 可以一次增加多個
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [mydb]> select * from tbl1;
+----+------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+------+------+---------+--------+
| 1 | tom | NULL | 1 | M |
| 2 | tao | NULL | 1 | M |
| 3 | xiu | NULL | 2 | M |
+----+------+------+---------+--------+
3 rows in set (0.00 sec)
MariaDB [mydb]> insert into tbl1 value (4,'wang',25,5,default); # 直接指定值增加，但是每個字段都必須有值
Query OK, 1 row affected (0.02 sec)
MariaDB [mydb]> select * from tbl1;
+----+------+------+---------+--------+
| id | name | age | ClassID | gender |
+----+------+------+---------+--------+
| 1 | tom | NULL | 1 | M |
| 2 | tao | NULL | 1 | M |
| 3 | xiu | NULL | 2 | M |
| 4 | wang | 25 | 5 | M |
+----+------+------+---------+--------+
4 rows in set (0.00 sec) 2.DELETE 刪除數據
★語法：
☉DELETE FROM tbl_name
[WHERE where_condition]
演示：
MariaDB [mydb]> select * from tbl1; +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 4 rows in set (0.00 sec) MariaDB [mydb]> delete from tbl1 where ClassID = 5; # 刪除ClassID為5的行 Query OK, 1 row affected (0.00 sec) MariaDB [mydb]> select * from tbl1; +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | +----+------+------+---------+--------+ 3 rows in set (0.00 sec)
3.UPDATE 修改數據
★語法：
☉UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
演示：
MariaDB [mydb]> update tbl1 set age=18 where id=2; # 修改id=2的行age=18 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [mydb]> select * from tbl1; +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | 18 | 1 | M | | 3 | xiu | NULL | 2 | M | +----+------+------+---------+--------+ 3 rows in set (0.00 sec) MariaDB [mydb]> update tbl1 set age=18,ClassID=3 where id=1; #修改id=2的行age=18，ClassID=3 Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [mydb]> select * from tbl1; +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | 18 | 3 | M | | 2 | tao | 18 | 1 | M | | 3 | xiu | NULL | 2 | M | +----+------+------+---------+--------+ 3 rows in set (0.00 sec)
4.SELECT：查詢
★Query Cache：緩存查詢的執行結果；
☉key：查詢語句的hash值；
☉value：查詢語句的執行結果；
☉SQL語句的編寫方式：
要養成同一種風格書寫方式，要大寫都大寫，要小寫都小寫，方便查詢緩存；
★查詢執行路徑：
請求-->查詢緩存
請求-->查詢緩存-->解析器-->預處理器-->優化器-->查詢執行引擎-->存儲引擎-->緩存-->響應
★SELECT語句的執行流程：
★單表查詢：
☉SELECT
☉語句用法：
◆SELECT * FROM tbl_name;
查詢一個指定表的所有行（生產環境中一定不可用）；
◆SELECT col1, col2, ... FROM tble_name；
查詢指定表符合條件的字段，（字段為所有行的字段）
字段別名：col1 AS ALIAS
◆SELECT col1, col2, ... FROM tble_name WHERE clause；
以指定條件過濾行之后再挑選字段
◆SELECT col1, col2, ... FROM tble_name [WHERE clause] GROUP BY col_name [HAVING clause]；
以指定的字段進行分組，分完組之后聚合，然后對聚合后的結果使用having子句進行過濾；
◆SELECT col1, ... FROM tbl1_name [WEHRE CLAUSE] ORDER BY col1, col2, ... [ASC|DESC]
查詢后以指定字段排序（默認為升序）
★選項：
☉DISTINCT：數據去重；
☉SQL_CACHE：顯式指定緩存查詢語句的結果；
☉SQL_NO_CACHE：顯式指定不緩存查詢語句的結果；
☉query_cache_type服務器變量有三個值：
ON：啟用；
SQL_NO_CACHE：不緩存；默認符合緩存條件都緩存；
OFF：關閉；
DEMAND：按需緩存；
SQL_CACHE：緩存；默認不緩存；
☉字段可以使用別名 ：
col1 AS alias1, col2 AS alias2, ...
☉WHERE子句：指明過濾條件以實現“選擇”功能；
◆過濾條件：布爾型表達式；
◆[WHERE where_condition]
算術操作符：+, -, *, /, %
比較操作符：=, <>, !=, <=>, >, >=, <, <=
·IS NULL， IS NOT NULL
·區間：BETWEEN min AND max
·IN(list)：列表；
·LIKE 'PATTERN'：模糊比較
通配符：
%：任意長度的任意字符；
_；匹配任意單個字符；
·RLIKE或REGEXP
邏輯操作符：AND， OR， NOT， XOR
☉GROUP BY：
根據指定的字段把查詢的結果進行“分組”以用于“聚合”運算；
avg(), max(), min(), sum(), count()
☉HAVING：
對分組聚合后的結果進行條件過濾；
☉ORDER BY：根據指定的字段把查詢的結果進行排序；
升序：ASC
降序：DESC
☉LIMIT：對輸出結果進行數量限制
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
演示：
1.select查詢指定的字段和行
MariaDB [mydb]> select * from tbl1; # 查詢指定表的所有行 +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 4 rows in set (0.00 sec) MariaDB [mydb]> select name,gender from tbl1; # 查詢指定表符合條件的字段 +------+--------+ | name | gender | +------+--------+ | tom | M | | tao | M | | xiu | M | | wang | M | +------+--------+ 4 rows in set (0.00 sec) MariaDB [mydb]> select name AS StuName,gender from tbl1; # 定義字段別名 +---------+--------+ | StuName | gender | +---------+--------+ | tom | M | | tao | M | | xiu | M | | wang | M | +---------+--------+ 4 rows in set (0.00 sec) MariaDB [mydb]>
2.where 子句演示：Classe
MariaDB [mydb]> select * from tbl1; +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 4 rows in set (0.00 sec) MariaDB [mydb]> select * from tbl1 where ClassID > 2; # 查詢ClassID大于2的 +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 1 row in set (0.00 sec) MariaDB [mydb]> select * from tbl1 where ClassID >= 2 and ClassID <=5; # 組合條件查詢班號大于2小于5的 +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 2 rows in set (0.00 sec) MariaDB [mydb]> select * from tbl1 where ClassID between 2 and 5; # 同上，另一種寫法between...and.. +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 2 rows in set (0.00 sec) MariaDB [mydb]> select name from tbl1 where name like '%o%'; # 匹配name中帶o的名字 +------+ | name | +------+ | tom | | tao | +------+ 2 rows in set (0.01 sec) MariaDB [mydb]> select name from tbl1 where name like '%u'; # 匹配name中以u結尾的名字 +------+ | name | +------+ | xiu | +------+ 1 row in set (0.00 sec) MariaDB [mydb]> select name from tbl1 where name rlike '^.*o.*$';# 正則表達式匹配名字中帶o的 +------+ | name | +------+ | tom | | tao | +------+ 2 rows in set (0.00 sec) MariaDB [mydb]> select name,ClassID from tbl1 where ClassID in (1,2); # 在給定列表元素中查找 +------+---------+ | name | ClassID | +------+---------+ | tom | 1 | | tao | 1 | | xiu | 2 | +------+---------+ 3 rows in set (0.00 sec)
3.排序
MariaDB [mydb]> select * from tbl1; +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 4 rows in set (0.00 sec) MariaDB [mydb]> select * from tbl1 order by ClassID; # 按 ClassID 升序排序 +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 4 rows in set (0.01 sec) MariaDB [mydb]> select * from tbl1 order by ClassID,name; # 如果ClassID相同就按name升序排列 +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 2 | tao | NULL | 1 | M | | 1 | tom | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 4 rows in set (0.03 sec) MariaDB [mydb]> select * from tbl1 order by ClassID,name DESC;# 如果ClassID相同就按name降序排列 +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +----+------+------+---------+--------+ 4 rows in set (0.02 sec) MariaDB [mydb]> select * from tbl1 order by ClassID DESC; # 按ClassID降序排列 +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 4 | wang | 25 | 5 | M | | 3 | xiu | NULL | 2 | M | | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | +----+------+------+---------+--------+ 4 rows in set (0.00 sec) # 按ClassID降序排列，如果相同就按name降序排列 MariaDB [mydb]> select * from tbl1 order by ClassID DESC,name DESC; +----+------+------+---------+--------+ | id | name | age | ClassID | gender | +----+------+------+---------+--------+ | 4 | wang | 25 | 5 | M | | 3 | xiu | NULL | 2 | M | | 2 | tao | NULL | 1 | M | | 1 | tom | NULL | 1 | M | +----+------+------+---------+--------+ 4 rows in set (0.00 sec)
--------------------------------------------------------------------------------
1.select挑選字段和where挑選行
MariaDB [testdb]> show tables; +------------------+ | Tables_in_testdb | +------------------+ | tbl1 | | tbl2 | +------------------+ 2 rows in set (0.00 sec) MariaDB [testdb]> select id from tbl2; # select僅挑選字段 +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) MariaDB [testdb]> select * from tbl2; # 顯示所有的字段 +------+------+------+ | id | name | age | +------+------+------+ | 1 | tom | 21 | | 2 | tao | 15 | | 3 | jing | 22 | +------+------+------+ 3 rows in set (0.00 sec) MariaDB [testdb]> select * from tbl2 where age>=20; # where挑選行 +------+------+------+ | id | name | age | +------+------+------+ | 1 | tom | 21 | | 3 | jing | 22 | +------+------+------+ 2 rows in set (0.00 sec) MariaDB [testdb]> select name,age from tbl2 where age>=20; # 即挑選字段又挑選行 +------+------+ | name | age | +------+------+ | tom | 21 | | jing | 22 | +------+------+
2.對挑選出的數據分組 Group By，分組的目的在于聚合
MariaDB [testdb]> alter table tbl2 add gender enum('F','M'); #增加一個gender字段
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [testdb]> select * from tbl2;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | tom | 21 | NULL |
| 2 | tao | 15 | NULL |
| 3 | jing | 22 | NULL |
+------+------+------+--------+
3 rows in set (0.00 sec)
MariaDB [testdb]> update tbl2 set gender='M' where id=1; # 設定其值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testdb]> update tbl2 set gender='M' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testdb]> update tbl2 set gender='F' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testdb]> select * from tbl2;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | tom | 21 | M |
| 2 | tao | 15 | M |
| 3 | jing | 22 | F |
+------+------+------+--------+
3 rows in set (0.00 sec)
MariaDB [testdb]> select * from tbl2 group by gender; # 按性別對tbl2進行分組（有幾個值就分多少組）
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 3 | jing | 22 | F |
| 1 | tom | 21 | M |
+------+------+------+--------+
2 rows in set (0.01 sec)
MariaDB [testdb]> select avg(age),gender from tbl2 group by gender; # 對各分組求其平均值
+----------+--------+
| avg(age) | gender |
+----------+--------+
| 22.0000 | F |
| 18.0000 | M |
+----------+--------+
2 rows in set (0.00 sec)
MariaDB [testdb]> select sum(age),gender from tbl2 group by gender; # 對各分組求和
+----------+--------+
| sum(age) | gender |
+----------+--------+
| 22 | F |
| 36 | M |
+----------+--------+
2 rows in set (0.00 sec)
MariaDB [testdb]> select avg(age) as avg_age,gender from tbl2 group by gender; #對avg_age定義別名
+---------+--------+
| avg_age | gender |
+---------+--------+
| 22.0000 | F |
| 18.0000 | M |
+---------+--------+
2 rows in set (0.01 sec)3.對聚合后的結果指明過濾條件HAVING
MariaDB [testdb]> select avg(age) as avg_age,gender from tbl2 group by gender having avg_age >= 20; +---------+--------+ | avg_age | gender | +---------+--------+ | 22.0000 | F | +---------+--------+ 1 row in set (0.01 sec)
4.數據去重
MariaDB [testdb]> select gender from tbl2; +--------+ | gender | +--------+ | M | | M | | F | +--------+ 3 rows in set (0.00 sec) MariaDB [testdb]> select distinct gender from tbl2; # 去重 +--------+ | gender | +--------+ | M | | F | +--------+ 2 rows in set (0.00 sec) MariaDB [testdb]> SHOW VARIABLES LIKE '%cache%'; # 和緩存cache相關的參數 +-------------------------------+----------------------+ | Variable_name | Value | +-------------------------------+----------------------+ | aria_pagecache_age_threshold | 300 | | aria_pagecache_buffer_size | 134217728 | | aria_pagecache_division_limit | 100 | | binlog_cache_size | 32768 | | binlog_stmt_cache_size | 32768 | | have_query_cache | YES | | join_cache_level | 2 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | key_cache_segments | 0 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_stmt_cache_size | 18446744073709547520 | | metadata_locks_cache_size | 1024 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | # 為0，沒有緩存空間 | query_cache_strip_comments | OFF | | query_cache_type | ON | # 緩存為開啟狀態 | query_cache_wlock_invalidate | OFF | | stored_program_cache | 256 | | table_definition_cache | 400 | | table_open_cache | 400 | | thread_cache_size | 0 | +-------------------------------+----------------------+ 24 rows in set (0.00 sec)
=============================================================================
GRANT（授權）, REVOKE（回收權限）
命令演示：
1.授權一個用戶僅允許本地登錄
[root@centos7 bbs]# mysql -p134296 # 不指定用戶，默認就是使用root用戶登錄mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE ultrax; # 創建數據庫 Query OK, 1 row affected (0.00 sec) # 授權用戶名為ultraxuser,僅允許本地登錄訪問ultrax數據庫，密碼為134296 MariaDB [(none)]> GRANT ALL ON ultrax.* TO 'ultraxuser'@'localhost' IDENTIFIED BY '134296'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES; # 重載權限表 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
2.授權一個可遠程登錄的用戶和主機
[root@centos7 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # 授權的用戶為test，主機為10.1網段中的任何主機，允許訪問所有的數據庫和表，密碼為testpass MariaDB [(none)]> GRANT all ON *.* TO 'test'@'10.1.%.%' IDENTIFIED BY 'testpass'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> create database mydb; # 創建一個數據庫 Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec) MariaDB [(none)]> exit Bye
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
