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 worker

exists谓词子查询

–任务一
–查询所有进行过岗前培训的职工号和职工姓名。

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
from customer
where csex='男'

–任务二
–对于工资信息表salary，统计该表的工资总和。

select wid,sdate,totalsalary,actualsalary 
from salary
union
select '小计',null,SUM(totalsalary),SUM(actualsalary)
from salary

注意：选择列表中的表达式数目必须相同。