sql学习笔记(韩顺平)

--创建表
create table dept
(
deptId int primary key,--部门id
deptName nvarchar(50), --部门名称
deptLoc nvarchar(50)   --部门地址
)

--创建emp表
create table emp
(
empNo int primary key,
empName nvarchar(10),
job nvarchar(20),
mgr int,
hiredate datetime,
sal numeric(10, 2),
comm numeric(10, 2),
deptNo int foreign key references dept(deptId) --外键只能指向主键并且类型要一致
)

select * from dept
insert into dept (deptId, deptName, deptLoc) values(10,'ACCOUNTING','NEW YORK');
insert into dept (deptId, deptName, deptLoc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptId, deptName, deptLoc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptId, deptName, deptLoc) values (40, 'OPERATIONS', 'BOSTON');

select * from emp;
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, null, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empNo, empName, job, mgr, hiredate, sal, comm, deptNo) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);


--查询SMITH的薪水，工作和所在的部门
select sal, job, deptNo from emp where empName = 'SMITH'

--取消重复行
----统计一共有多少部门编号
select deptNo from emp;
select distinct deptNo from emp;

--显示每个员工的年工资
select empName, sal*16+isnull(comm, 0)*16 as '年工资' from emp order by '年工资' desc;

--显示工资高于3000的员工
select * from emp where sal > 3000

--查询在1982-1-1号之后入职的员工
select * from emp where hiredate > '1982-1-1'

--查询工资在2000到2500之间的员工的信息
select * from emp where sal between 2500 and 3000

--查询首字母为S的员工的姓名和工资
select empName, sal from emp where empName like 'S%'; --%代表任意个数的字符

--查询第三个字符为o的员工的信息
select * from emp where empName like '__o%';  --下划线表示单个字符

--查询empNo为7369,7499或者7521的员工的信息
select * from emp where empNo in (7369,7499,7521)

--显示没有上级的员工的信息
select empName from emp where mgr is null;

--查询工资高于500或者是岗位是MANAGER的，并且姓名的首字母是‘j’的员工的信息
select * from emp where (sal > 500 or job = 'MANAGER') and empName like 'j%';

--按照工资从低到高对员工的信息进行排名
select * from emp order by sal desc;
select * from emp order by hiredate asc;

--按照部门号升序而雇员的工资降序排列
select * from emp order by deptNo asc, sal desc;

--统计每个员工的年薪，从高到低排序显示
select empName,(sal+isnull(comm,0))*13 as '年薪' from emp order by '年薪' desc;

--显示所有的员工中工资最高的和工资最低的员工的姓名
select empName, sal from emp where sal = (select max(sal) from emp)
select * from emp
select empName,sal from emp where sal = (select min(sal) from emp);

--显示员工的平均工资和工资总和
select sum(sal) as '总工资', avg(sal) as '平均工资' from emp;

--显示工资高于平均工资的员工的姓名和该员工的工资，并且显示平均工资
select empName,sal,(select avg(sal) from emp) as '平均工资' from emp where sal > (select avg(sal) from emp);

--统计一共有多少员工
select count(*) as '员工数量' from emp;

--显示每个部门的平均工资和最高工资，并且按照平均工资递减排序显示
select job, avg(sal) as '平均工资', max(sal) as '最高工资' from emp group by job order by avg(sal) desc;

--显示每个部门的每个岗位的平均工资
select deptNo, job, avg(sal), min(sal) from emp group by deptNo, job order by deptNo

--显示平均工资低于2000的部门号和他的平均工资，并且按照从低到高排序显示
--having要对分组查询的结果进行筛选
select deptNo,avg(sal) from emp group by deptNo having avg(sal) > 2000 order by avg(sal)

--分组函数可以出现的地方：选择列表，having， order by中
--如果在select语句中同时出现group by，having，order by，那么他们的顺序是group by, having, order by
--在select的选择列表中如果有列，表达式和分组函数，则这些列和表达式必须出现在group by中

select * from emp;
select * from dept;

select * from emp, dept;--笛卡尔积

--显示完整的员工的信息，包裹员工的工作地址
select empNo, empName,job, mgr, hiredate, sal, comm, e.deptNo, deptName, deptLoc
from emp e, dept
where dept.deptId = e.deptNo

--显示部门号为10的部门的员工名，部门名称， 工资
select deptId, empName, sal from emp as e, dept as d where e.deptNo = d.deptId and deptId = 10
--显示部门号为10的部门的员工名，部门名称， 工资和平均工资
--？

--显示雇员的名字，工资，所在部门个名字，并按照部门排序
select empName,sal, deptName from emp e, dept d where e.deptNo = d.deptId order by d.deptName

----自连接
--显示FORD的上级
select empName from emp where empNo = (select mgr from emp where empName = 'FORD')
select * from emp;

--显示所有人的上级的姓名和本人的名字
select boss.empName '老板', emp.empName '员工' from emp boss, emp where boss.empNo = emp.mgr
select * from emp;


----自查询
--显示与SMITH在同一个部门的所有的员工
select empName from emp where deptNo = (select deptNo from emp where empName = 'SMITH')

--查询和部门10的工作相同的员工的名字，岗位，工资和部门号
select empName, job, sal, deptNo
from emp
where job in (select distinct job from emp where deptNo = 10) and deptNo != 10



----在from子句中使用自查询
--高于部门平均工资的员工的信息
select * from emp                                    --作为第一张表
select AVG(sal), deptNo from emp group by deptNo    --作为第二张表

--高于部门平均工资的员工的信息
select empName, sal, avgSal, emp.deptNo from emp,
(select avg(sal) as avgSal, deptNo from emp group by deptNo) as avgSalTable
where avgSalTable.deptNo = emp.deptNo
and sal > avgSal

--分页查询
select * from emp;
--显示第一个到第四个入职的员工的信息
select top 4 * from emp order by hiredate

--请显示第5个到第10个入职的雇员（按照时间排序）
select top 6 * from emp
where empNo not in
(select top 4 empNo from emp order by hiredate)
order by hiredate

select * from emp order by hiredate

--显示第11个到13个入职的员工的信息
select top 3 * from emp where empNo not in
(select top 10 empNo from emp order by hiredate)
order by hireDate

--显示薪水排在11到13位的员工的信息
select top 3 * from emp
where empNo not in
(select top 10 empNo from emp order by sal desc)
order by sal desc

select * from emp order by sal desc

--如何删除一张表中的重复记录
create table cat
(
catId int,
catName nvarchar(10)
)
insert into cat values(1, 'a')
insert into cat values(2, 'b')
select * from cat;
--删除一张表中的重复记录
select distinct * into #temp2 from cat
delete from cat
insert into cat select * from #temp2
drop table #temp2

----左外连接
--显示公司的每个员工姓名和他的上级的姓名，没有上级的显示上级为null
select emp.empName,boss.empName from emp, emp as boss where boss.empNo = emp.mgr
select emp.empName,boss.empName from emp left join emp as boss on boss.empNo = emp.mgr
select emp.empName,boss.empName from emp as boss right join emp on boss.empNo = emp.mgr

--完整性约束

create database aaa

create table goods
(
goodsId nvarchar(20) primary key,
goodsName nvarchar(50) not null,
unitPrice numeric(8, 2) check (unitPrice > 0),
category nvarchar(10) check (category in('生活类', '日用类')),
provider nvarchar(30)
)
create table customer
(
customerId nvarchar(30) primary key,
customerName nvarchar(20) not null,
customerAddress nvarchar(30),
email nvarchar(30) unique,
gender nchar(1) check (gender in ('男', '女')),
cardId varchar(20)
)

create table purchase
(
customerId nvarchar(30) foreign key references customer(customerId),
goodsId nvarchar(20) foreign key references goods(goodsId),
nums int check (nums > 0)
)


drop database aaa

--备份数据库
backup database aaa to disk='f:\\sqlPro\\aaa.bak'
--还原数据库
restore database aaa from disk='f:\\sqlPro\\aaa.bak'