聯(lián)表查詢(xún)（多表查詢(xún)）
create table article(
id int unsigned auto_increment primary key,
title varchar(100) ,
content_path varchar(100),
content_num int unsigned,
author_id int unsigned,
is_del bit default 0
);
create table author(
id int unsigned auto_increment primary key,
name varchar(100),
address varchar(100),
is_del bit default 0
);
01現(xiàn)有數(shù)據(jù).png
連接查詢(xún)
- inner join 數(shù)據(jù)的交集
- left join 數(shù)據(jù)的交集 并上 左邊表的特有數(shù)據(jù)
- right join 數(shù)據(jù)的交集 并上 右邊表的特有數(shù)據(jù)
inner join 內(nèi)連接或等值連接（相當(dāng)于兩表或者多表的的權(quán)重相等）
-- inner join 數(shù)據(jù)的交集
select * from article inner join author ;
select * from article inner join author on article.author_id = author.id;
02inner join.png
問(wèn)題：數(shù)據(jù)表就是一個(gè)集合，每一條記錄就是集合中的一個(gè)值，內(nèi)聯(lián)查詢(xún)就是對(duì)兩個(gè)集合（表）求笛卡爾乘積[1]，但是笛卡爾積的結(jié)果集并不是我們想要的結(jié)果，所以需要使用on 篩選出我們需要的結(jié)果集（笛卡爾積結(jié)果集的子集）
簡(jiǎn)單來(lái)說(shuō)內(nèi)聯(lián)查詢(xún)會(huì)得到 每個(gè)數(shù)據(jù)表記錄數(shù) 乘積 條記錄的結(jié)果集，如上面的兩張表article 和 author 分別有20 和 10 條記錄，會(huì)得到一個(gè)有 200（20*10）條記錄的結(jié)果集
注：
- on 聯(lián)表查詢(xún)時(shí)，做條件篩選時(shí)的關(guān)鍵字
- where 單表查詢(xún)時(shí)，做條件篩選時(shí)的關(guān)鍵字
- having 分組時(shí)，做條件篩選的關(guān)鍵字
left join 左連接 （左邊的權(quán)重大于右邊，左表為主）
-- 顯示公共部分，以及左表特有的部分（author_id 為null 的五條記錄）
select * from article left join author on article.author_id = author.id;
03left join.png
right join 右連接 （右邊的權(quán)重大于左邊，右表為主）
-- 顯示公共部分，以及右表特有的部分
select * from article right join author on article.author_id = author.id;
04right join.png
自關(guān)聯(lián)
當(dāng)需要的數(shù)據(jù)都在一張表中，但普通的查詢(xún)又不能滿(mǎn)足需求時(shí)，可以通過(guò)自關(guān)聯(lián)的方式得到一張能滿(mǎn)足需求的表
05省市縣數(shù)據(jù)表.png
如上表，省市縣在一張表中，如果要做三級(jí)聯(lián)動(dòng)[2]需要自關(guān)聯(lián)查詢(xún)得到想要的數(shù)據(jù)。
-- 查詢(xún)山東省的所有城市
select * from areas as a1 inner join areas as a2 on a1.pid = a2.aid where a2.atitle = "山東省";
06自關(guān)聯(lián).png
子查詢(xún)
查詢(xún)的嵌套 ,將一個(gè)查詢(xún)的結(jié)果集作為另一個(gè)查詢(xún)的輸入，可以作為條件，也可以作為數(shù)據(jù)源。
-- 查詢(xún)山東省的所有城市
select * from areas where pid = (select aid from areas where atitle = "山東省");
07子查詢(xún).png
Python操作數(shù)據(jù)庫(kù)
pymysql安裝[3]
from pymysql import connect # 導(dǎo)入模塊
if __name__ == '__main__':
#調(diào)用connect() 方法創(chuàng)建 Connection 對(duì)象
conn = connect(host="localhost", port=3306, user="root", password="dragon",
database="fang", charset="utf8")
cs = conn.cursor()
for tmp in range(20):
cs.execute("""insert into article(title,author_id) values(%s,%s); """, ("dragon", 1000))
conn.commit()
conn.close()
- connect() 方法的參數(shù)
- 參數(shù)host：連接的mysql主機(jī)，如果本機(jī)是'localhost'
- 參數(shù)port：連接的mysql主機(jī)的端口，默認(rèn)是3306
- 參數(shù)database：數(shù)據(jù)庫(kù)的名稱(chēng)
- 參數(shù)user：連接的用戶(hù)名
- 參數(shù)password：連接的密碼
- 參數(shù)charset：通信采用的編碼方式，推薦使用utf8
- Connection 對(duì)象的方法
- close()關(guān)閉連接
- commit()提交
- cursor()返回Cursor對(duì)象，用于執(zhí)行sql語(yǔ)句并獲得結(jié)果
- Cursor對(duì)象
- 用于執(zhí)行sql語(yǔ)句，使用頻度最高的語(yǔ)句為select、insert、update、delete
- 獲取Cursor對(duì)象：調(diào)用Connection對(duì)象的cursor()方法
Cursor 對(duì)象的方法 - close()關(guān)閉
- execute(operation [, parameters ])執(zhí)行語(yǔ)句，返回受影響的行數(shù)，主要用于執(zhí)行insert、update、delete語(yǔ)句，也可以執(zhí)行create、alter、drop等語(yǔ)句
- fetchone()執(zhí)行查詢(xún)語(yǔ)句時(shí)，獲取查詢(xún)結(jié)果集的第一個(gè)行數(shù)據(jù)，返回一個(gè)元組
- fetchall()執(zhí)行查詢(xún)時(shí)，獲取結(jié)果集的所有行，一行構(gòu)成一個(gè)元組，再將這些元組裝入一個(gè)元組返回
- Cursor 對(duì)象的屬性
- rowcount只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
- connection獲得當(dāng)前連接對(duì)象
sql注入問(wèn)題
from pymysql import connect
if __name__ == '__main__':
conn = connect(host="localhost", port=3306, user="root", password="fangfang",
database="fang", charset="utf8")
article_id = input("請(qǐng)輸入id：")
cs = conn.cursor()
sql_str = """select * from article where id >%s; """ % article_id
print(sql_str)
# for tmp in range(20):
cs.execute(sql_str)
conn.commit()
conn.close()
# 運(yùn)行結(jié)果
# 請(qǐng)輸入id：123
# select * from article where id >123;
# 假設(shè) 我輸入
# 請(qǐng)輸入id：123;delete from article
# select * from article where id >123;delete from article ;
# 假設(shè)是插入語(yǔ)句
# """inset into areas(atitle) values ("%s");""" % """a");delete from areas where (aid >"1"""
# 如下
# inset into areas(atitle) values ("a");delete from areas where (aid >"1");
08SQL注入 清空表數(shù)據(jù).png
解決方案
execute 可以傳參
def execute(self, query, args=None):
"""Execute a query
:param str query: Query to execute.
:param args: parameters used with query. (optional)
:type args: tuple, list or dict
:return: Number of affected rows
:rtype: int
If args is a list or tuple, %s can be used as a placeholder in the query.
If args is a dict, %(name)s can be used as a placeholder in the query.
"""
while self.nextset():
pass
query = self.mogrify(query, args)
result = self._query(query)
self._executed = query
return result
# 實(shí)例
# 列表 或 元組傳值
# execute("""select * from article where id >%s; """,(123,))
# 字典傳值
# sql_str = """insert into article(title,author_id) values (%(title)s,%(author_id)s); """
# cs.execute(sql_str, {"author_id": 22,"title": "aaaa"})
事務(wù)
- 事務(wù)作用:保證數(shù)據(jù)修改的完整性
- 特性:
數(shù)據(jù)操作后可以,可以回滾(注意刪除表,刪除庫(kù)沒(méi)辦法)
acid 原子性,一致性,隔離性,持久性 - 事務(wù)的操作
begin；-- 開(kāi)啟事務(wù)
start transaction；-- 開(kāi)啟事務(wù)
commit； -- 提交事務(wù)
rollback; -- 回滾
索引
- 有什么用:加快查詢(xún)的速度
- 原理:對(duì)我們的字段進(jìn)行分組,如果大并發(fā)的情況下,修改數(shù)據(jù)會(huì)變慢
-- 創(chuàng)建
create index 名字 on 表名(索引的字段);
-- 查看
show index from 表名;
-- 刪除
drop index 索引名 on 表名; # 必須指定表名
數(shù)據(jù)庫(kù)設(shè)計(jì)
三范式
- 第一范式（1NF）：強(qiáng)調(diào)的是列的原子性，即列不能夠再分成其他幾列。(一個(gè)數(shù)據(jù)一個(gè)字段)
- 第二范式（2NF）：首先是 1NF，另外包含兩部分內(nèi)容，一是表必須有一個(gè)主鍵；二是沒(méi)有包含在主鍵中的列必須完全依賴(lài)于主鍵，而不能只依賴(lài)于主鍵的一部分。（確定主鍵，擁有主鍵）
- 第三范式（3NF）：首先是 2NF，另外非主鍵列必須直接依賴(lài)于主鍵，不能存在傳遞依賴(lài)。即不能存在：非主鍵列 A 依賴(lài)于非主鍵列 B，非主鍵列 B 依賴(lài)于主鍵的情況。（減少重復(fù)）
數(shù)據(jù)庫(kù)軍規(guī)
到此結(jié)?DragonFangQy 2018.5.15
