加载中…
个人资料
岸芷汀蓝
岸芷汀蓝
  • 博客等级:
  • 博客积分:0
  • 博客访问:195,246
  • 关注人气:68
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

T-SQL学习

(2009-08-13 17:50:04)
标签:

it

分类: 数据库

一:简单的语法知识
 1 先举个例子
use myshop
declare @ident int
insert into orders
(customerid,orderdate)
values
(25,dateadd(day,-1,getdate()))--当前时间减去1天,就是昨天
select @ident = @@identity
insert into details
(orderid,productid,unitprice,quantity)
values
(@ident,1,50,25)
select 'the orderid of the inserted row is' + convert(varchar(8),@ident)
把数据插入定单表,得到新插入的记录的id然后插入定单明细表
2 定义变量给变量赋值
declare @myval int;
set @myval = 10;
set @myval1 = @myval2*12;
--下面语句是在查询中赋值变量
select @myval = max(id)+1 form mytable
3 if和else
use xland
if not exists(select id from mytable)
print '暂时没有数据'
else
print cast(@@rowcount as varchar)+'被检索到'
上面语句执行结果是 0行被检索到
4 begin和end
如果条件内部要跟代码块(多条语句)
就要用到begin和end
use xland
if not exists(select id from mytable)
print '没?有?检?索?到?数?据?'
else
begin
select id from mytable
print cast(@@rowcount as varchar)+'行?被?检?索?到?'
end
上面语句执行结果是:2行被检索到
5 case语句
第一种case语句
use AdventureWorks
select SalesOrderID ,Position = Case SalesOrderID%10
when 1 then 'First'
when 2 then 'Second'
when 3 then 'third'
else 'some thing else'
end
from Sales.SalesOrderHeader
第二种case语句
use AdventureWorks
select SalesOrderID ,Position = Case
when SalesOrderID%10 = 1 then 'First'
when SalesOrderID%10 = 2 then 'Second'
when SalesOrderID%10 = 3 then 'third'
else 'some thing else'
end
from Sales.SalesOrderHeader
第一种CASE when子句中可以跟表达式
第二种case when子句中表达式要返回true 或者false
当前面的条件和后面的条件同时满足时
按前面的条件处理,不需要break语句
6 循环语句
while 1=1
begin
waitfor time '01:00'
update mytable set num = 12 where id = 13
end
上面语句是在每天凌晨1点执行指定的操作
while允许break和continue的存在
二:简单的增 删 改 查和一些相关子句
1:update
2:insert
3:select
4:delete
5:where子句
6:order by子句
7:distinct关键字
8:group by 聚集函数 和 having子句
一:insert语句
into 关键字是可选的
values关键字前面的()是可选的,这里是要接收数据的列
values后面,有两种方式提供值
1:显式的给出值 2:从select语句中导出值
insert语句注意几点
1:不要理标志列,系统会给你插入的
2:给出实际的值,如果没有,那就null
3:给出默认的值,default关键字,告诉数据库取默认值
insert into ... select
什么时候会这么用,当成批的数据来自
1:数据库中的另一个表
2:同一台服务器完全不同的数据库
3:另一个SQLSERVER的数据库
4:来自同一个表中的数据
说实在的2和3我从来没用过
好,看一个例子
declare @mytable table
(
id int,
sortid int
);
insert into @mytable (id,sortid) select id,classid from product;
select * from @mytable;
注意我定义了一个表的对象
每一句之间是用分号隔开的
(id,sortid) 是可以忽略的
二:update语句
看例子
update e set e.flag = 'm' from employee e join contact ce on e.id = ce.employeeid where ce.name = 'jo'
这里用到了join子句,当然是可以用的
如果修改不止一列 只要加一个逗号
set num = num * 1.2当然可以写表达式
三:delete语句
delete from actors from actors a left join film f on a.filmid = f.filmid where f.filmid is null
outer连接将在没有匹配的一端返回null,
这里也就是film表没有匹配的行是null
注意 is null 的写法
四: select语句
这里还是不说了,还是多说说子句吧
五:where子句
1:
= > < >= <= <> != !> !<
其中<> 与 !=都是不相等的意思
 2:
and or not
如果一个where子句中同时出现这三个操作符
最先评估not 然后是and 然后是or
3:
between 例子 between 1 and 5 这个就不多说了
4:
like 例子:like "xland%"
%和_是通配符
%代表零个或多个任意字符
_表示单个任意字符
把字符包括在方括号中
[a-c]表示a b c都可行
[ab]表示a或b
^与not表示下一个字符将被排除掉
5:
in 例子 in ( 1,2,3) 这个也不多说了
六:order by子句

1:order by username 是可以识别中文的(sql先把中文转换成拼音再进行排序)
2:可以对 查询到的表中的任何列 进行排序 无论该列是否包含在select列表中
3:基于多个列的order by
例如:order by addtime,id
先按时间排序,再在这个基础上根据id排序,你也看到了,性能是有很大幅度的降低的
七:distinct关键字
select count(distinct column) from table
检索某一列不重复的记录数
八:group by 聚集函数 和 having子句
先看例子:
select orderid sum(orderMoney) from sales where orderid between 3 and 123 group by orderid
注意这里的orderid在表里是有可能重复的
这个语句的作用是检索出orderid从3到123的记录,
然后在这个记录集合上 以orderid分组
把orderid相同的数据分到一组 (这一组就是最终结果的一条记录)
然后通过sum函数把各组的orderMoney数据相加,
结果是最终结果的第二个字段
我发现用group by子句基本上都是和聚集函数一起用的
举几个聚集函数的例子
sum 求和
avg求平均数
min/max求最大和最小值
count(表达式|*)获取一个查询中的行数
只说说count吧:
count(coloum)如果这一列有null,那么这些null的行将不会计算在内
count(*)将得到表里的所有行的数目
聚集函数不一定非和group by一起使用不可
(另外avg基本上都是和group by一起使用的)
having子句是在组上放置条件的
看例子
select orderid sum(orderMoney) from sales where orderid between 3 and 123 group by orderid having sum(orderMoney)>5
group by得到了一个记录的集合
然后通过having子句,再在这个集合上做筛选
三:复杂一点的查询
1:inner join
2:outer join
3:full join cross join
4:union
5:子查询返回单个值
6:子查询返回多个值
7:any some 和 all
8:外部查询和内部查询
9:派生表
一:inner join
inner join 是在做排除,如果任一行在两个表中不匹配,则注定将从最终的结果中排除掉
例子1:select * from employee e inner join employee m on e.managerid = m.employeeid
这是从一个表里查询了两次
得到的一行记录将包括两个employee的信息 前面的是经理 后面的是打工的
注意from哪个表 哪个表的信息就在前面
其中e和m分别是表的别名,这里的别名和列的别名不同,不用写as
例子2 select e.employeeid, ce.firstname, m.employeeid as managerid, cm.firstname as managerfirst
from employee e inner join employee m on e.managerid = m.employeeid
inner join contact ce on e.contactid = ce.contactid
inner join contact cm on m.contactid = cm.contactid
第一个联接是把一个表与他自身进行连接 这个叫自引用(注意表的别名)
第二个连接得到经理的名字
第三个连接得到雇员的名字
看似很复杂的连接 其实很简单
最后说一点inner join 是默认的连接类型 inner 关键字是可选的
二:outer join
先看例子
select e.employeeid, m.employeeid as managerid from employee e
left outer join employee m on e.managerid = m.employeeid
无论左侧表(e)中的行是否与右侧表中的行相匹配都要显示
如果左侧表中的行在右侧表中找不到相匹配的数据, 那么右侧表的数据为null
right outer join 也类似 outer是可以忽略的
三:full join 与 cross join
这两个其实都不必多说
full join 是 left join 和 right join 的结合
full join将包含位于连接两侧的表的所有行
不存在的行就用null补齐
cross join 没有on操作符
得到的是两测表中所有行的 笛卡儿积
就是把两册的行排列组合一下
一般不会存在null的行
这是相当罕见的用法
只有科学家或者来搞样本数据的才会用到这个用法
四:union
union更像是从一个查询直接向另一个查询进行的数据追加(差别还是有的)
join更像是水平的合并数据(添加更多的列),union是垂直的合并数据(添加更多的行)
先看例子:select col2 from table1 union all select col4 from table2
1:select 列表中字段的数量必须相同
2:字段的数据类型必须隐士兼容
3:返回的结果集的标头取自第一个查询
4:默认返回方式是distinct,union alll返回全部的集合
五:子查询返回单个值
先看例子:
declare @hits int
select @hits = min(hitnum) from articles
select distinct A.title from articles A join users U on A.uid = U.id where A.hitnum = @hits
这是完全可行的,但是我们可以用另一种形式:
select distinct A.title from articles A join users U on U.id = A.uid where A.hitnum = (select min(hitnum) from articles )
这就是子查询
六:子查询返回多个值
接着看例子(子查询里返回多个值)
use database
select A.title from articles A join users U on A.uid = U.id where A.id in (select id from articles where istop = 'true')
再看一个例子(用子查询找出孤立的记录)
select A.title from article A join users U on A.uid = U.id where A.id not in (select id from
articles where istop = 'true')
这个例子写的有点牵强
但是这里注意 not in 子查询得到的字段 不能有null直 存在,如果有 那么整个句子将返回空
细心的人大概看出来了,前面提到的两个子查询的例子几乎都可以用内联(join)的方式替换掉
出于性能上的考虑,我们应该首先选择联结的解决方案 而不是子查询
七:any some 和 all
any与some功能上是相同的,推荐用some
any与some一般都是和比较运算符一起使用的(>= <= <> !> 等 )
>some (1,2,3)意味着大于1 >some就是大于最小的一个值
=some和in 起的作用是一样的
not in (a,b,c)展开为 <>a and <>b and <>c
<>some (a,b,c)展开为 <>a or <>b or <> c
>all (1,2,3)意味着大于3
八:where子句中的相关子查询(外部查询和内部查询)
先看个例子
use adventureworks
drop table #MinOrderdates
select CustomerID, min(OrderDate)as orderdate
into #MinOrderdates
from Sales.SalesOrderHeader
group by CustomerID
order by CustomerID
select o.CustomerID,o.SalesOrderID,o.OrderDate
from Sales.SalesOrderHeader o
join #MinOrderdates t
on o.CustomerID = t.CustomerID
and o.OrderDate = t.orderdate
order by o.CustomerID
drop table #MinOrderdates
每个顾客在系统中的第一张定单的orderid 和orderdate
用到了临时表,执行了两次查询
看下面的例子
select o1.CustomerID,o1.SalesOrderID,o1.OrderDate
from Sales.SalesOrderHeader o1
where o1.OrderDate =
(
select min(o2.OrderDate)
from Sales.SalesOrderHeader o2
where o2.CustomerID = o1.CustomerID
)
order by CustomerID
执行了一次查询
注意内部查询对外部查询有一个显示的引用 o2.CustomerID = o1.CustomerID
当然外部查询也可以引用内部查询中的列
第二个例子
select c.LastName,
(
select min(OrderDate)
from Sales.SalesOrderHeader o
where o.ContactID = c.ContactID
) as orderdate
from Person.Contact c
九:派生表
先看例子:
订购过某种特定产品的顾客列表
use AdventureWorks;
select c.FirstName,c.LastName
from Person.Contact As c
join Sales.SalesOrderHeader as soh
on c.ContactID = soh.ContactID
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID = sod.SalesOrderID
join Production.Product as p
on sod.ProductID =p.ProductID
where p.Name = 'Minipump'
查找顾客的名字以及首次订购的日期
90行受影响
现在我们想查找即订购过Minipump又订购过AWC Logo Cap的顾客
如果最后是
where p.Name = 'Minipump' or p.Name = 'AWC Logo Cap'
2412行受影响
这样做是错误的
这样得到的结果是订购过Minipump和订购过AWC Logo Cap的顾客
下面来看我们的解决方法
use AdventureWorks
select distinct c.FirstName,c.LastName
from Person.Contact As c
join
(
select ContactID from Sales.SalesOrderHeader As soh
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID = sod.SalesOrderID
join Production.Product As p
on sod.ProductID = p.ProductID
where p.Name = 'Minipump'
) pumps
on c.ContactID = pumps.ContactID
join
(
select ContactID from Sales.SalesOrderHeader as soh
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID = sod.SalesOrderID
join Production.Product as p
on sod.ProductID = p.ProductID
where p.Name = 'AWC Logo Cap'
) caps
on c.ContactID = caps.ContactID
把select出的内容当成一个表用
这就是派生表
四:约束
 一:类型
约束的类型一共分三种
域约束: 涉及一个或多个列,(限制某一列的数据大于0)
实体约束: 相同的值不能存在于其他的行中
引用完整性约束: 一个表中的一个列与某个表中的另一个列的值匹配
二:命名
约束是可以命名的 一般这样命名:
pk_customer_***
pk代表主键 customer代表主键所在的表 后面是你自己定义的(要确保整个名称的唯一性)
三:主键约束
主键约束:一般就是id, 一个表中最多有一个主键
例子1
use accounting
create table employee
(
id int identity not null,
firstname varchar(20) not null
)
例子2
use accounting
alter table employee
add constraint pk_employeeid
primary key (id)
四:外键约束
外键约束用在确保数据完整性和两个表之间的关系上
先看例子
create table orders
(
id int identity not null primary key,
customerid int not null foreign key references customer(id),
orderdate smalldatetime not null,
eid int not null
)
注意:这个表的外键必须是另一个表的主键!
在现有表上添加外键
alter table orders
add constraint fk_employee_creator_order
foreign key (eid) references employee(employeeid)
使用表自引用
表内至少要有一行数据才可以这么做
alter table employee
add constraint fk_employee_has_manager
foreign key (managerid) references employee(employeeid)
创建表的时候做表自引用 就可以忽略 foreign key 语句
表自引用的外键列 必须允许为null 要不是不允许插入的(避免对最初行的需要)
一个表与另一个表有约束,这个表是不能被删除的
级联操作
先看例子
create table orderdetails
(
orderid int not null ,
id int not null ,
description varchar(123) not null,
--设置主键
constraint pkOrderdetails primary key (orderid,id),
--设置外键,级联操作
constraint fkOrderContainsDetails
foreign key (orderid)
references orders(orderid)
on update no action
on delete cacade
)
on delete cacade 当删除父记录时 同时删除该记录
也就是当删除orders表中的一条记录,
与之相关的orderdetails表中的记录也将被删除
级联的深度是没有限制的,但是每个外键都必须设置on delete cacade
no action是可选的
五:unique约束
unique约束与主键约束类似,同样也是要求指定的列有唯一的值
但是一个表中可以有多个unique约束的列,同时这个列允许存在null值。(最多有一个null值)
看例子:
create table shippers
(
id int indentity not null primery key,
zip varchar(10) not null ,
phoneno varchar(14) not null unique
)
例子二:
alter table employee
add constraint ak_employeeSSN
unique(ssn)
六:check约束
check不局限于一个特定的列,可以约束一个列,也可以通过某个列来约束另一个列
定义check约束使用的规则与where子句中的基本一样
下面我写几个
between 1 and 12
like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
in ('ups','fed ex','usps')
price >=0
shipdate >= orderdate
看例子:
alter table customers
add constraint cn_customerDateinsystem
check
(dateinsystem <= getdate())
getdate()函数得到当前时间,上面这个例子的意思是dateinsystem列的数据不能大于当前时间
现在如果给这个列插入一个明天的时间,就会出错
七:default约束
如果插入的新行在定义了默认值的列上没有给出值,那么这个列上的数据就是定义的默认值
默认值只在insert语句中使用
如果插入的记录给出了这个列的值,那么该列的数据就是插入的数据
如果没有给出值,那么该列的数据总是默认值
八:禁用约束
在创建约束之前,数据库中已经有一些不符合规矩的数据存在。
创建约束之后,又想加入一些不符合规矩的数据。
这些时候就要禁用约束。primary key 和 unique约束 这对孪生约束是不能禁用的
对一个已经存在数据的表加一个约束:
alter table customers
add constraint cn_customerPhoneNo
check
(phone like '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9]')
如果表内有不符合这个约束的记录,sqlserver就会报错
如果这样写,就不会报错了
alter table customers
with no check
add constraint cn_customerPhoneNo
check
(phone like '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9]')
如果需要把一些不符合规矩的数据加入到表中怎么办
这时候就需要临时禁用现有的约束:
alter table customers
nocheck
constraint cn_customerPhoneNo
--允许不带套插入,此处的名称是前面定义的
insert into customer (phone) values (123456)
--开始不带套插入!
alter table customers
check
constraint cn_customerPhoneNo
--下次插入要带套
九:规则
先看例子:
Create rule SalaryRule
as @salary >0;
sp_bindrule 'SalaryRule' , 'Employee.Salary'
第一句定义了一个规则叫SalaryRule
进行比较的事物是一个变量
这个变量的值是所检查的列的值
第二句把规则绑定到某个表的一个列上
规则和ckeck约束很相似,
但是规则只作用在一个列上
一个规则可以绑定在多个列上,但是它不会意识到其他列的存在
check可以定义column1>=column2
取消规则
exec sp_unbindrule 'Employee.Salary'
删除规则
Drop rule SalaryRule
十:默认值
默认值与default约束类似(有区别的,但是我说不清楚)
先看例子:
create default salarydefault
as 0;
exec sp_binddefault 'salarydefault' , 'employee.salary';
取消默认值:
exec sp_unbinddefault 'employee.salary'
删除默认值:
drop default 'salarydefault'
五:create drop alter
1:create
2:drop
3:alter
一:drop
use accounting
drop table customers
删除表
use master
drop database accounting
删除数据库
这里删除数据库 use 的是master
一定要关闭外部连接 才能删除数据库
二:create
先看例子
create database accounting
on
(name = 'accounting',
filename = 'c:\database\accountingdata.mdf',
size = 10,
maxsize = 50,
filegrowth = 5)
log on
(name = 'accountinglog',
filename = 'c:\database\accountinglog.ldf',
size = 5MB,
maxsize = 25MB,
filegrowth = 5MB )
go
create database完整的语法有很多子句的
我上面写的那个例子只涉及到几个常用的
下面解释一下:
on指存储数据文件的位置,可以把数据存储到多个文件上(这只应用在超大型数据库上)
name是文件的逻辑名称,调整数据库文件大小时要用到这个名称
filename可选的参数,如果不使用这个参数,那么这个文件就存在MSSQL.1\MSSQL\DATA目录下
如果是数据文件那么名称与数据库名称相同,扩展名是.mdf
如果是日志文件那么名称是数据库名后跟一个_log,扩展名是.ldf
size数据库的大小,可以带单位也可以直接写整数,不能写小数
maxsize数据库能增大到的最大大小,如果不设置这个参数,那么就是磁盘的大小,建议设置一个数字比磁盘小一点
filegrowth数据库每次增长将增长多少,可以是整数,百分数
log on 是设置日志的,默认设置日志文件是数据文件大小的25%
日志文件最好不要和数据文件在同一个磁盘上,避免竞争,安全保障
Create table
先看例子
use accounting
create table customers
(
id int idetity not null primary key,
username varchar(30) not null
)
就不多说了
说一下命名规范
1:保持名称简短,长度要足以使名称具有描述性
2:当基于其他表来构造表时,新表的名称中其包含父表的名称
3:当名称中有两个单词时,不要使用任何分隔符,每个单词首字母大写
三:ALTER
alter database accounting modify file (name = accounting , size = 100MB)
上面是把数据库文件扩展到100MB
alter table customers add fedid varchar(9) null
alter table customers add
contact varchar(25) null,
lastraisedate datetime not null default '2005-12-4'
下面看个关于主键的alter语句
use accounting
alter table employee
add constraint pk_employeeid
primary key (employeeid)
要向表中添加约束,
添加约束的名字
约束的类型
将约束应用到的列
六:视图
1:创建视图
2:删除和修改视图
3:视图加密
一:简单的视图
use xland
go
create view my_vw
as
select a.id, a.title,u.username from mytable a join [user] u on u.id = a.uid
执行这个视图
use xland
go
select * from my_vw
结果跟直接运行
select a.id, a.title,u.username from mytable a join [user] u on u.id = a.uid
是一样的
二:删除和修改视图
alter view yourviewname as...
drop view yourviewname as...
三:加密视图
alter view yourviewname with encryption as...
加密了之后连你自己也看不到原代码了
七:存储过程与用户自定义函数
1:存储过程的简单创建\修改与删除
2:存储过程的输入参数和输出参数
3:用户定义函数
一:存储过程的简单创建,修改与删除
1.创建简单的存储过程
use AdventureWorks
go
create proc spEmployee
as
select * from HumanResources.Employee
执行上面的代码就创建了一个存储过程
如果想运行这个存储过程
可以直接执行exec spEmployee这个语句
2.更改存储过程
ALTER proc [dbo].[spEmployee]
as
select top 13 * from HumanResources.Employee
3.删除存储过程
drop proc dbo.spEmployee
二:存储过程的输入参数和输出参数
1.有输入参数的存储过程
use AdventureWorks
go
create proc spEmployee
@LastName nvarchar(50) = null
as
if @LastName is null
select top 13 * from HumanResources.Employee
else
select top 10 * from HumanResources.Employee
查看该存储过程的结果可以用
exec spEmployee '123'
或直接exec spEmployee
存储过程的重载...
2.有输出参数的存储过程
use AdventureWorks
go
alter proc spEmployee
@LastName nvarchar(50) = null output
as
if @LastName is null
begin
print 'null'
return '123'
end
else
begin
print @LastName
return '456'
end
看第一个测试该存储过程的语句
declare @myval nvarchar(50)
exec @myval = spEmployee @myval output
print @myval
输出null 123
第二个测试该存储过程的语句
declare @myval nvarchar(50)
set @myval = 'xland'
exec @myval = spEmployee @myval output
print @myval
输出xland 456
三:用户定义函数
1.返回标量值的用户定义函数
先做一个简单的日期处理函数
把长日期缩短成短日期
Create function dbo.DayOnly(@date datetime)
returns varchar(12)
as
begin
return convert(varchar(12),@date,101)
end
为了测试上面的函数先做个脚本
use Accounting
declare @counter int
set @counter = 1
while @counter <= 10
begin
insert into Orders values(1,dateadd(mi,@counter,getdate()),1)
set @counter = @counter +1
end
然后检索这个脚本 新插入的数据记录
use Accounting
select * from orders where dbo.DayOnly(date1) = dbo.DayOnly(getdate())
2.返回表的用户定义函数
先看例子
use AdventureWorks
go
create function dbo.fnContactSearch(@LastName nvarchar(50))
returns table
as
return (select * from Person.Contact where LastName like @LastName+'%')
执行这个例子
use AdventureWorks
select * from fnContactSearch('Ad')
3.综合例子:返回表,有输入参数
use xland
go
create function dbo.funGetMytable
(@id as int)
returns @allrows table
(
id int not null,
title nvarchar(max) null
)
as
begin
insert into @allrows select id,title from mytable where id = @id
return
end
go
执行这个例子
select * from funGetMytable(1)

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
前一篇:SQL语句大全
后一篇:T-SQL学习二
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

    < 前一篇SQL语句大全
    后一篇 >T-SQL学习二
      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有