MySQL Notes

MySQL Coursera Note

Prepare Env (on Ubuntu)

Install MySQL

sudo apt update && sudo apt install mysql-server

Put server on

sudo service mysql start

Log in to the server

mysql -u root -p

預設密碼為root

2. WHERE

創建所有關聯式資料集臨時表後再過濾資料

Basic operations (On MySQL-cli)

顯示Server上所有資料庫

SHOW DATABASES;

創建資料庫

CREATA DATABASES "Database's name";

選擇使用的資料庫

USE "Database's name";

創建表格

CREATE TABLE "Table's name"(
    "Column's name" "DataType" ("limmit"),
    "Column's name" "DataType" ("limmit"),
    .
    .
    .
);

顯示表格內各欄位設定

DESCRIBE "Table's name";

新增資料至表格

INSERT INTO "Table's name" ("column1", "column2", ...) VALUES ("Data1", "Data2", ...);

從表格刪除資料(有條件)

DELETE FROM "Table's name" (WHERE "Condition");

更新表格內資料

UPDATE "Table's name" SET "Target column's name" = "New content" WHERE "Target column's name" = "Target content"

顯示表格內資料(帶條件)(排序)(特定關鍵字)

SELECT * FROM "Table's name" (WHERE "condition") (ORDER BY "Column's name") (WHERE "Column's name" LIKE '%"Keyword"%');

計算表格內資料筆數(帶條件)

SELECT COUNT(*) FROM "Table's name" (WHERE "Condition");

修改Table內Column

ALTER TABLE "Table's name" 
DROP/ADD COLUMN "column's name";

---

Data Types

String

Understand character sets and indexable for searching

• CHAR : Allocates the entire space (for small strings and unknow length)
• VARCHAR : Depending on the date length (less space)

Usage: normal string data

Text

Have a character set but not used with index or sorting and can only prefix

• TINYTEXT : up to 255 characters
• TEXT : up to 65K
• MEDIUMTEXT : up to 16M
• LONGTEXT : up to 16G

Usage: paragraphs or HTML pages

Binary Type

8-32 bits depending on character set and not indexed or sorted

• BYTE : up to 255 bytes
• VARBINARY : up to 65K bytes

Usage: small image or sensor signal

Binary Large Object(BLOB)

No translation, index, or character set

• TINYBLOB : up to 255 bytes
• BLOB : up to 65K
• MEDIUMBLOB : up to 16M
• LONGBLOB : up to 4G

Usage: Large raw data, files, images, word doc, PDFs, movies

Interger

little storage, easy to compare, sort , indexed

• TINYINT : (-128, +128)
• SMALLINT : (-32768, +32768)
• INT : (2 Billion)
• BIGINT : (10**18 ish)

Usage: small image or sensor signal

Float

Wide range of values but limmilted accuracy

• FLOAT(32-bit) : 10**38 with 7 digits accuracy
• DOUBLE(64-bit) : 10**308 with 14 digits accuracy

Usage: Scientific data

Dates

Represent time with integer

• TIMESTAMP : ‘YYYY-MM-DD HH:MM:SS’(1970, 2037)
• DATETIME : ‘YYYY-MM-DD HH:MM:SS’
• DATE : ‘YYYY-MM-DD’
• TIME: ‘HH:MM:SS’

Buit-in MySQL function NOW()

---

關聯式資料集

1. 構思資料庫關係圖

1. Find the core of the database
2. Use the core to create the first table
3. Put non-duplicate columns into the first table
4. Find relation between other columns and point to more table

2. 標準資料型態

• Do not replicate data, use point
• Use integers for keys
• Add special ‘key’ column to each table, for reference (By AUTO_INCREMENT)

3. 創建關聯式資料集

code

4. 創建臨時表格

• Merge multilple databases and list all combinations

Database1 JOIN Database2 JOIN ...

• Filter to prevent JOIN from output all the combinations

ON "Foreign key of Database1" = "Foreign key of database2"

• Automatically delete date while related data was updated

ON DELETE CASCADE

5. 多對多關聯式資料集

Types of keys

• Primary key : Generally an integer AUTO_INCREMENT field
• Logical key : Generally a string for UI application to search (do not use logical key as primary key!!!)
• Foreign key : Generally an integer pointing to another table

條件判斷式差別

資料處理順序

掃描資料集關聯 => 創建臨時表 => 進行計算 => 輸出

1. ON

過濾關聯式資料集後創建臨時表

2. WHERE

創建所有關聯式資料集臨時表後過濾資料

3. HAVING

創建所有關聯式資料集臨時表並進行完統計後再輸出前過濾

運行效率

處理資料數量由上至下遞減，也因此處理速度由上至下減慢

踩雷筆記

GROUP BY 錯誤

使用資料庫

code

操作

SELECT DISTINCT (Genre.name), Artist.name
FROM Track JOIN Album JOIN Artist JOIN Genre
WHERE Track.album_id = Album.album_id AND Track.genre_id = Genre.genre_id and Artist.artist_id = Album.artist_id
GROUP BY Artist.name;

報錯

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'music.Genre.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解決

Terminal

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

最後一行加上

sql_mode = ""

---