MS SQL基本语法及实例操作

一：建表并初始化
============================
create database mf2011 --创建数据库

use mf2011 --使用数据库

create table dept --创建“部门”表
( www.jb51.net
deptno int primary key, --部门编号（主键）
dname nvarchar(30), --部门名称
loc nvarchar(30) --部门所在的地点
)
-----

create table emp --创建“员工”表
(
empno int primary key, --员工编号（主键）
ename nvarchar(30), --员工的姓名
job nvarchar(30), --员工的工作类型
mgr int, --员工的上级
hiredate datetime, --员工的入职时间（受聘时间）
sal numeric(10,2), --员工的每月工资
comm numeric(10,2), --员工的年终奖金
deptno int foreign key references dept(deptno) --创建一个外键指向部门表（描述该员工属于哪个部门）
)

--------
insert into dept values (10,'accounting','new york')
insert into dept values (20,'reasarch','dallas')
insert into dept values (30,'sales','chicago')
insert into dept values (40,'operations','boston')
-------

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7369,'michael','clerk',7902,'2010-1-12',675.23,300,20)

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7499,'allen','salesman',7698,'2009-1-23',1675.23,322.50,30)

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7521,'ward','salesman',7698,'2008-1-3',12675.99,399.50,30)

insert into emp (empno,ename,job,mgr,hiredate,comm,deptno) values
(7566,'jones','manager',7839,'2000-1-1',8675.99,20)

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7654,'martin','salesman',7698,'2007-12-31',1275.99,999.00,30)

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
(7782,'blake','manager',7839,'2007-12-20',1275.99,30)

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
(7788,'sccot','analyst',7566,'2003-1-22',1275.99,10)

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
(7781,'miller','opreator',7566,'2005-10-12',1275.99,40)
www.jb51.net
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
(7744,'adamc','opreator',7566,'2006-10-30',1244.0,40)

insert into emp (empno,ename,job,hiredate,sal,deptno) values
(7839,'king','president','2000-1-1',100244.0,10)

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values
(1999,'lxliog','opreator',7566,'2006-10-30',1244.0,40)
-----
select * from dept
select * from emp

--主键：唯一，非空
--外键：只能指向主键，与指向的主键数据类型必须一致

二：练习
============================
1，查询emp表所有列
select * from emp --刘君正

2，查询指定列（例如：姓名，工资两列）
select ename,sal from emp --郑朝阳

3，取消重复行（从emp表中查询部门号）
select distinct(deptno) from emp --徐绍峰,郝艳芳,刘辉,马东勤

4，从emp表中查询名字为lxliog员工的薪水，工作，所作部门
select sal,job,deptno from emp where 'lxliog'=ename --侯耀文,谭雪玲,黎小龙

5，统计总共有都少个部门(两种方式：从emp或从dept)
select count(deptno) from dept --诺布才仁
select count(distinct(deptno)) as 部门总数 from emp --诺布才仁

use mf2011
select * from dept;
select * from emp;
--6，显示每个雇员的年工资
select isnull(sal,0)*12+isnull(comm,0) as 年薪 from emp --扎西多杰

7，如何显示工资高于3000的员工信息
select * from emp where sal>3000 --邓文文
www.jb51.net
8，如何查找1982.1.1后入职的员工
select * from emp where hiredate>'1982-1-1'

9，如何显示工资在2000到2500之间的员工情况
select * from emp where sal between 1244 and 100244
select * from emp where sal>=2000 and sal=250000

10，如何显示首字母为S的员工姓名和工资(模糊查询)
select ename ,sal from emp where ename like 's%'

11，如何显示第三个字母为‘O'的所有员工的姓名和工资（模糊查询）
select ename 姓名,sal as 月薪 from emp where ename like '__R%';

12，如何显示empno为123,345,800...的员工情况(在where条件中使用in)
select * from emp where empno in(123,345,800,1999,7400,7744);

13，如何显示没有上级的雇员的情况（使用is null 的操作符）
select * from emp where mgr is null;

14,查询工资高于1000或是岗位为manager的雇员，
同时还要满足他们的姓名首字母为j（使用逻辑操作符）
select * from emp where (sal>1000 or job='manager')
and ename like 'j%';

16,如何按工资的从低到高的顺序显示雇员的信息
（使用order by字句asc， desc）
select * from emp order by sal desc;

17,如何按入职的先后顺序显示雇员的信息（使用order by字句asc， desc）
select * from emp order by hiredate desc;

18，按部门号升序而雇员的工资降序排列显示雇员信息
（使用order by字句asc，desc）
select * from emp order by deptno asc,sal desc;

19，统计每个人的年薪，并按从低到高的顺序排序(使用列的别名排序)
select isnull(sal,0)*12+isnull(comm,0) 年薪 from emp order by 年薪;

20,如何显示员工中最高工资和最低工资
select max(sal) as "max(sal)",min(sal) from emp;

21，如何显示最低工资的员工信息
select * from emp where sal = (select min(sal) from emp);
www.jb51.net
22，显示所有员工的平均工资和工资总和
select avg(sal) as "avg(sal)" , sum(sal) as "sum(sal)" from emp;

23，把高于平均工资的雇员的名字和他的工资显示出来
select ename,sal from emp where sal>(select avg(sal) from emp);

24，计算共有多少名员工
select count(ename) from emp;

25，如何显示每个部门的平均工资和最高工资
select avg(sal) as "avg",max(sal) as "max",deptno
from emp group by deptno

26，如何显示每个部门的高于总平均工资的平均工资和最高工资
select avg(sal) as "avg",max(sal) as "max",deptno
from emp where sal>(select avg(sal) from emp) group by deptno

27显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),deptno,job from emp group by deptno,job
order by deptno

28，显示平均工资低于2000的部门号和它的平均工资
（having往往和group by结合使用，可以对分组查询结果进行筛选）
select avg(sal),deptno from emp group by deptno having avg(sal)2000

29，使用企业管理器来操作数据库的分离与附加
见操作步骤

30，使用企业管理器来操作数据库的备份与恢复操作
见操作步骤

31，使用查询分析器器来操作数据库的备份与恢复操作
（1）备份数据库
backup database mf2011 to disk='f:/lxliog.bak';
（2）删除数据库
drop database mf2011;
（3）恢复数据库
restore database mf2011 from disk='f:/lxliog.bak';

复杂查询（多表查询）
32,显示sales部门的位置和其员工的姓名
select d.loc,e.ename from emp e,dept d where d.dname='sales' and d.deptno=e.deptno

33,显示雇员名字，雇员工资及所在部门的名字(当字段有歧义的时候要用别名)
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno
www.jb51.net
34，显示部门号为10的部门名称，及该部门的员工名字和工资
select d.dname, e.ename, e.sal from emp e,dept d where d.deptno=10 and d.deptno=e.deptno

35，显示雇员名字，雇员工资及所在部门的名字，并按部门名称排序
select e.ename, e.sal, d.dname from emp e,dept d where e.deptno=d.deptno order by d.dname

自连接：在同一张表的连接查询
36,显示某个员工的上级领导的姓名（比如：smith）
（1）select mgr from emp where ename='adamc'
（2）select ename from emp where empno=(select mgr from emp where ename='adamc')

37,显示公司每个员工和他上级的名字
select worker.ename, boss.ename from emp worker, emp boss where worker.mgr=boss.empno

子查询：嵌入在其它sql语句中的select语句，也叫嵌套查询。
单行子查询：只返回一行数据的子查询语句。
38,显示与jones同一部门的所有员工
(1)select deptno from emp where ename='jones'
(2)select * from emp where deptno=(select deptno from emp where ename='jones')

多行子查询：返回多行数据的子查询。
39,查询和部门号为10的工作相同的雇员的名字、岗位、工资、部门号。(注意要使用in，不能用=)
(1)select distinct job from emp where deptno=20
(2)select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20);

40，在上面查询结果的基础上排除20部门的员工
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20) and
deptno not in (20);
或select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20) and deptno >20;

在from字句中使用子查询
41 ,显示各个部门高于本部门平均工资的员工的信息
(1)先查出各个部门的平均工资 www.jb51.net
select avg(sal),deptno from emp group by deptno
(2)把上面的表当成一个临时表来对待
select e.ename,e.sal,tem.myavg,e.deptno
from emp e,(select avg(sal) myavg,deptno from emp group by deptno) tem
where e.deptno=tem.deptno and e.sal>tem.myavg

注意：（1）当在from字句中使用子查询时，该子查询会被当做一个临时表来对待，
（2）当在from字句中使用子查询时，必须给子查询指定一个别名

分页查询：按雇员的hiredate属性升序取出第5条到第10条记录
42，显示第5条到第10条记录
（1）显示第1到第4条记录
select top 4 empno from emp order by hiredate
（2）显示后6条记录（第5条到第10条记录）
select top 6 * from emp where empno
not in(select top 4 empno from emp order by hiredate)
order by hiredate;

43,显示第11个到第13个入职的人的信息（写法同上）

44，显示第5到9的人的信息，按薪水的降序排列（写法类似）
select top 5 * from emp where empno not in
(select top 4 empno from emp order by sal desc)
order by sal desc;
45，同一张表中的数据复制
（1）创建一张临时表：identity(1,1)表示该testId字段自增，从1开始每次+1
create table test(
testId int primary key identity(1,1),
testName varchar(30),
testPass varchar(30)
)
（2）插入一条数据
insert into test(testName, testPass) values('zhangsan','123456');
（3）复制数据
insert into test(testName, testPass) (select testName,testPass from test);

46，查询testId为第10000-10009的数据，看看性能。
select top 10 * from test where testId not in
(select top 9999 testId from test order by testId)
order by testId

用查询结果创建一张新表（一种快捷的建表方法）
47，语法：select *(这里可以选择字段) into 另一张表面 from 表
select testName,testPass into mytest from test where testId8
表mytest在上述语句中已经创建好了，并且初始化好了数据
并且把testId设置为主键：ALTER TABLE test01 ADD primary key(testId)
www.jb51.net
48，删除一张表中的重复数据
（1）create table cat(
catId int,
catName varchar(40)
)
（2）insert into cat values(1,'aa'); //重复执行几次
insert into cat values(2,'bb'); //重复执行几次
（3）select distinct * into #temp from cat;//把cat的记录distinct后的结果，插入到临时表#temp中
delete from cat;//把cat表的记录清空
insert into cat select * from #temp;//把#temp表的数据（没有重复的数据）插入到cat表中
drop table #temp;//删除表#temp3

左外连接和右外连接
左外连接：左边表的查询数据全部显示，右边的表中如果没有匹配的数据则用null填充
右外连接：右边表的查询数据全部显示，左边的表中如果没有匹配的数据则用null填充
49，显示emp表中所有雇员的及其上级的名字(看看区别)
（1）左外连接：select e.ename 雇员名字,b.ename 上级名字
from emp e left join emp b on e.mgr=b.empno;
（2）右外连接：select e.ename 雇员名字,b.ename 上级名字
from emp e right join emp b on e.mgr=b.empno;

常见约束：
(1)not null, 非空
(2)unique, 唯一，允许出现一个null
(3)primary key, 主键，唯一，非空
(4)foreign key, 外键,定义主表和从表的关联关系
(5)check，检查，强制数据必须满足定义的条件,例如：sal int check(sal>=2000 and sal=3000)
(6)default, 默认值，用于数据的完整性，例如：birthday datetime default getdate(),

50，复合主键只能用表级定义
例如：create table cat(
catId int,
catName varchar(40)，
catAge int,
primary key(catId, catName)
)
www.jb51.net
51，商品售货系统表设计案例：现有一个商店数据库，记录客户及其购物情况，由下面三个表组成：商品（goods），客户（customer），购买（purchase）
商品goods(商品号goodsId, 商品名称goodsName, 单价unitPrice, 商品类别category, 供应商provider);
客户customer(客户号customerId, 姓名name, 住址address, 电邮email, 性别sex, 身份证cardId);
购买purchase(客户号customerId, 商品号 goodsId, 购买数量nums);
（1）建表，在定义中要求申明：
ü 每个表的主键和外键；
ü 客户的姓名不能为空；
ü 单价必须大于0，购买数量必须在1到10之间
ü 电邮不能够重复
ü 客户的性别必须是：男或女，默认是男
ü 商品的类别是：食物，日用品

----goods表
Create table goods(
goodsid nvarchar(50) primary key,
goodsnamd nvarchar(80) not null,
unitPrice numeric(10,2) check (unitPrice>0)
category nvarchar(3) check (category in (‘食物','日用品')),
provider nvarchar(50)
)
----customer
Create table customer(
Customerid nvarchar(50) primary key,
Cusname nvarchar(50) not null,
Address nvarchar(100),
Email nvarchar(50) unique,
Sex nchar(1) check(sex in(‘男','女')) default ‘男',
Cardid nvarchar(18)
) www.jb51.net
----purchase
Create table purchase(
Customerid nvarchar(50) foreign key references customer(Customerid),
goodsid nvarchar(50) foreign key references goods(goodsid),
nums int check(nums>0 and nums10),
primary key(customerid, goodsid)
)

作者 qq395740774