java大数据学习---第二阶段---第四天---多表联查 SQL语句执行顺序 SQL索引

MySQL数据库中 单表查询上限1000w,超过会影响查询效率

多表联查

1.概念:表table代表了生活中一个主体,表关联则代表了表之间的关系

 表关联:association   
    
2.表关系   四种
  1>  一对一 one to one QQ和QQ邮箱，员工和员工编号
  2>  一对多 one to many 最常见，部门和员工，用户和订单
  3>  多对一 many to one 一对多反过来，员工和部门，订单和用户
  4>  多对多 many to many 老师和学生，老师和课程  比如:1师n生 1生n师   就是多对多
      
3.创建表
      //多条SQL语句一起执行时 每条SQL语句用  ;  表示结束
表设计特点：
表都以s结束，标识复数
字段多以表的首字母作为开头，在多表联查时，方便标识出是哪个表的字段
      
4.插入数据
      
5.多表联查的方式  三种
      
方式1:	笛卡尔积:Cartesian product
	  		 结构:
				select ? from 表1,表2....
      		产生的问题:
				产生了庞大的结果集,出现了大量重复的数据
	  		解决办法:
				select ? from 表1,表2...where 过滤条件#描述两个表的关系 比如1表部门名=2表部门名
      			比如:计算计算机导论课程所得的总分
					SELECT SUM(scores.degree) FROM courses,scores WHERE courses.cno=scores.cno AND courses.cname="计算机导论"
                    select	聚合函数(表名.字段名)/字段名   from    表1,表2	where	描述两个表的关系	and		真正的业务条件  
       /*笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。这点很值得注意，实
         际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，			包括过滤时的判断，都是既耗费资源，又浪费时间。*/    
                    
方式2: 连接查询 join
          //使用链接查询 尽量 join左边的表越简单越好  左边的比右边的表要简单   小表驱动大表
          练习5 统计陈冰老师能将的课程名称
			SELECT courses.cname FROM teachers JOIN courses ON teachers.tno=courses.tno WHERE teachers.tname="陈冰"
			select	聚合函数(表名.字段名)/字段名 from	表1	join	 表2		on	 描述两个表的关系		where		真正的业务需求
            
        三种连接 : ----最高效的是内连接
		内连接 : inner join   取两表的交集部分-----简写成 join
		左（外）连接 : left join 取左表的所有数据和右表符合条件的数据,然后右表不满足条件的数据填充null
		右（外）连接 : right join 取右表的所有数据和左表符合条件的数据,然后左表不满足条件的数据填充null
                   
方式3 :子查询 也叫 嵌套查询   subquery       
        1.概念:子查询是指把第一次的查询结果 作为第二次查询的条件 继续发起查询语句 
    			子查询执行效率低慎用。
		2. 
   		 //1.单行查询  用 = 
			#练习1   查询reserch部门的员工信息
			#第一次  查部门表   根据部门名称查部门编号
   					SELECT deptno FROM dept1 WHERE  dname='reserch'
			#第二次查  差员工表 根据部门编号查询员工信息
					SELECT * FROM emp1 WHERE deptno=2
			#子查询
					SELECT * FROM emp1 WHERE deptno=(SELECT deptno FROM dept1 WHERE  dname='reserch')     
   		 //2 多行查询 用 in
			#练习3 查询Java开发部和reserch部门的员工姓名
	SELECT ename FROM emp1 WHERE deptno IN (SELECT deptno FROM dept1 WHERE dname="java开发部" OR dname="reserch")
    	或者
	SELECT ename FROM emp1 WHERE deptno IN (SELECT deptno FROM dept1 WHERE dname IN ("java开发部","reserch"))    
            
 //备注: 笛卡尔积和连接查询如果不指定所查内容是哪个表 就会查出来两个表所有的信息 所以有时候需要指定查那个表的内容           

多表联查 综合案例

#练习1 查询可以讲高等数学的老师的名字
#笛卡尔积
SELECT tname FROM teachers,courses WHERE teachers.tno=courses.tno AND courses.cname="高等数学"
#连接查询
SELECT tname FROM teachers JOIN courses ON teachers.tno=courses.tno WHERE courses.cname="高等数学"
#子查询  在不同的表里查询
SELECT tname FROM teachers WHERE tno=(SELECT tno FROM courses WHERE cname="高等数学")


#综合练习2
#练习2 查询学生曾华的总得分
#笛卡尔积
SELECT SUM(scores.degree) FROM scores ,students WHERE scores.sno=students.sno AND students.sname="曾华"
#链接查询
SELECT SUM(scores.degree) FROM scores JOIN students ON scores.sno=students.sno WHERE students.sname="曾华"
#子查询   在不同的表里查询
SELECT SUM(scores.degree) FROM scores WHERE sno=(SELECT sno FROM students WHERE sname="曾华")
    
#子查询  
#查询高于平均工资的员工信息   在一个表里查询
SELECT * FROM emp1 WHERE sal>(SELECT AVG(sal) FROM emp1 )

SQL语句执行顺序

(1) FROM [left_table] 选择表
 
(2) ON <join_condition> 链接条件
 
(3) <join_type> JOIN <right_table> 链接
 
(4) WHERE <where_condition> 条件过滤
 
(5) GROUP BY <group_by_list> 分组
 
(6) AGG_FUNC(column or expression),... 聚合
 
(7) HAVING <having_condition> 分组过滤
 
(8) SELECT (9) DISTINCT column,... 选择字段、去重
 
(9) ORDER BY <order_by_list> 排序
 
(10) LIMIT count OFFSET count; 分页

SQL面试题 拓展

1.索引

1.概念:索引是一种排好序的快速查找的数据结构
2.作用: 提高数据库的查询效率
3.分类:
	1> 单值索引：一个索引只包括一个列/字段，一个表可以有多个列
	2> 复合索引：一个索引同时包括多列/字段    
    3> 唯一索引：特殊的单值索引,

4.创建单值索引
# 提高查询效率 建议给 经常用来查询的字段加索引
#1.查看索引   主键自带索引,不需要再添加索引
# 固定结构 show index from 表名    
	SHOW INDEX FROM students
#2. 创建索引    
//# 固定结构 create index 索引名 on 表名(字段名)
	CREATE INDEX   index1  ON students(sname)
	SHOW INDEX FROM students
#3.使用索引 按照索引列去查(看不到索引的操作)
#查看学生信息
SELECT * FROM students WHERE sname="陆君"
#4.查询SQL的执行计划/性能(只想关注你的SQL是否用到了索引  使用explain + SQL语句   查看执行结果里的key)   其实就是查看是否使用了索引
EXPLAIN SELECT * FROM students WHERE sname="陆君"
    
    
5.创建唯一索引     找到合适的列,字段的值都要唯一
//#语法结构 create unique index 索引名 on 表名(字段名) 
#create unique index index2 on students(ssex)#不能加唯一索引 值大量重复
CREATE UNIQUE INDEX index2 ON students(sno)
SHOW INDEX FROM students
#使用索引
SELECT * FROM students WHERE sno=101
EXPLAIN SELECT * FROM students WHERE sno=101 #不要给主键索引添加唯一索引 会让主键索引失效  单值索引可以加但是使用的时候还是主键索引,所以还是不要加  
    
6.创建复合索引
  # 创建复合索引 一个索引包含着多个列
 # 固定结构 CREATE INDEX 索引名 ON 表名(字段名1,字段名2....)    
CREATE INDEX index3 ON emp1(ename,job,deptno)
SHOW INDEX FROM emp1
#使用索引 最左特性  使用索引 必须包含复合最左边的元素
EXPLAIN SELECT * FROM emp1 WHERE ename="jack" #复合索引生效
EXPLAIN SELECT * FROM emp1 WHERE job="总监"  #复合索引失效  没有用到复合索引
EXPLAIN SELECT * FROM emp1 WHERE ename="jack"AND job="总监" #生效  且 两个条件可以互换位置 不影响  只要包含最左元素就好
EXPLAIN SELECT * FROM emp1 WHERE ename="jack"OR job="总监" #失效   多个条件用and连接才会生效 
    
    
7.删除索引
   语法结构: alter table 表名 drop index 索引名   
       
       
8.优缺点
  优点:
	-大大提高了查询效率
	-本质上数据库会为索引列的数据进行排序,快速查询
  2缺点:
	-本身索引是一个单独的表,也需要占空间的
	-索引适合查询的业务,但是,也需要同步更新修改一些新的数据,需要一定的时间
9.原则:
	1> 什么时候添加索引?
         频繁的按照一个规则去查询的数据,就应该考虑添加索引
	2> 给谁添加索引?
         给那些经常作为查询条件的字段添加索引
	3> 加啥索引?
         索引是有分类的,单值索引/唯一索引/复合索引 看情况选择不同的索引类型
    4> 如何查看SQL的执行计划/性能 或者说 怎么查看使用什么索引?
         使用 Explain
        

2.视图

3.SQL优化