MySQL 學(xué)習(xí)筆記
1 定義
數(shù)據(jù)庫中的表：一行叫一條記錄藻雌。每一列叫一個屬性雌续，或一個字段。
主鍵：表中的某個特殊字段胯杭，具有唯一的確定的值驯杜，可以根據(jù)該字段唯一的確定一條記錄
外鍵：表中的某個字段的值為另一張表（也可以是自身）中的某個字段的值。
2 MySQL 的數(shù)據(jù)類型
2.1 數(shù)值
- TINYINT 小整型
- INT/INTEGER 整型
- BIGINT 長整型
- FLOAT 小數(shù)
- DOUBLIE 雙精度小數(shù)
2.2 日期
- DATE YYYY-MM-DD
- TIME HH:MM:SS
- YEAT YYYY
- DATETIME 混合
- TIMESTAMP 高精度
2.3 字符串
- CHAR 定長字符串
- VARCHAR 不定長
- BLOB 二進(jìn)制文件
- LONGBLOB 長二進(jìn)制文件
- TEXT 長文本數(shù)據(jù)
- LONGTEXT 長文本數(shù)據(jù)
注：以上僅總結(jié)了常用類型做个，并不是所有類型
3 SQL語句
3.1 數(shù)據(jù)庫的創(chuàng)建
CREATE DATABASE 數(shù)據(jù)庫名 [參數(shù)]
例如創(chuàng)建一個名為test的數(shù)據(jù)庫鸽心，設(shè)置字符集為utf-8
CREATE DATABASE test CHARACTER SET utf8;
出現(xiàn)下面的內(nèi)容即說明創(chuàng)建成功。
Query OK, 0 rows affected (0.00 sec)
你可以使用下面的語句查看新創(chuàng)建的數(shù)據(jù)庫
SHOW DATABASES;
選擇需要操作的數(shù)據(jù)庫
USE test;
執(zhí)行完上面的語句后出現(xiàn)Database changed居暖，
你所有的操作都將在test數(shù)據(jù)庫中進(jìn)行顽频。
3.2 創(chuàng)建表
CREATE TABLE 表名 (
列名 列數(shù)據(jù)屬性 [約束]，
列名 列數(shù)據(jù)屬性太闺，
... ...
);
例如創(chuàng)建一個users表id是主鍵糯景，自增長。
PRIMARY KEY：約束 主鍵約束
AUTO_INCREMENT：約束 自增長
CREATE TABLE users(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
account VARCHAR(50),
pwd VARCHAR(50),
);
3.3 插入數(shù)據(jù)
INSERT INTO 表名 VALUES (值1, 值2,...,NULL省骂，...);
這種插入方式一定要將所有的值都寫上蟀淮，如果為空的話寫NULL。值與列要一一對應(yīng)
主鍵如果設(shè)置為自增的話寫NULL
也可以指定所要插入數(shù)據(jù)的列冀宴，向下面這樣灭贷。
INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,....);
按回車后出現(xiàn)
Query Ok, 1 row affected (0.05 sec)
說明成功。否則請檢查SQL語句略贮。
例如向剛剛創(chuàng)建的users表中插入一條數(shù)據(jù)
INSERT INTO users VALUES(NULL,'admin','admin');
第一列是id甚疟，主鍵自增，不需要插入逃延。
3.4 查詢數(shù)據(jù)
SELECT * FROM 表名 [WHERE 條件] [參數(shù)];
例如查詢users表里的所有數(shù)據(jù)的 account 列览妖。
SELECT account FROM users;
3.4.1 WHERE 查詢
可以給FROM后面的表起一個別名。例如:
SELECT tn.id,tn.account FROM tablename AS tn
WHERE tn.id = 1;
3.4.2 WHERE 后的條件語句
WHERE后面可以寫多種條件語句揽祥。
1. 關(guān)系語句: =,>,<,!=,>=,<=
2. 邏輯語句: AND,OR,NOT
3. IS NULL: 是否為空
4. BETWEEN:在兩者之間
WHERE u.id BETWEEN 11 AND 15;
id 在11-15之間讽膏。
5. IN:在..之中
WHERE u.account IN (值1,值2,...);
查詢 account 在(值1,值2,...)之中的數(shù)據(jù)
5. LIKE:模糊匹配
WHERE u.id LIKE '通配符';
通配符
'%':替代零個或多個字符
'_':替代一個字符
WHERE u.account LIKE '%A%';查詢account字段中含有'A'的數(shù)據(jù)。
[charlist]:字符列中的任何單一字符
WHERE u.account LIKE '[ASD]%'; 查詢以A S D 開頭的數(shù)據(jù)拄丰。
[!charlist]:不在字符列中的任何單一字符
WHERE u.account LIKE '[!ASD]%'; 查詢不以A S D 開頭的數(shù)據(jù)
3.4.3 聚合函數(shù)
SELECT AVG(列名) FROM 表名
1. AVG(列名):返回某一列的平均值
2. COUNT(列名):返回匹配指定條件的行數(shù)
3. FIRST(列名):返回指定的字段中第一個記錄的值府树。
4. LAST(列名):返回指定的字段中最后一個記錄的值。
5. MAX(列名):返回一列中的最大值料按。NULL 值不包括在計算中奄侠。
6. MIN(列名):返回一列中的最小值。NULL 值不包括在計算中载矿。
7. SUM(列名):返回數(shù)值列的總數(shù)（總額）垄潮。
8. UCASE(列名):把字段的值轉(zhuǎn)換為大寫。
9. LCASE(列名):把字段的值轉(zhuǎn)換為小寫。
10. MID(列名,開始位置弯洗，截取長度):用于從文本字段中提取字符旅急。
SELECT MID(account,1,3) as uname FROM users;
11. LEN(列名):返回文本字段中值的長度。
12. ROUND(列名,小數(shù)位數(shù)):用于把數(shù)值字段舍入為指定的小數(shù)位數(shù)牡整。
13. CURDATE():返回當(dāng)前日期藐吮。YYYY-MM-DD。
14. CURTIME():返回當(dāng)前時間果正。HH:MM:SS炎码。
15. NOW():返回當(dāng)前時間。YYYY-MM-DD HH:MM:SS秋泳。
16. YEAR(d)，MONTH(d),DAY(d):分別返回參數(shù)的年攒菠，月迫皱，日。
17. ADDDATE(d,n):在d的時間上加n天辖众。
18. SUBDATE(d,n):在d的時間上減n天卓起。
19. DATE_FORMAT(d,f):格式化時間d。
%a 縮寫星期名
%b 縮寫月名
%c 月凹炸，數(shù)值
%D 帶有英文前綴的月中的天
%d 月的天戏阅，數(shù)值(00-31)
%e 月的天，數(shù)值(0-31)
%f 微秒
%H 小時 (00-23)
%h 小時 (01-12)
%I(大寫的i) 小時 (01-12)
%i 分鐘啤它，數(shù)值(00-59)
%j 年的天 (001-366)
%k 小時 (0-23)
%l(小寫的L) 小時 (1-12)
%M 月名
%m 月奕筐，數(shù)值(00-12)
%p AM 或 PM
%r 時間，12-小時（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 時間, 24-小時 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天变骡，與 %X 使用
%v 周 (01-53) 星期一是一周的第一天离赫，與 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天塌碌，4 位渊胸，與 %V 使用
%x 年，其中的星期一是周的第一天台妆，4 位翎猛，與 %v 使用
%Y 年，4 位
%y 年接剩，2 位
3.4.4 分組查詢
GROUP BY 語句用于結(jié)合聚合函數(shù)切厘，根據(jù)一個或多個列對結(jié)果集進(jìn)行分組。
SELECT 列名, 聚合函數(shù)
FROM 表名
WHERE 條件
GROUP BY 列名
例如根據(jù)用戶表中的role屬性分組統(tǒng)計每組的人數(shù)
SELECT role, COUNT(id)
FROM users
GROUP BY role;
3.4.5 分組過濾
在 SQL 中增加 HAVING 子句原因是搂漠，WHERE 關(guān)鍵字無法與合計函數(shù)一起使用迂卢。
例如根據(jù)用戶表中的role屬性分組統(tǒng)計每組的人數(shù)，查找人數(shù)大于10的role
SELECT role, COUNT(id)
FROM users
GROUP BY role
HAVING COUNT(id)>10;
3.4.6 結(jié)果排序
ORDER BY 語句用于根據(jù)指定的列對結(jié)果集進(jìn)行排序。
默認(rèn)為升序而克，降序使用關(guān)鍵字DESC靶壮。
例如根據(jù)用戶表中的role屬性分組統(tǒng)計每組的人數(shù)，按照人數(shù)降序排列
SELECT role, COUNT(id) count
FROM users
GROUP BY role
ORDER BY count DESC;
3.5 更新數(shù)據(jù)
UPDATE 表名 SET 列名 = 新值 WHERE 條件
WHERE的修改條件是可選的员萍，不寫的話修改表的所有數(shù)據(jù)
例如修改users表里的數(shù)據(jù)腾降，將admin用戶的密碼改為root
UPDATE users SET pwd = 'root' WHERE account = 'admin';
3.6 刪除數(shù)據(jù)
DELETE FROM 表名 WHERE 條件
WHERE的修改條件是可選的，不寫的話刪除表的所有數(shù)據(jù)
4 表屬性的修改
4.1 表重命名
ALTER TABLE 表名 RENAME 新表名;
4.2 添加新的一列
ALTER TABLE 表名 ADD 列名 列數(shù)據(jù)類型 [AFTER 插入位置]
例如在users表的pwd字段后面添加一個role字段類型是INTEGER
ALTER TABLE users ADD role INTEGER AFTER pwd;
4.3 修改列
ALTER TABLE 表名 CHANGE 列名稱 新列名稱 新數(shù)據(jù)類型;
例如修改users表里的pwd字段改為password碎绎，類型是varchar(20)
ALTER TABLE users CHANGE pwd password varchar(20);
5 完整性約束
約束：對表中的列添加的一些限制條件螃壤。只有滿足這些條件時才能數(shù)據(jù)才能插入。
5.1 實體完整性
主鍵不能為空
5.2 參照完整性
外鍵的值必須是某張表中存在的值筋帖，可以為空
5.3 用戶自定義完整性
唯一性約束奸晴，某一列雖然不為主鍵，但是依然不能重復(fù)日麸。
5.4 添加約束
ALTER TABLE 表名
ADD CONSTRAINT 約束名 約束類型(列名);
例如寄啼，給users表的role列添加外鍵約束，引用自roles表的id列
ALTER TABLE users
ADD CONSTRAINT uq_phone FOREIGN KEY (`role`)
REFERENCES `roles` (`id`);
5.5 常見約束類型
1. PRIMARY KEY: 主鍵
2. CHECK:限制列中的值的范圍代箭。MySQL好像不支持
3. UNIQUE:唯一性約束
4. NOT NULL：非空約束
5. FOREIGN KEY:外鍵約束
6 范式
函數(shù)依賴:如果A依賴B墩划，那么當(dāng)確定A值后，B值也就確定了嗡综。
例如乙帮，在一張表中由于主鍵是唯一確定且不重復(fù)的。所以极景，當(dāng)主鍵的值確定了察净，那么其他列的值也就確定了。我們便說戴陡，其他列都依賴主鍵塞绿。
完全函數(shù)依賴：如果B函數(shù)依賴于A，并且對于A的任何一個真子集A1,都有 B不依賴與A1,稱A對B完全函數(shù)依賴恤批。
部分函數(shù)依賴：如果B函數(shù)依賴于A异吻，但是B不完全函數(shù)依賴于A，稱B對A部分函數(shù)依賴喜庞。
例如诀浪，在一張表中，有兩列延都，一列是用戶名雷猪，而另一列里某些行里記錄的是用戶的id，而某些行里在這一列里記錄的是用戶所在部門的id晰房，那么用戶名列便部分函數(shù)依賴于id列求摇，因為存在某些行依賴于id列的子集(記錄是部門id的行)射沟。
傳遞函數(shù)依賴：如果A函數(shù)依賴于B，B函數(shù)依賴于C与境，則稱A傳遞函數(shù)依賴于C验夯。
6.1 1NF(第一范式)
確保列不可分，即每一列只描述一個屬性摔刁，即達(dá)到1NF
6.2 2NF(第二范式)
在1NF的基礎(chǔ)上消除部分函數(shù)依賴即達(dá)到2NF挥转。即每一個非主屬性(非主鍵列)完全函數(shù)依賴于主鍵。
例如共屈，可以將上面部分函數(shù)依賴的例子中的表绑谣，改成兩張表，一張用戶表拗引，一張部門表借宵，所有的用戶名列都完全依賴于主鍵。便達(dá)到2NF寺擂。
6.3 3NF(第三范式)
在2NF的基礎(chǔ)上消除傳遞函數(shù)依賴便達(dá)到3NF暇务。即每一個非主屬性(非主鍵列)即不部分依賴于主鍵，也不傳遞依賴于主鍵怔软。
