內(nèi)容為日常學(xué)習(xí)筆記
轉(zhuǎn)載請(qǐng)注明：陳熹 chenx6542@foxmail.com （簡(jiǎn)書(shū)號(hào)：半為花間酒）
若公眾號(hào)內(nèi)轉(zhuǎn)載請(qǐng)聯(lián)系公眾號(hào)：早起Python
理論知識(shí)
存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取杯缺。其架構(gòu)模式是插件式的，支持InnoDB烹困、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。現(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB糠排，它從 MySQL 5.5.5 版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎
InnoDB支持事務(wù)和原子性操作
連接MySQL
mysql -h$ip -P$port -u$user -p
net start mysql # 或 mysql.server start
mysql -u root -p
mysql -u user -p db_name # 直接進(jìn)入指定數(shù)據(jù)庫(kù)
導(dǎo)入導(dǎo)出
導(dǎo)出現(xiàn)有數(shù)據(jù)庫(kù)數(shù)據(jù)：
mysqldump -u用戶(hù)名 -p密碼 數(shù)據(jù)庫(kù)名稱(chēng) >導(dǎo)出文件路徑 # 結(jié)構(gòu)+數(shù)據(jù)
mysqldump -u用戶(hù)名 -p密碼 -d 數(shù)據(jù)庫(kù)名稱(chēng) >導(dǎo)出文件路徑 # 結(jié)構(gòu)
導(dǎo)入現(xiàn)有數(shù)據(jù)庫(kù)數(shù)據(jù)：
mysqldump -uroot -p密碼 數(shù)據(jù)庫(kù)名稱(chēng) <導(dǎo)入文件路徑
數(shù)據(jù)庫(kù)操作
SELECT version(), current_date, now(); # 查看mysql版本號(hào)和日期傍衡、時(shí)間
SELECT version(); select now(); select current_date; # 分開(kāi)寫(xiě)則表格分開(kāi)
SOURCE c://test.sql # 用txt文件保存命令該后綴名 可直接執(zhí)行文件內(nèi)的命令
\c # 撤銷(xiāo)先前輸入
\G # 按行輸出
SHOW DATABASES; # 查看所有數(shù)據(jù)庫(kù)
SELECT DATABASE(); # 進(jìn)入數(shù)據(jù)庫(kù)后查看當(dāng)前數(shù)據(jù)庫(kù)
# 數(shù)據(jù)庫(kù)名稱(chēng)組成除了三大項(xiàng)還可以含$，但不能是純數(shù)字
CREATE DATABASE db_name; # 創(chuàng)建spiders數(shù)據(jù)庫(kù)
CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# 安裝utf8規(guī)則排序
# utf8mb4支持墨跡表情
USE db_name区匠； # 進(jìn)入數(shù)據(jù)庫(kù)
ALTER DATABASE db_name DEFAULT CHARSET SET utf8; # 修改數(shù)據(jù)庫(kù)
DROP DATABASE db_name干像； # 刪除數(shù)據(jù)庫(kù)
DROP VARIABLES LIKE 'datadir'; # 查看數(shù)據(jù)庫(kù)所在位置
SHOW TABLES; # 查看所有表
DESCRIBE db_name; # 查看表的描述，也可以用 DESC table;
SELECT * FROM db_name; # 查看表中所有數(shù)據(jù)
SELECT host,user FROM db_name; # 大小寫(xiě)不敏感
INSERT INTO person(name,birth) VALUES('A',1994-01-01); # VALUE也可
表操作
- 創(chuàng)建表
CREATE TABLE students(
nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, # 自增列必須是索引驰弄，最好是主鍵
name VARCHAR(20),
num INT NOT NULL DEFAULT 2)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- 主鍵
一張表只能有一個(gè)主鍵麻汰，值是唯一的（或多列組合是唯一的），不能重復(fù)不能為空戚篙，一般情況下自增列設(shè)置為主鍵五鲫。
常用 nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY
一張表可以多個(gè)唯一列
CREATE TABLE tb1(
nid INT NOT NULL,
num INT NOT NULL,
PRIMARY KEY(nid,num) # 兩列組成一個(gè)主鍵
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
主鍵的作用：
- 約束
- 索引，加速查找
- 外鍵
# 創(chuàng)建表時(shí)創(chuàng)建
CREATE TABLE color(
INT NOT NULL PRIMARY KEY,
name CHAR(16) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;
CREATE TABLE fruit(
INT NOT NULL PRIMARY KEY,
smt CHAR(32) NOT NULL ,
color_id INT NOT NULL,
CONSTRAINT fk_fruit_color FOREIGN KEY (color_id) REFERENCES color(nid)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;
# 表外創(chuàng)建
ALTER TABLE students ADD CONSTRAINT fk_tb2_tb1 FOREIGN KEY tb2(info) REFERENCES tb1(nid);
- 修改表屬性
# 添加列：
ALTER TABLE 表名 ADD 列名 類(lèi)型
# 刪除列：
ALTER TABLE 表名 DROP COLUMN 列名
# 修改列：
ALTER TABLE 表名 MODIFY COLUMN 列名 類(lèi)型; 一般只改類(lèi)型
ALTER TABLE 表名 CHANGE 原列名 新列名 類(lèi)型; 可改列名+類(lèi)型
# 添加主鍵：
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
# 刪除主鍵：
ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE 表名 MODIFY 列名 INT, DROP PRIMARY KEY;
# 添加外鍵：
ALTER TABLE 從表 ADD CONSTRAINT 外鍵名稱(chēng)（形如：FK_從表_主表）FOREIGN KEY 從表(外鍵字段) REFERENCES 主表(主鍵字段);
# 刪除外鍵：
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱(chēng);
# 修改默認(rèn)值：
ALTER TABLE tbl ALTER列名 SET DEFAULT 1000;
# 刪除默認(rèn)值：
ALTER TABLE tbl ALTER列名 DROP DEFAULT;
數(shù)據(jù)類(lèi)型
bit(M): 二進(jìn)制位岔擂，M為1~64臣镣，默認(rèn)M=1
-
int:
nid int unsigned 最大4GB (2**32 ? 1)
整數(shù)類(lèi)型中的m僅用于顯示辅愿，對(duì)存儲(chǔ)范圍無(wú)限制
tinyint: 有符號(hào)-128127，無(wú)符號(hào)0255忆某，默認(rèn)是signed
?MySQL中無(wú)布爾值点待，使用tinyint(1)構(gòu)造
bigint
smallint
-
decimal: 精確的小數(shù)，能夠存儲(chǔ)精確值的原因在于其內(nèi)部按照字符串存儲(chǔ)
num decimal(6,2) 6個(gè)有效數(shù)字(總長(zhǎng)度)弃舒，2位小數(shù)癞埠，最大是65和30
float，double: 不精確
-
char: 唯一定長(zhǎng)聋呢，其他都是變長(zhǎng)苗踪，數(shù)據(jù)不到該長(zhǎng)度其他空間會(huì)閑置，查找速度快削锰，浪費(fèi)空間
varchar: 變長(zhǎng)通铲，相對(duì)于char效率低。查找下一列時(shí)不確定跳多少存儲(chǔ)空間
字符最大是255
text：字符最大是65535 2**16-1
mediumtext：2**24-1
longtext：2**32-1
-
二進(jìn)制數(shù)據(jù)：
TinyBlob器贩、Blob颅夺、MediumBlob、LongBlob
上傳文件蛹稍，blob強(qiáng)制二進(jìn)制方式“苫疲現(xiàn)在多用varchar保存路徑，上傳文件保存在硬盤(pán)
-
時(shí)間
DATE YYYY-MM-DD（1000-01-01/9999-12-31） TIME HH:MM:SS（'-838:59:59'/'838:59:59'） YEAR YYYY（1901/2155） DATETIME YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y） TIMESTAMP YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時(shí)） -
枚舉 enum
支持65535個(gè)元素枚舉
CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large')); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');單選
-
集合 set
CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');可以多選
表內(nèi)容操作
# 寫(xiě)的順序
SELECT... FROM...
WHERE...
GROUP BY... HAVING...
ORDER BY...
LIMIT...
# 執(zhí)行順序
FROM...
WHERE...
GROUP BY...
SELECT...
HAVING...
ORDER BY...
LIMIT...
- 增
INSERT INTO 表 (列名,列名...) VALUES(值,值,值...);
INSERT INTO 表 (列名,列名...) VALUES(值,值,值...),(值,值,值...); # 增加多條數(shù)據(jù)
INSERT INTO 表 (列名,列名...) SELECT 列名,列名 FROM 表; # 后面選擇列不需要空格
# 如果數(shù)據(jù)可以轉(zhuǎn)則允許互轉(zhuǎn)
insert into students(name,age) SELECT caption,nid FROM tb2 WHERE nid>2;
- 刪
DELETE FROM 表;
DELETE FROM 表 WHERE id＝1 AND name＝'alex';
DELETE FROM 表 WHERE id > 1 OR name＝'alex';
# 刪除和清空
DROP TABLE student; # 刪除表
DELETE FROM student; # 清空表唆姐，有自增列則清空后自增記憶存在
TRUNCATE (TABLE) student拗慨；# 快速清空表，有自增列則清空后自增從1重新開(kāi)始
- 改
UPDATE students SET name ＝ 'alex' WHERE id>1;
UPDATE salary SET sex = IF(sex = 'm', 'f', 'm') # 條件判斷更改
UPDATE salary SET sex = char(ascii('m') + ascii('f') - ascii(sex));
UPDATE salary
SET
sex = CASE sex
WHEN "m" THEN "f"
ELSE "m" END;
# UPDATE和JOIN
UPDATE A JOIN B ON A.URL = B.URL
SET member_id = '00012138'
WHERE LOGIN_time BETWEEN '2019' AND '2020'
AND B.class_id = 'TNT'; # BETWEEN 后可以再跟AND
- 查
SELECT * FROM 表;
# 要全部列可以把列名寫(xiě)一遍奉芦，效率比*高
SELECT * FROM 表 WHERE id <> 1; # 即!=
SELECT * FROM 表 WHERE id BETWEEN 3 AND 5; # 即 id>=3 AND id<=5
SELECT nid,name,gender FROM 表 WHERE id%2 = 1;
SELECT DISTINCE name FROM score WHERE num < 60赵抢；
# distinct 去重只保留各組一項(xiàng) 也可以使用group by
WHERE (class, name) NOT IN (SELECT class, name FROM B) # 多個(gè)字段限制
- 條件選擇
SELECT * FROM 表 WHERE id BETWEEN 5 AND 16; # 也支持多個(gè)并列
SELECT * FROM 表 WHERE id IN(11,22,33)
SELECT * FROM 表 WHERE id NOT IN(11,22,33)
SELECT * FROM 表 WHERE id IN(select nid from 表)
- 通配符模糊匹配
SELECT * FROM 表 WHERE name LIKE 'ale%' # - ale開(kāi)頭的所有（多個(gè)字符串） where ... like ''
SELECT * FROM 表 WHERE name LIKE 'ale_' # - ale開(kāi)頭的所有（1個(gè)字符）
SELECT * FROM 表 WHERE name LIKE '_le%'
- 限制/分頁(yè)
SELECT * FROM 表 LIMIT 5; # 前5行
SELECT * FROM 表 LIMIT 4,5; # 從第4行下一行開(kāi)始的5行
SELECT * FROM 表 LIMIT 5 OFFSET 4 # 從第4行開(kāi)始的5行，與上一條功能一樣
- 排序
SELECT * FROM 表 ORDER BY 列 ASC # 根據(jù) “列” 從小到大排列
SELECT * FROM 表 ORDER BY 列 DESC # 根據(jù) “列” 從大到小排列
SELECT * FROM 表 ORDER BY 列1 DESC,列2 ASC # 根據(jù) “列1” 從大到小排列声功，如果相同則按列2從小到大排序昌讲，否則一列相同時(shí)其他列默認(rèn)升序排序
- 分組+聚合
# group by獲取各組第一行作為標(biāo)識(shí)，其余行丟棄
SELECT num FROM 表 GROUP BY num
# 分組的原理默認(rèn)是升序排序减噪，也可以降序
SELECT num FROM 表 GROUP BY num DESC
SELECT count(1) FROM 表 GROUP BY num # 也可使用
SELECT count(name) FROM students GROUP BY classid
# count(1) 和 count(name)區(qū)別
# 如果name中有null則count(name)不記錄，其余時(shí)候二者完全一樣
SELECT count(distinct name) FROM students GROUP BY classid # 去重
# sum只能對(duì)int類(lèi)型計(jì)算 否則是0
# 多列分組 多個(gè)均相同的分到一組
SELECT num,nid FROM 表 GROUP BY num,nid
SELECT num,nid FROM 表 WHERE nid > 10 GROUP BY num,nid ORDER BY nid DESC
SELECT num,nid,count(*) AS count,sum(score) AS sum,avg(score),max(score),min(score) FROM 表 GROUP BY num,nid # as 是自定義命名
# 如果要對(duì)聚合函數(shù)進(jìn)行篩選需引入having 順序在group by后
SELECT num FROM 表 GROUP BY num HAVING max(id) > 10
# 聚集函數(shù)
count sum max min avg group_concat(字符串拼接) # 都會(huì)去除null
# sum(1) 等于 count(1) 只能針對(duì)int類(lèi)型
# group_concat
SELECT id,GROUP_CONCAT(name) FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(name SEPARETOR ';') FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(DISTINCT name) FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(name ORDER BY name DESC) FROM aa GROUP BY id;
# 語(yǔ)法如下
DISTINCT name ORDER BY id DESC SEPARETOR '-'
group by 必須在where之后车吹，order by之前
- 組合
# 縱向組合筹裕，并以第一個(gè)表字段為準(zhǔn)
# 組合，自動(dòng)處理重合
SELECT nickname FROM A UNION SELECT name FROM B;
# 組合窄驹，不處理重合
SELECT nickname FROM A UNION ALL SELECT name FROM B;
- 連表
# 需要多行之間滿(mǎn)足一個(gè)需求就需要join
# 笛卡爾積
SELECT * FROM students,disc_info;
# 根據(jù)對(duì)應(yīng)關(guān)系連表朝卒，實(shí)際等同于inner join
SELECT * FROM students,disc_info WHERE students.discipline = disc_info.nid;
SELECT students.name,disc_info.discipline FROM students,disc_info WHERE students.discipline = disc_info.nid;
# join 左右連接如果無(wú)對(duì)應(yīng)關(guān)系顯示NULL，join效率高
# 取交集乐埠，inner join抗斤，也可以理解成過(guò)濾掉含NULL數(shù)據(jù)行的左右連接
SELECT A.num, A.name, B.name FROM
A INNER JOIN B
ON A.nid = B.nid囚企；
# 右連接，right join
SELECT A.num, A.name, B.name FROM
A RIGHT JOIN B
ON A.nid = B.nid
# 左連接瑞眼，right join
SELECT A.num, A.name, B.name FROM
A LEFT JOIN B
ON A.nid = B.nid
獲得的結(jié)果是個(gè)表龙宏，可以作為子查詢(xún)的臨時(shí)表
- CASE WHEN 判斷
CASE WHEN 表達(dá)式
THEN 輸出
WHEN 表達(dá)式 # 多少個(gè)WHEN都可以
THEN 輸出
ELSE
輸出
END
SELECT id,name,
(
CASE WHEN classid = 1 THEN 2
WHEN classid =2 THEN 1
ELSE classid END
) AS clid
FROM stu;
# 把tidydata轉(zhuǎn)為正常數(shù)據(jù)
SELECT name,
MAX(CASE WHEN project = '基礎(chǔ)' THEN score ELSE NULL END) as '基礎(chǔ)',
MAX(CASE WHEN project = '爬蟲(chóng)' THEN score ELSE NULL END) as '爬蟲(chóng)',
MAX(CASE WHEN project = 'SQL' THEN score ELSE NULL END) as 'SQL'
FROM score;
# 查詢(xún)兩門(mén)及以上不及格同學(xué)信息
SELECT st.Name, AVG(score) as Score_n
FROM SC JOIN student st ON SC.SId = st.SId
GROUP BY SId
HAVING COUNT(CASE WHEN Score < 80 THEN 1 ELSE NULL END) >=2；
- 半連接
SELECT
id,NAME,classid
FROM
student tf
WHERE
id = (
SELECT
max(id)
FROM
student ts
WHERE
ts.classid = tf.classid
);
# 也可以用常規(guī)方法
SELECT
id,NAME,classid
FROM
(
SELECT
max(id)
FROM
student
GROUP BY
classid
);
- 索引
# 基于B+樹(shù)
# 數(shù)據(jù)分的越開(kāi)的列則建索引效果越好
# OR情況不能用索引
# 如果是聯(lián)合索引 前部過(guò)濾條件可以做為索引
# 在SQL語(yǔ)句前加EXPLAIN就可以明確是否走索引
# 創(chuàng)建索引
CREATE INDEX name_index ON student(name);
# 聚簇索引葉子節(jié)點(diǎn)跟著數(shù)據(jù)伤疙，非聚簇索引葉子節(jié)點(diǎn)跟著主鍵(聚簇索引)
# 非聚簇走完多數(shù)會(huì)再走聚簇银酗，除非SELECT內(nèi)容均被包含于索引(全覆蓋索引)
# MySQL中主鍵是聚簇索引，其他均為非聚簇索引
# 如果沒(méi)有主鍵徒像，內(nèi)部會(huì)虛擬一個(gè)AUTO_INCREMENT的主鍵
- 視圖
CREATE VIEW V1 AS # 反復(fù)利用某個(gè)臨時(shí)表則可以創(chuàng)建視圖
SELECT * FROM stu WHERE id > 10;
# 視圖是一個(gè)動(dòng)態(tài)表黍特，會(huì)從物理表動(dòng)態(tài)讀出來(lái)。但無(wú)法直接對(duì)虛擬表即視圖修改
# 修改視圖
ALTER VIEW V1 AS SELECT * FROM stu WHERE id > 20;
# 刪除視圖
DROP VIEW V1;
- 觸發(fā)器
DELIMITER // # 修改終止符
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT INTO tb1 FOR EACH ROW
BEGIN
INSERT INTO tb2(name) VALUES('chenx');
INSERT INTO tb2(name) VALUES(NEW.sname); # 指代新插入的一行锯蛀，OLD可以用在DELETE和UPDATE
END //
DELIMITER ; # 修改回原終止符
# BEFORE可以換成AFTER灭衷，INSERT可以換成DELETE或者UPDATE
# 刪除觸發(fā)器
DROP TRIGGER tri_before_insert_tb1;
- 函數(shù)
# 內(nèi)置函數(shù)
SELECT CURDATE(); # 執(zhí)行函數(shù)的用法
SELECT DATE_FORMAT(ctime, "%Y-%m") FROM stu GROUP BY DATE_FORMAT(ctime, "%Y-%m") # 時(shí)間格式化
# 自定義函數(shù)
DELIMITER \\
CREATE FUNCTION f1(
i1 INT,
i2 INT)
RETURNS INT# 強(qiáng)類(lèi)型語(yǔ)言
BEGIN
DECLARE num INT DEFAULT 0; # 聲明變量
SET num = i1 + i2;
RETURN(num);
END \\
DELIMITER ;
- 事務(wù)、存儲(chǔ)過(guò)程旁涤、循環(huán)等見(jiàn)
https://www.cnblogs.com/wupeiqi/articles/5713323.html
pymysql
import pymysql
# 連接配置信息
config = {
'host':'localhost',
'port':3306,
'user':'root',
'password':'xxxx',
'charset':'utf8'}
db = pymysql.connect(**config)
cursor = db.cursor() # 獲得MySQL的操作游標(biāo)翔曲，利用游標(biāo)來(lái)執(zhí)行SQL語(yǔ)句，后續(xù)可以用execute()方法
cursor.execute('''執(zhí)行SQL語(yǔ)句''') # 該語(yǔ)句的返回值是受影響的函數(shù)
cursor.commit() # 確認(rèn)執(zhí)行語(yǔ)句拭抬，如果是查詢(xún)則不需要commit()
cursor.close() # 關(guān)閉游標(biāo)
db.close() # 關(guān)閉數(shù)據(jù)庫(kù)
# 如果是動(dòng)態(tài)參數(shù)必須用傳遞參數(shù)而不能用字符串拼接
imp = 'A'
SQL = 'INSERT INTO student(name) VALUES(%s)'
cursor.execute(SQL,imp)
# 如果一條記錄需要傳遞tuple
cursor.execute('INSERT INTO student(name,age) VALUES(%s,%d)',('A',25))
# 故單條單個(gè)記錄也可以寫(xiě)成
cursor.execute('INSERT INTO student(name) VALUES(%s)',('A',))
# 如果多條記錄需要用executemany()
lst = [('A',25),('B',25)]
cursor.executemany('INSERT INTO student(name,age) VALUES(%s,%d)',lst)
# 如果要返回查詢(xún)的結(jié)果
print(cursor.fetchall()) # 以元祖內(nèi)嵌元祖返回
print(cursor.fetchmany(10))
print(cursor.fetchone()) # 取一次后指針下移部默，下次執(zhí)行fetchone()返回下一個(gè)記錄；以元祖返回
results = cursor.scroll(0, mode='absolute') # 絕對(duì)指針造虎，回到總記錄第0位置
results = cursor.scroll(-1, mode='relative') # 相對(duì)指針傅蹂，回到當(dāng)前記錄上1位置
# 設(shè)置游標(biāo)參數(shù)，將返回值從tuple改為dictionary
cursor = db.cursor(cursor = pymysql.cursors.DictCursor)
# 操作完成后返回最后一個(gè)自增id
nid = cursor.lastrowid
