Day.21 从零开始 - 实务需求学SQL_2

延续昨天的内容介绍活动的新增改删以及表之间的查询。

• 新增活动资料

语法: 
INSERT INTO table_name (column1, column2...)
VALUES (value1, value2...);

INSERT INTO act (status,start_time,end_time,platform_id,updated_at,created_at) values(1,1611504000,1632931200,10868213102191,1632499200,1632499200);

• 更新活动资料

语法: 
UPDATE table_name
SET column1=value1, column2=value2, ···
[WHERE Clause];

UPDATE act SET status = 5 WHERE actives_id = 1 ; 

• 删除活动资料

语法: 
DELETE FROM table_name 
[WHERE Clause];  

ps.没加WHERE条件会资料全删除。

DELETE FROM act WHERE actives_id = 1 ; 

(补充)

问题: 当我们不确定资料是否已经存在而进行insert动作，会碰到资料已存在的问题而失败。

假设我新增了一组相同PK的纪录，就算後面栏位值有变化只要PK重复就无法新增(因为PK的唯一性)。

mysql> INSERT INTO act (actives_id,status,start_time,end_time,platform_id,updated_at,created_at) values(1,1,1611504000,1632931200,10868213102191,1632499200,1632499200);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

如果遇到上述问题，一般来说我们可以先SELECT在做INSERT去避免此状况，不过会变成要写2句语法～

这时就可以运用INSERT ... ON DUPLICATE KEY UPDATE语法一行就能实现功能。

• 资料不存在 -> INSERT

• 资料已存在 -> UPDATE

语法: INSERT ... ON DUPLICATE KEY UPDATE 

功能: 判断该主键是否重覆，如果重覆就会使用UPDATE该纪录来完成。

必要条件:

  资料表中必须有 PRIMARY/ UNIQUE ， INSERT 部分要带入主索引的栏位资料。

INSERT INTO act (actives_id,status,start_time,end_time,platform_id,updated_at,created_at)
values(1,1,1611504000,1632931200,10868213102191,1632499200,1632499200) ON DUPLICATE KEY UPDATE `status`=2 ;

上面这句等於当actives_id = 1的纪录已存在的话，就更新status为2 。反之不存在则新增该纪录

• 搜索字段中的指定内容(LIKE 用法)

假设我要查询游戏名称中包含ITHOME的纪录。

语法: 
(1)包含    .... WHERE field LIKE pattern   
(2)不包含  .... WHERE field NOT LIKE pattern

ps. 搭配％使用  ex. %ITHOME (名称字尾为ITHOME)， %ITHOME% (名称中包含ITHOME)

mysql> select * from act_game;
+------------+-----------------+-------------------------------+------+------------+
| actives_id | game_name       | support_rules                 | type | created_at |
+------------+-----------------+-------------------------------+------+------------+
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] |    7 | 1632499200 |
+------------+-----------------+-------------------------------+------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM act_game WHERE game_name like '%ITHOME%';
+------------+-----------------+-------------------------------+------+------------+
| actives_id | game_name       | support_rules                 | type | created_at |
+------------+-----------------+-------------------------------+------+------------+
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] |    7 | 1632499200 |
+------------+-----------------+-------------------------------+------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM act_game WHERE game_name not like '%ITHOME%';
Empty set (0.00 sec)

注意: 在语句使用like模糊匹配查询的方式时，会有几种造成吃不到INDEX的问题！

注意以下各种查询方式有无吃到game索引～

• 资料排序(ORDER BY 用法)

假设我要查询这次的活动纪录建立时间先後顺序(降序排列)。

语法: 
SELECT column1, column2...
FROM table_name
ORDER BY column_name1 ASC|DESC, column_name2 ...

使用: ASC (升序排列)  /   DESC (降序排列)

mysql> SELECT * FROM act ORDER BY created_at DESC ;

• 资料分组(GROUP BY 用法)

假设我要查询这次的活动纪录一共有几种活动状态(status)。

语法: 

SELECT column1, column2...
FROM table_name
[WHERE Clause]
GROUP BY column_name1, column_name2...;

mysql> SELETE status FROM act GROUP BY status;
+--------+
| status |
+--------+
|      0 |
|      1 |
|      2 |
+--------+
3 rows in set (0.01 sec)

最後我们以用於处理逻辑判断的CASE作为结尾。

• CASE

假设我要查询这次的活动纪录状态是上架(1) or 未上架(0) or 准备中(N)

语法: 

CASE
  WHEN condition THEN result
  WHEN···
  ELSE result
END;


SELECT actives_id, CASE
  WHEN status = 0 THEN 'OFF'
  WHEN status = 1 THEN 'ON'
  ELSE 'READY'
END AS status
FROM act;

+------------+--------+
| actives_id | status |
+------------+--------+
|          1 | ON     |
+------------+--------+
1 row in set (0.00 sec)

明天来说一下应用上常用的 表与表之间的连结查询(JOIN)～