文章目錄

前言
常用查詢
- 一、按關鍵字排序
- 1.單欄位排序
- 2.條件查詢
- 3.多欄位排序
- 4.區間判斷 AND/OR ——且/或
- 5.查詢不重復記錄
- 二、對結果進行分組
- 三、限制結果條目（limit）
- 四、設定別名（alias——>as)
- 五、通配符
- 六、子查詢
- 1.子查詢—exists
- 2.子查詢—as
- 3.視圖
- 4.NULL值

前言

準備模板表

mysql -uroot -p123123

create database puxin;
#創建庫

create table xjj (id int(10) primary key not null auto_increment,name varchar(20),score decimal(5,2),address varchar(40),hobby varchar(20));
#創建表

insert into xjj values(1,'liuyi',80,'beijing',2);
insert into xjj values(2,'wangwu',90,'shengzheng',2);
insert into xjj values(3,'lisi',60,'shanghai',4);
insert into xjj values(4,'tianqi',99,'hangzhou',5);
insert into xjj values(5,'jiaoshou',98,'laowo',3);
insert into xjj values(6,'hanmeimei',10,'nanjing',3);
insert into xjj values(7,'lilei',11,'nanjing',5);
insert into xjj values(8,'caicai',16,'nanjing',5);
#插入表


mysql> select * from xjj;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)

常用查詢

增、刪、改、查
對MySQL資料庫的查詢，除了基本的查詢外，有時候需要對查詢的結果集進行處理，例如只取 10 條資料、對查詢結果進行排序或分組等等

一、按關鍵字排序

使用select陳述句可以將需要的資料從mysql資料庫中查詢出來，如果對查詢的結果進行排序操作，可以使用order by 陳述句完成排序，并且最終將排序后的結果回傳給客戶
類比于windows任務管理器

select 欄位 from 表名 order by 欄位 ASC|DESC

ASC|DESC

ASC是按照升序進行排名的，默認的排序方式，可省略

DESC是按照降序的方式進行排序的
order by 也可以通過 where 子陳述句對查詢結果進行進一步的過濾
可進行多欄位的排序

1.單欄位排序

#按分數排序，默認不指定升序排列 asc
mysql> select id,name,score from xjj order by score;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  6 | hanmeimei | 10.00 |
|  7 | lilei     | 11.00 |
|  8 | caicai    | 16.00 |
|  3 | lisi      | 60.00 |
|  1 | liuyi     | 80.00 |
|  2 | wangwu    | 90.00 |
|  5 | jiaoshou  | 98.00 |
|  4 | tianqi    | 99.00 |
+----+-----------+-------+
8 rows in set (0.00 sec)

#按分數排序，使用desc降序
mysql> select id,name,score from xjj order by score desc;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  4 | tianqi    | 99.00 |
|  5 | jiaoshou  | 98.00 |
|  2 | wangwu    | 90.00 |
|  1 | liuyi     | 80.00 |
|  3 | lisi      | 60.00 |
|  8 | caicai    | 16.00 |
|  7 | lilei     | 11.00 |
|  6 | hanmeimei | 10.00 |
+----+-----------+-------+
8 rows in set (0.00 sec)

2.條件查詢

mysql> select name,score from xjj where address='hangzhou' order by score desc;
+--------+-------+
| name   | score |
+--------+-------+
| tianqi | 99.00 |
+--------+-------+
1 row in set (0.00 sec)

3.多欄位排序

#原則：order by之后的引數，使用 “，”分割，優先級是按先后順序而定

mysql> select id,name,hobby from xjj order by hobby desc,id asc;
+----+-----------+-------+
| id | name      | hobby |
+----+-----------+-------+
|  4 | tianqi    | 5     |
|  7 | lilei     | 5     |
|  8 | caicai    | 5     |
|  3 | lisi      | 4     |
|  5 | jiaoshou  | 3     |
|  6 | hanmeimei | 3     |
|  1 | liuyi     | 2     |
|  2 | wangwu    | 2     |
+----+-----------+-------+
8 rows in set (0.00 sec)

小結：order by之后的第一個引數只有在出現相同的數值，第二個欄位才有意思

4.區間判斷 AND/OR ——且/或

mysql> select * from xjj where score > 70 and score <=90;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
+----+--------+-------+------------+-------+


mysql> select * from xjj where score > 70 or score <=90;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)


mysql> select * from xjj where score > 70 or (score <60 and score >15);
+----+----------+-------+------------+-------+
| id | name     | score | address    | hobby |
+----+----------+-------+------------+-------+
|  1 | liuyi    | 80.00 | beijing    | 2     |
|  2 | wangwu   | 90.00 | shengzheng | 2     |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou | 98.00 | laowo      | 3     |
|  8 | caicai   | 16.00 | nanjing    | 5     |
+----+----------+-------+------------+-------+
5 rows in set (0.00 sec)

5.查詢不重復記錄

select distinct 欄位 from 表名;

distinct 必須放在最開頭

distinct 只能使用需要去重的欄位進行操作

distinct 去重多個欄位，含義是：幾個欄位同時重復時才能被過濾

mysql> select distinct hobby from xjj;
+-------+
| hobby |
+-------+
| 2     |
| 4     |
| 5     |
| 3     |
+-------+
4 rows in set (0.00 sec)


mysql> select name,hobby from xjj where hobby in (select distinct hobby from xjj);
+-----------+-------+
| name      | hobby |
+-----------+-------+
| liuyi     | 2     |
| wangwu    | 2     |
| lisi      | 4     |
| tianqi    | 5     |
| jiaoshou  | 3     |
| hanmeimei | 3     |
| lilei     | 5     |
| caicai    | 5     |
+-----------+-------+
8 rows in set (0.00 sec)

二、對結果進行分組

通過SQL查詢出來的結果，還可以對其進行分組，使用group by 陳述句來實作
group by 通常都是結合聚合函式一起使用的，常用的聚合函式包括：計數（count)、求和（sum）、請平均數（avg)、最大值（max）、最小值（min），group by 分組的時候可以按一個或者多個欄位對結果進行分組處理

select 欄位,聚合函式 from 表名 (where 欄位名(匹配) 數值) group by 欄位名;

#對xjj進行分組，篩選范圍/條件是score大于等于45的 'name',score相同的會默認分在一個組
mysql> select count(name),score from xjj where score>=45 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 | 60.00 |
|           1 | 80.00 |
|           1 | 90.00 |
|           1 | 98.00 |
|           1 | 99.00 |
+-------------+-------+
5 rows in set (0.00 sec)

#對xjj表中所有address相同的內容進行分組
mysql> select count(id),address from xjj group by address;
+-----------+------------+
| count(id) | address    |
+-----------+------------+
|         1 | beijing    |
|         1 | hangzhou   |
|         1 | laowo      |
|         3 | nanjing    |
|         1 | shanghai   |
|         1 | shengzheng |
+-----------+------------+
6 rows in set (0.00 sec)

#基于上一條操作，結合order by把統計的id數量進行按降序序排列
mysql> select count(id), hobby from xjj group by hobby order by count(id)
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
|         3 | 5     |
|         2 | 3     |
|         2 | 2     |
|         1 | 4     |
+-----------+-------+
4 rows in set (0.00 sec)

#結合where陳述句，篩選分數大于等于70的分組，計算學生個數按降序排列
mysql> select count(name),score,hobby from xjj where score>=70 group by hobby order by count(name) desc;
+-------------+-------+-------+
| count(name) | score | hobby |
+-------------+-------+-------+
|           2 | 80.00 | 2     |
|           1 | 99.00 | 5     |
|           1 | 98.00 | 3     |
+-------------+-------+-------+
3 rows in set (0.00 sec)

三、限制結果條目（limit）

在使用mysql select 陳述句進行查詢操作時，結果集回傳的是所有匹配的記錄，有時候僅需回傳第一行或前幾行，這時候就需用到limit 子句

select 欄位 from 表名 limit [offset,] number

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

#查詢所有資訊顯示前4行記錄
mysql> select * from xjj limit 3;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
|  3 | lisi   | 60.00 | shanghai   | 4     |
+----+--------+-------+------------+-------+
3 rows in set (0.00 sec)


#從第4行開始，往后顯示3行內容
mysql> select * from xjj limit 3,3;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  4 | tianqi    | 99.00 | hangzhou | 5     |
|  5 | jiaoshou  | 98.00 | laowo    | 3     |
|  6 | hanmeimei | 10.00 | nanjing  | 3     |
+----+-----------+-------+----------+-------+
3 rows in set (0.00 sec)

#結合order by陳述句，按id的大小升序排列顯示前三行
mysql> select id,name from xjj order by id limit 3;
+----+--------+
| id | name   |
+----+--------+
|  1 | liuyi  |
|  2 | wangwu |
|  3 | lisi   |
+----+--------+
3 rows in set (0.00 sec)

#輸出最后三行
mysql> select id,name from xjj order by id desc limit 3;
+----+-----------+
| id | name      |
+----+-----------+
|  8 | caicai    |
|  7 | lilei     |
|  6 | hanmeimei |
+----+-----------+
3 rows in set (0.00 sec)

四、設定別名（alias——>as)

在mysql查詢時，當表的名字比較長或者表內某些欄位比較長時，為了方便書寫或者多次使用相同的表時，可以給欄位列或表設定別名
方便操作，增強可讀性

列的別名
select 欄位 as 欄位別名 表名

表的別名
select 欄位 from 表名 as 別名 

as 可以省略

#列別名設定示例：
mysql> select name as 姓名,score as 成績 from xjj;
+-----------+--------+
| 姓名      | 成績   |
+-----------+--------+
| liuyi     |  80.00 |
| wangwu    |  90.00 |
| lisi      |  60.00 |
| tianqi    |  99.00 |
| jiaoshou  |  98.00 |
| hanmeimei |  10.00 |
| lilei     |  11.00 |
| caicai    |  16.00 |
+-----------+--------+
8 rows in set (0.00 sec)

#不用as也可以，一樣顯示
mysql> select count(*) number from xjj;
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)

使用場景

對復雜的表進行查詢的時候，別名可以縮短查詢陳述句

多表相連查詢的時候（通俗易懂、簡短SQL陳述句）

此外，AS 還可以作為連接陳述句的運算子
創建yyy表，將xjj表的查詢記錄全部插入yyy表
mysql> create table yyy as select * from xjj;

#此處AS起到的作用：
1、創建了一個新表yyy 并定義表結構，插入表資料（與xjj表相同）
2、但是'約束'沒有被完全'復制'過來 
#但是如果原表設定了主鍵，那么附表的：default欄位會默認設定一個0
相似：
#克隆、復制表結構
create table yyy (select * from xjj);

#也可以加入where 陳述句判斷
mysql> create table aaa as select * from xjj where score >=70;


在為表設定別名時，要保證別名不能與資料庫中的其他表的名稱沖突
列的別名是在結果中有顯示的，而表的別名在結果中沒有顯示，只在執行查詢時使用

五、通配符

主要用于替換字串中的部分字符，通過部分字符的匹配將相關結果查詢出來
通配符都是跟like（模糊查詢）一起使用，并協同where自居共同來完成查詢任務

常用的： %：百分號表示零個、一個或多個字符 _ ：下劃線表示單個字符

#查詢名字是c開頭的記錄
mysql> select id,name from xjj where name like 'c%';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)

#查詢名字里是c和i中間有一個字符的記錄
mysql> select id,name from xjj where name like 'c_ic_i';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)

#查詢名字中間有g的記錄
mysql> select id,name from xjj where name like '%g%';
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)

#通配符“%”和“_”不僅可以單獨使用，也可以組合使用
mysql> select id,name from xjj where name like 'w%_';
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)

六、子查詢

子查詢也被稱作內查詢或嵌套查詢，是指一個查詢陳述句里面嵌套著另一個查詢陳述句
子查詢陳述句是先于主查詢陳述句被執行的，其結果作為外層的條件回傳給主查詢進行下一步的查詢過濾
PS: 子陳述句可以與主陳述句所查詢的表相同，也可以是不同表

mysql> select name,score from xjj where id in (select id from xjj where score > 70);
+----------+-------+
| name     | score |
+----------+-------+
| liuyi    | 80.00 |
| wangwu   | 90.00 |
| tianqi   | 99.00 |
| jiaoshou | 98.00 |
+----------+-------+
4 rows in set (0.00 sec)
#主陳述句：select name,score from info where id
子陳述句(集合)： select id from info where score >80
PS:子陳述句中的sql陳述句是為了，最后過濾出一個結果集，用于主陳述句的判斷條件
in: 將主表和子表關聯/連接的語法

子查詢不僅可以在select 中使用，在insert、update、delete中同樣可以使用
支持多層嵌套
in陳述句是用來判斷某個值是否在給定的集合內（結果集），in往往和select搭配使用
可以使用 not in 來進行對結果集取反

mysql> create table ttt(id int(4));
mysql> insert into ttt values(1),(2),(3);

#多表查詢
mysql> select id,name,score from xjj where id in (select * from ttt); 
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | liuyi  | 80.00 |
|  2 | wangwu | 90.00 |
|  3 | lisi   | 60.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

1.子查詢—exists

exists 這個關鍵字在子查詢時，主要用于判斷exists之后的條件是否成立，如果成立，則正常執行主陳述句的匹配，如不成立，則不會執行主陳述句查詢，
如子查詢結果集不成立的話，輸出為null

count為計數，sum為求和，使用sum求和結合exists，如子查詢結果集不成立的話，輸出為null

#查詢如果存在分數等于80的記錄則計算xjj的欄位數
mysql> select count(*) from xjj where exists(select id from xjj where score=80);
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

#查詢如果存在分數小于50的記錄則計算xjj的欄位數，xjj表沒有小于50的，所以回傳0
mysql> select count(*) from xjj where exists(select id xjj where score<60);
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

2.子查詢—as

例：
select name,hobby from xjj;
以上命令可以查詢一張表的name和hobby資訊

將結果作為“表”進行查詢的時候，我們也需要用到別名
比如我們輸入：select hobby from (select name,hobby from xjj);
會報錯，因為 select name,hobby from xjj 得到的是一個結果集，而不是表，mysql 是不能識別的
所以我們需要對結果集設定一個別名，這樣mysql就能將結果集視為一張表

mysql> select a.id from (select id,name from xjj) a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)
相當于
select xjj.id,name from xjj;
select 表.欄位，欄位 from 表；

3.視圖

視圖是從一個或多個表中匯出來的表，是一種虛擬存在的表
資料庫中只存放了視圖的定義，而沒有存放視圖中的資料，這些資料存放在原來的表中
使用視圖查詢資料時，資料庫系統會從原來的表中取出對應的資料
視圖中的資料依賴于原來表中的資料，一旦表中資料發生改變，顯示在視圖中的資料也會發生改變
資料庫中的虛擬表，這張虛擬表中不包含真實資料，只是做了映射
鏡花水月/倒影，動態保存結果集（資料）
作用：
1.使操作簡單化，可以對經常使用的查詢定義一個視圖，使用戶不必為同樣的查詢操作指定條件

2.增加資料的安全性，通過視圖，用戶只能查詢和修改指定的資料

3.提高表的邏輯獨立性，視圖可以屏蔽原有表結構變化帶來的影響

使用視圖的大部分情況時為了保障資料安全性，提高查詢效率
根據篩選條件創建了一張虛擬表，當修改表中資料時，原表中的資料不再滿足篩選條件時，視圖中的表將不會再顯示此資料

需求：滿足80分的學生展示在視圖中
PS：這個結果會動態變化，同時可以給不同的人群（例如權限范圍）展示不同的視圖
#創建視圖
create view v_score as select * from info where score>=80;

show table status\G

#查看視圖
mysql> select * from v_score;
+----+----------+-------+------------+-------+
| id | name     | score | address    | hobby |
+----+----------+-------+------------+-------+
|  1 | liuyi    | 80.00 | beijing    | 2     |
|  2 | wangwu   | 90.00 | shengzheng | 2     |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou | 98.00 | laowo      | 3     |
+----+----------+-------+------------+-------+
4 rows in set (0.00 sec)

#修改原表資料
update info set score='60' where name='wangwu';

#查看視圖
mysql> select * from v_score;
+----+----------+-------+----------+-------+
| id | name     | score | address  | hobby |
+----+----------+-------+----------+-------+
|  1 | liuyi    | 80.00 | beijing  | 2     |
|  4 | tianqi   | 99.00 | hangzhou | 5     |
|  5 | jiaoshou | 98.00 | laowo    | 3     |
+----+----------+-------+----------+-------+
3 rows in set (0.00 sec)

4.NULL值

null值與空值的區別(空氣與真空)
null，不占空間
空字符和0，占用空間
is null無法判斷空值
空值使用"=“或者”<>"來處理（！=）
count（）計算時，NULL會忽略，空值會加入計算

插入一條記錄，分數欄位輸入null，顯示出來就是null

#驗證：
 alter table xjj add column addr varchar(50);

update xjj set addr='nj' where score >=70;

#統計數量：檢測null是否會加入統計中
select count(addr) from xjj;

#將info表中其中一條資料修改為空值''
update xjj set addr='' where name='wangwu';

#統計數量，檢測空值是不會被添加到統計中
mysql> select count(addr) from xjj;
+-------------+
| count(addr) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

#查詢null值
mysql> select * from xjj where addr is null;
+----+-----------+-------+----------+-------+------+
| id | name      | score | address  | hobby | addr |
+----+-----------+-------+----------+-------+------+
|  1 | liuyi     | 80.00 | beijing  | 2     | NULL |
|  3 | lisi      | 60.00 | shanghai | 4     | NULL |
|  4 | tianqi    | 99.00 | hangzhou | 5     | NULL |
|  5 | jiaoshou  | 98.00 | laowo    | 3     | NULL |
|  6 | hanmeimei | 10.00 | nanjing  | 3     | NULL |
|  7 | lilei     | 11.00 | nanjing  | 5     | NULL |
|  8 | caicai    | 16.00 | nanjing  | 5     | NULL |
+----+-----------+-------+----------+-------+------+
#空值資料: select count(*) from YourTable where Your Column Name is null

#查詢不為空的值
mysql> select * from xjj where addr is not null;
+----+--------+-------+------------+-------+------+
| id | name   | score | address    | hobby | addr |
+----+--------+-------+------------+-------+------+
|  2 | wangwu | 60.00 | shengzheng | 2     |      |
+----+--------+-------+------------+-------+------+
#非空值資料: select count(*) from YourTable where YourColumnName is not null

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340506.html

標籤：其他

上一篇：【資料庫】-- MySQL中比like更高效的三個寫法

下一篇：用React中的狀態分配值替換用戶輸入的值

Mysql資料庫——高階陳述句（上）