--1

--1

use test;
create table dept (
 deptno int primary key,
 dname varchar(20)unique not null,
 loc varchar(20)
);

create table salgrade(
 grade int primary key,
 losal int not null,
 hisal int not null
);

create table emp(
 empno int primary key,
 ename varchar(20),
 sex char(2) check(sex in ('男','女')),
 job varchar(20),
 manager int,
 birthday datetime,
 hiredate datetime,
 sal int,
 comm int,
 deptno int ,
 foreign key(deptno) references dept(deptno)
);

insert dept values(10,'ACCOUNTING','NEW YORK');
insert dept values(20,'RESEARCH','DALLAS');
insert dept values(30,'SALES','CHICAGO');
insert dept values(40,'OPERATIONS','BOSTON');

insert salgrade values (1,700,1200);
insert salgrade values (2,1201,1400);
insert salgrade values (3,1401,2000);
insert salgrade values (4,2001,3000);
insert salgrade values (5,3001,9999);

insert emp values(7369,'SMITH','男','CLERK',7902,'1980-5-15','2000-12-17',800.00,null,20);
insert emp values(7900,'JAMES','男','CLERK',7698,'1981-5-30','2001-12-3',950.00,null,30);
insert emp values(7876,'ADAMS','女','CLERK',7788,'1987-1-7','2006-5-23',1100.00,null,20);
insert emp values(7654,'MARTIN','女','SALESMAN',7698,'1981-5-15','2001-9-28',1250.00,1400.00,30);
insert emp values(7521,'WARD','男','SALESMAN',7698,'1981-9-7','2001-2-22',1250.00,500.00,30);
insert emp values(7934,'MILLER','女','CLERK',7782,'1982-11-8','2002-1-23',1300.00,null,10);
insert emp values(7844,'TURNER','女','SALESMAN',7698,'1981-12-13','2001-9-8',1500.00,null,30);
insert emp values(7499,'ALLEN','女','SALESMAN',7698,'1981-2-22','2001-2-20',1600.00,300.00,30);
insert emp values(7782,'CLARK','男','MANAGER',7839,'1981-8-13','2001-6-9',2450.00,null,10);
insert emp values(7698,'BLAKE','男','MANAGER',7839,'1981-9-9','2001-5-1',2850.00,null,30);
insert emp values(7566,'JONES','男','MANAGER',7839,'1981-5-28','2001-4-2',2975.00,null,20);
insert emp values(7902,'FORD','男','ANALYST',7566,'1981-4-23','2001-12-3',3000.00,null,20);
insert emp values(7788,'SCOTT','男','ANALYST',7566,'1987-7-7','2007-4-19',3000.00,null,20);
insert emp values(7839,'KING','男','PRESIDENT',null,'1981-6-15','2001-11-17',5000.00,null,10);
insert emp values(7347,'ROBI','男','CLERK',7902,'1986-5-15','2000-11-15',3000.00,null,null);

--select
select * from emp;
/*select * from dept;
select * from salgrade*/
select ename from emp;
select empno, ename, job from emp;
select empno 编号, ename 姓名, job 工作 from emp;
select empno as 编号, ename as 姓名, job as 工作 from emp;
--distinct
select distinct job from emp;
select distinct deptno, job from emp;
select distinct deptno from emp;
select distinct job from emp;

select ename,sal, sal * 12 income from emp;
--where
select * from emp where sal = 1500;
select * from emp where sal > 1500;
select * from emp where empno != 7369;
select * from emp where empno <> 7369;
--is not null
select * from emp where comm is not null;
--is null
select * from emp where comm is null;
--and/or
select * from emp where sal > 1500 and comm is not null;
select * from emp where sal > 1500 or comm is not null;
--not
select * from emp where not(sal > 1500 and comm is not null);
--between  and
select * from emp where sal > 1500 and sal < 3000;
select * from emp where sal >= 1500 and sal <= 3000;
select * from emp where sal between 1500 and 3000;
select * from emp where hiredate between '2001-1-1' and '2001-12-12';
--in/not in
select * from emp where ename = 'SMITH';
select * from emp where ename in('SMITH', 'ALLEN', 'KING');
select * from emp where deptno in(select deptno from dept where dname='SALES');
select * from emp where empno = 7369 or empno = 7499 or empno = 7521;
select * from emp where empno in(7369, 7499, 7521);
select * from emp where empno not in(7369, 7499, 7521);
--查询员工BLAKE直接下属的信息
select ename from emp where exists(select * from emp where empno=7566);
select * from emp where manager in(select empno from emp where ename='BLAKE');
--like
select * from emp where ename='M';
select * from emp where ename like '_M%';
select * from emp where ename like '%M%';
select * from emp where ename like '%M%T%';
select * from emp where birthday like '%81%';
select * from emp where sal like '%5%';

--order by
select * from emp
select * from emp order by sal;
select * from emp order by sal asc;
select * from emp order by sal desc;
select * from emp where deptno = 20 order by sal desc, hiredate asc;
--迪卡尔积
select * from emp, dept;
select * from emp cross join dept;
--内连接
select * from emp;--15
select * from dept;--4
select ename,e.deptno from emp as e, dept as d where e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;
--要求查询出雇员的编号, 姓名, 部门的编号, 名称, 地址
select e.empno, e.ename, d.deptno, d.dname, loc from emp as e, dept as d where e.deptno = d.deptno;
--要求查询出雇员的姓名, 工作, 雇员的直接上级领导姓名
select e.ename, e.job, m.ename from emp as e, emp as m where e.manager = m.empno;
--要求查询出雇员的姓名, 工作, 雇员的直接上级领导姓名以及部门名称
select e.ename as 员工, e.job as 职位, m.ename as 上司, d.dname as 部门
 from emp e, emp m, dept d
  where e.manager = m.empno and e.deptno = d.deptno;
--要求查询出每个雇员的姓名, 工资, 部门名称, 工资在公司的等级, 及其领导的姓名及工资所在公司的等级
select e.ename, e.sal, d.dname, s.grade, m.ename, m.sal, ms.grade
 from emp e, dept d, salgrade s, emp m, salgrade ms
 where e.deptno = d.deptno
 and e.sal between s.losal and s.hisal
 and e.manager = m.empno
 and m.sal between ms.losal and ms.hisal;
select * from salgrade;
--左外连接
select * from emp;
select * from dept;
select empno, ename, d.deptno, dname, loc from emp e left outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e left join dept d on e.deptno = d.deptno;
--右外连接
select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right join dept d on e.deptno = d.deptno;
--满连接
select empno, ename, d.deptno, dname, loc from emp e full join dept d on e.deptno = d.deptno;
--select empno, ename, d.deptno, dname, loc from emp e,dept d where e.deptno = d.deptno;

select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.manager = m.empno;
select e.empno, e.ename, m.empno, m.ename from emp e left join  emp m on e.manager = m.empno;
select e.empno, e.ename, m.empno, m.ename from emp e right join  emp m on e.manager = m.empno ;

select d.deptno, d.dname, temp.c
 from (select deptno, count(e.empno) c from emp e group by e.deptno) temp, dept d
 where temp.deptno = d.deptno;

--聚合函数
select * from emp;
select count(ename) from emp;
select min(sal) from emp;
select max(sal) from emp;
select sum(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp where deptno = 20;
select avg(sal) from emp where deptno = 20;
--group by
--求出每个部门的雇员数量
select deptno from emp;
select deptno, count(empno) from emp group by deptno;
select manager,deptno,count(empno) from emp group by manager,deptno;
--按部门分组, 并显示部门的名称, 及每个部门的员工数
select d.dname, count(e.empno) from emp e, dept d
 where e.deptno = d.deptno
 group by d.dname;
select d.dname as '部门' ,count (e.empno) as '人数'
 from emp as e right join dept as d
 on e.deptno= d.deptno
 group by d.dname;

select * from dept;

select d.dname,count (e.empno)
 from emp as e full join dept as d
 on e.deptno=d.deptno
 group by d.dname;
--having
--要求显示出平均工资大于 2000 的部门编号和平均工资
select deptno, avg(sal) as avgsal
 from emp
 group by deptno
 having avg(sal) > 2000;
/*显示非销售人员(SALESMAN)工作名称以及从事同一工作雇员的月工资的总和,
并且要满足从事同一工作的雇员的月工资合计大于 5000,
输出结果按月工资的合计升序排序.
*/
select job,sum(sal) as sumsal
 from emp
 where job not in('SALESMAN')
 group by job
 having sum (sal)>5000
 order by sumsal;

 

select job, sum(sal) as sumsal
 from emp
 where job <> 'SALESMAN'
 group by job
 having sum(sal) > 5000
 order by sumsal;

select temp.job, sum(temp.sal) s
 from (select job, sal from emp e where job <> 'SALESMAN') as temp
 group by temp.job
 having sum(temp.sal) > 5000
 order by s;
--子查询值唯一
select * from emp
 where sal >(select sal from emp where empno = 7654);
select * from emp
 where sal >(select sal from emp where empno = 7654)
 and job = (select job from emp where empno = 7788);
select ename, job, sal from emp where sal = (select min(sal) from emp);
--要求查询出:　部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名
 select d.dname, temp.c, temp.a, e.ename
 from dept d,
 (select deptno, count(empno) c, avg(sal) a, min(sal) m from emp group by deptno) temp,
 emp e
 where d.deptno = temp.deptno and e.sal = temp.m;

select d.deptno, temp.dname, temp.c, temp.a, e.ename, e.sal
 from
 (select d.dname , count(e.empno) c, avg(e.sal) a, min(e.sal) m
 from emp e full join dept d
 on e.deptno = d.deptno
 group by d.dname) as temp,
 emp e,
 dept d
 where temp.m = e.sal
 and temp.dname = d.dname;

--any/all
select min(sal) from emp group by deptno;
select * from emp where sal in(select min(sal) from emp group by deptno);
select * from emp where sal =any(select min(sal) from emp group by deptno);
select * from emp where sal >all(select min(sal) from emp group by deptno);
--union
select deptno from emp
union
select deptno from dept;
--union all
select deptno from emp
union all
select deptno from dept;
--intersect
select deptno from emp
intersect
select deptno from dept;
--except
select deptno from dept --10,20,30,40
except
select deptno from emp;--null,10,20,30

--top,percent
select top 10 * from emp;
select top 50 percent * from emp;

select top 10 * from emp
where empno not in
 (select top 10 empno from emp);

--通用用表达式
with fy as (select *,rn = ROW_NUMBER()over(order by empno DESC) from emp)
select * from fy where rn between 11 and 20;

--常用函数
select cast(2.002 as int)as value;
select cast('2004-09-08 12:22:43'as datetime) value
select round(1.23456,2)as value;
select rand() as value;
select ename,IsNull(comm,0) as value from emp;
select substring('abcd',2,2) as value;
select len('abcdefg') as value;
select lower('AbCdEfG'),upper('AbCdEfG');
select getdate() as  value;
select convert(char(10),getdate(),20) value;
select convert(char(8),getdate(),108) value;
select datepart(yy,getdate()) value;
select year(getdate())value;--month,day
select datediff(hh,getdate(),getdate()+12.3) value;
select dateadd(mi,-8,getdate()) value;

select count(ename) from emp;
select min(sal) from emp;
select max(sal) from emp;
select sum(sal) from emp;
select avg(sal) from emp;