Day3 高级查询

一、连接查询

1.接连查询:

同时查询多个表中的数据
SELECT * FROM 表名1,表名2... WHERE 连接条件;
连接查询如果不加连接条件，结果是一个笛卡尔积：（a,b,c）(1,2,3) --> (a1,a2,a3,b1,b2,b3,c1,c2,c3)

查询每个老师对应的学院

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

查询学生姓名对应的学科名的分数

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分的学生名字和对应的平均分分数
注意:如果连接查询的时候既有连接条件又有筛选条件，我们要把筛选条件写在连接条件后边

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.内连接

SELECT * FROM 表1 inner join 表2 on 表2的连接条件 inner join 表3 on 表3的连接条件 ...;
注意: 中间表写在最前面(存在关联其他表外键的表)
查询学生姓名对应的学科名的分数

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(右表)
左外连接：将左表中对应字段的所有数据取出，然后再对应的右表中字段的值，如果右表对应的值不存在结果就为null
右外连接：将右表中对应字段的所有数据取出，然后再对应的左表中字段的值，如果左表对应的值不存在结果就为null

查所有学生名对应的成绩

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;

二、字段索引

字段的索引就相当于于目录，作用是为了能够快速的对这个字段进行查找
添加索引的好处是可以大大的提高查询效率；缺点是：1.会消耗额外的存储空间， 2.会让添加和删除的效率降低
建议: 1.索引不能滥用 2.如果项目中针对某个字段的查询很频繁，建议加个对应的索引
EXPLAIN：获取执行计划

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.创建用户

create user 用户名@登录地址
登录地址: （限制用户能够登录MySQL的主机地址）， ip地址(指定地址), localhost(数据库本机), %(任何位置)
CREATE USER 'zhangshan'@'%' IDENTIFIED BY 'yuting123456';

删除用户: drop user 用户名;
DROP USER 'zhangshan';

2.授权

grant 权限类型 on 数据库.对象 to 用户名;

GRANT SELECT on school.tb_student TO 'zhangshan';
GRANT UPDATE on school.tb_student TO 'zhangshan';  
GRANT all PRIVILEGES ON school.* TO 'zhangshan';   -- 添加所有权限  
GRANT all PRIVILEGES ON school.* TO 'zhangshan' WITH GRANT OPTION;   -- 添加所有权限，并且能够将自己的权限再授权给其他用户

3.召回授权

REVOKE 权限类型 on 数据库.对象 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.事务

完成一个任务需要执行多条sql，但是要求这多个操作中只要有一个操作失败，这个任务就失败，数据全部还原；所有的操作都成功，整个任务才成功的时候就使用事务

开启事务环境
BEGIN;
UPDATE tb_student set stuname='444' WHERE stuname='222';
UPDATE tb_student set birth2='1990-1-1' WHERE stuname='222';
提交事务(只有begin到commit之间的所有的sql都执行成功，才会执行commit; 否则执行rollback)
COMMIT;
事务回滚(放弃beigin到commit之间执行成功的所有sql语句的结果)
ROLLBACK;

四、在python中使用pyMySQL

第一步：按照第三方库pymysql

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

第二步：创建连接

    conn = pymysql.connect(host='localhost',    # 本机用localhost，远程用IP地址
                           port=3306,           # 端口号
                           user='root',         # 用户名
                           password='123456',   # 密码
                           database='db_myschool',  # 数据库
                           charset='utf8',      # 支持中文
                           autocommit=True)     # 每次执行sql语句自动提交

第三步：

# 获取游标对象 - 提供数据库的上下文
    cursor = conn.cursor()

第三步：

# 查询所有数据
    cursor.execute('select * from tb_student')
    for row in cursor.fetchall():
        print(row)
    print("共%d条数据。" % cursor.rowcount)

第四步：

# 关闭连接
    conn.close()