Day3 高級(jí)查詢

一含懊、連接查詢

1.接連查詢:

同時(shí)查詢多個(gè)表中的數(shù)據(jù)
SELECT * FROM 表名1,表名2... WHERE 連接條件;
連接查詢?nèi)绻患舆B接條件纹笼，結(jié)果是一個(gè)笛卡爾積：（a,b,c）(1,2,3) --> (a1,a2,a3,b1,b2,b3,c1,c2,c3)

查詢每個(gè)老師對(duì)應(yīng)的學(xué)院

SELECT teaname, collname FROM tb_college, tb_teacher WHERE tb_teacher.collid=tb_college.collid;

查詢學(xué)生姓名對(duì)應(yīng)的學(xué)科名的分?jǐn)?shù)

SELECT stuname, couname, mark FROM tb_student, tb_course, tb_score 
WHERE tb_score.stuid=tb_student.stuid and tb_score.couid=tb_course.couid;

查詢平均分高于80分的學(xué)生名字和對(duì)應(yīng)的平均分分?jǐn)?shù)
注意:如果連接查詢的時(shí)候既有連接條件又有篩選條件，我們要把篩選條件寫在連接條件后邊

SELECT stuname, avg_mark FROM 
tb_student, 
(SELECT stuid, AVG(mark) as avg_mark FROM tb_score GROUP BY(stuid)) as temp_t 
WHERE tb_student.stuid = temp_t.stuid and avg_mark>80;

2.內(nèi)連接

SELECT * FROM 表1 inner join 表2 on 表2的連接條件 inner join 表3 on 表3的連接條件 ...;
注意: 中間表寫在最前面(存在關(guān)聯(lián)其他表外鍵的表)
查詢學(xué)生姓名對(duì)應(yīng)的學(xué)科名的分?jǐn)?shù)

SELECT stuname, couname, mark FROM tb_score  
INNER JOIN tb_studnt ON tb_student.stuid=tb_score.stuid  
INNER JOIN tb_course ON tb_course.couid=tb_score.couid 
WHERE mark < 60;

3.外連接

外連接分為左外連接、右外連接和全連接，但是在MySQL中支持左外連接和右外連接
表1(左表) left/right/inner join 表2(右表)
左外連接：將左表中對(duì)應(yīng)字段的所有數(shù)據(jù)取出，然后再對(duì)應(yīng)的右表中字段的值佛猛，如果右表對(duì)應(yīng)的值不存在結(jié)果就為null
右外連接：將右表中對(duì)應(yīng)字段的所有數(shù)據(jù)取出，然后再對(duì)應(yīng)的左表中字段的值坠狡，如果左表對(duì)應(yīng)的值不存在結(jié)果就為null

查所有學(xué)生名對(duì)應(yīng)的成績

SELECT stuname, mark FROM tb_score INNER JOIN tb_student ON tb_student.stuid=tb_score.stuid;
SELECT stuname, mark FROM tb_score  RIGHT JOIN tb_student ON tb_student.stuid=tb_score.stuid;

二继找、字段索引

字段的索引就相當(dāng)于于目錄，作用是為了能夠快速的對(duì)這個(gè)字段進(jìn)行查找
添加索引的好處是可以大大的提高查詢效率逃沿；缺點(diǎn)是：1.會(huì)消耗額外的存儲(chǔ)空間婴渡， 2.會(huì)讓添加和刪除的效率降低
建議: 1.索引不能濫用 2.如果項(xiàng)目中針對(duì)某個(gè)字段的查詢很頻繁，建議加個(gè)對(duì)應(yīng)的索引
EXPLAIN：獲取執(zhí)行計(jì)劃

EXPLAIN SELECT * FROM tb_student WHERE stuid=110;
EXPLAIN SELECT * FROM tb_student WHERE stuname='張三';

添加索引: create index 索引名稱 on 表名 (字段名); - 給指定表中的指定字段添加索引

CREATE INDEX index_stuname on tb_student (stuname);   -- 給名字加索引
CREATE INDEX index_stuname1 on tb_student (stuname(1));    -- 按姓加索引

刪除索引

ALTER TABLE tb_student DROP INDEX index_stuname;

三凯亮、DCL

1.創(chuàng)建用戶

create user 用戶名@登錄地址
登錄地址: （限制用戶能夠登錄MySQL的主機(jī)地址）边臼， ip地址(指定地址), localhost(數(shù)據(jù)庫本機(jī)), %(任何位置)
CREATE USER 'zhangshan'@'%' IDENTIFIED BY 'yuting123456';

刪除用戶: drop user 用戶名;
DROP USER 'zhangshan';

2.授權(quán)

grant 權(quán)限類型 on 數(shù)據(jù)庫.對(duì)象 to 用戶名;

GRANT SELECT on school.tb_student TO 'zhangshan';
GRANT UPDATE on school.tb_student TO 'zhangshan';  
GRANT all PRIVILEGES ON school.* TO 'zhangshan';   -- 添加所有權(quán)限  
GRANT all PRIVILEGES ON school.* TO 'zhangshan' WITH GRANT OPTION;   -- 添加所有權(quán)限，并且能夠?qū)⒆约旱臋?quán)限再授權(quán)給其他用戶

3.召回授權(quán)

REVOKE 權(quán)限類型 on 數(shù)據(jù)庫.對(duì)象 from 用戶名;

REVOKE DELETE on school.* FROM 'zhangshan';
REVOKE all PRIVILEGES on school.* FROM 'zhangshan';
REVOKE all PRIVILEGES on school.* FROM 'zhangshan';
REVOKE SELECT on school.tb_student FROM 'zhangshan';
REVOKE UPDATE on school.tb_student FROM 'zhangshan';

4.事務(wù)

完成一個(gè)任務(wù)需要執(zhí)行多條sql假消，但是要求這多個(gè)操作中只要有一個(gè)操作失敗柠并，這個(gè)任務(wù)就失敗，數(shù)據(jù)全部還原富拗；所有的操作都成功臼予，整個(gè)任務(wù)才成功的時(shí)候就使用事務(wù)

開啟事務(wù)環(huán)境
BEGIN;
UPDATE tb_student set stuname='444' WHERE stuname='222';
UPDATE tb_student set birth2='1990-1-1' WHERE stuname='222';
提交事務(wù)(只有begin到commit之間的所有的sql都執(zhí)行成功，才會(huì)執(zhí)行commit; 否則執(zhí)行rollback)
COMMIT;
事務(wù)回滾(放棄beigin到commit之間執(zhí)行成功的所有sql語句的結(jié)果)
ROLLBACK;

四啃沪、在python中使用pyMySQL

第一步：按照第三方庫pymysql

pip install -i https://pypi.doubanio.com/simple pymysql

第二步：創(chuàng)建連接

    conn = pymysql.connect(host='localhost',    # 本機(jī)用localhost粘拾，遠(yuǎn)程用IP地址
                           port=3306,           # 端口號(hào)
                           user='root',         # 用戶名
                           password='123456',   # 密碼
                           database='db_myschool',  # 數(shù)據(jù)庫
                           charset='utf8',      # 支持中文
                           autocommit=True)     # 每次執(zhí)行sql語句自動(dòng)提交

第三步：

# 獲取游標(biāo)對(duì)象 - 提供數(shù)據(jù)庫的上下文
    cursor = conn.cursor()