sql基础2

1交叉联接

　　cross join 笛卡尔积 将一个表的每行与另一个表的所有行进行匹配 m行表与n行表cross join 得到m*n行的结果集

--ANSI SQL-92标准 CROSS JOIN 关键字
SELECT  c.custid ,
        e.empid
FROM    Sales.Customers AS c
        CROSS JOIN hr.Employees AS e
--ANSI SQL-89标准
SELECT  c.custid ,
        e.empid
FROM    Sales.Customers AS c ,
        hr.Employees AS e

--利用自联接生成数字表
DECLARE @table TABLE
    (
      digit INT NOT NULL PRIMARY KEY
    )
INSERT  INTO @table( digit )VALUES  ( 0 ),( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 9 )

SELECT  T1.digit + T2.digit * 10 + T3.digit * 100 + 1 AS d        
FROM    @table AS T1
        CROSS JOIN @table AS T2
        CROSS JOIN @table AS T3
ORDER BY d

2内联接

-- join ... on ..  推荐使用 更安全
SELECT  e.empid ,e.firstname ,e.lastname ,o.orderid
FROM    hr.Employees AS e
        JOIN Sales.Orders AS o ON e.empid = o.empid

--where
SELECT  e.empid ,e.firstname ,e.lastname ,o.orderid
FROM    hr.Employees AS e ,
        Sales.Orders AS o
WHERE   e.empid = o.empid

3其他联接

--组合联接
SELECT  *
FROM    tabName1 AS A
        INNER JOIN tabName2 AS B ON A.id1 = B.id
                                    AND A.id = B.id2

--不等联接
SELECT  *
FROM    tabName1 AS A
        INNER JOIN tabName2 AS B ON A.id1 > B.id

--多表联接
SELECT c.custid ,c.companyname ,o.orderid ,od.productid ,od.qty
 FROM   Sales.Customers AS c
        INNER JOIN Sales.Orders AS o ON c.custid = o.custid
        INNER JOIN Sales.OrderDetails AS od ON o.orderid = od.orderid

--外联接

left join, right join, full join 

 4存储过程

　　优点 ：容易维护，移植，执行速度较快,可设置权限

--常用系统存储过程
exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

--实例
--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

　　1获取某个员工的所有下级

--with as 相当于子查询
Create PROCEDURE [dbo].[getnode]
@emp_id int
AS
BEGIN

 WITH aCTE AS
(
SELECT *
FROM Employees
WHERE emp_id=@emp_id and emp_isworking = 730
UNION ALL
SELECT b.*
FROM aCTE a
JOIN Employees b
ON a.emp_id=b.emp_pid and b.emp_isworking = 730
)

SELECT * FROM aCTE where emp_id <>@emp_id order by emp_id
END

--
exec getnode 46

　　2、变量

　　　　局部变量@开头，全局变量@@开头

--简单声明
declare @emp_id int
--声明时直接赋值
declare @emp_id int = 3

set与select 
--当表达式返回多个值时，使用set赋值报错,使用select赋值返回结果集最后一列值
1、
declare @name nvarchar(200)
set @name = (select emp_zhname from Employees)
print @name
/*报错*/
2、
declare @name nvarchar(200)
select @name = (select emp_zhname from Employees)
print @name 
/*返回结果集中嘴个一个emp_zhname的值*/

--当表达式未返回值时，使用set赋值null,使用select返回原来的值
declare @name varchar(20)
set @name='jack'
set @name= (select username from userinfo where username='not')
print @name  --Null值
/**/
declare @name varchar(20)
set @name='jack'
select @name=username from userinfo where username='not'
print @name  --jack,保存原来的值
/*jack*/

	SELECT	SET
同时对多个变量同时赋值时	支持	不支持
表达式返回多个值时	将返回的最后一个值赋给变量	出错
表达式未返回值时	变量保持原值	变量被赋null值

 　　3、流程控制 if...else...，case，while（没有for循环）

--if else简单使用
declare @id int           --声明个变量
set @id=5                 --设置厨初始值
if(@id=1)
    begin
        print 'right'
    end
else if(@id=0)
    begin
        print 'error'
    end
else
    begin 
         print 'default'
    end

--while简单使用
Declare @i int,@sum int
set @i=0
set @sum=0
while @i<=100   --开始循环
begin
set @sum=@sum+@i

set @i=@i+1  --自增一
 end print @sum
结果
-----------

--case简单使用
Declare @iret int,@var varchar(10)
set @var='A'
select @iret=
case when @var='A' then 0
 when @var='B' then 1
 when @var='C' then 2
 when @var='D' then 3
 else -1
 end
 print @iret

结果
-------

　　4、事务

　　　　开始事务：BEGIN TRANSACTION
　　　　提交事务：COMMIT TRANSACTION
　　　　回滚事务：ROLLBACK TRANSACTION

　　　　1、使用存储过程执行事物，需要开启XACT_ABORT参数(默认值为Off)，将该参数设置为On，表示当执行事务时，如果出错,会将transcation设置　　　　　　为uncommittable状态，那么在语句块批处理结束后将回滚所有操作；如果该参数设置为Off，表示当执行事务时，如果出错，出错的语句将不会执行，其他正确的操作继续执行。(Set XACT_ABORT on)

　　　　2、Set NOCOUNT ON 不返回计数

USE [StoreTest]
GO

create Procedure  [dbo].[P_InsertUser]
@UserName varchar(100),
@UserPwd varchar(100)
AS
Begin
Set NOCOUNT ON; 
Set XACT_ABORT ON; --这句话非常重要

Begin try
    if(isnull(@UserName,'')='')
　　 begin
        print 'UserName is empty';
　　　　 return ;
    end

    declare @iCount int;
    set @iCount = 0;
    select @iCount = Count(1) from userinfo with(nolock) where username=@UserName;
    if( @iCount > 0 )
    begin
        print 'the current name already exist';
        return
    end
    Begin  Tran   --开始事务,事务中不能有return语句
    --insert
    insert into userinfo(
        username
        ,userpwd
        ,RegisterTime 
        )
        values(
        @UserName,
        @UserPwd,
        getdate()
        )

    Commit Tran       --提交事务
end try
begin catch
--在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务
--表示在事务内部发生错误了。Xact_state()有三种值：-1.事务不可提交；
--1.事务可提交；0.表示没有事务，此时commit或者rollback会报错。
if xact_state()=-1
begin
    rollback tran;   --事务回滚
    SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage;
end
end catch   
Set XACT_ABORT off; 
End

--调用存储过程
exec [P_InsertUser] '','admin'   --插入失败的
GO