--创建dept表
create table dept
(deptno int primary key,
dname nvarchar(50),
loc nvarchar(50)
)
select * from dept
--创建emp表
create table emp
(empno int primary key,
ename nvarchar(30),
job nvarchar(30),
mgr int,
hiredate datetime,
sal numeric(10,2),
comn numeric(10,2),
deptno int foreign key references dept(deptno))
--外键只能指向references主键,并且数据类型要一致
--查询语句
--1查询所有列 select *from 表名 where 条件
select *from emp where sal>2500
--2 查询指定列 select 字段1 ,字段2 from 表名where 条件
--查询smith的薪水 工作和员工标号
select ename ,sal, job, empno from emp where ename='smith'
select sal,job,deptno from emp where ename='smith'
--3如何取消重复行
--统计共有多少个部门
select distinct deptno from emp
--4如何使用like语句查询
--显示第一个字符为s的姓名和工资
select ename, sal from emp where ename like 's%'
--显示第三个字符为o的姓名和工资
select ename, sal from emp where ename like '__o%'
--5在where条件中使用in
--显示empno为123,234,800...的雇员情况
select * from emp where empno in(123,234,800)
--6使用is null
--显示没有上级的员工情况
select *from emp where mgr is null
--显示公司大于3500或者职位是经理 同时满足名字手写为j的员工
select *from emp where (sal>3500 or job='manager') and ename like'j%'
--7 order by 排序 (默认从低到高asc 从高到低desc)
--工资从高到低
select *from emp order by sal desc
--入职先后
select *from emp order by hiredate
--按照部门号升序而工资降序排列
select *from emp order by deptno ,sal desc
--8使用别名排序
select ename , sal*13+isnull(comn,0) 年薪 from emp order by '年薪' desc
select ename ,sal*13+isnull(comn,0) 年薪 from emp order by '年薪'
--9复杂查询
--1数据分组(max,min, avg,sum,count)
--最低工资和最高工资
select ename,sal from emp where sal=(select min(sal) from emp)
select ename,sal from emp where sal=(select max(sal) from emp)
--平均工资和工资总和
select avg(sal)平均工资,sum(sal)工资总和 from emp
--把高于平均工资的员工名字和工资显示出来
select ename,sal from emp where sal>(select avg(sal)from emp)
--统计员工个数
select count(*)员工个数 from emp
select count(*) from emp
--2
--显示每个部门的平均工资和最高工资
select deptno,avg(sal)平均工资, max(sal)最高工资 from emp group by deptno
select deptno, avg(sal),max(sal)from emp group by deptno
--显示每个部门每个岗位的平均工资和最低工资
select deptno,job,avg(sal)平均工资, min(sal)最高工资 from emp group by deptno,job order by deptno,job
select deptno, job,avg(sal),min(sal)from emp group by deptno,job order by deptno,job
--多表查询
--1不同表
--查询所有部门标号为10的员工信息
select *from emp ,dept where dept.deptno=10and emp.deptno=dept.deptno
--查询所有部门sales的员工信息
select *from emp ,dept where dname='sales'and emp.deptno=dept.deptno
--查询所有部门标号为10的部门名称,员工名称,工资
select d.dname,e.ename,sal from emp e,dept d where e.deptno=10 and e.deptno=d.deptno
--显示雇员名,雇员工资,及所在部门的名字 ,并按部门排序
select e.ename,sal,d.dname from emp e ,dept d where e.deptno=d.deptno order by d.deptno
--2同一张表的自连接(看做两张相同的表,名称不同)
--显示某个员工的名字和他上级领导的姓名,比如 “Ford”的上级
select w.ename 雇员名,l.ename 领导 from emp w ,emp l where w.mgr=l.empno order by l.ename
--子查询
--1单行子查询
--如何显示与smith同一个部门的所有员工
select *from emp where deptno=(select deptno from emp where ename='smith')
--2多行子查询
--如何显示与部门10的工作相同的员工名字,岗位,工资,部门号
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10 )and deptno=10
--3在from子句中使用子查询
--显示高于部门平均工资的员工名称, 薪水, 部门平均工资,部门标号
--分析
--首先得出每个部门的员工平均工资
select deptno ,avg(sal)from emp group by deptno
--然后被上面结果作为一个临时表
select tem.deptno,ename,sal,avgm from emp,( select deptno ,avg(sal) avgm from emp group by deptno) tem where emp.sal> tem.avgm
--4分页查询
--显示第五个到第十个入职的雇员,按时间的先后顺序
select top 5 * from emp where emp.empno not in(select top 5 empno from emp order by hiredate asc) order by hiredate asc
--*快速往数据库添加数据
create table test (
id int identity(1,1) not null,
name varchar(20) not null,
)
insert into test (name) values ('tom')
--接着循环以下一句语句数次
insert into test(name) select name from test
--分页,查询第60000-60010行记录
select top 10 *from test where id not in(select top 60000 id from test)
--5如何删除一张表中的重复记录
-- 首先创建一张有多条重复记录的表
create table test2(
id int,
name varchar(20),
)
--数次重复以下一句语句
insert into test2 values(1,'tom')
--删除方法
select distinct * into temp from test2
delete from test2
insert into test2 select * from temp
drop table temp
--6左外联,右外联
--思考题:显示公司每个员工和他的上级的名字
--内连接:
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno
--思考题:显示公司每个员工和他的上级名字,要求没有上级名字的人也要显示出来
select e.ename,w.ename from emp e left join emp w on e.mgr=w.empno
--左外联:指左边的表记录全部显示,如果没有匹配就用null填写
select e.ename,w.ename from emp w right join emp e on w.empno=e.mgr
--右外联:指右边的表记录全部显示,如果没有匹配就用null填写