sql 语句查询 函数 的使用

--这个是适合T-SQL入门的人看的,我也是刚入门的，现在讲自己的一些学习代码与大家分享，欢迎各位的指正和教导！加我QQ：962589149
以下就要是围绕
一:简单的语法知识
　　1:定义变量给变量赋值
　　2:if和else
　　3:begin和end
　　4:两种case语句
　　5:循环
二:简单的增 删 改 查和一些相关子句 
　　1:update
　　2:insert
　　3:select
　　4:delete
　　5:where子句
　　6:order by子句
　　7:distinct关键字
　　8:group by 聚集函数 和 having子句
三:复杂一点的查询
　　1:inner join
　　2:outer join
　　3:full join cross join
　　4:union
　　5:子查询返回单个值
　　6:子查询返回多个值
　　7:any some 和 all
　　8:外部查询和内部查询
　　9:派生表
四:约束
　　1:类型
　　2:命名
　　3:主键约束
　　4:外键约束
　　5:unique约束
　　6:check约束
　　7:default约束
　　8:禁用约束
　　9:规则
　　10:默认值
五:create drop alter
　　1:create
　　2:drop
　　3:alter
六:视图
　　1:创建视图
　　2:删除和修改视图
　　3:视图加密
七:存储过程与用户自定义函数
　　1:存储过程的简单创建\修改与删除
　　2:存储过程的输入参数和输出参数
　　3:用户定义函数
八:触发器
　　1:什么是触发器
　　2:一个简单的触发器
　　3:针对触发器的相关操作
　　4:使用触发器记录操作情况
　　5:视图上的触发器
　　6:触发器相关函数
九:索引
　　1:简要介绍全文索引
　　2:全文索引的相关操作
　　3:全文目录的相关操作
　　4:全文查询语法
　　5:综合实例
十:游标
　　1:什么是游标
　　2:创建一个简单的游标
　　3:游标的作用域

在此有很多不足，还请多多留言相告！
--创建数据库
create database test
--创建表
CREATE TABLE [myTrea] (
 [trea_id] [int] NOT NULL ,
 [trea_name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [trea_date] [datetime] NULL ,
 [trea_price] [float] NULL ,
  PRIMARY KEY  CLUSTERED
 (
  [trea_id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [myTrea2] (
 [trea_id] [int] NOT NULL ,
 [trea_name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [trea_date] [datetime] NULL ,
 [trea_price] [float] NULL ,
 [trea_regdate] [datetime] NULL ,
  PRIMARY KEY  CLUSTERED
 (
  [trea_id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO
select * from mytrea
--T-SQL 语句
--定义(声明)变量给变量赋值
declare @username varchar(4)  --定义变量(声明,变量名称,变量数据类型 必须指定长度，否则一直为1)
declare @userid   int         --自动增长列
select @username='abcd'       --select赋值
set @username='艳明二号'      --se赋值
set @userid=(select max(trea_id)  from mytrea)--获取自动增长列的值
print @username
print @userid
insert into mytrea values(@userid+1,@username,dateadd(day,-1,getdate()),15000)
select * from mytrea where trea_name =@username
--if else 的用法
declare @rowcount int
if not exists(select trea_id from mytrea)
  print '暂时没有数据'
else
  set @rowcount=(select count(trea_id) from mytrea  )
  print cast(@rowcount as varchar)+'条数据被检索到'
--begin 和end 用法如果条件内部要跟代码块(多条语句)就要用到begin和end
declare @trea_id int
if(@trea_id is null)
  begin
   print '@trea_id没有赋值';
   set @trea_id=(select trea_id from mytrea where trea_id=1)
   print '@trea_id已经获值'+convert(varchar,@trea_id);
  end
else
  print '@trea_id已经赋值'+convert(varchar,@trea_id);
--case 用法
select * from mytrea
select trea_id,trea_name,trea_date
   =case
  when  trea_date ='2012-06-12' then trea_price
  when  trea_date ='2012-05-12' then trea_price
  else '0'
  end
from mytrea
-- while 循环语句while允许break和continue的存在
while 1=1
  begin
  waitfor time '10:21'
  update mytrea set trea_name = '天下无敌2' where trea_id = 1
  waitfor time '10:23'
  SELECT GETDATE()
  PRINT '该下班了！'
end
--:inner join 是在做排除，如果任一行在两个表中不匹配，则注定将从最终的结果中排除掉
select * from mytrea  a inner join   mytrea2  b on a.trea_id=b.trea_id
--outer join无论左侧表(e)中的行是否与右侧表中的行相匹配都要显示(就是left join 或者right join)
select * from mytrea  a left outer join   mytrea2  b on a.trea_id=b.trea_id
--full  join  与  cross  join
--full  join  是  left join  和  right join 的结合
--full  join将包含位于连接两侧的表的所有行
select * from mytrea  a full outer join   mytrea2  b on a.trea_id=b.trea_id
--unionunion更像是从一个查询直接向另一个查询进行的数据追加（差别还是有的）
--1：select 列表中字段的数量必须相同
--2：字段的数据类型必须隐士兼容
--3：返回的结果集的标头取自第一个查询
--4：默认返回方式是distinct，union  alll返回全部的集合
select trea_name from mytrea union  all select trea_name from mytrea2
--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
select * from mytrea where trea_id = any(select trea_id from mytrea  where trea_name='艳明')
select * from mytrea where trea_id <> all(select trea_id from mytrea  where trea_name='艳明')
select * from mytrea where trea_id = all(select trea_id from mytrea  where trea_id=2)
--创建视图:删除和修改视图
--alter view  yourviewname   as...
--drop view  yourviewname   as...
--加密视图alter  view  yourviewname  with encryption as...加密了之后连你自己也看不到原代码了
create view myview
as
select * from mytrea
select * from myview

--存储过程
create proc myProc
as
update mytrea set  trea_name='存储过程' where trea_id=7
select * from mytrea
exec myProc  --调用
--带参数存储过程
create proc spComputer
    @trea_name nvarchar(50) = null
as
if @trea_name is null
  print '你未输入查询参数'
else
select * from mytrea where trea_name=@trea_name

exec spComputer '存储过程'
--输出存储过程
drop proc spComputer
alter proc spComputer
    @reX      nvarchar(50) ='123',
    @reY      nvarchar(50) = '456',
    @LastName nvarchar(50) = null output
as
if @LastName is null
    begin
        print @reX
        return '123';
    end
else
    begin
        print @reY
        return '456'
    end
--调用的
declare @myOut varchar(50)
set @myOut='123'
exec @myOut = spComputer @myOut,@myOut,@myOut output
print @myOut

--自定义函数(时间格式处理)
drop function DayOnly
Create function dbo.DayOnly(@date datetime)
    returns varchar(12)
as
begin
    return convert(varchar(12),@date,103)
end
select  dbo.DayOnly(trea_date) from mytrea --时间调用

--自定义table 查询
create function dbo.fnTableSearch(@TreaName nvarchar(50))
   returns table
as
  return (select * from mytrea where trea_name like @TreaName+'%')

select * from fnTableSearch('存储过程') --调用table 数据
--需要参数函数
drop function selTableByParam
create function dbo.selTableByParam
    (@id as int,@name as varchar)  --输入参数
    returns @allrows table  --临时表(及字段)
    (
        trea_id  int not null, 
        trea_name  nvarchar(40) null,
 trea_price float   null
    )
as
begin
    insert into @allrows  select trea_id,trea_name,trea_price from mytrea where trea_id = @id
return
end
go
select * from selTableByParam(2,'12233')
--触发器触发器是一种响应特定事件的特殊类型的存储过程
--最简单的触发器,当插入或者改变时 查找所有info
create trigger myTriger
 on mytrea
 after insert, update
as
select * from mytrea
--测试
insert into mytrea values('11','测试触发器',getdate(),350000)
update mytrea set trea_name ='我测试的' where trea_id=2
--1.禁用触发器
disable trigger all  --这里的all可以是具体的触发器名
--修改触发器
alter trigger myTriger
on mytrea
for insert, update
as
if update(trea_name)
begin
    print('你修改了trea_name的值')
end
update mytrea set trea_name ='我测试的' where trea_id=2
--视图触发器
create view trea_vw
with Schemabinding
as
select trea_id,trea_name from dbo.mytrea

create trigger myViewTrigger on trea_vw
instead of insert
as
begin
   print '视图触发器'
end
select * from trea_vw
insert into trea_vw values('12','视图触发器')
--全文索引 是对数据库表中一个或多个列的值进行排序的结构
SELECT databasePropertyex('test','isfulltextenabled')--此语句是否开启索引0为未开启,返回1为已开启
exec sp_fulltext_database @action = 'enable'--激活和禁用索引 'enable'为激活
--创建全文检索
create fulltext index on mytrea(trea_name,trea_price,trea_date)  --对某个列使用全文索引
 key index Pk_mytable --索引名(一般就是表名前面加Pk)
on maincatalog
with change_tracking off, --关闭跟踪特性
no population --不进行初始化填充
create index IN_myIndex
on mytrea(trea_name,trea_price,trea_date)
create full text index   IN_myIndex
on dbo.mytable (trea_name,trea_price,trea_date)
-- 优点加快访问速度
--加强行的唯一性
--缺点
--带索引的表在数据库中需要更多的存储空间
--操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新
--游标
drop proc spCursorScope
close  tablecursor
deallocate tablecursor

create proc spCursorScope
as
declare @id int
declare @title varchar(4)
declare @username varchar(50)
declare @num int
set @num = 1

declare tablecursor cursor  global  for
select a.trea_id,a.trea_name,a.trea_name from mytrea a
open tablecursor
fetch next from tablecursor into @id,@title,@username
while (@num <=3) and (@@fetch_status = 0)
begin
set @num = @num+1
print(cast(@num as varchar) +'         '+ cast(@id as varchar)+'       '+@title+'      '+@username)
fetch prior from tablecursor into @id,@title,@username
end

exec spCursorScope

--next --移动到下一条记录
--prior --移动到上一条记录
--first  --移动到第一条记录
--last  --移动到最后一条记录
--感谢各位IT(挨踢)朋友们的，下期将提供SQL 的高级查询，谢谢！
use test
go
select * from mytrea for xml AUTO
select @@CPU_BUSY
select @@IDLE
select APP_NAME()
select case(1 is null )
select  trea_id,case when trea_id =1 THEN '100000' else'120'end from mytrea
select UPPER('123456666 AAAA   vvvv') --将小写转换为大写
select stuff('abcdefg',2,3,'123456789');
SELECT STR(123.45, 6, 1)--由数字数据转换来的字符数据。
select SPACE(100)--返回空格数
select SOUNDEX ('aC');
select REVERSE('aabb123='); --翻转
select REPLICATE('123456',2);--将字符串重复次数
select REPLACE('123456','3','bbbbbbbbbbbbbbbb') --替换
select PATINDEX('b','abcdaddd')
select * from mytrea  where trea_name like '%艳明%'   -- 双向都可以获取
SELECT *  from mytrea where PATINDEX('%艳明%', trea_name)=1    --只有在前面的才可以获取
select RAND(123)
select rOUND(6)
select ROUND(748.58, -1)  --四舍五入
select SQRT(12)   --求平方根
select SQUARE(3)  --求平方
select DATEADD(year,12,getDate()) --在向指定日期加上一段时间的基础上，返回新的 datetime 值。
select DATEDIFF(day,'2011-06-12',getdate()) --比较两个指定日期的日期相差
select DATENAME(month,getdate())            --获取特定日期的year , month ,day
select DATEPART(minute,getdate());            --获取特定日期的year , month ,day,hour,minute,second,millisecond
select DAY(getdate());
select GETUTCDATE();
select GETDATE();
select ABS(-1200.5);
select LEFT('you are my wife',6)
select right('you are my wife',6)
select LEN(' 123456789');
select LEN('123456789 ');
select LOWER('AAAAAA&&**QWERTYUIOPLKJHGFDSAZXCVBNM$$##@@!!');
select NCHAR(11)
select isDate('2012-2-12')
EXEC sp_addsrvrolemember 'ZHIXUN-22C9E7A8\wuxiaolong', 'sysadmin'
select User
select RTRIM('a123456 123456')
select REPLICATE('123',5);
SELECT QUOTENAME('abc def')
select STATS_DATE(1,1)
use test
go
select * from mytrea,mytrea2 --等于表a的行数*表b的行数 
select * from mytrea as a ,mytrea2 as b where a.trea_id=b.trea_id --等于交集
select * from mytrea as a left join mytrea2 as b  on  a.trea_id=b.trea_id --等于left表的行数
select * from mytrea as a right join mytrea2 as b  on  a.trea_id=b.trea_id --等于right表的行数
select * from mytrea as a where trea_id in(select trea_id from mytrea2 )   --等于交集
select * from mytrea as a inner join mytrea2 as b  on  a.trea_id=b.trea_id --等于内连接
select * from mytrea as a full join mytrea2 as b  on  a.trea_id=b.trea_id --类似等于right join 表的行数

 

 

select * from mytrea,mytrea2

select * from mytrea2
select * from mytrea