乾貨操作：MYSQL資料庫高階SQL語句詳解+實驗案例----------------------------下午好打工人

Hero_V發表於2020-10-14
MySql資料庫

MYSQL資料庫高階SQL語句詳解

在對 MySQL 資料庫的增、刪、改、查操作有一定了解之後，就可以學習一些 SQL 語句的高階使用方法。SQL
語句的熟練使用，在平時的運維工作中可以提供不小的幫助，尤其是在一些規模較小的公司，運維身兼數職，可能會有不少資料庫的相關工作。本章將從不
同的方面出發介紹 SQL 語句的高階運用方法。在這裡插入圖片描述

一：MyAQL進階查詢

1.1：按關鍵字排序

使用ORDERBY語句來實現排序

排序可針對一個或多個欄位

ASC:升序，預設排序方式 【升序是從小到大】

DESC:降序 【降序是從大到小】

ORDER BY的語法結構

ORDER BY後面跟欄位名

SELECT column1, column2,....FROM table_name ORDER BY column1,column2,...ASC|DESC;

1.11：按單字短排序

  • 我們這邊新建一個資料庫tt
#建立tt資料庫
mysql> cerate databases tt;

#切換到tt庫
mysql> use tt;

#建立表結構tt
mysql> create table tt (id int(10) not null primary key auto_increment,name char(20) not null,score decimal(5,2),adddress varchar（40）default '未知')engine=innodb;

#檢視錶結構
mysql> desc tt;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(10)      | NO   | PRI | NULL    | auto_increment |
| name    | char(20)     | NO   |     | NULL    |                |
| score   | decimal(5,2) | YES  |     | NULL    |                |
| address | varchar(40)  | YES  |     | 未知    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

#插入資料記錄
mysql> insert into tt (name,score,address) values ('wangwu',70,'beijing'),('lisi',90,'nanjing');

mysql> insert into tt (name,score,address) values ('zhangsan',80,'beijing'),('zhaoliu',60,'nanjing');

#檢視資料記錄
mysql> select * from tt;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | wangwu   | 70.00 | beijing |
|  2 | lisi     | 90.00 | nanjing |
|  3 | zhangsan | 80.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
+----+----------+-------+---------+
4 rows in set (0.00 sec)
  • 篩選分數大於70分的
mysql> select name,score from tt where score>=70;
+----------+-------+
| name     | score |
+----------+-------+
| wangwu   | 70.00 |
| lisi     | 90.00 |
| zhangsan | 80.00 |
+----------+-------+
3 rows in set (0.00 sec)

#篩選大於70分的進行升序排序 【對score欄位排序】
mysql> select name,score from tt where score>=70 order by score;
+----------+-------+
| name     | score |
+----------+-------+
| wangwu   | 70.00 |
| zhangsan | 80.00 |
| lisi     | 90.00 |
+----------+-------+
3 rows in set (0.00 sec)

#進行降序排序
mysql> select name,score from tt where score>=70 order by score desc;
+----------+-------+
| name     | score |
+----------+-------+
| lisi     | 90.00 |
| zhangsan | 80.00 |
| wangwu   | 70.00 |
+----------+-------+
3 rows in set (0.00 sec)

【SELECT 語句中如果沒有指定具體的排序方式，則預設按 ASC 方式進行排序。DESC 是按降序方式進行排列。當然 ORDER BY 前面也可以使用 WHERE 子句對查詢結果進一步過濾.】

1.12：按多欄位排序

  • 這邊我們再次插入新的資料內容
mysql> insert into tt (name,score,address) values ('tianqi',80,'beijing'),,('shuaige',70,'suzhou');

#查詢資料記錄
mysql> select * from tt;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | wangwu   | 70.00 | beijing |
|  2 | lisi     | 90.00 | nanjing |
|  3 | zhangsan | 80.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
|  5 | tianqi   | 80.00 | beijing |
|  6 | shuaige  | 70.00 | suzhou  |
+----+----------+-------+---------+
6 rows in set (0.00 sec)

#單欄位匹配【降序】
mysql> select id,name,score from tt where score>=70 order by score desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  2 | lisi     | 90.00 |
|  3 | zhangsan | 80.00 |
|  5 | tianqi   | 80.00 |
|  1 | wangwu   | 70.00 |
|  6 | shuaige  | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)

#修改lisi成績為80分
mysql> update tt set score=80 where id=2;

mysql> select id,name,score from tt where score>=70 order by score desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  2 | lisi     | 80.00 |
|  3 | zhangsan | 80.00 |
|  5 | tianqi   | 80.00 |
|  1 | wangwu   | 70.00 |
|  6 | shuaige  | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)

#進行多段排序
mysql> select id,name,score from tt where score>=70 order by score desc,id desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  5 | tianqi   | 80.00 |
|  3 | zhangsan | 80.00 |
|  2 | lisi     | 80.00 |
|  6 | shuaige  | 70.00 |
|  1 | wangwu   | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)
#這邊把id欄位也做了一個排序

1.2：對結果進行分組

通過 SQL 查詢出來的結果，還可以對其進行分組，使用 GROUP BY 語句來實現。

GROUP BY 從字面上看，是以 BY 後面的內容對查詢出的資料進行分組，就是將一個“資料集”劃分成若干個“小區域”，然後針對這些個“小區域”進行資料處理。

  • 使用GROUP BY語句來實現分組

  • 通常結合聚合函式一起使用

  • 可以按一個或多個欄位對結果進行分組

  • GROUP BY的語法結構

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;

1.21：常用的聚合函式包括：

  • count （欄位名稱） 計數 函式

    • count （*） 技術

    • sum （*） 求和

    • avg （*） 平均值

    • max （*） 最大

    • min （*） 最小值

1.22：GROUP BY分組

  • 統計70跟80分的人數
#查詢所有資料記錄
mysql> select * from tt;                                                 
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | wangwu   | 70.00 | beijing |
|  2 | lisi     | 80.00 | nanjing |
|  3 | zhangsan | 80.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
|  5 | tianqi   | 80.00 | beijing |
|  6 | shuaige  | 70.00 | suzhou  |
+----+----------+-------+---------+
6 rows in set (0.00 sec)

#進行分組
mysql> select count(name),score from tt where score >=70 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           2 | 70.00 |
|           3 | 80.00 |
+-------------+-------+
2 rows in set (0.00 sec)
  • 求班級的學生的平均成績
avg （*）  平均值

mysql> select avg(score) from tt;
+------------+
| avg(score) |
+------------+
|  73.333333 |
+------------+
1 row in set (0.00 sec)

1.23：GROUP BY集合ORDER BY

#分組降序
mysql> select count(name),score from tt where score >=70 group by score order by score desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           3 | 80.00 |
|           2 | 70.00 |
+-------------+-------+
2 rows in set (0.00 sec)

#分組升序
mysql> select count(name),score from tt where score >=70 group by score order by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           2 | 70.00 |
|           3 | 80.00 |
+-------------+-------+
2 rows in set (0.01 sec)

1.3：限制結果條目

在使用 MySQL SELECT 語句進行查詢時，結果集返回的是所有匹配的記錄。有時候僅需要返回第一行或者前幾行，這時候就需要用到 LIMIT 子句。

LIMIT 的第一個引數是位置偏移量（可選引數），是設定 MySQL 從哪一行開始顯示。如果不設定第一個引數，將會從表中的第一條記錄開始顯示。需要注意的是，第一條記錄的位置偏移量是 0，第二條是 1，以此類推。第二個引數是設定返回記錄行的最大數目。

  • 只返回SELECT查詢結果的第一行或前幾行

  • 使用LIMIT語句限制條目

  • LIMIT語法結構

SELECT column1,column2,...FROM table_name LIMIT[offset,] number;
number：返回記錄行的最大數目
[offset,]：位置偏移量，從0開始
  • 檢視前三行的記錄
mysql> select * from tt limit 3;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | wangwu   | 70.00 | beijing |
|  2 | lisi     | 80.00 | nanjing |
|  3 | zhangsan | 80.00 | beijing |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
  • 檢視3到5行的記錄
#2代表索引 往下數3行
mysql> select * from tt limit 2,3;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  3 | zhangsan | 80.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
|  5 | tianqi   | 80.00 | beijing |
+----+----------+-------+---------+
3 rows in set (0.00 sec)
  • 列出班級前三名
    • 在插入一些資料記錄
mysql> insert into tt (name,score,address) values ('tom',87,'shanghai'),('shuaige',76,'hangzhou');

#查詢資料記錄
mysql> select * from tt;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | wangwu   | 70.00 | beijing  |
|  2 | lisi     | 80.00 | nanjing  |
|  3 | zhangsan | 80.00 | beijing  |
|  4 | zhaoliu  | 60.00 | nanjing  |
|  5 | tianqi   | 80.00 | beijing  |
|  6 | shuaige  | 70.00 | suzhou   |
|  7 | tom      | 87.00 | shanghai |
|  8 | shuaige  | 76.00 | hangzhou |
+----+----------+-------+----------+
8 rows in set (0.00 sec)

#列出前三名 先排序
mysql> select * from tt order by score desc limit 3;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  7 | tom      | 87.00 | shanghai |
|  2 | lisi     | 80.00 | nanjing  |
|  3 | zhangsan | 80.00 | beijing  |
+----+----------+-------+----------+
3 rows in set (0.00 sec)

方法2：   只要條件成立就行
mysql> select * from tt  where 1=1 order by score desc limit 3;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  7 | tom      | 87.00 | shanghai |
|  2 | lisi     | 80.00 | nanjing  |
|  3 | zhangsan | 80.00 | beijing  |
+----+----------+-------+----------+
3 rows in set (0.00 sec)

1.4：設定別名

在 MySQL 查詢時，當表的名字比較長或者表內某些欄位比較長時，為了方便書寫或者多次使用相同的表，可以給欄位列或表設定別名。使用的時候直接使用別名，簡潔明瞭，增 強可讀性。

  • 使用AS語句設定別名，關鍵字AS可省略
  • 設定別名時，保證不能與庫中其他表或欄位名稱衝突
  • 別名的語法結構
SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name ASalias_name;

1.41：AS的用法

示例

mysql> select count(*) from tt;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) as 數量 from tt;
+--------+
| 數量   |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)
  • 多表相連
#這邊我們只有tt一張表 我們在建立一張表命名為gg
create table gg (id int(5) not null primary key auto_increment,gg_name varchar(20) not null);

#顯示所有表
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| gg           |
| tt           |
+--------------+
2 rows in set (0.00 sec)

#檢視錶結構

mysql> desc gg;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(5)      | NO   | PRI | NULL    | auto_increment |
| gg_name | varchar(20) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

#插入資料
mysql> insert into gg (gg_name) values ('小當家'),('小神龍'),('小福貴');;

#檢視gg表資料記錄
mysql> select * from gg;
+----+-----------+
| id | gg_name   |
+----+-----------+
|  1 | 小當家    |
|  2 | 小神龍    |
|  3 | 小福貴    |
+----+-----------+
3 rows in set (0.00 sec)

#為了對應我們給tt表增加一列
mysql> alter table tt add column hobby int(3) not null;

#檢視錶結構
mysql> desc tt;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(10)      | NO   | PRI | NULL    | auto_increment |
| name    | char(20)     | NO   |     | NULL    |                |
| score   | decimal(5,2) | YES  |     | NULL    |                |
| address | varchar(40)  | YES  |     | 未知    |                |
| hobby   | int(3)       | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#查詢表資料記錄
mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 70.00 | beijing  |     0 |
|  2 | lisi     | 80.00 | nanjing  |     0 |
|  3 | zhangsan | 80.00 | beijing  |     0 |
|  4 | zhaoliu  | 60.00 | nanjing  |     0 |
|  5 | tianqi   | 80.00 | beijing  |     0 |
|  6 | shuaige  | 70.00 | suzhou   |     0 |
|  7 | tom      | 87.00 | shanghai |     0 |
|  8 | shuaige  | 76.00 | hangzhou |     0 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)
定義了不允許為空 null 這邊顯示跌是預設值
如果定義的是允許為空  就是顯示的是null 什麼都沒雨

小結：

資料庫中’ ':是空的字串 是有值的

null：是什麼都沒有 沒有值

1.42：進行多表相連

  • 把80分以上的hobby欄位設為1 80以下的人設為2
mysql> update tt set hobby=1 where score >=80;

mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 70.00 | beijing  |     0 |
|  2 | lisi     | 80.00 | nanjing  |     1 |
|  3 | zhangsan | 80.00 | beijing  |     1 |
|  4 | zhaoliu  | 60.00 | nanjing  |     0 |
|  5 | tianqi   | 80.00 | beijing  |     1 |
|  6 | shuaige  | 70.00 | suzhou   |     0 |
|  7 | tom      | 87.00 | shanghai |     1 |
|  8 | shuaige  | 76.00 | hangzhou |     0 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)

#再把0全部改為2
mysql> mysql> update tt set hobby=2 where ;

#查詢表資料記錄
mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 70.00 | beijing  |     2 |
|  2 | lisi     | 80.00 | nanjing  |     1 |
|  3 | zhangsan | 80.00 | beijing  |     1 |
|  4 | zhaoliu  | 60.00 | nanjing  |     2 |
|  5 | tianqi   | 80.00 | beijing  |     1 |
|  6 | shuaige  | 70.00 | suzhou   |     2 |
|  7 | tom      | 87.00 | shanghai |     1 |
|  8 | shuaige  | 76.00 | hangzhou |     2 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)

進行兩連查詢
inner join 【內連線】

#tt表的外來鍵對應的是gg表的主鍵

mysql> select * from tt inner join gg on tt.hobby=gg.id;
+----+----------+-------+----------+-------+----+-----------+
| id | name     | score | address  | hobby | id | gg_name   |
+----+----------+-------+----------+-------+----+-----------+
|  1 | wangwu   | 70.00 | beijing  |     2 |  2 | 小神龍    |
|  2 | lisi     | 80.00 | nanjing  |     1 |  1 | 小當家    |
|  3 | zhangsan | 80.00 | beijing  |     1 |  1 | 小當家    |
|  4 | zhaoliu  | 60.00 | nanjing  |     2 |  2 | 小神龍    |
|  5 | tianqi   | 80.00 | beijing  |     1 |  1 | 小當家    |
|  6 | shuaige  | 70.00 | suzhou   |     2 |  2 | 小神龍    |
|  7 | tom      | 87.00 | shanghai |     1 |  1 | 小當家    |
|  8 | shuaige  | 76.00 | hangzhou |     2 |  2 | 小神龍    |
+----+----------+-------+----------+-------+----+-----------+
8 rows in set (0.00 sec)

#只看興趣愛好組的人
mysql> select tt.name,gg.gg_name from tt inner join gg on tt.hobby=gg.id;
+----------+-----------+
| name     | gg_name   |
+----------+-----------+
| wangwu   | 小神龍    |
| lisi     | 小當家    |
| zhangsan | 小當家    |
| zhaoliu  | 小神龍    |
| tianqi   | 小當家    |
| shuaige  | 小神龍    |
| tom      | 小當家    |
| shuaige  | 小神龍    |
+----------+-----------+
8 rows in set (0.00 sec)
  • 設定別名
#設定gg別名為G  tt別名為T
mysql> select T.name,G.gg_name from tt as T inner join gg as G on T.hobby=G.id; 
+----------+-----------+
| name     | gg_name   |
+----------+-----------+
| wangwu   | 小神龍    |
| lisi     | 小當家    |
| zhangsan | 小當家    |
| zhaoliu  | 小神龍    |
| tianqi   | 小當家    |
| shuaige  | 小神龍    |
| tom      | 小當家    |
| shuaige  | 小神龍    |
+----------+-----------+
8 rows in set (0.00 sec)

#as是自定義的可以取消
mysql> select T.name,G.gg_name from tt T inner join gg G on T.hobby=G.id;
+----------+-----------+
| name     | gg_name   |
+----------+-----------+
| wangwu   | 小神龍    |
| lisi     | 小當家    |
| zhangsan | 小當家    |
| zhaoliu  | 小神龍    |
| tianqi   | 小當家    |
| shuaige  | 小神龍    |
| tom      | 小當家    |
| shuaige  | 小神龍    |
+----------+-----------+
8 rows in set (0.00 sec)

1.43：定義列別名

mysql> select T.name 姓名,G.gg_name 興趣愛好 from tt T inner join gg G on T.hobbyy=G.id;
+----------+--------------+
| 姓名     | 興趣愛好     |
+----------+--------------+
| wangwu   | 小神龍       |
| lisi     | 小當家       |
| zhangsan | 小當家       |
| zhaoliu  | 小神龍       |
| tianqi   | 小當家       |
| shuaige  | 小神龍       |
| tom      | 小當家       |
| shuaige  | 小神龍       |
+----------+--------------+
8 rows in set (0.00 sec)

1.5：萬用字元

萬用字元主要用於替換字串中的部分字元，通過部分字元的匹配將相關結果查詢出來。 通常萬用字元都是跟 LIKE 一起使用的，並協同 WHERE 子句共同來完成查詢任務。

  • 用於替換字串的部分字元

  • 通常配合LIKE一起使用，並協同WHERE完成查詢

  • 常用的萬用字元

%表示零個、一個或多個

_表示單個字元

1.51:萬用字元的用法

  • 過濾zhangsan
7個_ 表示z後面7個隨機字元
mysql> select name,score from tt where name like 'z_______';
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 80.00 |
+----------+-------+
1 row in set (0.00 sec)
  • z後面任意字元
mysql> select name,score from tt where name like 'z%';
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 80.00 |
| zhaoliu  | 60.00 |
+----------+-------+
2 rows in set (0.00 sec)

1.52：兩者集合使用

  • 過濾前面為一個字元 緊接著是hu兩個字元 後面兩個、一個、多個字元
mysql> select name,score from tt where name like '_hu%';
+---------+-------+
| name    | score |
+---------+-------+
| shuaige | 70.00 |
| shuaige | 76.00 |
+---------+-------+
2 rows in set (0.00 sec)

1.6：子查詢

子查詢也被稱作內查詢或者巢狀查詢，是指在一個查詢語句裡面還巢狀著另一個查詢語 句。子查詢語句是先於主查詢語句被執行的，其結果作為外層的條件返回給主查詢進行下一 步的查詢過濾。子查詢不僅可以在 SELECT 語句中使用，在 INERT、UPDATE、DELETE 中也同樣適用。在巢狀的時候，子查詢內部還可以再次巢狀新的子查詢，也就是說可以多層 巢狀。

  • 也稱作內查詢或者巢狀查詢
  • 先於主查詢被執行，其結果將作為外層主查詢的條件
  • 在增刪改查中都可以使用子查詢
  • 支援多層巢狀
  • IN語句是用來判斷某個值是否在給定的結果集中

1.61：子查詢的用法

  • 檢視小當家的興趣愛好表
mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 70.00 | beijing  |     2 |
|  2 | lisi     | 80.00 | nanjing  |     1 |
|  3 | zhangsan | 80.00 | beijing  |     1 |
|  4 | zhaoliu  | 60.00 | nanjing  |     2 |
|  5 | tianqi   | 80.00 | beijing  |     1 |
|  6 | shuaige  | 70.00 | suzhou   |     2 |
|  7 | tom      | 87.00 | shanghai |     1 |
|  8 | shuaige  | 76.00 | hangzhou |     2 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)

mysql> select * from gg;
+----+-----------+
| id | gg_name   |
+----+-----------+
|  1 | 小當家    |
|  2 | 小神龍    |
|  3 | 小福貴    |
+----+-----------+
3 rows in set (0.00 sec)

#篩選小當家的興趣愛好組的人
mysql> select name,hobby from tt where hobby in(select id from gg where gg_name=''小當家');
+----------+-------+
| name     | hobby |
+----------+-------+
| lisi     |     1 |
| zhangsan |     1 |
| tianqi   |     1 |
| tom      |     1 |
+----------+-------+
4 rows in set (0.02 sec)

方法二：    後面輸出的結果賦予了前面的值
mysql> select name,hobby from tt where hobby=(select id from gg where gg_name=' 小當家');
+----------+-------+
| name     | hobby |
+----------+-------+
| lisi     |     1 |
| zhangsan |     1 |
| tianqi   |     1 |
| tom      |     1 |
+----------+-------+
4 rows in set (0.00 sec)
  • 篩選出小當家與小神龍的興趣愛好的人
mysql> select name,hobby from tt where hobby in (select id from gg);
+----------+-------+
| name     | hobby |
+----------+-------+
| wangwu   |     2 |
| lisi     |     1 |
| zhangsan |     1 |
| zhaoliu  |     2 |
| tianqi   |     1 |
| shuaige  |     2 |
| tom      |     1 |
| shuaige  |     2 |
+----------+-------+
8 rows in set (0.00 sec)
  • 檢視小神龍興趣愛好的人
mysql> select name,hobby from tt where hobby<>(select id from gg where gg_name=' 小當家');
+---------+-------+
| name    | hobby |
+---------+-------+
| wangwu  |     2 |
| zhaoliu |     2 |
| shuaige |     2 |
| shuaige |     2 |
+---------+-------+
4 rows in set (0.00 sec)

mysql> select name,hobby from tt where hobby in(select id from gg where gg_name='小神龍');
+---------+-------+
| name    | hobby |
+---------+-------+
| wangwu  |     2 |
| zhaoliu |     2 |
| shuaige |     2 |
| shuaige |     2 |
+---------+-------+
4 rows in set (0.00 sec)

mysql> select name,hobby from tt where hobby=(select id from gg where gg_name=' 小神龍');
+---------+-------+
| name    | hobby |
+---------+-------+
| wangwu  |     2 |
| zhaoliu |     2 |
| shuaige |     2 |
| shuaige |     2 |
+---------+-------+
4 rows in set (0.00 sec)

mysql> select name,hobby from tt where hobby!=(select id from gg where gg_name=' 小當家');
+---------+-------+
| name    | hobby |
+---------+-------+
| wangwu  |     2 |
| zhaoliu |     2 |
| shuaige |     2 |
| shuaige |     2 |
+---------+-------+
4 rows in set (0.00 sec)

mysql> select name,hobby from tt where hobby<>(select id from gg where gg_name=' 小當家');
+---------+-------+
| name    | hobby |
+---------+-------+
| wangwu  |     2 |
| zhaoliu |     2 |
| shuaige |     2 |
| shuaige |     2 |
+---------+-------+
4 rows in set (0.00 sec)

總結：經過測試發現mysql中用<>與!=都是可以的，但sqlserver中不識別!=,所以建議用<>

1.62：mysql算數運算子號

mysql> select 1+5;
+-----+
| 1+5 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)

mysql> select 1*5;
+-----+
| 1*5 |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

mysql> select 1=2;
+-----+
| 1=2 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

mysql> select 1!=2;
+------+
| 1!=2 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> <> or != 用於判斷數字，字串，表示式不相等的判斷,如果不相等，返回值為1，
否則返回值為0.
  • 建立一個新表，把nanjing匯入到新表中
#克隆tt表  新表命名為xi表
mysql> create table xi like tt;

#顯示所有表
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| gg           |
| tt           |
| xi           |
+--------------+
3 rows in set (0.01 sec)

#xi表沒有表結構 我們建立一下
mysql> select * from xi;
Empty set (0.00 sec)

#把tt表的beijing匯入到xi表中
mysql> insert into xi select * from tt where id in (select id from tt where address='beijing');

mysql> select * from xi;
+----+----------+-------+---------+-------+
| id | name     | score | address | hobby |
+----+----------+-------+---------+-------+
|  1 | wangwu   | 70.00 | beijing |     2 |
|  2 | lisi     | 80.00 | nanjing |     1 |
|  3 | zhangsan | 80.00 | beijing |     1 |
|  4 | zhaoliu  | 60.00 | nanjing |     2 |
|  5 | tianqi   | 80.00 | beijing |     1 |
+----+----------+-------+---------+-------+
5 rows in set (0.00 sec)
  • 把沒有達到80分的人的分數每人加5分
mysql> select * from tt;                                                         
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 70.00 | beijing  |     2 |
|  2 | lisi     | 80.00 | nanjing  |     1 |
|  3 | zhangsan | 80.00 | beijing  |     1 |
|  4 | zhaoliu  | 60.00 | nanjing  |     2 |
|  5 | tianqi   | 80.00 | beijing  |     1 |
|  6 | shuaige  | 70.00 | suzhou   |     2 |
|  7 | tom      | 87.00 | shanghai |     1 |
|  8 | shuaige  | 76.00 | hangzhou |     2 |
+----+----------+-------+----------+-------+

#
mysql> update tt set score=score+5 where score<80;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 75.00 | beijing  |     2 |
|  2 | lisi     | 80.00 | nanjing  |     1 |
|  3 | zhangsan | 80.00 | beijing  |     1 |
|  4 | zhaoliu  | 65.00 | nanjing  |     2 |
|  5 | tianqi   | 80.00 | beijing  |     1 |
|  6 | shuaige  | 75.00 | suzhou   |     2 |
|  7 | tom      | 87.00 | shanghai |     1 |
|  8 | shuaige  | 81.00 | hangzhou |     2 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)

1.62：EXIST 這個關鍵字在子查詢時，主要用於判斷子查詢的結果集是否為空。如果不為空， 則返回 TRUE；反之，則返回 FALSE。

mysql> select name,score from tt where exists (select id from gg where gg_name=' 小當家');
+----------+-------+
| name     | score |
+----------+-------+
| wangwu   | 75.00 |
| lisi     | 80.00 |
| zhangsan | 80.00 |
| zhaoliu  | 65.00 |
| tianqi   | 80.00 |
| shuaige  | 75.00 |
| tom      | 87.00 |
| shuaige  | 81.00 |
+----------+-------+
8 rows in set (0.00 sec)

mysql> select name,score from tt where exists (select id from gg where gg_name=' 位置');
Empty set (0.00 sec)

1.7：NULL值

  • 表示缺失的值
  • 與數字0或者空白(spaces）是不同的
  • 使用IS NULL或IS NOT NULL進行判斷
  • NULL值和空值的區別
    • 空值長度為0，不佔空間;NULL值的長度為NULL，佔用空間
    • .IS NULL無法判斷空值
    • 空值使用“="或者“<>"來處理
    • .cOUNT()計算時，NULL會忽略，空值會加入計算

NULL裡面沒有任何值 ，佔用一部分空間

示例

建立一個表
mysql> create table num (id int(4) not null primary key auto_increment,name char(10));
#檢視錶結構
mysql> desc num;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
#插入內容
mysql> insert into num (id,name) values (2,'shuai');
Query OK, 1 row affected (0.00 sec)
#查詢資料記錄
mysql> select * from num;
+----+-------+
| id | name  |
+----+-------+
|  2 | shuai |
+----+-------+
1 row in set (0.00 sec)

#再次插入
mysql> insert into num (id) values (3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from num;
+----+-------+
| id | name  |
+----+-------+
|  2 | shuai |
|  3 | NULL  |
+----+-------+
2 rows in set (0.00 sec)

#在向表內插入記錄或者更新記錄時，如果該欄位沒有 NOT NULL 並且沒有值，這時候新記錄的該欄位將被儲存為 NULL
  • 進行統計
mysql> select count(id) from num;
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)


mysql> select count(name) from num;
+-------------+ 
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

#cOUNT()計算時，NULL會忽略，空值會加入計算`

#插入空值
mysql> insert into num (id,name) values (4,'');

#查詢num資料記錄
mysql> select * from num;
+----+-------+
| id | name  |
+----+-------+
|  2 | shuai |
|  3 | NULL  |
|  4 |       |
+----+-------+
3 rows in set (0.00 sec)

mysql> select count(name) from num;
+-------------+
| count(name) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

#可以看出控制會被加入計算 null不會

1.71：查詢name欄位不為空的記錄

mysql> select * from num where name is not null;
+----+-------+
| id | name  |
+----+-------+
|  2 | shuai |
|  4 |       |
+----+-------+
2 rows in set (0.00 sec)

1.72：查詢name欄位為NULL值的記錄

mysql> select * from num where name is null;
+----+------+
| id | name |
+----+------+
|  3 | NULL |
+----+------+
1 row in set (0.00 sec)

二：正規表示式

MySQL 正規表示式通常是在檢索資料庫記錄的時候，根據指定的匹配模式匹配記錄中符合要求的特殊字串。MySQL 的正規表示式使用 REGEXP 這個關鍵字來指定正規表示式的匹配模式。

  • 根據指定的匹配模式匹配記錄中符合要求的特殊字元
  • 使用REGEXP關鍵字指定匹配模式
  • 常用匹配模式

2.1：正規表示式匹配表

在這裡插入圖片描述
瞭解正規表示式的匹配規則之後，就可以將這些規則技巧應用於 SQL 語句中，從而可以更準確、更快速的查出所需的記錄。下面通過示例的方式詳細介紹 MySQL 正規表示式的使用方法。

示例：

  • 列印以wa為開頭的欄位
mysql> select * from tt where name regexp '^wa';
+----+--------+-------+---------+-------+
| id | name   | score | address | hobby |
+----+--------+-------+---------+-------+
|  1 | wangwu | 70.00 | beijing |     2 |
+----+--------+-------+---------+-------+
1 row in set (0.01 sec)
  • 列印以結尾的欄位
mysql> select * from tt where name regexp 'm$';
+----+------+-------+----------+-------+
| id | name | score | address  | hobby |
+----+------+-------+----------+-------+
|  7 | tom  | 87.00 | shanghai |     1 |
+----+------+-------+----------+-------+
1 row in set (0.00 sec)

2.11：包含指定字串的記錄

  • 在tt表中查詢包含an字串的name欄位並列印
mysql> select  * from tt where name regexp 'an';
+----+----------+-------+---------+-------+
| id | name     | score | address | hobby |
+----+----------+-------+---------+-------+
|  1 | wangwu   | 70.00 | beijing |     2 |
|  3 | zhangsan | 80.00 | beijing |     1 |
|  5 | tianqi   | 80.00 | beijing |     1 |
+----+----------+-------+---------+-------+
3 rows in set (0.00 sec)

#測試一下不連續的的是否能列印 這邊我們就測試hi把 【shuai裡的字元】
mysql> select  * from tt where name regexp 'hi';
Empty set (0.00 sec)

#測試是必須連續的字母 不連續是不可以的

2.12：以"."代替字串的任意一個字元的記錄

在tt表查詢包含字串zh與ng，且兩個字元之間只有一個字元的name欄位並列印出對應的記錄

mysql> select  * from tt where name regexp 'zh.ng';
+----+----------+-------+---------+-------+
| id | name     | score | address | hobby |
+----+----------+-------+---------+-------+
|  3 | zhangsan | 80.00 | beijing |     1 |
+----+----------+-------+---------+-------+
1 row in set (0.00 sec)

#查詢以shu後面單個字元
mysql> select  * from tt where name regexp 'shu..';
+----+---------+-------+----------+-------+
| id | name    | score | address  | hobby |
+----+---------+-------+----------+-------+
|  6 | shuaige | 70.00 | suzhou   |     2 |
|  8 | shuaige | 76.00 | hangzhou |     2 |
+----+---------+-------+----------+-------+
2 rows in set (0.00 sec)

2.13：匹配包含或者關係的記錄

  • 列如，在tt表中查詢包含字串zh或者ti欄位並列印對應的欄位記錄
mysql> select  * from tt where name regexp 'zh|ti';
+----+----------+-------+---------+-------+
| id | name     | score | address | hobby |
+----+----------+-------+---------+-------+
|  3 | zhangsan | 80.00 | beijing |     1 |
|  4 | zhaoliu  | 60.00 | nanjing |     2 |
|  5 | tianqi   | 80.00 | beijing |     1 |
+----+----------+-------+---------+-------+
3 rows in set (0.00 sec)

2.14：匹配前面字元的任意多次

  • 列如，在tt表中查詢包含三個或者更多的連續的o的name欄位並列印對應的欄位記錄
#這邊我們首先先插入資料

mysql> insert into tt (name,score,address,hobby) values ('0000',37,'tianjing',2),('000000000',88,'shanghai',3);


#進行查詢表資料
mysql> select * from tt;                                                           
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | wangwu    | 70.00 | beijing  |     2 |
|  2 | lisi      | 80.00 | nanjing  |     1 |
|  3 | zhangsan  | 80.00 | beijing  |     1 |
|  4 | zhaoliu   | 60.00 | nanjing  |     2 |
|  5 | tianqi    | 80.00 | beijing  |     1 |
|  6 | shuaige   | 70.00 | suzhou   |     2 |
|  7 | tom       | 87.00 | shanghai |     1 |
|  8 | shuaige   | 76.00 | hangzhou |     2 |
| 13 | 0000      | 37.00 | tianjing |     2 |
| 14 | 000000000 | 88.00 | shanghai |     3 |
+----+-----------+-------+----------+-------+
10 rows in set (0.00 sec)

##匹配包含00或者更多0的欄位
mysql> select * from tt where name regexp '00*';
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
| 13 | 0000      | 37.00 | tianjing |     2 |
| 14 | 000000000 | 88.00 | shanghai |     3 |
+----+-----------+-------+----------+-------+
2 rows in set (0.00 sec)

#*是否能匹配？？？
mysql> select * from tt where name regexp '*';
ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp

這邊*不是顯示所有   0*顯示前面出現一個或者多個     *是次數的含義

2.15：”+“匹配前面字元至少一次

  • 在tt表中查詢包含四個或者更多個0的name欄位並列印對應記錄
mysql> select * from tt where name regexp '0000*';
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
| 13 | 0000      | 37.00 | tianjing |     2 |
| 14 | 000000000 | 88.00 | shanghai |     3 |
+----+-----------+-------+----------+-------+
2 rows in set (0.00 sec)

2.16：匹配指定字符集的任意一個

  • 在tt表中查詢包含以a、s開頭的name欄位並列印對應的記錄
mysql> select * from tt where name regexp '^[a-s]';
+----+---------+-------+----------+-------+
| id | name    | score | address  | hobby |
+----+---------+-------+----------+-------+
|  2 | lisi    | 80.00 | nanjing  |     1 |
|  6 | shuaige | 70.00 | suzhou   |     2 |
|  8 | shuaige | 76.00 | hangzhou |     2 |
+----+---------+-------+----------+-------+
3 rows in set (0.00 sec)

三：運算子

3.1：算數運算子

  • MySQL 的運算子用於對記錄中的欄位值進行運算。MySQL 的運算子共有四種，分別是：算術運算子、比較運算子、邏輯運算子和位運算子。下面分別對這四種運算子進行說明。

  • 用於對記錄中的欄位值進行運算

3.11：運算子號分類

  • 算數運算子

  • 比較運算子

  • 邏輯運算子

  • 位運算子 二進位制進行運算

3.11：MYSQL支援使用的運算子號

在這裡插入圖片描述
例如，以 SELECT 命令來實現最基礎的加減乘除運算，具體操作如下所示。

mysql> select 2+3,2*4,8%2,9/2;
+-----+-----+------+--------+
| 2+3 | 2*4 | 8%2  | 9/2    |
+-----+-----+------+--------+
|   5 |   8 |    0 | 4.5000 |
+-----+-----+------+--------+
1 row in set (0.00 sec)

在除法運算和求餘數運算中，除數不能為 0，若除數是 0，返回的結果則為 NULL。需要注意的是，如果有多個運算子，按照先乘除後加減的優先順序進行運算，相同優先順序的運算 符沒有先後順序。

3.2：比較運算

字串的比較迷人不區分大小寫，可使用binary來區分

3.31：常用比較運算子

在這裡插入圖片描述
下面對幾種常用的比較運算進行一些講解和舉例

3.21：等於運算子

等號（=）是用來判斷數字、字串和表示式是否相等的，如果相等則返回 1，如果不相等則返回 0。如果比較的兩者有一個值是 NULL，則比較的結果就是 NULL。其中字元的比較是根據 ASCII 碼來判斷的，如果 ASCII 碼相等，則表示兩個字元相同；如果 ASCII 碼不相等，則表示兩個字元不相同。例如，等於運算子在數字、字串和表示式上的使用，具 體操作如下所示。

示例

mysql> select 2=3,3='3','shuai'='shuai',(3+4)=(2+5),'a'=NULL;
+-----+-------+-----------------+-------------+----------+
| 2=3 | 3='3' | 'shuai'='shuai' | (3+4)=(2+5) | 'a'=NULL |
+-----+-------+-----------------+-------------+----------+
|   0 |     1 |               1 |           1 |     NULL |
+-----+-------+-----------------+-------------+----------+
1 row in set (0.00 sec)

從以上查詢可以看出來：

  • 如果兩者都是整數，則按照整數值進行比較。

  • 如果一個整數一個字串，則會自動將字串轉換為數字，再進行比較。

  • 如果兩者都是字串，則按照字串進行比較。

  • 如果兩者中至少有一個值是 NULL，則比較的結果是 NULL

3.22：不等於運算子

不等於號有兩種寫法，分別是<>或者!=，用於針對數字、字串和表示式不相等的比較。如果不相等則返回 1，如果相等則返回 0，這點正好跟等於的返回值相反。需要注意的是不等於運算子不能用於判斷 NULL。

mysql> mysqlt 'shuai'<>'chou',13!=17,NULL=NULL;
+-----------------+--------+-----------+
| 'shuai'<>'chou' | 13!=17 | NULL=NULL |
+-----------------+--------+-----------+
|               1 |      1 |      NULL |
+-----------------+--------+-----------+
1 row in set (0.00 sec)

#不等於運算子不能判斷NULL

數值與字串是否能比較？

mysql> select 'hhh'!=45;
+-----------+
| 'hhh'!=45 |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'hhh'=45;
+----------+
| 'hhh'=45 |
+----------+
|        0 |
+----------+
1 row in set, 1 warning (0.00 sec)

3.23：大於、大於等於、小於、小於等於運算子

  • 大於（>）運算子用來判斷左側的運算元是否大於右側的運算元，若大於返回 1，否則返回 0，同樣不能用於判斷 NULL。

  • 小於（<）運算子用來判斷左側的運算元是否小於右側的運算元，若小於返回 1，否則返回 0，同樣不能用於判斷 NULL。

  • 大於等於（>=）判斷左側的運算元是否大於等於右側的運算元，若大於等於返回 1，否則返回 0，不能用於判斷 NULL。

  • 小於等於（<=）判斷左側的運算元是否小於等於右側的運算元，若小於等於返回 1，否則返回 0，不能用於判斷 NULL。

數值比較會自動轉換ASCII表的數值

具體內容點選 https://baike.baidu.com/item/ASCII/309296?fr=aladdin

我們需要大體記住幾個常用Dec（十進位制）的就行：

0是48

大寫A是65 B是66 依次往後推算

小寫a是97 b是98

mysql> mysql> select 5>3,'a'>'b',3>=4,(5+6)>=(3+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 5>3 | 'a'>'b' | 3>=4 | (5+6)>=(3+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
|   1 |       0 |    0 |            1 |     0 |   1 |        1 |      1 |      NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
1 row in set (0.00 sec)

3.24： IS NULL、IS NOT NULL

  • SNULL 判斷一個值是否為 NULL，如果為 NULL 返回 1，否則返回 0。
  • IS NOT NULL 判斷一個值是否不為 NULL，如果不為 NULL 返回 1，否則返回 0
    具體操作如下
mysql> select 2 is NULL,'d' is not NULL,NULL IS NULL;
+-----------+-----------------+--------------+
| 2 is NULL | 'd' is not NULL | NULL IS NULL |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)

3.25：BETWEEN AND

  • BETWEEN AND 比較運算通常用於判斷一個值是否落在某兩個值之間。例如，判斷某數字是否在另外兩個數字之間，也可以判斷某英文字母是否在另外兩個字母之間

示例

mysql> select 5 between 2 and 8,7 between 5 and 10,'d' between 'a' and 'z';
+-------------------+--------------------+-------------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 'd' between 'a' and 'z' |
+-------------------+--------------------+-------------------------+
|                 1 |                  1 |                       1 |
+-------------------+--------------------+-------------------------+
1 row in set (0.00 sec)

數字能否與字元比較？
mysql> select 5 between 2 and 8,7 between 5 and 10,8 between 'a' and 'z'; 
+-------------------+--------------------+-----------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 8 between 'a' and 'z' |
+-------------------+--------------------+-----------------------+
|                 1 |                  1 |                     0 |
+-------------------+--------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#數字不能與字元比較的

3.26：LEAST、GREATEST

  • LEAST：當有兩個或者多個引數時，返回其中的最小值。如果其中一個值為 NULL，則返回結果就為 NULL。

  • GREATEST：當有兩個或者多個引數時，返回其中的最大值。如果其中一個值為 NULL， 則返回結果就為 NULL。

具體操作如下

mysql> SELECT least(1,2,3),least('a','d','f'),greatest(1,2,3),greatest('a','d','f');
+--------------+--------------------+-----------------+-----------------------+
| least(1,2,3) | least('a','d','f') | greatest(1,2,3) | greatest('a','d','f') |
+--------------+--------------------+-----------------+-----------------------+
|            1 | a                  |               3 | f                     |
+--------------+--------------------+-----------------+-----------------------+
1 row in set (0.00 sec)

min是否能求最小值呢
mysql> select min(2,3,4),min('d','e','f');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3,4),min('d','e','f')' at line 1

#對了是不能的  min後面只能跟欄位名稱

3.27：IN、NOT IN

  • IN 判斷一個值是否在對應的列表中，如果是返回 1，否則返回 0。

  • NOT IN 判斷一個值是否不在對應的列表中，如果不是返回 1，否則返回 0。

mysql> select 2 in (1,2,3,4,5),'c' not in ('a','b','c');
+------------------+--------------------------+
| 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') |
+------------------+--------------------------+
|                1 |                        0 |
+------------------+--------------------------+
1 row in set (0.00 sec)

從以上結果可以看出，IN 和 NOT IN 的返回值正好相反。

3.28：LIKE、NOT LIKE

  • LIKE 用來匹配字串，如果匹配成功則返回 1，反之返回 0。LIKE 支援兩種萬用字元：’%’ 用於匹配任意數目的字元，而’_’只能匹配一個字元。NOT LIKE 正好跟 LIKE 相反，如果沒有匹配成功則返回 1，反之返回 0
mysql> select 'shuai' like 'shu_','xiao' like '%k','lisi' not like '%a';
+---------------------+------------------+----------------------+
| 'shuai' like 'shu_' | 'xiao' like '%k' | 'lisi' not like '%a' |
+---------------------+------------------+----------------------+
|                   0 |                0 |                    1 |
+---------------------+------------------+----------------------+
1 row in set (0.00 sec)

3.3：邏輯運算子

  • 邏輯運算子又被稱為布林運算子，通常用來判斷表示式的真假，如果為真返回 1，否則返回 0，真和假也可以用 TRUE 和 FALSE 表示。

  • 又被稱布林值運算子

  • 用來判斷表示式的真假

3.31：常用的邏輯運算子號

在這裡插入圖片描述

3.32：邏輯非

  • 邏輯運算子中最簡單的運算子就是邏輯非，邏輯非使用 NOT 或！表示。邏輯非將跟在它後面的邏輯測試取反，把真變為假，把假變為真。如果 NOT 後面的運算元為 0 時，所得值為 1；如果運算元為非 0 時，所得值為 0；如果運算元為 NULL 時，所得值為 NULL。例如，對非 0 值和 0 值分別作邏輯非運算，具體操作如下所示。
mysql> select not 0,not 2,!4,!(2-2);
+-------+-------+----+--------+
| not 0 | not 2 | !4 | !(2-2) |
+-------+-------+----+--------+
|     1 |     0 |  0 |      1 |
+-------+-------+----+--------+
1 row in set (0.00 sec)

3.33：邏輯與

  • 邏輯與通常用於判斷兩個值或多個值的有效性，如果所有值都是真返回 1，否則返回 0。邏輯與使用 AND 或者&&表示。例如，對非 0 值、0 值和 NULL 值分別作邏輯與運算，具體操作如下所示。
#兩個都是非0值  出來結果就是1 
區分是按照非0值與0區分的
mysql> select 3 and 5,0 and 8,0 and NULL,1 and NULL;
+---------+---------+------------+------------+
| 3 and 5 | 0 and 8 | 0 and NULL | 1 and NULL |
+---------+---------+------------+------------+
|       1 |       0 |          0 |       NULL |
+---------+---------+------------+------------+
1 row in set (0.00 sec)

3.34： 邏輯或

  • 邏輯與通常用於判斷兩個值或多個值的有效性，如果所有值都是真返回 1，否則返回 0。邏輯與使用 AND 或者&&表示。例如，對非 0 值、0 值和 NULL 值分別作邏輯與運算。
    一旦有個非0值，出來就是非0值
mysql> select 3 OR 5,0 OR 8,0 OR NULL,1 OR NULL;
+--------+--------+-----------+-----------+
| 3 OR 5 | 0 OR 8 | 0 OR NULL | 1 OR NULL |
+--------+--------+-----------+-----------+
|      1 |      1 |      NULL |         1 |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)

3.35:邏輯異或

  • 兩個非 NULL 值的運算元，如果兩者都是 0 或者都是非 0，則返回 0；如果一個為 0， 另一個為非 0，則返回結果為 1；當任意一個值為 NULL 時，返回值為 NULL。例如，對非0 值、0 值和 NULL 值分別作邏輯異或運算，具體操作如下所示。
mysql> SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;
+---------+---------+---------+------------+---------------+
| 2 XOR 3 | 0 XOR 0 | 0 XOR 5 | 1 XOR NULL | NULL XOR NULL |
+---------+---------+---------+------------+---------------+
|       0 |       0 |       1 |       NULL |          NULL |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)

3.4：位運算子

  • 位運算子實際上是對二進位制數進行計算的運算子。MySQL 內位運算會先將運算元變成二進位制格式，然後進行位運算，最後在將計算結果從二進位制變回到十進位制格式，方便使用者查 看。

3.41：MYSQL支援的運算子

在這裡插入圖片描述
示例：對數字進行按位與、或和取反運算

mysql> select 10&15,10|15,10^15,5 &~1;
+-------+-------+-------+-------+
| 10&15 | 10|15 | 10^15 | 5 &~1 |
+-------+-------+-------+-------+
|    10 |    15 |     5 |     4 |
+-------+-------+-------+-------+
1 row in set (0.00 sec)

在這裡插入圖片描述
按位與運算（&），是對應的二進位制位都是 1 的，它們的運算結果為 1，否則為 0，所以 10 & 15 的結果為 10。

按位或運算（|），是對應的二進位制位有一個或兩個為 1 的，運算結果為 1，否則為 0， 所以 10 | 15 的結果為 15。

按位異或運算（^），是對應的二進位制位不相同時，運算結果 1，否則為 0，所以 10 ^ 15的結果為 5。

按位取反（~），是對應的二進位制數逐位反轉，即 1 取反後變為 0, 0 取反後變為 1。數字 1 的二進位制是 0001，取反後變為 1110, 數字 5 的二進位制是 0101，將 1110 和 0101進行求與操作，其結果是二進位制的 0100，轉換為十進位制就是 4。

列如對數字進行左移動或右移的運算

mysql> select 1<<2;
+------+
| 1<<2 |
+------+
|    4 |
+------+
1 row in set (0.01 sec)

mysql> select 10<<2;
+-------+
| 10<<2 |
+-------+
|    40 |
+-------+
1 row in set (0.00 sec)

<<左移位運算  向左移動兩位，空缺出補0
1<<2
0001  
0100    左移後

10<<2
1010
101000  左移後

3.42：運算子的優先順序

  • 以上不管哪種運算子，在使用過程中都有優先順序問題。運算子的優先順序決定了不同的運 算符在計算過程中的先後順序。級別高的運算子會先進行計算，如果運算子的級別相同， MySQL 會按照順序從左到右依次進行計算。如果不確定所使用的運算子的優先順序，可以使用（）改變優先順序。
    在這裡插入圖片描述

3.4：連線查詢

  • MySQL 的連線查詢，通常都是將來自兩個或多個表的行結合起來，基於這些表之間的共同欄位，進行資料的拼接。首先，要確定一個主表作為結果集，然後將其他表的行有選擇 性的連線到選定的主表結果集上。使用較多的連線查詢包括：內連線、左連線和右連線。

  • 為了便於理解，這裡使用兩個表tt和gg老進行演示

mysql> select * from tt;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | wangwu    | 70.00 | beijing  |     2 |
|  2 | lisi      | 80.00 | nanjing  |     1 |
|  3 | zhangsan  | 80.00 | beijing  |     1 |
|  4 | zhaoliu   | 60.00 | nanjing  |     2 |
|  5 | tianqi    | 80.00 | beijing  |     1 |
|  6 | shuaige   | 70.00 | suzhou   |     2 |
|  7 | tom       | 87.00 | shanghai |     1 |
|  8 | shuaige   | 76.00 | hangzhou |     2 |
| 13 | 0000      | 37.00 | tianjing |     2 |
| 14 | 000000000 | 88.00 | shanghai |     3 |
| 15 | aaa       | 66.00 | wuchang  |     0 |
| 16 | bbb       | 45.00 | sichuan  |     0 |
+----+-----------+-------+----------+-------+
12 rows in set (0.00 sec)

mysql> select * from gg;                                               
+----+-----------+
| id | gg_name   |
+----+-----------+
|  1 | 小當家    |
|  2 | 小神龍    |
|  3 | 小福貴    |
|  4 | 小可愛    |
+----+-----------+
4 rows in set (0.00 sec)

3.41：內連線

  • 內連線是系統預設的表連線，所以在 FROM 子句後可以省略 INNER 關鍵字，只使用關鍵字 JOIN。同時有多個表時，也可以連續使用 INNER JOIN 來實現多表的內連線，不過為了更好的效能，建議最好不要超過三個表。

  • 列如，在剛剛兩個表的tt表和gg表內使用內連線查詢

mysql> select T.name,G.gg_name from tt T inner join gg G on T.hobby=G.id;
+-----------+-----------+
| name      | gg_name   |
+-----------+-----------+
| wangwu    | 小神龍    |
| lisi      | 小當家    |
| zhangsan  | 小當家    |
| zhaoliu   | 小神龍    |
| tianqi    | 小當家    |
| shuaige   | 小神龍    |
| tom       | 小當家    |
| shuaige   | 小神龍    |
| 0000      | 小神龍    |
| 000000000 | 小福貴    |
+-----------+-----------+
10 rows in set (0.00 sec)

上述操作是將 tt表的每一行與 gg表的每一行進行比較，以檢查它們是否都滿足條件。當滿足條件時，INNER JOIN 將返回由 tt表和 gg表中的列組成的新行。如果沒有匹配項，查詢將返回一個空的結果集。

具體的INNER JOIN工作原理
在這裡插入圖片描述
如果表中至少有一個匹配項，INNER JOIN 關鍵字將返回一行。如果沒有匹配項，則不會有輸出結果。

3.42：左連線

  • MySQL 除了內連線，還可以使用外連線。區別於 MySQL 外連線是將表分為基礎表和參考表，再依據基礎表返回滿足條件或不滿足條件的記錄。外連線按照連線時表的順序來分， 有左連線和右連線之分。

  • 左連線也可以被稱為左外連線，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 關鍵字來表示。左連線以左側表為基礎表，接收左表的所有行，並用這些行與右側參考表中的記錄進行匹配，也就是說匹配左表中的所有行以及右表中符合條件的行。

  • 查詢出tt表的左右內容，並查詢出T.hobby和G.id相等判斷的gg中的部分

mysql> select T.name,G.gg_name from tt T left join gg G on T.hobby=G.id;
+-----------+-----------+
| name      | gg_name   |
+-----------+-----------+
| lisi      | 小當家    |
| zhangsan  | 小當家    |
| tianqi    | 小當家    |
| tom       | 小當家    |
| wangwu    | 小神龍    |
| zhaoliu   | 小神龍    |
| shuaige   | 小神龍    |
| shuaige   | 小神龍    |
| 0000      | 小神龍    |
| 000000000 | 小福貴    |
| aaa       | NULL      |
| bbb       | NULL      |
+-----------+-----------+
12 rows in set (0.01 sec)

主表全部顯示

從以上左連線查詢結果可以看出，除了匹配的行，還包括了左表中有但右表中沒有的行，如果有表沒有對應值，則使用 NULL 代替。也就是說在左連線查詢中，使用 NULL 值表示右表中沒有找到與左表中匹配的記錄。

在這裡插入圖片描述

3.43：右連線

  • 右連線也被稱為右外連線，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 關鍵字來表示。右連線跟左連線正好相反，它是以右表為基礎表，用於接收右表中的所有行，並用這些記錄與左表中的行進行匹配。也就是說匹配右表中的每一行及左表中符合條件的記錄
mysql> mysql> select T.name,G.gg_name from tt T right join gg G on T.hobby=G.id;
+-----------+-----------+
| name      | gg_name   |
+-----------+-----------+
| wangwu    | 小神龍    |
| lisi      | 小當家    |
| zhangsan  | 小當家    |
| zhaoliu   | 小神龍    |
| tianqi    | 小當家    |
| shuaige   | 小神龍    |
| tom       | 小當家    |
| shuaige   | 小神龍    |
| 0000      | 小神龍    |
| 000000000 | 小福貴    |
| NULL      | 小可愛    |
+-----------+-----------+
11 rows in set (0.00 sec)
#右邊所有資訊都顯示（tt） 左表是輔助

在右連線的查詢結果集中，除了符合匹配規則的行外，還包括右表中有但是左表中不匹 配的行，這些記錄在左表中以 NULL 補足。
在這裡插入圖片描述

四：資料庫函式

MYSQL提供了實現各種功能的函式

4.1：常用的函式分類

  • 數學函式

  • 聚合函式

  • 字串函式

  • 日期時間函式

4.2：數學函式

  • abs（x）返回x的絕對值
mysql> select abs(-2),abs(-3.14);
+---------+------------+
| abs(-2) | abs(-3.14) |
+---------+------------+
|       2 |       3.14 |
+---------+------------+
1 row in set (0.00 sec)

mysql> select abs(2),abs(-3.14);
+--------+------------+
| abs(2) | abs(-3.14) |
+--------+------------+
|      2 |       3.14 |
+--------+------------+
1 row in set (0.00 sec)
  • rand（）返回0到1的隨機數
mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.9606223953521088 |
+--------------------+
1 row in set (0.00 sec)

取值範圍：0<=x<1
  • mod（x,y）返回x除以y以後的餘數
mysql> select mod(10,2);
+-----------+
| mod(10,2) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
  • power（x，y）返回x的y次方
mysql> select power(3,3);
+------------+
| power(3,3) |
+------------+
|         27 |
+------------+
1 row in set (0.00 sec)
3的3次方
mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)
  • round（x） 返回離x最近的整數
#返回的是整數 四捨五入
mysql> select round(2.49);
+-------------+
| round(2.49) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> select round(2.59);
+-------------+
| round(2.59) |
+-------------+
|           3 |
+-------------+
1 row in set (0.01 sec)

mysql> select round(1.49,1);
+---------------+
| round(1.49,1) |
+---------------+
|           1.5 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(1.49,2);
+---------------+
| round(1.49,2) |
+---------------+
|          1.49 |
+---------------+
1 row in set (0.00 sec)
  • sqrt（x）返回x的平方跟
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
  • truncate(x,y)返回數字x截斷為y位小數的值
mysql> select truncate(3.1415926,3);
+-----------------------+
| truncate(3.1415926,3) |
+-----------------------+
|                 3.141 |
+-----------------------+
1 row in set (0.00 sec)
  • ceil（x）返回大於或等於x的最小整數
mysql> select ceil(1.4);
+-----------+
| ceil(1.4) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql> select ceil(1.9);
+-----------+
| ceil(1.9) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)
  • floor（x）返回小於或等於x的最大整數
mysql> select floor(1.99);
+-------------+
| floor(1.99) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select floor(1.09);
+-------------+
| floor(1.09) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
  • greates（x1，x2…)返回集合中最大得值
  • least（x1,x2…)返回集合中最小的值
mysql> select greatest(11,12,13),least(10,12,14);
+--------------------+-----------------+
| greatest(11,12,13) | least(10,12,14) |
+--------------------+-----------------+
|                 13 |              10 |
+--------------------+-----------------+
1 row in set (0.0 sec)

4.4：字串函式

  • length（x）返回字串x的值
#空格也算長度
mysql> select length('aaaa');
+----------------+
| length('aaaa') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> select length('aa aa');
+-----------------+
| length('aa aa') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)
  • trim（）返回去除指定格式的值
mysql> select trim('abcd');
+--------------+
| trim('abcd') |
+--------------+
| abcd         |
+--------------+
1 row in set (0.00 sec)

mysql> select trim('  abcd');
+----------------+
| trim('  abcd') |
+----------------+
| abcd           |
+----------------+
1 row in set (0.00 sec)

mysql> select trim('ab cd');
+---------------+
| trim('ab cd') |
+---------------+
| ab cd         |
+---------------+
1 row in set (0.00 sec)
#只能去除前後兩端空格 不能過濾中間的空格

concat（x，y）將提供的引數x和y拼接成一個字串

mysql> select concat('abc','fff');
+---------------------+
| concat('abc','fff') |
+---------------------+
| abcfff              |
+---------------------+
1 row in set (0.00 sec)

#空格是不能被過濾
mysql> select concat('abc','   fff');
+------------------------+
| concat('abc','   fff') |
+------------------------+
| abc   fff              |
+------------------------+
1 row in set (0.00 sec)

字串有空格進行去除相連

mysql> select concat('asd',trim('  qwe'));
+-----------------------------+
| concat('asd',trim('  qwe')) |
+-----------------------------+
| asdqwe                      |
+-----------------------------+
1 row in set (0.00 sec)

upper（x）將字串x的所有字母變為大寫字母

lower（x）將字串x的所有字母變為小寫字母

mysql> select upper('abc'),lower('QWE');
+--------------+--------------+
| upper('abc') | lower('QWE') |
+--------------+--------------+
| ABC          | qwe          |
+--------------+--------------+
1 row in set (0.00 sec)

left（x，y）返回字串的前y個字元

right（x，y）返回字元的後y個字元

截圖前3個字元
mysql> select left('abcert',3);
+------------------+
| left('abcert',3) |
+------------------+
| abc              |
+------------------+
1 row in set (0.00 sec)

擷取後3
mysql> select right('abcert',3);
+-------------------+
| right('abcert',3) |
+-------------------+
| ert               |
+-------------------+
1 row in set (0.00 sec)

怎麼去除中間的c字母？？
mysql> select concat(left('abcert',3),right('abcert',3));
+--------------------------------------------+
| concat(left('abcert',3),right('abcert',3)) |
+--------------------------------------------+
| abcert                                     |
+--------------------------------------------+
1 row in set (0.00 sec)

repeat（x，y）將字串重複y次

mysql> select repeat('qwer',3);
+------------------+
| repeat('qwer',3) |
+------------------+
| qwerqwerqwer     |
+------------------+
1 row in set (0.00 sec)

space（x）返回x個空格
#做一個內嵌

replace（x，y，z）將字串z代替字串x中的字元y

mysql> select replace('shuai','ua','xx');
+----------------------------+
| replace('shuai','ua','xx') |
+----------------------------+
| shxxi                      |
+----------------------------+
1 row in set (0.00 sec)

strcmp（x，y）比較x和y，返回的值可以為-1，0，1

mysql> select strcmp(14,5);
+--------------+
| strcmp(14,5) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)

mysql> select strcmp(14,14);
+---------------+
| strcmp(14,14) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> select strcmp(27,7);
+--------------+
| strcmp(27,7) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)

#比較的是第一個數

substring（x，y，z）獲取從字串的x中的第y個位置開始長度為z的字串

mysql> select substring('abcdefg',2,3);
+--------------------------+
| substring('abcdefg',2,3) |
+--------------------------+
| bcd                      |
+--------------------------+
1 row in set (0.00 sec)

#substring（完整字串，起始位置，長度）；

reverse（x）將字串x反轉

#倒過來輸出
mysql> select reverse ('abc');
+-----------------+
| reverse ('abc') |
+-----------------+
| cba             |
+-----------------+
1 row in set (0.00 sec)

#反過來大寫
mysql> select upper(reverse ('abc'));
+------------------------+
| upper(reverse ('abc')) |
+------------------------+
| CBA                    |
+------------------------+
1 row in set (0.00 sec)

4.5：日期時間函式

4.51：常用的日期時間函式

curdate（)返回當前時間的年月日）

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-08-25 |
+------------+
1 row in set (0.00 sec)

curtime（）返回當前時間的時分秒

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:34:44  |
+-----------+
1 row in set (0.00 sec)

now（）返回當前時間的日期和時間

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-08-25 16:35:53 |
+---------------------+
1 row in set (0.00 sec)

mount（x）返日期x中的月份值

mysql> select month('2020-08-25');
+---------------------+
| month('2020-08-25') |
+---------------------+
|                   8 |
+---------------------+
1 row in set (0.00 sec)

week（x）返回日期x是年度第幾個星期

mysql> select week('2020-08-25');
+--------------------+
| week('2020-08-25') |
+--------------------+
|                 34 |
+--------------------+
1 row in set (0.00 sec)

hour（x）返回x中的小時值

mysql> select hour('16:35:53');
+------------------+
| hour('16:35:53') |
+------------------+
|               16 |
+------------------+
1 row in set (0.00 sec)

顯示當前的小時/分鐘/秒

mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
|              16 |
+-----------------+
1 row in set (0.00 sec)

#小時
mysql> select minute(curtime());
+-------------------+
| minute(curtime()) |
+-------------------+
|                41 |
+-------------------+
1 row in set (0.00 sec

#顯示當前的秒

dayofweek（x）返回x是星期幾，1星期日，2是星期一

mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.00 sec)

dayofmonth（x）計算日期x是本月的第幾天

mysql> select dayofmonth(curdate());
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
|                    25 |
+-----------------------+
1 row in set (0.00 sec)

dayodyear（x）計算日期x是本年的第幾天

mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  238 |
+----------------------+
1 row in set (0.00 sec)

五：儲存過程簡介

  • 儲存過程簡介
    是一組為了完成特定功能的SQL語句集合
    比傳統SQL速度更快、執行效率更高
    儲存過程的優點
    執行一次後，會將生成的二進位制程式碼駐留緩衝區，提高執行效率
    SQL語句加上控制語句的集合，靈活性高
    在伺服器端儲存，客戶端呼叫時，降低網路負載
    可多次重複被呼叫，可隨時修改，不影響客戶端呼叫
    可完成所有的資料庫操作，也可控制資料庫的資訊訪問許可權
    [外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-cuhGa48b-1599059515276)(C:\Users\liu\AppData\Roaming\Typora\typora-user-images\image-20200825184206771.png)]

減輕網路負載

增加安全性

5.1：建立儲存過程的語法結構

  • 使用CREATE PROCEDURE語句建立儲存過程

  • 建立儲存過程的語法結構

CREATE PROCEDURE<過程名>([過程引數[…..]])<過程體>
[過程引數[.-..]]格式
IN| ouT|lNouTj引數名><型別>

引數分為
輸入引數:IN
輸出引數:OUT
輸入/輸出引數:INOUT

5.11：建立儲存過程

儲存過程主題部分，被稱為過程體

以BEGIN開始，以END結束，若只有一條SQL語句，則可以省略BEGIN-END

以DELIMITER開始和結束

mysql>DELIMITER $$ //$$是使用者自定義的結束符
//省略儲存過程其他步驟
mysql> DELIMITER; //分號前有空格

5.12儲存過程的語法結構

#定義儲存過程

delimiter $$
create procedure儲存過程名(in 引數名引數型別)
begin

#定義變數

declare變數名變數型別
#變數賦值
set變數名=值
sql語句1;
sql語句2;
…
sql語句n;
end$
delimiter;

#呼叫儲存過程

call儲存過程名(實際引數);

#查詢儲存過程

show procedure status where db='資料庫';

#刪除儲存過程

drop procedure儲存過程名;

例項：

建立資料庫

mysql> delimiter $$     #將語句的結束符從分號開始；臨時改為兩個$$
mysql> create procedure TT()
    -> begin
    -> select name,score from tt;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;       #將語句的結束符恢復為分號

查詢儲存過程

mysql> mysql> show procedure status where db='tt';
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| tt | TT   | PROCEDURE | root@localhost | 2020-08-25 19:55:07 | 2020-08-25 19:55:07 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

#不做為where條件篩選會輸出的東西比較多

呼叫儲存過程：

mysql> call TT();
+----------+-------+
| name     | score |
+----------+-------+
| wangwu   | 75.00 |
| lisi     | 80.00 |
| zhangsan | 80.00 |
| zhaoliu  | 65.00 |
| tianqi   | 80.00 |
| shuaige  | 75.00 |
| tom      | 87.00 |
| shuaige  | 81.00 |
+----------+-------+
8 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

5.13：儲存過程的引數

MySQL儲存過程的引數用在儲存過程的定義，共有三種引數型別,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 儲存過程名([[IN |OUT |INOUT ] 引數名 資料類形...])

IN 輸入引數：表示呼叫者向過程傳入值（傳入值可以是字面量或變數）
OUT 輸出引數：表示過程向呼叫者傳出值(可以返回多個值)（傳出值只能是變數）
INOUT 輸入輸出引數：既表示呼叫者向過程傳入值，又表示過程向呼叫者傳出值（值只能是變數）
檢視指定使用者資訊

mysql> delimiter $$
mysql> create procedure PP(in my_name varchar(10))
    -> begin
    -> select name,score from tt where name=my_name;
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

#查詢儲存
mysql> show procedure status where db='tt';
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| tt | PP   | PROCEDURE | root@localhost | 2020-08-25 22:05:04 | 2020-08-25 22:05:04 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| tt | TT   | PROCEDURE | root@localhost | 2020-08-25 19:55:07 | 2020-08-25 19:55:07 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.01 sec)

#實現呼叫
mysql> call PP('lisi');
+------+-------+
| name | score |
+------+-------+
| lisi | 80.00 |
+------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

加入控制語句

需求：輸入一個成績

把lisi進行分組 輸入75分以上被分配到第一組 75以下改為第二組

mysql> create procedure CC(in my_score decimal(5,2))
    -> begin
    -> declare hob int(3);
    -> if my_score >=75 then
    -> set hob = 1;
    -> else
    -> set hob = 2;
    -> end if;
    -> update tt set score=my_score,hobby=hob where name='lisi';
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

呼叫儲存

mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 75.00 | beijing  |     2 |
|  2 | lisi     | 80.00 | nanjing  |     1 |
|  3 | zhangsan | 80.00 | beijing  |     1 |
|  4 | zhaoliu  | 65.00 | nanjing  |     2 |
|  5 | tianqi   | 80.00 | beijing  |     1 |
|  6 | shuaige  | 75.00 | suzhou   |     2 |
|  7 | tom      | 87.00 | shanghai |     1 |
|  8 | shuaige  | 81.00 | hangzhou |     2 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)

#我們設定成績76分看一下分組情況
mysql> call CC(76);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | wangwu   | 75.00 | beijing  |     2 |
|  2 | lisi     | 76.00 | nanjing  |     1 |
|  3 | zhangsan | 80.00 | beijing  |     1 |
|  4 | zhaoliu  | 65.00 | nanjing  |     2 |
|  5 | tianqi   | 80.00 | beijing  |     1 |
|  6 | shuaige  | 75.00 | suzhou   |     2 |
|  7 | tom      | 87.00 | shanghai |     1 |
|  8 | shuaige  | 81.00 | hangzhou |     2 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)
#以改好

內容較多，便於自己學習回顧

相關文章