mysql-sql高級應用_ZenDei技術網路在線

sql語言進階典型操作 order by - select * from play_list order by createtime; - select * from play_list order by bookedcount desc,createtime asc; order by 語句用於 ...

sql語言進階 典型操作

order by

- select * from play_list order by createtime;
- select * from play_list order by bookedcount desc,createtime asc;

order by 語句用於根據指定的列對結果集進行排序 order by 語句預設按照升序對記錄排序，使用desc則降序排序 order by 也可以多個欄位排序，而desc只作用於一個欄位； distinct

     select distinct userid from play_list;
     select distinct userid,play_name from play_list;    
    （userid,play_named都相同時去重）

distinct 用於去重，可以返回多列的唯一組合； distinct 在後臺做排序，所以很消耗CUP的； group by having 場景：統計雲音樂創建歌單的用戶列表和每人創建歌單的數量

mysql> select userid,count(*) AS play_num from play_list group by userid having count(*)>=2;

分組關鍵字userid，需要在查詢中出現，且一般查詢分組關鍵字後要加聚合函數； like

   select * from play_list where play_name like '%男孩%'；

通配符	描述
%	代替一個或多個字元
_	替代單個字元
[charlist]	中括弧中的任何單一字元
[^charlist] 或者 [!charlist]	不在中括弧中的任何單一字元

大表慎用like; 除了%號在最右邊以外，會對錶中所有記錄進行查詢匹配； limit offset 場景4：查詢一個月內創建的歌單（從第6行開始顯示10條記錄）

select * from play_list where (createtime between 1427701323 and 1430383307) limit 10 offset 6

註意：offset 後面的值不要太大，假設offset1000，那它會掃描前1000條記錄，這樣IO開銷會很大 case when case when 實現類似編程語言的 if else 功能，可以對SQL的輸出結果進行選擇判斷；場景5：對於未錄入的歌單（trackcount = null）,輸出結果時歌曲數返回0

mysql> select play_name,case when trackcount is null then 0 else trackcount end from play_list;

連接-join 用一個SQL 語句把多個表中相互關聯的數據查詢出來；場景6：查詢收藏“老男孩”歌單的用戶列表

mysql> SELECT play_fav.userid FROM play_fav INNER JOIN play_list ON play_fav.play_id = play_list.id where play_list.play_name = '老男孩';

另一種寫法：

mysql> select f.userid from play_list lst,play_fav f where lst.id = f.play_id and lst.play_name = '老男孩'

子查詢及問題

子查詢的寫法：

select userid from play_fav where play_id=(select id from play_list where play_name = '老男孩');

別名可以不用使用AS 關鍵字，直接空格加別名就可以了；子查詢在性能上有一定劣勢，不利於mysql性能優化器進行優化；因為內層表和驅動表用戶自己定死了，而聯結的驅動表和內層表性能優化器會根據實際情況來定；子查詢為什麼不利於優化：聯結是嵌套迴圈查詢實現；如select * from play_list,play_fav where play_list.id = play_fav.play_id; play_list驅動表（where等號左邊）；內層表play_fav（where等號右邊）; 遍歷去東北play_list.id，找到一個id後再去play_fav.play_id中找；依次迴圈下去；內層表此時可以查詢一次或者幾次索引，便可以得到；所以基本的優化就是將表量比較小的作為驅動表，這樣減少了迴圈的次數； union 作用：把不同表中相同的欄位聚合在一個結果集中返回給用戶場景8：老闆想看創建和收藏歌單的所有用戶，查詢play_list和play_fav兩表中所有的userid;

mysql> select userid from play_list
         -> union
         -> select userid from play_fav;

預設的union 會對結果集進行去重處理，不想去重使用 union all； DML進階語法

多值插入：insert into table values（.....）,(.....)
覆蓋插入：replace into table values(...)
忽略插入：insert ignore into table values(...)
查詢插入：insert into table_a select * from table_b

多值插入：減少資料庫訪問次數，提高效率；覆蓋插入，忽略插入：簡化業務邏輯的判斷；避免主鍵重覆；查詢插入：導表結構中； insert主鍵重覆則update： insert into table tb1 values(id,col1,col2) on duplicate key update col2=....;

mysql> insert into  a values(1,100) on duplicate key update age=100;

如果id = 1存在，則鍵age 更改為100；註意得是主鍵噢，如何表中沒有設置主鍵，則會新增加一條記錄；而給表a增加主鍵則是：

mysql> alter table order add primary key （id）;

連表update

用B表中的age 更新到 A 表中的age :

mysql> select * from a;
+----+------+
| id | age  |
+----+------+
|  1 |  100 |
|  2 |   34 |
|  3 |   23 |
|  4 |   29 |
+----+------+
mysql> select * from b;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | pw   |   20 |
|    2 | ljb  |   30 |
+------+------+------+
mysql> update a,b set a.age=b.age where a.id = b.id;
mysql> select * from a;
+----+------+
| id | age  |
+----+------+
|  1 |   20 |
|  2 |   30 |
|  3 |   23 |
|  4 |   29 |
+----+------+

連表delete 用B表中的條件去刪除A表中數據；

mysql> select * from a;
+----+------+
| id | age  |
+----+------+
|  1 |   20 |
|  2 |   30 |
|  3 |   23 |
|  4 |   29 |
+----+------+
mysql> select * from b;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | pw   |   20 |
|    2 | ljb  |   30 |
+------+------+------+
mysql> delete a from a,b where a.id=b.id and b.name='pw';
mysql> select * from a;
+----+------+
| id | age  |
+----+------+
|  2 |   30 |
|  3 |   23 |
|  4 |   29 |
+----+------+

刪除語法：

DELETE FROM Customers
WHERE cust_id = '1000000006';

連表在delete之後還要將需要刪除的表放在delete後面； 內置函數

目標：掌握常用的mysql聚合函數，預定義函數
在SQL查詢語句中運用上述函數結構group by,order by等語法完成各種統計功能

聚合函數

聚合函數面向一組數據，對數據進行聚合運算後返回單一的值
mysql聚合函數基本語法：select function（列） from 表

常用聚合函數：

場景：查詢播放次數最多的歌曲

mysql> select song_name,max(playcount) from song_list; //錯誤查法
#select song_name,沒有對應 playcount；
#註意聚合函數是對返回列來做處理的，此中放回列是所有歌曲；
 
mysql> select song_name,playcount from song_list order by playcount desc limit1；//正確
 
子查詢方法：
select song_name from song_list where playcount=(select max(playcount) from song_list);

場景：顯示每張專輯的歌曲列表。例如：

mysql> select album,group_concat(song_name) from song_list group by album;
+------------------+-------------------------------------------------+
| album            | group_concat(song_name)                  |
+------------------+-------------------------------------------------+
| 1701             | 大象,定西                                        |
| Straight Shooter | Good Lovin' Gone Bad,Weep No More,Shooting Star |
| 作品李宗盛         | 風櫃來的人                                       |
| 紅雪蓮            | 紅雪蓮                                           |
+------------------+-------------------------------------------------+

group_concat 連接的最長字元是1024，可以通過參數調整；使用聚合函數做資料庫行列轉換：

預定義函數：

預定義函數面向單一值數據，返回一對一的處理結果（聚合函數可以理解成多對一）
預定義函數基本語法：select function（列） from 表；select * from 表 where 列 = function（value）

時間處理函數：

總結

order by
distinct
limit offset:
case when then else end
連接-join兩種寫法
子查詢為什麼不利於優化：優化器不能改變，驅動表，內層表；從而不能優化；驅動表一般表量較小，因其需要全表id；內層表，僅需要查找一個或幾個索引；這就是jion後優化器工作
union：把不同表中相同欄位聚合在一個結果集中
連表update，根據B表age值更新A表age：update a,b set a.age=b.age where a.id = b.id;
連表delete，根據B表name刪除A表的數據：delete a from a,b where a.id=b.id and b.name='pw';
聚合函數：AVG(),COUNT(),COUNT(DISTNCT),MAX(),MIN(),SUM()常與 group by,order by連用；
group_concat()mysql特有
預定義函數