-- 创建表案例
create table table1
(
--  |字段|类型|约束    |约束名  |主键 
  t_id int constraint t_pk primary key,
  t_name varchar(20) not null,
  t_age int not null,
  t_sex varchar(1)
)
create table table2
(
  t2_id int constraint t2_pk primary key,
  t2_name varchar(15) not null,
--  |字段|类型 |约束    |约束名  |外键     |涉及到     |表名
  t1_id int constraint t2_fk foreign key references table1
)

constraint t_pk primary key(字段1,字段2,字段3)

select ename, sal*12 as "年薪" from emp

-- 过滤重复字段
select distinct deptno as "部门编号" from emp

-- between的使用 查询sal在800-1500之间的值【包含两端】
select sal from emp 
where  sal between 800 and  1500

-- 只取in内值
select sal from emp 
where sal in(800,1500)
-- 只取in外的值
select sal from emp 
where sal not in(800,1500)

-- 取所有sal中的前两个值
select top 2 sal from emp

-- 输出工资在1500-3000之间工资最高的前四个人姓名工资
select top 4 ename,sal from emp
where sal between 1500 and 3000
order by sal desc

-- 输出前四个人年薪和基本信息
 select top 4 *,sal*12 + ISNULL(comm,0) from emp

-- 查询姓名和工资，以工资降序排列
select  ename,sal from emp
order by sal desc
-- 查询各部门姓名和

-- 在员工表中查询所有名字以A开头的人名
select ename from emp  where ename like 'A%'
-- 在员工表中查询所有名字以A-F开头的人名
select ename from emp  where ename like '[A-F]%'
-- 在员工表中查询所有名字以A或F开头的人名
select ename from emp  where ename like '[A,F]%'
-- 在员工表中查询所有名字不以A-F开头的人名
select ename from emp  where ename like '[^A-F]%'

-- 搜索名字中带有%的内容
select name from student where name like '%\%%' escape '\'

-- 统计emp表中所有记录数
select count(*) from emp
-- 统计emp表中所有人名
select count(ename) from emp

select deptno, job,count(*) from emp 
where sal > 1000
group by deptno,job
having count(*) > 1

--1.求出每个员工的姓名 部门编号 薪水 和 薪水的等级
select S.grade
from emp "E" 
join salgrade "S" 
on E.sal<=S.hisal and E.sal>=S.losal 
--2.查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
select T.dno,S.grade,T.avg_sal
from(select D.deptno as "dno",AVG(sal) as "avg_sal"
from emp "E" join dept "D" on E.deptno = D.deptno 
group by D.deptno) "T" join salgrade "S"  on T.avg_sal >= S.losal and T.avg_sal<= S.hisal
--3.求出emp表中所有领导的姓名
select ename from emp
where empno in(select mgr from emp)
--4.求出平均薪水最高的部门的编号和部门的平均工资
select T.deptno,D.dname,T.avg_sal from 
(select deptno,AVG(sal) as "avg_sal"
from emp group by deptno) "T" join dept "D" on T.deptno = D.deptno
--5.把工资大于所有员工平均工资最低的前3个人的姓名 工资 部门编号输出
select top 3 E.ename,E.sal,E.deptno,D.dname 
from emp "E" join dept "D" on E.deptno = D.deptno
where E.sal > (select AVG(sal) from emp )
order by sal asc

--在不使用聚合函数的情况下，查询工资最高的人员信息
select * from emp "E"
where E.sal not in (select E1.sal from emp "E1" join emp "E2" on E1.sal < E2.sal)

-- 联合的使用
select emp.ename,emp.sal,E.ename "上级名称" 
from emp join emp "E"
on emp.mgr = E.empno
union select ename ,sal,'boss'
from emp where mgr is null

-- 分页查询每页显示4人信息（按照工资降序）
--1-3
select top 3 * from emp
order by sal desc
-- 4-6
select top 3 * from emp
where empno not in(select top 3 empno from emp
order by sal desc)
order by sal desc
-- 7-9
select top 3 * from emp
where empno not in(select top 6 empno from emp
order by sal desc)
order by sal desc
-- 10-12
select top 3 * from emp
where empno not in(select top 9 empno from emp
order by sal desc)
order by sal desc
-- 13-14
select top 3 * from emp
where empno not in(select top 12 empno from emp
order by sal desc)
order by sal desc

create table tableT
(
  empid int identity(1, 1),
  ename nvarchar(20) not null
)
-- 插入数据
insert into tableT values ('aaaa');
insert into tableT values ('bbbb');
insert into tableT values ('cccc');
insert into tableT values ('dddd'); 
 --删除empid为4的记录select* from emp
delete from tableT delete from emp where empid =4
--因为执行delet时empid为4，所以下一句插入时empid会从5开始
insert into tableT values('eeee') 
delete from tableT where empid = 5
dbcc checkident('tableT',reseed,3) --此行把emp表中identity字段的初始值重新设置为3
insert into tableT values('eeee') --此时插入记录时，empid为4,上一行已经把empid设置成了3
select* from emp

-- 视图操作
 --创建vs1视图
 CREATE VIEW vs1
  AS SELECT ename,deptno FROM emp
 -- 使用视图vs1查数据
  select sal from vs1