聯(liá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

連接查詢

• 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

問題：數(shù)據(jù)表就是一個集合耕餐，每一條記錄就是集合中的一個值凡傅，內(nèi)聯(lián)查詢就是對兩個集合（表）求笛卡爾乘積^[1]，但是笛卡爾積的結(jié)果集并不是我們想要的結(jié)果肠缔，所以需要使用on 篩選出我們需要的結(jié)果集（笛卡爾積結(jié)果集的子集）

簡單來說內(nèi)聯(lián)查詢會得到 每個數(shù)據(jù)表記錄數(shù) 乘積 條記錄的結(jié)果集夏跷，如上面的兩張表article 和 author 分別有20 和 10 條記錄，會得到一個有 200（20*10）條記錄的結(jié)果集

注：

• on 聯(lián)表查詢時明未，做條件篩選時的關(guān)鍵字
• where 單表查詢時槽华，做條件篩選時的關(guān)鍵字
• having 分組時，做條件篩選的關(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ù)都在一張表中亲雪，但普通的查詢又不能滿足需求時勇凭，可以通過自關(guān)聯(lián)的方式得到一張能滿足需求的表

05省市縣數(shù)據(jù)表.png

如上表，省市縣在一張表中义辕，如果要做三級聯(lián)動^[2]需要自關(guān)聯(lián)查詢得到想要的數(shù)據(jù)虾标。

-- 查詢山東省的所有城市
select * from areas as a1 inner join areas as a2 on a1.pid = a2.aid where a2.atitle = "山東省";

06自關(guān)聯(lián).png

子查詢

查詢的嵌套 ,將一個查詢的結(jié)果集作為另一個查詢的輸入，可以作為條件灌砖，也可以作為數(shù)據(jù)源璧函。

-- 查詢山東省的所有城市 
select * from areas where pid = (select aid from areas where atitle = "山東省");

07子查詢.png

Python操作數(shù)據(jù)庫

pymysql安裝^[3]

from pymysql import connect  # 導(dǎo)入模塊

if __name__ == '__main__':
    #調(diào)用connect() 方法創(chuàng)建 Connection 對象 
    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主機，如果本機是'localhost'
  • 參數(shù)port：連接的mysql主機的端口基显，默認(rèn)是3306
  • 參數(shù)database：數(shù)據(jù)庫的名稱
  • 參數(shù)user：連接的用戶名
  • 參數(shù)password：連接的密碼
  • 參數(shù)charset：通信采用的編碼方式蘸吓，推薦使用utf8
• Connection 對象的方法
  • close()關(guān)閉連接
  • commit()提交
  • cursor()返回Cursor對象，用于執(zhí)行sql語句并獲得結(jié)果
• Cursor對象
  • 用于執(zhí)行sql語句撩幽，使用頻度最高的語句為select美澳、insert、update摸航、delete
  • 獲取Cursor對象：調(diào)用Connection對象的cursor()方法
    Cursor 對象的方法
  • close()關(guān)閉
  • execute(operation [, parameters ])執(zhí)行語句，返回受影響的行數(shù)舅桩，主要用于執(zhí)行insert酱虎、update、delete語句擂涛，也可以執(zhí)行create、alter、drop等語句
  • fetchone()執(zhí)行查詢語句時浩蓉，獲取查詢結(jié)果集的第一個行數(shù)據(jù)运褪，返回一個元組
  • fetchall()執(zhí)行查詢時，獲取結(jié)果集的所有行狰右，一行構(gòu)成一個元組杰捂，再將這些元組裝入一個元組返回
• Cursor 對象的屬性
  • rowcount只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
  • connection獲得當(dāng)前連接對象

sql注入問題

from pymysql import connect

if __name__ == '__main__':
    conn = connect(host="localhost", port=3306, user="root", password="fangfang",
                   database="fang", charset="utf8")

    article_id = input("請輸入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()
    
    # 運行結(jié)果
    # 請輸入id：123
    # select * from article where id >123;
    
    # 假設(shè) 我輸入
    # 請輸入id：123;delete from article 
    # select * from article where id >123;delete from article ;

    # 假設(shè)是插入語句
    # """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

# 實例
# 列表 或 元組傳值
# 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ù)操作后可以,可以回滾(注意刪除表,刪除庫沒辦法)
  acid 原子性,一致性,隔離性,持久性
• 事務(wù)的操作

     begin棋蚌；-- 開啟事務(wù)
     start transaction嫁佳；-- 開啟事務(wù)
     commit； -- 提交事務(wù)
     rollback; -- 回滾

索引

• 有什么用:加快查詢的速度
• 原理:對我們的字段進行分組,如果大并發(fā)的情況下,修改數(shù)據(jù)會變慢

-- 創(chuàng)建
    create index 名字 on 表名(索引的字段);
-- 查看
    show index from 表名;
-- 刪除
    drop index 索引名 on 表名; # 必須指定表名

數(shù)據(jù)庫設(shè)計

三范式

• 第一范式（1NF）：強調(diào)的是列的原子性谷暮，即列不能夠再分成其他幾列蒿往。(一個數(shù)據(jù)一個字段)
• 第二范式（2NF）：首先是 1NF，另外包含兩部分內(nèi)容湿弦，一是表必須有一個主鍵瓤漏；二是沒有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分。（確定主鍵蔬充，擁有主鍵）
• 第三范式（3NF）：首先是 2NF蝶俱，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴娃惯。即不能存在：非主鍵列 A 依賴于非主鍵列 B跷乐，非主鍵列 B 依賴于主鍵的情況。（減少重復(fù)）

數(shù)據(jù)庫軍規(guī)

到此結(jié)?DragonFangQy 2018.5.15

1. 笛卡爾乘積 ?

2. 三級聯(lián)動數(shù)據(jù)庫資源,需要積分 ?

3. pymysql模塊的安裝 ?