数据库第四天

一，练习
			#联查students和scores表的练习:
			#分别使用笛卡尔积 / join / 子查询实现
			#查询sname='李军'的总分
			#方式1：子查询
			select sum(degree) from scores where sno=(
				select sno from students where sname='李军'
			)
			#方式2：笛卡尔积
			select sum(degree) from scores a , students b
			where a.sno=b.sno #表关系
			and b.sname='李军' #过滤条件
			#方式3：join
			select sum(degree) from scores a  inner join students b
			on a.sno=b.sno #表关系
			and b.sname='李军' #过滤条件

二，索引
	--1，概述
		提高查询效率，给字段加索引
		普通索引：
		唯一索引：字段的值必须唯一
		复合索引：一个索引包含着多个字段
	--2，语法
		创建普通索引：create index 索引名 on 表名(字段名)
		查看索引：show index from 表名
		创建唯一索引：ALTER TABLE 表名 ADD UNIQUE (字段名)
		创建复合索引：ALTER TABLE 表名 ADD INDEX 索引名 (字段名,字段名)
		删除索引：ALTER TABLE 表名 DROP INDEX 索引名
	--3，测试
			#创建普通索引：
			create index name_index on teachers(tname)
			#查询索引
			show index from teachers
			#使用索引
			explain
			select * from teachers where tname='易天'
			#创建唯一索引
			ALTER TABLE teachers ADD UNIQUE (tbirthday)
			#使用索引
			explain 
			#解释SQL的执行过程,主要是看有没有用到索引(看key的是否有值)
			select * from teachers 
			where tbirthday='1958-12-02 00:00:00'
			#创建复合索引--使用时遵循最左特性：优先使用最左边的
			ALTER TABLE teachers ADD INDEX fuhe_index
			(tname,tsex,tbirthday)

	--4，总结
		好处：提高查询效率
		坏处：索引不适合有太多，占用内存
		怎么用索引？
			--正常的写SQL语句
			--使用explain关键字来查看SQL的执行计划，SQL有没有用到索引(key)
			--查看所有的索引:show index from 表名
三，视图
	--1，概述
		当做一个表来用，缓存了上一次的查询结果。
	--2，语法
		创建视图：create view 视图名 as SQL语句 
		使用视图：select  * from  视图名
	--3，测试
			#创建视图
			create view stu_view 
			as
			select * from students limit 3

			#使用视图
			select * from stu_view
	--4，总结
		好处：优化了SQL，复杂的SQL只写一次，结果就会缓存在视图中，下次查视图就可以了
		坏处：数据都是重复的，占内存。SQL无法优化。
四，SQL面试题		
			#查询所有记录
			select * from students  #低效
			#只查询指定
			select sno,sname from students #高效
			#查询id为100的记录
			select sno,sname from students where sno=101
			#模糊查询记录 %通配符，通配0~n字符
			select sno,sname,class from students where class like '%3%'
			#查询之间范围之间的所有记录 [101~105]
			select * from students where sno BETWEEN 101 and 105
			#in 以下两句等效
			select * from students where sno in (101,105)
			select * from students where sno=101 or sno=105
			#查询满足两个条件的记录
			select * from students where sno=103 and ssex='男'
			#查询jack的住址(tb_user 联查 tb_user_address)
			select address from tb_user_address 
			where user_id = (
					select id from tb_user where name='jack'
			)
			#算每个学员的年龄
			select sname,year(now())-year(sbirthday) age 
			from students
			#按age升序查询记录asc
			select sname,year(now())-year(sbirthday) age 
			from students order by age desc
			#以name升序、age降序查询记录
			select sname,year(now())-year(sbirthday) age 
			from students order by sname #unicode值
			#总记录数
			select count(1) from scores
			#查询记录中最年长和最年轻
			SELECT max(age),min(age) from user
			#查询年龄最大的用户信息
			SELECT * from user order by age desc LIMIT 1#年龄倒序取第一个
			#查询大于平均年龄的记录
			SELECT * from user where age > (
				SELECT avg(age) from user 
			)
			#查询各部门的最高薪
			select max(sal),deptno from emp GROUP BY deptno
			#查询各部门的平均工资
			select avg(sal),deptno from emp GROUP BY deptno
			#查询存在部门的员工信息
			select * from emp where deptno is not null
			#查询没划分部门的员工信息
			select * from emp where deptno is  null
			#查询同名的员工记录
			select * from emp 
			where #count(ename) >1 where里不能用聚合函数
			ename in(
				select ename from emp 
				group by ename  #名字相同，就按名字分组
				having count(ename)>1 #过滤，次数>1的
			)
			#查询得分前3名的学员信息
			select sno from scores order by degree desc limit 3
			select * from students where sno in (103,107,105)
			select * from students where sno in (
				#子查询里不支持order by / limit
				#select sno from scores order by degree desc limit 3
			)
			#查询课程是“计算机导论”的，得分前3名的学员信息
			#课程号“3-105”的倒数最后3名学员排行
			#查询女老师的信息

五，Oracle
	--1，概述
		和MySQL一样，可以用来存储数据和管理数据
	--2，安装
		--服务器端：设置密码
		--客户端：PLSQL，免安装绿色版，双击就能用
			--登录：使用system，口令就是安装时的密码
	--3，使用
		--MySQL：数据库 - 表 - 记录
		--Oracle：用户 - 表 - 记录
		--创建用户：左上角新建-用户-输入用户名和密码-设置权限dba/resource/connect-确定
		--新用户登录：左上角的钥匙-输入用户名和密码-确定
		--创建表：选中Tables-右键-新建-输入表名、字段、主键-确定
		--创建记录：选中表-右键-编辑数据-填新数据-√
	--4，测试
			select t.* from student t
			--oracle默认的日期格式：日-月-年
			insert into student values('王','1','10-8月-1990','php',3)
			--修改日期格式：年-月-日
			alter session set nls_date_format = 'yyyy-mm-dd' ;
			--按照新格式插入数据
			insert into student values('刘','0','2000-1-1','java',4)
			--decode判断，如果查到的是0就显示女，是1就显示男
			select ssex, decode(ssex,0,'女',1,'男') sex  from student
			--casewhen 判断，如果查到的是0就显示女，是1就显示男
			select ssex, 
			       case ssex when '0' then '女' else '男' end   sex
			from student
			--oracle的分页  rownum 
			--select t.* from student t  limit 2  mysql
			select t.* from student t where rownum <= 2 --oracle的特殊语法

--