Microsoft SQL Server 学习笔记
创建数据库
create database factory数据库名为factory。
使用数据库
use factory
使用或者切换到数据库。
删除数据库
drop database factory删除factory数据库。
创建表
create table worker
wid char(3) primary key, -- id 主键
wname varchar(10) not null, -- 名字 非空
wsex char(2) check(wsex in ('男'，'女')), -- 性别 只能是‘男’或者‘女’
wbirthdate date, -- 生日
wparty char(2), -- 政治面貌
wjobdate date, -- 参加工作时间
depid char(1) -- 部门
)创建worker数据表，保存员工的信息。
MSSQL中的数据类型
字符类型：char(n)，varchar(n)，text，image
整型类型：int(4字节)，smallint(2字节)，tinyint(1字节)
浮点类型：float(8字节)，real(4字节)，decimal(精度28位)
货币类型：money(8字节)，smallmoney(4字节)
日期时间类型：date(年月日)，datetime(年月日时分秒毫秒)，smalldatetime（年月日时分秒）
浮点类型：decimal(a,b) a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。
数据库中数据的移动
先分离数据库，然后移动.mdf和.ldf文件即可，两个文件需要同时移动，要不然在附加数据库的时候会报错。
数据库中的常用术语
关系：一个关系对应一张二维表，二维表的表名即为关系名。
关系模式：对关系表结构的描述。一般表示为“关系名（字段名1，字段名2，…，字段名n）”。
记录：二维表中的一行称为关系的一条记录，或称为元组、行。
字段：二维表中的列称为关系的字段，或称为属性、列。
主码：关系中的某个字段或字段组，能唯一地标识一条记录，又称为主键。
表与表之间的关系
外码(foreign key)：外码指的是这样的字段（或字段组），它在本表中不是主码，而在其他的表中是主码，外码又称为外键。
参照完整性规则：外码的取值要么为空，要么必须来自于主码表中所存在的值。
查询
简单查询
select <目标列1 [as 列名1]>[，<目标列2 [as 列名2]>…] from <表名>；
select dname as 部门名,dmaster as 部门经理 from depart;
条件查询
–条件查询
–任务一查询salary（工资）表中实际工资(actualsalary)大于3000的职工号和实际工资。
select wid,actualsalary from salary where actualsalary>=3000
–任务二查询salary（工资）表中实际工资(actualsalary)在2000和3000之间的
–职工号和实际工资。
select wid,actualsalary from salary where actualsalary between 2000 and 3000
select wid,actualsalary from salary where actualsalary>=2000 and actualsalary<=3000
–任务三查询worker(职工)表中在部门“1”或“2”工作的职工的职工号、姓名、部门号。
select wid,wname,depid from worker where depid in ('1','2')
select wid,wname,depid from worker where depid='1' or depid='2'
–任务四查询worker(职工)表中所有姓“孙”职工的职工号、姓名和性别;
–查询worker(职工)表中所有姓名第二个字不是“华”的职工号、姓名和性别。
select wid,wname,wsex from worker where wname like '孙%'
select wid,wname,wsex from worker where wname not like '_华%'
–任务五查询depart（部门）表中部门经理为空的部门信息。
select * from depart where dmaster is null
–任务六查询worker（职工）表中男职工是党员的职工号和姓名。
select wid,wname from worker where wsex='男' and wparty='是'
查询条件中可以使用的条件谓词
聚集查询
聚集函数
平均值函数 avg()
计数函数 count()
最大值函数 max()
最小值函数 min()
求和函数 sum()
–任务一：查询salary（工资）表中日期为‘2011-01-04’的总工资(totalsalary)的平均工资。
select * from salary
select AVG(totalsalary) as '2011-01-04平均工资' from salary where sdate='2011-01-04'
–任务二：查询职工的总数；
–查询在salary（工资）表中发过工资的职工人数，一个职工只计数一次。
select * from worker
select * from salary
select COUNT(*) as 职工总数 from worker
select COUNT(distinct wid) as 职工人数 from salary
–任务三：查询salary（工资）表中最低的实发工资。
select MIN(actualsalary) as 最低工资 from salary
–任务四：查询salary（工资）表中最高的实际工资。
select max(actualsalary) as 最高工资 from salary
–任务五：查询salary（工资）表中‘2011-01-04’工资的总额。
select SUM(actualsalary) as 工资总额 from salary
top和distinct
–任务一：查询worker表中前两项职工的信息。
select top 2 * from worker
–任务二：查询worker表中女职工所出现的部门号，相同的只出现一次
select distinct depid from worker where wsex='女'
附加子句查询
order by子句
–任务一
–查询职工的职工号、职工姓名、出生日期、部门号，查询结果按照出生日期从早到晚排序。
select wid,wname,wbirthdate,depid from worker order by wbirthdate asc
–任务二
–查询职工的职工号、职工姓名、出生日期、部门号，查询结果按照出生日期从晚到早排序。
select wid,wname,wbirthdate,depid from worker order by wbirthdate desc
–任务三
–查询职工的职工号、职工姓名、出生日期、部门号，查询结果按照部门号从大到小排序，同一部门的按照出生日期从早到晚排序。
select wid,wname,wbirthdate,depid from worker order by depid desc,wbirthdate
group by子句
–任务一
–分别统计男职工和女职工的人数。
select wsex as 性别,COUNT(*) as 职工人数 from worker group by wsex
–任务二
–分别统计每个日期的应发工资(totalsalary)总和。
select sdate as 发工资日期,SUM(totalsalary) from salary group by sdate
having子句
–任务一
–分别统计每位员工的应发工资(totalsalary)总和，并且只显示工资总和在5000元以上的信息。
select wid as 职工号,SUM(totalsalary) as 工资总和 from salary group by wid having SUM(totalsalary)>=5000
–任务二
–统计worker表中各部门党员的人数，并且显示党员人数在2个人以上的相关信息。
select depid as 部门号,COUNT(*) as 党员人数 from worker where wparty='是' group by depid having COUNT(*)>=1
注意：聚合函数不应该出现在where子句中，除非该聚合函数位于having子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。
into子句
–任务一
–查询男职工的基本信息，并且存入临时表#worker1中。
select * into #worker from worker where wsex='男'
select * from #worker
临时表只在当前连接中有效，断开数据库的连接，重新连接数据库，临时表丢失。
子查询
in谓词子查询
–任务一
–查询与职工号为“001”的职工一起进行过企业相关培训的职工号。
select wid
from study
where wid<>'001' and study_id in
(select study_id
from study
where wid='001')
–任务二
–查询与职工号为“001”的职工一起进行过企业相关培训的职工姓名。
select wname
from worker
where wid in
(select wid
from study
where wid<>'001' and study_id in
(select study_id
from study
where wid='001') )
注意：’<>’表示’不等于’。
比较运算符子查询
–任务一
–查询2011年1月的实发工资小于该月平均实发工资的职工号。
select wid
from salary
where YEAR(sdate)=2011 and MONTH(sdate)=1 and actualsalary<
(select AVG(actualsalary)
from salary
where YEAR(sdate)=2011 and MONTH(sdate)=1)
–任务二
–查询比部门号为“1”的职工年龄都小的职工姓名和出生年月。
select wname,wbirthdate
from worker
where wbirthdate>all
(select wbirthdate
from worker
where depid='1')
–任务三
–显示最高工资（应发工资）的职工所在的部门名。
select dname
from depart
where did=
(select depid
from worker
where wid=
(select wid
from salary
where totalsalary=
(select MAX(totalsalary)
from salary)))
–等价的多表连接查询
select dname
from depart inner join worker on worker.depid=depart.did inner join salary on worker.wid=salary.wid
where totalsalary=
(select MAX(totalsalary)
from salary)
–in谓词子查询任务二
–查询与职工号为“001”的职工一起进行过企业相关培训的职工姓名。
select wname
from worker inner join study on worker.wid=study.wid
where worker.wid<>'001' and study_id in
(select study_id
from study
where wid='001')
使用子查询代替表达式
–任务一
–显示所有职工的职工号，姓名和平均工资。
select worker.wid,wname,AVG(totalsalary) as avgtoal
from worker inner join salary on worker.wid=salary.wid
group by worker.wid,wname
–任务二
–使用子查询代替表达式
select worker.wid,wname,AVG(totalsalary) as avgtotal
from worker inner join salary on worker.wid=salary.wid
group by worker.wid,wname
select wid,wname,
(select AVG(totalsalary) from salary where worker.wid=salary.wid) as avgtotal
from workerexists谓词子查询
–任务一
–查询所有进行过岗前培训的职工号和职工姓名。
select wid,wname
from worker
where exists
select *
from study
where worker.wid=study.wid and study_name='岗前培训'
)
–任务二
–查询所有未进行过岗前培训的职工号和职工姓名。
select wid,wname
from worker
where not exists
select *
from study
where worker.wid=study.wid and study_name='岗前培训'
)
union组合结果集
–任务一
–增加一个customer客户表，然后查询所有男职工和男客户的信息。
select wid as id,wname,wsex,wbirthdate
from worker
where wsex='男'
union
select cid,cname,csex,cbirthdate