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之间的员⼯的信息
--查询⼯资在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;
--显⽰所有⼈的上级的姓名和本⼈的名字
pName '⽼板', pName '员⼯' from emp boss, emp pNo = 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                                    --作为第⼀张表
sql容易学吗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
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
pName from emp, emp as boss pNo = pName from emp left join emp as boss pNo = pName from emp as boss right join emp pNo =
--完整性约束
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'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。