MSSQL 基本语法及实例操作语句（2）

在from字句中使用子查询
41 ,显示各个部门高于本部门平均工资的员工的信息
(1)先查出各个部门的平均工资 www.2cto.com
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 testId<8
表mytest在上述语句中已经创建好了，并且初始化好了数据
并且把testId设置为主键：ALTER TABLE test01 ADD primary key(testId)
www.2cto.com
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.2cto.com
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.2cto.com
----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 nums<10),
primary key(customerid, goodsid)
)

作者 qq395740774