数据库第四天

第四天"/>

数据库第四天

一，练习#联查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 bwhere a.sno=b.sno #表关系and b.sname='李军' #过滤条件#方式3：joinselect sum(degree) from scores a  inner join students bon 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#使用索引explainselect * 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 asselect * 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升序查询记录ascselect 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 3select * 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   sexfrom student--oracle的分页  rownum --select t.* from student t  limit 2  mysqlselect t.* from student t where rownum <= 2 --oracle的特殊语法--