SQL Server用法总结

SQL Server 用法总结

(一). 创建操作

1.创建数据库：
create database baiyunfei;

2.创建表：
create table stu_info --创建带约束的表
(
stu_id int identity(1,2) primary key foreign key references stu_info(stu_id), --表识列，主键约束，外键约束
stu_xuehao int default' ' , --缺省约束
stu_chengji int default' ' check(stu_chengji>=0 and stu_chengji<=100),--缺省约束，检查约束
stu_banji char(20) default 'yiban', --缺省约束
stu_name char(20) default' ', --缺省约束
stu_jiguan char(20) null unique nonclustered --唯一约束
)

create table course --建表时创建级联更新
(
course_id int primary key,
stu_id int foreign key references stu_info(stu_id) on update cascade,
course_name char(20)
)

3.创建默认值：
create default default_name (默认值名) as
constant_expression (默认值，是一个常数表达式)

sp_helptext default_name(默认值名) --查看默认值的脚本信息
sp_bindefault default_name(默认值名),'object_name'(要绑定的列名或用户自定义数据类型,若指定的是表中的某列，格式'table_name.column')；--绑定默认值
sp_unbindefault 'object_name'(格式：'table_name.column’) --解除绑定
例子：
use jwgl
go
create default profession_default as '讲师'
go
sp_bindefault profession_default,'teacher.profession'
go
sp_unbindefault 'teacher.profession'
drop default profession_default

4.创建规则:
create rule rule_name(规则名) as condition_expression
sp_helptext rule_name --查看规则脚本信息
sp_bindrule rule_name,object_name(指定要与该规则相绑定的列名或用户自定义数据类型名，如果指定是列名，格式'table_name.column') --绑定规则
sp_unbindrule rule_name,obj_name --解除规则
drop rule rule_name --删除规则

5.创建标识列：
alter table stu_info
add stu_id int identity(seed,increment) --seed 标识列的种子值，increment 步长值

6.创建视图：
create view view_stu_info with encryption(对视图定义进行加密)
as
select stu_id ,stu_name from stu_info
with check option (迫使通过试图执行的所有数据修改语句必须符合视图定义中设置的条件)

7.创建索引：
create unique(惟一索引)/ clustered(聚簇索引)/nonclustered(非聚簇索引) index index_name(索引名)
on table_name(表名)(column_name (字段列表) ) --多字段为联合索引
例子：
(创建惟一索引)
create unique clustered index book_id_index on book(book_id asc) with fillfactor=50(填充英子)
(创建联合索引)
create nonclustered index student_course_index on student_course(student_id asc ,course_id desc) with fillfactor=50

8.创建存储过程：
create procedure/proc proc_name(存储过程名) @parametr_name(参数) type(类型) =[default](赋初值) [output](输出参数)[with encryption](对存储过程加密)[with recomile](允许重

编译存储过程)
as
sql_statement

例子：
create procedure sp_stu_info --创建存储过程
@stu_id int=null,
@stu_name char(30)=null, --给参数付初始值
@stu_birth datetime=null,
@stu_age int=null,
@stu_banji char(30)=null,
@stu_jiguan char(30)=null,
@stu_tel char(20)=null,
@stu_scores int=null

as
if @stu_id is null or
@stu_name is null or
@stu_birth is null or
@stu_banji is null or
@stu_scores is null
begin
print '请重新输入该学生信息'
print '你必须提供学生的学号，姓名，出生日期，班级，分数'
print '联系电话，年龄，籍贯可以为空'
return
end

declare @bitbanji int
if @stu_banji='专修一班'
set @bitbanji=1
else if @stu_banji='专修二班'
set @bitbanji=2
else if @stu_banji='专修三班'
set @bitbanji=3
else
set @bitbanji=4

begin transaction --开始事务
insert into stu_info
(stu_id,stu_name,stu_birth,stu_age,stu_banji,stu_jiguan,stu_tel,stu_scores) values
(@stu_id,@stu_name,@stu_birth,@stu_age,@bitbanji,@stu_jiguan,@stu_tel,@stu_scores)

if @@error<>0
begin
rollback tran --撤消事务
return
end
commit transaction --结束事务

exec sp_stu_info 13,'wenhua','1985-12-23',30,'专修一班','hehe','123456',86 with recompile --重新编译存储过程并执行语句.
exec sp_stu_info 13,'wenhua','1985-12-23',30,'专修一班','hehe','123456',86 --使用存储过.
exec sp_stu_info 'wenhua','1985-12-23' output --带输出参数的存储过程在执行时应带上output参数.
sp_helptext proc_name --查看存储过程脚本信息

9.创建触发器:
create trigger trigger_name on table_name [with encryption] for update,insert,delete
as
sql_statement

例子：

if exists(select name from sysobjects where type='TR' and name='updated') --判断是否存在同名的触发器，如果存在则删除掉该触发器
drop trigger updated

create trigger updated --触发器结合游标实现自动逐条更新
on salary with encryption
for update
as
declare c cursor for select emp_id from salary
declare @emp_id char(5)
open c
fetch next from c into @emp_id
update salary set work_age=(select'工龄'=round(datediff(mm, sdate,(getdate()))/12,2) from salary where emp_id=@emp_id)where emp_id=@emp_id
while (@@fetch_status=0)
begin
fetch next from c into @emp_id
update salary set work_age=(select'工龄'=round(datediff(mm, sdate,(getdate()))/12,2) from salary where emp_id=@emp_id)where emp_id=@emp_id
end
close c
deallocate c

10.创建游标
declare cursor_name(游标名) [scroll] (滚动游标) cursor for select_statement
for read only(只读)/update of column_name_list(可更新的字段列表)

open cursor_name -- 打开游标
fetch next(取下一行数据)/prior(取前一行数据)/first(取第一行数据)/last(取最后一条数据)/absolute(按绝对位置取数据)/relative(按相对位置取数据)
from cursor_name into fetch_target_list(变量列表)
close cursor_n

ame --关闭游标
deallocate cursor cursor_name --释放游标

两个全局变量@@fetch_status,@@rowcount
@@fetch_status=0 表示成功完成fetch语句
@@fetch_status=-1 表示fetch语句执行有错误，或者当前游标位置已在结果集中的最后一行，结果集中不再有数据
@@fetch_status=-2 表示提取的行不存在
@@rowcount 保存着游标结果集中提取的行数,如果结果集中所有行都被提取，则@@rowcount的值就是该结果集的总行数,关闭游标时，该@@rowcount变量被删除.


11.使用事务(事务的优点，可以对数据进行回滚；缺点，建立临时回滚点，耗时长，效率低下，大数据量操作不适宜)
begin tran/transaction
sql_statement(语句序列)
save tran save_point (设置保存点)
sql_statement(语句序列)
if @@error<>0 --@@error为0表示未出错.@@error<>0表示出错.
rollback tran save_point (回滚到事务保存点)
commit tran (提交事务)

SQL Server中有5种对象可以回滚：create ,drop ,insert ,delete,update;

事务中不能包含的语句：
create database
alter database
backup log
drop database
reconfigure
restore log
update statistics

12.数据库还原与备份
backup database +要备份的数据库名 to disk='e:\+备份后的保存名 --数据库备份
restore database +还原后的数据库名 from disk='e:\+要还原的数据库名' --数据库还原


(二).修改操作：
alter table stu_info --加字段 alter table stu_info --修改字段属性
add stu_type char(20) alter column stu_type int

alter table stu_info --删除字段
drop column stu_type

对表重新命名：sp_rename 'stu_info','stu_sc_info'; 或者：sp_rename stu_info,stu_sc_info;
对字段重新命名: sp_rename 'stu_info.stu_chengji','stu_scroes'; --先找到表名，在命名列
对数据库重新命名：sp_renamedb 'baiyunfei','byf'; 或者:sp_renamedb baiyunfei,byf;
对索引重新命名： sp_rename 'stu_info.stu_id_index','stu_info.stu_index';
对视图重新命名：sp_rename old_view_name,new_view_name
修改存储过程： alter procedure/proc proc_name .......


添加主键约束: alter table stu_info
add constraint pk_id primary key(stu_id)
添加外键约束: alter table course
add constraint fk_id foreign key(stu_id) references stu_info(stu_id) on delete cascade on update cascade; --实现级联更新级联删除
添加缺省约束： alter table couse
add constraint default_name default '语文' for course_name;
添加检查约束: alter table stu_info
add constraint check_scroes check(stu_chengji>=0 and stu_chengji<=100);
添加唯一约束: alter table stu_info
add constraint unique_xuehao unique nonclustered(非聚簇索引)/clustered(聚簇索引) (stu_xuehao)

(三) 删除操作：

删除表： drop table stu_info;
删除数据库: drop database byf;
删除约束: alter table stu_info
drop pk_id;
删除视图: drop view hehe(视图名);
删除索引： drop index table.index(索引名)
删除存储过程: drop procedure/proc stu_pro(存储过程名)
删除触发器: drop trigger updated(触发器名)
清空表数据： truncate table +表名 (完全清空，不具有事务特性，不能回滚)

(四) 常用函数
(转换函数)
select convert(char(20),stu_id) from stu_info
select cast(stu_id as char(20) from stu_info
select cast(getdate() as datetime)
(数学函数)
select abs(-2) --求绝对值
select rand(2) --返回0到1之间的随机float值
select round(numeric_expression,length) --length为正，numeric_expression的小数按length位四舍五入，length为负，对numeric_expression从小数点左边length位四舍五入。
select PI() --返回圆周率 3.14159265
(字符串函数)
select ASCII('A') --返回字符表达式第一个字符的ASCII值
select char(97) --将int ASCII代码转换为字符
select ltrim(' ddkddk') --去掉左空格
select rtrim('ddddd ') --去掉右空格
select len('dhdhd') --统计字符个数
selectt datalength('中国') --统计字节数
select substring('adsfsfs',3,3) --从第三位开始取3个字符
select replace('中国人哈哈中国','中国','湖南') --用湖南替换所有在第一个参数中出现的中国
select space(' hdh dhhd dd') --返回指定个数的空格
select left('hahahh',3) -- 从左边开始返回3个字符
select right('ahahha',3) --从右边开始返回3个字符
select stuff('hahah',2,3,'hehehe') --从第一个表达式的第二个字符开始删除后面的三个字符替换成'hehehe'.
(日期函数)
select year(getdate()) --返回日期中的年份，返回类型为int
select month(getdate()) --返回日期中的月份，返回类型为int
select day(getdate()) --返回日期中的日数，返回类型为int
select getdate() --返回系统当前时间
select dateadd(mm,1,getdate()) --返回当前时间往后加一个月的时间
select datediff(mm,getdate(),dateadd(yy,3,getdate())) --返回当前时间加3年后与当前时间之间相隔的月份数
select datename(mm,getdate()) --返回当前时间对应的月份数，返回类型为字符型
select datepart(mm,getdate()) --返回当前时间对应的月份数，返回类型为整数值
(系统函数)
select db_id() --返回当前数据库标识号
select db_name --返回当前数据库名称
select current_user --返回当前用户
select isnull(emp_name,'白云飞') from emp;--查询emp表中emp_name字段是否有空值行，如果有，则将该行对应的emp_name替换为白云飞。 update emp set emp_name=isnull(emp_name,'白云飞') from emp ; --判断emp表中emp_name字段是否有空值行，如果有，则将该行对

应的emp_name更新为白云飞。
select isnumeric('dhdhdh') --判断表达式是否为有效的数字类型，为真返回1，为假返回0。
select isdate(sdate) from work where emp_id='00001' --判断员工编号为00001的上班日期是否为有效的日期类型，如果为真，返回1，为假，返回0。
(集合函数)
avg() --返回表达式的平均值
count(all/distinct expression) --统计行数(全部或者无重复)
count(*) --统计所有的行数
max() --求最大值
min() --求最小值
sum() --求总和

(五)其它知识点

1.模糊查询通配符含义：
- 匹配任一字符
% 匹配任意个字符
[] 匹配方括号中任一字符
[^ ] 匹配不在方括号中的任一字符
[ - ] 匹配一定范围的字符
# 匹配任一数字，例如：A#匹配A1到A9


2.运算符
算术运算符： + 加法
- 减法
* 乘法
/ 除法
% 取余
字符串连接符： select 'abc'+'dec' --(+)
比较运算符： = > < >= <= <>(不等于) () 优先级控制符
逻辑运算符： 按优先级排序为：not(取反),and ,or;


3.常用系统存储过程
sp_help object_name --查看指定数据库对象的信息
sp_helpdb database_name --查看指定数据库的信息
sp_who 'login' --查看指定登录用户的信息

4.常用系统表：
syslogins master 含有每一个连接到SQL Server的登录帐号信息
sysmessages master 由SQL Server返回的系统错误或警告信息
sysdatabases master 每个数据库的相关信息
sysobjects 所有库 数据库中每个数据库对象的相关信息


5.查询分类：
按连接类型查询分为：内查询，外查询，交叉查询，自查询
按子查询分为：单列单行子查询，单列多行子查询，多列单行子查询，多列多行子查询，嵌套子查询

top关键字：select top 5 * from emp; --查询emp表中前5行
select top 20 percent * from emp; --查询emp表中前20%的行
distinct关键字：select all/distinct emp_name from emp; --查询emp表中的emp_name，消除重复行。

--分类汇总的用法
select stu_id 编号 ,sum(stu_age) 年龄 from stu_info group by stu_id with cube --group by的用法 --求出stu_age的年龄并按编号分类汇总
select stu_id 编号,stu_age 年龄 from stu_info compute sum(stu_age) --compute的用法
select stu_id 编号 ,stu_age 年龄 from stu_info order by stu_id asc compute sum(stu_age) by stu_id --order by 与compute by结合使用
select stu_banji,sum(convert(int,stu_chengji)) from stu_info order by stu_banji compute sum(convert(int,stu_chengji)) by stu_banji; --按班级分组，显示每班成员的成绩和总成绩(stu_chengji为字符类型,前后都需要转换成整型求和)；

6.常用数据类型
数字数据类型(精确整数)：int(4个字节),s

mallint(2个字节),tinyint(1个字节)
数字数据类型(精确小数):decimal和numeric(如：numeric(7,2)总长度为7位，小数点后占两位)
数字数据类型(近似数字)：float,real
字符类型(非unicode类型)：char(固定长度，最大长度8000个字符)， varchar(可变长度，最大长度8000个字符)，text(可变长度，最大长度2的31次方减1个长度)
字符类型(unicode类型)：nchar(固定长度，最大长度4000个字符),nvarchar(可变长度，最大长度4000个字符),ntext(可变长度，最大长度2的31次方减1个长度)
日期和时间类型：datetime,smalldatetime. 可以设置日期格式，用函数：set dateformat(@format) @format参数包括mdy,dmy,ymd,ydm,myd,dym六种格式参数，默认为mdy.
货币数据类型:money(8个字节，能够精确到货币单位的千分之十),smallmoney(4个字节，能够精确到货币单位的千分之十)
特殊数据类型: bit (由1和0组成)；

7.变量使用：
声明变量：用declare，在声明时它被初始化为null。(如：declare @emp_name char(20))
变量赋值：set @emp_name='baiyunfei' 或 select @emp_name='baiyunfei'; (@为局部变量，@@为全局变量)
查看变量值：select @emp_name(用select查看) 如：select @@version 返回当前SQL SERVER服务器的版本，处理器类型
select @@connections 返回今天试图连接到本服务器的连接数目
select @@remserver 返回登录记录中远程服务器的名字
8.流程控制语句：

if ......else 条件执行命令；
begin .....end 将一组SQL语句作为一个语句块；
while 循环执行相同的语句；
return 无条件返回
print 在屏幕上显示信息
raiserror 将错误信息显示在屏幕上，同时也可以记录在NT日志中。

case 表达式 用法：case (expression)
when (expression) then (result)
else (result)
end

9.注释：
/* .......*/ 注释多行
-- 注释一行


(六)SQL Server 实用

--创建人事系统数据库
use master
if exists(select * from master.dbo.sysdatabases where name='rsxt1')
begin
drop database rsxt1;
end
go
create database rsxt1;
go

--进入人事系统数据库
if exists(select * from master.dbo.sysdatabases where name='rsxt1')
begin
use rsxt1;
end
else
begin
print '数据库rsxt未建立'
return
end


--创建部门信息字典
create table dept_info
(
dept_id char(2), --部门编号
dept_name char(20) not null, --部门名称
constraint pk_dept_id primary key(dept_id)--以部门编号为主键,主键名为pk_dept_id
);

--部门数据初始化
insert into dept_i

nfo values('01','市场部');
insert into dept_info values('02','行政部');
insert into dept_info values('03','学术部');
insert into dept_info values('04','就业部');

--创建人员花名册
create table emp
(
emp_id char(5), --工号
emp_name char(20) not null, --姓名
dept_id char(2), --部门
age int, --年龄
tel char(10), --号码
bdate datetime default getdate() --入职时间
constraint pk_emp_id primary key (emp_id),--以工号为主键,主键名为pk_emp_id
constraint fk_dept foreign key(dept_id) references dept_info(dept_id)--建立对dept_info的外键
);

--人员数据初始化
insert into emp(emp_id,emp_name,dept_id) values('00001','胡','01');
insert into emp(emp_id,emp_name,dept_id) values('00002','温','02');
insert into emp(emp_id,emp_name,dept_id) values('00003','李','03');
insert into emp(emp_id,emp_name,dept_id) values('00004','张','03');
insert into emp(emp_id,emp_name,dept_id) values('00005','A','01');
insert into emp(emp_id,emp_name,dept_id) values('00006','B','02');
insert into emp(emp_id,emp_name,dept_id) values('00007','C','03');
insert into emp(emp_id,emp_name,dept_id) values('00008','D','03');

--创建薪水表
create table salary
(
emp_id char(5), --工号
dept_id char(5), --部门编号
salary numeric(7,2), --薪水
sdate char(6) --年月
);

--创建考勤表
create table work
(
emp_id char(5), --工号
dept_id char(2), --部门编号
sdate datetime, --上班时间
edate datetime --下班时间
);

--查看人员详细信息
select b.dept_name 部门,a.emp_id 工号,a.emp_name 姓名,age 年龄,bdate 入职时间
from emp a,dept_info b
where a.dept_id=b.dept_id;

--按部门统计人员数量
select count(a.emp_id) 人数,b.dept_name 部门
from emp a,dept_info b
where a.dept_id=b.dept_id
group by b.dept_name;

--增加人员考勤信息
insert into work values('00001','01',getdate(),dateadd(hh,8,getdate()));
insert into work values('00002','02',dateadd(mi,10,getdate()),dateadd(hh,10,getdate()));

--给市场部的所有人员增加一条考勤:上班时间为当前时间向后推10分钟,下班时间为当前时间向后推10小时
insert into work
select emp_id,dept_id,dateadd(mi,10,getdate()),dateadd(hh,10,getdate())
from emp
where dept_id='01';

--给行政部的所有人员增加一条考勤:上班时间为当前时间向后推20分钟,下班时间为当前时间向后推11小时
insert into work
select emp_id,dept_id,dateadd(mi,20,getdate()),dateadd(hh,11,getdate())
from emp
where dept_id='02';

--考勤统计,统计各部门11:00之后上班的人数
select count(*),a.dept_name
from dept_info a,work b
where a.dept_id=b.dept_id

and substring(cast(sdate as char(20)),12,5)>'11:00'
group by a.dept_name;
--另一种写法
select count(*),a.dept_name
from dept_info a,work b
where a.dept_id=b.dept_id
and datepart(hh,sdate)>11
group by a.dept_name;

--在考勤表中找01和02两个部门的数据
select * from work where dept_id in('01','02');
--在考勤表中找不是01和02两个部门的数据
select * from work where dept_id not in('01','02');



--程序块:
--查看市场部上班的记录
begin
declare @v_deptid char(2)--定义一个变量
--给该变量赋值
select @v_deptid=dept_id from dept_info where dept_name='市场部'
--打印变量
print @v_deptid

select a.emp_id 工号,a.dept_id 部门编号,
c.dept_name 部门名称,b.emp_name 姓名,a.sdate 上班时间
from work a,emp b,dept_info c
where a.dept_id=@v_deptid and a.emp_id=b.emp_id
and a.dept_id=c.dept_id
order by a.emp_id desc,sdate desc

--开始一个事物
begin transaction
--在事物当中删除市场部上班的记录
delete work where dept_id=@v_deptid
--回滚该事物
rollback
end;

--创建一个不带参数的存储过程p_insert
create procedure p_insert
--alter procedure p_insert
as
begin
--向工资表中给所有人员写一条工资为1000的初始记录
insert into salary(emp_id,dept_id,salary,sdate)
select emp_id,dept_id,1000,getdate()
from emp
commit;--提交该事物
end
return

--执行该存储过程
exec p_insert


--创建一个触发器tr_insert
--当考勤表中有人员9点之前(含9点)到岗时,给该人员在薪水表中增加一条记录给该人员当月奖励10元
create trigger tr_insert
--alter trigger tr_insert
on work
for insert,update
as
begin
declare @sdate datetime;
--取work表中更新数据后的上班时间
select @sdate=inserted.sdate from inserted;
--判断该上班时间是否小于等于9点
--if (datedepart(hh,@sdate)<=9)
if (substring(cast(@sdate as char(20)),12,1)<='9')
begin--begin if
--向薪水表中给该员工写奖励记录
insert into salary(emp_id,dept_id,salary,sdate)
select emp_id,dept_id,10,
cast(datepart(yyyy,getdate()) as char(4))+
(
case
when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as char(2))
when datepart(mm,getdate())>=10 then cast(datepart(mm,getdate()) as char(2))

end
)
from inserted;
end;--end if
end

--创建过程调用动态语法执行sql
create proc p_temp
--alter proc p_temp
@v_column varchar(255),--字段名
@v_tab varchar(255) --表名
as
begin
declare @v_sql nvarchar(255)
set @v_sql='select '+@v_column+' from '+@v_tab;
exec sp_executesql @

v_sql,N'@level tinyint',@level = 35;
end;

--执行该过程
p_temp 'emp_id','emp'
p_temp 'emp_id,emp_name','emp'
p_temp 'emp_id,emp_name,dept_name','emp a,dept_info b where a.dept_id=b.dept_id'

--月度考勤奖惩:该过程每月底执行一次,
--当考勤表中有人员9点之前(含9点)到岗时,给该人员在薪水表中增加一条记录给该人员当月奖励10元
--当考勤表中有人员9点之后到岗时,给该人员在薪水表中增加一条记录给该人员当月扣10元
create proc p_salary
as
begin--begin
declare @v_curdate datetime
if (select datediff(mm,getdate(),dateadd(dd,1,getdate())))<>1
begin
--raiseerror('当前时间不是月底，不能进行统计!')
print '当前时间不是月底，不能进行统计!';
return;
end
--向薪水表中给该员工写奖励记录
insert into salary(emp_id,dept_id,salary,sdate)
select emp_id,dept_id,10,
cast(datepart(yyyy,getdate()) as char(4))+
(
case
when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as char(2))
end
)
from work where substring(cast(sdate as char(20)),12,1)<='9';
--向薪水表中给该员工写惩罚记录
insert into salary(emp_id,dept_id,salary,sdate)
select emp_id,dept_id,-10,
cast(datepart(yyyy,getdate()) as char(4))+
(
case
when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as char(2))
end
)
from work where substring(cast(sdate as char(20)),12,1)>'9';
end;--end
return;

--修改salary的emp_id,sdate不能为空，并在该两字段上建立联合主键
--alter table salary drop constraint pk_semp_id;
alter table salary alter column emp_id char(5) not null;
alter table salary alter column sdate char(5) not null;
alter table salary add constraint pk_semp_id primary key(emp_id,sdate);
--修改emp表的emp_id字段长度为10
alter table emp alter column emp_id char(10);

--更改表t的字段名t为t1
sp_rename 't.t','t1','column'
--更改表名t为t1
sp_rename 't','t1'

create proc sp_stat
as
begin
--动态创建统计表
if exists(select 1 from sysobjects where name='tmp_stat')
begin
drop table tmp_stat
exec sp_executesql N'create table tmp_stat(emp_name varchar(20),dept_id varchar(5),sc int)',N'@level tinyint',35
end
else
exec sp_executesql N'create table tmp_stat(emp_name varchar(20),dept_id varchar(5),sc int)',N'@level tinyint',35

--向表中写迟到数据
insert into tmp_stat(emp_name,dept_id,sc)
select b.emp_name,a.dept_id,count(*)
from work a,emp b
where substring(convert(char(23),a.sdate,121),12,5)>'16:30'
and a.emp_id=b.emp_id
group by b.emp_name,a.dept_id
end;

select a.dept_id,dept_name,emp_name,age
from dept_info a,emp b
where a.dept_id=b.dept_id
order by a.dept_id asc,age desc

--游标
declare c_get cursor for select name from sysobjects --定义游标的数据来源
declare @v_name ch

ar(20)
declare @n_i int
set @n_i=0
open c_get --打开游标
fetch next from c_get into @v_name --取游标中的一条数据
print '@v_name的值:'+@v_name
print @@fetch_status --打印从游标中取出的数据
while (@@fetch_status=0) --从游标提取成功则循环直到取完游标所有数据为止
begin
set @n_i=@n_i+1
fetch next from c_get into @v_name
print '第'+rtrim(cast(@n_i as char(10)))+'次循环@v_name的值:'+@v_name
end
close c_get --关闭游标
deallocate c_get --释放游标,必须释放,否则会影响游标的下次使用



1.考勤统计,统计各部门11:00之后上班的人数

select b.dept_name 部门名称,count(emp_id)人数 from work a join dept_info b on a.dept_id
=b.dept_id where datepart(hh,a.sdate)>=11 group by
a.dept_id ,b.dept_name

2.对考勤表的下班时间建立索引

create nonclustered index work_edate_index on work(edate)

3.对考勤表的上班时间和人员ID建立联合索引

create nonclustered index work_emp_id_index on work(emp_id,sdate)

4.创建存储过程实现:在考勤表中统计学术部9:00以后上班的人员ID、人员姓名、部门名称、上班时间
并将以上信息写入到一张新生成的统计表(执行存储过程前不存在)中
create procedure count_sum
as
select a.emp_id 人员ID,b.emp_name 人员姓名,c.dept_name 部门名称,a.sdate 上班时间 into new_work from work a
join emp b on a.emp_id=b.emp_id join dept_info c on b.dept_id=c.dept_id
where datepart(hh,a.sdate)>=9 and c.dept_name='学术部'


5.创建存储过程实现月度考勤奖惩:
1)该过程每月底执行一次,当前非月底则给出提示不允许执行
2)统计考勤表中人员9:00(含9点)到岗的记录,为这些人员在奖惩表(另建)中增加一条10元的记录
3)统计考勤表中人员9:00后(含9点)到岗的记录,为这些人员在奖惩表中增加一条-10元的记录

create procedure jc
as
declare @now int,@sum int
set @now=datepart(day,getdate())
set @sum=datediff(dd,dateadd(month,1,getdate()),getdate())
if @now<>@sum
print '非月第不允许执行'
else
begin
insert into work_jc(emp_name,sdate,jc) select a.emp_name ,b.sdate,10 from emp a join work b on a.emp_id=b.emp_id
where datepart(hh,b.sdate)<9
insert into work_jc(emp_name,sdate,jc) select a.emp_name ,b.sdate,-10 from emp a join work b on a.emp_id=b.emp_id
where datepart(hh,b.sdate)>=9
end

6.创建触发器实现: 删除人员表某人数据时自动删除该人员在薪水表、考勤表中的记录
(1.通过级联删除实现功能）
alter table work
add constraint fk_emp_id foreign key(emp_id)references emp(emp_id) on delete cascade
alter table salary
add constraint fk_id foreign key(emp_id)references emp(emp_id) on delete cascade
(2.通过触发器实现功能)
create trigger del_emp on emp for delete
as
delete f

rom salary from salary a,emp b,deleted c where a.emp_id=c.emp_id
delete from work from work a,emp b,deleted c where a.emp_id=c.emp_id

7.创建触发器实现: 删除人员表某人数据时自动将该人员在薪水表、考勤表中的人员ID变更为0000
create trigger up_emp on emp for delete
as
update work set emp_id='0000' from work a,emp b,deleted c where a.emp_id=c.emp_id
update salary set emp_id='0000' from salary a,emp b,deleted c where a.emp_id=c.emp_id

8.创建触发器实现: 考勤表写入9:00前上班的数据时自动将该人员在薪水表中当月薪水加10
create trigger inser_jc on work for insert
as
if(select datename(hh,a.sdate)+':'+datename(mi,a.sdate)from work a,
inserted b where a.sdate=b.sdate)<'9:00'
insert into work_jc(emp_name,sdate,jc) select a.emp_name ,b.sdate,10 from
emp a join work b on a.emp_id=b.emp_id join inserted c
on b.sdate=c.sdate

9.计算所有人员2007年9月的薪水,公式:工资= 基本工资+奖金+减扣+工龄*100
工龄公式:当前系统时间-入职时间/12并四舍五入取小数点后的两位
要求查询结果中包含:人员姓名、部门名称、基本工资、奖金、减扣、工龄、薪水

select sum(总收入) from b where (select emp_id ,sum(salary+addsalary +outsalary+work_age*100) 总收入
from salary group by emp_id ) b
select sum(总收入) 总收入 from tt
select sum(salary+addsalary +outsalary+work_age*100) 总收入
from salary where substring(cast( getdate() as char(20)),12,7)

--更新工龄
update salary set work_age=(select'工龄'=round(datediff(mm, sdate,(getdate()))/12,2) from salary where emp_id=0002)where emp_id=0002

SP_HELP salary
--查出工资总和
select * from salary
select sum(salary+addsalary+outsalary+sdate in(select sdate , round(datediff(mm, sdate,(getdate()))/12,2) from salary))
from salary
select*from emp

--创建视图 加密
create view dep_gm with encryption
as
select * from dept_info ,stu_info where
(dept_id =stu_id) and (stu_id in(select id from scores
where scores.id between 1 and 2))with check option
sp_helptext 'dep_gm' --对加密视图进行查看， 看是否成功加密

--游标运用(滚动游标，没有scroll，为只进游标）
declare h scroll cursor for select stu_id ,stu_banji from stu_info
declare @c int ,@b char(20)
open h
fetch next from h into @c,@b
update stu_info set stu_name='heihei',stu_banji='yiban' where stu_id=@c
while(@@fetch_status=0)
begin
fetch next from h into @c,@b
update stu_info set stu_name='heihei',stu_banji='yiban' where stu_id=@c
end
close h
deallocate h

--insert ,delete ,update 的灵活用法
select stu_id,stu_name into byf from stu_info --将从stu_info表查询到的列stu_id,stu_name值插入到新表byf中。
insert scores select stu_id ,stu_name ,stu_chengji from stu_info --将从stu_info表中查询到stu_id,stu_name,stu_chengji

字段值插入到scores表中
delete from scores from stu_info ,scores where stu_info.stu.id=scores.stu_id --带from子查询
update scores set stu_name='wenjianhao' from stu_info,scores where stu_info.stu_id=scores.stu_id --带from子查询

--连接类型查询
select * from employee,department --交叉连接
select * from employee inner join department on manager=employee_name --内连接
select * from employee left/right outer join department on manager=employee_name --外连接（左连接/右连接)
select * from employee a,employee b where a.emp_id=b.emp_id --自连接

select b.emp_id 人员ID,b.emp_name 人员姓名,c.dept_name 部门名称,a.sdate 上班时间 from work a join emp b
on a.emp_id=b.emp_id join dept_info c on b.dept_id=c.dept_id where
datepart(hh,a.sdate)>9 and c.dept_name='学术部'

--好用法
insert into work(emp_id,dept_id,sdate,edate) select emp_id,dept_id,dateadd(hh,-9,getdate()),
dateadd(hh,2,getdate()) from emp
insert into salary select emp_id,dept_id,1200,datename(yy,sdate)+'年'+datename(mm,sdate)+'月'from work

--case语句的用法(用来分类统计)
create table area(
tel char(2),
area char(6),
y1 char(1),
y2 char(1),
y3 char(1)
)

select area 地区,sum(case y1 when 1 then 1 else 0 end)答对第1道题的人数,sum(case y2 when 1 then 1 else 0 end)答对第2道题的人数,sum(case y3 when 1 then 1 else 0 end)答对第3道题的人数 from area group by area;
select area ,sum(case y1 + y2 + y3 when '111' then 1 else 0 end ) 同时答对3道题的人数 from area group by area;

select area ,sum(case y1 + y2 + y3 when '011' then 1 when '101' then 1 when '110' then 1 when '111' then 1
else 0 end ) 同时答对2道题的人数 from area group by area;

select area ,sum(case y1+y2+y3 when '111' then 1 else 0 end) 同时答对3道题的人,sum(case y1+y2+y3 when '011' then 1
when '101' then 1 when '110' then 1 else 0 end) 同时答对2道题的人,sum(case y1+y2+y3 when '001' then 1 when '100' then 1
when '010' then 1 else 0 end) 同时答对1道题的人 from area group by area;
select * from area;

--通过查询生成新的数据集表
select a.emp_id ,a.emp_name from emp a,(select * from emp where emp_id between '00001' and '00005')b where a.emp_id=b.emp_id;

--通过参照emp信息表实现work考勤表中部门id数据一致
update work set dept_id=(select dept_id from emp b where work.emp_id=b.emp_id) where exists(select dept_id from work b
where work.emp_id=b.emp_id);

--通过参照emp信息表实现work考勤表中部门id数据一致(效果同上)
update work set dept_id=(select dept_id from emp where work.emp_id=emp.emp_id)

--运用变量求1+2+3...+50 的和
declare @x int, @y int
set @x=0 set @y=0
while(@x<50)
begin
set @x=@x+1
set @y=@y+@x
end
print @y;