mysql大事务查询_MySQL连接查询和事务

=================================连接查询=======================================

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;

查询学生名对应的学生的平均成绩

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 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 [email protected]

登录地址: (限制用户能够登录MySQL的主机地址)， ip地址(指定地址), localhost(数据库本机), %(任何位置)

CREATE USER [email protected]%’ 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;