常用sql语句

select 季度工作量.工号,dbo.人员管理.姓名,奖金
from dbo.人员管理,dbo.季度工作量
where 季度工作量.工号=人员管理.工号

select dbo.机器维修记录表.职员编号,dbo.人员管理.姓名,维修记录,机器维护内容
from dbo.机器维修记录表,dbo.人员管理
where dbo.机器维修记录表.职员编号=人员管理.工号

select 工号,姓名,原始住址,avg(当前薪水)
from 人员管理
with cube 原始住址

--Union
select 当前薪水 as 薪水
from 人员管理
union all
select avg(当前薪水)
from 人员管理
--order by 薪水 desc

--查询各个地址的个数
select 原始住址,count(*)
from 人员管理
group by 原始住址

--注意：要想按某种方式分组，必须将那一列放在group by第一个位置
select 人员管理.姓名,工作部门
from 员工工作部门,人员管理

where 员工工作部门.工号=人员管理.工号
group by 工作部门,人员管理.姓名
--with cube
--with rollup
having 工作部门 in('部门3','部门2','部门5')

sp_renamedb 人事部管理,公司人员管理

--简单回顾 2008-7-4

select dbo.人员管理.姓名,dbo.初始薪水.薪水段 初始薪水,dbo.人员管理.当前薪水,人员管理.当前薪水-初始薪水.薪水段 薪水差
from dbo.人员管理,dbo.初始薪水
where dbo.人员管理.初始薪水=dbo.初始薪水.学历代码

--回顾多表查询
--课本14-1
use northwind
go
select distinct CompanyName,Suppliers.SupplierID,CompanyName,ContactName,Address
from dbo.Products,dbo.Suppliers

--14-4-1
use pubs
go
select sum(price)
from dbo.titles

select sum(price)
from dbo.titles
where type='business'--别忘了加单引号

--14-3.3
use northwind
go
select Productname,count(*)
from products
group by productname

select count(Productname)
from products

--14.5.1
use northwind
go
select SupplierID,avg(UnitPrice) 平均价格
from products
group by SupplierID
--having avg(unitprice)>15

--问题
use 公司人员管理
go
select distinct a.学员编号,a.科目3
from dbo.学员成绩表 a join dbo.学员成绩表 b --内连接是找左边与右边的交集，两边共有的部分
on a.科目3=b.科目3
where a.学员编号<>b.学员编号
order by a.科目3 desc

--课件的例子↓
CREATE TABLE 库存信息
(库存编号 int IDENTITY(1,1) PRIMARY KEY,
产品名称 nvarchar(50),
库存量 int
)
CREATE TABLE 订单信息
(订单编号 int IDENTITY(1,1) PRIMARY KEY,
产品名称 nvarchar(50),
数量 int
)
GO
--插入数据
INSERT 库存信息 (产品名称,库存量) VALUES (N'鱿鱼',2)
INSERT 库存信息 (产品名称,库存量) VALUES (N'柳橙汁',5)
INSERT 库存信息 (产品名称,库存量) VALUES (N'浪花奶酪',3)
INSERT 订单信息 (产品名称,数量) VALUES (N'鱿鱼',38)
INSERT 订单信息 (产品名称,数量) VALUES (N'浪花奶酪',76)
INSERT 订单信息 (产品名称,数量) VALUES (N'酸奶酪',34)
GO

SELECT *
FROM 订单信息
INNER JOIN 库存信息
ON 库存信息.产品名称 = 订单信息.产品名称

--外连接……左外连接-保留左边所有的行
select *
from 订单信息 left outer Join 库存信息
on 库存信息.产品名称 = 订单信息.产品名称

--右外连接 保留右边的所有信息
select *
from 订单信息 right outer Join 库存信息
on 库存信息.产品名称 = 订单信息.产品名称

--全外连接 都包括在内

select *
from 订单信息 full outer Join 库存信息
on 库存信息.产品名称 = 订单信息.产品名称

--交叉连接
--因为交叉连接本身为一种笛卡尔积，所以没有条件where
select *
from 订单信息 cross Join 库存信息

--2008.7.4 end

--2008.7.7 视图
--创建视图
create view v_人员管理
as
select 工号,姓名,政治面貌,原始住址
from 人员管理

--修改视图
alter view v_人员管理
as
select 工号,姓名,员工政治面貌.政治面貌,人员管理.政治面貌 政治面貌编号,原始住址
from 人员管理,员工政治面貌
where 人员管理.政治面貌=员工政治面貌.代号

--浏览视图 即 浏览代码 ↓

sp_helptext v_人员管理

sp_depends v_人员管理 --返回试图所引用的数据库中的相关表格，及其关系

sp_help v_人员管理 --浏览视图中各列的列表

sp_help --浏览数据库中的详细信息

--视图中插入数据

delete --本例通过两步来像一个拥有两个表数据的视图中添加数据
from 人员管理
where 姓名='李四光'
go

insert into v_人员管理 --添加一个表中的数据 （数据插入……）
(姓名,原始住址)
values
('李四光','山东泰安')

update v_人员管理 --再通过修改刚添加的数据，来添加另一个表的数据 （数据更新……）
set 政治面貌=3
where 姓名='李四光'

alter view v_人员管理
with encryption --修改为加密
as
select all 姓名,工作时间,学历,薪水段,初始薪水
from dbo.人员管理,dbo.初始薪水
where dbo.初始薪水.学历代码=dbo.人员管理.初始薪水 ---只有插入的数据的视图中的数据在两个表中都能够体现才可插入显示

update v_rygl
set 初始薪水=1
where 姓名='李四光'

--↓--此时无法删除试图中的一条记录，因为是来自多表
delete
from v_rygl
where 姓名='李四光'

--删除视图
drop view v_rygl

--第十五章 数据的完整性
--2008.7.9

--创建主外键

alter table 宿舍
--alter column 员工编号
add constraint pk_员工编号 primary key (员工编号)

--删除主键
alter table 宿舍
drop pk_员工编号

--创建外键
alter table 宿舍
add constraint fk_员工编号 foreign key (员工编号) --忘记当前列了
references 人员管理(工号)
on delete cascade
on update cascade
not for replication
--删除外键
alter table 宿舍
drop fk_员工编号

------------------------------------------------------------------
--唯一键索引 unique
alter table 宿舍
drop u_员工编号

alter table 宿舍
add constraint u_员工编号 unique(员工编号)
----------------------------------------------------------------
--创建 check 约束
alter table 学员成绩表
add constraint ck_xycjb check (科目1>=0 and 科目1<=100)
-----------------------------------------------------------------
alter table 学员成绩表
--nocheck constraint all--禁用约束
check constraint all--启用约束
--with nocheck [check]
----------------------------------------------------------------
insert into 学员成绩表
values(1000024,101,86,80,64) --设置成功



alter table 学员成绩表 --删除约束 check
drop ck_xycjb
---------------------------------------------------------------------
--创建rule（规则）
create rule 成绩
as @cj>=0 and @cj<=100

drop rule 成绩
--绑定规则
sp_bindrule 成绩,'学员成绩表.科目1' --绑定列名
sp_unbindrule '学员成绩表.科目1' --解除绑定
insert into 学员成绩表--绑定成功
values(1000025,101,58,35,98)

--绑定到用户自定义的数据类型上

sp_addtype cj,'varchar(25)'
--与cj进行绑定
sp_bindrule 成绩,'cj'

sp_unbindrule cj--解除与自定义的数据类型的绑定

--验证是否成功
insert into 学员成绩表
values (1000027,88,90,66,101)

--设置默认值
create default 性别 --创建了一个默认值，名字叫 性别
as '男'
alter table 人员管理
drop default

sp_unbindefault '人员管理.性别'
sp_bindefault '性别','人员管理.性别'

--2008年7月11日14:46:33 编程sql

declare @name nvarchar(20),@xuehao nvarchar(20) --声明变量时，别忘了设置数据类型
select @name=姓名,@xuehao=工号--此处的赋值若为 列名 则不加分号
from dbo.人员管理
where 工号=1000011
--select @name,@xuehao--结果报表形式

print @name--结果为文档模式
print @xuehao

--声明变量
DECLARE @name varchar(20)
DECLARE @age int , @sex bit
--为变量赋值
SET @name = '张三'
SET @age = 20
SELECT @sex = 1
--输出变量
SELECT @name
SELECT @age
SELECT @sex
PRINT @name
PRINT @age
PRINT @sex
------------------------------------------------------------------------
declare @i int,@sum int
set @i=1
set @sum=0
while @i<=100
begin
set @sum=@sum+@i
set @i=@i+1
end
select @sum

求1-100的和

DECLARE @id int, @sum int

SET @id = 1
SELECT @sum = 0

WHILE @id<=100
begin
SET @sum = @sum + @id
SET @id = @id + 1
end
SELECT @id - 1, @sum

declare @i int,@max int
select @max=max(工号),@i=min(工号)
from 人员管理
while @i<=@max
begin
if @i%2=1
begin
select 工号,姓名
from 人员管理
where 工号=@i
end
else
begin
print '***********************'
end
set @i=@i+1-----------------------注意：赋值语句要在语句前面加上 set
end
print '结束'

--高斯法求和
declare @i int,@sum int,@j int
select @i=1,@j=100
set @sum=0
while @i<=50
begin
set @sum=@sum+@i+@j
set @i=@i+1
set @j=@j-1
end
select @sum
go
declare @i int,@sum int
set @i=1
set @sum=0
while @i<=100
begin
set @sum=@sum+@i
set @i=@i+1
end
select @sum

----------------------------------------------------------------------------------
--2008年7月13日
--创建我的绿化库《苗木信息》

create database 绿化苗木
on
(
name='绿化',
filename='H:\数据库文件\绿化\绿化.mdf',
size=1,
filegrowth=10%
)
log on
(
name='绿化_log',
filename='H:\数据库文件\绿化\绿化_log.ldf'
)
go
create table 绿化苗木大全
(
编号 int identity(1,1) primary key,树种 nvarchar(30) unique ,产地 nvarchar(50),适应对象 nvarchar(50) default '北方'
)
go

--插入数据 接口
--insert into 绿环苗木大全
--(树种,产地,适应对象)
--values()
--go
create table 苗木报价
(
苗木编号 int,苗木种类 nvarchar(30),报价 int
)
go
create table 苗木描述
(
苗木编号 int,描述 nvarchar(100)
)
create table 留言本
(
日期 datetime,留言 nvarchar(100)
)
----------------------------------------------------------------------------------------------------------------

declare @i int ,@j int,@sum int
set @i=1
set @j=100
set @sum=0
while @i<=50
begin
set @sum=@sum+@i+@j
set @i=@i+1
set @j=@j-1
end
select @i,@sum

-----------------------------------------
declare @i int ,@j int,@sum int
set @i=1
set @j=2
set @sum=0

begin
set @sum=(@j-@i+1)*(@i+@j)/2

end
select @i,@sum
-------------------------------------------
--查询以查询行数
print cast(@@rowcount as varchar(10))

--
declare @i varchar(20)
select 科目1,case
when 科目1<60 then 'bujige'
when 科目1>60 and 科目1<80 then 'yigan'
when 科目1>+80 then 'good'
end
from dbo.学员成绩表

--goto
declare @sum int, @count int
select @sum=0, @count=1

label_1:

select @sum=@sum+@count
select @count=@count+1
if @count<=100

goto label_1

select @sum

--
declare 游标 cursor
for select * from dbo.人员管理

open 游标
fetch last from 游标
close 游标
deallocate 游标
--
use 公司人员管理
go
declare a SCROLL CURSOR --此处如果不加scroll则不允许使用relative,absolute,first ,last
for select * from 人员管理

open a
fetch absolute 3 from a
close a
deallocate a
--------------------------------------
USE pubs
GO

-- Execute the SELECT statement alone to show the
-- full result set that is used by the cursor.
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

-- Declare the cursor.
DECLARE authors_cursor SCROLL CURSOR FOR
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Fetch the last row in the cursor.
FETCH LAST FROM authors_cursor

-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM authors_cursor

-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM authors_cursor

-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM authors_cursor

-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

---------------------------------------------------
declare rygl scroll cursor for
select 姓名,工号
from dbo.人员管理
open rygl
while (@@fetch_status=0)
begin
declare @姓名 varchar(20),@工号 int
fetch next from rygl into @姓名,@工号
print '姓名'
print @姓名
print ''
print '工号='
print @工号
end
close rygl
deallocate rygl

--事物
--显示事务
SELECT * FROM 人员管理
BEGIN TRAN --以此开始
INSERT 人员管理(工号,姓名) VALUES (401,'叮当')
IF @@ERROR> 0/*若成功返回为0*/ OR @@ROWCOUNT <>1--不反回行则为0
GOTO lable
--查看在事务嵌套之前的记录情况
SELECT * FROM 人员管理
SAVE TRAN SS--设置保存点
INSERT 人员管理(工号,姓名) VALUES (402,'迈克')
IF @@ERROR> 0 OR @@ROWCOUNT <>1
BEGIN
lable: ROLLBACK TRAN--回滚一个事务单元
END
ELSE
COMMIT TRAN--完成一个事务单元
SELECT * FROM 人员管理
INSERT 人员管理(工号,姓名) VALUES (403,'TOM')
SELECT * FROM 人员管理
----------------------------------------------------------------------------------

--存储过程通过带参数的存储过程实现动态累加，可传进参数进行累加
create procedure up_累加传参数
@i int=1,
@j int
with encryption--代码加密
as
declare @sum int
set @sum=0
begin
while @i<=@j
begin
set @sum=@sum+@i+@j
set @i=@i+1
set @j=@j-1
end
end
return @sum
-----------------------------执行带参且有 return的
declare @s int
exec @s=up_累加传参数 default,100
print @s
------------------------------------------------------ 2008年7月16日14:51:32 homework
alter procedure up_阶乘
@n int
with encryption--代码加密
as
declare @i int,@s int,@j int
set @s=1
set @i=1
set @j=1

begin
while @i<=@n
begin
set @j=@n
set @s=@s*@j*(@j-1)
set @i=@i+1
set @j=@j-1
end
return @s
end

--执行

create PROCEDURE up_阶乘
@in float
AS
BEGIN
DECLARE @i int,@s float,@out1 int
SET @i=1
SET @s=1
set @out1=1
WHILE @i<=@in
BEGIN
SET @s=@s*@i
SET @i=@i+1
END
SET @out1=@s
return @out1
END

DECLARE @out float
EXEC @out=up_阶乘 4
PRINT @out

--创建
create proc

--2008年7月18日14:40:33
--sql中的函数

select abs(-10)
select ascii('a')--求解ascill码
select ceiling(5.6)--返回大于等于这个数的最小值
select floor(5.6)--返回小于等于这个数的最大值

--实例：创建一个可以求奇偶性的函数
create function 奇偶数
(@i float)--参数必须带括号
returns nvarchar(4)
as
begin
declare @a nvarchar(4)
if @i/2=ceiling(@i/2)
set @a= '偶数'
else
set @a= '奇数'
return @a
end
-----------------------------执行函数
select dbo.奇偶数(2)
go

create function 字母顺序
(@i char(2))
returns int
as
begin
declare @a int
if ascii(@i)>=ascii('a') and ascii(@i)<=ascii('z')
set @a=ascii(@i)-ascii('a')+1
else if @i>=ascii('A') and @i<=ascii('Z')
set @a=ascii(@i)-ascii('A')+1
return @a
end

select dbo.字母顺序('a')

sp_help select ascii('e')-ascii('a')+1

select power(3,2)--求解一个数的几次方值
select pi()
select sqrt(4)--求解平方根
select square(4)--求平方
select round(3.1415926,3)--四舍五入

select substring('asfsfsdf',2,3)--2为从几位开始，3代表总共截取几位

select upper('aaBASFEaff')--全部装换位大写字母
select lower('aaBASFEaff')--全部转换为小写字母

select 'a'+space(5)+'b'--加空格函数
select replicate('你好 ',5)--重复函数
----------------------------------------------------------
declare @str nvarchar(50)
set @str='在交流空间撒方师傅0'
select len(@str)-------球字符串的长度

declare @str nvarchar(50)
set @str='在交流空间撒方师傅0'
print right(@str,3)
print left(@str,3)

print charindex('a','sfdsaljl')--返回a在字符串中的位置

print str(1465)--------------把数字转换为字符串

select ltrim(' 你好 ')--去左边的空格
select rtrim(' 你好 ')--去右边的空格

--------------强制类型转换 cast----------------------
print cast (@@rowcount as char(4))

------------------日期函数-------------------------
print getdate()--结果 07 18 2008 3:35PM
print datepart(year,getdate()) --结果：2008
print datepart(week,getdate())--这一年的第29个周
print datepart(second,getdate())--34

-------
print year(getdate())
print month(getdate())
print day(getdate())

--------------------自定义函数-----------------------------
create function 函数名
[函数参数]
基本格式as
{sql语句}
------------------------------------------------------------2008-7-18 15:47:40

复习1，sql 数据库建立

drop database sdxh1

create database sdxh1
on
(name=sdxh_1,
filename='e:\\sdxh_1.mdf',
size=5,
maxsize=10,
filegrowth=10%
),
filegroup rjkf
(
name='rjkf',
filename='e:\rjkf.ndf',
size=3,
maxsize=5,
filegrowth=10%
)
log on
(
name=sdxh_log,
filename='e:\\sdxh_log.ldf',
size=1,
maxsize=5
)

Create database 软件2班
on PRIMARY
( name='软件2班_data1',filename='e:\软件2班_data1.MDF',
size=10, maxsize=UNLIMITED, filegrowth=10% ),

Filegroup fg1
( name='软件2班_data2',filename='e:\软件2班_data2.NDF',--此为在文件组内再添加文件
size=20, maxsize=100, filegrowth=10%
)

log on
( name='软件2班_log',filename='e:\软件2班_log.LDF',
size=10, maxsize=50, filegrowth=1 )
go

sp_helpdb 软件2班
use 软件2班
go
sp_helpfile '软件2班'
sp_help
-----------------------------------
sp_renamedb 'sdxh1','rjsb'
---------------------------------------------
alter database rjsb---修改数据库的名字
modify name=软件四班
------------------------------------
alter database 软件四班-------------------修改数据库文件的名字
modify file (name=nam,newname=ice)
-------------------------------------修改指定数据库文件的大小
alter database 软件四班
modify file
(
name=ice,--------为已存在的数据库文件名
size=12-------修改后的大小
)
-------------------------------

alter database 软件四班

add file
(name='nam',
filename='e:\name.ldf',
size=10,
maxsize=11,
filegrowth=1%)
--注释：在数据库中修改数据库文件是，没修改一次需要单独修改，而不能一次性修改完毕

alter database 软件四班
add filegroup rjsb

------------------------------复习，创建表
create table 软件四班
(
学号 int identity(1,1) primary key,
name nvarchar(20),
addres nvarchar(50)
constraint fk_地址 foreign key references rjzy(地址),----外键约束
sex bit default(1)
)

create table rjzy
(
学号 Int identity(1,1),
地址 nvarchar(50) primary key
)
alter table rjzy

add 姓名 nvarchar(30)

alter table rjzy
drop column 姓名

alter table 软件四班
add constraint ck_性别
check(sex='男'or sex='女')

sp_rename 'rjzy.xm','xm1','column'------------重命名列名

create index ix_学号
on rjzy(学号)
alter index ix_学号

drop Index rjzy.ix_学号
create nonclustered index ix_xh
on rjzy(学号)

sp_helpindex rjzy-------此处为表名

select * from dbo.学员成绩表

alter table rjzy
alter column 学号
-------------------------2008年9月10日8:49:31

--备份还原
--1.增加设备
sp_addumpdevice 'disk','back','e:/back.bak'
sp_addumpdevice 'tape'……
sp_addumpdevice 'pipe','back'……
--删除设备
sp_dropdevice 'back'

-----创建数据库备份
backup database 公司人员管理
to back--完整备份
with differential--若加上这句话，则为差异备份

backup log 公司人员管理
to back
[with no_truncate]

backup database '公司人员管理'
file='公司人员管理.mdf'
filegroup='primary'
to back

-------数据恢复
restore database 公司人员管理
from back