Microsoft SQL Server 学习笔记

2017-11-15 10:48:40来源:https://masterxsec.github.io/2017/07/31/Microsoft-SQL-Server作者:Note's blog人点击

分享
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

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


微信扫一扫

第七城市微信公众平台