MySQL 秒入門/秒上手

建立table

Create Table

CREATE TABLE IF NOT EXISTS articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(256) NOT NULL,
  author VARCHAR(20) NOT NULL,
  content TEXT(1024),
  viewCnt BIGINT,
  createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
  lastModified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入資料

INSERT INTO articles (title, author, browsingCnt) VALUES("this is title", "welson", 0);

更新資料

UPDATE articles SET title="title modified", viewCnt=viewCnt+1 WHERE id=1

增加/刪除/變更欄位

# 增加欄位
ALTER TABLE users ADD (email VARCHAR(120));
ALTER TABLE users ADD COLUMN (email VARCHAR(120));
ALTER TABLE users ADD COLUMN (email VARCHAR(120) NOT NULL DEFAULT '', age INT DEFAULT 13);
# 刪除欄位
ALTER TABLE articles DROP email;
ALTER TABLE articles DROP COLUMN email;
# 變更欄位
ALTER TABLE articles CHANGE $old_col_name $column_definition;
ALTER TABLE articles MODIFY $col_name $column_definition;
    ## 以下同效
    ALTER TABLE articles CHANGE email email VAHRCHAR(100);
    ALTER TABLE articles MODIFY email VAHRCHAR(100);

基本query

SELECT * FROM articles;
SELECT * FROM articles WHERE id=1;
SELECT * FROM articles WHERE title LIKE '%hello%' ORDER BY createTime DESC LIMIT 2,10;
SELECT * FROM articles WHERE title LIKE '%hello%' ORDER BY createTime DESC LIMIT 10 OFFSET 0;

ps: '%'是mysql的萬用字元

Join

join(結合)，是指資料交集的操作 JOIN分為INNER JOIN, LEFT JOIN, RIGHT JOIN三種

今有 <table1>

PK FK
1 a1
2 a2
3 a3

<table2>

PK Desc
a2 a2很棒
a3 a3很棒
a4 a4很棒
  • INNER JOIN: 完全符合交集條件的資料集合
    SELECT * FROM table1, table2 WHERE table1.FK=table2.PK
Table1_PK Table1_FK Table2_Desc
2 a2 a2很棒
3 a3 a3很棒
  • LEFT JOIN：符合交集條件，且左表格的資料都要出來
    SELECT * FROM table1 LEFT JOIN table2 on table1.FK = table2.PK
Table1_PK Table1_FK Table2_Desc
1 null null
2 a2 a2很棒
3 a3 a3很棒
  • RIGHT JOIN：符合交集條件，且右表格的資料都要出來
    SELECT * FROM table1 RIGHT JOIN table2 on table1.FK = table2.PK
Table1_PK Table2_PK Table2_Desc
2 a2 2很棒
3 a3 a3很棒
null a4 a4很棒

多對多

一般多對多關係，需用另一張table記錄 討論「使用者vs商品」的多對多關係 假設有和兩張表格，使用另一張表格記錄其關係

<users>:

id name
1 welson
2 jordan

<products>:

id name
1 p1
2 p2
3 p3
4 p4

<productsOfUsers>:

uid pid
1 1
1 2
2 3
2 4
2 1
  • Q: user=welson的商品清單？
    A: 期望得到如下join過後的表格
uid name pid name
1 welson 1 p1
1 welson 2 p2

顯然，後兩欄要由和兩個join, 之後再和join.

(Step1): join出1,3,4欄，即 uid, pid 與 product.name 的關係

SELECT pu.uid,pu.pid,p.name FROM productsOfUsers pu INNER JOIN products p ON pu.pid=p.id;

(Step2): join出與上述的關係

SELECT k.uid,u.name,k.pid,k.name FROM users u INNER JOIN ( ${Step1的結果} ) AS k ON u.id=k.uid;

即

SELECT k.uid,u.name,k.pid,k.name FROM users u INNER JOIN ( SELECT pu.uid,pu.pid,p.name FROM productsOfUsers pu INNER JOIN products p ON pu.pid=p.id ) AS k ON u.id=k.uid;

(Step3): 篩選出user.id=1

SELECT k.uid,u.name,k.pid,k.name FROM users u INNER JOIN ( SELECT pu.uid,pu.pid,p.name FROM productsOfUsers pu INNER JOIN products p ON pu.pid=p.id ) AS k ON u.id=k.uid WHERE k.uid=1 ORDER BY k.name ASC;

ps: () AS k 很重要，避免 Every derived table must have its own alias. 錯誤

  • Q: 誰買過商品p1？
    A: 期望得到如下join過後的表格
uid name pid name
1 welson 1 p1
2 jordan 1 p1

顯然，和上一題一樣，只有where的部份不同

SELECT k.uid,u.name,k.pid,k.name FROM users u INNER JOIN ( SELECT pu.uid,pu.pid,p.name FROM productsOfUsers pu INNER JOIN products p ON pu.pid=p.id ) AS k ON u.id=k.uid WHERE k.pid=1 ORDER BY k.name ASC;

參考

results matching ""

    No results matching ""