-- ==============连接查询==================
-- 1.连接查询: 同时查询多个表中的数据
-- select * from 表名1,表名2,... where 连接条件;
-- 连接查询如果不加连接条件,结果是笛卡尔积: (a,b,c) (123) --> (a1,a2,a3,b1,b2,b3,c1,c2,c3)
-- (小明, 小王, 小玲) (设计学院, 信通学院) --> 笛卡尔积
-- SELECT teaname, collname from tb_college, tb_teacher;
-- 查询每个老师对应的学院
SELECT teaname, collname from tb_college, tb_teacher WHERE tb_teacher.collid=tb_college.collid;
-- 查询学生姓名对应的学科名的分数(3个表)
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) from tb_score group by stuid;
select stuname, avg_mark from tb_student as t1,
(select stuid, avg(mark) as avg_mark from tb_score group by stuid
) as t2 where t1.stuid = t2.stuid;
-- 法二 inner jion ... on(连接条件) - 内连接
select stuname as 姓名, avg_mark as 平均成绩 from tb_student as t1 inner join
(select stuid, avg(scmark) as avg_mark from tb_score group by stuid
) as t2 on t1.stuid = t2.stuid;
-- 查询平均分高于80分的学生名字和对应的平均分分数
-- 法一，通过在分组后Having
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_score.stuid = temp_t.stuid;
-- 法二 and avg_mark>80;(在后面加,写在连接条件后面)
-- 注意:如果连接查询的时候既有连接条件又有筛选条件,我们要把筛选条件写在连接条件后边
-- 2.内连接
-- 注意: 中间表写在最前面(存在关联其他表外键的表)
-- SELECT * from 表1 INNER JOIN 表2 on 表2的连接条件 INNER JOIN 表3 on 表3的连接条件 ...;
--查询学生姓名对应的学科名的分数，from后面先写连接的表(tb_score通
过这个表去连接其他的两个表)
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;
-- 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 'zhangsan'@'%' IDENTIFIED by 'tanghao123456'; -- 此时还没有权限
-- 删除用户: drop user 用户名;
drop USER 'zhangsan';
-- 2.授权 SELECT(给它查看权限)
-- grant 权限类型 on 数据库.对象
GRANT SELECT on school.tb_student to 'zhangsan';
GRANT UPDATE on school.tb_student to 'zhangsan';
GRANT all PRIVILEGES on school.* to 'zhangsan'; -- 添加所有权限
GRANT all PRIVILEGES on school.* to 'zhangsan' with grant option;
-- 添加所有权限,并且能够将自己的权限再授权给其他用户。
-- 3.召回授权
-- REVOKE 授权类型 on 数据库 对象 from 用户名;
REVOKE DELETE on schcool.* from 'zhangsan';
-- 4.事务 (如银行转账,支付环境等就可以用)
-- 如果完成一个任务需要执行多条sql，但是要求多个操作只要有一个操作失败,这个任务就失败,数据全部还原;所有的操作都成功,整个任务才成功的时候就使用事务
-- 开启事务环境
BEGIN;
UPDATE tb_student set stuname ='一一' WHERE stuname='12';
DELETE FROM tb_student WHERE stuid=110;
-- 提交事务(只有beigin到commit之间的所有的sql都执行成功,才会执行commit;
否则执行rollback)
COMMIT;
-- 事务回滚(放弃beigin到commit之间执行成功的所有sql语句的结果) 执
行的任务无效回到之前的状态
ROLLBACK;
网友评论