SQL-(3) --复杂查询

男娘i 2024-02-17 20:23 5阅读 0赞

接SQL（2） [http://blog.csdn.net/u013943420/article/details/65629186][http_blog.csdn.net_u013943420_article_details_65629186]

-- 表的复杂查询    多表查询
    select * from emp, dept   -- 笛卡尔积 emp X dept
    -- 显示雇员名称，雇员工资及所在部门的名字，部门号
    select ename, sal, loc, emp.deptno from emp, dept where dept.dname='sales' and emp.deptno = dept.deptno
    -- 表名太长时使用别名来代替   emp as e
    select ename, sal, loc, e.deptno from emp as e, dept where dept.dname='sales' and e.deptno = dept.deptno
    -- 显示部门号为10的部门名、员工名和工资        并按部门排序
    select d.deptno, e.ename, e.sal from emp e,dept d where e.deptno=30 and e.deptno = d.deptno
    order by d.dname
    
    -- 自连接 内连接   要找的两个信息都在一个表里
    -- 显示某个员工的上级领导的名字
    select ename from emp where  empno = (select mgr from emp where ename='smith')
    -- 显示每一个员工和他上级领导的名字
    -- 把emp看做两个表     内连接 
    select worker.ename as '雇员名', boss.ename from emp worker, emp as boss where worker.mgr=boss.empno
    -- 左外连接和右外连接    ( ... left join ... on ...)
    -- 显示公司每个员工和它的上级名字 没有上级的人也要显示出来
    -- 左外连接:左边的表的记录全都要显示， 如果没有匹配的记录就用null填充
    select w.ename worker, b.ename boss from emp w left join emp b on w.mgr=b.empno
    select w.ename worker, b.ename boss from emp w right join emp b on w.mgr=b.empno
    select w.ename worker, b.ename boss from emp w inner join emp b on w.mgr=b.empno
    
    
    
    -- 子查询  嵌套查询：单行子查询， 多行子查询
    select * from emp where deptno=
    (select deptno from emp where ename='smith')
    -- 查询和部门20的工作相同的雇员的名字
    select * from emp where job in
    (select distinct job from emp where deptno=20)
    
    -- from子句中使用子查询  视图
    -- 显示高于部门平均工资的员工信息, 部门平均工资
    select emp.ename, emp.sal, temp.myavg from emp, 
    (select avg(sal) myavg, deptno from emp group by deptno) as temp
    where emp.deptno=temp.deptno and emp.sal > temp.myavg
    
    -- 分页查询
    -- 显示第5到10个入职的雇员
    select top 6 * from emp where empno not in
    (select top 4 empno from emp order by hiredate)
    order by hiredate
    
    
    --找到早于12年前的员工
    select * from emp where datediff(YEAR, hiredate, GETDATE())>12
    --找到早于12天前的员工
    select * from emp where datediff(DAY, hiredate, GETDATE())>12
    --以首字母大写的方式显示员工姓名
    select upper(substring(ename,1,1))+LOWER(substring(ename,2,len(ename)-1)) from emp
    --显示正好为5个字符的员工的姓名
    select * from emp where ename like '_____'
    select * from emp where len(ename)=5
    --用a替换t，显示所有员工姓名
    select REPLACE(ename, 't', 'a') from emp
    --显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在的月、年排序
    select ename, DATEPART(year, hiredate) y, datepart(month, hiredate) m from emp order by m, y
    --列出至少有一个员工的所有部门
    select count(*), deptno from emp group by deptno having count(*) > 1
    
    
    
    select * from dept
    select * from emp
    
    --- 测试表为了测试分页查询效率更高 ----
    create table test(
    testId int primary key identity(1,1), --- 自增长 从1开始，每次增加1
    testName varchar(30),
    testPass varchar(30)
    )
    
    insert into test(testName, testPass) values('jiaozl','jiaozl')
    
    insert into test(testName, testPass) (select testName, testPass from test)
    
    select count(*) from test
    
    select * from test
    
    -- id排序 
    select top 6 * from test where testId not in
    (select top 20 testId from test)
    -- 通过临时表 删除重复记录 
    select distinct testname, testpass into #temp from test
    delete from test
    insert into test select * from #temp
    drop table #temp

[http_blog.csdn.net_u013943420_article_details_65629186]: http://blog.csdn.net/u013943420/article/details/65629186