83.数据库的使用3

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

-- 查询每个老师对应的学院
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;

-- 查询学生名对应的学生的平均成绩
-- SELECT stuid, AVG(mark) as avg_mark FROM tb_score GROUP BY(stuid);
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;

-- 查询平均分高于80分的学生名字和对应的平均分分数
SELECT stuname, avg_mark FROM
tb_student,
(SELECT stuid, AVG(mark) as avg_mark FROM tb_score GROUP BY(stuid) HAVING AVG(mark)>80) as temp_t
WHERE tb_student.stuid = temp_t.stuid;

-- 查询平均分高于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_student 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(解释：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_stuname1;

-- ==================================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;

having 和where的区别在于：

    select goods_price,goods_name from sw_goods where goods_price > 100
    select goods_price,goods_name from sw_goods having goods_price > 100

因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。
总之写having的前提必须是前面已经查询了这项才能才后面写该项的查询条件，不然是会报错的