Leon's Blogging

Coding blogging for hackers.

Sql 好用的 Command

| Comments

最近常常使用 sql，因應各種需求，也發現一些蠻好用的指令~

tutorial

FIELD()

FIELD 會 return 第一個參數的，position

1
2
3
4
5
6
7

-- 第一個參數 "c", 在後面第三個位置
SELECT FIELD("c", "a", "b", "c", "d", "e");
-- 3

-- 如果找不到就會是 0
SELECT FIELD("f", "a", "b", "c", "d", "e");
-- 0

可以搭配 order，讓取出來的順序按照原本給的參數

1
2
3

user_ids = [3, 5, 1, 2, 4]

users = User.where(id: user_ids).order("FIELD(id, #{user_ids.join(',')})")

1

SELECT `users`.* FROM `users` WHERE `users`.`id` IN (3, 5, 1, 2, 4)  ORDER BY FIELD(id, 3,5,1,2,4)"

GROUP_CONCAT

可以透過 group 將所有的 book 做分類，並且依照分類將 book name group 起來

1
2
3
4
5
6

# 取出 user 底下所有的 books，並且照 books_type, books_name(會是 string 串聯起來)

User.select("books.type AS book_type, GROUP_CONCAT(DISTINCT books.name SEPARATOR ', ') as book_name")
    .joins("LEFT JOIN books ON users.id = books.user_id")
    .where(id: 8)
    .group("books.type")

1
2
3
4
5

SELECT user.id, books.type, GROUP_CONCAT(DISTINCT books.name SEPARATOR ', ') as book_name
FROM users
LEFT JOIN books ON users.id = books.user_id
WHERE user.id = 8
GROUP BY books.type

也可以排序

1

SELECT GROUP_CONCAT(DISTINCT books.id ORDER BY books.id ASC SEPARATOR ', ') as book_name

CONCAT

可以將多的 column 串在一起

1

CONCAT(字串1, 字串2, 字串3, ...)

IFNULL 

1
2

-- 如果x不是NULL(不包含0)，IFNULL()返回x，否則它返回y。
IFNULL(x, y)

CONVERT 

1
2

-- 將時間轉成 date
CONVERT(created_at, date)

CASE

像 if else 一樣，可以根據條件，給不同的值

1
2
3
4
5
6
7

SELECT CASE ("欄位名")
  WHEN "條件1" THEN "結果1"
  WHEN "條件2" THEN "結果2"
  ...
  [ELSE "結果N"]
  END
FROM "表格名";

LOWER() & UPPER()

將字串轉小寫或大寫

1
2

LOWER("HI");
UPPER("hi");

AUTO_INCREMENT 

1

ALTER TABLE `pre_campaign_details_channels` CHANGE `id` `id` INT(11)  NOT NULL  AUTO_INCREMENT , ADD UNIQUE (`id`);

unix_timestamp

date 轉成 Unix Timestamp

2018/01/01 -> 1514736000(s) -> 1514736000000(ms)

1
2
3

SELECT unix_timestamp(my_datetime_column) as stamp
-- milliseconds
SELECT unix_timestamp(my_datetime_column) * 1000 as stamp

Convert MySql DateTime stamp into JavaScript’s Date format

BETWEEN

可以直接指定在某個區段的時間

1
2
3

SELECT *
FROM Store_Information
WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';

SQL Between

Time 

1
2
3
4
5
6
7

SELECT NOW(), CURTIME(), CURDATE(), (CURDATE() - INTERVAL 1 DAY), (CURDATE() - INTERVAL 1 MONTH)
-- 2018-09-20 17:20:48
-- 17:20:48
-- 2018-09-20
-- 2018-09-19
-- 2018-10-20
-- INTERVAL - 可以將時間去做加減

specify order

指定順序

1
2
3
4
5
6
7
8
9

SELECT `users`.*
FROM `users`
WHERE `users`.`id` IN (11, 13, 3, 5, 7, 9)
ORDER BY
  CASE
  WHEN `users`.`id`=11 THEN 0
  WHEN `users`.`id`=13 THEN 1
  WHEN `users`.`id`=9 THEN 2
  ELSE 3 END ASC

Table View

虛擬表格。它跟表格的不同是，表格中有實際儲存資料，而視觀表是建立在表格之上的一個架構，它本身並不實際儲存資料。

1
2

CREATE VIEW "視觀表名"
AS "SQL SELECT 語句";

1
2

CREATE VIEW OR REPLACE "視觀表名"
AS "SQL SELECT 語句";

Grant

SQL GRANT 授與資料庫使用權限

1

GRANT type_of_permission ON database_name.table_name TO 'username'@'hostname';

1

GRANT ALL PRIVILEGES ON *.* TO 'mike'@'%';

Reference

Comments